All Users Benefit When You Prime the OLAP Cache
- by Gary Nolan, SAP BI Strategic Architect, Sapiex Associates LLC
- January 1, 2005
Even when the OLAP cache is enabled in your BW system, the first query must read data from the fact table to fill the cache. This takes time and denies initial query users the performance boost that caching offers. Here’s a way to speed up the process for all users of your most popular queries.
Reporting Agent in BW 3.0B allows you to precache data by filling the OLAP cache prior to running a query, if attached to Web templates. The system also provides techniques to monitor the cache.
In the November/December 2004 issue of BW Expert, I wrote an article about providing the online analytical processing (OLAP) cache with access to a subset of data in the BW environment (“Help Your Queries Run More Quickly Using the OLAP Cache”). The OLAP cache stores query result sets that are used during subsequent query runs, saving time and enhancing system performance. The subset cache further improves performance by allowing a smaller result set to be read from the larger data set residing in the cache.
In the technique described in my last article, the first query must read data from the fact table even when the cache is enabled. It’s the result set from the initial query run that fills the cache. Future users then enjoy the benefits from the initial query because the cache is filled and queries don’t have to locate the transaction data fact table or aggregate again.
Under some conditions, it is possible to “prime” or prefill the cache by running queries in batch using Reporting Agent prior to the first user’s query. This precaching guarantees that even the initial query user benefits from the OLAP cache. After all batch loads are complete, the system runs your most popular queries to fill the cache. Other than faster running queries, there is no evidence to the end user that cache is being used.
Priming the cache via Reporting Agent is supported in BW Release 3.0B and later versions. Note that only queries or query views attached to Web templates can run with Reporting Agent in the background. You cannot use Reporting Agent to fill the cache via a BEx workbook. If Web functionality has not been implemented on your system, cache priming is not possible.
Would you like to see this full item?