Modeling Hierarchies

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Define a hierarchy to organize data for efficient navigation

Hierarchy

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:

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.

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.

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.

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.

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

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.

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.

Create a Parent-Child Hierarchy

Type of Generated Hierarchy

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 PropertiesGeneral 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 meta-data 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 SQL Hierarchy Views below. 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 meta-data 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 meta-data defined in the BIMC tables.

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

Hierarchy Type

 AutoSQL Hierarchy ViewsNo Hierarchy View
Hierarchy Meta-data (records in the BIMC* tables in schema _SYS_BI)YesYesYes
SQL Hierarchy (classic SQL View in the container schema)YesYesNo

Name of a Calculation Views and its Associated SQL Hierarchy Views

Let's take an example to show how the various objects related to a Calculation View are named in the corresponding HDI Container schema.

Assume one level hierarchy, PROD_LEV_HIER, has been modeled within the Calculation View HC300::CVC_SALES_HIER.

Then the SQL Hierarchy view is called HC300::CVC_SALES_HIER/PROD_LEV_HIER/sqlh/PROD_LEV_HIER.

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

Login or Register