Page 134 - DCAP606_BUSINESS_INTELLIGENCE

Unit 9: Advanced Dimensional Design

In Microsoft® SQL Server™ 2000 Analysis Services, a dimension with multiple hierarchies is Notes
actually two or more distinct dimensions that can share dimension tables and may share the
same aggregations.
Different dimensions with a single hierarchy is called schema. It requires a time span to indicate
the presence of more than one hierarchy.

Notes When creating dimensions with multiple hierarchies, the hierarchy part of the
name should not be same to 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 established. One way to do this is to name the dimension with a time
span and a hierarchy title part at the time of creation. Additional hierarchies can then be created
by utilising the identical dimension name part followed by the period and the hierarchy title
part.
Dimensions that have multiple hierarchies can be created in the Dimension Wizard or Dimension
Editor. For each hierarchy that is being created, the method is alike to creating a new dimension.
To create a dimension with a single defined hierarchy using the Dimension Wizard:
1. In the Analysis Manager Tree pane, expand the database in which you want to create a
dimension with multiple hierarchies.
2. Right-click the Shared Dimensions folder, point to New Dimension, and then click Wizard.
3. In the second step of the wizard select either Star Schema: A single dimension
table or Snowflake Schema: Multiple, related dimension tables.
4. Follow the remaining wizard steps to define levels and various options for the dimension.
5. In the Finish step of the wizard, enter a name in the Dimension name box.
6. Select the Create a hierarchy of a dimension box.
7. Enter a name in the Hierarchy name box.

8. Click Finish to complete the wizard. After you complete the wizard, Dimension Editor
appears so that you can further refine the dimension.
9. (Optional.) To create another hierarchy of the dimension, from the File menu in Dimension
Editor, point to New Dimension, and then click Wizard. Follow the steps in the next
procedure, “To create a dimension with additional defined hierarchies using the Dimension
Wizard,” beginning with Step 3.
To create a dimension with additional defined hierarchies using the Dimension Wizard:

1. In the Analysis Manager Tree pane, expand the database in which you want to define
additional hierarchies for a dimension with at least one named hierarchy.
2. Right-click the Shared Dimensions folder, point to New Dimension, and then click Wizard.

3. In the second step of the Dimension Wizard select either Star Schema: A single dimension
table or Snowflake Schema: Multiple, related dimension tables.
4. Follow the remaining wizard steps to define levels and various options for the dimension.

5. Select the Create a hierarchy of a dimension box.

LOVELY PROFESSIONAL UNIVERSITY 129