Automate OBIEE 11g Cache Clearing Process

Most of the OBIEE 11g leverages the Cache mechanism to provide the user with faster results . However the downfall is that if the Cache becomes stale, it might show outdated result . Hence it is necessary to clear the Cache at regular Intervals.

Cache Mangement is a huge concept in OBIEE. In this post, i would just deal with a way on clearing the OBIEE 11g Cache using Analysis.

1.) We would need a separate database conenction in the RPD, this would connect us directly to the BI server.Create a new database and choose "ODBC Basic" as the Database type. Make sure you check the direct database request checkbox.

2.) Next, create a new Connection Pool for the above Database and  in the DNS ( data source name) select the default database connection to BI server (ususally it starts as coreapplication... XXX). A user name and password is not required here .

3.) Since you have allowed the Direct database access request, you could setup permissions for it so that the security is not compromised. Click on the "permissions" tab to set it up. This step is not mandatory.

4.) Now login to Analytics ( http://yourServername:9704/analytics) . Go to the Administration Tab and make sure you have the access privileges to send "Direct Database Requests".

5.) Next, Click New - > Create Direct Database Request

6.) Mention the "Database Name"."Connection pool name" that you had created in the earlier steps . in the SQL statement, pass the function SAPurgeAllCache() which would clear the entire cache.The end output would look similar to this:

So, in this manner you could create an analysis , when executed would clear the analysis.

Note: If required we can create an Agent/iBot and call this analysis on fixed schedule which would make this entire process truly automated.


I am ref below blog:
http://obiee-sharat.blogspot.com/2013/06/automate-obiee-11g-cache-clearing.html