[OBIEE Issue] Whole Cache Purging Scripts

Be sure to set your Oracle BI home in the ‘.profile’, as the base installed directory of OBIEE, on the OBIEE Server. The following script would reference it as ORACLEBI

I will create a temporary “purge-log”  folder under Oracle BI Home that would store the log information of the cache purge.

1.You need to put the following lines into a .sql file. I call it purgecache.sql and put this file under purge-log folder temporarily.

// The below script should be run at the terminal to purge the OBIEE Cache.
// The following command should run from $ORACLEBI/server/Bin64
// nqcmd -d AnalyticsWeb64 -u Administrator -p -s /purge-log/purgebicache.sql
Call SAPurgeAllCache()

2.The script for purging all BI Server cache is shown below and I name it as cachepurge.sh.

# Custom Script for Purging OBIEE Server Cache
# Usage ./cachepurge.sh <OBI_Administrator_Password>
# Oracle BI Home is the Root Directory where OBIEE is installed
# and this parameter should be set in the profile
c1=`pwd`
echo OracleBI Home Directory is $ORACLEBI
cd $ORACLEBI/setup
echo current directory is `pwd`
. common.sh
. sa-init64.sh
echo SAROOTDIR is $SAROOTDIR
#echo 2
cd $ORACLEBI/server/Bin64
echo current directory is `pwd`
# script to run nqcmd
date >> $SAROOTDIR/purge-log/cacheoutput.log
echo ——– >> $SAROOTDIR/purge-log/cacheoutput.log
echo ‘OBIEE Whole Cache Purging’ >> $SAROOTDIR/purge-log/cacheoutput.log
echo ——– >> $SAROOTDIR/purge-log/cacheoutput.log
nqcmd -d AnalyticsWeb64 -u Administrator -p $1 -s $SAROOTDIR/purge-log/purgebicache.sql >> $SAROOTDIR/purge-log/cacheoutput.log
echo ‘script completed’

cd c1

4.You could run the script anywhere once you set up your Oracle BI home. Suppose I put the script under purge-log folder as well.The format to run the Cache Purging scripts is

$cd $ORACLEBI/purge-log
./filename.sh OBI_Administrator_password
Eg: To clear the OBIEE Cache as a whole, run the following command assuming ‘Administrator’ as the password
./cachepurge.sh Administrator
Advertisements

4 thoughts on “[OBIEE Issue] Whole Cache Purging Scripts

  1. Is there a way to Purge specific queries or Queries containing specific values like Dates.

    We have a requirement such that the requests for current day should not be cached.

  2. Hi, Rampradeep
    If I understand you correctly that you want to present the current day dynamically on your dashboard, what you can do is that you could create a dynamic repository variable in the rpd and initialize this variable by your customized query retrieving the current date from database.

    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

    Hope this would answer your question!

    Best Regards,
    Claire

  3. Hi Claire,
    This is a good idea,however it will not serve my purpose.

    We want the requests for previous days to be cached,and when the end user selects for current day i need the request to go to DB.

    We are working on OLTP,Client requirement is to see current data and this should not effect the performance.

    So,we are trying to build query cache for previous days so the request for previous days data will be from cache ,and if the request is for current day it must go to DB.

    Thanks for any ideas.

    Regards,
    Rampradeep

  4. too good piece of information, I had come to know about your site from my friend sajid, bangalore,i have read atleast 11 posts of yours by now, and let me tell you, your web-page 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 post, once again hats off to you! Thanks a lot once again, Regards,obiee online training

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s