SCD Type2 in ODI

缓慢变化维（Slowly changing Dimensions）指的是维表中的维度字段值会随着时间或业务调整，而在后续的分析中，历史数据仍然要使用旧的维度值，新的数据会使用当前维度值。在数据仓库建设过程中，ODI 提供有专门的SCD 集成模块，更方便的对维表进行更新。在ODI 12c中实现缓慢变化维SCD2的例子如下。

一、创建测试表格

创建源表，并插入数据

 1 DROP TABLE EMP_SCD2_SRC;
 2 CREATE TABLE EMP_SCD2_SRC(
 3 EMPNO NUMBER (4) CONSTRAINT PK_EMP PRIMARY KEY,
 4 ENAME VARCHAR2 (10),
 5 JOB VARCHAR2 (9),
 6 MGR NUMBER (4),
 7 HIREDATE DATE,
 8 SAL NUMBER (7, 2),
 9 COMM NUMBER (7, 2),
10 DEPTNO NUMBER (2)
11 );

View Code

插入测试数据

 1 INSERT INTO EMP_SCD2_SRC VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
 2 INSERT INTO EMP_SCD2_SRC VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
 3 INSERT INTO EMP_SCD2_SRC VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
 4 INSERT INTO EMP_SCD2_SRC VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
 5 INSERT INTO EMP_SCD2_SRC VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
 6 INSERT INTO EMP_SCD2_SRC VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
 7 INSERT INTO EMP_SCD2_SRC VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
 8 INSERT INTO EMP_SCD2_SRC VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
 9 INSERT INTO EMP_SCD2_SRC VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
10 INSERT INTO EMP_SCD2_SRC VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
11 INSERT INTO EMP_SCD2_SRC VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
12 INSERT INTO EMP_SCD2_SRC VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
13 INSERT INTO EMP_SCD2_SRC VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
14 INSERT INTO EMP_SCD2_SRC VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

View Code

创建目标表

 1 CREATE TABLE EMP_SCD2_TAG
 2 (
 3 EMPNO NUMBER(4),
 4 ENAME VARCHAR2(10 BYTE),
 5 JOB VARCHAR2(9 BYTE),
 6 MGR NUMBER(4),
 7 HIREDATE DATE,
 8 SAL NUMBER(7,2),
 9 COMM NUMBER(7,2),
10 DEPTNO NUMBER(2),
11 START_DATE DATE,
12 END_DATE DATE,
13 CURRENT_FLAG NUMBER
14 )

View Code

二、逆向工程、创建接口

模型上新建关键字PK_EMP，否则会报错：未指定关键字，无法进行流控制

新建接口SCD2,拖拽两个数据存储，自动映射如下

START_DATE= sysdate

END_DATE=to_date('29990101', 'YYYYMMDD')

CURRENT_FLAG= 0 (0:old record, 1: Latest record)

指定LKM

三、渐变维度配置

修改目标表为渐变维度

执行目标表属性的SCD行为：

自然关键字、代理关键字、更改时覆盖、更改时添加、开始时间戳、结束时间戳、当前记录标记

指定目标表的集成知识模块

四、测试

第一次运行接口，目标表中数据

4.1、更新时添加字段的测试

更新源表记录的SAL字段(更新时添加行)

UPDATE "ODI_SRC"."EMP_SCD2_SRC" SET SAL=3000 WHERE EMPNO='7369';

重新运行接口，发现EMPNO=7369的多了一行

4.2 更新时覆盖字段的测试

更新MGR字段(更新时覆盖)

UPDATE "ODI_SRC"."EMP_SCD2_SRC" SET MGR=7566 WHERE EMPNO='7369';

不会新增记录

参考

http://dwteam.in/scd-type2-in-odi/