Different Types of Type 2 Slowly Changing Dimensions in OBIA

In Type 2 Slowly Changing Dimension, a new record is added to the table to represent the new information and old records are updated. Therefore, both the original and the new record will be present. The new record gets its own primary key.

Example: Consider dimension table with supplier information, such as supplier name, supplier ID and year. When the name of the supplier changes, a new row is added to the table with latest name and the previous row is retained by adding a new column

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. Here changes are tracked by versioning the primary key .Version number is created for each dimension rows.

The current version will have the highest version and highest value for the primary key.

• Flag Current Mapping

In Flag Current Mapping the changes are tracked by flagging the current version of each dimension and versioning the primary key. For the current version of the dimension the current flag is set to 1 and the primary key will be the highest.

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

Here changes are tracked by keeping effective dates for each version of the dimension in the target. In Effective Date Range Mapping each record will have Start date and End Date. For the current version of the record the start date will be current date and end date will be default date (max 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.