In this blog, we will understand the approach of utilizing SAP Hierarchies in Visualization tools like Power BI, Tableau, and so on. To make use of the SAP Dimension Hierarchies, maintained in a parent-child structure for Visualizations, we 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
These 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 the SAP BPC Country Hierarchy using table function
- Step 1 – The first step is to determine the maximum number of levels our hierarchy can contain. Here, we will assume that the 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, NODE2, and NODE3 will contain the parents at hierarchy levels 1,2, and 3 respectively . BASELEVEL will contain the leaf nodes.
- Step 2 – Select the following fields from the hierarchy table restricting to the Hierarchy to flatten using HIEID:
lt_hier_table = SELECT
NODEID,
NODENAME,
PARENTID
FROM <Hierarchy Table>
WHERE OBJVERS = ‘A’ AND
HIEID = <Hierarchy ID>;
- Step 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”;
It_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 |
It is not very useful format. For instance, if we require a filter to show only Region: EMEAI, which column should we 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.
- Step 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. 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;
The 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.
Conclusion:
It is a 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 one of the best approaches. We have seen a tried and tested method of pulling in SAP BW/BPC hierarchy to Power BI, with SAP HANA as an intermediary.
However, 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 increases, so will the complexity of coalesce logic in Step 4.
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;
