The Type 2 Dimension/Flag Current mapping filters source rows based on user-defined comparisons and inserts both new and changed dimensions into the target. Changes are tracked in the target table by flagging the current version of each dimension and versioning the primary key. In the Type 2 Dimension/Flag Current target, the current version of a dimension has a current flag set to 1 and the highest incremented primary key.
Use the Type 2 Dimension/Flag Current mapping to update a slowly changing dimension table when you want to keep a full history of dimension data in the table, with the most current data flagged. Versioned primary keys track the order of changes to each dimension.
When you use this option, the Designer creates two additional fields in the target:
Flagging the Current Value
The Integration Service generates a current flag of 1 for each row written to the target. This flag indicates the dimension is new or newly updated. If the row is an update to an existing dimension, the Integration Service resets the existing dimension current flag to 0.
As a result, all current versions of a dimension appear in the target with a current flag of 1. All previous versions have a current flag of 0.
For example, the following dimension data is current because the current flag is set to 1:
When these dimensions change, the Integration Service inserts the updated versions with the current flag of 1. The Integration Service also updates the existing rows in the target. It finds the previous current version (where the current flag is set to 1 and updates the current flag to 0:
When you use the Flag Current option, the Integration Service generates a primary key value for each row written to the target, incrementing key values by one. An Expression transformation increments key values by 1,000 for new dimensions. When updating an existing dimension, the Integration Service increments the existing primary key by 1.
For example, the following dimensions are current dimension data (current flags set to one). Their primary keys are multiples of 1,000. This indicates they are both the first version of a dimension:
When the Integration Service inserts updated versions of these dimensions into the target, it sets the current flag to 1. It also creates a new primary key for the updated row by incrementing key of the existing dimension by one.
The Integration Service indicates the existing dimension is no longer current by resetting its current flag to 0:
Understanding the Mapping
The Type 2 Dimension/Flag Current mapping performs the following tasks:
The following figure shows a mapping that the Type 2 Dimension/Flag Current option in the Slowly Changing Dimensions Wizard creates:
The Type 2 Dimension/Flag Current 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.
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 three data flows.
The first data flow uses the Filter transformation, FIL_InsertNewRecord, to filter out existing 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 new row. The Expression transformation, EXP_KeyProcessing_InsertNew, increases the increment between keys by 1,000 and creates a current flag of 1 for each new row.
In the second data flow, the FIL_InsertChangedRecord Filter transformation allows only changed rows to pass to the Update Strategy transformation, UPD_ChangedInserts. UPD_ChangedInserts inserts changed rows to the target. The Expression transformation, EXP_KeyProcessing_InsertChanged, increments the primary key by one and creates a current flag of 1 to indicate the updated row contains current dimension data.
In the third data flow, for each changed row written to the target, the Filter transformation, FIL_UpdateChangedRecord, passes the primary key of the previous version to the Update Strategy transformation, UPD_ChangedUpdate. UPD_ChangedUpdate updates dimensions in the target. The Expression transformation, EXP_UpdateChanged, sets the current flag to 0. This changes the status of the previous dimension from current to not-current.
Understanding the Transformations
The following table describes the function of each transformation in the Type 2 Dimension/Flag Current 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 and increments it by 1,000. It also flags the row current and inserts it into the target:
Data Flow for Changed Rows
The following table describes the data flow for changed rows. For each changed row in the source, this data flow increments the existing primary key by 1, flags the row current, and inserts it 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 end date of the corresponding row in the target to indicate the existing row is no longer current:
Steps to Create a Type 2 Dimension/Flag Current Mapping
To create a Type 2 Dimension/Flag Current 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. To remove a column from the list, select the column and click Remove.
The new mapping appears in the workspace. Make the necessary edits to the mappings.
Note:In the Type 2 Dimension/Flag Current mapping, the Designer uses three instances of the same target definition to enable the three separate data flows to write to the same target table. Generate only one target table in the target database.
Configuring a Type 2 Dimension/Flag Current Session
The Type 2 Dimension/Flag Current mapping inserts both new and updated rows with a unique primary key. It also updates existing rows in the target. When you configure a session for the mapping, 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.