Enabling near real-time updates for dynamic cubes

To enable near real-time updates, you must add a nullable transaction ID (TID) column to each fact table.

Before you begin

Before you load fact data to a dynamic cube, you insert new rows into the fact tables in the data source. New rows for near real-time updates must adhere to the following rules:

  • Each insert transaction must use a TID value greater than any previous transaction.
  • All rows can use the same TID value within a single transaction.

Procedure

  1. Add a nullable transaction ID (TID) column to each fact table.

    The data type for this column can be set to any type that supports SQL comparison operators and MAX SQL functions. You can use the BIGINT, INTEGER, or TIMESTAMP data type.

    To improve query performance, create an index on the TID column.

    For the initial fact data, set the TID column to Null. Any other TID value implies an incremental update to the fact data. This is illustrated in the following example.

    Table 1. Fact table
    Product Sales Transaction ID (TID)
    Paper 50  
    Pen 75  
    Paper 45  
    Paper 5  
    Paper 20 1
    Paper 5 1
    Paper 25 2

    Create the aggregate tables from the initial fact data only (rows with a null TID column), as illustrated in this table.

    Table 2. Aggregate table for initial fact data
    Product Aggregate sales
    Paper 100
    Pen 75
  2. Identify the TID column in the dynamic cube using Cognos® Cube Designer:
    1. From the Project Explorer tree, expand your cube.
    2. Select the measure dimension folder.
    3. In the Properties pane, select the TID column from the Transaction ID drop-down list.
  3. Publish the dynamic cube.

Results

When you start a published dynamic cube, the query service performs the following tasks:

  • Checks for the highest TID value and uses it for the initial load.

    In the example fact table in step 1, there are rows of initial fact data with null TID values, and rows of updates for two increments with TID values 1 and 2. In this case, the query service uses the TID value 2 for the initial load.

  • Loads the aggregate cache and query data cache with aggregate table and fact data based on the initial load state.