Version 2: Redesigning QueryDimension

Monday, December 19, 2011

You can download this version of the sample, and an improved version of the component library from here.

Once I've added drilling capabilities to QueryAxis, my next task has been redesigning QueryDimension member selection capabilities. My design goal was to improve the integration of this class with the UI components used to allow the user selecting the set of members to be included in the dimension. This goal pushed me to change the semantics associated to the include/exclude methods. The original olap4j implementation executes first all the includes and then all the excludes. My implementation executes the includes and excludes in the order they were invoked, so an hypothetical UI can include and exclude members as instructed by the user and get the resulting selection state immediately. For example, the following selection sequence:
    QueryDimension dim;











will produce a different set of selected members in my implementation (April to December) than in the olap4j QueryDimension (May to December).

Usage Sample

This is the code used to initialize the query in the sample web app. It selects only the states of USA for the Store hierarchy, all the members of the Gender hierarchy and shows only Unit Sales and Measures.
    private Query initQuery() throws OlapException {

        Cube c = getConnection().getOlapSchema().getCubes().get("Sales");

        Query q = new Query("MyQuery", c);

        QueryAxis columnsAxis = q.getAxis(Axis.COLUMNS);


        columnsAxis.addDimension(selectAll(q, "Gender"));


        QueryHierarchy measuresDim = q.getDimension("Measures");




                IdentifierNode.ofNames("Measures","Unit Sales").getSegmentList());



                IdentifierNode.ofNames("Measures","Store Cost").getSegmentList());



        QueryAxis rowsAxis = q.getAxis(Axis.ROWS);


        QueryHierarchy storeDim = q.getDimension("Store");

        storeDim.include(Operator.CHILDREN, IdentifierNode.ofNames("Store","USA").getSegmentList());


        rowsAxis.addDimension(selectAll(q, "Store Type"));


        return q;


    private QueryHierarchy selectAll(Query q, String dimension) throws OlapException {

        QueryHierarchy dim = q.getDimension(dimension);

        dim.include(Operator.DESCENDANTS, dim.getHierarchy().getRootMembers().get(0));

        return dim;


And this is the resulting output, after a pair of drills. Note that the drills controls at CA, OR and WA are a bug, they don't drill anything as those members have no children in this query hierarchy

Another design goal, suggested in a comment by Julian Hyde, was to define the selection mechanism in terms of hierarchies instead of dimensions, allowing selections on non-default hierarchies. So I renamed my QueryDimension to QueryHierarchy. My initial implementation supports only member selections using the operators MEMBER, CHILDREN, INCLUDE_CHILDREN and DESCENDANTS. The remaining member selections: ANCESTOR and SIBLING, can be implemented in terms of the previous ones and I decided to postpone implementation of level selections.

Implementation: Select as you Drill

The implementation of member selection is centered in the idea of including/excluding nodes at drilling time. Every usage of the hierarchy in a query axis is translated into a MDX expression with the following structure
    Exclude(DrilldownMember(<include expression>,<drill expression>, RECURSIVE), <exclude expression>)
Those sets are generated with the following algorithm:
    Initialize the <include expression> with the "roots" of the QueryHierarchy

    for every drilled member M
        add M to the <drill expression>
        add to the <exclude expression> the excluded children of M
        add to the <include expression> the "orphans" of M
The roots of the query hierarchy are those selected members having no selected ancestors in the query hierarchy. And the orphans of a member are those members, descendants of that member, having no selected ancestor below that member.
Another key point of the implementation is the way I store selection state for the members. It's stored as a tree of MemberSelectionState (an implementation class) keeping the operator includes and excludes issued for the member. And the children of the node are the children members that: override the selection dictated by its ancestor, or have any descendant overriding such a selection. This way of storing selections allows improvements to the previous algorithm that produce MDX expressions proportional in length to the number of drills executed on that usage of the hierarchy (refer to the QueryHierarchy.updateDrillSets() method implementation for details.

Query adaptation for handling hierarchies instead of dimensions

Using query hierarchies instead of query dimensions has an impact on the class Query. I've renamed the methods referring to the dimensions: getDimension to getHierarchy and getDimensions to getHierarchies. I've added the method getAvailableHierarchies to list the hierarchies that can be added to the current query. For a QueryHierarchy to be available nor It nor any hierarchy in the same dimension can be used in any axis.

Next Steps

Augment QueryHierarchy with methods to expand levels, allowing presenting a pre-drilled hierarchy to the user, and implementing a method to test if a certain member is drillable in the query hierarchy (has selected descendants).
Implement Level, ANCESTOR and SIBLING selections
Add filtering capabilities to Query
Implement a faces component to allow member selection.

Version 1: Drill-Enabled CellSet Table

Sunday, December 11, 2011
You can download this version of the sample, and an improved version of the component library from here.

Screenshot of the version 1 sample webapp. Using an standard color scheme and
image buttons for drilling.
This entry describes how to leverage olap4j, JSF standard components and the drilling capability presented in my previous entry to create a drillable cellset table alla JPivot. First I explain how to add the necessary drill buttons, and then I describe the strategy I've chosen to save the query state between requests.

Adding Drill Buttons

Let's start modifying the contents in the <olap:cellSetAxis
. I'll add a <h:commandButton/> to let the user drill/undrill a member in the cellset table. This button must be rendered only if the member has children, and will show a '-' if the member is already drilled or a '+' otherwise. This is the corresponding Facelet markup.
style="padding-left: #{m.member.depth}ex">


rendered="#{m.member.childMemberCount > 0}"

value="#{olapSample.isDrilled(component,m.position) ? '-' : '+'}"



To support this markup we'll add two methods to our managed bean:

  • boolean isDrilled(UIComponent source, List<Member> position);This method receives a component and a positioned member, and returns a boolean value indicating if that member is drilled or not.

  • boolean toggleDrill(UIComponent source, List<Member> position);This method receives a component and a positioned member, and modifies the current query to change the drill status of the positioned member.

This is the code snippet for toggleDrill
void toggleDrill(UIComponent c, List<Member> position)

            throws OlapException, SQLException {
        // Find the UICellSetAxis within the ancestors of 'c'
        while (c != null && !(c instanceof UICellSetAxis)) {
            c = c.getParent();
        if (c == null)

        // Get the query axis based on the UICellSetAxis information
        final UICellSetAxis axisComponent = (UICellSetAxis) c;
        QueryAxis queryAxis = getQuery().getAxis(

        // Toggle drill
        Member[] members = position.toArray(new Member[position.size()]);
        if (queryAxis.isDrilled(members))

        // Invalidate the CellSet caché
        cs = null;


The isDrilled method has a similar structure.
The only point remaining to be explained is the getQuery call, it's related to the query state saving strategy.

Query State Saving Strategy

The OlapSample managed bean is a request-scoped bean, so it doesn't keep state between requests. But, to make the table functional, we need to keep the drill state of current query between requests. So I'll save the query state in the ViewState. This would be the code:
Map<String, Object> viewState = FacesContext.getCurrentInstance().getViewRoot().getViewMap();


// Put the query in the View State

viewState.put("SavedQuery", query);


// Get the query from the View State

query = (Query)viewState.get("SavedQuery");

Unfortunately it won't work… Query instances cannot be serialized (mainly because they contain references to database connections), so they can't be added to the ViewState; I need helper class to create a serializable object from a Query instance and to reconstruct the original query from that object. That class, QuerySaver, has to static public methods:
  • Object saveQuery(Query q)Generates the serializable object from the query. It will be an array of objects containing the names of the query, the source cube, axis dimensions, drilled members, etc.
  • Query restoreQuery(OlapConnection cn, Object state)This method receives an object produced by saveQuery and an OlapConnection and recreates the original query.
So, the getQuery method is something like:
private Query getQuery() throws OlapException {

    if (query != null)


    Map<String, Object> viewMap = FacesContext.getCurrentInstance()


    Object savedQuery = viewMap.get("SavedQuery");

    if (savedQuery == null) {

            // There is no saved query, so we create the query used to
            // show our initial CellSet.

        query = initQuery();

    } else {

        query = QuerySaver.restoreQuery(getConnection(), savedQuery);




I'm going to save the query state just before rendering the cellset. To make this I'll use attach a listener method to the preRenderComponent event for the <olap:cellSetTable>

value="#{olapSample2.sampleCellSet}" …>




Let's Drill: Inner Workings

Saturday, December 10, 2011

This is the first of two entries documenting the process of adding drilling capabilities to OLAP-Faces. This entry starts explaining my reasons to rewrite the org.olap4j.query package and later describes the drilling capability added to that rewrite.

In the following entry I will apply this new package to add drilling capabilities to the <olap:cellSet> JavaServer Faces component and will provide you with a working example.

A Hard Decision: Rewrite the query Package

The current version of the specification provides the class DrillDownOnPositionTransform to drill a positioned member within a CellSet, it should work like:

// Create a query

Query q = new Query("myquery", salesCube);

QueryDimension productDim = q.getDimension("Product");

QueryDimension measuresDim = q.getDimension("Measures");




CellSet cs = q.execute();

// Generate the MDX to for drilling

DrillDownOnPositionTransform drillTransform =

new DrillDownOnPositionTransform(Axis.ROWS, 0, 0, cs);

SelectNode drilledMdx = drillTransform.apply(q.getSelect());

// Execute new query to get the drilled CellSet

OlapStatement stmt = salesCube.getSchema().getCatalog().getMetaData()


CellSet drilledCs = stmt.executeOlapQuery(drilledMdx);

Unfortunately that class is not implemented in the released version of olap4j ( and I’ve not been able to find a simple enough implementation; mainly because such an implementation must accept as input a generic MDX expression.

I think that drilling in a cell set is a must, so my proposal is: adding drilling support into the QueryAxis class. I’ll rewrite a highly simplified version of the org.olap4j.query package to produce a proof of concept for this approach.

Initially I’m going to ignore dimension selections, change notifications and query filtering (filtering has no sense without selections).

My proposed use case for drilling is

// Create a query

Query q = new Query("myquery", salesCube);

QueryDimension productDim = q.getDimension("Product");

QueryDimension measuresDim = q.getDimension("Measures");



CellSet cs = q.execute();

// Drill it

Member defaultMember =




CellSet drilledCellSet = q.execute();

In this code all the classes in org.olap4j.query package have been replaced by classes in the es.cgalesanco.olap4j.query package.

Translating QueryAxis.drill() into MDX

Drilling a one-dimensional axis is easy: just use DrilldownMember MDX function passing the initial member set for the dimension as the first parameter, the set of drilled members as the second parameter and request for recursive drill resolution.

For example:


  {[Store].[All Stores]},    

  {[Store].[All Stores],[Store].[USA],[Store].[USA].[OR]},


Produces an axis with the following structure

|-All Stores






    | |-Portland

    | |- Salem


An easy way to extend this to multi-dimensional axes is

1.       generate a DrillDonwMember call, grouping all the drills having the same prefix (drill specifications with the same length differing only in the last element)

2.       put these expressions in  CrossJoins to generate a set of tuples as required for the axis

3.       compute the union of all the previously generated cross joins and hierarchize

So for an axis with two dimensions ([Store] and [Store Type]) and the following sequence of drills

1.       [Store].[All Stores]

2.       [Store].[USA], [Store Type].[All Store Types]

3.       [Store].[USA]

4.       [Store].[All Stores].[Store Type].[All Store Types]

The generated MDX will be





        {[Store].[All Stores]},

        {[Store].[All Stores],[Store].[USA]},



      [Store Type].[All Store Types]





        {[Store Type].[All Store Types]},

        {[Store Type].[All Store Types]},





      {[Store].[All Stores]},


        {[Store Type].[All Store Types]},

        {[Store Type].[All Store Types]},






This is a basic algorithm and clearly optimizable, but it will do for my proof of concept.

QueryAxis’ New Methods

So our revamped QueryAxis will contain an additional list of drilled positions, supported with these new methods

void drill(Member[] drilledPos);

This method adds drilledPos to the list of drilled positions. If that position was already drilled it’s a no-op.

void undrill(Member[] drilledPos);

This method removes drilledPos from the list of drilled positions.

boolean isDrilled(Member[] drilledPos);

This method returns a boolean value indicating if drilledPos is in the list of drilled positions.

List<Member[]> listDrilledPositions();

This method returns the list of drilled positions.

Version 0: Rendering a CellSet

Wednesday, December 07, 2011
You can download the source code for the v0 component library and the sample web project here.

The focus for this initial version is about rendering an arbitrary cellset result. In the previous post I proposed a component structure with a main UICellSet component acting as a container of two UICellSetAxis component instances, one for each CellSetAxis, and a UICellSetCells component to handle the data cells.

I will use the delegated implementation rendering model for those components. So, following the pattern used in the JSF API, I’ll extend those component classes to handle HTML specific properties and methods. The resulting classes will be

·         HtmlCellSet, extending UICellSet

·         HtmlCellSetAxis, extending UICellSetAxis, and

·         HtmlCellSetCells, extending UICellSetCells

These classes will have an associated set of renderer classes: HtmlCellSetRenderer, HtmlCellSetCellsRenderer, HtmlColumnsAxisRenderer and HtmlRowsAxisRenderer. The last two renderers both render a HtmlCellSetAxis, but are specialized to render a columns (ordinal 0) axis and a rows (ordinal 1) axis. This is achived overriding the HtmlCellSetAxis.getRenderType() method to return a different type of renderer based in the type of CellSetAxis its bound to


       public String getRendererType() {

             if (isFor(TableArea.columnAxis))

                    return "es.cgalesanco.faces.olap4j.columnsCellSetAxis";


                    return "es.cgalesanco.faces.olap4j.rowsCellSetAxis";


HtmlCellSetRenderer, drives the global rendering of a CellSet.

·         encodeBegin() renders the starting <table> element, the upper-left corner cell, and delegates (indirectly) on HtmlColumnsAxisRenderer the rendering of the <tr> tags for the rows containing the columns axis. In the last row of the columns axis, it delegates on HtmlRowsAxisRenderer to render the header cells for the rows axis.

·         encodeChildren() renders the cell set rows containing the rows axis and the data cells. Renders the <tr> elements and computes the first cell of the rows axis to be rendered in the row (taking into account previous cells rows spans); delegates the rendering of the cells on HtmlRowsAxisRenderer and HTmlCellSetCellsRenderer.

The following colored HTML shows which renderers renders which tag:






     <th>&nbsp;</th><th colspan=”2”>Measures</th>



     <th>Store</th><th>Unit Sales</th></th>Store Cost</th>



     <th>All Stores</th><td>266,733</td><td>225,627.23</td>










Styling the CellSet

This distribution of responsibilities rendering the table is used to style the table using the properties of the HTML components. The properties of these components are


·         styleClass. The HTML style class passed through to the class attribute of the main <table> element.

·         cornerClass. The HTML style class passed through to the <th> element rendering the corner cell.

·         alternateClass. The HTML style class passed through to the <tr> elements starting the odd rows rendered by the HtmlCellSetRenderer (the gray rows in the previous colored HTML)


·         styleClass. The HTML style class passed through to the <tr> element rendered by the HtmlColumnsAxisRenderer or the <col> element rendered by the HtmlRowsAxisRenderer.

·         headerClass. The HTML style class passed through to the <th> elements containing the hierarchy headers.

Wrapping It Up

First, the @ManagedBean backing our sample CellSetTable; just change the getConnection() method to fit your olap4j provider and connection string. Caching the resulting CellSet is important, as the method getSampleCellSet() can be invoked repeatedly within the faces components.


public class OlapSample {

   private OlapConnection cn;

   private CellSet cs;


   public OlapConnection getConnection() throws SQLException {

      if ( cn != null )

          return cn;


      try {


      } catch(ClassNotFoundException ex) {

          throw new RuntimeException(ex);


      Connection jdbcCn = DriverManager.getConnection("jdbc:mondrian:"

             + "JdbcDrivers=com.mysql.jdbc.Driver;"

             + "Jdbc=jdbc:mysql://localhost/foodmart;"

             + "JdbcUser=root;JdbcPassword=root;"

             + "Catalog=file:/users/cesar/FoodMart.xml");

      return cn = jdbcCn.unwrap(OlapConnection.class);




   public void tearDown() throws SQLException {

      if ( cn != null )




   public CellSet getSampleCellSet() throws ClassNotFoundException, SQLException {

      if ( cs != null )

          return cs;


      OlapConnection cn = getConnection();


      return cs = cn.createStatement().executeOlapQuery(

             "SELECT " +

             "    CrossJoin([Gender].AllMembers,[Measures].AllMembers) ON COLUMNS," +

             "    NON EMPTY CrossJoin([Store].AllMembers,[Store Type].AllMembers) ON ROWS " +

             "FROM Sales");



And the Facelets XHTML page

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"


<html xmlns=""






   <title>First CellSetTable</title>


   <style type="text/css">

   … see below …




   <olap:cellSetTable value="#{olapSample.sampleCellSet}"





     <olap:cellSetAxis forAxis="columns" var="m"



        <f:facet name="header">

           <h:outputText value="#{m.hierarchy.caption}" />


        <h:outputText value="#{m.member.caption}"/>



     <olap:cellSetAxis forAxis="rows" var="m"



        <f:facet name="header">

           <h:outputText value="#{m.hierarchy.caption}"/>


        <!-- Indents the member name based on its depth -->

        <h:outputText value="#{m.member.caption}" style="padding-left:#{m.member.depth}ex"/>



     <olap:cellSetCells var="cell">

        <h:outputText value="#{cell.cell.formattedValue}"/>              






It has the structure discussed in my previous post; I’ve added the class attributes to allow styling and you can see how I’ve implemented member indentation in the rows axis using the Member.getDepth()method.

And this is the CSS styles I’ve used to render the table. They pretend to be as pedagogical as possible, so I beg your pardon about the color scheme.

     body { font-family:Verdana,Helvetica; font-size:small }


     /* Every header cell will be aligned on the left and top */

     .cellSet th { text-align:left;white-space:nowrap;vertical-align:top; }


     /* Every data cell will be aligned on the right */

     .cellSet td { text-align:right;white-space:nowrap }


     /* Sets background color for rowsAxis cells */

     .cellSet col.rowsAxisClass { background-color:red }


     /* Sets background color for columnsAxis cells */

     .cellSet tr.columnsAxisClass { background-color:blue;color:white; }


     /* Overrides colors for hierarchy header cells within the columnsAxis */

     .cellSet th.columnsAxisHeaderClass { background-color:darkblue; color:white; }


     /* Overrides colors for hierarchy header cells within the rowsAxis */

     .cellSet th.rowsAxisHeaderClass { background-color:maroon; color:white }


     /* Sets style for the upper-left corner empty cell */

     .cellSet th.cellSetCorner { background-color:yellow; }


     /* Sets style for alternating rowsAxis background color */

     .cellSet tr.alternate th {background-color:darksalmon}


     /* Sets style for alternating data cells background color */

     .cellSet tr.alternate td {background-color:gainsboro}