Configure Type 2 SCDs*

*This document is a work in progress

Type 2 Slowly Changing Dimension (SCD) is a method used in data warehousing to track changes in data over time without losing its history. It works as follows:

1. When a value in a dimension (like customer information) changes, instead of overwriting the old data, a new record is created.

2. Each new record contains the updated information and includes start and end dates to show the duration when each record was valid.

3. This method allows you to analyze historical data and understand how things have evolved over time.

For more details on Type 2 SCDs, click here.

1. Configuration

It is recommended to configure an auto increment key non editable column as a surrogate key.

Configure the Following Fields:

1. Surrogate Key:

   • Define a surrogate key as a unique identifier for each record in the dimension table.

   • This key is auto incremented by one.

2. Business Key:

   • Identify and define the business key for the table, which uniquely identifies the business entity (e.g., customer ID, product code).

3. Identifier Key:

   • Define the identifier key(s) to track changes in the SCD table. This key helps to capture changes and maintain historical data for the dimension.

     1. Start Date:

     2. Specify a start date field to indicate when the record became effective.

     3. This field is critical for understanding the timeline of changes and for querying historical data accurately.

     4. End Date:

     5. Specify an end date field to indicate when the record is no longer effective.

     6. This field helps in determining the duration a particular record was active and is used in conjunction with the start date for time-based queries.

     7. Active Flag:

     8. Define an active flag field to indicate the current status of the record.

2. Accessing Row History

• Select a Record: Choose the record you are interested in. To view the history of that Row

• View History: Once you select the record, you will be able to view its history. This history includes all the changes that have been made to the record over time.

• Most Recent First: The changes are displayed in order, with the most recent changes at the top. This way, you can quickly see the latest updates first.

At a time, you will only be able to view the history of a single row, not more than two rows can be displayed simultaneously.

The history of the particular record.