Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Simplify SQL Server Database Development     ====>    Webcast Registration
 

How To Handle a Parent-Child Relationship in Analysis Services Tabular


By:   |   Read Comments   |   Related Tips: > Analysis Services Development

Problem

I have a table with a parent-child relationship: employees and their managers. I need to show this data using Analysis Services Tabular, but it doesn’t support parent-child relationships. Is there a work around?

Solution

Since the introduction of the Power Pivot engine in Office 2010 - on which the in-memory columnstore engine of Analysis Services (SSAS) Tabular is based - till the most recent version of SSAS Tabular 2017, there has been no direct support for parent-child relationships. Unlike Analysis Services Multidimensional, which has out-of-the-box support. In the Tabular model however, it is not possible to create a relationship between a table and itself.

Luckily, there’s a work around: we can model the parent-child relationship as a natural hierarchy. A natural hierarchy has a fixed number of levels (while a parent-child relationship can have in theory an indeterminate number of levels). Let’s illustrate with an example. Here we have a simple parent-child relationship:

org chart as parent child

As you can see, some branches can go deeper than others. The branch from Selina to Bruce is only 1 level deeper, while Stephanie is 3 levels deeper than Bruce. Some parents have children, others do not. Some have only one child, others have multiple, and so on. When modeled like a natural hierarchy, the organizational chart becomes:

org chart as natural hierarchy

There is now a fixed number of levels. If a node doesn’t have children, it is repeated into the next level.

SSAS Tabular can support hierarchies like this one. Let’s take a look at an example.

Test Set-up

As a source of sample data, we’re going to use the AdventureWorks 2017 data warehouse, which can be downloaded from Github. In the data warehouse, we’re going to use the employee dimension – which has a parent-child relationship between an employee and his/her manager – and the reseller sales fact table. Create a new SSAS Tabular project in Visual Studio (you can download the latest versions of SSDT here, or you can install the Analysis Services Projects extension in Visual Studio 2017). In this tip we will use the Modern Get Data experience to load data into the model. You can learn more about it in the tip How to import data for SSAS Tabular vNext.

While the model is opened, click on Model in the title bar and choose Import From Data Source…

import from data source

In the Get Data dialog, choose Database and then SQL Server database.

connect to SQL Server

In the next step, enter the server and database name.

enter server and db name

After that, you need to choose the credential to connect to the database. Make sure these credentials have read permissions on the AdventureworksDW2017 database.

use service account

In the Navigator, select the DimEmployee and the FactResellerSales tables:

choose tables in navigator

When clicking OK, the query editor will open (which is similar to Power Query and the query editor of Power BI Desktop). The first step is to choose the columns of the DimEmployee dimension.

choose columns query editor

You can simply select the columns you want to keep. We will retain the following columns: EmployeeKey, ParentEmployeeKey, FirstName, LastName, Title, Marital Status, Gender, Department and Status.

column picker

Next we will clean up the Status column (which indicates the current row for an employee). We will replace null values with “Previous”. Right-click on a null value and choose Replace Values…

replace values

In the editor, type Previous in the “Replace With” field:

replace null with previous

Now we’re going to create an extra column that contains the concatenation of the last name with the first name. Select the LastName column, hold CTRL and then select the FirstName column. In the Transform menu at the top, choose Text Column and then Merge Columns.

merge columns menu

In the editor, choose comma as delimiter and name the new column FullName.

merge columns

As a result, the FullName column will replace the FirstName and LastName columns. Let’s take a look at the query for FactResellerSales. There we will only keep the EmployeeKey column and the SalesAmount.

choose columns for fact table

Now we’re ready to import the data into the model:

import all the queries

When the data has been loaded, the model has automatically detected the relationship between the two tables:

automatic relationship

If we want to use the SalesAmount column in Excel later on for testing, we need to create an explicit measure first (Power BI Desktop doesn’t have this problem and can work with implicit measures, aka numeric columns with no measures defined on them). We can do this by selecting the SalesAmount column and then by clicking on the summation symbol sigma:

create default explicit measure

This will create an explicit measure with the default aggregation of sum. As a best practice, we will also hide the EmployeeKey column from client tools, as well as the SalesAmount column since we want only the measure to be used. In the DimEmployee table, we can also hide the EmployeeKey and ParentEmployeeKey columns.

hide from client tools

You should end up with the following model:

final model after test set-up

Creating the Natural Hierarchy

With the use of DAX formulas and calculated columns, we will “naturalize” the parent-child relationship. First we need to construct the hierarchy path. This is the path between every leaf of the hierarchy and the root node. DAX has a function that does this for us: PATH. This function returns a delimited string with all of the identifiers of the parents of the current identifier right until the root node.

path function dax

In the example above, we can see the result of the path function for employee ID 1. From right to left, we have ID 1 (the current employee), then 18 (the immediate parent, which you can verify in the ParentEmployeeKey column), then 23 and finally 112, which is the ID of the CEO of AdventureWorks.

Now we know the path for each employee. But how do we know how many levels we need for our hierarchy? Using the PATHLENGTH function, we can count the number of levels traversed in the hierarchy path. In the resulting column, we just need to find the highest number, which is five in our case.

pathlength function DAX

Finally we can create our hierarchy. Let’s start with the first level, the CEO. We use the LOOKUPVALUE function to retrieve the name of the employee using the first identifier of the hierarchy path. The first argument of LOOKUPVALUE is the column we wish to retrieve (the full name of the employee), the second argument is the column in which we need to search for a specific value and the third argument contains that specific value. This value is the identifier (EmployeeKey) of the employee we wish to retrieve. We can retrieve the identifier from the hierarchy path with the function PATHITEM.

retrieve first level of hierarchy

The PATHITEM function takes three arguments: the hierarchy path, the position we want to retrieve (the first in our case) and an optional argument that specifies the type of the result. This type can be either text or integer.

Let’s move on to the second level of the hierarchy. Now we adapt our formula for the calculated column slightly. We need to check if the employee is in the current level or even lower. We can do this by checking of the depth of the hierarchy is bigger or equal to the current level. If it is, we use the same formula as in level 1. If it isn’t, we need to repeat the value of the previous level.

level 2 calc

The formulas for level 3, 4 and 5 are similar to the formula of the level 2 column. Once all the calculated columns have been created, we can create a hierarchy in the model view. Right-click the level 1 column and choose Create Hierarchy.

create hierarchy

This will add a hierarchy at the bottom of the table, with level 1 as it’s only level. Rename the hierarchy to EmployeeOrganization. To add the second level, drag the Level2Org column right below the first level, as indicated in the screenshot:

add level 2

Make sure you don’t drag the level too far. If the black line spans the whole table width, you’ve dragged too far and the level won’t be added.

no dice

When all the levels have been added, we have the following hierarchy:

finished hierarchy

We can now test our hierarchy in Excel. You can launch Excel with a connection to your workspace model by clicking the Excel icon in the task bar:

launch Excel

There we can create the following pivot table to verify the hierarchy works:

success!

As you might notice, some members of the hierarchy are repeated – such as Alberts Amy – because the original parent-child hierarchy has leaf members at every level. Not all employees are shown, because not all employees are sales persons. By adding a distinct count on the EmployeeKey, we can visualize the entire hierarchy:

full hierarchy

Conclusion

In this tip, we’ve shown how you can transform a parent-child hierarchy to a natural hierarchy using calculated columns in the Tabular model. The disadvantage of this method is that some members are repeated over different levels of the hierarchy, which can be confusing.

Next Steps
  • If you want to take a look at the model and it’s DAX formulas, you can download it here. The solution was created with Visual Studio 2017 (SSDT version 15.4).
  • A short tip on querying parent-child hierarchies using DAX: SQL Server DAX Parent Child Data.
  • You can find more Analysis Services tips in this overview.


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools