New Cube Design Features - Part 2 - More on Hierarchies

Part 1 further down the page was cut short because I could see that it was going to be one of those 4 page word document blog postings that I would never go back and read let alone someone with any interest in this thing taking any notice of. Anyway, the dimension / hierarchies change thing is pretty huge in 2005 so continuing my piece on Chris Webb’s piece a bit more about attribute hierarchies.

The last thing I mentioned was around relationships between attributes in hierarchies being cemented with member properties. 2005 also understands transitive relationships, again plagiarising Chris’ presentation and example; If there is a one to many relationship between City and Store and City is a member property of Store, Analysis Services automatically infers the relationship of State and Store, Clever huh?

Member properties can express two types of relationship, rigid and flexible. Rigid relationships will retain aggregations when the dimension is updated and is used for relationships between members that do not change over time. Flexible relationships can change over time such as in a slowly changing dimension. The point to remember and what makes this relationship type a little more critical is that if you mark a dimension as rigid and there are changes then Analysis Services will error during processing similar to when a member changes parent in a non changing Analysis Services 2000 dimension.

The structure of hierarchies has changed quite a bit also. Traditionally I’ve worked on projects that have made heavy use of alternate hierarchies. There’s never been anything particular alternate about them apart from the naming, nothing that distinguishes them from any other dimension with some applications allowing you to slice by more than a single alternate hierarchy at one time. Analysis Services is capable of opening up all attributes as hierarchies that would be usable from a client. This may sound great but obviously becomes impractical when attributes such as telephone number or house number are included.

Attributes have a setting called the AttributeHierarchyEnabled property. Setting this to true means that the attribute will be available as a hierarchy on the dimension with an ‘All’ level and its members under that. Setting it to False makes the attribute behave in same manner as a traditional member property. There are many other important settings for attributes, for example if you want to add an attribute as a level to multilevel hierarchies, but you do not want users to see the attribute hierarchy in client applications, set AttributeHierarchyEnabled to True and set AttributeHierarchyVisible to False. These settings prevent users from browsing an attribute hierarchy without disabling it. The AttributeHierarchyVisible setting is ignored if AttributeHierarchyEnabled is set to False.

An attribute hierarchy will have an ‘All’ level if the IsAggregatable property is set to True and can also be added to any level of a hierarchy. If it’s set to False then it can only be used at the root of a hierarchy. Settings on the AttributeHierarchyOptimizedState property determine whether additional indexes are built on the attribute to improve query performance.

Many of the concepts within Analysis Services 2005 user and attribute hierarchies are going to be relatively easy to grasp for anyone involved with implementations on previous versions of the product. Personally I think that it should be easier to understand how to build dimensions that perform and emulate the behaviour of the business for someone starting out in Microsoft OLAP technologies. It’s been too easy in the past to build a cube and just ignore the advanced settings then let it run like a dog as it’s still faster than trying to write SQL that traverses multiple levels in a hierarchy. You can still build poor structures but 2005 seems to be a lot clearer in reasons why and the consequences if you don’t understand the additional properties of a dimension.