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 Foreign, East
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.
.png)
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.)
.png)
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: East, Midwest 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 (East, Midwest 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:
.png)
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.