How many times a Query being accessed

[Former Member]

Hi,

We have few cubes in our system and each one with 'hell' lot of queries. I'm trying to identify which of these queries are used most often so that I can delete the rest.

Is there any way where I could find like.. say for a particular cube, how many times a query is being accessed?

I'm trying to dig into Technical Content cubes/queries but couldn't find much.

Would appreciate if someone helps me in this..

Regards,

Vikrant.

[sachin_kulshrestha2 · Accepted solution]

hi,

we have bw statistics as inforprovider in which u can look for the query which is being accessed how many times

look for 0BWTC_C02 cube to get the desired result

[Former Member]

Hi Pizzaman,

Thx, that was quite usefull. I've assigned all of you points.

Thx once again,

Regards,

Vikrant.

[Former Member]

Pizaman

1. ST03N is not as you say.

I have looked at ST03N and the info is not relevant

for understanding the relationship between Query and runtime, it is more of a Basis backend tool.

please correct me if I am wrong.

2. RSDDSTAT is not captoring all Query run's.

I have run Query's and then checked the table and they where missing. Also, BW Stats are based on RSDDSTAT so I assume the data there is problemtic too.

any idea why is that?

Reg's

Edan

[Former Member]

Please make sure you are in the <b>EXPERT MODE</b> in STO3N, then you will be able to see all the info Pizzaman had mentioned.

Thanks.

[Former Member]

ST03N in Expert Mode has it. No access right now to check for sure what the item is called. Down the left side should be something like Warehouse Load or something similar. From there you have the last minute, today, yeserday, this week, last week etc. options.

I'll admit it is NOT intuitive. Keep looking, but I'll post more detail when I have a screen in front of me to look at. Probably wll post a screen shot somewhere with a link from the forum post. Sure wish we could post a graphic here....

As far as missing stats - have you doublechecked that the InfoProviders are checked under the BE Statistics for InfoProviders I mentioned earlier in the thread. If that is not checked you wouldn't any queries for a given InfoProvider. MultiProviders must also be checked to gather stats on them. Are you missing stats on just a particular query but have them for other queries on the same InfoProvider? Not aware of any problems with items missing from RSDDSTAT.

[Former Member]

ST03N

In upper left corner is a little hierarchy type of icon. If you click on it, you have three options, Administrator, Expert Mode, and Service Engineer. Select Expert Mode.

In Expert Mode, down the left side navigation area, you should have a BW System Load entry. If you expand that, you have Last Minute's Load, Day, Week, Month options.

Select one of those, (response may take a few minutes as it may have to go read if large volume of data) and you should get a listing by InfoProvider of the number of navigations and several statistical KFs, such as toal and avg query time, DB time, rows selected from the DB, rows transferred to the OLAP processor, etc.

If you then doubleclick on the InfoProvider, or open the Aggregation drop Down, you can get the same detail for each query associated with the InfoProvider. SO you cna now see the number of navigations and resource consumption for each query. As of SP22 you can also breakout queries between whether the used OLAP Cache or not.

ST03N also provide info on aggregate rollup/rebuild times.

What veriosn of BW are you on. It's possible that older versions do not provide this info in ST03N. We're at 3.1 SP22 currently, and I'm sure it has been there in for the last several SPs.

[Former Member]

Ok, I can see that now.

The problem is that this also seems to be based on RSDDSTAT table and I have noticed that it does not capture all the reports that have been run in the system.

Do you know anything about this?

Edan

[Former Member]

Exactly what is RSDDSTAT missing?

If it is missing everything for a particular InfoProvider, have you checked that that InfoProvider is checked under the "BW Statistics for InfoProvider" setting.

Are you just missing selected queries on a InfoProvider?

Are they Web or Excel based queries?

Are they queries against a MultiProvider?

Message was edited by: Pizzaman

What version / SP are we talking about?

[Former Member]

Its for both Web and Bex.

Its of Infoset and of ODS.

BW 3.0B SP 16

[Former Member]

What about -

If it is missing everything for a particular InfoProvider, have you checked that that InfoProvider is checked under the "BW Statistics for InfoProvider" setting.

Are you just missing selected queries on a InfoProvider?

Or are you missing just sporadic execetions - that is, Query A has stats for 8 of 10 executions.

[Former Member]

Hi,

Thx for the response.

But I could not understand the meaning of fields in RSDDSTAT table.

I was expecting a single record for each query in this table.. but there are multiple entries for each query. How should I extract info like.. particular query is run for say 100 times or 200 times..

Regards,

Vikrant.

[Former Member]

There is only one record for each execution of the query. To clarify, a user runs a query and gets the initial result - that generates a record to RSDDSTAT. The user then drills down on a characteristic - that is another execution of the query, and another row added to RSDDSTAT. They then filter on a certain Business Area, that results in another entry to RSDDSTAT.

Don't know of any doc on RSDDSTAT. If you becomes familiar with the data presented in ST03N or the data in the BW Statistics cubes, it will probably help you understand what you see in RSDDSTAT.

As far as missing stats, from the RSA1 screen under Tools there should should be an item "BW Statistics for InfoProviders". Follow that and check - each InfoProvider has two checkbox options - one to turn on query statistics collection and the other to turn on load statistics (WHM) collection. Check and see if all your InfoProviders are checked.

Help info on the BW Statistics MultiProvider:

http://help.sap.com/saphelp_bw33/helpdata/en/d1/e81c3b85e6e939e10000000a11402f/frameset.htm

[Former Member]

As others have mentioned -

ST03N - in Expert Modes provides summarized info about query executions for:

today, yesterday, 2 days ago

this week, last week, 2 weeks ago

this month, last month, 2 months ago.

This info is summarized from the RSDDSTAT tables. It provides lots of performance metrics, e.g. rows slected, DB time, etc.

It is the quickest way to see what's going on.

RSDDSTAT lets you see the individual query info. Very nice when you have a couple wide open runs that run a long time and distort the averages.

BW Statistics - OLAP in the Tech Content cube also provides this info.

I presume you don't really just intend to delete queries just because they are not run very often - if they are never run, well that's a different story.

The query stats will help you decide which ones warrant further examination as far tuning, aggregates, etc.

[Former Member]

Hi,

U can check the query statistics by using the T-code ST03N.

Also..

RSDDSTAT will show query history info, who ran it, cube and query info, time, etc.

Hope this helps-

Regards-

MM

Message was edited by: vishnuC

[Former Member]

Hi,

I heared that The solution manager will support to find out the regularly used queries and transactions.

With rgds,

Anil Kumar Sharma .P