使用BO DI处理 SCD2维表

By zw1840 on November 21, 2008 11:25 PM | No Comments | No TrackBacks

摘要：本文首先介绍了缓慢变化维的定义、三种处理方式。之后以员工维表数据为例，详细说明如何使用BO DI实现SCD2维表的ETL。最后讲解了SCD2维表如何使用。

 

1 缓慢变化维简介

某些维度的属性会随着时间缓慢变化，这种维度被称为缓慢变化维（Slowly Changing Dimension，SCD）。举个例子，跨国企业的员工可能会在不同区域间调动工作，但两次调动之间的时间间隔不会太短，因此员工的所属区域属性在缓慢变化，员工维是一个缓慢变化维。

 

在数据仓库中缓慢变化维如何建模，如何ETL呢？下面就以员工维表为例介绍三种常用的SCD处理方式。

1.1 SCD1：更新旧数据

SCD1 方式直接修改维度记录的属性值，维度属性表示员工当前状态。

 

假设企业内每个员工都隶属于某个地区，张三原属于亚洲地区。员工维表最初存储的数据如下表：

 

EMP_ID / 代理键

EMP_CODE / 员工代码

EMP_NAME / 员工姓名

REGION / 所属地区

1

A001

张三

Asia

 

    其中EMP_ID 是员工维表的主键，用于和事实表关联；EMP_CODE是员工代码，唯一地标识每位员工；REGION即员工所属地区。

 

现在因业务需要，张三被派驻到非洲地区了。在SCD1方式中，将张三的地区属性直接更新为"Africa"，其余字段不变，事实表所引用的EMP_ID也无需变化。下表为员工维表修改后的情况，只有张三的所属地区属性被更新，维度记录表示张三当前的状态。

 

EMP_ID / 代理键

EMP_CODE / 员工代码

EMP_NAME / 员工姓名

REGION / 所属地区

1

A001

张三

Africa

 

SCD1是处理缓慢变化维最简单的方法，只有维表的属性字段需要修改，事实表数据不受影响。SCD1的缺点在于丢失了维度属性的历史数据；此外与变化属性有关的汇总数据必须重新计算，否则汇总与明细数据将无法对应。

 

维度属性的缓慢变化是否需要在数据仓库中体现是根据分析需求而定的。这类需求在调研阶段很容易被业务用户忽略，因此建模人员应检查每个维度属性是否有变化的可能，以及是否有分析历史信息的需要。不要想当然地把SCD1作为默认的维度处理方法。

1.2 SCD2：增加新记录

    SCD2方式使用新记录存储更新的维度信息，能够保存维度历史信息，因此是最主要的缓慢变化维处理方式。

 

我们继续使用在区域间调动工作的例子。张三于2007年5月从亚洲调动到非洲后，使用SCD2方式存储的维表如下所示：

 

EMP_ID / 代理键

EMP_CODE / 员工代码

EMP_NAME / 员工姓名

REGION / 所属地区

1

A001

张三

Asia

2

A001

张三

Africa

 

张三原有记录不变，增加一条新记录作为张三最新的信息。新记录的所属地区字段值为"Africa"，新记录的EMP_ID字段值是自动生成的代理键。事实表中张三2007年5月之前的事实数据使用旧EMP_ID（1），2007年5月之后的事实数据使用新记录的EMP_ID（2）。

 

SCD2方式实际上存储了同一员工在不同时间的多个版本的信息，自动生成的EMP_ID可以唯一地标识不同版本，而逻辑上唯一的EMP_CODE显示了同一员工不同版本信息间的联系。

 

需要注意的是，SCD2维表与事实表关联时无需附加任何日期时间条件，因为在抽取事实数据时就已经选择了与事实数据时间相匹配的维度EMP_ID。在SCD2维表中可以加入"信息生效时间戳"和"信息失效时间戳"两个字段，通过这两个字段就可以还原出任意时间点员工信息的快照。在SCD2维表中还可以加入"当前有效信息标志"，便于查询出最新版本的维度信息。

1.3 SCD3：增加新数据列

SCD3方式使用新数据列存储更新的维度信息，能够保存有限的维度历史信息。

 

    仍旧用张三调动所属区域的例子，使用SCD3方式存储的维表如下所示：

 

EMP_ID / 代理键

EMP_CODE / 员工代码

EMP_NAME / 员工姓名

REGION / 所属地区

REGION_PRIOR / 之前的所属地区

1

A001

张三

Africa

Asia

 

    员工维表增加了一列REGION_PRIOR，此列存储张三的旧所属地区属性"Asia"，而REGION字段则存储新所属地区属性"Africa"。

 

    有些分析要求同时按维度的新旧属性查看事实数据。比较常见的例子是销售区域划分发生变化后，分析人员希望既能按划分后的区域查看事实数据，也能按划分前的区域查看。只有SCD3能满足此类需求。

1.4 小结

以上是对三种SCD方式的介绍。这三种方式的出场顺序没有任何含义，不代表谁好谁差。三种方式各有优劣，开发者应该根据数据的特点、分析的要求来选择。

 

以上述三种SCD方式为基础，还可以形成多种混合SCD方法，使维度数据满足更特殊的分析要求。本文不详细介绍了，深入了解可以参考《Data warehouse toolkit》，p102-105，Hybrid Slowly Changing Dimension Techniques。

2 用Business Objects Data Integrator 实现SCD2维表

    本节用Business Objects Data Integrator（BO DI）实现一个完整的SCD2维表抽取示例。这里继续使用前面提到的员工在区域间调动的例子，演示SCD2如何ETL员工维表的变化数据，以及在员工维表中如何记录某一时间版本的员工信息的生效时间戳、失效时间戳、当前版本标志。

2.1数据源表、目标表

首先需要建立示例的源表和目标表。员工数据源表为SCD2_DS_EMP，员工属性发生变化时，其中的数据会被更新，员工属性历史信息在此表中不存储；数据仓库员工维表为SCD2_DT_EMP，目标表的结构满足SCD2的要求，可以存储员工维的历史信息。建表语句及测试数据见附录A。

 

员工数据源表 SCD2_DS_EMP的字段如下：

 

字段名

描述

EMP_ID

员工记录自增序列，主键

EMP_CODE

员工代码，唯一键

EMP_NAME

员工姓名

REGION

员工所属地区

BIRTHDAY

员工生日

UPD_TIMESTAMP

员工信息最后更新时间戳，EMP_NAME、REGION、或BIRTHDAY字段更新时，此字段记录更新时间

 

员工数据源表 SCD2_DS_EMP的初始数据如下：

 

EMP_ID

EMP_CODE

EMP_NAME

REGION

BIRTHDAY

UPD_TIMESTAMP

1

A001

张三

Asia

1980-08-02

2005-01-01 12:30:43

2

A002

李四

Asia

1948-02-20

1998-04-12 09:30:32

3

A003

王五

Asia

1951-01-19

2000-09-21 10:44:43

 

数据仓库员工维表为SCD2_DT_EMP的字段如下：

 

字段名

描述

EMP_SID

代理主键，ETL维表时自动生成

EMP_CODE

员工代码，唯一键

EMP_NAME

员工姓名

REGION

员工所属地区

BIRTHDAY

员工生日

DATE_EFFECT

信息生效时间戳

DATE_EXPIRE

信息失效时间戳

FLAG_CURRENT

当前有效信息标志

 

数据仓库员工维表SCD2_DT_EMP 初始无数据。

2.2定义Job

定义Job命名为JOB_SCD2_EMP，在其中调用下面定义的Data Flow。在Job中还要定义一个定义全局变量 $GV_ETL_MODE，此变量表示ETL的模式：初始（INI）或增量（INC）。此变量在Data Flow的Query对象中引用，用于判断DATE_EFFECT字段的值。Job和全局变量如下：

 

 

设置Job的属性，令$GV_ETL_MODE= 'INC',使Job默认抽取模式为增量。

 

2.3定义Data Flow

    本节定义的DI对象的导出文件参见附录B。

2.3.1 DI中的Operation Code

在定义Data Flow之前，先要讲一下操作符（Operation Code）的概念。在DI的Data Flow中，对象的输入、输出数据集的每条记录都会附加一个操作符。操作符表示将记录加载到目标对象时DI执行何种操作。

 

DI中共定义了四种操作符，操作符的种类和描述如下：

 

操作符

描述

NORMAL

在目标对象创建新数据行。

当数据从原对象抽取出来时，记录的操作符均为NORMAL；当操作符为NORMAL的记录被加载到目标对象时，此记录作为新数据行插入目标对象；DI中大多数Transform的输出都被标志为NORMAL。

INSERT

在目标对象创建新数据行。

只有Table_Comparison和Map_Operation能够输出带有INSERT操作符的数据；只有History_Preserving和Key_Generation能够接收带有INSERT操作符的数据；当操作符为INSERT的记录被加载到目标对象时，此记录作为新数据行插入目标对象；

DELETE

被目标对象忽略。

操作符为DELETE的记录不会加载到目标对象。只有Table_Comparison和Map_Operation能够输出带有DELETE操作符的数据；只有History_Preserving 能够接收带有DELETE操作符的数据；

UPDATE

覆盖目标对象中已有的数据行。

只有Table_Comparison和Map_Operation能够输出带有UPDATE操作符的数据；只有History_Preserving和Key_Generation能够接收带有UPDATE操作符的数据；

2.3.2 创建Data Flow

创建一个Data Flow，命名为DF_SCD2_EMP，此Data Flow的作用是实现SCD2方式抽取员工维表。将DF_SCD2_EMP添加到JOB_SCD2_EMP中。DF_SCD2_EMP能够将数据源表SCD2_DS_EMP中的员工信息ETL到数据仓库员工维表SCD2_DT_EMP中，当SCD2_DS_EMP中员工属性发生变化时，SCD2_DT_EMP能够保留历史信息，并使用新数据行记录新信息。

 

DF_SCD2_EMP中包含6个对象。首先是源对象SCD2_DS_EMP和目标对象SCD2_DT_EMP。在源和目标之间依次经过四个Transform，分别为：Query、Table_Comparison、History_Preserving、以及Key_Generation。员工维表的SCD2 ETL就是通过这四个Transform实现的，下面分别介绍她们的做用及使用方法。DF_SCD2_EMP中的对象如下图所示：

 

2.2.3 Query

Query很好理解，可以把她看作一个SQL Select语句。

 

在Query中自定义一个输出列, 命名为 DATE_EFFECT, Mapping的表达式如下：

 

ifthenelse(

  $GV_ETL_MODE = 'INI',

  to_date('19000101','yyyymmdd'),

  to_date(to_char(SCD2_DS_EMP.UPD_TIMESTAMP,'yyyymmdd'),'yyyymmdd')

)

 

DATE_EFFECT 是目标表SCD2_DT_EMP的一个字段，表示某一时间版本员工信息的生效时间戳。在这个例子中，数据源表SCD2_DS_EMP中不记录历史信息，员工属性信息发生变化时直接更新员工记录对应的字段，同时在SCD2_DS_EMP.UPD_TIMESTAMP中记录更新操作时间戳。在示例中，张三的记录的UPD_TIMESTAMP值为2005-01-01 12:30:43，此时间戳既有可能是张三记录的创建时间，也有可能是张三记录的最后一次修改时间。总之此时间戳之前张三的员工信息变化情况已经无法准确获得了，我们只能假设自原系统上线至数据仓据初次抽取之时，张三的信息均为当前数据源表所记录的信息。因此DATE_EFFECT的取值分为两种情况：

Ÿ       员工维表首次ETL时（$GV_ETL_MODE = 'INI'）,统一取一个一定小于任何业务数据发生日期的日期，这里取1900-01-01

Ÿ       员工维表增量ETL时（$GV_ETL_MODE = 'INC'）,取SCD2_DS_EMP.UPD_TIMESTAMP，这个时间戳将作为新版员工信息的生效时间戳，也作为旧版员工信息的失效时间戳。

DATE_EFFECT 字段在后面的History_Preserving中还会用到。

 

    在Query中，SCD2_DS_EMP.EMP_ID字段没有被包含进查询。这个字段是原系统中通过序列自动生成的主键，对业务分析无任何意义。而员工代码SCD2_DS_EMP.EMP_CODE与SCD2_DS_EMP.EMP_ID一样能够唯一地代表一个员工。在数据仓库员工问表SCD2_DT_EMP中，我们将使用通过序列自动生成SCD2_DT_EMP.EMP_SID作为主键，唯一地确定某一员工的某一时间版本的信息记录；同一员工的各个时间版本的信息记录可以通过SCD2_DT_EMP.EMP_CODE字段建立起联系。

2.2.4 Table Comparison

Table Comparison有两个输入：首先是来自源对象Data Flow中其他Transform的输入数据集(Input data set) ，Table Comparison 只接受其中操作符（Operation Code）为NORMAL的记录，其余记录忽略；其次是在Table Comparison中设置的比较表（Comparison Table）。Table Comparison的作用是比较输入数据集和比较表的记录，输出有差异的数据行，输出记录的操作符可能为 INSERT, UPDATE, 或 DELETE。

 

Table Comparison的实质是比较同一个数据集在不同时间点上的两个映像。在示例中，输入数据集为数据源表SCD2_DS_EMP，比较表为数据仓库员工维表SCD2_DT_EMP，这两个表存储的是代表同一业务含义的数据集，但两个数据集所处的时间点不同，一般来说SCD2_DS_EMP比SCD2_DT_EMP的信息更接近当前实际状况。Table Comparison能够挑选出SCD2_DS_EMP中新增、更新、或删除的数据行，分别标记操作符为INSERT、UPDATE、UPDATE后输出。

 

下图为Table Comparison的设置，我会结合实例说明重要选项的含义。

 

 

Ÿ       Schema In

即输入数据集，通常为最新状态的数据集。我把Query和Table Comparison相连，使用基于SCD2_DS_EMP的查询做为Table Comparison的输入。

Ÿ       Table name

即比较表，通常为时间上落后于当前状态的数据集。在这里比较表设为SCD2_DT_EMP。

Ÿ       Input primary key columns

用于唯一确定输入数据集中一条记录的键，从 Schema In 中拖入。Table Comparison使用此字段值过滤比较表的同名字段，寻找是否有匹配的记录。在SCD2_DS_EMP中，EMP_CODE是唯一键，能够唯一地标识一个员工，因此设为比较键。

Ÿ       Generated Key

如果输入数据集中的一个键值在比较表中可以匹配出多条记录，必须设置比较表的某个字段为Generated Key，此字段应能唯一地区分每一条记录。在这个例子中，输入数据集的键为 EMP_CODE, 而在 SCD2_DT_EMP 中同一 EMP_CODE 可能有多条记录（代表不同的时间版本）, 因此需要设置SCD2_DT_EMP.EMP_SID为Generated Key。

Ÿ       Detect Deleted row(s) from comparison table

如果比较表中存在输入数据集中不存在的键值，则Table Comparison认为输入数据集中对应的记录已经被删除了。此种情况下，Table Comparison将比较表记录标志为DELETE，如果比较表内有失效时间戳、或当前有效信息标志等字段时，将数据加载到比较表时可以更新这些字段以反映记录的删除状态。

Ÿ       Compare columns

可从 Schema In 中拖入字段，Table Comparison在比较两个输入时只判断选定的列是否被更新。如果不设置此选项，则输入数据集和比较表的同名字段都会被比较。在例子中我选择比较三个字段EMP_NAME、REGION、和BIRTHDAY。

 

Table Comparison还有两个用于调优的选项，在这简单介绍一下：

Ÿ       Comparison Method

决定 DI 采用何种方式执行比较，应根据输入数据集和比较表的数据情况选择。参见 Data Integrator Technical Manuals，p1160，Reference Guide。

Ÿ       Run as separate process

运行时为 Table Comparison 创建一个单独的数据流，目的是实现分布式处理，提高性能。参见Data Integrator Technical Manuals，p409，Designer Guide；Data Integrator Technical Manuals，p1859，Performance Optimization Guide。

 

    讲完了Table Comparison的设置，再来看一下Table Comparison是如何实现比较两个输入，如何生成输出数据集的。

 

Table Comparison 的输出结构（Schema Out）与比较表相同。输出记录的 Operation Code 可能为 INSERT, UPDATE, 或 DELETE。其中 DELETE 只有在选择了 Detect Deleted row(s) from comparison table 才会出现。

 

对于输入数据集的每条记录，有三种可能的情况

    1、输入数据集的 Input primary key columns 在比较表没有找到匹配的记录，Table Comparison 使用输入数据集的字段值生成一条 Operation Code 为 INSERT 的输出，如果输入数据集的字段少于比较表，则字段值取 NULL。

    2、输入数据集的 Input primary key columns 在比较表找到一条或多条匹配的记录，且比较列发生变化，Table Comparison 使用输入数据集的字段值生成一条 Operation Code 为 UPDATE 的输出。如果输入数据集的字段少于比较表，则字段值取比较表的字段值。需要注意的是，比较表内与 Input primary key columns 匹配的记录可能是多条，如果设定了 Generated Key，则 Table Comparison 取比较表中 Generated Key 值最大的记录进行比较，否则随机取一条。根据 SCD2 方法的规则，Generated Key 值最大的记录就是反映员工最新状态的记录。

    3、输入数据集的 Input primary key columns 在比较表找到一条或多条匹配的记录，但比较列没有发生变化，则跳过这条 Input 记录。

 

最后，如果选择了 Detect Deleted row(s) from comparison table 选项后, 且比较表中的键值在输入数据集中不存在，Table Comparison 使用比较表记录的字段值生成一条操作符为 DELETE 的输出。

2.2.5 History Preserving

History Preserving是专为实现SCD2维表抽取而设的，她的作用是将数据源中的更新操作转化为目标表中的一条新数据。此外History Preserving还可以管理缓慢变化数据的信息生效时间戳、信息失效时间戳，以及当前有效信息标志三个字段。

 

History Preserving只能接收 Table Comparison 的输出数据集作为输入数据集。前面提到过，Table Comparison 的输出数据集的记录可能有三种操作符：INSERT、UPDATE、或DELETE。History Preserving。虽然DI的文档没有提到，但我根据History Preserving的工作方式猜测History Preserving还需要读取之前Table Comparison 的设置，她需要从Table Comparison中读取比较表以及与比较相关的设置。

 

在History Preserving中可以设定需要保存历史信息的比较列。History Preserving根据用户的设定对输入数据集和比较表中的数据列进行比较，并依此决定输出数据集的操作符。History Preserving输出数据集的记录可能具有两种操作符：INSERT, UPDATE。对于History Preserving输入数据集的每行记录：

Ÿ       如输入记录的操作符为INSERT，输出记录的操作符不变。这种情况表示此记录被Table Comparison的标志为INSERT，是数据源中的出现的新数据，需要插入目标表。

Ÿ       如输入记录的操作符为UPDATE，且存在列值不同的比较列，输出记录的操作符变为INSERT。这种情况表示此记录被Table Comparison标志为UPDATE，属于数据源中已有数据发生了更新，且被更新的字段属于需要保留历史记录的字段，应作为最新时间版本的数据插入目标表。

Ÿ       如输入记录的操作符为UPDATE，但不存在列值不同的比较列，输出记录的操作符仍为 UPDATE。这种情况表示此记录被Table Comparison标志为UPDATE，属于数据源中已有数据发生了更新，但被更新的字段不属于需要保留历史记录的字段，因此只需要更新目标表中对应字段记录。

Ÿ       如输入记录的操作符为DELETE，输出记录的操作符变为UPDATE。只有在Table Comparison中设置了"Detect deleted row(s) from comparison table"且在History Preserving中设置了"Preserve delete row(s) as update row(s)"才会出现这种情况，此情况表示记录被Table Comparison标志为DELETE，即数据源中已经被删除的记录。如果目标表中设定了"信息失效时间戳"以及"当前有效信息标志"，这两个字段会被更新；否则不会对目标表产生影响。

 

下图是示例中History Preserving的设置：

 

 

Ÿ       Compare columns

需要保存历史信息的比较列。从Schema In中拖入，我在这选择员工的所有属性，包括：EMP_NAME，REGION，BIRTHDAY。

Ÿ       Data columns\Valid from

目标表中存储"信息生效时间戳"的字段。表示某一时间版本员工信息的生效时间，还被用来更新之前紧邻时间版本员工信息的失效时间。这里使用SCD2_DT_EMP.DATE_EFFECT字段。

Ÿ       Data columns\Valid to

目标表中存储"信息失效时间戳"的字段。表示某一时间版本员工信息的失效时间。这里使用SCD2_DT_EMP.DATE_EXPIRE字段。

Ÿ       Data columns\Valid to date value\New record

操作符为INSERT的记录的失效时间戳值（日期型）。对于一个INSERT记录来说，失效时间戳是未知的，按理说应写成NULL。但将此值设为一个无限大的时间能使对维表的查询书写更为简单，因此这个选项的默认值为"9000.12.31"。这个选项可以填入固定值，也可以使用变量。

Ÿ       Data columns\Valid to date value\Old record

    之前紧邻时间版维度记录的"信息失效时间戳"的值。

Ÿ       Current flag\Column

目标表中存储"当前有效信息标志"的字段。这里使用SCD2_DT_EMP.FLAG_CURRENT字段。

Ÿ       Current flag\Set value

    当前有效信息的标志值。此处设置为"YES"，用于标志此记录为员工最新时间版本的信息。

Ÿ       Current flag\Reset value

非当前有效信息的标志值。此处设置为"NO"，用于标志此记录不是员工最新时间版本的信息。

Ÿ       Preserve delete row(s) as update row(s)

设置了此选项后，History Preserving才会将输入数据集中操作符为DELETE的记录转换为UPDATE记录。如果目标表中设定了"信息失效时间戳"以及"当前有效信息标志"，向目标表加载数据时这两个字段会被更新，其中"信息失效时间戳"被设置为数据抽取的执行时间。

2.2.6 Key Generation

Key Generation 的功能是为操作符为INSERT的记录生成一个主键值。Table Comparison或History Preserving的输出可以作为Key Generation的输入，因此为这两个Transform的输出中可能包含操作符为INSERT的记录。

 

Key Generation中可以设置三个参数：

Ÿ       Table name

作为源表，用于确定当前最大键值。这里使用目标表SCD2_DT_EMP。

Ÿ       Generated Key

源表中的字段，用于确定当前最大键值。这里使用目标表主键SCD2_DT_EMP.EMP_SID。输入数据集中必须有与此字段同名的字段。

Ÿ       Increment value

键值的增量值。

 

下图是示例中Key Generation的设置：

 

 

    输入数据集全部被输出，记录的操作符不变。如记录的操作符为INSERT，则Generated Key字段将被赋予一个自增的键值。

3 ETL测试

3.1初始抽取

初次执行 JOB_SCD2_EMP，在执行属性对话框里令全局变量$GV_ETL_MODE = 'INI'，执行属性能够覆盖定义Job时设置的Job属性。

 

 

    目标表SCD2_DT_EMP增加了三条记录，对应数据源表的三条员工信息。由于是初始抽取，三条记录的DATE_EFFECT字段值均为1900-01-01。

3.2增量抽取

3.2.1源数据记录有Update操作

修改数据源表SCD2_DS_EMP张三的 Region 字段，再执行JOB_SCD2_EMP，参数 $GV_ETL_TYPE = 'INC'。执行后SCD2_DT_EMP中增加了一条张三的新记录，张三原有效记录（EMP_SID=1）的DATE_EXPIRE被更新为20060421，FLAG_CURRENT标志被更新为'NO'。

 

update SCD2_DS_EMP

set REGION = 'Africa', UPD_TIMESTAMP = to_date('20060421104325','yyyymmddhh24miss')

where EMP_CODE = 'A001';

commit;

 

再次修改数据源表SCD2_DS_EMP张三的 Region 字段，再执行JOB_SCD2_EMP，参数 $GV_ETL_TYPE = 'INC'。执行后SCD2_DT_EMP中又增加了一条张三的新记录，张三原有效记录（EMP_SID=4）的DATE_EXPIRE被更新为20070521，FLAG_CURRENT标志被更新为'NO'。

 

update SCD2_DS_EMP

set REGION = 'Europe', UPD_TIMESTAMP = to_date('20070521140417','yyyymmddhh24miss')

where EMP_CODE = 'A001';

commit;

 

修改数据源表SCD2_DS_EMP李四的 Region 字段，再执行JOB_SCD2_EMP，参数 $GV_ETL_TYPE = 'INC'。执行后SCD2_DT_EMP中增加了一条李四的新记录，李四原有效记录（EMP_SID=2）的DATE_EXPIRE被更新为20070608，FLAG_CURRENT标志被更新为'NO'。

 

update SCD2_DS_EMP

set REGION = 'America', UPD_TIMESTAMP = to_date('20070608113502','yyyymmddhh24miss')

where EMP_CODE = 'A002';

commit;

 

    上面三个例子演示了数据源表字段发生更新，且被更新字段包含于History Preserving的Compare columns列表里，Data Flow会将更新信息作为一条新记录插入到目标维表中。新记录的DATE_EFFECT字段取自SCD2_DS_TMP.UPD_TIMESTAMP，DATE_EXPIRE字段值为History Preserving中设定的Data columns\Valid to date value\New record，FLAG_CURRENT标志值为'YES'；原有效记录的DATE_EXPIR取自新记录的DATE_EFFECT，原有效记录的FLAG_CURRENT标志被更新为'NO'。

3.2.2源数据记录有Insert操作

在数据源表SCD2_DS_EMP中增加新员工赵六的数据，再执行JOB_SCD2_EMP，参数 $GV_ETL_TYPE = 'INC'。执行后SCD2_DT_EMP中增加了一条赵六的记录。

 

insert into SCD2_DS_EMP

values(4,'A004','赵六','Asia',to_date('19791011','yyyymmdd'),to_date('20070714102315','yyyymmddhh24miss'));

commit;

 

这个例子演示了数据源表增加了新记录的情况，数据源中的新记录会被Insert到目标表，目标表记录的DATE_EFFECT字段取自SCD2_DS_TMP.UPD_TIMESTAMP，DATE_EXPIRE字段值为History Preserving中设定的Data columns\Valid to date value\New record，FLAG_CURRENT标志值为'YES'。

3.2.3源数据记录有Delete操作

    从数据源表SCD2_DS_EMP中删除张三的数据，再执行JOB_SCD2_EMP，参数 $GV_ETL_TYPE = 'INC'。执行后SCD2_DT_EMP中张三当前有效数据（EMP_SID=5）的DATE_EXPIRE字段值被更新为执行日期，FLAG_CURRENT标志值被更新为'NO'。

 

delete from SCD2_DS_EMP where EMP_CODE = 'A001';

commit;

 

这个例子演示了数据源表删除了记录的情况。DI会找出数据源中的被删除记录在目标表的当前有效记录，将其 DATE_EXPIRE字段值更新为作业运行日期，FLAG_CURRENT标志值更新为'NO'。

 

我发现两个问题：一是DATE_EXPIRE字段的值为日期时间，无法只取日期。二是再次执行Job，张三当前有效数据（EMP_SID=5）的DATE_EXPIRE值又被更新为新的执行日期时间了。按理说DATE_EXPIRE的值反映的是数据源记录被删除的时间，记录了一次就不应该再变了。这两个问题我还没找到好的解决方法。

3.3 History Preserving的Compare columns 选项

修改History Preserving的Compare columns，去掉BIRTHDAY字段。这表示BIRTHDAY字段不必保留历史信息，向目标表加载数据是可以直接更新相应的字段。

 

    修改数据源表SCD2_DS_EMP李四的 Birthday字段，执行JOB_SCD2_EMP，参数 $GV_ETL_TYPE = 'INC'。

 

update SCD2_DS_EMP

set BIRTHDAY = to_date('19480228','yyyymmdd'), UPD_TIMESTAMP = to_date('20081121115532','yyyymmddhh24miss')

where EMP_CODE = 'A002';

commit;

 

执行后目标表中李四当前有效记录（EMP_SID=6）的Birthday字段被直接更新了，没有插入新记录。李四已经失效的记录（EMP_SID=2）不再更新。

4 SCD2 维表的使用

4.1 查询维度在某一时间点的情况

    查询2006-04-21时企业员工的状况

 

select *

from SCD2_DT_EMP

where

DATE_EFFECT <= to_date('20060421','yyyymmdd')

and DATE_EXPIRE > to_date('20060421','yyyymmdd')

 

查询条件为 DATE_EFFECT <= 时间点 and DATE_EXPIRE > 时间点。

4.2 维表与事实表关联

示例事实表SCD2_DT_SALES中只记录了张三的销售数据。查询张三所有销售数据的SQL如下：

 

select

  F1.DATE_SALES,D1.EMP_CODE,D1.EMP_NAME,D1.REGION,F1.SALES_AMOUNT

from

  SCD2_DT_SALES F1

  ,SCD2_DT_EMP D1

where

  F1.EMP_SID = D1.EMP_SID(+)

  and D1.EMP_NAME = '张三'

order by

  F1.DATE_SALES

 

    注意查询结果EMP_CODE和EMP_NAME分别为A001和张三，但REGION字段不同，说明每条事实记录关联的是适当时间版本的张三信息。

 

在SCD2维表与事实表关联时，对维表的DATE_EFFECT、DATE_EPIRE、FLAG_CURRENT这三个管理性的字段无需任何过滤条件。事实表ETL时，已经根据每笔销售业务的销售日期选择了维表中适当的员工记录。

4.3 事实表抽取时如何Lookup员工主键EMP_SID

如果员工维表初始抽取时DATE_EFFECT值设置为1900-01-01，或一个很小的日期（小于所有业务的发生日期），那么事实表初始抽取、增量抽取在Lookup员工主键EMP_SID时可以使用相同的方法。Lookup使用的SQL语句：

 

select EMP_SID

from SCD2_DT_EMP

where

  DATE_EFFECT <= to_date('20050101','yyyymmdd')

  and DATE_EXPIRE > to_date('20050101','yyyymmdd')

  and EMP_CODE = 'A001';

 

    其中 20050101 为业务发生日期，EMP_CODE为业务记录上的员工编号。

附录A Demo数据

以下SQL最好在Oracle中执行。

A.1 员工数据源表 SQL

drop table SCD2_DS_EMP cascade constraints;

create table SCD2_DS_EMP (

  EMP_ID number(20) not null,

  EMP_CODE varchar2(1024) not null,

  EMP_NAME varchar2(1024) not null,

  REGION varchar2(1024) not null,

  BIRTHDAY date not null,

  UPD_TIMESTAMP date

);

 

alter table SCD2_DS_EMP

add constraint PK_SCD2_DS_EMP primary key (EMP_ID);

 

alter table SCD2_DS_EMP

add constraint UK_SCD2_DS_EMP_01 unique (EMP_CODE);

 

comment on table SCD2_DS_EMP is 'SCD2 Demo 员工维表数据源表';

comment on column SCD2_DS_EMP.EMP_ID is '自增序列，主键';

comment on column SCD2_DS_EMP.EMP_CODE is '员工代码，唯一键';

comment on column SCD2_DS_EMP.EMP_NAME is '员工姓名';

comment on column SCD2_DS_EMP.REGION is '员工所属地区';

comment on column SCD2_DS_EMP.BIRTHDAY is '员工生日';

comment on column SCD2_DS_EMP.UPD_TIMESTAMP is '员工信息最后更新时间戳，EMP_NAME、REGION、或BIRTHDAY字段更新时，此字段记录更新时间';

A.2 数据仓库员工维表 SQL

drop table SCD2_DT_EMP cascade constraints;

create table SCD2_DT_EMP (

  EMP_SID number(20) not null,

  EMP_CODE varchar2(1024) not null,

  EMP_NAME varchar2(1024) not null,

  REGION varchar2(1024) not null,

  BIRTHDAY date not null,

  DATE_EFFECT date,

  DATE_EXPIRE date,

  FLAG_CURRENT varchar2(1024)

);

 

alter table SCD2_DT_EMP

add constraint PK_SCD2_DT_EMP primary key (EMP_SID);

 

comment on table SCD2_DT_EMP is 'SCD2 Demo 员工维表目标表';

comment on column SCD2_DT_EMP.EMP_SID is '代理主键，ETL维表时自动生成';

comment on column SCD2_DT_EMP.EMP_CODE is '员工编号，唯一键';

comment on column SCD2_DT_EMP.EMP_NAME is '员工姓名';

comment on column SCD2_DT_EMP.REGION is '员工所属地区';

comment on column SCD2_DT_EMP.BIRTHDAY is '员工生日';

comment on column SCD2_DT_EMP.DATE_EFFECT is '信息生效时间戳';

comment on column SCD2_DT_EMP.DATE_EXPIRE is '信息失效时间戳';

comment on column SCD2_DT_EMP.FLAG_CURRENT is '当前有效信息标志';

A.3 数据仓库销售事实表 SQL

drop table SCD2_DT_SALES cascade constraints;

create table SCD2_DT_SALES(

  DATE_SALES date,

  EMP_SID number(20),

  SALES_AMOUNT number(20,6)

);

 

comment on table SCD2_DT_SALES is 'SCD2 Demo 销售事实表';

comment on column SCD2_DT_SALES.DATE_SALES is '销售日期';

comment on column SCD2_DT_SALES.EMP_SID is '员工主键';

comment on column SCD2_DT_SALES.SALES_AMOUNT is '销售金额';

A.4 初始化数据

truncate table SCD2_DS_EMP;

insert into SCD2_DS_EMP values(

1,'A001','张三','Asia',to_date('19800802','yyyymmdd'),

to_date('20050101123043','yyyymmddhh24miss')

);

insert into SCD2_DS_EMP values(

2,'A002','李四','Asia',to_date('19480220','yyyymmdd'),

to_date('19980412093032','yyyymmddhh24miss')

);

insert into SCD2_DS_EMP values(

3,'A003','王五','Asia',to_date('19510119','yyyymmdd'),

to_date('20000921104443','yyyymmddhh24miss')

);

commit;

 

truncate table SCD2_DT_EMP;

 

truncate table SCD2_DT_SALES;

--业务发生日期早于第一次修改日期,EMP_SID值为1

insert into SCD2_DT_SALES values(

  to_date('20050101','yyyymmdd'),1,100

);

--业务发生日期等于于第一次修改日期,EMP_SID值为2

insert into SCD2_DT_SALES values(

  to_date('20060421','yyyymmdd'),4,200

);

--业务发生日期晚于第一次修改日期,且小于第二次修改日期，EMP_SID值为2

insert into SCD2_DT_SALES values(

  to_date('20070321','yyyymmdd'),4,300

);

--业务发生日期晚于第二次修改日期，EMP_SID值为5

insert into SCD2_DT_SALES values(

  to_date('20070522','yyyymmdd'),5,400

);

commit;

附录B DI对象

Data Integrator版本为11.7。示例对象导出为atl文件，从菜单选择Tools\Import from File可以导入。Atl文件下载地址为： SCD2_EMP.atl。

参考

【1】       Data warehouse toolkit 2nd Ed，p95-102，Slowly Changing Dimensions

【2】       Data warehouse toolkit 2nd Ed，p102-105，Hybrid Slowly Changing Dimension Techniques

【3】       Data warehouse toolkit 2nd Ed，p105，rapidly changing attribute

【4】       Data warehouse toolkit 2nd Ed，p100，SCD2 ETL workflow

【5】       Data warehouse toolkit 2nd Ed，p100，SCD2 ETL中使用CRC进行表比较

【6】       Data Integrator Technical Manuals，p1064-1065，Operation Code

【7】       Data Integrator Technical Manuals，p1130-1152，Query Transforms

【8】       Data Integrator Technical Manuals，p1159-1167，Table Comparison Transforms

【9】       Data Integrator Technical Manuals，p1102-1108，History Preserving

【10】   Data Integrator Technical Manuals，p1108-1109，Key Generation

Tags:

No TrackBacks

TrackBack URL: http://www.zw1840.com/mt/mt-tb.cgi/12

Leave a comment

Search

About this Entry

This page contains a single entry by zw1840 published on November 21, 2008 11:25 PM.

首次修改 MT 的 Entry Template was the previous entry in this blog.

GRANT is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.