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:
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.
Do we already know which SAP BI clients are consuming this?
WebI and Lumira in particular would greatly benefit from this.
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.
That's really good!
I would be more than happy to discuss the potential benefits for WebI to use this new functionnality.
is there any new information about client who using that ability?
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,
hierarchy features in the WEB IDE will only supported with HANA 2.0 SP00.
Thanks Matthias !! 🙂
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"
WHERE "EMPLOYEE_HIERNode" = 'MAJESTIX'
GROUP BY "EMPLOYEE_ID"
It is giving 0 records.
If I execute below query
sum("SALARY") AS "SALARY"
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.
this sounds strange - can you share the system details - or better create an internal BCO issue about this?
The information is pretty good, is the "SQL Access" will enable only for the Star join ?
Its may be a bit late, buts as its unanswered.
Yes, SQL Access will available with Star join only.
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.
Hi @Matthias Jensen,
Do we now have Lumira consuming such views?
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.
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)
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 !
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 ???