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

了解要求

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

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

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

使用維度模型

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

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

創(chuàng)建總線矩陣

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

圖 1 Adventure Works 銷售總線矩陣

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

創(chuàng)建數(shù)據(jù)映射

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

圖 2 DimProduct 數(shù)據(jù)映射選項卡

每個維度表（日期維度表除外）中都包含名為代理鍵的主鍵（通常為標(biāo)識列）。使用代理鍵的好處之一是在合并多個系統(tǒng)的數(shù)據(jù)時不會出現(xiàn)重復(fù)鍵。

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

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

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

由于我的小型架構(gòu)所需的 ETL 過程相當(dāng)簡單，因此這樣的數(shù)據(jù)映射足以滿足需要。在實際項目中，我會為數(shù)據(jù)映射添加注釋，指出何時需要進(jìn)行復(fù)雜的轉(zhuǎn)換。

構(gòu)建數(shù)據(jù)市場

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

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

開發(fā) ETL 過程

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

在 BIDS 中創(chuàng)建 SSIS 項目

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

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

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

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

創(chuàng)建公共數(shù)據(jù)連接

SSIS 2008 中的另一項出色功能是可以在單個數(shù)據(jù)包之外創(chuàng)建數(shù)據(jù)源連接。您可以定義數(shù)據(jù)源連接一次，然后在解決方案中的一個或多個 SSIS 數(shù)據(jù)包中引用此連接。有關(guān)如何創(chuàng)建 BIDS 數(shù)據(jù)源的更多信息，請參閱“如何：使用數(shù)據(jù)源向?qū)Фx數(shù)據(jù)源（分析服務(wù)）”。

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

開發(fā)維度 ETL

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

接下來，我需要使用預(yù)建的數(shù)據(jù)源來創(chuàng)建本地數(shù)據(jù)包連接管理器。新建兩個引用先前生成的數(shù)據(jù)源的連接管理器。

定義數(shù)據(jù)流以提取并加載

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

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

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

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

在 BIDS 中可以輕松執(zhí)行該數(shù)據(jù)包。要測試產(chǎn)品維度數(shù)據(jù)包，請打開該數(shù)據(jù)包并按 F5。

開發(fā)其他數(shù)據(jù)包

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

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

開發(fā) Internet 銷售事實表數(shù)據(jù)包

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

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

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 用于按產(chǎn)品、日期和客戶劃分的 Internet 銷售的 T-SQL 代碼

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

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

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

最后步驟

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

圖 5 用于 Internet 銷售事實表的 OLE DB 目標(biāo)映射

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

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

原文地址

本文來源：微軟TechNet中文站