An Experiment: Using Javascript to Display CellSet Data

I've just started to use Javascript professionally so I've decided to apply my new knowledge to experiment on using this technology to display MDX data. My goal was restricted to display a CellSetAxis from a fixed query, allowing the user to drill down/drill up on any member.

Version 7: Added Support for Level Selections

Live Demo updated with this version

Source code can be found here

My goal for this release was adding support for level selections in QueryHierarchy. I do know it wouldn't be easy, but it was harder than expected. So this version implements a UI rather clumsy to include/exclude levels from a QueryHierarchy: I've added a Level option to the operator selector that lets you include or exclude a level by acting upon any member of that level. The following screenshot shows the query hierarchy editor for the [Store] hierarchy after including the [Store].[Store Country] and [Store].[Store City] hierarchies. And the resulting cellset table after collapsing [Store].[Canada]

Version 6: Hierarchy Expansion, let’s drill-up

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

This version includes the capability to expand/collapse a hierarchy in the cellset table. An expanded hierarchy initially shows all of its members, and the user can drill-up to hide details. A collapsed hierarchy initially shows only its root members, and the user can drill-down to show further detail.

Expanded Hierarchy: Store Type hierarchy expanded with some undrilled members

I've modified the queryCellSet composite component (and QueryCellSetBean, its associated managed bean) to include a hierarchy expansion button in the header of both cellset axes, allowing the user toggling the expand state for a hierarchy.

This functionality is supported by three methods in the QueryAxis class:

• void expandHierarchy(QueryHierarchy h)Expands the hierarchy, invalidating any previous drill/undrill operation on every appearance of h in this axis
• void collapseHierarchy(QueryHierarchy h)
  Collapses the hierarchy, invalidating any previous drill/undrill operation on every appearance of h in this axis
• boolean isExpanded(QueryHierarchy h)
  Tests if the provided hierarchy is expanded in this axis.

QueryHierarchy Expression Generation

The strategy to MDX generation for an expanded hierarchy is as follows:

• A fully expanded hierarchy generates this MDX:
  DESCENDANTS(<root members set>, 0, SELF_AND_AFTER)
• Member undrill is implemented by an external EXCEPT:
  EXCEPT(
      DESCENDANTS(<root members set>, 0, SELF_AND_AFTER),
      DESCENDANTS(<undrilled members set>, 0, AFTER)
  )

Refactoring Axis Set Expression Generation

Previously, axis MDX expression generation was driven by the drill tree. I've refactored this logic to let the axis expression generation be driven by the QueryHierarchy inclusion/exclusion tree.

This is a simplified version of the function to generate an axis set expression for a QueryHierarchy

  /**
   * Recursively generates the set expression for a Query Hierarchy.
   *
   * @param current
   *            currently visited node
   * @param expander
   *            helper object to execute drills/expansions
   * @param drillList
   *            members to drill/undrill
   * @param expression
   *            generated expression
   */
  @SuppressWarnings("unused")
  private void toOlap4jQueryDidactic(VisitingInfo current,
      HierarchyExpander expander, List<Member> drillList,
      AxisExpression expression) {
    boolean isMemberIncluded =
        current.getEffectiveSign(Operator.MEMBER) == Sign.INCLUDE;
    boolean areChildrenIncluded =
        current.getEffectiveSign(Operator.CHILDREN) == Sign.INCLUDE;
    boolean areDescendantsIncluded =
        current.getEffectiveSign(Operator.DESCENDANTS) == Sign.INCLUDE;


    // Processes current member, including or excluding it from the
    // expression as necessary.
    Member currentMember = current.getMember();
    if (isMemberIncluded) {
      expression.include(currentMember);
      if (expander.isDrilled(currentMember, drillList)) {
        // Current member is included in the hierarchy but collapsed.
        // Ends this visit as we should not include any descendant.
        return;
      }
    }

    // Recursively calls toOlap4jQueryDidactic on overrided children
    List<Member> overridedChildren = new ArrayList<Member>();
    for (SelectionTree overridedChild : current.getNode()
                        .getOverridingChildren()) {
      overridedChildren.add(overridedChild.getMember());

      VisitingInfo childVisit = current.visitChild(overridedChild);
      toOlap4jQueryDidactic(
          childVisit,
          expander,
          drillList,
          expression);
    }

    if (areDescendantsIncluded) {
      // Expand/undrill descendants
      MemberSet expansionBase;
      if (areChildrenIncluded) {
        expansionBase = new ChildrenMemberSet(currentMember,
            overridedChildren);
      } else {
        expansionBase = new GrandchildrenSet(currentMember,
            overridedChildren);
      }
      expander.expand(expansionBase, drillList, expression);
    } else {
      // Include children if necessary
      if (areChildrenIncluded) {
        MemberSet nonOverridingChildren
          = new ChildrenMemberSet(currentMember, overridedChildren);
        expression.include(nonOverridingChildren.getMdx());
      }
    }
  }
}

Version 3: Part I, Drilling CellSet Encapsulation

First of all: Happy New Year to everybody!!

During this holidays I've working on implementing capabilities visually edit a query: adding/removing of hierarchies and inclusion/exclusion of members of those hierarchies. I'll explain you my achievements in two blogs entries: this one explains the AbstractQueryBean and its usage to provide an easy to use drillable cellset table, the next one will explain components allowing selection of hierarchies and inclusion/exclusion of its members.

Anyway, for the impatient among us, this is a screenshot of "version 3", and a link to the source code.

An Abstract Managed Bean to Handle Query State

The version in my previous blog entry used a helper class to serialize/deserialize the Query instance, and put it explicitly into the ViewState. This strategy works but has two main drawbacks: it's not easily extensible, and the name used to store the serialized query into the view state can silently clash with the names of other objects.
After some refactoring cycles I've ended up with a better solution: use a ViewScoped managed bean to manage the state of the query, and use the standard Java serialization mechanism to save and restore that state. This bean will handle connection management, needed to support the query deserialization; and caching for the query CellSet result.
To accomplish the vision of an easily pluggable component library I've added an abstract class AbstractQueryBean intended to serve as a base class for concrete view
scoped managed beans. This class defines two abstract methods to be implemented by deriving classes:

• OlapConnection initConnection()Classes derived from AbstractQueryBean must implement this method to return a connection to the olap4j provider.
  
• Query initQuery()Classes derived from AbstractQueryBean must implement this method to return newly a initialized query.
  

This abstract class, included in the olap4j-faces jar, implements writeObject and readObject to serialize the query state. The implementations of those members parallel those of the previous helper class, with an important difference: after serializing the query state, writeObject tears down the connection with the olap4j provider.
As I've pointed before, this class provides also with caching for the resulting CellSet from the query. The method getCellSet() tests for a cached CellSet before executing the current query. Convenience methods included in this class altering the query state invalidate this cache, to force a query execution in the next call to getCellSet(). And there is a invalidateCellSet() method to force this cache invalidation if the user of the class modifies externally the state of the query.
I'll change this manual cache invalidation mechanism and replace it with another based on QueryListener as the one used in the original query package.
Follows a minimal example of how to use this class to create a managed bean to serve as backing bean for drillable cellset:
package olaptest;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;


import javax.faces.bean.ManagedBean;
import javax.faces.bean.ViewScoped;


import org.olap4j.Axis;
import org.olap4j.OlapConnection;
import org.olap4j.OlapException;
import org.olap4j.mdx.IdentifierNode;
import org.olap4j.metadata.Cube;


import es.cgalesanco.faces.olap4j.managedBeans.AbstractQueryBean;
import es.cgalesanco.olap4j.query.Query;
import es.cgalesanco.olap4j.query.QueryAxis;
import es.cgalesanco.olap4j.query.QueryHierarchy;
import es.cgalesanco.olap4j.query.Selection.Operator;

@ManagedBean

@ViewScoped // View scoped to save the state of the query between requests

public
class SampleQueryBean extends AbstractQueryBean{
    private static final long serialVersionUID = 1L;
    @Override    
    protected OlapConnection initConnection() throws OlapException {
        …
        Connection jdbcCn = DriverManager.getConnection(…);

        …
        return jdbcCn.unwrap(OlapConnection.class);
    }

    @Override    
    protected Query initQuery() throws OlapException {
        Cube c = getConnection().getOlapSchema().getCubes().get("Sales");
        Query query = new Query("MyQuery", c);
        QueryAxis columnsAxis = query.getAxis(Axis.COLUMNS);
        columnsAxis.setNonEmpty(true);
        columnsAxis.addHierarchy(query.getHierarchy("Gender"));
        …
        return query;
    }
}

Composite Control To Encapsulate Drilling CellSet

Insisting on easy construction of olap4j/JSF applications I've encapsulated the previous sample of a drillable cellset into a composite component using the AbstractQueryBean. This version of the composite components adds functionality to remove a hierarchy from the query by clicking on a close button in the hierarchy header cell.
So, the following code snippet, includes a drillable cellset table using the previous sample query bean:
        <olap:queryCellSet id="table" query="#{sampleQueryBean}">


Yeah, that's all folks. And, a slightly more complicated version using AJAX to update the table on drills and hierarchy removals
    <h:form id="form">
         …

        <olap:queryCellSet id="table" query="#{queryBean}">

            <f:ajax event="drill" render=":form:table"/>

            <f:ajax event="axisChange" render=":form:table"/>

        </olap:queryCellSet>            


 

Version 2: Redesigning QueryDimension

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;

  

    dim.include(

        Operator.DESCENDANTS,

        IdentifierNode.ofNames("Time","2000").getSegmentList());

    dim.exclude(

        Operator.DESCENDANTS,

        IdentifierNode.ofNames("Time","2000","Q1").getSegmentList());

    dim.include(

        Operator.MEMBER,

        IdentifierNode.ofNames("Time","2000","Q1","April").getSegmentList());



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.setNonEmpty(true);

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

        

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

        columnsAxis.addDimension(measuresDim);

        measuresDim.include(

                Operator.MEMBER,

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

        measuresDim.include(

                Operator.MEMBER,

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

        

  

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

        rowsAxis.setNonEmpty(true);

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

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

        rowsAxis.addDimension(storeDim);

        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

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
forAxis="rows"/>. 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.
<span
style="padding-left: #{m.member.depth}ex">

<h:commandButton

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

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

        action="#{olapSample.toggleDrill(component,m.position)}"
    <h:outputText
value="#{m.member.caption}"
/>

</span>


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
    public
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)
            return;


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


        // Toggle drill
        Member[] members = position.toArray(new Member[position.size()]);
        if (queryAxis.isDrilled(members))
            queryAxis.undrill(members);
        else
            queryAxis.drill(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)

        return
query;



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

        .getViewRoot().getViewMap();

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

    }



    return
query;

}



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>


<olap:cellSetTable
value="#{olapSample2.sampleCellSet}" …>

<f:event
type="preRenderComponent"
listener="#{olapSample.saveQuery}"/>

…

<olap:cellSetTable/>








  

Let's Drill: Inner Workings

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");

q.getAxis(Axis.ROWS).addDimension(productDim);

q.getAxis(Axis.COLUMNS).addDimension(measuresDim);

q.validate();

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()

             .getConnection().createStatement();

CellSet drilledCs = stmt.executeOlapQuery(drilledMdx);

Unfortunately that class is not implemented in the released version of olap4j (1.0.0.445) 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");

q.getAxis(Axis.ROWS).addDimension(productDim);

q.getAxis(Axis.COLUMNS).addDimension(measuresDim);

CellSet cs = q.execute();

// Drill it

Member defaultMember =

   productDim.getDimension().getDefaultHierarchy().getDefaultMember();

q.getAxis(Axis.ROWS).drill(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:

DrillDownMember(

  {[Store].[All Stores]},    

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

  RECURSIVE)

Produces an axis with the following structure

|-All Stores

  |-Canada

  |-Mexico

  |-USA

    |-CA

    |-OR

    | |-Portland

    | |- Salem

    |-WA

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

Hierarchize(

  Union(

    CrossJoin(

      DrillDownMember(

        {[Store].[All Stores]},

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

        RECURSIVE

      ),

      [Store Type].[All Store Types]

    ),

    CrossJoin(

      {[Store].[USA]},

      DrillDownMember(

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

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

        RECURSIVE

      )

    ),

    CrossJoin(

      {[Store].[All Stores]},

      DrillDownMember(

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

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

        RECURSIVE

      )

    )

  )

)

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.