數據在任何商業智能 (BI) 解決方案中都是最重要的部分。正如 Stacia Misner 在“規劃您的首個 Microsoft BI 解決方案”中所說，收集和維護 BI 解決方案數據涉及幾個步驟。BI 專業人員稱這些步驟為提取、轉換和加載 (ETL) 過程。即使您不準備將 BI 作為自己的工作重心，您仍可以利用 ETL 方法和工具來管理日常工作中進行決策所需的數據。在本文中，我將向您介紹如何設計和構建一個簡單的數據市場，從而演示如何使用 SQL Server 2008 Integration Services (SSIS) 對您自己的 BI 解決方案執行 ETL。

了解要求

和所有 IT 項目一樣，開始 ETL 項目的最佳做法是了解您希望構建的 BI 解決方案的總體要求，然后再決定怎樣利用數據才能最好地滿足這些要求。第一篇系列文章提供了 BI 解決方案的案例背景。在案例中需要 BI 解決方案的是虛擬公司 Adventure Works。文章通過敘述該公司要回答的幾個問題列出了分析要求。從這些問題中可以明顯看出 Adventure Works 需要從以下不同角度了解其產品銷售：不同分銷渠道（經銷商或 Internet）的收益率、產品需求隨時間推移發生的變化以及按產品、銷售人員、地理區域和經銷商類型統計的實際銷售額與預測銷售額之間的差異。回答這些問題有助于 Adventure Works 決定將重點放在哪個分銷渠道才能增加利潤、如何調整生產流程才能最好地滿足需求、如何改變銷售戰略才能幫助該公司實現銷售目標。將 SQL Server Reporting Services (SSRS) 添加到 BI 解決方案后，您就能看出數據是如何幫助回答這些商業問題的。

在按照這些要求開始為 Adventure Works 設計數據市場之前，我希望從商業角度建立信息需求模型。換言之，設計數據市場的依據是用戶如何提問，而不是從數據源獲取數據的方式。

本文中的代碼示例需要 SQL Server 2008 Adventure Works OLTP 示例數據庫。

使用維度模型

構建數據市場通常要使用維度模型設計。維度模型設計是非常適合用于分析的數據庫架構。（kimballgroup.com 是了解維度模型的一個非常好的資源。）維度模型以用戶熟悉的方式呈現數據，而且能夠幫助您構建適合查詢大量數據的優化數據結構。您可以通過對數據進行去規范化實現這種優化。去規范化能夠使數據庫引擎在查詢時快速選擇并高效聚合大量數據。我將在 Adventure Works 解決方案的去規范化架構中設定兩種類型的表：維度表和事實表。維度表存儲經銷商或產品等有關業務實體和對象的信息。事實表用來存儲需要聚合的銷售額數值，表中包含將事實表與維度表關聯起來的度量值和鍵。稍后我將在下文中詳細介紹事實表。

可以采用兩種架構實現維度模型表：星型架構和雪花型架構。簡單說來，星型架構中每種維度使用一個表，因此每個查詢與事實表之間都是單一聯接的。雪花型架構中每種維度使用兩個或多個表，因此查詢中需要更多聯接才能查看所有數據。這種級聯聯接的集合意味著雪花型架構的查詢速度通常要比星型架構的查詢速度慢。出于本文的需要，為了簡化設計，我將使用星型架構。

創建總線矩陣

Adventure Works 的 BI 解決方案中的重點是與銷售相關的維度。為了確定與銷售相關的維度，我要創建一個總線矩陣，這是維度建模過程中的一個步驟。Adventure Works 有兩種銷售渠道：向經銷商批發和通過 Internet 零售。我還使用總線矩陣來確定每個維度與以上兩種類型的銷售渠道或其中一種的關系。圖 1 所示為我的 Adventure Works 銷售總線矩陣示例。

Adventure Works 銷售	日期	產品	客戶	促銷	地域	經銷 商	銷售 區域	員工	貨幣
Internet 銷售	X	X	X	X			X		X
經銷商銷售	X	X		X		X	X	X	X

圖 1 Adventure Works 銷售總線矩陣

下一步是確定解決方案的度量值。度量值是進行分析所需的數值。這些數值可以直接取自銷售額或產品成本等數據源，也可以經過計算得出，如將某一數量乘以一定的金額得到擴展銷售額。此外，還需要確定在每個維度中應包含哪些屬性。屬性是維度中的單個元素（對應于表中的列），如銷售區域維度中的國家/地區或日期維度中的年份。您可以根據分析需要使用屬性對數據進行分組或篩選。本文不會對所有標識的度量值或維度屬性進行詳細介紹，但您需要注意有必要執行標識過程。

創建數據映射

在創建數據市場的物理表之前，我需要進行一些其他規劃。具體而言，我需要構建一個數據映射文檔，以便將數據市場架構中的每個目標列映射到 Adventure Works OLTP 源系統中的列。源系統就是 AdventureWorks2008 數據庫，您可以按照 Stacia Misner 文章第 31 頁的內容下載和安裝該數據庫。您可以使用各種應用程序創建數據映射。與格式相比，更重要的是內容。我習慣在 Microsoft Office Excel 中開發數據映射。圖 2 所示為我在數據映射中創建的 DimProduct 選項卡。此外，我還創建了 DimCustomer 和 FactInternetSales 數據映射。工作簿中的每個工作表表示數據市場中的一個表。每個工作表中只有兩列：一個源列和一個目標列。

圖 2 DimProduct 數據映射選項卡

每個維度表（日期維度表除外）中都包含名為代理鍵的主鍵（通常為標識列）。使用代理鍵的好處之一是在合并多個系統的數據時不會出現重復鍵。

維度表還有一個可選鍵列。這些可選鍵表示自然鍵，有時也稱為業務鍵。自然鍵用于標識源系統。例如，客戶維度中的 CustomerAlternateKey 列映射到 Adventure Works OLTP 數據庫中 Sales.Customer 表的 AccountNumber 字段。通過將這些鍵存儲在維度表中，每次對各個維度運行 ETL 過程時，我可以將維度中已有的記錄與從數據源中提取的記錄進行匹配。

幾乎每個數據市場都包含日期維度，因為業務分析往往按照日期、星期、月份、季度或年份來比較度量值變化。由于日期維度很少從源系統中獲取，因此不應使用基于 SQL Server 標識的鍵。為此，我將改用以 YYYYMMDD 格式存儲為 SQL Server 整數列的智能鍵。智能鍵是根據邏輯或腳本生成的鍵，而不是像 SQL Server 中的標識列那樣自動遞增的鍵。

請注意，日期維度通常并不映射到源表。因此，我將使用腳本生成數據，以便將記錄加載到表中。

由于我的小型架構所需的 ETL 過程相當簡單，因此這樣的數據映射足以滿足需要。在實際項目中，我會為數據映射添加注釋，指出何時需要進行復雜的轉換。

構建數據市場

邏輯建模完成之后，現在我需要創建 ETL 過程要加載的物理表及這些表的承載數據庫。我將使用基本的 T-SQL 腳本來創建數據庫及其關聯的維度表和事實表。在（2009 代碼下載）的示例 BI 解決方案的相應下載中可以找到完整的 T-SQL 腳本。

出于本文的需要，我僅構建了整個銷售數據市場架構的一個子集，以便在 SSIS 中涵蓋整個 ETL 過程。在精簡版架構中，我僅加入了 Internet 銷售事實表中的 OrderQuantity 和 SalesAmount 兩個度量值。此外，在此精簡版架構中，我還加入了簡化的客戶、產品以及日期維度表。

開發 ETL 過程

構建 BI 解決方案的下一步是設計和開發 ETL 過程。我們先回顧一下，ETL 包含從數據源提取數據、轉換數據然后再將數據加載到目標庫的整個技術過程。一般來說，BI 解決方案中的 ETL 過程先從平面文件以及 OLTP 操作數據庫中提取數據，然后轉換數據使其適應維度模型（例如，星型架構），最后再將結果數據加載到數據市場。

在 BIDS 中創建 SSIS 項目

開發 ETL 過程的第一步是在 Business Intelligence Development Studio (BIDS) 中創建新項目。SQL Server 2008 附帶了 BIDS，在安裝過程中選擇“工作站組件”選項即可安裝 BIDS。BIDS 中提供適用于 SSIS、SSAS 和 SSRS 的項目模板。與 Visual Studio 一樣，BIDS 也支持源代碼控制集成。

要啟動 BIDS，請轉到“開始”\“程序”\“Microsoft SQL Server 2008”\“Business Intelligence Development Studio”并選擇“文件”\“新建項目”。您會看到如圖 3 所示的“新建項目”模板。

圖 3 BIDS 2008 中的“新建項目”模板

在“模板”窗格中選擇“Integration Services 項目”。在“名稱”文本框中，鍵入“ssis_TECHNET_AW2008”，然后單擊“OK”。BIDS 應顯示一個打開的 SSIS 項目。

創建公共數據連接

SSIS 2008 中的另一項出色功能是可以在單個數據包之外創建數據源連接。您可以定義數據源連接一次，然后在解決方案中的一個或多個 SSIS 數據包中引用此連接。有關如何創建 BIDS 數據源的更多信息，請參閱“如何：使用數據源向導定義數據源（分析服務）”。

創建兩個新的數據源連接：一個用于 TECHNET_AW2008SalesDataMart 數據庫，另一個用于 AdventureWorks2008 OLTP 數據庫。將這兩個數據源連接分別命名為 AW_DM.ds 和 AW_OLTP.ds。

開發維度 ETL

用來加載產品維度的 ETL 非常簡單。我需要從 Adventure Works Production.Product 表提取數據，并將這些數據加載到 TECHNET_AW2008SalesDataMart 數據庫中。首先，我要重命名 BIDS 為我的 SSIS 項目創建的默認數據包。（數據包就是一個容器，存儲 SSIS 將執行的工作流中的所有步驟。）在解決方案資源管理器中右鍵單擊默認數據包，選擇“重命名”。鍵入“DIM_PRODUCT.dtsx”，然后按 Enter。

接下來，我需要使用預建的數據源來創建本地數據包連接管理器。新建兩個引用先前生成的數據源的連接管理器。

定義數據流以提取并加載

SSIS 中的一個數據流任務封裝了對于簡單維度實施 ETL 所需的全部數據。我只需將一個數據流任務從工具箱拖動到控制流設計器圖面并將其重命名為 EL（用于提取和加載）。右鍵單擊設計器中的數據流任務，然后選擇“編輯”。BIDS 現在顯示數據流設計器。

產品維度數據包的提取部分需要查詢 AdventureWorks2008 Production.Product 表。為設置此任務，我從工具箱中將一個 OLE DB 源組件拖動到數據流設計器圖面上，然后將該 OLE DB 源組件重命名為“AW_OLTP”。

接下來，我定義數據包的加載部分，以便加載到數據市場。我只將 OLE DB 目標組件的新實例拖動到數據流設計器圖面，并將其重命名為“AW_OLTP”。然后，我單擊 OLE DB 源 (AW_OLTP) 組件并將 OLE DB 源上顯示的綠色箭頭拖動到 AW_DM OLE DB 目標組件以連接這兩個組件。

此時，我已將必需的組件添加到數據流中，但我仍需要配置每個組件，以便 SSIS 知道我要如何提取和加載數據。右鍵單擊 AW_DM OLE DB 目標組件，然后選擇“編輯”。打開 OLE DB 目標編輯器后，我確保選擇 AW_DM 作為 OLE DB 連接管理器。然后，我展開表名稱下拉列表并選擇 dbo.DimProduct 表。最后，我單擊“映射”選項卡確認映射正確。單擊“確定”確認映射。如果您已經有了可以引用的數據映射，此過程就簡單多了，特別是在處理大型表時。產品維度的 ETL 數據包現已完成。

在 BIDS 中可以輕松執行該數據包。要測試產品維度數據包，請打開該數據包并按 F5。

開發其他數據包

我按照創建產品數據包的方式創建客戶維度數據包。此處將不重述創建此新數據包應遵循的步驟。請自行嘗試創建此數據包。請注意，此數據包使用數據源中的 XML 數據類型列 (Person.Person.Demographics)，這需要您解析出單獨的人口統計相關屬性。要解析 SQL Server XML 數據類型列中的單個值，您可以在 XML 數據類型固有的 Value() 方法中使用 XQuery。請將完成的數據包命名為 DIM_CUSTOMER.dtsx。

為日期維度開發 SSIS 數據包是可選的。由于此維度通常沒有源數據，因此加載它的最簡單方法是使用基本的 T-SQL 腳本。您可以在已完成的解決方案中找到我使用的腳本。

開發 Internet 銷售事實表數據包

Internet 銷售事實表數據包查詢所有的 Internet 銷售，并按照產品、客戶和日期（即訂單日期）返回銷售詳情。與維度表數據包不同，事實表數據包在向事實表加載數據前需要一個額外的步驟，即查詢相應維度表中的代理鍵和智能鍵。您可以創建一個新數據包并將其命名為 FACT_INTERNET_SALES.dtsx。

此數據包的提取部分需要使用圖 4 所示的 T-SQL 代碼查詢 AdventureWorks2008 OLTP 數據庫。

SELECT 
       P.ProductID  
       ,CONVERT(INT,CONVERT( CHAR(4), YEAR(H.OrderDate) ) + RIGHT('0'+ CONVERT(VARCHAR(3),   
            MONTH(H.OrderDate) ),2) + RIGHT('0'+ CONVERT( VARCHAR(3),   
DAY(H.OrderDate) ),2)) AS OrderDateKey  
       ,C.AccountNumber   
       ,SUM(D.OrderQty) AS OrderQuantity  
       ,SUM(D.LineTotal) AS SalesAmount  
FROM 
       [Sales].[SalesOrderDetail] D  
INNER JOIN 
       [Sales].[SalesOrderHeader] H ON (D.SalesOrderID = H.SalesOrderID)  
INNER JOIN 
       [Production].[Product] P ON (D.ProductID = P.ProductID)  
INNER JOIN 
       [Sales].[Customer] C ON (H.CustomerID = C.CustomerID)  
WHERE 
        H.OnlineOrderFlag = 1  
GROUP BY 
        P.ProductID  
        ,H.OrderDate   
        ,C.AccountNumber 

圖 4 用于按產品、日期和客戶劃分的 Internet 銷售的 T-SQL 代碼

在此數據包的控制流圖面中創建一個新的數據流。打開數據流設計器，創建一個 OLE DB 源組件。將該組件命名為 AW_OLTP，使用圖 4 中的查詢作為其源。此查詢會生成 Adventure Works 銷售表中 OrderQuantity 和 SalesAmount 兩個度量值的聚合（總和）。

現在，您需要配置一個查詢轉換。將查詢轉換組件的兩個新實例從工具箱中拖動到數據流設計器圖面，并將其重名為“產品”和“客戶”。配置第一個實例（產品），使其查詢產品維度表中的 ProductKey。配置方法為將維度表的 AlternateKey 與從 AW_OLTP 源查詢傳入的 ProductID 字段相聯接。

配置第二個實例（客戶），使其查詢客戶維度表中的 CustomerKey。配置方法為將維度表中的 AlternateKey 與從 AW_OLTP 源查詢傳入的 AccountNumber 字段相聯接。

最后步驟

最后一步是將數據加載到 FactInternetSales 事實表，并將每個維度的自然鍵替換為查詢轉換找到的代理鍵。拖放 OLE DB 目標組件的新實例，并將其命名為“AW_DM”。編輯該 OLE DB 目標組件，選擇 AW_DM 連接管理器。然后，選擇 dbo.FactInternetSales 表并單擊“映射”選項卡。確保映射如圖 5 所示。單擊“確定”完成此數據包邏輯。

圖 5 用于 Internet 銷售事實表的 OLE DB 目標映射

要測試 Internet 銷售事實數據包，請在 BIDS 中打開此數據包并按 F5。

現在，您基本了解了維度建模和使用 SSIS 構建按 ETL 設計的數據包。在第三篇系列文章中，您將學習如何使用填充的數據市場創建用于 SSAS 數據庫的維度和多維數據集。多維數據集構建完成后，您可以開發一個 SSIS 數據包。這樣，每次有新數據添加到數據市場中，您都可以在 SSAS 數據庫中持續更新這些對象。當使用單一查詢無法滿足報告要求時，SSIS 甚至能夠準備在 SSRS 報告中顯示的數據。如您所見，SSIS 可以做大量工作來幫助您管理 BI 解決方案，而不僅僅是 ETL 處理。

原文地址

本文來源：微軟TechNet中文站