Skip to content

Examples

Jump to bottom
Kamil edited this page Aug 6, 2020 · 1 revision

Examples

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

Source Table

Target (Destination) Table

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:

  • Source Column
  • Transformation
  • Target Column
  • Custom Value 1
  • Custom Value 2

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:

{anchor:bk}

Business Key

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

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 | {anchor:scd0}

SCD0

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 | {anchor:scd1}

SCD1

SCD1 transformations should be defined like this:

![](Examples_ 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 | {anchor:scd2}

SCD2

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 | {anchor:scd2df}

SCD2 Date From

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 | {anchor:scd2dt}

SCD2 Date To

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 | {anchor:scd2ia}

SCD2 Is Active

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 | {anchor:scd3c}

SCD3 Current

SCD3 Current transformation should be defined like this:

| 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 | {anchor:scd3o}

SCD3 Original

SCD3 Original transformation should be defined like this:

| 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 | {anchor:scd3p}

SCD3 Previous

SCD3 Previous transformation should be defined like this:

| 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 | {anchor:scd3df}

SCD3 Date From

SCD3 Date From transformation should be defined like this:

| 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 | {anchor:cd}

Created Date

Created Date transformation should be defined like this:

| 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 | {anchor:md}

Modified Date

Modified Date transformation should be defined like this:

| 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 | {anchor:dd}

Deleted Date

Deleted Date transformation should be defined like this:

| 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 | {anchor:id}

Is Deleted

Is Deleted transformation should be defined like this:

| 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 | {anchor:vn}

Version Number

Version Number transformation should be defined like this:

| 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 |

Clone this wiki locally