The Type 1 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 by overwriting the existing dimension. In the Type 1 Dimension mapping, all rows contain current dimension data.
Use the Type 1 Dimension mapping to update a slowly changing dimension table when you do not need to keep any previous versions of dimensions in the table.
For example, you might have a site dimension table with store code, location, and overhead that you update after the company opens a new store. This dimension is used for sales and overhead calculations. Since you do not need to know the previous address of the same store or the store overhead from the previous year, you do not need previous dimension data in the table. With the Type 1 Dimension mapping, you can keep current data without a historical log.
When you use the Type 1 Dimension option, the Designer creates an additional column in the mapping target, PM_PRIMARYKEY. In this column, the Integration Service generates a primary key for each row written to the target, incrementing new key values by 1.
Understanding the Mapping
The Type 1 Dimension mapping performs the following tasks:
The following figure shows a mapping that the Slowly Changing Dimensions Wizard creates when you select the Type 1 Dimension option:
The Type 1 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.
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 separate 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, and a Sequence Generator creates a primary key for each row.
In the second data flow, the FIL_UpdateChangedRecord Filter transformation allows only changed rows to pass to the Update Strategy transformation, UPD_ChangedUpdate. UPD_ChangedUpdate replaces existing rows in the target with the updated source rows.
Understanding the Transformations
The following table describes the function of each transformation in the Type 1 Dimension mapping:
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.
The following table describes the data flow for new rows:
Data Flow for Changed Rows
For each changed row in the source, this data flow marks the row for update and overwrites the corresponding row in the target.
The following table describes the data flow for changed rows:
Steps to Create a Type 1 Dimension Mapping
To create a slowly growing target mapping, complete the following steps.
To create a Type 1 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 workflow containing 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 workflow containing 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 1 Dimension mapping, the Designer uses two instances of the same target definition to enable inserting and updating data in the same target table. Generate only one target table in the target database.
Configuring a Type 1 Dimension Session
The Type 1 Dimension mapping inserts new rows with a new primary key and updates existing rows. When you create a session for the mapping, configure the session as follows:
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.