Skip to Content

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.

BI Content Cubes

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!!! )

Data Display

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.

Filters

Fields for Selection
Make sure you check the “Output number of Hits “ – I will come to why this is required later in this blog.

Under fields of selection – select whichever is applicable – Infoprovider / query and also check the following.

 Fields for Selection

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…

Resultset

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..

RSDDSTATEVENTS
Now go back to the base data you have and do the following :

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…

Arun Varadarajan

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Jay Roble
    Have you looked at this OSS note to take care of the overflow issue.

    te 992805 – Overflow during the arithmetical operation (type P)

    (0) 
    1. Arun Varadarajan Post author
      Jay,
      I am on SP 10 for BI_CONT 703.
      Also this is not an error we receive when loading data – this is happening when I select data from the cube – possibly because of the numbers exceeding their prescribed limits with DB Aggregation.

      Arun

      (0) 
  2. Kenneth Murray
    Great information and thanks for taking the time.  It is very useful.

    But I have to say that it is very sad that this is what we have to go through today to get the information we need.  With all of the money clients pay we shouldn’t have to go through various tables, etc. to get this information.

    (0) 
  3. Ravi Kumar
    Hi Arun,

    Can you direct me on where I could get the information about diff events? I am really struggling with understanding some events like Data Manager,Authorized Values and Intervals, etc.

    Many thanks,
    Ravi.

    (0) 

Leave a Reply