Our BW systems may have many reports. After sometime some reports lose their importance but it remains in system unnecessarily. To figure out those queries there is a work around. It’s better to create a Report for no. of times a BEx report executed by a user in a period of time. In this blog I am showing how to bring such information into BW reports.
All the Query execution information resides in the table RSDDSTAT_OLAP. Below is the snapshot of the table contents.
The important Fields in this table are,
HANDLETP, UNAME, CALDAY, UTIME, INFOPROV, OBJNAME, STARTTIME.
A generic data source can be created on top of this Table. Where HANDLETP is equal to OLAP for all the BW query execution, so we can restrict our info package selection as HANDLETP=OLAP. STARTTIME is the time stamp of query start time which can be used as generic delta pointer. Other fields can be interpreted as follows,
UNAME = User name, CALDAY = Day on which query executed, UTIME = Time at which query executed, INFOPROV = Infoprovider on which report was created, OBJNAME = Technical name of the Query/report.
This data source can be replicated to BW system and necessary dataflow can be created to store the data in an Infoprovider.
Tips: Add a field ZCOUNTER to Infoprovider and while creating the transformation set this field to constant transformation “1”. This counter can be used for calculating the no. of times a report executed by a user in a specified time( Summation of COUNTER). By adding other characteristics like CALDAY, UTIME in free characteristics it is possible to have various drill downs.
5 Comments
