A Functional CellSet Pivot Table Using AngularJS and jQuery

I've take my previous Javascript experiment one step further; up to the point where it provides a functional CellSet pivot table and a hierarchy selector. I'm now learning AngularJS, and I like its MVC approach, so I'm using it for this experiment.

Sources, Live Demo and Maven Repository

You can find the sources for this experiment in my GitHub olap4j-js repository. Sources for olap4j-query are also in GitHub: olap4j-query; but I've set up a maven repository to host a olap4j-query 0.0.1 release version, used in this experiment. The required Maven snippet to use this repository is:

<repositories>
  ...
  <repository>
      <id>desarrollo-agil</id>
      <url>http://demo.desarrolloagil.es/nexus/content/repositories/releases/</url>
  </repository>
  </repositories>

Live Demo!!

I've just set installed a live demo running the current SNAPSHOT version of the project.

http://demo.desarrolloagil.es/olap-faces/test.jsf

Version 5: Refining Hierarchy Selection UI and Query Axis Editing

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




Sample page including the <olap:queryAxisEditor> component

This version replaces the previous listbox and buttons used to add hierarchies to the query axes with a new composite component that:

• Displays the current cube's hierarchies organized by the axis where they are used. Unused hierarchies are displayed in an "Unused"
  axis.
• Allows for hierarchy sorting (within the ROWS and COLUMN axes).
• Allows hierarchy removal from an axis.
• Allows moving used hierarchies to other query axes.
• Keeps a selected hierarchy (in the sample page this selected hierarchy is the one displayed by the query hierarchy editor)
• Includes a button to swap rows and columns axes.
• Allows selecting of the non-empty state for ROWS and COLUMNS axes.

All this functionality is added to the sample page with the following facelet snippet

<olap:queryAxisEditor
id="axisEditor"
query="#{queryBean}"
value="#{queryBean.selectedHierarchyName}">

<f:ajax
event="change"
execute=":form:axisEditor"
render=":form:axisEditor :form:hierarchyEditor"/>

<f:ajax
event="edit"
render="@all"/>

</olap:queryAxisEditor>

<olap:queryAxisEditor> Composite Component Internals

The queryAxisEditor composite component takes the following parameters:

• query: AbstractQueryBean derived managed bean keeping the query state
• value: backing bean property used to keep the current selected hierarchy name

This component can fire two client side events:

• change: when the user changes the currently selected hierarchy
  
• edit: when the user clicks on any action button (remove, add to rows, add to columns, add to filter, push up or push down) changing the current query.
  

This is the facelet used to implement this composite component

<composite:interface>

<composite:attribute
name="query"
/>

<composite:attribute
name="value"
/>

<composite:attribute
name="styleClass"
/>

<composite:attribute
name="headerClass"
/>

<composite:attribute
name="rowClasses"
/>

<composite:clientBehavior
name="change"
default="true"
event="click"
targets="columns rows filter unused"
/>

<composite:clientBehavior
name="edit"
event="edit"
targets="columns rows filter unused"
/>

<composite:clientBehavior
name="edit"
targets="swap"
/>

</composite:interface>

<composite:implementation>

<div
id="#{cc.attrs.clientId}">

<h:commandButton
id="swap"
styleClass="cgaofHierarchyListSwapBtn"
value="Swap Axes"
action="#{queryAxisEditorBean.swapAxes(cc.attrs.query)}"/>

<h:inputHidden
id="selected"
value="#{cc.attrs.value}"/>

<olap:queryAxisEditorBase
query="#{cc.attrs.query}"
value="#{cc.attrs.value}"
valueControl="#{cc.attrs.clientId}:selected"
axisName="COLUMNS"
id="columns"

styleClass="#{cc.attrs.styleClass}"
headerClass="#{cc.attrs.headerClass}"
rowClasses="#{cc.attrs.rowClasses}"/>

<olap:queryAxisEditorBase
query="#{cc.attrs.query}"
value="#{cc.attrs.value}"
valueControl="#{cc.attrs.clientId}:selected"
axisName="ROWS"
id="rows"

styleClass="#{cc.attrs.styleClass}"
headerClass="#{cc.attrs.headerClass}"
rowClasses="#{cc.attrs.rowClasses}"/>

<olap:queryAxisEditorBase
query="#{cc.attrs.query}"
value="#{cc.attrs.value}"
valueControl="#{cc.attrs.clientId}:selected"
axisName="FILTER"
id="filter"

rendered="#{cc.attrs.query.slicerHierarchies.size() > 0}"
styleClass="#{cc.attrs.styleClass}"

headerClass="#{cc.attrs.headerClass}"
rowClasses="#{cc.attrs.rowClasses}"/>

<olap:queryAxisEditorBase
query="#{cc.attrs.query}"
value="#{cc.attrs.value}"
valueControl="#{cc.attrs.clientId}:selected"
id="unused"

styleClass="#{cc.attrs.styleClass}"
headerClass="#{cc.attrs.headerClass}"
rowClasses="#{cc.attrs.rowClasses}"/>

</div>

</composite:implementation>

I'm using an inputHidden control to keep the selected hierarchy name: its value is changed by the inner queryAxisEditorBase controls, responsible for displaying the contents of a single axis. Those controls receive the query bean, the current selected hierarchy name, the client id of the hidden input field and the name of the axis they are displaying. Each axis is displayed using a table:

• The axis name and the non-empty toggle button are displayed within the table's header.
• Each hierarchy within the axis is displayed in a row with three columns:
  • A column for the remove button.
  • A column for the hierarchy's caption. The caption includes javascript to change the selected hierarchy on clicking.
  • A column for the other action buttons: rows, columns, filter, push up and push down.

Both composite components (queryAxisEditor and queryAxisEditorBase) relay on the QueryAxisEditorBean managed bean to execute actions and simplify facelets.

Required Query Package Improvements

The improvements implemented in the query package have been straightforward and all of them consisted in implementing methods already defined by the org.olap4j.query package:

• Query.swapAxes to allow axes swapping
• QueryHierarchy.isNonEmpty(), QueryHierarchy.setNonEmpty() to check/set the flag controlling the generation of NON EMPTY axis.
  
• QueryHierarchy.pushUp(), QueryHierarchy.pushDown()to allow moving a hierarchy within an axis.

Version 4: Filtering Capabilities

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

Screenshot of the sample application showing a filtered query and filter editor.

The logic used in previous versions to generate MDX expressions for query axis (ROWS, COLUMNS, CHAPTERS, etc.) cannot be used against the FILTER axis. One of the reasons for this is that it's based on the drill state of displayed members. Furthermore the semantics associated to those axes are completely different from the semantics of the slicer axis. Specifically the operator argument to include and exclude methods in the QueryHierarchy class has no sense in a slicer axis, as including a MEMBER will aggregate the measures of its DESCENDANTS, and is equivalent to aggregate all of its CHILDREN.
So I've decide to change the behavior of some QueryHierarchy's methods when the instance lives in a slicer axis (hierarchy.geAxis().getLocation() == Axis.FILTER):

• include(Member m, Operator op), exclude(Member m, Operator op)Both methods ignore the op argument and assume Operator.DESCENDANTS instead.
  
• isIncluded(Member m)Returns true if and only if m and all of its descendants are included.
  
• isExcluded(Member m)
  (new)
  Returns true if and only if m and all of its descendants are excluded. Equivalente to !isIncluded(m) for instances living in query axes.
  

I've also changed the <olap:queryHierarchyEditor> faces component, changing its appearance and behavior when editing a query hierarchy living in a slicer axis:

• The operator dropdown menu disappears
• The include/exclude buttons are replaced by tri-state checkbox buttons, checked when the member is included, unchecked when it's excluded, or mixed if neither isIncluded(m)
  nor isExcluded(m) return true.

I've created a new faces composite component to show the contents of the slicer axis (active filters for the current query) and allow them to remove hierarchies from that axe. It' a table with a row for each hierarchy showing the hierarchy's caption in the first column and a descriptive text of the filter in the second column. This text lists the included members for simple query expressions and shows an informative label to indicate a filter too complex to be detailed.

MDX Generation for WHERE clause

The package private method toOlap4j in the QueryAxis class delegates in the new toOlap4jFilter() method when generating the MDX expression for a slicer axis. This method generates a cross join of the expressions returned by the olap4jFilter() method for each hierarchy in the axis:
    private AxisNode toOlap4jFilter() {

        CrossJoinBuilder xJoin = new CrossJoinBuilder();

        for(QueryHierarchy h : hierarchies) {

            xJoin.join(h.toOlap4jFilter());

        }

        return
new AxisNode(null, false, axis, null, xJoin.getJoinNode());

    }



The MDX expression for a silcer QueryHierarchy is computed recursively based on the tree of included/excluded members:

• Simple case: current node has no overriding children.The resulting expression is the MemberNode for the current member if it's included, void expression if it's not included.
  
• Recursive case: current node has overriding children.
  • If the current node is excluded, return the union of the recursive expressions for every overriding children
    
  • If the current node is included, return the union of
    
    • computing the set of non-overriding children as EXCEPT(<currentNode>.CHILDREN, <set of overriding children>)
      
    • computing the union of the recursive expression for every overriding children
      

This is the current implementation; with an immersion parameter to carry the current descendants include/exclude sign.
    private
ParseTreeNode toOlap4jFilter(SelectionTree selectionNode,

            Sign defaultSign) {

        Sign selectionSign = selectionNode.getStatus().getEffectiveSign(

                Operator.DESCENDANTS, defaultSign);



        if (!selectionNode.hasOverridingChildren()) {

            // Current node has no overriding children, its filter expression is

            // the corresponding MemberNode if the member is included, void in

            // other case.

            if (selectionSign == Sign.INCLUDE)

                return Mdx.member(selectionNode.getMember());

            else

                return
null;

        } else {

            // Current node has overriding children



            UnionBuilder finalExpression = new UnionBuilder();

            if (selectionSign == Sign.INCLUDE) {

                // Current node is included, so overriding children are excluded

                // or have excluded descendants.



                UnionBuilder overridingChildren = new UnionBuilder();

                for (SelectionTree overriding : selectionNode

                        .getOverridingChildren()) {

                    overridingChildren.add(Mdx.member(overriding.getMember()));

                    finalExpression.add(toOlap4jFilter(overriding,

                            selectionSign));

                }



                // Return the set of non overriding children plus recursive

                // expression evaluations

                finalExpression.add(Mdx.except(

                        Mdx.children(selectionNode.getMember()),

                        overridingChildren));

            } else {

                // Current node is excluded, returns the union of recursive

                // evaluation for overriding children.

                for (SelectionTree overriding : selectionNode

                        .getOverridingChildren()) {

                    finalExpression.add(toOlap4jFilter(overriding,

                            selectionSign));

                }

            }

            return finalExpression.getUnionNode();

        }

    }

FilterAxisInfo Composite Faces Component

The component to display the filter axis for the current query hides a standard <h:dataTable> and relays on a managed bean to compute the filter expression to generate the textual description of the filter and adapt the call to remove a hierarchy from the filter axis. This component has the following attributes:

• value (instance of AbstractQueryBean)
  The query containing the axis to display (used to remove a hierarchy from the axis)
• style, styleClass
  CSS style and CSS style class to be applied to the component
• headerClassCSS style class to be applied to the column showing the hierarchy captions.
• expressionClassCSS style class to be applied to the column showing the filter descriptions.

It generates a client event when the user removes a hierarchy from the filter axis. This code snippet is from the sample test page:
        <h:panelGroup
id="filterAxisInfo"
>

            <olap:filterAxisInfo
value="#{queryBean}">

                <f:ajax
render=":form:filterAxisInfo :form:table :form:hierarchyEditor"/>

            </olap:filterAxisInfo>

        </h:panelGroup>


It uses default style classes and uses the client event to re-render itself, the result set table and the query hierarchy editor after filter hierarchy removal.



 

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.