OBIEE Manage the cache part 1
Post date: 15-Jan-2010 06:45:34
@ Copyrights reserved to john minkian #compiled from
Emptying/Purging the cache
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.
For a customer I made a couple of handy scripts for handling the 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
// John Minkjan Ciber Nederland
// 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
// John Minkjan Ciber Nederland
// FileName: PurgeTableCache.txt
// executed by cmd string:
// nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s
c:\obiee\mscripts\PurgeTableCache.txt
Call SAPurgeCacheByTable( 'JJMORCL_SH', NULL, 'SH', 'TBLTRUCKS' );
You can execute this from the commandline: nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s c:\obiee\mscripts\PurgeTableCache.txt
WOW: the database, schema, (catalog), and tablename are the names in OBIEE not in the database.
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
// John Minkjan Ciber Nederland
// FileName: PurgeQueryCache.txt
// executed by cmd string:
// nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s
c:\obiee\mscripts\PurgeQueryCache.txt
Call SAPurgeCacheByQuery('SELECT TBLFUELCONSUMPTION.LICENSEPLATE,
TBLFUELCONSUMPTION.MEASUREDATE, TBLFUELCONSUMPTION.FUELCONSUMPTION FROM Trucks
WHERE TBLFUELCONSUMPTION.MEASUREDATE <= TIMESTAMPADD(SQL_TSI_YEAR, -1,NOW())'); // The "query" line must be one contiues line! You can execute this from the commandline: nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s c:\obiee\mscripts\PurgeQueryCache.txt
WOW: the "query" is the OBIEE one not the database one!
4 Purging the cache by database
Put the following in a .txt. file in your maintenance directory:
// Purge cache by Database
// John Minkjan Ciber Nederland
// FileName: PurgeDataBaseCache.txt
// executed by cmd string:
// nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s
c:\obiee\mscripts\PurgeDataBaseCache.txt
Call SAPurgeCacheByDatabase( 'JJMORCL_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