Google Search - Blog...........

ABAP - Keeping An Eye On Aggregates In Your System.

BACKGROUND

In the BW application environment, we build aggregates on the cubes, so as to improve the performance of the queries based on those cubes.
Aggregates are basically MINI CUBES or SUBSET of a cube, which physically contain data and have the same structure as that of a cube. Aggregates are used to improve the performance of the query by reducing the execution time.

Although, the aggregates are used to improve performance of the queries, maintaining unnecessary aggregates would prove to be an overhead in the system and they also occupy physical space.

Validating the Aggregate usage on a periodic basis is important due to following factors.
1) Unnecessarily creating aggregates within the system, which are not primarily used, also contributes to the slow down of system performance.
2) Unnecessary aggregates occupy physical space.
3) Identifying unused aggregates will help in utilizing the system space more efficiently.
4) Limitation should also be set on the number of aggregates being created, for ideal system performance.
5) User behavior pertaining to the query analysis and navigation may be variable in terms of usage. This may lead to non-usage of certain aggregates over a certain period of time, thereby rendering the aggregate useless.


In such circumstances, the ASTAT tool eases the task of recognition of such aggregates, which are not used for a long time and also affecting the system performance.
The performance of the system can be improved by taking care of the unused aggregates.


PURPOSE:

There are no standard SAP defined programs which provide the aggregate statistics.
There is a standard query defined under the Technical content ? BW Statistics Multiprovider. But this query provides only the 'Number of Navigations' and 'Last Used (Day)' and 'Last Used (Time of Day)', unlike the ASTAT which provides the entire statistical data pertaining the date of roll-up, date of last change, date of last use of the aggregate.


This tool also provides additional information like:
1) Cube name,
2) Aggregate name,
3) User responsible for changing the aggregate last time,
4) Version of the aggregate,
5) Average Factor Reduce.


The basic purpose of this tool is to provide interface to the user, based on which, one can enhance the system performance, by identifying the aggregates which are not being used over a period of time.

Also, it displays the date of roll-up, date of last change, date of last use of the aggregate pertaining to the particular infocube, thereby giving a fair idea of the usage of the aggregate


BENEFITS:

1) This tool gives a quick overview of the statistics pertaining to the aggregate created on the selected cube.
2) As Rolling up of aggregates increases loading time in backend, therefore, a considerable amount of loading time can be saved, if the unused aggregates are identified.
3) By identifying unused aggregates, the aggregates can either be remodeled or can be deleted, thereby leading to more appropriate usage of space.


FEATURES:

The following features are available in this tool:
1) It provides Multiple Cube Selection at the selection screen.
2) It also provides selection between cube ranges.
3) If the date for corresponding fields in the statistics is not available, then, the result display is left blank.


DESCRIPTION:

This program displays the following details
1) Cube Name,
2) Aggregate Number,
3) Last Roll-up date of the Aggregate,
4) Last changed date for the Aggregate,
5) User who last changed the Aggregate,
6) Last Used date for the Aggregate,
7) Aggregate User Id,
8) Average Factor Reduce,
9) Version.



How to create ASTAT in your system?

STEP 1: Go to transaction SE38: ABAP Editor
Select Sub objects: Source Code
Give the name of program and click on the pushbutton 'Create'

STEP 2: Copy the source code given
Click on the icon to 'Check' the code or press (Ctrl+F2).
Save the code.

Click to 'Activate' the code or press (Ctrl+F3).



*&---------------------------------------------------------------------*
*& Report Z_ASTAT_TOOL_VER2 *
*& *
*&---------------------------------------------------------------------*
REPORT z_astat_tool_ver2. .
TYPE-POOLS: rsd, rsdu, slis, rs.
TABLES: rsddaggrdir, rsddaggrt, rsddstataggr.
DATA: agg_d(19) .
DATA: agg_de(19).
DATA: agg_de1(19).
DATA: agg_de2(19).
DATA: agg_dc(40).
DATA: agg_dt TYPE sy-datum.
DATA: agg_dt1 TYPE sy-datum.
DATA: agg_dt2 TYPE sy-datum.
DATA: agg_pmdt TYPE sy-datum.
DATA: agg_ic TYPE rsddaggrdir-infocube.
DATA: agg_ac TYPE rsddaggrdir-aggrcube.
DATA: agg_uid TYPE rsddaggrdir-aggruid.
DATA: agg_afr TYPE rsddaggrdir-avgfactreduce.
DATA: agg_ov TYPE rsddaggrdir-objvers.
DATA: agg_noc TYPE rsddaggrdir-calls.
DATA: agg_lu TYPE rsddaggrdir-lastupdateuser.
DATA: l_cname(40) TYPE c.
DATA: l_aname(40) TYPE c.
DATA: c_tstp(19).
DATA: u_tstp(19).
DATA: c_date TYPE sy-datum.
DATA: u_date TYPE sy-datum.
DATA: c_date1 TYPE sy-datum.
DATA: u_date1 TYPE sy-datum.
DATA: agg_rur TYPE rsddstataggr-timeread.
DATA: agg_rui TYPE rsddstataggr-timeinsert.
DATA: agg_rut TYPE rsddstataggr-timeinsert.
DATA: id TYPE sy-uzeit.
DATA: agg_cc(21) TYPE c.
DATA: r_time TYPE rsddstataggr-timeread.
DATA: h_time TYPE rsddstataggr-timeinsert.
DATA: d_time TYPE rsddstataggr-timeread.
DATA: time TYPE rsddstataggr-timeread.
DATA: time1 TYPE rsddstataggr-timeinsert.
DATA: time2 TYPE rsddstataggr-timeinsert.
DATA: intime TYPE rsddstataggr-timeindex.
DATA: dtime TYPE rsddstataggr-timedbanalyze.
DATA: ctime TYPE rsddstataggr-timecondense.
DATA: itime TYPE rsddstataggr-timeinsert.
DATA: rtime TYPE rsddstataggr-timeread.
DATA: ttime1 TYPE rsddstataggr-timeinsert.
DATA: agg_odt(25) TYPE c.
DATA: s_ntime(16) TYPE c.
DATA: stime(25).
DATA: count LIKE sy-tabix.
DATA: agg_d1(27).
DATA: agg_d2(27).
DATA: agg_1 TYPE rsddstataggr-starttime.
DATA: agg_2 TYPE rsddstataggr-starttime.
DATA: g_repid LIKE sy-repid, "Report Name
x_fieldcat TYPE slis_fieldcat_alv,
t_fieldcat TYPE slis_t_fieldcat_alv.
CONSTANTS: c_version LIKE rsdcube-objvers VALUE 'A',
c_status LIKE rsdcube-objstat VALUE 'ACT'.
*----------------------------------------------------------------------
* TYPE DECLARATION
*----------------------------------------------------------------------
TYPES : BEGIN OF t_agg,
t_cnm TYPE rsddaggrdir-infocube,
t_an TYPE rsddaggrdir-aggrcube,
t_lld TYPE rsddaggrdir-lastupdate,
t_lc TYPE rsddaggrdir-timestmp,
t_lca TYPE rsddaggrdir-lastcall,
t_lcb TYPE rsddaggrdir-lastupdateuser,
t_noc TYPE rsddaggrdir-calls,
t_or TYPE rsddaggrdir-aggruid,
t_rec TYPE rsddaggrdir-avgfactreduce,
t_ver TYPE rsddaggrdir-objvers,
t_dc TYPE rsddaggrt-txtlg,
END OF t_agg.
TYPES : BEGIN OF t_agg1,
t_cnm TYPE rsddaggrdir-infocube,
t_an TYPE rsddaggrdir-aggrcube,
t_lld TYPE sy-datum,
t_lc TYPE sy-datum,
t_lca TYPE sy-datum,
t_lcb TYPE rsddaggrdir-lastupdateuser,
t_noc TYPE rsddaggrdir-calls,
t_or TYPE rsddaggrdir-aggruid,
t_rec TYPE rsddaggrdir-avgfactreduce,
t_ver TYPE rsddaggrdir-objvers,
t_dc TYPE rsddaggrt-txtlg,
t_rut TYPE rsddstataggr-timeread,
t_hacr TYPE rsddstataggr-timeread,
t_drop TYPE rsddstataggr-timeread,
END OF t_agg1.
TYPES: BEGIN OF t_lc,
t_cn TYPE rsdcube-infocube,
t_ld TYPE rsdcubet-txtlg,
END OF t_lc.
TYPES: BEGIN OF t_tab_dt ,
v_cname TYPE rsddaggrdir-infocube,
v_aname TYPE rsddaggrdir-aggrcube,
v_cdate TYPE rsddaggrdir-timestmp,
v_udate TYPE rsddaggrdir-lastcall,
END OF t_tab_dt.
TYPES: BEGIN OF t_tab_ru ,
v_acube TYPE rsddstataggr-aggrcube,
v_adate TYPE rsddstataggr-timeread,
v_pdate TYPE rsddstataggr-timeinsert,
END OF t_tab_ru.
TYPES: BEGIN OF t_new ,
v_icube TYPE rsddstataggr-infocube,
v_acube TYPE rsddstataggr-aggrcube,
v_cmode TYPE rsddstataggr-changemode,
v_sdate TYPE rsddstataggr-starttime,
v_rtime TYPE rsddstataggr-timeread,
v_itime TYPE rsddstataggr-timeinsert,
v_intime TYPE rsddstataggr-timeindex,
v_dtime TYPE rsddstataggr-timedbanalyze,
v_ctime TYPE rsddstataggr-timecondense,
END OF t_new.
************************************************************************
* INTERNAL TABLES *
************************************************************************
DATA : it_agg TYPE STANDARD TABLE OF t_agg WITH HEADER LINE.
DATA : it_agg1 TYPE STANDARD TABLE OF t_agg1 WITH HEADER LINE.
DATA : it_lc TYPE STANDARD TABLE OF t_lc.
DATA : it_dt TYPE STANDARD TABLE OF rsdatrange WITH HEADER LINE.
DATA : it_nd TYPE STANDARD TABLE OF rsintrange WITH HEADER LINE.
DATA : it_ret LIKE ddshretval OCCURS 0 WITH HEADER LINE.
DATA : it_tab_dt TYPE STANDARD TABLE OF t_tab_dt WITH HEADER LINE.
DATA : it_tab_ru TYPE STANDARD TABLE OF t_tab_ru WITH HEADER LINE.
DATA : it_roll TYPE STANDARD TABLE OF t_new WITH HEADER LINE.
DATA : it_hacr TYPE STANDARD TABLE OF t_new WITH HEADER LINE.
DATA : it_drop TYPE STANDARD TABLE OF t_new WITH HEADER LINE.
* internal table used to store infocube values for user selection
DATA: BEGIN OF it_cube OCCURS 0,
infocube LIKE rsdcube-infocube,
desc LIKE rsdcubet-txtlg,
END OF it_cube,
t_return LIKE ddshretval OCCURS 0 WITH HEADER LINE.
DATA: BEGIN OF it_cdate OCCURS 10, "..............19/6/07
v_cdate LIKE rsddaggrdir-timestmp,
* v_ldate LIKE rsddaggrdir-lastcall,
END OF it_cdate.
*----------------------------------------------------------------------
* SELECTION SCREEN
*----------------------------------------------------------------------
SELECTION-SCREEN BEGIN OF BLOCK blka WITH FRAME.
SELECT-OPTIONS p_cube FOR rsddaggrdir-infocube NO INTERVALS.
SELECT-OPTIONS p_cdate FOR sy-datum NO INTERVALS.
SELECT-OPTIONS p_udate FOR sy-datum NO INTERVALS.
SELECTION-SCREEN END OF BLOCK blka.
*&---------------------------------------------------------------------*
* START-OF-SELECTION *
*&---------------------------------------------------------------------*
START-OF-SELECTION.
*******************check when no date is available for change and used******************
IF p_cdate IS INITIAL AND p_udate IS INITIAL.
* Select required fields from table RSDDAGGRDIR
SELECT infocube aggrcube timestmp lastcall
FROM rsddaggrdir
INTO TABLE it_tab_dt
WHERE infocube IN p_cube AND
objvers = 'A'.
* *Filter the data
LOOP AT it_tab_dt.
l_cname = it_tab_dt-v_cname.
l_aname = it_tab_dt-v_aname.
PERFORM get_data.
ENDLOOP.
ELSE.
******************check for change date but not for used date*******************
IF p_cdate IS NOT INITIAL AND p_udate IS INITIAL.
SELECT infocube aggrcube timestmp lastcall
FROM rsddaggrdir
INTO TABLE it_tab_dt
WHERE infocube IN p_cube AND
objvers = 'A'.
LOOP AT it_tab_dt.
c_tstp = it_tab_dt-v_cdate.
c_date = c_tstp+4(8).
c_date1 = p_cdate+3(8).
IF c_date < l_cname =" it_tab_dt-v_cname." l_aname =" it_tab_dt-v_aname." objvers =" 'A'." u_tstp =" it_tab_dt-v_udate." u_date =" u_tstp+4(8)." u_date1 =" p_udate+3(8)." l_cname =" it_tab_dt-v_cname." l_aname =" it_tab_dt-v_aname." objvers =" 'A'." c_tstp =" it_tab_dt-v_cdate." u_tstp =" it_tab_dt-v_udate." c_date =" c_tstp+4(8)." u_date =" u_tstp+4(8)." c_date1 =" p_cdate+3(8)." u_date1 =" p_udate+3(8)." l_cname =" it_tab_dt-v_cname." l_aname =" it_tab_dt-v_aname." aggruid =" b~aggruid" objvers =" b~objvers" aggrcube =" l_aname" infocube =" l_cname" objvers =" 'A'" langu =" 'E'." agg_ic =" it_agg-t_cnm." agg_ac =" it_agg-t_an." agg_dc =" it_agg-t_dc." agg_uid =" it_agg-t_or." agg_lu =" it_agg-t_lcb." agg_afr =" it_agg-t_rec." agg_ov =" it_agg-t_ver." agg_noc =" it_agg-t_noc." agg_de =" it_agg-t_lc." agg_de1 =" it_agg-t_lld." agg_de2 =" it_agg-t_lca." agg_dt =" agg_de+4(8)." agg_dt1 =" agg_de1+4(8)." agg_dt2 =" agg_de2+4(8)." t_cnm =" agg_ic." t_an =" agg_ac." t_dc =" agg_dc." t_rut =" r_time." t_hacr =" h_time." t_drop =" d_time." t_lld =" agg_dt1." t_lc =" agg_dt." t_lcb =" agg_lu." t_noc =" agg_noc." t_lca =" agg_dt2." t_or =" agg_uid." t_rec =" agg_afr." t_ver =" agg_ov." changemode =" 'D'" infocube =" it_agg-t_cnm" aggrcube =" it_agg-t_an." time1 =" wa-v_itime" h_time =" h_time" tabix =" 30." count =" sy-tabix." count =" sy-tabix." h_time =" h_time" h_time =" 0." changemode =" 'R'" changemode =" '" infocube =" it_agg-t_cnm" aggrcube =" it_agg-t_an." time =" wa-v_itime" r_time =" r_time" tabix =" 30." count =" sy-tabix." count =" sy-tabix." r_time =" r_time" r_time =" 0." changemode =" 'N'" infocube =" it_agg-t_cnm" aggrcube =" it_agg-t_an." time2 =" wa-v_itime" d_time =" d_time" tabix =" 30." count =" sy-tabix." count =" sy-tabix." d_time =" d_time" d_time =" 0." r_ucomm =" '&IC1'." fieldname =" 'T_CNM'." infocube =" rs_selfield-value." i_infocube =" infocube" i_objvers =" 'A'" i_action =" 'S'" user_not_authorized =" 1" no_aggr_possible =" 2" others =" 3."> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
ENDIF. " Double click
ENDFORM. "user_command
*&--------------------------------------------------------------------*
*& Form sub_top_of_page *
*&--------------------------------------------------------------------*
* text *
*---------------------------------------------------------------------*
FORM sub_top_of_page.
**Declaration of local Variables
DATA: l_date(10) TYPE c,
l_time(10) TYPE c.
* Declaration of local Internal Tables
DATA: lx_slis_listheader TYPE slis_listheader,
lt_slis_t_listheader TYPE slis_t_listheader.
* Declaration of local constants
CONSTANTS:lc_cstat(50)
TYPE c VALUE 'ASTAT-AGGREGATE STATISTICS TOOL'.
* Write Current date & Time to local variables
WRITE: sy-uzeit TO l_time,
sy-datum TO l_date.
lx_slis_listheader-typ = 'H'.
lx_slis_listheader-info = lc_cstat.
APPEND lx_slis_listheader TO lt_slis_t_listheader.
lx_slis_listheader-typ = 'S'.
lx_slis_listheader-key = 'Date:'(015).
lx_slis_listheader-info = l_date.
APPEND lx_slis_listheader TO lt_slis_t_listheader.
lx_slis_listheader-typ = 'S'.
lx_slis_listheader-key = 'Time:'(016).
lx_slis_listheader-info = l_time.
APPEND lx_slis_listheader TO lt_slis_t_listheader.
* FM to display the TOP-OF-PAGE
CALL FUNCTION 'REUSE_ALV_COMMENTARY_WRITE'
EXPORTING
it_list_commentary = lt_slis_t_listheader.
* I_LOGO =
* I_END_OF_LIST_GRID =
ENDFORM. "sub_top_of_page
*&---------------------------------------------------------------------*
*& Form sub_display_alv_report
*&---------------------------------------------------------------------*
FORM sub_display_alv_report .
g_repid = sy-repid.
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
* I_INTERFACE_CHECK = ' '
* I_BYPASSING_BUFFER = ' '
* I_BUFFER_ACTIVE = ' '
i_callback_program = sy-repid
* I_CALLBACK_PF_STATUS_SET = 'Sub_set_pf'
i_callback_user_command = 'USER_COMMAND'
i_callback_top_of_page = 'SUB_TOP_OF_PAGE' "'sub_top_of_page'
* I_CALLBACK_HTML_TOP_OF_PAGE = ' '
* I_CALLBACK_HTML_END_OF_LIST = ' '
* I_STRUCTURE_NAME =
* I_BACKGROUND_ID = ' '
i_grid_title = 'Aggregate Statistics'
* I_GRID_SETTINGS =
* IS_LAYOUT =
it_fieldcat = t_fieldcat[]
* IT_EXCLUDING =
* IT_SPECIAL_GROUPS =
* IT_SORT =
* IT_FILTER =
* IS_SEL_HIDE =
* I_DEFAULT = 'X'
* I_SAVE = ' '
* IS_VARIANT =
* IT_EVENTS =
* IT_EVENT_EXIT =
* IS_PRINT =
* IS_REPREP_ID =
* I_SCREEN_START_COLUMN = 0
* I_SCREEN_START_LINE = 0
* I_SCREEN_END_COLUMN = 0
* I_SCREEN_END_LINE = 0
* IT_ALV_GRAPHICS =
* IT_HYPERLINK =
* IT_ADD_FIELDCAT =
* IT_EXCEPT_QINFO =
* I_HTML_HEIGHT_TOP =
* I_HTML_HEIGHT_END =
* IMPORTING
* E_EXIT_CAUSED_BY_CALLER =
* ES_EXIT_CAUSED_BY_USER =
TABLES
t_outtab = it_agg1[]
EXCEPTIONS
program_error = 1
OTHERS = 2
.
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
ENDFORM. " sub_display_alv_report
*&---------------------------------------------------------------------*
*& Form sub_build_fieldcatalog
*&---------------------------------------------------------------------*
FORM sub_build_fieldcatalog .
x_fieldcat-fieldname = 'T_CNM'.
x_fieldcat-seltext_m = 'Infocube'.
x_fieldcat-col_pos = 0.
x_fieldcat-key = 'X'.
x_fieldcat-emphasize = 'X'.
x_fieldcat-fix_column = 'X'.
APPEND x_fieldcat TO t_fieldcat.
CLEAR x_fieldcat.
x_fieldcat-fieldname = 'T_AN'.
x_fieldcat-seltext_m = 'Aggregate Cube Name'(002).
x_fieldcat-col_pos = 1.
x_fieldcat-outputlen = 10.
APPEND x_fieldcat TO t_fieldcat.
CLEAR x_fieldcat.
x_fieldcat-fieldname = 'T_DC'.
x_fieldcat-seltext_m = 'Description'(002).
x_fieldcat-col_pos = 1.
x_fieldcat-outputlen = 40.
APPEND x_fieldcat TO t_fieldcat.
CLEAR x_fieldcat.
x_fieldcat-fieldname = 'T_LLD'.
x_fieldcat-seltext_m = 'Last Roll-up'(002).
x_fieldcat-col_pos = 1.
x_fieldcat-outputlen = 26.
APPEND x_fieldcat TO t_fieldcat.
CLEAR x_fieldcat.
x_fieldcat-fieldname = 'T_RUT'.
x_fieldcat-seltext_m = 'AVG Roll-up Time(s)'(002).
x_fieldcat-col_pos = 1.
x_fieldcat-outputlen = 26.
x_fieldcat-just = 'L'.
APPEND x_fieldcat TO t_fieldcat.
CLEAR x_fieldcat.
x_fieldcat-fieldname = 'T_HACR'.
x_fieldcat-seltext_l = 'AVG Delta HACR Time(s)'(002).
x_fieldcat-col_pos = 1.
x_fieldcat-outputlen = 26.
x_fieldcat-just = 'L'.
APPEND x_fieldcat TO t_fieldcat.
CLEAR x_fieldcat.
x_fieldcat-fieldname = 'T_DROP'.
x_fieldcat-seltext_m = 'AVG Rebuild Time(s)'(002).
x_fieldcat-col_pos = 1.
x_fieldcat-outputlen = 26.
x_fieldcat-just = 'L'.
APPEND x_fieldcat TO t_fieldcat.
CLEAR x_fieldcat.
x_fieldcat-fieldname = 'T_LC'.
x_fieldcat-seltext_m = 'Last Changed On'(002).
x_fieldcat-col_pos = 1.
x_fieldcat-outputlen = 26.
APPEND x_fieldcat TO t_fieldcat.
CLEAR x_fieldcat.
x_fieldcat-fieldname = 'T_LCB'.
x_fieldcat-seltext_m = 'Last Changed By'(002).
x_fieldcat-col_pos = 1.
x_fieldcat-outputlen = 26.
APPEND x_fieldcat TO t_fieldcat.
CLEAR x_fieldcat.
x_fieldcat-fieldname = 'T_LCA'.
x_fieldcat-seltext_m = 'Last Used Date'(002).
x_fieldcat-col_pos = 1.
x_fieldcat-outputlen = 26.
APPEND x_fieldcat TO t_fieldcat.
CLEAR x_fieldcat.
x_fieldcat-fieldname = 'T_NOC'.
x_fieldcat-seltext_m = 'Number Of Calls'(002).
x_fieldcat-col_pos = 1.
x_fieldcat-outputlen = 20.
x_fieldcat-just = 'L'.
APPEND x_fieldcat TO t_fieldcat.
CLEAR x_fieldcat.
x_fieldcat-fieldname = 'T_OR'.
x_fieldcat-seltext_m = 'Aggregate UID'(002).
x_fieldcat-col_pos = 1.
x_fieldcat-outputlen = 30.
APPEND x_fieldcat TO t_fieldcat.
CLEAR x_fieldcat.
x_fieldcat-fieldname = 'T_REC'.
x_fieldcat-seltext_m = 'Average Factor Reduce'(002).
x_fieldcat-col_pos = 1.
x_fieldcat-outputlen = 10.
APPEND x_fieldcat TO t_fieldcat.
CLEAR x_fieldcat.
x_fieldcat-fieldname = 'T_VER'.
x_fieldcat-seltext_m = 'Version '(002).
x_fieldcat-col_pos = 1.
x_fieldcat-outputlen = 10.
x_fieldcat-just = 'L'.
APPEND x_fieldcat TO t_fieldcat.
CLEAR x_fieldcat.
ENDFORM. " sub_build_fieldcatalog
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

No comments:

Post a Comment