Parent Child hierarchies like organization hierarchies and account structures are very common in reporting environments. PowerPivot V2 ships with a set of new DAX functions which allows us to leverage this in PowerPivot.
Let's have a look at our dimEmployee table. This table has a parent-child relations (ParentEmployeeKey --> EmployeeKey). As you can see in the screenshot below; you cannot create a relationship between the two columns in PowerPivot. Just like with roll-playing dimensions PowerPivot uses a different approach than Analysis Services.
In PowerPivot Parent Child relatinships are managed through DAX. Let's focus on employee John Campbell and play around with some DAX functions.
First function is PATH. PATH returns a delimited text string with the EmployeeKey of all the parents of the current EmployeeKey, starting with the earliest and continuing until the current.
=PATH([EmployeeKey],[ParentEmployeeKey])
John Campbell's manager is Peter Krebs, and Peter Kreb's manager is Ken Sánchez. If we look at the corresponding Employekeys this results in 112|23|20.
Second function is PATHLENGTH(). PATHLENGTH(PATH([EmployeeKey],[ParentEmployeeKey])) will return the length of the path. In John Campbell's case this is 3.
With PATHCONTAINS() we can check if a particular key exists in the path.
=PATHCONTAINS(PATH([EmployeeKey],[ParentEmployeeKey]),23) will return TRUE if EmployeeKey 23 is found anyware in the levels above the current Employee. In the case of John Campbell this will return TRUE.
PATHITEM AND PATHITEMREVERSE allows us to pick out a particular level in the hierarchy.
PATHITEM(PATH([EmployeeKey],[ParentEmployeeKey]),2) will return the 2nd level in the hierarchy starting from the top. PATHITEMREVERSE(PATH([EmployeeKey],[ParentEmployeeKey]),2) will return the 2nd level in the hierarchy starting from the bottom.
In the case of John Campbell this is two times 23.
The last function we need to peek at is LOOKUPVALUE which allows us to lookup a particular column value for a given search criteria. The following DAX statement will return the manager's name.
=LOOKUPVALUE([EmployeeName],[EmployeeKey],PATHITEMREVERSE(PATH([EmployeeKey],[ParentEmployeeKey]),2))
For John Campbell this is Peter Krebs.
If we now combine LOOKUPVALUE, PATH, and PATHITEM we can turn this parent-child dimensions into a natural hierarchy by creating a column for each level:
=LOOKUPVALUE([EmployeeName],[EmployeeKey],PATHITEM(PATH([EmployeeKey],[ParentEmployeeKey]),1))
=LOOKUPVALUE([EmployeeName],[EmployeeKey],PATHITEM(PATH([EmployeeKey],[ParentEmployeeKey]),2))
This approach is very similar to the concept used by BIDSHelper's
Parent-Child Dimension Naturalizer for Analysis Services.
Next step is to create a hierarchy in the diagram view and create a pivottable.
This nugget was written on CTP3. One thing that is missing here is an easy way to hide the "empty levels". E.g. Peter Krebs is on level 2, so level 3, 4, 5, .. are empty for Peter Krebs.
In Analysis Services we solve this by setting the HideMemberIf property. Alberto Ferrari
blogged a workaround for this in his post on Parent Child relationships. Alberto also opened an item on
Microsoft Connect. Let's vote for this feature request and hope it makes it in the RTM version.
The longest nugget so far on a very cool BI concept coming to PowerPivot.
