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

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

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.

REFERENCES

For more information regarding Multiple Hierarchies, see the "Multiple Hierarchies" topic in SQL Server 2000 Books Online.