Parent-child relationship in SSAS cube

When is this used.

Published 6/7/2010 7:31:12 AM by DevinKnight

A common practice with dimension tables storing Accounting and Employee data is to implement a parent-child relationship. This can be used to store hierarchies or levels of your data between just a couple columns. For example, if you want to easily see who the direct manager is for each of your employees you may find the parent-child, also known as a self-referencing dimension, the best way to go. The table is simply foreign keyed back to itself, hence it being called self-referencing. Each employee has a primary key value but only those employees who have a direct manager will have a foreign key. So the President of the company who has no boss then you will have a null value for the foreign key. The example below will help you visualize the Parent-Child dimension.

EmployeeKey / EmployeeName / ParentEmployeeKey
1 / Terry Boss / Null
2 / Natalie Gomez / 1
3 / Sam Copper / 4
4 / Marc Rumsey / 1
5 / Julia Ventura / 7
6 / Rick Henderson / 4
7 / Kimberly Lucas / 1
8 / Eric Jackson / 2

Analysis Services is able to use these table parent-child dimensions and automate the hierarchy building that you would typically do yourself. When initially building the dimension Analysis Services will generally automatically detect that it is a parent-child dimension purely based on the self-referencing key. When it finds the self-referencing key it will change the Usage property of the foreign key column to Parent, indicating it is part of the parent-child dimension. If it does not auto-detect this for some reason you can still manually make the change in the dimension designer, shown below.

  1. In your dimension table, create the FK which refers to the PK of same table.

For eg. Consider the following dimension table

Table: Instructor-dim with the following

Instructor_id int pk

Instructor_name varchar

Manager_id int FK (refers to instructor_id)

  1. When you define the cube, the SSAS automatically identifies the parent-child relation and creates the Manager_id – Instructor_id hierarchy.

IMPORTANT NOTE

  1. Now since the hierarchy is created automatically, you must not create one manually as you create normal hierarchies in Dimension Structure. If you try to create one, you will get error message and cube will not be deployed successfully.

4. Next step is to make two changes in properties.

- Go to the properties for Manager_id field from Dimension structure.

- Look for the MembersWithData property and set it to “NonLeafDataHidden”

- Go to the properties for Instructor_id field from Dimension structure.

- Look for the NameColumn property, click the ... at the end. Select the Instructor_name from the screen and click OK.

5. When you try to look at the cube structure, you will find the structure similar as below.

6. Deploy the Cube as shown below and if everything is fine, then you will see the screen similar to shown below.

7. Final Report

8 Notice it states Level 02 and 03 etc.

To change the level names to better represent the data you can change the NamingTemplate property on the parent key attribute in the dimension designer. By changing Level 2 to something like Employee Level *, as shown below, each level of the parent hierarchy will show Employee Level 02, Employee Level 03, etc...

9 We can also choose to hide our manager name from the lowest level by changing the MembersWithData property on the parent key attribute. This will remove the manager’s name while still keeping their sales so we can still accurately see total sales overall. By changing the property to NonLeafDataHidden you will get the desired results when browsing. Viewing the results after making these changes will look something like the below image. (This is a different image)

Notice all the totals are still the same but the Manager has been remove from Level 5. Also, you’ll see each level of the hierarchy named Employee Level.

WARNING

Using parent-child dimensions is larger record sets (as small as a few hundred) "MAY"significantly hurt the performance of your cube when browsing by the hierarchy. This is of course worth testing in your own working environment to see if it truly does cause performance issues, but if you notice any slowdown in query time this is likely your issue.