Bookshelf: Setting Up Event Polling Tables on the Physical Databases

This section describes how to set up the Siebel Analytics Server event polling tables on physical databases.

Polling Table Structure

You can set up a physical event polling table on each physical database to monitor changes in the database. You can also set up the event table in its own database. The event table should be updated every time a table in the database changes. The event table needs to have the structure shown in Table 28; some columns can contain NULL values depending on where the event table resides.

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 as shown in Table 28. Sample CREATE TABLE statements to create an event polling table are shown in Sample Event Polling Table CREATE TABLE Statements.

Table 28. Event Polling Table Column Names
Event Table Column Name	Datatype	Description
UpdateType	INTEGER	Specify a value of 1 in the update script to indicate a standard update. (Other values are reserved for future use.) Values cannot be NULL.
UpdateTime	DATETIME	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. Values cannot be NULL.
DatabaseName	CHAR or VARCHAR	The name of the database where the physical table that was updated resides. This is the name of the database as it is defined in the Physical layer of the Administration Tool. For example, if the physical database name is 11308Production, and the database name that represents it in the Administration Tool is SQL_Production, the polled rows in the event table has to contain SQL_Production as the database name. 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.
CatalogName	CHAR or VARCHAR	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.
SchemaName	CHAR or VARCHAR	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	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. Values cannot be NULL.
Other	CHAR or VARCHAR	Reserved for future enhancements. This column must be set to a NULL value.

The Siebel Analytics Server needs to 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 is changed data in the underlying databases. The server then invalidates any cache entries corresponding 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.

To allow Siebel Analytics Server write access to the event polling table but not to any other tables in a database, you can create a separate physical database in the Physical layer of the Administration Tool with a privileged connection pool (privileged to delete from the table). Populate this privileged database with the event table. Setting up this type of polling database makes sure that the Siebel Analytics Server has write access to the event polling table, but not to any tables that are used to answer user queries.

Sample Event Polling Table CREATE TABLE Statements

The following are sample CREATE TABLE statements for SQL Server 7.0 and Oracle 8i. These CREATE TABLE statements create the structure required for a Siebel Analytics Server event polling table. In these statements, the table created is named UET. It resides in the same database as the physical tables that are being updated.

NOTE: The column lengths need to be large enough to represent the object names in your repository.

// SQL Server 7.0 Syntax
create table UET (
UpdateType Integer not null,
UpdateTime datetime not null DEFAULT CURRENT_TIMESTAMP,
DBName char(40) null,
CatalogName varchar(40) null,
SchemaName varchar(40) null,
TableName varchar(40) not null,
Other varchar(80) null DEFAULT NULL
)

// Oracle 8i syntax
create table UET (
UpdateType Integer not null,
UpdateTime date DEFAULT SYSDATE not null,
DBName char(40) null,
CatalogName varchar(40) null,
SchemaName varchar(40) null,
TableName varchar(40) not null,
Other varchar(80) DEFAULT NULL
);

You might need to modify these CREATE TABLE statements slightly for different versions of SQL Server and Oracle, or for other databases. Additionally, if you want to specify any explicit storage clauses, you need to add the appropriate clauses to the statements.