Leonard Tan

July 26, 2016 22 minute read

Re-Designing SAP BW with the Right Information!

Recently, I have shared some important keys on the subjects of Master Data Redesign in BW. It’s more of a conceptual and informative blog which I hope that it would give you some ideas on how the redesign of Master Data should be done. In this blog, let’s get to the ground and get our hands dirty on some useful ABAP programs that I have created and personally benefited tremendously in getting the insights of existing data flow in SAP Business Warehouse (BW).

Back then when I first started my career as a junior consultant, I was first involved in a SAP BW Performance Enhancement Project. Initially, I thought it would be a rather challenging task where I will need to understand what system parameters are or doing necessary system tuning to enhance the performance of the system. To my surprise, none of the aforementioned challenges were part of my task, but was told to document all the existing data flow and the BEx queries instead. It was a really painful experience, as I will need to go through one after another existing data flow (InfoProviders, Transformations and DTPs) and BEx queries manually to get the right information documented. Since then, I realized documentation plays a super vital role when it comes to any redesign, enhancement or support work for SAP BW.

Fast forward to this year, I’m involved in a on-going SAP BW project and thought it would be really beneficial to have a simple ABAP program in my pocket that can help me in getting the insight of any existing BW data flows quickly and allow me to have the right information at a glance (without clicking through the InfoProviders / Transformation forth and back). I started working on 2 simple ABAP programs (which I’m going to share with you below) and since then using these programs to assist me in making the right informed decision on how I should proceed with the Redesign / Enhancement or determine whether the new requirement can be catered by the existing data flow.

The following ABAP programs are created with the intention to help you in getting the insights of any existing data flow(s) and you may use this for your documentation as well:

ZBW_LIST_IO_BY_INFOPROV
1. This Program will list the InfoObjects by InfoProviders
ZBW_LIST_TRANSF_MAPPING
1. This Program will list the transformation mapping between Source and Target

*Note:

(1) The intention of splitting the program and instead of combining will give you greater flexibility when it comes to documenting multiple InfoProviders / Transformation.

(2) Tested on BW 7.4.

A. Program(1) – ZBW_LIST_IO_BY_INFOPROV

Overview: This Program will list the InfoObjects by MultiProviders / InfoCube / DSO.

How does it work?

1. Execute the Program in SE38 (source code will be provided in the appendix)

2. Insert the InfoProvider’s technical name and select the type of InfoProvider, then execute (F8) the program.

**Note: If you are listing an SPO, remember to put “00” at the end of the technical name. For example, for spo ZSPO01, the technical name should be “ZSP0100”.

3. For MultiProviders / Infocube, result will be generated to list the InfoObjects by Dimension:

4. For DSO, result will be generated to list the InfoObjects by key fields and data fields:

5. You can then export this list and compile all the InfoProviders in an excel file, enabling you with the ability to search InfoObjects by InfoProviders (without over-relying on the where-used-list in BW)!

Tips:To make a more informed technical decision, most of the time, you will need to understand and know how each InfoObjects are populated in the InfoProviders as well. To obtain that information, you may proceed to 2^nd ABAP Program, ZBW_LIST_TRANSF_MAPPING, to get the mapping from the source to the target.

B. Program(2) – ZBW_LIST_TRANSF_MAPPING

Overview: This Program will list the Transformation mapping by the Transformation Target or the Transformation ID.

How does it work?

1. Execute the Program in SE38 (source code will be provided in the appendix)

2. Insert either the Transformation Target’s (DSO / InfoProviders / SPO / InfoSource) technical name and execute (F8) the program.

*Note: For SPO InfoProviders, put the Incoming InfoSource as the Transformation Target. For example, for spo ZSPO01, put incoming InfoSource, ZSP01_I as the Transformation Target instead.

3. Result will be generated to list the Source and Target with the mapping Rule by Transformation Target and Transformation ID:

4. You can then export this to combine it with the listing of InfoObjects by InfoProviders using simple vlookup in excel, which will give you the entire mapping of InfoObjects by InfoProviders to its source without needing to go forth and back on different Transformation or InfoProviders.

With this, you would be able to know how a particular InfoObject is populated in an InfoProvider and subsequently helps you in making the right decision in:

a. Redesigning the relevant InfoProviders or data flow

b. Determining how to enhance an existing data flow or whether a new data flow will need to be developed.

Hope it helps:) If you are interested in Lumira as well, may have a look at a blog that I have published previously:

Visualizing Air Pollutant Index (API) with Lumira and Import.io

Appendix (1) – ZBW_LIST_IO_BY_INFOPROV

REPORT ZBW_LIST_IO_BY_INFOPROV.

DATA: INP TYPE C LENGTH 10.
SELECT-OPTIONS: o_INP FOR INP.

PARAMETER : CUBE RADIOBUTTON GROUP GR1,
DSO RADIOBUTTON GROUP GR1.

TYPES: BEGIN OF ty_tab_dso,
                DSO_N TYPE RSDODSOBJECT,
               DSO_T TYPE RSTXTLG,
               IO_N TYPE RSIOBJNM,
               IO_T TYPE RSTXTSH,
               POSIT TYPE RSPOSIT,
               KEYFLAG TYPE KEYFLAG,
               OBJVERS TYPE RSOBJVERS,
               OBJVERS_2 TYPE RSOBJVERS,
               OBJVERS_3 TYPE RSOBJVERS,
               LANGU_D TYPE LANGU,
               LANGU_I TYPE LANGU,
END OF ty_tab_dso.

TYPES: BEGIN OF ty_tab_dso_disp,
               DSO_N TYPE RSDODSOBJECT,
               DSO_T TYPE RSTXTLG,
               IO_N_K TYPE RSIOBJNM,
               IO_N TYPE RSIOBJNM,
               IO_T TYPE RSTXTSH,
END OF ty_tab_dso_disp.

TYPES: BEGIN OF ty_tab_cube,
               IC_N TYPE RSINFOCUBE,
               IC_T TYPE RSTXTSH,
               ICD_N TYPE RSDIMENSION,
               ICD_T TYPE RSTXTSH,
               IO_N TYPE RSIOBJNM,
               IO_T TYPE RSTXTSH,
               POSIT TYPE RSPOSIT,
END OF ty_tab_cube.

TYPES: BEGIN OF ty_tab_cube_disp,
               IC_N TYPE RSINFOCUBE,
               IC_T TYPE RSTXTSH,
               ICD_T TYPE RSTXTSH,
               IO_N TYPE RSIOBJNM,
               IO_T TYPE RSTXTSH,
END OF ty_tab_cube_disp.

DATA: lt_tab_dso TYPE STANDARD TABLE OF ty_tab_dso,
wa_lt_tab_dso LIKE LINE OF lt_tab_dso,

lt_tab_dso_disp TYPE STANDARD TABLE OF ty_tab_dso_disp,
wa_lt_tab_dso_disp LIKE LINE OF lt_tab_dso_disp,

            lt_tab_cube TYPE STANDARD TABLE OF ty_tab_cube,
            lt_tab_cube_tmp TYPE STANDARD TABLE OF ty_tab_cube,
            wa_lt_tab_cube LIKE LINE OF lt_tab_cube,

lt_tab_cube_disp TYPE STANDARD TABLE OF ty_tab_cube_disp,
wa_lt_tab_cube_disp LIKE LINE OF lt_tab_cube_disp.

FIELD-SYMBOLS: <wa_lt_tab_cube> LIKE LINE OF lt_tab_cube.

* For ALV Table
DATA: it_fieldcat TYPE slis_t_fieldcat_alv,
wa_fieldcat TYPE slis_fieldcat_alv,

seltext_m TYPE SCRTEXT_M.

*&———————————————————————*
*&      MAIN
*&———————————————————————*
*       Retrieve List of IO by DSO
*———————————————————————-*
IF DSO = ‘X’.
      PERFORM retrieve_dso.
      seltext_m = ‘DSO’.
      PERFORM build_fieldcatalog.
      PERFORM DISPLAY_ALV_REPORT TABLES lt_tab_dso_disp.
ELSE.
      PERFORM retrieve_cube.
      seltext_m = ‘CUBE’.
      PERFORM build_fieldcatalog.
      PERFORM DISPLAY_ALV_REPORT TABLES lt_tab_cube_disp.
ENDIF.

*&———————————————————————*
*& Form RETRIEVE_DSO
*&———————————————————————*
* Retrieve List of IO byDSO
*———————————————————————-*
FORM retrieve_dso.

SELECT A~ODSOBJECT ATEXT~TXTLG A~IOBJNM B~TXTSH POSIT KEYFLAG
                A~OBJVERS ATEXT~OBJVERS B~OBJVERS ATEXT~LANGU B~LANGU
FROM RSDODSOIOBJ AS A
                JOIN RSDODSOT AS ATEXT
                ON A~ODSOBJECT = ATEXT~ODSOBJECT
                     AND A~OBJVERS = ATEXT~OBJVERS
                     AND ATEXT~LANGU = ‘E’
                JOIN RSDIOBJT AS B
                ON A~IOBJNM = B~IOBJNM
                     AND A~OBJVERS = B~OBJVERS
                     AND B~LANGU = ‘E’
                INTO TABLE lt_tab_dso
WHERE A~ODSOBJECT in o_INP
                AND A~OBJVERS = ‘A’.

SORT lt_tab_dso BY DSO_N POSIT .

LOOP AT lt_tab_dso INTO wa_lt_tab_dso.
wa_lt_tab_dso_disp–DSO_N = wa_lt_tab_dso–DSO_N.
wa_lt_tab_dso_disp–DSO_T = wa_lt_tab_dso–DSO_T.

IF wa_lt_tab_dso–keyflag = ‘X’.
      wa_lt_tab_dso_disp–IO_N_K = wa_lt_tab_dso–IO_N.
ELSE.
      wa_lt_tab_dso_disp–IO_N = wa_lt_tab_dso–IO_N.
ENDIF.
      wa_lt_tab_dso_disp–IO_T = wa_lt_tab_dso–IO_T.
      APPEND wa_lt_tab_dso_disp TO lt_tab_dso_disp.
      CLEAR: wa_lt_tab_dso_disp.
ENDLOOP.
ENDFORM.

*&———————————————————————*
*& Form RETRIEVE_CUBE
*&———————————————————————*
* Retrieve List of IO by CUBE
*———————————————————————-*
FORM retrieve_cube.

SELECT A~INFOCUBE   AS IC_N
                ATEXT~TXTLG AS IC_T
                A2~DIMENSION AS ICD_N
               A~IOBJNM     AS IO_N
                B~TXTSH      AS IO_T
                A2~POSIT
FROM RSDCUBEIOBJ AS A
                JOIN RSDCUBET AS ATEXT
                ON A~INFOCUBE = ATEXT~INFOCUBE
                     AND A~OBJVERS = ATEXT~OBJVERS
                     AND ATEXT~LANGU = ‘E’
                LEFT JOIN RSDDIMEIOBJ AS A2
                ON A~INFOCUBE = A2~INFOCUBE
                     AND A~OBJVERS = A2~OBJVERS
                     AND A~IOBJNM = A2~IOBJNM
                LEFT JOIN RSDIOBJT AS B
                ON A~IOBJNM = B~IOBJNM
                     AND B~LANGU = ‘E’
                     AND A~OBJVERS = B~OBJVERS
                INTO CORRESPONDING FIELDS OF TABLE lt_tab_cube
WHERE A~INFOCUBE in o_INP
                AND A~OBJVERS = ‘A’.

* Select Dimension Text
SELECT DTEXT~INFOCUBE AS IC_N
                DTEXT~DIMENSION AS ICD_N
                DTEXT~TXTSH    AS ICD_T
FROM RSDDIMET AS DTEXT
INTO CORRESPONDING FIELDS OF wa_lt_tab_cube
FOR ALL ENTRIES IN lt_tab_cube
WHERE LANGU = ‘E’
           AND INFOCUBE = lt_tab_cube–IC_N
           AND OBJVERS = ‘A’
           AND DIMENSION = lt_tab_cube–ICD_N.

MODIFY lt_tab_cube FROM wa_lt_tab_cube TRANSPORTING ICD_T
WHERE IC_N = wa_lt_tab_cube–IC_N
AND ICD_N = wa_lt_tab_cube–ICD_N.
ENDSELECT.

DATA: LINE_CNT TYPE I.
LINE_CNT = 1.
CLEAR: wa_lt_tab_cube.
LOOP AT lt_tab_cube ASSIGNING <wa_lt_tab_cube>.

wa_lt_tab_cube_disp–IC_N = <wa_lt_tab_cube>–IC_N.
wa_lt_tab_cube_disp–IC_T = <wa_lt_tab_cube>–IC_T.

      wa_lt_tab_cube_disp–ICD_T = <wa_lt_tab_cube>–ICD_T.
IF wa_lt_tab_cube–ICD_T <> <wa_lt_tab_cube>–ICD_T.
      IF <wa_lt_tab_cube>–ICD_T IS INITIAL.
           wa_lt_tab_cube_disp–ICD_T = ‘Key Figures’.
      ENDIF.
      APPEND wa_lt_tab_cube_disp TO lt_tab_cube_disp.
ENDIF.
CLEAR wa_lt_tab_cube_disp–ICD_T.

      wa_lt_tab_cube_disp–IO_N = <wa_lt_tab_cube>–IO_N.
      wa_lt_tab_cube_disp–IO_T = <wa_lt_tab_cube>–IO_T.
      APPEND wa_lt_tab_cube_disp TO lt_tab_cube_disp.
      wa_lt_tab_cube = <wa_lt_tab_cube>.
      CLEAR wa_lt_tab_cube_disp.
ENDLOOP.

ENDFORM.

*&———————————————————————*
*& Form BUILD_FIELDCATALOG
*&———————————————————————*
* Build Fieldcatalog for ALV Report
*———————————————————————-*
form build_fieldcatalog.

IF seltext_m = ‘DSO’.

      wa_fieldcat–fieldname = ‘DSO_N’.    ” Fieldname in the data table
      wa_fieldcat–seltext_m = seltext_m.   ” Column description in the output
      APPEND wa_fieldcat TO it_fieldcat.

      wa_fieldcat–fieldname = ‘DSO_T’.
      wa_fieldcat–seltext_m = ‘Desc’.
      APPEND wa_fieldcat TO it_fieldcat.

      wa_fieldcat–fieldname = ‘IO_N_K’.
      wa_fieldcat–seltext_m = ‘Key-Fields’.
      APPEND wa_fieldcat TO it_fieldcat.

      wa_fieldcat–fieldname = ‘IO_N’.
      wa_fieldcat–seltext_m = ‘Data Fields’.
      APPEND wa_fieldcat TO it_fieldcat.

      wa_fieldcat–fieldname = ‘IO_T’.
      wa_fieldcat–seltext_m = ‘IO Desc’.
      APPEND wa_fieldcat TO it_fieldcat.

ELSE.

      wa_fieldcat–fieldname = ‘IC_N’.    ” Fieldname in the data table
      wa_fieldcat–seltext_m = seltext_m.   ” Column description in the output
      APPEND wa_fieldcat TO it_fieldcat.

      wa_fieldcat–fieldname = ‘IC_T’.
      wa_fieldcat–seltext_m = ‘Desc’.
      APPEND wa_fieldcat TO it_fieldcat.

      wa_fieldcat–fieldname = ‘ICD_T’.
      wa_fieldcat–seltext_m = ‘Dimension’.
      APPEND wa_fieldcat TO it_fieldcat.

      wa_fieldcat–fieldname = ‘IO_N’.
      wa_fieldcat–seltext_m = ‘IO Tech’.
      APPEND wa_fieldcat TO it_fieldcat.

      wa_fieldcat–fieldname = ‘IO_T’.
      wa_fieldcat–seltext_m = ‘IO Desc’.
      APPEND wa_fieldcat TO it_fieldcat.

ENDIF.
endform. ” BUILD_FIELDCATALOG

*&———————————————————————*
*& Form DISPLAY_ALV_REPORT
*&———————————————————————*
* Display report using ALV grid
*———————————————————————-*
form display_alv_report TABLES lt_tab.

*Pass data and field catalog to ALV function module to display ALV list
CALL FUNCTION ‘REUSE_ALV_GRID_DISPLAY’
      EXPORTING
           IT_FIELDCAT   = it_fieldcat[]
      TABLES
           t_outtab      = lt_tab
      EXCEPTIONS
           program_error = 1
           OTHERS        = 2.

endform. ” DISPLAY_ALV_REPORT

Appendix (2) – Source Code for ZBW_LIST_TRANSF_MAPPING

REPORT ZBW_LIST_TRANSF_MAPPING.

DATA: TARGET TYPE SOBJ_NAME.

SELECT-OPTIONS: O_TARGET FOR TARGET.

DATA: TRANS TYPE RSTRANID.

SELECT-OPTIONS: O_TRANS FOR TRANS.

* For Data Display

DATA: it_fieldcat TYPE slis_t_fieldcat_alv,

wa_fieldcat TYPE slis_fieldcat_alv.

TYPES: BEGIN OF ty_sel,
TRANID TYPE RSTRANID,
SOURCENAME TYPE SOBJ_NAME,

END OF ty_sel.

TYPES: BEGIN OF ty_tab,
           TARGETNAME      TYPE C LENGTH 15,
           TRANID          TYPE RSTRANID,
           RULEID          TYPE RSTRAN_RULEID,
           STEPID          TYPE RSTRAN_STEPID,
           PARAMTYPE       TYPE RSTRAN_PARAMTYPE,
           SOURCENAME      TYPE SOBJ_NAME,
           PARAMNM_SOURCE TYPE C LENGTH 15,
           PARAMNM         TYPE C LENGTH 15,
           RULETYPE        TYPE RSTRAN_RULETYPE,
           RULETEXT        TYPE RSTXTLG,

END OF ty_tab.

TYPES: BEGIN OF ty_tab_disp,
           TARGETNAME      TYPE C LENGTH 15,
           TRANID          TYPE RSTRANID,
           RULEID          TYPE RSTRAN_RULEID,
           SOURCENAME      TYPE SOBJ_NAME,
           PARAMNM_SOURCE TYPE C LENGTH 15,
           PARAMNM         TYPE C LENGTH 15,
           RULETEXT        TYPE RSTXTLG,

END OF ty_tab_disp.

DATA: lt_sel TYPE STANDARD TABLE OF ty_sel,

lt_tab TYPE STANDARD TABLE OF ty_tab,
wa_lt_tab LIKE LINE OF lt_tab,

lt_tab_2 TYPE STANDARD TABLE OF ty_tab,
wa_lt_tab_2 LIKE LINE OF lt_tab_2,

lt_tab_final TYPE STANDARD TABLE OF ty_tab,

lt_tab_disp TYPE STANDARD TABLE OF ty_tab_disp,
wa_lt_tab_disp LIKE LINE OF lt_tab_disp.

*&———————————————————————*

*& MAIN

*&———————————————————————*

*———————————————————————-*

PERFORM data_retrieval.

PERFORM build_fieldcatalog.

PERFORM display_alv_report TABLES lt_tab_disp.

*&———————————————————————*

*& Form DATA_RETRIEVAL

*&———————————————————————*

*———————————————————————-*

form data_retrieval.

IF O_TARGET IS NOT INITIAL.

SELECT A~TARGETNAME A~TRANID B~RULEID STEPID PARAMTYPE
SOURCENAME PARAMNM C~RULETYPE CTEXT~TXTLG AS RULETEXT

FROM RSTRAN AS A JOIN RSTRANFIELD AS B

               ON A~TRANID = B~TRANID
                AND A~OBJVERS = B~OBJVERS
           LEFT JOIN RSTRANRULE AS C
           ON B~TRANID = C~TRANID
                AND B~OBJVERS = C~OBJVERS
                AND B~RULEID = C~RULEID
           LEFT JOIN RSTRANRULET AS CTEXT
           ON B~TRANID = CTEXT~TRANID
                AND B~OBJVERS = CTEXT~OBJVERS
                AND B~RULEID = CTEXT~RULEID
                AND CTEXT~LANGU = ‘E’
           INTO CORRESPONDING FIELDS OF TABLE lt_tab

WHERE TARGETNAME IN O_TARGET
AND A~OBJVERS = ‘A’.

ELSE.

SELECT A~TARGETNAME A~TRANID B~RULEID STEPID PARAMTYPE
SOURCENAME PARAMNM C~RULETYPE CTEXT~TXTLG AS RULETEXT

FROM RSTRAN AS A JOIN RSTRANFIELD AS B
           ON A~TRANID = B~TRANID
                AND A~OBJVERS = B~OBJVERS
           LEFT JOIN RSTRANRULE AS C
           ON B~TRANID = C~TRANID
                AND B~OBJVERS = C~OBJVERS
                AND B~RULEID = C~RULEID
           LEFT JOIN RSTRANRULET AS CTEXT
           ON B~TRANID = CTEXT~TRANID
                AND B~OBJVERS = CTEXT~OBJVERS
                AND B~RULEID = CTEXT~RULEID
                AND CTEXT~LANGU = ‘E’

INTO CORRESPONDING FIELDS OF TABLE lt_tab

WHERE A~TRANID in O_TRANS
AND A~OBJVERS = ‘A’.

ENDIF.

lt_tab_2[] = lt_tab[].

DELETE lt_tab WHERE PARAMTYPE = 1.

DELETE lt_tab_2 WHERE PARAMTYPE = 0.

DELETE lt_tab_2 WHERE RULETYPE = ‘END’.

SORT: lt_tab by tranid ruleid, lt_tab_2 by tranid ruleid.

LOOP AT lt_tab_2 INTO wa_lt_tab_2.
LOOP AT lt_tab INTO wa_lt_tab
WHERE TRANID = wa_lt_tab_2–TRANID
AND RULEID = wa_lt_tab_2–RULEID.

      IF sy–subrc = 0.
           wa_lt_tab_2–PARAMNM_SOURCE = wa_lt_tab–PARAMNM.
      ENDIF.

APPEND wa_lt_tab_2 TO lt_tab_final.
ENDLOOP.

IF sy–subrc <> 0.
APPEND wa_lt_tab_2 TO lt_tab_final.
ENDIF.
CLEAR sy–subrc.

ENDLOOP.

CLEAR: lt_tab, wa_lt_tab, lt_tab_2, wa_lt_tab_2.

LOOP AT lt_tab_final INTO wa_lt_tab.
      wa_lt_tab_disp–TARGETNAME = wa_lt_tab–TARGETNAME.
      wa_lt_tab_disp–TRANID = wa_lt_tab–TRANID.
      wa_lt_tab_disp–RULEID = wa_lt_tab–RULEID.
     wa_lt_tab_disp–SOURCENAME = wa_lt_tab–SOURCENAME.
      wa_lt_tab_disp–PARAMNM_SOURCE = wa_lt_tab–PARAMNM_SOURCE.
      wa_lt_tab_disp–PARAMNM = wa_lt_tab–PARAMNM.

IF wa_lt_tab–RULETYPE = ‘CONSTANT’.
wa_lt_tab_disp–RULETEXT = wa_lt_tab–RULETEXT.

ELSE.
wa_lt_tab_disp–RULETEXT = wa_lt_tab–RULETYPE.
ENDIF.
APPEND wa_lt_tab_disp TO lt_tab_disp.
CLEAR: wa_lt_tab_disp, wa_lt_tab.

ENDLOOP.

endform.

*&———————————————————————*

*& Form BUILD_FIELDCATALOG

*&———————————————————————*

* Build Fieldcatalog for ALV Report

*———————————————————————-*

form build_fieldcatalog.

wa_fieldcat–fieldname = ‘TARGETNAME’. ” Fieldname in the data table

wa_fieldcat–seltext_m = ‘Target’. ” Column description in the output