About Slowly Changing Dimensions
Slowly Changing Dimensions Defined
Slowly changing dimensions (SCD) is the name of a process that loads data into dimension tables. This data changes slowly, rather than changing on a time-based, regular schedule.
The dimension tables are structured so that they retain a history of changes to their
data. This record of data changes provides a basis for analysis.
As shown in the following diagram, dimension tables combine with fact tables to form
star schemas. Fact tables store numeric events.
Dimension tables store the detail data that describes the events. Key columns in the
tables connect events to details. For example, a star schema might store product sales numbers in a fact table, and use dimension tables to store information about customers, suppliers, and retail
locations.
You can use SAS Data Integration Studio to load data into star schemas and analyze
data to extract knowledge from the star schema.
The Star Schema and SAS Data Integration Studio
In SAS Data Integration Studio, the process of loading dimension tables takes place
in the SCD Type 1 Loader and SCD Type 2 Loader transformations.
Fact tables are loaded with the Lookup transformation.
Types of Slowly Changing Dimensions
The three most common types of slowly changing dimensions are defined as follows:
Type 1 SCD: no history of data changes
overwrites specified columns in dimension tables without retaining a history of changes.
Type 1 SCD is useful for maintaining
less-significant columns that are not used in historical analysis. In SAS Data Integration
Studio, the SCD Type 1 Loader transformation performs Type 1 updates. You can use
the SCD Type 2 Loader transformation to combine Type 1 and Type 2 updates in a single
operation.
Type 2 SCD: full history of data changes
maintains multiple records for each business key in the dimension table. The latest entry is the current entry for that business key. Other rows comprise
the historical record of data changes. New entries create new current rows. This comprehensive
record of data changes is the primary purpose of the SCD Type 2 Loader transformation.
Type 3 SCD: limited history of data changes
maintains a limited history of changes using multiple columns for selected variables.
For example, a Type 3 dimension table containing customer information has columns
named New Postal Code, Old Postal Code,
and Oldest Postal Code. Data is moved from column to column during the loading process.
Type 3 SCD has less analytical value than Type 2 SCD.
Transformations That Support Slowly Changing Dimensions
SAS Data Integration Studio provides the following transformations that you can
use to implement slowly changing dimensions:
SCD Type 1 Loader
inserts new rows, updates existing rows, and generates surrogate key values in a dimension table without maintaining a history of data changes. Each business
key is represented by a single row in the dimension table.
SCD Type 2 Loader
inserts new and Type 2 rows, updates existing rows, and generates surrogate key values
in a dimension table. At the same, it maintains a full history of data changes. Each
business key is represented by a current row and 0 through n number of closed out
rows. The closed
out rows enable change analysis over time.
Compare Tables
detects differences between matching rows in specified columns in two tables. Outputs
include changed, new, unchanged, and missing records tables. These outputs can be
used as the basis for performing Type 1 or Type 2 updates in a dimension table.
Lookup
loads source data into fact tables and loads foreign keys from dimension tables, with configurable
exception handling. The lookup process accesses dimension
tables by using hash objects for optimal performance.
Key Effective Date
updates dimension tables based on changes to the business key, when change detection
is unnecessary.
Surrogate Key Generator
generates unique key numbers for dimension tables in a manner that is similar but less feature-rich than
the SCD Type 2 Loader
transformation. Use the Surrogate Key Generator when key generation is the sole task
that is required at that point in the job.
SCD Project Stages
The process for loading a star schema for slowly changing dimensions follows these
general steps:
-
Stage operational data. In this initial step you capture data and validate the quality of that data. Your staging jobs make use of the Data Validation transformation, along with other data quality transformations and processes.
-
Load dimension tables. Data from the staging area is moved into the dimension tables of the star schema. Dimension tables are loaded before the fact table in order to generate the primary key values that are needed in the fact table.
-
Load the fact table. In this final step, you run a job that includes the Lookup transformation. This job loads numerical columns from the staging area into the fact table. Then the Lookup transformation captures foreign key values from the dimension tables.
Copyright © SAS Institute Inc. All Rights Reserved.
Last updated: January 16, 2018