OBIEE managing the cache: Emptying/Purging the cache Part 2

Besides using batch / shell command to purge the cache (see: http://obiee101.blogspot.com/2008/03/obiee-manage-cache-part-1.html), it’s also possible to do this automatically based on events in your ETL process.

This is done by having the OBIEE cache manager polling one or more a ‘EVENTS’ table(s). What you do is have a predefined reference table in a each database you are using. (If you have 1 complete ETL process you could log all table changes in one table.)

The Event table should have the following structure (copied from the OBIEE documentation )

Note 1: You can play around with the column names as long as they imported in this order in the OBIEE repository.

Note 2: Although UpdateTime needs to be unique, you can not put a UNIQUE constrain on this column. OBIEE copies the row back into the table during the purging process.

A script for an Oracle version would look like this:

CREATE TABLE OBIEE_UPDATE_EVENTS
(
UPDATE_TYPE INTEGER DEFAULT 1 NOT NULL,
UPDATE_TIME DATE DEFAULT SYSDATE NOT NULL,
DB_NAME VARCHAR2(40 BYTE),
CATALOG_NAME VARCHAR2(40 BYTE),
SCHEMA_NAME VARCHAR2(40 BYTE),
TABLE_NAME VARCHAR2(40 BYTE) NOT NULL,
OTHER VARCHAR2(80 BYTE) DEFAULT NULL
)
/

Imported in the OBIEE repository it would look like this:
Now assign this table as an event table:

Tools > Utilities > Oracle Event Tables
Drag the event table(s) to the right side
Set the polling frequency, press OK.

Let’s fill the cache with a simple query: (SH schema)

Check if the cache is actually filled:
Fill the Event table: (Based on the SH schema)

INSERT INTO obiee_update_events
(db_name, catalog_name, schema_name, table_name
)
VALUES (’orcl SH’, NULL, ‘SH’, ‘Sales’
)
/
COMMIT
/

Look in the cache directory after the polling time:

Note 3: OBIEE removes each entry from the EVENT table independent if it was a success or not.
Note 4: OBIEE only logs failures in the NQServer.log. Usually found in ..OracleBI\server\Log

Till Next Time