Front End Query Runtime Statistics - Using The Database Tables Or The Predefined Views

[DebjitSingha]

Update - BW/4HANA:

For BW/4HANA please refer below link. CDS view are used to deliver statistics.

• Statistical Analyses of the Data Warehouse Infrastructure


• Statistics for Data Loading


• Data Volume Statistics


• Process Chain Statistics


• Query Runtime Statistics


• Aggregation of Query Runtime Statistics

 

Objective:

Every once in a while we come across a situation where we need to evaluate the importance of BEx queries available in BW systems. Out of hundreds of BEx queries we need to identify queries that we can decommission from BW system. We need to gather real time stats : “user vs. query used”.

We can gather all these details via technical contents delivered by SAP. But these content needed to be installed. What if BI Statistic technical content is not yet installed and we need to address below mentioned questions:

• Who is using what BEx query?


• What are the top N BEx queries executed in as specific time period?

BW system have some tables and views that are even used by BW delivered technical contents. In this article we will go through some standard views and tables that will help us gather some BEx query related statistic information.

Introduction:

To analyze the statistics data, you have the following options:

Using the database tables or the predefined views

Using the technical content

BI Statics technical content DataSource:0TCT_DSA1 OLAP and cube: 0TCT_CA1  gives aggregated details of the query runtime statistics of the system.

Standard extractors extracts statistic related information from:

• BW 3.5                  :               RSDDSTAT (Table)


• BI 7.0                    :               RSDDSTAT_OLAP (View)

Tables included in view RSDDSTAT_OLAP are:

• RSDDSTATHEADER


• RSDDSTATINFO


• RSDDSTATEVDATA

BW extractor extract data directly from the table STARTTIME. RSDDSTAT_OLAP is a view built on the table RSDDSTATHEADER, RSDDSTATINFO and, RSDDSTATEVDATA. We will use RSDDSTAT_OLAP view to gather all BEx related information and address 2 queries mentioned on top.

Note:

BW Statistic cubes also comes with virtual cube as well (that reads real-time data from RSDDSTAT_OLAP) and Database table ‘ROOSGENDLM’ stores the delta pointer for each executed DataSource, which in turn checks field STARTTIME in RSDDSTAT_OLAP

If real time statistic are not populating in Statistic tables then we need to collect the statistics of the BI application or BI application object, you must set the BI statistics for BI objects to ON. This setting must be made in transaction RSDDSTAT.

There are various ways to gather detials:

• Gather statistic by specific period/date                      :               use field RSDDSTAT_OLAP-CALDAY or RSDDSTAT_OLAP-STARTTIME


• By User                                                                  :               use field RSDDSTAT_OLAP-UNAME


• By Infoprovider                                                         :               use field RSDDSTAT_OLAP-INFOPROV


• By Query                                                                :               use field RSDDSTAT_OLAP-OBJNAME , use technical name of the query.

Query Counter:

Events 3100 (OLAP: Read Data) and 3010 (OLAP: Generate Query) being executed in a session for a query handler. That means a query execution is associated with 2 events :

1. OLAP: Read Data


2. Generate Query

So in most of the cases single query execution will result in generating 2 records in statistic tables (one with Event 3010 and other with 3100). But in some situation query creates multiple Read data (3100) events but one Generate Query event. We can consider “Generate Query (3010)” event for query execution count.

Snapshot below shows example of query- ZFIGL_M01_Q0027 executed 3 times:



Fields Analysis:

• HANDLETP  (Internal Type of Query Runtime Object)      :               OLAP for BEx queries, F4 for help while executing query


• EVENTID (OLAP Statistics Event (Type NUMC9))                :               Events 3100 (OLAP: Read Data) and 3010 (OLAP: Generate Query)


• UNAME (User Name)                                                                    :               Who is executing query.


• STEPTP (OLAP Statistics: Step Type (ODBO, BEX, ...))        :               JAVA (means that front end toll using JAVA based connection eg. Tool like Analysis for MS Office) and BEX (when query executed via BEx Analyzer).


• CALDAY (BW Statistics: Day)                                                        :               date for which we want to collect Query statistics.


• RUNTIME (Validity Period)                                                           :               Duration of a step in seconds. Summation of time taken by read event and query generate event gives the total execution time of BEx query.


• STATLEVEL(Detail Level of OLAP Statistics Recording)     :               0 (Aggregated Data), 1 (OLAP Data), 2 (OLAP and DM Data), defined in RSDDSTT.

If required we can use table USER_ADDR for user details and RSZELTDIR for query definition.

Note: You might see slight delay in stat update process in Statistic tables, so the statistic read process will be near to real time. And time stamp might have some offset as well.

Feel free to leave your comment(s)

***This article shows how to use database tables or predefined views as an alternative to BI Technical Content Instalation.