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.