Thursday, March 13, 2008

OBIEE Manage the cache part 1

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

Till Next Time

17 comments:

Francisco said...

John, It was a great post to understand how to remove the cache. As we all know is a great feature but sometimes it can be annoying. I would like to know if it is possible to remove the cache directly from the OBIEE user interface. I constantly have the problem that new data is added to the base tables but OBIEE uses the cache and does not include the new data. Giving the user the ability to clear the cache from the dashboard would be great.
Thanks.
Francisco

John Minkjan said...

Hi Francisco,

Sorry for the late replay... Have a look at this article:
http://obiee101.blogspot.com/2008/07/obiee-managing-cache-emptyingpurging.html

Hope This Helps

John

Ziky said...

Hi John and Matt,

Can we do the reverse of the same process. Can we seed the cache with OBIEE report queries using C Prompt. Our case is a bit tricky. We cant use IBots as we dont have license for it and we want the OBIEE admin to run these queries at the application server and seed these for different users without knowing their passwords.

Ritesh said...

Hi John,

Just wanted to verify whether it is typo mistake or correct instruction for the last option. i.e. purge By Database name. The execution says -

You can execute this from the commandline: nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s c:\obiee\mscripts\PurgeQueryCache.txt

Which should be PurgeDataBaseCache.txt instead of PurgeQueryCache.txt. Is that correct?

John Minkjan said...

@Rits,

Thanks for noticing, it was a typo, now corrected.

regards

John

Marc said...

Hi, this is great information. However, can this be done in Windows based OS too?

John Minkjan said...

@Marc

All demos in this article are doen from a command box on a windows2003 box.

regards

Unknown said...

Hi John,

It is very helpful to implement Cache Purging.
Great Post for understanding.

If Possible could you post about the LDAP Authentication, How do we implement it in OBIEE including LDAP TABLES and also about Security in detail so that we can work on it easily.

Thanks and Regards
Swapna

vipul said...

i have a doubt...its from other topic but related to cache management....suppose our event poll table has been updated yesterday and the BI server has now polled the table then will it purge the cache???

and one more doubt..can we purge the event polling table??

if possible please mail me the answers my email id is vipul7287@yahoo.com....i need the answers please reply soon.

vipul said...

d one more thing... what is the difference between purging cahce by database and purging the whole cahe???

John Minkjan said...

@vipul,

"what is the difference between purging cahce by database and purging the whole cahe???"

OBIEE can query multiple databases at once so if you have only refreshed one DB you don't need to purge the whole cache.

regards

John

Uli Bethke said...

Very helpful.

Is it possible to encrypt the password when executing nqcmd?

ayaps said...

Is it possible to encrypt the password-when supplying to nqcmd ?

John Minkjan said...

@ayaps not that I'm aware off.

regards

Mahee said...

Hi John,, Thanks alot for this post. Can i know how can we set the automatic purging at rpd level

Abhijeet Nazar said...

@Mahee
You can schedule cache purge using job manager.
Use admin credentials to login to Job manager. Create new task using NQCMD and give path of the file created from above steps.

hunt4nothing said...

Is it possible to Seed the BI server cache using the NQCMD command ? If so can someone point to any details ?

The cache seeding via ibots needs to be scheduled and though it can be run conditionally, it wouldnt poll/check for the condition at any time other than the scheduled time. If for some reason the ETL run takes an hour later, then the scheduled ibot will evaluate the condition to false and the cache will not be seeded.

thanks,
sandeep.