Create Hierarchy from non-SAP Source

[rogerheckly]

Since BW 7.3 the hierarchy frame work allows to load hierarchy data from any datasource - also from a non-hierarchy datasource.

In this post, I'll walk through the process of loading a hierarchy from a SQL database table via a master data attributes datasource. The hierarchy to be created consists of 3 levels.

Source material

For my research I used following blog-posts:

• Create hierarchy using any source


• Loading Hierarchies from Flat File using the new DataSource and Transformation Technology


• Custom Hierarchy Extraction in SAP BW 7.3 (Part3)


• How to load and generate a custom hierarchy in SAP Business Warehouse (SAP BW)


• Creating Custom Hierarchies in BW 7.5


• Hierarchy Issues


• Help SAP - Examples of a Hierarchy Transformation

Introduction

In my BW 7.5 I loaded the hierarchies via Flat Files (generated by SQL Procedure), but with BW/4HANA I now spend time to check how to load the data direct from the SQL table instead of Flat Files. Above sources helped me, to figure out finally how it works, but none of them had all information in one place. So hopefully this blog post helps to understand how this works.

Hierarchy

I'm using SharePoint for the data entry by the user and load the SharePoint List into a specific SQL table. This SQL Database is connected to SAP BW via a "SAP HANA Smart Data Access" Source System. I don't explain to setup such a system connection.

The new Hierarchy Framework consists on 6 target segments:

1. Hierarchy Header


2. Hierarchy Header Texts


3. Node


4. Node Texts


5. Node Ranges


6. Hierarchy Levels

My hierarchies are not time dependent, nor version dependent and I do not load intervals. Also I'm loading all values in Language "English" - and each value is unique (means no links used). Also in my example hierarchy there are no external Info Objects - therefore this is out of scope for this documentation.

Because of only loading "English" I have one record for Segment ID 1 and 2, and for each node I have one record for Segment ID 3 and 4.

SQL Table

My table in SQL Server has following fields:

 

#	Column	Data Type	Comment	Seg 1	Seg 2	Seg 3	Seg 4	Seg 5	Seg 6
1	H_HIEID	varchar(100)	This is the unique Hierarchy ID, all records in the table of a specific hierarchy has the same value. Used to load multiple hierarchies.	X	X	X	X	X	X
2	H_HIENM	varchar(100)	Technical name of the hierarchy	(1)
3	SEGMENT_ID	varchar(10)	To identify the different record types in the transformation (expert routine)
4	H_NODEID	varchar(8)	A unique number for each Segment / Record			X
5	H_IOBJNM	varchar(30)	0HIER_NODE for text nodes Technical IOBJ name			(13)
6	H_HIERNODE	varchar(32)	if H_IOBJNM = "0HIER_NODE" then the technical name of the specific text node			(11)	(11)
7	H_NORESTNO	varchar(1)	Flag for Hierarchy Attribute "Suppress 'Unassigned' Node 0 = not checked X = checked	(7)
8	H_STARTLEV	varchar(2)	Value for Hierarchy Attribute "Drilldown Start Level" '' = 00 '01 = Level 01	(9)
9	H_NODEPOS	varchar(1)	Value for Hierarchy Attribute "Root/Totals Item Appears Above / Below" 0 = appears above 1 = appears below	(8)
10	H_LEAFNOD	varchar(1)	Flag for Hierarchy Attribute "Do not Display Leaves for Inner-Nodes in the Query" 0 = not checked X = checked	(5)
11	H_ALEAFNOC	varchar(1)	Flag for Hierarchy Attribute "Display Behavior for Leaves of Internal Nodes Not Changeable" 0 = not checked X = checked	(6)
12	H_LINK	varchar(1)	If a unique value is used multiple times in the hierarchy - out of scope in this blog post			X
13	H_PARENTID	varchar(8)	Parent H_NODEID of a Node			X
14	H_TLEVEL	varchar(8)	Level of the Node			X			X
15	H_CHILDID	varchar(8)	Next below Child H_NODEID			X
16	H_NEXTID	varchar(8)	On the same Level the next H_NODEID			X
17	LANGU	varchar(1)	Language of texts		X		X		X
18	TXTSH	varchar(20)	Short Text		(2)		(12)		(10)
19	TXTMD	varchar(40)	Medium Text		(3)		(12)		(10)
20	TXTLG	varchar(60)	Long Text		(4)		(12)		(10)
21	IOBJNM_VALUE	varchar(100)	Key value of the info object			(11)

 

Which values (x) fill which fields (x) in the Hierarchy:

The table in the SQL database is filled via a Stored Procedure which contains the logic to fill all the fields like ParentId, ChildId, NextId, etc. - the Stored Procedure is attached at the end.

Datasource

Create a Master Data - Attribute based datasource in the Smart Data Access Source System:

 

I've created a view which points to the SQL table which is the source for the data source:

 

The field "H_HIEID" is selectable, so I can enter a value in the DTP to load a specific hierarchy:

 

Transformation

I decided to go for an "Expert Routine" based transformation. The reason is the following: in the classical transformation you can only load the 6 segment result packages in the end routine, but then you cannot access the source package information "Segment ID" to have the indicator what is the record about. Before the expert routine I've created a logic based on the Node Id - I started each segment with the corresponding number - e.g. 10000001 for the header node, 30000001 for the nodes. With that I was able to identify in the end routine what kind of record it is.

But with the expert routine I have access to the source field "Segment_ID" and therefore I can assign the records to the specific result packages.

Here the code to map the source fields to the target fields:

    IF sy-batch IS INITIAL.

        BREAK-POINT.

    ENDIF.



*    1 Hierarchy Header

*    2 Header Texts

*    3 Nodes

*    4 Node Texts

*    5 Ranges

*    6 Level Texts

    FIELD-SYMBOLS: <source_package> type _ty_s_sc_1

                 .

    LOOP AT SOURCE_PACKAGE assigning <source_fields>.

        clear: result_fields_1, result_fields_2, result_fields_3, result_fields_4, result_fields_6.

        case <source_fields>-segment_id.

            when '1'.

                "1 = Header

                result_fields_1-objectid       = <source_fields>-h_hieid.

                result_fields_1-h_hienm        = <source_fields>-h_hienm.

                result_fields_1-h_norestno     = <source_fields>-h_norestno.

                result_fields_1-h_startlev     = <source_fields>-h_startlev.

                result_fields_1-h_nodepos      = <source_fields>-h_nodepos.

                result_fields_1-h_leafnod      = <source_fields>-h_leafnod.

                result_fields_1-h_aleafnoc     = <source_fields>-h_aleafnoc.                

                append result_fields_1 to result_package_1.



                "2 = Header Texts

                result_fields_2-objectid       = <source_fields>-h_hieid.

                result_fields_2-langu          = <source_fields>-langu.

                result_fields_2-txtsh          = <source_fields>-txtsh.

                result_fields_2-txtmd          = <source_fields>-txtmd.

                result_fields_2-txtlg          = <source_fields>-txtlg.

                append result_fields_2 to result_package_2.

            when '3'.

                "3 = Nodes

                result_fields_3-objectid       = <source_fields>-h_hieid.

                result_fields_3-h_nodeid       = <source_fields>-h_nodeid.

                result_fields_3-h_iobjnm       = <source_fields>-h_iobjnm.

                result_fields_3-h_parentid     = <source_fields>-h_parentid.

                result_fields_3-h_childid      = <source_fields>-h_childid.

                result_fields_3-h_nextid       = <source_fields>-h_nextid.

                result_fields_3-h_tlevel       = <source_fields>-h_tlevel.

                result_fields_3-h_link         = <source_fields>-h_link.

                result_fields_3-h_hiernode     = <source_fields>-h_hiernode.

                result_fields_3-/bic/zco_khinr = <source_fields>-iobjnm_value.

                append result_fields_3 to result_package_3.



                "4 = Nodes Texts

                result_fields_4-objectid       = <source_fields>-h_hieid.

                result_fields_4-langu          = <source_fields>-langu.

                result_fields_4-h_hiernode     = <source_fields>-h_hiernode.

                result_fields_4-txtsh          = <source_fields>-txtsh.

                result_fields_4-txtmd          = <source_fields>-txtmd.

                result_fields_4-txtlg          = <source_fields>-txtlg.

                append result_fields_4 to result_package_4.

            when '5'.

            when '6'.

                "Levels

                result_fields_6-objectid       = <source_fields>-h_hieid.

                result_fields_6-langu          = <source_fields>-langu.

                result_fields_6-h_tlevel       = <source_fields>-h_tlevel.

                result_fields_6-txtsh          = <source_fields>-txtsh.

                result_fields_6-txtmd          = <source_fields>-txtmd.

                result_fields_6-txtlg          = <source_fields>-txtlg.

                append result_fields_6 to result_package_6.



         endcase.

    endloop.

 

DTP

In the DTP I just enter the filter criteria for the H_HIEID to load a specific hierarchy.

 

SQL Stored Procedure

This stored procedure fills the SQL table with the hierarchy fields. The source is a SharePoint List which contains the needed information to create the hierarchy.

In here you can see how the fields ParentId, ChildId, NextId etc. are filled.

This examples is fixed to three levels only. I'm working on a stored procedure which is more generic and could handle as many levels as needed.

CREATE PROC [dbo].[P_CO_MD_DUMMY_HIER]



AS

BEGIN

SET NOCOUNT ON





DECLARE @HIERID   VARCHAR(100)

DECLARE @HIERNAME VARCHAR(100)

DECLARE @HIERNAME_TXTSH VARCHAR(20)

DECLARE @HIERNAME_TXTMD VARCHAR(40)

DECLARE @HIERNAME_TXTLG VARCHAR(60)



DECLARE @NODEID   BIGINT

DECLARE @PREVIOUS_L1 BIGINT

DECLARE @PREVIOUS_L2 BIGINT

DECLARE @PREVIOUS_L3 BIGINT



DECLARE @CHILD_L1 BIGINT

DECLARE @CHILD_L2 BIGINT

DECLARE @CHILD_L3 BIGINT



DECLARE @PARENTID_L1 BIGINT

DECLARE @PARENTID_L2 BIGINT

DECLARE @LEVEL    INT



DECLARE @DUMMY_NODE VARCHAR(50)

DECLARE @DUMMY_NODE_TEXT VARCHAR(50)

DECLARE @DUMMY_NODE_SORTKEY VARCHAR(50)



DECLARE @ZCOA00003 VARCHAR(50)

DECLARE @ZCOA00003_TXTMD VARCHAR(40)

DECLARE @ZCOA00003_SORTKEY VARCHAR(50)



DECLARE @DUMMY VARCHAR(12)

DECLARE @DUMMY_TXTMD VARCHAR(40)



SET @HIERID = '1'

SET @HIERNAME       = 'H_HIENM'

SET @HIERNAME_TXTSH = 'H_HIENM TXTSH'

SET @HIERNAME_TXTMD = 'H_HIENM TXTMD'

SET @HIERNAME_TXTLG = 'H_HIENM TXTLG'



--Segment ID = 1 = Hierarchy Header

--Segment ID = 2 = Hierarchy Header Texts

--Segment ID = 3 = Node Attributes

--Segment ID = 4 = Node Texts

--Segment ID = 5 = 

--Segment ID = 6 = Hierarchy Levels



DELETE FROM T_CA_MD_HIERARCHIES WHERE H_HIEID = @HIERID



SET @NODEID   = 1

INSERT INTO T_CA_MD_HIERARCHIES (H_HIEID, H_HIENM, SEGMENT_ID,  H_NODEID, H_IOBJNM, H_HIERNODE, H_NORESTNO, H_STARTLEV, H_NODEPOS, H_LEAFNOD, H_ALEAFNOC, H_LINK, H_PARENTID, H_TLEVEL, H_CHILDID, H_NEXTID, LANGU, TXTSH, TXTMD, TXTLG, IOBJNM_VALUE)

SELECT @HIERID, @HIERNAME, '1', COMMON.dbo.F_ALPHA(@NODEID, 😎          , ''    , ''      , '0'     , '01'    , '0'    , '0'    , '0'     , ''  , ''      , ''     , ''   , '', 'E', @HIERNAME_TXTSH, @HIERNAME_TXTMD, @HIERNAME_TXTLG, ''



--Segment ID = 6 = Hierarchy Levels

SET @NODEID   = 1

INSERT INTO T_CA_MD_HIERARCHIES (H_HIEID, H_HIENM, SEGMENT_ID,  H_NODEID, H_IOBJNM, H_HIERNODE, H_NORESTNO, H_STARTLEV, H_NODEPOS, H_LEAFNOD, H_ALEAFNOC, H_LINK, H_PARENTID, H_TLEVEL, H_CHILDID, H_NEXTID, LANGU, TXTSH, TXTMD, TXTLG, IOBJNM_VALUE)

SELECT @HIERID, @HIERNAME, '6', COMMON.dbo.F_ALPHA(@NODEID, 8), '', '', '', '', '', '', '', '', '', '1', '', '', 'E', 'Main', 'Main', 'Main', ''

SET @NODEID   = @NODEID + 1

INSERT INTO T_CA_MD_HIERARCHIES (H_HIEID, H_HIENM, SEGMENT_ID,  H_NODEID, H_IOBJNM, H_HIERNODE, H_NORESTNO, H_STARTLEV, H_NODEPOS, H_LEAFNOD, H_ALEAFNOC, H_LINK, H_PARENTID, H_TLEVEL, H_CHILDID, H_NEXTID, LANGU, TXTSH, TXTMD, TXTLG, IOBJNM_VALUE)

SELECT @HIERID, @HIERNAME, '6', COMMON.dbo.F_ALPHA(@NODEID, 8), '', '', '', '', '', '', '', '', '', '2', '', '', 'E', 'Sub', 'Sub', 'Sub', ''

SET @NODEID   = @NODEID + 1

INSERT INTO T_CA_MD_HIERARCHIES (H_HIEID, H_HIENM, SEGMENT_ID,  H_NODEID, H_IOBJNM, H_HIERNODE, H_NORESTNO, H_STARTLEV, H_NODEPOS, H_LEAFNOD, H_ALEAFNOC, H_LINK, H_PARENTID, H_TLEVEL, H_CHILDID, H_NEXTID, LANGU, TXTSH, TXTMD, TXTLG, IOBJNM_VALUE)

SELECT @HIERID, @HIERNAME, '6', COMMON.dbo.F_ALPHA(@NODEID, 8), '', '', '', '', '', '', '', '', '', '3', '', '', 'E', 'Level 3 TXTSH', 'Level 3 TXTMD', 'LEVEL 3 TXTLG', ''



SET @CHILD_L1 = ''

SET @CHILD_L2 = ''

SET @CHILD_L3 = ''



SET @NODEID   = 1

					SET @PREVIOUS_L1 = ''

					DECLARE cMAINNODE CURSOR FOR

					SELECT DISTINCT DUMMY_NODE, DUMMY_NODE_TEXT, DUMMY_NODE_SORTKEY

					FROM T_CO_MD_DUMMY_ATTR

					WHERE DUMMY_NODE <> ''

					ORDER BY DUMMY_NODE_SORTKEY



					OPEN cMAINNODE

					FETCH NEXT FROM cMAINNODE

					INTO @DUMMY_NODE, @DUMMY_NODE_TEXT, @DUMMY_NODE_SORTKEY

					WHILE (@@fetch_status=0) BEGIN

						SET @LEVEL = 1

						INSERT INTO T_CA_MD_HIERARCHIES (H_HIEID, H_HIENM, SEGMENT_ID,  H_NODEID, H_IOBJNM, H_HIERNODE, H_NORESTNO, H_STARTLEV, H_NODEPOS, H_LEAFNOD, H_ALEAFNOC, H_LINK, H_PARENTID, H_TLEVEL, H_CHILDID, H_NEXTID, LANGU, TXTSH, TXTMD, TXTLG, IOBJNM_VALUE)

						SELECT @HIERID, @HIERNAME, '3', COMMON.dbo.F_ALPHA(@NODEID, 8), '0HIER_NODE' , @DUMMY_NODE, '', '', '', '', '', '', '', CONVERT(VARCHAR(8), @LEVEL), '', '', 'E', LEFT(@DUMMY_NODE_TEXT, 20), @DUMMY_NODE_TEXT, @DUMMY_NODE_TEXT, ''

						SET @PARENTID_L1 = @NODEID

						if @PREVIOUS_L1 <> '' begin

							update T_CA_MD_HIERARCHIES SET H_NEXTID = @NODEID WHERE H_NODEID = @PREVIOUS_L1 AND H_HIEID = @HIERID

						end

						set @PREVIOUS_L1 = @NODEID

						SET @NODEID = @NODEID + 1



						SET @PREVIOUS_L2 = ''

						DECLARE cSUBNODE CURSOR FOR

						SELECT ZCOA00003, ZCOA00003_TXTMD, ZCOA00003_SORTKEY

						  FROM T_CO_MD_DUMMY_ATTR

						 WHERE DUMMY_NODE = @DUMMY_NODE

						 GROUP BY ZCOA00003, ZCOA00003_TXTMD, ZCOA00003_SORTKEY

						 ORDER BY ZCOA00003_SORTKEY, ZCOA00003



						OPEN cSUBNODE

						FETCH NEXT FROM cSUBNODE

						INTO @ZCOA00003, @ZCOA00003_TXTMD, @ZCOA00003_SORTKEY

						WHILE (@@fetch_status=0) BEGIN

							SET @LEVEL = 2

							INSERT INTO T_CA_MD_HIERARCHIES (H_HIEID, H_HIENM, SEGMENT_ID,  H_NODEID, H_IOBJNM, H_HIERNODE, H_NORESTNO, H_STARTLEV, H_NODEPOS, H_LEAFNOD, H_ALEAFNOC, H_LINK, H_PARENTID, H_TLEVEL, H_CHILDID, H_NEXTID, LANGU, TXTSH, TXTMD, TXTLG, IOBJNM_VALUE)

							SELECT @HIERID, @HIERNAME, '3', COMMON.dbo.F_ALPHA(@NODEID, 8), '0HIER_NODE', @ZCOA00003, '', '', '', '', '', '', CONVERT(VARCHAR(8), @PARENTID_L1), CONVERT(VARCHAR(8), @LEVEL), '', '', 'E', LEFT(@ZCOA00003_TXTMD, 20), @ZCOA00003_TXTMD, @ZCOA00003_TXTMD, ''

							SET @PARENTID_L2 = @NODEID

							if @PREVIOUS_L2 <> '' begin

								update T_CA_MD_HIERARCHIES SET H_NEXTID = @NODEID WHERE H_NODEID = @PREVIOUS_L2 AND H_HIEID = @HIERID

							end

							if @CHILD_L2 = '' BEGIN

								update T_CA_MD_HIERARCHIES SET H_CHILDID = @NODEID WHERE H_NODEID = @PREVIOUS_L1 AND H_HIEID = @HIERID

								SET @CHILD_L2 = @NODEID

						    end

							set @PREVIOUS_L2 = @NODEID

							SET @NODEID = @NODEID + 1



							--LEVEL 3

							SET @PREVIOUS_L3 = ''

							DECLARE cDUMMY CURSOR FOR

							SELECT DISTINCT DUMMY, DUMMY_TXTMD

							  FROM T_CO_MD_DUMMY_ATTR 

							 WHERE DUMMY_NODE = @DUMMY_NODE

							   AND ZCOA00003 = @ZCOA00003

						     ORDER BY DUMMY



							OPEN cDUMMY

							FETCH NEXT FROM cDUMMY

							INTO @DUMMY, @DUMMY_TXTMD

							WHILE (@@fetch_status=0) BEGIN

								SET @LEVEL = 3

								INSERT INTO T_CA_MD_HIERARCHIES (H_HIEID, H_HIENM, SEGMENT_ID,  H_NODEID, H_IOBJNM, H_HIERNODE, H_NORESTNO, H_STARTLEV, H_NODEPOS, H_LEAFNOD, H_ALEAFNOC, H_LINK, H_PARENTID, H_TLEVEL, H_CHILDID, H_NEXTID, LANGU, TXTSH, TXTMD, TXTLG, IOBJNM_VALUE)

								SELECT @HIERID, @HIERNAME, '3', COMMON.dbo.F_ALPHA(@NODEID, 8), 'DUMMY', '', '', '', '', '', '', '', CONVERT(VARCHAR(8), @PARENTID_L2), CONVERT(VARCHAR(8), @LEVEL), '', '', 'E', LEFT(@DUMMY_TXTMD, 20), @DUMMY_TXTMD, @DUMMY_TXTMD, @DUMMY

								if @PREVIOUS_L3 <> '' begin

									update T_CA_MD_HIERARCHIES SET H_NEXTID = @NODEID WHERE H_NODEID = @PREVIOUS_L3 AND H_HIEID = @HIERID

								end

								if @CHILD_L3 = '' BEGIN

									update T_CA_MD_HIERARCHIES SET H_CHILDID = @NODEID WHERE H_NODEID = @PREVIOUS_L2 AND H_HIEID = @HIERID

									SET @CHILD_L3 = @NODEID

								end

								set @PREVIOUS_L3 = @NODEID

								SET @NODEID = @NODEID + 1

							FETCH NEXT FROM cDUMMY

							INTO @DUMMY, @DUMMY_TXTMD

							END

							CLOSE cDUMMY

							DEALLOCATE cDUMMY

							SET @CHILD_L3 = ''



						FETCH NEXT FROM cSUBNODE

						INTO @ZCOA00003, @ZCOA00003_TXTMD, @ZCOA00003_SORTKEY

						END

						CLOSE cSUBNODE

						DEALLOCATE cSUBNODE

						SET @CHILD_L2 = ''





					FETCH NEXT FROM cMAINNODE

					INTO  @DUMMY_NODE, @DUMMY_NODE_TEXT, @DUMMY_NODE_SORTKEY

					END

					CLOSE cMAINNODE

					DEALLOCATE cMAINNODE

					SET @CHILD_L1 = ''



END

 

 

Conclusions :

This Article helps you to understand how you can load hierarchies from nonSAP System, what the fields are for and how you have to fill the different hierarchy segments.

Please share your comments and Like if you interested on this articles.