This blog is a joint effort by – Ravindra Shukla and Sanjay Sinha ( Century Link Cognilytics)
Problem statement
There is no automated process and no standard functions available to import BW hierarchy in SAP HANA and use it in reporting tools. This blog will talk about “How do we use hierarchy (parent and child) defined in BW into HANA model”.
Details:
SAP BW has matured to manage master data including hierarchy creation, management and using in BEX reports. BW hierarchy work seamlessly in BW on HANA. In the use case of using the BW hierarchy in HANA Modelling and Business Object frontend tools – Analysis for office, Design Studio with HANA modelled object, there are challenges to seamlessly integrate / reuse BW hierarchies. Although BW hierarchy can be as simple as Parent / Child relationship of one data dimension (ex G/L Account) or can be a more complex with Text Nodes and multiple dimensions such as Organizational Hierarchy with Levels, Region etc. This white paper will be focusing on two cases – Parent / Child with one dimension and Level Based Hierarchy.
In HANA models Hierarchy has to be defined in each Model that is used in reporting. This is another major difference than BW where you define the Hierarchy once on the info object and it can be used in any info provider. In HANA for every info provider you do not have to create the hierarchy but have to define it to be able to use it in areporting tool.
Pre-requisite - So what do you need to start off with?
An instance of BW on HANA
Hierarchies maintained in BW
Basic SQL and HANA Modeling background
A front end tool to consume the hierarchy created in HANA
Note – All tables, models, screenshots are for illustration only. You will have to adjust the code, configuration based on your requirement
Approach:
The approachwill depend on the use case.
Use Case1 – Level Based Hierarchy
Create a flattened view of the Hierarchical structure with fields for various levels in the Hierarchy and then use this view to define Level Hierarchy in HANA. Following section will cover this in details.
Use Case 2 – Parent / Child Relation
Using the BWhierarchy table for parent / child relationship and create a Hierarchy (Parent / Child) in HANA modelling.
Use Case 1: Level Based Hierarchy –
Flattening is based on tracing the parent of every node starting from leaf node.
Ex GL Hierarchy. It is an unbalanced Hierarchy –
Steps -
- Import/Enable BW Hierarchy Table into HANA so it can be used in Models.
Data Preview
2) Scripted Calculated view needs to be created to flatten the hierarchy.
Below is the code snippet to flatten a 4 Level Hierarchy. You can expand the code to have more levels if required. This is just an example.
select
F."NODENAME",
(CASE F."TLEVEL"
WHEN 5 THEN H1.NODENAME WHEN 4 THEN H2.NODENAME
WHEN 3 THEN H3.NODENAME WHEN 2 THEN H4.NODENAME
ELSE ' ' END ) AS L1,
(CASE F."TLEVEL"
WHEN 5 THEN H2.NODENAME WHEN 4 THEN H3.NODENAME
WHEN 3 THEN H4.NODENAME
ELSE ' ' END ) AS L2,
(CASE F."TLEVEL"
WHEN 5 THEN H3.NODENAME WHEN 4 THEN H4.NODENAME
ELSE ' ' END ) AS L3,
(CASE F."TLEVEL"
WHEN 5 THEN H4.NODENAME
ELSE ' ' END ) AS L4
FROM "BHO_POC"."GL_HIER1" F
LEFT OUTER JOIN "BHO_POC"."GL_HIER1" H4 ON H4."NODEID" = F."PARENTID"
LEFT OUTER JOIN "BHO_POC"."GL_HIER1" H3 ON H3."NODEID" = H4."PARENTID"
LEFT OUTER JOIN "BHO_POC"."GL_HIER1" H2 ON H2."NODEID" = H3."PARENTID"
LEFT OUTER JOIN "BHO_POC"."GL_HIER1" H1 ON H1."NODEID" = H2."PARENTID"
where F."IOBJNM" = '0GL_ACCOUNT'
and F."OBJVERS" = 'A';
Data Preview from the Calculated View – ( This is an unbalanced hierarchy , however the same code will work in case of a balanced hierarchy as well )
Similar result can be achieved using a HANA Function, however the performance may not be as good as above as a select is done for each row to get the parents vs a join as in above sql. The SQL in calculation view becomes much simpler with using FUNCTION.
SQL to create Function
CREATE FUNCTION GET_PARENT (
PARENTID SMALLINT , CNT INT, LVL INT )
RETURNS NODENAME varchar(12)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN
DECLARE PID SMALLINT;
DECLARE PID1 SMALLINT;
DECLARE CNT1 INT;
PID = :PARENTID;
CNT1 = :CNT;
IF :LVL > :CNT
THEN
WHILE :CNT1 < :LVLDO
select "NODENAME" , "PARENTID" INTO NODENAME,
PID1 FROM "BHO_POC"."GL_HIER1" WHERE "NODEID" = :PID AND "OBJVERS" = 'A' ;
PID = :PID1;
CNT1 := :CNT1 + 1;
END WHILE;
ELSE
NODENAME := ' ';
END IF;
END;
Calculation View to generate levels for all leaf value. You can see that adding level becomes much easier when compared to not using FUNCTION.
Example - For following
Out of following picture will be –
1000701(Leaf Value) --> 2010211 (L4) -> 201021 (L3) -> 20102 (L2) -> 2010(L1)
1000702(Leaf Value) --> 2010212 (L4) -> 201021 (L3) -> 20102 (L2) -> 2010(L1)
1000501(Leaf Value) --> -> (L4) --> -- (L3) -> 20101 (L2) –> 2010 (L1)
Flattened
Table from Calculation View –
We can create level based on hierarchy in HANA based on column L1, L2, L3 etc. The above calculation view can be joined to transactional data and then the hierarchy can be defined on the semantic layer of the final reporting view.
Issues with Flattened tables -
When we expand hierarchy in Analysis for Office OR Design studio tools – Blank Level are displayed as spaces. It need additional tackling in front end-tool.
Use Case 2: Parent / Child Based Hierarchy –
Well, there is no direct way to do it, but the following workaround will help you extract and replicate the hierarchy in HANA from BW. You can use the BW Hierarchy table and use the NODEID and PARENTID fields to define a Parent Child relationship in the Hierarchy Tab in the Semantic layer of the final reporting view.
I will take the example of HR Hierarchy. It is an organizational hierarchy based on Employee i.e. has each employee and its Manager has an entry
in the Hierarchy. The structure of BW Hierarchy table is same as in first use case, however the data is different.
Define an Attribute View with three fields NODEID, PARENTID and NODENAME. (Or you can add the hierarchy table to the
Master Data Table for the object in the Attribute view of the object, in this case ZEMPLOYEE and then add the three fields).
Use this Attribute view in a reporting Analytic View or Calculation View ( Join on ZEMPLOYEE) and then define the Hierarchy in the Semantic Layer as Below
Once the Hierarchy is defined in either use cases they can be used in Frontend Tools for reporting.
