Search This Blog

Saturday, November 15, 2008

Turning a non-natural hierarchy into a natural hierarchy

In the last post we had a very simple time dimension with a non-natural hierarchy. In this post I show how to turn this hierarchy into a natural hierarchy.

The basic idea is to modify the key column for the month attribute. As we still want to keep the original month attribute, I simply create a new one named 'H_Month' ('H' for hierarchy as we only want to use it in the hierarchy).


H_Month is just a simple copy of the month attribute. Now we edit the key columns of this attribute in the attribute's properties by clicking on the ellipsis:


In the DataItem Collection editor we choose 'Add' which gives us a new key column:


Now we can edit the source for the new key column by clicking on the ellipsis in the source field. We choose 'Column binding' as the binding type and take 'Year' as the key column as shown below:


After clicking 'OK' we rearrange the key columns so that the Year-column comes first. The result looks like this:


Before we can proceed we also have to set the name property of our attribute 'H_Month' because now we have more than one key column and therefor the name cannot be derived from the key. We choose the Month-attribute as the name:


Since we also want the months to be sorted correctly we set the OrderBy property of our new attribute to "Key":


Now we can process and check our new attribute H_Month. The dimension browser shows something like this:


Note that the month number is now repeated for every year. So we're having one member in our attribute for each year now. As this attribute is not what the user expects after selecting a month, we simply set this new attribute to invisible:


Now we can build our attribute relationship like this:


This relationship is correct because for each month in the attribute H_Month (defined by the key year/month) we only have one year as a parent. We can check this by using the BIDS Helper dimension health check:


Now we only have to modify our hierarchy by switching the attribute in the second level:



After doing so the yellow warning sign disappears and we are finished! Since the Name property for our level remains unchanged, the hierarchy looks the same as before:


You can check the dimension using the dimension browser in BIDS. Every year now has every month associated with it.

Of course modeling attribute relationship becomes more complex the more attributes you have to take into account. The dimension lettuce tool in BIDS Helper can be very useful in checking the dimension layout in SQL 2005. And with SQL 2008 the dimension editor shows the attribute relationships in a much more convenient way.


  1. Thanks a lot. This article just cleared up a few fundamentals in Dimension Attribute design for me.! :)

  2. Hi!
    Thanks for your blog. Very helpful.
    One question regarding this topic. Assuming you have two attributes like "Product Country" and "Product Category" to make it simple.
    Now you create your "natural user hierarchy" like you did in your example.
    "Product Category" has a collection of "Product Category" and "Product Country" because one country has n Categories and 1 Category has n Countries like




    If you create your user hierarchy everything is fine. Now I want to include an Attribute hierarchy for "Product Category" too, because Bikes in UK and Bikes in Germany means the same. If I enable the AttributeHierarchyVisible for "Product Category" now I have Bikes two times, because of the collection of the key. How do you solve this problem. Do you include the “Product category” once again in your Dimension and set the key to “Product category” without the "Product Country" and enable the AttributeHierarchyVisible for this Attribute only?
    Thanks for your thoughts!

  3. Hi Michaela, If the requirement is to have the attribute "stand-alone" and within a hierarchy, I would still make the hierarchy a natural one for performance reasons and create an additional attribute for use outside the hierarchy, just as you wrote. Since the attribute being used in the hierarchy will be invible it can have any name (the name of the hierarchy level can be assigned separately).
    Howevery, in many cases there will be no need for the additional attribute as you can hide hierarchy levels for example in Excel 2007+. But if the key leads to duplicated names you should create another attribute with a unique key. So, I absolutely agree with you.
    Cheers, Hilmar