Best Practices for Cache in OBIEE

Disk Space

The query cache requires dedicated disk space. How much space depends on the query volume, the size of the query result sets, and how much disk space you choose to allocate to the cache. For performance purposes, a disk should be used exclusively for caching, and it should be a high performance, high reliability type of disk system.

Administrative Tasks

There are a few administrative tasks associated with caching. You need to set the cache persistence time for each physical table appropriately, knowing how often data in that table is updated. When the frequency of the update varies, you need to keep track of when changes occur and purge the cache manually when necessary. You can also create a cache event polling table and modify applications to update the polling table when changes to the databases occur, making the system event-driven.

The Oracle BI Server also provides ODBC-extension functions for purging cache entries

programmatically. You can write your own scripts to call these functions at the appropriate times.

The ODBC functions are :

SAPurgeCacheByQuery()

SAPurgeCacheByTable()

SAPurgeCacheByDatabase()

SAPurgeAllCache()

Keeping the Cache Up To Date

If the cache entries are not purged when the data in the underlying databases changes, queries

can potentially return results that are out of date. You need to evaluate whether this is acceptable. It might be acceptable to allow the cache to contain some stale data. You need to decide what level of stale data is acceptable and then set up (and follow) a set of rules to reflect those levels.

For example, suppose your application analyzes corporate data from a large conglomerate, and you are performing yearly summaries of the different divisions in the company. New data is not going to materially affect your queries because the new data will only affect next year’s summaries. In this case, the tradeoffs for deciding whether to purge the cache might favor leaving the entries in the cache.

Suppose, however, that your databases are updated three times a day and you are performing

queries on the current day’s activities. In this case, you will need to purge the cache much more

often, or perhaps consider not using it at all.

Whatever your situation, you need to evaluate what is acceptable as far as having noncurrent

information returned to the users.