B Knowledge Modules Reference

This section provides detailed information about the Integration Knowledge Modules (IKMs) available with Oracle BI Applications.

This section contains the following topic:

• IKM BIApps Oracle Control Append

• IKM BIAPPS Oracle Event Queue Delete Append

• IKM BIAPPS Oracle Fact Incremental Update

• IKM BIAPPS Oracle Incremental Update

• IKM BIAPPS Oracle Period Delete Append

• IKM BIAPPS Oracle Slowly Changing Dimension

• IKM BIAPPS SQL Target Override

• Nested IKM BIAPPS Oracle Control Append

• Nested IKM BIAPPS Oracle Event Queue Delete Append

IKM BIApps Oracle Control Append

This IKM integrates data into an Oracle target table in append mode. All records are inserted without any key checks. Data can be controlled by isolating invalid data in the error table and recycling when fixed.

Prerequisites

Perquisites for using this IKM are the following:

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

• To use FLOW_CONTROL and RECYCLE_ERRORS options, the update ley must be set on the interface.

Options for Functionality

• Unspecified Record. If the target table is a dimension, set this option 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';
  

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.

Prerequisites

Prerequisites for using this IKM are the following:

• 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';
  

IKM BIAPPS Oracle Fact Incremental Update

This IKM integrates data into an Oracle target table in append mode. All records are inserted without any key checks. Data can be controlled by isolating invalid data in the error table and recycling when fixed.

Prerequisites

A prerequisite for using this IKM is the following:

• The scenario LOAD_PARTITION_METADATA must be run before any interface using this KM, passing in the target table name as a parameter. This will load W_ETL_PART_TABLES and W_ETL_PART_TABLE_PARTS with the required metadata.

Options for Functionality

For a description of the options available with this IKM, see the options described for "IKM BIAPPS Oracle Incremental Update.".

IKM BIAPPS Oracle Incremental Update

This IKM integrates data into an Oracle target table in incremental update mode. New records are inserted and existing records are updated. Data can be controlled by isolating invalid data in the error table and recycling when fixed.

Prerequisites

Prerequisites for using this IKM are the following:

• The update key must be defined in the interface and the key columns should be indexed.

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

Options for Functionality

• Soft Delete. There are several additional steps that you can perform if the soft delete option is enabled. The variables #SOFT_DELETE_FEATURE_ENABLED (global) and #SOFT_DELETE_PREPROCESS (can be set for each fact or dimension group) control exactly which steps are executed.

  If you are able to implement triggers to efficiently capture deletes on the source system, you can disable the expensive pre-process steps (which extract all source records and compare against all target records) and, instead, directly populate the delete table.

  Step	Action	Control
  Soft delete pre-process	Runs the "Identify Delete" step which compares the data in the primary extract table against the target table, and records any obsolete target rows in the delete table. Uses #LAST_ARCHIVE_DATE to filter target by CREATED_ON_DT system column (set variable to NULL to disable this). Only data sources that have implemented the primary extract are included. If a data source has no records in the primary extract table then no records will be added to the delete table for that data source.	#SOFT_DELETE_FEATURE_ENABLED #SOFT_DELETE_PREPROCESS
  Soft delete on target	Runs the "Soft Delete" step which updates the DELETE_FLG column to 'Y' on the target table for records which have been identified for delete.	#SOFT_DELETE_FEATURE_ENABLED
  Truncate delete table	Removes records from the delete table once they have been processed	#SOFT_DELETE_FEATURE_ENABLED

  
  

  Note that all these steps are committed together, along with any other inserts and updates to the target table. This keeps the data warehouse consistent.

  Prerequisites for using this option are the following:

  • The target table must have the ETL_PROC_WID and W_UPDATE_DT system columns.

  • Tables <target>_PE and <target>_DEL must be created with the columns in the interface key.

  • #LAST_ARCHIVE_DATE must be NULL if target table does not have the CREATED_ON_DT column.

  • #SOFT_DELETE_PREPROCESS should be refreshed from Oracle BI Applications Configuration Manager by the load plan component.

• Date Track. Automatically maintains effective from and to dates in the target table, similarly to a slowly changing dimension. It can handle dates or numbers (usually date keys, for example, YYYYMMDD). This can also set a current flag column, which will be 'Y' for the last record and 'N' for earlier records.

  Prerequisites for using this option are the following:

  • Set the slowly changing dimension behavior for the following table columns in the model:

    - Natural key

    - Starting/ending timestamp

    - Current flag (optional)

  • The natural key and starting timestamp columns should be indexed if they are not covered by the update key index. In the interface, map the effective to date column to a constant maximum value (usually #HI_DATE) set to execute on the target.

  • If using current flag, map it to 'Y' again executing on the target.

  • ETL_PROC_WID should be indexed.

• Change Capture. Captures target table changes to an image table to streamline the process of reflecting the changes in aggregates.

  Step	Action	Control
  Truncate change image table	Clears out image table ready to capture changes in the current process.	Always runs
  Capture preload changes	Captures target table records that are about to be updated.	Always runs
  Capture soft delete changes	Captures target table records that are about to be marked for soft delete.	Runs if soft delete feature is enabled
  Capture post load changes	Captures target table records that have been inserted or updated.	Always runs

  
  

  All these steps are committed together along with any other inserts and updates to the target table. This keeps the data warehouse consistent.

  Prerequisites for using this option are the following:

  • For W_FACT_F, the image table W_FACT_CMG must be created with all the columns of the target table as well as the following columns:

    CHANGED_IN_TASK

    PHASE_CODE

    PHASE_MULTIPLIER

• 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';
  

• Analyze Target - Statistics will be collected on the target table before it is loaded if the KM Option ANALYZE_TARGET is set to True. By default it is set to False.

• Analyze Flow Table - Statistics will be collected on the flow table (I$) after it is loaded if the KM Option ANALYZE_FLOW_TABLE is set to True. By default it is set to False. This option also affects how the effective dates are calculated in full load if the date track option is enabled. If the flow table is not analyzed then an UPDATE statement is used to set the effective to dates. Otherwise a MERGE statement is used.

• Bulk Mode (variable #ETL_BULK_MODE) - If enabled, bulk mode will use the direct path write to target (append hint) and bypass the I$ table (if no other KM options requiring it, for example, flow control, recycle errors, and date track). The bulk mode variable can be set to:

  Y - Enabled

  F - Enabled for full load only

  N - Disabled

IKM BIAPPS Oracle Period Delete Append

This IKM integrates data into an Oracle target table, aggregating data by time periods. New records are inserted, and changes to existing periods will be handled by deleting the old data for the period and then inserting the new data. Data for old periods may be purged. Data can be controlled by isolating invalid data in the error table and recycling when fixed.

Prerequisites

Prerequisites for using this IKM are the following:

• The Delete Period Type option must specify which period type the target table is based on.

• For Calendar Periods, the target table must contain a column for the period end date key:

  - Must be a date wid type (YYYYMMDD).

  - Must be named PERIOD_END_DT_WID or otherwise identified in the mapping using the UD1 check box.

• For MCAL calendar periods the target table must contain:

  - Period key, usually MCAL_PERIOD_WID or MCAL_DAY_WID, otherwise identified in the mapping using UD1 check box.

  - Calendar key, usually MCAL_CAL_WID, otherwise identified in the mapping using UD2 check box.

• Bitmap indexes on the above mentioned key columns.

Calendar Periods

Calendar periods of day, week, month, quarter and year are supported. For these period types, the incremental load assumes the interface is loading data for the current period only. The incremental update works by deleting any existing data for the current period. Then, the fresh set of data for the current period is inserted. Optionally, data older than a specified age can be automatically purged with the Periods to Keep option. The option Periods To Delete does not apply for calendar periods.

MCAL Calendar Periods

MCAL calendar periods of MCAL Day and MCAL Period are supported. For these period types, the incremental load assumes the interface is loading data for the current period and a given number of previous periods. The incremental update works by deleting any existing data for these periods. Then, the fresh set of data is inserted.The option Periods To Delete controls how many previous periods (as well as the current one) are being incrementally loaded by the interface. For example, a value of 1 would indicate reprocessing the current and previous period every load. The Periods to Keep option does not apply for MCAL calendar periods.

Options for Functionality

• Delete Period Type - The type of calendar periods the target table is based on. It is used for deleting current periods to reprocess or obsolete periods to purge. Valid values are the following:

  Calendar periods: CAL_DAY, CAL_WEEK, CAL_MONTH, CAL_QTR, CAL_YEAR.

  MCAL calendar periods: MCAL_DAY, MCAL_PERIOD.

  Prerequisites for using this option are the following:

  • For calendar periods, the target table must contain a column for the period end date key

    - Must be named PERIOD_END_DT_WID or otherwise identified in the mapping using the UD1 check box.

    - Must be a date wid type (YYYYMMDD).

  • For MCAL calendar periods the target table must contain:

    - Period key, usually MCAL_PERIOD_WID or MCAL_DAY_WID, otherwise identified in the mapping using UD1 check box.

    - Calendar key, usually MCAL_CAL_WID, otherwise identified in the mapping using UD2 check box.

• Periods to Delete - This option is for configuring additional functionality for the MCAL calendar periods only. It must be left at the default value (0 - delete current period only) for calendar periods. Setting this option to a value N > 0 will delete exactly N Julian periods of data based on the delete period type. For example, setting to 1 will mean the current and previous periods are deleted.

• Periods to Keep - This option is for configuring additional functionality for the calendar periods only. It must be left at the default value (0 - keep all periods) for calendar periods. Setting this option to a value N > 0 will delete data in periods which are exactly N julian periods older than the current julian period

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';
  

IKM BIAPPS Oracle Slowly Changing Dimension

This option integrates data into an Oracle target table modeled as a Type 2 slowly changing dimension. New records are inserted, and changes to existing records can either trigger an insert or an update depending on the whether there is a change to any of the Type 2 columns. Data can be controlled by isolating invalid data in the error table and recycling when fixed. This option uses the variables #TYPE2_FLG and #UPDATE_ALL_HISTORY to control behavior.

Prerequisites

Prerequisites for using this IKM are the following:

• Update key must be defined in the interface and the key columns should be indexed (usually INTEGRATION_ID, DATASOURCE_NUM_ID, SRC_EFF_FROM_DT).Slowly changing dimension behavior must be set for all target table columns (set in model) and must include the following:

  - Surrogate key (usually ROW_WID)

  - Natural key (usually INTEGRATION_ID, DATASOURCE_NUM_ID)

  - Start and end timestamps (usually EFFECTIVE_FROM_DT and EFFECTIVE_TO_DT)

  - Current flag (usually CURRENT_FLG)

• End timestamp should be mapped to the maximum value (usually #HI_DATE).

• Current flag should be mapped to Y.

• Source from and to dates should be Not Null (default to #LOW_DATE or #HI_DATE).

• ETL_PROC_WID should be indexed.

Column Classification

The following table describes how dimension columns should be categorized and the different behaviors for each classification. The categorization is done on individual table columns in the model.

Column	Description	SCD Behavior	Other Flexfields
Surrogate key	Warehouse generated primary key for dimension	Surrogate key
Natural key	Business or source key, unique in combination with time	Natural key
Start timestamp	Time record is effective from	Start timestamp
End timestamp	Time record is effective to	End timestamp
Current flag	Whether the record is the latest effective	Current flag
Type 2 columns	Creates new version of record (insert) if there is a change to any of the Type 2 columns	Insert on change
Update history columns	Always set to the value from the current record	Overwrite on change	Not a system column
Other system columns	Maintained as insert/update	Overwrite on change	System column
Change columns	Record is rejected if there is no change to any of these columns	Overwrite on change	Change column and system column
SCD1 key	Warehouse generated key corresponding to the natural key	Overwrite on change	SCD1 WID (column flexfield)

Slowly Changing Dimension Features

• Type 2 Changes - If the variable #TYPE2_FLG is turned off (set to 'N') then the dimension behaves as a Type 1 dimension. The natural key must be unique (no history allowed) because no maintenance of the start/end dates is performed. With #TYPE2_FLG on (set to 'Y') new records will be inserted; changes that update at least one Type 2 column will also trigger an insert, subject to some restrictions, and any other change will update the existing record.

  Type 2 changes are triggered as follows:

  - The incoming record must have at least one type 2 column different when compared to the current dimension record.

  - The new Type 2 record start timestamp is calculated as follows: If there is a change column with a non-null date value, then use that (the Oracle BI Applications standard is to use CHANGED_ON_DT as the change column); otherwise, use the current timestamp (sysdate)

• Update All History - With #TYPE2_FLG and #UPDATE_ALL_HISTORY both on (set to 'Y'), then any update history columns will be updated with the value from the current version of the record (latest record with the same natural key).

Options for Functionality

• 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.

• SCD1 Key - Set this to TRUE to automatically maintain a surrogate natural key or Type 1 key. This is managed using a Type 1 table named according to a standard pattern. If the dimension table is W_DIMENSION_D, then the Type 1 table will be W_DIMENSION_T1_D. The sequence that generates the Type 1 key is also named according to a standard pattern. Continuing the example, it would be named W_DIMENSION_D_S1W. Additional columns that are at the same grain (also Type 1) can be automatically maintained on the Type 1 table if they are marked with the UD1 flag.

  Prerequisites for W_DIMENSION_D are as follows:

  • Type 1 key column must be identified by the SCD1 WID flexfield in the model.

  • Type 1 key column should be mapped on source or staging to a constant value, for example, 0.

  • Type 1 key column should be insert only.

  • Type 1 table (W_DIMENSION_T1_D) must have at least the following columns:

    - Type 1 key column

    - Natural key columns

    - System columns W_INSERT_DT, W_UPDATE_DT and ETL_PROC_WID

    - Any columns marked as UD1

  • Type 1 table (W_DIMENSION_T1_D) should have indexes on: Type 1 key columns and natural key columns.

  • Type 1 Key sequence should be created (W_DIMENSION_D_S1W). Type 1 Key sequence should be created (W_DIMENSION_D_S1W) .

• Fill Gaps - Set this to TRUE to automatically extend the first records to cover any earlier date.

• Soft Delete. There are several additional steps that you can perform if the soft delete option is enabled. The variables #SOFT_DELETE_FEATURE_ENABLED (global) and #SOFT_DELETE_PREPROCESS (can be set for each fact or dimension group) control exactly which steps are executed.

  If you are able to implement triggers to efficiently capture deletes on the source system, you can disable the expensive pre-process steps (which extract all source records and compare against all target records) and, instead, directly populate the delete table.

  Step	Action	Control
  Soft delete pre-process	Runs the "Identify Delete" step which compares the data in the primary extract table against the target table, and records any obsolete target rows in the delete table. Uses #LAST_ARCHIVE_DATE to filter target by CREATED_ON_DT system column (set variable to NULL to disable this). Only data sources that have implemented the primary extract are included. If a data source has no records in the primary extract table then no records will be added to the delete table for that data source.	#SOFT_DELETE_FEATURE_ENABLED #SOFT_DELETE_PREPROCESS
  Soft delete on target	Runs the "Soft Delete" step which updates the DELETE_FLG column to 'Y' on the target table for records which have been identified for delete.	#SOFT_DELETE_FEATURE_ENABLED
  Truncate delete table	Removes records from the delete table once they have been processed	#SOFT_DELETE_FEATURE_ENABLED

  
  

  Note that all these steps are committed together, along with any other inserts and updates to the target table. This keeps the data warehouse consistent.

  Prerequisites for using this option are the following:

  • The target table must have the ETL_PROC_WID and W_UPDATE_DT system columns.

  • Tables <target>_PE and <target>_DEL must be created with the columns in the interface key.

  • #LAST_ARCHIVE_DATE must be NULL if target table does not have the CREATED_ON_DT column.

  • #SOFT_DELETE_PREPROCESS should be refreshed from Oracle BI Applications Configuration Manager by the load plan component.

Options for Performance Tuning

• Detection Strategy - To avoid updating the table if no changes have occurred, the incoming data is compared with the existing record on a set of change columns. These are either defined in the model column flexfield (OBI_CHANGE_COLUMN), or otherwise all columns are compared. To always process the changes, this option can be disabled.

• Hints and Full History - 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';
  

• Analyze Target - Statistics will be collected on the target table before it is loaded if the KM Option ANALYZE_TARGET is set to True. By default it is set to False.

IKM BIAPPS SQL Target Override

This is a unique IKM in that it allows you to specify a custom SQL statement to execute against a target table. This SQL statement will execute once for each source record returned by the interface. For a given execution of this SQL statement, values from the source record may be referenced as parameters in the SQL.

This IKM has a variety of different use cases; however, because it does not implement an interface in the typical manner of ODI interfaces, it should be reserved only for implementing system defined logic that will never be customized.

If the custom SQL is not suitable for all target technologies, then platform specific-SQL statements can be given as alternatives. This IKM will run the SQL in the option corresponding to the target technology on execution if available. Otherwise, the SQL in the generic option will be used.

Prerequisites

Prerequisites for using this IKM are the following:

• Implements non-customizable system logic.

• If the generic SQL Override is not suitable for a particular platform, perhaps for performance or incompatibility reasons, then a platform specific SQL Override option must be provided.

• SQL Statements must begin with "INSERT", "UPDATE" or "DELETE" keywords.

• You must ensure hint placeholders are included in the SQL.

• You must ensure all required indexes are in place.

Referencing Source Data

To use data from the source data in the SQL override, do the following:

• Map the source data you want to reference to one of the target columns on the interface.

• Reference it in the SQL Override using the syntax :TARGET_COLUMN.

Options for Functionality

SQL Override Options - A generic SQL Override option is available. Also, platform-specific options are provided for cases where there are variations in syntax or performance for different target technologies.Prerequisites for using this option are as follows:

• Begin SQL statements with "INSERT", "UPDATE" or "DELETE" keywords.

• If the generic SQL Override is not suitable for a particular platform, for example, due to performance or incompatibility reasons, then a platform-specific SQL Override option must be provided.

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';
  

Nested IKM BIAPPS Oracle Control Append

For a nested or temporary interface, using this IKM gives additional functionality in the SQL generated for the nested SQL block. The main benefit is allowing hints to be included. In addition, Aggregate Lookup and Source SQL Override can also be useful for some applications.

Options for Functionality

• Aggregate Lookup - Aggregates the SQL block by using the user flexfields to define a grouping of records to restrict the output to one row per group.

  Prerequisites for using this option are the following:

  • The interface must set the following user flexfields:

    - UD1: Group by columns. These columns are usually the columns being joined to.

    - UD2: Aggregated columns. The fields being returned.

    - UD3: Column for active lookup (LKP_ACTIVE). This is set to 1.

• Source SQL Override - Allows the generated nested SQL block to be overridden. This is useful if the SQL needs to be more dynamic in nature, for example, using variables to reference dynamic tables.

  Prerequisites for using this option are the following:

  • For each target interface column there must be an identically aliased column in the SELECT clause of the SQL override.

  • Use of this option is discouraged. Make sure the use case is reviewed by the Oracle BI Applications Standards Group before implementing.

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.

Nested IKM BIAPPS Oracle Event Queue Delete Append

As part of a main interface using this IKM for Event Queue controlled updates, this IKM can also be used in a nested or temporary interface to give additional functionality in the SQL generated for the nested SQL block. The main benefit is the ability to use the event queue table as the driving table in incremental load, but exclude the event queue table altogether in full load. Also, the use of hints is supported.

Prerequisites for Nested KM Usage

• The main interface must use this KM.

• The event queue table must be specified in the Event Queue Table option.

• The interface must contain only the following:

  • Main source table

  • Event queue table

  • Join between these using ANSI syntax

Mapping Event Queue Columns

In some cases the event queue columns may need to be referenced in parent interfaces. They can be mapped directly in the nested interface if they are identified with the UD1 flexfield. This includes them in the generated SQL block only in incremental load when the event queue table is also included. For full load both the event queue table and any columns mapped from it will be excluded.

Options for Functionality

Event Queue Table - Explicitly name the event queue table that is controlling the incremental load. This should be one of the two source tables on the nested interface.

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.