Financial Management Blogs by Members
Dive into a treasure trove of SAP financial management wisdom shared by a vibrant community of bloggers. Submit a blog post of your own to share knowledge.
cancel
Showing results for 
Search instead for 
Did you mean: 
prateek_bagora
Explorer
In this blog post, I will go through an approach of utilizing SAP Hierarchies in Visualization tools like Power BI, Tableau, and so on. To exploit the SAP Dimension Hierarchies, maintained in a parent-child structure for Visualizations, we first need to flatten these hierarchies. A common parent-child hierarchy is the Country dimension hierarchy.

SAP BPC Country Hierarchy:
ALLCOUNTRY : All Countries			
AMERICAS : Americas
LTAM : Latin America
CU : Cuba
PA : Panama
NRAM : North America
CA : Canada
US : United States
APAC : Asia Pacific
GRCH : Greater China
SEAO : Southeast Asia & Oceania
BT : Bhutan
FJ : Fiji
EMEAI : Europe, Middle East, Africa & India
CEUR : Central & Eastern Europe
AL : Albania
GE : Georgia
INDI : India
MEAT : Middle East, Africa & Turkey
AF : Afghanistan
BH : Bahrain
GB : Great Britain

Will be maintained in a hierarchy table as (all columns are not shown):












































































































































































HIEID NODEID IOBJNM NODENAME PARENTID
TKW5HD9UWAGP9MMEL8JBCL6W5 1 /CPMB/L5DRD35 ALLCOUNTRY 0
TKW5HD9UWAGP9MMEL8JBCL6W5 21 /CPMB/L5DRD36 AMERICAS 1
TKW5HD9UWAGP9MMEL8JBCL6W5 22 /CPMB/L5DRD37 APAC 1
TKW5HD9UWAGP9MMEL8JBCL6W5 23 /CPMB/L5DRD38 EMEAI 1
TKW5HD9UWAGP9MMEL8JBCL6W5 27 /CPMB/L5DRD39 LTAM 21
TKW5HD9UWAGP9MMEL8JBCL6W5 28 /CPMB/L5DRD40 NRAM 21
TKW5HD9UWAGP9MMEL8JBCL6W5 79 /CPMB/L5DRD41 CU 27
TKW5HD9UWAGP9MMEL8JBCL6W5 67 /CPMB/L5DRD42 PA 27
TKW5HD9UWAGP9MMEL8JBCL6W5 107 /CPMB/L5DRD43 CA 28
TKW5HD9UWAGP9MMEL8JBCL6W5 108 /CPMB/L5DRD44 US 28
TKW5HD9UWAGP9MMEL8JBCL6W5 29 /CPMB/L5DRD45 GRCH 22
TKW5HD9UWAGP9MMEL8JBCL6W5 31 /CPMB/L5DRD46 SEAO 22
TKW5HD9UWAGP9MMEL8JBCL6W5 128 /CPMB/L5DRD49 BT 31
TKW5HD9UWAGP9MMEL8JBCL6W5 133 /CPMB/L5DRD50 FJ 31
TKW5HD9UWAGP9MMEL8JBCL6W5 32 /CPMB/L5DRD51 INDI 23
TKW5HD9UWAGP9MMEL8JBCL6W5 34 /CPMB/L5DRD52 CEUR 23
TKW5HD9UWAGP9MMEL8JBCL6W5 35 /CPMB/L5DRD53 MEAT 23
TKW5HD9UWAGP9MMEL8JBCL6W5 176 /CPMB/L5DRD55 AL 34
TKW5HD9UWAGP9MMEL8JBCL6W5 184 /CPMB/L5DRD56 GE 34
TKW5HD9UWAGP9MMEL8JBCL6W5 196 /CPMB/L5DRD58 AF 35
TKW5HD9UWAGP9MMEL8JBCL6W5 199 /CPMB/L5DRD59 BH 35
TKW5HD9UWAGP9MMEL8JBCL6W5 276 /CPMB/L5DRD60 GB 38

To flatten this SAP BPC Country hierarchy using a table function:

1. The first step is to determine the maximum number of levels our hierarchy can contain. Here, we will assume that this Country Hierarchy will go up to four levels. Define a Table Function that will return the flattened hierarchy as follows:
FUNCTION "<System>"."<Package>::<Table Function>" ()
RETURNS TABLE (NODE1 VARCHAR(32),
NODE2 VARCHAR(32),
NODE3 VARCHAR(32),
BASELEVEL VARCHAR(32))
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN
-------code explained in subsequent steps go here-------
END;

Here, NODE1, NODE 2, and NODE 3 will contain the parents at hierarchy levels 1, 2, and 3 respectively. BASELEVEL will contain the leaf nodes.

2. Select the following fields from the hierarchy table restricting to the Hierarchy you want to flatten using HIEID:
lt_hier_table = SELECT
NODEID,
NODENAME,
PARENTID
FROM <Hierarchy Table>
WHERE OBJVERS = 'A' AND
HIEID = <Hierarchy ID>;

3. Now, we need to link each node with its parent node:
lt_ini_hier = 	SELECT
LV01."NODENAME" AS NODE1,
LV02."NODENAME" AS NODE2,
LV03."NODENAME" AS NODE3,
BASELEVEL."NODENAME" AS BASELEVEL
FROM ( SELECT
"NODEID",
"NODENAME",
"PARENTID"
FROM :lt_hier_table
WHERE "NODEID" NOT IN (SELECT DISTINCT "PARENTID"
FROM :lt_hier_table)) AS BASELEVEL
LEFT OUTER JOIN :lt_hier_table LV03
ON BASELEVEL."PARENTID" = LV03."NODEID"
LEFT JOIN :lt_hier_table LV02
ON LV03."PARENTID" = LV02."NODEID"
LEFT JOIN :lt_hier_table LV01
ON LV02."PARENTID" = LV01."NODEID";

lt_ini_hier looks like:























































































NODE1 NODE2 NODE3 BASELEVEL
ALLCOUNTRY AMERICAS LTAM CU
ALLCOUNTRY AMERICAS LTAM PA
ALLCOUNTRY AMERICAS NRAM CA
ALLCOUNTRY AMERICAS NRAM US
ALLCOUNTRY APAC GRCH
ALLCOUNTRY APAC SEAO BT
ALLCOUNTRY APAC SEAO FJ
ALLCOUNTRY EMEAI CEUR AL
ALLCOUNTRY EMEAI CEUR GE
ALLCOUNTRY EMEAI INDI
ALLCOUNTRY EMEAI MEAT AF
ALLCOUNTRY EMEAI MEAT BH
ALLCOUNTRY EMEAI NEU

Which is not a very useful format. For instance, if I want a filter to show only Region EMEAI, which column do I apply a filter on? So, we need to bring all occurrences of EMEAI in a single column. In other words, each node should exist in only one column.

4. If we shift all the nodes except base nodes towards the left to fill the blanks, we will obtain a format befitting our purpose.























































































NODE1 NODE2 NODE3 BASELEVEL
ALLCOUNTRY AMERICAS LTAM CU
ALLCOUNTRY AMERICAS LTAM PA
ALLCOUNTRY AMERICAS NRAM CA
ALLCOUNTRY AMERICAS NRAM US
ALLCOUNTRY APAC GRCH
ALLCOUNTRY APAC SEAO BT
ALLCOUNTRY APAC SEAO FJ
ALLCOUNTRY EMEAI CEUR AL
ALLCOUNTRY EMEAI CEUR GE
ALLCOUNTRY EMEAI INDI
ALLCOUNTRY EMEAI MEAT AF
ALLCOUNTRY EMEAI MEAT BH
ALLCOUNTRY EMEAI NEU

Now, our filters will work alright. In order to shift the nodes to the left, we will use coalesce function.
lv_fin_hier =		SELECT
COALESCE(NODE1,
NODE2,
NODE3) AS NODE1,
CASE
WHEN NODE1 IS NOT NULL
THEN
COALESCE(NODE2,
NODE3)
WHEN NODE2 IS NOT NULL
THEN NODE3
END AS NODE2,
CASE
WHEN NODE1 IS NOT NULL AND
NODE2 IS NOT NULL
THEN NODE3
END AS NODE3,
BASELEVEL
FROM :lv_ini_hier;

RETURN :lv_fin_hier;

This table function can then be called in a calculation view to combine the hierarchy with final data which can then be used as a source for visualizations.

 

This method comes with a few limitations:

• As a table function requires a fixed return type, we can only incorporate a fixed number of hierarchy levels. It will not dynamically expand if new hierarchy levels are introduced.

• As the number of levels in the hierarchy will increase, so will the complexity of coalesce logic in step 4.

 

Conclusion:

It is a very common requirement to bring in SAP Dimension Hierarchy to Power BI. While it is possible to fetch data directly from SAP BW/BPC to Power BI, using an SAP HANA Calculation View as an intermediate step is the best approach in my opinion. I have explained a tried and tested method of pulling in SAP BW/BPC hierarchy to Power BI, with SAP HANA as an intermediary.

 

References:

Table Functions in SAP HANA - step by step guide:
https://blogs.sap.com/2018/11/05/table-functions-in-sap-hana-step-by-step-guide/

Connect to SAP HANA databases in Power BI Desktop:
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-sap-hana

 

Appendix:

Below is a generic code that can be used for the transformation of a four leveled hierarchy.
FUNCTION "<System>"."<Package>::<Table Function>" ()
RETURNS TABLE (NODE1 VARCHAR(32),
NODE2 VARCHAR(32),
NODE3 VARCHAR(32),
BASELEVEL VARCHAR(32))
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN

lt_hier_table = SELECT
NODEID,
NODENAME,
PARENTID
FROM <Hierarchy Table>
WHERE OBJVERS = 'A' AND
HIEID = <Hierarchy ID>;

lt_ini_hier = SELECT
LV01."NODENAME" AS NODE1,
LV02."NODENAME" AS NODE2,
LV03."NODENAME" AS NODE3,
BASELEVEL."NODENAME" AS BASELEVEL
FROM ( SELECT
"NODEID",
"NODENAME",
"PARENTID"
FROM :lt_hier_table
WHERE "NODEID" NOT IN (SELECT DISTINCT "PARENTID"
FROM :lt_hier_table)) AS BASELEVEL
LEFT OUTER JOIN :lt_hier_table LV03
ON BASELEVEL."PARENTID" = LV03."NODEID"
LEFT JOIN :lt_hier_table LV02
ON LV03."PARENTID" = LV02."NODEID"
LEFT JOIN :lt_hier_table LV01
ON LV02."PARENTID" = LV01."NODEID";

lv_fin_hier = SELECT
COALESCE(NODE1,
NODE2,
NODE3) AS NODE1,
CASE
WHEN NODE1 IS NOT NULL
THEN
COALESCE(NODE2,
NODE3)
WHEN NODE2 IS NOT NULL
THEN NODE3
END AS NODE2,
CASE
WHEN NODE1 IS NOT NULL AND
NODE2 IS NOT NULL
THEN NODE3
END AS NODE3,
BASELEVEL
FROM :lv_ini_hier;

RETURN :lv_fin_hier;

END;
3 Comments