Setting up data caching
You can enable data caching in a data module, and specify the cache expiry options.
The cache is populated by the result sets from SQL queries to data servers. These SQL queries originate from widgets (visualizations in dashboards, reports, stories, and explorations).
The cached result sets are reused when a subsequent request that generates exactly the same or compatible SQL statement is made. An example of a compatible SQL statement is one that's identical to another statement, except that it has one more filter.
To see how caching is used, open a dashboard, report, story, or exploration, and then open another dashboard, report, and so on, where at least one of the widgets has exactly the same or compatible SQL statement.
Results that are cached from one user’s request can be used for a different user’s request only if both users have the same data security profiles, which means that the following information is the same for both users:
- Sign-on information (user name and password) to the data server.
- Expanded values of data source connection command blocks.
- Data security defined in the data module.
- Expanded values of macros within the queries used to populate the cache.
About this task
Data caching can be enabled at a source level, or at a table level. Tables don't automatically inherit the cache options from their sources.
- No cache
Data caching is disabled.
- Automatic
Reflects the data cache option that was specified for a source. This option is available for tables only.
- Custom
Enables data caching, and allows to specify the length of time to keep the cached data.
- Macro
Enables data caching that is based on a macro.
Data caching is not applicable to OLAP cube data, data sets, and uploaded files even if the Data cache setting is available in the user interface.
Procedure
Results
Cached results are retained for the length of time that is specified in the Data cache setting. The timestamp of the cache entry is the time at the beginning of the request, the moment before the query execution starts.
When fields from different tables that have different cache settings are used together, the cache is retained as long as the cache for the table with the smallest setting. For example, if the data cache for one table is set to 5 min, and for another table to No cache, there's no caching for a visualization that uses fields from both tables.
What to do next
The Data cache setting in data modules can be overwritten in dashboards and stories. In these components, you can also enable browser data caching that allows the client applications to store the query results in the browser. Browser data caching is not available in Cognos® Analytics reports.