Transforming Hierarchy using HANA Calculation view

Introduction:

This blog post is on usage of two powerful Nodes namely Hierarchy function and Minus Node in HANA Calculation view. Both Nodes are available in SAP HANA 2.0 XSA and HANA cloud.

Minus and Hierarchy function Node are available starting SAP HANA 2.0 SPS01 and SPS03 respectively for on premise and available in SAP HANA CLOUD version.

This use case will be helpful in business scenario where one wants to migrate from SAP BW 7.X to SAP HANA 2.0 XSA or SAP HANA Cloud. SAP BW is known for Data warehousing and strong reporting capabilities. While migration from SAP BW to SAP HANA 2.0 or SAP HANA cloud some of features are not available out the box. In this case HANA cloud is considered as backend for data processing and modelling purpose and Analysis for office for reporting.

Background:

Hierarchies created in SAP operational system like (SAP S/4HANA or SAP ECC) can be consumed in SAP BW to produce different perspective of transactional data. SAP BW does offer flexibility to consume in any shape or form i.e., it can be modified (like Node/child removal) independently in SAP BW. Additionally, SAP BEx reports provide OLAP and drill down features. Thus, enables business users to analyze data at different Hierarchy levels. For example: Analyzing posting at different level of GL Account hierarchy.

However same functionality is not available out of the box in SAP HANA and SAP Analysis for Microsoft office scenario, but it is achievable.

Note: This blog is not about feature comparison but demonstrates how to exclude Node/child from Hierarchy in SAP HANA cloud using SAP Analysis for Microsoft office scenario as front end.

Use Case:

In operational system, business use complete GL Hierarchy created in system but for reporting purposes either they may need full GL Account Hierarchy or  like to exclude few Nodes / GL from Hierarchy (like Node Recovery and Write-off in this case).

In SAP BW landscape same can be achieved by leveraging standard SAP BEx feature. It allows restriction on Node which need to be excluded i.e., Node chosen to exclude underneath node and GL Account in Hierarchy recursively.

For example, in below figure 1, characteristic restriction applied on GL Account where 3 nodes were excluded from GL Hierarchy used in Query.

(Figure 1: Sample Node Exclusion from GL Hierarchy in SAP BEx)

With this In build feature, recursively underneath defined Hierarchy Node and GL Account get excluded Automatically.

Possible ways to achieve same in SAP HANA Modelling:

There are two possible approaches, having pros and cons, as follows:

1) Design time procedure:

a. Develop

Develop Procedure in SAP HANA database for SQL data warehousing , consuming Hierarchy data table and remove the node not required for reporting using recursive loops and logic etc.

b. Stage

Stage data into table and consume in Dimension calculation view with parent child Hierarchy defined in semantics.

c. Consume

Thereafter, join dimensions view with transactional data set and consume in SAP Analysis for Microsoft office report.

Pros:

1. Quick implementation.

2. Flexibility with writing script.

Cons:

1. Intermediate data staging.

2. Scheduling and Maintenance to get latest snapshot of data.

2) Calculation view:

Achieving this requirement via. calculation view using standard 6 node (Projection/Aggregation/Join/Union/Rank/Table function) get quiet complex as recursive operation and complex logic need to build which can remove multiple Node and underline GL’s.

But with introduction of additional Nodes, this requirement can be achieved in calculation view without creating procedure, staging data or complex logic in view etc.

(Figure 2: Nodes in Calculation view)

In Figure 2, additional to standard 6 node, we now have 5 new nodes (Non equi Join/Minus/Intersect/Window function/Hierarchy Function). For this use case, Hierarchy function and Minus node are used from 5 new nodes.

Note: Considering scope of this blog, we will go through in detail related to use case for Hierarchy function and Minus Node.

Hierarchy Function Node

Users can model calculation views with hierarchy function view nodes that allow them to use several SAP HANA hierarchy functions in the view definition. The hierarchy functions typically help work with hierarchical data.

There could be multiple usages but in this use case, it is used for “Node Exclusion” namely Recovery and Write-offs, with their underlined list of GL Account, from GL hierarchy as shown in Figure 3 below:

(Figure 3: Highlighted Node & underneath GL account to be excluded)

Dimension calculation view created (shown in below figure 4) which consumed GL Hierarchical data (having column like Node Id, Parent Id, Child Id, Next Id etc.).This view use Node like Hierarchy function, Minus, Union and others from list.

(Figure 4: Dimension Calculation view Logic with Nodes)

Pseudo Code of Calculation view

◉ Projection HGL_ACCOUNT: Consume GL Hierarchy data.

◉ Hierarchy function: Input data from projection with definition as shown in figure 5:

(Figure 5: Properties configured in Hierarchy function Node)

Use below as definition in Hierarchy definition:

a. Parent: ParentId from underline Hierarchy data source.

b. Child: NodeId from underline Hierarchy data source.

c. Start: Technical node name ABC1/2 (corresponding to Recovery and Write-offs). It extracts data belonging to both nodes and underline 5 GL Accounts .

d. Default setting for Depth, orphan handling and cache.

e. Sibling Order By: NodeId in ascending order

Minus Node: Minus operation performed between GL Hierarchy data and Hierarchy Function to retrieve Hierarchy data which excludes require Node and underneath GL Account.

Join node 1, 2 and 3 use to retrieve text corresponding to GL Account and node in hierarchy.

Semantics:  Parent Child Hierarchy defined in semantics to enable creation of Hierarchy at run time.

Note:  Above dimension view with parent child Hierarchy is Inner joined with transactional data in reporting layer Calculation view (with star join) shown in Figure 6.

(Figure 6: Calculation view with star join)

Output of Report

In Analysis for office, calculation view shown in figure 6 is consumed. SAP Analysis for Microsoft office report displays G/L Account Hierarchy along with two KPI’s from transaction data. In below figure 7, Node Recoveries and Write-offs in G/L Hierarchy are excluded with underneath 5 G/L Account.

(Figure 7: Target scenario Result with Node removed from G/L Hierarchy)