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.

Comments

Post a Comment

Popular posts from this blog

Sample ABAP program for Updating notepad file data to Internal table and Sending it to Application server

 *--Structure type TYPES: BEGIN OF TY_STD, RONO TYPE C LENGTH 4, NAME1 TYPE C LENGTH 20, GEN TYPE C LENGTH 6, FEES TYPE C LENGTH 10. END OF TY_STD. *--Header line and internal table DATA WKA TYPE TY_STD. DATA INT_TAB TYPE TABLE OF TY_STD. *--Application server files are known as "Datasets" *--here "fname" is the Dataset DATA FNAME TYPE C LENGTH 40 VALUE "StudentsInfo.txt". *--Convert notepad file data into internal table CALL FUNCTION 'GUI_UPLOAD' EXPORTING FILENAME = 'C:Documents and SettingsAdministratorDesktopstudents.txt' HAS FIELD_SEPARATOR = 'X' TABLES DATA_TAB = INT_TAB. *--Sending data to Application Server OPEN DATASET FNAME FOR OUTPUT IN TEXT MODE ENCODING DEFAULT. LOOP AT INT_TAB INTO WKA. TRANSFER WKA TO FNAME. ENDLOOP. CLOSE DATASET FNAME.
Read more

OPEN SQL EXAMPLES IN ABAP – PART 2

 ******************************************* **displaying Raw Materials and Finished products **only from MARA table. DATA WA TYPE MARA. * **--WHERE clause is for filtering the records SELECT * FROM MARA INTO WA WHERE MTART = 'ROH' OR MTART = 'FERT'. WRITE: / WA-MATNR, WA-MBRSH, WA-MTART, WA-MEINS. ENDSELECT. * **************************************************
Read more

Domains and Data Elements

  Domains A domain provided Technical attributes (type, length and possible values) for a field. The definition of domain in ABAP is same as that in Mathematics. In the world of Mathematics, a domain is defined as the set of input values that are valid for a given function. The same holds true for domains in ABAP. To understand this, let us first see what is the role of domains in ABAP data dictionary. Domains -> Data Elements -> (Structures, DB tables, program references etc.)
Read more