Monthly Archives: November 2014

Slowly Changing Dimensions Type 2

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.

Informatica

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