Skip to Content

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.


UI.jpg

Optimum v/s Actual Characteristic Sequence

Output_Actual_Optimum_Seq.jpg

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.

Output_Status.jpg

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 DD03LTABNAME ,

     FIELDNAME         TYPE DD03LFIELDNAME ,

     POSITION          TYPE DD03LPOSITION ,

     CARDINALITY       TYPE I,

     O_POSITION        TYPE DD03LPOSITION ,

     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 TEXTT01.

PARAMETERS :

   P_DIMTAB     TYPE     DD02LTABNAME OBLIGATORY DEFAULT ‘DEFDIMTAB’.

SELECTION-SCREEN END   OF BLOCK SB1.

“Check Validity of Table
SELECT SINGLE *
   FROM DD02L
   INTO WA_DD02L
   WHERE TABNAME   = P_DIMTAB
     AND AS4LOCAL  = ‘A’.
IF SY-SUBRC <> 0.

   “Table does not Exists in system
   CONCATENATE
       ‘Table :’
       P_DIMTAB
       ‘does not exist in system.’
     INTO
       LV_ERR_MSG
     SEPARATED BY SPACE.

    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_CATLGFIELDNAME    = WA_TAB_DESNAME .

   WA_F_CATLGDATATYPE     = WA_TAB_DESTYPE_KIND.

   WA_F_CATLGINTTYPE      = WA_TAB_DESTYPE_KIND.

   WA_F_CATLGINTLEN       = WA_TAB_DESLENGTH.

   WA_F_CATLGDECIMALS     = WA_TAB_DESDECIMALS.

   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_CARDTABTABNAME          = P_DIMTAB.

   WA_CARDTABFIELDNAME        = WA_F_CATLGFIELDNAME.

   WA_CARDTABPOSITION         = SYTABIX.

   <FS_DYN_SORT_TAB> = <FS_DYN_TAB>.

   “Find unique records

   SORT <FS_DYN_SORT_TAB>

     BY (WA_F_CATLGFIELDNAME) ASCENDING.

   DELETE ADJACENT DUPLICATES

     FROM  <FS_DYN_SORT_TAB>

     COMPARING (WA_F_CATLGFIELDNAME).

   “Identify cardinality for each field

   DESCRIBE

     TABLE <FS_DYN_SORT_TAB>

     LINES WA_CARDTABCARDINALITY.

   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  = SYTABIX.

   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_FIELDCATCOL_POS = 1.

WA_FIELDCATKEY = ‘X’.

WA_FIELDCATFIELDNAME = ‘STATUS’.

WA_FIELDCATREPTEXT_DDIC = ‘Status’.

WA_FIELDCATICON = ‘X’.

WA_FIELDCATOUTPUTLEN = 6.

WA_FIELDCATTABNAME = ‘IT_CARDTAB’.

APPEND WA_FIELDCAT TO IT_FIELDCAT.

CLEAR WA_FIELDCAT.

WA_FIELDCATCOL_POS = 2.

WA_FIELDCATKEY = ‘X’.

WA_FIELDCATFIELDNAME = ‘TABNAME’.

WA_FIELDCATREPTEXT_DDIC = ‘Dimension Table’.

WA_FIELDCATOUTPUTLEN = 30.

WA_FIELDCATTABNAME = ‘IT_CARDTAB’.

APPEND WA_FIELDCAT TO IT_FIELDCAT.

CLEAR WA_FIELDCAT.

WA_FIELDCATCOL_POS = 3.

WA_FIELDCATKEY = ‘X’.

WA_FIELDCATFIELDNAME = ‘FIELDNAME’.

WA_FIELDCATREPTEXT_DDIC = ‘Field Name/SID Table’.

WA_FIELDCATOUTPUTLEN = 30.

WA_FIELDCATTABNAME = ‘IT_CARDTAB’.

APPEND WA_FIELDCAT TO IT_FIELDCAT.

CLEAR WA_FIELDCAT.

WA_FIELDCATCOL_POS = 4.

WA_FIELDCATFIELDNAME = ‘O_POSITION’.

WA_FIELDCATREPTEXT_DDIC = ‘Optimum Position in Dimension’.

WA_FIELDCATOUTPUTLEN = 25.

WA_FIELDCATTABNAME = ‘IT_CARDTAB’.

APPEND WA_FIELDCAT TO IT_FIELDCAT.

CLEAR WA_FIELDCAT.

WA_FIELDCATCOL_POS = 5.

WA_FIELDCATFIELDNAME = ‘CARDINALITY’.

WA_FIELDCATREPTEXT_DDIC = ‘Cardinality’.

WA_FIELDCATOUTPUTLEN = 20.

WA_FIELDCATTABNAME = ‘IT_CARDTAB’.

APPEND WA_FIELDCAT TO IT_FIELDCAT.

CLEAR WA_FIELDCAT.

WA_FIELDCATCOL_POS = 6.

WA_FIELDCATFIELDNAME = ‘POSITION’.

WA_FIELDCATREPTEXT_DDIC = ‘Actual Position’.

WA_FIELDCATOUTPUTLEN = 20.

WA_FIELDCATTABNAME = ‘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 = SYREPID

     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 SYSUBRC <> 0.

   CASE SYSUBRC.

     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

  1. 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.
  2. Simultaneous analysis of multiple dimensions.


To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Tobias Haas

    Hi,

    do you made some benchmarks of cubes where you restructured the positions?What performance benefit you gained?

    Thank you.

    Kind regards,

    Tobias

    (0) 

Leave a Reply