Exclusive 3 Types of Slowly Changing Dimension

Type1 Dimension

• The Type 1 Dimension Mapping does update rows of existing dimensions in the target by overwriting them.
• 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 if you do not need to keep any previous versions of dimensions in the table.

Type 2 Dimension 

• The Type-2 Dimension Data mapping 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 in the table.
• Use the Type 2 Dimension Data mapping to update a slowly changing dimension table, if you want to keep a complete history of dimension data in the table.
• Version numbers and versioned primary keys track the order of changes for each dimension.

Type 3 Dimension 

• 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 (it updates existing rows only if they have updates).
• During update, the Informatica Server saves existing data in different columns of the same row and replaces it with the updates.

Related Posts