Creating a Dimension Hierarchy
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. We will proceed by creating the Hierarchy for the Months dimension.
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 hand pane, so that it appears as below:
3. Select 1st Quarter, press the Ctrl key, and holding it down, select 2nd Quarter, 3rd Quarter, and 4th Quarter.
4. Drag and place the selection just below Total Year in the Hierarchy Definition dialog box, so that a sigma sign (for sum), appears beside Total Year. This indicates that Total Year is now an Aggregate member, the sum of the Members you placed below it (see following figure).
Tip
Another way to add Detail members as part of an Aggregate member is by using the Add Selected Members as Child, , button located at the toolbar. To do this, simply highlight the Detail members on the left hand pane (in the example above Detail members 1st Quarter, 2nd Quarter, 3rd Quarter and 4th Quarter) and then select a member that will be the Aggregate on the right hand pane (Total Year) then click on the Add Selected Members as Child button.
Notice that after clicking on the button, the number sign (#) beside Total Year changes to a sigma sign (Σ) which denotes that Total Year is now an Aggregate member.
5. Select January, hold down the Shift key, and then select March in the Members list box so that February is highlighted also.
Drag and place the selection just below 1st Quarter in the Hierarchy Definition dialog box. Now the sigma sign appears beside 1st Quarter.
6. Pressing the Ctrl key, select April, May and June. Release the Ctrl key. Now Select 2nd Quarter in the Hierarchy Definition dialog box.
7. Press the Add Selected Members as Child button, , on the toolbar. Notice that the sigma sign appears, next to 2nd Quarter.
8. Bring over July as a Detail member under 3rd Quarter.
9. Highlight August and September from the list on the left and highlight July on the right.
10. Click on the Add Selected As Sibling button to add August and September into the Hierarchy under the 3rd Quarter.
11. Complete the Hierarchy for the 4th Quarter.
Tip
We can also create the hierarchy by using the Add Selected As Sibling button, . In order to use this button, there must be an existing Aggregate member in the Hierarchy Definition dialog box. This is useful when user wants to include additional Detail members under a hierarchy.
Say for example, under the Aggregate member 4th Quarter it only has one Child member which is October defined in the Hierarchy Definition dialog box. To include November and December under the 4th Quarter hierarchy, simply highlight the Detail members to be included (November and December) in the Members list box, select October on the right hand pane and then click on the Add Selected As Sibling button.
Notice that the Detail members November and December now also appear as Child members of the Aggregate member 4th Quarter and that both exist on the same level within the hierarchy as that of October.
Note that other buttons are available for Hierarchy creation: Add all Members as Child, , and Add All As Sibling button,
.
12. Close the Dimension Hierarchy dialog box by clicking the OK button, ,
the rightmost button on the toolbar.
13. 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:
Dimension |
Member |
Type |
Accounts |
Net Sales Gross Profit |
Detail Aggregate |
Regions |
Canada North America |
Detail Aggregate |
‘Accounts’ Hierarchy dialog box
‘Regions’ Hierarchy dialog box