Choose a Cache Management Strategy
The choice of a cache management strategy depends on the volatility of the data in the underlying databases and the predictability of the changes that cause this volatility.
It also depends on the number and types of queries that comprise your cache and the usage those queries receive. This section provides an overview of the various approaches to cache management.
Disable Caching for the System
You can disable caching for the entire system to stop all new cache entries and stop any new queries from using the existing cache. Disabling caching lets you enable it at a later time without losing any entries that are stored in the cache.
Temporarily disabling caching is a useful strategy in situations where you might suspect having stale cache entries, but want to verify if they're actually stale before purging those entries or the entire cache. If you find that the data stored in the cache is still relevant, or after you have safely purged problem entries, then you can safely enable the cache. If necessary, purge the entire cache or the cache that's associated with a particular business model before enabling the cache again.
Cache and Cache Persistence Timing for Specified Physical Tables
You can set a cacheable attribute for each physical table, enabling you to specify whether queries for that table are added to the cache to answer future queries.
If you enable caching for a table, then any query involving the table is added to the cache. All tables are cacheable by default, but some tables mightn't be good candidates to include in the cache unless you use the Cache Persistence Time settings. For example, suppose that you've a table that stores stock ticker data that's updated every minute. You could use the Cache Persistence Time settings to purge the entries for that table every 59 seconds.
You can also use the Cache persistence time field to specify how long the entries for this table are stored in the query cache. This is useful for data sources that are updated frequently.
In Administration Tool, in the Physical layer, double-click the physical table.
In the Physical Table properties dialog, in the General tab, make one of the following selections:
To enable caching, select Cacheable.
To prevent a table from being cached, deselect Cacheable.
To set a cache expiration time, specify a Cache persistence time and specify a unit of measure (days, hours, minutes, or seconds). If you don't want cache entries to automatically expire, select Cache never expires.
Configure BI Server Event Polling Tables
BI Server event polling tables store information about updates in the underlying databases.
You can configure an application (such as one that loads data into a data mart) to add rows to an event polling table each time a database table is updated. The BI Server polls this table at set intervals and invalidates any cache entries corresponding to the updated tables. Event polling tables can be the sole method of cache management, or they can be used with other cache management schemes. Event tables offer less flexibility about choice of cache entries and the timing of purges. See Set Up Event Polling Tables on the Physical Databases.