Cubing — Result set caching

When you run an MDX query and a result set is returned, the result set is cached. For Cubing Services users, this improves response time for queries against both regular and virtual cubes. For Cognos® users, response time improves if the results of an MDX query can be returned from the cache rather than running the query again on the server.

For Cubing Services, result set caching is enabled by default. The result sets from most queries are cached, except if they contain a session-scoped name set or a session-scoped calculated member. Because those items are session-scoped, no other session can use their result sets, and no result sets are cached.

For each cube, you control the query results cache using a Java Virtual Machine (JVM) parameter. You set the JVM parameter in the Create Cube Server Wizard in the Administration Console:

enableResultSetCache

This parameter disables or enables the result set cache for all cubes and virtual cubes. The cache is enabled by default. The settings for this parameter are true and false.

The following example disables the results set cache:

-DenableResultSetCache=”false”

How the result set cache is cleared

All cubes, including virtual cubes, have their own result set cube cache. These result set caches are stored on disk in the same location as the data cache in the resultsetcache directory. If you change the location, new cache files and subdirectories will be created in the new directory after the cube server is restarted, but the cache files and subdirectories in the old directory will not be deleted.

The result set cache is cleared under the following conditions:

When a regular cube is refreshed.
When a base cube of a virtual cube is refreshed. The result set cache is cleared for all dependant virtual cubes too.
When security is refreshed on any cube (regular cube, base cube, or virtual cube).
When a cube is stopped or started.
When a Cubing Services server is restarted.
When you run the +clear command in administrator mode from the mdxcmd command line.

The files and subdirectories on disk under the alternative directory are only deleted when the cache for the cube is switched over during a refresh.

Performance improvements

You will see improved performance in these situations:

If you run a query that was run previously, and a result set already exists for that query, then Cubing Services returns results from the cache instead of running the query again. This can happen even if the first query was run by some other user, as long as you both have the same security access.
If you run a query that is already running, and the security settings for the queries are identical, then only one instance of the query is actually run. The other instances of the query wait for the first instance to complete.
In a Cognos dashboard, you can have multiple sub-windows that show different views of data. However, these views are just subsets of a larger result set from a single larger query that is run once for each sub-window. Having the result of the larger query in a cache prevents that query from being run repeatedly.
When you create a Cognos report in another format, such as PDF, HTML or spreadsheet format, the formatting change causes the same query to be run again. Reformatting reports is faster when you have the query results in a cache.