About
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.
Each row that is added to an event table describes a single update event, such as an update occurring to a Product table.
The Oracle BI Server cache system reads rows from, or polls, the event table, extracts the physical table information from the rows, and purges stale cache entries that reference those physical tables.
The event table is a physical table that resides on a database accessible to the Oracle BI Server. Regardless of where it resides—in its own database, or in a database with other tables—it requires a fixed schema.
It is normally exposed only in the Physical layer of the Administration Tool, where it is identified in the Physical Table dialog box as being an Oracle BI Server event table.
It does require the event table to be populated each time a database table is updated. Also, because there is a polling interval in which the cache is not completely up to date, there is always the potential for stale data in the cache.
A typical method of updating the event table is to include SQL INSERT statements in the extraction and load scripts or programs that populate the databases. The INSERT statements add one row to the event table each time a physical table is modified.
After this process is in place and the event table is configured in the Oracle BI repository, 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.
Polling Table Structure
You can set up a physical event polling table on each physical database to monitor changes in the database. The event table should be updated every time a table in the database changes.
The event table needs to have the structure shown below. The column names for the event table are suggested; you can use any names you want. However, the order of the columns has to be the same in the physical layer of the repository (then by alphabetic ascendant order)
Name Null Type
-------------- -------- -------------
UPDATE_TYPE NOT NULL NUMBER(10)
UPDATE_TS NOT NULL DATE
DATABASE_NAME VARCHAR2(120)
CATALOG_NAME VARCHAR2(120)
SCHEMA_NAME VARCHAR2(120)
TABLE_NAME NOT NULL VARCHAR2(120)
OTHER_RESERVED VARCHAR2(120)
In the OBIEE 10g we need to create the Event polling table manually but in the OBIEE 11g by default the table available in the BI Platform schema.
Create New Database in Physical Layer
Create a new connection pool
Import the S_NQ_EPT Table
Pointing the Table as an event table
In the Administration Tool go to Tools>Utilities
Select the Oracle BI Event Table and Click Execute Button to proceed.
In the list select the S_NQ_EPT table and set the Polling frequency and Click 'Ok ' Button.
Check that the S_NQ_EPT table icon got changed in the physical layer.
We have done all the configurations, for test purpose deploy the RPD and generate report based on any Fact table and save it. Go to the ‘S_NQ_EPT’ and insert a record for the same fact table which you created report in answers.
You can verify the cache file in the following location.
<BI_Home>\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\cache
Wait for 5 min, After the polling interval the cache has been cleared and the inserted row has been removed from the table.
You can verify in the nqsquery.log file
After purging happened the relevant cache file has been deleted from the mentioned cache folder.
Note:
For illustrative purpose I have manually inserted the record into S_NQ_EPT table. You can automate this process by using database trigger on the respective tables.
Trigger:
CREATE OR REPLACE TRIGGER <Trigger_Name>
AFTER INSERT OR DELETE OR UPDATE ON <Table_Name>
BEGIN
INSERT INTO DEV_BIPLATFORM.S_NQ_EPT VALUES(1,SYSDATE,'Oracle
Data Warehouse','Catalog','dbo','W_GL_OTHER_F','');
END;
