By: Siddharth Mehta
Overview
A tabular model consists of tables, fields and relationships. A table can contain attributes also known as fields, and one other important construct - Hierarchies. In general, hierarchies are organized by levels and each level has a parent-child relationship. To navigate data organized in this manner, we need to understand a set of specialized DAX functions that deal with this area of the tabular data model. In this chapter we will learn about some of these functions to navigate the data organized in a parent-child relationship.
Explanation
Geography is one of the tables which has a built-in hierarchy in the AdventureWorks Tabular model. If you browse this in the diagram view, you will be able to see the hierarchy as shown below. Try to navigate the Geography table in Excel and you will be able to understand how the data is structured.
There are certain tables like Employee which have data organized in a parent-child relationship logically by using fields like EmployeeKey and Parent EmployeeKey. Let's say that our intention is to navigate this parent-child data such that we are able to find child records for a given parent key. In this chapter we will look at how to achieve this.
Open the AdventureWorks tabular project in the data view, and add a calculated field as shown below. Here we are using the path function to create a path of keys or IDs from parent level to the level of the child record. Enter the DAX formula as shown below for this new calculated column and press the Enter key. Once processed, the field will have values as shown below. Deploy this new field to the SSAS Tabular instance.
Open SSMS, connect to the tabular SSAS instance where the AdventureWorks tabular model is deployed, and open a new MDX query window. All the perspectives that we saw in the last chapter, will be listed in the Cube pane. Select the Model perspective in the Cube pane, and type the below expression.
evaluate (
Filter ('Employee', PATHCONTAINS ([Path], "3"))
)
order by 'employee'[parentemployeekey], 'employee'[employeekey]
Execute the above expression and you should be able to find the results as shown below. In this expression, we are using the PathContains function to query the existence of a particular key in the Path field. As the path field contains the chain of all the keys from the parent key, all the child records that are a descendant of the parent record having the key value 3 will have 3 in their path. So using this function we can query the entire set of child records for any given parent key.
In this way we can navigate data organized in a parent-child relationship using parent-child functions. You can read more about these functions here.
Additional Information
- Using parent-child functions try creating a hierarchy in the Employee table and develop formulas for each level such that employees at a particular level are listed in each level of the hierarchy.
