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:

/wp-content/uploads/2015/03/1_671112.png

Step 2: Create Data Store Object (DSO):

Create DSO with Key Fields and Data Fields as shown below

/wp-content/uploads/2015/03/2_671117.png

Step 3: Create Transformations

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

/wp-content/uploads/2015/03/3_671119.png

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 sysubrc  = 0.

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

IF sysubrc NE 0.
CLEAR lv_hieid.
ELSE.
Clear lt_funds.
SELECT * FROM /bi0/hfunds_ctr
INTO TABLE lt_funds
WHERE hieid EQ lv_hieid.
IF sysubrc 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_fundsparentid.
lv_strlen         
= strlen( lw_fundsnodename ).
lv_strlen         
= lv_strlen 10.
lw_final
funds_ctr = lw_fundsnodename+lv_strlen(10).
CALL FUNCTION ‘CONVERSION_EXIT_ALPHA_OUTPUT’
EXPORTING
INPUT  = lw_finalfunds_ctr
IMPORTING
OUTPUT = lw_finalfunds_ctr.

CLEAR lv_strlen.

DO.
READ TABLE lt_funds1 INTO lw_funds1 WITH KEY nodeid =
lv_parentid
.
IF sysubrc 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_textTXTMD
IMPORTING
OUTPUT = lw_textTXTMD.

CASE lw_funds1tlevel.
WHEN ’01’.

lw_final/BIC/ZLEVEL1   = lw_textTXTMD.

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_funds1parentid IS NOT INITIAL.
lv_parentid
= lw_funds1parentid.
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.

/wp-content/uploads/2015/03/5_671123.png

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

/wp-content/uploads/2015/03/6_671124.png

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


/wp-content/uploads/2015/03/7_671125.png

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.


/wp-content/uploads/2015/03/8_671126.png

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.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply