Skip to content

Definitions of Transformations

Jump to bottom
Kamil edited this page Aug 6, 2020 · 2 revisions

Definitions of Transformations

SCD Merge Wizard supports following transformations for defining your business needs:

Business Key

Business keys are used to connect source and target (or history) table by uniquely identifying single record in both tables. At least one Business Key transformation must be defined, but of course, there can be more, depending on your data. {anchor:scd0}

SCD0

SCD0 (fixed attribute in Microsoft's language) represents source column which is written into corresponding column on the target only once - when new record is added. After that, changing of such column on source, doesn't effect corresponding column on the target.

Read this article for more details about SCD0 transformation. {anchor:scd1}

SCD1

SCD1 (changing attribute) transformation means that if column changes on the source, corresponding column in the target table will be overwritten with the value from the source table.

Read this article for more details about SCD1 transformation. {anchor:scd2}

SCD2

SCD2 (historical attribute) transformation means that if this column changes on the source table, existing record on the target table will be "marked as old" and new record will be added to the target table.

This type of transformation differs from from transformations we explained earlier because it is not enough to define just SCD2 columns, but you also need to define "helper" columns:

SCD2 Date From

This transformation is used only when there is at least one SCD2 transformation defined. It represents the date (and time) after which some record is considered as active (because SCD2 transformation creates new record each time). {anchor:scd2dt}

SCD2 Date To

This transformation can be used only when there is at least one SCD2 transformation defined. It represents the date (and time) until some record is considered as active.

Note: you can use "SCD2 Is Active" together with this transformation. {anchor:scd2ia}

SCD2 Is Active

This transformation can be used only when there is at least one SCD2 transformation defined. This field is used as a flag which represents if record is currently active record. As we said earlier, SCD2 transformation can produce more than one record for one business key, but only one record can be "active record" for defined business key and this transformation identifies this record by setting corresponding flag.

Note: you can use "SCD2 Date To" together with this transformation. {anchor:scd3c}

SCD3 Current

By it's functionality, this transformation is completely the same as SCD1 transformation - it looks for the changes in source and transfers those changes into target. The only difference between SCD1 and SCD3 Current is that SCD3 Current is used by SCD3 Previous (if exists) as a compare reference.

Read this article for more details about SCD3 transformation. {anchor:scd3o}

SCD3 Original

This transformation is completely the same as SCD0 transformation explained earlier.

Read this article for more details about SCD3 transformation. {anchor:scd3p}

SCD3 Previous

This transformation represents column where previous value of SCD3 Current transformation is kept when SCD3 Current is changed.

Read this article for more details about SCD3 transformation. {anchor:scd3df}

SCD3 Date From

This transformation represents date (and time) from which current SCD3 Current value is considered as effective. {anchor:cd}

Created Date

Created Date represents column where record creation date is stored. {anchor:md}

Modified Date

Modified Date represents column where last record modification date is stored. {anchor:dd}

Deleted Date

This transformation represents column where source record deletion date is stored. {anchor:id}

Is Deleted

This transformation represents a flag if source record is deleted or not.

|Important note about deleting records: In most cases, records from target (Dimension) table should not be deleted if corresponding records are deleted in the source, because of the referential integrity and history. But, it is always good idea to have some mechanism to track deleted records on the source and to set some flags on the target.| {anchor:vn}

Version Number

This transformation represents a column which tracks number of changes of corresponding record. For every SCD1 and SCD3 change, version number is increased by 1 and for every SCD2 change (for new record) version number can be increased by 1 or reset to value 1, depending on user's choice.

Clone this wiki locally