Create Hierarchy from non-SAP Source

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.

For my research I used following blog-posts:

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.

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.

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.

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:

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.

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

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.