Tips & Tricks -Developing smart Web intelligence reports over SAP BW Bex Query- Part 2

Hi All ,

First of all I would like to thank you all for the great feedback I got  on“Tips & Tricks on developing smart Web intelligence reports over SAP BW Bex Query- Part  1”

When I started to create my Part 2 , I have run into an awesome new articles on BO for SAP ,especially  the article created by Kurt Holst from SAP and his team  , Kurt document is very helpful to understand how webi works with BICS protocol  I would recommend you all to read Kurt document – you can find it hear.-https://scn.sap.com/docs/DOC-33706

The point I would like to consider in this article is  separates to 3 layers – when I remind you that Part 2 is focused on Performance

1. 1.BO and local preferences   2. BO front end  3. BW Modeling

Forgive me that I cannot cover everything in 1 document , I will try to focus on what I think is basic & important , my recommendations is from personal experience with  BW data on info provider over then 30M or records , if you have small amount of data maybe you will have smaller performance issues .

Important: SAP BO web intelligence is not OLAP tool, most of us SAP BW users/developers/consultants somehow mix it with the SAP BW Analyzer or WAD, so please don’t compare performance with them both since they are different – if you want to use Bex or WAD OLAP functionality you have the wonderful toll of BO- Analysis for OLAP- it looks and behaves like Bex , even the “Go to” drill  elements works there (and more features as notes ), as I also understand the Analysis  will replace the Bex in the upcoming future

BO and Local preferences

1. If it takes you time to open  the webi , please update your webi preferences to View –>web ,modify –>Rich Internet Application , when working in view as Rich Internet Application- all the java components are being downloaded and installed , the computer anti-virus start to check it and it takes time to open – please make sure users are working in View web and modify web or rich- please also be aware that there are different application functionality between web or rich- but this is for different article
2. For section 1 you can also define in you organization anti-virus to unchecked the BO services
3. Update your java run time environment settings in run time parameters with -Xms128m -Xmx256m – for that go to control panel- click on java then in the java control panel go to java à view ,there you will find the  run time parameters- when running on rich it will open faster
4. Update your java for  the latest version
5. DSL separation- you must separate the DSL bridge and give it enough memory- Kurt explains this in his article

BO Front End

1. Query Stripping – Query stripping is explained in Kurt document please read it is important; Query stripping is set by default from SAP BO 4 + SP05.
2. In BO you have the option of creating several of aggregation data sets – for instance you have the option  in 1 BO report to create  table with  profit center, cost center, customer and revenue and another table which is on the same Bex Query with profit center, cost center, customer, supplier, area, month, day, revenue etc , the Bex Query will run 2 times , so please consider that on how you design your reports
3. In webi you can have in 1 webi report several of tab strips- which each tab strip contains a different view of the reports or different report , what I would recommend is to separate each tab strip to a different Bex query , so when the user will have refresh he/she can choose which query to refresh , and not all of them together
4. Caching BW – the BO works with the SAP BW Caching, so please make sure your BW is not on the SAP standard caching of 4 MB  , you can check it in RSRT chache monitor,local and global – for sizing the caching :

The pre-requisite for the OLAP Cache is that the SHM Memory should at least be equal to the Global Memory, Cache (OSS Note: 656060). The parameter is rsdb/esm/buffersize_kb (Size of the ESM buffer) and can be set using Transaction code RZ11

    5. It is recommended that the filters will be in BW Bex Query so that the SQL selection will be slim, but when I tested the filters issue ,when filtering in BW  Bex query                     you cannot see the filters in the webi report elements , only if the filter came from BO Query panel

    6.If you have key figures which you can transfer from Bex Query to BW modeling  it will help performance run time

    7.Try to simplify your BW Bex Calculated KF and Restricted KF

    8. When using RKF over BW M.Provider , in the RKF restrict on the info cube info object

    9. If you are facing performance problems and you know that your data is refreshed 1 or 2 a day , you can schedule the report to run before the user comes to work ,                    and when he/she will open the report it will run twice faster

    10.  All issues as hierarchy, result rows and other good recommendations you can find in Kurt document

    11. Do not run queries on DSO’s or info sets

    12. Use Multi providers on every BW info cube  

    13. Read section 7.2.2.2 in Kurt document

    14.BO statistics, adding the BO statistics/auditing can affect performance, you can deactivate the BO auditing and you will get extra percentage

BW modeling

BW modeling is a crucial factor for Query performance, especially if we are using BO as front end 

   1.     In your physical info cubes use DB statistics , add them to your process chains , this information helps the system to be more efficient in performance- you can find it            in the info cube manage –>performance tab

   2.     Make sure you are building your info provider indexes properly

   3.     If you have info provider that is over then 50M records I would recommend you to separate them by year, company code, profit centers, location , regions etc

   4.     If you are at BW 7.X use info provider hints

   5.     If you are at  BW 7.3 you can you can use the new semantic partitions concept

   6.     Build aggregation whenever you can , use the BW statistics and BO reports in order to build an efficient aggregation

All the above are my basic recommendations , there are lots more information in other articles in SCN , share us your tips and recommendation

Best Regards

Adir