Hierarchies are a great asset to cube users. They allow them to drill down into increasing levels of detail with a click. Since hierarchies are so pervasive in cubes, it is pretty much guaranteed that folks who write MDX against cubes will need to perform hierarchy navigation at some point. There are many MDX functions that allow you to move up and down or side to side in hierarchies. Since this is an Intro, we will keep things pretty simple and just describe five basic ones, as shown in Table 1. For each function, I indicate what kind of object that function takes for input (argument) and also what kind of object it outputs (object returned). I do this so that you will know how to nest them properly in order to perform more advance logic. For example, if function A takes in a member and outputs a set and Function B takes in a set, you can pass an expression with Function A as the input to Function B.
Table 1
Function
|
Argument
|
Result
|
Description
|
.children
|
member |
set |
Returns a set of all members of the hierarchy on the level immediately below the member passed as the argument. |
.parent
|
member |
member |
Returns the member of the hierarchy on the level immediately above the member passed as the argument. |
.siblings
|
member |
set |
Returns a set of all members of the hierarchy on the same level as the member passed as the argument. Note that the member passed is also included in the resulting set. |
.firstchild
|
member |
member |
Returns the first member (according to configured hierarchy sort order in the Cube) of the hierarchy on the level immediately below the member passed as the argument. |
.lastchild
|
member |
member |
Returns the last member (according to configured hierarchy sort order in the Cube) of the hierarchy on the level immediately below the member passed as the argument. |
Figure 1
For this post, and for this section of my MDX Trek: First Contact presentation upon which this series is based, I have used an image I found on Flickr (Creative Commons licensed content). I call it the Star Trek Family, as seen in Figure 1. Since you can see that hierarchy navigation functions in MDX are based on that of a family tree, you can also likely imagine how delighted I was when I found this image. It is perfect for demonstrating the basic of hierarchy navigation. I want to note that, when I first found this picture, its URL was this. When I checked this URL again on 9/3/2012, it no longer exists. However, I want to thank AngieK47 for posting it way back when.
Since you have been following this series with such gusto, you will recall from Intro To MDX Decathlon – The Trouble With Tuples (3/10) that Analysis Services has ways to deal with omitted member references. The Partial Tuple Decision Tree in Figure 2 should be a good reminder.
Figure 2
.children
This function takes a member expression for its argument. It returns a set of the members of the hierarchy on the level immediately below the level of the member passed.
So, using the Star Trek Family image, let’s suppose we pass in Mom as the argument.
Our result is the set of Mr. Blue Shirt and Mr. Yellow Shirt. Note: I do not know for sure of those are the real names for these lads. But let’s go with it, k?
Let’s look at some examples from my Borg cube.
Attribute Hierarchy Example
In Query 1, we use my [Series].[Series Name] attribute hierarchy.
Query 1
SELECT
[Measures].[Episode Count] ON COLUMNS
, [Series].[Series Name].children ON ROWS
FROM [Borg]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Result 1
You will notice that Result 1 does not include the [All] member. Remember that the .children function takes a member expression as its argument. Instead, Query 1 passes it an attribute hierarchy. Behind the scenes, SSAS uses the Partial Tuple Decision Tree and finds that my [Series].[Series Name] attribute hierarchy has a default member, [All], and uses that to pass to the function instead. Since .children returns a set of all members of the hierarchy on the level immediately BELOW the member in the argument passed, Result 1 is a set of all member in my [Series].[Series Name] attribute hierarchy below the [All] member. The [All] member cannot exist on the level below itself.
Just to demonstrate this further, the only difference between Query 1 and Query 2 is that Query 2 actually specifies the [All] member as the argument for the .children function.
Query 2
SELECT
[Measures].[Episode Count] ON COLUMNS
, [Series].[Series Name].[All].children ON ROWS
FROM [Borg]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Result 2
Result 1 and Result 2 are identical.
User Hierarchy Examples
Using .children with a user hierarchy isn’t really very different. In Query 3, I use my [Date].[Date Hierarchy]. Note that Query 3 also uses the NON EMPTY keyword (in conjunction with a Slicer specifying the [Series].[Series Name].[Star Trek: The Next Generation] member) to limit the results just those date hierarchy members that have Episode Counts for Star Trek: The Next Generation. I explain the use of the NON EMPTY keyword in the next post in this series. I am just using that here in order to return a result set that takes less space to display.
Query 3
SELECT
[Measures].[Episode Count] ON COLUMNS
, NON EMPTY [Date].[Date Hierarchy].children ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Result 3
Again, since I passed a hierarchy to .children, and not a member like it requires, the Partial Tuple Decision Tree comes into play. In this case, my [Date].[Date Hierarchy] has no default member. So, SSAS looks for an [All] member, which it finds. Since the level immediately below the [All] member in my [Date].[Date Hierarchy] is the level for [Calendar Year], the .children function returns a set of years. I won’t demonstrate passing the [All] member again, but it works here, too.
In Query 4, we pass in a particular member of the [Calendar Year] level of my [Date].[Date Hierarchy].
Query 4
SELECT
[Measures].[Episode Count] ON COLUMNS
, NON EMPTY [Date].[Date Hierarchy].[Calendar Year].[1987].children ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Result 4
The level in my [Date].[Date Hierarchy] immediately below [Calendar Year] is [Calendar Quarter]. So, Result 4 returns the [Calendar Quarter] members under [Calendar Year].[1987] during which Star Trek: The Next Generation aired original episodes. It first aired in September of 1987, so the first two quarters of that year are filtered from the result set.
The .children function is great for building available values lists for Reporting Services multi-value parameters. Since SSRS builds the “Select ALL” option for you, you can keep the [All] member from confusing things by using .children to return just the distinct member values within the hierarchy.
.parent
This function takes a member expression for its argument and returns the member on the hierarchy level immediately above it.
So, using the Star Trek Family image again, we pass in Mr. Blue Shirt.
The result is Mom.
Let’s see some example of the .parent function from my Borg cube.
Attribute Hierarchy Example
In Query 5, we pass the [Star Trek: The Next Generation] member as the argument.
Query 5
SELECT
[Measures].[Episode Count] ON COLUMNS
, [Series].[Series Name].[Star Trek: The Next Generation].parent ON ROWS
FROM [Borg]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Result 5
My [Series].[Series Name] attribute hierarchy has only two levels. Level 0 is the [All] member and Level 1 underneath it has the set of each Star Trek series as members. So, when passing in a member series, we go up one level to the [All] member.
User Hierarchy Example
Query 6 passes a member of the [Calendar Quarter] level of my [Date].[Date Hierarchy] as the argument.
Query 6
SELECT
[Measures].[Episode Count] ON COLUMNS
, [Date].[Date Hierarchy].[Calendar Quarter].[1987-Q3].parent ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Result 6
Since the level immediately above [Calendar Quarter] in my [Date].[Date Hierarchy] is the [Calendar Year], our result is the year in which [1987-Q3] took place: 1987.
.siblings
This function takes a member as its argument and returns the set of members that exist at the same level in hierarchy, including that member.
So, once again using the Star Trek Family image, we can pass Mr. Blue Shirt to the .siblings function.
Our result is the set of Mr. Blue Shirt and Mr. Yellow Shirt. They are both siblings that appear at the same level in the hierarchy as Mr. Blue Shirt.
To the Borg cube!
Attribute Hierarchy Example
Query 7 passes the [Star Trek: The Next Generation] series member as the argument to the .siblings function.
Query 7
SELECT
[Measures].[Episode Count] ON COLUMNS
, [Series].[Series Name].[Star Trek: The Next Generation].siblings ON ROWS
FROM [Borg]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Result 7
The result is the full set of all of the members at Level 1 of the [Series].[Series Name] hierarchy. Since the [All] member is Level 0, above all of these, it is not returned.
User Hierarchy Example
Query 8 passes the [1987-Q3] member, at the [Calendar Quarter] level of the [Date].[Date Hierarchy], as the argument.
Query 8
SELECT
[Measures].[Episode Count] ON COLUMNS
, [Date].[Date Hierarchy].[Calendar Quarter].[1987-Q3].siblings ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Result 8
The result is the full set of [Calendar Quarter] members that have [Calendar Year].[1987] as a parent. Note that Query 8 did not include the NON EMPTY keyword, which would have suppressed the (null) results for [1987-Q1] and [1987-Q2].
.firstchild
This function takes in a member as an argument and returns the member on the hierarchy level immediately below it that comes first according to the hierarchy’s sort order configured in the Cube.
So, with our Star Trek Family, we can pass Mom as the argument.
Take a moment to look at the children in this photo. Mr. Blue Shirt appears to be a bit taller than Mr. Yellow shirt. He also seems to have an expression suggesting he may be tired of the Star Trek Vacations this family could take each year. With this in mind, I have decided that Mr. Blue Shirt is the older, and therefore First, child in the hierarchy.
Therefore, our result is Mr. Blue Shirt.
Resistance to my Borg cube is futile.
Attribute Hierarchy Example
Query 9 pass the [Series].[Series Name] hierarchy as the argument. The Partial Tuple Decision Tree comes into play and, behind the scenes, SSAS passes the default member of the hierarchy, [All], to the function.
Query 9
SELECT
[Measures].[Episode Count] ON COLUMNS
, [Series].[Series Name].firstchild ON ROWS
FROM [Borg]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Result 9
I constructed the members in my series dimension chronologically. So, the first member is [Star Trek: The Original Series].
User Hierarchy Example
Query 10 passes the [1987-Q4] member of the [Calendar Quarter] level of my [Date].[Date Hierarchy] user hierarchy.
Query 10
SELECT
[Measures].[Episode Count] ON COLUMNS
, [Date].[Date Hierarchy].[Calendar Quarter].[1987-Q4].firstchild ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Result 10
Since my [Date].[Date Hierarchy] is sorted chronologically by Date (yeah, I was clever), and the level immediately below [Calendar Quarter] is [Calendar Month], our result is the first month that occurs within the fourth quarter of 1987: [1987-10] which is October.
.lastchild
This function takes in a member as an argument and returns the member on the hierarchy level immediately below it that comes last according to the hierarchy’s sort order configured in the Cube.
So, in the fabulous Star Trek Family image, we can pass Mom as the argument.
In looking at the image, you can see that Mr. Yellow Shirt is a bit smaller than Mr. Blue Shirt. You will also notice the outright glee on his face (whether it is due to the Star Trek Vacation itself or just the fact that his brother is miserable is up for debate). Either way, I have decided that Mr. Yellow Shirt is the youngest, and therefore Last, child.
Our result is Mr. Yellow Shirt.
Once more to the Borg cube, my friends.
Attribute Hierarchy Example
Query 11 pass the [Series].[Series Name] hierarchy as the argument. Again, the [All] member is used behind the scenes.
Query 11
SELECT
[Measures].[Episode Count] ON COLUMNS
, [Series].[Series Name].lastchild ON ROWS
FROM [Borg]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Result 11
The result, since my series members are order according to the order in which they first aired, is Star Trek: Enterprise.
User Hierarchy Example
Query 12 passes the [1987-Q4] member, just like in Query 10 above.
Query 12
SELECT
[Measures].[Episode Count] ON COLUMNS
, [Date].[Date Hierarchy].[Calendar Quarter].[1987-Q4].lastchild ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Result 12
The result is the last month in the fourth quarter of 1987: [1987-12] which is December.
Hierarchy navigation is very important when dealing with MDX. Luckily, it does not also have to complicated. The basics are pretty straightforward. Stay tuned to the next installment in this series in which was discuss the NON EMPTY keyword and the mighty CROSSJOIN function.
Mark Vaillancourt 