Skip to Content

In most of the MSSQL versions (MSSQL 2005, 2008 & 2008 R2) there is a limit of 1000 partitions for Infocubes. So for a business where there is daily data loading happens in BI, it becomes critical to know the Infocubes which are approaching towards 1000 partitions. 

Based on this information we can take some preventive actions otherwise there is risk of data load failure. In MSSQL we have a program called RSMSSPARTMON but using this program you can only check number of partitions for single Infocube, it becomes tedious job to check for all the active Infocubes.  

 So I have created a program, which will help to get the list of required Infocubes with number of partitions at one execution.

Once you execute the program you will see following selection screen,

 Selection Screen

Parameters Used

All Infocubes: This option is used to select all the active Infocubes in the system.

InfoArea: This option is used to select all the Infocubes under particular InfoArea. The InfoArea name given should contain the direct Infocubes under that InfoArea.

Selection: This option will collect all the Infocubes given manually in the selection.

Language: The Infocube description will be shown in the language input given by user.

Example

Let us take a scenario where in I want to find out number of partitions for few random Infocubes. We will do manual selection of Infocubes and will get the following output.

Selection Screen

After executing the program we will get following output. The output will be displayed with descending order of Number of Partitions.

Output

 Source Code

 See the below attached source  code,

*& Report  ZBW_PARTITION_INFO

*&

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

*&

*& TITLE: Find the total number of partitions for Infocubes

*&

*& CREATED ON: 22 August 2011

*&

*& CREATED BY: Durgesh Gandewar

*&

*& DESCRIPTION: This program can be used to find the current number

*& of partitions for multiple Infocubes at once, as MSSQL has got restriction of

*& 1000 partitions per cube. This program will help to find out such cubes

*& which are approaching towards 1000 partitions.

*&

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

 

 

REPORT  zbw_partition_info.

 TABLES: rsdcube,rszelttxt.

 ************************* Type Declarations ****************************

 TYPE-POOLS: slis.

 TYPES:

 BEGIN OF ty_rsdcube,

infocube      TYPE         rsinfocube, “Infocube name

END OF ty_rsdcube,

 

BEGIN OF ty_rsdcubet,

infocube      TYPE         rsinfocube, “Infocube name

langu         TYPE         langu,

txtlg         TYPE         rstxtlg,

END OF ty_rsdcubet,

 

BEGIN OF ty_rsmdatastate,

infocube      TYPE         rsinfocube, “Infocube name

tmstmp_rollup TYPE         rsmtmstmprollup,

tmstmp_qualok TYPE         rsmtmstmpqualok,

END OF ty_rsmdatastate,

 

BEGIN OF ty_output,

infocube      TYPE         rsinfocube, “Infocube name

txtlg         TYPE         rstxtlg,    “Cube Description

partno        TYPE         sydbcnt,

qualok        TYPE         sydatum,

rollup        TYPE         sydatum,

status        TYPE         symsgv,

END OF ty_output.

 

************************* Internal Tables ******************************

 

DATA: it_fieldcat     TYPE slis_t_fieldcat_alv,

      it_rsdcube      TYPE STANDARD TABLE OF    ty_rsdcube,

      it_rsdcubet     TYPE STANDARD TABLE OF    ty_rsdcubet,

      it_rsmdatastate TYPE STANDARD TABLE OF    ty_rsmdatastate,

      it_output       TYPE STANDARD TABLE OF    ty_output,

      it_partitions   TYPE                      msspartfunctab.

 

************************* Work Areas ***********************************

 

DATA: wa_fieldcat     TYPE      slis_fieldcat_alv,

      wa_rsdcube      TYPE      ty_rsdcube,

      wa_rsdcubet     TYPE      ty_rsdcubet,

      wa_output       TYPE      ty_output,

      wa_rsmdatastate TYPE      ty_rsmdatastate,

      wa_partitions  TYPE      msspartfuncstruc.

 

************************* Variables ************************************

DATA:

i_title_grid  TYPE   lvc_title,

v_count       TYPE   sy-dbcnt,

v_temp(20)    TYPE   c,

i_repid       TYPE   sy-repid.

 

************************* Constants ************************************

CONSTANTS:

c_gr       TYPE   string VALUE ‘CUBE’,

c_active   TYPE   rsobjvers  VALUE ‘A’.

 

************************************************************************

*                    SELECTION SCREEN DETAILS

************************************************************************

SELECTION-SCREEN BEGIN OF BLOCK blk1 WITH FRAME TITLE text-001.

 

   PARAMETER p_cubes TYPE char1 RADIOBUTTON GROUP c_gr.

   SELECTION-SCREEN BEGIN OF LINE.

     PARAMETERS: r_area TYPE char1 RADIOBUTTON GROUP c_gr.

     SELECTION-SCREEN COMMENT 3(29) text-are.

     PARAMETERS: p_area LIKE rsdcube-infoarea.

   SELECTION-SCREEN END OF LINE.

   PARAMETER p_sel   TYPE char1 RADIOBUTTON GROUP c_gr.

    SELECT-OPTIONS so_cube  FOR rsdcube-infocube.

SELECTION-SCREEN END OF BLOCK blk1.

 

SELECTION-SCREEN BEGIN OF BLOCK blk2 WITH FRAME TITLE text-002.

   PARAMETER p_lang TYPE sylangu DEFAULT ‘E’.

SELECTION-SCREEN END OF BLOCK blk2.

************************************************************************

*                SELECTION OF DATA                                     *

************************************************************************

IF r_area = space.

  p_area = ‘%’.

ENDIF.

IF p_cubes = ‘X’.

  CLEAR   so_cube.

  REFRESH so_cube.

ENDIF.

* select cubes

SELECT infocube

         FROM  rsdcube

         INTO TABLE it_rsdcube

         WHERE  infocube IN so_cube

         AND    objvers  = c_active

         AND    infoarea LIKE p_area.

IF sy-subrc = 0.

* Get cube descriptions

 

  SELECT infocube

         langu

         txtlg

         FROM rsdcubet

         INTO TABLE it_rsdcubet

         FOR ALL ENTRIES IN it_rsdcube

         WHERE infocube = it_rsdcube-infocube

         AND objvers = c_active

         AND langu = p_lang.

 

* Get cube loading information

 

  SELECT infocube

         tmstmp_rollup

         tmstmp_qualok

         FROM rsmdatastate

         INTO TABLE it_rsmdatastate

         FOR ALL ENTRIES IN it_rsdcube

         WHERE infocube = it_rsdcube-infocube.

ENDIF.

 

* Fill up the output table with required information.

 

LOOP AT it_rsdcube INTO wa_rsdcube.

  CLEAR: wa_output,

         wa_rsdcubet.

  wa_output-infocube = wa_rsdcube-infocube.

 

*Populate the description.

 

  READ TABLE it_rsdcubet INTO wa_rsdcubet

        WITH KEY infocube = wa_rsdcube-infocube.

  IF sy-subrc = 0.

    wa_output-txtlg = wa_rsdcubet-txtlg.

  ENDIF.

 

* populate the loading status information

 

  READ TABLE it_rsmdatastate INTO wa_rsmdatastate

      WITH KEY infocube = wa_rsdcube-infocube.

  IF sy-subrc = 0.

    v_temp = wa_rsmdatastate-tmstmp_rollup.

    CONDENSE v_temp.

    wa_output-rollup = v_temp+0(8).

    v_temp = wa_rsmdatastate-tmstmp_qualok.

    CONDENSE v_temp.

    wa_output-qualok = v_temp+0(8).

  ENDIF.

  CONCATENATE ‘/BIC/F’ wa_rsdcube-infocube INTO wa_rsdcube-infocube.

 

* This FM will give the partitioning information

 

  CALL FUNCTION ‘MSS_EXISTS_PARTITION_FUNCTION’

    EXPORTING

      part_tabname            = wa_rsdcube-infocube

    TABLES

      part_func_info_tab      = it_partitions

    EXCEPTIONS

      wrong_database_platform = 1

      function_not_found      = 2

      internal_error          = 3

      db_error                = 4

      no_db_access            = 5

      schema_not_found        = 6

      invalid_input           = 7

      db_not_found            = 8

      not_running_on_mssql    = 9

      OTHERS                  = 10.

  IF sy-subrc <> 0.

    wa_output-partno = 0.

 

* Populate the status information in output.

 

    CASE sy-subrc.

      WHEN 1.

        wa_output-status = ‘Wrong Database Platform’.

      WHEN 2.

        wa_output-status = ‘Function Not Found’.

      WHEN 3.

        wa_output-status = ‘Internal Error’.

      WHEN 4.

        wa_output-status = ‘DB Error’.

      WHEN 5.

        wa_output-status = ‘No DB Access’.

      WHEN 6.

        wa_output-status = ‘Schema Not Found’.

      WHEN 7.

        wa_output-status = ‘Invalid Input’.

      WHEN 8.

        wa_output-status = ‘DB Not Found’.

      WHEN 9.

        wa_output-status = ‘Not Running on MSSQL’.

      WHEN OTHERS.

        wa_output-status = ‘Others’.

    ENDCASE.

  ELSE.

    DESCRIBE TABLE it_partitions LINES v_count.

    v_count = v_count + 1.

    wa_output-partno = v_count.

    wa_output-status = ‘PARTITIONED’.

  ENDIF.

  APPEND wa_output TO it_output.

ENDLOOP.

 

* Sort the output by no. of partitions.

 

SORT it_output BY partno DESCENDING infocube.

 

* Prepare field catalog for ALV display

PERFORM fieldcat.

 

* Display ALV

PERFORM display.

 

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

*&     Form   FEILDCAT

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

*      FIELD  CATALOG TO DISPLAY COLUMNS

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

FORM  fieldcat .

  wa_fieldcat-col_pos      = 1.

  wa_fieldcat-fieldname = ‘INFOCUBE’.

  wa_fieldcat-reptext_ddic = ‘InfoProvider’.

  wa_fieldcat-outputlen = 15.

  wa_fieldcat-tabname      = ‘IT_OUTPUT’.

  APPEND wa_fieldcat TO it_fieldcat.

 

  CLEAR  wa_fieldcat.

  wa_fieldcat-col_pos      = 2.

  wa_fieldcat-fieldname = ‘COMPID’.

  wa_fieldcat-reptext_ddic = ‘Query Technical Name’.

  wa_fieldcat-outputlen = 35.

  wa_fieldcat-tabname      = ‘IT_OUTPUT’.

  APPEND wa_fieldcat TO it_fieldcat.

 

  CLEAR  wa_fieldcat.

  wa_fieldcat-col_pos = 3.

  wa_fieldcat-fieldname = ‘TXTLG’.

  wa_fieldcat-reptext_ddic = ‘Query Description’.

  wa_fieldcat-outputlen = 60.

  wa_fieldcat-tabname      = ‘IT_OUTPUT’.

  APPEND wa_fieldcat TO it_fieldcat.

 

  CLEAR  wa_fieldcat.

  wa_fieldcat-col_pos = 4.

  wa_fieldcat-fieldname = ‘AUTHOR’.

  wa_fieldcat-reptext_ddic = ‘Query Owner’.

  wa_fieldcat-outputlen = 12.

  wa_fieldcat-tabname      = ‘IT_OUTPUT’.

  APPEND wa_fieldcat TO it_fieldcat.

 

  CLEAR  wa_fieldcat.

  wa_fieldcat-col_pos = 5.

  wa_fieldcat-fieldname = ‘LASTUSER’.

  wa_fieldcat-reptext_ddic = ‘Last Changed By’.

  wa_fieldcat-outputlen = 15.

  wa_fieldcat-tabname      = ‘IT_OUTPUT’.

  APPEND wa_fieldcat TO it_fieldcat.

ENDFORM.                   

 

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

*&     Form   DISPLAY

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

*      DISPLAY OUTPUT USING ALV GRID

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

FORM  display .

  CALL FUNCTION ‘REUSE_ALV_GRID_DISPLAY’

    EXPORTING

      i_callback_program = i_repid

      i_grid_title       = i_title_grid

      it_fieldcat        = it_fieldcat

    TABLES

      t_outtab           = it_output

    EXCEPTIONS

      program_error      = 1

      OTHERS             = 2.

  IF sy-subrc <> 0.

   ENDIF.

 ENDFORM. 

To report this post you need to login first.

3 Comments

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

  1. Lars Breddemann
    … to start: thanks for the contribution.
    It’s sure a nice example of how one can gather meta data information about infocube/database structures.

    Two comments on this:
    – I wouldn’t consider this a typical blog topic. You may disagree, but to me blogs should provide a new insight, an opinion or an experience. Not just a plain technical ‘thing’ with source code and all.
    This would be better suited for a WIKI article.

    – There already exists such functionality.
    The expert mode of report “RSDD_MSSQL_CUBEANALYZE” provides multiple checks and tests and among them there is a ‘partition counting’ check.

    Also, the problem with many partitions is NOT that there is a LIMIT of 1000 (or whatever).
    The problem is, that the F-fact table partitions don’t help your query performance.
    They are there, to enable a quick DELETION of data.
    The partitioning of the E-fact table however, can support your query. That’s one of the reasons why we recommended over and over again to condense requests.
    If you got more than 50 uncondensed requests in your cube, than it’s time to start the compression!

    Anyhow: keep on contributing. That wasn’t a bad start at all 😉

    (0) 
  2. Durgesh Gandewar Post author
    Lars, Thanks for the comments as I mentioned earlier this is my first blog and surprisingly I had written an article on this topic at first but then SAP Mentors rejected the article and suggested me to write a blog instead of article and here it is.

    Regards,
    Durgesh.

    (0) 

Leave a Reply