Creating Dimensions with Multiple Hierarchies
Multiple hierarchies can be created for a dimension to provide alternative views of dimension members. For example, a time dimension that has two hierarchies can consist of a regular calendar view and a fiscal calendar view.
In Microsoft® SQL Server™ 2000 Analysis Services, a dimension with multiple hierarchies is actually two or more separate dimensions that can share dimension tables and may share the same aggregations. Using multiple hierarchies helps optimize building aggregations in Analysis Services.
Unlike dimensions with a single hierarchy, the naming schema requires a period to indicate the presence of more than one hierarchy. (Dimensions with a single hierarchy can use a period and a hierarchy name part, but this is optional.) In the previous example, when a time dimension is defined to provide a regular calendar view and a fiscal calendar view, the resulting names can be Time.Calendar and Time.Fiscal.
Important When creating dimensions with multiple hierarchies, the hierarchy part of the name should not equal any current or future level name or member name in the dimension because queries using the dimension may be ambiguous.
To help minimize disruption to cubes, it is helpful to identify dimensions with multiple hierarchies before they are deployed. One way to do this is to name the dimension with a period and a hierarchy name part at the time of creation. Additional hierarchies can then be created by using the same dimension name part followed by the period and the hierarchy name part.
Dimensions that have multiple hierarchies can be created in the Dimension Wizard or Dimension Editor. For each hierarchy that is being created, the process is similar to creating a new dimension.
To create a dimension with multiple hierarchies using the Dimension Wizard