Thomas.I Microsoft BI & Analytics

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

Different types of hierarchies in SSAS2005

Posted by thomasivarssonmalmo on October 21, 2007


I recently answered a question about this in the SSAS2005 newsgroup but thought that I can add a few more thoughts about this topic.

Attribute and user hierarchies

I the dimension editor in BIDS you have the attribute hierarchy in the left pane and user hierarchies in the middle pane. If you do not ask the dimension wizard to detect natural hierarchies you will always attribute hierarchies with the attributes you have choosen. An Attribute hierarchy only have an all-member at the top and its values as member. Gender in the customer dimension have an all-gender member at the top and the values male and female.

Attribute hierarchies and member properties

One thing that strikes me as a problem is to understand the difference between an attribute hierarchy and a member property. I have always thought of member properties as attributes that do not have a natural hierarchy like the color of a product. What is the next level above green or brown? In SSAS2005 you can add attributes as member properties of other attributes, both for simple things like sorting that attribute hierarchy but also for adding related information for that attribute hierarchy. In Excel 2007 and ProClarity Professional you will see them as information boxes on a member or attribute that you put the cursor on. Note that this relation is also a part of a structural description of your user hierarchy, attribute relations that is important for aggregations and query results.

Unnatural and natural hierarchies

In the user hierarchies(middle) pane you can build both natural hierarchies and unnatural hierarchies. A natural hierarchy always have a one-to-many or one-to-one relation from the top level to the leaf level. The most common is one-to-many like TimeCalendar(Year-Quarter-Month-Date).  It is possible to build an unnatural hierarchy like day of week and holiday but you should be careful with this type.

Parent-Child and natural hierarchies

Some confusion always appears regarding parent-child hierarchies in SSAS2005.  A natural hierarchy, like TimeCalendar described earlier have named levels like Year-Quarter-Month-Date and all levels will have members like &[2007], &[Q4 2007], &[Oct 2007] and &[2007-10-21] . The relation from the top to leaf level is one-to-many.

A parent-child dimension always have a recursive relation from the leaf level to the parent level. In the dimension table behind this dimension you will only see two columns that describe the relation of levels, the child key and the parent key. This is done by using a primary key and foreign key relation within a single table.  As a general decription you can say that their is a single member and level at the top but different number of levels under the top level. An organization with managers and employees is the most frequent use for this type. If you would try to model this relation as a natural hierarchy with named levels you will have levels with no members because the deep can vary in each subtree.

The account dimension in the Adventure Works cube is built as a parent-child dimension, wrongly beacuse accounts are natural hierarchies.

Parent-child dimensions do not have attribute relations.

Ragged hierarchies

Finally, ragged hierarchies are sometimes, uncorrectly, described as parent-child but natural hierarchies can be ragged as well. In United States the customer dimension have an natural hierarchy with Country-State-City-Zip code and Customer. In Sweden and other countries the state level do not exist in the same way as in USA so that level could be empty outside of USA. Natural hierarchies with different number of levels between the top and the leaf level are called ragged hierarchies.

The dimension editor have a property that you can see if you click on a level in a user hierarchy and choose properties, HideMemberIf, where you could hide empty levels. Be careful with this property because it has performance implications and will not be supported in Katmai according to Books On Line.


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: