In AS 2000 dimension design, once the database has been loaded, is a 10 minute task. Declare your hierarchies, set up a few member properties, a short look at aggregations settings and off you go. There really was no such thing as a multiple hierarchies. Member properties was a design afterthought – as anyone that has ever designed a virtual dimension can testify. Though MDX feature powerful query abilities – there were still queries that were best served by the relations database.
Enter AS 2005. Dimensions design has become much more powerful. We now have the full flexibility of the relational model build into the UDM. However, this power comes with a heavy price tag.
When you design a dimension in AS 2005, every column in the relational source is a potential attribute. If you leave the properties of these attributes at their default value – you have set yourself up for a nasty performance surprise. Not only will performance suffer – your interface, should you use any version of Excel pre-2007 will be a mess.
For each attribute in your dimension you must manually adjust each property to fit your needs. Books Online is a bit vague on the subject – so here’s a quick summary:
The name of the property is misleading – it really should be called: EnableAggregations. When set to true, the default value, this attribute is considered part of the aggregated cube space and will influence the aggregations design. This also means that you cannot set this value to false if your attribute participates in a user hierarchy – AS 2005 will raise an error if you do.
However, you don’t want all attributes to be aggregatable. For example, the street address of a customer is generally not a good candidates for aggregations – its solely there for display purposes. In AS 2000 you would implement this as a member property.
Settings the value of AttributeHierarchyEnable to false tells Analysis Services that this attribute is used only for display purposes and that storage and processing time should not be wasted on building aggregations. If you really don’t need these aggregations – setting this value to false helps the aggregation wizard do its work
If you have set AttributeHierarchEnable to false this setting should be set to false. However, its default value is true. If set to true you attribute will, as the name says, be displayed as a hierarchy in client tools.
What does this mean? Even though the attribute already participates in user hierarchies it will also have its own, separate, hierarchy. In Excel 2003 and earlier this means that you will see one extra dimension for each attribute with this property set to true. This tends to create unnecessary clutter in you interface.
If you are designing solely for older client tools you should consider settings this property to false. If you are blessed with Enterprise Edition you can use the perspective feature. With perspectives this property can be true but you can still hide the hierarchy to older clients.
This innocent looking property can have a big influence no your processing time.
My understanding is not complete, but let me try to illustrate:
Assume you have a three attribubutes A0, A1, A2 that are related (through an attribute relationship) in a natural hierarchy:
A0 -> A1 -> A2
A0 is the leaf (key) level of the dimension.
During the aggregation phase, Analysis Services will first build aggregation on the leaf level A0. Using the leaf level aggregations the aggregations for A1 can be build (A1 is directly related to the leaf). Since A2 is related to A1 an optimization can be made: The aggregations for A2 can be now be build using only the aggregation from A1 – without scanning the aggregations for A0.
Assume OptimizedState is set to FullyOptimized (the default) for A2. This instructs Analysis Services to build aggregations specifically for this attribute. The means that A0 is scanned to build aggregations A2. This is of course an expensive operations – the leaf level can be quite big in large cubes. Setting OptimizedState to NotOptimzed will disable this behavior.
One of my customers has reduced his process time from 14 hours to 5 just by disabling this property for selected attributes. It may be based on my incomplete understanding – but I think the default property for this attribute is wrong and you generally would use NotOptimized instead of FullyOptimzed.
What I really don’t get about this setting is why it is even needed – especially when you consider the property:
This property is used to force a specific behavior in the aggregation wizard for this attribute. The Books Online documentation is pretty good:
One question remains though: If I can use AggregationUsage to fine tune my which properties are build with the aggregation wizard – why do I even need OptimizedState?