SQL Server 技術資料
技術校閲者 : Donald Farmer、Grant Dickinson
パートナー : Intellinet
適用対象 : SQL Server 2005

ダウンロード

Project REAL ETL Design Practices.doc
656 KB ( Microsoft Word ファイル)

概要 : SQL Server 2005 Integration Services (SSIS) の実際の運用について説明します。プロジェクト REAL というビジネス インテリジェンスの実装例では、SSIS が実際に運用される大規模なデータの抽出、変換、および読み込み (ETL) プロセスに使用されています。この ETL ソリューションは、数 TB のデータ ウェアハウスをサポートしており、大規模ウェアハウスに取り組むうえでの代表的なデータ処理、構成、および管理メカニズムを採用しています。

トピック

はじめに
データ プロファイル
SSIS 開発環境
まとめ

はじめに

ビジネス インテリジェンス (BI) アプリケーションを開発するには、実行の基盤となる確かなツールが必要です。また、このようなアプリケーションを作成する場合は、開発者と管理者が実装を成功させるためのノウハウ、つまりベスト プラクティスの情報を共有することで、スムーズに作業を進めることができます。プロジェクト REAL は、Microsoft がパートナー数社と取り組んでいるプロジェクトで、Microsoft® SQL Server™ 2005 に基づいてビジネス インテリジェンス (BI) アプリケーションを開発する場合のベスト プラクティスの調査を目的としています。プロジェクト REAL では、この目標を実現するために、実際の顧客シナリオに基づいた実装例を構築します。つまり、顧客データを社内に用意し、そのデータを使用して、顧客企業が実際の展開時に直面するのと同じ問題に取り組みます。具体的には、次のような問題があります。

• スキーマのデザイン (リレーショナル スキーマと Analysis Services で使用されるスキーマ)

• データの抽出、変換、および読み込み (ETL) プロセスの実装

• レポートの作成や対話的な分析に適した、クライアントのフロントエンド システムのデザインと展開

• 実運用に適したシステム規模の決定

• データの増分更新など、日常的なシステムの管理とメンテナンス

プロジェクト REAL では、実際の展開シナリオを扱っているため、ツールの使用方法を完全に理解できます。このプロジェクトでは、大規模企業が実際の展開時に直面するあらゆる問題に対処することを目標としています。

このホワイト ペーパーでは、プロジェクト REAL における SQL Server Integration Services (SSIS) による抽出、変換、および読み込み (ETL) のデザインに重点を置いて説明します。このデザインは、Barnes & Noble の ETL アーキテクチャに基づくものです。このアーキテクチャは SSIS を使用するようにゼロから開発された、SSIS による初の実稼動の ETL 実装です。このソリューションは、データ変換サービス (DTS) をはじめとする ETL ツールを使用するデザインをアップグレードしたものではないため、採用されている方法の多くは DTS による通常の ETL アーキテクチャとは異なっています。このソリューションの目標は、これまでの既成概念の枠を超えて、SSIS による新しいアプリケーション アーキテクチャを利用する汎用的な ETL デザインのベスト プラクティスとなる ETL プロセスをデザインすることでした。このホワイト ペーパーでは、各シナリオで採用しているデザインと、プロジェクト REAL における SSIS の実装の詳細について説明します。

プロジェクト REAL の概要については、ホワイト ペーパー「プロジェクト REAL: 技術概要」を参照してください。プロジェクト REAL の進行中、プロジェクトが完了するまでに多数のホワイト ペーパー、ツール、およびサンプルが作成される予定です。最新情報を入手するには、以下のサイトをチェックしてください。

http://www.microsoft.com/sql/bi/ProjectREAL (英語)

プロジェクト REAL は、Microsoft と、BI 分野の多数の Microsoft パートナーとが協力している試みです。パートナーには、Apollo Data Technologies、EMC、Intellinet、Panorama、Proclarity、Scalability Experts、Unisys (アルファベット順) などが名を連ねています。プロジェクト REAL のビジネス シナリオとソース データ セットは、Barnes & Noble から提供されました。

プロジェクト REAL ETL の目的

どのようなビジネス インテリジェンス (BI) システムでも、レポート作成や分析要件のサポートに ETL 処理が使われています。ETL は、このようなサポートを提供する役割を担うことを念頭に、実装する必要があります。このことで、ETL が果たす役割の重要性が軽減されるわけではありません。レポートされるデータは、ETL 処理によって直接処理されるためです。ETL 実装に際して考慮すべき点は、タイミング、パフォーマンス、処理の正確さが挙げられます。また、ETL デザインのサポート、管理、スケーラビリティ、および拡張性も重要です。実稼動システムでは、ETL に影響する不明点や異常が常に存在します。このため、ETL 処理は容易に変更に対応でき、安定したシステムという最終的な目標を達成できるようにする必要があります。

プロジェクト REAL では、ETL デザインの主な目的を達成するうえで、以下の領域が重要でした。

• ETL 管理 : 管理上の要件をサポートできるよう、ETL メタデータの追跡とレポートが可能なデザインを実装しました。これにより、情報を参照したり、トラブルシューティングを行う場合に、処理の状態を明確に把握できるため、問題を分離し、解決するうえで役立ちます。

• 動的構成 : これは、コア コンポーネントのパブリケーションや配布に、エンタープライズ システムをサポートすることが目的でした。また、ビジネス要件や技術要件の変更に対応できるデザインと、大規模サポート チームや開発チームに適した環境も必要でした。

• プラットフォーム統合 : このためには、BI ソリューションの複数の層と通信できるソリューションのデザインが必要でした。これには、セキュリティ、インフラストラクチャ、リレーショナル構造と OLAP 構造、およびデータを取得するためのレポート作成や分析のツールが含まれます。

• パフォーマンス : パフォーマンスの問題に注意を向けることは、ウェアハウス内で処理および管理されるデータ量を考えると、プロジェクト REAL のソリューションにとって非常に重要でした。最終的に、データは数 TB に達しました。

要約

このホワイト ペーパーでは、主にいくつかの具体的なデザイン上の原則、デザイン プロセスを通じて学んだ教訓、およびソリューションのアーキテクチャの全体像について説明します。参考となる全体像を提供し、ソリューションのデザイン上重要な部分についても説明しています。新しいソリューションが成熟し、SQL Server 2005 プラットフォームを基盤にした開発の進行に合わせて、より詳細で包括的な資料が公開される予定です。今後提供される資料は、概念をさらに詳しく説明し、パフォーマンス デザインを調整し、おそらくより優れたデザイン例を紹介するものになるでしょう。このホワイト ペーパーは、SSIS ベースの BI ETL の有効な参考資料です。ETL の再デザイン、アップグレード、および新たな実装を計画し開発する場合に、BI アーキテクトが使用する道具となり得ます。

SSIS の機能は、ETL の処理にとどまりません。SSIS は、システム統合、情報管理、データ変換にまつわるさまざまな機能を提供します。このホワイト ペーパーでは、ETL 処理に関する SSIS のコア機能である、そのうちの一部の機能のみを取り上げます。

概要

このホワイト ペーパーの例は、プロジェクト REAL の実装に直接関係しています。各例には、ETL 処理に応用する場合に特に SSIS の特徴を端的に表すものが選ばれています。これらの例は、先に説明した目標の一部と、次のような一般的な ETL シナリオを表しています。

• SSIS 開発環境

• ETL の監査とログ記録

• プロパティとデータ ソース管理の動的構成デザイン

• 標準のシナリオと独特のシナリオでのディメンション処理

• ディメンション アソシエーションとファクト テーブルの更新によるファクト テーブル処理

• データ処理アーキテクチャ デザイン

• データ処理の最適化手法

これらの例はすべて、90 日分の日次および週間実稼動データを用いて正常に運用されているプロジェクト REAL SSIS ソリューションに実装されています。処理対象のデータには、休暇シーズンの繁忙期のデータも含まれ、2 年にわたる運用実績により、事例としてこのソリューションが確かな安定性と信頼性を備えていることを証明しています。前述のとおり、プロジェクト REAL ETL の基盤となる "実際の" SSIS ソリューションは、2004 年 11 月から Barnes & Noble において運用されています。これらの例やパッケージの多くは、実装の仕様を詳細に確認できるように、公開中または今後公開される予定です。この情報については、今後プロジェクト REAL の Web サイトにおいて告知されます。また、パッケージについては、業界のカンファレンスで発表され、プロジェクト REAL SSIS デザインのデモが行われる予定です。

データ プロファイル

プロジェクト REAL は基本的に小売システムにまつわるものですが、プロジェクト REAL の ETL は、多くのビジネス シナリオのデータ抽出要件を代表しています。ETL 処理では一般的なことですが、毎日の処理が必要な一連のデータがあり、ソース データへの変更や追加が毎日システムを通じて抽出および処理されます。また、多次元モデルに使われる週次のインベントリのスナップショットの詳細度を管理するための一連の週次プロセスも実行されます。データ プロファイルには、毎日実行されるトランザクションの抽出、ディメンションの更新、毎週実行されるインベントリ ファクト テーブルの管理が含まれます。

ファクト テーブル

リレーショナル モデルは、売上と在庫という 2 つの主要なファクト テーブルを用いた標準的なスター スキーマ デザインです。販売トランザクションは日々収集され、Web 注文も含め小売店全体の詳細な製品販売状況を表しています。処理されるトランザクションは数百万件にのぼり、これは売上ファクト構造に追加される必要があります。このとき、売上レコードの大半は前日の売上のものです。また、システムに遅れて追加される過去の売上についてのレコードもわずかですが存在します。すべての売上は、日次の詳細度で追跡され、ETL プロセスは終日データの複数処理が可能であるようにデザインされています。追跡対象となる主な指標は、各項目レベルのトランザクションの数量と売上高を基にしています。

在庫構造は週次の詳細度の標準のスナップショット ファクト テーブルとしてデザインされ、このテーブルでは在庫状況は毎日更新されますが、管理は従来から一週間の増分ベースで行われています。在庫は、店舗詳細度と流通センター詳細度の両方で、商品レベルまで追跡されるため、毎週数千万行のレコードが作成され、毎日数百万件の変更が必要になります。レポート作成の主な目的の 1 つは、在庫と売上の傾向を把握し、在庫切れの状況を防ぐことにあります。したがって、標準の '手持ち (on-hand)' 数量のほかに、'在庫日数 (days in stock)' ファクトも追跡し、各店舗と流通センターの週次の詳細度で、ある商品の在庫が保持されている日数を特定します。週の終わりが近づくと、在庫レベルの値が複製されて新しい週の初期値として設定されるため、集中した ETL 処理が必要になります。

ディメンション テーブル

ファクト テーブルをサポートするディメンションには、いくつか独特の側面があり、このためにデザインが興味深いものとなったほか、SSIS のさまざまな側面を浮かび上がらせています。製品ディメンションには数百万のメンバがあり、標準的な変更と履歴属性を保持していますが、販売が発生してからでないと、履歴追跡属性や階層の変更は行われないようにする必要もあります。これにより ETL がどのような影響を受けるかについては、後ほど説明します。また、製品ディメンションのほかに、いくつか典型的な小売ディメンションが使われています。ソース データを管理する方法のために、すべてのディメンションで、ファクト処理中にディメンション メンバが不足していた場合、ディメンションのソースが完全に揃い、完全な更新が行えるようになるまで、関連するビジネス キーを持つプレースホルダ レコードがディメンションに追加される必要があります。これは、"推定メンバ" と呼ばれ、ETL の処理に影響します。また、ディメンションのいくつかは、プライマリのトランザクションまたは在庫ソース テーブルから直接データを取得しているため、これらをファクト テーブルに追加するためには特別な処理が必要です。

ディメンションが複雑なことから、SSIS プロセス全体ではツールの柔軟性とスケーラビリティの高さが示され、SQL 2005 プラットフォームを基盤に構築されるさまざまな ETL デザインで有用な参考例となることを目指しています。

SSIS 開発環境

Business Intelligence (BI) Development Studio は、Microsoft Visual Studio® 2005 (VS) を基に構築されています。SQL Server 2000 の Enterprise Manager ベースの UI から大幅に変更されていますが、BI Development Studio の機能は、データベース管理者 (DBA) やデータ アーキテクト、開発者が一様に容易に移行できるように実装されています。VS を基盤としたことによる抵抗を緩和できるよう、UI は BI 開発者のニーズに特化し、簡素化されています。

プロジェクト REAL では、1 つの BI ソリューションで Recurring ETL プロジェクトをサポートしています。このプロジェクトには、増分ベースで実行される SSIS パッケージのすべてが含まれています。図 1 は、共有データ ソースと SSIS パッケージを表示しているソリューション エクスプローラです。各パッケージは、その機能がわかるような名前が付けられています。パッケージには種類がいくつかあります。まず、最初に並んでいる一連の最も単純なパッケージはディメンション パッケージです。それぞれ専用のデータ ソースを持つ各ディメンションに、専用のパッケージがあります。ファクト テーブル パッケージは、同様のデザインですが、それぞれの定期実行スケジュール (日次または週次) を示す名前が付けられています。日次と週次ではビジネス ロジックが異なるため、日次と週次で個別のパッケージが存在しています。たとえば、対象とするファクト テーブルは同じであっても、Fact_Daily_Store_Inventory パッケージは、その週次版である Fact_Weekly_Store_Inventory パッケージとは異なる処理タスクを実行します。

処理の調整を行うパッケージとして、グループ読み込みパッケージと呼ばれる 3 種類目の一連のパッケージが表示されています。これらのパッケージには、処理ビジネス ロジックは含まれておらず、前述のディメンションとファクト パッケージのほか、アプリケーションの監査と再起動処理のワークフローの調整に使用されます。図 2 は、グループ読み込みパッケージの例を示しています。Execute Package タスクは子ディメンションとファクト パッケージの実行を支援し、制御フローはいくつかの処理タスクのワークフローと並列処理の調整に使われます。他には、処理の監査や再起動を支援するタスクがあります。これらについては、後ほど詳しく説明します。

ソース管理の統合

VS を基盤とする BI Development Studio のメリットの 1 つに、ソース管理との統合があります。Microsoft Visual Source Safe® (VSS) に限りませんが、REAL の共有開発環境は VSS を利用して、複数の開発者が同時に同じプロセスの作業を行おうとする開発上の "デッドロック" を防いでいます。VSS の標準の機能には、履歴とバックアップ機能、チェック インやチェック アウトしたパッケージ、データ ソース、ファイルのロック機能、差異を特定するバージョン比較機能などがあります。ソース管理機能のほとんどは、[ファイル] の [ソース管理] メニューに含まれています (図 3 参照)。

Visual Source Safe などのソース管理アプリケーションを使ってソース管理を実装すると、BI Development Studio のオブジェクト関連の機能の多くは、パッケージやデータ ソースを右クリックするだけでアクセスできるようになります (図 4 参照)。

命名方法とレイアウト方法

パッケージのレイアウト、タスクの命名、および注釈の作成時に使用した方法については、触れておいた方がよいでしょう。一貫性を保つため、すべてのタスクと変換の名前は、そのタスクまたは変換の種類を表す 3 ～ 4 文字の略語で始まり、その後にそのオブジェクトの機能を示す 3 ～ 4 文字の語が続きます。これは、ログの詳細をオブジェクト名を使って追跡できるため、監査やログを行ううえで非常に役に立っています。パッケージのレイアウトのフローは、基本的に上から下へと進み、プライマリ制御フローやデータ フローから派生するタスクや変換は右方向に進みます。また、注釈を使って各タスクをより詳細に記述することで、パッケージの説明を補足しています。

ETL の監査とログ記録

親ワークフロー パッケージと子パッケージを利用してパッケージ構造のデザインに統合されているのは、高レベルの実行の詳細を追跡するカスタム監査ステップです。この詳細には、パッケージの開始時刻、終了時刻、エラー発生時刻のほか、データ量および処理の検証に役立つ行カウントが含まれます。SSIS には、さまざまなログ プロバイダに対して詳細なパッケージ実行ログをネイティブに記録できます。これらの詳細ログ エントリは、指定したプロバイダの書き込み先にイベント ドリブン型で非正規形で記録されます。たとえば、データベースがログ プロバイダとして指定されている場合、すべてのログ エントリは任意の 1 テーブルに挿入されます。イベント (OnWarning、OnValidation、OnExecute、OnPreExecute、OnPostExecute など) は、エンジンの実行中の参照ポイントとなります。図 6 は、ログ対象とする詳細イベントの選択画面を示しています。

ログ イベント内の各レコードは、当該タスクまたは変換が実行される関連パッケージの実行 ID に関連付けられます。この実行 ID は、パッケージが実行されるたびに生成される一意の GUID です。想像できるとおり、SSIS ログが最も低レベルの詳細度で有効にされている場合、数千とはいかないまでも数百のエントリがパッケージごとに生成される可能性があります。SSIS ログは、トラブルシューティング目的では、きわめて詳細な情報が得られます。ただし、SSIS イベントを熟知し、適切な方法でパッケージの実行 ID をエンジンが実行する特定のパッケージにマップするようにしていないと、この情報自体の取得や解釈は困難です。

プロジェクト REAL では、組み込みのログ プロバイダを拡張して、ある特定の BI 関連実行監査を行えるようにするだけでなく、さらに詳細なドリルダウン レポートの作成にもログ機能を利用できるようにすることを目標としました。監査構造には、次の機能の実装を目標としました。

• パッケージとグループ読み込みのアソシエーションと識別

• ウェアハウス構造への系列列の追加

• 行カウント検証の監査

• ドリルダウン機能を備えた ETL 処理レポート作成

パッケージとグループ読み込みの追跡

ETL 処理に使われるパッケージの調整を理解しておくことは、BI ソリューションをサポートおよび管理するうえで非常に重要です。したがって、より高レベルの追跡テーブルを作成し、合わせて実行されるパッケージ間のアソシエーションが行えるようにしました。また、単一のパッケージの実行の詳細度でもテーブルを作成しました。これは SSIS ログ機能と多少重複しているようにも思えますが、これによりパッケージ名のある組み込みのログ プロバイダの実行 ID と上位レベルの実行ワークフローのグループ プロセス ID 間とのマッピングを容易に行うことができます。パッケージの監査テーブルには、パッケージの実行ごとに 1 レコードしか格納されないため、レポート作成も簡素化され、ドリルダウン レポート機能も実現されます。

プロジェクト REAL のアプリケーション監査は、主に SQL 実行タスクによって実装されています。ワークフロー パッケージでは、制御フローの最初と最後のステップにより、グループ読み込みの監査を管理します。このグループ読み込みパッケージの例では、制御フロー内でこのプロセスを処理する最初と最後のステップが強調表示されています。

パッケージ ワークフロー監査の 1 つ下のレベルはパッケージ レベルの追跡で、これにも最初と最後の制御フロー ステップを使用した同様のデザインが含まれています。グループ読み込みパッケージ自体がパッケージなので、図 5 を見ると、最初から 2 番目と最後から 2 番目のステップでも SQL 実行タスクの監査を行っています。ディメンション パッケージまたはファクト テーブル パッケージでは、これらが最初と最後のステップになります。

監査の別の主な側面としては、エラーをすばやく特定できることが挙げられます。SSIS の優れた機能の 1 つに、イベント ハンドラの制御フローの概念があります。これは、パッケージ UI の 3 番目のタブから操作できます。このエラーをすばやく特定するという目的では、OnError イベント ハンドラをパッケージ レベルで定義し、これを利用して発生したパッケージ エラーをトラップします。

また、一連のパッケージ変数を使用して、これらの SQL 実行タスクによりデータベースとパッケージ間のメタデータを追跡します。主に使用する変数は、システム変数の system::PackageExecutionID および system::PackageName と、ユーザー変数の user::ETL_Load_ID です。このユーザー変数は、データベース内で作成され、パッケージに返される ID です。また、これらの変数は、SSIS の親変数構成機能を使用して、親パッケージから子パッケージに渡されます。

ウェアハウスへの系列の追加

バッチ ID の ETL_Load_ID は、レポート作成と分離目的で監査メタデータを関連付けるだけでなく、ウェアハウス内のレコード ソースの識別にも使用されています。各ディメンションおよびファクト レコードは特定のデータ読み込みによって取得されています。この列により、その読み込みを識別します。これは、データ系列の特定と検証を行う場合や、データが破損したために手動での修正が必要になる場合に便利です。

バッチ ID は、派生列変換を使用して、ソース データを抽出した直後にデータフローに追加します。したがって、下流の変換は更新、挿入、追跡にこの列を使用できます。また、レコードにこのメタデータを追加するために必要になるオーバーヘッドはほとんどありません。

行カウントの追跡

データの評価を行うことで、安心が得られます。データの評価は、管理やトラブルシューティングを行うために BI ソリューションには欠かせない操作であると同時に、ユーザーも安心してシステムを利用することができます。ユーザーがデータを信頼できなければ、ソリューションは失敗に終わる危険性があります。プロジェクト REAL では、行カウントをデータ評価の 1 つとして取り入れています。このレベルの評価は実行されるべき操作のサブセットを表すものに過ぎませんが、第 1 層の評価としては信頼できるものです。

行カウントの監査は、行カウント変換を使用してデータ フロー内に実装されます。この処理では、単純に処理された行数をカウントし、その値を事前定義されたユーザー変数に格納します。行カウント変換の優れている点の 1 つとして、必要なオーバーヘッドとリソースが最小限ですむことが挙げられます。したがって、すべてのディメンションおよびファクト パッケージの中核のデータ フロー内で、すべてのソースの後と、すべての宛先または OLE DB コマンドの前のほか、データ フロー内のあらゆる重要なポイントに行カウント変換が挿入されています。各カウントは専用の変数に渡され、データ フロー変換の直後に SQL 実行タスクによりデータベースに格納されます。図 10 は、これらの行カウント変換がデータ フローに実装されている状態を示しています。

ETL レポート作成

すべてを関連付け、管理者や開発者向けのトラブルシューティング ツール用に適切に整理された情報を提供するため、監査、評価、およびログを関連付ける一連の Reporting Services リンク レポートがデザインされています。前述の監査についての説明を読まれていれば、メタデータの関連付けを可能にするスキーマについて理解されたと思います。このスキーマでは 4 つのプライマリ テーブルが使われています。3 つはユーザー定義テーブルで、組み込みの SSIS ログ テーブルと統合されます。

プロジェクト REAL の ETL レポートはこれらの構造を基盤にし、高レベルのグループ読み込み実行の概要情報を提供します。また、このレポートは、Reporting Services のテーブルのグループ化機能とリンク サブレポート機能を使用した、詳細情報へのドリルダウン機能を備えています。レポートには、以下のレベルのレポートがあります。

• グループ読み込み実行の概要 – 開始時刻と終了時刻、実行時間の概要、および実行状態。

• パッケージ実行の概要 – グループ読み込みのアソシエーション、開始時刻と終了時刻、実行時間の概要、および実行状態。

• 行カウントの詳細 – ステップの説明と種類、行カウント。

• パッケージ制御フロータスクの概要 – タスクの実行時間と実行状態など、基盤のログ テーブルから集約したタスクの概要。

• 詳細なイベント ログ系列 – 指定したパッケージまたはタスクに応じてフィルタ処理され、並べ替えられた詳細ログ エントリ。

ETL レポートによりすばやく状況を把握できるため、SSIS ソリューションの管理およびトラブルシューティングを行ううえで役に立ちます。

動的構成デザイン

SSIS パッケージの管理の容易性、配布、および展開の鍵は、構成にあります。SSIS には、パッケージのプロパティを実行時に構成する方法がいくつかあるため、実行時の動的な構成が必要な接続情報、変数、他のタスクや変換のプロパティを更新できます。組み込みの構成の方法には以下の方法があり、さまざまな環境要件に対応でき、異なるソリューションで利用できます。

• 構成ファイル

• 環境変数

• SQL 構成テーブル

• 親パッケージ変数

この機能が本当に役に立つのは、ETL パッケージを開発環境から運用に向けてのステージング環境に移行する場合です。ほとんどの場合、これは単に構成システムのエントリをいくつか変更するだけで達成できます。

SQL の構成

プロジェクト REAL の目標は、構成を一元管理し、ETL デザインをスケールアップ モデルと分散モデルという 2 つの特有な環境に展開できるようにすることでした。同様に、いくつかの異なる REAL ソリューション (完全な規模のソリューション、サンプル規模のソリューション、デモ用ソリューション) も、参考やデモ目的で存在しています。したがって、構成は組み込みの SQL 構成を使用して、ソリューションの各種データベースに一元化しました。これにより、構成プロパティおよびマッピングをリレーショナル テーブルに格納し、パッケージ間で共有できるようにしています。一元管理ツールを開くには、[SSIS] をクリックし、[構成] をクリックします。図 13 は、SQL の構成オプションを示しています。

構成テーブルには、各プロパティにいくつかのエントリがあります。最初の一連のエントリは接続用です。接続の構成エントリがどのように適用され、ソリューション データ ソースがパッケージにどのように使われるかを説明しておく必要があるでしょう。データ ソース オブジェクトを基に接続が作成される場合、この接続はデータ ソースの実行時コピーとなり、パッケージが実行されるときに親のデータ ソースからは更新されません。データ ソースはデザイン時の構造なので、UI からパッケージが開かれると、SSIS ソリューション内のデータ ソースから構築されていれば、接続は更新されます。このため、接続は構成の対象となる可能性が非常に高くなります。接続は、通常、環境 (開発、テスト、実稼動) に応じて、動的に構成する必要があります。プロジェクト REAL の構成テーブルのその他のエントリとして変数マッピングがあります。この変数マッピングにより、ロジックの処理と管理に ETL で使われる変数値の更新が可能になります。

XML ファイル構成

図 13 では、適切な SQL 構成テーブルの場所は、パッケージ接続を基準にしています。ただし、すべての接続情報が構成テーブルに配置されている場合、これは結果としてハードコードされたパッケージ接続値を使用する循環参照となりますが、これは望ましくありません。これを回避するため、XML ファイル構成という別の種類の構成が使われます。この場合も構成をデータベース テーブルに一元化することが目標にあるので、XML ファイルで必要になる構成エントリは、SQL 構成テーブルを含むデータベースを示す接続文字列だけです。図からおわかりのように、実際に XML ファイル構成のプロパティとしては、XML ファイルの場所とファイル名を指定するプロパティしかありません。

XML ファイル構成の優れた機能の 1 つとして、構成ファイルの場所を定義するサーバー環境変数を使用できる点があります。すべてのパッケージがこのファイルを参照するので、環境変数を使用することでファイルの変更を 1 か所で行うことができます。これは展開を行う場合にも有用で、同じパッケージを実行する他のサーバーには、別のファイルの場所と名前を使用できます。このような環境変数の使用は、SSIS 環境変数構成を使用する場合とは異なります。SSIS 環境変数構成の場合、サーバーはパッケージのプロパティを上書きする複数の環境変数を使用できます。

親変数構成

これまで説明したすべてのプロジェクト REAL 構成は、グローバルに使われるプロパティを対象としています。つまり、目的の環境内で任意のパッケージやパッケージ グループを実行するたびに使用される接続と変数に使用される構成です。ただし、構成の中にはパッケージや、パッケージが参加するワークフロー グループの特定の実行に適用範囲を限る必要があるものもあります。たとえば、ワークフロー グループのバッチ ID である ETL_Load_ID は、グループ読み込みパッケージの最初のステップで作成され、すべての子パッケージで使用されます。各パッケージは、それぞれ異なるバッチ コンテキストで実行されるので、この変数の構成は実行するパッケージに応じて動的に構成される必要があります。

SSIS の親変数構成機能は、子パッケージによる親パッケージの変数の継承を可能にします。これは、SSIS の前身である DTS と異なる点で、DTS では変数を親パッケージから子パッケージにプッシュ ダウンしていました。SSIS では、子パッケージが名前を指定して親パッケージの変数を要求することで、パッケージ実行タスクを使用して子パッケージを呼び出す、呼び出し元の親パッケージから変数を取得することができます。

プロジェクト REAL の要件として、パッケージや一連のパッケージの実行インスタンスにローカルな構成が、親変数構成機能に適している必要があります。前述のとおり、ETL_Load_ID はすべてのディメンションとファクト パッケージ、およびパッケージ間のデータの関連付けを実現する親パッケージの実行 ID によって継承されます。図 15 は、ID vnETL_Load_ID 識別子の変数構成を示しています。

データ処理アーキテクチャ

ここまでは、核となる ETL 処理ロジックに触れずに、SSIS デザインの基盤となる構造について説明してきました。この概説により、このホワイト ペーパーの前半で説明した概念を踏まえて、処理ロジックをより詳細に検討していく準備が整いました。ただし、プロジェクト REAL の具体的なデータ処理実装を説明する前に、一歩引いて、重要な ETL の原則に関連する SSIS の一部の新機能について説明する必要があります。

制御フローとデータ フロー

核となるビジネス ロジックの実装に使われる SSIS の主な機能は、制御フロー コンポーネントとデータ フロー コンポーネントに含まれています。これらのコンポーネントについては、プロジェクト REAL の環境や監査構造を取り上げた際に、既に何度か触れています。

簡単に説明すると、制御フローは、パッケージのビジネス プロセス フロー ロジックを調整するタスクのワークフロー エンジンです。1 つのステップしか含まない場合でも、数十の関連タスクを含んでいる場合でも、各パッケージにはプライマリの制御フローが 1 つだけあります (イベント ハンドラも、制御フローの一種です)。制御フロー内のタスクは、制約 (成功、失敗、完了、カスタム制約式やブール論理演算) により関連付けられます。

"データ フロー" は、データの移動、変換ロジック、データ編成、ソースや宛先からのデータの抽出とコミットを処理するデータ処理エンジンです。制御フローと異なり、パッケージには複数のデータ フローを定義できます。このような複数のデータ フローは制御フローで調整されます。データ フローには、制御フローのワークフロー コネクタと非常によく似た緑と赤のコネクタがありますが、機能は完全に違っています。データ フローのコネクタはデータのパイプラインのようなものです。データは、 "バッファ" と呼ばれるデータの小さなバッチで、ある変換から別の変換に移動します。これは、データ フローの動作を視覚化する最も簡単な方法ですが、実際には、定義済みの変換により、データ移動のほとんどの処理が行われます。このような定義済みの変換では、最高のパフォーマンスが得られるようにデータ バッファを使用します。

SSIS アーキテクチャのメリット

SSIS は相互運用性、構成、再起動処理、ログ機能の面で DTS よりも優れているだけでなく、スケール メリットがあり、より安定性が高く柔軟なパフォーマンス ベースのデザインが可能な ETL アーキテクチャを実現できる変換エンジンを備えています。プロジェクト REAL では、核となる ETL 開発にこのようなメリットを考え、従来の DTS ベースのアーキテクチャの枠を超えたデザインが行われています。

ステージングの削減

まず、SSIS では、RDBMS エンジンやストレージにほとんど依存せず、複雑なデータ変換、最適化、参照をデータ フロー内で直接実行できるようにすることで、ステージング環境を削減できるようにしています。ソースおよびウェアハウス テーブル間のデータ比較は、条件分割を使って適切な読み込みロジックに結果を返すことができる、参照変換やマージ変換によって処理できます。このため、データベース エンジンは、参照や結合、行の比較を実行するデータベースではなく、SSIS データ フローにデータを出力するだけで済みます。

パイプラインのメリット

データ フロー コンポーネントのほとんどは、(並べ替え変換や集計変換などのよく知られている一部の例外はありますが) 完全なパイプライン並列処理を実現できます。つまり、ウェアハウス オブジェクトの処理は、複数の小規模なデータ バッファを使って同時に実行されるので、上流処理全体が完了するのを待たなくても、次の処理に移ることができます。これにより、データ抽出からソース システムへの影響が緩和され、プロジェクト REAL 開発中、通常、SSIS パッケージが最適化されていれば、ソースから未加工のデータを抽出してこれを直ちにデータベースに格納する場合にかかる時間と、データを抽出してこれをデータ フロー コンポーネント内にデザインされた、一連のインメモリ変換に渡す場合にかかる時間はほぼ同じになります。

データの最適化と変換

SSIS に搭載されているデータ フロー変換には、あいまい参照や結合、文字マップ、データ型変換、派生列などの一連のデータ最適化ツールと、データ比較と置換用のブール値ベースの関数群が含まれています。

多対多のソースと宛先

1 つのデータ フローで複数の異種ソースと宛先を処理できるので、1 つのソースから複数の宛先にデータを分配することができます。これは逆の場合も可能で、複数のソース オブジェクトを 1 つの宛先にまとめることもできます。多くの場合 BI システムでは、ディメンションは同じまたは異なる複数のシステム内の複数のテーブルからデータを取得します。同様に、ファクトも 1 つまたは複数のテーブルから取得したり、トランザクション ソースのデータが複数のファクト テーブルに分配される場合があります。

ディメンションとファクトの詳細度と種類の変更

通常、ウェアハウス オブジェクトは、ソースの OLTP オブジェクトと同じ詳細度で読み込まれます。ただし、より高い詳細度でディメンションを収集したり、親子の自己結合ソースが標準の階層に合わせて正規化が解除されることがあります。たとえば、第 4 正規形のソース行が、関連する属性を統合したレコードセットに統合される場合、ソース レコードをピボットする必要がある場合があります。レポート要件に合わせて集計またはグループ化される場合、ファクト テーブルにも同様の変換が行われることがあります。このようなやや特殊なシナリオは、多くの場合、その他の組み込みの変換 (集計、ピボット、逆ピボット、並べ替え、マージ結合など) を使用して、データ フロー内部で処理できます。

ディメンションの処理

ディメンションの履歴の処理は、ETL ソリューションの複雑な課題の 1 つです。プロジェクト REAL では、ディメンション読み込みシナリオには、履歴属性と変更属性の処理だけでなく、ディメンション変更の種類や非同期ファクトのディメンション アソシエーションも含まれています。SCD (緩やかに変化するディメンション) ウィザードの組み込み機能のほか、このプロジェクトに含まれるその他の要件についても 2、3 取り上げます。

• 推論ディメンション メンバ : 読み込み対象のディメンション レコードが完全に揃っていないため、対応するディメンション メンバがかけた状態でファクトを受け取ります。"孤立ファクト" と呼ばれることもあります。

• 変化する SCD 型 : 1 つのディメンション内のメンバの履歴変更要件が一様でなく、異なるタイミングで個別に変更される可能性があります。

SSIS は、プロジェクト REAL の標準的な事例と特有の事例に対応できる機能が備わっています。これについては、後述します。

緩やかに変化するディメンション ウィザード

どの ETL デザイナも望むものに、緩やかに変化するディメンションを魔法のように処理できるツールがあります。SSIS はこの望みの実現に後一歩のところにいます。SSIS には、ソースと宛先のスキーマに基づき、一連の手順を案内して変更特性を特定できるウィザードが用意されています。その後、ウィザードはディメンションの処理に必要な変換を構築します。要件が変更された場合でも、再度ウィザードをステートフルに実行できるので、新しいプロセスを処理するために元の指定を変更できます。

プロジェクト REAL では、緩やかに変化するディメンション (SCD) ウィザードは、非常に便利でした。1 つを除き、すべてのスター スキーマのディメンション テーブルで、SCD 変換が使用されています。このウィザードにより、劇的にディメンション処理の開発時間を短縮できました。ここでは、このウィザードが最も包括的に使用する Store ディメンションを例にして、SCD ウィザードの機能を示します。Store ディメンションの要件は、以下のとおりです。

• 新しいディメンション メンバ – ソースに追加される新しいディメンション メンバの追加

• 変化するディメンション属性 – ソース列の値が変化するたびに履歴が上書きされる、従来の種類 1 列の変更。

• 履歴ディメンション属性 – 次の変更が実行されるまで、すべての新しいファクト レコードに関連付けられる新しいディメンション レコードの追加により、履歴が保持される従来の種類 2 列。

• 推定メンバ – ファクト プロセスの実行前に、あるディメンション メンバがディメンション テーブルに読み込まれていないので、プレースホルダ レコードが追加されている状態。これは、いずれソース ディメンションが完全に揃った時点で (種類 1 および種類 2 の両方の列が) 更新されます。

Store ディメンション用にウィザードを実行すると、最初の画面で列の一覧が表示され、その中でビジネス キーに使用できる列が示されます (図 16 参照)。

ウィザードの次の画面では、変更型とする列を指定します。変更属性、履歴属性、および固定属性のいずれかを指定します。固定属性を指定した場合は、その列は変更されません。図 17 はこれらの属性を示したものです。

履歴または種類 2 列を含むディメンションには、各変更の現在状態と履歴状態を管理するメタデータが必要です。次の画面 (図 18) では、Store ディメンションがどのように履歴を追跡するかをプロセスが把握できるようにします。この場合は、Current_Row 列を使用して、どのディメンション レコードが変更されたディメンション行の現在のレコードであるかを追跡します。

次に、推定メンバが使用されている場合は 図 19 のような画面が表示されます。ここでは、ディメンション レコードが推定メンバであることを示す方法を指定し、ビジネス キー以外のすべての列が処理中に更新されるようにします。この場合のオプションは 2 つあります。最初のオプションでは、すべての非キー列が NULL 値の場合に、推定メンバであるとします。2 番目のオプションでは、メンバが推定メンバであるかどうかを示すフラグ列により特定します。NULL 列は Analysis Services では適切に表示されないことを考慮し、このプロジェクトでは Inferred_Member という列を使用しました。これにより、Analysis Services 階層内で使われている属性を、指定された Unknown 値で置き換えることができました。

ウィザードの最後の画面を完了すると、ウィザード処理中に入力された詳細情報を基にカスタマイズした一連の変換が生成されます。主要な変換は、緩やかに変化するディメンション変換と呼ばれます。これは、ソース レコードがディメンション ソースの完全なコピーであっても、ソース レコードのサブセットに過ぎなくても、ソースに追加またはソース上で変更されたディメンションのソース レコードを入力として受け取ります。SCD タスクは、キャッシュされない参照変換と条件分割変換を組み合わせたものと考えてください。ソース ディメンション レコードは、ウェアハウス ディメンションを基準に評価され、その後複数の SDC 出力として配布されます。図 20 は、Store SCD 変換とこれに関連付けられた出力の最終的な UI イメージです。

特有のディメンション シナリオ

プロジェクト REAL のディメンション処理パッケージのうち、唯一 SCD 変換を使用していないディメンションは、Item ディメンションです。このディメンションの要件は特有であり、約 600 万メンバというサイズのため、拡張性に特別な処理が必要です。

Item ディメンションと他のディメンションを区別している特徴の 1 つは、行われる SCD 変更の種類です。推定メンバ、変更属性、および履歴属性が必要なだけでなく、あるメンバについては、その属性変更の種類を変更属性から履歴属性 (種類 1 から種類 2) に変更できる必要があります。このような状況は、ある商品の最初の売上が発生したときに、発生します。最初の売上が発生するまでは、すべての属性が変更 (種類 1) 属性として機能していますが、一度売上が発生すると、属性のサブセットが履歴 (種類 2) 属性に変化します。 このシナリオのために、新たに種類 1.5 の変更が作成されました。このシナリオは、ディメンションへの種類 2 の追加数を制限したいというビジネス ニーズにより生じたものです。これは、ある商品がトランザクション システムに初めて入力された場合、その特性を確立するプロセスにより、最初の数日間で複数の変更が発生するためです。これらの初期属性の詳細が確立するまで、ディメンション メンバは、どのような属性に対する変更であっても、新しい種類 2 履歴レコードではなく、ディメンション内のその属性が更新されるような状態に置かれます。この方法で値を提供することで、ディメンション テーブルの増大を、商品が売れて値が確定した時点の重要な履歴変更分のみにとどめるようにしています。原因となったビジネス要件は異なりますが、このシナリオは推定メンバの機能と同様であると言えます。ただしこの場合は、ソース ディメンション レコードが取得できていて、すべての属性を更新するためには、販売要件が満たされる必要がある点が異なります。

SCD ウィザードを使用するかどうかは、ディメンションで処理されるレコードの量により決まりました。600 万メンバを有する Item ディメンションでは、その 100 個の属性全体にわたり、1 日数万件もの変更が行われる可能性があります。組み込みの SCD コンポーネントの参照プロセスは、同じ数のデータベース呼び出しを生成し、大規模テーブルをクエリして、数十にものぼる列を結果行に返していました。必要な時間枠内でこのプロセスを完了することは難しかったため、別の方法が採用されました。

方法の 1 つは、参照変換を使用してディメンション全体をキャッシュに読み込み、すべての列で変更の種類を比較できるようにすることでした。ただし、大規模テーブルの列をすべてキャッシュするとなると数 GB のメモリが必要になり、メモリへの読み込みに膨大な時間がかかることが予想されました。このため、代わりに左マージ結合変換を使用しました。この変換では、ビジネス キーにより、左側のソース レコードが、右側の現在のディメンション メンバと対応付けられます (図 21 参照)。この結合により、実際に関連のあるファクトで使用されている Item レコードのみが取得されるようになります。変更の種類の分析に必要なディメンションの列は、レコードの対応付けのため、データ フローに含まれています。左結合を使用したのは、ソース (左辺) の新しいレコードを引き続きパイプ経由で取得し、新しいメンバとしてディメンションに追加されるようにするためです。

このシナリオの結合は、入力列が既に並べ替えられているため、非常にうまく機能します。対応付けができると、処理のためレコードが下流変換に送られます。

条件分割変換 (マージ結合変換のすぐ下にあります) は、ある条件を評価した後、各行をそれぞれ異なる変換出力に配布します。条件は順番に評価されます。ある行の出力先はその行が一致する最初の条件で指定され、1 つの行が複数の出力先に送られないようにしています。

図 22 の条件分割変換は、まず ISNULL 関数を使用して、結合の右辺に対応するレコードがあるかどうかを評価しています。この NULL チェックに適合したソース行は、その行を新しいディメンション メンバとして追加する変換へ出力されます。残りのメンバはすべてウェアハウスのディメンション テーブルに対応しているため、変更の種類の条件が評価されます。対応するレコードの場合、最初に評価される条件は、Inferred Member および Sales Flag 条件です。この 2 つはいずれも、ディメンション属性に完全な更新が実行されている必要があるため、2 つ合わせて同時に処理しています。次に履歴変更属性が評価されます。履歴追跡属性のうち 1 つでも変更された場合、種類 2 の変更レコードが生成されます。最後に、残りの変更の種類列に変更があった場合、ディメンション更新ステートメントが生成され、その属性の以前の値が上書きされます (種類 1 と同様)。

最後の条件は指定されていませんが、このシナリオは条件分割変換の既定の出力によって定義されています。ソース レコードは新規または変更された行だけなので、その他のすべての要件が満たされた場合、最後の条件を残りのレコードに適用できることは明らかです。このことから、この方法で Item ディメンションを適切に処理するためには、条件の順番が非常に重要であることがわかります。

条件分割変換の変換フローは、Store ディメンションの例における SCD 変換の出力とよく似ています。これは、SCD の新規追加、変更、推定メンバ処理をモデルとして、出力がデザインされているためです (種類 1.5 変更により、"完全な更新" と呼ばれます)。

ファクト テーブルの処理

ファクト テーブルの処理は、その大部分がディメンションの処理とは大きく異なります。また、ファクト テーブルのあるプロセスは、その次のプロセスと非常に異なっている可能性があります。ただし、ほとんどのファクト テーブルのプロセスには、ファクト行の比較とディメンション キーの参照が含まれています。説明のため、このセクションでは 2、3 の異なるプロジェクト REAL ファクト テーブル パッケージを取り上げて説明します。これらのパッケージは、一般的なシナリオをモデルにしています。

増分抽出と完全なソース抽出

プロジェクト REAL の ETL には、ファクト テーブルの抽出方法として 2 種類があります。1 つは "完全なソース抽出" で、変更されたレコードや新規レコードを特定できません。もう 1 つは "増分抽出" で、この場合は新規レコードや変更されたレコードだけが抽出されます。

完全なソース抽出

地区センター (DC) の在庫状況は、約 800 万件の DC と商品の組み合わせについて、週単位に追跡されます。ソース システム上では、これらのレコードは 1 つのテーブル内に格納され、新規レコードか変更レコードかは特定されていません。したがって、ETL プロセスにより在庫ソースとファクト テーブルのレコードを比較し、変更されているかどうかを特定する必要があります。これにより、適切に挿入または更新を処理することができます。

このプロジェクトでは、ソース テーブルと宛先テーブルの完全なデータセットを用いて、完全なマージ結合を使用する方法を採用しました。完全な結合を行うことで、在庫レコードがソースに追加されたり、完全に削除された場合、これを特定することができます。このソリューションでは、削除されたソース レコードは、ファクト テーブルではゼロの在庫状況として追跡する必要がありました。この要件は、マージ結合変換を完全結合として構成することで対応することができました。このマージを図 24 に示します。

次の下流の変換は条件分割変換で、結合の結果を評価してレコードの変更を特定し、列値の比較を行います。

条件分割変換で適用される条件は実行順に処理され、結合により対応付けができていて、属性とメジャーが変更されていない場合がまず評価されます。レコードのほとんどがこの条件に当てはまりますが、出力ストリームが使用されていないため、この出力はまずデザイナに一覧表示されます。変更されていないレコードは処理が必要ないので、この処理により変更されていないレコードを除外できます。2 番目の条件で、新しい在庫レコードを特定します。新規レコードの場合、そのソース行には対応する既存のファクト テーブル レコードがありません。対照的に、次は、ソース行が削除されたために、ファクトの在庫値をゼロに設定する必要があるような完全結合を評価します。最後は、他のすべての行が既定の出力に送られます。これらの行は、在庫状況が変更されているため、データベース内での更新が必要な行です。

増分ソース抽出

抽出プロセスによりソース システム上の更新と挿入を分離できる場合は、これにより、関連する ETL プロセスのパフォーマンスを大幅に向上できます。幸いに、プロジェクト REAL の大規模データ ソースの多くは、このような対象を特定した抽出を利用できます。この 1 例として、Store Inventory 抽出があります。毎日の変更の処理に店頭在庫ソース全体 (2 億レコード弱) が必要であった場合、処理を 1 日で終えることはできなかったでしょう。しかし、毎日の在庫は増分抽出することができるため、処理時間は十分に許容できる時間に短縮できます。

Store Inventory の場合は、増分抽出プロセスにキャッシュ参照を使用して、増分レコードが挿入レコードであるか更新レコードであるかを特定できるようにしています。このプロセスを支援するプロセスの 1 つに、中間ステージ プロセスがあります。これは、参照内のレコードをフィルタ選択し、処理を最適化して、実装全体を支援するために使われています。詳細については、後述する「データ処理の最適化手法」を参照してください。

図 26 は、Store Inventory ファクト処理のデータ フローを示しています。変更されたソース レコードと現在のファクト レコードを比較する参照部分は、強調表示されています。

ディメンション参照

各ファクト テーブル プロセスには、ファクトとディメンション テーブルを関連付ける方法が必要です。これは、ソリューション全体で、ディメンションをキャッシュできる参照変換を使用して処理されます。ソース行が取得されると、すぐにビジネス キー アソシエーションを基にファクト テーブルに必要な代理キーを返します。このプロセスは、単純でありながら、行カウントの少ないディメンションには特に有効です。ディメンションに履歴 (種類 2) 変更、したがって現在行 ID が含まれていて場合は、現在行のみが使用されるようにキャッシュがフィルタされ、最新のディメンション メンバがファクトに関連付けられるようになっています。図 27 は、参照変換エディタの [参照テーブル] を示しています。Store ディメンションを対象としているこの例では、クエリを使用して Current_Row をフィルタしています。

参照変換エディタの [列] タブでは、データ フロー列を参照テーブル列にマップしています。ディメンション参照の目標は、ディメンションの代理キーを取得することにあるので、ビジネス キー (Store_Num) をマップとして使用し、データ フローの下流に使用するセカンダリ列と共に代理キー (SK_Store_ID) を返しています。

データ フロー側から見ると、ソース行は単純に参照から参照にマップされ、行は最新のディメンション代理キーに関連付けられます。図 29 のデータ フローは、上記の参照変換を表したものです。スクリプト変換や推定メンバに使用する全体結合変換など、いくつか他の変換も表示されています。推定メンバについては、次で説明します。

Item ディメンションの場合、数百万行が含まれているので、最適化手法を利用して、ファクト テーブル プロセスの実行に必要な行のみを参照に取得するようにしています。詳細については、後述する「データ処理の最適化手法」を参照してください。

推定メンバの追加を処理する

プロジェクト REAL のディメンションでは、ディメンション レコードが存在しない場合に推定メンバを作成する必要があるため、さらに作業が必要です。繰り返しますが、"推定メンバ" とは、プレースホルダの役割を果たす、ビジネス キー値のみを持つディメンション レコードです。完全なディメンション レコードが揃った時点で、すべてのディメンション列は取得できた新しい値で更新されます。更新プロセスはディメンションの処理中に実行されますが、推定メンバの追加は、参照変換により対応レコードが生成されなかった場合に、ファクト プロセスの処理中に行われます。キーを取得するためキャッシュ ディメンション参照を処理する参照変換が、対応レコードを見つけられなかった場合、行は実際に失敗します。出力を構成して、行が失敗したパイプからリダイレクトされるようにするには、[参照変換エディタ] ダイアログ ボックスの [エラー出力の構成] ボタンをクリックし、表示された [エラー出力の構成] ダイアログ ボックスで行がリダイレクトされるように構成します。図 30 を参照してください。

プライマリの参照変換をこのように構成すると、対応がない行がリダイレクトされ、代わりに推定メンバをディメンションに追加できます。プロジェクト REAL の代理ディメンション キーはすべて ID 列です。このため、キーが SSIS によって生成できる一意の ID であった場合に比べて、このプロセスを難しいものにしています。推定メンバの追加を処理するために、いくつかの方法が検討されました。データ量を考慮すると、プライマリのデータ フロー内で推定メンバの追加を行うようにすることで、プロセスが最適に実行されるようにする方法が必要でした。プロセスの合理性を保ち、複数のデータ フローの発生を避けるため、新しく生成された推定メンバ キーは、次のディメンション参照に移る前に、データ フローに戻す必要がありました。また、プライマリの参照変換キャッシュはデータ フローの実行前に読み込まれるため、推定メンバがデータベースに始めて追加される場合、その他のディメンション レコードがあるキャッシュには追加されないことも、考慮する必要がありました。したがって、同じ対応のないビジネス キーが渡されたファクト レコードが数千レコードあった場合、これらのレコードはすべてキャッシュ内にはなく、したがって推定メンバの追加プロセスを実行するためにエラー出力に送られます。

上記の要件を考慮した結果、推定メンバの追加の処理には、スクリプト変換を使用することにしました。スクリプト変換は、スクリプト タスクとは異なるもので、データ フロー内で使われています。スクリプト変換は、パイプライン経由で送られてくる行や列に対してスクリプト ベースの操作を実行できます。特有のシナリオにより特別な処理が必要とされる場合、スクリプト変換を使うことで、柔軟に状況に合わせた方法で値を提供できます。ここでの具体的な目標は、データベースに同じ対応のないビジネス キーをクエリする呼び出しを複数回行うことなく、対応のない参照出力を取得し、推定メンバをデータベースのディメンション テーブルに追加して、新たに生成された代理キーを返すことです。Visual Basic® .NET スクリプトを作成する前に、スクリプト変換プロセスを概観できるよう、以下にコードの概要をまとめました。

1. 最初のレコードの処理前に実行される初期手順は、次のとおりです。

   1. 変数を宣言します。

   2. 最適化のためビジネス キーと出力代理キーの参照に使われるハッシュ テーブルを作成します。

2. スクリプト変換パイプラインから渡される行ごとに、ハッシュ テーブルを調べ、現在のビジネス キーの有無を確認します。その後、次の操作を実行します。

   1. ビジネス キーが存在する場合は、パイプラインの出力に代理キーを返します。

   2. 代理キーが存在していない場合は、次の操作を実行します。

      1. データベースに接続します。

      2. 推定メンバを作成し、代理キーを返すストアド プロシージャを実行します。

      3. ビジネス キーと新しい代理キーをハッシュ テーブルに追加します。

      4. 代理キーをパイプラインの出力に返します。

3. パイプラインの入力の最終行が読み込まれたら、クリーンアップと割り当て解除手順を実行します。

次のスクリプト変換は、Store_Num に対応するレコードが参照内にない場合に使われる、Store Lookup 用の推定メンバ プロセスの例です。

' Microsoft Data Transformation Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for DTS Script Components

Imports System
Imports System.Data
Imports System.Collections
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
___________________________________

Public Class ScriptMain
  Inherits UserComponent
  Private htBusinessID As New Hashtable
  Private objConnection As New SqlClient.SqlConnection
  Private objCommand As New SqlClient.SqlCommand
  Private boolInit As Boolean = False
  Private strProcedureName As String = "config.up_ETL_DimStore_CreateInferredMember"
  Private strBusinessID As String = "@pnStore_Num"
  Private strSurrogateID As String = "@pnSK_Store_ID"
  Private strETLLoadID As String = "@pnETL_Load_ID"
  Private strReturnValue As String = "@RETURN_VALUE"
___________________________________

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    If (boolInit = False) Then Me.Connect()
    If Not (htBusinessID.Contains(Row.InBusinessID)) Then
      Dim strSurrogateKey As String = Me.Execute(Row.INBusinessID, Row.ETLLoadID)
      htBusinessID.Add(Row.InBusinessID, strSurrogateKey)
    End If
    Me.ProcessRow(Row)
  End Sub
  Public Sub ProcessRow(ByVal Row As Input0Buffer)
    Row.OutSurrogateID = 
     System.Convert.ToInt16(htBusinessID.Item(Row.INBusinessID).ToString())
  End Sub
  Private Function Execute(ByVal BusinessId As Decimal, ByVal ETLLoadID As Integer, ByVal) 
  As String
    Me.objCommand.Parameters(strBusinessID).Value = System.Convert.ToString(BusinessId)
    Me.objCommand.Parameters(strETLLoadID).Value = System.Convert.ToString(ETLLoadID)
    Me.objCommand.ExecuteNonQuery()

    Execute = 
     System.Convert.ToDecimal(Me.objCommand.Parameters(strSurrogateID).Value).ToString()
  End Function
___________________________________

  Private Sub Connect()
    If boolInit = False Then
      Dim strConnection As String = Connections.SQLRealWarehouse.ConnectionString
      Dim x As Integer = strConnection.ToUpper().IndexOf("PROVIDER")
      If x >= 0 Then
        Dim y As Integer = strConnection.IndexOf(";", x + 1)
        If (y >= 1) Then strConnection = strConnection.Remove(x, y - x + 1)
      End If

      Me.objConnection.ConnectionString = strConnection
      Me.objConnection.Open()
      Me.objCommand.Connection = Me.objConnection
      Me.objCommand.CommandType = CommandType.StoredProcedure
      Me.objCommand.CommandText = Me.strProcedureName

      Dim Parm As New SqlClient.SqlParameter(strBusinessID, SqlDbType.Decimal)
      Parm.Direction = ParameterDirection.Input
      objCommand.Parameters.Add(Parm)

      Parm = New SqlClient.SqlParameter(strETLLoadID, SqlDbType.Int)
      Parm.Direction = ParameterDirection.Input
      objCommand.Parameters.Add(Parm)

      Parm = New SqlClient.SqlParameter(strSurrogateID, SqlDbType.Int)
      Parm.Direction = ParameterDirection.InputOutput
      Parm.Value = 0
      objCommand.Parameters.Add(Parm)

      Parm = New SqlClient.SqlParameter(strReturnValue, SqlDbType.Int)
      Parm.Direction = ParameterDirection.ReturnValue
      objCommand.Parameters.Add(Parm)

      Me.boolInit = True
    End If
  End Sub
___________________________________

  Private Sub Close()
    If boolInit = True Then Exit Sub
    Me.objCommand.Dispose()
    Me.objConnection.Close()
    Me.objConnection.Dispose()
    Me.htBusinessID = Nothing
    MyBase.Finalize()
  End Sub
End Class

スクリプト変換により実行されるプロシージャでは、ディメンションを調べてビジネス キーの有無を確認し、レコードが存在しない場合は新しいレコード (推定メンバ) を挿入します。その後、新しく追加した ID 列をスクリプトに返して、データフローの下流で使用できるようにしています。

推定メンバ スクリプト変換が完了すると、レコードは全体結合変換を使用してプライマリのパイプラインに戻されます。こうして、次のディメンション参照で利用できるようになります。図 31 は、一連の参照とそれらに関連付けられている推定メンバ参照、および結果を戻すために必要なマージ結合を示しています。

データ処理の最適化手法

プロジェクト REAL の開発プロセス全体で、ETL の合理化に役立ついくつかの最適化手法が使われています。これには、SSIS のアーキテクチャ上のメリットに基づく原則、SSIS 内の設定、大容量を処理するためのデータ フローの調整などがあります。最適化には、次のような処理が含まれます。

• 重要なデータのみを取得するステージングを使用した、参照とデータ マージ ソースのフィルタ。

• 集計や並べ替えなど、全行セットを操作する (他のプロセスをブロックする) データ フロー変換の使用の制限。

• 例外に先立つ、一般的なデータ処理シナリオの処理。

• 大規模ディメンションまたはファクト テーブルの更新に、パッチ更新の使用を検討。

重要なデータのみを取得するステージング

完全にキャッシュされた参照変換は、ディメンション キーをファクト テーブル レコードに関連付けるなど、ソース間のデータを関連付けます。ただし、大規模ディメンションの場合、ディメンション全体を参照メモリ キャッシュに読み込むには長時間を要し、他のプロセスに使用できる RAM を消費します。 プロジェクト REAL では、大規模 Item ディメンションのビジネス キー用に、対象を制限したステージング テーブルを作成しました。このテーブルには、600 ～ 700 万メンバが保持されます。この対象が限定されたステージング テーブルは、1 つのデータ ソースと宛先を含み、トランザクション ソースから製品ビジネス キーのみを抽出して、これを任意のステージング テーブルに格納するデータ フローを使用して、ファクト テーブル パッケージに追加されます。図 32 は、440 万のビジネス キーのステージングを 55 秒で無事に完了した処理を示しています。抽出は対象範囲が絞り込まれ、限定されているため、このデータ フローは数百万行を処理指定してもわずか数十秒で完了できています。

その後、ステージングされたキーは、参照がキャッシュへの読み込みに使用するクエリのフィルタに使われます。ディメンション内のビジネス キーは既にインデックス化されているので、ディメンション レコードを制限するための結合は非常にうまく機能します。この例では、この手法によりディメンション参照キャッシュをフィルタして、サイズが完全なディメンションの約 1/10 になりましたが、同じビジネス キーのセットが参照に使用されているので、ファクト処理中に参照で必要となるディメンション メンバはすべて保持しています。次の SQL コードを使用して参照キャッシュにデータを取得しています。このコードには、ステージング テーブル (tbl_DWS_ETL_Store_Inventory_Log) 内に格納されているビジネス キーを使用したディメンション (Tbl_Dim_Item) のフィルタも含まれています。

SELECT distinct
 ITEM.SK_Item_ID
, ITEM.SysID
, ITEM.SK_Parent_Item_ID
, ITEM.Retail_Amt
FROM dbo.Tbl_Dim_Item as ITEM
INNER JOIN config.tbl_DWS_ETL_Store_Inventory_Log_Keys as INV_LOG
ON ITEM.SysID = INV_LOG.SysID
AND ITEM.Current_Row = 1

この方法は、マージ結合変換に使用するソース レコードを制限する場合にも使用できます。マージ結合は参照と同様のいくつかのシナリオで、ソースと宛先データの関連付けに使用されています。ソースとウェアハウス間で数十の列の比較が必要な場合、参照変換では、各行の各列をメモリに格納する必要があるため、参照キャッシュのサイズを処理できない可能性があります。これに代わる方法として、マージ結合変換を使用して、ソースとウェアハウスのデータを結合する方法があります。マージ結合にはメモリのオーバーヘッドがありませんが、前述のようにビジネス キーがステージングされている場合、フィルタされたウェアハウス ソースを利用することもできます。

並べ替えと集計データ フロー変換の制限

並べ替えと集計変換を制限するとパフォーマンス上のメリットがありますが (これらの変換はパイプライン内の行すべてをロックし、時間とリソースを消費するため)、これらの変換が必要になる場合があります。たとえば、プロジェクト REAL のいくつかのパッケージで使用されているマージ結合では、結合を定義する列を基準にソースを並べ替える必要があります。両ソースに対して並べ替え変換を使用すると、マージ結合に制御が渡される前に、マージ対象の 2 つのソースの行がすべて並べ替え変換によりロックされる必要があります。小規模データ セットの場合はこの影響を受けない可能性がありますが、規模が大きくなるといく分影響を受けます。まず、並べ替え (または、別の例では集計) に読み込まれるレコードは、メモリ内に格納されます。しきい値に達すると、キャッシュの一部は並べ替え変換または仮想メモリ マネージャにより一時的にディスクに読み込まれることが考えられ、このため I/O のパフォーマンスが低下し、他の下流変換が処理に必要とするメモリ リソースを消費します。データ フロー内で使われる変換によりデータがロックされると、パイプライン内の上流のデータに影響し、これがソース接続のフィルタに使用される場合、抽出のパフォーマンスが低下し、結果として全体の処理時間が増加します。

だからといって、完全に並べ替えまたは集計変換を使用を回避しているわけではありません。総体的には、これらの変換はきわめて高速に処理され、多くの状況で有用です。むしろ、処理量が膨大なシナリオやメモリ リソースの制限がある場合の注意を喚起するものです。

このマージ結合の例では、ソース データがあらかじめ並べ替えられている場合は、並べ替え変換を省くことができます。すべてのプロジェクト REAL シナリオにおいて、これは非常に有効でした。ETL プロセスは通常、ディメンションのビジネス キーまたはファクトの代理キーのデータ アソシエーションにマージ結合を使用するため、並べ替えはソース接続クエリの段階で行うこともできます。リレーショナル エンジンでの並べ替えは、適切なインデックスまたはインデックスの組がない限り、かなりのオーバーヘッドが必要になる可能性があります。ビジネス キーと代理キーはインデックスに使われる可能性が高いので、SQL クエリに ORDER BY 句を追加することは、有益で効率的な処理であると言えます。このためには、ソースが並べ替えられていること、および並べ替えを適用する列と方向をデータ フローが認識できる必要があります。これは、ソース接続の詳細エディタを使用して行います。[入力プロパティと出力プロパティ] タブで、[OLE DB ソースの出力] の最上位プロパティを確認します。IsSorted というプロパティがあります。この値を True に設定する必要があります。次に、出力列コンテナの SortKeyPosition プロパティを使用して、並べ替える列を指定する必要があります (図 33 参照)。これで、データ フローが並べ替えを認識するようになります。

データ フロー データのサブセットのみを対象とする並べ替えや集計が必要なその他の状況では、別の最適化方法としてデータを (マルチキャスト変換を使用して) 分岐する方法があります。その後、必要に応じて行をフィルタし (条件分割変換を使用)、データ処理に必要な出力列のサブセットを (並べ替えまたは集計内部で) 指定します。

例外に先立つ、一般的なシナリオの処理

パイプライン内で分岐、マージ、フィルタ、結合などの機能を使用できるので、特有のシナリオを個別に処理することで、処理を最適化する方法があります。これは、キャッシュ参照や一括宛先などの合理化プロセスを使用する時間で処理の 90% 以上を完了でき、残りの 10% は効率の落ちる方法で処理を制限する必要があるような場合に利用できます。

OLE DB コマンド変換対バッチ更新

大規模な更新は、ETL プロセスのアキレス腱であるとも言えます。システムによっては、プロセスのオーバーヘッド コストを回避するため、ファクト テーブルの更新をまったく行わない場合もあります。オフセット メジャーの差分に対するファクト テーブルの変更レコードの作成には、レポート作成や処理に独特の課題が伴います。プロジェクト REAL の日次在庫処理は、この状況をモデルにしています。在庫スナップショット ファクト テーブルには、毎週格納される詳細度が週単位の 2 億行のレコードがあります。また、現在の在庫データには、毎日 1,000 万件もの変更がある場合があります。これを合わせると、ボトルネックになることは目に見えています。

この規模の更新プロセスの処理には、次のような 2 つの主要な方法があります。

1. パラメータ化クエリを使用して OLE DB コマンド変換を使用する。

2. 変更レコードをステージング テーブルに格納し、セット ベースの RDBMS 更新を実行する。

最初の方法の場合は、SSIS にデータベースと直接通信してさまざまな操作 (最も一般的なものは更新ステートメント) を処理できる変換があります。OLE DB コマンド変換は、データ フロー列へのマッピングでパラメータ化された SQL ステートメントを使用します。行が変換に渡されると、行内に保持されているデータを使用して操作が実行されます。操作は 1 度に 1 行ずつ実行されるので、大規模な更新の処理に使われる場合は、多数の制限があります。数百万行がこの変換によりプッシュされて更新が実行される場合、リレーショナル データベースへの影響やデータ フローに課される影響、処理の完了に要する時間など、重大な問題が発生します。

2 番目の方法では、データのステージングを行い、リレーショナル エンジンを使って、更新ステートメント内でステージング テーブルと宛先テーブルを結合することで、更新を処理します。これは、ステージング環境をかなり利用することになりますが、コストを考えると、最初のメソッドの代わりにこの方法を進めることで、全体としてはメリットがある可能性があります。この方法の欠点は、ステージング環境の使用に伴うリソースのコストのほか、更新プロセス時のウェアハウス データベースへの影響が挙げられ、この場合システムのリソースの負担、およびテーブル、ページまたは行のロックが発生する可能性があります。

2 番目の方法のメリットは、もう 1 つの方法と比べると明らかです。更新に必要なデータをステージングすると、宛先の変換を最適化できるため、パイプラインのパフォーマンスが向上します。また、宛先テーブルへの影響がある期間も全体的に短縮されます。これは、最初の方法の行単位での更新に比べて、SQL による操作をより効率よく、セット ベースで処理できるためです。更新は、最適なインデックスを作成することや、おそらく一連の小さなバッチ プロセスに分けて更新を処理することでも、パフォーマンスを向上できます。

まとめ

核となる SSIS コントロールと、データ フロー コンポーネントにカプセル化されているビジネス ロジックは、プロジェクト REAL 全体の目的をサポートする基本的な ETL 操作を表現しています。ETL ソリューションにはビジネス データ プロセスの他に、管理や開発方法をサポートするメカニズムが必要ですが、これもプロジェクト REAL ソリューションに実装されています。概して、SSIS はこのようなデータや操作上の前提条件を満たす、パフォーマンスに優れた、開発が容易で柔軟な機能を提供しています。これは、現在の複雑なニーズに応えるためには非常に重要です。

謝辞 : Barnes & Noble、Intellinet、および Microsoft Consulting チームには、"本物の (REAL)" ソリューションを成功に導いたことに対し、賞賛と共にお礼を申し上げます。また、Microsoft SSIS 開発チームには、SQL Server 2005 の Beta 期間を通じてデザインについての助言やサポートを提供するうえで、非常に貴重なご支援をいただいたことを感謝いたします。このホワイト ペーパーで紹介した教訓は、プロジェクト REAL に注がれた時間や労力だけではなく、Barnes & Noble において実施されたパフォーマンス テスト、デザイン レビュー、試行錯誤、およびプロジェクト要件の収集および計画により習得されたものです。これを基盤として、プロジェクト REAL では中核となる ETL に着手し、最適化、導入、および改良に注力して、SQL Server 2005 プラットフォーム向けの確かな ETL モデルを確立することができています。

詳細情報 :

http://www.microsoft.com/japan/sql/