Custom Program to find the Size of the InfoCube

Applies to:

SAP  BW 7.x. For more information, visit the Business Intelligence homepage.

Summary

This Document describes how to know check the size of the individual InfoCube’s using the custom program.

Author          : Lakshminarasimhan Narasimhamurthy

Created on   : 26 August 2015

Author Bio

Lakshminarasimhan Narasimhamurthy is ABAP certified and BW certified consultant and worked on multiple implementation, development and support project’s within India and outside of India. He has worked for fortune 500 clients like Nestle, Warner Bros,General Electric. He is strong in HANA modeling and BO/BI tools like WEBI, Dashboard, IDT, Lumira.

Details

InfoCube is primary InfoProvider for storing data and we create report over them. If dimension tables are too large then they can result in badly performing the table joins, at the database level, as well as result in poor query performance. SAP_INFOCUBE_DESIGNS shows the database tables of the InfoCubes on the system, the number of records in these tables & the ratio of the dimension table size to the fact table size. SAP_INFOCUBE_DESIGNS program shows the size of the fact table and dimensional tables of the InfoCube’ s. It is a program delivered by SAP and can be run in the BW system from the transaction SE38.

When you run the program, all the cubes which are active in the system are considered and results are shown for those active cubes, if in case the cube is empty then the results are not displayed in the report. There are no input fields available and hence the program runs for all of the active cubes which consume lot of time especially in the production system. Report SAP_INFOCUBE_DESIGNS reads the number of records from the database table DBA_TABLES.

When you run the program SAP_INFOCUBE_DESIGNS the output shows the “Rows” which indicate the number of rows in that Dimension and Fact table and the last column Ratio (% in the last column) indicates the percentage of number of rows in that table divided by number of rows in the fact table.

As a standard, the ratio of rows in Dimension table to rows in Fact table should not be more than 10%. If the size of the dimension table is more than 10% it will be displayed in RED, then we need to redesign the dimension. Note  : The red highlight will only show up for cubes with more than 30.000 rows to avoid false alarms).

The above error can be corrected by either creating additional dimensions or make a dimension as line item dimension as the there will be  no need of generating the “Dim ID’s” only the SID will fall directly into the fact table.

The customs  program has a “select options” that takes the input from the user and the Function module RSDU_INFOCUBE_TABLE_SIZES  will be called to get the size of the dimension table.

The standard program is copied as a  ZSAP_INFOCUBE_DESIGNS program. Select Options defined to get the data from the user and we check the entries entered by user against the table RSDCUBE and call the FM to get the size.

The custom program does 3 things

  • Select-option to get the values from user and input help has been enabled for the user to select the cubes.
  • For every entry in the select options, the Function module RSDU_INFOCUBE_TABLE_SIZES is called and size of the cube is determined
  • If the cube is empty the program displays the message that the cube is empty.
  • Also you can run this program without any input parameters to check the list of cube which are active but empty, as this program lists the cubes that are empty too. Again running without any inputs would take lot of time.

Steps –

  • Go to t-code SE38
  • Type  the Program SAP_INFOCUBE_DESIGNS and then copy it to ZSAP_INFOCUBE_DESIGNS

P1.png

P2.png

  • Edit the copied program in SE38

The code of the custom program is given below,

REPORT ZBW_SAP_INFOCUBE_DESIGNS.

TYPE-POOLS :     RSD,

                            RSDU,

                            SSCR.

TABLES     : RSDCUBEV.

DATA          : RESTRICT     TYPE SSCR_RESTRICT,

                     OPT_LIST TYPE SSCR_OPT_LIST,

                     LV_SSCR_*** TYPE SSCR_***,

                     IT_CUBE      TYPE STANDARD TABLE OF RSINFOCUBE,

                     WA_CUBE      TYPE RSINFOCUBE,

                      IT1_CUBE     TYPE STANDARD TABLE OF RSINFOCUBE, ” To capture the empty cubes

                      WA1_CUBE TYPE RSINFOCUBE,

                      INFOCUBE     TYPE STANDARD TABLE OF ZINFOCUBE.

” Get the list of Infocubes to be tested for its Size/Design

SELECTION-SCREEN BEGIN OF BLOCK B1.

SELECT-OPTIONS : LS_CUBE FOR RSDCUBEV-INFOCUBE.

SELECTION-SCREEN END OF BLOCK B1.

DATA: L_T_CUBE TYPE RSD_T_CUBE,

           L_S_CUBE            TYPE RSD_S_CUBE,

           L_FACTROWS          TYPE I,

           L_S_TABLSIZE        TYPE RSDU_S_TABLSIZE,

           L_T_TABLSIZE        TYPE RSDU_T_TABLSIZE,

           L_DENSITY           TYPE F,

           L_PDENSITY          TYPE P DECIMALS 1,

           L_COUNTER           TYPE I,

           L_ODD               TYPE I.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR LS_CUBE-LOW. ” F4 help for the select options

  PERFORM LS_CUBE_F4_HELP.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR LS_CUBE-HIGH. ” F4 help for the select options

  PERFORM LS_CUBE_F4_HELP.

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

*&      Form LS_CUBE_F4_HELP

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

*       text

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

FORM LS_CUBE_F4_HELP.

  SELECT DISTINCT INFOCUBE FROM RSDCUBE INTO TABLE IT_CUBE WHERE OBJVERS = ‘A’.

  CALL FUNCTION ‘F4IF_INT_TABLE_VALUE_REQUEST’

    EXPORTING

      DDIC_STRUCTURE         = ‘ZINFOCUBE’   “ Create a custom structure

      RETFIELD               = ‘INFOCUBE’

*     PVALKEY                = ‘ ‘

      DYNPPROG               = SY-REPID

      DYNPNR                 =  SY-DYNNR

      DYNPROFIELD            = ‘LS_CUBE’

*     STEPL                  = 0

*     WINDOW_TITLE           =

*     VALUE                  = ‘ ‘

      VALUE_ORG              = ‘S’

*     MULTIPLE_CHOICE        = ‘ ‘

*     DISPLAY                = ‘ ‘

*     CALLBACK_PROGRAM       = ‘ ‘

*     CALLBACK_FORM          = ‘ ‘

*     CALLBACK_METHOD        =

*     MARK_TAB               =

*  IMPORTING

*     USER_RESET             =

   TABLES

      VALUE_TAB              = IT_CUBE

*     FIELD_TAB              =

*     RETURN_TAB             =

*     DYNPFLD_MAPPING        =

   EXCEPTIONS

      PARAMETER_ERROR        = 1

     NO_VALUES_FOUND        = 2

     OTHERS                 = 3

            .

  IF SY-SUBRC <> 0.

* Implement suitable error handling here

  ENDIF.


“LS_CUBE_F4_HELP

START-OF-SELECTION.

     CLEAR : IT_CUBE[].

     SELECT DISTINCT

     INFOCUBE

    FROM

    RSDCUBE ” Cube table

    INTO TABLE IT_CUBE

    WHERE

    OBJVERS = ‘A’

   AND

   INFOCUBE IN LS_CUBE. ” IN operator

  LOOP AT IT_CUBE INTO WA_CUBE.

* get cube layout information

    CALL FUNCTION ‘RSDU_INFOCUBE_TABLE_SIZES’

      EXPORTING

      I_INFOCUBE   = WA_CUBE     ” Technical name of the cube

      I_CHECK      = RS_C_TRUE

      IMPORTING

      E_FACTROWS   = L_FACTROWS

      E_T_TABLSIZE = L_T_TABLSIZE

      E_DENSITY    = L_DENSITY.

* give details only for non-empty cubes

    IF L_FACTROWS = 0.

      APPEND WA_CUBE TO IT1_CUBE.   ” Empty cube with no data

      CONTINUE.

    ENDIF.

* count non-empty cubes

    L_COUNTER = L_COUNTER + 1.

* set format

    FORMAT COLOR COL_BACKGROUND INVERSE ON.

* print cube infos

    L_PDENSITY = L_DENSITY.

    WRITE: AT /   L_S_CUBE-INFOCUBE,

           AT 20  ‘ROWS:’, L_FACTROWS,

           AT 40  ‘DENSITY:’, L_PDENSITY, ‘%’.

* print cube table infos

    LOOP AT L_T_TABLSIZE INTO L_S_TABLSIZE.

*   swap formats

      L_ODD = L_COUNTER MOD 2.

      IF L_ODD = 1.

        FORMAT COLOR COL_BACKGROUND INTENSIFIED OFF INVERSE OFF.

      ELSE.

        FORMAT COLOR COL_BACKGROUND INTENSIFIED ON INVERSE OFF.

      ENDIF.

*   write …

      IF L_S_TABLSIZE-SIZE_CHECK <> RSDU_C_CHECK-GREEN.

        FORMAT COLOR COL_NEGATIVE INTENSIFIED OFF INVERSE ON.

      ENDIF.

      WRITE: AT /   L_S_CUBE-INFOCUBE,

             AT 20  L_S_TABLSIZE-TABLNM,

             AT 40  ‘ROWS:’, L_S_TABLSIZE-ROWS,

             AT 60  ‘DENSITY:’, L_S_TABLSIZE-PERCENT, ‘%’.

    ENDLOOP.

  ENDLOOP.

  ULINE.

  ” Cubes with no data stored on IT1_CUBE

  LOOP AT IT1_CUBE INTO WA_CUBE.

    WRITE : AT / ‘INFOCUBE ‘, AT 20 WA_CUBE, AT 40 ‘ IS EMPTY’.

  ENDLOOP.

Explanation on the code

Given below statement declares “select-options”  to get the input from the user

” Get the list of Infocubes to be tested for its Size/Design

SELECTION-SCREEN BEGIN OF BLOCK B1.

SELECT-OPTIONS : LS_CUBE FOR RSDCUBEV-INFOCUBE.

SELECTION-SCREEN END OF BLOCK B1.

We need to provide F4 help for the “select-options” and hence we create the below statements. We must provide the list of active cubes for the user to select. The lists of active cubes are present in the SAP BW system in the table RSDCUBE. We take those cubes and put them in internal table IT_CUBE. I have created a structure ZINFOCUBE as given below.

P3.png

AT SELECTION-SCREEN ON VALUE-REQUEST FOR LS_CUBE-LOW. ” F4 help for the select options

  PERFORM LS_CUBE_F4_HELP.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR LS_CUBE-HIGH. ” F4 help for the select options

  PERFORM LS_CUBE_F4_HELP.

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

*&      Form  LS_CUBE_F4_HELP

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

*       text

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

FORM LS_CUBE_F4_HELP.

  SELECT DISTINCT INFOCUBE FROM RSDCUBE INTO TABLE IT_CUBE WHERE OBJVERS = ‘A’.

  CALL FUNCTION ‘F4IF_INT_TABLE_VALUE_REQUEST’

    EXPORTING

      DDIC_STRUCTURE         = ‘ZINFOCUBE’

      RETFIELD               = ‘INFOCUBE’

*     PVALKEY                = ‘ ‘

      DYNPPROG               =  SY-REPID

      DYNPNR                 =  SY-DYNNR

      DYNPROFIELD            = ‘LS_CUBE’

*     STEPL                  = 0

*     WINDOW_TITLE           =

*     VALUE                  = ‘ ‘

      VALUE_ORG              = ‘S’

*     MULTIPLE_CHOICE        = ‘ ‘

*     DISPLAY                = ‘ ‘

*    CALLBACK_PROGRAM       = ‘ ‘

*     CALLBACK_FORM          = ‘ ‘

*     CALLBACK_METHOD        =

*     MARK_TAB               =

* IMPORTING

*     USER_RESET             =

      TABLES

      VALUE_TAB              = IT_CUBE

*     FIELD_TAB              =

*     RETURN_TAB             =

*     DYNPFLD_MAPPING        =

EXCEPTIONS

       PARAMETER_ERROR        = 1

       NO_VALUES_FOUND        = 2

       OTHERS                 = 3

            .

  IF SY-SUBRC <> 0.

* Implement suitable error handling here

  ENDIF.


“LS_CUBE_F4_HELP

User can select single cube or multiple single cubes or range of cubes. So we have the below select statement to pick the list of cubes given in the select-options.

START-OF-SELECTION.

CLEAR : IT_CUBE[].

SELECT DISTINCT

INFOCUBE

FROM

RSDCUBE ” Cube table

INTO TABLE IT_CUBE

WHERE

OBJVERS = ‘A’

AND

INFOCUBE IN LS_CUBE. ” IN operator

We  loop at the internal table IT_CUBE and call the FM RSDU_INFOCUBE_TABLE_SIZES to determine  its size. In case if the cube is empty it is captured in the internal table IT1_CUBE.

LOOP AT IT_CUBE INTO WA_CUBE.

* get cube layout information

    CALL FUNCTION ‘RSDU_INFOCUBE_TABLE_SIZES’

      EXPORTING

I_INFOCUBE   = WA_CUBE     ” Technical name of the cube

        I_CHECK      = RS_C_TRUE

      IMPORTING

E_FACTROWS   = L_FACTROWS

        E_T_TABLSIZE = L_T_TABLSIZE

        E_DENSITY    = L_DENSITY.

* give details only for non-empty cubes

    IF L_FACTROWS = 0.

      APPEND WA_CUBE TO IT1_CUBE.   ” Empty cube with no data

      CONTINUE.

    ENDIF.

We print the details of the empty cubes.

  ” Cubes with no data stored on IT1_CUBE

  LOOP AT IT1_CUBE INTO WA_CUBE.

    WRITE : AT / ‘INFOCUBE ‘, AT 20 WA_CUBE, AT 40 ‘ IS EMPTY’.

  ENDLOOP.

Remaining code is unaltered copy of the SAP_INFOCUBE_DESIGNS.

Some times  even if you run the standard program, “SAP_INFOCUBE_DESIGNS” for few cubes the result will not be shown, because it is not just enough to load the data we need to maintain the statistics for the Infocube too. Right click the cube –> Manage —-> Performance —> Build Statistics.

After building statistics immediately the program does not recognize the cube 🙂 After sometimes it does recognize the cube!! Strange but true.

Related Content

For more information visit the webpage SAP Business Warehouse – SAP Help Portal Page


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