Microsoft KB Archive/306408

= INF: Use of Multiple Hierarchies Versus the Creation of Separate Hierarchies =

Article ID: 306408

Article Last Modified on 2/21/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Analysis Services

-



This article was previously published under Q306408



SUMMARY
This article list three reasons why it is beneficial to create multiple hierarchies in a single dimension rather than individual dimensions for each hierarchy.



MORE INFORMATION
Here are the three reasons as to why it is beneficial to create multiple hierarchies.
 * Sharing a single key in the fact table. You can create a single key to represent both hierarchies in the fact table, thus reducing the fact table size and complexity.
 * Usability. Multiple hierarchies in a single dimension provide better usability for the end user. If the front end application supports multiple hierarchies, the user sees all the hierarchies logically grouped together.
 * Sharing of aggregates. Suppose you have two hierarchies. One hierarchy is Year,Month,Day and the second is Year,Quarter,Day. The two hierarchies share the aggregates that are built on the Year level, thus reducing the processing time necessary to create the same aggregate a second time.