Summary
Hierarchies play an important role in multi-Dimensional reporting; our traditional BW system is efficient and more popular in handling complex hierarchies for analytics. The customers with HANA platform have the option to use HANA modeling tools to build Information views directly on database tables and consume them in reporting. In this HANA side-car approach, information views/models are built with the combination of master data and transaction for dimensional reporting in BI.
In this document I am going to explain the topic how to model the account hierarchy in HANA and combined with transaction data using graphical calculation views.
Preparation
In SAP ECC, hierarchies are defined as sets and they are stored in SETNODE and SETLEAF tables. We will leverage these 2 tables to build the Parent-child hierarchy model.
For this document purpose, I have created an account hierarchy in GS01 T code, as a basic set.
ROOTNODE is the Top Node of the hierarchy, which wouldn’t have Parent. It is available in SETNODE table; logic will be defined to get it, explained in the next section of the document.
SUBNODES are all the below nodes forming the hierarchy structure to roll up, the information is stored in SETNODE table, join logic is used to get all the required sub nodes.
LEAVES are the actual dimension values for which transaction data is posted, it is available in SETLEAF table, Join logic is used to get them as required.
I have created a transaction data table in ECC HANA system, which got some amounts as below,
A HANA model will be built to analyze this transaction data by leveraging the master data hierarchy for account.
Solution in Detailed
Step 1 Creation of ROOTNODE model
a. SETNODE table is taken into 2 projection nodes and then join the SETNAME with SUBSETNAME.
Left outer join on these 2 Projections, Project1.SETNAME < -- > Projection2.SUBSETNAME with this, all the Subsets which are also the Sets are retrieved.
Note: In this document, SETCLASS and SUBCLASS columns are filtered with values applicable to the hierarchy, SETCLASS = ‘0000’ and SUBCLASS = Blank in all the places used. However, you may configure them as variables or input parameters to dynamically select the required hierarchy class.
b. Aggregation on SUBSETNAME column, this field is taken from Projection 2.
Filter is defined on the SUBSETNAME column to get the records where it is NULL only.
With that filter, only ROOT node is taken, in our example, it is HANA.
C. Data preview on the RootNode calculation view.
In the below screen shows the complete model of Rootnode calculation view. Also in the data preview, SETNAME is filtered to HANA to display the number of records for SETNAME column. However, it is filtered with input parameter.
Step 2, Creation of Account Hierarchy Calculation view.
Parent-child hierarchy derivation is split into 4 parts, they are
a.SUBNODES
b.Leaves
c.LEAVES to SUBNODE link
d. RootNode
These 4 parts are explained in detailed,
a.SUBNODES derivation, link is created with 2 columns, they are parent and Child. In this example, the data for SUBNODES would be as in below table.
| Parent | Child |
| BALSHEET | CASH |
| ProfitLoss | EXPENSE |
In order to get this display, SETNODE table is taken 2 times in the projections. First projection is named as Parent and the second one as Child. Join is on Parent.SUBSETNAME < -- > Child.SETNAME
The output columns after the join are
| Parent.SETNAME = Parent | Child.SETNAME = Child |
b. LEAVES derivation, master data values are available in SETLEAF table, so it is taken into a projection.
The output columns on this node are,
| SETLEAF.SETNAME = Parent | SETLEAF.VALFROM = Child |
c. LEAVES to SUBNODE link, the immediate parents of Leaves couldn’t be derived in the step 2.a and 2.b, because they don’t exist as SETs in the SETNODE table.
Example record to be derived in this node is,
| Parent | Child |
| EXPENSE | OPEX |
SETLEAF and SETNODE are taken into 2 projections, then join is created on
SETLEAF.SETNAME < -- > SETNODE.SUBSETNODE
Projection 5 is used for make a join on Projection_6 , after that SETNAME and SUBSETNAME both are taken as Parent and child from the Projection_6 only.
The output columns after the join node are,
| LeavesNode.SETNAME = Parent | LeavesNode.SUBSETNAME = Child |
d. ROOTNODE Datasource, RootNode calculation view is taken as Datasource.
Input parameter is created for ROOTNODE value, it is used to filter on the column SETNAME of RootNode Datasource.
The output column after this node is, only the child column.
| RootNode.SETNAME = Child |
Step 3, UNION and mapping, combining the above 4 parts to get the parent child hierarchy.
Parent and Child fields from the 4 nodes are mapped to respective columns of the UNION node.
Note: RootNode wouldn’t have Parent column to map.
Step 4, Parent Child Hierarchy set up, account hierarchy is setup by taking the Parent and child columns from the above UNION node.
Note: ROOT node in the hierarchy should be filtered with input parameter ZIP_ROOT to input the value in the report. Also in the advanced tab, Orphan nodes, update the setting to Ignore.
Then the final model should look like below,
Note: Map the ZIP_ROOT input parameter to this calculation view also.
Step 6, Execute the query in Analysis office report for data display.
Created a query in Analysis office to analyze the values, input parameter value is given as HANA.
Then, hierarchy set up on the Child column is dragged into Rows section of analysis office. The result set is categorized with hierarchy rollup of HANA as shown below.
Conclusion
We have successfully created a parent child Hierarchy on GL account configured in ECC with native HANA modeling capabilities.
Note: Creating variables/filters on a certain node level is not possible, for example if the user wanted to see only OPEX amount in the above example, will have to navigate from the root node to it. Input can’t be directly on OPEX.
Level hierarchy with FI/CO accelerator approach can overcome this problem; it will be explained in another detailed document.
- SAP Managed Tags:
2 Comments
