Implementing Static Cache

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Improve Calculation View Performance with Static Cache

Caching View Results

Complex calculation views place a heavy load on the system. To reduce this, it is possible to cache the results of a calculation view in order to speed up the time needed to get the results when executing a query against the view. This feature is useful when you have complex scenarios or large amounts of data so that the results do not have to be recalculated each time the query is executed. The benefits are not just the reduced time for the results to appear for the user of the query, but it also means CPU and memory consumption is reduced leading to better performance for other tasks.

If the cache cannot be used, due to a query requesting a different data set than what is held in cache, a query does not fail. It means that the query will need complete processing.

Caching should not be applied to raw data but to data that has been highly processed and reduced through aggregations and filtering. In practice, this means applying caching to the top-most nodes of a calculation view.

Cache can only be used for calculation views that do not check for analytic privileges. This means analytic privileges should be defined on the top-most view only, in a calculation view stack. The optimal design would be to define the cache setting at the highest calculation view possible in the stack, but not at the very top where analytic privileges are checked. This means that the user privileges are always checked against the cached data, if the cache is useable.

It is possible to fine-tune the calculation view cache using column and filter settings.

Firstly, calculation view caching can be defined at the column level. This means queries that use only a sub-set of the cached columns can be served by the cache. It is recommended to cache only the columns that are frequently requested in order to reduce memory consumption and speed up cache refresh. If you do not specify columns in the cache settings, then all columns are cached.

The cache size can be further reduced by defining filters in the cache settings. Queries that use either exactly the cache filters, or a subset of the filters, are served by the cache. Cache is only used if the query filter matches exactly the filter that is defined for the cache, or reduces the data further.

It is possible to define a retention period of the cache so that the cached data expires. The value entered is in minutes.

Note
It is currently not possible to define that cache should be invalidated if new data is loaded to the underlying tables. This feature is expected to come later. For now we just have a time-based expiry (in minutes).

In order to use the calculation view static cache there are some important prerequisites that must be met.

Firstly, the basic setting Enable Cache must be set in the calculation view to be cached.

Then, the query must be able to be fully-unfolded. This means that the entire query must be fully translatable into a plain SQL statement and not require the calculation of interim results. You can use the Explain Plan feature of the SQL Analyzer to check if unfolding can occur.

There must be no calculations that depend on a specific level of calculated granularity as the cache is stored at the level of granularity requested by the first query. This may not align to the aggregation level required by subsequent queries and could cause incorrect results.

Even with these prerequisites met, the cache feature is disabled by default and is only considered when explicitly invoked by using one of the following RESULT_CACHE hints:

If the prerequisites for cache usage are not met, it might still be possible to force the cache to be used. You can use the setting Force so that the cache is used. However, you should test extensively to ensure that correct results are returned.

Save progress to your learning plan by logging in or creating an account