How to implement SCD Type 2 using History Preserve Transform in Data Services

This article is part of our comprehensive data services tutorial Learning SAP Data Services - Online Tutorial, you may want to check that tutorial first if you have not already done so.

SCD Type 2

SCD stands for slowly changing dimension. An SCD Type 2 mapping tries to capture the changes of a dimension table and store the history of changes in different rows in the target table. We also mark each row with a flag with value 'Y' or 'N' signifying if the row is latest or old.

When loading the target table, we extract only changed record sets from the source so that we do not need to process extra records unnecessarily. In order to extract only the changed records (also known as incremental loading), we use a Query Transform in the data flow.

Table Comparison Transform

In the previous tutorial we have already learnt how to use a table comparison transform. We use the same table comparison transform here so that we can specify the compare columns against the primary key columns which is used for joining.

Table comparison transform basically gives us the Op-code or operational code which helps us identify if the row is already present in the target or not.

History Preserving Transform

In order to correctly implement an SCD Type 2 dimensional table, we need to populate columns like - active flag, valid from, valid to etc. While active flag only tells us if the record is the current record or not, valid from and valid to columns tell us the date ranges within which the record was valid.

History preserving transform lets us specify the valid from and valid to date columns as well as the active/current flag columns.

Hands-on tutorial video that shows you how to use History Preservation Transform in SAP data services (BODS)