SCD缓慢变化维拉链表SQL实现

1 缓慢变化维概述

SCD英文Slow Changing Dimensions(缓慢变化维)，它是数据仓库建模过程中一个非常重要的概念。众所周知数据仓库是基于历史数据的，而历史数据的变化依赖于维度的定义，缓慢变化维就是用来跟踪和表现维度表变化的一种方法。事实表往往在跟维度表关联时需要“卡”住相应的时间节点，这就是SCD记录历史变化的作用。

注: 1 当前代码演示环境是SQL Server，基于Merge语法，其它数据库类似。

      2 ​​SCD缓慢变换维Kettle实现​​

      3 ​​Kettle转换作业应用50个案例脚本​​ 

数据仓库里维度表的字段改变（变化）常见的有3种，分别记作Type1、Type2、Type3。这里假如我们有用户维度表customer客户(cust_id用户编号、name姓名、jobtitle职位)，如果在某个时间将某个用户的职位由Dev更新成CTO，我们看下三种维度处理的方法的情况：

  • Type1(不会记录维度里关键字段值的变化历史情况)：

cust_idnamejobtitle1张三Dev

新的customer表为:

cust_idnamejobtitle1张三CTO

  • Type2(记录维度里关键字段每次的变化情况)：

针对1的情况假设更新时间是2020-10-21，那么对应到客户维度表里(这里end_date对应最新时一般设置为9999-12-31或者NULL)，则有如下的轨迹:

cust_idnamejobtitlestart_dateend_dateis_current1张三Dev2020-10-102020-10-2101张三CTO2020-10-21NULL1

  • Type3(直接记录当前最新的值和上一次变化前的值)

cust_idnamejobtitlepre_jobtitle1张三CTODev

综上所述不难发现type1和type3不能很好的记录维度的每次变化情况，type1没有，type3只能记录最新的一次变化，而type2会记录每次变化(关注的字段需自定义)的情况。

注:一般来说事实表会存放维度表的ID，而事实表抽取（ETL）的过程中是通过维度表的start_date和end_date来确定事实表里度量(指标)的时间范围(即维度表的对应时间)。

2 代码与注释

2.1 表结构与数据

-- step1 准备表和数据，当前运行在SQL Server里。

-- 业务系统(OLTP)的客户表
CREATE TABLE Customer(
       ID int IDENTITY(1,1) NOT NULL,
       FullName nvarchar(50) NULL,
       City nvarchar(50) NULL,
       Occupation nvarchar(50) NULL)

      
-- 数据仓库的(OLAP)的客户维度表
CREATE TABLE DimCustomer(
              CustomerID int IDENTITY(1,1) NOT NULL,
              CustomerAlternateKey int NULL,
              FullName nvarchar(50) NULL,
              City nvarchar(50) NULL,
              Occupation nvarchar(50) NULL,
              StartDate datetime NULL,
              EndDate datetime NULL,
              IsCurrent bit NULL,
       PRIMARY KEY CLUSTERED
       (
              CustomerID ASC
       )
)

GO

ALTER TABLE DimCustomer ADD  DEFAULT ((1)) FOR IsCurrent
INSERT INTO Customer(FullName,City,Occupation)
SELECT 'BIWORK','Beijing','CEO' UNION ALL
SELECT 'ZhangSan','Shanghai','Education' UNION ALL
SELECT 'Lisi','Guangzhou','IT' UNION ALL
SELECT 'Wangwu','Beijing','Finance'

2.2 缓慢变换维代码

-- step2 SCD 模块
-- 1 修改状态
MERGE INTO dbo.DimCustomer AS Dim
USING dbo.Customer AS Src
    ON Dim.CustomerAlternateKey = Src.ID
WHEN NOT MATCHED BY TARGET
    THEN INSERT VALUES(Src.ID,Src.FullName,Src.City,Src.Occupation,GETDATE(),NULL,1)
WHEN MATCHED AND (Dim.City <> Src.City OR Dim.Occupation <>  Src.Occupation) AND Dim.IsCurrent=1

    THEN UPDATE SET Dim.EndDate =CASE WHEN Dim.EndDate IS NULL THEN GETDATE() ELSE Dim.EndDate END,Dim.IsCurrent = 0;

-- 2 修改数据
MERGE INTO dbo.DimCustomer AS Dim
USING dbo.Customer AS Src
    ON Dim.CustomerAlternateKey = Src.ID
       AND Dim.City = Src.City AND Dim.Occupation = Src.Occupation
       WHEN NOT MATCHED BY TARGET
       THEN INSERT VALUES(Src.ID,Src.FullName,Src.City,Src.Occupation,getDATE(),NULL,1);

2.3  修改数据验证

-- Step3 验证
-- 新插入一条
INSERT INTO Customer(FullName,City,Occupation) VALUES
('qinliu','Beijing','Finance')


-- Case1: 执行如下更新后执行SCD模块,这里的ID依赖于自增序列生成的序号

UPDATE Customer
SET Occupation = 'IT'
WHERE ID = 6


-- Case2: 执行如下更新后执行SCD模块,这里的ID依赖于自增序列生成的序号
UPDATE Customer
SET Occupation = 'Publisher',
    City = 'Hangzhou'
WHERE ID = 6

-- 每次修改后对照查看DimCustomer表的变化，查看是否追踪到数据的历史变更信息。以下为查询示例：
SELECT * 
FROm DimCustomer
WHERE FullName='qinliu'

SCD缓慢变化维拉链表_sql