Cache Event Processing with an Event Polling Table
You can use an Oracle BI Server event polling table (event table) as a way to notify the Oracle BI Server that one or more physical tables have been updated. Each row that's added to an event table describes a single update event, such as an update occurring to the Product table in the Production database.
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 relational database accessible to the Oracle BI Server. Regardless of whether it resides in its own database, or in a database with other tables, it requires a fixed schema as described in Set Up Event Polling Tables on the Physical Databases. It is normally exposed only in the Physical layer of the Model Administration Tool, where it's identified in the Physical Table dialog as an Oracle BI Server event table.
Using event tables is one of the most accurate ways of invalidating stale cache entries, and is probably the most reliable method. It does, however, require the event table to be populated each time that a database table is updated. Also, because there is a polling interval in which the cache isn't completely up to date, there's always the potential for stale data in the cache. See Populate the Oracle BI Server Event Polling Table.
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 that a physical table is modified. After this process is in place
and the event table is configured in the Oracle Analytics Server semantic model, 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.
This section contains the following topics:
Set Up Event Polling Tables on the Physical Databases
You can configure a physical event polling table on each physical database to monitor changes in the database.
You can also configure the event table in its own database. The event table is updated every time a table in the database changes.
If the event polling table is on an Oracle Database, then configure the event table
in its own database object in the Physical layer
of the Model Administration Tool. Then, ensure that the feature
PERF_PREFER_IN_LISTS isn't
selected in the Features tab of the Database
dialog for the event polling table. Following
these guidelines avoids errors related to
exceeding the maximum number of allowed
expressions in a list.
To create an event polling table, run the Repository Creation Utility (RCU) to create the BIPLATFORM schemas in your physical database. RCU creates an event polling table called S_NQ_EPT. See Installing and Configuring Oracle Analytics Server for information about running the Repository Creation Utility.
Event tables must have the structure that's shown in the following table. Some columns can contain null values, depending on where the event table resides. The names for the columns must match the column names that are shown in the next table. Data Types shown are for an Oracle Database.
| Event Table Column | Data Type | Description |
|---|---|---|
|
CATALOG_NAME |
VARCHAR2 |
The name of the catalog where the physical table that was updated resides. Populate the CATALOG_NAME column only if the event table doesn't reside in the same database as the physical tables that were updated. Otherwise, set it to the null value. |
|
DATABASE_NAME |
VARCHAR2 |
The name of the database where the physical table that was updated resides. This is the name of the database as it's defined in the Physical layer of the Model Administration Tool. For example, if the physical database name is 11308Production, and the database name that represents it in the Model Administration Tool is SQL_Production, then the polled rows in the event table must contain SQL_Production as the database name. Populate the DATABASE_NAME column only if the event table doesn't reside in the same database as the physical tables that were updated. Otherwise, set it to the null value. |
|
OTHER_RESERVED |
VARCHAR2 |
Reserved for future enhancements. This column must be set to a null value. |
|
SCHEMA_NAME |
VARCHAR2 |
The name of the schema where the physical table that was updated resides. Populate the SCHEMA_NAME column only if the event table doesn't reside in the same database as the physical tables being updated. Otherwise, set it to a null value. |
|
TABLE_NAME |
VARCHAR2 |
The name of the physical table that was updated. The name must match the name that's defined for the table in the Physical layer of the Model Administration Tool. Values can't be null. |
|
UPDATE_TS |
DATE |
The time when the update to the event table occurs. This must be a key (unique)
value that increases for each row that's added to
the event table. To ensure a unique and increasing
value, specify the current timestamp as a default
value for the column. For example, specify
Values can't be null. Because this column must be a unique value that increases for each row that's added
to the event table, you might need to set a very
high precision if you require many inserts per
second. Because of this, you might want to adjust
the database feature
For example, for Oracle Database or Teradata, you might want to change |
|
UPDATE_TYPE |
NUMBER |
Specify a value of 1 in the update script to indicate a standard update. You can't use null values. |
The Oracle BI Server must have read and write permission on the event polling table. The server reads the event table at specified intervals to look for changed data. Applications add rows to the event table when database tables are modified (for example, during a load operation). When there are rows in the event table, there's changed data in the underlying databases. The server then invalidates any cache entries that correspond to the changed physical tables and periodically deletes obsolete rows from the event table. The next time it checks the event table, the process repeats.
Note:
In a clustered deployment, a single event polling table is shared by every Oracle BI Server node in the cluster. However, a single event polling table can't be shared by multiple Oracle BI Server clusters.To enable the Oracle BI Server to have write access to the event polling table but not to any other tables in a database, perform the following tasks:
-
Create a separate physical database in the Physical layer of the Model Administration Tool with a privileged connection pool.
-
Assign a user to the connection pool that has delete privileges.
-
Populate the privileged database with the event table.
The Oracle BI Server has write access to the event polling table, but not to any tables that are used to answer user queries.
Make the Event Polling Table Active
After the table is created on the physical database, you can make it active in the Oracle BI Server. To do this, you first import the physical table, and then you mark the table object as an event polling table.
To import the physical table:
-
In the Model Administration Tool, open the semantic model and import metadata from the physical database. To do this, select File, then select Import Metadata.
-
Follow the wizard steps. Be sure to select the Tables option in the Select Metadata Types screen to import the table metadata.
See Import Metadata and Working with Data Sources in Managing Metadata Repositories for Oracle Analytics Server for detailed information about the Import Metadata wizard.
-
If you have multiple event polling tables, then repeat steps 1 and 2 for each event table. Be sure the data source that's specified for the event table has read and write access to the event table. The semantic model both reads the table and deletes rows from it, so it needs write permission. Event tables don't need to be exposed in the Business Model and Mapping or logical layer.
To mark the table object as an event polling table:
When a table has been registered as an Oracle BI Server event table, the table properties change. Registration as an event table removes the option to make the table cacheable, as there's no reason to cache results from an event polling table.
Populate the Oracle BI Server Event Polling Table
The Oracle BI Server doesn't populate the event polling table. The event table is populated by inserting rows into it each time a table is updated.
This process is normally configured by the database administrator, who typically modifies the load process to insert a row into the polling table each time a table is modified. This can be done from the load script, using database triggers (in databases that support triggers), from an application, or manually. If the process of populating the event table isn't done correctly, then the Oracle BI Server cache purging is affected, because the server assumes the information in the polling table is correct and up to date.
Troubleshoot Problems with Event Polling Tables
You can start troubleshooting event polling table issues in activity logs.
If you experience problems with cache polling, then you can search the Oracle BI Server activity logs for any entries regarding the server's interaction with the event table.
-
The obis1-diagnostic.log file logs activity automatically about the Oracle BI Server. Log entries are self-explanatory and can be viewed in Fusion Middleware Control or in a text editor.
-
When the Oracle BI Server polls the event table, it logs queries in the nqquery.log file using the administrator account (set upon installation) unless the logging level for the administrator account is set to 0. Set the logging level to 2 for the administrator account to provide the most useful level of information.
You can find the obis1-diagnostic.log and the nqquery.log in the following location:
BI_DOMAIN/servers/obisn/logs