Martin Maruskin blog (something about SAP): How to put query into SAP BW cache?

Tuesday, December 29, 2015

How to put query into SAP BW cache?

There are few different cache memories used within the SAP BW server. One of them is OLAP cache. Its purpose is to store query result sets. Once the query set is stored into the OLAP Cache it can be later re-used by all users and system doesn’t need to retrieve the data sets from the underlying BW InfoProviders objects (e.g. cubes) again.

Now how particular BW query gets stored into the BW’s server OLAP cache? There are few cases for this. The most common is that once user executes a query, the result set for that query’s request can be stored in the OLAP access to those result sets. Later in case the same query is then executed again (also it can be by another user); the query request can be filled by accessing the result set already stored in the OLAP cache.

How can we get the query into the cache upfront in order to prepare it for very first user so even execution of the query by very first user takes place it will hit the OLAP cache?

1. pre-calc server:

By using simple web template which is published for the BW query and is scheduled to run in the background using the reporting agent. As the query has one variable there can be multiple query views within the web template. Information Broadcasting can be used in this case in BW 7.x. In SAP BW 3.x instead of the Information Broadcasting there is reporting agent functionality available to achieve the same.

2. BEx Broadcaster:

The BW query can be published to BEx Broadcaster from BEx Query Designer tool. The tool is running in JAVA stack. So once you do this in from the BEx Query Designer you get to web environment. You can setup different settings in here. The settings includes like following: Distribution Type (Broadcast to: email, Portal, printer, etc and one of the Type is also OLAP cache. The BEx Broadcaster Settings can regularly run as a job or it can also be automated using Process Chains (Process type: Trigger event data change for Broadcaster).

In closing words I want to show how the BW query loaded into the OLAP cache looks like. These queries can be monitored in tcode RSRCACHE (you can jump there also from tcode RSRT -> Cache Monitor. On picture below is like BW query loaded into the OLAP cache looks like. To housekeep the OLAP cache you can use ABAP report RSR_CACHE_RSRV_CHECK_ENTRIES e.g. to delete entries in the cache.