OBIEE 11G Event Polling table

Writen by Devil 2:12 PM - 42 Comments

OBIEE 11G Event Polling table  / OBIEE 11G Event Pooling table

Oracle BI Server event polling table is used to notify the Oracle BI Server that one or more physical tables have been updated and the query cache entries on these  tables are stale/Old.


How Event Polling table Works:
1)Each row that is added to an event table represents a single update event, Example an update to a Product table.
2)Oracle BI Server cache system reads rows from the event table, It extracts the physical table information from the rows and purges stale/old cache entries that reference those physical tables.


Features of Event Polling table :
1) It is a physical table that resides on a database accessible to the Oracle BI Server.
2) It can resides in its own database, or in a database with other tables, but it requires a fixed schema.
3) It is exposed only in the Physical layer of the Administration Tool, Tick the Dialog box that identifies it as being an Oracle BI Server event table.
4) It does require the event table to be populated each time a database table is updated.



How to use an Event Polling table at ETL Level:
 A Common way of updating the event table is by including a SQL INSERT statements in the extraction and load scripts/programs that populate the databases. Where an INSERT statements add one row to the event table each time a physical table is modified.

Configuring Event Polling table in the Oracle BI repository:

1) Create the table:

In 10g: under below location EVT Script can be found/ or below script can be used*

Obi_Home\bi\server\Schema\SAEPT.Oracle.sql

--------------------------------------

--  Create the Event Polling Table. --

--------------------------------------

CREATE TABLE S_NQ_EPT (

  UPDATE_TYPE    DECIMAL(10,0)  DEFAULT 1       NOT NULL,

  UPDATE_TS      DATE           DEFAULT SYSDATE NOT NULL,

  DATABASE_NAME  VARCHAR2(120)                      NULL,

  CATALOG_NAME   VARCHAR2(120)                      NULL,

  SCHEMA_NAME    VARCHAR2(120)                      NULL,

  TABLE_NAME     VARCHAR2(120)                  NOT NULL,

  OTHER_RESERVED VARCHAR2(120)  DEFAULT NULL        NULL 

) ;

PS: In 11g, the table is already present in the BIPLATFORM metadata repository.

2) Now import the Table into the RPD Physical Layer (Using import Metadata)

3)Go to the Utilities

4)Select Oracle BI Event Tables, then click on execute

5)Select the Table from the List, Give polling Frequency as 15 minutes/ or as required and Click on ok.

6) EPT table turns Yellow on completion of the process.

Have a Great Caching !! 

• Facebook
• Tweeter
• Google+
• Whatsapp Pinterest