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 –

  1. Import/Enable BW Hierarchy Table into HANA so it can be used in Models.

/wp-content/uploads/2015/12/1_853182.jpg

     Data Preview

Capture.JPG

2)  Scripted Calculated view needs to be created to flatten the hierarchy.

Capture.JPG

  
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 )

/wp-content/uploads/2015/12/1_853182.jpg

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.

/wp-content/uploads/2015/12/1_jpg_853193.png

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.

/wp-content/uploads/2015/12/1_853182.jpg

Example – For following

/wp-content/uploads/2015/12/1_853182.jpg

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 –

/wp-content/uploads/2015/12/1_853182.jpg

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.

/wp-content/uploads/2015/12/1_853182.jpg

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

/wp-content/uploads/2015/12/1_853182.jpg

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

/wp-content/uploads/2015/12/1_853182.jpg

Once the Hierarchy is defined in either use cases they can be used in Frontend Tools for reporting.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply