Executing Queries

Executing a query against an OLAP server is similar to what you would do for a relational database. Queries can consist of a query parsed into a SelectNode object, as in Listing Five(a), or executed using a String of MDX, as in Listing Five(b).

Listing Five(a): Executing a query with SelectNode.

// We can execute the parsed SelectNode itself...
CellSet data =
  oConn
    .createStatement()
      .executeOlapQuery(parsedObject);

Listing Five(b): Executing a query with MDX.

// ...or use the MDX query directly.
data =
  oConn
    .createStatement()
      .executeOlapQuery(myQuery);

Reading the Results of an OLAP Query

Once the query is executed, you can explore the returned data. A CellSet object is different than a ResultSet. You must think of CellSets as data organized on any number of axes. On each of those axes, there are a series of positions. Each position describes a set of tuples, formed by cross-joining all the members of the current axis.

Listing Six: Iterating over the results of an olap4j query

// Iteration over a two-axis query
for (
  Position axis_0 
  : data.getAxes().get( Axis.ROWS.axisOrdinal() ).getPositions()) 
{
  for (
    Position axis_1
    : data.getAxes().get(Axis.COLUMNS.axisOrdinal()).getPositions())
  {
    Object value =
      data.getCell(axis_0, axis_1)
        .getValue();
  }
}

The code in Listing Six demonstrates how to iterate over the results of a query that included only two axes; columns and rows. If your query uses more than two axes, will need to iterate over each of them. In our simple two-dimensional use case, olap4j includes a very handy helper package to help display the results of the query:

// We use the utility formatter.
RectangularCellSetFormatter formatter =
  new RectangularCellSetFormatter(false);

// Print out.
PrintWriter writer = new PrintWriter(System.out);
formatter.format(cellSet, writer);
writer.flush();

Going Further

With the information presented so far, you should now be able to connect, execute queries against an OLAP server and obtain meaningful results. The olap4j project can get you far beyond this point. The Resources section contains links to materials that showcase the most advanced features, including how to build a user interface to explore OLAP data sources using a programmatic query model, statistical simulations, and real-time updates.

Resources

olap4j.org: Home of the olap4j project.

olap4j.org/api: The latest API reference.

olap4j download resources.

— Luc Boudreau is a co-manager of the olap4j project and Mondrian, an open source OLAP engine. He works at Pentaho Corporation as a Senior Software Engineer and is an active member of many community projects related to business intelligence.