It is very difficult to manage OBIEE cache. Cache results often get old. So we need to purge cache.
Generally we call SAPurgeAllCache() function provided by OBIEE for purging the cache.
We can automate the cache purge by using following steps:
1.Create new database in physical layer of RPD.
2.Name new database as "Cache" shown bellow. Do not forget to check "Allow direct database requests by default"
3.Create new object "Connection Pool" in newly created database "Cache".
4. Name this connection pool as "Cache Purge". DSN should be current RPD name and credentials should be of analytics user name and password.
Click OK and check consistency. Save and close RPD.
5. Create new analysis. Without selecting any subject area, select "Create Direct Database Request"
6. Use connection pool as "Database"."Connection Pool" eg. "Cache"."Cache Purge". In SQL statement block Call SAPurgeAllCache(); Refer following screenshot.
7. Save analysis as Cache Purge Report and Schedule it is as shown bellow.
8. In scheduler set frequency start date and time and so on. Please run agent as created for automated cache purge.
Comment if it helps...
Thanks,
Sagar
