In our BW projects, we are frequently required to know the “BEx Query last execution” details from BI Statistics. In this blog, I am going to show ” How to deal with this common requirement in a simple manner” 😎
Let’s suppose we require Last Executed date, Last modified date, Last Modified by and last but not least Last Executed user. There is no standard BI Statistics Query to give last executed user details. We need to either hit Statistics Cubes or tables.
The first table which comes to our mind is RSDDSTAT_OLAP. This is basically a view out of following 3 tables below :-
We can find Last Executed User name from this table(below image). But this table can hold data up to specific time limit mentioned in RSADMIN table for Object: TCT_KEEP_OLAP_DM_DATA_N_DAYS. Usually, the value will be 30 days. So we will be able to get Last Executed user details of the BEx Queries which were executed in last 30 days only.
What if our requirement is to get User names who executed 6 months ago 😯 ?
Even the tables RSZCOMPDIR and RSRREPDIR tables do not store Last Executed User details. These tables store info like Last Executed date, Last modified date, Last Modified by etc
This is the situation where we need to hit BI Statistics cubes. If your BI Statistics process chains are being run regularly, then the historical statistics data might got uploaded to Cubes.
Create a simple BEx Query on Cube, Front-End and OLAP Statistics (Details) : 0TCT_C02 like below :
The reason why I have used highlighted Infoobjects in rows pane is, they are Compounding Infoobjects to Query Runtime object.
If we do not use these 3 infoobjects in the Query, then “Query Runtime Object” field will show data separated by / .
To avoid this and show just Query name in the column, we need to use them like above image and choose “No Display” like below. All this is for better visibility in the report.
Drag all the 3 KFs into KF pane from the left side(Cube). This is just to complete the definition. Create a new Formula variable locally in New formula for Latest date. This should be a replacement path variable by Calday.
Since our objective is to show last executed user, we will simply create a Condition on Latest date KF as TOP N = 1. This will show the record which was the latest up to 6 months back(as we have set a filter in query)
To run this Query, you should paste the query technical names in selection screen(right side) like below. If we try to enter in direct input, they will not be found because Query runtime object field expects values in compounded form separated with slashes( / ).
Upon executing the query, you will get a report like below which shows Query Name, Calday and Last executed User(6 months back).
Thanks for reading!!