Analysis Services supports three kinds of hierarchies. Balanced, unbalanced and ragged. A balanced hierarchy is a hierarchy in which all branches descend to the same level and each member’s logical parent is the level immediately above the member. Examples of balanced hierarchies are regular and virtual dimensions. In unbalanced hierarchies, the branches of the hierarchy descend to different levels as in parent child dimensions. Ragged hierarchies are hierarchies in which one members’ logical parent is not in the level immediately above the member. As a result the branches of the hierarchy descend to different levels. Ragged hierarchies are supported in regular and in parent child dimensions. The ragged hierarchy becomes an unbalanced hierarchy in the parent child dimension. The Hide Member If property in the property pane is used to make a hierarchy ragged or the Skipped Levels column property of a level is used to support a ragged structure. These properties are not supported in virtual dimensions.
Analysis services supports dimensions with multiple hierarchies. These provide an alternate view of the cube data. Dimensions with multiple hierarchies can be defined as two or more dimensions having names that share the same prefix followed by a period but having different suffixes. Example:– Time Calendar and Time Fiscal. The only condition to this is that the suffix should not be equal to any current or future level name or member name in the dimension. This rule is imposed to ensure that queries using the dimension do not get ambiguous results. The process of creating hierarchies for a time dimension is illustrated in the section Creating a Time Dimension.
How to create a dimension with multiple hierarchies in Dimension Editor
In the Analysis Manager tree pane, expand the database in which you want to create the dimension with multiple hierarchies.
Right-click the Shared Dimensions folder, point to New Dimension, and then click Editor.
In the Choose a Dimension Table dialog box, expand a data source, click the dimension table for the dimension, and then click OK. Dimension Editor appears with the dimension table showing in the Schema tab.
To add more tables to the dimension, on the Insert menu, click Tables, and then use the Select Table dialog box.
To add a hierarchy to a dimension without a previously identified hierarchy
In the Analysis Manager tree pane, expand the database in which you want to create the dimension with multiple hierarchies, and then expand the Shared Dimensions folder.
In Dimension Editor, to create a new dimension with a new hierarchy based on the selected dimension, on the File menu, click Save As to display the Dimension: New Name dialog box. In the New name box, provide a different dimension name followed by a period and a hierarchy name. Click OK.
(Optional.) In Dimension Editor, edit the hierarchy of the dimension by adding or deleting levels or changing its schema.