THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alberto Ferrari

Parent/Child Hierarchies in Tabular with Denali

Some time ago, I wrote a post about how to handle Parent/Child hierarchies in PowerPivot 1.0. It was pretty intricate, because P/C are not handled natively in PowerPivot. Now, with the advent of Denali, Microsoft gave us all the functions needed to handle P/C in a convenient way. Kasper has already wrote a nice post about P/C here. His post is very informative and I suggest you reading it to look at all the P/C functions. In this post, I will need to repeat something he has already said but, hopefully, I will add some more insights into the DAX language solving a small issue that still exists with Parent/Child hierarchies, at least in CTP3.

Let us start with the data model. We have this data model (wow, take a look at how wonderful is the the new graphical view of tables in PowerPivot… great job guys!):

image

where the Hierarchy table contains this unbalanced P/C hierarchy, using the classical ParentID data model:

image

In order to show this P/C data structure in a PivotTable, we need to understand how DAX implements P/C handling. It is based on some very powerful functions, the most important of which are:

  • PATH: computes the full path of a node following the ParentID column, starting from the root
  • PATHITEM: extracts an item from a path, as returned by PATH
  • LOOKUPVALUE: searches for a value inside a table, following a relationship that is valid only inside the formula

Thus, by means of defining some simple columns:

  • HierarchyPath = PATH([NodeId], [ParentNodeID])
  • Level1= LOOKUPVALUE ([Node], [NodeId], PATHITEM ([HierarchyPath], 1))

You get this result, which is self-explaining:

image

In order to have the Parent/Child hierarchy work, we still need to create a user define hierarchy putting the various Levels, from 1 to 3, inside a single hierarchy:

image

Et voilà, the work is done. We can put this hierarchy in a PivotTable, expand all the levels and we get this result:

image

There are both good and bad news here.

  • The good news is that all the levels, once put into a hierarchy, behave exactly like a Multidimensional P/C.
  • The bad news is that if the hierarchy is a ragged one, as it is often the case with P/C, Denali CTP3 still makes a load of empty nodes visible.

SSAS handles this scenario with the usage of the HideMemberIf property of hierarchies, which hides useless nodes from the final result shown by the PivotTable. In CTP3, there seem not to be a way to set the HideMemberIf property for hierarchies, leading to this unwanted behavior. Hopefully, in the final release of Tabular the HideMemberIf will be available (if you are interested in having it, please vote this connect item, to make Microsoft guys aware of how useful this feature would be). If the item will be solved, then the next part of this post will be useless and life much easier. Thus, you might want to follow that item to check for its solution.

Nevertheless, if we want to mimic the HideMemberIf feature in CTP3, we still need to make some work with DAX. So, let us roll up our sleeves and start thinking at a viable solution.

The key to hide the unwanted rows is to note that:

  • We can define, for each node, its depth. Annabel and Bill have a depth of one, being root nodes. Catherine, being a child of Annabel, has a depth of two. Vincent, being a nephew of Bill, has a depth of three. The depth can be defined at the row level of the Hierarchy table.
  • We can define, for each cell, a Browse depth which is the depth up to which the P/C hierarchy has been unfolded, to make that cell visible.

With these definitions in mind, our PivotTable can be seen in this way:

image

If we hide, from this PivotTable, all the rows where NodeDepth is greater than BrowseDepth, we will reach the desired result. In PowerPivot 1.0 this was a big pain in the neck, as can be seen in my previous post. In Denali CTP3, the solution is much easier and elegant, because there is a very interesting function available in DAX: ISFILTERED.

ISFILTERED gets a column reference and returns a Boolean value indicating whether the column is filtered in the current filter context or not. Now, it is easy to note that a cell with BrowseDepth of 1 has a filter on the column Level1 and no filter for Level2 and Level3. A cell with BrowseDepth of 2, will have a filter on Level1 and a filter on Level2, with no filter for Level3.

Thus, we can define the BrowseDepth measure with this formula:

BrowseDepth :=
    
IF (ISFILTERED ('Hierarchy'[Level3]), 3,
    
IF (ISFILTERED ('Hierarchy'[Level2]), 2,
    
IF (ISFILTERED ('Hierarchy'[Level1]), 1
)))

This measure will compute, for each cell, the browse depth. We are still missing the node depth of each row. Another useful DAX function will help us: PATHLENGTH, which computes the length of a path column. We can add a calculated column NodeDepth=PATHLENGTH([HierarchyPath]) and get this:

image

Clearly, the NodeDepth cannot be aggregated using SUM, we need to use MIN. Thus, we define a new measure MinNodeDepth which simply aggregates the NodeDepth column using MIN. The final result, with all these measures in a PivotTable, can be seen here:

image

The final touch is to make the Amount measure BLANK whenever BrowseDepth is greater than MinNodeDepth, with this definition:

SumOfAmount := 
    IF (
        [BrowseDepth] > [MinNodeDepth], 
        BLANK(),
        SUM (Invoices[Amount])
    )

All the measures in a PivotTable:

image

And, after removing all the technical measures, we get the final result, which is nice as we wanted it to be:

image

Pretty easy, elegant and super fast, all of the requirements of DAX are met Sorriso

I am confident that the SSAS team will take the time to let us use Parent/Child hierarchies with the HideMemberIf option. In the meantime this is a viable solution and a good exercise to start getting acquainted with the new DAX functions.

Last, but not least, in Tabular P/C hierarchies are standard user hierarchies. Thus, you can have as many P/C for a single table as you want. I know of many customers who will be happy to have more than one P/C hierarchy on the same dimension, without having the need to define separate dimensions as it was the case with Multidimensional in SSAS.

Published Tuesday, July 19, 2011 1:15 AM by AlbertoFerrari
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

milang said:

I was just looking for this. Thanks :)

July 19, 2011 6:26 AM
 

milang said:

If you needed a measure (SumOfLeafAmount) that only shows childrens values, what would it be like?

In that case Annabel should show only the values for her leaf members (1200+800+600=2600) and Bill should show 1200 (400+500+300=1200).

July 19, 2011 9:53 AM
 

incognito007 said:

If reverse the order of the formular "BrowseDepth" as below:

if(isfiltered(level1),1

if(isfiltered(levle2),2

if(isfiltered(level3),3)))

It all return 1.

Why?

July 19, 2011 2:23 PM
 

AlbertoFerrari said:

@Incognito,

At BrowseDepth=2, both Level1 and Level2 are filtered. Thus, ISFILTERED(Level1) is always true, if the hierarchy is in the PivotTable. You need to start from the farthest node from the root with your tests to get meaningful results, because, at the leaf level, all levels are filtered.

@Milang,

Your "simple" question inspired my next blog post. Thus, the answer is coming but... as an innocent question as it looks, it is not very easy.

Stay tuned :)

July 19, 2011 3:03 PM
 

Alberto Ferrari said:

In my last post about Parent/Child hierarchies , there is a question, in the comments, that I found interesting.

July 19, 2011 5:05 PM
 

Stef said:

What about unary operators? How would you implement this very helpful functionality in tabular?

July 24, 2011 4:51 AM
 

AlbertoFerrari said:

@Stef,

I have already written about unary oeprators with DAX here: http://sqlblog.com/blogs/alberto_ferrari/archive/2011/03/01/powerpivot-parent-child-and-unary-operators.aspx

The very same technique can be used in Denali, the only issue being in computing the sign of leaf level nodes at each browse depth, but this can be easily accomplished with DAX.

Hope this helps.

July 24, 2011 5:18 PM
 

challen said:

Hi Alberto, could you please show the sample on this link? my personal email is youhoo_Fu@hotmail.com http://sqlblog.com/blogs/alberto_ferrari/archive/tags/PowerPivot/default.aspx

Many thanks.

September 26, 2011 10:04 PM
 

Stef said:

Hello Alberto

thanks for your answer concerning unary operators. Now I would like to create something like a calculated member in the account hierarchy. Can you explain me how one can achieve this. Thanks a lot.

Regards

Stefan

October 19, 2011 11:04 AM
 

AlbertoFerrari said:

@Stef,

Well, your request is a very generic one, I'd need to understand your specific issue better to give any kind of answer, you can accomplish a similar behavior with an IF statement but... it really depends from the specifics of your need.

October 19, 2011 2:05 PM
 

Stef said:

With UDM and MDX I could create KPIs for example the ratio of two real members of the [Account] dimension (total net revenue / total FTE) and add the calculated member to the account hierarchy. I would like to achieve something similar with tabular model

October 22, 2011 8:26 AM
 

AlbertoFerrari said:

@Stef,

It can be done using IF inside a measure. Not very elegant, but it can be done... if you need the complete solution, I need to look at your workbook to write one.

In general, in Tabular you will need to write several measurea and then a single one using IF or SWITCH to choose the right value to show at each level, so to mimic calculated members.

October 25, 2011 6:53 AM
 

Karthik said:

Hi Alberto,

Thanks for this post.

I have a scenario where I use P/C hierarchy to construct and browse by Organisation Structure in the Employee dimension itself.Structure looks like below:

¦EmployeeID¦EmpName¦OrganisationID¦ParentOrgID¦

Issue is that in such a case, every ParentOrgID need not occur in the OrgID column,(i.e. every org unit need not have an employee directly assigned to it). So, the PATH function does not work and instead gives the error:  "Calculation error in column 'dimEmployee'[]: The value '1209' in 'dimEmployee'[ParentOrgID] must also exist in 'dimEmployee'[organisationid].  Please add the missing data and try again."

The function description on msdn points out that "Values in parent_columnName must be present in ID_columnName." But as in this case, the business scenario can be different unlike the employee-manager structure. Can you think of any solution for this ? Thank you.( I can think of modelling as two different dimensions and hiding the blank ones as in your post, but I have a feeling that this kind of parent-child scenario should have been supported by the PATH function as well)

December 12, 2011 9:44 AM
 

AlbertoFerrari said:

@Karthik,

A quick answer is "you should model it as two dimensions: one P/C and another one as a classical dimension".

Another possible solution would be to use a calculated column to blank out links to non-existent parents.

It all depends from the data model you want the customer to browse...

December 12, 2011 4:31 PM
 

Francesco De Chirico said:

Hi Alberto,

what do you think about modify BrowseDepth formula in this way:

BrowseDepth := ISFILTERED('Hierarchy'[Level1]) + ISFILTERED('Hierarchy'[Level2]) + ISFILTERED('Hierarchy'[Level3])

avoiding the use of the IF() functions?

February 9, 2012 4:59 PM
 

AlbertoFerrari said:

@Francesco,

This looks a great definition for the formula, much easier to write and mantain. Like it very much! :)

Thanks for sharing this.

February 9, 2012 5:07 PM
 

skiwi said:

so essentially we have to hard code how many levels there are?  

if so this is a massive problem and will mean that many will have to use regular SSAS.  what if you work for an organisation of 100,000+ people where there could be 20 or more levels...

July 18, 2012 9:37 PM
 

txeriff said:

Hi Alberto,

Thank you for your post! it helped me lots.

I have a short question:

I have this hierarchy;

http://social.msdn.microsoft.com/Forums/getfile/192856

For some reason the first row, area 30 its doing 36-22=14.

22 its the value from SQL Query to the tables.

No clue why its doing subtraction

November 12, 2012 9:28 AM
 

Juan José Macias said:

Hola,

Yo para eliminar los espacios en blanco aplico las siguientes formulas para cada nivel:

Level 1:

LOOKUPVALUE(Hierarchy[Node];Hierarchy[ParentNodelID];PATHITEM(Hierarchy[HierarchyPath];1;1))

Level 2:

IFf(ISBLANK(PATHITEM(Hierarchy[HierarchyPath];2;1)); "(" & LOOKUPVALUE(Hierarchy[Node];Hierarchy[ParentNodelID];PATHITEMREVERSE(Hierarchy[HieararchyPath];1;1))&" datos)"; LOOKUPVALUE(Hierarchy[Node];Hieararchy[HierarchyPath];PATHITEM(Hieararchy[HieararchyPath];2;1)))

Level 3 y sucesivos:

La fórmula sería igual pero en vez de 2;1 indicar 3;1 , 4;1 y así hasta el último nivel.

Un saludo y gran blog.

March 9, 2013 11:43 AM
 

Eric said:

Thanks ! Very good analyse and elegant solution (it is just a shame that MS does not work naturaly like that...)

btw the PATHLENGTH work only with 'piped' full path.

I usualy use /, for a better human user comprehenssion.

If you are in the same situation, just use a Dax SUBSTITUTE (PATHLENGTH(SUBSTITURE([HierarchyPath];"/";"|")

April 10, 2013 2:14 PM
 

ziy said:

i just wanna know weather ferrari has a tall or hierarchical organizational structure  

thank you

May 18, 2013 10:58 PM
 

Keith Barney said:

I wanted to try out your instructions so I created a hierarchy in the employee table following the instructions from Kasper De Jong in the link you provided.  I was able to create the hierarchy (4 levels) but when I try to select in the field list of a new pivot table, I get his error: "The command was cancelled".  Any suggestions?

July 11, 2013 2:56 PM
 

Abhinav Bisht said:

I am trying to do the same in Excel 2013 powerpviot model, but somehow it doesnt work for me, the only differnce i have is that i am doing this on the via the tabular Model on the BIDS and writing formula on the model as the new table column any insights?

July 16, 2013 8:44 PM
 

Abhinav BIsht said:

What i mean is that the ISFILTERTED function always returns me false for all rows, and i cant get what does the isfiltered acutally does,

i have created excatly the same level1 level2 and levl 3 fileds as required here

July 16, 2013 8:50 PM
 

Dan said:

Just discovered some odd behaviour in Powerview hierarchy when using the ISFILTERED function to derive the BrowseDepth.  In an Excel Pivot Table using Powerpivot data, the hierarchy displays with the correct BrowseDepth, however in Powerview the BrowseDepth is equal to the depth of the hierarchy, not the current visible depth.  I am flagging this up to Microsoft as it appears to be a bug in Powerview.

August 9, 2013 7:34 AM
 

Dan said:

I have discovered that due to Powerview issuing DAX and Excel PivotTables issuing MDX against the Tabular Model, there are some semantic differences between the functions you can use in both.  ISFILTERED doesn't have a semantic equivalent in DAX compared to MDX, therefore when creating the same tables in Powerview and Excel, the BrowseDepth is different.  

A suggested alternative is using COUNTROWS(FILTERS...  as follows :

BrowseDepth:=

     IF (COUNTROWS(FILTERS('Employees'[Level3])) = 1,3,

     IF (COUNTROWS(FILTERS('Employees'[Level2])) = 1,2,

     IF (COUNTROWS(FILTERS('Employees'[Level1])) = 1,1

))))))

However this only seems to work where the hierarchy goes down to the full depth.  For an unbalanced hierarchy this formula gets it right for Bill's side of the hierarchy but not Annabel's.  

August 15, 2013 4:00 AM
 

Dan said:

There is no workaround for this issue in Powerview, although Microsoft are planning to release the equivalent of HideMemberIf in the Tabular model in a coming release.

August 27, 2013 2:19 PM
 

Fred Lorrain said:

In the post of Kasper there is a screenshot that show the Index of every node and leaf, eg 0 - Dutch Treats Inc or  04 - HR

How do you do that ?

April 7, 2014 5:21 AM

Leave a Comment

(required) 
(required) 
Submit

About AlbertoFerrari

Alberto Ferrari is a Business Intelligence consultant. He his interests lie in two main areas: BI development lifecycle methodologies and performance tuning of ETL and SQL code. His main activities are with SSIS and SSAS for the banking, manufacturing and statistical sectors. He is also a speaker in international conferences like European PASS Conference and PASS Summit.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement