OBIEE - Schedule Purge and Re-build of cache programmatically

In a standard OBIEE implementation, we generally want to purge and re-build the cache after every ETL run. This post deals with how to programmatically purge and re-build cache and also schedule it.

Purging Cache
OBIEE provides ODBC-extension functions that can be used to purge cache programmatically. These functions can be called using NQCMD commandline tool. There are four different functions that can be used to purge cache.

• SAPurgeAllCache - Purges all cache
• SAPurgeCacheByDatabase - Purges all cache for a specific physical database
• SAPurgeCacheByTable - Purges cache for a specific physical table
• SAPurgeCacheByQuery - Purges cache for a specific query

Here's how to use the command in windows environment.

Create a text file using notepad with name "PurgeCache.txt" and add following line to it. Save the file to local disk (Let's assume to drive C:\)

Call SAPurgeAllCache();

Start command prompt and run following command. Note, there are four parameters in the command. You must replace this with values relavent to your implementation.

-d = ODBC datasource name
-u = Admin user name
-p = Admin Password
-s = sql file

NQCMD -d AnalyticsWeb -u Administrator -p Administrator -s C:\PurgeCache.txt

Since we used SAPurgeAllCache function, it will clear all cache on the data source . Refer this link for syntax of other functions.

Re-building Cache
Once the cache is purged, you may want to re-build cache for some of the frequently used reports to avoid delay in response to the users. Re-Building cache requires executing the "logical" SQL query against the data source. We can use NQCMD same way we used for purging cache. Instead of purge function, we have to include logical SQL statements in the text file.

Create a text file using notepad with name "BuildCache.txt" and add logical SQL commands to it. You can include more than one command separated by semicolon(;). Save the file to local disk (Let's assume to drive C:\). Below is example of the text file content.

SELECT Product."Department" saw_0, Product."Style" saw_1, Sales."SalesAmt" saw_2 FROM MYCATALOG ORDER BY saw_0, saw_1;

SELECT Product."Department" saw_0, Product."Style" saw_1, Sales."CostAmt" saw_2 FROM MYCATALOG ORDER BY saw_0, saw_1;

Start command prompt and execute the command same way we did for purging the cache.

NQCMD -d AnalyticsWeb -u Administrator -p Administrator -s C:\BuildCache.txt

Scheduling Purge and Re-build
You may not want to run purge/rebuild cache manually after each ETL run, so you can create a batch file using notepad, "Caching.bat" and add the two command that we ran manually to it. Save the file.

NQCMD -d AnalyticsWeb -u Administrator -p Administrator -s C:\PurgeCache.txt
NQCMD -d AnalyticsWeb -u Administrator -p Administrator -s C:\BuildCache.txt

We can schedule this file to run automatically using windows scheduling using following steps.

Go to Control Panel and double click "Schduled Tasks"

In the Schduled Tasks window, double click "Add Scheduled Task".

The Scheduled Task Wizard screen appears.
Click Next to continue.

Click Browse button on next screen. Navigate to the C:\Caching.bat and click Open button.
Next, you give the task a name and choose when to run the task.

Name the scheduled task.
Pick a name that will make sense to you later when you want to check the list or make any changes.

Choose when to run the scheduled task.
There are a number of choices as to when you would like to run the task:

Daily – once a day
Weekly – once a week
Monthly – once a month
One time only
When computer starts
When I log in

Click "Next" to continue.
Specify the day and time to run the scheduled task.

Click "Next" to continue once you have made your time selections.
Enter the name and password of the user who is authorized to run the task. Generally Administrator.

Click Next to continue.

Click Finish to complete scheduling the task.