I’ve found a lot of references to purging OBIEE cache using dynamic variables to assist with automating our cache purge process (to find a way to trigger the cache purging after ETL completes)
Most references quote the Oracle admin guide: “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 was a bit unclear. How does a business model need to reference a variable in order for this to work? Is it truly ALL entries from a business model, or only those entries that reference the variable? Perhaps it’s not as strict, and only cache entries from a logical table referencing a variable are purged? I decided to do a test.
First, I created a new init block and dynamic variable. For this test, the variable selected sysdate from dual, refreshing every ten minutes.
I then created a logical column in one of our business models (in the logical table ‘FACT – POS Sales’) , deriving the column as the value of the variable:
After deploying the RPD, I ran several queries, ensuring that some reports were from the business model I modified, some from a logical table with no reference to the variable (but from the same business model) as well as some entries from a second business model without any reference to the variable. I then waited for the 10 minutes to elapse for the variable to refresh.
Cache entries before cache purge:
After the refresh, I found that all entries from the business model that did not include any reference to the dynamic variable remained, while all others had been purged.
Cache entries after variable refreshed and cache purge:
So it appears that using dynamic variables to initiate a cache purge will result in an all-or-nothing purge for all queries referencing a single business model. This would work for repositories that are set up with a single business model per data set, but not for those where a business model can contain multiple data sets that are loaded via ETL over a longer course of time.
I referenced the following for guidance on this test:
http://gerardnico.com/wiki/dat/obiee/query_cache_purging#with_dynamic_repository_variables
http://docs.oracle.com/cd/E25178_01/bi.1111/e10541/querycaching.htm#i1018704