Skip to Content

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.

HierEnablement.jpg

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.

HierPopup.jpg

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.

HierBimcCubes.jpg

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

HierBimchier.jpg

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:

HierExample.jpg

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.

HierFilter.jpg

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:

HierAggregation.jpg

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.

HierPrivilege.jpg

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:


FormBasedHierPrivileges.jpg

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.

To report this post you need to login first.

11 Comments

You must be Logged on to comment or reply to a post.

    1. Matthias Jensen 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.

      Matthias

      (0) 
  1. Ankita Bhagat

    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

    SELECT

      “EMPLOYEE_ID”,

      sum(“SALARY”) AS “SALARY”

    FROM “_SYS_BIC”.”RC_HANA/0CA_EMP_01″

    WHERE “EMPLOYEE_HIERNode” = ‘MAJESTIX’

    GROUP BY “EMPLOYEE_ID”

    It is giving 0 records.

    If I execute below query

    SELECT

      “EMPLOYEE_ID”,

      sum(“SALARY”) AS “SALARY”

    FROM “_SYS_BIC”.”RC_HANA/0CA_EMP_01″

    WHERE “EMPLOYEE_HIERNode” = ‘THE_BIG_BOSS’

    GROUP BY “EMPLOYEE_ID”

    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.

    Regards,

    Ankita

    (0) 
    1. Matthias Jensen Post author

      Hi Ankita,

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

      Regards

        Matthias

      (0) 
  2. Ricky Mustonen

    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.

    Thanks,

    Ricky

    (0) 

Leave a Reply