Freelancers
Cancel
 Sign In
Join Now

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:

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, 8)          , ''    , ''      , '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.

Tags: SAP BW/4HANA
Sara Sampaio

Sara Sampaio

Author Since: March 10, 2022

0 0 votes
Article Rating
Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x