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 we have considered HANA cloud as backend for data processing and modelling and Analysis for office for reporting purpose.

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 unavailable 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 may like to exclude few Nodes / GL from Hierarchy (like Node Recovery and Write-off).

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 way to achieve same in SAP HANA Modelling:

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

• Design time procedure:

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

2. Stage

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

3. 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

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

But with introduction of new Node in calculation view this requirement can be achieved in Dimension view without need of creating procedure, staging table or complex logic in view etc.

(Figure 2 Nodes in Calculation view)

In above snapshot Figure 2 additional to standard 5 node, we now have 5 new nodes. For this requirement, additional to standard 5 node in calculation view we used Hierarchy function and Minus node.

Official documentation related to all Node can be referenced below:

Working With View Nodes – SAP Help Portal

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

HIERARCHY FUNCTION 

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 of this but in this case, it is used for “Node Exclusion” namely Recovery and Write-offs, with their underlined GL Account, from GL hierarchy as show 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.) Node with usage of Union, Hierarchy function and other node.

(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:

1. Parent: ParentId from underline Hierarchy data source.
2. Child: NodeId from underline Hierarchy data source.
3. Start: Technical node name ABC1/2 (corresponding to Recovery and Write-offs in this). It extracted data belonging to both nodes and underline 5 GL Accounts here.
4. Default setting for Depth, orphan handling and cache
5. Sibling Order By: NodeId in ascending order

• Minus Node: Minus operation performed between GL Hierarchy data and Hierarchy Function to retrieve Hierarchy data which excluded required GL Account and Node.
• 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 we can see that Node Recovery 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)

Summary

Thus, with usage of powerful Node like Hierarchy Function, Minus and other node of Calculation view , requirement like these can be achieved. During runtime,Hierarchy creation get triggers with On-fly logic of Hierarchy Node and underneath GL Account removal.

With this approach step like Data processing,staging was avoided and requirement  had been achieved with flexibility.