INF: Use of Multiple Hierarchies Versus the Creation of Separate Hierarchies (306408)


The information in this article 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.

REFERENCES

For more information regarding Multiple Hierarchies, see the "Multiple Hierarchies" topic in SQL Server 2000 Books Online.
Modification Type:MajorLast Reviewed:6/29/2004
Keywords:kbinfo KB306408
©2004 Microsoft Corporation. All rights reserved.