Implementing SCD – Using Designer Screen Wizards - Learning Informatica PowerCenter 9.x - page 87

EMPLOYEE_ID (supposed to be the primary key) column, and PM_VERSION_NUMBER
to understand the current and historical records.

The following SCD2 table has the flag column added to it:

PM_PRIMARYKEY EMPLOYEE_ID NAME LOCATION PM_CURRENT_FLAG

100 1001 STEVE INDIA 0

101 1001 STEVE USA 1

As you can see, we are maintaining the full history by adding new records to maintain the
history of the previous records. We add two new columns in the table, that is,
PM_PRIMARYKEY to handle the issues of duplicate records in the primary key in the
EMPLOYEE_ID column, and PM_CURRENT_FLAG to understand the current and history
record.

Again, if STEVE is shifted, the data would look like this:

PM_PRIMARYKEY EMPLOYEE_ID NAME LOCATION PM_CURRENT_FLAG

100 1001 STEVE INDIA 0

101 1001 STEVE USA 0

102 1001 STEVE JAPAN 1

The following table of type SCD2 shows you the data range added to it:

PM_PRIMARYKEY EMPLOYEE_ID NAME LOCATION PM_BEGIN_DATE PM_END_DATE

100 1001 STEVE INDIA 01-01-14 31-05-14

101 1001 STEVE USA 01-06-14 99-99-9999

As you can see, we are maintaining the full history by adding new records to maintain the
history of the previous records. We add three new columns in the table, that is,