Cache Purging – API calls

 

In the last post, we saw that you can take control over your cache entries and purge them on a table by table basis. However, if you are like most BI departments, your tables do not have independent refresh frequencies. You probably run ETL once (twice? three time?) a day, and that ETL updates nearly every table in the warehouse. In this case, clearing call is simply issuing the following command via odbc to the BI server at the end of your ETL process.

 

Call SAPurgeAllCache()

 

Of course, if you have more than one BI server (e.g. in a cluster) you would have to issue this more than once – once for each server.  There are also API calls for purging cache by database/table but are required less often. If you do need to use these APIs see this great post by John.

Pros and Cons

Pros

1. This is much simpler to implement than event polling especially in environments with more than 1 BI server. There is no need for a database table with the event polling table. No need for separate schemas for each BI server, and no communication between the ETL tools and metadata except a one-time (hopefully) handoff of a list of BI servers to your ETL developer.
2. Since this is a call directly into the BI server, there is no latency whereas with event polling entries could stay in the cache for an extra 10 minutes (polling interval).

Cons

1. This is a syncronous API. If a BI server happens to be down – for administration or due to unintended failure – it might never receive this call, and will serve stale data when it brought online later.
2. It is hard to add/remove servers to the cluster without modifying ETL scripts. Moreover, if another BI server points to the same environment – say the QA environment – it will never have its cache purged.