The Type 2 Dimension/Effective Date Range 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 maintaining an effective date range for each version of each dimension in the target. In the Type 2 Dimension/Effective Date Range target, the current version of a dimension has a begin date with no corresponding end date.
Use the Type 2 Dimension/Effective Date Range mapping to update a slowly changing dimension table when you want to keep a full history of dimension data in the table. An effective date range tracks the chronological history of changes for each dimension.
When you use this option, the Designer creates the following additional fields in the target:
Maintaining the Effective Date Range
The Integration Service generates a begin date for each new and changed dimension it inserts into the target, using the current system date. The end date for these dimensions is NULL.
Each time the Integration Service inserts a changed dimension, it updates the previous version of the dimension in the target, using the current system date to fill the previously null end date column.
As a result, all current dimension data in the Type 2 Dimension/Effective Date Range target have null values in the PM_END_DATE column. All previous versions of dimension data have a system date in PM_END_DATE to indicate the end of the effective date range for each version.
For example, the following dimensions are current dimension data since their end date columns are null:
When the Integration Service finds updated versions of these dimensions in the source, it inserts them into the target, using the system date to indicate the beginning of their effective date ranges and leaving the end dates null.
The Integration Service also updates the existing versions in the target, entering the system date to end the effective date range:
When you use the Effective Date Range option, the Integration Service generates a primary key value for each row written to the target, incrementing key values by one.
Understanding the Mapping
The Type 2 Dimension/Effective Date Range mapping performs the following tasks:
The following figure shows a mapping that the Type 2 Dimension/Effective Date Range option in the Slowly Changing Dimensions Wizard creates:
The Type 2 Dimension/Effective Date Range 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 columns that you want the Integration Service to compare against the existing target.
For each source row without a 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 row. The Expression transformation, EXP_KeyProcessing_InsertNew, uses the system date to indicate the start of the effective date range. The transformation leaves the end date null, whichindicates the new row contains current dimension data.
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, uses the system date to indicate the start of the effective date range. The transformation leaves the end date null, which indicates the changed 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 rows in the target. The Expression transformation, EXP_UpdateChanged, updates the end date column with the system date. This changes the status of the dimension from the current version to a previous version.
Understanding the Transformations
The following table describes the function of each transformation in the Type 2 Dimension/Effective Date Range 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, sets the beginning of the effective date range, and inserts the row 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 creates a new primary key, sets the beginning of the effective date range, 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 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/Effective Date Range Mapping
To create a Type 2 Dimension/Effective Date Range 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.
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 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/Effective Date Range 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/Effective Date Range Session
The Type 2 Dimension/Effective Date Range 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.