第5章 資料倉儲的資料建置

本章目次 簡介 5.1 資料倉儲的資料建置流程 5.2 資料倉儲的資料建置規劃 5.3 資料抽取 5.4 資料清理 5.5 資料一致化 刪除Discussion一章 5.6 資料一致化 5.7 資料交付 5.8 中繼資料 5.9 總結 Copyright © 滄海書局

學習目標(1) 瞭解資料倉儲的資料建置流程中，其規劃與設計的步驟和任務。 瞭解資料倉儲的資料處理流程的步驟，以及各步驟之任務。 瞭解資料倉儲的資料建置規劃，需要考慮的各種重要需求及 ETL 系統架構。 瞭解「資料集結」的概念， 及設計資料集結區的方法與工具。 瞭解 ETL 系統中常用的資料結構及其特性。 瞭解邏輯資料對照表的用途及其組成內容。 瞭解進行來源系統分析時，資料探索階段的步驟和工具。 瞭解在資料抽取過程中如何捕獲發生變化的資料。 Copyright © 滄海書局

學習目標(2) 瞭解在資料清理時，資料品質檢查主要著重的四個方面。 瞭解在資料清理時，資料品質系統面對的四種壓力及平衡的方法。 瞭解在資料清理時，在進行資料清理之前、中、後，追縱錯誤與衡量品質的方法。 瞭解資料清理時，資料品質之檢查方法的四種類型。 瞭解資料清理時，資料品質檢查的處理流程與指導原則。 瞭解資料清理時，資料進行一致化之原因，及一致化維度與一致化事實之概念。 瞭解資料一致化時，建置一致化維度需要的三個步驟。 瞭解資料交付時，交付一致化維度表與一致化事實表之步驟。 瞭解提交事實表時，上載事實表的技巧。 瞭解提交事實表時，彙總事實表之用途、架構及設計要點。 瞭解在 ETL 系統中，中繼資料的架構與分類。 Copyright © 滄海書局

ETL是資料倉儲運作的基礎，ETL 系統對於資料倉儲專案的成敗有決定性的影響 5.1 簡介 ETL是資料倉儲運作的基礎，ETL 系統對於資料倉儲專案的成敗有決定性的影響

「抽取–轉換–載入」 (Extract-Transform-Load, ETL) ETL是資料倉儲運作的基礎，一個設計良好的 ETL 系統需要從來源系統抽取資料，再清理資料提升資料品質，然後將資料一致化，使各方的來源資料可以整合在一起使用，最後以規範的格式交付資料，以便應用開發者可以開發應用系統，也使終端使用者可以制定決策。對於一個資料倉儲系統，開發、建置和維護 ETL 系統所投入的資源很容易就可以到達 70%。由此可見 ETL 系統對於資料倉儲專案的成敗有決定性的影響。 參考 書籍 Kimball 所著 THE DATA WAREHOUSE ETL Toolkit 一書 Copyright © 滄海書局

5.2 資料倉儲的資料建置流程 規劃設計流程 資料處理流程

規劃設計流程 Copyright © 滄海書局

資料處理流程 抽取： 清理： 一致化： 交付： 從原始資料來源抽取資料。 確保資料品質，清理資料使資料能夠被使用。 確保各個資料來源的資料一致性。 一致化： 將資料交付給使用者進行開發或執行決策。 交付： Copyright © 滄海書局

Copyright © 滄海書局

5.3 資料倉儲的資料建置規劃 需求分析 架構設計

需求分析 一切圍繞著「需求」 1. 業務需求 2. 法規需求 3. 品質需求 4. 安全需求 5. 整合需求 6. 時程需求 7. 備份需求 8. 交付需求 9. 技能需求 10. 資源需求 1. 業務需求 (Business needs) 業務需求指的是資料倉儲終端使用者的資訊需求，是最基礎且最重要的 需求。業務需求直接決定了資料來源的選擇。業務需求是不斷變化的，ETL 小組的核心工作就是不斷地了解和檢驗業務需求。 2. 法規需求 (Compliance requirements) 近年來，依據法令的要求，企業上報的資料必須更加準確與完整，尤其 是財務報表。許多行業已使用資料倉儲來滿足日常報表需求。對資料倉儲典 型的法規需求包括：資料來源及資料倉儲的歸檔備分、修改交易資料的完整 性證明等等。 3. 品質需求 (Quality requirements) 資料品質需透過各種資料評估方法來檢查資料，充分瞭解 ETL 需要使用 的資料的內容、結構和品質，其評估結果可能關係到資料倉儲的成敗。好的 資料評估能夠處理巨量的資料，並找到需要解決的資料問題。 4. 安全需求 (Security requirements) 資料倉儲需要更高規格的安全考量。基於安全性考量，資料倉儲只能向 使用者提供其有權查詢的資料。對於許多資料倉儲而言，安全問題仍只被視 為一個附加的工作，如此自然的就會存在風險。此外，安全考量的範疇須擴 展到資料的實體備份。 5. 整合需求 (Integration requirements) 整合需求係為能整合各種來源資料進行分析。在 ETL 系統中，資料整合 亦叫做一致化步驟，用以產生提供一致化的維度表和事實表，以達成整合不 同來源系統之來源資料進行分析之目的。 6. 時程需求 (Latency requirement) 時程需求係描述資料提交到終端使用者的速度。時程需求對系統的架構 有巨大的影響。例如，時程需求太緊迫，ETL 系統架構就必須從批次處理架 構轉化成串流式處理架構。這樣的轉變，無論是軟體和硬體，都需要完全不 同的考量。 7. 備份需求 (Backup requirements) 為了能夠比對歷史資料、重新復原與回寫等作業需求，ETL 過程一定需 要進行資料備份 (將資料無限期地儲存到永久的介質上)。在抽取、清理、一 致化與交付各步驟所集結的資料都應當備份，除非已確定絕不會恢復某個資 料集。 8. 交付需求 (Delivery requirements) ETL 系統的最終步驟是將資料提交給使用者的應用程式。提交過程十分 的重要。ETL 小組必須與建模小組緊密配合，對提供終端使用者應用程式所 用到的資料結構和內容進行嚴格把關，務必使交付的過程簡單便捷。 9. 技能需求 (Skills requirements) 建立 ETL 系統時，設計方面的重要決定必須由建置和管理系統的人做出 適當決定。如果具有 ETL 工具的使用經驗，並且知道如何管理專案，那麼在 建立 ETL 系統時可能會有更強的信心。 10. 資源需求 (Resource requirements) 很多情況下，上級管理部門堅持使用既有的軟硬體授權。大部分情況 下，這個要求是能夠接受的，並且也符合實際的需要。但有些情況下，使用 既有的授權卻是個錯誤，因為不可能滿足使用者的需求。 Copyright © 滄海書局

架構設計 ETL 架構考量 1. 購買 ETL 工具或自行開發 2. 批次還是串流的處理流程 3. 水平還是垂直的任務依賴 4. 排程自動化 5. 異常處理的機制 6. 品質控制的機制 7. 復原與重新啟動 8. 中繼資料的處理方式 9. 資料安全的控管方式 Copyright © 滄海書局

資料集結 (Data Staging) 指後端所進行 ETL 的各個步驟 (抽取、清理、一致化與交付) 將所產生的資料進行儲存。 持久集結區 (Persistent staging area)：係維護歷史資料而使用的集結區。 臨時集結區(Temporary staging area)：則是資料在每次載入過程後即被刪除。 資料集結區(Data staging area) Copyright © 滄海書局

集結區規模估算表 (Staging tables volumetric worksheet) 提供給資料庫管理員和系統管理員進行集結區的空間分配。 集結區規模估算表的用途 (1) 資料表名稱 (Table name)：在集結區中的資料表的名字。 (2) 更新策略 (Update strategy)：如，截斷/重新載入、新增/刪除等 。 (3) 載入頻率 (Load frequency)：如，每日、每週、每月等。 (4) ETL 作業 (ETL job(s))：處理資料表的作業或程式。(可能有多個作業) (5) 初始資料量 (Initial row count)：資料表初始時的資料筆數。 (6) 平均資料長度 (Avg row length)：資料表中資料的平均長度。 (7) 增長依據 (Grows with)：定義資料表何時會增長。(如，維度表-增加新狀態時) (8) 預計每月資料量 (Expected monthly rows)：預計資料的每月資料的筆數。 (9) 預計每月位元組數 (Expected monthly bytes)：等於平均資料長度 × 預計每月資料量。 (10) 初始資料表大小 (Initial table size bytes)：等於平均資料長度 × 初始資料量。 (11) 6 個月資料表大小 (Table size 6mo. (MB))：公式為 ((平均資料長度 ×初始資料) + (平均資料長度 × 預計每月資料量 × 6))/1,048,576 (以 MB為單位)。 集結區規模估算表的內容 Copyright © 滄海書局

Copyright © 滄海書局

ETL 系統中的資料結構 1. 平面文件 (Flat files) 2. XML 資料集 (XML Data sets) 3. 關聯資料表 (Relational tables) 4.獨立的 DBMS 工作表 (Independent DBMS working tables) 5. 第三正規化 ER 模型 (Third normal form entity/relation models) 6. 非關聯式資料來源 (Nonrelational data sources) 7. 維度資料模型 (Dimensional data models) Copyright © 滄海書局

資料抽取是ETL 操作程序的基礎和開端。抽取前需擬定資料抽取的計畫，並在資料抽取過程中捕獲發生變化的資料。 5.4 資料抽取 資料抽取是ETL 操作程序的基礎和開端。抽取前需擬定資料抽取的計畫，並在資料抽取過程中捕獲發生變化的資料。

邏輯資料對照表 (The logical data map) 就是為 ETL 開發者提供一個藍圖，清晰地描述在轉換過程中來源系統的資料定義，目標資料倉儲的資料模型，以及從原始資料到最終目的資料轉換所需要的操作。 邏輯資料對照表的用途 (1) 目標表名稱 (Target table name)：資料倉儲中出現的資料表名稱。 (2) 目標欄位名稱 (Target column name)：資料倉儲資料表中的欄位名稱。 (3) 資料表類型 (Table type)：說明這個表是事實表、維度表等。 (4) 緩時變維度類型 (SCD type)：緩時變維度的三種處理方式 (註 1)。 (5) 來源資料庫 (Source database)：來源資料所在的資料庫的名稱 (連接字串)。 (6) 來源資料表名稱 (Source table name)：來源資料所在資料表名稱。 (7) 來源欄位名稱 (Source column name)：生成目標所需的相關欄位名稱。 (8) 轉換規則 (Transformation)：使用來源資料產生期望目標資料的操作(SQL 或虛擬碼)。 邏輯資料對照表的內容 Copyright © 滄海書局

Copyright © 滄海書局

來源系統分析 資料探索階段的目的 為了瞭解來源系統的情況，為建置邏輯資料對照表做好準備。 資料探索階段的步驟 來源系統分析 (Source system analysis) 資料探索階段 (Data discovery phase) 異常偵測階段 (Anomaly detection phase) 為了瞭解來源系統的情況，為建置邏輯資料對照表做好準備。 資料探索階段的目的 資料探索階段的步驟 (1) 收集來源系統 包含來源系統的說明檔、資料字典、 報告等。 (2) 追蹤來源系統 建置來源系統追縱報告 (Source system tracking report)。 (3) 確定原始系統 確定資料的發源地，解決相同資料不同版本之問題。 (4) 分析來源系統 如完整的瞭解來源系統之 ER 模型。 Copyright © 滄海書局

來源系統追蹤報告 (Source system tracking report) 詳細的記錄來源系統的相關資訊，作為分析來源系統的參考依據 來源系統追蹤報告的用途 (1) 主題名稱 (Subject area)：來源系統支援的資料超市的名稱。 (2) 介面名稱 (Interface name)：來源系統支援的交易應用系統的名稱。 (3) 業務名稱 (Business name)：業務使用者通常使用的名稱。 (4) 優先順序 (Priority)：用於確定將來處理的順序。 (5) 部門/業務 (Department/Business use)：使用來源系統的主要部門。 (6) 業務所有者 (Business owner)：與使用或應用來源系統的聯絡人或者小組。 (7) 技術所有者 (Technical owner)：負責維護來源系統的 DBA 或 IT 專案經理。 (8) 資料庫管理系統 (DBMS)：來源系統所使用資料庫管理系統的名稱。 (9) 上線伺服器 (Production server/OS)：運行資料庫的伺服器名稱。 (10) 日常使用者數 (# Daily users)：評估有多少操作型人員使用這些資料。 (11) DB 大小 (DB size)：評估 ETL 的優先順序和將要付出的工作量。 (12) DB 複雜度 (DB complexity)：指來源系統中資料表和檢視表 (View)的數目。 (13) 每日交易數 (# Transactions per day)：評估增量載入過程所需要的容量。 (14) 備註 (Comments)：例如，是否為原始系統的原因。 來源系統追蹤報告的內容 Copyright © 滄海書局

Copyright © 滄海書局

抽取變化資料 監測來源系統 發生變化的資料 1. 使用審計欄 2. 使用資料庫日誌 3. 按時抽取 4. 排除處理 5. 初始和增量載入 被刪除或覆蓋的資料 1.和來源系統的所有者商量，如果可能，明確的通知所有刪除或覆蓋的資料。 2.週期性的檢查來自來源系統的量值的匯總值，並通知 ETL 人員內容發生變化，並盡可能的深入下去找到這個變化。 Copyright © 滄海書局

在抽取資料之後，需要對它們進行清理，確認和修復資料中的錯誤和缺失，以提升資料的品質。 5.5 資料清理 在抽取資料之後，需要對它們進行清理，確認和修復資料中的錯誤和缺失，以提升資料的品質。

資料品質檢查的面向 資料品質 檢查 (1) 正確性檢查 (Correct) (2) 明確性檢查 (Unambiguous) (3) 一致性檢查 (Consistent) (4) 完全性檢查 (Complete) 資料品質檢查的四個面向： (1) 正確性檢查 (Correct)：檢查資料的值及其描述是否真實反映客觀事務。例如地址的描述是否完全。 (2) 明確性檢查 (Unambiguous)：檢查資料的值及其描述是否只有一個意思或者只有一個解釋。例如地名相同的兩個縣需要加區分方法。 (3) 一致性檢查 (Consistent)：檢查資料的值及其描述是否統一的採用固定的方式來表示。例如幣別代號的一致性。 (4) 完全性檢查 (Complete)：完全性有兩個需要檢查的地方，一個是檢查欄位的資料的值是否完全，例如檢查空值。另一個是檢查資料合計的筆數是否正確。 Copyright © 滄海書局

資料品質系統的壓力 資料品質系統的壓力 (1) 完備性 (Be thorough) (2) 快速性 (Be fast) (3) 正確性 (Be corrective) (4) 透明性 (Be transparent) 資料品質系統的壓力的四種面向： (1) 完備性 (Be thorough)：必須保證在資料檢測的全面性。 (2) 快速性 (Be fast)：必須快速的處理大量且不斷增長的資料。 (3) 正確性 (Be corrective)：必須保證資料的正確性。 (4) 透明性 (Be transparent)：必須誠實的揭露資料的錯誤與缺陷。 Copyright © 滄海書局

資料品質系統的衝突 對於資料品質系統來講，完全同時達到前述目標是不可能的，必須要做適當的取捨。 完備性 快速性 對於資料品質系統來講，完全同時達到上述目標是不可能的。必須要做適當的取捨，包含： 1. 衝突一：完備性與快速性 資料品質系統無法同時兼顧快速性和完備性。解決的方法就是思考資料品質的問題來找到平衡點，比如：(1) 時效性：什麼時間點資料會變得過時？及 (2) 正確性：保證這些資料的正確性有多重要？等等。 2. 衝突二：正確性與透明性 資料品質系統無法同時滿足正確性和透明性。解決的方法是對不同的錯誤建立一個可判斷的原則界限，能夠在清理過程進行糾正或者標識，並生成 Copyright © 滄海書局

資料概況分析報告 (Data-profiling analysis) 對於來源系統資料品質的分析，用來決定資料移轉的設計和做法，並且為 ETL 系統中需要建立的錯誤事件事實表 (Error event fact table) 和審計維度表 (Audit dimension)預作準備， 資料概況分析報告的用途 (1) 結構定義 (Schema definitions) (6) 業務物件 (Business objects) (2) 應用主題 (Domains) (7) 資料來源 (Data sources) (3) 資料表定義 (Table definitions) (8) 資料表別名 (Synonyms) (4) 資料規則 (Data rules) (9) 值規則 (Value rules) (5) 需要處理的問題 (Issues that need to be addressed) 資料概況分析報告 的內容 Copyright © 滄海書局

錯誤事件事實表 (Error event fact table) 係在資料清理過程中用來記錄發現的錯誤事件。是 ETL 系統中收集、分析並且控制資料品質的核心工具 錯誤事件事實表的用途 錯誤事件事實表的範例 (1) 錯誤事件事實表 (Error event fact table) ： 每一個被篩檢程式 (Screen)發現的錯誤或問題都被記錄在錯誤事件事實表中。(如果運行 10 個不同的篩檢程式，且每個篩檢程式發現 10 個缺陷資料，總共就產生100 個資料。) (2) 日期維度表 (Date dimension table)： 表示日期和時間。 (3) 批次處理維度表 (Batch dimension table)： 為每次批次處理建立一筆資料。 (4) 來源系統維度表 (Source system dimension table)： 用來識別有缺陷資料的資料來源系統。 (5) 篩檢程式維度表 (Screen dimension table)：描述每個篩檢程式的內容，該資料表記錄的屬性如下： (a) 篩檢程式類型 (Screen type) 和篩檢程式種類 (Screen categoryname)：按主題把相關的篩檢程式分組。 (b) ETL 階段 (ETL stage)：描述篩檢程式在 ETL 過程中被應用的階段。 (c) 處理順序編號 (Processing order number)：篩檢程式在 ETL 過程中的處理順序編號。 (d) 嚴重程度分數 (Default severity score)：異常錯誤之嚴重程度計分，這個評分將反應至錯誤事件事實表中，彙總計算最終的嚴重程度得分。 (e) 異常處理 (Exception action)：發現異常資料時之動作 (如通過、拒絕，或停止 ETL)。 (f) 篩檢 SQL (Screen SQL)：篩檢程式的實際 SQL 片段。 Copyright © 滄海書局

審計維度表 (Audit dimension) 在資料清理工作完成後提供終端使用者關於事實表資料品質的一個維度表，其與事實表中的每個事實資料相關聯，記錄篩檢程式執行的時間戳記和輸出結果、錯誤類型和發生次數，以及資料品質評分等訊息。 審計維度表的用途 審計維度表的範例 Copyright © 滄海書局

異常偵測階段 (Anomaly detection phase) 來源系統分析 (Source system analysis) 資料探索階段 (Data discovery phase) 異常偵測階段 (Anomaly detection phase) 資料異常 (Data anomaly) 指的是那些不適合和其他資料放到一起的資料，發現這些異常需要特別的技術及仔細地分析，並且最好及早發現。 異常資料偵測的功用 1. 資料計數 (Data count) 2. 資料採樣 (Data sampling) 異常資料偵測的作法 1. 資料計數 (Data count) 最簡單的檢測方法是對有問題的欄位進行分組時計算該表的資料筆數。透過簡單的查詢、分析值的分布，如發現奇異值，就可能發現損壞的資料。 SQL 範例如下： Select state，count(*) From order_detail Group by state 2. 資料採樣 (Data sampling) 對於資料量大的表，一般通過採樣技術來減少資料量，然後分析其分布的合理性。需注意，儘量不要用日期區間來做限制條件，因為很可能會錯過異常。 select a.* from employee a， (select rownum counter， a.* from employee a) B where a.emp_id = b.emp_id and mod(b.counter， trunc((select count(*) Copyright © 滄海書局

資料品質檢查的方法 1. 欄位檢查 2. 結構檢查 資料品質 檢查方法 4.值檢查 3. 資料檢查 就單筆資料確保由來源系統輸入的資料包含目標系統期望的資料 2. 結構檢查 專注於各個欄位間關係之正確性 3. 資料檢查 對單筆資料根據業務規則進行檢查 4.值檢查 根據業務規則在資料規則上做合理的延伸檢查。(如彙總結果) 資料品質 檢查方法 5.5.3.2 資料品質檢查 資料品質檢查需要根據各種檢查目的設計出各種篩檢程式，ETL 系統透 過執行這些篩檢程式來確保資料具有足夠程度之資料品質。資料品質之檢查 方法大致可分為四個大的類型，分別說明如下： 1. 欄位檢查 (Column property enforcement) 欄位檢查係就單筆資料確保由來源系統輸入的資料包含目標系統期望的 資料。較常用的欄位檢查包括如下篩檢程式： (1) 檢查空值 (Null) (5) 出現不合理長度之文字 (2) 出現不合理大小的數值 (6) 出現無效值列表中的數值 (3) 出現有效值列表以外的數值 (7) 拼寫檢查 (4) 格式檢查 24 商業智慧 基於這些篩檢程式的檢查結果，ETL 作業流程可以選擇： (1) 讓沒有錯誤的資料通過 (2) 讓資料通過，同時標記有錯誤的欄位值。(一般情況之指導原則) (3) 拒絕資料 (4) 停止 ETL 作業流 2. 結構檢查 (Structure enforcement) 相對於欄位檢查係專注於單一欄位之正確性，結構檢查則是專注於各 個欄位間關係之正確性。例如，檢查每個表有合適的主鍵和外來鍵，並且遵 循參照完整性；檢查確認欄位間存在之階層架構，例如有效的郵件通訊位址 (如郵遞區號、通訊地址)。 3. 資料檢查 (Data enforcement) 資料檢查主要係根據業務規則進行檢查，主要是對單筆資料做業務規則 檢查，如客戶單筆消費金額合理上下限檢查。 4. 值檢查 (Value enforcement) 對於值檢查亦根據業務規則進行檢查，值規則是在資料規則上做合理的 延伸，如進一步根據業務規則進行彙總計算後進行分析判斷，如單日客戶總 消費金額之合理上下限檢查。 Copyright © 滄海書局

資料品質檢查的處理流程 持續運行原則 (1) 執行： 一系列的篩檢程式按照中繼資料定義的順序執行。 (2) 記錄： 篩檢程式所偵測到的每個錯誤記錄在錯誤事件事實表中。 (3) 判斷： 當每一篩檢程式執行後，就會在錯誤事件事實表中查詢是否遇到致命的錯誤 (如整批資料遺失)。 (a) 如果沒有發現：持續運行 ETL 流程； (b) 如果發現：停止 ETL 處理流程。 (4) 審計： 當篩檢程式執行完畢後，根據錯誤事件事實表為審計維度表計算整個資料品質得分。 (1) 執行：一系列的篩檢程式按照中繼資料定義的順序執行。 (2) 記錄：篩檢程式所偵測到的每個錯誤記錄在錯誤事件事實表中。 (3) 判斷：當每一篩檢程式執行後，就會在錯誤事件事實表中查詢是否遇到致命的錯誤 (如整批資料遺失)。 (a) 如果沒有發現：持續運行 ETL 流程； (b) 如果發現：停止 ETL 處理流程。 (4) 審計：當篩檢程式執行完畢後，根據錯誤事件事實表為審計維度表計算整個資料品質得分。 持續運行原則 資料清理的指導原則是發現並且記錄存在的資料品質錯誤，而不是跳過資料或者是停止 ETL 過程。因為，資料品質問題在資料倉儲生命週期中是一個不幸並且存在的事實。為儘量使資料清理系統持續運行，必須提供一些預備機制以處理意想不到情況的機制 (如資料缺陷或系統斷線)。資料清理系統也必然需要一些例外處理，例如，如果檢測到過多底層錯誤 (如代碼整批錯誤)，就要採取例外之處理動作。 持續運行原則 Copyright © 滄海書局

5.6 資料一致化 由於終端使用者會從多個分離的事實表組合所需的資料進行應用，所以在清理資料後，就需對資料進行一致化處理，解決不同來源資料間資料不一致的問題，使資料能夠在資料倉儲中一起使用。

一致化維度與一致化事實 一致化維度對於每個被關聯的事實表來說都是相同的，為所有事實表提供統一的資料內容和屬性描述，這樣才可以將不同事實表間的資料進行資料整合。一個好的一致化維度常需要融合多個舊系統的資源及外部資料來源。 一致化維度 在確定一致化維度的過程中同時也確定了標準的事實定義。建立一致化維度與一致化事實是一個合作的過程，因為每一張事實表的管理員都必須就使用一致化維度來達成一致化的事實。 一致化事實 Copyright © 滄海書局

2. 比對 (去重複) (Matching drives deduplication) 5.6.1 建置一致化維度 3. 篩選 (Surviving) 篩選是指整合一組完成比對的資料作為一致化維度的主資料，成為最終交付的一致化維度，提供最豐富完整之維度資料。 2. 比對 (去重複) (Matching drives deduplication) 指刪除重複的標準化資料。 1. 標準化 (Standardizing) 標準化的目的是使不同資料來源採用相同的資料編碼方式、資料格式等，為下一步比對 (去重複) 步驟作準備。 Copyright © 滄海書局

支援欄位值一致化之資料模型 參考圖 5-10，為一個支援欄位值一致化的中繼資料表範例。其記錄每 個資料表和它相關欄位的中繼資料。該一致化欄位參照事實表中 (Conformed column reference fact table) 記錄了標準的一致化欄位值定義，而欄位維度 (Column dimension) 包含來源資料中對應欄位之原始值。舉例來說，如果 Male 和 Female 是性別維度一致化的目標值 (Conformed value)，則事實表將 會在來源系統 A 中用 M 關聯 Male，用 F 關聯 Female；在來源系統 B 中用M 關聯 Male，但是用 W 關聯 Female；而在來源系統 C 中，則用 Man 關聯 Male，用 Woman 關聯 Female。 Copyright © 滄海書局

支援篩選之資料模型 參考圖 5-11，為用以支援篩選的中繼資料表。來源至目標對照表 (Survivorship source to target map) 用來記錄來源資料欄位 (Source column key) (已清理但沒有一致化的欄位) 與目標資料欄位 (Target column key) (一致化 維度的欄位) 之間的資料映射關係。篩選分組表 (Survivorship block) 則把 上述的映射 (來源資料欄位到目標資料欄位) 進行分組，每一組表示其所含 之資料映射關係相互共存 (如前面所提到地址欄位的問題)。該優先排序表 (Survivorship block source rank) 記錄每一篩選分析對應來源系統的優先順序。 根據此中繼資料，就能使用動態 SQL 為每一篩選分組按照該來源系統排序， 查到非空之最適值。 Copyright © 滄海書局

5.6.2 建置一致化事實 維度表管理者 (The dimension manager) 負責管理和發布所有一致化維度。一致化維度必須是集中管理的物件，當維度表管理者發布了維度的新版本時，事實表提供者有義務立刻更新本地端的維度表，並且需配合嚴格的版本控管。 維度表管理者 (The dimension manager) 事實表提供者管理一張或者更多的事實表，並且負責終端使用者如何連結使用。他們必須使用維度表管理者提供的一致化維度，來建構其所負責事實表之資料。 事實表提供者 (The fact table provider) Copyright © 滄海書局

當資料完成一致化步驟後，最後一個步驟就是將完成一致化之維度表與事實表交付給使用者 5.7 資料交付 當資料完成一致化步驟後，最後一個步驟就是將完成一致化之維度表與事實表交付給使用者

一致化維度表的交付步驟 (1) 增加新的維度資料到一致化維度，產生新的代理鍵。 (2) 為第二型緩時變維度 (保留歷史) 的變化，增加新維度記錄到現有一致化維度中，並生成新的代理鍵。 (3) 為第一型緩時變維度 (覆寫) 和第三型緩時變維度 (替換實體) 的變化，修改維度資料，但不改變代理鍵。 (4) 一旦完成修改，則更新維度的版本號。 (5) 為所有的事實表提供者同步發布修訂後的一致化維度。 Copyright © 滄海書局

一致化事實表的交付步驟 (1) 接收或者下載更新一致化維度。 (2) 處理標記為新且當前在用的維度資料，改映射到代理鍵的當前鍵值。 (3) 處理標記為新並且為遲到的維度資料。 (4) 用正確的代理鍵替換自然鍵後，把所有新事實資料添加到事實表中。 (5) 修改所有的事實表。包含為了修正錯誤、補填累積快照事實表的日期和遲到維度資料更新所引發的修改。 (6) 刪除無效的彙總 (參考 5.7.2 節)。只有當第一型或第三型緩時變維度的變化發生在彙總的目標欄位上)，或者歷史事實資料在第 (5) 步中被修改的時候，當前的歷史彙總才會變得無效，其他屬性的改變不會使彙總無效。 (7) 重新計算受影響的彙總。如果維度的新版本沒有更新，則只有當載入了新的事實資料時才需要重新處理。如果維度的版本號有更新，則在第 (6) 步中的刪除彙總事實表後，整個的歷史彙總將重新計算。OLAP 系統會自動處理這些步驟。 (8) 確保所有基礎資料表和彙總事實表的資料品質，確保正確計算彙總事實表。 (9) 把更新完成的事實表和維度表上線。 (10) 通知終端使用者資料倉儲已被更新。 Copyright © 滄海書局

上載事實表之技巧 1. 管理索引 (Managing indexes) 索引對於查詢來說可以提升性能，但是在資料載入時卻是相反作用。使用了大量索引的表將導致你的處理變得非常緩慢。 1. 管理索引 (Managing indexes) 為了管理或者提高查詢性能，使用分割技術將資料表 (及它們的索引) 在實體上分為一些小的資料表。 2. 管理分割 (Managing partitions) 回溯日誌又叫做重做日誌 (Redo log)，這對交易系統 (OLTP) 來說是非常重要的，但在資料倉儲環境中並不太需要回溯日誌， 3. 省略回溯日誌 (Outwitting the rollback log) Copyright © 滄海書局

事實表彙總技術 彙總技術的用途 提高大型資料倉儲查詢的性能 彙總技術的原理 根據原事實表上另建立一組適當的彙總事實表，提供不同查詢細度之 SQL 指令，選擇最適合之彙總版本進行查詢。(比較圖5-12和圖5-13) 彙總技術的原理 比較圖 5-12 與圖 5-13，提高大型資料倉儲性能最重要的手段是在基礎資 料上建立一組適當的彙總資料。亦即藉由控制維度表的細度 (如產品項目 (小) 改成產品類別 (大))，來改變原事實表事實之細度，並對原事實表之量值進行 彙總計算後，另產生一組細度較大之彙總事實表，可以大幅的減少原事實表 的資料量，作為查詢的可用版本。藉此，就可以提供不同查詢細度之 SQL 指 令，選擇最適合之彙總版本進行查詢。透過建立彙總之技術可以顯著的提高 性能。參 Copyright © 滄海書局

彙總導航架構之設計要點 (1) 針對資料倉儲中重要維度常用的彙總層級，建立多組彙總事實表。 (2) 彙總導航器是中介軟體的一部分，位於用戶端和 DBMS 之間。 (3) 彙總導航器用於將查詢基礎事實表的 SQL 轉化為查詢彙總事實表的SQL。 (4) 彙總導航器使用特殊的中繼資料進行要點 (3) 之轉化，該中繼資料描述了資料倉儲之彙總方式。 彙總導航架構設計之要點如下： Copyright © 滄海書局

ETL系統要透過中繼資料完全瞭解所有資料的細節特性才可以正確的發揮作用。 5.8 中繼資料 ETL系統要透過中繼資料完全瞭解所有資料的細節特性才可以正確的發揮作用。

中繼資料的三層架構 從業務層面上描述資料的涵義，包含： 業務定義 (Business definitions)、 來源系統資訊(Source system info)、 資料倉儲資料字典 (Data warehouse data dictionary)、 邏輯資料映射 (Logical data mappings) 等。 1. 業務中繼資料 (Business metadata) 從技術層面上描述資料，包含： 系統清單 (System inventory)、 資料模型(Data models)、 資料定義 (Data definitions)、 業務規則 (Business rules)、 ETL任務 (ETL jobs)、 資料轉換 (Transformations)、 批次參數 (Batch parameters) 等。 2. 技術中繼資料 (Technical metadata) 從執行層面上輔助執行與記錄結果的資料，包括： 執行結果 (Run results)、 例外處理 (Exception handling)、 即時排程 (Immediate schedule) 等。 3. 處理過程中繼資料 (Process metadata) 1. 業務中繼資料 (Business metadata) 從業務層面上描述資料的涵義，包含業務定義 (Business definitions)、來源系統資訊(Source system info)、資料倉儲資料字典 (Data warehouse data dictionary)、邏輯資料映射 (Logical data mappings) 等用途之中繼資料。 2. 技術中繼資料 (Technical metadata) 從技術層面上描述資料，包含系統清單 (System inventory)、資料模型(Data models)、資料定義 (Data definitions)、業務規則 (Business rules)、ETL任務 (ETL jobs)、資料轉換 (Transformations)、批次參數 (Batch parameters) 等用途之中繼資料。 3. 處理過程中繼資料 (Process metadata) 從執行層面上輔助執行與記錄結果的資料，包括執行結果 (Run results)、例外處理 (Exception handling)、即時排程 (Immediate schedule) 等用途之中繼資料。

ETL各步驟用到的中繼資料 (a) 邏輯資料對照表 (參考 5.4.1 節) (b) 來源系統追縱報告 (參考 5.4.2 節) 資料抽取 (c) 審計維度表 (參考 5.5.2 節，圖 5-7) 資料清理 (a) 支援欄位值一致化之資料模型 (參考 5.5.4 節，圖 5-10) (b) 支援篩選之中繼資料 (參考 5.5.4 節，圖 5-11) 資料一致化 (a) 各類維度表資料模型 (參考第三章) (b) 各類事實表資料模型 (參考第三章) 資料交付 Copyright © 滄海書局

5.9 總結

學習成效檢視 抽取 清理 一致化 提交 設計與規劃流程 資料處理流程 了解資料倉儲資料建置的流程 了解ETL 四個步驟的核心概念 了解一致化維度和一致化事實的重要性，並學習經由標準化、比對、篩選三個階段來建置一致化維度。 一致化 了解提交維度表與事實表的步驟、問題與對策。 提交 Copyright © 滄海書局