How to know the Query Usage Statistics in BW
The number of times users have seen the BI reports can be analyzed in BW. All of this information is recorded as BW statistics automatically by the system provided the setting is activated for the targets.
The data where the information is stored is the cube 0TCT_C01 [Front-End and OLAP Statistics (Aggregated)].
This cube is SAP standard cube and holds the usage statistics data. This comes under Technical Content info area in BW.
The main info objects in the cube and their functions / meanings are summarized below-
- 0TCTSESUID – Frontend session. This is for session id. A user logs on an opens a BI report, this will be counted as 1 session. The data in this field will be some internally generated alphanumeric character like – 00733D7RN9537BQNVZN3KM13C
- 0TCTSTEPUID – Query (Navigation) Step. This is for step id. A user after opening a BI report can perform navigation or filtering within the same session. For each such step an id is generated and recorded in the cube with the same session id. Thus each session id can have many step ids. The field value of step id is also a system generated set of characters – 00733D7RN954XM4Y15L32KRNS
- 0TCTSTEPCNT – Counter of Steps During Query Execution. The step count here can be any whole number. This doesn’t signify anything and is a system generated number. E.g User did filtering – step count is 4 and then user did some navigation, step count is 16 with a different step id.
- 0TCTBIOBJCT – BI Application. This is basically the query technical name or the web template name.
- 0TCTBISBOBJ – BI Application Object. This holds the query technical name. e.g. if a web template has multiple queries, 0TCTBIOBJCT will be the web template technical name and 0TCTBISBOBJ will be the query technical name under that web template.
- 0TCTBIOTYP – BI Application Type. This is for the BI Application type. E.g. The value is ‘BTMP’ if the BI report is a BEx web template.
- 0TCTBISOTYP – BI Application Object Type. This value will hold the application object type like if it is a query, the value is ‘ELEM’ and if it is an info object, the value is ‘IOBJ’
- 0TCTIFPROV – Used InfoProviders.This is the infoprovider technical name which can be e.g. cube or multiprovider.
- 0TCTIFTYPE – InfoProvider Type. This holds the value of the type of infoprovider. E.g. ‘MPRO’ for multiprovider.
- 0TCTWTCOUNT – Counter for BI Applications. This counter will be 1 if a web template or a query is executed.
- 0TCTQUCOUNT – Counter for BI Application Objects. This counter is incremented whenever a query is executed and also whenever user performs drill down/filtering etc. in the same session. Thus, each step in the same session, increments this counter.
For getting the query usage stats, you can easily build the query on top of the cube 0TCT_C01.
To get the correct query and info provider information – you have to apply 0TCTBISOTYP filter as ‘ELEM’
Rows of the query could have – 0TCTIFPROV to show the info provider name and 0TCTBISBOBJ to show the query technical name.It can also have the characteristic 0TCTBIOTYPE to show the type of the query like BEx web or workbook for example.
Columns of the query could have – 0TCTWTCOUNT to show the number of times the query is executed.
In my query output I get the BI application as blank for the analysis for office reports use.