I would like to expand on one aspect of improving reporting performance
in SAP BW – exploiting the OLAP cache. The OLAP cache is standard
functionality that is setup during a technical install of SAP BW.
However, to realise the full benefits of speedy queries based on cached
data, some additional steps need to be taken post-install. I’ve noticed
these steps, and in particular the scheduled population of the OLAP
cache, has been overlooked on quite a few implementations.
In this post i’ll focus on the concept of ‘pre-filling’ the cache on a
regular basis through the Business Explorer (BEx) broadcaster. I’ll
also cover some points to consider in maintaining the OLAP cache and
performance tuning. Let’s use the functionality available in a standard
install to give your users the benefits of cached data!
Cache: A safe place for hiding or storing things.
A cache can be described as a buffer that’s employed to re-use
commonly occurring items. A cache has many applications in a computing
context: the memory cache, internet browsing history cache, disk
caching, etc. In all of these cases, caching provides an answer or data
retrieval, faster than another alternative. Faster data access can be achieved through caching data in registers (accessed in 1-5 clock cycles) vs. storing it in slower RAM (10-100 cycles).
SAP BW OLAP cache
In a SAP BW context, the OLAP cache buffers query results sets
retrieved from the database (disk) by storing them in resident memory as
highly compressed cluster data. Why bother storing query results in
memory? The answer is simple – this is a speed contest between disk
access vs. memory access, with memory being the much faster winner. On
both sides of the equation, physical and electronic, there’s compelling
logic that application performance will be improved through maintaining
data in memory, rather than retrieving it from disk. Disk access is one
of the few mechanical (as opposed to electronic) functions integral to
processing and suffers from the slowness of moving parts. On the
software side, disk access also involves a ‘system call’ that is
relatively expensive in terms of performance. The desire to improve
performance by avoiding disk access is the fundamental rationale for
database management system (DBMS) caching and other file system caching
methods. SAP BW is no different – and contains caching at the
application layer to improve OLAP reporting performance.
Users progressively fill the OLAP cache as they access queries. When
a user accesses a query result set for the first time, a database read
from disk takes place and the result set is then written to cache
memory. A cache read action takes place when the same result set (or
subset) is subsequently accessed. Hence, the result set is retrieved
from memory, instead of being retrieved from disk. An important feature
of the OLAP cache is that read/write access to the cache is available
to all users on the application server. All users get the benefits of
faster data retrieval from the cache if another process or user has
previously written the data to it.
The OLAP cache has a physical memory size and hence a storage limit. The cache persistence mode
can be setup to either swap to disk or be overwritten when it’s full.
OLAP cache data is invalidated each time a request is loaded into a
Basic InfoCube; meaning that any Basic InfoCube that is updated daily,
will have its underlying cache data wiped. This makes sense as the
cached query result set data for that InfoCube, whilst accurate as a
historical snapshot is now no longer representative of the current data
in the InfoCube. Hence the old query result set needs to be erased.
This begs the question, when should the cache be filled with the
required query result sets?
Default OLAP Cache
In the default approach post BW install, only users
progressively fill the OLAP cache as they access queries. Users that
access query data for the first time will retrieve data from disk and
not the OLAP cache – meaning they will experience a longer delay than
users subsequently accessing the same data set.
The delay in retrieving query data from the database for the first
user access can be significant for certain queries – a key problem that
needs to be solved. This first access delay is compounded for web
templates or dashboards that execute several queries for the first time
in parallel. The delay is also influenced by the complexity of the
query and your success with other performance optimisation techniques.
Furthermore the delay will occur during user runtime – a particularly
inconvenient time – as we want to shift any delays in accessing data
away from the user’s waking hours. In any case, if this query result delay is significant – then you’ve lost the user already.
Accessing the OLAP Processor
When searching for requested data, a BI query accesses performance
optimised sources in the order shown to the left. The important
take-away is that the OLAP cache is read first, in preference to all
other performance optimisation techniques, even the BI Accelerator.
However, this isn’t to say that these techniques don’t have their place.
Performance optimisation techniques, such as aggregating data,
compressing data, limiting table size, etc. are still vital to any
successful implementation. They are still valid and necessary even with
an OLAP cache. Not only will they help to soften the blow of the first
disk read as previously discussed, but it is impossible and not
efficient to cache all the permutations of query result sets. OLAP
analysis by its very nature is predicated on not being able to predict
every possible query request.
Even if you did try to make such a prediction, you’d most likely end up caching a whole bunch of result sets that:
- User’s will never read; and
- Would be invalidated on a regular basis as the Cubes are updated.
Pre-filling the cache
The idea of pre-filling or ‘warming up’ the OLAP cache is to defer
the database operations away from user run-time to another more
convenient point in time, system time. Such a time could be after the
daily load in the early hours of the morning when few (if any) query
requests are being processed.
The database is going to have to do the work at some point in time to
fill the cache – this much is clear. It could also be argued that this
work, the ‘pre-filling’ the OLAP cache, represents an additional
operation and load on the system in addition to the regular data flow
processes. In my opinion this is probably the wrong way of looking at
the problem. If we can accurately predict what the most common query
requests are going to be, then there will be no additional load on the
system. These query requests would happen in the normal course of
business. Additional load would only be generated if we go overboard
caching every possible query request permutation possible and then that
cached data is never accessed.
To prevent this we should endeavor to restrict data being loaded to
the cache to only likely requests and this will vary from Data Mart to
Data Mart. Tools are available in SAP BW through the Transaction
RSRCACHE to determine which query entries have been read from the cache.
This should be monitored on an ongoing basis to optimise your caching
strategy and remove any poorly performing cache entries from being
continually generated. See Monitoring the OLAP Cache in SAP Documentation for further details.
The most common and convenient way of pre-filling the OLAP cache is
through the BEx broadcaster, although there are several other
approaches, such as:
- Using the Reporting Agent (3.x release) to fill the cache (Transaction Code REPORTING_AGENT). Note that in 7.x a warning message appears stating that this can only be run for 3.x queries and templates, but it works fine with 7.x queries. However, it doesn’t work with 7.x web templates and will not be developed further by SAP; or
- Create a program/function module/web service that executes the relevant queries (See FM RRW3_GET_QUERY_VIEW_DATA );
- Create an Analysis Process to run the relevant queries and output the data to a dummy target such as a DSO.
Each of these methods are able to be inserted into a process chain and hence, can be regularly scheduled.
BEx Broadcasting Approaches:
Some recommended broadcasting approaches that i’ve seen work well on
implementations are listed below. Let me know what approaches have
worked well for you – I’d be interested to hear your feedback.
1. Schedule broadcast on data change
Firstly in the BEx Broadcaster schedule the broadcaster to run on a data change event.
Add a data change event to the end of each datamart load process chain.
This event triggers the broadcasts which pre-fill the OLAP cache. In
the data change event you specify which InfoProvider has had it’s data
changed (and hence the OLAP cache has been erased for it) and broadcasts
scheduled on the event for the InfoProvider are triggered.
2. Web Templates
Web templates can contain multiple data providers and hence multiple
queries. Instead of creating individual broadcast settings for each
query in the template, a single global setting can be created for the
template. When scheduled and broadcasted, this web template setting
will run, and hence cache, all of the underlying queries in the template
as they would appear in the dashboard.
Unfortunately for web templates, there isn’t a broadcast distribution
type to fill the OLAP cache. Instead if you’re objective is to only
fill the OLAP cache, another setting such as Broadcast to the Portal
will need to be used. I’d recommend the following settings:
| Distribution Type: | Broadcast to the Portal | ||||
| Output Format: | Online Link to Current Data | ||||
| Authorization User: | ADMIN | ||||
| Export Document to My Portfolio: | Check | ||||
| Export User: | ADMIN |
Using these settings will result in generated online links to the web
template (and not larger files) being posted for the Admin User in the
Business Intelligence Role in My Portfolio/BEx Portfolio/Personal BEx
Documents.
The online links posted in this folder will overwrite each other and
hence preventing a large amount of documents being stored in this
directory over time.
3. Queries
Queries can be individually broadcasted. Unlike with broadcasting a
web template, more settings are available, such as broadcasting by
multiple selection screen variants and by filter navigation on the query
result set, i.e. by a characteristic.
Cache maintenance
Now that you’ve broadcast queries/web templates to the OLAP cache,
you’re going to need to maintain it. To do so, a little more
information is needed about the technical architecture of the SAP BW
OLAP cache. Technically, the OLAP cache consists of two caches, the
local cache and the global cache. These two caches can be setup with
different parameters, such as size. The local cache is accessed by a
single user within a session on an application server. Local cache data
is retained in the roll area as long as it is required by the OLAP
processor for that user. Global cache data is shared by all users
across all application servers. Global cache data is retained for as
long as it is required and will either be deleted when it is no longer
needed, e.g. the underlying data has changed and the cache is
invalidated, or depending on the persistence mode will be swapped to
disk when the cache size is exceeded.
The cache size parameters indicate the maximum size that the local
and global caches are permitted to grow to. The global cache size
should be larger than the local cache size, as the global cache is
accessed across multiple users. The local and global cache size values
should be generally extended from their default settings when you
install SAP BW. This will take advantage of memory available and ensure
that the stored cache entries do not exceed the cache capacity. The
size parameters should be reviewed periodically depending on cache
usage, hit ratio and overflow. The cache size must be appropriate to
manage the frequency of query calls and the number of users. Some
indications that your cache size should be extended are:
- The number of cache entries has filled the capacity of the cache at least once.
- Average number of cache entries corresponds to at least 90% of the capacity or has reached this capacity around 30% of the time.
- Ratio of hits to gets, is lower than 90%.
You can configure the cache parameters using Transaction RSCUSTV14.
Note that the size of the global cache is determined by the minimum
value of the Global Size MB parameter and the actual memory available in
the shared memory buffer (profile parameter rdsb/esm/buffersize_kb). You should therefore use Transaction ST02 to check whether the size of
the export/import buffer is appropriate – as the default setting of
4,096 KB is often too small. SAP recommends the following settings:
- rsdb/esm/buffersize_kb=200000
- rsdb/esm/max_objects=10000
The permitted cache size needs to be realistic though. If you’re
talking in the multiples of gigabytes, you may wish to review why you
need so much data in the cache in the first place. A significant OLAP
processing load would need to take place to generate that much cache
data.
Wrap up
We’ve covered a fair bit of ground on the SAP BW OLAP cache in this
post. Significant performance benefits can be achieved through the
system (and not the user) pre-filling the cache on a
regular basis as the underlying data changes. The benefits are available
to all users as the OLAP analysis results are stored in a central
repository, the OLAP Cache. Furthermore the initial hit on the database
(and subsequent delay) when the query/web template is run the first
time after the data has changed, is taken away from the user and
performed by the system at a more convenient time. A benefit that
they’ll sure be appreciative of first thing in the morning when they
arrive at their desk.
Hope you like this post and useful.
