This project is read-only.

Examples

Following tables will be used in this section for better understanding of the way transformations should be defined:

Source Table
SourceTableSchema.png

Target (Destination) Table
TargetTableSchema.png

Beside, every transformation example will have image explanation. These image is created using snapshots from real SCD Merge Wizard application and can have 3-5 columns:
  1. Source Column
  2. Transformation
  3. Target Column
  4. Custom Value 1
  5. Custom Value 2
TransHeaders.png

Some transformations don't use custom values, so these two columns won't be shown in that case.


Click on the transformation you wish to see an example:

Business Key

Following example shows how Business Key between our source and target table should be defined:

BusinessKeyDef.png

As you can see, connection between two tables is made using target column EmployeeID, not EmployeeSID (which is table primary unique key). This is because SCD2 transformation can create more than one record and we need one column to create connection between two tables (EmployeeID - so called business key) and one column that will uniquely identify every row in the target table (EmployeeSID - usually called surrogate key or surrogate identity - SID).

Source Column Business Key in source table
Transformation Business Key
Target Column Business Key in target table
Custom Value 1 Not used
Custom Value 2 Not used

SCD0

SCD0Def.png

You need to define which source column transforms into which target column.

Source Column SCD0 column on the source
Transformation SCD0
Target Column SCD0 column on the target
Custom Value 1 Not used
Custom Value 2 Not used

SCD1

SCD1 transformations should be defined like this:

SCD1Def.png

You need to define which source column transforms into which target column.

Source Column SCD1 column on the source
Transformation SCD1
Target Column SCD1 column on the target
Custom Value 1 Not used
Custom Value 2 Not used

SCD2

SCD2Def.png

You need to define which source column transforms into which target column. Beside, you will need to define other SCD2 transformations (so called helper transformations) to support SCD2 feature. You will have to define SCD2 Date From and one or both of SCD2 Date To, SCD2 Is Active.

Source Column SCD2 column on the source
Transformation SCD2
Target Column SCD2 column on the target
Custom Value 1 Not used
Custom Value 2 Not used

SCD2 Date From

SCD2DateFromDef.png

You cannot define source column because this transformation is used for all SCD2 transformations / columns, but you need to define custom values: you need to define value when new version of a record is created on source (custom value 1) as well as initial value when record with defined business key is written into the target table for the first time (custom value 2).

Source Column Not used
Transformation SCD2 Date From
Target Column SCD2 Date From column on the target
Custom Value 1 Date when new record becomes active
Custom Value 2 Start date of first record for defined business key

SCD2 Date To

SCD2DateToDef.png

You cannot define source column because this transformation is used for all SCD2 transformations. You need to define value of this column when this record is becoming inactive / newer record is added (custom value 1) as well as value for last (active) record (custom value 2).

Source Column Not used
Transformation SCD2 Date To
Target Column SCD2 Date To column on the target
Custom Value 1 Date when old records become inactive
Custom Value 2 End date of last (current) record for defined business key

SCD2 Is Active

SCD2IsActiveDef.png

You cannot define source column because this transformation is used for all SCD2 transformations. You will have to define value of this column when this record is active (custom value 1) as well as value when record is not active (custom value 2).

Source Column Not used
Transformation SCD2 Is Active
Target Column SCD2 Is Active flag column on the target
Custom Value 1 Value when record is active
Custom Value 2 Value when record is not active

SCD3 Current

SCD3 Current transformation should be defined like this:

SCD3CurrDef.png

Source Column SCD3 Current column on source
Transformation SCD3 Current
Target Column SCD3 Current column on the target
Custom Value 1 Not used
Custom Value 2 Not used

SCD3 Original

SCD3 Original transformation should be defined like this:

SCD3OrigDef.png

Source Column SCD3 Original column on source
Transformation SCD3 Original
Target Column SCD3 Original column on the target
Custom Value 1 Not used
Custom Value 2 Not used

SCD3 Previous

SCD3 Previous transformation should be defined like this:

SCD3PrevDef.png

Source Column SCD3 Previous column on source
Transformation SCD3 Previous
Target Column SCD3 Previous column on the target
Custom Value 1 Initial Value - when record for defined business key is added for the first time
Custom Value 2 Not used

SCD3 Date From

SCD3 Date From transformation should be defined like this:
SCD3EffFrom.png

Source Column SCD3 column on source
Transformation SCD3 Date From
Target Column SCD3 Date From column on the target
Custom Value 1 Effective from value
Custom Value 2 Initial Value - when record for defined business key is added for the first time

Created Date

Created Date transformation should be defined like this:

CreatedDateDef.png

Source Column Not used
Transformation Created Date
Target Column Created Date column on the target
Custom Value 1 Date when record is created
Custom Value 2 Not used

Modified Date

Modified Date transformation should be defined like this:

ModifiedDateDef.png

Source Column Not used
Transformation Modified Date
Target Column Modified Date column on the target
Custom Value 1 Date when record is modified
Custom Value 2 Initial Value

Deleted Date

Deleted Date transformation should be defined like this:

DeletedDateDef.png

Source Column Not used
Transformation Deleted Date
Target Column Deleted Date column on the target
Custom Value 1 Date when record is deleted
Custom Value 2 Initial Value

Is Deleted

Is Deleted transformation should be defined like this:

IsDeletedDef.png

Source Column Not used
Transformation Is Deleted
Target Column Is Deleted column on the target
Custom Value 1 Value when record is deleted
Custom Value 2 Value when record is not deleted

Version Number

Version Number transformation should be defined like this:

VersionDef.png

Source Column Not used
Transformation Version Number
Target Column Version Number column on the target
Custom Value 1 Not used
Custom Value 2 Not used

Last edited Jun 23, 2013 at 1:24 PM by knyazs, version 17

Comments

No comments yet.