Establishing Hierarchies


Once the Members have been defined for a Dimension, the Hierarchy of these Members must also be defined. The Hierarchy determines the aggregation of Dimension members.

You will now create the Hierarchy by selecting Members and moving them to the Hierarchy Definition dialog box on the right side of the Dimension Hierarchy dialog box.

Define the Hierarchy for the Months Dimension as follows:

1. Select Total Year from the Members list box on the left.

2. Drag it so that it is placed just below Months in the Hierarchy Definition box on the right, so that it appears as below:

3. Select 1st Quarter, press and hold “Ctrl”, select 2nd Quarter3rd Quarter, and 4th Quarter.

4. Drag the selected Members just below Total Year in the Hierarchy Definition dialog box. A sigma sign (for sum) appears beside Total Year indicating that now this Member has become an Aggregate Member.

5. Select January through March (by holding the “Shift key”)

6. Drag the selected Members just below 1st Quarter in the Hierarchy Definition dialog box. Now the sigma sign appears beside 1st Quarter.

7. Select AprilMay and June in the left window. Release the “Ctrl key”. Now Select 2nd Quarter in the Hierarchy Definition dialog box on the right like so:

8. Press the Add Selected Members as Child button . The sigma sign appears, next to 2nd Quarter.

NOTE: These are the two methods, drag and via buttons, that you can use to create and edit Dimension Hierarchies.

9.  Drag over the Detail Member July under 3rd Quarter.
10. Highlight August and September from the list on the left and highlight July on the right.
11. Click on the Add Selected As Sibling button, to add them to the 3rd Quarter Hierarchy. Now August and September are shown under July, and the three Members comprise 3rd Quarter.

12. Complete the Hierarchy for the 4th Quarter so that when you are done, the Hierarchy looks as follows:

Close the Dimension Hierarchy dialog by clicking the  button. You are returned to the Dimension dialog box.


Hierarchies for “Accounts” and “Regions”

1. Add the following Members into the appropriate Dimensions. You will define their type, whether Detail or Aggregate, according to the Hierarchies you see in the following two figures:

     detail-aggregate-chart

2. When complete, the Dimension Hierarchies for Accounts and Regions should look as follows:

“Accounts” Hierarchy dialog box

“Regions” Hierarchy dialog box

hierarchical relationships — as you completed in the three DimensionsMonthsAccounts and Regions — defines a parent-child relationship between Members. Just as we have member siblings that exist on the same level in a Hierarchy under an Aggregate member, we also speak in terms of Child members, which are defined as all Members that make up parent aggregations. It is important to note that a Child member is not necessarily a Detail member. Child members may themselves be parents of other Members within a Hierarchy. For example, in the Regions dimension, North America is an Aggregate member; but it is also a child of Total Regions.


The Dimension Hierarchy Toolbar

The buttons on the toolbar in the Dimension Hierarchy dialog box, and the functions they perform, are as follows:

The functions listed above are described more fully in the PowerOLAP User Manual, though we will now discuss Aggregate weights in order to complete our QS Database Dimension set-up.