次の方法で共有

  • [アーティクル]

ビジネス インテリジェンス

BI ソリューションのデータの基盤を構築します。

Derek Comingore

 

コードのダウンロード: SampleCode2009_08.exe(151 KB)

概要。

  • 次のデータ マートを構築するディメンション、モデル
  • マップの ETL プロセスおよびデータを開発します。
  • ETL の統合サービスの使用
  • 入札価格の統合サービス プロジェクトの作成

内容

理解の要件
ディメンション、モデルの使用
バスをマトリックスの作成
データ マップの作成
データ マートの構築
データ マートを展開します。
ETL プロセスの開発
入札価格で SSIS プロジェクトの作成
共通のデータ接続の作成
ディメンションは、ETL の開発
展開を読み込み、データ フローを定義します。
その他のパッケージの開発
インターネット販売のファクト テーブル パッケージの開発
最後の手順

データはあらゆるビジネス インテリジェンス (BI) ソリューションの最も重要な部分です。 Stacia Misner で説明した"よう 最初に Microsoft BI ソリューションの計画、[BI ソリューションにデータを取得およびそこに 1 回の保守手順いくつか。 BI 専門家の抽出、変換および読み込み (ETL) プロセスの手順を参照します。 BI に重点を置いて、キャリアを追求する予定がない、場合でもでも利用できますの ETL 手法と、ジョブで日常的な決定を通知するデータを管理するツールです。 この記事では、説明する設計および ETL を独自の BI ソリューションを実行する SQL Server 2008 の統合サービス (SSIS) 使用する方法を説明する単純なデータ マートを構築する方法します。

理解の要件

同様、任意の IT プロジェクト、ETL プロジェクトを開始する最適な方法は、BI ソリューションの一般的な要件を理解するビルドし動作、データにそれらの要件をサポートするには最適な方法を決定します。 データ系列の最初の記事、会社が回答する必要がいくつかの質問と、分析の要件を記述して必要な架空の会社あなたは Adventure Works BI ソリューションの段階を設定します。 これらの質問別の視点からその商品売上を理解する Adventure Works の必要性を強調表示: (販売店またはインターネット) の配布チャネル、やがて、製品の需要の変化および売上実績と売上予測製品、営業担当者、地域および販売店の種類の違いによって収益。 これらの質問の答えを Adventure Works の配布チャネルに適応、製造プロセスを最も満たす要求、方法、および販売戦略の変更は、会社の販売目標を達成に役立つ方法増加利益に集中する必要があります、会社の決定に役立ちます。 BI ソリューションを SQL Server レポート サービス (SSRS) を追加するとこれらビジネス質問に答えて、データのサポートがわかります。

これらの要件に注意するくださいとあなたは Adventure Works のデータ マートをデザインを開始する前に必要のあるビジネスの観点から、情報モデルにします。 つまり、データ マートの設計に基づいて必要があります、ユーザーが質問の方法、データは、データ ソースのものではなく。

この資料に記載のコード サンプルについて、SQL Server 2008 あなたは Adventure Works OLTP サンプル データベース必要です。

ディメンション、モデルの使用

データ マートは通常も分析に適したデータベース スキーマは、次元モデル設計を使用して作成されます。 (ディメンション モデリングを学ぶの優れたリソースが kimballgroup.com.) ディメンショナル モデルのユーザーが使い慣れた方法でデータを表示でき、大量のデータを照会用として最適化されているデータ構造を構築します。 データベース エンジンは、急速に選択し、クエリ時に、膨大な量のデータを効率的に集計を支援する、データを denormalizing してこの最適化を実現できます。 あなたは Adventure Works ソリューションの正規化されていないスキーマでテーブルの 2 つの種類が含めるされます: ディメンションとファクト。 ディメンション テーブルにはビジネス エンティティおよび販売店や製品などのオブジェクトに関する情報が格納されます。 必要がある数値の販売データの使用して、ファクト テーブルの集計には、数値のメジャーおよびファクト、ディメンション テーブルに関連するキーが含まれます。 について説明しますよりについてこの資料の後半のファクト テーブル。

スキーマの 2 つのディメンション、モデルのテーブルを実装できます: 星とスノーフレーク。 簡単に言えば、スター スキーマは、クエリが順番 1 つの結合を持つファクト テーブルに関連する各ディメンションは、に関する 1 つのテーブルを使用します。 スノーフレーク スキーマ各ディメンションの 2 つ以上のテーブルを使用してその結果、データすべてを表示するクエリで複数の結合が必要です。 によりカスケード結合のこのコレクション、クエリ多くの場合、実行遅くスター スキーマではよりスノーフレーク スキーマでします。 この資料で、デザインをシンプルし、スター スキーマを使用する説明します。

バスをマトリックスの作成

、、ディメンション モデリング プロセスの一環としてする行列を作成バスの販売に関連付けられたディメンションを識別するあなたは Adventure Works の BI ソリューションのフォーカス。 あなたは Adventure Works が 2 つの販売チャネルを覚えて: 販売店に販売数や、インターネット経由の個々 の販売を wholesale。 使用するしてもバス マトリックスの各ディメンションの売り上げ高の一方または両方の種類に関係の識別。 図 1 はあなたは Adventure Works の売り上げ高のマイ サンプル バス行列を示します。

あなたは Adventure Works の売り上げ高の 1 のバス行列を図します。
冒険は売上バス マトリックス 日付 製品 顧客 昇格 地理的な 販売店 販売区域 従業員 通貨
インターネット販売 X X X X     X   X
再販業者の売り上げ高 X X   X   X X X X

自分次手順では、ソリューションのメジャーの決定をします。 メジャーは分析に必要な数値値です。 ドルの販売や製品のコストのソースから直接取得できるまたは拡張、営業時間、金額、数量を掛けることなど、計算によって派生します。 またする各ディメンションに含める属性を決定する必要があります。 属性が日付ディメンション内に (対応テーブルの列に) 使用できるグループまたはフィルターのデータに国/地域、営業担当地域ディメンションに年などの分析、ディメンション内の個々 の要素。 すべて識別されたメジャーまたはこの資料内のディメンションの属性をについて詳しく説明するしません-実施するには、識別プロセス必要があるだけで実現します。

データ マップの作成

データ マートの物理テーブルを作成する前にするいくつか追加の計画を行う必要があります。 あなたは Adventure Works OLTP ソース システム (ダウンロードし、p Stacia Misner の資料で説明したインストールが可能な AdventureWorks2008 データベースで、データ マートのスキーマ内の各コピー先列列にマップするデータ マップ ドキュメントを作成する具体的には。 31)。 さまざまなアプリケーションを使用して、データのマップを作成することができます。 形式はコンテンツとして重要でありません。 Microsoft Office Excel でデータのマップを作成したいのです。 図 2 [データのマップで作成する [DimProduct] タブを示します。 DimCustomer と FactInternetSales のデータを作成したマップします。 各ブックのシートはいずれかの個人用データ マートでテーブルを表します。 1 枚にだけである 2 つの列: 変換元列と変換先列に 1 つです。

fig02.gif

図 2 DimProduct データ マッピング] タブ

各ディメンション テーブル (日付ディメンション) の以外は主キー、代理キー (通常は、ID 列) と呼ばれるを持っています。 代理キーを使用しての 1 つ利点は、重複するキーの危険性なく複数のシステムからデータを結合できますです。

ディメンションのテーブルにも、別のキー列があります。 これらの代替キーは、ビジネス キーとも呼ばれる、自然なキーを表します。 これらは、ソース システムの識別子です。 たとえば、Customer] ディメンションの CustomerAlternateKey 列 Sales.Customer テーブルであなたは Adventure Works OLTP データベース フィールド宣言にマップします。 これらのキーを格納するディメンション テーブルに、ことによって各ディメンションに対して、ETL 処理を実行するたびに、ソースから抽出したレコードを持つディメンションで既にレコードと結合する方法があります。

ほとんどすべてのデータ マートがビジネス分析は日、週、月、四半期または年でメジャーの変更を比較することがよくありますので、日付ディメンションには含まれます。 日付ディメンションに、SQL Server の IDENTITY–based キーを使用してその理由を適用しないように、ことはほとんどありません、ソース システムから取得します。 代わりに、SQL Server の整数型の列として格納 YYYYMMDD の形式を持つスマート キーと呼ばれるを使用するでしょいます。 スマート キーは、ロジックや SQL Server では、IDENTITY 列など、自動インクリメント キーではなくにスクリプトから生成されるキー。

日付ディメンションが、通常、ソース テーブルに対応していないことに注意して保持します。 代わりに、スクリプトを使用してレコードをテーブルに読み込むデータを生成します。

マイ小さなスキーマに必要な ETL プロセスはかなり単純なためにはマイ データ マップは正常です。 実際のプロジェクトでは複雑な変換が必要なときに指摘するには、データ マップ注釈付けます。

データ マートの構築

論理モデルの作成が完了を ETL プロセスに読み込まれますが、物理テーブルおよびホスト データベースこれらのテーブルを作成する必要があります。 基本的な T-SQL スクリプトおよび作成する両方データベースとその関連ディメンションのファクト テーブル使用します。 (でサンプル BI ソリューションの付属ダウンロードで T-SQL スクリプト全体を検索できます。 2009 コードをダウンロードします。).

この資料のためには、作成販売データ マートのスキーマは、全体のサブセットのみように SSIS での ETL プロセス全体をカバーすることができます。 小さい、スキーマのバージョンがインターネット Sales ファクト テーブルの OrderQuantity および SalesAmount、メジャーだけを含めます。 さらに、[小さいスキーマには簡略化されたバージョン、顧客、製品、および日付のディメンション テーブルにはが含まれています。

データ マートを展開します。

データ マートを展開するには、だけでするインスタンスの SQL Server インスタンスで新しいテーブルを作成する前に記述する T-SQL の実行をする必要があります。 T-SQL を実行するには、Start\All Programs\Microsoft の SQL Server 2008\SQL Server 管理 Studio をクリックして SQL Server の管理 Studio (SSMS) を作成します。 SSMS が開いたら、する自分専用の SQL Server インスタンスの名前を入力して [Windows 認証を使用して [接続] ダイアログ ボックスで接続する] をクリックします。 SQL Server の管理 Studio を使用して TECHNET_AW2008SalesDataMart.sql ファイルを開くをスクリプトを実行します。

ETL プロセスの開発

設計および開発 ETL プロセスは、次のステップ、BI ソリューションの構築にです。 確認するには、ETL には変換、および、先のリポジトリにロードしに、データ ソースからデータが抽出するすべての技術的なプロセスが含まれます。 一般的には、ETL プロセス BI ソリューション内のフラット ファイルおよび OLTP オペレーション データベースからデータを抽出、ディメンショナル モデル (など、スター スキーマ) に適合するデータを変更し、結果のデータをデータ マートに読み込みます。

入札価格で SSIS プロジェクトの作成

最初の手順、ETL プロセスの開発には、入札でビジネス インテリジェンス開発 Studio (価格) に新しいプロジェクトを作成するは。 入札価格は SQL Server 2008 が付属し、インストール プロセス中に、ワークステーション コンポーネント オプションを選択するとのインストールします。 入札価格には、SSIS、SSAS SSRS プロジェクト テンプレートが含まれます。 Visual Studio は同様ソース管理の統合もサポートします。

入札価格を開始するには SQL Server の Start\Programs\Microsoft 2008\Business インテリジェンス開発 Studio に移動し、File\New プロジェクトを選択します。 図 3 に示す新しいプロジェクト テンプレートが表示する必要があります。

fig03.gif

図 3 の入札価格 2008年で新しいプロジェクト テンプレート

テンプレート ペインでの統合サービス プロジェクトを選択し、[名] テキスト ssis_TECHNET_AW2008 を入力して [OK] をクリックします。 入札価格が SSIS プロジェクトを開く、表示されます。

共通のデータ接続の作成

SSIS 2008 の別のすばらしい機能は、個々 のパッケージの外部データ ソース接続の作成機能です。 1 回、データ ソース接続を定義し、それをソリューション内の 1 つまたは複数の SSIS パッケージで参照できます。 入札価格データ ソースの作成の詳細を参照して" 方法: データ ソース ウィザード (分析サービス) を使用したデータ ソースを定義する".

2 つの新しいデータ ソース接続を作成: TECHNET_AW2008SalesDataMart データベースと AdventureWorks2008 OLTP データベースの別。 AW_DM.ds と AW_OLTP.ds、データ ソース接続の名前をそれぞれ。

ディメンションは、ETL の開発

ETL、製品ディメンションを読み込むは非常に簡単です。 あなたは Adventure Works Production.Product テーブルからデータを抽出を TECHNET_AW2008SalesDataMart データベースにデータを読み込む必要があります。 最初に、する、既定のパッケージの入札価格が SSIS プロジェクトに対して作成変更する必要があります。 パッケージは、SSIS に実行されるワークフローの手順をすべてのコンテナーです。 ソリューション エクスプローラーで、既定パッケージを右クリックし、名前の変更を選択します。 DIM_PRODUCT.dtsx を入力して Enter を押します。

次に、ローカルのパッケージ作成済みのデータ ソースを使用して接続マネージャーを作成する必要があります。 以前のバージョンに組み込まれて、データ ソースを参照する 2 つの新しい接続マネージャーを作成します。

展開を読み込み、データ フローを定義します。

SSIS にはデータ フロー タスク カプセル化、単純なディメンションの ETL を実装するすべてにはが含まれます。 だけが制御フロー デザイナーのサーフェイスにツールボックスから、データ フロー タスクをドラッグし、(抽出と負荷) 用 EL にタスクをリネームします。 デザイナーで、データ フロー タスクを右クリックし、編集] を選択します。 入札価格には、データ フロー デザイナーが表示されます。

製品ディメンション パッケージの抽出部分が、AdventureWorks2008 Production.Product テーブルをクエリー必要です。 このタスクをセットアップするには、データ フロー デザイナー画面にツールボックスから、OLE DB ソース コンポーネントをドラッグするし AW_OLTP にし、OLE DB ソース コンポーネントを変更します。

次に、[パッケージのデータ マートに読み込むの読み込み部分定義します。 だけがデータ フロー デザイナー画面に、OLE DB 変換先のコンポーネントの新しいインスタンスをドラッグし、AW_OLTP 名前を変更します。 OLE DB ソース (AW_OLTP) をクリックし、ドラッグ表示される緑色の矢印、OLE DB ソースで AW_DM OLE DB 先コンポーネント、2 つのコンポーネントを接続するにします。

この時点で、必要なコンポーネント、データ フローに追加しましたがまだ SSIS を抽出し、データを読み込む方法を知っているようにに各コンポーネントを構成します。 AW_DM OLE DB 先コンポーネントを右クリックし、[編集] をクリックします。 OLE DB 変換先エディターを使って開かれている、する AW_DM はオン確認 OLE DB 接続マネージャーとしてします。 ドロップダウン テーブルの名前を展開を表示し dbo.DimProduct の表は、選択します。 最後が [マッピング] タブのマッピングが正しいことを確認するをクリックします。 [OK] のマッピングをクリックするとします。 このプロセスは大きなテーブルで作業している場合に特に参照できるデータ マップがある場合にはるかに簡単です。 製品ディメンションの ETL パッケージが完成しました。

入札価格の内部パッケージを簡単に実行することができます。 製品ディメンション パッケージをテスト パッケージを開く、F5 キーを押します。

その他のパッケージの開発

製品パッケージ行いましたと同様に、顧客ディメンション パッケージを作成するとします。 この新しいパッケージの作成に必要な手順を繰り返し説明するされません。 自分で作成しようとする必要があります。 このパッケージの個々 の人口統計関連の属性が解析する必要がありますソースをでに XML データの入力列 (Person.Person.Demographics) を使用することを確認します。 SQL Server の XML データの入力列から個々 の値を解析するには、XML データ型のネイティブの値 () メソッドで、XQuery を利用できます。 名前、完成したパッケージの DIM_CUSTOMER.dtsx です。

日付ディメンションの SSIS パッケージの開発はオプションです。 このディメンションはソース データがある、ため、読み込む簡単は [基本的な T-SQL スクリプトを使ってです。 完成したソリューションで、スクリプトを使用して検索できます。

インターネット販売のファクト テーブル パッケージの開発

インターネット販売のファクト テーブル パッケージすべてのインターネット販売のクエリし、それら、ブレークダウンされた販売を返す顧客と日付 (つまり、受注日) です。 ディメンション、パッケージとは異なり、サロゲートとファクト テーブルにデータを読み込む前に対応するディメンション テーブル内のスマート キーを検索する追加手順が、ファクト テーブル パッケージが必要です。 新しいパッケージを作成し、FACT_INTERNET_SALES.dtsx という名前をことができます。

ようにの T-SQL コードを用いて AdventureWorks2008 OLTP データベースを照会する必要が、パッケージの抽出部分 図 4 .

製品、Date およびユーザーのインターネットの売り上げの T-SQL コードを図 4

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 のメジャーの集約 (合計) を作成します。

ここで、参照変換を構成する必要があります。 データ フロー デザイナー画面に、ツールボックスからの参照変換コンポーネントの 2 つの新しいインスタンスをドラッグして製品と顧客名にします。 1 つ目の構成 (製品) AW_OLTP ソース クエリから着信 [商品コード] フィールドに、ディメンション テーブルの AlternateKey を結合して、製品ディメンション テーブルに ProductKey を検索します。

2 番目の構成 (顧客) AW_OLTP ソース クエリから着信宣言フィールドに、ディメンション テーブルの AlternateKey を結合して、顧客ディメンション テーブルに CustomerKey を検索する 1 つです。

最後の手順

最後に、参照変換が見つかりました、代理キー各ディメンションの自然なキーを置き換える、FactInternetSales、ファクト テーブルに、データを読み込むがします。 OLE DB 変換先のコンポーネントの新しいインスタンスをドラッグし、AW_DM という名前をします。 OLE DB 変換先のコンポーネントを編集し、AW_DM 接続マネージャーを選択します。 次に、dbo.FactInternetSales テーブルを選択し、マッピング タブをクリックします。 されるように、マッピング 図 5 のようです。 [OK] をクリックして、パッケージのロジックを完了をします。

fig05.gif

図 5 の ファクト インターネット SalesFact テーブルの OLE DB の変換先のマッピング

インターネット販売のファクト パッケージのテスト、入札価格で、パッケージを開く F5 キーを押します。

ディメンション モデリングおよびパッケージを SSIS の ETL デザインの構築の基礎を理解するようになりました。 このシリーズで、3 番目の記事では、データが入力されているデータ マートを使用してディメンションと SSAS データベースのキューブを作成する方法について説明します。 キューブを構築した後継続的に各時間の新しいデータは、データ マートに追加された SSAS データベース内のこれらのオブジェクトを更新するには、SSIS パッケージを開発できます。 SSIS できますでもデータを準備 SSRS レポートに表示レポート要件できません、1 つのクエリで満たされたはとき。 ご覧のとおり、SSIS だけの ETL 処理よりも、BI ソリューション管理に役立つより多く実行できます。

Derek Comingore 上級のアーキテクト ComFrame. SQL Server MVP と、認定の担当者を SQL Server BI を含むいくつかの Microsoft テクノロジで彼地域および国の SQL Server ユーザー グループに話すあり、発行作成者 SQL Server のトピック。 Derek では、構築と、エンタープライズ クラスのデータ ウェアハウジングと SQL Server での BI ソリューションを他の Microsoft BI ソフトウェア製品と共に提供について説明します。 Derek でアクセスできます。 dcomingore@comframe.com.