Event Pooling Tabel In OBIEE 10G / 11G

OBIEE 10G/11G – Event Pooling table (Event Table) to purge the cache automatically after ETL process

The use of an Oracle BI Server event polling table (event table) is a way to notify the Oracle BI Server that one or more physical tables have been updated and then that the query cache entries are stale.

An event polling table(S_NQ_EPT) is a way to notify the Oracle BI Server that one or more physical tables have been updated. Each row that is added to an event table describes a single update event. The cache system reads rows from, or polls, the event table, extracts the physical table information from the rows, and purges cache entries that reference those physical tables.

Finally it truncate the Event Table(S_NQ_EPT)

Here is how one configures the event polling table.

Step-By-Step :

Step1:

Navigate to the path “<Middleware>\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\schema” –> Open SAEPT.Oracle file and copy the DDL from there .
DDL should be as below .
–======================================================
–  Script that creates an Event Polling Table for    ==
– the Siebel Analytics Server in an Oracle database. ==
–======================================================

————————————————–
–  Make sure to drop any existing table having –
– the same name as the Event Polling Table.    –
————————————————–
drop table S_NQ_EPT ;

————————————–
–  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
) ;

copy this DDL and create table in Oracle database .

  

Step2: Import S_NQ_EPT tabble into Repository .

Step3:  To define the table as an event table perform the steps as shown in below screenshot

Test :

Insert data into S_NQ_EPT table .

INSERT INTO S_NQ_EPT(update_type,update_ts,database_name,catalog_name,schema_name,table_name,other_reserved)
VALUES (1,sysdate,’Mapdemo’,NULL,’MAPDEMO’,'MAPDEMO_F’,NULL);

Wait the polling interval frequency and verify that the cache entry is deleted after 10 Mins. You can trace in the NQQuery.log file and also observe that , the data from table S_NQ_EPT is truncated .