Cache Purging–Event Polling Tables

 

We saw in our last post that setting timeouts for physical tables is not enough to ensure correctness when data changes and that some mechanism is required to signal to the OBIEE cache that it should purge old entries.

There are various ways in which this can be accomplished. In simple systems, one option would be to use the ‘Event Polling Table’

The idea behind this table is simple – it serves as a message bus. You put the names of the tables for which the data has changed. The OBIEE server polls this table for update events and purges cache entries that reference those tables. It also removes those entries from the polling table – so avoid purging them at the next polling interval.

Here is how one configures the event polling table.

Step 1: Create the polling table on the database. For Oracle, the create table statement is:

create table UET (
                UpdateType Integer not null,
                UpdateTime date DEFAULT SYSDATE not null,
                DBName char(40) null,
                CatalogName varchar(40) null,
                SchemaName varchar(40) null,
                TableName varchar(40) not null,
                Other varchar(80) DEFAULT NULL
);

Step 2: Import this table into the rpd and set up the connection pool info. Remember that this will not have user session – so needs a well defined user anme and password (i.e. :USER will not work)

Step 3: Declare this table to be the event polling table by going to Tools –> Utilities

and set the polling frequency – it is recommend that this be higher than 10 minutes. A smaller polling frequency reduces possibility of stale cache but will impact end-user throughput.

Step 4: Add code to your ETL process so that when a table is updated, rows are inserted into the event polling table. Remember to use the DB, schema and table names as defined in the rpd, not as defined on the database.

Pros and Cons

Pros

1. Event polling is a very reliable way to purge cache for only those tables that have been updated, and is fairly easy to implement.
2. Because of high selectivity – can be used in environments which have a high refresh rate. In particular, if you a warehouse where some table update frequently – say every 10 minutes - you can use implement event polling to delete entries which reference only the latter batch.

Cons

1. The entries in the event polling table reference db, schema, catalog and table names are as defined in the repository and not in the database. This means that this requires communication between the ETL and the metadata developers on a regular basis to keep this working correctly.
2. The above is compounded by the fact that entries involving aliases are not driven by the name of the base table. If you have ten aliases (say) for you your calendar table – a row needs to be inserted in the event polling table for each of those 10!
3. Since there are practical limitations on how often you can poll, this still allows stale cache hits for up to 10 minutes.
4. In any environment which has more than 1 BI server querying a single database (e.g. in a clustered environment), this can not be implemented. In such an environment the first server to find a record for DATE_D will delete its cache entries for it – but also the row in the event polling table. This means that the other servers in the cluster will never purge their cache for that table, and will continue to server old data. Users will see different results for reports depending upon which server they are routed to.