Use of BW cache in SAP Design Studio
Using BW cache is a good instrument to speed up the start-up time of your SAP Design Studio dashboard.
In Design Studio you can use the parameter “&PROFILING=X” while running an application to display statistics regarding your dashboard. These statistics however don’t explicitly show if the data is being read from the BW cache or directly from the database.
In this blog I want to demonstrate an easy way to fill the cache every morning and how we can monitor if the cache is being filled correctly.
I will use an Analysis for Excel report to schedule a report to fill the BW cache and use the Design Studio statistics and the BW cache monitor to test the result.
Let’s get started by following these steps:
- 1. Empty the cache
- 2. Measure the run time in Design Studio
- 3. Empty the cache again
- 4. Schedule the Analysis Office report a couple of time so that the BW cache gets filled
- 5. Measure the run time in Design Studio again
Clear the BW cache
In BW transaction RSRT go to the cache monitor and lookup the query that you are using
Right click on the folder and select delete
** Note that you can check in the cache which variable values are applied in the dataset that is in cache by choosing the option contents
Measure the statistics in Design Studio
First you need the open document link (URL to your dashboard). You can get the link from the BO platform by opening the context menu of the report and select the DocumentLink.
You can also get the link in Design Studio by selecting the Send to mobile device and then Copy URL to clipboard
Now that you have the open document link, insert this in your favourite browser and add the statement &PROFILING=X at the end of the URL
The statistics window will appear in your browser. From the sample image below you can see that the total run time was 14 seconds. The time Design Studio needed to get the data was about 12.8 seconds and de remaining time was the time needed to render the information on the screen.
Let’s empty the cache again. Every morning when the data is loaded in BW we need to fill the cache. Unfortunately you cannot use Design Studio to do this automatically (yet). However, you can use other tools like WebI or Analysis for Office to perform this task.
In this case we will use Analysis for Office. We have to make sure that the cache is filled correctly so that is can be used for Design Studio as well.
Create an Analysis document with exactly the same structure as the data source in Design Studio
Schedule this report to run 4 or 5 times so that we know the cache is being filled properly.
** Note that to schedule an Analysis for Office report, you need the Analysis for Office scheduling service enabled on the BO platform.
At this point the cache is filled. You can check to see the entry in the cache monitor in BW
Measure the performance again in Design Studio
The statistics show that the data source time is decreased from about 12 seconds to less than 1 second!
We have seen that making use of BW cache can speed up the performance for your Design Studio dashboards a lot. Keep in mind that you need exactly the same view of the data in both Design Studio and in Analysis for Office in order for the cache to work.
When the user applies a filter, the data will be fetched again from the database. You can think about using this method to cache frequently used filters as well.
This is a very useful write-up, which I think clarifies the required approach to address the issues faced in discussions such as the following: Utilizing OLAP-CACHEof Webi reports for Design Studio
please can you clarify this point :
"Create an Analysis document with exactly the same structure as the data source in Design Studio"
Hi Dwain, that's a very interesting way of speeding up. Easy maintenance and easy setup p.
Anand, Dwain means:
You can use 1 query multiple times in Design Studio. You can also change the dimensions in the rows and columns. If you do this you have to make sure the rows and columns in Analysis are also the same.
Dwain - thanks for the smart trick. Good work.