靈活高效的數據倉庫解決方案（三）：設計與實現倉庫 ETL 流程

前言

數據集成是數據倉庫的核心概念。ETL（提取、轉換、加載）流程的設計與實現，是構建數據倉庫解決方案的關鍵環節。ETL 流程負責從多個異構數據源抽取業務數據，經過清洗、整合，最終裝載進數據倉庫，為後續的數據分析奠定堅實基礎。

ETL 流程設計原則

雖然具體的 ETL 設計高度依賴所選工具，但系統化的高層設計能顯著提升流程的效率、靈活性與可維護性。在深入設計前，請牢記 ETL 的黃金法則：「ETL 應優化數據，而非修改數據。」若發現需修改業務數據且不確定是否改變其業務含義，務必在啟動 ETL 前與客戶確認。

模組化的 ETL 功能架構

鑑於 ETL 流程的過程化特性及可能涉及成百上千個操作，精確的模組化設計至關重要。數據轉換操作大致可分為六大功能模組：提取、驗證、清理、集成、聚集、裝載。按邏輯順序排列這些模組，可最大程度簡化流程、優化性能並便於後期維護。下圖展示了標準的執行次序：

數據映射表：設計的藍圖

在業務需求分析階段產出的數據映射信息，是指導 ETL 設計的最佳工具。ETL 數據映射表不僅記錄源目標字段對應關係，還可作為與業務方溝通的橋梁。映射表分為實體級與屬性級，包含不同粒度的細節。下表為實體級映射表示例，每個「X」代表通往操作細節或更低級映射文檔的鏈接：

源實體	驗證	清理	轉換	集成	聚集	目標實體
賬戶客戶	X	X	?	X	X	客戶
信貸客戶	X	X	X
借貸客戶	X	?	X
支票賬戶	X	X	?	X	X	賬戶
儲蓄賬戶	X	?	X
信貸賬戶	X	?	X
借貸賬戶	X	X	?

基於 DB2 Data Warehouse Center 的 ETL 實現

DB2 Universal Database Data Warehouse Edition 提供了增強的倉庫功能性能與可用性。DB2 Data Warehouse Center (DWC) 是一款可視化的 ETL 設計與實現工具，內建於 DB2 UDB 中。本節將演示如何使用 DB2 UDB V8.2.1 DWC 設計並實現倉庫 ETL 流程。

創建倉庫控制數據庫

倉庫控制數據庫存儲 DWC 元數據所需的控制表。自 V8.2 起，控制數據庫必須為 UTF-8 編碼，以提供擴展語言支持。若嘗試用非 Unicode 數據庫登錄，將報錯。可使用 Warehouse Control Database Management 工具將元數據遷移至 Unicode 數據庫。

創建並啟動新控制數據庫的步驟：

1. 確保 DB2 Warehouse 服務器及相關服務已啟動。在管理窗口填寫控制數據庫名、模式名（IWH）、用戶 ID 及密碼並創建。若有舊版倉庫，亦可遷移至當前版本。
2. 用步驟 1 相同的憑證登錄 DWC（見圖 2）。若控制數據庫為遠程，需編目節點與數據庫。

提示：DWC 登錄窗口支持在多個控制數據庫間切換，極利於多項目、多開發人員共享同一服務器的場景。

定義代理站點

倉庫代理管理數據源與目標倉庫間的數據流，支援 AIX、Linux、iSeries、z/OS、Windows 及 Solaris 等平台。代理通過 ODBC 或 DB2 CLI 與數據庫通訊。少量代理即可處理大量遷移，代理數量取決於連接配置與數據量。若需並行運行，可生成多個代理實例。

代理站點是安裝代理軟件工作站的邏輯名稱，區別於 TCP/IP 主機名。一個工作站可定義多個代理站點。搭建數據倉庫時，必須定義用於訪問源/目標數據庫的代理站點。DWC 默認使用本地代理，但也可定義遠程代理站點（見圖 3）。

定義倉庫源與目標

倉庫源指定為倉庫提供數據的表與文件。DWC 支持主流平台上的各類關係與非關係數據源（見圖 4），配置連接極為便捷。建立連接並確定源表後，即可在 DWC 中定義源；若使用遠程源，需在代理所在工作站編目該數據庫。定義過程因源類型而異，圖 5 展示了定義關係數據源的示例。

倉庫目標指包含已轉換數據的表或文件，可為其他目標（如部門級數據集市）提供數據。創建目標有兩種方式：從現有表/文件導入，或由倉庫系統生成。定義目標表時可指定是否由 DWC 創建，以及是否屬於 OLAP 星型模型中的維度/事實表（見圖 6）。

定義主題領域、流程與步驟

倉庫步驟定義單個操作：如何移動、轉換數據。DWC 支持多種步驟類型：SQL（插入/更新/替換）、文件（FTP/導入導出）、DB2 程序（導出/裝載/重組/統計）、倉庫轉換器（清理/鍵表/時間表/翻轉透視）、統計轉換器。每個步驟包含：源數據鏈接、輸出表定義、填充機制（SQL/程序）、處理選項與調度。

倉庫流程由一系列步驟組成，執行特定轉換與移動，可產出單表或總結表集合。主題領域劃分邏輯業務域相關的流程（如銷售、營銷）。圖 7 展示流程定義界面。

流程與步驟的生命週期模式

DWC 通過三種模式管理步驟開發：開發、測試、生產。模式決定步驟是否可修改、是否按調度運行。升級即將步驟推向更高模式（開發→測試→生產）。

• 開發模式：創建後默認處於此模式，可修改任意屬性。目標表不在目標倉庫中，需升級至測試模式才能運行測試。
• 測試模式：升級時 DWC 創建目標表。之後只能做不破壞表結構的修改（如增列不可刪列）。可單獨運行步驟，不按自動調度。
• 生產模式：激活調度與任務鏈，表明步驟定型。僅可修改不影響數據的設置（調度、描述等），不可修改參數。

步驟升降級可在 DWC 內操作，亦可通過外部觸發器程序批量自動化管理。

外部觸發器程序

外部觸發器由服務器腳本 (XTServer) 與客戶端腳本 (XTClient) 組成，用於升降級步驟、啟動流程/步驟，在 ETL 開發測試中極為實用。觸發步驟前，需在步驟屬性中勾選「Run on demand」。Windows 平台升級步驟的腳本示例：

CLASSPATH=%CLASSPATH%;%DB2PATH%toolsdb2XTrigger.jar;%DB2PATH%Javacommon.jar
"%DB2PATH%javajdkbinJava" db2_vw_xt.XTServer <port_number>  (默認 11004)
"%DB2PATH%javajdkbinJava" db2_vw_xt.XTClient <server> <port> <DWC_user> <password> <ETL_step> <command> [<wait_for_result>]

command 可為：populate/run step、promote to test/production、demote to test/development、populate/run process、verify server。wait_for_result：1=等待完成返回碼，0=不等待。

核心 ETL 技術深度解析

數據提取策略

提取方式主要有完全刷新與增量更新。完全刷新對中間庫做全量複製，可能替換倉庫內容、添加時間點快照或生成修改記錄。增量更新僅捕獲變更數據，實現方式與源系統緊密相關，屬逐案實現。DWC 支持多種捕獲方法：SQL 查詢、FTP、文件導入/裝載、數據復制。

大數據量提取優化

Select and Insert SQL：增量提交選項可控制提交頻率，防止日志填滿導致失敗。指定每提交處理的行數（約為 16 的倍數），代理循環選擇插入並提交直至完成。圖 8 為相關選項界面。

倉庫負載實用程序：DB2 Load 轉換器從定界文件高速裝載數據，默認不記錄數據日志，無需擔心日志空間。裝載後表空間處於暫掛狀態，需備份方可使用；若勾選「保存輸入副本」，裝載即刻可用。圖 9 展示性能調優選項。

倉庫數據復制

DB2 復制服務與 DWC 緊密集成，廣泛用於完全刷新與增量更新。增量更新從日志文件與時間取樣源捕獲變更。DWC 支持定義五類復制步驟：

• 用戶副本：源表完整壓縮副本，目標有主鍵供更新。
• 時間點：特定時刻的壓縮副本，目標含主鍵與時間戳。
• 基本聚集：歷史表，每訂閱周期追加新行，基於基表 SQL 列函數計算。
• 更改聚集：歷史表，基於更改數據表 (CD 表) 的列函數計算。
• 中間表：包含已提交事務數據的目標表，即一致性更改數據表。

復制配置關鍵步驟

1. 準備源數據庫：設置 LOG_RETAIN=RECOVERY 保留日志，備份解除 BACKUP PENDING，檢查 LOGPRIMARY、LOGFILSIZ、LOGSECOND。
2. 創建 Capture/Apply 控制表：在 Replication Control Center 分別對倉庫控制庫與目標庫創建，指定源表數量、目標表數量、應用頻率、日交易量等參數。
3. 註冊源表：在 Replication Center 註冊需捕獲變更的表。
4. 導入源/目標表至 DWC：若未列出則作為倉庫源/目標導入；已列出的目標表需重新導入以識別復制註冊。
5. 定義復制步驟：類似普通步驟，指定五類復制類型。若鏈接現有目標表，需先啟用「DWC created this table」連接後再取消。
6. 創建密碼文件：使用 asnpwd 實用程序生成加密的 <applyqual>.pwd，放置於 VWS_LOGGING 目錄。
7. 啟動 Capture 程序：從 Replication Center 啟動，確保日志目錄空間充足。
8. 升級步驟至測試/生產：升級時自動創建訂閱集與 Apply 程序，降級時刪除。

圖 10 展示 User Copy 復制步驟屬性配置。

復制故障排查

日志默認在 C:\Program Files\IBM\SQLLIB\LOGGING。Capture 錯誤查 DB2.<source_db>.<schema>.CAP.log；Apply 錯誤查 <ApplyName>.trc 與 DB2.<target_db>.<ApplyName>.APP.log。

數據驗證與清理

業務分析階段產出的數據質量假設，劃分雙方責任：方案商負責清理增強，客戶負責源端修復與語義解釋。假設應納入合同，作為進度協商依據。即使客戶履責，源端仍可能產生劣質數據，故需在 ETL 中實現驗證過濾模組拒絕異常數據。驗證包括：域值有效性、行級/表級/跨表上下文有效性、關係完整性（外鍵檢查）等。拒絕數據暫存客戶可訪問位置，並通知客戶；修復後重入 ETL 周期。DWC 的 Clean Data 轉換器可用於基本驗證，複雜驗證可編寫 SQL 步驟。

數據清理旨在使有效數據更精確、有意義，任務包括：多源合併、域轉換同步、類型格式轉換、數據分離。常見案例為姓名地址多源合併與重新同步。Clean Data 轉換器支持多種清理類型：

• 查找替換：按規則表映射替換值。
• 數值截斷：超出範圍值用邊界值替換。
• 離散化分箱：按規則表區間離散化。
• 帶空值處理的透傳：指定輸出列、替換空值或拒絕空值寫入錯誤表。
• 大小寫轉換：統一字符大小寫。
• 無效值編碼：用指定值替換規則表外的值。

大多數類型提供「匹配選項」窗口控制匹配行為。

數據集成與聚集

數據集成將多源數據統一為分析接口，是倉庫轉換最關鍵步驟，可應用業務規則與轉換邏輯。源可來自多源（涉及連接操作）或單源（域值合併轉換），產出易理解的新實體/屬性。數據聚集以總結形式表達信息，常見於業務報表。多維模型中聚集路徑是維度設計重點。建議從數據集市構建報表，若直接面向倉庫報表，需將聚集表與核心模式隔離，避免報表需求變更影響倉庫結構。

裝載目標表與拒絕處理

裝載是 ETL 最後一步。兩大基本方法：SQL IUD（日誌記錄、可恢復）與批量 Load（高速、適合大數據量）。選擇取決於業務環境，應在設計文檔中明確。拒絕數據發生於違背質量假設、參考完整性、集成規則時。拒絕數據存放雙方約定位置，修復屬客戶職責，需 DBA 與用戶協作，修復後重入 ETL。

執行次序與調度

並非所有流程可並行。影響次序因素：實體依賴（父表先裝載）、屬性依賴（計算依賴）、ETL 模塊邏輯順序（驗證清理先於集成）、集成業務規則依賴。DWC 設計階段用鏈接工具定義步驟捷徑控制次序（見圖 7）。

運行方式：隨需、指定時間、單次、重複（如每周五）、級聯（前一步結束啟動下一步）、完成觸發（無論成敗）。調度與任務流在 Scheduler 記事本的 Process Task Flow 頁面配置。Work in Progress 窗口監控所有運行/計劃步驟與流程（見圖 11），失敗可查日志定位問題。

元數據管理與原型驗證

ETL 元數據管理

元數據涵蓋設計、執行歷史、拒絕記錄、調度、增長存儲、訪問日志等。DWC 支持導出/導入：XML、CWM、OLAP Integration Server 元數據。導出默認包含依賴對象，可選排除源定義或依賴步驟/級聯。圖 12 為導出界面。導入時對象分配至指定安全組。支持導入類型：XML、CWM、ERwin、MQSeries、Trillium。

注意：導入導出版本相關，跨版本遷移不可直接用導出文件。大量操作消耗資源，建議增加 DB2 應用程序堆大小至 8192。服務器名、用戶名導出，密碼不導出，導入後需重新配置。

設置原型驗證

首個業務域實現後，應建立原型驗證：技術棧、設計實現、業務需求、倉庫性能、技術 QA。讓用戶實地操作測試目標，記錄缺口，執行功能驗證。檢查設計模型增刪，確保可理解。可行的進入實現，不可行的退回需求澄清重建模。

性能調優與安全

數據倉庫性能調優

倉庫性能受系統、網絡、應用、數據庫、報表、人員綜合影響。從終端用戶視角（查詢負載、響應時間）切入。負載動態變化：新用戶、焦點轉移、業務週期、數據增長。唯讀倉庫中索引使用更自由，應按訪問模式與查詢需求優化。隨需求變化，定期評估：廢棄無用索引、創建新索引、評估聚集引用、調整並行限制。調優是連續改進過程：從差響應投訴/觀察開始，確定期望響應時間，特徵化負載（分組、資源需求），然後循環：監控分析瓶頸、記錄影響因素、識別未達標查詢、用 Query Performance Monitor 剖析制定行動計劃（含權衡）、持續調整至全部達標。

數據倉庫安全性

倉庫包含敏感業務數據，安全設計常被忽視，隨源與主題增加而複雜。關鍵考量：

• 數據訪問：決策支持場景，只讀訪問。
• 終端用戶：不同用戶授權不同子集，非全開放。
• 分析方法：標準報表易控制（預定義報表權限）；即席 OLAP/挖掘通常通過分配數據庫級集市/子集給用戶組實現。
• 性能平衡：限制性安全方案犧牲性能，需平衡。
• 設計分層：兩層架構假設用戶只訪問友好的數據集市，而非複雜倉庫結構，大幅簡化安全。
• 工具安全：DWC 安全結構獨立於 DB/OS，包含倉庫組與倉庫用戶。組授權功能權限，用戶通過組獲取對象訪問權。倉庫組/用戶無需與控制庫 DB 用戶/組匹配。圖 13 展示用戶管理結構。

交付成果與結語

關鍵交付物：業務需求、數據質量假設、架構文檔、邏輯/物理模型、集成規範、ETL 設計文檔、測試/部署計劃與結果、客戶教育文檔、技術支持文檔。

本系列介紹了商業智能，提供了基於 IBM DB2 Data Warehouse Edition V8.2.1 交付靈活低成本倉庫方案的基方法，適配中型市場客戶。從首次會面到生產部署，涵蓋 ETL、設計實現、安全性能等實用思路。IBM 為頂級 BI 提供商，BI 為關鍵戰略，方案範圍從部門級數據集市到 PB 級企業倉庫。本文剖析了顧問/方案商交付 IBM BI 戰略部分方案所需投入。