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
- 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.
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
- SAP Managed Tags:
