OBIEE 10G/11G – Event Polling 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.
Now event table is configured in the Oracle BI repository and cache invalidation occurs automatically. As long as the scripts that update the event table are accurately recording changes to the tables, stale cache entries are purged automatically at the specified polling intervals(10mins in our example).
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 .
Hope this blog helped you.
Documentation / Reference : http://gerardnico.com/wiki/dat/obiee/event_table
-By Prasad Madhasi
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.
Now event table is configured in the Oracle BI repository and cache invalidation occurs automatically. As long as the scripts that update the event table are accurately recording changes to the tables, stale cache entries are purged automatically at the specified polling intervals(10mins in our example).
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 .
Hope this blog helped you.
Documentation / Reference : http://gerardnico.com/wiki/dat/obiee/event_table
-By Prasad Madhasi
