IKM BIAPPS Oracle Event Queue Delete Append

This IKM integrates data into an Oracle target table using an event queue to process incremental changes. This IKM is used when working with data that is versioned over time (similar to a slowly changing dimension).

In full load all records are inserted. The event queue tracks the natural keys that are changing, and the earliest date a change occurred. For each natural key, any existing target records on or after the earliest change are deleted and the new records inserted.

If there are effective from and to dates, these are maintained automatically in both full and incremental loads.

Data can be controlled by isolating invalid data in the error table, but recycling data is not supported.

Note:

This IKM now supports ETL diagnostics and automatic corrections. The flow control option is no longer used with this IKM.

Prerequisites

Prerequisites for using this IKM are:

  • The event queue table must be defined using the Event Queue Table option.

    • It must have the column EARLIEST_CHANGE_DATE (DATE data type)

    • It must follow the standard naming convention ending with _EQ_TMP

  • Join between target table and event queue must be defined using the Event Queue Join option.

  • Target table must have SCD behavior set for:

    • Natural key

    • Starting/ending timestamp

  • Interface must only select the source data that is changing, as controlled by the event queue, which lists the natural keys that are changing and the earliest date of any change.

    • Either the data is selected from temporary or staging tables which only contain incremental data

      Or

    • Use the nested IKM BIAPPS Oracle Event Queue Delete Append to include a join to the event queue in incremental load

  • If the "Synchronize Deletions from Journal" process is executed, the deleted rows on the target are committed.

Options for Functionality

  • Event Queue Table — The name of the event queue table that holds the incremental changes. This option is mandatory.

    Prerequisites for this option are the following:

    • Event queue table must contain EARLIEST_CHANGE_DATE column (DATE data type)

    • Oracle BI Applications naming standard for table ends with _EQ_TMP

  • Event Queue Join — Assuming the alias T for Target Table and Q for Event Queue Table, define the equi-join between the Target Table and the Event Queue Table. This is used in the Event Queue Update and Event Queue Delete steps and in rare cases may be omitted if neither of those steps are required. The filter on EARLIEST_CHANGE_DATE should not be included in the join option.

  • Event Queue Delete — Whether or not to delete records in the target that are being processed in incremental load. In most cases this should be enabled, but in rare cases where more than one interface loads the target table then it only needs to be enabled on the first one.

  • Event Queue Update — Whether or not to correct effective dates on records in the target that are affected by the incremental load. In most cases this should be enabled, but in rare cases where more than one interface loads the target table then it only needs to be enabled on the last one.

  • High Data Value — The default value to use for the maximum ending timestamp. In most cases the default value of #HI_DATE is fine, but for some persisted staging tables that reflect the OLTP might use a different value e.g. #ORA_HI_DATE.

  • Unspecified Record — If the target table is a dimension, set this to TRUE to automatically insert an "Unspecified" record. This is referenced by facts in case no other dimension record matches. The default column values are determined by model naming standards using the user-defined function GET_UNSPEC_VALUE.

Options for Performance Tuning

  • Hints — This IKM allows the passing of hints into the generated SQL. For more information, see the article titled "Oracle Business Intelligence Applications Version 11.1.1.7.1 Performance Recommendations (Doc ID 1539322.1)" on My Oracle Support.

  • Alter Session List — Applies a list of alter session commands to the session used by the KM. Commands should be separated by a semi-colon and without the "ALTER SESSION" prefix. Each command should be prefixed SRC or TGT depending on whether it should be executed on the source connection (relevant if using an LKM) or the target connection. For example: 
    SRC set TRACEFILE_IDENTIFIER='ODI_TRACE'; SRC set events '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8'; TGT set TRACEFILE_IDENTIFIER='ODI_TRACE_TGT';