Dimension Hierarchies

When a new hierarchy in created in a dimension, all of the attributes are listed as related to the lowest level of the hierarchy.

I have heard that this restricts SSAS to only creating aggregations at the leaf and all levels for that hierarchy until you re-distibute the attribute relationships. This will then allow SSAS to create aggregations at levels higher than the leaf level.

What I mean by this is that when you have both Year, Month and Day in a hierarchy, you can have Year and Month under Day (not allowing aggregations at something other than the leaf level) or you can put Year under Month and now that will allow aggs on all levels of the hierarchy.

Can someone confirm this?

What are the implications of not defining the relationships - what are the benefits of doing it?

Thanks

Mark

http://mgarner.wordpress.com

Hi Mark,

The "Project REAL: Analysis Services Technical Drilldown" paper seems to confirm what you've surmised:

http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx

>>

Best Practice: Spend time with your dimension design to capture the attribute relationships within the dimensions.

Important: You must define attribute relationships if you want to design effective aggregates, if you want effective run-time calculations from the formula engine, or if you want valid results in MDX time functions.

In the hierarchy-based nature of SQL Server 2000 Analysis Services (which only supports natural hierarchies), aggregates are designed around hierarchies. In SQL Server 2005 Analysis Services, aggregates are combinations of attributes. User-defined hierarchies are not used. The Storage Design Wizard uses attribute relationships to determine when combinations of attribute rollups will be useful (and thus aggregates will be designed for those attributes). Without relationships, one attribute is as significant as any other attribute, so the Storage Design Wizard simply ignores the attribute and uses the ALL level for the dimension. Thus if you want to design effective aggregates, you must define attribute relationships. Without them the system still returns the proper number, but values must be calculated at run time and aggregates are not useful.

>>

There are more detailed explanations of AS 2005 aggregations elsewhere, such as in the "Designing Aggregations" sections of Teo Lachev's book on AS 2005, and in the PASS 2005 session: " Understanding Analysis Services 2005 Aggregations from Every Angle" (if you have access to the archive).

|||

Deepak,

Thanks for the info. That doc was great. I knew of some of the Project REAL documentation, but not that part.

Thanks for the help.

Mark

http://mgarner.wordpress.com