Click here to Skip to main content
Click here to Skip to main content

Tagged as

Retrieving SAP BW Monitor Statistics (Basic Approach)

, 8 Feb 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
A basic approach to retrieve SAP BW statistics displayed in the monitor (/rsmo).
This is an old version of the currently published tip/trick.

Introduction

When a typical SAP-processing system is loading data into warehouse (cubes, PSA and other objects) the state of loading transaction could be seen via SAP Monitor (“/nrsmo”). In this article we present the way how to automate the load-statistics collection from SAP internal tables stored in the original system. Of course this statistics is time-dependent.

Theory

For our purposes we introduce several objects the SAP BW utilizes when collecting data, namely: InfoProvider, InfoPackage, Target and Request. There is a hierarchical dependency between InfoProvider and InfoPackage shown below, but the last one is a key node in loading process which initiates it.

When InfoPackage loading is initiated, the new request is produced which properties are to be retrieved. This process loads records or simply rows into the target that could be multidimensional cube, ODS object or token with attributes. These loadings of rows are internally organized as packets stored in the target object. The participating objects have following properties:

  • InfoProvider: name (textual representation);
  • InfoPackage: log_id (textual identifer), description;
  • Target: name (textual representation), type (cube, token or ODS object);
  • Request: req_id (request number), num_rows (number of records in request already loaded into target), start_load and end_load (the beginning and ending time of loading process), target name, SID (the packet id).

Basic Approach

We describe it first because of its simplicity and fast practical performance. To be complete, it's necessary to describe some SAP-internal tables the statistics information from which are composed:

  • "RSMONICTAB" (Monitor IC table) – contains "RNR", "INFOCUBE" and "TIMESTAMP" fields where RNR – is a textual identifier of the loading process or simply request number, INFOCUBE – a named value of the destination object into which the data are stored, TIMESTAMP – the date and time of the starting point when the request was initiated.
  • "RSMONMESS" (Messages for the monitor) – contains "RNR" (request number) and "TIMESTAMP" fields where TIMESTAMP is a date-time value of the incoming message displayed in the monitor.
  • "RSMONFACT" (Fact table monitor) – contains "RNR", "REQ_SELECT" fields where REQ_SELECT – the number of rows in bucket (processed by the separate thread routine) that is already loaded and thus saved in SAP BW.

In general the Monitor IC table aggregates all the destination target objects by the request in which they are processed (i.e. there could be several targets at once in one request, but not inverse). Now we are able to define the following problem: what is the number of rows loaded into SAP BW for some period of time sorted and groupped by the target object. From all the stated above the solution comes quickly: we need to filter out all the distinct request numbers from the Monitor IC table by the target object and sum all the selected row numbers then join it together with the minimum and maximum of TIMESTAMP from "RSMONMESS". Let's make note that time in SAP internal tables is shifted past by 6 hours, at least, this happens so with my locale. Also it's presented in format "YYYYDDMMHH24MISS".

Example SQL query to retrieve stastics of data loaded into the cube "X_CUBE" for last 2 days:

select
  A.RNR,
  A.NUM_ROWS,
  B.START_LOAD,
  B.END_LOAD
from
  (select RNR, sum(REQ_SELECT) NUM_ROWS
  from sapsr3.RSMONFACT
  where RNR in
    (select distinct RNR from sapsr3.RSMONICTAB where INFOCUBE in
      (
        'X_CUBE' /* Target */
      )
      and to_date(substr(to_char(timestamp), 1, 8), 'yyyymmdd')
        between (sysdate - 3) and (sysdate + 1) /* Time period shifted 1 day past and future */
   )
  group by RNR
  ) A
  inner join
  (select RNR,
        (to_date(min(timestamp), 'YYYYMMDDHH24MISS') + 0.25) START_LOAD,
        (to_date(max(timestamp), 'YYYYMMDDHH24MISS') + 0.25) END_LOAD
  from sapsr3.RSMONMESS
  where RNR in
    (select distinct RNR from sapsr3.RSMONICTAB where INFOCUBE in
      (
        'X_CUBE' /* Target */
      )
      and to_date(substr(to_char(timestamp), 1, 8), 'yyyymmdd')
        between (sysdate - 3) and (sysdate + 1) /* Time period shifted 1 day past and future */
   )
  group by RNR
  ) B
  on A.RNR = B.RNR
where
  B.START_DATE between (sysdate - 2) and sysdate /* Time period */

Conclusion

Hope the information provided was helpful. In the next article we will describe more features of SAP monitoring.

References

  • Oracle PL/SQL reference (OraDoc);
  • BW310, BW330.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

rbtinf

Kazakstan Kazakstan
No Biography provided

Comments and Discussions


Discussions posted for the Published version of this article. Posting a message here will take you to the publicly available article in order to continue your conversation in public.
 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.141022.2 | Last Updated 8 Feb 2012
Article Copyright 2012 by rbtinf
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid