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.

The following data cache options can be specified for sources and tables:
  • 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

  1. From Team content or My content, open a data module.
  2. To specify data caching for a source, do the following steps:
    1. Click the Source view pane Source view icon. to expand it, and locate the source.
    2. From the source context menu, select Data cache.
    3. Specify one of the cache options, and click OK.
  3. To specify data caching for a table, do the following steps:
    1. In the Data module pane, select one or more tables, and from the context menu, click Properties.
    2. Under Advanced properties, locate the Data cache property.
    3. Specify one of the cache options, and click OK.

      The Automatic cache option reflects the cache option that was specified in the source. For example, if the source data cache was set to Custom, with the time limit of 1 day, the automatic cache option for the table is shown as Automatic (1 day). If multiple tables are selected, the cache option of the table that was selected first is shown as the automatic option for the selected group of tables.

  4. Save the data module.

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.