public byte[] exportDrillthroughCsv(String queryName, int maxrows) { OlapStatement stmt = null; try { final OlapConnection con = olapDiscoverService.getNativeConnection( getQuery(queryName).getCube().getConnectionName()); stmt = con.createStatement(); String mdx = getMDXQuery(queryName); if (maxrows > 0) { mdx = "DRILLTHROUGH MAXROWS " + maxrows + " " + mdx; } else { mdx = "DRILLTHROUGH " + mdx; } ResultSet rs = stmt.executeQuery(mdx); return CsvExporter.exportCsv(rs); } catch (SQLException e) { throw new SaikuServiceException("Error DRILLTHROUGH: " + queryName, e); } finally { try { if (stmt != null) stmt.close(); } catch (Exception e) { } } }
public ResultSet drillthrough(String queryName, int maxrows, String returns) { OlapStatement stmt = null; try { final OlapConnection con = olapDiscoverService.getNativeConnection( getQuery(queryName).getCube().getConnectionName()); stmt = con.createStatement(); String mdx = getMDXQuery(queryName); if (maxrows > 0) { mdx = "DRILLTHROUGH MAXROWS " + maxrows + " " + mdx; } else { mdx = "DRILLTHROUGH " + mdx; } if (StringUtils.isNotBlank(returns)) { mdx += "\r\n RETURN " + returns; } ResultSet rs = stmt.executeQuery(mdx); return rs; } catch (SQLException e) { throw new SaikuServiceException("Error DRILLTHROUGH: " + queryName, e); } finally { try { if (stmt != null) stmt.close(); } catch (Exception e) { } } }
public ResultSet explain(String queryName) { OlapStatement stmt = null; try { final OlapConnection con = olapDiscoverService.getNativeConnection( getQuery(queryName).getCube().getConnectionName()); if (!con.isWrapperFor(RolapConnection.class)) throw new IllegalArgumentException("Cannot only get explain plan for Mondrian connections"); stmt = con.createStatement(); String mdx = getMDXQuery(queryName); mdx = "EXPLAIN PLAN FOR \n" + mdx; ResultSet rs = stmt.executeQuery(mdx); return rs; } catch (Exception e) { throw new SaikuServiceException("Error EXPLAIN: " + queryName, e); } finally { try { if (stmt != null) stmt.close(); } catch (Exception e) { } } }
public ResultSet drillthrough( String queryName, List<Integer> cellPosition, Integer maxrows, String returns) { OlapStatement stmt = null; try { IQuery query = getIQuery(queryName); CellSet cs = query.getCellset(); SaikuCube cube = getQuery(queryName).getCube(); final OlapConnection con = olapDiscoverService.getNativeConnection(cube.getConnectionName()); stmt = con.createStatement(); String select = null; StringBuffer buf = new StringBuffer(); buf.append("SELECT ("); for (int i = 0; i < cellPosition.size(); i++) { List<Member> members = cs.getAxes().get(i).getPositions().get(cellPosition.get(i)).getMembers(); for (int k = 0; k < members.size(); k++) { Member m = members.get(k); if (k > 0 || i > 0) { buf.append(", "); } buf.append(m.getUniqueName()); } } buf.append(") ON COLUMNS \r\n"); buf.append("FROM " + cube.getCubeName() + "\r\n"); SelectNode sn = (new DefaultMdxParserImpl().parseSelect(getMDXQuery(queryName))); final Writer writer = new StringWriter(); sn.getFilterAxis().unparse(new ParseTreeWriter(new PrintWriter(writer))); if (StringUtils.isNotBlank(writer.toString())) { buf.append("WHERE " + writer.toString()); } select = buf.toString(); if (maxrows > 0) { select = "DRILLTHROUGH MAXROWS " + maxrows + " " + select + "\r\n"; } else { select = "DRILLTHROUGH " + select + "\r\n"; } if (StringUtils.isNotBlank(returns)) { select += "\r\n RETURN " + returns; } log.debug("Drill Through for query (" + queryName + ") : \r\n" + select); ResultSet rs = stmt.executeQuery(select); return rs; } catch (Exception e) { throw new SaikuServiceException("Error DRILLTHROUGH: " + queryName, e); } finally { try { if (stmt != null) stmt.close(); } catch (Exception e) { } } }
/** * Exercises as many fields of the monitoring stats classes as possible. So that we can check that * they are being populated. */ public void testMe() throws SQLException { String queryString = "WITH MEMBER [Measures].[Foo] AS\n" + " [Measures].[Unit Sales]" + " + case when [Measures].[Unit Sales] > 0\n" + " then CInt( ([Measures].[Foo], [Time].PrevMember) )\n" + " end\n" + "SELECT [Measures].[Foo] on 0\n" + "from [Sales]\n" + "where [Time].[1997].[Q3].[9]"; final OlapStatement statement1 = getTestContext().getOlap4jConnection().createStatement(); CellSet cellSet = statement1.executeOlapQuery(queryString); StringWriter stringWriter = new StringWriter(); new RectangularCellSetFormatter(true).format(cellSet, new PrintWriter(stringWriter)); statement1.close(); println(stringWriter); final MondrianServer mondrianServer = MondrianServer.forConnection(getConnection()); final Monitor monitor = mondrianServer.getMonitor(); final ServerInfo server = monitor.getServer(); println("# stmts open: " + server.statementCurrentlyOpenCount()); println("# connections open: " + server.connectionCurrentlyOpenCount()); println("# rows fetched: " + server.sqlStatementRowFetchCount); println("# sql stmts open: " + server.sqlStatementCurrentlyOpenCount()); // # sql stmts by category (cell query, member query, other) // -- if you want to do this, capture sql statement events // cell cache requests // cell cache misses // cell cache hits final List<ConnectionInfo> connections = monitor.getConnections(); ConnectionInfo lastConnection = connections.get(connections.size() - 1); final List<StatementInfo> statements = monitor.getStatements(); StatementInfo lastStatement = statements.get(statements.size() - 1); println( "# cell cache requests, misses, hits; " + "by server, connection, mdx statement: " + server.cellCacheRequestCount + ", " + server.cellCacheMissCount() + ", " + server.cellCacheHitCount + "; " + lastConnection.cellCacheRequestCount + ", " + (lastConnection.cellCacheRequestCount - lastConnection.cellCacheHitCount) + ", " + lastConnection.cellCacheHitCount + "; " + lastStatement.cellCacheRequestCount + ", " + lastStatement.cellCacheMissCount + ", " + lastStatement.cellCacheHitCount); // cache misses in the last minute // cache hits in the last minute // -- build a layer on top of monitor that polls say every 15 seconds, // and keeps results for a few minutes println("number of mdx statements currently open: " + server.statementCurrentlyOpenCount()); println( "number of mdx statements currently executing: " + server.statementCurrentlyExecutingCount()); println( "jvm memory: " + server.jvmHeapBytesUsed + ", max: " + server.jvmHeapBytesMax + ", committed: " + server.jvmHeapBytesCommitted); println( "number of segments: " + server.segmentCount + ", ever created: " + server.segmentCreateCount + ", number of cells: " + server.cellCount + ", number of cell coordinates: " + server.cellCoordinateCount + ", average cell dimensionality: " + ((float) server.cellCoordinateCount / (float) server.cellCount)); println("Statement: " + lastStatement); println("Connection: " + lastConnection); println("Server: " + server); // number of mdx function calls cumulative // how many operations have been evaluated in sql? // number of members in cache // number of cells in segments // mdx query time // sql query time // sql rows // olap4j connection pool size // sql connection pool size // thread count // # schemas in schema cache // cells fulfilled by sql statements // mondrian server count (other stats relate to just one server) // // Events: // // SQL statement start // SQL statment stop // external cache call // sort // (other expensive operations similar to sort?) }
public static CellSet getResultSet(String mdx, OlapConnection conn) throws OlapException { OlapStatement statement = conn.createStatement(); CellSet cellSet = statement.executeOlapQuery(mdx); return cellSet; }