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

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:

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.

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;