Turning a non-natural hierarchy into a natural hierarchy

SQL Server 2005 | SQL Server 2008

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.