Skip to Content
Technical Articles

Flattening SAP Dimension Hierarchy using a Table Function

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;
2 Comments
You must be Logged on to comment or reply to a post.
  • You seriously should check out hierarchy functions in HANA.

    The approach of joining a table n-times to resolve n-levels of the hierarchy is neither efficient nor elegant. It’s the thing you do when you don’t have any other way and you’re happy with always using exactly n-levels.

    In many other DBMS you would use recursive common table expressions for this but with HANA you actually get a specific set of hierarchy functions that are highly efficient to deal with hierarchical data.