Hierarchies in Analysis Services

Looking at that product dimension for my last post, I noticed that there were five hierarchies defined for the product dimension.  A hierarchy is a very important part of any OLAP engine and allows users to drill down from summary levels down to detail levels much as you might want to zoom in on a virtual earth map to see your house from looking at the county you live in.

A good example of a hierarchy is Product Model Categories.  All hierarchies start with an All level not shown here as it's a property of the hierarchy and you can decide what it's called e.g. All Products etc.  The next level down is Category then Sub-Category and then Model.  This type of hierarchy is called a natural hierarchy because a given Model only has one Sub-Category and a given Sub-Category only has one Category.  In other words there is a one to many relationship between product and subcategory and another between subcategory and category.  This makes for an efficient cube design which will be reflected in fast processing and query response times.

However how do you tell Analysis Services to do this.  In its 2005 vintage you would change the attribute relationships by dragging attributes underneath each other on the attributes window above.  There isn't a lot of help on this and it can be quite difficult to get the relationships correct.  However the screen grab above is form CTP4 of SQL Server 2008 and no there is a New Attribute Relationships tab to show all this more clearly:

As I mentioned in my previous post blue squigglies are not good and in this diagram we can see that the sample product dimension has a  redundant relationship between product and subcategory.  As you expect hovering over either of these gives the nature of the warning:

Surely you could get rid of that and then you're sorted. Well no because if we do and then go back to the Dimension Structure tab you see that there is now a red squiggly under product and this is because Product is ordered by Sub-Category and now there is no relationship to get at this.  So you have choices:

• Put up with a less than perfect design and leave the redundant relationship in
• Order product by something else and ignore your requirements spec
• Derive a new column to order products in the table or data source view based on Sub-Catgory.

So the blue squigglies are there to highlight potential performance problems, but what you do about them is up to you just like advice from any expert, your doctor, your accountant or your personal trainer! 

Technorati Tags: BI, Business Intelligence, Analysis services 2008, Dimensions