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

Source: Internet
Author: User
Tags datetime ssis

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.