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
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Flattening SAP Dimension Hierarchy using a Table Function

prateek_bagora
Explorer
‎08-11-2020 6:40 AM
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
Popular Blog Posts
Top kudoed authors
View all