資料倉儲資料質量的問題探討(轉)

一、 資料質量和清洗
ETL是資料倉儲的最重要的基礎，良好的ETL從業務系統中抽取資料，轉換資料質量，保證資料一致性，這樣才能夠保證各個獨立的不同的資料來源能夠整合到一起，最終只有這樣才能真正達到決策支援的目的。
資料清洗是ETL系統的一個最重要的步驟，資料的抽取和載入也是很必要的，但是他們只負責資料的遷移和重組格式。只有資料清洗才能真正改變資料，並且為了目標提供高質量的資料保證。[@more@](

高質量資料意味著：
• 正確的。資料的值和描述一定是真實的和業務系統保持一致的。
• 明確的。資料的值和描述有且只能有一個意思
• 一致的。資料的值和描述在全域性中也即資料倉儲中都表示一個意思
• 完整的。這有兩個方面。首先確保每一條資料都必須是有意義的（不能為NULL值），其次要求我們在處理過程中不能有任何資訊的“損失”

通常情況下，當BI/DW專案結束時，使用者總是會將BI報表和OLTP報表或者明細報表進行比較，以檢驗資料倉儲報表的準確性。一旦使用者發現它們之間是不一致的或者誤差超過一定比率，他們往往就會認為BI專案很失敗。
沒有絕對的準確，但是一定要知道為什麼不準確，這是資料倉儲專案的一個基本要求。

資料倉儲的資料來自於多個資料來源，所以資料的一致性很難得到保證，既然沒有絕對的準確，那麼就需要制定一個標準。因此我們建議和客戶達成一種相對標準，定義一個可以接受的誤差範圍。在這個前提下，我們找到誤差的原因，並給出分析報告，來提高客戶的滿意度和對資料倉儲專案的信心，從而確保資料倉儲專案成功的機率。

二、 原因分析和解決思路
資料質量存在問題的根本原因在於資料來源，保證資料質量是很困難的事情。這是事實，但是那一些潛在的問題會帶來資料質量問題呢？可以歸為以下幾類：
1. 資料格式問題,l例如資料缺失，超出資料範圍，無效資料格式等等。
2. 資料一致性問題，處於資料庫效能考慮，有時候可能會有意的去掉一些外間或者檢查約束。
3. 業務邏輯問題，這個很難說正確還是錯誤，通常是由於資料庫設計得不夠嚴格或者謹慎。

構造資料倉儲系統時，完全理解業務系統的業務邏輯和整體情況是不可能的。我們不能完全去研究那些詳細的設計文件，同時後來的很多需求變更也並不完全放映到文件上來，因此需要花費大量的時間去定位和分析其中的原因和變化。使用者要求在進行ETL之前必須瞭解所有的業務邏輯和規則，顯然也是不現實的。個人認為我們只需要瞭解和處理那些可能遭遇問題的資料。我們必須決定這些資料是拒絕呢還是處理。假如資料質量得不到保證的話，在後續的處理過程中，這樣的錯誤將逐漸被放大。
正是因為資料質量問題貫穿於專案的整個生命週期，而且不能避免，我們必須面對而且給出解決辦法，儘量把影響減小到最少。
通常情況下，當我們遇到錯誤資料，ETL一般提供以下4種解決辦法：
1. 沒有任何處理的透過記錄
2. 透過記錄，打上錯誤標記
3. 拒絕記錄
4. 停止ETL任務

下面就這幾種情況進行一下分析：
首先選項1明顯不能保證資料質量，並將最終影響報表質量。
其次選項3也不能保證資料完整性，因為資料將發生遺棄，也將會影響報表質量。
再次選項4會影響ETL處理，導致資料倉儲不能正常執行下去。
所以，最常見的處理方式就是選項2，首先保證這些記錄順利透過，然後記錄一些錯誤標誌，並透過報表反映出來。

這樣做有以下四種好處：
1. 透過特殊處理確保了資料的完整性
2. 反映了資料倉儲的資料來源資料質量
3. 對資料質量可以有一個比較準確的度量
4. 確保了資料倉儲的順利實施和任務的正常排程
資料質量應該儘量確保在ETL環節中進行，因為每一點的錯誤都會導致後續處理的無限放大，同時資料倉儲的處理是線性進行的，當發現錯誤時，很難回過頭來對資料進行重新的處理。因此儘量把錯誤和資料質量問題消除在靠前的位置。

三、 處理對策

資料清洗通常根據不同的情況進行處理，在這裡沒有辦法一一列舉，只能對常用的幾種情況進行分析處理。

1. 維度：NULL值
假如維度資料為空，在資料處理時可能會導致錯誤的處理，透過SQL處理時事實表中可能會丟失這部分資料。

2. 維度：外來鍵丟失
前者提到處於資料庫效能考慮，業務系統有時候會放棄外間約束或者檢查約束，但這樣資料的完整性有時無法得到保證，當資料被修改或者刪除的時候，這部分資料可能會變成孤兒資料。

3. 度量值：超出範圍
假如沒有約束和檢查規則，原始資料表中的度量值可能為空或者超出預想的範圍，當我們處理和計算這部分資料的時候，也會導致錯誤的結果。

4. 業務邏輯和錄入錯誤
很顯然，這部分錯誤，我們基本上是無能為力的，缺乏有效的驗證和糾錯，實際上資料倉儲的流水線作業形式和巨大的資料量，讓我們對這些資料的校驗變得不太可能了。我們只能祈求業務系統錄入人員的責任心了。

四、 處理指令碼
實際上對於NULL值，缺乏一致性，資料範圍的處理，可以透過簡單的語句來完成。
Example:
-- Create a original table, only include ID and two measure fields.
-- here ID is one of attribute columns only, so it can be Null values.
-- two measure fields have no constraint or check rule, they may be
-- NULL or out of Scope, on the assumption that the scope value of
-- measure1 and measure2 field is between 0 and 100.
Create table table_original
(
ID INT,
Measure1 LONG,
Measure2 LONG
)
-- Create a dimension table, only include ID and Name fields.
CREATE TABLE table_dimension
(
ID INT CONSTRAINT Pri_Table_Dimension PRIMARY KEY,
Name VARCHAR2 (20)
)
-- Create a fact table, Only include ID and two Measure. It will save
-- compute or count result.
Create table table_fact
(
ID INT NOT NULL,
Measure1 LONG,
Measure2 LONG
)
--Insert a sample data into dimension table and original table.
INSERT INTO table_dimension VALUES (1,'a');
INSERT INTO table_dimension VALUES (2,'b');
COMMIT;
INSERT INTO table_original VALUES (1,1,1);
INSERT INTO table_original VALUES (2,101,2);
INSERT INTO table_original VALUES (3,3,3);
INSERT INTO table_original VALUES (null,4,4);
COMMIT;

--Common OLTP Report Usage, Query SQL is like
SELECT A.ID ID,SUM(A.Measure1) Measure1,SUM(A.Measure2) Measure2
FROM table_original A,table_dimension B
WHERE A.ID = B.ID
GROUP BY A.ID;
--The Result is below:
ID Measure1 Measure2
1 1 1
2 101 2
So we can get the count value of 1 and 2 in ID field only, it means two
Records are lost, in fact the Measure1 value of ID 2 is out of scope.
--If in OLAP report, we will make Data Clean
--If ID is NULL, we will use -1 replace it
--If ID is not exist in original table, we will use -2 replace it
--We also need check the validity and scope of measures field, we will
--give a default value or average value, it depend on our need.
--So query SQL is like
SELECT -- A.ID,B.ID, -- the transformed field names ID
DECODE(NVL(A.ID,-2),-2,-2, DECODE(NVL(B.ID,-1),-1,-1,B.ID)) AS ID,
SUM(CASE A.Measure1>0 AND A.Measure1<100 THEN Measure1 ELSE 0 END
) Measure1,
SUM(CASE A.Measure2>0 AND A.Measure2<100 THEN Measure2 ELSE 0 END
) Measure2,
FROM table_original A,table_dimension B
WHERE A.ID = B.ID(+)
GROUP BY DECODE(NVL(B.ID,-2),-2,-2, DECODE(NVL(B.ID,-1),-1,-1,B.ID))

結果如下：

Record A.ID B.ID ID Measure1 Measure2
1 1 1 1 1 1
2 2 2 2 0 2
3 3 NULL -2 3 3
4 NULL NULL -1 4 4

加入校驗過於複雜，我們也可以求助使用者自定義函式或者儲存過程進行資料清洗。

五、 可度量性
如果使用者對報表質量提出質疑，我們可以比較容易的給出錯誤度量報表，只需要提供各個維度中-1，-2的資料即可。假如方便的話，還可以提供相關圖表。
當然如果在報表仍然存在問題，我們只能求助於SQL，透過排除法，來完成報表的質量驗證工作。
--First count the record number of original table.
SELECT COUNT(*)
FROM table_original A
--Relate with the first dimension table, and capture the record number.
SELECT COUNT(*)
FROM table_original A,table_dimension1 B
WHERE A.ID = B.ID;
--Relate with the second and more dimension table, and capture the
--record number, to verify the result.
SELECT COUNT(*)
FROM table_original A,table_dimension1 B, table_dimension2 C
WHERE A.ID = B.ID
AND A.ID = C.ID;