SSIS: Three ways to implement slowly changing dimension slowly changing dimensions in the Data Warehouse

On the theoretical concept of slowly changing Dimension slowly changing dimension see Data Warehouse Series-Slow slowly changing dimension (slowly changing Dimension) common three types and prototype design

This article summarizes several ways to realize the slow gradual change dimension, and analyzes the logical process of changing attribute and historical attribute output.

Example one: Using the slowly changing Dimension control in SSIS

Example two: Implementing a simple SCD effect with SQL Merge statements

Example three: Using Lookup in SSIS, Conditional Split, multicast, and other controls to achieve SCD effects

Test table and test data, where customer is the data source table, DimCustomer simulates the Customer dimension table in the Data Warehouse.

Each example starts with an empty table, Dimension the table without data for the first run, adds several data to the Customer Data source table before the second run, and modifies several data at the same time.

Note, however, that this example loads all of the data source data, regardless of the incremental load based on the data source data, and the implementation of the incremental load is explained in other articles in the BI series.

Use biwork_ssis
go
    
if object_id (' Customer ') is not NULL
DROP TABLE Customer 
go
    
if object_id (' DimCustomer ') is not a NULL
DROP TABLE dimcustomer go
    
CREATE table Customer
(
    ID INT PRIMARY KEY IDENT ity (1,1),
    FullName NVARCHAR, City NVARCHAR (m),
    occupation NVARCHAR (m)
    
CREATE TABLE DimCustomer 
(
    CustomerID int PRIMARY KEY IDENTITY (1,1),
    customeralternatekey int,
    FullName NVARCHAR (m), City
    NVARCHAR,
    occupation NVARCHAR,
    startdate datetime,
    enddate datetime ,
    iscurrent BIT DEFAULT (1)
)
    
inserts into BIWORK_SSIS.dbo.Customer VALUES
(' biwork ', ' Beijing ', ' IT '),
(' Zhangsan ', ' Shanghai ', ' education '),
(' Lisi ', ' Guangzhou ', ' Student ')

Example a slowly changing Dimension in SSIS

Create a new Package and drag and drop a data flow to establish a connection with the Customer table's datasource in the flow, creating a new slowly changing Dimension scd_dimcustomer.