Making Hierarchies Natural

I though it might be worth another post on hierarchies in Analysis Services 2008 CTP4 to show some more attribute relationship. If we look at the attribute relationships in the date dimension of adventure works below:

the relationships match the hierarchies and so the design is well optimised and there are no blue squigglies.  However for this to work, there is some odd stuff that has been done in the date dimension.  If we go back to the dimension structure tab and look at the properties

The key column for month name is a concatenation of Calendar Year and Month Number Year and this is important because it makes a month unique within a year i.e. January 2005 only belongs to 2005, where as if the key was just Month Name then January belongs to every year and every year would has every month in it so we couldn't put in a relationship between month and year.  This shows you an example of a work around to make your hierarchies natural.  However in my opinion you should design the data dimension from the ground up to support your analysis and reporting requirements, but that is another discussion for another post.

Technorati Tags: Buisness Intelligence, BI, Analysis services 2008