Defining a Dimension Hierarchy

In this example, which leverages information in an underlying relational table, parent-child relationships exist in the dim_Accounts table, ensuring that an accurate calculation occurs when Sales and Cost of Sales "hierarchize" as Margin.

1. In the Table window of the corresponding relational component, go to Design Tab and select a column that serves as root member.

2. Right-click on the column, select Define As Hierarchy Level and click At Root.

3. Select the direct child of root, right-click on it, then select Define As Hierarchy Level; then click Sub-level of <rootname>.

4. Continue defining the rest of the hierarchy by following the same steps.
Note: By default, the lowest or leaf-level Members are those derived from the columns designated as Dimension Members. There is no need to define those Dimension Members as a sub-level of a particular parent within the Hierarchy. It isn’t necessary to explicitly define these Dimension Members as a child level of a parent in the hierarchy.

A hierarchy has now been established for the Dimension. If, in another scenario, you want to add additional Members as children or descendants within the hierarchy, you would follow the same steps. The hierarchy you’ve created for the Dimension Members will appear in the Property Grid.

In the first example, the dim_Regions table serves as the sample relational data source. When examining the dim_Months table in the Design tab, you’ll find three columns: Region, Continent, and World. By checking the Data tab, you can see the actual records stored in each of these columns.

For the Region dimension, assume that the Dimension Members are derived from the Region column. We now aim to establish a three-level Dimension Hierarchy:

First Level (Root Member): World, consisting solely of the member World.
Second Level: direct child Continent, consisting of the members North America and South America.
Third Level (Lowest Level / Leaf Members): Region, consisting of the members Mexico, Colombia, Venezuela, Brazil, Argentina, USA, and Canada.

To proceed with defining the hierarchy:

1. Navigate to the Design tab and select the World column (last column).

2. Right-click and select the Define As Hierarchy Level, and then select the At Root option.

3. Define the second level hierarchy, which is Continent. Select the Continent column, right-click and select the Define As Hierarchy Level and then select the Sub-Level of World option.

Because this example has only three hierarchy levels and the ROOT LEVEL and SECOND LEVEL are already defined, there’s no need to explicitly define the THIRD LEVEL or the CHILDMOST Member. By default, the childmost level is set as the Dimension Members. Since the Region column is designated as the Dimension Member here, OLATION will automatically consider the regions in that column as the lowest-level Members and link them as direct children of the Second Level Members, which are represented by the Continent column.

The Property Grid of the Table Window indicates the hierarchy we have just defined.

4. After clicking the Save command on the Olation ribbon, you can check the Dimension Hierarchy created by:

a. Navigate to the Database Explorer.

b. Expand Dimensions and right-click on the appropriate Dimension name (in this case, Region).

c. Select the View Hierarchy option.