5 关于数据仓库维度数据处理的方法探究系列——缓慢变化维处理——全历史记录...

   全历史记录是缓慢变化维中最为强大的一种加载方式。它将可以完全实现覆盖方式能实现的加载方式，且可以实现对数据的历史记录，可以记录下每一个数据的细微变化。

3.3.2 全历史记录（ Type 2 Dimension -- keep a full history of changes in the target ）

全历史记录是缓慢变化维中最为强大的一种加载方式。它将可以完全实现覆盖方式能实现的加载方式，且可以实现对数据的历史记录，可以记录下每一个数据的细微变化。通过比对，如发现数据仓库中当前数据已为旧数据，则对当前已有记录进行旧数据标记，主键值不变，同时把修改过的数据作为新的一条数据插入，并赋予新的代理主键值；如发现有新数据，则把新数据加载到数据仓库中，并赋予新的代理主键值。通俗地说，就是指对于源表中的同一条数据，目标（数据仓库）中会根据变化保留下多条，即记录下每次的变化，并对最新的一条进行标记。

而对于全历史记录，根据数据仓库建模思想，又可采取三种记录方式。版本号方式、标志方式及时戳方式。

1. 版本号方式

    需要在目标表中增加版本号字段，用于记录该数据的版本号。

采用该方式，可以根据记录的版本号对比，版本号最大的将是最新的记录，而数据的历史信息，可以由版本号的递增得出或者数据库的递增字段特性来实现。

2. 标志方式

    需要在目标表中增加标志字段，用于标记同样的数据那一条是最新记录。

采用该方式，根据标志字段值即可得出当前最新记录，如标志为 1的表示为最新记录，标志为 0的为历史信息，历史信息可由其主键值大小得出历史记录的先后。

3. 时戳方式

   需要在目标表中增加起始时间及结束时间字段，用于标记该条记录抽取进入目标表的时间及因为其数据被更改后，变为历史数据的时间。

采用该方式，根据其开始时间及结束时间字段来得到当前最新记录，即结束时间为空的就是最新的记录，其余的填入结束时间的记录，可以根据时间的先后，得出历史信息的演变。而此种方式因为利用了时间字段，也详细记录下了历史信息的演变时间，是缓慢变化维中最为强大的一种加载方式，它记录的信息也是最全的。

下面分别对三种方式作一个详细说明：

3.3.2 .1 版本号方式
    它的操作方式与覆盖方式相同之处在于对新增数据的处理，只是根据原主键进行判断后进行数据的插入处理，代理主键利用数据库的 Identity类型字段进行自动生成。

    主要差别在于对更新字段数据的处理，它的操作方式为，为了每条记录预留相当部分的版本空间如 1000，对于不同的两条记录，其进入数据库后代理主键的相差值将是 1000，而中间的这些值，既是留给每条记录的版本空间。当进入为新数据时，系统根据上一个序列号值 *1000，产生新记录的代理主键值，并插入目标表。当发现是已有的数据发生改变，则把修改后的数据插入目标表，其代理主键值取原记录值的代理主键值 +1，而版本号亦是取原记录的版本号 +1，每条新记录的版本号均是从 0开始编号。

     以上方法需要利用游标进行逐条数据处理，性能不高；同时为了简化处理本例子采用系统步增列进行代理主键的生成，插入时默认从 1~N依次插入，当发现已有数据改变时，把修改的数据插入目标列，其代理主键自动取最大的主键值 +1，这样对于同一条记录最后更新的过的对应到维度表的记录必然是 ID值相同主键值最大的那条记录。

代码

-- 关于维度表的设计是版本方式，标志方式，时戳方式的合集，后续不再重复建表

CREATE TABLE t_dem_xxx

(

SurID INT IDENTITY(1,1),

-- 缓慢维度变化中的代理键 , 也可以采用非递增字列，为简单起见最好使用递增字段

ID VARCHAR(20) NOT NULL,

Name1 VARCHAR(50),

Name2 VARCHAR(50),

IsNew BIT, -- 标志方式类型中标志字段 是否为最新 0 否， 1 是

BeginDate DATETIME, -- 时戳方式类型中开始时间

EndDate DATETIME, -- 时戳方式类型中结束时间

CONSTRAINT PK_t_dem_xxx PRIMARY KEY (SurID)

)

go

CREATE TABLE t_tmp_xxx

(

ID VARCHAR(20) NOT NULL,

Name1 VARCHAR(50),

Name2 VARCHAR(50),

CONSTRAINT PK_t_tmp_xxx PRIMARY KEY (ID)

)

go
 
CREATE PROCEDURE p_dem_xxx

AS

-- 维度抽取存储过程

BEGIN

DECLARE

@num NUMERIC(10,0)

SELECT @num = COUNT(*) FROM t_dem_xxx

-- 如果原表为空，构造缺省值

IF @num = 0

BEGIN

INSERT INTO t_dem_xxx (ID,Name1,Name2) SELECT '-2','NULL 值 ',''

INSERT INTO t_dem_xxx (ID,Name1,Name2) SELECT '-1',' 缺失外键 ',''

END

-- 根据主键插入在维度表中找不到的基础数据

INSERT INTO t_dem_xxx

(

ID ,

Name1 ,

Name2

)

SELECT a.ID,a.Name1,a.Name2

FROM t_tmp_xxx a LEFT OUTER JOIN t_dem_xxx b

ON a.ID = b.ID

WHERE b.ID IS NULL

-- 根据主键插入在维度表中找到但是已经发生变化的的基础数据

INSERT INTO t_dem_xxx

(

ID ,

Name1 ,

Name2

)

SELECT a.ID,a.Name1,a.Name2

FROM t_tmp_xxx a JOIN t_dem_xxx b

ON a.ID = b.ID

WHERE a.Name1<>b.Name1 OR a.Name2<>b.Name2

END

  

3.3.2 .2标志 方式
    它的操作方式类似版本号方式，为每条记录预留 1000个变化空间或者为步增方式递增。新记录的插入代理主键的产生类似版本号方式，以 1000为倍数增加，而其新记录标志设置为 1，而当为旧数据时，一旦发现数据被更改过，则把该修改后的数据新插入目标，代理主键值取旧数据代理主键 +1或者取最大值然后 +1，并同时找到旧数据，更新其标志位为 0。

代码

CREATE PROCEDURE p_dem_xxx

AS

-- 维度抽取存储过程

BEGIN

DECLARE

@num NUMERIC(10,0)

SELECT @num = COUNT(*) FROM t_dem_xxx

-- 如果原表为空，构造缺省值

IF @num = 0

BEGIN

INSERT INTO t_dem_xxx (ID,Name1,Name2,IsNew) SELECT '-2','NULL 值 ','',1

INSERT INTO t_dem_xxx (ID,Name1,Name2,IsNew) SELECT '-1',' 缺失外键 ','',1

END

-- 根据主键插入在维度表中找不到的基础数据

INSERT INTO t_dem_xxx

(

ID ,

Name1 ,

Name2 ,

IsNew

)

SELECT a.ID,a.Name1,a.Name2,1

FROM t_tmp_xxx a LEFT OUTER JOIN t_dem_xxx b

ON a.ID = b.ID

WHERE b.ID IS NULL

-- 根据主键更新在维度表中找到但是已经发生变化的的基础数据的标志位为 0

UPDATE t_dem_xxx

SET IsNew = 0

FROM t_tmp_xxx a,t_dem_xxx b

WHERE a.ID = b.ID

AND (a.Name1<>b.Name1 or a.Name2<>b.Name2)

-- 根据主键插入在维度表中找到但是已经发生变化的的基础数据

INSERT INTO t_dem_xxx

(

ID ,

Name1 ,

Name2 ,

IsNew

)

SELECT a.ID,a.Name1,a.Name2,1

FROM t_tmp_xxx a JOIN t_dem_xxx b

ON a.ID = b.ID

WHERE a.Name1<>b.Name1 OR a.Name2<>b.Name2

END
 

3.3.2 .3 时戳方式
 

    它的操作方式为当新记录插入时，取系统的当前时间，为其置上开始时间，此时其结束时间为空，当为旧数据时，把修改后的数据插入目标，代理主键由序列号生成器顺序产生，同时找到旧数据，更新其结束时间为当前系统时间。这种方式在系统中，只要结束时间为空的即是当前的最新值，而旧数据也可根据其开始时间结束时间看出它的实效空间，及旧数据的先后顺序。
     全历史记录的方式是缓慢变化维的最为强大的一种记录方式，由于其设计的精妙，可以完全冗余发生的在抽取过程中出现的灾难事故及数据恢复工作，即使在运行过程中发生了断电等事故，我们完全不需要去关系，抽取到底进行到那个步骤，抽取了多少，目标插入了多少。。。。。。我们只要在环境恢复后，重新运行抽取，即可重新完成数据的整合。

代码

CREATE PROCEDURE p_dem_xxx

AS

-- 维度抽取存储过程

BEGIN

DECLARE

@num NUMERIC(10,0)

SELECT @num = COUNT(*) FROM t_dem_xxx

-- 如果原表为空，构造缺省值

IF @num = 0

BEGIN

INSERT INTO t_dem_xxx (ID,Name1,Name2,BeginDate)

SELECT '-2','NULL 值 ','',GETDATE()

INSERT INTO t_dem_xxx (ID,Name1,Name2,BeginDate)

SELECT '-1',' 缺失外键 ','',GETDATE()

END

-- 根据主键插入在维度表中找不到的基础数据

INSERT INTO t_dem_xxx

(

ID ,

Name1 ,

Name2 ,

BeginDate

)

SELECT a.ID,a.Name1,a.Name2,GETDATE()

FROM t_tmp_xxx a LEFT OUTER JOIN t_dem_xxx b

ON a.ID = b.ID

WHERE b.ID IS NULL

-- 根据主键更新在维度表中找到但是已经发生变化的的基础数据的结束时间为当前

UPDATE t_dem_xxx

SET EndDate = GETDATE()

FROM t_tmp_xxx a,t_dem_xxx b

WHERE a.ID = b.ID

AND (a.Name1<>b.Name1 or a.Name2<>b.Name2)

-- 根据主键插入在维度表中找到但是已经发生变化的的基础数据

INSERT INTO t_dem_xxx

(

ID ,

Name1 ,

Name2 ,

BeginDate

)

SELECT a.ID,a.Name1,a.Name2,GETDATE()

FROM t_tmp_xxx a JOIN t_dem_xxx b

ON a.ID = b.ID

WHERE a.Name1<>b.Name1 OR a.Name2<>b.Name2

END