OBIEE 10G/11G - Event Pooling table (Event Table) to purge the cache after ETL process
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.
Articles Related
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)
| Column Name by alphabetic ascendant order | Data Type | Null | Description | Advise |
|---|---|---|---|---|
| CatalogName | CHAR or VARCHAR | Yes | 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 | Yes | The name of the database where the physical table that was updated resides. | 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 | Yes | Reserved for future enhancements. | This column must be set to a null value. |
| SchemaName | CHAR or VARCHAR | Yes | 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 | No | 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. |
| UpdateTime | DATETIME | No | 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. For example, specify DEFAULT CURRENT_TIMESTAMP for Oracle 8i. |
| UpdateType | INTEGER | No | Specify a value of 1 in the update script to indicate a standard update. | Other values are reserved for future use. |
Step by Step for the Oracle database
Create the user
CREATE user OBIEE_REPO IDENTIFIED BY OBIEE_REPO; GRANT connect,resource TO OBIEE_REPO;
Create the table
In 10g: 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 ) ;
In 11g, the table is present in the BIPLATFORM metadata repository.
Import the table with ODBC (not with OCI)
Import the table with ODBC and change the call interface connection pool to OCI.
If you don't have an Oracle ODBC connection, you can simply suppress the table after importation with OCI and copy paste on the schema OBIEE_REPO the following UDML statmement:
DECLARE TABLE "ORCL".."OBIEE_REPO"."S_NQ_EPT" AS "S_NQ_EPT" NO INTERSECTION PRIVILEGES ( READ);
DECLARE COLUMN "ORCL".."OBIEE_REPO"."S_NQ_EPT"."UPDATE_TYPE" AS "UPDATE_TYPE" TYPE "DOUBLE"
PRECISION 10 SCALE 0 NOT NULLABLE PRIVILEGES ( READ);
DECLARE COLUMN "ORCL".."OBIEE_REPO"."S_NQ_EPT"."UPDATE_TS" AS "UPDATE_TS" TYPE "TIMESTAMP"
PRECISION 19 SCALE 0 NOT NULLABLE PRIVILEGES ( READ);
DECLARE COLUMN "ORCL".."OBIEE_REPO"."S_NQ_EPT"."DATABASE_NAME" AS "DATABASE_NAME" TYPE "VARCHAR"
PRECISION 120 SCALE 0 NULLABLE PRIVILEGES ( READ);
DECLARE COLUMN "ORCL".."OBIEE_REPO"."S_NQ_EPT"."CATALOG_NAME" AS "CATALOG_NAME" TYPE "VARCHAR"
PRECISION 120 SCALE 0 NULLABLE PRIVILEGES ( READ);
DECLARE COLUMN "ORCL".."OBIEE_REPO"."S_NQ_EPT"."SCHEMA_NAME" AS "SCHEMA_NAME" TYPE "VARCHAR"
PRECISION 120 SCALE 0 NULLABLE PRIVILEGES ( READ);
DECLARE COLUMN "ORCL".."OBIEE_REPO"."S_NQ_EPT"."TABLE_NAME" AS "TABLE_NAME" TYPE "VARCHAR"
PRECISION 120 SCALE 0 NOT NULLABLE PRIVILEGES ( READ);
DECLARE COLUMN "ORCL".."OBIEE_REPO"."S_NQ_EPT"."OTHER_RESERVED" AS "OTHER_RESERVED" TYPE "VARCHAR"
PRECISION 120 SCALE 0 NULLABLE PRIVILEGES ( READ);
When you import the table with OCI, the UDML metadata are not the same. They have different syntax for the definition of a column. You can remark in the two below statement that the OCI metadata has an extra EXTERNAL clause that you don't find in the ODBC metadata.
Declaration of the column CATALOGNAME after import with OCI:
DECLARE COLUMN "ORCL".."OBIEE_REPO"."S_NQ_EPT"."CATALOGNAME" AS "CATALOGNAME" EXTERNAL "obiee_repo" TYPE "VARCHAR" PRECISION 40 SCALE 0 NULLABLE PRIVILEGES ( READ);
Declaration of the column CATALOGNAME after import with ODBC:
DECLARE COLUMN "ORCL".."OBIEE_REPO"."S_NQ_EPT"."CATALOGNAME" AS "CATALOGNAME" TYPE "VARCHAR" PRECISION 40 SCALE 0 NULLABLE PRIVILEGES ( READ);
Define the table as an event table
You can restart the Oracle BI Server service to be sure that the table is now seen as a good event table. If you have any error in the NQServer.log during the start, correct them.
Test
- Create a report with a product attribute to seed the cache.
- Insert a row to give to OBIEE the instruction to delete the cache from all SQL query cache which contain the product table.
INSERT INTO S_NQ_EPT ( update_type, update_ts, database_name, catalog_name, schema_name, table_name, other_reserved ) VALUES ( 1, sysdate, 'orcl SH', NULL, 'SH', 'PRODUCTS', NULL )
- Wait the polling interval frequency and verify that the cache entry is deleted and that you can find the below trace in the NQQuery.log file.
+++Administrator:fffe0000:fffe001c:----2010/06/16 00:45:33
-------------------- Sending query to database named ORCL (id: <<13628>>):
select T4660.UPDATE_TYPE as c1,
T4660.UPDATE_TS as c2,
T4660.DATABASE_NAME as c3,
T4660.CATALOG_NAME as c4,
T4660.SCHEMA_NAME as c5,
T4660.TABLE_NAME as c6
from
S_NQ_EPT T4660
where ( T4660.OTHER_RESERVED in ('') or T4660.OTHER_RESERVED is null )
minus
select T4660.UPDATE_TYPE as c1,
T4660.UPDATE_TS as c2,
T4660.DATABASE_NAME as c3,
T4660.CATALOG_NAME as c4,
T4660.SCHEMA_NAME as c5,
T4660.TABLE_NAME as c6
from
S_NQ_EPT T4660
where ( T4660.OTHER_RESERVED = 'oracle10g' )
+++Administrator:fffe0000:fffe001c:----2010/06/16 00:45:33
-------------------- Sending query to database named ORCL (id: <<13671>>):
insert into
S_NQ_EPT("UPDATE_TYPE", "UPDATE_TS", "DATABASE_NAME", "CATALOG_NAME", "SCHEMA_NAME", "TABLE_NAME", "OTHER_RESERVED")
values (1, TIMESTAMP '2010-06-16 00:44:53', 'orcl SH', '', 'SH', 'PRODUCTS', 'oracle10g')
+++Administrator:fffe0000:fffe0003:----2010/06/16 00:45:33
-------------------- Cache Purge of query:
SET VARIABLE QUERY_SRC_CD='Report',SAW_SRC_PATH='/users/administrator/cache/answers_with_product';
SELECT Calendar."Calendar Year" saw_0, Products."Prod Category" saw_1, "Sales Facts"."Amount Sold"
saw_2 FROM SH ORDER BY saw_0, saw_1
+++Administrator:fffe0000:fffe001c:----2010/06/16 00:45:33
-------------------- Sending query to database named ORCL (id: <<13672>>):
select T4660.UPDATE_TIME as c1
from
S_NQ_EPT T4660
where ( T4660.OTHER_RESERVED = 'oracle10g' )
group by T4660.UPDATE_TS
having count(T4660.UPDATE_TS) = 1
+++Administrator:fffe0000:fffe001c:----2010/06/16 00:45:33
-------------------- Sending query to database named ORCL (id: <<13716>>):
delete from
S_NQ_EPT where S_NQ_EPT.UPDATE_TS = TIMESTAMP '2010-06-16 00:44:53'
Support
The cache polling event table has an incorrect schema
In the NQServer.log file
[56001] The cache polling event table S_NQ_EPT has an incorrect schema.
You must import the event table via ODBC 3.5. When you use OCI, the UDML of the table has a difference. You can then change the call interface to OCI.
The physical table in a cache polled row does not exist
In the NQServer.log file
[55001] The physical table ORCL::SH:PRODUCTS in a cache polled row does not exist.
Verify the location of your table.
For instance, in my case, the good location was “orcl SH::SH:PRODUCTS” as you can see in the picture below.
The cache polling SELECT query failed for table
When you get the following trace in NQQuery.log, it's because:
- your event table was deleted
- or that the name in the physical layer doesn't match any more the table in the database.
2010-06-15 07:52:28 [55003] The cache polling SELECT query failed for table S_NQ_EPT.
2010-06-15 07:52:28 [nQSError: 17001] Oracle Error code: 942, message: ORA-00942: table or view does not exist
at OCI call OCIStmtExecute.
[nQSError: 17010] SQL statement preparation failed.
2010-06-15 07:52:28 [55005] The cache polling delete statement failed for table S_NQ_EPT.
faulting module NQSCache.dll
Oracle BI Server can literally crash when you use the example script that is given in the documentation (with an OTHER column). You can find this information in the even viewer of Windows.
Description: Faulting application NQSServer.exe, version 10.1.3.4, faulting module NQSCache.dll, version 10.1.3.4, fault address 0x00016b43.
Solution: change the structure of the table by using the script in the schema directory (such as above in the document)
