You can start by identifying the major components of your query time.
This is similar to the query statistics that you see in RSRT but then I have decided to analyze the same using statistics information available as part of standard content.
I have installed the BI content as prescribed in the BI technical content installation and I am on BI 7.0 – The same has not been attempted in 3.x for want of a system.
The OLAP statistics detailed cube has all the necessary information ( also a lot of it – execute your queries on the same with care – it might lead to too much of data to analyze!!! )
This exercise can be done in excel / you can have a query for the same – I did this in excel since I was not sure if any business content query existed for the same.
Here the fields of interest right now are :
- Used Infoprovider
- Query Runtime Object
- Calendar Day ( Do not query for all days – too much of data might lead to overflow on the key figures !!!)
Here it depends on how you want to do the analysis – you can either go by Query or by Infoprovider ( Multiprovider also constitutes an infoprovider – this is the infoprovider on which the query is built )
Enter the query technical name in the Query runtime object and give the necessary filters of Calendar day as indicated below – I have taken only 3 days of data – but then have not found any issues even while taking 3 to 6 months of data – only that the resultset generation is a little slow for my system.
Under fields of selection – select whichever is applicable – Infoprovider / query and also check the following.
The statistics event is key for this analysis…
I have found overflows to occur in Olap Counter and Step when selecting a larger slice of data in terms of days and hence have deselected them. I have used the number of hits as a counter instead.
The output will be something like this – have hidden the actual results … but then going on to what we can do with the results…
We have got a long list of Statistics events – but then what do we do with the same….?
Go to table RSDDSTATEVENTS – this has the description of the statistics events which are displayed above..
Statistics Event| Rowcount | OLAP Time | Average time
Here the Average time is OLAP Time / Rowcount. This is debatable – but then compress the cube fully to get accurate rowcounts. Or use the OLAP counter if you do not get any overflow message.
Now we will need to find the percentage contribution to the total
This again is a simple Excel formula where you take the percentage contribution to total and you have the Events that contribute a major portion of the query time and accordingly look at resolving the same.
We had used this to determine queries / cubes that were candidates for BI Accelerator – but then the same analysis can be used for other purposes also.
The cube contains a lot more detail and a lot more analysis can be done – this blog is to initiate this query analysis and take it further.
The same can be analyzed using RSRT also – but then historical analysis can be done using the exercise mentioned above.
Will post a blog on using RSRT and a query run schedule analysis soon…