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.

More information:

How to… Performance Tuning with the OLAP Cache

Configuration and Utilization of the OLAP Cache to Improve the Query Response Time

Step by Step Procedure to Create Broadcasters, to Schedule and to Enhance of SAP- BI Queries from Query Designer

SCN Wiki

How to clear BW/(SAP AS) caches

There are several places across BW tools from where clearing of different types of cache can be done.

BEx Web Application Designer metadata cache: stores metadata information about objects related to web templates. You can also delete the cache in which metadata information belonging to current opened template within the session to BW backend. To delete this cache choose in BW Web Application Designer: Menu Tools -> Setting -> Delete cache -> Delete cache. Documentation for NW2004 can be found here. This is not valid for BW based on version 7.x.

 

OLAP Cache: so called Query cache is type of cache is used to buffer BW queries data sets to be available for next query run. In this case OLAP processor first checks cache and do not need to read the data out of BW infoproviders. To clear OLAP cache run TA RSRT -> Cache monitor -> Delete or you can go via TA RSRCACHE. Online docu here. If you want to do it from your custom ABAP program you may use function module: RSR_CACHE_CLEAR_ALL_CACHES.

 

HTTP server cache: this is related to HTTP server build in SAP AS server. You can clear it via TA SMICM -> Goto -> HTTP server cache -> Invalidate cache -> Global in system. Online docu here.