The Type 3 Dimension mapping filters source rows based on user-defined comparisons and inserts only those found to be new dimensions to the target. Rows containing changes to existing dimensions are updated in the target. When updating an existing dimension, the Integration Service saves existing data in different columns of the same row and replaces the existing data with the updates. The Integration Service optionally enters the system date as a timestamp for each row it inserts or updates. In the Type 3 Dimension target, each dimension contains current dimension data.
Use the Type 3 Dimension mapping to update a slowly changing dimension table when you want to keep only current and previous versions of column data in the table. Both versions of the specified column or columns are saved in the same row.
When you use this option, the Designer creates additional fields in the target:
Saving Previous Values
When you step through the Slowly Changing Dimensions Wizard, you choose the columns in which you want to detect changes. The Designer creates an additional column for each column you select and names the additional column after the original, PM_PREV_ColumnName. The Integration Service uses these columns to store previous dimension values.
When the Integration Service writes a new dimension to the target, the previous columns remain null. Each time the Integration Service updates a dimension, it writes existing data into the corresponding previous column, and then writes updates into the original column. As a result, each row in a Type 3 Dimension target contains current dimension data. Each row also contains the previous versions of dimension data, if the dimension has changed.
For example, the first time the Integration Service writes the following dimensions to the target, the previous column, PM_PREV_STYLES remains null:
When the Integration Service updates these rows, it writes the values in the STYLES column (20 and 25) into PM_PREV_STYLES, and then replaces the style data with new style data (14 and 31):
In the Type 3 Dimension mapping, the Integration Service generates a primary key value for each new row written to the target, incrementing key values by one. Updated rows retain their original key values.
Marking the Effective Date
The Type 3 Dimension mapping can optionally note the date on which the Integration Service creates or updates a dimension. If you choose this option, the Designer creates the PM_EFFECT_DATE column. The Integration Service enters the system date in this column each time it creates a new row or updates a row.
Understanding the Mapping
The Type 3 Dimension mapping performs the following tasks:
The following figure shows a mapping that the Type 3 Dimension option in the Slowly Changing Dimensions Wizard creates:
The Type 3 Dimension mapping uses a Lookup and an Expression transformation to compare source data against existing target data. When you step through the Slowly Changing Dimensions Wizard, you enter the lookup conditions (source key columns) and source columns that you want the Integration Service to compare against the existing target. The Designer creates additional columns for the change columns to hold historic data.
For each source row without a matching primary key in the target, the Expression transformation marks the row new. For each source row with a matching primary key in the target, the Expression compares user-defined source and target columns. If those columns do not match, the Expression marks the row changed. The mapping then splits into two data flows.
The first data flow uses the Filter transformation, FIL_InsertNewRecord, to filter out rows. The Filter transformation passes only new rows to the UPD_ForceInserts Update Strategy transformation. UPD_ForceInserts inserts new rows to the target. A Sequence Generator creates a primary key for each row. If you select the Effective Date option in the mapping wizard, the Designer creates an Expression transformation, EXP_EffectiveDate_InsertNew.
The Integration Service uses the system date to indicate when it creates a new row.
In the second data flow, the FIL_UpdateChangedRecord Filter transformation allows only changed rows to pass to the Update Strategy transformation UPD_ChangedInserts. In addition, the Filter transformation updates the changed row: it takes the new versions of data from the source qualifier, and uses existing versions of dimension data (passed from the Lookup transformation) to populate the previous column fields. UPD_ChangedInserts inserts changed rows to the target. If you select the Effective Date option in the mapping wizard, the Designer creates an Expression transformation, EXP_EffectiveDate_InsertChanged. The Integration Service uses the system date to indicate when it updates a row.
Understanding the Transformations
The following table describes the function of each transformation in the Type 3 Dimension mapping:
Data Flow for New Rows
The following table describes the data flow for new rows. For each new row in the source, this data flow creates a primary key, optionally notes the load date, and inserts the row into the target:
Data Flow to Update Existing Rows
The following table describes the data flow for existing rows. For each changed row in the source, this data flow updates the corresponding row in the target, copying existing data into previous columns, updating new data, and optionally notes the date the row changed:
Steps to Create a Type 3 Dimension Mapping
To create a Type 3 Dimension mapping:
All available source definitions appear in the Select Source Table list. This list includes shortcuts, flat file, relational, and Application sources.
The wizard adds selected columns to the Logical Key Fields list.
Tip:The columns you select should be a key column in the source.
When you run the session, the Integration Service performs a lookup on existing target data. The Integration Service returns target data when Logical Key Fields columns match corresponding target columns. To remove a column from Logical Key Fields, select the column and click Remove.
The wizard adds selected columns to the Fields to Compare for Changes list.
When you run the session, the Integration Service compares the columns in the Fields to Compare for Changes list between source rows and the corresponding target (lookup) rows. If the Integration Service detects a change, it marks the row changed.
Note:Select the columns for which you want to keep previous values. To keep previous values in the target, the Designer creates an additional column for each column in this list. It names the columns PM_PREV_ColumnName. To remove a column from the list, select the column and click Remove.
The wizard displays the columns the Integration Service compares and the name of the column to hold historic values.
Note:In the Type 3 Dimension mapping, the Designer uses two instances of the same target definition to enable the two separate data flows to write to the same target table. Generate only one target table in the target database.
Configuring a Type 3 Dimension Session
The Type 3 Dimension mapping inserts new rows and updates existing rows in the target. When you configure a session, complete the following steps:
Using The Designer
Working With Sources
Working With Flat Files
Working With Targets
Mapping Parameters And Variables
Working With User-defined Functions
Using The Debugger
Viewing Data Lineage
Managing Business Components
Creating Cubes And Dimensions
Using The Mapping Wizards
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.