Help Your Queries Run More Quickly Using the OLAP Cache

  • by Gary Nolan, SAP BI Strategic Architect, Sapiex Associates LLC
  • November 1, 2004
Your users can benefit from one another when they run similar queries thanks to the online analytical processing (OLAP) cache. It is designed to store the results set used by certain queries to improve system performance. The system quickly reads from past query results stored in the cache rather than from the InfoCube or aggregate. It must be maintained properly along with the queries that use it for the OLAP cache to reach its fullest potential. Here's how.
Key Concept
Frequently used queries, if configured to do so, are automatically saved into the OLAP cache when the user initially runs the query. The same or similar queries are read from the OLAP cache after the initial query is run, typically providing better performance. Storage options are available for query results and can be optimized to best meet the needs of the system as well as for the individual queries.

It goes without saying that all BW customers are concerned about query performance. Of all the tools and methods to improve query performance, one of the most underused is the online analytical processing (OLAP) cache.

The OLAP cache was introduced in BW 2.0B expressly to improve query execution. It allows data that supports certain queries to be stored or “cached” in the main memory or distributed to an application server or a network. After a query is run, the results are stored and available to similar queries that run at a later time. The OLAP cache offers significant performance gains over other techniques such as aggregates when it is configured correctly. Moreover, its benefits can be expanded if qualifying queries are enabled to fully access cached data.

As with most performance improvement techniques, however, costs are associated with caching. Penalties are incurred when data is organized and retained in the cache. It is important to make sure the OLAP cache is configured properly to minimize any drag it may place on the system. To learn about the essentials of configuring the OLAP cache, refer to Ned Falk’s article, “OLAP Cache: Does It Have to Be Configured?” in the April 2004 issue of BW Expert.

I will provide you with an overview of the storage options available with the OLAP cache. Then I will introduce you to a couple of new terms and discuss how to manage your cache. Lastly, I will show you how to make sure the queries you’re running are best able to access cached data so they can perform at their highest level. In an upcoming article for BW Expert, I will discuss how to use a reporting agent to prefill the cache in batch, providing more efficient cache usage and better performance.

Gary Nolan

Gary Nolan is an SAP NetWeaver BW-certified consultant and author of Efficient SAP NetWeaver BW Implementation and Upgrade Guide and Efficient SAP NetWeaver BI Implementation and Project Management. Gary specializes in gathering and evaluating requirements, configuring SAP NetWeaver BW, and providing project management, performance management, and data architecture and data modeling expertise. A former platinum consultant with SAP America, Inc., Gary has more than 20 years of SAP experience, working with SAP NetWeaver BW since version 1.2B.

See more by this author


Comments

No comments have been submitted on this article. 


Please log in to post a comment.

To learn more about subscription access to premium content, click here.