Skip to main content

Slowly changing dimension (SCD) types using Data Services

Published on 13 April 2021
Slowly Changing Dimension (SCD) Implementation | SAP Data Services
Neethu Navas
Neethu Navas
SAP Data Migration Trainee

Neethu Navas has been working with Applexus Technologies for the past 1.5 years, as a part of the Data Migration team. She has experience on Data migration of Retail SAP business and has worked on migration project of a Grocery chain as well as in internal product development. Neethu holds a Btech degree in Electronics and communication and has completed PG in Signal processing.

What is a Slowly Changing Dimension

Slowly Changing Dimension (SCD) is a dimension that stores and manages both current and historical data over time in a data warehouse. It is considered and implemented as one of the most critical ETL tasks in tracking the history of dimension records.

  • TYPE 0 - Fixed Dimension.
    No changes allowed, dimension never changes.

  • TYPE 1- No History.
    Update record directly, there is no record of historical values, only current state.

  • TYPE 2- Row Versioning.
    Track changes as version records with current flag & active dates and other metadata

  • Type 3 – Previous Value column.
    Track change to a specific attribute, add a column to show the previous value, which is updated as further changes occur

  • Type 4 – History Table
    Show current value in dimension table but track all changes in separate table

  • Type 6 – Hybrid SCD
     Utilize techniques from SCD Types 1, 2, and 3 to track change

In reality, only types 0, 1, and 2 are widely used, with the others reserved for very specific requirements.

Here we are going to implement the commonly used SCDs i.e.: TYPE0, TYPE 1and TYPE 2 using BODS

Fixed Dimension.

Value remains the same as it were at the time the dimension record was first entered.
Consider an example with fields Name, IDate and Designation.

Slowly Changing Dimension (SCD) Implementation | SAP Data Services
Fig 1 Source Type 0

Here the date and designation remain the same.
We can simply map all the values to a table using query transform.
DF_TYPE_0: Map all the fields

Slowly Changing Dimension (SCD) Implementation | SAP Data Services
Fig 2 Data flow for Type 0

Direct mapping of the fields alone is required.

Slowly Changing Dimension (SCD) Implementation | SAP Data Services
Fig 3 Query for type 0

The output will be the same as the source.

Slowly Changing Dimension (SCD) Implementation | SAP Data Services
Fig 4 Output of Type 0

TYPE 1-No History.

This method does not track any history data. This methodology overwrites old data with new data without keeping the history. This method is mainly used for misspelled names.
Let us consider below-given data as our target data after the first run.

Slowly Changing Dimension (SCD) Implementation | SAP Data Services
Fig 5 Output after the first run

Consider the designation of ‘John’ is changed to B on the date 2003.12.01 and a new member’ June’ also joined.

Slowly Changing Dimension (SCD) Implementation | SAP Data Services
Fig 6 Source for type 1(Changed records)

In TYPE2, No history has preserved the designation and IDate are updated with new value.
In BODS it can be implemented by using the following transformation.<.p>

Slowly Changing Dimension (SCD) Implementation | SAP Data Services
Fig 7 Data flows for Type 1 implementation

DF_TYPE1_source: Map the source to the table.

Slowly Changing Dimension (SCD) Implementation | SAP Data Services
Fig 8 Data flow to map the source

DF_TYPE1_UPDATED: Implement the type 2 logic.

Slowly Changing Dimension (SCD) Implementation | SAP Data Services
Fig 9 Data flow to implement Type 1

Source: The file which contains the changed values:

Slowly Changing Dimension (SCD) Implementation | SAP Data Services

Query: Map the source data to query transform without applying any transformation.

Slowly Changing Dimension (SCD) Implementation | SAP Data Services
Fig 11 Query for type 1

Table Comparison: Table comparison used to compare source data and the target table data.

Slowly Changing Dimension (SCD) Implementation | SAP Data Services
Fig 12 Table_Comparison for type 1

Here we are comparing the source data with the target table (create a staging table with required fields as the target table i.e.: add ID field for key generation).
Key_Generation: This transform is used to generate a surrogated key.

Slowly Changing Dimension (SCD) Implementation | SAP Data Services
Fig 13 Key_Generation for type 1

After the execution of the job, we will get the required output:

Slowly Changing Dimension (SCD) Implementation | SAP Data Services
Fig 14 Output of type 1

Here the IDATE and Designation is replaced with current values.

TYPE 2-Row Versioning.

In Type 2 Slowly Changing Dimension, a new record is added to the table to represent the new information. Therefore, both the original and the new record will be present.

In order to support type 2 changes, we need to add four columns to our table:

Surrogate Key – the original ID will no longer be sufficient to identify the specific record we require, we therefore need to create a new ID that the fact records can join to specifically.

Current Flag – A quick method of returning only the current version of each record

Start Date – The date from which the specific historical version is active

End Date – The date to which the specific historical version record is active

In the first run, we have the following values,

Slowly Changing Dimension (SCD) Implementation | SAP Data Services
Fig 15 Source for first run

In BODS, it can be implemented by using the following transformation.

Slowly Changing Dimension (SCD) Implementation | SAP Data Services
Fig 16 Data flow for Type 2

DF_TYPE2_TABLE: Map the values

Slowly Changing Dimension (SCD) Implementation | SAP Data Services
Fig 17 Data flow to map the source

DF_TYPE2_UPDATION: Implement the logic.

 

Slowly Changing Dimension (SCD) Implementation | SAP Data Services
Fig 18 Data flow to implement Type 2

Source: The file which contains the changed values:

Slowly Changing Dimension (SCD) Implementation | SAP Data Services
Fig 19 Source of Type 2

Query: Map the source data to query transform without applying any transformation. Add extra 2 columns for the target table ID and Current flag.

Slowly Changing Dimension (SCD) Implementation | SAP Data Services
Fig 20 Query for Type 2

Table_Comparison: Table comparison used to compare source data and the target table data.

Slowly Changing Dimension (SCD) Implementation | SAP Data Services
Fig 21 Table_Comparison for type 2

History_Preserving:- This transform is used to store the history data, and if any updates on source data then new row will be inserted.
Here we are providing Valid-from date as Start Date and Valid to date as End_Date. For new records, Valid to date has to be given. Current _Flag can be set to determine the current and old values.

Slowly Changing Dimension (SCD) Implementation | SAP Data Services
Fig 22 History_Preserving for type 2

Key_Generation: This transform is used to generate a surrogated key.

Slowly Changing Dimension (SCD) Implementation | SAP Data Services
Fig 23 Key_Generation for type 2

After the execution of the job.

Slowly Changing Dimension (SCD) Implementation | SAP Data Services
Fig 24 Output of Type 2

Here we can see that John has 2 records, the Current-flay set to “N” are the old record, and the Current-flay set to “Y” is the current record.

Add new comment

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.