Cube Optimization
The following program can help in redesigning Infocubes for better load performance.
The idea is to arrange the characteristics in an infocube dimension in the decreasing order of cardinality (No. of unique records). This program analyses a dimension of a cube and finds out the cardinality associated with each characteristic/SID. It then compares the current position of the characteristic in the dimension with the possible optimum position.
User Interface
Give the Cube’s dimension table name and click on execute.
Optimum v/s Actual Characteristic Sequence
Status
Green indicates that the characteristic is at its optimum position and a yellow status indicates that the characteristics needs to be moved from its current position.
Code
Create an executable program and copy-paste this code.
| Code |
|---|
|
*&———————————————————————* *& Report Z_BW_CUBE_DIMENSION_OPT *& *&———————————————————————* *& *& *&——————————————————————–&* *& ABAP Name : Z_BW_CUBE_DIMENSION_OPT &* *& Changed By : Rahul R Nair &* *& Created On : 03/19/2012 &* *& Version : 1.0 &* *& Description : This program can be used to find the optimum &* *& sequence of chracteristics in a Cube dimension &* *& table. &* *&—————————————————————- REPORT Z_BW_CUBE_DIMENSION_OPT. *$*********************************************************************** *$*$ TABLES *$*$********************************************************************* TABLES : DD02L. TYPE-POOLS : SLIS, ICON, ABAP. *$*********************************************************************** *$*$ TYPES *$*$********************************************************************* TYPES : BEGIN OF TY_CARDTAB, TABNAME TYPE DD03L–TABNAME , FIELDNAME TYPE DD03L–FIELDNAME , POSITION TYPE DD03L–POSITION , CARDINALITY TYPE I, O_POSITION TYPE DD03L–POSITION , STATUS TYPE CHAR4, END OF TY_CARDTAB. *$*********************************************************************** *$*$ DATA DECLARATION *$*$********************************************************************* DATA : IT_CARDTAB TYPE STANDARD TABLE OF TY_CARDTAB, WA_CARDTAB TYPE TY_CARDTAB. DATA : IT_DYN_TAB TYPE REF TO DATA, IT_DYN_SORT_TAB TYPE REF TO DATA, WA_F_CATLG TYPE LVC_S_FCAT, IT_F_CATLG TYPE LVC_T_FCAT. DATA : LV_DIM_TAB_REF TYPE REF TO CL_ABAP_STRUCTDESCR. DATA : IT_TAB_DES TYPE ABAP_COMPDESCR_TAB, WA_TAB_DES TYPE ABAP_COMPDESCR. FIELD-SYMBOLS : <FS_DYN_TAB> TYPE STANDARD TABLE, <FS_DYN_SORT_TAB> TYPE STANDARD TABLE. FIELD-SYMBOLS : <FS_CARDTAB> TYPE TY_CARDTAB. DATA : IT_FIELDCAT TYPE SLIS_T_FIELDCAT_ALV, WA_FIELDCAT TYPE SLIS_FIELDCAT_ALV. “FIELD CATALOG DATA : IT_SORT TYPE SLIS_T_SORTINFO_ALV , WA_SORT TYPE SLIS_SORTINFO_ALV. DATA : LV_ERR_MSG TYPE STRING, LV_TITLE TYPE TEXT70. *$*********************************************************************** *$*$ SELECTION SCREENS *$*$********************************************************************* SELECTION-SCREEN BEGIN OF BLOCK SB1 WITH FRAME TITLE TEXT–T01. PARAMETERS : P_DIMTAB TYPE DD02L–TABNAME OBLIGATORY DEFAULT ‘DEFDIMTAB’. SELECTION-SCREEN END OF BLOCK SB1. “Check Validity of Table “Table does not Exists in system MESSAGE LV_ERR_MSG TYPE ‘E’. ENDIF. “Identify structure of table LV_DIM_TAB_REF ?= CL_ABAP_TYPEDESCR=>DESCRIBE_BY_NAME( P_DIMTAB ). IT_TAB_DES = LV_DIM_TAB_REF->COMPONENTS. “Populate field catalog LOOP AT IT_TAB_DES INTO WA_TAB_DES. CLEAR WA_F_CATLG. WA_F_CATLG–FIELDNAME = WA_TAB_DES–NAME . WA_F_CATLG–DATATYPE = WA_TAB_DES–TYPE_KIND. WA_F_CATLG–INTTYPE = WA_TAB_DES–TYPE_KIND. WA_F_CATLG–INTLEN = WA_TAB_DES–LENGTH. WA_F_CATLG–DECIMALS = WA_TAB_DES–DECIMALS. APPEND WA_F_CATLG TO IT_F_CATLG. ENDLOOP. “*********************************************************** “Create Dynamic Internal Table CALL METHOD CL_ALV_TABLE_CREATE=>CREATE_DYNAMIC_TABLE EXPORTING IT_FIELDCATALOG = IT_F_CATLG IMPORTING EP_TABLE = IT_DYN_TAB. “Assign Dynamic internal table to Field Symbol ASSIGN IT_DYN_TAB->* TO <FS_DYN_TAB>. “*********************************************************** “Create Dynamic Internal Table (Sort) CALL METHOD CL_ALV_TABLE_CREATE=>CREATE_DYNAMIC_TABLE EXPORTING IT_FIELDCATALOG = IT_F_CATLG IMPORTING EP_TABLE = IT_DYN_SORT_TAB. “Assign Dynamic internal table (Sort) to Field Symbol ASSIGN IT_DYN_SORT_TAB->* TO <FS_DYN_SORT_TAB>. “*********************************************************** “Get the data from the DB table to the internal table SELECT * INTO TABLE <FS_DYN_TAB> FROM (P_DIMTAB). “”*********************************************************** “Start of Analysis LOOP AT IT_F_CATLG INTO WA_F_CATLG. CLEAR WA_CARDTAB. WA_CARDTAB–TABNAME = P_DIMTAB. WA_CARDTAB–FIELDNAME = WA_F_CATLG–FIELDNAME. WA_CARDTAB–POSITION = SY–TABIX. <FS_DYN_SORT_TAB> = <FS_DYN_TAB>. “Find unique records SORT <FS_DYN_SORT_TAB> BY (WA_F_CATLG–FIELDNAME) ASCENDING. DELETE ADJACENT DUPLICATES FROM <FS_DYN_SORT_TAB> COMPARING (WA_F_CATLG–FIELDNAME). “Identify cardinality for each field DESCRIBE TABLE <FS_DYN_SORT_TAB> LINES WA_CARDTAB–CARDINALITY. APPEND WA_CARDTAB TO IT_CARDTAB. ENDLOOP. “Compare Proposed and Actual Sequence SORT IT_CARDTAB BY CARDINALITY DESCENDING. LOOP AT IT_CARDTAB ASSIGNING <FS_CARDTAB>. <FS_CARDTAB>–O_POSITION = SY–TABIX. IF <FS_CARDTAB>–POSITION = <FS_CARDTAB>–O_POSITION. <FS_CARDTAB>–STATUS = ICON_GREEN_LIGHT.“ ELSE. <FS_CARDTAB>–STATUS = ICON_YELLOW_LIGHT.“ ENDIF. ENDLOOP. SORT IT_CARDTAB BY O_POSITION ASCENDING. “*********************************************************** “********************************************************** “DISPLAY WA_FIELDCAT–COL_POS = 1. WA_FIELDCAT–KEY = ‘X’. WA_FIELDCAT–FIELDNAME = ‘STATUS’. WA_FIELDCAT–REPTEXT_DDIC = ‘Status’. WA_FIELDCAT–ICON = ‘X’. WA_FIELDCAT–OUTPUTLEN = 6. WA_FIELDCAT–TABNAME = ‘IT_CARDTAB’. APPEND WA_FIELDCAT TO IT_FIELDCAT. CLEAR WA_FIELDCAT. WA_FIELDCAT–COL_POS = 2. WA_FIELDCAT–KEY = ‘X’. WA_FIELDCAT–FIELDNAME = ‘TABNAME’. WA_FIELDCAT–REPTEXT_DDIC = ‘Dimension Table’. WA_FIELDCAT–OUTPUTLEN = 30. WA_FIELDCAT–TABNAME = ‘IT_CARDTAB’. APPEND WA_FIELDCAT TO IT_FIELDCAT. CLEAR WA_FIELDCAT. WA_FIELDCAT–COL_POS = 3. WA_FIELDCAT–KEY = ‘X’. WA_FIELDCAT–FIELDNAME = ‘FIELDNAME’. WA_FIELDCAT–REPTEXT_DDIC = ‘Field Name/SID Table’. WA_FIELDCAT–OUTPUTLEN = 30. WA_FIELDCAT–TABNAME = ‘IT_CARDTAB’. APPEND WA_FIELDCAT TO IT_FIELDCAT. CLEAR WA_FIELDCAT. WA_FIELDCAT–COL_POS = 4. WA_FIELDCAT–FIELDNAME = ‘O_POSITION’. WA_FIELDCAT–REPTEXT_DDIC = ‘Optimum Position in Dimension’. WA_FIELDCAT–OUTPUTLEN = 25. WA_FIELDCAT–TABNAME = ‘IT_CARDTAB’. APPEND WA_FIELDCAT TO IT_FIELDCAT. CLEAR WA_FIELDCAT. WA_FIELDCAT–COL_POS = 5. WA_FIELDCAT–FIELDNAME = ‘CARDINALITY’. WA_FIELDCAT–REPTEXT_DDIC = ‘Cardinality’. WA_FIELDCAT–OUTPUTLEN = 20. WA_FIELDCAT–TABNAME = ‘IT_CARDTAB’. APPEND WA_FIELDCAT TO IT_FIELDCAT. CLEAR WA_FIELDCAT. WA_FIELDCAT–COL_POS = 6. WA_FIELDCAT–FIELDNAME = ‘POSITION’. WA_FIELDCAT–REPTEXT_DDIC = ‘Actual Position’. WA_FIELDCAT–OUTPUTLEN = 20. WA_FIELDCAT–TABNAME = ‘IT_CARDTAB’. APPEND WA_FIELDCAT TO IT_FIELDCAT. CLEAR WA_FIELDCAT. CONCATENATE ‘Dimension Table Analysis – ‘ P_DIMTAB INTO LV_TITLE. CALL FUNCTION ‘REUSE_ALV_GRID_DISPLAY’ EXPORTING I_CALLBACK_PROGRAM = SY–REPID I_GRID_TITLE = LV_TITLE IT_FIELDCAT = IT_FIELDCAT IT_SORT = IT_SORT TABLES T_OUTTAB = IT_CARDTAB EXCEPTIONS PROGRAM_ERROR = 1 OTHERS = 2. IF SY–SUBRC <> 0. CASE SY–SUBRC. WHEN 1. LV_ERR_MSG = ‘Program error during list generation.’. WHEN 2. LV_ERR_MSG = ‘Unknown error during list generation.’. ENDCASE. MESSAGE LV_ERR_MSG TYPE ‘E’. ENDIF. “DISPLAY “********************************************************** |
Text Elements
Selection Text
| P_TAB | Dimension Table Name : |
Text Symbols
| T01 | Cube Dimension Table Name |
Acknowledgment
I would like to thank Ramji Krishnamoorthy for giving this idea and encouraging me to write this program.
Future development
- This program can be enhanced to allow the user to login to a dev system and analyse a cube present in a Production or QA system. An FM to read data using an RFC connection would be required to achieve this functionality.
- Simultaneous analysis of multiple dimensions.
Hi,
do you made some benchmarks of cubes where you restructured the positions?What performance benefit you gained?
Thank you.
Kind regards,
Tobias
Hi Tobias,
These are based on the guidelines specified by SAP for creating index.
What to Keep in Mind for Secondary Indexes (SAP Library - Tables)
Thanks & Regards,
Rahul Rajagoapalan Nair