Oracle^® Business Intelligence Applications Installation and Configuration Guide > Customizing the Oracle Business Analytics Warehouse > Other Types of Customizations Requiring Special Handling >

Modifying Type II SCD Triggers

All dimensions are configured as Type I dimensions when first installed. If you want to capture historical changes, the dimension must be enabled to capture Type II changes. However, a common form of customization is to change the criteria that triggers a Type II change in a dimension. Most changes in a dimension are treated as Type I changes in that the existing column is simply overwritten with the new value. Once enabled, there are only a small number of columns that will trigger a Type II change. You can extend the logic that triggers a Type II change by adding additional columns to the logic that tracks Type II changes. In addition, you can remove columns from this logic in case you do not want these types of changes to trigger a Type II change. Modifying the Type II tracking logic is one of the only exceptions to the rule that you should not make changes to shipped logic. The logic that tracks Type II changes is contained in exposed objects in each SIL dimension mapping that supports Type II changes.

There is a lookup between the Source Qualifier and the Filter. This lookup is used to determine if the record already exists in the target and, therefore, needs to be updated in addition to other system columns. Columns that track Type II changes are returned in this lookup and passed to the next expression. The columns returned by the lookup are compared with the columns passed from the staging table. If any of these columns are different, the record is flagged for a Type II change.

This expression contains a variable port named 'TYPE2_COLS_DIFF'. If this port is flagged as 'Y' then a Type II change will be triggered. If it is flagged as 'N' then a Type I change will be triggered.

To change the columns used to determine a Type II change, modify the lookup to pass any additional columns you want to be evaluated for Type II changes. Then, modify the variable port 'TYPE2_COLS_DIFF' to include this column when being evaluated.

For example, the SIL_BOMHeaderDimension mapping compares the following columns:

• BOM_HEADER
• BOM_VERSION
• BASE_QTY
• ACTIVE_FLG

If you wanted to include BOM_VERSION as part of Type II logic, you would change the logic for 'TYPE2_COLS_DIFF' from the following:

IIF(BOM_NUMBER != LKP_BOM_NUMBER, 'Y',

IIF(BOM_VERSION != LKP_BOM_VERSION, 'Y',

IIF(BASE_QTY != LKP_BASE_QTY, 'Y',

IIF(ACTIVE_FLG != LKP_ACTIVE_FLG, 'Y',

'N'))))

To this:

IIF(BOM_NUMBER != LKP_BOM_NUMBER, 'Y',

IIF(BOM_VERSION != LKP_BOM_VERSION, 'Y',

IIF(BASE_QTY != LKP_BASE_QTY, 'Y',

IIF(ACTIVE_FLG != LKP_ACTIVE_FLG, 'Y',

IIF(BOM_VERSION!= LKP_ BOM_VERSION, 'Y',

'N')))))