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.

 

Continue creating the hierarchy.

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.

Now August and September are shown under July, and the three Members comprise 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, .

The completed Hierarchy looks as follows:

12. Close the Dimension Hierarchy dialog box by clicking the OK button, ,
the rightmost button on the toolbar.

You are returned to the Dimensions dialog box.

Next, you will create the Hierarchy for the Accounts and Regions dimensions.

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 Cost of Sales Gross Profit	Detail Detail Aggregate
Regions	Canada United States Venezuela Colombia North America South America Total Regions	Detail Detail Detail Detail Aggregate Aggregate Aggregate

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

‘Accounts’ Hierarchy dialog box

‘Regions’ Hierarchy dialog box

A hierarchical relationships—as you completed in the three Dimensions, Months, Accounts 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.