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:
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
37 | |
10 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
2 | |
2 |