How Transformations Work?

This section will provide you with the answers how transformations work using examples.

Business Key

SCD 0

Current Source:

ID Name
12 John Doe


Current Target:

SURROGATE_KEY ID Name


When record with ID=12 is read from source and there is no record with that ID on the target, new record will be added:

SURROGATE_KEY ID Name
1 (new) 12 (new) John Doe (new)


If record changes on the source:

ID Name
12 Ted Mosby


Next time this record is read from the source, target will no be affected:

SURROGATE_KEY ID Name
1 12 John Doe



SCD 1

Current Source:

ID Name
40091 Adventure Works Co Ltd


Current Target:

SURROGATE_KEY ID Name


When record with ID=40091 is read from source and there is no record with that ID on the target, new record will be added:

SURROGATE_KEY ID Name
1 (new) 40091 (new) Adventure Works Co Ltd (new)


If record changes on the source:

ID Name
40091 ACME Ltd


Next time this record is read from the source, target will be updated as well:

SURROGATE_KEY ID Name
1 40091 ACME Ltd (upd)



SCD 2 (together with SCD2 Date From, SCD2 Date To, SCD2 Is Active)

SCD2 transformation uses helper transformations SCD2 Date From, SCD2 Date To and SCD2 Is Active. SCD2 Date From is mandatory transformations and other two can be used both or just one.

Current Source:

ID Name
331 New York


Current Target:

SURROGATE_KEY ID Name DATE_FROM DATE_TO IS_ACTIVE


When record with ID=331 is read from source and there is no record with that ID on the target, new record will be added:

SURROGATE_KEY ID Name DATE_FROM DATE_TO IS_ACTIVE
1 (new) 331 (new) New York (new) "SCD2 Date From Insert Value" (new) "SCD2 Date To Insert Value" (new) "SCD2 Is Active Insert Value" (new)


If record changes on the source:

ID Name
331 New Jersey


Next time this record is read from the source, target will be updated as well:

SURROGATE_KEY ID Name DATE_FROM DATE_TO IS_ACTIVE
1 331 New York "SCD2 Date From Insert Value" "SCD2 Date To Update Value" (upd) "SCD2 Is Active Update Value" (upd)
2 (new) 331 (new) New Jersey (new) "SCD2 Date From Insert Value" (new) "SCD2 Date To Insert Value" (new) "SCD2 Is Active Insert Value" (new)

Last edited Jul 28, 2013 at 10:30 PM by knyazs, version 4

Comments

No comments yet.