Create slowly changing dimensions

Use Type 2 and Type 3 slowly changing dimensions (SCDs) to track changes to dimension data. In this article, you learn how to configure each SCD type and understand how they preserve historical and current attribute values. To know more about SCDs, see slowly changing dimensions.

Prerequisites#

The source data used to populate a type 2 slowly changing dimension must have the following columns:

• A natural key such as region ID, customer ID, or product ID.
• A unique sequence number that will be used as the surrogate key.
• Two date columns that capture the start and end dates.
• A boolean column that indicates whether a record is active.

The source data used to populate a Type 3 SCD must include a corresponding history column for each column that maintains historical values.

Configure type 2 SCD#

Slowly changing dimensions are configured during table creation. In this example, you create an SCD table using data imported from an Excel file.

1. In the table configuration, turn on Enable support for Slowly Changing Dimensions (SCD) and select Type-2.

2. Map the source columns to the surrogate key, natural key, active flag, and date range fields. Use End Date Default Value to define the default end date for active records. Choose either a null value or an infinite date.

3. In SCD Columns, select the columns whose changes you want to track in the slowly changing dimension and select Finish.

The table is created.

View change history

Slowly changing dimensions capture the changes made to each record.

1. Edit a cell value and select Save to Database to commit the change.

2. The original record is marked as inactive and excluded from the grid. A new record containing the updated values is inserted at the end of the grid. To view the change history for a record, select the record and then select History.

3. In the History pane, records with the Active flag enabled are the current active records. Their End Date is displayed as either null or an infinite date, based on the SCD configuration. After reviewing the change history, select the back icon to return to the grid.

Configure type 3 SCD#

You can configure Type 3 SCDs when creating a table from a file or semantic model or by manually entering data.

1. In the table configuration, turn on Enable support for Slowly Changing Dimensions (SCD) and select Type-2.

2. Select the current value column and its corresponding history column to track both the latest and previous values. Select + Add More to maintain historical data for more than one column.

3. Select Finish to create the table.

4. When you edit a value, the older value is moved to the configured history column. Select Preview Changes.

5. Verify the changes made.

6. Select Added, Updated, or Deleted to preview records with specific changes. Select Save to Database to commit the changes.