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

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.

Comments are closed.