Relationships within hierarchies: important rule

Often the attributes linked to the dimensions in a hierarchy are closely related to each other and are often attributes of the same entity. A customer dimension is related to the region dimension that in turn is related to the country dimension and this can be a reason to make a hierarchy in order to reduce the amount of categories of the dimensions. Dimensions of higher order (those dimensions that have the lowest amount of categories) should reside higher in the hierarchy. The product-line dimension is of a higher order then the product dimension and therefore the hierarchy is as follows: Product-line à Product.

Different levels? Then there is a hiearchy

Dimensions do not have necessarily a hierarchy. However, if a dimension consists of different levels, those levels have a relationship with the level above and the level below. The type of such relationships can be expressed, similar to relational algebra, in four forms:

• one-to-one relationships;
• one-to-many relationships;
• subset one-to-one relationships;
• many-to-many relationships.

Important rule for binding measures to dimensions

The initial relationships between dimensions and the binding of a measure to a dimension can be extracted from the meta data model of the source database. The binding of a measure to a dimension can be derived by using the following rule:

If a measure is linked to an attribute of entity x and a dimension is linked to an attribute of entity y, and x has a one-to-many relation with y then the measure cannot be dimensioned by the attribute from y.

The rule can be easily extracted from the data model

The variation we found here can be easily extracted from the data model. Thus, when the user chooses a given dimension, the interface should disable or hide non-valid key indicators. In addition, when the user chooses a given measure, the interface should disable or hide non-valid dimensions.

Types of hierarchies

An hierarchy may exist in many forms, which deserves a closer inspection because senior managers are, due to their high position in the organization, much more exposed to data and information that is hierarchically shaped. Some types of hierarchies are not balanced, which should be clear to the user. The different types of hierarchies can be applied to measures as well as dimensions. Buytendijk describes 6 types of hierarchies (Buytendijk, 1996a):

1. unbalanced hierarchy (applies to dimensions and measures);
2. time related hierarchy (applies only to dimensions);
3. multiple hierarchy (applies only to dimensions);
4. non additive hierarchy (applies only to measures);
5. multiple top hierarchy (applies to dimensions and measures);
6. branch-value-hierarchy (applies only to dimensions).

These hierarchies can be combined but a few can never appear together, such as the unbalanced hierarchy and the time related hierarchy. Branch-value-hierarchies, where the tree, the branches as well as the leaves might contain values, must be carefully presented to users since the sum of the all the values of the childs might differ with the total value of the parent.

Make clear where to drill

For each hierarchy, I suggest, when presenting hierarchies to the user, to make clear when a drill-down or roll-up can be executed (for example by changing the mouse cursor and by using some kind of +/- indication) and how aggregation is performed. In the table below, I have summarized the different types of hierarchies with a brief explanation for each type.

Hierarchy type	Description
Time-related	A time-related hierarchy is a special dimension that shows how measures will succeed over time.
Unbalanced	This type of hierarchy does not have the same number of levels throughout the tree. An organization hierarchy for instance, might have more depth at one branch than another might.
Multiple	A multiple hierarchy has at least two different dimensions to drill-down to a lower level of detail. A customer hierarchy can have for instance a dimension country and a dimension customer type to drill-down.
Non-additive	Non-additive hierarchies are measure hierarchies that could consist of all kinds of aggregations. For example, the two base measures Gross margin and Turnover rate should be multiplied (instead of being summed) to get the top-level element Return on Equity.
Multi-top	Hierarchies should have multi top categories when an overall total category doesn’t make sense as the case would be with time-dimensions.
Branch-value	Branch value hierarchies have branches that contain numbers that have to be summarized on the same level. Imagine for instance a customer hierarchy with a top level that consists of parent companies. The level below the top level consists of daughter companies that have turnover as well as their parent company. In that case, a company at the top level represents the summarization of the belonging daughter companies along with the turnover accomplished by the parent company itself.

The depth of the hierarchy is an important variation too. The user should know how many times a drill-down or roll-up operation should be executed to arrive at the bottom- or top-level of the hierarchy. Thanks to Daan van Beek, Business Intelligence consultant.