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 meta-data defining the hierarchy, as well as column views materializing the MDX hierarchy. No SQL Hierarchy view is generated.
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.
| ||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.