Skip to Content
Author's profile photo Suman Chakravarthy K

How to optimize Reporting Performance

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.





Assigned Tags

      183 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Good points made......Suman.

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Hi AMir,

      Thanks for the feedback. ๐Ÿ™‚

      Regards,

      Suman

      Author's profile photo Former Member
      Former Member

      I guess Suman I did it already, it was 4 Stars. You know always there is something to work on.

      Regards

      Amir

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      You know always there is something to work on.

      What do you mean by this? Thanks for the rating.

      Author's profile photo Satendra Mishra
      Satendra Mishra

      Nice information Suman...

      ๐Ÿ™‚

      ~ SM

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thank you Mishra ๐Ÿ™‚

      Author's profile photo Former Member
      Former Member

      very well documented ℹ , Thanks For sharing ๐Ÿ™‚ ๐Ÿ™‚

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thank you SG ๐Ÿ™‚

      Author's profile photo Former Member
      Former Member

      I just found this.. This is really helpful. Thanks!

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Amazed to know it is really helpful ๐Ÿ™‚

      Author's profile photo Former Member
      Former Member

      very good info..! Thanks

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thank you Ravikanth Reddy Vinta for your feedback ๐Ÿ™‚

      Author's profile photo Former Member
      Former Member

      very good document about improving the performance of a report.

      ๐Ÿ™‚

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thank you so much for your kind feedback Verma ๐Ÿ™‚

      Author's profile photo Former Member
      Former Member

      Thanks for sharing a significant document. It'll be very useful in real time..

      Regards,

      B Devi

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Certainly it will be useful. Thanks for the feedback ๐Ÿ™‚

      Author's profile photo Ganesh Jagdhane
      Ganesh Jagdhane

      Very helpful Information ...

      Thanks  for sharing

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thank you Ganesh for your valuable feedback ๐Ÿ™‚

      Author's profile photo Ganesh Bothe
      Ganesh Bothe

      Hi Suman,

      Very good information....It will useful while creating qurey..thanks for sharing ๐Ÿ™‚

      Regards,

      Ganesh Bothe

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thanks a lot for your cheerful comments Ganesh ๐Ÿ™‚

      Author's profile photo Former Member
      Former Member

      Very nice blog.

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thank you Koti Rama ๐Ÿ™‚

      Author's profile photo Former Member
      Former Member

      hi

      suman sir it is very useful to me .

      Thank u sir.

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Please don't call me sir. Thanks for the feed back ๐Ÿ™‚

      Author's profile photo Former Member
      Former Member

      It will really be very helpful for anyone on BI as the performance is one of the major issue evryone used to face...

      Thanks for sharing .gr8 effort. ๐Ÿ™‚

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Hi Prerna,

      Thanks for your noble words ๐Ÿ™‚ Welcome..

      Regards,

      Suman

      Author's profile photo Former Member
      Former Member

      Very Informative Suman, Keep up the good work.

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thanks for appreciating it ๐Ÿ™‚ ๐Ÿ™‚

      Vikrant Soni

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Hi Chandresh Patel ,

      Exception Aggregation is only for OLAP aggregation behavior. Keep Material infoobject in your Aggregate. This should improve your report performance ๐Ÿ˜‰

      Regards,

      Suman

      Author's profile photo Former Member
      Former Member

      Very helpful information Suman. Thank you.



      Regards,

      gopi

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thank you Gopi ๐Ÿ™‚

      Author's profile photo Former Member
      Former Member

      Thanks Suman for the nice blog. Very much helpful.

      Kind Regards,

      Vinod

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thank you vinod for your valuable feedback. ๐Ÿ™‚

      Author's profile photo Kodanda Pani KV
      Kodanda Pani KV

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

      its very useful for us.

      Thanks,

      Phani.

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      HI Pani,

      This is my objective. Whomsoever come across this job, they can happily use my blog ๐Ÿ™‚

      Regards,

      Suman

      Author's profile photo Former Member
      Former Member

      Hello Suman,

      Thanks for sharing your knowledge out of experience, really helpful ๐Ÿ™‚

      True most of the key points are focused.

      -LS

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Hi Sabbani,

      Thanks for your cheerful comments ๐Ÿ™‚

      Regards,

      Suman

      Author's profile photo Former Member
      Former Member

      Performance of BEx query is very important . Very handy documents . Happy to read ๐Ÿ™‚ .

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Glad to receive your wonderful feedback Saikat Pal ๐Ÿ™‚ ๐Ÿ™‚ ๐Ÿ™‚ ๐Ÿ™‚ . You may check my other blogs as well in Bex..

      Author's profile photo Former Member
      Former Member

      Very useful information ๐Ÿ™‚

      Regards,

      Chandra Sekhar

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thank you Chandra Sekhar for your valuable feedback.

      Author's profile photo Aravind Nag M
      Aravind Nag M

      A  very good & detailed explaination. Keep it up Suman....

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Glad to receive your valuable feedback Aravind Nag M ๐Ÿ™‚ ๐Ÿ™‚ ๐Ÿ™‚

      Author's profile photo Former Member
      Former Member

      Excellent  man ๐Ÿ™‚ .

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thanks Giri Prasad ๐Ÿ™‚ for your wonderful feedback.

      Author's profile photo Amer Syed
      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?

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog 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

      Author's profile photo Former Member
      Former Member

      Hi Suman,

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

      Regards,

      Bansi

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Hi p bansi ,

      Really amazed by your splendid comment ๐Ÿ™‚ ๐Ÿ™‚ . Thank you!!

      Regards,

      Suman

      Author's profile photo Former Member
      Former Member

      Hi Suman,

      Its tough to touch all points in one blog...Its really great effort....

      regards

      Rah

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Hi Rajeev Parimi ,

      Yep!! It's true! ๐Ÿ˜Ž Thanks for your comments.

      Regards,

      Suman

      Author's profile photo Aparajit Banik
      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!!

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog 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

      Author's profile photo Yasemin ULUTURK
      Yasemin ULUTURK

      Hi Suman,

      I have read the document maybe a hundred times by now and just realized that did not thank you for sharing ๐Ÿ™‚

      Author's profile photo Aparajit Banik
      Aparajit Banik

      Yasemin,

      I need to thank you too along with Suman for patiently helping me always
      Rama has always been an inspiration as well.
      -Aparajit

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Hi Yasemin ULUTURK ,

      Thank you for the wonderful gesture ๐Ÿ™‚ Really delighted to receive a comment from MoM ๐Ÿ™‚ .

      Regards,

      Suman

      Author's profile photo Yasemin ULUTURK
      Yasemin ULUTURK

      Hi Suman,

      Being appreciated by the community is really nice and motivating, you deserve much more Suman...

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Hi Yasemin ULUTURK,

      Exactly!! I too feel the same. ๐Ÿ™‚ Appreciation/Applause is the key driving factor to continue sharing our experiences. Thanks for mentioning/recognizing my efforts ๐Ÿ™‚

      Regards,

      Suman

      Author's profile photo Balaji Bysani
      Balaji Bysani

      Thanks for sharing useful information..!

      Author's profile photo Leslaw Piwowarski
      Leslaw Piwowarski

      Hi,

      Very usefull, for beginers and more advanced users.

      Thanks for sharing.

      Regards, Leszek

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      You are welcome bysani balaji ๐Ÿ™‚ .

      It's my pleasure to share my knowledge with the BEx Community Leslaw Piwowarski.

      This document will also help to attend interviews ๐Ÿ˜‰

      Author's profile photo Sayan Dey
      Sayan Dey

      This article had actually helped me for my presentation. Great work, Suman Chakravarthy K! Would be looking for more such articles in the future, as beginners like me need them dearly.

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K
      Blog Post Author

      Thank you Sayan for your wonderful comments on my blog. You may check out my other blogs in SCN

      Author's profile photo Sayan Dey
      Sayan Dey

      Yes, I will. In fact, I already have. Thanks again. Keep composing such great articles, as it is really helpful for us.

      Author's profile photo swati gawade
      swati gawade

      nice article

      Author's profile photo vinod bokade
      vinod bokade

      Thanks for sharing Suman. Very useful information.

      Thank-You.

      Regards,

      VB

      Author's profile photo Suhas Karnik
      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.

      Author's profile photo David, Choong Poey Yee
      David, Choong Poey Yee

      Good compilation of reporting performance.

      Author's profile photo Adem Gรผler
      Adem Gรผler

      Useful article. Thank you for sharing.