Wednesday, 15 February 2012

Cache in OBIEE!!

Different ways to Manage Cache in OBIEE!!
One of the most powerful features of OBIEE is the way it uses it’s cache. Good cache management can really boost your performance. From the system management point of view there are a couple of tips and tricks to influence the cache performance.
Here are few Examples of Managing Cache.
1. Purging the whole cache.
If you have a completed database reload or want to do some performance testing with your repository you might want to purge the whole cache.
Put the following in a .txt file in your maintenance directory
// Purge complete cache
// executed by cmd string:
// nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s
c:\obiee\mscripts\purgecompletecache.txt Call SAPurgeAllCache()
You can execute this from the commandline: nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s c:\obiee\mscripts\purgecompletecache.txt
2. Purging the cache by table
If you have a major update of your dimensional tables you might want to clear the cache for just one table.
Put the following in a .txt file in your maintenance directory:
// Purge complete cache
// FileName: PurgeTableCache.txt
// executed by cmd string:
// nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s
Call SAPurgeCacheByTable( ‘AQIES_SH’, NULL, ‘SH’, ‘TBLTRY’ );

You can execute this from the commandline: nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s c:\obiee\mscripts\PurgeTableCache.txt
3. Purging the cache by query

Sometimes you only want to purge only “old” data from your cache.
Put the following in a .txt. file in your maintenance directory:
// Purge cache by Query
// FileName: PurgeQueryCache.txt
// executed by cmd string:
// nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s
Call SAPurgeCacheByQuery(’SELECT * FROM Transactions
WHERE Transactions.Date_entered <= TIMESTAMPADD(SQL_TSI_YEAR, -1,NOW())’); // The “query” line must be one continues line! You can execute this from the commandline: nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s c:\obiee\mscripts\PurgeQueryCache.txt

4 Purging the cache by database
Put the following in a .txt. file in your maintenance directory:
// Purge cache by Database
// FileName: PurgeDataBaseCache.txt
// executed by cmd string:
// nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s
Call SAPurgeCacheByDatabase( ‘AQIES_SH’ );
// The “dbName” is the OBIEE name!

You can execute this from the commandline: nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s c:\obiee\mscripts\PurgeDataBaseCache.txt
You can also purge cache direct from Dashboard by following the given steps.
Goto  Settings—>Administration —> Issue sql
call sapurgeallcache() and click issues sql..below is the screen shot

PRESENTATION CACHEBy default, the cache files for the presentation server reside in the \tmp directory within the respective subdirectories sawcharts, sawrptcache and sawvc; while the xml cache files lying in the \tmp folder itself.   

Chart Cache - \OracleBIData\tmp\sawcharts\
Report Cache - \OracleBIData\tmp\sawrptcache\
State Pool Cache - \OracleBIData\tmp\sawvc\
XML Cache - \OracleBIData\tmp\
See image below for an example of how to modify the instance config to explicitly change the default presentation cache directory locations. 

Additionally, and only specific to the XML Cache directory location change, you must also make a change to the nqconfig file as follows:

WORK_DIRECTORY_PATHS = "C:\DataSources\Cache\tmp";

In instanceconfig.xml file ( \OracleBIData\web\config\instanceconfig.xml )


In instanceconfig.xml file ( \OracleBIData\web\config\instanceconfig.xml )


Sometimes you want to bypass the presentation / cache for development purposes. Or more often when you get weird write back behaviour. Add this to the instanceconfig file:

Via Oracle BI Web Application
Settings-->Administration-->Reload Files and Metadata-->Finished


Via Server Service
Shut down Presentation Services to remove the files for the Presentation Cache

**If you delete cache files when Presentation Services are still running or SAW does not shut down cleanly, then various cache files might be left on disk.

By default, the BI Server Cache is stored in the \OracleBIData\cache\ directory and stored as NQS*.tbl files. 


NQS(Prefix)_VMVGGOBI(Originating Server Name)_733547(Days passed since 1-1-0000)40458(Seconds passed since last midnight)_00000006(Incremental number since last BI server start).TBL

To enable the query cache for the entire application, you must set the ENABLE cache parameter value to YES in the file nqsconfig.  ( \OracleBI\server\Config\NQSConfig.INI )

To disable the query cache for the entire application, you must set the ENABLE cache parameter value to NO in the file nqsconfig.  ( \OracleBI\server\Config\NQSConfig.INI )

In Answers, goto Advanced Reporting tab when building report, set Prefix value to following:

To clear the BI Server Cache files, run the following command file:  \OracleBI\server\cache_purge_reseed\call.bat  

This calls the purge.txt file, which simply contains the following command:
call SAPurgeAllCache();

This will clear the BI Server Cache (.TBL) files in the Cache directory: 


An alternate way of doing this is via the OBI Admin Tool, using the Cache management feature.


...When a dynamic repository variable is updated, cache is automatically purged. This is designed behavior. Cache will be invalidated (i.e. purged) whenever the initialization block that populates dynamic repository variable is refreshed. The reason that refreshing a variable purges cache is that if a variable was used in a calculation, and the variable changed, then cache would have invalid data. By purging cache when a variable changes, this problem is eliminated.

Since this is the designed functionality, Change Request 12-EOHPZ3 titled ‘Repository variable refresh purges cache’ exists on our database to address a product enhancement request. The workaround is to go through the dynamic repository variables and verify that the variables are being refreshed at the correct interval. If a variable needs to be refreshed daily, there may be a need to set up a cache seeding .bat file that runs after the dynamic variable has been updated. If the cache seeding .bat file runs prior to the refresh of the dynamic variable refresh, then the cache will be lost.

OBIEE cache is enabled, but why is the query not cached?...

Non-cacheable SQL function:  If a request contains certain SQL functions, OBIEE will not cache the query. The functions are CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, RAND, POPULATE. OBIEE will also not cache queries that contain parameter markers.

Non-cacheable Table:  Physical tables in the OBIEE repository can be marked ‘non-cacheable’. If a query makes a reference to a table that has been marked as non-cacheable, then the results are not cached even if all other tables are marked as cacheable.

Query got a cache hit: In general, if the query gets a cache hit on a previously cached query, then the results of the current query are not added to the cache. Note: The only exception is the query hits that are aggregate “roll-up” hits, will be added to the cache if the nqsconfig.ini parameter POPULATE_AGGREGATE_ROLLUP_HITS has been set to Yes.

Caching is not configured: Caching is not enabled in NQSConfig.ini file.

Result set too big: The query result set may have too many rows, or may consume too many bytes. The row-count limitation is controlled by the MAX_ROWS_PER_CACHE_ENTRY nqsconfig.ini parameter. The default is 100,000 rows. The query result set max-bytes is controlled by the MAX_CACHE_ENTRY_SIZE nqsconfig.ini parameter. The default value is 1 MB. Note: the 1MB default is fairly small. Data typically becomes “bigger” when it enters OBIEE. This is primarily due to Unicode expansion of strings (a 2x or 4x multiplier). In addition to Unicode expansion, rows also get wider due to : (1) column alignment (typically double-word alignment), (2) nullable column representation, and (3) pad bytes.

Bad cache configuration: This should be rare, but if the MAX_CACHE_ENTRY_SIZE parameter is bigger than the DATA_STORAGE_PATHS specified capacity, then nothing can possibly be added to the cache.

Query execution is cancelled: If the query is cancelled from the presentation server or if a timeout has occurred, cache is not created.

OBIEE Server is clustered: Only the queries that fall under “Cache Seeding” family are propagated throughout the cluster. Other queries are stored locally. If a query is generated using OBIEE Server node 1, the cache is created on OBIEE Server node 1 and is not propagated to OBIEE Server node 2
Thanks to-

Thanks to-

OBIEE Purge/Clear Cache using Answers

I found this one quite useful to clear the cache using an Answer request, its very handy and can be scheduled to run on a regular basis as well. I have tried this using OBIEE 11g and works, guess it should work in 10g as well, as the concept is very basic.
Just follow the steps here
1. Open the RPD, create a new database and select Allow direct database requests by default
Name: BIServer and Database: ODBC Basic
2. Create a connection pool with no username and password, set the right permissions on the connection pool
connection pool name – AnalyticsWeb
Select ODBC 2.0 and the DSN is AnalyticsWeb (this should match with the entry in odbc.ini)
Save the RPD.
4. Create a new Answer (or Analysis in 11g) and select “Create a New Direct Database Request”
In the connection pool, enter “BIServer”.”AnalyticsWeb” (database.connection pool)
In the sql statement, enter the command
Call SAPurgeAllCache();
Clicking on “Validate SQL and Retrieve columns” should retrieve couple of columns as shown.
Save the request and that’s it, this request can now be run anytime to purge the cache or even scheduled.


  1. Amazing post !!! .. did not know till now that there are API's to control cache

  2. Very informative !

  3. really good piece of information, I had come to know about your site from my friend shubodh, kolkatta,i have read atleast nine posts of yours by now, and let me tell you, your site 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 lot once again, Regards, obiee training in hyderabad


Thanks to Comment