全歷史記錄是緩慢變化維中最為強大的一種載入方式。它將可以完全實現覆蓋方式能實現的載入方式，且可以實現對資料的歷史記錄，可以記錄下每一個資料的細微變化。

全歷史記錄（Type 2 Dimension -- keep a full history of changes in the target）

全歷史記錄是緩慢變化維中最為強大的一種載入方式。它將可以完全實現覆蓋方式能實現的載入方式，且可以實現對資料的歷史記錄，可以記錄下每一個資料的細微變化。透過比對，如發現資料倉儲中當前資料已為舊資料，則對當前已有記錄進行舊資料標記，主鍵值不變，同時把修改過的資料作為新的一條資料插入，並賦予新的代理主鍵值；如發現有新資料，則把新資料載入到資料倉儲中，並賦予新的代理主鍵值。通俗地說，就是指對於源表中的同一條資料，目標（資料倉儲）中會根據變化保留下多條，即記錄下每次的變化，並對最新的一條進行標記。

而對於全歷史記錄，根據資料倉儲建模思想，又可採取三種記錄方式。版本號方式、標誌方式及時戳方式。

1. 版本號方式

需要在目標表中增加版本號欄位，用於記錄該資料的版本號。

採用該方式，可以根據記錄的版本號對比，版本號最大的將是最新的記錄，而資料的歷史資訊，可以由版本號的遞增得出或者資料庫的遞增欄位特性來實現。

2. 標誌方式

需要在目標表中增加標誌欄位，用於標記同樣的資料那一條是最新記錄。

採用該方式，根據標誌欄位值即可得出當前最新記錄，如標誌為1的表示為最新記錄，標誌為0的為歷史資訊，歷史資訊可由其主鍵值大小得出歷史記錄的先後。

3. 時戳方式

需要在目標表中增加起始時間及結束時間欄位，用於標記該條記錄抽取進入目標表的時間及因為其資料被更改後，變為歷史資料的時間。

採用該方式，根據其開始時間及結束時間欄位來得到當前最新記錄，即結束時間為空的就是最新的記錄，其餘的填入結束時間的記錄，可以根據時間的先後，得出歷史資訊的演變。而此種方式因為利用了時間欄位，也詳細記錄下了歷史資訊的演變時間，是緩慢變化維中最為強大的一種載入方式，它記錄的資訊也是最全的。

下面分別對三種方式作一個詳細說明：

.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

.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時戳方式

它的操作方式為當新記錄插入時，取系統的當前時間，為其置上開始時間，此時其結束時間為空，當為舊資料時，把修改後的資料插入目標，代理主鍵由序列號生成器順序產生，同時找到舊資料，更新其結束時間為當前系統時間。這種方式在系統中，只要結束時間為空的即是當前的最新值，而舊資料也可根據其開始時間結束時間看出它的實效空間，及舊資料的先後順序。
全歷史記錄的方式是緩慢變化維的最為強大的一種記錄方式，由於其設計的精妙，可以完全冗餘發生的在抽取過程中出現的災難事故及資料恢復工作，即使在執行過程中發生了斷電等事故，我們完全不需要去關係，抽取到底進行到那個步驟，抽取了多少，目標插入了多少。。。。。。我們只要在環境恢復後，重新執行抽取，即可重新完成資料的整合。

程式碼

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

5 關於資料倉儲維度資料處理的方法探究系列——緩慢變化維處理——全歷史記錄

.1版本號方式

.2標誌方式

.3時戳方式

相關文章