When using Oracle BI Applications there is often the need to invalidate OBIEE’s query cache once new data has been loaded into the Data Warehouse to prevent users from seeing stale data in the reports.
Perhaps the simplest and most elegant method to do this is to make use of the standard Event Polling Table mechanism.
With this decision made and the table set up the final part of the jigsaw is how to insert data into the polling table once the ETL has completed. There are a number of ways to do this including:
1. Editing each Informatica Workflow to include post-ETL SQL insert statements
2. Configuration of new tasks within the DAC to handle inserts
3. Triggers in the database (Oracle only) to automate the inserts
Method 1 involves too much work as it will potentially involve editing hundreds of workflows and the associated overhead to maintain this code. Method 2 would be a good solution but again involves a lot of configuration effort. I decided on Method 3 as it will be largely ‘plug in and forget’ and should be easy to set up.
First I created a new table to hold the names of those Warehouse tables which I want to be able to purge from the OBIEE cache. This is a simple 1 column table, nothing more.
CREATE TABLE BAW.S_NQ_EPT_TABLES
"TABLE_NAME" VARCHAR2(120 BYTE) NOT NULL ENABLE
The next step was to populate this new table with the names of the required tables. This you can do manually via your own scripting.
The final step is to add a trigger to the DAC metadata table W_ETL_REFRESH_DT. It is into this table that a refresh date gets stamped once the ETL completes. The trigger will fire every time the table is changed and if the refresh date is not null then it will insert a row into the polling table. Here’s the code for the trigger (note that the event polling table column names and values should match your environment):
CREATE OR REPLACE TRIGGER BAW.S_NQ_EPT_ADDROW
AFTER UPDATE ON DACINFA.W_ETL_REFRESH_DT
FOR EACH ROW
IF :NEW.LAST_REFRESH_DT IS NOT NULL THEN
MERGE INTO BAW.S_NQ_EPT P
USING (SELECT TABLE_NAME FROM BAW.S_NQ_EPT_TABLES WHERE TABLE_NAME = :NEW.TABLE_NAME ) T
ON (P.TABLE_NAME = T.TABLE_NAME)
WHEN NOT MATCHED THEN INSERT
(P.UPDATE_TYPE, P.UPDATE_TS, P.DATABASE_NAME, P.CATALOG_NAME,
P.SCHEMA_NAME, P.TABLE_NAME, P.OTHER_RESERVED)
VALUES (1, SYSTIMESTAMP, 'Oracle Data Warehouse', 'Catalog', 'dbo', T.TABLE_NAME, NULL);
I hope this helps someone out there.