Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

Proper use of parent child dimensions

Posted by thomasivarssonmalmo on August 29, 2007

When should you use them? The appropriate use of parent child dimensions in SSAS2005 is very simple.

You must use them if you have a fact table with diffent granularity of facts. This means when you have fact data that are from different levels in a dimension and not only the leaf level.

I have a customer with this problem in their product dimension. Costs will appear at a higher level in the product dimension, than individual products on the leaf level. Revenue, on the other hand, is collected on the leaf level. Instead of using different allocation rules for distributing cost to the leaf level, because it is very hard in this business, we agreed to make the product dimension a parent child.

This implementation is not very large by US-standard so it has worked very well and is still in AS2000.

Then next appropriate use is for recursive hierarchies.

A product dimension is not a recursive hierarchy(If you look at the sales of final products) and is mostly modelled as a natural hierarchy(like in the Adventure Works cube), but in order to support the business problem, of this customer, we had to make the product dimension a recursive hierarchy.

A recursive relation is technically a primary key and foreign key within the same table. For a parent child dimension you will see an employee key and a manager key for each record in an employee or organization table. You can see this in the Employee table in the AdventureWorks DW database, that is part of the samples provided with SQL Server 2005.  The levels between a leaf level employee can be different depending on the different number of management levels above each employee.

If you analyze products from the manufacturing perspective you will have an even more complex recursive relation, called "Bill of Materials" (BOM). The difference between a parent child dimension and a BOM dimension is that the later have several parents at the top and the first, only one.

The last appropriate use of parent child dimensions is if you use dimension writeback.

This feature was first released with AS2000 and I have used it for the account dimension in the same project I have described above. The source system did not have the all the members and levels that this customer needed and we used dimension writeback to supply these missing members and levels.

Finally, what will you have to pay for using parent child dimensions? Performance. Have a look at the "Analysis Services 2005 Performance Guide" and what it says about this topic. It is not the largest chapter in that document but much of SSAS2005 performance are based on natural hierarchies and attribute relations, something that is not part of parent child dimensions.

How is the account dimension modeled in the Adventure Works cube? Parent-Child! Why? I do not know but probably because of the first business problem I have described, different granularity of fact data.

In the real world it is possible and recommended to model account as a natural hierarchy because that is how it has been done since the 13th century. Accounts are five digit integers. The first digit tells if it is a balance account(starts with 1 and 2) ot income and expenses( 3 to 9). The next level is the two first digits and the following levels is three alll the way down to leaf level, with five digits, the individual account. Both balance and income accounts have five levels and should qualify as a natural hierarchy.

My advice is to always use natural hierarchies and avoid parent child dimensions. Only use them when you have the business requirements listed above.


One Response to “Proper use of parent child dimensions”

  1. […] […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: