Modeling Hierarchies

After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Define a hierarchy



Parent-child hierarchy columns usually contain IDs or key fields instead of plain text.

Hierarchy Comparison

Level Hierarchies

Procedure to Implement Level Hierarchies

A level hierarchy is defined by choosing columns from a source data set and organizing them in a sequence that provides a drill down path. Each level is based on a separate column in the source data.

Node Styles

Node styles are used to define the output format of a node ID.

Using a fiscal hierarchy example, the following table demonstrates the different node styles:

Node Styles

Level StyleOutputExample
Level NameLevel and node nameMONTH.JAN
Name OnlyNode name onlyJAN
Name PathNode name and its ancestorsFISCAL_2015.QUARTER_1.JAN

Level Types

A level type specifies the semantics for the level attributes. For example, the level type TIMEMONTHS indicates that the attributes are months such as January, February, or March.

The level type REGULAR indicates that the level does not require any special formatting.

Hierarchy Member Order

Using the Order By drop-down list, an attribute can be selected for ordering the hierarchy members in the order specified in the Sort Direction column.

Orphan Nodes

An orphan node in a hierarchy is a member that has no parent member.

Level hierarchies offer four different ways to handle orphan nodes. They are as follows:

  • Root Nodes

    Any orphan node will be defined as a hierarchy root node.

  • Error

    When encountering an orphan node, the view will throw an error.

  • Ignore

    Orphan nodes will be ignored.

  • Step Parent

    Orphan nodes are assigned to a step parent you specify.

Parent-Child Hierarchies

When creating a parent-child hierarchy, the first step is to define the nodes that make up the hierarchy.

The Child column contains the attribute used as the child within the hierarchy, whereas the Parent column contains the attribute used for its parent.

You can define multiple parent-child pairs to support the compound node IDs. For example:

  • CostCenterParentCostCenter

  • ControllingAreaParentControllingArea

The preceding list of parent-child pairs constitutes a compound parent-child definition to uniquely identify cost centers.


Multiple parents and compound parent-child definitions are not supported by MDX.

Advanced Properties of a Parent-Child Hierarchy

Additional attributes can also be added to the hierarchy, making it easier to report on.

Aggregate All Nodes

The Aggregate All Nodes property defines whether the values of intermediate nodes of the hierarchy should be aggregated to the total value of the hierarchy’s root node. If you are sure that there is no data posted on aggregate nodes, you should set the option to False. The engine then executes the hierarchy faster.

Default Member

The Default Member value helps identify the default member of the hierarchy. If you do not provide any value, all members of the hierarchy are default members.

Orphan Nodes

In a parent-child hierarchy, you might encounter orphan nodes without a parent. The Orphan Nodes property defines how these should be handled.


If you choose to assign an orphan node to a step parent, the following rules apply:

  • The step parent node must be already defined in the hierarchy at the ROOT level.

  • The step parent ID must be entered according to the node style defined in the hierarchy.

Root Node Visibility

The Root Node Visibility property is used to define whether an additional root node needs to be added to the hierarchy.


Cycles are typically not desirable in a hierarchy.

In such cases, the Cycles property is used to define how these should be broken when encountered, or whether an error should be thrown.

Time-Dependent Hierarchies

Time dependency is supported for more complex hierarchy data, such as human resources applications with their organizations, or material management systems with BOMs where information is reliant on time.


Defining a time dependency is only possible in calculation views, and for parent-child hierarchies.

Enabling time dependency supports hierarchies based on changing elements valid for specific time periods. This allows displaying different versions of a hierarchy.

Your source data needs to contain definition columns consisting of a Valid From and a Valid To column.

Determining the Validity Period

When a hierarchy needs to show elements from an interval, you have to define two input parameters; a From Parameter, and a To Parameter. If the hierarchy needs to show elements valid on a specific date, you need one input parameter defined as the Key Date.

Create a Parent-Child Hierarchy

Save progress to your learning plan by logging in or creating an account