Just to get myself acquainted with the new Informatica version, I created a mapping in which SCD 2 was inplemented. The mapping is shown here.
In the first step, the input data are read.Let us assume that these records are read. The records contain a number and a name:
number Name 1 Tom 2 ine 3 paula 4 stella 5 Bart
The records may be different in two respects to the records that are tored in the database; the records may be completely new or the records might be an update to the existing records. Let us assume the database contains subsequent records:
PK number name DATE DATE_TO 1 2 'ine' 18-11-2014 16:23:10 NULL 2 3 'paula' 18-11-2014 16:23:10 NULL 3 4 'stella' 18-11-2014 16:23:10 NULL 5 5 'Bartje' 18-11-2014 16:23:10 NULL
The records contain a unique key, the number, a name, a data on which the records are read and a date until which the data are valid.
Comparison between the records that are added and records that already stored show that we have one record that is new (1, Tom) and one record that must be updated (5, Bart). Such comparison is done in the Informatica flow by a lookup, that is followed by a procedure where it is established as to whether a record is unchanged (ine, paula, stella), a record is new (Tom) and a record that is changed (Bart).
PK NEW OLD CHANGE NEW DATE 1.0 ine ine 0 0 2014-11-21 15:11:09.0 2.0 paula paula 0 0 2014-11-21 15:11:09.0 3.0 stella stella 0 0 2014-11-21 15:11:09.0 Tom 0 1 2014-11-21 15:11:09.0 5.0 Bart Bartje 1 1 2014-11-21 15:11:09.0
A record that is new, must be inserted in the database with a new key. It is established that such number must be higher than 5. Hence “Tom” will be inserted in the database.
A record that is updated leads to two actions: the existing record will be updated as to indicate that it is not valid anymore and a new record will be inserted to store “Bart”.
In total,
- we have an update action: the old “Bartje” record will be updated as to indicate that it is not valid anymore.
- we have an insert action that comes from:
- the update on Bart that generates a new record
- the insert of Tom.
We then have this result:
PK number name DATE DATE_TO 1 2 'ine' '18-11-2014 16:23:10.' NULL 2 3 'paula' '18-11-2014 16:23:10.' NULL 3 4 'stella' '18-11-2014 16:23:10.' NULL 5 5 'Bartje' '18-11-2014 16:23:10.' '21-11-2014 15:52:49.' 9 1 'Tom' '21-11-2014 15:52:49.' NULL 10 5 'Bart' '21-11-2014 15:52:49.' NULL