public Cube getNativeCube(SaikuCube cube) throws SaikuOlapException {
try {
OlapConnection con = connections.getOlapConnection(cube.getConnectionName());
if (con != null) {
for (Database db : con.getOlapDatabases()) {
Catalog cat = db.getCatalogs().get(cube.getCatalogName());
if (cat != null) {
for (Schema schema : cat.getSchemas()) {
if (schema.getName().equals(cube.getSchemaName())) {
for (Cube cub : schema.getCubes()) {
if (cub.getName().equals(cube.getName())
|| cub.getUniqueName().equals(cube.getUniqueName())) {
return cub;
}
}
}
}
}
}
}
} catch (Exception e) {
throw new SaikuOlapException("Cannot get native cube for ( " + cube + " )", e);
}
throw new SaikuOlapException("Cannot get native cube for ( " + cube + " )");
}
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 List<SaikuCube> getCubes(String connectionName) {
OlapConnection olapcon = connections.getOlapConnection(connectionName);
List<SaikuCube> cubes = new ArrayList<SaikuCube>();
if (olapcon != null) {
try {
for (Catalog cat : olapcon.getOlapCatalogs()) {
for (Schema schem : cat.getSchemas()) {
for (Cube cub : schem.getCubes()) {
cubes.add(
new SaikuCube(
connectionName,
cub.getUniqueName(),
cub.getName(),
cub.getCaption(),
cat.getName(),
schem.getName(),
cub.isVisible()));
}
}
}
} catch (OlapException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
Collections.sort(cubes, new SaikuCubeCaptionComparator());
return cubes;
}
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) {
}
}
}
/**
* Adds a catalog and its children to the cache. Do not use directly. This must be called with a
* write lock on the cache.
*
* @param catalogName The name of the catalog to load in cache.
*/
private void addCatalogToCache(IPentahoSession session, String catalogName) {
final IOlapService.Catalog catalog =
new Catalog(catalogName, new ArrayList<IOlapService.Schema>());
OlapConnection connection = null;
try {
connection = getConnection(catalogName, session);
for (org.olap4j.metadata.Schema schema4j : connection.getOlapSchemas()) {
connection.setSchema(schema4j.getName());
final IOlapService.Schema schema =
new Schema(
schema4j.getName(),
catalog,
new ArrayList<IOlapService.Cube>(),
new ArrayList<String>(connection.getAvailableRoleNames()));
for (org.olap4j.metadata.Cube cube4j : schema4j.getCubes()) {
schema.cubes.add(new IOlapService.Cube(cube4j.getName(), cube4j.getCaption(), schema));
}
catalog.schemas.add(schema);
}
// We're done.
getCache(session).add(catalog);
} catch (OlapException e) {
LOG.warn("Failed to initialize the olap connection cache for catalog " + catalogName, e);
} finally {
try {
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
LOG.warn("Failed to gracefully close an olap connection to catalog " + catalogName, e);
}
}
}
public void setCellValue(
String queryName, List<Integer> position, String value, String allocationPolicy) {
try {
IQuery query = getIQuery(queryName);
OlapConnection con =
olapDiscoverService.getNativeConnection(query.getSaikuCube().getConnectionName());
Scenario s;
if (query.getScenario() == null) {
s = con.createScenario();
query.setScenario(s);
con.setScenario(s);
System.out.println("Created scenario:" + s + " : cell:" + position + " value" + value);
} else {
s = query.getScenario();
con.setScenario(s);
System.out.println("Using scenario:" + s + " : cell:" + position + " value" + value);
}
CellSet cs1 = query.execute();
query.storeCellset(cs1);
Object v = null;
try {
v = Integer.parseInt(value);
} catch (Exception e) {
v = Double.parseDouble(value);
}
if (v == null) {
throw new SaikuServiceException("Error setting value of query " + queryName + " to:" + v);
}
allocationPolicy = AllocationPolicy.EQUAL_ALLOCATION.toString();
AllocationPolicy ap = AllocationPolicy.valueOf(allocationPolicy);
CellSet cs = query.getCellset();
cs.getCell(position).setValue(v, ap);
con.setScenario(null);
} catch (Exception e) {
throw new SaikuServiceException("Error setting value: " + queryName, e);
}
}
/** @see org.pivot4j.analytics.datasource.DataSourceManager#getCubes(java.lang.String) */
@Override
public List<CubeInfo> getCubes(String catalogName) {
if (catalogName == null) {
throw new NullArgumentException("catalogName");
}
SimpleDataSourceInfo definition = getDefinition(catalogName);
if (definition == null) {
throw new IllegalArgumentException(
"Data source with the given name does not exist : " + catalogName);
}
OlapDataSource dataSource = createDataSource(definition);
List<CubeInfo> cubes = new LinkedList<CubeInfo>();
OlapConnection connection = null;
try {
connection = dataSource.getConnection();
for (Cube cube : connection.getOlapSchema().getCubes()) {
if (cube.isVisible()) {
cubes.add(new CubeInfo(cube.getName(), cube.getCaption(), cube.getDescription()));
}
}
} catch (SQLException e) {
throw new PivotException(e);
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
throw new PivotException(e);
}
}
}
return cubes;
}
/**
* Flushes all catalogs in the catalogNames collection. If hosted=true the method breaks after the
* first successful schemaCacheFlush, since we know that all schemas will be flushed by the
* operation. For remote we assume that each needs to be flushed separately, since there are
* possibly multiple servers.
*/
private void flushCatalogs(Collection<String> catalogNames, IPentahoSession session)
throws SQLException {
for (String name : catalogNames) {
OlapConnection connection = null;
try {
connection = getConnection(name, session);
XmlaHandler.XmlaExtra xmlaExtra = getXmlaExtra(connection);
if (xmlaExtra != null) {
xmlaExtra.flushSchemaCache(connection);
}
} catch (Exception e) {
LOG.warn(
Messages.getInstance()
.getErrorString("MondrianCatalogHelper.ERROR_0019_FAILED_TO_FLUSH", name),
e);
} finally {
if (connection != null) {
connection.close();
}
}
}
}
public static void main(String[] args) throws Exception {
// Load the driver
Class.forName("mondrian.olap4j.MondrianOlap4jDriver");
// Connect
final Connection connection =
DriverManager.getConnection(
"jdbc:mondrian:" // Driver ident
+ "Jdbc=jdbc:hsqldb:file:foodmart/foodmart;" // Relational DB
+ "Catalog=file:foodmart/FoodMart.xml;"); // Catalog
// We are dealing with an olap connection. we must unwrap it.
final OlapConnection olapConnection = connection.unwrap(OlapConnection.class);
// Get a cube object.
Cube salesCube = olapConnection.getOlapSchema().getCubes().get("Sales");
// Build a query object.
Query myQuery = new Query("myQuery", salesCube);
// Lookup some dimensions
QueryDimension productDim = myQuery.getDimension("Product");
QueryDimension storeDim = myQuery.getDimension("Store");
QueryDimension timeDim = myQuery.getDimension("Time");
// Place dimensions on some axis
myQuery.getAxis(Axis.COLUMNS).addDimension(productDim);
myQuery.getAxis(Axis.ROWS).addDimension(storeDim);
myQuery.getAxis(Axis.FILTER).addDimension(timeDim);
// Including a member by metadata
Member year1997 =
salesCube.lookupMember(IdentifierNode.ofNames("Time", "1997").getSegmentList());
timeDim.include(year1997);
// Including a member by name parts
productDim.include(
Selection.Operator.CHILDREN,
IdentifierNode.ofNames("Product", "Drink", "Beverages").getSegmentList());
// We can also exclude members
productDim.exclude(
IdentifierNode.ofNames("Product", "Drink", "Beverages", "Carbonated Beverages")
.getSegmentList());
// Validate this query
myQuery.validate();
// Print!
System.out.println("/********************* QUERY ***********************/");
System.out.println(myQuery.getSelect().toString());
System.out.println("/********************* QUERY ***********************/");
System.out.println(" ");
System.out.println(" ");
System.out.println(" ");
System.out.println(" ");
System.out.println("/********************* RESULTS ***********************/");
RectangularCellSetFormatter formatter = new RectangularCellSetFormatter(false);
PrintWriter writer = new PrintWriter(System.out);
formatter.format(myQuery.execute(), writer);
writer.flush();
System.out.println("/********************* RESULTS ***********************/");
}
public SaikuConnection getConnection(String connectionName) throws SaikuOlapException {
OlapConnection olapcon = connections.getOlapConnection(connectionName);
SaikuConnection connection = null;
if (olapcon != null) {
List<SaikuCatalog> catalogs = new ArrayList<SaikuCatalog>();
try {
for (Catalog cat : olapcon.getOlapCatalogs()) {
List<SaikuSchema> schemas = new ArrayList<SaikuSchema>();
for (Schema schem : cat.getSchemas()) {
List<SaikuCube> cubes = new ArrayList<SaikuCube>();
for (Cube cub : schem.getCubes()) {
cubes.add(
new SaikuCube(
connectionName,
cub.getUniqueName(),
cub.getName(),
cub.getCaption(),
cat.getName(),
schem.getName(),
cub.isVisible()));
}
Collections.sort(cubes, new SaikuCubeCaptionComparator());
schemas.add(new SaikuSchema(schem.getName(), cubes));
}
if (schemas.size() == 0) {
OlapDatabaseMetaData olapDbMeta = olapcon.getMetaData();
ResultSet cubesResult = olapDbMeta.getCubes(cat.getName(), null, null);
try {
List<SaikuCube> cubes = new ArrayList<SaikuCube>();
while (cubesResult.next()) {
cubes.add(
new SaikuCube(
connectionName,
cubesResult.getString("CUBE_NAME"),
cubesResult.getString("CUBE_NAME"),
cubesResult.getString("CUBE_NAME"),
cubesResult.getString("CATALOG_NAME"),
cubesResult.getString("SCHEMA_NAME")));
}
Collections.sort(cubes, new SaikuCubeCaptionComparator());
schemas.add(new SaikuSchema("", cubes));
} catch (SQLException e) {
throw new OlapException(e.getMessage(), e);
} finally {
try {
cubesResult.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
Collections.sort(schemas);
catalogs.add(new SaikuCatalog(cat.getName(), schemas));
}
} catch (OlapException e) {
throw new SaikuOlapException(
"Error getting objects of connection (" + connectionName + ")", e);
}
Collections.sort(catalogs);
connection = new SaikuConnection(connectionName, catalogs);
return connection;
}
throw new SaikuOlapException("Cannot find connection: (" + connectionName + ")");
}
public static CellSet getResultSet(String mdx, OlapConnection conn) throws OlapException {
OlapStatement statement = conn.createStatement();
CellSet cellSet = statement.executeOlapQuery(mdx);
return cellSet;
}
public CellDataSet execute(String queryName, ICellSetFormatter formatter) {
String runId = "runId:" + ID_GENERATOR.getAndIncrement();
try {
// System.out.println("Execute: ID " + Thread.currentThread().getId() + " Name: " +
// Thread.currentThread().getName());
IQuery query = getIQuery(queryName);
OlapConnection con =
olapDiscoverService.getNativeConnection(query.getSaikuCube().getConnectionName());
Long start = (new Date()).getTime();
if (query.getScenario() != null) {
log.info(
runId
+ "\tQuery: "
+ query.getName()
+ " Setting scenario:"
+ query.getScenario().getId());
con.setScenario(query.getScenario());
}
if (query.getTag() != null) {
query = applyTag(query, con, query.getTag());
}
String mdx = query.getMdx();
log.info(runId + "\tType:" + query.getType() + ":\n" + mdx);
CellSet cellSet = query.execute();
Long exec = (new Date()).getTime();
if (query.getScenario() != null) {
log.info("Query (" + queryName + ") removing scenario:" + query.getScenario().getId());
con.setScenario(null);
}
CellDataSet result = OlapResultSetUtil.cellSet2Matrix(cellSet, formatter);
Long format = (new Date()).getTime();
log.info(
runId
+ "\tSize: "
+ result.getWidth()
+ "/"
+ result.getHeight()
+ "\tExecute:\t"
+ (exec - start)
+ "ms\tFormat:\t"
+ (format - exec)
+ "ms\t Total: "
+ (format - start)
+ "ms");
result.setRuntime(new Double(format - start).intValue());
getIQuery(queryName).storeCellset(cellSet);
return result;
} catch (Exception e) {
if (log.isInfoEnabled()) {
String error = ExceptionUtils.getRootCauseMessage(e);
log.info(runId + "\tException: " + error);
}
throw new SaikuServiceException(runId + "\tCan't execute query: " + queryName, e);
} catch (Error e) {
if (log.isInfoEnabled()) {
String error = ExceptionUtils.getRootCauseMessage(e);
log.info(runId + "\tError: " + error);
}
throw new SaikuServiceException(runId + "\tCan't execute query: " + queryName, e);
}
}
