Share via

  • 發行項

商務智慧

建立商業情報方案的基礎資料

Derek Comingore

 

可在程式碼下載： SampleCode2009_08.exe(151 KB)

一眼：

  • 下列維度模型來建置資料超市
  • 開發 ETL 程序和資料對應
  • 使用 「 整合服務的 ETL
  • BIDS 中建立一個整合服務的專案

內容

瞭解需求
使用維度的模型
建立匯流排矩陣
建立資料的對應
建立資料超市
部署資料超市
開發 ETL 程序
建立一個 SSIS 專案中 BIDS
建立一般的資料連接
開發 [ETL 維度
定義要擷取並載入資料流
開發其他的封裝
開發網際網路銷售事實資料表封裝
最後的步驟

資料是任何商務智慧 (BI) 解決方案的最重要的部分。 如所述 Stacia Misner 的" 規劃您的第一個 Microsoft BI 解決方案"擷取資料到您的商業情報解決方案，和維護它，一旦有很牽涉到幾個步驟。 商業情報的專業人員，請參閱這些解壓縮、 轉換和載入 (ETL) 處理序的步驟。 即使您不打算追求焦點放在商業情報一個工作，您可以仍然利用 ETL 技術和工具來管理的資料，您需要通知日常您在您的工作中所做的決策。 此的文件中我將告訴您如何設計和建置簡單的資料超市來說明您可以如何使用 SQL Server 2008 整合服務 (SSIS) 來執行 ETL 自己商業情報的解決方案。

瞭解需求

若要啟動的 ETL 專案，最好是要了解 BI 方案的一般的需求與任何的 IT 專案要建置並再執行 [回到資料的方式，以判斷如何用最佳方式來支援這些需求。 數列中的第一個文件設定虛構的公司的 Adventure Works 必須藉由為公司要回答的幾個問題描述分析需求 BI 解決方案的階段。 這些問題反白顯示需要瞭解其產品銷售，從不同觀點 Adventure Works： 由通訊通道 (轉銷商或網際網路)、 經過一段時間，產品需求的變更和實際銷售額與銷售預測，由產品、 銷售員、 地理區域和轉銷商型別之差異的獲利性。 在這些問題的答案會幫助決定如何最符合其製造程序要求以適應以及銷售策略中的變更可能會有符合其業務目標公司幫助公司應該著重以增加利潤的通訊通道的 Adventure Works。 您會看到資料如何支援回答這些業務問題，當您將 SQL Server 報告服務 (SSRS) 新增到 BI 方案。

我開始設計資料超市記住這些需求 Adventure Works 之前，我要從商務的觀點來看所需要的資訊模型。 亦即資料超市的設計應該根據，使用者會詢問其問題，而不是資料的方式來自資料來源的方法。

您將需要 SQL Server 2008 Adventure Works OLTP 範例資料庫的程式碼範例在這份文件中找到。

使用維度的模型

資料超市通常是使用一個維度的模型] 設計即也適用於分析資料庫結構描述來建置。 (好維度模組化的學習資源是 kimballgroup.com.) 維度的模型的使用者，熟悉的方式呈現資料，並它幫助您建立最佳化查詢的資料的最高磁碟區的資料結構。 您可以 denormalizing 幫助，資料庫引擎，快速地選取並有效率地在查詢時，彙總大量資料的資料，以達到這項最佳化。 反正規化結構描述，Adventure Works 方案，在中，我將會包含兩種類型的資料表： 維度和事實。 維度資料表儲存有關商業項目和物件，例如轉銷商或產品的資訊。 我將使用數值的銷售資料，我需要的資料表彙總、 包含數字的量值和維度資料表的相關事實的索引鍵。 我將更多說明有關在本文稍後的事實資料表。

您可以實作維度模型的資料表的結構描述兩種類型： 星狀和雪花式。 簡單的用語星狀結構描述會使用為每個查詢依次與單一的聯結與事實資料表相關的維度資料表。 雪花結構描述用於每個維度的兩個或多個資料表，，因此需要多個聯結，查詢若要查看所有資料中。 這個集合的階層式聯結表示查詢通常執行速度會變慢雪花結構描述比在星狀結構描述中。 基於本文的目的，我會簡化設計，並使用星狀結構描述。

建立匯流排矩陣

維度模組化程序的一部分，我建立匯流排矩陣來協助識別維度關聯銷售，Adventure Works 的 BI 方案的焦點。 請記住 Adventure Works 會有兩個銷售的通道: wholesale 要轉銷商的銷售額與透過網際網路的個別銷售。 我也會使用匯流排矩陣來識別每個維度的銷售的一或兩個類型的關聯性。 [圖 1 顯示我的範例匯流排矩陣 Adventure Works 銷售。

[圖 1 匯流排矩陣 Adventure Works 銷售
冒險 Works 銷售匯流排矩陣 日期 產品 客戶 升級 地理位置 轉售商 銷售領域 員工 貨幣
網際網路銷售 X X X X     X   X
轉售商銷售 X X   X   X X X X

我的下一步是決定解決方案的量值。 量值是所需的分析的數值。 它們可以直接來自來源，例如銷售金額或產品的成本，或是透過計算，例如數量乘以延伸的銷售數量的貨幣量衍生。 我也需要決定要包含每個維度的屬性。 屬性是在 「 日期 」 維度中的維度 (對應到資料表中資料行) 您可以使用群組或篩選的資料來進行例如國家/地區銷售領域維度或年中的分析中個別項目。 我不會詳細說明每個識別的量值] 或 [這份文件中的維度屬性 — 只知道需要進行識別處理程序。

建立資料的對應

在建立資料超市的實體資料表之前，我需要進行一些額外的規劃。 特別是，需要製作資料對應文件對應到資料行的資料超市結構描述中的每個目的資料行，在 Adventure Works OLTP 系統中來源 (AdventureWorks2008 資料庫，您可以下載並安裝中所述 Stacia Misner 文件上 p。 31)。 您可以使用各種應用程式來建立資料對應。 格式不是內容與一樣重要。 我要開發 Office Excel 中的資料對應。 圖 2 ] 顯示我的資料對應中建立 [DimProduct] 索引標籤。 也建立 DimCustomer 和 FactInternetSales 資料對應。 活頁簿中的每個工作表表示其中一個我的資料超市中的資料表。 每張，我只是有兩個資料行： 一個用於來源資料行，另一個目的資料行。

fig02.gif

[圖 2 DimProduct 資料對應] 索引標籤

(除了為日期維度) 的每個維度] 資料表有主索引鍵稱為 Surrogate 索引鍵 (通常是識別資料行)。 使用代理鍵的一個好處是您可以結合多個系統不重複的索引鍵的風險中的資料。

維度資料表也有其他索引鍵資料行。 這些替代的機碼代表有時也稱為業務索引鍵的自然金鑰。 這些是來源系統的識別項。 就說 「 客戶 」 維度中的 [CustomerAlternateKey] 欄位會對應到 Sales.Customer 資料表中，Adventure Works OLTP 資料庫欄位 AccountNumber。 藉由儲存在維度資料表中的這些機碼，我有能夠符合已經在維度中的記錄時執行 ETL 程序的每個維度，從來源擷取的記錄。

幾乎每一個資料超市包含日期維度，因為商業分析通常比較量值的變更日期、 週、 月、 季或年。 「 日期 」 維度幾乎永遠不會是來自一個的原始檔系統因此理由使用 SQL Server IDENTITY–based 機碼不適用。 而，我要使用什麼稱為智慧金鑰以 YYYYMMDD 儲存為 SQL Server 整數資料行的格式。 智慧索引鍵是索引鍵由邏輯或相較於例如 IDENTITY 資料行在 SQL Server 中的自動遞增索引鍵的指令碼產生的。

請記住，「 日期 」 維度通常並不會對應到來源資料表。 而是，我會使用指令碼來產生資料載入到資料表的記錄。

因為我小的結構描述所需的 ETL 程序相當簡單，我的資料對應是正常的。 在真實世界的專案中我會註解資料對應來指出需要複雜的轉換時。

建立資料超市

既然邏輯模型是完成，我需要建立 ETL 程序將會載入該實體資料表和這些資料表的主應用程式資料庫。 我將使用基本的 T-SQL 指令碼，來建立我的資料庫和其相關聯的維度和事實資料表。 您可以在隨附的下載中找到整個的 T-SQL 指令碼，範例 (在商業情報解決方案 2009 程式碼下載項目).

基於本文的目的，我建置整個銷售資料超市結構描述的子集合，讓我可以涵蓋整個 ETL 程序在 SSIS 中。 在較小的結構描述版本，我會包含只在 OrderQuantity 和 SalesAmount 的量值網際網路銷售事實資料表。 此外，我較小的結構描述包括客戶]、 [產品] 和 [日期] 維度資料表的簡化的版。

部署資料超市

若要部署資料超市，我只需要執行在 T-SQL 我稍早撰寫具現化新的資料表在 SQL Server 執行個體上。 若要執行 T-SQL 的我開始 SQL Server 管理 Studio (SSMS) 按一下 Start\All Programs\Microsoft SQL Server 2008\SQL 伺服器管理 Studio。 SSMS 開啟後我會輸入我指定的 SQL Server 執行個體的名稱，再按 [連線] 對話方塊中使用 Windows 驗證連線。 使用 SQL Server 管理 Studio 開啟檔案 TECHNET_AW2008SalesDataMart.sql 並執行指令碼。

開發 ETL 程序

設計和開發 ETL 程序是建置 BI 方案在下一個步驟。 若要檢視，ETL 包含來自資料來源轉換，並再載入到目的儲存機制來擷取資料的所有技術處理序。 通常，BI 方案內的 ETL 程序，從一般檔案和 OLTP 操作資料庫擷取資料、 改變成維度的模型結構 (例如，星狀描述)，資料然後將資料超市中載入產生的資料。

建立一個 SSIS 專案中 BIDS

開發 ETL 程序之第一個步驟是建立新專案的商務智慧開發 Studio (BIDS)。 BIDS 附有 SQL Server 2008，並安裝您選取 [工作站元件選項在安裝過程時。 BIDS SSIS、 SSAS 和 SSRS，包含專案範本。 它也支援來源的控制項整合一樣 Visual Studio。

啟動 BIDS、 移至 Start\Programs\Microsoft SQL Server 2008\Business 智慧開發 Studio]，然後選取 [File\New 專案]。 您應該會看到 [圖 3 ] 所示的 [新增專案] 範本。

fig03.gif

[圖 3 BIDS 2008 中的新專案範本

[範本] 窗格中選取整合服務專案]，輸入 [在 [名稱] 文字方塊中的 [ssis_TECHNET_AW2008，然後按一下 [確定]]。 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，然後按 [輸入。

接下來，我需要建立本機封裝使用預先建立的資料來源的連接管理員。 建立兩個新的連接管理員參考先前建置的資料來源。

定義要擷取並載入資料流

SSIS 包含一 「 資料流量工作封裝我需要實作簡單的維度，ETL 的一切。 我只要將資料流程工作拖曳從 [工具箱] 拖曳控制流程設計工具的介面，再重新 EL 工作命名 (用於擷取和載入)。 用滑鼠右鍵按一下在設計工具中，資料流程工作，然後選取 [編輯]。 BIDS 現在會顯示資料流程的設計工具。

產品維度封裝擷取部分需要查詢 AdventureWorks2008 Production.Product 資料表。 若要設定這項工作設定，我從工具箱拖曳至 [資料流程] 設計工具介面上拖曳的 OLE DB 來源元件]，然後重新命名 AW_OLTP OLE DB 來源元件。

下一步]，我定義了我封裝以載入資料超市載入部分。 我只是將新的 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 資料型別欄位中的個別值，您可以利用的 XQuery 與 XML 資料型別的原生的值 () 方法。 名稱已完成封裝 DIM_CUSTOMER.dtsx。

開發 「 日期 」 維度的 SSIS 封裝是選擇性的。 因為此維度通常沒有來源資料，最容易將其載入的方法是使用基本的 T-SQL 指令碼。 您可以在已完成的方案中找到使用指令碼。

開發網際網路銷售事實資料表封裝

網際網路銷售事實資料表封裝所有網際網路銷售都查詢，並傳回依產品，細分這些銷售客戶和日期 (亦即，順序日期)。 與維度封裝，不同事實資料表封裝會需要額外的步驟來查閱對應的維度資料表，才能將資料載入事實資料表中的智慧索引鍵與代理。 您可以建立新的封裝，並命名 FACT_INTERNET_SALES.dtsx。

封裝擷取部分需要查詢使用 T-SQL 程式碼所示 AdventureWorks2008 OLTP 資料庫 [圖 4 .

網際網路銷售產品]、 [日期] 和 [客戶] 的 [圖 4 T-SQL 程式碼

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

建立一個新的資料流量任務，在封裝的控制流程介面中。 開啟 [資料流程設計工具中，並建立的 OLE DB 來源元件。 名稱元件 AW_OLTP，並使用它的來源與所用查詢在 [ 圖 4] 。 這項查詢會產生 Adventure Works 銷售資料表中找到 [OrderQuantity 和 SalesAmount 量值的彙總 (加總)。

現在您需要設定查閱轉換。 將查詢轉換元件的兩個新的執行個體從工具箱拖曳至資料流程的設計工具介面，並命名它們，產品和客戶。 設定第一個 (產品) 來查詢產品維度資料表中 ProductKey 藉由加入 [連入產品編號] 欄位中的維度資料表的 [AlternateKey，從 AW_OLTP 來源查詢。

設定第二個其中一個 (客戶)，客戶維度資料表中 CustomerKey 藉由從 AW_OLTP 來源查詢中加入連入 AccountNumber 欄位的 [AlternateKey 維度資料表的查詢。

最後的步驟

最後一個步驟，是載入 FactInternetSales 事實資料表取代查閱轉換找到的代理索引鍵的自然的每個維度索引鍵的資料。 拖曳的 OLE DB 目的地元件的新執行個體，並命名 AW_DM。 編輯的 OLE DB 目的地元件，並選取 AW_DM 連接管理員。 接下來，選取 dbo.FactInternetSales 資料表，然後按一下 [對應] 索引標籤。 請確定對應看起來像 [圖 5 ] 中。 按一下 [確定] 以完成封裝的邏輯。

fig05.gif

[圖 5 事實網際網路 SalesFact 資料表的 OLE DB 目的地對應

若要測試網際網路銷售事實封裝、 BIDS 中開啟封裝並按下 F5 鍵。

現在您了解維度的模型和建置與 SSIS 的 ETL 設計封裝的基本概念。 此系列中第三個文件，將教您如何建立維度和 Cube 為 SSAS 資料庫使用填入的資料超市。 一旦您建立 Cube，您可以再開發來更新這些物件持續在每次新的資料會加入至資料超市 SSAS 資料庫中的 SSIS 封裝。 報表需求不會符合使用單一查詢時 SSIS 甚至可以顯示在報表中 SSRS 準備資料。 如您見 SSIS 可以進行更多可協助您管理 BI 方案比只 ETL 程序。

Derek Comingore 是一個資深架構設計人員與 ComFrame. SQL Server MVP 以及數個的 Microsoft 技術包括 SQL Server BI，在 「 認證專業人員他在本機和國家 (地區) 的 SQL Server 使用者群組，一個發行的作者，SQL Server 的主題。 Derek 著重於建置及提供企業級資料倉儲和 BI 解決方案與 SQL Server，連同其他 Microsoft BI 軟體產品。 您可以到達 Derek 在 dcomingore@comframe.com.