公開日: 2005年9月1日

SQL Server 技術資料
技術校閲者 : Grant Dickinson、Dave Wickert、Len Wyatt、Stuart Ozer
適用対象 : SQL Server 2005

ダウンロード

Project REAL Data Lifecycle Partitioning.doc
260 KB( Microsoft Word ファイル )

トピック

はじめに
データ ライフサイクルの概要
リレーショナル パーティション分割
キューブのパーティション分割
ETL の変更点
関連資料
まとめ

はじめに

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

• スキーマのデザイン (リレーショナル スキーマと Analysis Services のスキーマ)

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

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

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

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

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

このホワイト ペーパーでは、リレーショナル データ ウェアハウスや Analysis Services キューブに、パーティションを実装した方法について詳しく説明します。通常の "実装方法" の概説に加えて、具体的なコードのほか、読者の参考になることを願い、このプロジェクトで成功と失敗の両方から学んだ教訓をお伝えします。SQL Server 2005 を基盤とする BI システムを計画または実装する場合に、ここでご紹介する実装例がお役に立つことを願っています。

プロジェクト 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 から提供されました。

データ ライフサイクルの概要

どのようなデータ ウェアハウス実装においても、プロジェクトに適していて、業務要件に合ったデータ ライフサイクル戦略を開発することは、非常に重要な作業です。データ ライフサイクルは、プロジェクトのさまざまな場面に適用されることがありますが、ここでは新しいデータの追加を処理し、古いデータの削除を容易にするデータ管理プロセスを実装するための、データ ライフサイクルを規定しました。このため、このプロジェクトではパーティション分割、コスト効率の高いディスクへの古いデータの移動、ディメンションの排除の 3 領域にデータ ライフサイクルを分けています。これらの各領域が何を対象にしているかについて、簡単に以下に説明します。

パーティション分割

パーティション分割は、大規模データ セットをより小さな扱いやすいサイズに分割する方法の 1 つです。このホワイト ペーパーでは、SQL Server テーブルと Analysis Services のメジャー グループのパーティション分割について説明します。特に、パーティション分割列の値を基準にしてデータ行を分割する、行方向のパーティション分割を取り上げます。これは、大規模データ ウェアハウス実装では一般的な戦略で、基本的にデータの管理、キューブの読み込み、およびメンテナンスの手段として採用されています。また、クエリがパーティション分割列を使用してデータをフィルタする場合、SQL Server でも Analysis Services でも、クエリに関係のあるパーティションがどれかを推論できるので、データの照会にも役に立ちます。

コスト効率の高いディスクへの古いデータの移動

データ ウェアハウスは、その性質上、きわめて大量のデータを保持しています。このような大規模データ セットをメンテナンスする場合は、利用可能な技術を使用して、リソースのコストを管理することをお勧めします。データ パーティション分割戦略を使用する場合、利用頻度が低くなった古いデータが保持されている部分を時系列に検索できます。この時点で、関連するデータをコスト効率の高いディスクへ移動できます。このようなディスクでは、パフォーマンスが落ちたり、可用性が低くなりますが、データを引き続き照会できます。これは、リソース コスト、クエリのパフォーマンス、データの可用性のバランスを考えたうえで、業務により決定されるトレードオフです。

ディメンションの排除

データ管理において見過ごされがちなのが、ディメンションの排除です。これは、多くの場合、ディスクやデータベースの管理というよりも、ユーザビリティの問題です。Barnes & Noble では、最大のディメンションは Item (商品) ディメンションと Customer ディメンションの 2 つです。特に数百万のメンバが含まれるディメンションを参照することを考えると、提供中止になった製品や取引のなくなった顧客のデータは排除する必要がありました。

ディメンションの排除の要件としては、ファクト データがディメンションを参照しないことが 1 つ挙げられます。実は Analysis Services 2005 は、1 つ以上のディメンションがソース データにないときに、"不明" ディメンション キーをファクト レコードに格納する機能を提供していますが、ほとんどの場合このような操作は望ましくありません。ファクト レコードが存在するのであれば、対応するディメンション レコードがないと意味がないからです。

企業は、ディメンションを安全にデータ ウェアハウスから削除できるタイミングを決定する必要があります。たとえば、"製品を参照している売上レコードも在庫レコードがなくなってから 2 年" のように規定します。ただし、古いデータをファクト テーブルから削除するポリシーも、この規定と整合性がある必要があります。

プロジェクト REAL のディメンション排除の実装については、別のホワイト ペーパーで詳しく説明します。

Barnes & Noble の現在の環境

2004 年 に Barnes & Noble は、Microsoft Data Warehouse Framework を使用してデータ ウェアハウスを実装しました。対象となる領域は、顧客売上、店頭在庫、および DC (流通センター) 在庫です。現在、売上については 3 年分、店頭在庫については 1 年分、DC 在庫については半年分のデータがあります。データは、週次テーブルに読み込まれます。テーブルの命名規則を規定し、テーブル内に格納されているデータを識別できるようにしています。現在、SQL Server 2005 Integration Services (SSIS) を使用して、SQL Server 2000 リレーショナル データ ウェアハウスを読み込み、Analysis Services 2000 を使用して少数のキューブをホストしています。リレーショナル データ ウェアハウスのデータベース全体は、約 2 TB です。データは論理的にパーティション分割されていますが、SQL Server 2005 テーブル分割を実装することでさまざまなメリットが得られます。これについては、このホワイト ペーパーで後ほど説明します。

パーティション分割戦略を決定する前に、収集する必要がある要件がいくつかあります。そのうちの一部は、データの読み込み方法と、任意のパーティション分割の間隔に基づくパーティションのサイズに関係しています。業務により、データにアクセスできる期間が決まります。Barnes & Noble では、最終的には常時 5 年分の売上履歴データと 3 年分の在庫データを保持することを目標にしています。現時点では目標とする履歴データ量に達していないので、"スライディング ウィンドウ" 方式の実装の機能を例示できるよう、REAL プロジェクト独自の要件を設定しました。これについては、このホワイト ペーパーで後述します。次は、Barnes & Noble のパーティション分割の長所と短所のほか、パーティション分割が実装を進めるだけの価値があるものとして、これを行う最良の方法は何かを検討していきます。

リレーショナル パーティション分割

リレーショナル パーティション分割は、基盤となるリレーショナル データ ストアのパーティション分割を指します。従来のデータ ウェアハウスでは、このようなデータは、一般にスター スキーマまたはスノーフレーク スキーマと呼ばれるディメンション形式で格納されます。このプロジェクトのデータも同様です。その結果、テーブルはディメンション テーブルかファクト テーブルのいずれかです。ほとんどのディメンション テーブルは、列が数列と行が数行しかない比較的規模が小さいものです。Barnes & Noble には、かなり規模の大きなディメンション テーブルが 2 つあります。1 つは Item ディメンション テーブル (700 万行、5 GB) で、もう 1 つは Customer ディメンション テーブル (約 600 万行、1 GB) です。3 番目に大きなディメンションは Store ディメンションで、これは行が 400 行しかなく、使用するディスク領域も 2 MB 未満です。Customer ディメンションと Item ディメンションは、ファクト テーブルに比較すると規模は小さいと言えます。

読み込み、バックアップと復元、インデックスのメンテナンスなどを行う場合に、これらのテーブルを扱いやすくするためには、より規模の小さいテーブルになるよう行方向に分割することが最善の方法です。これが、このホワイト ペーパーで説明する "パーティション分割" 方法です。

パーティション分割のメリット

パーティション分割のメリットは、ここまでに簡単に触れてきました。より明確には、テーブルをパーティション分割した場合、大規模テーブルの管理操作をすべて、より細かい単位で実行できる点にあります。パーティション分割されたテーブルでは、バックアップを細かく分けて実行できます。これは、SQL Server 2005 の他の一部の新機能により、さらに容易に実行できます。具体的には、ファイル グループが読み取り専用の場合、SQL Server はファイル グループの復元時にトランザクション ログ バックアップが必要なくなりました。この場合、最新のパーティションを、古い非揮発性パーティションとは別のファイルグループに配置できます。非揮発性パーティションは、専用の読み取り専用ファイル グループに含めることができ、この場合はメンテナンスとバックアップが 1 度で済みます。このようにすると、メンテナンスが必要なのは揮発性パーティションだけになります。インデックスのメンテナンスなど、その他の操作も、パーティション分割を用いることで容易になります。これは、数 TB のデータベースを動的に管理する場合と、100 GB 未満のデータ パーティションを管理する場合との違いであると言えます。

また別のメリットとして、特にデータ ウェアハウス環境においては、クエリのパフォーマンスが向上することが挙げられます。これは、クエリがキューブの処理中に Analysis Services から発行される場合にも、エンド ユーザーがリレーショナル データ ウェアハウスを直接クエリする場合にも当てはまります。キューブ処理の場合は、処理対象のキューブがリレーショナル ソースと同じパーティション分割列を使ってパーティション分割されていると、パフォーマンスが向上します。パーティション キューブが処理されると、Analysis Services はそのパーティション内のデータのみを対象とするクエリを発行します。たとえば、処理対象のパーティションに最終日が 2005 年 1 月 1 日の週のデータが含まれている場合、Analysis Services はこの週のデータのみを照会するクエリを SQL Server に発行します。このため、スキャンするパーティション数を大幅に削減できます。

ユーザーからリレーショナル データ ウェアハウスに直接発行されるクエリには、通常日付要素が含まれています。一般的なクエリは、今期と前年期との売上比較です。このような場合、SQL Server はデータ量を参照されているデータに限定できるので、クエリのパフォーマンスが向上します。これは、Analysis Services に対するクエリにも当てはまります。

SQL Server 2000 のオプション

SQL Server 2000 のパーティション分割オプションは、"パーティション ビュー" のみです。パーティション ビューを作成する場合は、パーティション ビューに含めるすべてのテーブルを表示するビューを定義し、"UNION ALL" ステートメントを使用して、これらのテーブルを連結するだけです。クエリ オプティマイザが、あるクエリに関連するパーティションのみを分離するには、信頼できる制約を実装して、どのパーティションにどのデータが含まれているかを通知する必要があります。"信頼できる" 制約とは、"WITH CHECK" オプションを使用して作成される制約のことです。制約を作成するときにこのオプションを使用しない場合、または読み込み中に CHECK_CONSTRAINTS ヒントを提示しないで、データが一括読み込みまたは BCP 入力される場合、制約に違反するデータが存在してしまう可能性があります。SQL Server がこのような制約や多くの最適化を信頼しなくなり、結果として、これらの制約の機能メリットが実感されません。

SQL Server 2005 のオプション

SQL Server 2005 は、パーティション テーブルとパーティション インデックスという新しいパーティション分割オプションが用意されます。パーティション テーブルは、テーブルの他のユーザーへの影響を最小限に抑えつつ、行方向のデータ セグメントを読み込みおよび管理する手段を提供します。別のフォーラムでパーティション テーブルについて詳細な説明がされているので、ここでは大まかに概念を要約するにとどめます。他の情報への参照情報については、このホワイト ペーパーの最後に記載しています。

テーブルのパーティション分割により、テーブルやインデックスの論理的に区別できる各セグメントを、独立したエンティティとして扱うことができます。これらのセグメント、つまりパーティションは、テーブルの外部から読み込むことができるので、読み込み処理中にパーティション テーブルのユーザーには影響しません。パーティションの読み込みが完了すると、このパーティションはパーティション テーブルに "切り替わり" ます。パーティションの切り替えが正常に行われるためには、いくつか条件があります。そのうちの最も一般的なものは、以下のとおりです。

• パーティション テーブル構造のスキーマは、切り替え先のテーブルと同じである必要があります。これには、列名、データ型、NULL 値の許容属性、照合順序、有効桁数、主キー制約が含まれます。

• パーティション テーブルのインデックスが切り替え先のテーブルにもあり、名前以外のすべてのインデックス属性が一致している必要があります (インデックス列、クラスタ属性、一意属性など)。

• 切り替え先のパーティションは、空である必要があります。

• 切り替え元テーブルと切り替え先のパーティションのファイル グループは、同じである必要があります。

• 切り替え元テーブルには、切り替え先のパーティションと互換性のある、信頼できるチェック制約が必要です。

繰り返しますが、これは要件の一部に過ぎません。詳細な、より具体的な制限については、SQL Server Books Online を参照してください。

SQL Server によるパーティション テーブルの実装には固有の用語が多数あります。これらの簡単な定義を以下に記載しますが、詳細については SQL Server Books Online や Kimberly Tripp によるパーティション テーブルについてのホワイト ペーパーなど、他の資料を参照してください (このホワイト ペーパーの最後に参照情報を記載しています)。

• パーティション関数

  パーティション関数は、パーティションの境界値を定義する物理データベース オブジェクトです。

• パーティション構成

  パーティション構成は、パーティション関数を基に定義を行う物理データベース オブジェクトです。パーティション構成では、パーティション関数により定義された各パーティションのディスク上の場所を定義します。

• 固定

  パーティション テーブルおよびインデックスは、黙視または明示を問わず、同じパーティション関数に基づいて作成されている場合、固定されていると表現されます。

• 同一配置

  パーティション テーブルやパーティション インデックスは、同等のパーティション関数を使用しているだけでなく (固定されていて)、同等のパーティション構成を使用している場合、同一配置であると表現されます。このような場合、パーティション境界は同じになり、この境界に対応するデータは同じファイル グループに配置されます。

• RANGE LEFT/RANGE RIGHT

  これは、おそらくテーブルのパーティション分割の概念の中で紛らわしいものの 1 つでしょう。パーティション関数は、RANGE LEFT または RANGE RIGHT のいずれかを指定して定義します。違いを覚える方法としては、RANGE LEFT を指定した関数では、境界から相対のパーティション データが境界の左側に相対になります。RANGE RIGHT では、境界から相対のパーティション データが境界の右側に相対になります。したがって、境界が "01/01/2005" であり、パーティション関数に RANGE LEFT が指定されている場合、"01/01/2005" が境界の上限になり、その境界値以下のデータは境界の左側のデータになります。

• MERGE

  パーティションをマージすると、2 つのパーティションを 1 つにまとめられます。パーティションを 1 つ削除する場合に使用します。

• SPLIT

  パーティションを分割すると、1 つのパーティションを 2 つに分けられます。SPLIT は、パーティションを 1 つ追加する場合に使用します。

パーティション テーブルとパーティション ビューを比較する

パーティション ビューは SQL Server 2005 でも利用できます。また、データ ウェアハウスにおけるパーティション分割のオプションとしても有効です。通常、パーティション テーブルの方が管理が容易になります。以下の表は、パーティション テーブルとパーティション ビューの長所と短所を簡単に比較したものです。

表 1 SQL Server 2005 のパーティション テーブルとパーティション ビューの比較

実装の方針

Barnes & Noble では、SQL Server 2000 のパーティション ビューを実装していませんでした。これは、コンパイルにかなりの時間がかかるためで、ときに 30 秒にも達することがありました。現在、同社では 3 年分の売上データがあり、これは 156 のテーブル パーティションと相関関係があります。パーティション ビュー全体を対象とするクエリの場合、基盤となるテーブルの 1 つ 1 つを検証して、実行プランを評価する必要があります。このため、パーティション ビューは使用できませんでした。パーティション ビューではなくパーティション テーブルを選択した最大の理由は、コンパイルにかかる時間以外に、テーブルのメンテナンスが容易な点、複数のパーティションを対象とする INSERT、UPDATE、および DELETE 操作の実行が容易な点でした。

パーティション分割の方法が決定したら、次はパーティション分割列と最適な日付間隔を決定する必要がありました。データ ウェアハウスでのパーティション分割のメリットの多くは、パーティション分割列を日付列にすることでもたらされます。このプロジェクトの場合は、パーティション分割列は文字どおり datetime データ型ではなく、この列に保持されているデータは integer (CCYYMMDD) 値として格納されている日付を参照しています。

パーティション分割のデザインの方針

まず、パーティション キーについてのデザインを決定します。売上ファクト テーブルには Transact_Date 列があり、これは SQL Server datetime データ型の列です。これは、最初理想的なパーティション分割列であるように思えました。ただし、この列は在庫ファクト テーブルには定義されていません。このテーブルの "日付" 列は、領域の割り当てを最小限にするため、SQL Server int データ型 (CCYYMMDD の形式) で実装されています。名前が SK_Date_ID というこの列は、3 つのファクト テーブルすべてで一貫して実装されているので、この列をパーティション キーとすることにしました。この列を選んだことで 1 つ問題なのは、この列は売上ファクト テーブルの Transact_Date 列を照会することです。これは、Transact_Date 列が正真正銘の datetime データ型であるためです。ただし、クエリ オプティマイザは、クエリがパーティション分割列を基準にフィルタをする場合、スキャン対象のパーティションを制限してクエリを解決することしかできません。

次に、パーティション間隔についてのデザインを決定します。Barnes & Noble のデータは、在庫の読み込みが論理的に週単位に実行される (スナップショットが毎週始めに作成され、毎日更新される) ため、週次テーブルとして保存されています。また、これにより各パーティションのサイズが約 25 GB になります。これは、管理しやすいサイズです。月次のパーティション構成の場合は、約 100 GB となり、このサイズでは管理が難しくなります。売上データに関しても同じパーティション分割戦略を採用し、パーティション テーブルの固定によるメリットが得られるようにしました。これについては、後ほど説明します。

おそらく、最大の懸案事項は、パーティションをどのようにディスク上に配置するかでした。パーティションを、1 つ以上のファイルを使って 1 つ以上のファイル グループにマップするべきでしょうか。パーティション マッピングの 2 つの高レベルの戦略が持つ長所と短所については、ホワイト ペーパー「Strategies for Partitioning Relational Data Warehouses in Microsoft SQL Server」(英語) で詳しく説明されています (このホワイト ペーパーの最後に参照情報を記載しています)。このプロジェクトでは、各パーティションを専用のファイル グループに (基盤となるファイルを 1 つだけ使用して) マップする方がよいと判断しました。このようにすることで、ディスクへの配置方法を制御できます。この方法が望ましいかどうかについては、さまざまな見方があります。このプロジェクトでは個別のファイル グループを使用することで実現しているストライピングは、ディスク サブシステムを利用することで実装できるとも言えます。個別のファイル グループを使用して実装している主な理由は、3 つあります。

1. 読み取り専用属性をファイル グループ レベルで設定できます。パーティションが作成されてから 8 週間を過ぎると、ファクト テーブルの挿入は発生しなくなるため、読み取り専用属性を有効にできます。これにより、段階的な部分バックアップと復元 (これは SQL Server 2005 の新機能です) が可能になり、ロック範囲を縮小できます。

2. 経過期間による制御をディスク レベルで実行できる可能性があります。これを実現するには、各パーティションが専用のディスクファイルに分離されている必要があります。パーティションがファイル グループの複数のディスクにまたがりストライピングされている場合、これを実現することはできません。

3. 古いパーティションを経過期間によりコスト効率のよいディスクへ実装するためには、アクティブなディスク アレイ用に 1 つと、非アクティブなディスク アレイ用に 1 つの、少なくとも 2 つのファイル グループが必要です。このために、すべてを 1 つのファイル グループにマップすることはできません。

Barnes & Noble 環境へのパーティション テーブルの実装には、2 つの作業がありました。まず、既存のテーブルをすべて作成したパーティション テーブルに移動する必要がありました。次に、既存の ETL プロセスを変更して、毎週新しいパーティションを作成し、これらのパーティションに新しいデータを読み込むようにする必要がありました。これらの作業については、以下で個別に説明します。

パーティション インデックスについて

インデックスのパーティション分割についても、決定すべきことがいくつかあります。まず、パーティション テーブルのインデックスをパーティション分割するかどうか自体を決定する必要があります。分割する場合は、インデックスと基盤のテーブルとを固定するかどうかを決める必要があります。また、基盤のテーブルと同一配置にするかどうかも決める必要があります。インデックスをパーティション分割するかどうかの決定は、簡単に下すことができました。パーティション分割という考え自体はそもそも、管理性 (扱いやすさ) と管理の簡素化に大きく関係しています。揮発データはごく一部のデータのみなので、データの大部分が変更されないのであれば、規模の大きいインデックスを管理する意味はありません。ほとんどのインデックス メンテナンスはパーティションに対応しているため、必要なメンテナンスをインデックス全体ではなくインデックスのサブセットを対象にして実行できます。

インデックスは、クラスタ化インデックスでない限り、基盤となるベース テーブルとは異なる構造でパーティション分割できます。これは、定期的にデータをスワップするような状況では、ほとんど意味がありません。インデックスが同じパーティション関数を使用する場合、データは同じ構造で分割されているため、データをより効率よく移動できます。

最後の問題は、インデックスの配置場所に関するものです。クラスタ化インデックスの場合は、既定で基本テーブルのパーティション構造が適用され、パーティションと同じディスクに配置されます。非クラスタ化インデックスの場合は、別のパーティション構造を基に作成することができますが、関連するデータが確実に同じファイル グループ上に置かれた方が、SQL Server による操作の並列処理が容易になります。詳細については、Kimberly Tripp によるホワイト ペーパー「Partitioning in SQL Server 2005」(英語) を参照してください。

既存のテーブルのパーティション テーブルへの変換

既存のテーブルを変換するための最初の手順は、現在の Barnes & Noble 環境を見直すことでした。ディメンション テーブルとファクト テーブルは、現在 2 つのデータベースに格納されています。1 つは在庫ファクト テーブル (店頭在庫および流通センター在庫) 用で、もう 1 つは売上ファクト テーブルおよびディメンション テーブル用です。プロジェクト REAL では、データ マスク プロセス中に、これらを 1 つのデータベース REAL_Warehouse に統合しました。このプロジェクトでのパーティション分割作業としては、ファクト テーブルのみを処理することにしました。したがって、これについて主に説明します。

レガシの売上ファクト テーブルは、週単位の物理的な SQL Server テーブルとして表現されています。各テーブルは、"Tbl_Fact_Store_Sales_WE_ccyy_mm_dd" という命名規則に従っています。このとき、日付はその週の終わり (土曜日) の日付になります。テーブル名の例としては、"Tbl_Fact_Store_Sales_WE_2003_12_27" となります。このテーブルには、2003 年 12 月 21 から 2003 年 12 月 27 日までのデータが含まれています。

在庫ファクト テーブルに関する情報は、上記の情報と同様です。実際、DC (配送センター) 在庫用と店頭在庫用の 2 種類の在庫ファクト テーブルがあります。これらのテーブルの命名規則は、それぞれ "Tbl_Fact_DC_Inventory_WE_ccyy_mm_dd" と "Tbl_Fact_Store_Inventory_WE_ccyy_mm_dd" です。この 2 つのテーブルのメタデータは非常に異なるため、これらを結合することはできません。売上テーブルと同様に、パーティション分割列は SK_Date_ID です。データ型も同じです。

データ型が同じなので、SK_Date_ID を 3 つのファクト テーブルの共通のパーティション分割列にすることができました。これにより、結合しやすいように、これらのテーブルを固定することができます。キーは、日付をあらわす integer 値です。たとえば、"December 25, 2004" は 20041225 になります。既存の物理ソース テーブルは週次増分ベースなので、同様に週次ベースのパーティションへの変換は非常に容易です。パーティション テーブル内の 1 パーティションは、1 物理ソース テーブルに相当します。以下に、各論理ファクト テーブルを読み込むための大まかな手順を示します。

• すべてのファイルとファイル グループの作成

• パーティション関数の作成 - これにより、任意のパーティション テーブルの境界をすべて定義します。以下は、CREATE PARTITION FUNCTION ステートメントのサブセットです。

  
  CREATE PARTITION FUNCTION pf_Range_Fact(int) 
  AS  
  RANGE LEFT FOR VALUES ( 
     20020105, 
     20020112, 
     20020119, 
     . 
  . 
  . 
     20041231)
  

• パーティション構成の作成 - これにより、各パーティションをディスクにどのように配置するかを定義します。在庫ファクト テーブルと売上ファクト テーブルを固定することにしましたが、ストレージ固定ではありません。これにより、各パーティション間の結合が容易になると同時に、物理ストレージ全体に I/O をに分散できます。ここで "固定" と言う場合、パーティション テーブルが同等のパーティション関数を使用していることを表します。これは、文字どおりパーティション関数が同じであるということではなく、境界の定義、パーティション キーのデータ型、およびパーティション数が同じである必要があることを意味します。ストレージ固定は、両方のテーブルの任意のパーティションが同じファイル グループ上にあることを示します。

• パーティション テーブルの作成 - これは、また別の CREATE TABLE ステートメントのように思えますが、テーブルが上記のパーティション構成に "配置" されることを示しています。

• 切り替え元テーブル (例 : dbo.Tbl_Store_Inventory_WE_2004_12_25) ごとに、以下を実行します。

  • SELECT INTO を使用して一時テーブルを作成します。このテーブルが、いずれパーティション テーブルに切り替えられます。

  • 切り替え先のパーティションに、対応するチェック制約 (WITH CHECK) を追加します。

  • 次の構文を使用して、切り替え先パーティションを特定します。$partition function:SELECT @PartitionNum = $partition.FactRangePFN(20041225)

  • SWITCH を使用して、一時テーブルを切り替え先パーティションに切り替えます。

• インデックスの作成

CREATE PARTITION FUNCTION コマンドと CREATE PARTITION SCHEME コマンドは、自動的に生成されました。特に週次レベルでは、これらのコマンドを手入力すると面倒で間違いを生じやすいためです。また、パーティションの境界を定義する場合にいく分柔軟性が得られ、パーティション構成がパーティション関数の境界と一致していることを確認しやすくなりました。このコードは、再利用できるよう、プロジェクト REAL のサンプルに含まれています。

パーティション テーブルを初めて作成する場合は、非常に時間のかかるプロセスになる場合があります。このプロジェクトの場合、実際 1.5 TB のデータをコピーしました。課題は、このプロセスを可能な限り合理化することでした。INSERT INTO では、ログやロックのオーバーヘッドが発生するため、すぐに候補から外されました。BCP の場合は、最初にデータをディスクにコピーし、その後 BULK INSERT を使って一時テーブルに再読み込みする必要がありました。この方法は、SELECT INTO オプションを使用する場合よりもファイルのコピーをより細かく制御できますが、SELECT INTO を実行する方が、単に BCP を実行してデータを書き出すよりも実際に短時間ですみました。SELECT INTO オプションを使用した場合、BCP でログを作成せず、ロックを最小限に抑えた場合でも、BCP と BULK INSERT を組み合わせた方法よりも 9 倍以上も高速に処理できました。

SELECT INTO を使用する場合の最大の問題は、プロセス中に作成されるテーブルのファイル グループを指定する方法がないことです。これは、この一時テーブルのファイル グループが、読み込み先のパーティションのファイル グループと一致する必要があるため、非常に重要です。一時テーブルのファイル グループが適切に定義されているようにするため、データベースの既定のファイル グループを SELECT INTO を実行する直前に変更して、テーブルが目的のファイル グループ上に作成されるようにしました。これにより、テーブルを並列処理する機能が大幅に制限されました。すべての同時実行 SELECT INTO が同じファイル グループを使用しなければならないためです (一度に既定のデータベースのファイル グループを 1 つしか処理できません)。また、ディスクのホットスポットが発生し、並列処理に悪影響を及ぼす可能性もありました。それでも、BCP と BULK INSERT を使用する方法よりも、直列処理になっても SELECT INTO オプションを使用する方が望ましいと判断し、SELECT INTO での処理を進めました。

切り替え元テーブルには、テーブル内の日付データに対する制約が含まれていませんでした。切り替えが正常に行われるようにするには、切り替え先のパーティション境界と一貫性のある切り替え元パーティション分割列上に、信頼できるチェック制約が存在している必要があります。この制約は、SELECT INTO の完了後、読み込みプロセス中に追加しました。

また、3 つのテーブルすべてで、パーティション分割列 SK_Date_ID を NULL 値を許容するように定義しました。SK_Date_ID = NULL の行はすべて処理されるようなパーティションを定義する境界をパーティション関数に含めることもできましたが、これはパーティション分割戦略が日付を基準にしている場合意味がありません。また、NULL 値が設定された SK_Date_ID は、このプロジェクトの CHECK CONSTRAINT に違反します。SK_Date_ID が NULL になることはないことを Barnes & Noble に確認し、スキーマを使ってこのビジネス ルールを実装しました。

次の表は、大規模な在庫ファクト テーブルの 1 つの処理時間の内訳です。

表 2 テーブル パーティションのコピーの内訳

対象のテーブルの SK_Date_ID 列での NOT NULL 制約の実装に、ALTER TABLE ステートメントを使用していないことに注意してください。ISNULL 関数を使用することで、SELECT INTO ステートメント内にこれを実装することができました。この点を理解するには、次のステートメントを参照してください。

SELECT <Column 1>,  
<Column 2>,  
isnull([SK_Date_ID], -1) as [SK_Date_ID], 
. 
. 
. 
<Column x> 
INTO dbo.Tmp_NewPartition FROM Tbl_Fact_Store_Sales_WE_2005_01_01

この裏技を実装する前は、NOT NULL 制約を追加する ALTER TABLE ステートメントの実行に、1 テーブルにつき 6 分近くもかかっていました。3 つのパーティション テーブル全体でパーティションごとにこの時間が倍数的に増えると、かなりの時間になります。この "裏技" を使用することで短縮できたと予想される時間は、店頭在庫ファクト テーブルだけでもなんと 5 時間以上です。

パーティション テーブルを検証する

新しいパーティション テーブルを読み込んだら、まず結果を確認します。すぐにわかることは、直ちに違いは現れないということです。最初の検証では、パーティション テーブルと非パーティション テーブルの違いを示すものは何もありません。ただし、1 つ明確な点は、データベース内のオブジェクト数がかなり少ないことです。ソース データベースには 229 のファクト テーブルが含まれていました。一方、パーティション テーブルを使用したデータベースに含まれているファクト テーブルは 3 つです。この 3 つのファクト テーブルのいずれも、SQL Server Management Studio でプロパティを確認することで、さらに細かく検証することができます。以下の図は、Tbl_Fact_Store_Inventory テーブルのプロパティを表示している画面です。

行数から判断して、この読み込みは成功しています。ソース テーブルの合計行数が、このダイアログ ボックスで表示されている行数と同じかどうかを確認することで、追加の検証が可能です。[ストレージ] セクションでは、テーブルがパーティション分割されていることと、このパーティションがマップされているパーティション構成が表示されています。

動的管理ビュー (DMV) を照会して、パーティションに分散されている行の状態をより細かく確認することができます。これは、実際のカウントがテーブルの各パーティションに対して実行されるため、コストがかかる処理ですが、次のクエリを実行すると、全体的なパーティション テーブルと行の分散の状態を確認できます。

SELECT $partition.pf_Range_Fact(o.SK_Date_ID) AS [Partition Number] 
      , min(o.SK_Date_ID) AS [Min Date] 
      , max(o.SK_Date_ID) AS [Max Date] 
      , count(*) AS [Rows In Partition] 
FROM dbo.Tbl_Fact_Store_Sales AS o 
GROUP BY $partition.pf_Range_Fact(o.SK_Date_ID) 
ORDER BY [Partition Number]

このクエリの結果、売上ファクト テーブルについて次のような情報が返されます (ここでは、簡単に結果セットの一部のみを表示しています)。

増分処理

パーティション テーブルを配置できたら、これを継続的にメンテナンスする必要があります。これには、毎週始めに各パーティション テーブルに新しいパーティションを作成する処理が含まれます。この他、"スライディング ウィンドウ方式" を実装し、高価なディスクからよりコスト効率の高いディスクへの古いデータの移行も行います。

スライディング ウィンドウ方式

"スライディング ウィンドウ方式" の実装は、時間の経過に合わせてパーティション テーブル内のデータ範囲を変更します。つまり、新しいデータの読み込み時に新規パーティションを追加し、古いデータが不要になるのに合わせて古いパーティションを削除します。パーティション テーブル内に保持するデータの量は、完全にビジネス要件により決定されます。この要件は、実装ごとに異なります。たとえば、過去 3 年分の売り上げデータに常にアクセスできるようにすることが、要件の場合もあります。このような場合、古い売上レコードを削除すると同時に、新しい売上レコードを追加します。ここでは、以下のシナリオを基に、スライディング ウィンドウ方式の実装について明確に説明し、SQL Server 2005 パーティション テーブルでパーティションの追加と削除がどのように行われるかを例示します。

1. 2005 年 1 月 1 日で終わる週用の新しい外部ファクト テーブルに情報を読み込んだら、パーティション テーブル内でこの新しい情報を利用できるようにするとします。

   

   図 2
   拡大表示する
   
   

2. まず、パーティション関数の最後のパーティション (技術的には、2004 年 12 月 25 日より後のデータすべてで構成されるパーティション) を "分割" し、2004 年 12 月 25 日から 2005 年 1 月 1 日までのデータすべてで構成される新しい境界を含むように変更します。

   

   図 3
   拡大表示する
   
   

3. 次に、手順 1. の前に読み込まれていた外部テーブルと、新しく作成した (空の) パーティションを切り替えます。この結果、空の外部テーブルが作成され、これを削除することになります。

   

   図 4
   拡大表示する
   
   

4. これで、新しいデータを含むパーティション テーブルが追加されました。

   

   図 5
   拡大表示する
   
   

5. 次に、最も古いパーティションのデータを削除します。まず、パーティション テーブルと同じテーブル構成を使って、空の外部テーブルを作成します (インデックス含む)。

   

   図 6
   拡大表示する
   
   

6. パーティション テーブルの最初のパーティションと、この前の手順で作成した外部テーブルとを切り替えます。

   

   図 7
   拡大表示する
   
   

7. 2002 年 1 月 5 日以前の日付を含む最初のパーティションと 2 番目のパーティションをマージします。これで、最初のパーティションには、2002 年 1 月 12 日以前の日付のデータがすべて保持されています。2002 年 1 月 5 日で終わる週のデータはパーティション テーブルの外部に置かれているため、何らかの方法でこれをアーカイブした後、SQL Server からこのテーブルを削除します。

スライディング ウィンドウ方式の実装は、通常、分割と切り替えおよびマージと切り替えの操作により、パーティションの追加と削除を同時に行うことを表します。ただし、パーティションの追加と削除は直接依存し合うものではなく、完全に個別に実行できます。その他のスライディング ウィンドウ方式では、新しいデータの追加と同じ頻度で古いデータを削除できない場合も考えられます。たとえば、ビジネス要件が 3 会計年度分の全データを保持することである場合などです。このシナリオでは、新会計年度のデータは、毎週または毎月、徐々に蓄積されていき、最終的にその会計年度の全データが蓄積されます。この時点で、最も古い会計年度のデータを 1 度に削除します。このようなシナリオはすべて、プロジェクト REAL で使用したスライディング ウィンドウ方式の実装を用いて実現できます。シナリオによって違う点は、マージと切り替え操作を実行する方法とタイミングです。

Barnes & Noble では、まだ、データのアーカイブが必要なほど売上データも在庫データも蓄積されていません。したがって、スライディング ウィンドウ方式の実装をテストできるように、削除するまでの期間を短縮しました。このプロセスは、パラメータ ドリブンであり、容易に変更してタイミングを調整できます。

古いデータの処理

よく話題には上るものの実装されることがほとんどない概念として、大部分のアクティブ データを最も高速で可用性の高いディスク サブシステムに保持し、それほどアクティブでないデータを速度や可用性がやや落ちる、より安価なディスクでホストするという考えがあります。これは、極めてストレージ要件が高く、数年間分のデータをオンラインにしておく必要のあるデータ ウェアハウスで特に話題になります。この場合、過去 1 ～ 2 年間のデータに対する操作によるホットスポットが発生することが認識されています。しかし、これは、時折クエリにより、または法的な要件で、古いデータが必要にならないというわけではありません。このようなインフラストラクチャのメンテナンスにかかる費用を管理するため、プロジェクト REAL では増分処理中に、高価なディスク サブシステムから安価なものにパーティションを移動するメカニズムを実装しました。これは、パーティションの追加と削除が行われるのと同じ増分プロセス中に実行されます。

安価なディスクへの移動は、理論上はやや複雑ですが、実際には比較的簡単に実行できます。重要なことは、実際にデータをディスク サブシステム間で移動することです。これは、データ ウェアハウス内のデータを移動する場合は特に、非常にコストのかかる操作になりかねません。常に移動するデータの量が最小限ですむよう、1 週間分のパーティションごとに移動を行うようにしました。

次に、理論上複雑になる理由を説明します。パーティション構成により、パーティション関数で定義されているパーティションのディスクへの配置方法が定義されます。パーティション構成が作成されると、この名前を変更したり、新しいパーティションの配置先の指定以外は、構成自体を変更することができません。パーティション関数でパーティションを分割およびマージすることで、パーティションを追加または削除できますが、これは既存のパーティションの配置場所には関係ありません。したがって、パーティションを "移動" するには、新しいパーティション構成を作成して、ディスク上のパーティションの新しい配置場所を指定する必要があります。

以下は、古いデータの移動を実行する際に使用した手順の概要です。一見したところ、このプロセスは売上パーティション テーブルに 150 以上のパーティションがあるプロジェクト REAL のような状況では特に、やや複雑に思えます。実際には、データの移動の手順を除き、上記の手順はすべてメタデータの操作になるため、極めて短時間で実行されます。

1. 既存のパーティション関数を基に、新しいパーティション構成を作成します。これは、移動するパーティション以外については、既存のパーティション構成とまったく同じです。新しいパーティション構成の定義では、移動するパーティションの境界に、安価なディスク上のファイル グループを指定します。

2. 新しいパーティション構成を基に、新しいパーティション テーブルを作成します。

3. 各パーティションを順番に処理して、移動するパーティションに到達するまで、古いパーティションを新しいパーティション内の同じパーティション番号に切り替えます (どちらのパーティション テーブルも同じパーティション関数を使用します)。網掛けされている四角形はデータが読み込まれているパーティションを示し、白い四角形は空のパーティションを示しています。

   

   図 10
   拡大表示する
   
   

4. 移動するパーティションは、データを移動するため、明示的にコピーする必要があります。この場合、INSERT INTO..SELECT を使って、古いパーティションから新しいパーティションに直接データをコピーすることもできます。または、SELECT INTO を使って移動先のパーティションと同じファイル グループにある外部テーブルを利用することもできます。最初の読み込みと同様に、SELECT INTO の方が INSERT INTO よりもパフォーマンスがすぐれているため、ここでは SELECT INTO を使用しました。

   

   図 11
   拡大表示する
   
   

5. SELECT INTO の方法を使用する場合、外部テーブルを新しいパーティション テーブル内の最終的なデータの移動先に切り替える必要があります。

   

   図 12
   拡大表示する
   
   

6. 次に、現在のパーティション構成内の残りのパーティションを順に処理し、手順 3. で実行したようにパーティションを新しいパーティション テーブルに切り替えます。

   

   図 13
   拡大表示する
   
   

7. 最後の処理として、古いパーティション テーブルとパーティション構成を削除し、新しいパーティション テーブルの名前を元のパーティション テーブル名に変更します。

   

   図 14
   拡大表示する
   
   

コード サンプル

ここでは、この前に概説した古いデータの管理の実装に使用しているプロセスをより詳しく説明します。

1. 新しいパーティション構成を作成します。

   新しいパーティション構成は、パーティションを "リタイア" させるタイミングを定義する境界の日付値を変更している以外は、既存のパーティション構成の完全なコピーです。名前の最後には、現在の週の最終日を付け、一意の名前になるようにします。パーティション構成の名前は変更できないので、この名前は次の古いデータの処理プロセスで、新しいパーティション構成によりこの構成が置き換えられるまで保持されます。

   新しいパーティションを作成する通常の週次プロセスでは、1 つのパーティションのみが "アクティブな" ファイル グループから "古い" ファイル グループに移動されます。境界を移動し、新しいパーティション構成のスクリプトを構築するためのカーソルをメタデータ上に作成します。どのような ETL プロセスにおいても、パフォーマンスが悪いため、通常カーソルの使用は望ましくありませんが、この場合は少数のメタデータ オブジェクトに繰り返し処理を実行するためだけに使用しています。"アクティブ" から "古い" ファイルグループに移動されるパーティションを除いて、両方のパーティション構成ですべてのファイル グループが同じであることに注意してください。このコードは次のようになります。

   DECLARE CurrentSchemePartitions CURSOR FOR  
   SELECT FileGroupName, Boundary  
   FROM dbo.fn_Get_FileGroupsByPartitionBoundary(@psOld_Scheme_Name) 
   ORDER BY Boundary ASC  
    
   OPEN CurrentSchemePartitions 
    
   SET @psSQL_Text = 'CREATE PARTITION SCHEME ' + @psNew_Scheme_Name + '   
   AS PARTITION pf_Range_Fact  
   TO (' 
    
      FETCH NEXT FROM CurrentSchemePartitions INTO @psFG_Name, @pnBoundary_Date 
    
      WHILE @@FETCH_STATUS = 0 
      BEGIN 
         -- If the partition boundary is less than the beginning date, use the file    
         -- group for the new partition to move into the Agedd area 
         IF @pnBoundary_Date < CONVERT(int, CONVERT(char(10), @pdLogical_Date, 112)) 
         BEGIN 
     SET @psSQL_Text = @psSQL_Text + @psFG_Name + ', '    
            SET @psAged_FG_Name = @psFG_Name 
         END 
         -- If the partition boundary is less than or equal to the Aged date and was  
         -- previously in one of the "Current" filegroups, script the partition to  
         -- the Aged filegroup that will be relinquished when the old partition drops   
         -- off. 
         ELSE IF @pnBoundary_Date <= CONVERT(int,CONVERT(char(10),@pdAged_Date, 112))  
            AND @psFG_Name LIKE @psActive_FG_Prefix + '%' 
         BEGIN 
            SET @psSQL_Text = @psSQL_Text + @psAged_FG_Name + ', ' 
     SET @psActive_FG_Name = @psFG_Name 
         END 
         ELSE    
            SET @psSQL_Text = @psSQL_Text + @psFG_Name + ', ' 
    
         FETCH NEXT FROM CurrentSchemePartitions INTO @psFG_Name, @pnBoundary_Date 
    
      END 
    
      -- !!When we are done, we need to add a additional partition to the scheme.  
      -- This is for the right-most partition, which was not represented in our  
      -- cursor query because it is not in the partitioning function. Since we are  
      -- left partitioning, data will ever be in this final partition. 
      SET @psSQL_Text = @psSQL_Text + '[Primary])' 
      EXEC (@psSQL_Text) 
    
      CLOSE CurrentSchemePartitions 
      DEALLOCATE CurrentSchemePartitions
   

2. 新しいパーティション テーブルを作成します。

   新しいパーティション テーブルの定義は、古いものとまったく同じに見えます。唯一の違いは、先ほど作成した新しいパーティション構成を基に定義されることです。また、パーティションが古いパーティション テーブルから新しいパーティション テーブルに直接切り替えられるように、インデックスも作成する必要があります。

3. 手順 1. で使用したものとまったく同じカーソルを新規作成します。このカーソルをループして、各パーティションを新しいテーブル内の対応するパーティションに直接切り替えます。新しいパーティション構成も古いパーティション構成も同じパーティション関数を基盤としているため、移動元と移動先のパーティション番号は同じであることがわかっています。

   SELECT @PartitionNum = $partition.pf_Range_Fact(@pnBoundary) 
   SET @psSQL_Text = 'ALTER TABLE ' + @psPartitioned_Table_Name +  
   ' SWITCH PARTITION ' + CONVERT(varchar(3), @PartitionNum) + ' TO '  
   + @psNew_Partitioned_Table_Name + ' PARTITION ' +  
   CONVERT(varchar(3), @PartitionNum) 
   EXEC (@psSQL_Text)
   

4. パーティションを移動する場合、同じ構造の新しい外部テーブルにデータをコピーする必要があります。前述のとおり、最も効率的な方法は、SELECT INTO 操作を実行することです。その後、チェック制約とインデックスを作成します。外部テーブル (インデックスが作成された時点でパーティション テーブルになっています) を、手順 2. で作成したパーティション テーブルに切り替えます。これらの手順のコードは、次のようになります。

   SET @pnAged_Boundary_Date = @pnBoundary  
    
   -- Get the name of the Aged FG that the moving partition will reside on by  
   -- looking it up on the new partition scheme 
   SELECT @psNew_FG_Name = dbo.fn_Get_FileGroupForBoundary(@psNew_Scheme_Name,  
   @pnBoundary) 
    
   -- Change the default filegroup to the filegroup the moving partition will  
   -- reside on so the SELECT INTO will create the table on the correct filegroup. 
   EXEC etl.up_SetDefaultFG @pnBoundary, @psNew_Scheme_Name 
    
   IF (SELECT COUNT(*) FROM sys.tables WHERE name = 'MovingPartition') > 0 
   DROP TABLE MovingPartition 
    
   -- Copy the data from the moving partition on the old table to a temporary  
   -- partition on the new filegroup. 
   SELECT @PartitionNum = $partition.pf_Range_Fact(@pnBoundary) 
   SET @psSQL_Text = 'SELECT * INTO MovingPartition FROM ' +  
   @psPartitioned_Table_Name + 'WHERE $partition.pf_Range_Fact(SK_Date_ID) = '  
   + CONVERT(varchar(4), @PartitionNum) 
   EXEC (@psSQL_Text) 
    
   -- Since constraints and indexes were lost during the SELECT INTO, create them  
   -- to match those on the destination partitioned table. 
   SET @psBoundary = @pnBoundary 
   SET @pdWeek_Begin = SUBSTRING(@psBoundary, 5, 2) + '/' + 
   SUBSTRING(@psBoundary, 7, 2) + '/' +  
   SUBSTRING(@psBoundary, 1, 4) 
    
   SET @psSQL_Text = 'ALTER TABLE MovingPartition WITH CHECK  
   ADD CONSTRAINT MovingPartition_Date CHECK   
   (SK_Date_ID BETWEEN ' +  
   CONVERT(varchar(8), DATEADD(dd, -6, @pdWeek_Begin), 112) + ' AND ' +  
   CONVERT(varchar(8), @pnBoundary, 112) + ')' 
   EXEC (@psSQL_Text) 
    
   EXEC etl.up_CreateIndexes 'MovingPartition', @psNew_FG_Name 
    
   -- Get the partition number for the moving partition and switch it in to the  
   -- new partitioned table 
   SET @psSQL_Text = 'ALTER TABLE MovingPartition SWITCH TO ' +  
   @psNew_Partitioned_Table_Name + ' PARTITION ' +  
   CONVERT(varchar(3), @PartitionNum) 
   EXEC (@psSQL_Text) 
    
   DROP TABLE MovingPartition
   

5. 古いパーティション テーブルの削除または名前の変更を行います。

6. 新しいパーティション テーブルの名前を変更します。

所見と推奨事項

完全なデータを用いた実験を基に、いくつか所見がまとめられました。これらは、基本的にパフォーマンスの比較に関するものです。以下では、これらの所見について説明します。

パーティション関数を共有する

論理的には 3 つのファクト テーブルすべてで 1 つのパーティション関数を使用しています。これを解析してこれらのファクト テーブルを同じ方法でパーティション分割し、テーブル間の結合を容易にしています。たとえば、本年度の各月の在庫状況を比較するために、ある商品の売上データを紹介するリレーショナル クエリを発行するとします。SQL Server は、これらのパーティション テーブルが固定されている、つまり同じパーティション関数を共有していることを特定できます。固定パーティション テーブルを結合する場合、オプティマイザは、まず各パーティション内部で結合を行い、その後それぞれの結合結果をまとめることができます。テーブルが同じパーティション関数を共有する場合、文字どおり同じパーティション関数を共有する必要があるのではありません。それぞれのパーティション関数が、同じ数のパーティションを保持し、パーティション キーのデータ型が同じである必要があることを意味します。このような規則を遵守する最も簡単な方法は、同じパーティション関数を文字どおり共有することでした。

同じパーティション関数を共有することには、いくつか問題があります。最後のパーティションを分割して新しい境界を追加する場合、このパーティション関数を参照するすべてのパーティション構成では、次のパーティションの配置場所を特定しておく必要があります。マージ関数を使用してパーティションを削除する場合は、依存関係にあるすべてのパーティション構成でこのパーティションを空にする必要があります。問題のパーティション関数を参照するすべてのテーブルで最初のパーティションが空でない場合、マージ操作により削除されるパーティションからマージ先のパーティションの場所へ物理的に行が移動されます。これは重大な問題ではありませんが、行の移動中にパフォーマンスが低下する可能性があるほか、アーカイブ対象の行が実際には削除されません。

以下の表では、パーティション関数を左側に、3 つのパーティション スキーマを右側の列に並べています。新しいデータの切り替えの準備で最後のパーティションを分割するときに、3 つのパーティション構成のいずれにも適切な NEXT FG を設定し、新しいデータが適切な場所に読み込まれるようにする必要があります。2002 年 1 月 5 日で終わる週の削除の準備で最初のパーティションをマージするときに、3 つのパーティション構成を使用するすべてのパーティション テーブルで、最初のパーティション データが切り替えられ、テーブルから除かれていて、空である必要があります。たとえば、Tbl_Fact_Store_Inventory テーブルにこの週のデータが依然として残っていた場合、マージ操作によりこのデータは Aged FG 3 に移動されます。

これは、別のパーティション関数を作成することですべて解決できたかも知れませんが、いずれにしても同じ操作を実行する必要があります。唯一のメリットは、一度に全操作を実行する必要がないことです。パーティション関数を分けると、ある時点でファクト テーブルが保持しているパーティション数が一致しなくなる可能性もあります。これでは、そもそも固定テーブルを利用するメリットが失われます。

インデックスを作成する

パーティション テーブルへの最初のデータ読み込み時に、パーティション テーブルのインデックスをあらかじめ作成しておくか、読み込み後に作成すべきかが問題になりました。最初、この決定により、パフォーマンスには最小限の影響しかないように思えました。以下のようなオプションがあります。

オプション 1 - パーティション テーブルの読み込み前にインデックスを作成する

1. パーティション テーブルを作成します。

2. パーティション テーブルのインデックスを作成します。

3. 各ソース テーブルで次の操作を実行します。

   1. 外部テーブルを作成します。

   2. SELECT INTO を使用して外部テーブルにデータを読み込みます。

   3. チェック制約を作成します。

   4. パーティション テーブルのインデックスと同じインデックスを作成します。

   5. 外部テーブルをパーティション テーブル内の適切なパーティションに切り替えます。

オプション 2 - パーティション テーブルの読み込み後にインデックスを作成する

1. パーティション テーブルを作成します。

2. 各ソース テーブルで次の操作を実行します。

   • 外部テーブルを作成します。

     1. SELECT INTO を使用して外部テーブルにデータを読み込みます。

     2. チェック制約を作成します。

     3. 外部テーブルをパーティション テーブル内の適切なパーティションに切り替えます。

3. パーティション テーブルのインデックスを作成します。

下線が引かれている手順は、この 2 つのオプションで異なる部分です。どちらの場合も、データを読み込んだ後にインデックスを作成するため、2 つのオプションの間には大きな違いはないと思われますが、実際には違いがありました。オプション 2 の方が 70% も高速に処理できたので、最初の読み込み時にはこちらのオプションを使用しました。増分読み込みの場合は、この 2 つのオプション間で選択する余地はありません。最終的には、既にインデックスが作成されているパーティション テーブルに切り替えるためです。このため、切り替え前に外部テーブルに一致するインデックスを作成する必要があります。

オプション 1 を使用する場合、最初の読み込みであっても、増分読み込みであっても、読み込み先のパーティション テーブルが使用しているパーティション構成を基にインデックスを作成するようにします。つまり、これにより、読み込まれたパーティションが 1 つだけのパーティション テーブルに、外部テーブルを変更できます。したがって、SWITCH 処理中に、外部ソース テーブルと読み込み先テーブルの両方のパーティション番号を指定する必要があります。

メタデータ クエリ用のユーザー定義関数

パーティション関数とパーティション構成のメタデータを表示する新しいデータ管理ビュー (DMV) がいくつかあります。実装のさまざまな段階でこれらを利用しましたが、ここで説明する 2 つのユーザー定義関数を作成することで、複雑さを緩和することができました。これらの DMV についてのドキュメントは、このプロジェクトの開発時には作成されていなかったので、これらの正しい使用方法は、パーティション関数とパーティション構成オブジェクトの CREATES スクリプトを作成する際に、SQL Server Management Studio によって生成されたクエリをトレースすることで判断しました。

1 つめの関数は、任意のパーティション構成のすべてのファイル グループとこれらに関連付けられているパーティション境界 (このプロジェクトでは、LEFT パーティション関数を使用したため、境界の上限値) を表形式の結果セットにして返します。

CREATE FUNCTION dbo.fn_Get_FileGroupsByPartitionBoundary(@SchemeName varchar(50)) 
RETURNS TABLE 
AS RETURN 
( 
SELECT sf.name AS FileGroupName,  
CONVERT(int, sprv.value) AS Boundary 
FROM sys.partition_schemes AS sps 
INNER JOIN sys.partition_functions AS spf  
ON sps.function_id = spf.function_id 
INNER JOIN sys.destination_data_spaces AS sdd  
ON sdd.partition_scheme_id = sps.data_space_id  
AND sdd.destination_id <= spf.fanout 
INNER JOIN sys.partition_range_values sprv  
ON sprv.function_id = spf.function_id 
AND sprv.boundary_id = sdd.destination_id 
INNER JOIN sys.filegroups AS sf  
ON sf.data_space_id = sdd.data_space_id 
WHERE sps.name= @SchemeName 
)

2 番目のユーザー定義関数は、指定された境界以前の最後のファイル グループを返します。

CREATE FUNCTION dbo.fn_Get_FileGroupForBoundary (@SchemeName varchar(50), @Boundary int)RETURNS varchar(50)WITH EXECUTE AS CALLERAS-- Find last filegroup prior to or equal to specified boundaryBEGINDECLARE @FileGroupNamevarchar(50)SELECT TOP 1 @FileGroupName = sf.name FROM sys.partition_schemes AS spsINNER JOIN sys.partition_functions AS spf ON sps.function_id = spf.function_idINNER JOIN sys.destination_data_spaces AS sdd ON sdd.partition_scheme_id = sps.data_space_id AND sdd.destination_id <= spf.fanoutINNER JOIN sys.partition_range_values sprv ON sprv.function_id = spf.function_idAND sprv.boundary_id = sdd.destination_idINNER JOIN sys.filegroups AS sf ON sf.data_space_id = sdd.data_space_idWHERE sps.name = @SchemeName AND sprv.value <= @BoundaryORDER BY sprv.value DESCRETURN(@FileGroupName)END

キューブのパーティション分割

キューブのパーティション分割のメリット

リレーショナル データ ウェアハウスをパーティション分割するだけでなく、Analysis Services キューブもパーティション分割しました。より正確には、Analysis Services 2005 では、メジャー グループをパーティション分割しました。ただし、一般的にこの全体のプロセスを "キューブのパーティション分割" と表現します。大規模なキューブをパーティション分割することで、リレーショナル データ ウェアハウスをパーティション分割した場合と同様のさまざまなメリットが得られます。最も顕著なメリットは、キューブのメンテナンスに関するものです。キューブは、パーティション レベルで完全処理または増分処理されます。複数のパーティションのあるキューブでは、パーティションを指定して処理できます。これは、ソース テーブルの変更により影響を受けるパーティションが 1 ～ 3 つ程度しかない場合は、特に関係があります。これにより、増分処理のバッチ処理時間を大幅に短縮できます。また、パーティションごとに個別に集計やストレージ モードを設定できます。日付を基準にしてパーティション分割する場合、古いパーティションは低い集計レベルまたは異なるストレージ モード (ROLAP、HOLAP、MOLAP) で処理し、必要なディスク領域や必要な処理時間を削減できます。プロアクティブ キャッシュも、ディメンション レベルで定義されます。パーティションは、リモート サーバー上にリモート パーティションとして保存することもできます。古いパーティションは単純に削除できるので、管理の容易性も向上します。メジャー グループ全体を再処理し、基盤となるリレーショナル データ ソースのデータのアーカイブを認識する必要はありません。パーティション キーを基にデータを制限するようなキューブ クエリを作成する場合は、クエリ パフォーマンスの向上も期待できます。

Analysis Services 2005 での変更点

Analysis Services 2000 でのパーティション分割についての原則のほとんどは、Analysis Services 2005 にも当てはまります。いくつかの変更があり、その大部分は以前のバージョンの機能強化です。

まず、パーティションがキューブ レベルではなく、メジャー グループ レベルで定義されるようになりました。キューブには、1 つ以上のメジャー グループがあり、これはメジャー グループのソース テーブル内の論理ファクト テーブルに関連付けられます。優れたパフォーマンスの向上が得られているのは、キューブやメジャー グループ処理の自動化により、基盤のパーティションが並列処理される点にあります。Analysis Services 2000 では、Parallel Process Utility (Microsoft Web サイトから無償でダウンロード可能) など、カスタムの DSO プログラムにより明示的に並列処理を行わない限り、パーティションは直列に処理されます。

Analysis Services 2000 では、データ スライスがキューブ パーティション上に定義され、OLAP エンジンがどのデータがどのパーティションに保持されているかを認識できるようにしている場合のみ、パーティション分割によるクエリ パフォーマンスのメリットを実現できました。これは、パーティション ビューに定義チェック制約を設定して、SQL Server オプティマイザによりパーティション分割列を基に、クエリの対象にするテーブル数を最小限にできるようにすることに似ています。Analysis Services 2005 では、この目的のために MOLAP キューブにデータ スライスを定義する必要がなくなりました。これは、MOLAP キューブに、異なるパーティションに保持されているデータをマップするためのヒューリスティックが含まれているためです。プロアクティブ キャッシュの再構築中などに ROLAP に戻るキューブの場合、これらのヒューリスティックは利用できず、データ スライスが定義されていない限り、優れたパフォーマンスが得られない可能性があります。したがって、キューブが ROLAP に戻るような場合は、データ スライスを定義することが推奨されます。

Analysis Services 2000 キューブのデータは、直接ソースのリレーショナル データベース内のテーブルまたはビューから定義しました。このとき、フィルタを定義して、テーブルまたはビューのサブセットを指定できました。たとえば、パーティション ビューを使用する場合、パーティション ビューの名前を Analysis Services 2000 パーティションのソースとして使用し、別個のフィルタを使用して AS パーティション内のデータをパーティション ビュー内の基盤のテーブル内のデータに制限することができました。Analysis Service 2005 では、ソースにテーブル、ビュー、またはクエリのいずれかを指定します。基盤のテーブルまたはビューのサブセットをパーティションに読み込む場合、パーティションの定義でクエリを指定します。これを、"クエリ バインド" と呼びます。

また、Analysis Services 2005 のパーティション作成の自動化オプションの種類が増えました。以前は、既存のパーティションの "複製"、関連属性の変更、新しいパーティションの保存に DSO (Decision Support オブジェクト) を使用していました。DSO は、AMO (分析管理オブジェクト) に置き換えられています。AMO からの要求はすべて、最終的には XMLA (XML for Analysis) スクリプトに変換されます。SSIS はネイティブ XMLA を実行できるため、これはまた別のオプションです。どれが推奨のオプションでしょうか。プロジェクト REAL では、これを見極めることを目標にしています。

Analysis Services パーティション分割戦略の検討

Analysis Services のパーティション分割を実装する最初の手順は、パーティション分割戦略を決定することです。つまり、どの境界でパーティションを分割するかです。リレーショナル データ ウェアハウスの場合と同様に、一般的な戦略は、日付を基準にしてパーティションを分割する方法です。データは通常日付を基に増分が読み込まれるため、これにより処理が容易になります。また、アーカイブも簡素化されます。前述のとおり、バックアップが完了したら、古いパーティションを単純に削除できるためです。Analysis Services のキューブは、基盤となるリレーショナル データ ウェアハウスと同じ条件でパーティション分割すると便利です。これにより、適切なパーティションをデータ ウェアハウスとキューブの両方から削除する 1 プロセスで、データのアーカイブを実行できます。これは、プロジェクト REAL で Barnes & Noble の環境に実装されている戦略です。

Barnes & Noble の実装では、各ファクト テーブルを基にしたビューを利用して、関連付けられているディメンション メンバの詳細情報を追加しています。これは、Analysis Services 2000 キューブ パーティションのソース "テーブル" として機能します。パーティション テーブルを導入することで、ビューの数が 229 ビュー (各ファクト テーブルに対し 1 週間ごとに 1 つのビュー) から 3 ビュー (ファクト テーブルごとに 1 つ) に激減しています。Analysis Services 2005 のパーティションは、関係のあるビューに対するクエリを指定し、WHERE ステートメントを使用してデータ範囲を 1 つのパーティションに制限しています。

SELECT [SK_Store_ID], [SK_Parent_Store_ID], ... 
FROM [dbo].[vTbl_Fact_Store_Inventory]  
WHERE [SK_Date_ID] BETWEEN 20041212 AND 20041218

Analysis Services 2005 のキューブの作成と処理の自動化には、2 つの方法があります。長所と短所も含め、これらの方法の詳細について、以下で説明します。

XML/A の概要

1 つめの方法は、XMLA、つまり、より具体的には ASSL です。XMLA は、2001 年 4 月に発表された OLAP データのクエリ用の XML 仕様です (XMLA 仕様の詳細については、www.xmla.org (英語) を参照)。ASSL は Analysis Services 固有のもので、OLAP 用の XML DDL 仕様です。XMLA は Analysis Services 2005 用のネイティブ データ交換プロトコルです。Analysis Services との通信はすべて、最終的には XMLA を介して行われます。これは、変換処理が必要ないため、Analysis Services 2005 との最速の通信手段となっています。ただし、これは、おそらくほとんどのメタデータ操作では、重要な要素ではありません。Analysis Services に対して送られるクエリの数はそれほど多くないので、変換コストが大きくないためです。

XMLA スクリプトも、最初はいくぶん複雑に思えるかもしれません。他の XML スクリプト言語と同様に、XMLA の要素は階層構造で表現され、自己記述的です。XMLA は複雑な言語なので、ゼロから容易にスクリプトを開発できるようなものではありません。幸い、Analysis Services 2005 を使用することで、比較的容易に取り掛かることができます。

XML/A の実装

Analysis Services 2005 では、キューブやディメンション、パーティションなどのスクリプト オブジェクトの機能が大幅に強化されています。これに比較して、Analysis Services 2000 には、標準の状態ではスクリプト機能はありません。このスクリプト機能により、XMLA 仕様の詳細な知識が必要とされないので、XMLA の使用を妥当な選択肢として検討できるようになりました。XMLA は、新しい [Analysis Services XMLA クエリ] ウィンドウを開くことで、SQL Server Management Studio から実行できます。また、SQL Server Integration Services (SSIS) からも "Analysis Services DDL 実行タスク" を使用して実行できます。このため、XMLA を利用したパーティションの作成と処理を容易に自動化できます。

まず、各キューブ メジャー グループ用の XMLA スクリプトを作成します。これには、SQL Server Management Studio でメジャー グループの任意のパーティションに移動し、これを右クリックして [名前を付けてパーティションをスクリプト化] をクリックします。次に、作成できた XMLA を確認して、パーティションごとにどのような変更が必要かを把握します。これには、SQL Server Management Studio の [XMLA クエリ] ウィンドウにスクリプトを貼り付けます。次に、このスクリプトを SSIS スクリプト タスクに貼り付け、パーティションごとに異なる変数であるテキスト部分はすべて、各パーティションに合わせて変更される変数に置き換えられます。この XML 文字列を、Analysis Services DDL 実行タスクで実行する SSIS 変数として保存します。次のスクリプトは、REAL Warehouse キューブの Store Inventory メジャー グループのパーティションをスクリプト化した SSIS スクリプト タスクの抜粋です。変数は、パーティション名、パーティション ID、およびリレーショナル ソースに対するクエリ用に作成されています。

sXMLA = sXMLA & "<Create  
xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine"">" 
sXMLA = sXMLA & "    <ParentObject>" 
sXMLA = sXMLA & "        <DatabaseID>REAL Warehouse Partitioned</DatabaseID>" 
sXMLA = sXMLA & "        <CubeID>REAL Warehouse</CubeID>" 
sXMLA = sXMLA & "        <MeasureGroupID>Store Inventory</MeasureGroupID>" 
sXMLA = sXMLA & "    </ParentObject>" 
sXMLA = sXMLA & "    <ObjectDefinition>" 
sXMLA = sXMLA & "        <Partition xmlns:xsd=""http://www.w3.org/2001/XMLSchema""  
xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"">" 
sXMLA = sXMLA & "            <ID> & sPartitionName & </ID>" 
sXMLA = sXMLA & "            <Name> & sPartitionName & </Name>" 
sXMLA = sXMLA & "            <Annotations>" 
sXMLA = sXMLA & "                <Annotation>" 
sXMLA = sXMLA & "                    <Name>AggregationPercent</Name>" 
sXMLA = sXMLA & "                    <Value>13</Value>" 
sXMLA = sXMLA & "                </Annotation>" 
sXMLA = sXMLA & "            </Annotations>" 
sXMLA = sXMLA & "            <Source xsi:type=""QueryBinding"">" 
sXMLA = sXMLA & "                <DataSourceID>REAL Warehouse</DataSourceID>" 
sXMLA = sXMLA & "                <QueryDefinition> & sQuery & "</QueryDefinition>" 
sXMLA = sXMLA & "            </Source>" 
sXMLA = sXMLA & "            <StorageMode>Molap</StorageMode>" 
sXMLA = sXMLA & "            <ProcessingMode>Regular</ProcessingMode>" 
sXMLA = sXMLA & "            <ProactiveCaching>" 
sXMLA = sXMLA & "                <SilenceInterval>PT10M</SilenceInterval>" 
sXMLA = sXMLA & "                <Latency>-PT1S</Latency>" 
sXMLA = sXMLA & "                <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>" 
sXMLA = sXMLA & "                <ForceRebuildInterval>-PT1S</ForceRebuildInterval>" 
sXMLA = sXMLA & "                <Source xsi:type=""ProactiveCachingInheritedBinding"" />" 
sXMLA = sXMLA & "            </ProactiveCaching>" 
sXMLA = sXMLA & "            <EstimatedRows>2000000</EstimatedRows>" 
sXMLA = sXMLA & "            <AggregationDesignID>AggregationDesign 2</AggregationDesignID>" 
sXMLA = sXMLA & "        </Partition>" 
sXMLA = sXMLA & "    </ObjectDefinition>" 
sXMLA = sXMLA & "</Create>"

キューブ処理は、XMLA を使ってスクリプト化できます。それには、SQL Server Management Studio で任意のメジャー グループ パーティションに移動し、これを右クリックして [処理] をクリックします。次に、表示されるダイアログ ボックスの上部にある [スクリプト] をクリックします。キューブを処理するサンプルの XMLA スクリプトは、以下のようになります。

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> 
  <Parallel> 
    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema"  
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
      <Object> 
        <DatabaseID>REAL Warehouse Partitioned</DatabaseID> 
        <CubeID>REAL Warehouse</CubeID> 
        <MeasureGroupID>Store Inventory</MeasureGroupID> 
        <PartitionID>Store Inventory WE 2004 12 11</PartitionID> 
      </Object> 
      <Type>ProcessFull</Type> 
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation> 
    </Process> 
  </Parallel> 
</Batch>

AMO の概要

分析管理オブジェクト (AMO) は、Analysis Services 管理用の .NET オブジェクトの完全なセットです。AMO は、Analysis Services 2000 および SQL Server 7.0 の OLAP Services で使われていた DSO オブジェクト モデルの後継です。Analysis Services 2000 では分析マネージャのアクションが DSO によって実装されていたのと同様に、Analysis Services 2005 では、Analysis Services インスタンスとデータベースの管理のすべてのメカニズムは、AMO を使用して実装されています。AMO は XMLA の上位層で、最終的に XMLA を生成して Analysis Services インスタンスとのすべての通信を実行します。このため、パフォーマンスが XMLA よりも若干劣ります。ただし、SMO メソッド呼び出しが多数行われない限り、パフォーマンスの違いはわかりません。

SQL Server 2005 の他の機能強化により、AMO の操作がその前身の DSO に比べてかなり簡素化されました。DTS では頻繁にオートメーション タスクが実装されていたので、VBScript 開発環境で作業しなければなりませんでした。この環境には、VSA (Visual Studio for Applications) インターフェイスを使用している SSIS のスクリプト タスクにあるような機能が豊富なコーディング機能 (Intellisense、色分けなど) がありませんでした。プロジェクト REAL の AMO コード開発では、Books Online に AMO についての記述がまだ少なかったので、この機能を非常によく使用しました。

おそらく、最も機能性が低い DSO の機能は、パーティション オブジェクトに移動するための MDStores インターフェイスの実装でした。これは、AMO では提供されておらず、データベースのナビゲーションはより直感的になりました。これについては、以下のコード サンプルを参照してください。

なお、Barnes & Noble における Analysis Services 2000 の現在の実装では、DSO と DTS によるキューブのパーティション作成は行っていません。パーティションは 1 年に 1 度手動で作成し、処理は Parallel Process Utility を使用して実装します。これは、Analysis Services 2000 ではパーティションをネイティブに並列処理できないためです。Analysis Services 2005 の機能強化により、これらのプロセスを容易に自動化でき、管理オーバーヘッドを削減できるようになりました。

AMO の実装

前述の XMLA を使用して実行した機能と同じ機能を AMO を使用して実行することが目標でした。実際、AMO オブジェクト モデルに継承されているループ メカニズムを基に、機能を強化できました。各メジャー グループをループし、メジャー グループが指定されたファクト テーブルを参照しているかどうかを確認しました。参照している場合は、指定した処理の日付を使用して、関連付けられているパーティションが既に存在しているかどうかを確認しました。パーティションが存在している場合は、これを作成する必要はありません。

XMLA 実装では、事前に生成したスクリプトを実行し、パーティション名、パーティション ID、およびクエリ定義を指定する XML の要素を変更することでパーティションを作成しました。AMO では、Clone メソッドを使用して、メジャー グループ内の既存のパーティションをコピーすることができます。明示的にパーティションのプロパティをコードに設定してパーティションを作成するのではなく、Clone メソッドを使用して Partitions コレクションの最後のパーティションからすべてをコピーし、前述の XMLA スクリプトで変更したものと同じプロパティを変更しました。

また、スクリプト タスクに実装されている VSA 環境を使用することで、より優れたエラー処理機能を利用できました。DTS の VBScript では、グローバルにエラーを処理する方法がなかったので、各アクションの後にエラー チェックをつける必要がありました。このため、コードがより複雑で、読みにくくなりました。SQL Server Integration Services (SSIS) のスクリプト タスクで Visual Basic .NET を使用することで、Try..Catch ステートメントを使用して 1 つのステートメント セットだけでエラーを一貫して処理できました。また、変数を宣言し、初期化する機能を使用して、コードを読みやすくすることもできました。

その他に使用した手法は、既に実装されていた SSIS 接続マネージャの接続情報を収集することでした。つまり、このパッケージを QA および実稼動環境に展開する場合、サーバーとデータベース情報を 1 か所で変更するだけで済みます。また、これは、SSIS の構成を使用しても実装できました。

以下は、パーティション作成機能を実現するために使用したコードの中核的な部分です。

Try 
 
   Dim oDB As Database = oServer.Databases(sDatabase) 
   Dim oCube As Cube = oDB.Cubes("REAL Warehouse") 
 
   Dim dLogicalDate As Date = CDate(Dts.Variables("vdtLogical_Date").Value) 
   Dim sTableName As String = CStr(Dts.Variables("vsPartitioned_Table_Name").Value) 
   Dim sWeekEnd As String = GetIntegerDateFormat(dLogicalDate) 
 
   ' Find all measure groups that reference the table being processed 
   For Each oMeasureGroup In oCube.MeasureGroups 
  oPartition = oMeasureGroup.Partitions(0) 
      oQueryBinding = oPartition.Source 
      If oQueryBinding.QueryDefinition Like "*" & sTableName & "*" Then 
         ' Get the relevant boundary partition name and check to see if it  
         ' already exists 
         sPartitionNew = GetNewPartitionName(sWeekEnd, oPartition.Name) 
         oPartition = oMeasureGroup.Partitions.FindByName(sPartitionNew) 
         If oPartition Is Nothing Then 
            ' Get the last partition 
            oPartition = oMeasureGroup.Partitions(oMeasureGroup.Partitions.Count - 1) 
            ' Clone the properties from the last partition to the new partition. 
            oPartitionNew = oPartition.Clone 
            oPartitionNew.ID = sPartitionNew 
            oPartitionNew.Name = sPartitionNew 
            oQueryBinding = oPartitionNew.Source 
            oQueryBinding.QueryDefinition = GetNewQuery(oPartition.Source, sWeekEnd) 
            oMeasureGroup.Partitions.Add(oPartitionNew) 
            oPartitionNew.Update() 
         End If 
      End If 
   Next 
   Dts.TaskResult = Dts.Results.Success 
Catch ex As Exception 
   Dts.Events.FireError(0, "Create Partition", ex.Message, "", 0) 
   Dts.TaskResult = Dts.Results.Failure 
End Try 
If oServer.Connected Then 
   oServer.Disconnect() 
End If

VSA には 1 つ問題があります。それは、参照されるアセンブリがすべて、<Windows パス>\ Microsoft.NET\Framework パスの適切なバージョンのサブディレクトリに置かれている必要があることです。AMO アセンブリと SMO アセンブリは、手動で上記ディレクトリの <SQL Server>\90\SDK\Assemblies ディレクトリにコピーする必要があります。SQL Server の新しいビルドをインストールした場合、新しいバージョンの VSA のディレクトリが作成される可能性があるので、これらのファイルを再コピーが必要になるように、アセンブリの変更が施されている可能性があります。このホワイト ペーパーで取り上げている AMO コーディングでは、AMO アセンブリ (Microsoft.AnalysisServices.DLL) のみが必要です。

VSA が認識できる場所に適切なファイルをコピーしたら、スクリプト タスクでこれらのファイルへの参照を追加する必要があります。これには、プロジェクト エクスプローラの [参照] を右クリックし、[参照の追加] をクリックします。分析管理オブジェクト参照を探し、これを追加します。次に、以下の図のように、"Imports Microsoft.AnalysisServices" (Microsoft.AnalysisServices をインポートする) スクリプトの最初にコードを追加します。

所見と推奨事項

XMLA スクリプトと AMO オプションは、特に SSIS を使用すると、いずれも簡単に実装できます。それぞれの方法の所見を以下に記載します。具体的な実装により、一方のオプションがより望ましいと判断される可能性があります。相対的に、以下に示すように、XMLA よりも AMO の方がメリットが多いことがわかりました。

AMO の所見

長所 :

• より洗練され、簡潔です。これは主観的な意見ですが、おそらく大半の場合に当てはまります。

• 将来オブジェクトの追加に使用できます。AMO コードを参照するとわかりますが、各メジャー グループの最初のパーティションが検証され、渡されたファクト テーブルへのクエリ参照の有無が確認されます。適切にデザインされたキューブがある場合は、同じファクト テーブルを参照するために追加のキューブが追加されることは考えにくいですが、そのような状況に対応できます。また、メジャー グループ名を変更しなければならない場合にも対応できます。XMLA 実装ではメジャー コード名はハードコードされます。

• パーティションのプロパティの変更を動的に組み込むことができます。最後のパーティションをコピーしてから、集計デザイン、ストレージ モード、プロアクティブ キャッシュなどが変更された場合、これを新しいパーティションにコピーします。通常は、これは望ましい処理です。

• 読みやすいスクリプト タスクですべてのコードを参照できます。XMLA 実装では、スクリプト タスクのほか、Analysis Services DDL 実行タスクを使用する必要があります。

• 追加の作業と組み合わせて、1 つのタスクのように処理できます。処理を Analysis Services DDL 実行タスクを使用して実装する場合でも、Analysis Services 処理タスクを使用して実装する場合でも、個別のタスクが必要です。これに比較して、AMO スクリプトでは 1 行のコードを追加するだけで、処理を実行できます。

短所 :

• SQL Server の新しいビルドごとに Microsoft .NET にアセンブリを手動でコピーする必要があります。

XMLA の所見

長所 :

1. すべてが公開されます。基盤となるオブジェクトの関連プロパティがすべて公開されるので、変更すべきものと変更すべきではないものを容易に特定できます。

2. 最低レベル。XMLA は Analysis Services 用のネイティブな通信プロトコルなので、最速で処理されます。

短所 :

• 手動でスクリプトを作成する必要があります。実際、これは厳密な要件ではありません。メジャー グループごとに異なる可能性のあるプロパティをすべて置き換える変数を使用する一般的なスクリプトを作成できます。これらのプロパティは、メジャー グループごとにハードコードするか、AMO を使用して各メジャー グループから抽出する必要があります。追加のメジャー グループについては、やはり手動でスクリプトに追加する必要があります。

• パーティションの有無の確認には AMO を使用する必要があります。このプロジェクトのシナリオでは、ETL プロセスを再起動可能にするため、目的のパーティションを作成する前に、このパーティションが既に存在しているかどうかを確認します。これは、AMO を使用しないと実現できません。このコードは、AMO 実装シナリオでは、パーティションを作成するコードに自然な形で組み込まれます。

• もともとスクリプト化されていないものを変更する場合、スクリプトの再作成または手動での変更が必要になります。これは、おそらくパーティション作成を自動化する場合、XMLA スクリプトの最大の問題です。まれに、常に最初にスクリプトしたプロパティを維持することが望ましい場合があります。この場合は、AMO または XMLA のいずれを使用しても、容易に実装できます。

全体的な所見

どちらの方法も、SSIS の多くの機能強化によるメリットがあります。具体的には、次のとおりです。

• SSIS ではパーティション作成を容易に実装できます。DTS は DSO コードの開発に VBScript タスクを提供していましたが、この開発環境には、SSIS スクリプト タスクが使用する VSA 環境で提供されているメリットがほとんどありませんでした。Analysis Services DDL 実行タスクを使用すると、XMAL を非常に簡単に実行できます。

• より優れたエラー処理とデバッグ機能により、読みやすいコードを短い時間で開発できるようになりました。

• AMO をまったく使用しない場合、何らかの回避策を用いて VSA が AMO アセンブリを参照できるようにする必要があります (「AMO の所見」の最後の項目を参照してください)。

ETL の変更点

すべてのコンポーネントを定義して、増分パーティション メンテナンスを実装できたら、これを既存の ETL プロセスに統合する必要があります。既存の実装は、パーティションごとに明示的な SQL Server 2000 テーブルを使用していました。パーティションのアーカイブの実装が必要になるほど履歴データがないため、このプロセスは現在の ETL には実装されていません。同様に、古いデータの処理を実装するプロセスもありません。

以下は、Barnes & Noble の店頭在庫と DC 在庫ファクト テーブルの既存の ETL プロセスの概要です。これは、リレーショナル パーティションの管理への適用状態を示しています。

• 実行前の処理を行います。

• "現在" のテーブルの内容を "名前を付けた" 新しいテーブルにコピーし、先週の状態を反映します (例 : Tbl_Fact_Store_Inventory_WE_2004_12_11)。

• "名前を付けた" テーブルのインデックスを作成します (SELECT INTO を使用したため)。

• "現在" のテーブルを再初期化し、次の週の状態を反映します (SK_Date_ID、Days_In_Stock、ETL_Load_ID)。

• 実行後の処理を行います。

売上ファクト パーティションは、事前に一括で作成されるため、上記に相当するプロセスはありません。これは、増分の処理を作成する必要をなくすための処置です。売上ファクトパーティションのメンテナンスは、プロジェクト REAL の増分処理に含まれています。このため、手動のプロセスは必要ありません。増分パーティションのメンテナンスは、次のように変更されています。

1. 実行前の処理を行います。この部分は、変更ありません。

2. 最後のパーティションのデータを次のファイル グループの新しい外部テーブルにコピーします (この手順で SK_Date_ID などの列を再初期化できます)。

3. 新しい外部テーブルのインデックスを作成します (SELECT INTO を使用したため)。

4. 最後のパーティションを分割し、新しい外部テーブルをパーティションに切り替えます。

5. 最も古いパーティションを削除し、アーカイブ パーティションを安価なディスクに移動します。これは新機能です。

6. 実行後の処理を行います。

Barnes & Noble における Analysis Services 2000 キューブ パーティションのメンテナンスは、現在 ETL プロセス外部で実行されています。現在から 1 年分の売上と在庫のキューブ パーティションが作成され、現在週のデータがこれらのパーティションに読み込まれるまで処理されません。これは、キューブ パーティションのメンテナンスを簡単に ETL プロセスに統合できたので、必要なくなりました。

その他の要件

データ ライフサイクル管理のパーティションのメンテナンスに関して、その他 2 つの要件を追加しました。

1. 再起動性

   パーティションのメンテナンスの観点から、確実にこのプロセスは再起動が可能となるようにすることを目標としました。つまり、パーティションの新規作成、古いパーティションの削除、またはパーティションのアーカイブを行うコードは、既に実行されているかどうかを検証することを意味します。

2. 曜日の分離 (SSIS パッケージの実行のため) とパーティション管理プロセスの起動

パーティション メンテナンス SSIS パッケージは、どの曜日でも実行できます。ストアド プロシージャは、メタデータを照会して、パーティション メンテナンス関数のいずれかを実行する日かどうかを確認します。したがって、処理を実行する日が月曜日であった場合は、さらに関係のあるパーティションが存在するかどうかの確認が実行されます。これらのクエリは非常に短時間で実行され、日次プロセスに大きなオーバーヘッドは発生しません。これにより、日次 ETL プロセスと週次 ETL プロセスを分離する必要がなくなります。

テーブル パーティション コンポーネントを統合する

この機能を自動化する段が近づき、"スライディング ウィンドウ方式" の実装によるデータ移動操作の統合を試みました。特に将来のメンテナンスの観点から、これはすぐにかなり複雑な処理になりました。この方法では、新しいパーティション構成を作成して、新しいパーティションを追加し、古いパーティションを削除する必要があります。パーティションの追加や削除は、パーティション関数レベルで行われますが、これはすべてのパーティション構成で共有されています。このため、あるパーティションが古い構成に存在していて新しい構成に存在しないものなのか、またはその逆であるかを特定する必要があるので、コードはすぐに複雑になりました。結局、この 2 つの処理を分離することで、コードをかなり簡単にでき、ビジネス要件がこれを規定していない場合は、プロセスを物理的に分離できるようになりました。事実、スライディング ウィンドウ方式の実装も、2 つのストアド プロシージャに分離されています。このため、将来ビジネス要件を変更しやすくなっています。たとえば、新しいパーティションは後から毎週追加しても、古いパーティションは 1 年に 1 度削除するように決定した場合、より容易にこの変更を実装できます。次のストアド プロシージャは、増分パーティション メンテナンス用のロジックをカプセル化するために作成されたものです。

• up_CreateNewPartition - 論理日付 (データが適用される日付) を基に、このデータのパーティションが存在するかどうかを確認し、存在していない場合はパーティションを作成します。これが在庫パーティションの場合は、パーティションをこの前のパーティションのデータで初期化し、SK_Date_ID など関係のある列を変更します。これが売上パーティションの場合は、これ以上の処理は必要ありません。

• up_RemoveOldPartitions - 論理日付を基に、アーカイブが必要なパーティションがないかどうかを確認します。プロジェクト REAL では、単純に古いパーティションを削除していますが、テープへのアーカイブ戦略を実装することもできます。

• up_MoveAgedPartitions - 論理日付を基に、安価なディスクへの移動が必要なパーティションがないかどうかを確認します。

上記 3 つのストアド プロシージャは、親のストアド プロシージャである up_MaintainPartitionedTable から呼び出されます。パーティション メンテナンス処理全体は、全 ETL プロセスの実行中に呼び出される別個の SSIS パッケージにカプセル化されています。

関連資料

• 「Strategies for Partitioning Relational Data Warehouses in Microsoft SQL Server」(英語):
  http://www.microsoft.com/technet/prodtechnol/sql/2005/plan/spdw.mspx

• 「SQL Server 2005 のパーティション分割されたテーブルとインデックス」(作成者 : Kimberly L. Tripp) -
  http://www.microsoft.com/japan/msdn/sqlserver/sql2005/sql2k5partition.asp

• TechNet Webcast: SQL Server 2005 Series (Part 6 of 10): Managing Large Databases using Partitioning (英語) -
  http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?eventID=1032270016&Culture=en-US

まとめ

Barnes & Noble では、SQL Server 2005 のパーティション テーブルを使用することで、多くのメリットが得られました。同社では、コンパイルに時間がかかる (30 秒もかかることがある) ために、パーティション ビューは実装しませんでした。データは複数のテーブルにパーティション分割されていましたが、これらのテーブルは個別に管理されていました。ETL プロセスでは、データの読み込み時に更新対象となる適切なテーブルを決定するためのコードを作成する必要がありました。パーティション テーブルを利用することでこの必要がなくなり、その結果管理オーバーヘッドを大幅に削減できています。

詳細情報 :

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