Project REAL:商務智慧 ETL 設計練習

發佈日期: 2005 年 9 月 1 日 | 更新日期: 2007 年 1 月 5 日

由 作者:Erik Veerman

SQL Server 技術文件
技術檢閱者:Donald Farmer、Grant Dickinson
協力廠商:Intellinet
適用於:SQL Server 2005

摘要:閱讀 SQL Server 2005 Integration Services (SSIS) 運作的資訊。使用 SSIS 實作 Project PERL 商務智慧參考,藉此示範在真實世界的鉅量資料環境中進行擷取、轉換、載入 (ETL) 處理。這項 ETL 解決方案支持多兆位元組資料倉儲,並包含在大型資料倉儲開發案中資料處理、組態和管理的典型機制。

本頁內容

簡介
資料分析
SSIS 開發環境
結論

簡介

成功的商務智慧 (Business Intelligence,BI) 應用程式需要靠強固的工具來執行。如果開發人員和管理人員具備成功實作的知識、瞭解何謂最佳作法,就能使應用程式的建構過程更順利。目前 Microsoft 及許多協力廠商正透過 Project REAL,根據真實客戶案例來建立參考實作,藉此發掘以 Microsoft® SQL Server™ 2005 為基礎的 BI 應用程式的最佳實作方法。這表示我們納入客戶資料,用以模擬客戶在部署期間所要面對的相同問題。這些問題包括:

  • 結構描述 (包括關聯式結構描述與 Analysis Services 所用的結構描述) 的設計。

  • 資料擷取、轉換與載入 (ETL) 程序的實作。

  • 專為報告與互動分析設計與部署客戶前端系統。

  • 為生產而調整系統大小。

  • 持續不間斷的系統管理與維護,包含遞增更新資料。

我們藉由實行真實的部署案例,徹底瞭解運用工具的方式,希望能因應大型企業在實際部署過程中會面臨的所有困擾。

本文討論 Project REAL 在 SQL Server Integration Services (SSIS) 擷取、轉換和載入 (ETL) 方面的設計。架構上採美國連鎖書店 Barnes & Noble 的 ETL 設計,以 SSIS 從有到無開始建構,這也是第一個採用 SSIS 實際作業的 ETL 實作。由於本文所述之解決方案並不是「資料轉換服務 (DTS)」或其他 ETL 工具設計出的可升級模式,因此這裡採行的方法大多異於 DTS 內傳統的 ETL 架構。這項解決方案的目的在於突破傳統,設計出一個既能展現出一般 ETL 設計最佳作法,又能利用 SSIS 架構的應用程式的 ETL 程序。我們將透過這篇白皮書,說明每種情況下的設計決策,以及實作 Project REAL SSIS 的細節。

如需 Project REAL 概觀,請參閱《REAL 專案:技術概觀》(英文) 白皮書的連結。在進行Project REAL 的期間,許多文件、工具與範例也會相繼而生。如需最新資訊,請查閱網站:

http://www.microsoft.com/sql/bi/ProjectREAL

Project REAL 是 Microsoft 與數位協力廠商在商務智慧領域合作的心血結晶,包括 Apollo Data Technologies、EMC、Intellinet、Panorama、Proclarity、Scalability Experts 及 Unisys。Project REAL 中的商務案例以及來源資料組由 Barnes & Noble 慷慨提供。

附註:此份白皮書為草稿。其中包含我們從 SQL Server 2005 早期 Communicty Technology Preview (CTP) 建構經驗中所得來的建議最佳實作方法。此份白皮書的內容到發行當日為止仍正確無誤。此文件中所描述之產品功能或最佳作法未來有可能會有所異動或改進。

Project REAL ETL 目標

不管是哪種商務智慧 (BI) 系統,ETL 程序都是為了滿足報告和分析需求而生。所以在實作 ETL 時要謹記它的任務就是支援。然而這並不表示 ETL 是個不重要的配角,因為要報告的資料最後還是會直接透過 ETL 程序來處理。ETL 牽涉的層面包括處理的時機、效能和正確性。ETL 的設計也須考慮到支援性、管理性、延展性和擴充性。在現實世界的系統中,ETL 會受到各種未知因素和突發情況的影響,所以 ETL 程序必須要能靈活地因應變化並達到穩定系統的最終目的。

在 Project REAL 中,我們針對這幾項重點,列出以下 ETL 設計的主要目標:

  • ETL 管理:為了提供管理支援,設計為允許追蹤和報告 ETL 中繼資料。如此就能清楚掌握處理的狀態,以便提供資訊、排解疑難,同時區隔問題和對症下藥。

  • 動態組態:目的在於支援企業系統的發行和發佈核心元件功能。其中還牽涉到因應商務與技術需求變化的設計變通性,以及適合大型的支援與開發團隊作業的環境。

  • 平台整合:設計出能夠與商務智慧解決方案多元層面互動的解決方案,兼顧安全、基礎架構、關聯式與 OLAP 架構,以及擷取資料的報告和分析工具。

  • 效能:Project REAL 解決方案相當重視效能,因為資料倉儲中必須處理和管理的資料量很驚人,總計高達數兆位元組。

摘要

本文著重探討幾項設計原則、設計過程中的心得,以及解決方案的全局架構。本白皮書可以當作大原則參考,並提供幾個解決方案的設計重點。日後隨著更多新的解決方案在 SQL Server 2005 平台上逐漸落實及開發,我們會發表更詳盡的內容。未來將推出更多白皮書,進一步解說概念、加強效能設計,甚或示範一些更理想的設計範例。本文以 SSIS 為基礎,可作為商務智慧 ETL 的有力參考。商務智慧應用程式建構人員在計畫或進行重新設計、升級、實作 ETL 時,可以參考這份白皮書。

SSIS 功能其實不僅是處理 ETL,還能進行系統整合、資訊管理、資料轉換等多重功能。本文只針對 SSIS 中與 ETL 程序相關的核心部份來討論本產品的一些面向。

綱要

本文中的各個範例都與 Project REAL 實作有直接關係。我們有意挑選出在套用到 ETL 程序時,與 SSIS 的特定層面相關的重點範例。這些範例能傳達前文所述和通用 ETL 案例的一些目標,包括:

  • SSIS 開發環境

  • ETL 稽核和記錄

  • 專為屬性和資料來源管理設計的動態組態

  • 標準和特殊案例的維度處理

  • 維度關聯和事實資料表更新的事實資料表處理

  • 資料處理架構設計

  • 資料處理最佳化技巧

以上所有範例已經在 Project REAL SSIS 解決方案中實作,連續 90 天成功執行每日與每週產生的鉅量資料。處理的資料包括兩年來假日零售量高峰的一部份資料,以現實世界的範例而言,其穩定性與可靠性已獲肯定。如前所述,Project REAL ETL 中所使用的「實際」SSIS 解決方案已被 Barnes & Noble 採用,自 2004 年 11 月運行至今。近期或未來將推出許多這類的範例及封裝,以提供實作詳情審閱。相關資訊將於 Project REAL 網站陸續刊出,在業界研討會中也會提出封裝,以便示範 Project REAL SSIS 的設計。

資料分析

Project REAL 的 ETL 雖然以零售系統為對象,但是此參考專案能夠代表許多商務案例的資料擷取需求。常見的 ETL 程序就是一組每日處理作業,用來擷取來源資料的變更和添加內容,然後每天透過系統來進行處理。另外還要執行一組每週處理作業,用來管理維度模型的每週庫存快照粒狀資料單位 (Grain)。資料分析包括每日擷取交易資料、維度更新、以及每週管理庫存事實資料表。

事實資料表

關聯式模型是一種典型的星狀結構描述設計,包含銷售和庫存這兩個主要的事實資料表。銷售交易資料會每天蒐集,代表各家零售店 (包括網路訂單) 的產品購買詳細資料。好幾百萬筆交易得添加到銷售事實結構內,而大部分的銷售記錄來自前一日的銷售資料。此外,在系統中還有少數新的銷售資料實際上屬於晚到的過去銷售資料。所有的銷售結果必須依每日粒狀資料單位追蹤,因此 ETL 程序在設計上也必須允許整日進行多重資料處理。追蹤的主要單位是各個項目層次的數量與銷售額。

庫存結構的設計是依每週粒狀資料單位的標準快照事實資料表,其中庫存狀態每日更新,但是以每週遞增的方式進行歷程管理。系統會依項目層次細分,同時追蹤店家和物流中心雙方的庫存,因此每週都有數千萬列的資料,每日則需要變更數百萬筆資料。報告的主要目的是要查看庫存和銷售的趨勢,避免發生缺貨的情況。因此,除了標準的「在庫量」庫存,尚須追蹤「庫存天數」現況,以便查詢店家或物流中心每週粒狀資料單位的項目庫存天數。接近週末時,ETL 必須處理一項繁重的作業,就是複製和啟動庫存水準,以便為新的一週作準備。

維度資料表

支援事實資料表的維度有幾項特點,所以設計上相當有趣,更突出了 SSIS 的幾項特色。產品維度有好幾百萬的成員,且包含標準的 Changing 維度和 Historical 維度。但是它也要求歷史追蹤的屬性和階層變更只在銷售進行後啟動。本文將討論這些因素如何影響 ETL。除了產品維度,這裡還牽涉到其他好幾個典型的零售維度。為因應來源資料管理的方式,所有的維度都規定一旦事實處理過程中缺少某個維度成員,就要在維度中添加一個包含相關業務索引鍵的替代記錄,直到取得完整的資料來源以執行完整更新為止。這稱為「推斷成員 (Inferred Member)」,將影響到 ETL 的處理作業。有些維度也是直接來自主要交易或庫存來源資料表,所以在添加至事實資料表時需要特別處理。

考慮到維度的複雜性,整體 SSIS 處理展現出此工具的彈性與延展性,並試圖為 SQL 2005 平台上建置的許多 ETL 設計提供有力的參考。

SSIS 開發環境

Business Intelligence (BI) Development Studio 建構在 Microsoft Visual Studio® 2005 (VS) 平台上。相較於 SQL Server 2000 以 Enterprise Manager 為基礎的 UI, BI Development Studio 雖然在外觀上經過相當大的翻新,但是資料庫管理員 (DBA)、資料建構人員、開發人員等應該很快就能上手。因為新工具剔除 VS 讓人望之卻步的一些因素,簡化的 UI 更貼近商務智慧開發人員的需求。

在 Project REAL 中,我們拿一個商務智慧解決方案來介紹 Recurring ETL 專案。這個專案會以遞增的方式執行所有 SSIS 封裝。[圖 1] 顯示 [方案總管] 與共享資料來源和 SSIS 封裝。封裝的名稱代表其功能。這裡有好幾種封裝,第一個也是最簡單的是維度封裝。每個源自本身來源實體的維度都有自己的封裝。每個事實資料表封裝的設計都很類似,但是會根據各自的週期性排程 (每日或每週) 來命名。也就是說,每日和每週群組有各自的封裝,因為兩者間的商務邏輯不同。例如,Fact_Daily_Store_Inventory 封裝和每週執行的 Fact_Weekly_Store_Inventory 封裝處理的工作就不一樣,雖然使用的事實資料表一樣。

Cc645519.realetldp01(zh-tw,TechNet.10).gif

[圖 1]

為了配合處理,這時會顯示第三組封裝,稱為載入群組封裝。這些封裝並不包含處理商務邏輯,主要功能在於處理前述的維度和事實封裝的工作流程配合,以及一些應用程式的稽核與重新啟動的能力。[圖 2] 顯示載入群組封裝的範例。「執行封裝」工作用來執行子維度和事實封裝,「控制流程」則用來處理工作流程與一些處理工作的平行處理。其他工作則是用來輔助處理的稽核與重新啟動,本文稍後會再加詳述。

Cc645519.realetldp02(zh-tw,TechNet.10).gif

[圖 2]

原始檔控制整合

建構在 VS 上的 BI Development Studio 具有與原始檔控制整合的好處。REAL 的共用開發環境運用 Microsoft Visual Source Safe® (VSS) 來預防開發「死結」(指兩個以上的開發人員同時使用相同處理序),當然其他工具也能做到這點。VVS 標準功能包括歷程記錄與備份功能,簽入簽出封裝、資料來源或檔案的鎖定功能,以及強調差異處的版本比較功能。大部分的來源檔控制功能可在 [檔案] > [原始檔控制] 功能表中取得 (如 [圖 3] 所示)。

Cc645519.realetldp03(zh-tw,TechNet.10).gif

[圖 3]

使用 Visual Source Safe 或其他原始檔控制應用程式實作原始檔控制後,只要在封裝或資料來源上按一下滑鼠右鍵,就可以存取 BI Development Studio 中許多與物件有關的功能 (如 [圖 4] 所示)。

Cc645519.realetldp04(zh-tw,TechNet.10).gif

[圖 4]

命名與配置作法

封裝配置、工作命名和註解的慣例值得花點篇幅討論。為了保持一致性,所有工作和轉換的命名方式如下:開頭是工作或轉換類型的 3 或 4 個字母英文縮寫,緊接著 3 或 4 個描述物件功能的英文字。由於記錄詳細資訊是根據物件名稱來追蹤,因此此命名方式對稽核和記錄作業有很大的幫助。封裝配置一般先往下展示,然後往右分支,表示工作或轉換從主要控制流程和資料流程中分出來。註解能描述每個工作的細節,有助於記錄封裝內容。

Cc645519.realetldp05(zh-tw,TechNet.10).gif

[圖 5]

ETL 稽核和記錄

自訂稽核步驟會透過父工作流程封裝和子封裝來整合到封裝結構設計,以便追蹤高階的執行詳細資訊。其中包括封裝啟動、結束、失敗次數以及輔助資料量驗證與處理的資料列計數。SSIS 本身會提供各種記錄提供者類型的詳細封裝執行記錄。這些詳細記錄檔項目是由事件驅動,並會在選定的提供者目的地進行反正規化 ?(Denormalize)。舉例來說,如果選定某個資料庫作為記錄提供者,則所有記錄項目都會插入單一資料表內。事件是引擎在整個執行過程中的參考點,如「警告中 (On Warning)」、「驗證中 (On Validation)」、「執行中 (On Execute)」、「前置執行中 (On Pre Execute)」、「後置執行中 (On Post Execute)」。[圖 6] 特別顯示記錄事件詳細資料選取器。

Cc645519.realetldp06(zh-tw,TechNet.10).gif

[圖 6]

記錄事件中的每筆記錄都與執行工作或轉換所在的相關封裝的執行識別碼 (Execution ID) 有關聯。執行識別碼是每次執行封裝時產生的唯一專屬 GUID。如您所料,當 SSIS 記錄在最底層的粒狀資料單位啟動時,便會針對每個封裝產生數百甚至數千筆項目。SSIS 記錄相當詳盡,是疑難排解時很有用的參考。但是,如果對 SSIS 事件瞭解得不夠透徹,或缺乏簡潔的方法比對封裝執行識別碼與引擎執行的特定封裝,就很難蒐集或瞭解這些資訊。

Project REAL 的目的在於提供與商務智慧有關的特定執行稽核功能,強化內建的記錄提供者;同時利用記錄功能,提供詳細的深入剖析 (Drill-down) 報告。稽核結構主要針對下列功能而設計:

  • 封裝和載入群組關聯與識別。

  • 新增到倉儲結構的歷程資料行。

  • 資料列數驗證稽核。

  • 具備深入剖析能力的 ETL 程序報告。

封裝和載入群組追蹤。

要支援和管理商務智慧解決方案,就必須瞭解 ETL 程序使用的封裝搭配。為此需建立更高層級的追蹤資料表,以便與所有已執行的相關封裝產生關聯。此外,還需在單一封裝執行的粒狀資料單位處建立另一個資料表。雖然這和 SSIS 的記錄功能似乎重複,但是它可用來直接對應內建記錄提供者的執行識別碼與更高層級之執行工作流程的封裝名稱和群組處理識別碼。由於在封裝稽核資料表中,每個封裝執行只有一筆記錄,因而能簡化報告,實現深入剖析報告的功能。

Project REAL 應用程式稽核主要以「執行 SQL」工作實作。就工作流程封裝而言,「控制流程」的第一個和最後一個步驟負責管理載入群組稽核。在這個載入群組封裝範例中,您會看到「控制流程」內負責這個作業的第一個和最後一個步驟是圈起來的。

Cc645519.realetldp07(zh-tw,TechNet.10).gif

[圖 7]

封裝工作流程稽核下一層是封裝層追蹤,採用的設計與第一個和最後一個「控制流程」步驟雷同。由於載入群組封裝本身也是一個封裝,所以第二個和倒數第二個步驟也會稽核「執行 SQL」工作 (如 [圖 7] 所示)。在維度或事實資料表封裝內,它們是第一個和最後一個步驟。

稽核的另一個重點在於快速指出錯誤。SSIS 裡面就具備一個很棒的功能,稱為「事件處理器控制流程 (Event Handler Control Flow)」,可以從封裝 UI 的第三個索引標籤中取得。OnError 事件處理器就是用於這個目的,可在封裝層定義,只要封裝內出現錯誤就加以攔截。

Cc645519.realetldp08(zh-tw,TechNet.10).gif

[圖 8]

此外,我們還使用一組封裝變數,在這些「執行 SQL」工作中追蹤資料庫與封裝間的中繼資料。主要變數為系統變數 system::PackageExecutionIDsystem::PackageName,以及使用者變數 user::ETL_Load_ID,這是資料庫內產生的識別碼,會傳回給封裝。這些變數也會使用 SSIS 的「父變數組態 (Parent Variable Configuration)」功能,從父封裝傳給子封裝。

新增到倉儲的歷程

批次識別碼 ETL_Load_ID 不僅可以繫結稽核中繼資料以達到報告和隔絕目的,還可以用在資料倉儲中,協助識別記錄來源。每個維度和事實記錄都來自特定的資料載入,而這個資料行就是用來識別該載入。這項功能有利於資料歷程和驗證目的,發生資料毀損時也可以用來手動修正資料。

一旦擷取來源資料後,就會使用 Derived Column 轉換功能,立刻在資料流程內新增批次識別碼。因此,任何下游轉換都可以利用這個資料行進行更新、插入和追蹤,而且在記錄內加入此中繼資料不會產生太大的系統負荷量。

Cc645519.realetldp09(zh-tw,TechNet.10).gif

[圖 9]
資料列計數追蹤

資料驗證是 DBA 的強心劑,商務智慧解決方案整合了 DBA 來進行管理和疑難排解,同時加強使用者團體的信心。因為如果使用者不信任資料,那麼解決方案的價值就會大打折扣。Project REAL 利用資料列計數當作資料驗證的一種方法。雖然這只是諸多驗證方法的一種,但當作第一環驗證的確有其價值所在。

我們使用 Row Count 轉換,在資料流程內實作資料列計數稽核,也就是計算由此傳來的資料列數目,然後將數值記錄在預先定義的使用者變數內。資料列計數轉換的好處之一在於,它所需的效能負荷與資源不多。那麼,在所有的維度和事實封裝的核心資料流程中,資料列計數轉會插入到每個來源之後、每個目的地或 OLE DB 命令之前,以及資料流程中的最高點處。計數結果會放在不同的變數內,在「資料流程」轉換後立即進行「執行 SQL」工作,藉此在資料庫中保留計數結果。[圖 10] 顯示在「資料流程」中實作這些資料列計數轉換的情況。

Cc645519.realetldp10(zh-tw,TechNet.10).gif

[圖 10]
ETL 報告

我們設計了一系列的連結 Reporting Services 報告,其中整合稽核、驗證和記錄功能,以便匯集所有資訊並為管理人員提供有系統的資訊,同時也可當作開發人員排解疑難的工具。回顧一下,如果您剛才讀過稽核討論的部分,對於用來關聯中繼資料的支援結構描述應該不陌生。結構描述使用四個主要資料表,其中三個是使用者定義的資料表,並與內建的 SSIS 記錄資料表整合。

Cc645519.realetldp11(zh-tw,TechNet.10).gif

[圖 11]

Project REAL 的 ETL 報告以這些結構為基礎,提供高層的載入群組執行摘要,還能利用 Reporting Services 資料表與連結子報表來深入剖析詳細資訊。包含的報告層級如下:

  • 載入群組執行摘要:開始與完成時間、持續期間摘要和執行狀態。

  • 封裝執行摘要:載入群組關聯、開始與完成時間、持續期間摘要和執行狀態。

  • 資料列計數詳細資訊:步驟描述和類型、資料列計數。

  • 封裝控制流程工作摘要:從基礎記錄表取得的工作摘要彙總,包含工作持續期間和狀態。

  • 詳細的事件記錄歷程:針對選定封裝或工作而排序及篩選的詳細記錄項目。

只消看一眼 ETL 報告,就知道它對管理 SSIT 解決方案和疑難排解真的很有幫助。

Cc645519.realetldp12(zh-tw,TechNet.10).gif

[圖 12]

動態組態設計

管理、散發和部署 SSIS 封裝能否順利,本身的組態很重要。SSIS 有好幾種方法可以在執行時間設定封裝的屬性,以便更新連線資訊、變數、以及其他在執行期間需要動態組態的工作或轉換屬性。SSIS 提供好幾種內建的組態方法,內容涵蓋範圍廣泛的環境需求,可供各種解決方案使用,包括:

  • 設定檔案

  • 環境變數

  • SQL 組態資料表

  • 父封裝變數

從開發環境遷移 ETL 封裝到預備環境,再移到生產環境時,這個功能就可以派上用場。通常只要變更組態系統中的幾個項目,就可以完成設定。

SQL 組態

Project REAL 的目的在於集中組態,讓 ETL 設計可以部署到兩個獨特的環境中:一是向上升級模型,二是分散模型。同樣地,REAL 解決方案也有數個版本 (完整版、範例版及展示版) 可供參考與示範之用。因此,利用內建的 SQL 組態,各種組態被集中到解決方案的不同資料庫版本中,以便將組態屬性和對應放置到關聯式資料表並於封裝中共用。若要開啟集中式管理工具,請選取 [SSIS],再選取 [組態]。[圖 13] 顯示 [SQL 組態] 選項。

Cc645519.realetldp13(zh-tw,TechNet.10).gif

[圖 13]

在組態資料表中,不同的屬性有不同的項目。第一組項目主要供連線設定。我們必須說明如何套用連線的組態屬性,以及封裝如何使用「解決方案資料來源 (Solution Data Sources)」,這點很重要。從 Data Source 物件建立連線後,連線就成為資料來源在執行階段的一個副本,封裝執行後這個連線並不會從父資料來源更新。資料來源是設計階段建構,因此在 UI 開啟封裝時,從 SSIS 解決方案內的資料來源建置的連線就會進行更新。基於這項特點,連線特別適用組態,它們通常需要根據環境 (開發、測試或生產) 而動態產生。Project REAL 組態資料表中的其他項目是變數對應,可更新 ETL 用來處理邏輯與管理的變數值。

XML 檔案組態

[圖 13] 中,SQL 組態資料表的適當位置端視封裝連線而定。但是,如果所有的連線資訊都在組態資料表中,可能會發生循環參考,最後迫不得已得要使用硬式編碼的封裝連線值。要避免這種情況,就得使用第二種組態類型:XML 檔案組態。同樣地,為了在資料庫資料表集中管理組態,XML 檔案只需要一個組態設定項目,也就是指向包含 SQL 組態資料表之資料庫的連線字串。如您所見,XML 檔案組態真的只有一個屬性,包含 XML 檔案的位置和檔名。

Cc645519.realetldp14(zh-tw,TechNet.10).gif

[圖 14]

檔案組態還具有一項很棒的功能,它可以利用伺服器環境變數來定義組態檔案的位置。既然所有的封裝都參考這個檔案,使用環境變數表示檔案變更時只要更改一個位置就成了。對於部署作業,這項功能也相當寶貴,因為其他執行這些封裝的伺服器也可使用不同的檔案位置和檔名。請注意,這裡對於環境變數的用法與「SSIS 環境變數組態 (SSIS Environment Variable Configuration)」不一樣,後者允許伺服器包含多個環境變數,可覆寫任何封裝屬性。

父變數組態

截至目前為止介紹的 Project REAL 組態用法都是針對全域範圍內的屬性,也就是說,每次在指定的環境中執行封裝或封裝群組時,使用的連線和變數。但是有些組態需要限制只用於執行特定封裝並限制封裝參與的工作流程群組。例如,工作流程群組的批次識別碼「ETL_Load_ID」會在載入群組封裝的初始步驟中建立,並用於所有的子封裝。每次執行封裝都是在不同的批次內容中進行,因此這個變數必須根據執行的封裝設定動態組態。

SSIS 的「父變數組態 (Parent Variable Configuration)」功能允許子封裝繼承父封裝的變數。這與 SSIS 的前身 DTS 不一樣,DTS 中的變數會從父封裝下推到子封裝。而在 SSIS 中,子封裝會依名稱向父封裝要求變數,可讓任何使用「執行封裝」工作的父封裝呼叫子封裝,同時讓被呼叫的子封裝繼承父封裝的變數。

Project REAL 要求組態必須與單一或一組封裝的執行個體處於同一本機,而「父變數組態 (Parent Variable Configuration)」功能可以做到這點。如上所述,所有維度、事實封裝和父封裝的執行識別碼都會繼承「ETL_Load_ID」,以便產生封裝間的資料關聯。[圖 15] 顯示「vnETL_Load_ID」識別碼的變數組態。

Cc645519.realetldp15(zh-tw,TechNet.10).gif

[圖 15]

資料處理架構

目前我們已經討論過 SSIS 設計的支援架構,但還沒深究 ETL 程序邏輯的核心。現在以本文前面幾節所討論的概念為基礎,我們可以開始詳細介紹處理邏輯。但是在深入講解 Project REAL 資料處理實作前,我想先講幾個重點,討論 ETL 幾個重要的原則下會使用的 SSIS 新功能。

控制流程和資料流程

實作核心商務邏輯使用的 SSIS 主要功能都包含在「控制流程」和「資料流程」元件中。本文在說明 Project REAL 的環境與稽核結構時,曾有幾次提到這些元件。

簡單來說,「控制流程」是一種工作流程引擎,可協調封裝的商務作業流程邏輯。每個封裝都有一個主要「控制流程」(事件控制項就是一種控制流程),裡面可能包含一個步驟,或好幾個交互連線的工作。「控制流程」內的工作以條件約束來連結,包括成功、失敗、完成、自訂條件約束運算式和布林邏輯。

「資料流程」是一個資料處理引擎,可以處理資料移動、轉換邏輯、資料組織,以及來源和目的地之間的資料擷取與授予。不同於「控制流程」,在封裝中可以定義多個由「控制流程」協調的「資料流程」。「資料流程」有紅綠兩色的連接器,和「控制流程」的工作流程連接器很像,但彼此功能完全不同。「資料流程」好比是一條資料管線,以小型資料批次的流量從一個轉換流到另一個轉換,這稱為「緩衝區」。這是「資料流程」運作原理最簡單的比喻,但實際上做工出力的絕大都是定義的轉換工作,由它在資料緩衝區上進行以達到最高效能。

SSIS 架構優勢

除了在互通性、組態、重新啟動能力和記錄功能等產品優勢超越 DTS 以外,SSIS 還提供具有規模效益的轉換引擎,並讓 ETL 架構在設計上帶來更高的穩定性、彈性和效能。Project REAL 在開發核心 ETL 時考慮到上述的優勢,所以有些設計決策與目前以 DTS 為基礎的架構不同。

有限制的預備區

首先,SSIS 會精簡預備環境,直接由「資料流程」執行複雜的資料轉換、整理、查閱,同時降低對 RDBMS 引擎和存放區的依賴。來源和倉儲資料表之間的資料比較可以透過查閱加以處理,並合併轉換與條件式分割,以便將結果導向適當的載入邏輯。有鑑於此,資料庫引擎只需要輸出資料到「SSIS 資料流程 (SSIS Data Flow)」,而非輸出到執行查詢、聯結或資料列比較的資料庫。

管線優勢

大部分的「資料流程」元件都能真正實現管線平行處理 (除了一些明顯的例外情況,如 Sort 和 Aggregate 轉換),這表示倉儲物件的處理幅度同時正在小型資料緩衝區內進行,無須等待整個上游處理完成後,再進行下一個處理。如此能減輕擷取作業對來源系統的影響,在 Project REAL 開發的過程中,我們常發現 SSIS 封裝經過最佳化後,從來源擷取未經處理資料,然後立即將資料放入資料庫所需的時間,幾乎等於擷取資料後,將其傳遞到「資料流程」元件中設計的一系列記憶體中轉換所花的時間。

資料整理和轉換

最新的 SSIS 資料流程轉換包含一系列的資料整理工具,例如模糊查閱與聯結、字元對應表、資料類型轉換、衍生的資料行,還有進行資料比較和替換的一組布林值函式。

多對多來源和目的地

既然單一資料流程可以包含多個異質性來源與目的地,因此來自單一來源的資料也可分割成多個目的地。反之亦然,多重來源物件可以合併到單一目的地內。通常在商務智慧系統中,維度可能來自相同系統或不同系統中的各種資料表。同樣地,事實也可以來自一或多個資料表,交易來源也可以分散到多個事實資料表目的地。

維度及事實粒狀資料單位和類型變更

大多數情況下,倉儲物件載入時的粒狀資料單位與其來源 OLTP 物件一樣。但是有時候維度會彙總到較高的粒狀資料單位,或是父子自我聯結的來源反正規化而成為標準階層。來源記錄可能需要進行樞紐分析,例如來源資料列從第四個一般設計向下合併至相關屬性的資料錄集時。此外,為了符合報告需求而彙總或群組化事實資料表時,這些資料表也要執行類似轉換。這些較少見的情況可以其他最新的轉換功能 (彙總、樞紐分析、反樞紐分析、排序、合併聯結等),在「資料流程」內處理。

維度處理

處理維度歷程是 ETL 解決方案中比較棘手的部分。對 Project REAl 而言,維度載入方案不僅是處理歷程記錄和變更屬性,還涉及維度關聯的維度變更類型與非同步事實。除了考慮使用 SCD (Slowly Changing Dimension) 精靈的內建功能,我們還將介紹本專案中幾個較為特殊的需求:

  • 推斷維度成員,在此擷取的事實沒有相符的維度成員,因為尚無完整的維度記錄可供載入。有時這稱為「孤立事實 (Orphan Fact)」。

  • 變更 SCD 類型,其中單一維度內的成員具有不同的歷程變更需求,且這些個別需求可能會與時變更。

SSIS 能夠處理 Project REAL 解決方案中的標準和特殊案例,下面將為您介紹。

Slowly Changing Dimension 精靈

每個 ETL 設計人員都夢想有個工具,能夠神奇地處理緩慢變更的維度。SSIS 其實也相差不遠,SSIS 中提供的精靈會根據來源與目的地維度結構描述,逐步引導開發人員判斷出變更屬性。然後精靈再建置處理維度所需的轉換作業。即便日後需求生變,仍可依設定狀況重新叫用精靈,藉此變更原先的設定來因應新的處理作業。

Project REAL 中的 Slowly Changing Dimension (SCD) 工具有許多好處,所有的星狀結構描述維度資料表都使用 SCD 轉換,只有一個例外;這樣就能大幅縮短維度處理的開發時間。Store 維度提供最完整的精靈使用情況,為您示範 SCD 精靈如何運作。Store 維度的需求包括:

  • 新的維度成員:新增已加入來源的新維度成員。

  • 變更維度屬性:傳統的類型 1 (Type-1) 資料行變更,每當來源資料行值變更時,歷程都會被覆寫。

  • 歷程維度屬性:傳統的類型 2 (Type-2) 資料行,藉由新增與所有新事實記錄相關聯的新維度記錄來保留歷程,直到下次變更為止。

  • 推斷成員如果維度成員在事實處理執行前尚未載入維度資料表,此時會新增一筆替代記錄,待取得完整的來源維度後,再接著更新此記錄 (類型 1 與類型 2 資料行兩者)。

針對 Store 維度執行精靈時,第一個出現的畫面顯示資料行清單,以及可供選取的業務索引鍵,如 [圖 16] 所示。

Cc645519.realetldp16(zh-tw,TechNet.10).gif

[圖 16]

接下來,精靈會要求區隔出變更類型中牽涉的資料行。可用選項有 Changing 屬性、Historical 屬性和 Fixed 屬性,後者指示不應變更的資料行。[圖 17] 顯示這些屬性。

Cc645519.realetldp17(zh-tw,TechNet.10).gif

[圖 17]

包含歷程或類型 2 資料行的維度需要一些中繼資料,來管理每項變更的目前和歷程性質。下一個畫面 ([圖 18]) 可用來讓處理瞭解 Store 維度追蹤歷程的方式。本例中,由 Current_Row 資料行會追蹤對於變更的維度資料列而言,哪個維度記錄是最新的。

Cc645519.realetldp18(zh-tw,TechNet.10).gif

[圖 18]

接下來,如果使用推斷成員,[圖 19] 顯示的畫面便會顯示 SCD 如何判斷維度記錄為推斷成員,以便更新業務索引鍵以外的所有資料行。我們有兩種選擇:第一個選項指示所有非索引鍵的資料行為空值 (Null),以便識別推斷成員。第二個選項由旗標資料行驅動,表示是否推斷成員。由於 Analysis Services 無法清楚顯示 NULL 資料行,因此我們決定使用 Inferred_Member 資料行。這樣就能將 Analysis Services 階層中使用的屬性取代為具名 Unknown 值。

Cc645519.realetldp19(zh-tw,TechNet.10).gif

[圖 19]

最後一個畫面結束後,精靈會根據先前步驟中輸入的細節,產生一系列的自訂轉換。其中的主要轉換稱為 Slowly Changing Dimension 轉換。此轉換可視為來源記錄針對維度的輸入,無論他們是完整的維度來源複本,或是來源記錄的子集 (即來源的新增或變更內容)。您可以把 SCD 工作想成是非快取 Lookup 轉換和 Conditional Split 轉換的綜合體,在此根據倉儲維度評估來源維度記錄,然後再將這些記錄分散到不同的 SCD 輸出。[圖 20] 顯示 Store SCD 轉換與其相關輸出的最終 UI 影像。

Cc645519.realetldp20(zh-tw,TechNet.10).gif

[圖 20]
與眾不同的維度案例

Item 維度是唯一未使用 SCD 轉換的 Project REAL 維度處理封裝。此封裝的需求特殊,而且其大小 (約 6 百萬個成員) 需要針對延展性進行特別處理。

Item 維度與其他維度不同之處在於產生的 SCD 變更類型。除了需要推斷成員、 Changing 屬性、和 Historical 屬性外,還要求特定成員的屬性類型可以從 Changing 調制成 Historical,從類型 1 調制成類型 2。項目的第一次銷售會發生這種情況。進行第一筆銷售之前,所有的屬性都是 Changing 類型 1 屬性;一旦銷售行為發生後,屬性的子集就變成 Historical 類型 2 變更。這種情況會產生類型 1.5 變更,並且遵守限制可加入維度中的類型 2 的商務需求。這是因為當項目第一次輸入交易系統時,建立此項目之特性的作業在頭幾天會產生好幾筆變更。釐清這些初始屬性詳細資訊的同時,維度成員的狀態是:任何屬性的變更都會更新維度中的屬性,而非產生新的類型 2 Historical 記錄。這種方法的好處在於,能夠限制維度資料表只納入有價值的歷程變更,也就是穩定狀態和銷售狀態的項目。雖然商務需求各有不同,但這個案例與推斷成員的運作方式大同小異。不過在本案例中,已經有可用的來源維度記錄,且更新所有屬性的需求在達到銷售需求之前都有效。

維度需要處理的記錄資料量決定了是否需要使用 SCD 精靈。Item 維度含有六百萬筆的成員,每天可能有跨一百個屬性、多達上萬筆的變更。 內建 SCD 元件查閱作業向資料庫產生同樣數目的呼叫、查詢大型資料表,並在結果列中傳回數十筆資料行。所需的時間間隔不足以執行這個作業,因此我們採用另一種作法。

我們可以使用 Lookup 轉換,將整個維度拉到快取區,這樣所有的資料行就可以用來進行變更類型比較。但是話說回來,快取大型資料表的所有資料行,需要佔用好幾 GB 的記憶體,光是載入記憶體也得耗費不少時間。因此我們改用左 Merge Join 轉換,左邊的來源記錄與業務索引鍵右邊的目前維度成員比對,如 [圖21] 所示。此聯結效果就是以串流方式,只傳入實際用於相關事實中的項目記錄。變更類型分析所需的維度資料行包含在「資料流程」中,以便比對記錄。我們也使用左合併,這樣來源 (位於左方) 中的新記錄會新增到維度中來延長管線,成為新成員。

Cc645519.realetldp21(zh-tw,TechNet.10).gif

[圖 21]

這個案例中的合併進行得很順利,因為輸出資料欄已經經過排序,一旦比對後,就會釋放記錄至下游轉換以供處理。

Conditional Split 轉換 (位於 Merge Join 轉換正下方) 會分析特定條件,再將資料列導向到多個轉換輸出。條件會依序進行評估,第一個符合的條件會決定資料列的輸出,以免將資料列傳送到多個輸出。

Cc645519.realetldp22(zh-tw,TechNet.10).gif

[圖 22]

[圖 22] 中的 Conditional Split 轉換會使用 ISNULL 函數,先評估聯結的右方是否有符合的項目。符合 Null 檢查的來源資料列會被輸出到轉換,然後轉換會新增資料列成為新的維度成員。由於其餘成員都具有倉儲維度資料表的相符項目,因此這時會評估 Changing 類型準則。比對記錄的第一個評估準則是「推斷成員」和「銷售旗標」條件。由於這兩者都需要對維度屬性進行完整更新,因此結合並同時處理這兩個條件。接下來,我們評估歷程變更屬性。如果歷程追蹤屬性有一或多筆變更,便會產生類型 2 變更記錄。最後,如果剩餘的 Changing 類型資料行也有變更,便會產生維度更新陳述式來覆寫該屬性的前一個值 (依照類型 1 的形式)。

請注意,我們並沒有指定最後的條件,而是以 Condition Split 的預設輸出來解說這個案例。由於來源記錄只包含新的和變更的資料行,所以能推知若其他所有條件都符合了,就必須將最後的準則套用在剩餘記錄上。這裡強調準則的順序對於 Item 維度的正確處理相當重要。

條件式分割的轉換下游看起來很像 Store 範例中 SCD 轉換的輸出結果,這是因為輸出結果是在 SCD 處理新增、變更和推斷成員 (因為屬於類型 1.5 變更,因此稱為「完整更新」) 後才進行模式化。

Cc645519.realetldp23(zh-tw,TechNet.10).gif

[圖 23]

事實資料表處理

處理事實資料表和維度處理相當不同,各種事實資料表處理也各異。但大部分的事實資料表處理都包含事實資料列比較和維度索引鍵查閱。為了方便說明,我們會特別針對幾個不同的 Project REAL 事實資料表封裝來討論。這些封裝可視為一般案例的典型。

遞增和完整來源擷取

Project REAL ETL 有兩種事實資料表擷取;「完整來源擷取」無法識別變更或新的資料列,「遞增擷取」只會擷取新增和變更的記錄。

完整來源擷取

每週追蹤約 8 百萬個不同 DC 和項目組合的分區中心 (DC) 庫存狀態。在來源系統中,這些記錄都放在不區別新記錄或修改記錄的資料表內,因此 ETL 程序必須比較庫存來源和事實資料表之間的這些記錄,才能識別發生變更的時間。接著才能正確處理插入或更新作業。

採用的方法是來源與目的地資料表雙方,都與完整資料集進行完整合併聯結。完整聯結將有助於識別庫存記錄新增到來源或被完全移除的時間。這個解決方案要求在事實資料表中,以零庫存狀態來追蹤刪除的來源記錄,只要使用設定為完整聯結的合併聯結轉換就可以達成這項要求。合併結果如 [圖 24] 所示。

Cc645519.realetldp24(zh-tw,TechNet.10).gif

[圖 24]

下一個下游轉換作業是 Conditional Split 轉換,可用來評估聯結結果和資料行值的比較,藉此識別記錄變更。

Cc645519.realetldp25(zh-tw,TechNet.10).gif

[圖 25]

Conditional Split 轉換中套用的各個條件會依序執行以提升效能,首先從聯結產生相符項目開始,且屬性和量值不會被變更。因為大部分的記錄都符合此條件,所以設計工具中會先列出這個輸出,但不會使用輸出資料流。既然未變更的記錄不需要處理,此舉能夠篩選出這些資料列。第二個條件的目的是識別新的庫存記錄,也就是來源資料列與現有的事實資料表記錄均不相符的庫存記錄。相對來說,下一個評估是完整聯結案例,其中的事實庫存狀態必須設為零,因為來源資料列已被移除。最後,預設輸出會限制其他所有資料列;這些資料列包含庫存狀態變更,而且必須在資料庫中更新。

遞增來源擷取

當擷取處理可以隔離來源系統上的更新與插入組合時,就能大幅增強相關 ETL 程序的效能。幸好 Project REAL 中的許多大型資料來源都能利用這些目標提取。就拿「分店庫存」擷取為例:如果需要處理整間店的庫存來源 (接近 2 億筆記錄) 的每日變更,那麼即使一天一夜也做不完。但是既然每日庫存可以以遞增方式來擷取,就可以將時窗縮減為可管理的程度。

「分店庫存」的遞增擷取作業使用快取查詢,以判斷遞增記錄屬於插入或更新。「臨時分段式處理」可用來篩選查閱中的記錄、最佳化處理作業和協助整體實作,藉此輔助這個處理。詳細資訊,請參閱本文稍後的〈資料處理最佳化技巧〉。

[圖 26] 顯示分店庫存事實處理的資料流程。圈出來的部分表示比較變更來源記錄與目前事實記錄的查閱步驟。

Cc645519.realetldp26(zh-tw,TechNet.10).gif

[圖 26]
維度查閱

每個事實資料表處理都必須能夠建立事實與維度資料表之間的關聯。整個專案中我們都使用 Lookup 轉換來快取維度,以便建立關聯。來源資料行流經這個轉換時,它會根據業務索引鍵關聯,快速傳回事實資料表所需的代理鍵 (Surrogate Key)。這種處理簡潔直接,維度的資料列數少時特別有效。只要維度中有 Historical 類型 2 變更並因而產生目前的資料列識別碼,就會從快取中篩選出目前的資料列,使最新版的維度成員與事實產生關聯。[圖 27] 顯示 Lookup 轉換的 [參考資料表 (Reference Table)] 索引標籤。在 Store 維度範例中,使用查詢來篩選 Current_Row。

Cc645519.realetldp27(zh-tw,TechNet.10).gif

[圖 27]

Lookup 轉換的 [資料行 (Columns)] 索引標籤中,「資料流程」資料行會對應到參考資料表資料行。查閱維度的目的在於取得維度的代理鍵,因此我們把業務索引鍵 (Store_Num) 當成對應,這樣代理鍵 (SK_Store_ID) 就會隨著「資料流程」下游使用的第二個資料行一併傳回。

Cc645519.realetldp28(zh-tw,TechNet.10).gif

[圖 28]

從資料流程的觀點來看,來源資料列不過是從一個查閱流到另一個查閱,同時建立資料列與最新維度代理鍵的關聯罷了。[圖 29] 的資料流程顯示上述的 Lookup 轉換。同時顯示一些其他轉換,包括下節將詳述的 Script 轉換,以及推斷成員使用的 Union All 轉換。

Cc645519.realetldp29(zh-tw,TechNet.10).gif

[圖 29]

由於項目維度包含數百萬筆資料列,我們採用一種最佳化技巧,將查閱範圍限制為事實資料表處理執行時所需的資料列。詳細資訊,請參閱本文稍後的〈資料處理最佳化技巧〉。

處理新增推斷成員

Project REAL 維度規定,如果維度記錄不存在,就得建立推斷成員,所以這裡有些準備工作要作。希望各位還記得,「推斷成員 (Inferred Member)」是一筆替代的維度記錄,其中只包含業務索引鍵值;等到取得完整的維度記錄後,所有的維度資料行才會更新為可用的最新值。更新作業會在處理維度時進行,但是新增推斷成員的動作則是當 Lookup 轉換找不到相符項目時,在事實處理的過程中進行。負責處理快取維度的 Lookup 轉換查閱索引鍵,卻找不到符合資料時,表示資料列失敗。為了設定輸出以便重新導向失敗管線的資料列,可以按一下查閱 UI 的 [設定錯誤輸出 (Configure Error Output)] 按鈕,並將資料列設為「重新導向」。請參閱 [圖 30]。

Cc645519.realetldp30(zh-tw,TechNet.10).gif

[圖 30]

如此設定主要 Lookup 轉換之後,不符合的資料列就會被重新導向,並以其他方式將推斷成員新增至維度內。Project REAL 中所有的代理維度鍵都是識別資料行,而不是由 SSIS 產生的唯一識別碼,因此使得這個處理更複雜。我們可以使用好幾種方式來處理推斷成員新增動作。由於資料量相當大,有必要將新增推斷成員的動作限制在主要「資料流程」中進行,以便最佳化執行的處理。為了簡化處理作業並避免多個「資料流程」,必須在下一次維度查閱前將新產生的推斷成員索引鍵帶回「資料流程」中。這裡就牽涉到另一個問題:由於主 Lookup 轉換快取是在「資料流程」執行前載入,當推斷成員第一次加入資料庫時,並非與其他維度記錄一併加入快取。萬一不相符的業務索引鍵包含上千筆事實記錄,那麼在快取中會找不到這些記錄,因此會傳送到推斷成員新增處理的錯誤輸出。

基於以上種種考量,我們選擇 Script 轉換來處理推斷成員新增。Script 轉換 (和 Script 工作不同) 主要用於「資料流程」,可以在來自管線的資料列和資料行中執行指令碼操作。對於需要特殊處理的獨特案例,Script 轉換能夠提供彈性與自訂功能,所以是上選。本例中的特定目的是取得不相符查閱輸出結果、在資料庫的維度資料表中新增推斷成員,然後傳回新產生的代理鍵,一氣呵成,不必為了取得同一個不相符的業務索引鍵對資料庫發出多筆呼叫。開發 Visual Basic® .NET 指令碼之前,我們先整理了一份提供 Script 轉換處理概觀的程式碼大綱:

  1. 處理第一個記錄之前,要先執行的初始步驟包括:

    1. 宣告變數。

    2. 建立可供業務索引鍵和輸出代理鍵查閱所使用的雜湊表,以達到最高效能。

  2. 針對來自 Script 轉換管線的每個資料列,檢查雜湊表中是否有最新的業務索引鍵。接著:

    1. 如果有業務索引鍵,將代理鍵傳回至管線輸出。

    2. 如果沒有業務索引鍵:

      1. 連接到資料庫。

      2. 執行預存程序以建立推斷成員並傳回代理鍵。

      3. 將業務索引鍵和新的代理鍵新增到雜湊表。

      4. 將代理鍵傳回到管線輸出。

  3. 管線輸入最後一個資料列後,緊接著清除和解除配置。

以下 Script 轉換是 Store 查閱的推斷成員處理範例,當 Store_Num 在查閱中找不到相符記錄時,便會採用此處理。

' Microsoft Data Transformation Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for DTS Script Components

Imports System
Imports System.Data
Imports System.Collections
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
___________________________________

Public Class ScriptMain
  Inherits UserComponent
  Private htBusinessID As New Hashtable
  Private objConnection As New SqlClient.SqlConnection
  Private objCommand As New SqlClient.SqlCommand
  Private boolInit As Boolean = False
  Private strProcedureName As String = "config.up_ETL_DimStore_CreateInferredMember"
  Private strBusinessID As String = "@pnStore_Num"
  Private strSurrogateID As String = "@pnSK_Store_ID"
  Private strETLLoadID As String = "@pnETL_Load_ID"
  Private strReturnValue As String = "@RETURN_VALUE"
___________________________________

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    If (boolInit = False) Then Me.Connect()
    If Not (htBusinessID.Contains(Row.InBusinessID)) Then
      Dim strSurrogateKey As String = Me.Execute(Row.INBusinessID, Row.ETLLoadID)
      htBusinessID.Add(Row.InBusinessID, strSurrogateKey)
    End If
    Me.ProcessRow(Row)
  End Sub
  Public Sub ProcessRow(ByVal Row As Input0Buffer)
    Row.OutSurrogateID = 
     System.Convert.ToInt16(htBusinessID.Item(Row.INBusinessID).ToString())
  End Sub
  Private Function Execute(ByVal BusinessId As Decimal, ByVal ETLLoadID As Integer, ByVal) 
  As String
    Me.objCommand.Parameters(strBusinessID).Value = System.Convert.ToString(BusinessId)
    Me.objCommand.Parameters(strETLLoadID).Value = System.Convert.ToString(ETLLoadID)
    Me.objCommand.ExecuteNonQuery()

    Execute = 
     System.Convert.ToDecimal(Me.objCommand.Parameters(strSurrogateID).Value).ToString()
  End Function
___________________________________

  Private Sub Connect()
    If boolInit = False Then
      Dim strConnection As String = Connections.SQLRealWarehouse.ConnectionString
      Dim x As Integer = strConnection.ToUpper().IndexOf("PROVIDER")
      If x >= 0 Then
        Dim y As Integer = strConnection.IndexOf(";", x + 1)
        If (y >= 1) Then strConnection = strConnection.Remove(x, y - x + 1)
      End If

      Me.objConnection.ConnectionString = strConnection
      Me.objConnection.Open()
      Me.objCommand.Connection = Me.objConnection
      Me.objCommand.CommandType = CommandType.StoredProcedure
      Me.objCommand.CommandText = Me.strProcedureName

      Dim Parm As New SqlClient.SqlParameter(strBusinessID, SqlDbType.Decimal)
      Parm.Direction = ParameterDirection.Input
      objCommand.Parameters.Add(Parm)

      Parm = New SqlClient.SqlParameter(strETLLoadID, SqlDbType.Int)
      Parm.Direction = ParameterDirection.Input
      objCommand.Parameters.Add(Parm)

      Parm = New SqlClient.SqlParameter(strSurrogateID, SqlDbType.Int)
      Parm.Direction = ParameterDirection.InputOutput
      Parm.Value = 0
      objCommand.Parameters.Add(Parm)

      Parm = New SqlClient.SqlParameter(strReturnValue, SqlDbType.Int)
      Parm.Direction = ParameterDirection.ReturnValue
      objCommand.Parameters.Add(Parm)

      Me.boolInit = True
    End If
  End Sub
___________________________________

  Private Sub Close()
    If boolInit = True Then Exit Sub
    Me.objCommand.Dispose()
    Me.objConnection.Close()
    Me.objConnection.Dispose()
    Me.htBusinessID = Nothing
    MyBase.Finalize()
  End Sub
End Class

Script 轉換會執行一個程序,查看維度中是否有業務索引鍵,如果沒有此記錄則插入一筆新記錄 (也就是推斷成員)。然後該程序會將新增的識別 (Identity) 資料行傳給指令碼,供「資料流程」下游使用。

Inferred Member 指令碼轉換後,再使用 Union All 轉換將記錄重新與主要管線合併,這樣下次的維度查閱就可以使用這些記錄了。[圖 31] 顯示一連串查詢作業、關聯的推斷成員查閱,以及集結結果所需的等位。

Cc645519.realetldp31(zh-tw,TechNet.10).gif

[圖 31]
資料處理最佳化技巧

在整個 Project REAl 開發過程中,使用一些最佳化技巧可簡化 ETL 作業。這些技巧包括以產品架構優勢為主的原則、SSIS 內的設定,以及處理大量資料的資料流程微調。以下列出一些優勢:

  • 使用高價值的目標資料預備區來篩選查閱與資料合併來源。

  • 慎用諸如彙總和排序等 Full Rowset (封鎖) Data Flow 轉換。

  • 在處理例外狀況前,先處理一般資料處理案例。

  • 進行大型資料量維度或事實資料表更新時,考慮採用批次更新。

高價值的目標預備區

完全快取的 Lookup 轉換可以將不同來源的資料相互關聯,例如建立維度索引鍵與事實資料表記錄的關聯。但是如果維度相當龐大,將整個維度載入查閱記憶體快取區會浪費很多時間,而且霸佔可以用於其他作業的 RAM。針對包含六、七百萬成員的大型項目維度,我們專為 Project REAL 建立這些業務索引鍵的目標預備區資料表。我們使用包含單一來源和目的地的「資料流程」,先只擷取交易來源中的產品業務索引鍵,再將其放入預備資料表中,藉此將這個精簡預備區資料表填入事實資料表封裝中。[圖 32] 顯示在 55 秒內成功將 440 萬筆的業務索引鍵放入預備區。由於擷取的目標與範圍集中,「資料流程」才有辦法在不到一分鐘的時間內處理數百萬筆的資料列。

Cc645519.realetldp32(zh-tw,TechNet.10).gif

[圖 32]

接著使用這些預備索引鍵篩選出查閱用來載入快取的查詢。由於維度中的業務索引鍵已編製為索引,限制維度記錄的聯結進行得很順利。本例中,這個技巧篩選出的維度查閱快取區只是整個維度大小的 1/10,但是事實處理所需的所有維度成員卻是一個都不少,因為查閱使用的業務索引鍵是同一組。下列 SQL 程式碼可用來填入查閱快取區,包含使用預備區資料表 (tbl_DWS_ETL_Store_Inventory_Log) 中的業務索引鍵來篩選出維度 (Tbl_Dim_Item) 。

SELECT distinct
 ITEM.SK_Item_ID
, ITEM.SysID
, ITEM.SK_Parent_Item_ID
, ITEM.Retail_Amt
FROM dbo.Tbl_Dim_Item as ITEM
INNER JOIN config.tbl_DWS_ETL_Store_Inventory_Log_Keys as INV_LOG
ON ITEM.SysID = INV_LOG.SysID
AND ITEM.Current_Row = 1

這個技巧也可以應用於限制 Merge Join 轉換所使用的來源記錄。合併聯結在好個案例中也可以扮演與查閱類似的功能,替來源與目的地資料建立關聯。如果需要比較來源與倉儲之間的幾十資料行,Lookup 轉換可能無法處理此查閱快取區的大小,因為各個資料列的每個資料行都必須儲存在記憶體中。這時可改用 Merge Join 轉換來結合來源與倉儲資料。Merge Join 並不會佔用記憶體,而且如先前所述,又能利用預備區內的業務索引鍵篩選資料倉儲來源。

限制 Sort 與 Aggregate 資料流程轉換

限制 Sort 與 Aggregation 轉換有益於效能,因為這樣能先壓抑管線中佔用時間和資源的資料列,但有時候的確需要進行這兩種轉換。舉例來說,Project REAL 內好幾個封裝都使用的 Merge Join 轉換規定定義聯結的資料行必須先排序來源。但是在兩個來源使用 Sort 轉換,就表示合併兩方的所有資料列在釋放到 Merge Join 之前,必須先保留在 Sort 轉換內。如果資料列集不大,應該不成問題。但如果很大,那問題就來了。首先,載入排序 (另一個例子是彙總) 的記錄會儲存在記憶體內。一旦達到臨界值,有一部份的快取內容會先被 Sort 轉換或虛擬記憶體管理程式暫時載入到磁碟內,進而降低 I/O 的效率,並佔用需要用來處理其他下游轉換的記憶體資源。因為「資料流程」中使用的轉換作業造成資料被扣留,會對管線的上游資料造成排擠效應,如果此篩選作業回到來源連線處,會減慢擷取速度,使得整個處理都慢下來。

這並不是告訴讀者應該要完全避免使用 Sort 或 Aggregation 轉換。整體而言,這些作業通常還是很快速很有用的。我們只是想提醒讀者,在處理鉅量資料或記憶體資源有限時要格外小心。

本 Merge Join 範例中,如果能預先排序來源資料,其實可以避免 Sort 轉換。這種作法在所有 Project REAL 案例中都能有效運作。由於 ETL 程序通常會使用 Merge Join,建立資料索引鍵與維度的關聯或代理鍵與事實的關聯,排序可以上推至來源連線。在關聯式引擎上進行排序可能會佔用很多資源,除非已經有適當的索引或索引組合。由於業務索引鍵和代理鍵很適合編排索引,因此在 SQL 查詢中加入 ORDER BY 子句是有效又實用的作法。為此,「資料流程」必須知道來源已經經過排序,還須知道套用排序的資料行與方向。我們使用來源連線的 Advanced Editor 來完成這項工作。在 [輸入和輸出屬性 (Input and Output Properties)] 索引標籤上,尋找 [OLE DB 來源輸出 (OLE DB Source Output)] 最上層的屬性,其中稱為「IsSorted」的屬性必須設為 True。接著使用「輸出資料行 (Output Columns)」容器中的「SortKeyPosition」屬性,指定要排序的資料行 (如 [圖 33] 所示);這樣「資料流程」就會知道排序的情況。

Cc645519.realetldp33(zh-tw,TechNet.10).gif

[圖 33]

另一種情況是排序或彙總需求只牽涉到「資料流程」資料的子集,建議您採用另一種最佳化作法,使用 Multicast 轉換來將資料子集分支出來,然後使用 Conditional Split 轉換篩選出需要的資料列,最後指定 (排序或彙總內的) 資料處理時,會派上用場的輸出資料行的子集。

在處理例外狀況前,先處理一般案例

由於管線具備分支、合併、篩選和聯集的能力,您可以將特殊情況分開處理,藉此將處理最佳化。例如,我們可以把百分之九十幾的處理交給快取查閱或大量目的地等精簡的作業負責,剩下來百分之十的例外情況就交給效率較低的方法來處理就可以了。

OLE DB Command 轉換和批次更新的比較

ETL 程序最怕遇到的情況就是大量更新。有些系統乾脆完全避開事實資料表更新,以免此程序造成嚴重的系統負荷。為了減輕量值差異而建立事實資料表變更記錄,但會造成本身在報告與處理方面的挑戰。Project REAL 的每日庫存處理就是模擬這種情況。庫存快照事實資料表使用每週粒狀資料單位,每週儲存高達兩億筆記錄。此外,目前庫存資料每天約有一千萬筆變更。這些因素加起來很容易造成瓶頸。

處理此大量更新作業的方法主要有兩種:

  1. 使用 OLE DB Command 轉換與參數化查詢。

  2. 將變更記錄放在預備區資料表中,並針對集執行 RDBMS 更新。

第一個方法中,SSIS 內包含一個可以直接與資料庫互動的轉換,可以處理數種作業,包括最常見的 UPDATE 陳述式。OLE DB Command 轉換使用根據「資料流程」資料行對應而參數化的 SQL 陳述式。資料列傳給轉換的同時,會使用資料列中提供的資料處理作業。由於作業一次只會執行一個資料列,所以處以大筆更新時會遇到幾項限制。試想數百萬筆的資料要通過轉換以進行更新,會帶來很大的負面影響,例如對關聯式資料庫造成衝擊、對「資料流程」造成壓力、完成處理耗費的時間。

第二種方法涉及預備資料並使用關聯式引擎,透過 Update 陳述式將預備資料表與目的資料表聯結,藉此處理更新作業。雖然這種手法相當依賴預備環境,但評估成本後,整體來看還是比第一種方法好。但它本身還是有些缺點,例如使用預備環境的來源成本,以及更新過程中會對倉儲資料庫造成衝擊,系統和資料表、分頁或資料列鎖定雙方都會承擔資源壓力。

第二種方法的好處的優點只有在與第一種比較時才看得出來。將需要更新的資料放在預備環境,目的轉換就能夠最佳化,連帶提升管線作業效能。再者,因為 SQL 比另一個逐列更新的方法更能有效處理以集為基礎的作業,因此可減少對目的資料表的影響時間總長度。更新也能受益於最佳化索引,以及處理一連串較小批次作業的更新。

結論

濃縮在核心「SSIS 控制 (SSIS Control)」與「資料流程」元件中的商務邏輯,可提供支援 Project REAL 各大目標的基本 ETL 作業:可用性、組織化,以及提供分析使用的正確資訊。除了商務資料處理,ETL 解決方案還需要支援管理與開發方法的機制,而這些方法都已經整合在 Project REAL 解決方案中。整體來看,SSIS 有能力符合上述這幾項必要條件,不管是資料或運作上,都具備高效能、容易開發與富有彈性的特色,能滿足今日複雜的需求。

在此鳴謝 Barnes & Noble、Intellinet 和 Microsoft Consultin 團隊將「真實 (Real)」解決方案推向成功,也感謝 Microsoft SSIS 開發團隊不遺餘力,提供設計諮詢,並在整個 SQL Server 2005 的測試版生命週期中鼎力協助。這份白皮書涵蓋的內容是用 Project REAL 解決方案的時間與心血得來,另外還多虧 Barnes & Noble 花費許多時間進行效能測試、設計檢討、從錯誤中學習教訓、蒐集和規劃專案需求,由此得來的辛苦結晶。基於這些努力,Project REAL 才有辦法深入探討核心 ETL,進行最佳化、修改和增強,使其成為 SQL Server 2005 平台的穩固 ETL 模型。

其他相關資訊:

http://www.microsoft.com/taiwan/sql/

下载

ProjectREAL ETLDP.doc
4.89 MB
Microsoft Word 文件

顯示: