Modeling Hierarchies

Hierarchies play a key role in data modeling as they provide an easy-to-use navigation aid when drilling through lots of data. As the user clicks on the hierarchy node, the node is expanded to offer even more lower level values. Typically, measures are accumulated from the lower level to the upper levels. A lot of business data is organized by hierarchy so this is a very popular topic for modeler.

SAP HANA Cloud calculation views support two types of hierarchy: Level and Parent-Child.

Note

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

Hierarchy Comparison

Let's take a look at an example of each type of hierarchy:

When you are 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:

• CostCenter → ParentCostCenter

• ControllingArea → ParentControllingArea

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

Caution

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.

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.

Note

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.

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.

Caution

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.

When you define a hierarchy in an SAP HANA Cloud calculation view, whether it is a level or parent-child hierarchy, the hierarchy is materialized upon build/deployment by various tables and/or views in the HDI Container schema (column views or classic SQL views) or in other locations, especially the BIMC* tables and views in the _SYS_BI schema. These generated objects provide detailed hierarchy node relationship data to enable processing of the hierarchy when the front-end tool is not able to generate the hierarchy relationships itself.

A key setting allows you to influence the way SAP HANA translates the defined hierarchy into technical tables and views in the database. This is the Hierarchy Type setting, which you can define in the Semantics of the Calculation View, in the View Properties → General tab.

The setting can take three values:

• Auto (default value)

  This setting is useful if you exchange views between SAP HANA Cloud and On-Premise, because upon build, SAP HANA generates the following:

  • In SAP HANA Cloud: classic SQL views to materialize the SQL hierarchy

  • In SAP HANA On-Premise: MDX hierarchies (only compatible with SAP HANA on-Premise), including the metadata defining the hierarchy, as well as column views materializing the MDX hierarchy. No SQL Hierarchy view is generated.

  Note

  In SAP HANA Cloud, the setting Auto has exactly the same effect as the following SQL Hierarchy Views. The key benefit is that you can transfer Calculation Views that have the Auto setting from SAP HANA On-Premise to SAP HANA Cloud without a need to change their properties. For more details about SQL hierarchies in analytical queries, you can consult SAP Note 3139372.
• SQL Hierarchy Views

  Upon build, classic SQL views are generated to materialize the SQL hierarchy.

• No Hierarchy Views

  Upon build, the metadata of the hierarchies is generated (_SYS_BI.BIMC* tables) but the hierarchies themselves (detailed list of members, and so on) are NOT generated.

  This setting is should be used when the consuming front-end tool itself can generate the set of hierarchy members and their relationships, based on the hierarchy metadata defined in the BIMC tables.

The table below summarizes which objects and references are generated based on the chosen option.