OBIS - Purging / Disabling the Query Cache

Bi Server Architecture

About

How to purge the Query Cache

For the Presentation Server cache, see Cache

Disabling

For the entire application

To disable completly the query cache, you must set to the ENABLE cache parameter the value NO in the file nqsconfig.

[CACHE]

ENABLE = NO;

For one session

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

  • DISABLE_CACHE_HIT
  • DISABLE_CACHE_SEED

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 :

Obiee Set Disable Cache Hit

SET VARIABLE DISABLE_CACHE_HIT=1;

Purging

Via ODBC functions

Note : Replace OracleBI_Home by your own directory.

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.

When failed:

call SAPurgeAllCache()
----------------------------------------------------------------
RESULT_CODE  RESULT_MESSAGE
----------------------------------------------------------------
59115        [59115] Operation not performed because caching is not enabled.
----------------------------------------------------------------
Row count: 1
----------------------------------------------------------------

With 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

Manually via the cache manager

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.

More … OBIEE 10G/11G - Event Pooling table (Event Table) to purge the cache after ETL process

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.

Documentation / Reference





Discover More
Obiee Dashboard Prompt Scope
OBIEE - Request variable

Request Variables are used to update Session Variables in the scope of a logical sql. You will find it in a OBIEE logical Sql in its SET VARIABLE part. For instance : Its aim is to set an OBI server...
Obiee11g System Session Variable
OBIEE - System session variables (reserved variables)

System session variables are session variables that the Oracle BI Server and Oracle BI Presentation Services use for specific purposes. System session variables have reserved names, that cannot be used...
Obiee Product Table Location
OBIEE 10G/11G - Event Pooling table (Event Table) to purge the cache after ETL process

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 and then that the query cache entries are stale....
Obiee Agent Cache Seeding
OBIS 10G/11G - BI Server (Logical Query|Result) Cache

Query caching is a persistent cache mechanism. When you ask for data with a query, the data are retrieved from the cache instead to ask again the data source. The BI Server Query Cache The Presentation...



Share this page:
Follow us:
Task Runner