Intro To MDX Decathlon–Hierarchy Navigation Basics (6/10)

4 September, 2012 (13:00) | Decathlon, MDX | By: Mark V

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

imageFor 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.

Smile

 

 

 

 

 

 

 

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

image

.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.

imageSo, using the Star Trek Family image, let’s suppose we pass in Mom as the argument.

 

 

 

 

 

 

 

 

imageOur 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]

Result 1

image

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]

 

Result 2

image

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]



Result 3

image

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]

 

Result 4

image

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.

imageSo, using the Star Trek Family image again, we pass in Mr. Blue Shirt.

 

 

 

 

 

 

 

 

 

imageThe 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]

 

Result 5

image

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]

 

Result 6

image

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.

image

So, once again using the Star Trek Family image, we can pass Mr. Blue Shirt to the .siblings function.

 

 

 

 

 

 

 

 

 

imageOur 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]

 

Result 7

image

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]

 

Result 8

image

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.

image

So, with our Star Trek Family, we can pass Mom as the argument.

 

 

 

 

 

 

 

 

imageTake 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]

 

Result 9

image

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]

 

Result 10

image

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.

image

So, in the fabulous Star Trek Family image, we can pass Mom as the argument.

 

 

 

 

 

 

 

 

 

image

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]

 

Result 11

image

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]

 

Result 12

image

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.

Comments

Pingback from Intro To MDX Decathlon–Introduction | Mark V SQL
Time September 4, 2012 at 2:32 pm

[…] 1. Intro To MDX Decathlon – Cube Space: The Final Frontier (1/10) 2. Intro To MDX Decathlon – The Basic MDX Query (2/10) 3. Intro To MDX Decathlon – The Trouble With Tuples (3/10) 4. Intro To MDX Decathlon – Sets: A Shirt of a Different Color (4/10) 5. Intro To MDX Decathlon – .members Function (5/10) 6. Intro To MDX Decathlon – Hierarchy Navigation Basics (6/10) […]

Pingback from Intro To MDX Decathlon–NON EMPTY and CROSSJOIN (7/10) | Mark V SQL
Time September 10, 2012 at 2:00 pm

[…] with the utmost, life-changing, interest, then you have most certainly read the previous post, Intro To MDX Decathlon – Hierarchy Navigation Basics (6/10). In that post, I used the NON EMPTY keyword and mentioned I would cover it in the next post. Tada! […]

Comment from Sally
Time January 10, 2014 at 10:35 pm

Hi Mark, you are my new hero with this series that explains MDX so clearly. I have had to build my own “data warehouse” and cube with the help of professor google and although it deploys and processes ok I am pretty sure there are some structural faults, particularly with the user hierarchy I made. You haven’t written something similar re data warehouse or cube making perchance?

Comment from Mark V
Time January 11, 2014 at 11:18 pm

Hi, Sally. Thanks so much for the kind words. I love that you are finding this series helpful. As far as more general SSAS stuff, I don’t yet have another series like this. I can recommend, though, a book. I used the 2008 version of the following book and found it helpful. http://www.amazon.com/Delivering-Business-Intelligence-Microsoft-Server/dp/0071759387 I have this 2012 version but have not yet dug into it. I assume it will be of similar quality. I hope that helps. I would also strongly recommend getting on Twitter, if you are not already. Twitter is a great place to turn for help and advice. The SQL Server community as a whole is just fantastic. I hope this helps.

Write a comment