- SAP Managed Tags:
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.
17 Comments
