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;
}
