Skip to Content
Technical Articles

Info-Provider Record Statistics Tool

Hi All,

I would like to share with you all regarding a BW tool that has been developed by myself. The tool is called as ‘ Info-provider Record Statistics tool’.

Introduction:

The Info-provider Record Statistics tool can be used for analyzing the key statistics in a Data – provider (Infocube, DSO, Master Data and DataSource). The tool determines the following statistics when we provide the technical name of the desired data provider as an input. The metrics determined are as follows:

 

  • Info-Area and Description (where the Info-provider resides)
  • Info-provider and Description
  • Total number of records (Combined Number of Records in F-fact & E-fact table in Infocube or Active Data table in case of DSO),
  • Total number of Request IDs,
  • Date & Time of the update of First Request ID
  • Date & Time of the update of Last/Latest Request ID.

 

The program makes use of back-end tables RSSTATMANPART and RSMDATASTATE_EXT along with various text tables (viz. RSDCUBET, RSDODSOT, RSDIOBJT, etc.) for data-providers to output the meaningful explanation of desired statistics.

Scenarios & Tool Usage:

There are various scenarios wherein you have to check when the Info-provider was first loaded with the data, the total number or records updated so far, when the Info-provider was last updated, etc. If you know the Info-provider name, it can be provided as an input to this tool and the tool will yield the necessary statistics. If your Info-provider is an SPO (Semantically Partitioned Object), then please provide the main SPO name followed by asterik (as provided in the screenshot below).

Advantages:

  • This program can be installed in any SAP-BW system to determine the statistics.
  • If any input is not provided and executed directly, all the data-targets will be displayed along the aforementioned statistics.
  • This program does not impact the performance of the system.

Conclusion:

The Info-provider Record Statistics tool can therefore be used to find the key statistics of the relevant Info-provider which you are giving as an input. Otherwise, all Info-provider statistics can be displayed if you do not provide any input. This program can be deployed in Development environment and then can be moved to all landscapes until Quality, Pre-production and Production systems, to find the Info-provider statistics. This program can be customized as per your requirement as well. This tool helps to find the statistics hassle-free without dwelving deep into the system. As aforementioned, the statistics for all the Info-providers can also be seen and downloaded in Excel for analysis purpose too. Hope it helps in your daily day-to-day work of analysis in SAP-BW system.

 

Example Screenshots:

 

Input Screen:

 

 

Output Screen:

 

(both screens viewed together as output)

 

 

Program:

============================================================================

REPORT ZREQ2.

TABLES: rsmdatastate_ext, rsstatmanpart, rsdcube, rsdodso, rsdcubet, rsdodsot, rsdiobjt, rsdst.

TYPES: BEGIN OF ty_rec_dp,
dta TYPE rsmdatastate_ext-dta,
dta_type TYPE rsmdatastate_ext-dta_type,
records_all TYPE rsmdatastate_ext-records_all,
requests_all TYPE rsmdatastate_ext-requests_all,
ts_qual_changed TYPE rsmdatastate_ext-ts_qual_changed,
END OF ty_rec_dp.

DATA: it_rec_dp TYPE TABLE OF ty_rec_dp,
wa_rec_dp TYPE ty_rec_dp.

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
SELECT-OPTIONS: target FOR rsdcube-infocube NO INTERVALS.
*select-options: target_o for rsdodso-odsobject no intervals.
*parameter: target TYPE C LENGTH 15.
SELECTION-SCREEN END OF BLOCK b1.

SELECT dta dta_type records_all requests_all ts_qual_changed FROM rsmdatastate_ext INTO TABLE it_rec_dp WHERE dta IN target.

TYPES: BEGIN OF ty_rec_dr,
dta TYPE rsstatmanpart-dta,
dta_type TYPE rsstatmanpart-dta_type,
datum_verb TYPE rsstatmanpart-datum_verb,
zeit_verb TYPE rsstatmanpart-zeit_verb,
END OF ty_rec_dr.

DATA: it_rec_dr TYPE TABLE OF ty_rec_dr,
wa_rec_dr TYPE ty_rec_dr.

TYPES: BEGIN OF ty_rec1,
infoarea TYPE rsdcube-infoarea,
infoareat TYPE rsdareat-txtlg,
dta TYPE rsdcubet-infocube,
txtlg TYPE c LENGTH 50,
dta_type TYPE rsmdatastate_ext-dta_type,
records_all TYPE rsmdatastate_ext-records_all,
requests_all TYPE rsmdatastate_ext-requests_all,
ts_qual_changed TYPE rsmdatastate_ext-ts_qual_changed,
ts_qual_date TYPE sy-datum,
ts_qual_time TYPE sy-uzeit,
ts_first_date TYPE sy-datum,
ts_first_time TYPE sy-uzeit,
END OF ty_rec1.

DATA: itab TYPE TABLE OF ty_rec1,
wtab LIKE LINE OF itab.

DATA : it_field_cat TYPE slis_t_fieldcat_alv ,
wa_field_cat TYPE slis_fieldcat_alv.

DATA: lc_timestamp TYPE timestamp,
resolve_time TYPE sy-zonlo,
lv_date TYPE sy-datum,
lv_time TYPE sy-uzeit.

TYPES: BEGIN OF ty_rec_ct,
infocube TYPE rsdcubet-infocube,
objvers TYPE rsdcubet-objvers,
txtlg TYPE rsdcubet-txtlg,
END OF ty_rec_ct.

DATA: it_rec_ct TYPE TABLE OF ty_rec_ct,
wa_rec_ct TYPE ty_rec_ct.

SELECT infocube objvers txtlg FROM rsdcubet INTO TABLE it_rec_ct WHERE objvers = ‘A’.

TYPES: BEGIN OF ty_ia_cube,
infocube TYPE rsdcube-infocube,
objvers TYPE rsdcube-objvers,
infoarea TYPE rsdcube-infoarea,
END OF ty_ia_cube.

DATA: it_ia_cube TYPE TABLE OF ty_ia_cube,
wa_ia_cube TYPE ty_ia_cube.

SELECT infocube objvers infoarea FROM rsdcube INTO TABLE it_ia_cube WHERE objvers = ‘A’.

TYPES: BEGIN OF ty_ia_dso,
odsobject TYPE rsdodso-odsobject,
objvers TYPE rsdodso-objvers,
infoarea TYPE rsdodso-infoarea,
END OF ty_ia_dso.

DATA: it_ia_dso TYPE TABLE OF ty_ia_dso,
wa_ia_dso TYPE ty_ia_dso.

SELECT odsobject objvers infoarea FROM rsdodso INTO TABLE it_ia_dso WHERE objvers = ‘A’.

TYPES: BEGIN OF ty_rec_dt,
odsobject TYPE rsdodsot-odsobject,
objvers TYPE rsdodsot-objvers,
txtlg TYPE rsdodsot-txtlg,
END OF ty_rec_dt.

DATA: it_rec_dt TYPE TABLE OF ty_rec_dt,
wa_rec_dt TYPE ty_rec_dt.

SELECT odsobject objvers txtlg FROM rsdodsot INTO TABLE it_rec_dt WHERE objvers = ‘A’.

TYPES: BEGIN OF ty_rec_it,
iobjnm TYPE rsdiobjt-iobjnm,
objvers TYPE rsdiobjt-objvers,
txtlg TYPE rsdiobjt-txtlg,
END OF ty_rec_it.

DATA: it_rec_it TYPE TABLE OF ty_rec_it,
wa_rec_it TYPE ty_rec_it.

SELECT iobjnm objvers txtlg FROM rsdiobjt INTO TABLE it_rec_it WHERE objvers = ‘A’.

TYPES: BEGIN OF ty_rec_dst,
datasource TYPE rsdst-datasource,
objvers TYPE rsdst-objvers,
txtlg TYPE rsdst-txtlg,
END OF ty_rec_dst.

DATA: it_rec_dst TYPE TABLE OF ty_rec_dst,
wa_rec_dst TYPE ty_rec_dst.

SELECT datasource objvers txtlg FROM rsdst INTO TABLE it_rec_dst WHERE objvers = ‘A’.

TYPES: BEGIN OF ty_rec_iat,
infoarea TYPE rsdareat-infoarea,
objvers TYPE rsdareat-objvers,
txtlg TYPE rsdareat-txtlg,
END OF ty_rec_iat.

DATA: it_rec_iat TYPE TABLE OF ty_rec_iat,
wa_rec_iat TYPE ty_rec_iat.

SELECT infoarea objvers txtlg FROM rsdareat INTO TABLE it_rec_iat WHERE objvers = ‘A’.

DATA: flext_fin(50) TYPE c,
flext(2) TYPE n.

LOOP AT it_rec_dp INTO wa_rec_dp.

* CONDENSE lv_temp NO-GAPS.
* CONCATENATE lv_temp+0(4) lv_temp+4(2) lv_temp+6(2) INTO lv_date.
* CONCATENATE lv_temp+8(2) lv_temp+10(2) lv_temp+12(2) INTO lv_time.

wtab-dta = wa_rec_dp-dta.
wtab-dta_type = wa_rec_dp-dta_type.
wtab-records_all = wa_rec_dp-records_all.
wtab-requests_all = wa_rec_dp-requests_all.
wtab-ts_qual_changed = wa_rec_dp-ts_qual_changed.
lc_timestamp = wtab-ts_qual_changed.
CONVERT TIME STAMP lc_timestamp TIME ZONE resolve_time INTO DATE lv_date TIME lv_time.
wtab-ts_qual_date = lv_date.
wtab-ts_qual_time = lv_time.
flext = strlen( wa_rec_dp-dta ).
flext = flext – 2.
flext_fin = wa_rec_dp-dta(flext).
SELECT dta dta_type datum_verb zeit_verb FROM rsstatmanpart INTO TABLE it_rec_dr WHERE dta = wa_rec_dp-dta OR dta = flext_fin.

SORT it_rec_dr ASCENDING BY datum_verb zeit_verb.
READ TABLE it_rec_dr INTO wa_rec_dr INDEX 1.
IF sy-subrc = 0.
wtab-ts_first_date = wa_rec_dr-datum_verb.
wtab-ts_first_time = wa_rec_dr-zeit_verb.
CLEAR wa_rec_dr.
ENDIF.
* SORT it_rec_dr DESCENDING BY DATUM_VERB ZEIT_VERB.
* read table it_rec_dr INTO wa_rec_dr INDEX 1.
* if sy-subrc = 0.
* wtab-ts_qual_date = wa_rec_dr-DATUM_VERB.
* wtab-ts_qual_time = wa_rec_dr-ZEIT_VERB.
* clear wa_rec_dr.
* endif.

* IF wtab-dta_type = ‘CUBE’.
READ TABLE it_ia_cube INTO wa_ia_cube WITH KEY infocube = wa_rec_dp-dta.
IF sy-subrc = 0.
wtab-infoarea = wa_ia_cube-infoarea.
ENDIF.

READ TABLE it_ia_dso INTO wa_ia_dso WITH KEY ODSOBJECT = wa_rec_dp-dta.
IF sy-subrc = 0.
wtab-infoarea = wa_ia_dso-infoarea.
ENDIF.
* ENDIF.

READ TABLE it_rec_iat INTO wa_rec_iat WITH KEY INFOAREA = wtab-infoarea.
IF sy-subrc = 0.
wtab-infoareat = wa_rec_iat-txtlg.
ENDIF.

* READ TABLE it_rec_iat INTO wa_rec_iat WITH KEY ODSOBJECT = wa_rec_dp-dta.
* IF sy-subrc = 0.
* wtab-infoareat = wa_rec_iat-txtlg.
* ENDIF.

IF wtab-dta_type = ‘CUBE’.
READ TABLE it_rec_ct INTO wa_rec_ct WITH KEY infocube = wa_rec_dp-dta.
IF sy-subrc = 0.
wtab-txtlg = wa_rec_ct-txtlg.
ENDIF.
ELSEIF wtab-dta_type = ‘ODSO’.
READ TABLE it_rec_dt INTO wa_rec_dt WITH KEY odsobject = wa_rec_dp-dta.
IF sy-subrc = 0.
wtab-txtlg = wa_rec_dt-txtlg.
ENDIF.
ELSEIF wtab-dta_type = ‘DTASRC’.
READ TABLE it_rec_dst INTO wa_rec_dst WITH KEY datasource = wa_rec_dp-dta.
IF sy-subrc = 0.
wtab-txtlg = wa_rec_dst-txtlg.
ENDIF.

ELSEIF wtab-dta_type = ‘FLEX_M’.
READ TABLE it_rec_it INTO wa_rec_it WITH KEY iobjnm = wa_rec_dp-dta.
IF sy-subrc = 0.
wtab-txtlg = wa_rec_it-txtlg.
ENDIF.
ELSEIF wtab-dta_type = ‘FLEX_T’.
READ TABLE it_rec_it INTO wa_rec_it WITH KEY iobjnm = flext_fin.
IF sy-subrc = 0.
wtab-txtlg = wa_rec_it-txtlg.
ENDIF.
ENDIF.
APPEND wtab TO itab.
CLEAR wtab.
ENDLOOP.

SORT itab BY infoarea dta_type dta.

PERFORM build_fieldcat.
PERFORM display.

*&———————————————————————*
*& Form build_fieldcat
*&———————————————————————*
* text
*———————————————————————-*
FORM build_fieldcat .
* clear wa_field_cat.

wa_field_cat-reptext_ddic = ‘InfoArea’.
wa_field_cat-fieldname = ‘INFOAREA’.
wa_field_cat-tabname = ‘ITAB’.
* wa_field_cat-key = ‘X’.
* wa_field_cat-hotspot = ‘X’.
wa_field_cat-outputlen = 15.
APPEND wa_field_cat TO it_field_cat.
CLEAR wa_field_cat .

wa_field_cat-reptext_ddic = ‘InfoArea Text’.
wa_field_cat-fieldname = ‘INFOAREAT’.
wa_field_cat-tabname = ‘ITAB’.
* wa_field_cat-key = ‘X’.
* wa_field_cat-hotspot = ‘X’.
wa_field_cat-outputlen = 20.
APPEND wa_field_cat TO it_field_cat.
CLEAR wa_field_cat .

wa_field_cat-reptext_ddic = ‘Data Target’.
wa_field_cat-fieldname = ‘DTA’.
wa_field_cat-tabname = ‘ITAB’.
* wa_field_cat-key = ‘X’.
* wa_field_cat-hotspot = ‘X’.
wa_field_cat-outputlen = 15.
APPEND wa_field_cat TO it_field_cat.
CLEAR wa_field_cat .

wa_field_cat-reptext_ddic = ‘Data Target Name’.
wa_field_cat-fieldname = ‘TXTLG’.
wa_field_cat-tabname = ‘ITAB’.
wa_field_cat-outputlen = 60.
APPEND wa_field_cat TO it_field_cat.

wa_field_cat-reptext_ddic = ‘Data Target Type’.
wa_field_cat-fieldname = ‘DTA_TYPE’.
wa_field_cat-tabname = ‘ITAB’.
wa_field_cat-outputlen = 15.
APPEND wa_field_cat TO it_field_cat.
* clear wa_field_cat .

wa_field_cat-reptext_ddic = ‘Number of Records’.
wa_field_cat-fieldname = ‘RECORDS_ALL’.
wa_field_cat-tabname = ‘ITAB’.
wa_field_cat-outputlen = 20.
APPEND wa_field_cat TO it_field_cat.

wa_field_cat-reptext_ddic = ‘Number of Requests’.
wa_field_cat-fieldname = ‘REQUESTS_ALL’.
wa_field_cat-tabname = ‘ITAB’.
wa_field_cat-outputlen = 20.
APPEND wa_field_cat TO it_field_cat.

wa_field_cat-reptext_ddic = ‘First Request Update Date’.
wa_field_cat-fieldname = ‘TS_FIRST_DATE’.
wa_field_cat-tabname = ‘ITAB’.
wa_field_cat-outputlen = 20.
APPEND wa_field_cat TO it_field_cat.

wa_field_cat-reptext_ddic = ‘First Request Update Time’.
wa_field_cat-fieldname = ‘TS_FIRST_TIME’.
wa_field_cat-tabname = ‘ITAB’.
wa_field_cat-outputlen = 20.
APPEND wa_field_cat TO it_field_cat.

wa_field_cat-reptext_ddic = ‘Last Request Updated Date’.
wa_field_cat-fieldname = ‘TS_QUAL_DATE’.
wa_field_cat-tabname = ‘ITAB’.
wa_field_cat-outputlen = 20.
APPEND wa_field_cat TO it_field_cat.

wa_field_cat-reptext_ddic = ‘Last Request Updated Time’.
wa_field_cat-fieldname = ‘TS_QUAL_TIME’.
wa_field_cat-tabname = ‘ITAB’.
wa_field_cat-outputlen = 20.
APPEND wa_field_cat TO it_field_cat.

ENDFORM. “build_fieldcat

*&———————————————————————*
*& Form display
*&———————————————————————*
* text
*———————————————————————-*
FORM display .
CALL FUNCTION ‘REUSE_ALV_GRID_DISPLAY’
EXPORTING
i_callback_program = sy-repid
i_callback_user_command = ‘USER_COMMAND’
it_fieldcat = it_field_cat
i_default = ‘X’
i_save = ‘ ‘
TABLES
t_outtab = itab
EXCEPTIONS
program_error = 1
OTHERS = 2.
ENDFORM. “display

 

============================================================================

2 Comments
You must be Logged on to comment or reply to a post.
  • Hi, thanks for sharing this. An overview like this is really useful.
    Unfortunately, on my BW 7.4 (SP09) system the numbers for DSO and IOBJ’s are way out (I have not checked Cubes yet).

    To give you an idea of the magnitude of the error:

    Total number of records in Active table:

    DSO: Statistics Tool – 92.382.970 records. SE16: 86.902
    IOBJ: Statistics Tool – 21.034.542.836 records. SE16: 18.772.876

    I don’t know what the ‘number of records’ column represents, but certainly not the records in the Active table. Perhaps it is the total number of records loaded to target, but ignores the fact that records with the same key are updated, rather than inserted? (That is just a wild guess though).

    Jan.

     

     

    • Hi Jan,

       

      Thanks for using this tool in your system and providing your feedback.

      Are you checking the statistics in one DSO? Has your DSO undergone Selective Deletion and NLS Archiving so far?

       

      Thanks,

      Roeas Bassam K.