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

了解要求

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

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

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

使用維度模型

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

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

創(chuàng)建總線矩陣

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

圖 1 Adventure Works 銷售總線矩陣

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

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

在創(chuàng)建數(shù)據(jù)市場的物理表之前，我需要進(jìn)行一些其他規(guī)劃。具體而言，我需要構(gòu)建一個(gè)數(shù)據(jù)映射文檔，以便將數(shù)據(jù)市場架構(gòu)中的每個(gè)目標(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 選項(xiàng)卡。此外，我還創(chuàng)建了 DimCustomer 和 FactInternetSales 數(shù)據(jù)映射。工作簿中的每個(gè)工作表表示數(shù)據(jù)市場中的一個(gè)表。每個(gè)工作表中只有兩列：一個(gè)源列和一個(gè)目標(biāo)列。

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

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

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

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

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

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

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

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

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

開發(fā) ETL 過程

構(gòu)建 BI 解決方案的下一步是設(shè)計(jì)和開發(fā) ETL 過程。我們先回顧一下，ETL 包含從數(shù)據(jù)源提取數(shù)據(jù)、轉(zhuǎn)換數(shù)據(jù)然后再將數(shù)據(jù)加載到目標(biāo)庫的整個(gè)技術(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 項(xiàng)目

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

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

圖 3 BIDS 2008 中的“新建項(xiàng)目”模板

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

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

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

創(chuàng)建兩個(gè)新的數(shù)據(jù)源連接：一個(gè)用于 TECHNET_AW2008SalesDataMart 數(shù)據(jù)庫，另一個(gè)用于 AdventureWorks2008 OLTP 數(shù)據(jù)庫。將這兩個(gè)數(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 項(xiàng)目創(chuàng)建的默認(rèn)數(shù)據(jù)包。（數(shù)據(jù)包就是一個(gè)容器，存儲(chǔ) SSIS 將執(zhí)行的工作流中的所有步驟。）在解決方案資源管理器中右鍵單擊默認(rèn)數(shù)據(jù)包，選擇“重命名”。鍵入“DIM_PRODUCT.dtsx”，然后按 Enter。

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

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

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

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

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

此時(shí)，我已將必需的組件添加到數(shù)據(jù)流中，但我仍需要配置每個(gè)組件，以便 SSIS 知道我要如何提取和加載數(shù)據(jù)。右鍵單擊 AW_DM OLE DB 目標(biāo)組件，然后選擇“編輯”。打開 OLE DB 目標(biāo)編輯器后，我確保選擇 AW_DM 作為 OLE DB 連接管理器。然后，我展開表名稱下拉列表并選擇 dbo.DimProduct 表。最后，我單擊“映射”選項(xiàng)卡確認(rèn)映射正確。單擊“確定”確認(rèn)映射。如果您已經(jīng)有了可以引用的數(shù)據(jù)映射，此過程就簡單多了，特別是在處理大型表時(shí)。產(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)，這需要您解析出單獨(dú)的人口統(tǒng)計(jì)相關(guān)屬性。要解析 SQL Server XML 數(shù)據(jù)類型列中的單個(gè)值，您可以在 XML 數(shù)據(jù)類型固有的 Value() 方法中使用 XQuery。請將完成的數(shù)據(jù)包命名為 DIM_CUSTOMER.dtsx。

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

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

Internet 銷售事實(shí)表數(shù)據(jù)包查詢所有的 Internet 銷售，并按照產(chǎn)品、客戶和日期（即訂單日期）返回銷售詳情。與維度表數(shù)據(jù)包不同，事實(shí)表數(shù)據(jù)包在向事實(shí)表加載數(shù)據(jù)前需要一個(gè)額外的步驟，即查詢相應(yīng)維度表中的代理鍵和智能鍵。您可以創(chuàng)建一個(gè)新數(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)建一個(gè)新的數(shù)據(jù)流。打開數(shù)據(jù)流設(shè)計(jì)器，創(chuàng)建一個(gè) OLE DB 源組件。將該組件命名為 AW_OLTP，使用圖 4 中的查詢作為其源。此查詢會(huì)生成 Adventure Works 銷售表中 OrderQuantity 和 SalesAmount 兩個(gè)度量值的聚合（總和）。

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

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

最后步驟

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

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

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

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

原文地址

本文來源：微軟TechNet中文站