OBIEE 自动清理缓存– Cache Purging/Seeding

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.

Purging Cache:

Purging can be done using the following functions:

• 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 “Purging.txt”. Save the file to local disk (Let’s assume to drive D:\Cache)

 

 

 

 

 

Add Call SAPurgeAllCache(); to it.

 

 

 

 

Save the file.

Then from a command dos,navigate to the path where the NQCMD.exe file is located(say D:\OBIEE11G\Oracle_BI1\bifoundation\server\bin) call nqcmd and run following 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 weblogic -p weblogic1 -s D:\Cache\Purging.txt

Since we used SAPurgeAllCache function, it will clear all cache on the data source .

 

 

 

 

 

 

Seeding Cache:

In order to seed the cache we can use NQCMD same way we used for purging cache. Instead of purge function we use Call SASeedQuery() function including logical SQL statements in the text file as shown below:

 

 

 

 

Run the NQCMD command again and check whether the cache is seeded as .TBL files into the cache folder located under<MWHome>\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\cache

 

 

 

 

 

Purging Cache using Issue SQL:

Navigate to the Issue SQL by clicking on the Administration link as shown below:

 

 

 

 

 

Enter the Call SAPurgeAllCache() function in the Issue SQL window and click on Issue SQL Button as shown below:

 

 

 

 

 

See that the Cache entries will be purged both in the cache folder and in the RPD.

Similarly for Seeding issue the Seeding function and check the results.

Happy Purging……………

Thanks,

HaRisha.