KPI to calculate query usage

[Former Member]

Hi Experts

I need to find the top ten reports based on maximum usage of query. I came to know that 0TCT_MC01_Q0200 query answers all this questions, In this report time stamp is filtered by 0I_24CTS (Last 24 hours)variable which takes last 24 hrs data only, So please guide me what are all the KPI need to consider to cal calculate the maximum usage, Tell me whether we can find the same in cube 0TCT_C01 and in table RSDDSTAT_OLAP.Even in tables I do know how to calculate.

Kindly Suggest

[anshu_lilhori]

Hi,

I can give you a rough idea to get this information at table level..

Go to table RSDDSTAT_OLAP--Give the query name--and enter the particular date on which you need to find the usage of this query.

Now as you execute the table with this input,then if this query has been run multiple times then in table each time unique session id will be created.

With that you can calculate like in a day how many times that query has been executed..In case you need to narrow down the information based on user id that also you can do by filtering on a particular user id.

Hope this gives an idea.

Regards,

AL

[Former Member]

Hi Anshu,

Is there any other way to find out from the above quey?? Also i need to find out top 10 most usage queries

[former_member182470]

Check my reply..

[former_member182516]

1) To get the No of Users - Create Formula Variable (ZNO_USER)

General Tab:

Processing by:replacement path, Ref Char: 0TCTUSERNM

Replacement path:

Replace with Attribute value: 1ATTRREF

2) Create a CKF(ZCKF_NOU) to calculate no of Users

ZNO_USER *1

This will provide you the no of users using the query.

Now place ZCKF_NOU - Rows

Columns:

0CALDAY

0TCTUSERNM

Create a condition to get top 10 query users.

You can modify the query based on your need. Try to execute and check the out put and modify the query accordingly.

[former_member182470]

Have you tried removing 0I_24CTS variable in your query? That should meet your requirement, right?

The cube you mentioned can also give you that data. Please check

[Former Member]

Hi Suman,

Please see KPI below for the query, from this how will i calculate the query usage??

Is there any formula??

[Former Member]

I like you are querying usage of query

As per me - From backend we can see using ST13 with tool name BW_QUERY_USAGE.

If you need at query level you may use the links & data described in http://scn.sap521

[former_member182470]

Here are my ideas. Just try..

1) Take the RKF : Counter for Query during last 24 hours and copy to a new one and remove Last 24 hrs restriction in it.

2) Create a new CKF by using above RKF and use MAX function. Eg: MAX(RKF)

Hope this will give you the result.