Purging / Disabling the Query Cache

We always need to play around with cache during OBIEE development. Be it disabling it for a particular report or purging for the entire application or a particular table etc. Here we deal with cache related tasks in OBIEE.

DISABLING CACHE

For the Entire Application:

• Log into your enterprise manager
• Navigate to Business Intelligence -> coreapplication
• Select Capacity Management tab and go to the Performance sub tab
• Once here click ‘Lock and Edit Configuration’       
• Now you will see a ‘Cache enabled’ tick box.  Untick it disable cache for the entire application.

• Once this is performed, you will have to activate the changes and then restart services for this to take effect.

From the OBIEE Application one can purge all the cache by doing the following:

• Go to Administration

·         Navigate to Issue SQL

• 
  

         Key in:  Call SAPurgeAllCache()

•     Click “Issue SQL”
  
  
  You can also clear the cache of specific databases.
  
  Call SAPurgeCacheByDatabase( ‘<DB_NAME>’ );

For a Particular Session:

For a particular session you will have to set the system session variable with the value 1.

• DISABLE_CACHE_HIT
• DISABLE_CACHE_SEED

For a Particular Analysis:

For a particular analysis, you will have to set the request variable with the values 1.

• DISABLE_CACHE_HIT
• DISABLE_CACHE_SEED

Once this is written in the prefix, click ‘apply’ and the query will not be taken from cache. You can also go without specifying the log level. In that case the query will be generated using the existing log level.

PURGING CACHE

Using Dynamic Repository Variable:

When the value of a dynamic repository variable changes, all cache entries associated with a business model that reference the value of that variable will be purged automatically. This pretty much means that when the variable is refreshed, the cache will be purged.

For Example, if you need your cache purged every time an ETL process happens, create a dynamic repository variable which gets populated with the last_updated_date  from your warehouse.

Manually using Cache Manager:

• Open the rpd in online mode.
• Go to Manage -> Cache
• You will see the different Business Models. You can either purge the cache for a particular Business Model or the entire rpd. 

Using Event Polling Tables:

Event Polling table is a mechanism of notifying the OBI Server that one or more of the physical tables have been updated. The OBI server cache system reads rows from event table, extracts the physical table information from the rows, and purges cache entries that reference those physical tables.

Setting Cache Persistence Time:

For a particular table, you can purge the cache automatically by setting the cache persistence time in Physical Table. It can be in Days, Hours, Minutes or seconds duration.

Cheers,

 Gautam