Skip to Content

Business Scenario:

In one of my earlier document ‘How to find Top 10 routines in SAP BW system taking maximum time during data loading’ at link
http://scn.sap.com/docs/DOC-32859 I explained the strategy using which we can find the top 10 routines in BW system. Now in this document I have written an ABAP code to automate that strategy in BW.

Introduction:

In this tool, we would fetch the total run time taken during a data load and then time taken by a routine. After that, we will divide these two times and present it in the form of percentage time taken by routine as compared to total data load time.

In the above screenshot, we have an option to choose the analyses period for which we want routine statistics tool to run in our system. By default today date will be present in this.

Below mentioned is the list of the processes when data load occurs in BW system:

  • U_CUBE : Time taken in updating data to cube.
  • ERROR_FILTER : Time taken in filter values being read inside the DTP.
  • TRFN : Time taken in the execution of transformation.
  • X_DATASTORE : Time taken in extracting data from the source.
  • Not assigned : Time taken in performing the miscellaneous activities like process allocation, start time, end time, message update time , status update time etc.

Among these processes, we will consider all the processes when calculating total duration and processes TRFN & Not assigned when calculating TRFN duration.

Code:

Below is the code which will run in background to fetch the routine statistics.

*&---------------------------------------------------------------------*

*& Report  zbw_ROUTINESTATS

*&

*&---------------------------------------------------------------------*

*&

*&

*&---------------------------------------------------------------------*

REPORT  zbw_routinestats.

TYPE-POOLS: rs, rsdrc,slis.

TABLES:rstranrule,rstran,rsbkdtp.

TYPES:  BEGIN OF t_result,

  0tctdtpid(30) TYPE c,

  0tctdtpcmd(30) TYPE c,

  0tctsource(45) TYPE c,

  0tcttarget(45) TYPE c,

  0calday TYPE dats,

  0tctreqsid(9) TYPE c,

  0tctdurtion   TYPE p DECIMALS 2,

  0tctstrtdat   TYPE dats,

  0tctenddat   TYPE dats,

  END OF t_result,

BEGIN OF t_result1,

  0tctdtpid(30) TYPE c,

  0tctsource(45) TYPE c,

  0tcttarget(45) TYPE c,

  0calday TYPE dats,

  0tctreqsid(9) TYPE c,

  0tctstrtdat   TYPE dats,

  0tctenddat   TYPE dats,

  0tctdurtion   TYPE p DECIMALS 2,

  trfndurtion TYPE  p DECIMALS 2,

  trfnpercentage TYPE p DECIMALS 2,

  END OF t_result1,

  BEGIN OF t_result2,

  0tctdtpid(30) TYPE c,

  0tctsource(45) TYPE c,

  0tcttarget(45) TYPE c,

  0calday TYPE dats,

  0tctreqsid(9) TYPE c,

  0tctdurtion   TYPE p DECIMALS 2,

  0tctstrtdat   TYPE dats,

  0tctenddat   TYPE dats,

  END OF t_result2,

  BEGIN OF t_rstransrule,

    tranid   TYPE rstranrule-tranid,

    ruletype TYPE rstranrule-ruletype,

    END OF t_rstransrule,

  BEGIN OF t_rstran,

    tranid     TYPE rstran-tranid ,

    sourcetype TYPE rstran-sourcetype,

    sourcename(45) TYPE c,

    targettype TYPE rstran-targettype,

    targetname(45) TYPE c,

    END OF t_rstran,

  BEGIN OF t_rsbkdtp,

    dtp TYPE rsbkdtp-dtp,

    src TYPE rsbkdtp-src,

    tgt TYPE rsbkdtp-tgt,

    END OF t_rsbkdtp .

DATA: i_char          TYPE rsdri_th_sfc,

      wa_char         TYPE rsdri_s_sfc,

      i_kf            TYPE rsdri_th_sfk,

      wa_kf           TYPE rsdri_s_sfk,

      i_out           TYPE STANDARD TABLE OF t_result,

      i_result        TYPE STANDARD TABLE OF t_result1,

      wa_result       TYPE t_result1,

      i_result1       TYPE STANDARD TABLE OF t_result,

      wa_result1      TYPE t_result,

      i_result2       TYPE STANDARD TABLE OF t_result2,

      wa_result2      TYPE t_result2,

      i_result3       TYPE STANDARD TABLE OF t_result2,

      wa_result3      TYPE t_result2,

      wa_range        TYPE rsdri_s_range,

      i_range         TYPE rsdri_t_range,

      i_rstranrule    TYPE STANDARD TABLE OF t_rstransrule,

      wa_rstranrule   TYPE t_rstransrule,

      i_rstran        TYPE STANDARD TABLE OF t_rstran,

      wa_rstran       TYPE t_rstran,

      i_rsbkdtp       TYPE STANDARD TABLE OF t_rsbkdtp,

      wa_rsbkdtp      TYPE t_rsbkdtp.

DATA: reportid TYPE sy-repid,

      i_fieldcat TYPE slis_t_fieldcat_alv,

      wa_fieldcat LIKE LINE OF i_fieldcat.

DATA: g_end_of_data  TYPE rs_bool,

      g_first_call   TYPE rs_bool.

SELECTION-SCREEN: BEGIN OF BLOCK a WITH FRAME TITLE text-t01.

SELECT-OPTIONS: s_date FOR sy-datum OBLIGATORY DEFAULT sy-datum .

SELECTION-SCREEN: END OF BLOCK a .

START-OF-SELECTION.

  reportid = sy-repid.

  SELECT tranid

         ruletype

         FROM rstranrule

         INTO TABLE i_rstranrule

         WHERE ruletype IN ('START','END','EXPERT','ROUTINE')

         AND objvers = 'A'.

  SORT i_rstranrule BY tranid

       ruletype .

  DELETE ADJACENT DUPLICATES FROM i_rstranrule COMPARING tranid.

  SELECT tranid

        sourcetype

        sourcename

        targettype

        targetname

        FROM rstran

        INTO TABLE i_rstran

        FOR ALL ENTRIES IN i_rstranrule

        WHERE tranid EQ i_rstranrule-tranid

        AND objvers ='A'.

  SELECT dtp

         src

         tgt

         FROM rsbkdtp

         INTO TABLE i_rsbkdtp

         FOR ALL ENTRIES IN i_rstran

         WHERE src =  i_rstran-sourcename

         AND tgt = i_rstran-targetname

         AND objvers = 'A'.

  REFRESH: i_char,i_kf.

  CLEAR wa_char.

  wa_char-chanm = '0TCTDTPID'.

  wa_char-chaalias = '0TCTDTPID'.

  wa_char-orderby = 0 .

  INSERT wa_char INTO TABLE i_char.

  CLEAR wa_char.

  wa_char-chanm = '0TCTDTPCMD'.

  wa_char-chaalias = '0TCTDTPCMD'.

  wa_char-orderby = 0 .

  INSERT wa_char INTO TABLE i_char.

  CLEAR wa_char.

  wa_char-chanm = '0TCTSOURCE'.

  wa_char-chaalias = '0TCTSOURCE'.

  wa_char-orderby = 0 .

  INSERT wa_char INTO TABLE i_char.

  CLEAR wa_char.

  wa_char-chanm = '0TCTTARGET'.

  wa_char-chaalias = '0TCTTARGET'.

  wa_char-orderby = 0 .

  INSERT wa_char INTO TABLE i_char.

  CLEAR wa_char.

  wa_char-chanm = '0CALDAY'.

  wa_char-chaalias = '0CALDAY'.

  wa_char-orderby = 0 .

  INSERT wa_char INTO TABLE i_char.

  CLEAR wa_char.

  wa_char-chanm = '0TCTREQSID'.

  wa_char-chaalias = '0TCTREQSID'.

  wa_char-orderby = 0 .

  INSERT wa_char INTO TABLE i_char.

  CLEAR wa_kf.

  wa_kf-kyfnm = '0TCTDURTION'.

  wa_kf-kyfalias = '0TCTDURTION'.

  wa_kf-aggr = 'SUM'.

  INSERT wa_kf INTO TABLE i_kf.

  CLEAR wa_kf.

  wa_kf-kyfnm = '0TCTSTRTDAT'.

  wa_kf-kyfalias = '0TCTSTRTDAT'.

  wa_kf-aggr = 'SUM'.

  INSERT wa_kf INTO TABLE i_kf.

  CLEAR wa_kf.

  wa_kf-kyfnm = '0TCTENDDAT'.

  wa_kf-kyfalias = '0TCTENDDAT'.

  wa_kf-aggr = 'SUM'.

  INSERT wa_kf INTO TABLE i_kf.

  REFRESH i_range.

  CLEAR wa_range.

* --- name of the characteristic

  wa_range-chanm    = '0TCTDTPID'.

* --- including or excluding condition ?

  wa_range-sign     = rs_c_range_sign-including.

* --- comparison operator

  wa_range-compop   = rs_c_range_opt-equal.

* --- low value

  LOOP AT i_rsbkdtp INTO wa_rsbkdtp.

    wa_range-low      = wa_rsbkdtp-dtp.

* --- high value

*      wa_range-high     = .

* --- include into list of restrictions

    APPEND wa_range TO i_range.

  ENDLOOP.

  IF s_date IS NOT INITIAL.

    CLEAR wa_range.

* --- name of the characteristic

    wa_range-chanm    = '0CALDAY'.

* --- including or excluding condition ?

    wa_range-sign     = s_date-sign.

* --- comparison operator

    wa_range-compop   = s_date-option.

* --- low value

    wa_range-low      = s_date-low.

* --- high value

    wa_range-high     = s_date-high.

* --- include into list of restrictions

    APPEND wa_range TO i_range.

  ENDIF.

  REFRESH:i_out,i_result1.

* --- this variable will be set to TRUE when the last data

*     package is read

  g_end_of_data = rs_c_false.

* --- this variable indicates whether this is an initial

*     call to the reading module or a follow-up call (which

*     simply retrieves already selected data)

  g_first_call  = rs_c_true.

  WHILE g_end_of_data = rs_c_false.

    CALL FUNCTION 'RSDRI_INFOPROV_READ'

      EXPORTING

        i_infoprov             = '0TCT_C22'

        i_th_sfc               = i_char

        i_th_sfk               = i_kf

        i_t_range              = i_range

*       I_TH_TABLESEL          =

*       I_T_RTIME              =

        i_reference_date       = sy-datum

        i_rollup_only          = rs_c_false

*       I_T_REQUID             =

        i_save_in_table        = rs_c_false

        i_save_in_file         = rs_c_false

        i_packagesize          = 20000

        i_maxrows              = 0

        i_authority_check      = rsdrc_c_authchk-read

*       I_CURRENCY_CONVERSION  = 'X'

        i_use_db_aggregation   = rs_c_false

        i_use_aggregates       = rs_c_false

        i_read_ods_delta       = rs_c_false

        i_caller               = rsdrs_c_caller-rsdri

        i_debug                = rs_c_false

        i_clear                = rs_c_false

        i_commit_allowed       = rs_c_true

        i_with_nls             = rs_c_true

        i_check_results        = rs_c_false

      IMPORTING

        e_t_data               = i_out

        e_end_of_data          = g_end_of_data

      CHANGING

        c_first_call           = g_first_call

      EXCEPTIONS

        illegal_input          = 1

        illegal_input_sfc      = 2

        illegal_input_sfk      = 3

        illegal_input_range    = 4

        illegal_input_tablesel = 5

        no_authorization       = 6

        illegal_download       = 7

        illegal_tablename      = 8

        trans_no_write_mode    = 9

        inherited_error        = 10

        x_message              = 11

        no_commit_free_read    = 12

        OTHERS                 = 13.

    IF sy-subrc <> 0.

*      BREAK-POINT.                                         "#EC NOBREAK

      EXIT.

    ENDIF.

    APPEND LINES OF i_out TO i_result1.

  ENDWHILE.

  CLEAR:wa_result1,wa_result2,wa_result3.

  SORT i_result1 BY 0tctdtpid 0tctdtpcmd 0tctsource 0tcttarget 0calday 0tctreqsid.

*FETCHING TRANSFORMATION DATA

  LOOP AT i_result1 INTO wa_result1 WHERE 0tctdtpcmd = 'TRFN' OR 0tctdtpcmd = ' '.

    wa_result2-0tctdtpid =  wa_result1-0tctdtpid.

    wa_result2-0tctsource = wa_result1-0tctsource.

    wa_result2-0tcttarget = wa_result1-0tcttarget.

    wa_result2-0calday = wa_result1-0calday.

    wa_result2-0tctreqsid = wa_result1-0tctreqsid.

    wa_result2-0tctdurtion = abs( wa_result1-0tctdurtion ).

    wa_result2-0tctstrtdat = wa_result1-0tctstrtdat.

    wa_result2-0tctenddat = wa_result1-0tctenddat.

    COLLECT wa_result2 INTO i_result2.

  ENDLOOP.

* FETCHING COMPLETE DATA AND AGGREGATING IT

  LOOP AT i_result1 INTO wa_result1.

    wa_result3-0tctdtpid =  wa_result1-0tctdtpid.

    wa_result3-0tctsource = wa_result1-0tctsource.

    wa_result3-0tcttarget = wa_result1-0tcttarget.

    wa_result3-0calday = wa_result1-0calday.

    wa_result3-0tctreqsid = wa_result1-0tctreqsid.

    wa_result3-0tctdurtion = abs( wa_result1-0tctdurtion ).

    wa_result3-0tctstrtdat = wa_result1-0tctstrtdat.

    wa_result3-0tctenddat = wa_result1-0tctenddat.

    COLLECT wa_result3 INTO i_result3.

  ENDLOOP.

*FILLING OUTPUT TABLE

  CLEAR:wa_result, wa_result3.

  LOOP AT i_result3 INTO wa_result3.

    wa_result-0tctdtpid =  wa_result3-0tctdtpid.

    wa_result-0tctsource = wa_result3-0tctsource.

    wa_result-0tcttarget = wa_result3-0tcttarget.

    wa_result-0calday = wa_result3-0calday.

    wa_result-0tctreqsid = wa_result3-0tctreqsid.

    wa_result-0tctdurtion = wa_result3-0tctdurtion.

    wa_result-0tctstrtdat = wa_result3-0tctstrtdat.

    wa_result-0tctenddat = wa_result3-0tctenddat.

    CLEAR wa_result2.

    READ TABLE i_result2 INTO wa_result2

    WITH KEY 0tctdtpid = wa_result3-0tctdtpid

              0calday = wa_result3-0calday

              0tctreqsid = wa_result3-0tctreqsid.

    IF sy-subrc EQ 0.

      wa_result-trfndurtion = wa_result2-0tctdurtion.

    ELSE.

      wa_result-trfndurtion = 0.

    ENDIF.

    IF wa_result-0tctdurtion NE 0.

*    CALCULATING PERCENTAGE

      wa_result-trfnpercentage = ( wa_result-trfndurtion / wa_result-0tctdurtion ) * 100 .

    ELSE.

      wa_result-trfnpercentage = 0.

    ENDIF.

    APPEND wa_result TO i_result.

  ENDLOOP.

  SORT i_result DESCENDING BY 0calday trfnpercentage.

  PERFORM display_list.

*&---------------------------------------------------------------------*

*&      Form  DISPLAY_LIST

*&---------------------------------------------------------------------*

*       text

*----------------------------------------------------------------------*

*  -->  p1        text

*  <--  p2        text

*----------------------------------------------------------------------*

FORM display_list .

  DATA: o_alv TYPE REF TO cl_salv_table,

       lx_msg TYPE REF TO cx_salv_msg,

       lo_display TYPE REF TO cl_salv_display_settings,

       lr_columns TYPE REF TO cl_salv_columns_table,

       lr_column  TYPE REF TO cl_salv_column_table.

  TRY.

      cl_salv_table=>factory(

        IMPORTING

          r_salv_table = o_alv

        CHANGING

          t_table      = i_result ).

    CATCH cx_salv_msg INTO lx_msg.

  ENDTRY.

*   get display object

  lo_display = o_alv->get_display_settings( ).

*

*   set ZEBRA pattern

  lo_display->set_striped_pattern( 'X' ).

*

*   Title to ALV

  lo_display->set_list_header( 'ROUTINE STATISTICS' ).

*   Calling method to set the PF-Status

  o_alv->set_screen_status(

    pfstatus      =  'SALV_STANDARD'

    report        =  'SALV_DEMO_TABLE_SELECTIONS'

    set_functions = o_alv->c_functions_all ).

  lr_columns = o_alv->get_columns( ).

  TRY.

      lr_column ?= lr_columns->get_column( '0TCTDTPID' ).

      lr_column->set_long_text( 'DTP ID' ).

      lr_column ?= lr_columns->get_column( '0TCTSOURCE' ).

      lr_column->set_long_text( 'SOURCE NAME' ).

      lr_column ?= lr_columns->get_column( '0TCTTARGET' ).

      lr_column->set_long_text( 'TARGET NAME' ).

      lr_column ?= lr_columns->get_column( '0TCTREQSID' ).

      lr_column->set_long_text( 'REQUEST SID' ).

      lr_column ?= lr_columns->get_column( '0TCTSTRTDAT' ).

      lr_column->set_long_text( 'START DATE' ).

      lr_column ?= lr_columns->get_column( '0TCTENDDAT' ).

      lr_column->set_long_text( 'END DATE' ).

      lr_column ?= lr_columns->get_column( '0TCTDURTION' ).

      lr_column->set_long_text( 'DURATION (Sec)' ).

      lr_column ?= lr_columns->get_column( 'TRFNDURTION' ).

      lr_column->set_long_text( 'TRFN DURATION (Sec)' ).

      lr_column ?= lr_columns->get_column( 'TRFNPERCENTAGE' ).

      lr_column->set_long_text( 'PERCENTAGE (%)' ).

    CATCH cx_salv_not_found.                            "#EC NO_HANDLER

  ENDTRY.

  o_alv->display( ).

ENDFORM.                    " DISPLAY_LIST

Output:

Below is the output which will be obtained after running this code.

In this output, we will have the statistics related to all the routines written in the BW system. Here we will be able to see the total run time of a data load alongside time taken by a routine and their respective percentage. Based on this data we can find the routine having maximum percentage and optimize them.

To report this post you need to login first.

1 Comment

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

Leave a Reply