Skip to Content
Query Performance – Is Aggregates the way out for me?   As a part of my job I need to interact with SAP BI consultants quite frequently & I have been quite amazed by the knowledge & passions displayed during these chit chats. I have had discussion on many BI topics with these Gurus which led to enrichment of my knowledge in Business Intelligence Space.   Among the topics discussed my favorite & most frequently discussed, has been Performance Optimization more specifically, Query Performance. My interest in this area is driven by simple reason – Bad Performance delivery can bring the project to halt & worse can really slow down the ROI (Return on Investment).  As soon as I start discussing BW Query Performance & there will be echoes of Aggregates from all the corners, every discussion in this area started with Aggregates & more often than not ended at Aggregates.   This led me to obvious question – Is This the Only Way? Or will Aggregates always help me?  I wondered is there a way to find out whether Aggregates can work in my specific query performance problem. To address these following steps can be followed –   [Prerequisite – Check if the statistics of the Cube (on which bad query / queries are based) are active. Go to (Administrator workbench) RSA1 – Tools – BW Statistics for InfoProvider – Look for desired Cube on which query is based on – Switch on both the statistics.]  1.     Open transaction ST03 and change the view of ST03 from Administrator to Expert Mode.  2.     Open BW System Load by month.  3.     Choose the last month or period for analysis. image 4.     Identify the Cube & then Query which is to be optimized. Note down the relevant parameters for this query. image 5.     Notice the following two parameters –   a)     % of Database time ¡V Database time / Total Runtime  b)     Ratio of selected to transferred records (Number of records selected / Number of Transferred Records)  Decide whether Aggregate will help?  a)     If in above analysis Summarization Ratio (records selected to records transferred) >10   And   b)     Percentage of DB time > 30% (Time spent on database is a substantial part of whole query runtime)   Then   c)     Go ahead Aggregates may help otherwise look at the other optimizing techniques.
To report this post you need to login first.

18 Comments

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

  1. Anurag Khungar
    Thanks Vikash for enriching us with the useful information. Really makes it easier to objectively understand when to use aggregates or not.

    Regards
    Anurag

    (0) 
  2. Anurag Khungar
    Thanks Vikash for enriching us with the useful information. Really makes it easier to objectively understand when to use aggregates or not.

    Regards
    Anurag

    (0) 
  3. Anurag Khungar
    Thanks Vikash for enriching us with the useful information. Really makes it easier to objectively understand when to use aggregates or not.

    Regards
    Anurag

    (0) 

Leave a Reply