Mapping Architect for Visio Guide

10.5
- 10.4.0

Back Next

Type 2 Slowly Changing Dimensions Template

In the Type 2 Dimension mapping, the slowly changing dimensions table is updated with new and changed dimensions. There are three types of Type 2 Slowly Changing Dimensions:

Version Data Mapping
. The Type 2 Dimension/Version Data 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 versioning the primary key and creating a version number for each dimension in the table. In the Type 2 Dimension/Version Data target, the current version of a dimension has the highest version number and the highest incremented primary key of the dimension.

Use the Type 2 Dimension/Version Data mapping to update a slowly changing dimensions table when you want to keep a full history of dimension data in the table. Version numbers and versioned primary keys track the order of changes to each dimension.

Flag Current Mapping
. 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 dimensions 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.

Effective Date Range Mapping
. 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 start date with no corresponding end date. Use the Type 2 Dimension/Effective Date Range mapping to update a slowly changing dimensions 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.

For example, you might have a dimension table with product information, such as product name, product ID, year, and product price. When the price of the product changes, a new row is added to the table with latest price information and the previous row is retained by adding a new column with a version\date\flag mapping. When the product price changes continuously, the complete history of the changes are stored.

Using Informatica Mapping Templates

Parameters

Understanding the Mapping

Download Guide

Watch

Comments

Communities

Knowledge Base

Success Portal