Home | Ciber
Knowledge page of Ciber Netherlands

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

Besides using batch / shell command to purge the cache (see: http://knowledge.ciber.nl/weblog/?p=96), 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):

 

Column Name

Data type

Description

CatalogName

CHAR or
VARCHAR
The name of the catalog where the physical table that was updated resides.
Populate the CatalogName column only if the event table does not reside in the same database as the physical tables that were updated. Otherwise, set it to the null value.

DatabaseName

CHAR or
VARCHAR
The name of the database where the physical table that was updated resides. This is the name of the database as it is defined in the Physical layer of the Administration Tool. For example, if the physical database name is 11308Production, and the database name that represents it in the Administration Tool is SQL_Production, the polled rows in the event table has to contain SQL_Production as the database name.
Populate the DatabaseName column only if the event table does not reside in the same database as the physical tables that were updated. Otherwise, set it to the null value.

Other

CHAR or
VARCHAR
Reserved for future enhancements. This column must be set to a null value.

SchemaName

CHAR or
VARCHAR
The name of the schema where the physical table that was updated resides.
Populate the SchemaName column only if the event table does not reside in the same database as the physical tables being updated. Otherwise, set it to the null value.

TableName

CHAR or
VARCHAR
The name of the physical table that was updated. The name has to match the name defined for the table in the Physical layer of the Administration Tool.
Values cannot be null.
UpdateTime CHAR or
VARCHAR
The time when the update to the event table occurs. This needs to be a key (unique) value that increases for each row added to the event table. To make sure a unique and increasing value, specify the current timestamp as a default value for the column. Values cannot be null.
UpdateType INTEGER Specify a value of 1 in the update script to indicate a standard update. (Other values are reserved for future use.) Values cannot be null.

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:

image

Now assign this table as an event table:

Tools > Utilities > Oracle Event Tables

image

Drag the event table(s) to the right side

image

Set the polling frequency, press OK.

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

image

image

Check if the cache is actually filled:

image

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:

image

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

John Minkjan is a senior BI-consultant at Ciber in the Netherlands, the text of this article is also published on his personal blog http://obiee101.blogspot.com/

No comments yet. Be the first.

Leave a reply