On this project we wanted to automatically empty the cache after updating the OBI dimensions or facts table. Given that we use as ETL ODI, we decided to configure OBI Event Polling Table (ERA), with the ODI Knowledge Module will ensure the table is updated inserting a corresponding record in the Event Polling Table (more on the Event Polling Table see. Oracle BI Documentation Server Administration Guide, Chapter Cache Event Processing with an Event Polling Table).This is basically a very trivial matter, on the web we found some simple step by step procedures for configuring ERA in ODI. In practice, however, we encountered the following problem: Used by the Oracle Database 10gR2 and filing script ERA table according to the documentation. After configuring the Event Polling Table in log NQServer.log discovered the following errors, depending on what driver was used to import metadata ERA table:
The cause was the most Event Polling incorrect reverse engineering ERA metadata repository tables to OBI. A column of type DATE UPDATE_TS was necessary to manually set the OBI repository DATETIME. I enclose a functional combination of the loading script the table and a screenshot ERA ERA in OBI metadata repository:create table S_NQ_EPT ( UPDATE_TYPE DECIMAL (10,0) DEFAULT 1 NOT NULL DATE DEFAULT SYSDATE UPDATE_TS 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 );
- If we use the OCI driver: - [55004] The prepare operation failed while polling from table UET. - [nQSError: 22005] Repository metadata: Has no column data type information. - [55005] The cache polling delete statement failed for table UET.
- If we used the ODBC driver: - [55004] The prepare operation failed while polling from table UET. - [nQSError: 22006] Repository metadata: missing column object: ID = 0:34. - [55005] The cache polling delete statement failed for S_NQ_EPT table. - [nQSError: 22006] Repository metadata: missing column object: ID = 0:34.
The cause was the most Event Polling incorrect reverse engineering ERA metadata repository tables to OBI. A column of type DATE UPDATE_TS was necessary to manually set the OBI repository DATETIME. I enclose a functional combination of the loading script the table and a screenshot ERA ERA in OBI metadata repository:create table S_NQ_EPT ( UPDATE_TYPE DECIMAL (10,0) DEFAULT 1 NOT NULL DATE DEFAULT SYSDATE UPDATE_TS 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 );
Posts
