Skip to Content
Author's profile photo Matthias Jensen

New Hierarchy SQL enablement with Calculation Views in SAP HANA 1.0 SPS 10

SQL enabled hierarchies with SAP HANA Calculation Views

Modeling SAP HANA Calculation Views is the key approach to successfully exploit the power of the SAP HANA Platform and leverage key SAP HANA capabilities. With SAP HANA SPS 10 (find enhancement overview here), calculation views provide a deeper integration of hierarchy objects and their exposure for usage within SQL. By leveraging the SQL integration of hierarchy objects, hierarchy-based filters, aggregations and hierarchy-driven analytic privileges are enabled.

Enabling hierarchies for SQL access

Within the SAP HANA calculation view general properties, there is a new checkbox “Enable Hierarchies for SQL access”. With that hierarchy views from shared dimensions used in a Star Join Calculation View are enabled for SQL access.


Once activated, in the open hierarchy dialog for shared hierarchies (available in the semantic node of star join calculation views), the new “SQL Access”-tab is activated and proposes names for a new Node Column and a Hierarchy Expression Parameter for each shared hierarchy. Alternatively to the general enablement of all shared hierarchies of a Star Join calculation view, hierarchies may be enabled individually too.


The hierarchy node column can be referenced for filtering and aggregations as the examples below will highlight.

The hierarchy expression parameter will be used to leverage hierarchy-specific expressions in SQL queries against calculation views. This capability will however only be fully enabled, once hierarchy-expression are documented in the SAP HANA SQL documentation.

The SAP HANA Calculation View metadata tables indicate that SQL access is enabled in the flag HIERARCHIES_SQL_ENABLED of the analytic catalog view BIMC_CUBES. With that, BI clients and query tools will be informed whether they can access the hierarchy data by querying the calculation view.


Additionally, the node column- and the expression parameter-names are documented in the view BIMC_HIERARCHIES of the analytic catalog:


Note: The analytic metadata queries prefixed with MDX <select from BIMC…> ensure consistent semantic information incl. for example localized descriptions.

Using hierarchies for filtering and aggregation within SQL queries

Now, let’s have a look at examples of how hierarchies enabled for SQL access can be leveraged in SQL queries.

Imagine the following hierarchy:


We can now reference the new node column as SalesRepHierarchyNode to filter the result set of our SalesRep-based hierarchy to the subtree of the filter specified.


All the descendants of the node are selected (in this case there are only two direct children of MAJESTIX, but descendants on all levels are included).

Furthermore, the new node column can be used within SQL aggregations in group by clauses and thus additionally ensures the correct evaluation of calculated columns:


In the example shown the Revenue and Cost column contain the hierarchical aggregated values. The Revenue for MAJESTIX is the sum of the revenue of all its descendants (compare this with the Revenue in the filter query above that was grouped by SalesRep i.e. without hierarchy aggregation).

The calculated column Margin is calculated correctly after the aggregation on each hierarchy level. It has been defined as a calculated measure with the following formula: ( “Revenue” – “Cost” )* 100 / “Revenue”.

Hierarchy-driven Analytic Privileges

With SAP HANA SPS 10, in addition to the existing approach of creating classical analytic privileges, users can now create SQL-based analytic privileges as design-time objects, thus providing the flexibility to create analytic privileges restriction based on SQL expressions allowing a simpler way of implementing more complex filtering rules.

For Calculation View hierarchies, which have been enabled for SQL access as detailed out above, within the SQL expression editor of SQL-based Analytic Privileges, you can maintain a filter expression referencing the hierarchy node column.


Because of using the node column this privilege will filter the subtree of the node at runtime and thus behave as a hierarchical analytic privilege.

With SAP HANA SPS 11 the hierarchical privileges can also be defined in the form based part of the editor. There you don’t have to write SQL and you even get a hierarchical value help:


Current Restrictions:

For parent-child hierarchies, compound hierarchies are not supported, i.e. the parent child hierarchies must only have a single attribute as parent and child.

Further new hierarchy-related capabilities:

You find more information about new hierarchy capabilities in SAP HANA calculation views like time-dependent hierarchies and use of hierarchies with variable- and input parameter-definitions as part of the SAP HANA SPS 10 new capabilities blog (overview here), specifically in the What’s new in SAP HANA Modeling presentation here.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Henrique Pinto
      Henrique Pinto

      Do we already know which SAP BI clients are consuming this?

      WebI and Lumira in particular would greatly benefit from this.

      Author's profile photo Matthias Jensen
      Matthias Jensen
      Blog Post Author

      No client is using this yet. It will be probably adopted by the INA layer although I don't know the exact time line. Then Lumira, Cloud for Analytics and AAO could benefit from it.

      No Idea about Webi Plans.


      Author's profile photo Gregory BOTTICCHIO
      Gregory BOTTICCHIO

      Hi Matthias,

      That's really good!

      I would be more than happy to discuss the potential benefits for WebI to use this new functionnality.



      Author's profile photo Former Member
      Former Member

      is there any new information about client who using that ability?

      Author's profile photo DAMODAR SAI MALLIKA

      Hi Matthias Jensen,

      I am creating a view similar to the mentioned one , but i am using SAP HANA WEB IDE .

      i am not able to find SQL ACCESS option here .

      can you please share the information .

      Thanks and Regards,

      Damodar Sai

      Author's profile photo Matthias Jensen
      Matthias Jensen
      Blog Post Author

      Hi Damodar,

      hierarchy features in the WEB IDE will only supported with HANA 2.0 SP00.



      Author's profile photo DAMODAR SAI MALLIKA

      Thanks Matthias !! šŸ™‚

      Author's profile photo Former Member
      Former Member

      Hi Matthias Jensen,

      I have created a Calculation view as Dimension, With parent Child hierarchy. When I checked _SYS_BIC schema -> Column View filder -> It has generated 2 views one for is the normal column view and another one is for hierarchy (RC_HANA/0CA_EMPLOYEE/hier/EMPLOYEE_HIER). When I did data preview for this hierarchy column view, it is showing correct data with all level of hierarchy.

      Now I have created a Calculation view as Cube with Star Join. Here I have joined fact table with above created dimension calculation view. And as u said I have enable hierarchy for SQL Access. So it has generated Node and Attribute both. Once I activated and doing data preview it is showing correct result.

      But If I go and check hierarchy column view (RC_HANA/0CA_EMPLOYEE/hier/EMPLOYEE_HIER), it is only showing root node (LEVEL 00). Only one record I am getting as a result instead of 8.

      Bcz of this if I run below query



        sum("SALARY") AS "SALARY"

      FROM "_SYS_BIC"."RC_HANA/0CA_EMP_01"



      It is giving 0 records.

      If I execute below query



        sum("SALARY") AS "SALARY"

      FROM "_SYS_BIC"."RC_HANA/0CA_EMP_01"



      It gives single record for 'THE_BIG_BOSS', as this is the root node.

      I guess because of this RC_HANA/0CA_EMPLOYEE/hier/EMPLOYEE_HIER hierarchy view is not working correctly it is giving erroneous result with SQL filter conditions.

      Please help with this. It is very urgent as I have to use it in my current project.



      Author's profile photo Matthias Jensen
      Matthias Jensen
      Blog Post Author

      Hi Ankita,

      this sounds strange - can you share the system details - or better create an internal BCO issue about this?



      Author's profile photo Preethish Nandan Botlagunta
      Preethish Nandan Botlagunta


      The information is pretty good, is the "SQL Access" will enable only for the Star join ?



      Author's profile photo Former Member
      Former Member

      Its may be a bit late, buts as its unanswered.

      Yes, SQL Access will available with Star join only.

      Author's profile photo Former Member
      Former Member

      Hi Matthias.

      Is there any documentation available now on how to use the expression parameter?

      I have a very large parent-child hierarchy and I believe I need to query using this expression parameter to drill down to a particular level.

      I have had a look at the SQL documentation on Hierarchy Functions but I can't see how it fits in with the expression parameter name that is generated.



      Author's profile photo Aamir Fakir Mohamed Lakdawala
      Aamir Fakir Mohamed Lakdawala

      HiĀ @Matthias Jensen,

      Do we now have Lumira consuming such views?



      Author's profile photo L. BI
      L. BI

      Hi Mathijjs,

      This is a real good document.

      The SQL gets executed in HANA studio but I get the below error when I try to consume in AO or SAC.

      I am not able to figure out why I am getting this error.

      Also I tried to enable "Propagate Instantiation to SQL View setting in my parent child hierarchy view even then I find no luck.

      Is it to do with front end connection to HANA as I find AP properly applied when I preview data in HANA studio.




      Author's profile photo Danish Muhammad
      Danish Muhammad

      V - was you able to find answer to this? I am also facing same issue while connecting HANA view cube with star join (using hierarchical dimension view)

      Author's profile photo Vikram Kate
      Vikram Kate

      Hi Matthias

      Thanks for great blog. Indeed a great feature and well documented here!

      We have a scenario - user may have access to 2 nodes in hierarchy e.g. in your case level 0 -Ā THE BIG BOSS Ā and level 1 - MAJESTIX.

      SalesRepHierarchyNode in ('THE BIG BOSS','MAJESTIX')

      Problem isĀ view starts duplicating lines for all the nodes under MAJESTIX.

      Any idea how to ensure the nodes are joined distinctively instead of duplicating lines in star join in Calc view?

      Only way I could think of is selecting the leaf nodes of ('THE BIG BOSS','MAJESTIX') with distinct clause in AP itself instead of hierarchy node/s.

      Appreciate your inputs on this !




      Author's profile photo Kiran K
      Kiran K

      Matthias Jensen - Hello Jensen ,

      In Graphical Hana View(transactional Data) if I enableĀ  & associate the Parent-Child hierarchy to the Variable prompt.

      When I Select any intermediate node from the loaded List of Values which are displayed in Hierarchical fashion , Will that filter considers internally & aggregates KPI for all descendent nodes ???



      Kiran K