Version 7: Added Support for Level Selections

Thursday, March 29, 2012

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]

Query Hierarchy editor showing level selections and the corresponding cellset

Also, this version drops support for parent/child hierarchies. I've been used many of those types of hierarchies so I'll re-include them in a later version. Are you using this type of hierarchies? Tell me, I want to know about the way other OLAP engines support parent/child hierarchies. And, finally, I've also postponed the implementation of level selections in filter hierarchies.

Query Package Changes

The changes to the query package API are simple: I've mainly added two new methods:

void QueryPackage.include(Level l);
void QueryPackage.exclude(Level l);

Those methods include/exclude all the members of the specified level in the query hierarchy. This selection is executed in call-order, along with member selections. So, given the following code snippet:

qh.include([Time].[1997], Operator.DESCENDANTS);
qh.include([Time].[1998], Operator.DESCENDANTS);

The members [Time].[1997].[Q1..Q4] won't show up in the resulting hierarchy as its level is excluded afterwards. However [Time].[1998].[Q1..Q4] do will show up, since they are included after the level exclusion.

Implementation Details

I've ended up with (another) big refactoring to the QueryHierarchy class and its support classes. The selection sequence is mainly maintained by the SelectionTree class and its non-static inner class SelectionNode. SelectionTree keeps the list of level selections, the root SelectionNode instance for the member selection tree, and the sequence number (more on this later). Making SelectionNode a non-static inner class gives its instances access to level selections potentially affecting them. I use the sequence number to maintain the correct ordering between level an member selections. It works as follows:

  • Any DESCENDANTS member selection is tagged with the current sequence number.
  • Level selections increase the sequence number and are tagged with that resulting sequence number.
  • Level selections remove any MEMBER selection for that level and any CHILDREN selection for the previous level.

Following this rules, the correct order for selections is generated by a pre-order visit of the tree and honoring the following rules:

  • Level selections must be ordered by sequence number (level selections with greater sequence numbers must go after level selections with smaller sequence numbers)
  • Any MEMBER selection found in the tree must go after its level selection (if any)
  • Any CHILDREN selection found in the tree must go after its previous level selection (if any)
  • Any DESCENDANTS selection found in the tree must go after the level selection tagged with the node's sequence number, and before any level selection tagged with a greater sequence number.

MDX Generation

The MDX generation for a QueryHierarchy is delegated to instances of the ExpanderVisitor interface. There are two classes implementing this interface: HierarchyDrillerVisitor used to generate the MDX for a collapsed hierarchy, and HierarchyExpanderVisitor used to generate the MDX for an expanded hierarchy.

For each visited SelectionNode those classes generate the MDX necessary to include/exclude the current node's member and any child subtree not affected by member selections.


  1. ¡Hola Un trabajo muy útil. ¿Hay un enlace al código fuente de la versión 7?

  2. El código completo no podré publicarlo hasta dentro de una semana. La lógica de consulta esta en

  3. Gracias. Será interesante de ver.

    1. Andrey, acabo de actualizar la entrada de la V7 con un enlace al código