Summary
Retraction of master data is one of the main concerns during the implementation of SAP Business Objects Planning and Consolidation (BPC) in Net weaver Landscape. This paper presents a detailed investigation of the customized methods that can be employed while writing back BPC master data into BW Master Data (Info Object). Best practice can be determined and implemented based on data volume and other variables dependent on the customer system
Authors: Arulmurugan S, Kanchana Subbiah
Author Bio
Arulmurugan is a Consultant at Deloitte Consulting LLP with an experience of 7 years. He has specialized in SAP Business warehouse 3.5, 7.0 and 7.3 version of Net Weaver. He has worked on multiple implementation projects for various industry sector.
Kanchana is a Consultant at Deloitte Consulting LLP with an experience of 7 years. She has specialized in SAP Business Objects Planning and Consolidation, 7.5 and 10 version of Net Weaver. She has worked on multiple implementation projects for various industry sector.
Detailed Description
Retraction of data from SAP BPC to SAP BW can be achieved through write-back of data methodology using the BPC standard method by setting parameter WRITE equal to ON in the BPC script-logic. The write back can also be handled within the BADI by setting parameter WRITE to OFF. The method CL_UJR_WRITE_BACK-> WRITE_BACK_INT called in the BADI, passing the appropriate parameters serves the purpose of data write-back. The coding within the BADI can be designed to write back the data at once or in batches. Batch write-back improves performance for large volumes of data while sequential write-back works better for smaller volumes.
However in case master data load like Hierarchy we still face many challenges while retracting data from BPC to BW landscape since the hierarchy stores data in different format in both places. This document will show you how to load the master data using some customized methodologies
BW Hierarchy Master Data
A hierarchy is a method of displaying a characteristic structured and grouped according to individual evaluation criteria.
Structure of a Hierarchy
- Nodes - A hierarchy is created from nodes. The uppermost node is the root
- Roots - A hierarchy can have several roots. They do not have superior nodes
- Leaves - The leaves of a hierarchy consist of characteristic values for the basic characteristic and therefore can also have entries in the fact table. A leaf cannot have any sub nodes
Basic Characteristic - Hierarchies can be created only for those characteristics that do not reference other characteristics.Structure of Hierarchy in BW Landscape
BPC Hierarchy Master DataDimension in BPC is structured as Member (Parent) and Children relationship in the format of Hierarchy structure, the relationships includes member only, ascendants, descendants, siblings and base level. Data in BPC is always stored at the (Base) leaf node level.
Structure of a Hierarchy
- Parent Member – Specified parent at the top most of all the hierarchy, more than one hierarchy could be defined.
Parent Member - 2012.TOTAL
- Children Member – Child of the Parent specified. Children are also descendants.
Child Member - 2012.Q1, 2012.Q2, 2012.Q3, 2012.Q4
- Descendants – Child Nodes of Level1 parent member.
Descendant Member - 2012. TOTAL at the top then 2013.Q1 as Level1 child then Jan, Feb, Mar.
- Ascendants (Same level) – Member at same level, or Current Level +1
Ascendant Member – 2012.TOTAL for 2012.Q1
- Siblings - Members Sharing a Common Parent Member
Siblings Member – 2012.Q1, 2012.Q2, 2012.Q3, 2012.Q4 which share the common parent of 2012.TOTAL
- Base level ( Base cannot have any sub base children to it)
Base Member – 2012.Jan to 2012.Dec.
Structure of Hierarchy in BPC Landscape
Difference in BPC and BW Hierarchy Master DataTop level node will have always only one characteristic in BW but whereas BPC can have multiple top level node and branch out still further in tree structureImplication of this differenceWe need to rearrange the format of the BPC Hierarchy master data into BW format only then it would allow you to load the data without any errorProgram to Change the format of Master data
*&---------------------------------------------------------------------*
*& Report ZBPC_COPY_MASTER_HIERDATA*&
*&---------------------------------------------------------------------*
*&
*&*&---------------------------------------------------------------------*
REPORT ZBPC_COPY_MASTER_HIERDATA.
TYPES: BEGIN OF ty_pc_keyop,id TYPE uj_dim_member,
parent TYPE uj_dim_member,END OF ty_pc_keyop,BEGIN OF ty_member,
dim_member TYPE uj_dim_member,END OF ty_member.
TYPES: BEGIN OF s_fieldname,field(20) TYPE c,END OF s_fieldname.
TYPES : BEGIN OF GS_OUTPUT,
NODEID TYPE i,
INFOOBJECT(20) TYPE C,
NODENAME(20) TYPE C,
LINK(1) TYPE C,
PARENTID TYPE I,
LANGU(2) TYPE C,
TXTSH(20) TYPE C,
TXTMD(40) TYPE C,
TXTLG(60) TYPE C,END OF GS_OUTPUT.
TYPES : BEGIN OF GS_INPUT,id TYPE uj_dim_member,
parent TYPE uj_dim_member,END OF GS_INPUT.
DATA: lm_id TYPE uj_dim_member VALUE 'ID',
lm_RLI TYPE uj_dim_member VALUE 'RLI',
lm_profit_center TYPE uj_dim_member VALUE 'PROFIT_CENTER',
lm_func_area TYPE uj_dim_member VALUE 'FUNC_AREA'.
DATA : GT_INPUT TYPE STANDARD TABLE OF GS_INPUT,
gt_med TYPE STANDARD TABLE OF gs_input,
gt_med1 TYPE STANDARD TABLE OF gs_input,
gw_med1 TYPE gs_input,
gw_med TYPE gs_input,
GW_INPUT1 TYPE GS_INPUT,
GT_OUTPUT TYPE STANDARD TABLE OF GS_OUTPUT,
GW_OUTPUT TYPE GS_OUTPUT,
GW_OUTPUT1 TYPE GS_OUTPUT,
it_fieldname TYPE STANDARD TABLE OF s_fieldname,
wa_fieldname TYPE s_fieldname.
DATA: l_packagesize TYPE i,
lv_tabix TYPE i,
lt_attr_name TYPE uja_t_attr_name,
lt_sel TYPE uj0_t_sel,
ls_sel TYPE uj0_s_sel,
lo_dim TYPE REF TO cl_uja_dim,
lr_dim_data TYPE REF TO if_uja_dim_data,
lr_data TYPE REF TO data,
ls_data TYPE REF TO data.
DATA : LV_NODEID TYPE I,
LV_IOBJNAME TYPE STRING,
LV_DIM TYPE uj_dim_name,
lv_appset TYPE uj_appset_id,
lv_tablename TYPE string,
lv_filename TYPE STRING,
lv_extn TYPE STRING VALUE '.xls',
lv_time TYPE string.CONSTANTS: lv_path TYPE STRING VALUE 'C:\End_Output_'.
FIELD-SYMBOLS: <lt_data> TYPE STANDARD TABLE,
<ls_RLI_data> TYPE ANY,
<ls_RLI> TYPE ANY,
<gw_input> TYPE gs_input.
* Selection screen for getting dimension input from the user.
selection-screen begin of block a with frame.
parameters : p_appset type uj_appset_id obligatory default 'IPM_NA',
p_dim type uj_dim_name obligatory.
selection-screen end of block a.
* Selection screen validationat
selection-screen.
translate p_appset to upper case.
translate p_dim to upper case.
select single appset_id from uja_dimension into lv_appset
where appset_id = p_appset.if sy-subrc <> 0.
message s001(bl) display like 'E' with 'Appset - ' p_appset ' not found'.
ENDIF.
select single dimension from uja_dimension into lv_dim
where dimension = p_dim.if sy-subrc <> 0.
message s001(bl) display like 'E' with 'Dimension - ' p_dim ' not found'.
endif.
REFRESH: lt_attr_name, lt_sel.
CLEAR: ls_sel.
start-of-selection.
TRY .
CREATE OBJECT lo_dim
EXPORTING
i_appset_id = p_appset
i_dimension = p_dim.
CATCH cx_uja_admin_error .
END TRY.
lr_dim_data = lo_dim.
APPEND: 'ID' TO lt_attr_name.
TRY.
CALL METHOD lr_dim_data->read_mbr_dataEXPORTING
it_attr_list = lt_attr_name
IMPORTING
er_data = lr_data.
CATCH cx_uja_admin_error .
ENDTRY.ASSIGN lr_data->* TO <lt_data>.
gt_input[] = <lt_data>[].
CASE p_dim.WHEN 'RLI'.
MODIFY ZRLI_HIER FROM TABLE GT_INPUT.
WHEN 'TIME'.MODIFY ZTIME_HIER FROM TABLE GT_INPUT.
ENDCASE.
LOOP at gt_input ASSIGNING <gw_input>.
IF <gw_input>-parent = space.
<gw_input>-parent = 'TOP_HIER'.
ENDIF.
endloop.
gw_input1-id = 'TOP_HIER'.
gw_input1-parent = space.
append gw_input1 to gt_input.
read table gt_input into gw_input1 with key parent = space.
gw_med-parent = gw_input1-parent.
gw_med-id = gw_input1-id.
append gw_med to gt_med.
delete gt_input where parent = space.
sort gt_input by parent.
Loop at gt_med INTO gw_med.
read table gt_input into gw_input1 with key parent = gw_med-id binary search.
if sy-subrc = 0.
lv_tabix = sy-tabix.
loop at gt_input INTO gw_input1 from lv_tabix.
if gw_input1-parent <> gw_med-id.exit.endif.
gw_med1-id = gw_input1-id.
gw_med1-parent = gw_input1-parent.append gw_med1 to gt_med1.
endloop.
LOOP AT gt_med1 INTO gw_med1.
APPEND gw_med1 to gt_med.ENDLOOP.
endif.
clear : gw_med,gt_med1,gw_med1.
endloop.
CASE p_dim.WHEN 'RLI'.
lv_iobjname = 'ZSS_RLI'.WHEN 'TIME'.
lv_iobjname = 'ZSS_TIME'.
ENDCASE.
IF GT_MED[] IS NOT INITIAL.
LOOP AT GT_MED INTO GW_MED.IF SY-TABIX = 1.
LV_NODEID = LV_NODEID + 1.
GW_OUTPUT-NODEID = LV_NODEID.
GW_OUTPUT-INFOOBJECT = lv_iobjname.
GW_OUTPUT-NODENAME = GW_MED-ID.
GW_OUTPUT-LINK = ''.
GW_OUTPUT-PARENTID = 0.
GW_OUTPUT-LANGU = 'EN'.
GW_OUTPUT-TXTSH = ''.
GW_OUTPUT-TXTMD = ''.
GW_OUTPUT-TXTLG = ''.APPEND GW_OUTPUT TO GT_OUTPUT.eLSE.
LV_NODEID = LV_NODEID + 1.
GW_OUTPUT-NODEID = LV_NODEID.
GW_OUTPUT-INFOOBJECT = lv_iobjname.
GW_OUTPUT-NODENAME = GW_MED-ID.
GW_OUTPUT-LINK = ''.READ TABLE GT_OUTPUT INTO GW_OUTPUT1 WITH KEY NODENAME = GW_MED-PARENT.IF SY-SUBRC = 0.
GW_OUTPUT-PARENTID = GW_OUTPUT1-NODEID.ENDIF.
GW_OUTPUT-LANGU = 'EN'.
GW_OUTPUT-TXTSH = ''.
GW_OUTPUT-TXTMD = ''.
GW_OUTPUT-TXTLG = ''.
APPEND GW_OUTPUT TO GT_OUTPUT.
ENDIF.
CLEAR : GW_OUTPUT.
CLEAR : GW_MED.ENDLOOP.ENDIF.
lv_time = sy-timlo.CONCATENATE lv_path lv_time lv_extn INTO lv_filename.
wa_fieldname-field = 'NODEID'.APPEND wa_fieldname TO it_fieldname.
wa_fieldname-field = 'INFOOBJECT'.APPEND wa_fieldname TO it_fieldname.
wa_fieldname-field = 'NODENAME'.APPEND wa_fieldname TO it_fieldname.
wa_fieldname-field = 'LINK'.APPEND wa_fieldname TO it_fieldname.
wa_fieldname-field = 'PARENTID'.APPEND wa_fieldname TO it_fieldname.
wa_fieldname-field = 'LANGU'.APPEND wa_fieldname TO it_fieldname.
wa_fieldname-field = 'TXTSH'.APPEND wa_fieldname TO it_fieldname.
wa_fieldname-field = 'TXTMD'.APPEND wa_fieldname TO it_fieldname.
wa_fieldname-field = 'TXTLG'.APPEND wa_fieldname TO it_fieldname.
CALL FUNCTION 'GUI_DOWNLOAD'EXPORTING
FILENAME = lv_filename*
FILETYPE = ''
WRITE_FIELD_SEPARATOR = 'X'TABLES
DATA_TAB = GT_OUTPUT
FIELDNAMES = it_fieldname.IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO*
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.ENDIF.
WRITE : 'Hierarchies loaded successfully'.
How to modify above code according to your requirement CASE p_dim.WHEN 'RLI'.
lv_iobjname = 'ZSS_RLI'.WHEN 'TIME'.
lv_iobjname = 'ZSS_TIME'.
ENDCASE.
Please make changes to this case statement to accommodate all property in BPC are covered
CASE p_dim.
WHEN 'BPCMASTERDATA'.
lv_iobjname = 'CORRESPONDINGBWINFOOBJECT'.
ENDCASE.
Steps
- Create a program in SE38
- Execute the program
- In Selection screen give the BPC Master data name to be loaded to BW Info object
- Hierarchy loaded successfully from BPC Master data into BW Info object
- BPC MASTER DATA - Entity
- BW Info Object – ZSS_RLI
How to modify this program according to your requirement
1) Change the Appset Name in selection screen option
* Selection screen for getting dimension input from the user.
selection-screen begin of block a with frame.
parameters : p_appset type uj_appset_id obligatory default 'Appsetname',
p_dim type uj_dim_name obligatory.
selection-screen end of block a.
2) Include the BPC Dimension name and its corresponding BW info object name inside the case statement in the program
CASE p_dim.
WHEN 'RLI'. – BPC MASTER DATA DIMENSION NAME
lv_iobjname = 'ZSS_RLI'. - CORRESPONDING BW INFOOBJECT NAME
WHEN 'TIME'.
lv_iobjname = 'ZSS_TIME'.
ENDCASE.
Related Content
Help documentation for SAP BPC 7.5 NW can be found in the following location:
- SAP Managed Tags:
1 Comment
