Create Dimension Hierarchy

 

Once you have added Members to a Dimension, you will want to define the Dimension hierarchy. The Hierarchy determines the aggregation of Dimension members. Familiarity with the Dimension Hierarchy dialog box will enable you to quickly and easily configure the Dimension hierarchy.

Create a Dimension Hierarchy as follows:

1.   If the Dimension Hierarchy dialog box is not already open,
go to the PowerOLAP ribbon then select Model tab, Dimension command to bring up the Dimensions dialog then double-click on a <dimension name>.

2.   Position the highest-level member by dragging it from the left side and releasing it over the Dimension name in the Hierarchy Definition box on the right. Do the same for sibling Members (if any)—Members that will now be established as the highest-level Members in the Hierarchy. For our exercise, several Salespeople can be added and categorized by regions, such as, Domestic and ForeignEast and Midwest. The Domestic and Foreign Members will represent the top two parent Members categories.
Note that the Dimension Hierarchy  toolbar has several buttons that can help when adding Members to a Hierarchy,  which will be described in the Dimension Hierarchy Toolbar SUMMARY section.

3.   Position the next-highest-level Member by dragging it from the left side and releasing it over the Member that will be its parent (Note that this establishes the parent as an Aggregate member). Repeat this for Member’s “siblings”—that is, place each on the Member that will be its parent.

4.   Repeat this last step as necessary for other Members to position them as children of their desired parent.

5.   Position Detail members by dragging them from the left side and releasing them over the Member that will be their Parent. (Remember that Detail members may occur anywhere in a Hierarchy—what distinguishes them as Detail members is the fact that they have no Children.)

 

 Tip

You may select non-consecutive members by Ctrl>-Clicking or multiple-select consecutive members by Shift>-Clicking and then dragging them on top of their Parent member. An Aggregate member icon is displayed when a Member is positioned over another Member. You may also use several of the toolbar buttons, which are explained in the Summary at the end of this section.

In the figure above, the Dimension Hierarchy dialog box for the Salesperson Dimension contains Domestic and Foreign Aggregate members at the top level. Domestic has Children that are Aggregates: EastMidwest and Southwest, with Detail salespeople under them, while Foreign salespeople are grouped together as Detail members.

Note that the Hierarchy can be collapsed or expanded by selecting the + or  icon to the left of an Aggregate member name.

Delete Members from a Hierarchy

To delete Members from a Dimension hierarchy:

1.   Select the Member(s) in the Hierarchy Definition box,
on the right in the Hierarchy dialog box.

2.   Right-click and select Delete.

 Notes

Remember that when you delete a Member from a Hierarchy, as described above, you DO NOT delete the Member from the Dimension. The Member will still exist in the Member list box, on the left in the Hierarchy dialog box.

 

Multiple and Ragged Hierarchies

In the last example, the Salesperson Hierarchy had two top-level Aggregates, Foreign and Domestic. You probably noticed that the Domestic aggregate was further broken down into regions (EastMidwest and Southwest), whereas the Foreign aggregate did not include that additional level of depth. This is an example of a Ragged Hierarchy—a Hierarchy in which some different level-structures can exist within Dimensions.

As a further example of a ragged Hierarchy, you could create some salespeople who do not belong to either the foreign or domestic aggregations at all, but exist as Detail members at the top level, beside Foreign and Domestic.

You may also want to create Multiple Hierarchies in a Dimension. Perhaps your salespeople, in addition to being categorized as foreign or domestic, are also meaningfully rolled up into types: those who sell to wholesalers and those who sell to restaurants. You could call these categories Type A and Type B. Some of your domestic salespeople along with some of your foreign salespeople may be Type A, and others Type B, and others might not even be included in either type.

These Types could roll up into an aggregate called Total Customer Type. The Domestic and Foreign aggregates might themselves roll up into a separate Hierarchy label entitled Total World. This is what our completed Hierarchy would then look like:

Ragged and Multiple hierarchies in one Dimension can provide flexible and meaningful aggregations.

Use multiple and ragged hierarchical structures to model the Members of a Dimension in meaningful aggregations, useful in your reports and business analysis.