Flat Multiple Hierarchies for Single Master Data Object in SAP BW

[Former Member]

Flat Multiple Hierarchies for Single Master Data Object in SAP BW

Applies to: SAP ECC 6.0, SAP BI 7.3, SAP Business Objects BI 4.1

Author: Ajay Singh

Company: HP Enterprise Services

Created on: March 24, 2015

Author Bio:  Ajay Singh is Sr. SAP BI Consultant in HP Enterprise Services. He has over 12 years of experience defining and delivering Business Intelligence solutions. His primary area of expertise involves leading and architect data warehouse and business intelligence solutions leveraging SAP HANA, SAP BW and Business Objects.

Business Scenario:

SAP ECC provides hierarchies in the hierarchical structure for Master Data Objects (E.g. Profit Center, Fund Center) and sometimes same object has multiple hierarchies. Due to some reporting requirements multiple hierarchies need to be flattened.

Illustration:

The following steps illustrate how we can flat multiple hierarchies by using Data Store Object (DSO) in SAP BW. In this case, we are using the Info Object 0FUND_CTR for flattening its defined levels.

Pre-requisites:

We need to have pre-defined levels of the hierarchies and the Hierarchies should be uploaded and activated for the corresponding Info Object (e.g. 0FUND_CTR).

Step 1: Create Custom Info Objects for all Hierarchy Levels (Level1, Level2, Level3 etc.) as shown below for Level 1 in SAP BW:

Step 2: Create Data Store Object (DSO):

Create DSO with Key Fields and Data Fields as shown below

Step 3: Create Transformations

Now, create transformations by considering 0FUNDS_CTR as Source and Custom DSO (created in previous step) as Target Object.

Step 4: Create End Routine

In order to flatten the hierarchy write End/Start Routine.

In this case, we are using the End Routine.

METHOD end_routine.
*=== Segments ===

FIELD-SYMBOLS:
<RESULT_FIELDS>    TYPE _ty_s_TG_1.

DATA:
MONITOR_REC     TYPE rstmonitor.

*$*$ begin of routine - insert your code only below this line        *-*
... "insert your code here
*--  fill table "MONITOR" with values of structure "MONITOR_REC"
*-   to make monitor entries
... "to cancel the update process
*    raise exception type CX_RSROUT_ABORT.

DATA:
lw_funds TYPE /BI0/HFUNDS_CTR,
lw_funds1 TYPE /BI0/HFUNDS_CTR,
lw1_funds TYPE /BI0/HFUNDS_CTR,
lw_text TYPE RSTHIERNODE,
l_count type RSARECORD,

lt_funds TYPE standard table of /BI0/HFUNDS_CTR initial size 0
,
lt1_funds TYPE standard table of /BI0/HFUNDS_CTR initial size
0
,
lt_funds1 TYPE standard table of /BI0/HFUNDS_CTR initial size
0,
lt_text TYPE standard table of RSTHIERNODE initial size
0,
lw_final   TYPE _ty_s_TG_1,
lw_final1   TYPE _ty_s_TG_1,
lt_final   TYPE STANDARD TABLE OF _ty_s_TG_1,
lv_hieid   TYPE rshieid.
DATA: lv_parentid TYPE rsparent,
lv_strlen   TYPE n LENGTH 2.

Clear RESULT_PACKAGE.

IF    lt_funds[] IS INITIAL
AND lt_final[] IS INITIAL
AND lt_funds1[] IS INITIAL
AND lv_hieid IS INITIAL.


SELECT * FROM /bi0/hfunds_ctr
INTO TABLE lt1_funds
WHERE nodeid EQ 1.

If sy-subrc  = 0.

loop at lt1_funds into lw1_funds.
SELECT SINGLE hieid
FROM /bi0/hfunds_ctr
INTO lv_hieid
where nodename = lw1_funds-nodename.

IF sy-subrc NE 0.
CLEAR lv_hieid.
ELSE.
Clear lt_funds.
SELECT * FROM /bi0/hfunds_ctr
INTO TABLE lt_funds
WHERE hieid EQ lv_hieid.
IF sy-subrc EQ 0.
lt_funds1[] = lt_funds[].
SORT lt_funds1 BY nodeid.
SORT lt_funds BY iobjnm DESCENDING tlevel DESCENDING.

SELECT * FROM RSTHIERNODE
INTO TABLE lt_text.


LOOP AT lt_funds INTO lw_funds WHERE IOBJNM <>
'0HIER_NODE'.

CLEAR lv_parentid.
lv_parentid        = lw_funds-parentid.
lv_strlen          = strlen( lw_funds-nodename ).
lv_strlen          = lv_strlen - 10.
lw_final-funds_ctr = lw_funds-nodename+lv_strlen(10).
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
EXPORTING
INPUT  = lw_final-funds_ctr
IMPORTING
OUTPUT = lw_final-funds_ctr.

CLEAR lv_strlen.

DO.
READ TABLE lt_funds1 INTO lw_funds1 WITH KEY nodeid =
lv_parentid.
IF sy-subrc EQ 0.
READ TABLE lt_text INTO lw_text WITH KEY NODENAME =
lw_funds1-nodename  hieid = lv_hieid.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
EXPORTING
INPUT  = lw_text-TXTMD
IMPORTING
OUTPUT = lw_text-TXTMD.


CASE lw_funds1-tlevel.
WHEN '01'.

lw_final-/BIC/ZLEVEL1   = lw_text-TXTMD.


WHEN '02'.
lw_final-/BIC/ZLEVEL2   =
lw_text-TXTMD.


WHEN '03'.
lw_final-/BIC/ZLEVEL3   =
lw_text-TXTMD.


WHEN '04'.
lw_final-/BIC/ZLEVEL4   =
lw_text-TXTMD.


WHEN '05'.
lw_final-/BIC/ZLEVEL5   =
lw_text-TXTMD.


WHEN '06'.
lw_final-/BIC/ZLEVEL6   =
lw_text-TXTMD.

WHEN '07'.
lw_final-/BIC/ZLEVEL7   =
lw_text-TXTMD.


WHEN '08'.
lw_final-/BIC/ZLEVEL8   =
lw_text-TXTMD.

ENDCASE.

CLEAR lv_parentid.
IF lw_funds1-parentid IS NOT INITIAL.
lv_parentid = lw_funds1-parentid.
ELSE.
EXIT.
ENDIF.
ENDIF.
ENDDO.
l_count = l_count + 1.
lw_final-RECORD = l_count.
APPEND lw_final TO lt_final.
CLEAR: lw_final, lv_strlen.
ENDLOOP.
ENDIF.
ENDIF.
endloop.
ENDIF.
ENDIF.

RESULT_PACKAGE = lt_final.


CLEAR: lw_funds1, lw_funds, lw_final, lv_parentid, lv_strlen.
*$*$ end of routine - insert your code only before this line         *-*
ENDMETHOD.                    "end_routine

Step 5: Create Data Transfer Process (DTP)

Create DTP and Load the data in the Custom DSO.

Step 6: Create Relational Connections to SAP BW in SAP Business Objects Information Design Tool 4.1

• Create Relational Connection to InfoCube (Transaction Data for Fund Center)
• Create second Relational Connection to Custom DSO created in Step 2

Step 7: Create Data Foundation by adding two Connections created in Step 5.

Follow the below steps to add connections created in the Step 5

Go to File Menu -> New -> Data Foundation -> Connections -> Add Connections

Step 8: Join the DSO & InfoCube with Right Outer Join.

Go to Insert -> Insert Join -> Flag Right Outer Join

Note: Infocube is in the right side of the below screen.

Step 9: Create Business Layer based on the data foundation layer created in Step 7

Right Click on Data Foundation -> New Business Layer

Step 10: Create and Publish the Universe

Right Click on the Business Layer -> Publish to the Repository.