Filling OLAP Cache using Broadcasting for Performance Improvement
For some reports that process a huge amount of data, the users may go mad about the runtimes of queries. For some extend we can improve the performance by playing with the design of the datasource, dataflow, the queries or the aggregates. In this document I will explain how filling OLAP Cache using broadcasting affects the performance of a query.
For this aim, a query which has a long runtime is selected: ZTEST_Q0001. For filling the OLAP cache, BEx Broadcaster is selected in query design menu as seen below:
When we select BEx Broadcaster, an html page is opened in the browser. If no broadcasting setting is made beforehand, you will see the screen as below:
Select Create New Setting. You will see a pane below where a list of possible alternatives for broadcasting is made:
When “Fill OLAP Cache” option is selected, the output format will automatically be OLAP Cache Content
Save the broadcast giving the technical name and description:
Now it is seen in the main browser and can be changed anytime:
The next step will be creating a variant to schedule this broadcast. For this aim se38 transaction is used to run the program: ”RSRD_BROADCAST_STARTER”. Select Variants and click on Display button:
Create a variant for the program by clicking on the create button next to the Variant field:
Give a name to the variant and create it.
Set the technical name of the setting same as the technical name given in broadcast definition in the previous step. Click on Attributes button and enter a description:
Save the variant. Go back to se38 to run RSRD_BROADCAST_STARTER. Select “With Variant” option and select the variant just created in the previous step. The program can directly be run manually or can be scheduled using execution in background.
You can schedule this job everyday for those queries that have a long run time. This process can also be included in a process chain. I experienced around 75% improvement on a query which is built on an infocube with 50 million records. You can also measure the performance improvement running the query with RSRT using cache and not using cache and analyze the statistics.
I hope it gives an idea for performance improvement.