Latest News

Thursday, February 14, 2013

OBIEE - Purging or Disabling the Query Cache

This article tells how to purge the Query Cache


Disabling Caching

To Disable the entire application Cache

To disable completely the query cache, you must set to the ENABLE cache parameter the value NO in the file nqsconfig.
[Cache]

ENABLE = NO;

1) For one session

For one session, you have to set the session system variable with the value 1:
  • DISABLE_CACHE_HIT
  • DISABLE_CACHE_SEED

2) For one request / answer

For one session, you have to set this request variables with the value 1:
  • DISABLE_CACHE_HIT
  • DISABLE_CACHE_SEED
Example in the advanced tab of an answer :

Purging Cache Techniques

1) Using ODBC functions

Create a file PurgeAllCache.sql and add the SAPurgeAllCache ODBC Function :
{call SAPurgeAllCache()};
And save it in a directory (for instance OracleBI_Home\Cache).
Then from a command dos, call nqcmd with this statement :
OracleBI_Home\server\Bin\nqcmd.exe -d AnalyticsWeb -u Administrator -p Password 
-s "OracleBI_Home\Cache\PurgeAllCache.sql" -o "OracleBI_Home\Cache\PurgeAllCache.log"
You will retrieve then in the PurgeAllCache.log :
{call SAPurgeAllCache()}
----------------------------------------------------------------
RESULT_CODE  RESULT_MESSAGE
----------------------------------------------------------------
1            [59118] Operation SAPurgeAllCache succeeded!
----------------------------------------------------------------
Row count: 1
----------------------------------------------------------------
Processed: 1 queries
You can also use the ODBC Bi Server cache Function in a script that can call the job manager.

2)Using Dynamic Repository Variables

In the Server Administration Guide, section ”Understanding and Creating Repository Variables”, you can read that :
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 means that the query cache will be purged when the variable is refreshed. By creating a Dynamic Repository Variable that selects a specific value from a table you can easily easily trigger a cache purge.
For instance, from an ETL process :
  • create a date column to contain the last update date from your datawarehouse
  • then create a dynamic variable to refresh this value (every 10 minutes).
  • then when the value change, the cache is automatically purged

3) Manually via the cache manager

  • Open the Administration Tool
  • In the Menu : Manage / Cache

Via web service

OBIEE: as a SOAP API that you can use to purge the cache  :OBIEE - Web Services API (SOAP,WSDL)

Via the event pooling table

The use of an Oracle BI Server event polling table (event table) is a way to notify the Oracle BI Server that one or more physical tables have been updated. The Oracle BI Server cache system reads rows from, or polls, the event table, extracts the physical table information from the rows, and purges stale cache entries that reference those physical tables.

by setting the Cache Persistence Time

You can purge the cache automatically for a particular table, by setting the cache Persistence Time field in the Physical Table.
  • Google+
  • Pinterest
« PREV
NEXT »

2 comments

  1. nice piece of information, I had come to know about your internet site from my friend vinay, delhi,i have read atleast 12 posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new post, once again hats off to you! Thanx a ton once again, Regards, Obiee online training

    ReplyDelete
  2. awful piece of information, I had come to know about your blog from my friend vimal, mumbai,i have read atleast 13 posts of yours by now, and let me tell you, your blog gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a million once again, Regards, obiee training in hyderebad

    ReplyDelete