Hierarchy Navigation

One of the most powerful features of MDX is that it is location aware. This makes navigating the hierarchy a simple task.

Relative Cell Referencing

Consider a cell in the cube which contains the sales of icCube licenses in the year 2010. This cell is represented by the tuple:

( [Product].[Prod].[License], [Measures].[Amount], [Time].[Year].[2010] )

The MDX expression below gives the sales of licenses for the year 2009. In the expression below, we did not have to specify 2009 in the query. MDX automatically referenced the member 2009, which comes before 2010 in the year attribute:

{ [Product].[Prod].[License], [Measures].[Amount], [Time].[Year].PrevMember }

Similarly, we can navigate to the next member in the year level:

{ [Products].[Clothes], [Measures].[Amount], [Time].[Year].NextMember }

This offers great functionality because it allows you to write dynamic code which can navigate the hierarchy without you having to hard code the member names.

To further harness the power of relative referencing, you can use the feature called CurrentMember. This feature gives you the current location of a cell with reference to a hierarchy.

For example, instead of writing the first cell as ( [Product].[Prod].[License], [Measures].[Amount], [Time].[Year].[2010]), we can write it as ( [Product].[Prod].[License], [Measures].[Amount], [Time].[Year].CurrentMember). This acts as a pointer to the current cell and allows us to write dynamic MDX code. Dynamic MDC code can navigate the hierarchies without hard coding or knowing member names.

Children

The Children function dynamically identifies the members that are one level below the current level:

[Product].[Prod].CurrentMember.Children

The MDX expression above takes the current member of the category's attribute and then extracts all child members of that attribute. This can also be written in a short hand format:

[Product].[Prod].Children

We can also identify the first and the last child by MDX expressions [Product].[Prod].FirstMember and [Product].[Prod].LastMember. And we can create a set from the child members using the following range expression:

{ [Product].[Prod].FirstChild : [Product].[Prod].LastChild }

Parent

The Parent function is the opposite of the Children function. The Children function returns members who are under the current node. The Parent function will reference the node which is the parent of the current node.

Together these Children and Parent functions let you move up and down the hierarchy. And the PrevMember and NextMember functions let you move left and right in the hierarchy. This navigation capability, combined with the ability of MDX to determine its current position by CurrentMember, gives rise to a powerful query mechanism where the entire cube space can be navigated dynamically.

Functional Language Support

As you might have noticed in the previous example, an MDX expression may contain function calls. The syntax for a function call is either post-fixed as in the following example:

[Time].[Calendar].[Year].[2010].Children

and/or pre-fixed as in this example:

Head( [Time].[Calendar].[Year].[2010].Children, 3 )

Post-fixed function calls can be chained as follows:

[Time].[Calendar].[Year].[2010].PrevMember.Children

Next chapter: Common Calculations.