Skip to Content

Introduction

                 We face many challenges in our BI projects in terms of Reporting Performance. Users always expect outstanding performance of our BI reports. No matter what ever we do in the backend (Modeling and Query Designer). I am going to share very important tips & techniques which I have learned through out my experience which consists of some standard thumb rules too.

  1. It is recommended to use Inclusions Instead of  Exclusions wherever possible as Exclusions cant access DB indices and will impact performance.

                                                                          Rep3.JPG

2.     It is recommended to to Suppress Result rows Wherever possible.

               Rep2.JPG

3.     It is recommended to use SAP Exits where ever possible and bring down the Customer Exits

4.     SAP Suggests free characteristics in reports should be limited to 8-10.

         In RSRV checks, free characteristics usage is marked in red which has very high impact on reports

               Rep1.JPG

5.      It is recommended to reduce RKFs & CKFs in the Query to as few as possible.

         a.     Because, when huge no. of  RKFs & CKFs are Included in a Query, Restricting, Conditioning and Computations are done for each of them      during query execution.

         b.     This is very time consuming and a high number of RKFs & CKFs can seriously hurt Query Performance

6.     It is recommended to redefine your Aggregates in an optimized way by taking Statistics(BI Admin Cockpit should be in place) & Query                    definitions into Consideration.

         a.     I mean, please open your query definition and pick up the fields which are used in the query while defining Aggregates.

         b.     Delete the unused Aggregates

         c.     If the aggregates are too Large, those not only degrades the Query performance but also loading performance by longer times to roll up and           Attribute Change runs also takes longer times.

         d.     Make sure you get good valuation indicators for your Aggregates, but not like below

              

          Rep4.JPG

7.     Archiving (NLS) is recommended to archive unused data.

                          

        a.     Reduction of online disk storage

        b.     Improvement in BW query performance

        c.     Increased data availability as rollup, change runs and backup times will be shorter

        d.     Reduced hardware consumption during loading and querying

8.     Reports should be designed  on Multiproviders wherever possible.

         a.   You can use 0INFOPROVIDER field to restrict to the specific infoproviders in Queries

         b.   You can also maintain partitioning criteria in RRKMULTIPROVHINT table

9.    Logical and physical partitioning is recommended for better performance.


10.     Extensive use of Filters at Query level is recommended.


11.     Select appropriate Read mode settings for Multiproviders with “H” and Infosets with “X” in RSDIPROP t-code

12.     Reporting on Infosets should be considered below tips :

          a.     Do not select all the fields which are part of Infoset definition like below. You can select the fields which we want to use in query only.

                       Rep5.JPG

         b.     You should select “Use Selection of Structure Elements” in RSRT–>Properties

                          Rep6.JPG

        c.     Do not make too many joins as it cause high runtimes to fetch data after the joins

         d.     You can set a limit of “Limit value for large Where conditions” in RSCUSTV19 Eg: 300



13.     Statistical Reports performance can be improved by broadcasting  query result  to cache and prefilling cache.


14.     Deletion of unused Queries is recommended


15.     Delete temporary Query Views is recommended.


16.     It is recommended to be careful while creating Cell Structures as they require high query run times and will lead to performance degradation.


17.     Program RSR_CACHE_RSRV_CHECK_ENTRIES can be scheduled to run on regular basis to remove the unused Cache entries .


18.     Make proper Query read mode and Cache mode settings in RSRT–>Properties. The recommended Cache mode could be 1 or 5.

Conclusion :

It is always better to keep an eye on above points while developing our Business models and Queries. This blog will help us to satisfy our users with good reporting performance.





To report this post you need to login first.

182 Comments

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

  1. Chandresh Patel

    Thanks for sharing..

    But my query have exception aggregation on material level. And volume of data is huge. So performance is very low for 1 day data. please provide quick reply it will be appreciated.

    Thanks,

    Chandresh Patel

    (0) 
  2. Phani KV

    Thank you suman, while i am working on Performance issue came then i saw these document.

    its very useful for us.

    Thanks,

    Phani.

    (0) 
  3. Amer Syed

    i have reports on huge multiproviders. which includes around 8 to 9 cubes. i thought the concept was to provide lots of query field to user and then let the user create their own reports as with BOBJ 4.0 Webi has query stripping which only runs when fields are brought in and increase performance.

    SAP Suggests free characteristics in reports should be limited to 8-10.”


    i was informed to keep out of rows and put everythign in free chracteristics as this allows for better reporting instead of Webi trying to understand whats rows and whats columns.


    can you shed some light on this?

    (0) 
    1. Suman Chakravarthy K Post author

      Hi Syed,

      SAP suggests the best practices. But you can still keep more than 10 free chars in your queries. In one of our reports, i have put 60 free chars and the report is performing nicely with aggregates. Make sure you keep some of the most important drill down chars in your aggregates. This can improve the performance.

      Regards,

      Suman

      (0) 
  4. p bansi

    Hi Suman,

    Thanks for sharing such wonderful document. I feel its like “ALL IN ONE” dictionary for performance tuning.

    Regards,

    Bansi

    (0) 
  5. Aparajit Banik

    Hi Suman
    Thanks a lot  for the KT. All the points are so important.
    Its helping me a lot as I am started with report developments in SAP BEx.
    Would you like to give me your inputs in a issue in preparing an inventory report

    Regards!!

    (0) 
    1. Suman Chakravarthy K Post author

      Hi Aparajit,

      Glad to receive your comment on my blog. 🙂 BEx is the most interesting and brain storming tool to achieve wonders and gain Users confidence. Your BEx reports should talk on behalf of you to the users community. Sorry, I have not worked on MM reporting. But I will definitely come up with exciting inventory reporting in Bex whenever I get a chance to work with it..

      Regards,

      Suman

      (0) 
  6. Suhas Karnik

    Good blog Suman!

    I am curious about the second half of point #3 though:

    It is recommended to use SAP Exits where ever possible and bring down the Customer Exits

    Customer exits are normally used to calculate the variable values to be passed to queries. They do require a certain amount of time to calculate, but the code for these is usually not data-intensive. Also they’re calculated once-per-query-run, not once-per-record. So the time spent on this would be negligible compared to the time taken by the system to query the InfoProviders and actually retrieve and process the data for the front-end. The only exceptions I can think of are if your query is trivially small, or the cust exit code is data-intensive, both of which are rare scenarios. 

    So again, I’m curious why Customer Exits would be a significant point in query optimization.

    (0) 

Leave a Reply