Home | Ciber
Knowledge page of Ciber Netherlands

OBIEE managing the cache: 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

image

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

image

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

image

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\PurgeQueryCache.txt

image

Till Next Time

John Minkjan is a senior BI-consultant at Ciber in the Netherlands, the English text of this article is also published on his personal blog http://obiee101.blogspot.com/

4 Comments so far

  1. […] Besides using batch / shell command to purge the cache (see: http://knowledge.ciber.nl/weblog/?p=96), it’s also possible to do this automatically based on events in your ETL process. […]

  2. Sundar December 8th, 2008 15:52

    I’m looking for a way to “retain” specific cache entries and purge the rest on a daily basis…

    To do this, I need to be able to get the SQL of the cache entry…

    Any suggestions to get the sql statement of all exsisting cache entries…?

    If I can get the sql statements of existing cache entries in to a text file, then I can find a way to filter out the queries I need to retain the cache and purge others using scripts that will utilize SAPurgeCacheByQuery…

    Any other suggestion is much appreciated…

    Thanks
    Sundar

  3. John Minkjan December 11th, 2008 14:17

    Hi Sundar,

    You can set the logging level to 2 and pick the sql up from the nqQuery.log or switch on usage tracking and get it from the tacking table.

    Regards John

  4. sandeep maturi August 23rd, 2012 9:57

    i want to purge all the queries related to current date in bi server my data in db goes on changing for every 15 min so i need fresh data for current day.we are generating reports on oltp

Leave a reply