Tell us what you think

We want to create the best possible experience for our learners. Your feedback helps us to improve the experience for everyone.

Modeling Hierarchies

Objective

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.

The type of hierarchy you want to create will depend on the structure of the source data:

A level hierarchy requires each level to be stored in a separate column. Rows represent leaf nodes.
A parent-child hierarchy requires columns of the same data type for parents and children. Rows represent each node.

Source data tables for 2 different types of hierarchy. For a level hierarchy, a table with two columns with the same data type. For the Parent-Child hierarchy, a table with as many columns as the number of levels.

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:

Here are the main differences between the two hierarchy types:

Parent-Child Hierarchy

Distinct fields define the parent-child relation.
Parent and child fields usually have the same data type.
Parent and child fields usually have the same data type.

Level Hierarchy

Heterogeneous fields (possibly with different data types) are combined in a 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.

To implement level hierarchies, use the following procedure:

Step 1

Select the source tables(s) for the view.

Step 2

Select the columns that should be part of the view, including any columns required for the hierarchy.

Step 3

In the Semantics node, select the Hierarchies tab and click the '+' button in the Hierarchy pane.

Step 4

Add the columns to the hierarchy in the correct level order from top to bottom, with the lowest granularity at the lowest level of the hierarchy.

Additionally, you can define an ascending or descending sort direction per level.

Creation of a Level Hierarchy. Step 4: the PRODUCT_LEVEL_HIERARCHY is defined. First level is PRODUCT_TEXT and second level is PRODUCT_TEXT.

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 Style	Output	Example
Level Name	Level and node name	MONTH.JAN
Name Only	Node name only	JAN
Name Path	Node name and its ancestors	FISCAL_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 dropdown 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 you are creating a parent-child hierarchy, the first step is to define the nodes that make up the hierarchy.

Creation of a Parent-Child Hierarchy. In the Hierarchies tab, the EMPLOYEE_HIERARCHY is defined. DNUMBER is selected as Child column and PARENT_DNUMBER is selected as Parent column.

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.

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 you to 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.

Creation of a Parent-Child Hierarchy. In the TIME DEPENDENCY section, Enable time dependency is selected. Valid From and Valid To columns need to be defined. You can choose to query the hierarchy using either a date range or a single specific date.

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

DemoStart Demo

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 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.

Hierarchy Type

	Auto	SQL Hierarchy Views	No Hierarchy View
Hierarchy Meta-data (records in the BIMC* tables in schema _SYS_BI)	Yes	Yes	Yes
SQL Hierarchy (classic SQL View in the container schema)	Yes	Yes	No

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.

Continue to quiz