Dave Wickert, Microsoft Corporation

SQL Server 技術資料
発行 : 2005 年 9 月
対象製品 : SQL Server 2005

要約   このホワイト ペーパーでは、プロジェクト REAL で開発された Analysis Services の設計とベスト プラクティスについて、技術的に詳しく説明します。データ ソース、データ ソース ビュー、ディメンション、階層、属性、メジャー グループ、パーティションなど、さまざまなタイプのオブジェクトについて掘り下げています。また、リレーショナル データベースのパーティション スキームに基づいてメジャー グループ パーティションの作成を自動化する、SQL Server 2005 Integration Services パッケージを作成する方法についても説明します。

トピック

プロジェクト REAL について
はじめに
Analysis Services と対話する 3 つの方法
データベース設計
データ ソースとデータ ソース ビュー
ディメンション
メジャー グループ
サーバー全体の設定
設計の選択肢
まとめ
付録 A : パーティション作成の自動化

プロジェクト REAL について

プロジェクト REAL は、実際の顧客シナリオに基づいた実装例を作成することで、Microsoft® SQL Server™ 2005 に基づくビジネス インテリジェンス (BI) アプリケーションを開発する場合のベスト プラクティスを調査するプロジェクトです。そのために、社内に顧客データを用意し、そのデータを使用して、顧客が展開するときに直面するのと同じ問題に対処しています。たとえば、以下のような問題があります。

• リレーショナル スキーマと Analysis Services 用のスキーマの設計

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

• レポート作成と対話的な分析のための、クライアント フロントエンド システムの設計と展開

• システムの、実運用に適したサイズ決定

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

実際の展開シナリオを扱うことで、ツールの使い方を完全に理解することができます。我々の目標は、大企業が実際の展開時に直面するあらゆる問題に対処することです。

このホワイト ペーパーでは、プロジェクト REAL で開発された Analysis Services の設計とベスト プラクティスについて、技術的に詳しく説明します。データ ソース、データ ソース ビュー、ディメンション、階層、属性、メジャー グループ、パーティションなどのさまざまな種類のオブジェクトについて、重要な点に注意しながら詳しく説明します。

プロジェクト REAL の概要については、ホワイト ペーパー「プロジェクト REAL : 技術概要」を参照してください。プロジェクト REAL の進行にしたがい、いくつかの文書、ツール、サンプルが作成される予定です。最新情報については、プロジェクト REAL の Web サイト (英語) を参照してください
(http://www.microsoft.com/sql/bi/ProjectReal/ にあります)。

はじめに

この文書では、プロジェクト REAL Analysis Services の技術設計について概要を説明し、この設計に影響を与えるさまざまな問題について解説します。この文書で対象としている読者は、Analysis Services の設計について理解しており、プロジェクト REAL に付属しているスキーマを考察したことがある方です。たとえば、多対多の Vendorディメンションが必要になることを既に知っていることを前提とします。ここでは、それが "なぜ" 存在するか (および、その設計に最終決定する前に我々が検討した選択肢) について主に説明します。

この文書では、マルチディメンション設計における各種の Analysis Services オブジェクトについて考察します。最初に、データ ソースやデータ ソース ビューなどの物理スキーマ オブジェクトから説明します。次に、ディメンション、ユーザー定義階層、属性階層、メジャー グループについて説明します。その後、パーティション、集計デザイン、プロアクティブ キャッシュなどのメジャー グループの特性について掘り下げます。セクションの最後では、計算、主要業績評価指標 (KPI : Key Performance Indicator)、アクション、パースペクティブ、カスタム アセンブリ、ユーザー定義関数 (UDF)、MDX スクリプトなど、それ以外の論理設計について説明します。

最後のセクションでは、Analysis Services スキーマの設計時に検討した、2 つの設計上の選択肢について詳しく説明します。目標、検討した内容、実装内容を紹介します。

この文書の最後では、我々が変更したサーバー全体の設定と、変更の理由について説明します。

プロジェクト REAL の設計は、パーティションに大きく依存しています。メジャー グループをすべて合わせると、数百ものパーティションが定義されています。付録 A では、作成したさまざまなデータベース内でこれらすべてのパーティションをいかにして作成および管理するかという管理上の問題を、どのようにして解決したかについて説明します。

Analysis Services と対話する 3 つの方法

Analysis Services と対話するには、SQL Server Management Studio、プロジェクト モードでの Business Intelligence (BI) Development Studio、直接接続モードでの BI Development Studio の 3 つの方法があります。これらがそれぞれどのようなものなのかと、これらの方法を間違って組み合わせるとどんな問題が起きるかについて説明します。

SQL Server Management Studio

SQL Server Management Studio を起動すると、Analysis Services サーバーに直接接続して、要求された操作をすぐに実行します。

たとえば、キューブやディメンションを処理する場合、システムは内部的に "Analysis Management Objects (AMO)" と呼ばれる新しいマネージド API を使用して、情報の検索や、オブジェクトの処理などの管理機能を要求します。操作のいくつかは AMO を通じて実行され、いくつかは XMLA スクリプトを使用します。どちらの場合でも、動作中の Analysis Services サーバーに対してすぐに操作が実行されます。Analysis Services は同時アクセスをサポートしているため、システムは複数のオペレータが競合する操作を行わないようにするためのロック構造体を保持しています。たとえば、これらの構造体により、別の操作がディメンションを処理しているときは、ディメンションを削除することができません。

SQL Server Management Studio は、管理ユーティリティとして設計されており、開発環境ではありません。SQL Server Management Studio を使用して、Analysis Services サーバの参照、バックアップや復元、サーバーの同期、サーバーの構成など、操作に関連するさまざまな作業を行うことができます。SQL Server Management Studio は、簡単なプロパティの変更やオブジェクトの削除など、オブジェクトの基本的な操作を行うことができますが、Analysis Services オブジェクトを設計して展開するには、他のプログラムが必要です。

プロジェクト モードの BI Development Studio ( 既定値 )

Analysis Services データベースを設計および実装する開発者やデータベース管理者 (DBA) は、BI Development Studio を使用して Analysis Services とやり取りします。BI Development Studio は、Microsoft Visual Studio® シェルを使用して構築されています。BI Development Studio での操作は、他の SQL Server ツール (Integration Services パッケージや Reporting Services レポートの作成など) や、他のプログラミング ツール (Visual Basic®. NET や Visual C#® プログラムの作成など) と同様です。開発は編集、ビルド、展開の対話型のサイクルであるというのが、Visual Studio シェルの 1 つの考え方です。

Analysis Services にとって、このことは、Analysis Services プロジェクトの作業を始めると、Analysis Services サーバーから切断されることを意味します。最初にプロジェクトをビルドするには、リレーショナル ソースからディメンション、キューブ、その他のオブジェクトを作成し、プロジェクトを Analysis Services サーバーに展開する方法と、サーバーから既存の Analysis Services データベースを取得し、そこからプロジェクトを作成する方法があります。いったんプロジェクトを作成すると、Analysis Services サーバー (およびプロジェクトが表すデータベース) から切断されます。プロジェクトは、飛行機であろうと自宅であろうと、ラップトップなどの任意の方法で、オフラインで持ち運ぶことができます。Analysis Services サーバーに再接続するのは、プロジェクトを展開するときにだけです。

プロジェクトを展開するとき、プロジェクト内のオブジェクトと Analysis Services サーバー内のオブジェクトに違いがあると、BI Development Studio によって検出されます。Analysis Services サーバーをプロジェクトに同期させるため、これらの違いが Analysis Services サーバーに適用されます。この処理では、オフラインで作成した新しいオブジェクトの作成や、オフラインで変更したプロパティの編集、Analysis Services にあってプロジェクトにないオブジェクトの削除などが行われます。

これは非常に強力な方法です。開発者はアプリケーションを開発し、コンパイルして展開します。それ以降、プロジェクトは開発者のワークステーション上にオフラインで存在します。ワークステーション上で編集や開発を行い、再コンパイルして、再度展開することができます。これは、Analysis Services プロジェクトで使用するのと同じ方法です。

では、2 人の開発者が同じアプリケーション コードに対して競合する編集を行わないようにするにはどうすればよいでしょうか。それには、ソース コントロール システムを使用します。全員が同じソース コントロール システムを使用すれば、2 人の開発者が競合する変更を行うことはできません。BI Development Studio のプロジェクト ファイルでも同じことが起こります。アプリケーションを構成する C# プログラム ソース ファイルと違い、ディメンション ファイルやキューブ ファイルなど、プロジェクトを構成するプロジェクト サブファイルにもこれが当てはまります。

直接接続モードでの BI Development Studio

オペレータが SQL Server Management Studio を使用し、開発者が BI Development Studio を使用すれば、すべてうまくいくと思うかもしれません。しかし、DBA とオペレータが作業を行うのに、BI Development Studio が必要になるケースがあります。たとえば、どのメジャーがどのキューブから得られたものか、スキーマ全体がどのようになっているかを知る必要がある場合です。そのために、BI Development Studio には直接接続モードがあります。このモードにアクセスするには、BI Development Studio を起動した後で、[ファイル] メニューの [開く] を選択し、[Analysis Services データベース] を選択します。

このモードでは、オフライン プロジェクト ファイルは使用しません。代わりに、Analysis Services サーバーに直接接続されます。パーティション、キューブ、ディメンションを作成すると、すぐにオブジェクトが作成、更新、削除されます。ライブ データベースに対して作業することになります。オフラインの編集ではないため、展開や使用しているファイルのチェックインは行いません。

どのような問題があるか

問題は明らかです。開発者は、常にプロジェクト ファイルの整合性を保証するため、ソース コントロール システムを使用します。SQL Server Management Studio または直接接続モードの BI Development Studio を使用するオペレータ (および AMO を使用するアプリケーション プログラム) は、2 人のオペレータが両立しない変更を行わないようにするために、動作中のサービスに依存します。この 2 つの仕組みは、それぞれのグループ内で機能します。しかし、他方の変更には関与しません。動作中のサービスは、ソース コントロール システムについて関知せず、ソース コントロール システムは動作中のシステムに関知しません。プロジェクト ファイルは、ライブ データベースと常に一致しているわけではありません。以下に、この点が問題となるいくつかの例を示します。

• 開発者は、既存の Analysis Services データベースをプロジェクトにインポートすることで、プロジェクトを作成します。次に、オペレータが、SQL Server Management Studio を使用して、データベース内のキューブを削除します。開発者がプロジェクトを展開する際、キューブが元どおり作成されます。これは、図 1 に示すようなメッセージで通知されます。

  

  図 1    BI Development Studio が、最後の展開以降にデータベースが変更されたことを検出
  拡大表示する
  
  

  メッセージからは、どのオブジェクトが変更されたかがわからない点に注意してください。データベースのバージョンが一致していないことだけが報告されます。差異の大小がわからないため、開発者は [はい] ボタンをクリックすることしかできません。こうしてキューブが元どおりに作成されます。

• Analysis Services プロジェクトは、ソース コントロール システムに格納されています。ユーザーが、SQL Server 2005 Integration Services パッケージ内で XMLA スクリプトを実行することで、Analysis Services サーバー上に新しいパーティションを作成します。たとえば、入力データ ファイルを処理するために、毎日実行される SQL Server エージェント ジョブがパッケージを実行することも考えられます。新しく 1 か月分のデータが現れたら、パッケージは新しい月のパーティションを作成します。次に、開発者が、ソース コントロール システム配下のプロジェクトを変更し展開します。SQL Server エージェント ジョブで作成された 1 か月分のパーティションはすべて削除されます。

• 動作中のサーバーでシステムがクラッシュし、ディスク ドライブの内容が失われました。オペレータはデータベースを 3 か月前の状態に復元し、すべてのデータベースを再処理します。開発者は、QA (ソース コントロール システムの配下にある) 内に保留中の変更を実稼働環境に展開します。これによってアプリケーションが中断します。オペレータは、復元したバックアップが壊れていたためにアプリケーションが中断したものと考え、4 か月前のバックアップからシステムを復元します。開発者は、実稼働環境を見て、変更が有効になっていないことに気付きます。そこで、開発者はプロジェクトを再度展開しますが、これによって運用が中断されます。そこで、オペレータは、5 か月前のバックアップを使用するというように、延々続きます (もうおわかりですね)。

問題への対処方法

このような出来事の組み合わせが際限なく続く可能性があります。そこでいくつかの疑問が湧きます。

1. 問題が起きたことをどのようにして検出するのでしょうか。

2. どのようにして問題に対処するのでしょうか。

通常は、問題の検出は簡単です。オブジェクトのプロパティの変更など、軽微な変更であっても、図 1 のエラーが発生する原因となります。ただし、オフライン プロジェクト ファイル中のバージョンが有効でないことが知らされるのは、開発者だけです。オペレータは、開発者が言わない限り、オンラインの変更が開発者に影響を与えていることに気付きません。開発者がオペレータに通知せず、データベースが変更されているにもかかわらずプロジェクトの展開を続けた場合、オペレータからは自分の変更内容がランダムに切り替わるように見えます。

本当に難しいのは、いかにして状況に対処するかです。残念ながら、動作中の Analysis Services データベースとオフラインの Analysis Services プロジェクトを自動的に同期させる仕組みはありません。唯一の方法は、以下のステップを実行することです。

1. Analysis Services データベースを新しいプロジェクトに再インポートします。

2. インポート機能を使用してすべてのファイルを作成し直します。

3. ソース コントロール システムを使用して、プロジェクト (およびそのサブファイル) をチェックアウトします。

4. 新しいファイルでチェックアウトしたファイルを上書きします。

5. 新しいバージョンをソース コントロール システムにチェックインします。すべてが成功すれば、ソース コントロール システムにはすべてのファイルの新しいバージョンが格納されています。

6. 新しいプロジェクトを展開して、データベースのバージョン番号を一致させます。

もう 1 つのアプローチは、新しくインポートしたプロジェクト ファイルと、ソース コントロール システムにチェックインされている現在のファイルを比較することです。違いがわかったら、オブジェクトとプロパティを 1 つずつ変更して、ファイルを一致させます。結果的には、Analysis Services データベースとソース コントロール システムを手動で同期していることになります。このアプローチでは誤りが発生しやすく、すべての差異が見つかって正しく同期されるという保証がありません。

一致を手動で行う場合でも、ファイル全体を置き換える場合でも、一致作業の間はオペレータがオンライン システムに変更を加えることはできません。一致作業中に行った変更は、最終的な展開の際に上書きされます。すべての開発者は、ソース コントロール システムから最新版を取得するか、プロジェクト全体をチェックアウトして、ファイルのローカル コピーを更新する必要があります。

言うまでもなく、この方法には不確定要素が多数あり、容易に問題が発生します。このセクションで説明した操作を組み合わせる場合は、開発者とオペレータが連絡し合う方法についての詳細なガイドラインを作成し、必ずそれに準拠する必要があります。

データベース設計

プロジェクト REAL のデータ ウェアハウスは、以下の名前の一連のマルチディメンション Analysis Services データベース内に実装されています。

• REAL_Warehouse_V<n> (ここで <n> は、データにデータマスクを適用したものです) は、数テラバイト (TB) ある、データベースの完全なバージョンです。

• REAL_Warehouse_Development_V<n> は、開発版のデータベースです。ディメンション メンバがすべて格納されていますが、5 つのストア内に 15 個のパーティション (2004 年 11 月 27 日から 2005 年 3 月 3 日まで) があります。これは、プロジェクト REAL の開発チーム内で、構築とテストが容易なデータセット (15 GB 以下) として使用されています。サイズが小さいため、地理的に離れた開発チーム間で容易に共有することができます。

• REAL_Warehouse_Sample_V<n> は、サンプル版のデータベースです。このデータベースには、ディメンション構造のサブセットと、限られた数のファクトが格納されています。しかし、システム自体の優れたデモのためにすべてのデータが存在するよう、より長い期間 (52 週間分のデータ) をカバーしています。これは、プロジェクト REAL システムの一部として、外部に配布することを目的としています。つまり、システムを積極的に使用するために十分なデータがあります。また、プロジェクト REAL のさまざまなデモの基礎となります。

すべてのデータベースには同じ構造で同じオブジェクトが格納されています。各データベースには、1 個のキューブ、11 個のディメンション、4 個のメジャー グループがあります。

データ ソースとデータ ソース ビュー

データ ソースは、Analysis Services 内でのモデル化作業の開始点です。プロジェクト REAL では、3 つの SQL Server 2005 リレーショナル データベースを単一の RDBMS に統合し、システムで使用するキューブと構造のデータ ソースとして使用しています。

データ ソース

すべてのデータに対して単一のデータ ソースを使用する方法は、必ずしも一般的な構成ではありません。我々は、Analysis Services とソースの RDBMS が異なるドメインにある場合に、その間でどのようにアクセスするのが良いかをテストしたいという要望がありました。この場合、サーバーの間にファイアウォールがあることも考えられます。そのため、RDBMS にアクセスするために、SQL Server の統合セキュリティではなく、標準セキュリティを使用することを前もって決めていました。

我々は、Analysis Services からアクセスするための、専用の SQL ログインを作成しました。このアカウントの名前は REAL_User で、パスワードは password です。これは高度なセキュリティ環境ではありませんが、我々の目的にとっては十分に複雑なものでした。SQL Server データベースでは、このログインに "データの読み取り" 権限だけを与えました。

このデータ ソースを作成して使用する際に、SQL Server 2005 Analysis Services の興味深い動作に気付きました。データ ソースのパスワードはどちらも内部的に暗号化され (つまり、クリア テキストでは格納されない)、データ ソースを作成するかスクリプトを作成すると削除されることがわかりました。そのため、データベースのコピーを作成 (SQL Management Studio の [タスク] メニューで、[スクリプトの生成] の順に選択します) すると、XMLA スクリプト内のそのデータ ソースのパスワードに、必ず空白が設定されました。実際、スクリプトを使用したり、コピーと貼り付けを行うなどの方法でパスワードを別のオブジェクトにコピーする方法が見つかりませんでした。どうやってコピーを作成しても、パスワードがないか、データ ソースに対するパスワードが間違っている旨が出力されました。

ベスト プラクティス : 標準セキュリティで作業する場合は、パスワードを覚えておいてください。オブジェクトを移動させる際に、何度もパスワードを入力する必要があります。

パスワードの削除には、利点と欠点があります。利点としては、パスワードが盗まれにくくなることです。管理者ですらオブジェクトをコピーして使い続けることはできません。管理者は、コピーを最初に使用する際に、パスワードを再入力する必要があります。最初の使用以降は、データ ソース自体にパスワードを格納 (暗号化) し、データ ソースを開くたびに再入力が必要なことを指定するオプションがデータ ソースにあります。OLAP 管理者としてアクセスできる悪意を持ったユーザーも、オブジェクトをコピーして別のコンテキストで使用したり、データ ソースを調べてパスワードを割り出すことができません。

残念なことに、これによって、ソース コントロール システムに格納されているスクリプトから、毎晩 (または毎週) システムを自動的にビルドするのが難しくなります。これは、ソース コントロール システムに格納されているオブジェクトには、有効なパスワードが格納されていないためです。開発チームがソースからの自動再ビルトを行っている場合 (たとえば、Microsoft ではこれを行っています)、そのデータ ソースからビルドされるオブジェクトは、適切なパスワードを設定するまで Analysis Services を使用して処理することができません。ソース コントロール システムからすべての Analysis Services オブジェクトを取り出して再作成したら、ディメンション、属性、パーティション、その他のオブジェクトの処理を自動化する前に、データ ソースの有効なパスワードを設定する必要があります。通常、パスワードはクリア テキストで設定されるため、これはセキュリティ リスクを伴います。そのため、パスワードの設定方法とこれらのプロシージャを読み取る必要があるユーザーのアクセスを制御する方法に注意してください。

データ ソース ビュー

我々は、システムで使用するデータ ソース ビューを 1 つ作成しました。データ ソース ビューは、データ ソースによって公開されるオブジェクト (リレーショナル テーブルとビュー) を、Analysis Services のオブジェクトを作成するための元となるオブジェクトのコレクションに拡張するために使用される、抽象化レイヤです。一般的なリレーショナル データベースのベスト プラクティスを踏まえて、我々は生のテーブル自体からはオブジェクトを作成せず、常にリレーショナル データベース側のビューを使用しました。

データ ソース ビューの中に、ディメンション、階層、属性を作成するために使用するすべてのリレーショナル ビューを含めました。パーティションを作成するために使用するリレーショナル ビューは含めませんでした。また、各メジャー グループの "テンプレート テーブル" として 1 つのビューを含めました。これは、各メジャー グループ パーティションの基礎となります。テンプレートにはデータは含まれていません。テンプレートは、各パーティション テーブル、ビュー、クエリ バインド、その他のオブジェクトが何を返すかを定義しただけのものです。

ベスト プラクティス

この作業から、いくつかのベスト プラクティスが生まれました。

• できるだけ、 Analysis Services にとって質の高いスター スキーマまたはスノーフレーク スキーマとなるビューを使用し続けます。

  データ ソース ビューでは、DBA が複雑な構造を作成できますが、データにビジネスの価値を付加するのに必ずしも適切な場所ではありません。データ ソース ビューは、データベース、サーバー、アプリケーション間で共有できません。また、Analysis Services、Integration Services、Reporting Service などの、SQL Server 内の他の BI コンポーネント間でも共有できません。ビューを通じてビジネスの知識を追加するよりも、その知識をすべてのアプリケーションで再利用する方が有効です。

  データ ソース ビューを使用したシステムの拡張は、2 つの場合に優れた判断であることがわかりました。1 つ目は、RDBMS において設計者がビューを作成または変更できない場合です。2 つ目は、ビジネスの知識が、キューブのマルチディメンションの性質だけに関係しており、リレーショナル オブジェクトに関係しておらず、再利用が重要でない場合です (この文書で後述する、名前付きクエリ Months Since Opened の説明を参照してください)。

• 基となるデータ ソースに存在 しない外部キー (FK) のリレーションシップを作成するために、データ ソース ビューを使用します。

  FK のリレーションシップがデータ ソースに存在しない理由はさまざまです。たとえば、データ ウェアハウスから取得したデータの場合、参照整合性制約がデータ モデル内に存在する場合でも、データ ウェアハウスでその制約が緩和されていることも珍しくありません。これは、大量のデータを高速に処理するためや、整理フェーズの実行時に内容に疑問があるデータを含めるために行われます。同様に、データ ソースが実稼働データベースである場合は、基となるリレーショナル データベースが参照整合性制約をサポートしていないために、この制約が宣言されていないことがあります。たとえば、テーブルが異なるデータベースや異なるサーバーに存在している場合があります。SQL では、データベースやサーバーにまたがった参照整合性制約は使用できません。しかし、外部キーのリレーションシップは、Analysis Services が、処理時 (または、ストレージが ROLAP の場合はクエリ実行時) にデータ ソースからデータを収集する SQL ステートメントを作成しているときに非常に便利です。幸運にも、データ ソース自体で何が定義されているかにかかわらず、Analysis Services に対して FK のリレーションシップを追加するためにデータ ソース ビューを使用できます。

• データ ソース ビューに夢中になり、多数のリレーションシップを作成しがちです。

  データが既にスター スキーマに格納されている場合は、外部キーのリレーションシップが不要であることを理解することが重要です。一般に、リレーションシップは、Analysis Services に対して、処理のための SQL ステートメントを作成する方法を知らせるために使用します。オブジェクトやクエリを検索または操作するためには、リレーションシップは必要ありません。プロジェクト REAL データベースでは、データ ソース ビューのリレーションシップを定義していませんが、システムは順調に構築されました。

データ ソース ビューを使用したメタデータの拡張

データ ソース ビューを使用したメタデータの拡張は、簡単で明解です。プロジェクト REAL では、拡張メタデータをさまざまな方法で使用しました。

• 我々は、拡張メタデータを使用して、分離グループ化で使用するデータを変換する計算列を作成しました。たとえば、Item ディメンションには、Publish Year という属性があります。しかし、年自体には、分析機能がほとんどありません。それでは、1934 年に発行された本と 1992 年に発行された本の違いは何でしょうか。分析用として優れている属性は、発行からの年数です。そのため、Item テーブル内に、データ ソース ビューを使用して次の計算列を作成しました。

  ISNULL(DATEDIFF(yyyy,Publish_Year,GETDATE()),0)
  

  このステートメントは、標準の SQL 関数を使用して、発行された年を、発行からの年数を表す変数に変換します。

  Analysis Services は、発行からの年数など、値が連続した変数の自動的な分離をサポートしています。Analysis Services は、データ マイニング アルゴリズムを使用して、値を統計的に重要なグループに集めます。今回の場合は、最終的に以下の 10 個のグループになりました。

  -3 ～ 1 年 (Item テーブルには、3 年後に発行が予定された本が含まれているため、-3 は有効な値です)
  2 年
  3 年
  4 年
  5 年
  6 ～ 7 年
  8 ～ 9 年
  10 ～ 13 年
  14 ～ 103 年

  我々は、以下の属性に対してこの統計的アプローチを使用しました。

  ディメンション	属性
  項目	Years Since Published グループ
  	Retail Price グループ
  	Total Num of Pages グループ
  Store	Linear Ft グループ (書棚の面積)
  	Square Ft グループ (店舗の面積)

• しきい値が "わかっている" 分離グループの場合 (統計的でない場合) は、若干異なるアプローチを採用しました。このようなグループについては、計算列を作成し、Transact-SQL の CASE ステートメント内にしきい値をハード コーディングしました。たとえば、データ ソース ビュー Store 内の Age Of Store という計算列を以下に示します。この計算列では、CASE ステートメントを使用してソース属性 (open_date) を "グループ分け" しています。

  CASE
    WHEN DATEDIFF(mm,open_date,GETDATE()) BETWEEN 0 AND 12 
  THEN 'Less than a year old'
    WHEN DATEDIFF(mm,open_date,GETDATE()) BETWEEN 13 AND 24 
  THEN '1 - 2 years old'
    WHEN DATEDIFF(mm,open_date,GETDATE()) BETWEEN 25 AND 60 
  THEN '2 - 5 years old'
    WHEN DATEDIFF(mm,open_date,GETDATE()) BETWEEN 61 AND 120 
  THEN '5 - 10 years old'
    WHEN DATEDIFF(mm,open_date,GETDATE()) > 121 
  THEN 'Older than 10 years'
    ELSE 'Unknown'
  END
  

  統計的な手法では、ハード コーディングが不要ですが、グループ化を計算する方法をビジネス ユーザーが特定することができません。ここで、ビジネスの知識をデータ ソース ビュー内に直接埋め込むことができます。

• Age Of Store を正しく並べ替えるために、我々は計算列 Months Since Opened を作成し、次のように定義しました。

  ISNULL(DATEDIFF(mm,open_date,GETDATE()),0)
  

  次に、両方のフィールドを属性としてディメンション Store に追加しました。Months Since Opened と Age of Store の間のリレーションシップを作成し、Months Since Opened の Order By プロパティに Key を設定しました。Age of Store の Order By プロパティには Attribute Key を設定しました。この構成により、Age of Store が適切に並べ替えられるようになり (Months Since Opened によって)、両方の属性がクエリに含まれるようになります。

データ ソース ビューからのオブジェクトの省略

データ ソース ビューは強力な抽象化方法ですが、データ ソース ビューにオブジェクトを含めない方がよいこともあります。たとえば、次のオブジェクトはデータ ソース ビューに含めないことをお勧めします。

• 必要のないテーブルとビュー (特にパーティション テーブル)。テーブルやビューは、ディメンション内で使用しない場合や、メジャー グループのテンプレート テーブルとして使用しない場合は、含めないようにします。含めると、データ ソース ビューが雑然となり、内容の理解が困難になります。

• システムによって使用されないビジネス オブジェクト テーブル。たとえば、Personnel データ ソースが、使用できるようにデータ ソース ビューに含まれている場合は、Employee テーブルを追加し、それ以外のものは追加しません。Personnel アプリケーションで使用する他の参照テーブルやエンティティ テーブルは含めないようにします。

• 他のアプリケーションが使用するコントロール テーブル。たとえば、Personnel データ ソースに、バッチや ETL 処理の順序を制御し、Personnel アプリケーションのストリームを更新するために使用するテーブルがあるかもしれませんが、これらのテーブルは含めないでください。

データ ソース ビューの変更

いつかの時点で、データ ソース ビューを変更したい場合が出てきます。たとえば、あるデータ ソース テーブルのフィールドを integer から varchar に変更したり、varchar のサイズを 20 文字から 50 文字に変更することが考えられます。また、ディメンションの定義を変更した場合は、キューブに伝播させる必要があります。

ディメンションなどのオブジェクトをデータ ソース ビューから構築し、ディメンションをキューブに追加した後、パーティションをキューブに追加すると、既存のメタデータが依存関係オブジェクト ツリーを上に向けて伝播します。下位のオブジェクトを変更しても、必ずしも上位のオブジェクトに変更が自動的に適用されるわけではありません。たとえば、RDBMS のディメンション テーブル内のフィールドのデータ タイプを変更したとします。この場合、データ ソース ビューは自動的に変更されません。変更を有効にするには、データ ソース ビューを更新する必要があります。

同様に、データ ソース ビューを変更しても、その上に構築されているオブジェクトは自動的に変更されません。ディメンションまたはキューブの属性がフィールドの上に構築されている場合は、データ ソース ビューからオブジェクトを作成し直す必要があります。実際には、変更を行うときは、上位のオブジェクトを手動で更新する必要があると思ってください。システムの上位に変更が適用されるかどうかは、変更内容に依存します。以下にガイドラインを示します。

• データベースのフィールドを varchar(20) から varchar(50) に変更するなど、単にデータ型のサイズが変更になった場合には、データ ソース ビューの背景を右クリックし、[最新の情報に更新] を選択します。これにより、新しい定義でデータ ソース ビューが更新されます。まだ古い値の 20 が埋め込まれているオブジェクトがあるかもしれません。メジャー グループ内のメジャーとして使用されているフィールドの場合は、キューブを編集し、メジャーのデータ型とサイズを確認します。属性として使用されているフィールドについては、ディメンションを編集し、属性値を変更します。

• フィールドを追加した場合は、データ ソース ビューで [最新の情報に更新] をクリックします。これによってフィールドが利用可能になり、そのフィールドから Analysis Services オブジェクト (すなわち、ディメンションの属性、メジャー グループのメジャー) が作成可能となります。

• フィールドを削除した場合、データ ソース ビューを更新したときに何が起きるかは、Analysis Services オブジェクトを作成するためにそのフィールドを使用したかどうかに依存します。そのフィールドを使用して Analysis Services オブジェクトを作成していない場合は、データ ソース ビューで [最新の情報に更新] をクリックすることでフィールドが削除されます。そのフィールドを使用して Analysis Services オブジェクトを作成した場合は、データ ソース ビューで [最新の情報に更新] をクリックすると、削除されるオブジェクトが表示されます (これらのオブジェクトがこのフィールドを基にしているためです)。

• テーブルを追加または削除した場合は、データ ソース ビューで右クリックし、[テーブルの追加と削除] を選択します。Analysis Services が使用しているテーブルの削除は、最も破壊的な変更です。テーブルを削除する前に、まずキューブ内の上位オブジェクトを確認してください。テーブルがメジャー グループで使用されている場合は、メジャー グループを削除します。このテーブルがディメンション テーブルである場合は、まずそのディメンションをすべてのキューブから削除し、その後ディメンションを削除します。

  少しずつ変更を行うことで、削除操作の影響を慎重に評価することができます。単にデータ ソース ビューからテーブルを削除すると、削除される上位オブジェクトが表示されます (そのテーブルが多数の箇所で使用されている場合は、大量に表示されます)。[OK] をクリックすると、テーブルと表示されているオブジェクトが削除されます。この方法では、一度で簡単に操作できますが、広範な変更の影響を評価することができないため、ゆっくりと段階的に作業することをお勧めします。

• 物理構造ではなく論理構造を変更する場合もあります。たとえば、属性のキー構造を変更することがあります。この場合、物理的なデータ ソース ビューは変更されませんが、この新しい論理構造を使用しているすべてのものを変更する必要があります。それには、作成し直すか、構造を更新します。

  たとえば、たまたまキューブの粒度属性でディメンションを使用している場合は、キー構造がキューブ内に埋め込まれています。構造をリセットするには、キューブの粒度属性を他の属性に変更した後、元の属性に戻します。これにより構造が再構築され、新しいキー定義が使用されます。我々は、プロジェクト REAL の時間ディメンションでキー コレクションを調整する必要があったときにこの状況に遭遇しました。

  属性キーを一意にする場合には、単一のフィールドを使用するよりも、キー コレクションを使用する方が一般的です。プロジェクト REAL のスキーマの初期のバージョンでは、より単純な時間ディメンションを使用しており、Month 属性のキー (1 ～ 12) が一意でありませんでした。Month 属性のキーを一意にするために、Month キーに Year フィールドを追加しました。その際、キューブを展開できなくなっていることに気付きました。ディメンションは正常に構築され、適切な構造で参照できましたが、キューブを展開しようとするとエラーが発生しました。これは、キューブの時間ディメンションに、Month 属性のキー用に構成された 1 つのフィールドがあり、一方ではディメンション内の新しいキーに 2 つのフィールドがあったためです。このエラーを修正するために、キューブのディメンションの Month 属性を同期させました。

  同期させるために、キューブの粒度属性を Time.Day から Time.Week に変更しました。次に、キューブを保存し、再度 Time.Day を使用するようにリセットしました。埋め込まれているディメンションをリセットすることでキー構造もリセットされ、Month 属性が 2 つのフィールドを持つようになりました。これで問題が修正されました。

まとめ

要約すると、データ ソース ビューには、Analysis Services において 2 つの重要な役割があります。

1 つ目は、データ ソース ビューは Analysis Services が使用するオブジェクトとデータ ソースの間の抽象化レイヤであるということです。これにより、名前付きクエリや計算列などのオブジェクトを、データ ソース自身の中に作成することもできます (たとえば、リレーショナル ビューなど)。Analysis Services の管理者が、ソース システムでメタデータを変更するために必要な権限を持っていないことも考えられるため、この点は重要です。たとえば、ソース システムが、個人名簿マスタを保持している、企業の SAP システムであることが考えられます。企業のリソースであることから、Analysis Services サーバーを管理している DBA には、SAP システムのビューを追加、削除、変更する権限がないこともあります。DBA は、データ ソース ビューを変更することで、ソース システム自体を変更せずに、抽象化レイヤの恩恵を受けることができます。

データ ソース ビューを使用することで、物理的にデータベースに格納されていないテーブルとビューや、データベースにまたがっているテーブルとビューの間にリレーションシップを作成することができます。たとえば、異なる 2 つのテーブルに格納されているテーブルの間で外部キー制約を作成することはできませんが、これらの関係は、3NF データベースや OLTP データベースから構成されるディメンションでは重要です。

データ ソース ビューだけに頼らないで下さい。データ ソース ビューは万能薬ではなく、1 つのツールです。優れたマルチディメンション設計を開発すると、データ ソース ビューがそれほど万能でないことがわかるでしょう。

ディメンション

プロジェクト REAL では、豊富な機能を持った複雑なディメンションが多数あります。これが、我々がこのアプリケーションに引き寄せられた 1 つの理由です。ベンダが演じる役割に基づいて、多数の分析を行うことが可能です。手元にある数量や、その他の種類の在庫データなど、多数の準加法メジャーがあります。このマルチディメンション設計は、小売データベースを開発または保守したことがある人なら、非常に見慣れたものです。

ディメンション キー

ディメンションには、一般にファクト テーブル内のメジャーをフィルタ処理または参照するために使用するデータが含まれています。キューブ内のディメンションは、リレーショナル ディメンション テーブルにマッピングされます。

論理スキーマには、代理キーとビジネス キーの 2 種類のキーがあります。"代理キー" は、テーブル間の内部的な関係で使用します。"ビジネス キー" ("自然キー" とも呼ばれます) は、ビジネス自体のエンティティに対する識別子として機能します。そのため、たとえばベンダが内部的に代理キーによって指定される場合 (1 ～ n など、識別プロパティを持つ整数)、外部の世界にとっては、その自然キー属性がベンダ番号になります (たとえばベンダ番号 7233703)。ファクト テーブルでは、レコードが Analysis Services に公開されたときに変換済みとなっているように、ETL プロセスがビジネス キーを代理キーにマッピングします。

我々のケースでは、"タイプ 2 の緩やかに変化するディメンション (SCD : Slowly Changing Dimension)" を追跡するために、代理キーを使用しています。すべてのディメンション キー属性が代理キー フィールドを使用していることに気付くでしょう。複数のタイプ 2 変化レコードが同じビジネス キーを持つため、自然なビジネス キーをこの目的で使用することはできません。さまざまなタイプと SCD の使用方法はこのホワイト ペーパーの範囲を超えていますが、データ モデル自体は、タイプ 1 とタイプ 2 の SCD を、挿入のために推定メンバと共に使用しています。これにより、ETL プロセスが複雑になり、非常に興味深いものになっています。SCD の詳細については、ホワイト ペーパー「プロジェクト REAL: ビジネス インテリジェンス ETL のデザイン方法」を参照してください。また、Ralph Kimball 氏の著書『The Data Warehouse Toolkit』、Wiley、第 2 版 (http://search.barnesandnoble.com/ booksearch/isbnInquiry.asp?isbn=0471200247) (英語) も一読されることをお勧めします。

階層

ディメンションは、レポート作成時にメジャーに対するコンテキストと意味をもたらしますが、エンド ユーザーが使用する実際の分析オブジェクトは階層です。SQL Server 2005 Analysis Services には、"属性階層" と "ユーザー定義階層" の 2 種類の階層があります。

階層は "レベル" を集めたものです。階層の中のドリルダウンは、レベルと呼ばれます。たとえば、"時間" 階層には、年、四半期、月、週、日のレベルがあります。"本" 階層には、(プロジェクト REAL では Item と呼びます) には、Type、Subject、Category、SubCategory、Item のレベルがあります。

ユーザー定義階層には 2 種類あります (どちらも同じようにして構築され、まったく同じに見えます)。

レポート作成階層

ユーザー定義階層の最初の種類は、"レポート作成階層" です。この階層の目的は、レポート作成だけです。元になるデータは、さまざまなレベル間の実際のリレーションシップをサポートしていません。たとえば、"All -> Author -> Item" と設定されたレポート作成階層があるとします。これがレポート作成目的専用であるのは、商品 (item) が本を特定し、その逆は成り立たないためです。階層内で商品の上に著者 (author) を配置すると、多対多の関係ができます。

自然階層

もう 1 種類の階層は、"自然階層" です。これは、"強い階層" と呼ばれることもあります。自然階層では、各レベルのデータには、互いに多対 1 の関係があります。日は週にロール アップし、週は月にロール アップし、月は四半期にロール アップし、四半期は年にロール アップします。これによってシステムは事前にロール アップを計算 (集計) することができます。たとえば都市ごとに店舗の売り上げの小計を計算し、州ごとに各都市の小計を計算し、地域ごとに各州の小計を計算し、最後に国ごとに各地域の小計を計算するというように、階層の上に向けて小計を計算します。自然階層の各メンバは親を必ず 1 つだけ持つため、任意の箇所で切り離しや分割を行うことができます。

自然ユーザー定義階層は、SQL Server 2005 の新機能ではありません。Analysis Services の以前のバージョンにも存在しています。新機能は、レポート作成階層と、属性階層を使用してレポート作成階層を構築する方法です。

属性階層

"属性階層" は、SQL Server 2005 での新しい種類の階層です。これは、ディメンション テーブル内の属性やフィールドに基づいています。実際のところ、データベース内のどのフィールドもディメンションの属性とすることができます。ディメンションの属性を定義すると、その属性階層が作成されます。たとえば、時間ディメンション テーブルに、その日が休日かどうかを表す、0 か 1 の値を取る Holiday フィールドがある場合、そのフィールド上に Holiday 属性階層を作成することができます。属性階層はフラットです。階層は、ALL レベル (属性の合計) と、属性レベル自身の 2 つのレベルだけを含む場合に "フラット" であると言います。Holiday は、ユーザー定義階層での使用から独立したスタンドアロン階層として存在するため、エンド ユーザーは Holiday (休日) ごとに売り上げを分析することができます。SQL Server 2000 Analysis Services には、"仮想ディメンション" と呼ばれる同様の概念があります。仮想ディメンションを使用すると、メンバ プロパティからディメンションを作成できますが、仮想ディメンションは範囲と柔軟性の点で制限されています。

階層ベースのシステムと属性ベースのシステム

これは、SQL Server の 2 つのリリース間での重要かつ根本的な違いです。

SQL Server 2000 Analysis Services は "階層" ベースのシステムです。内部の構造は、階層とレベルを中心に作成されています。これには、レベルの組み合わせである集計 (各ディメンションに 1 つ) と、メンバ プロパティをディメンションに格上げする仮想ディメンションが含まれます。1 つのディメンションは、階層を 1 つだけ持つことができます。複数の階層は命名規則で実現します。2 つの時間階層 Time.Calendar と Time.Fiscal は、Time に関係しているように見えますが、内部的にはたまたま名前に Time が含まれている 2 つのディメンションに過ぎません。Product というディメンションも、Product という階層です。ディメンションと階層に実際の区別はありません。

SQL Server 2005 Analysis Services は "属性" ベースのシステムです。属性はオブジェクトの基本的な要素です。集計は、属性の小計を組み合わせたものです。既定では、属性の属性階層が自動的に作成されます。ディメンションには、複数の階層があることもあります。Time.Calendar と Time.Fiscal は、1 つの時間ディメンションの 2 つの階層 (Calendar と Fiscal) です。ユーザー定義階層 (Calendar および Fiscal) は、純粋にナビゲーション用のエンティティです。これは、属性の組織化を支援するためだけにあります。すぐにわかりますが、ディメンションについてこのセクションで説明する上で、このが概念が何度も出てきます。

ディメンションで使用する論理構造についてはこれくらいにしておきます。次に、ディメンションの物理的な特性、ここではそのメモリの使用方法について見ていきます。

Analysis Services 2005 のディメンション キャッシュ

SQL Server 2000 と Analysis Services 2005 では、ディメンション メンバの扱いが異なります。SQL Server 2000 では、すべてのデータベースのすべてのディメンション メンバをサーバーのアドレス空間に起動時に読み込む必要がありました。プロセス バッファやデータ キャッシュなどのその他の用途のメモリは、ディメンション メモリの先にありました。これは制限が厳しすぎました。32 ビット プロセス (Analysis Services は AWE 対応でないため、プロセスの仮想アドレス空間が 3 GB に制限されます) では、ディメンション メンバの合計最大数は、一般に 2 ～ 3 百万メンバでした。メンバ プロパティの数を減らし、名前とキーを短くすれば、おそらく 3 ～ 4 百万メンバまで使用できました。それ以上となると、より大きな仮想アドレス空間が利用できる 64 ビット サーバーを使用する必要がありました。プロジェクト REAL で使用していたデータベースも、以前は 64 ビット サーバーに格納していました。これは、Item ディメンションが、7 百万個弱のメンバで構成されていたためです。Customer ディメンションは、5 百万個弱のメンバで構成されていました。これは、32 ビット ハードウェア上の SQL Server 2000 Analysis Services で可能な範囲を大きく超えています。

SQL Server 2005 では、Analysis Services は、そのメンバを静的にメモリにマップして保持するのではなく、動的なディメンション キャッシュを使用します。メンバは、必要になったときにメモリに読み込まれます。その後、他のディメンション メンバが必要になると解放されます。これにより、プロジェクト REAL システム (実際には、システム全体の多数のバージョン) を、32 ビット ハードウェアと 64 ビット ハードウェア上に正常に構築できるようになりました。これは、32 ビット ハードウェアで動作する大規模なシステムにとって良い知らせです。

32 ビット ハードウェアを使用して構築できないディメンションもわかりましたが、そのしきい値は SQL Server 2000 よりもはるかに高いところにあります。決め手となる特性は、ディメンションのキー属性の属性階層を処理するために構築されるハッシュ テーブルです。属性が増えると (または属性のサイズが大きすぎると)、最終的に利用可能なメモリを超え、ディメンションが処理できなくなります。

大きすぎてメモリに格納できない、構築可能なしきい値を超えるディメンションについては、処理に必要なメモリを削減する方法が 2 つあります。

まず、すべての属性が本当に分析に必要かどうかを確認します。必要ないものがあれば、ディメンションから削除します。これで、ハッシュ テーブルに必要なスペースが小さくなります。

次に、カスケードした属性のリレーションシップを使用し、キーに直接依存する属性の数を減らします。どの属性も、ディメンションのキーに直接または間接的に関連付けられている必要があります。ディメンションを最初に作成する際、すべての属性がキーに直接関連付けられていることに気付きます。これはキー属性の特性によるものであり、すべての属性がキーに関連付けられます。しかし、ディメンションの自然ユーザー定義階層を定義すると、いくつかの属性にはそれ以外のリレーションシップができます。属性は、キーに直接関連付けられると共に、自然階層を通じてキー属性に間接的にも関連付けられます。たとえば、時間ディメンションの Day (日) がわかれば、Year (年) もわかります (年は、Day に直接包含されています)。また、Day は Week (週) を包含しており、Week はMonth (月) を、Month は Quarter (四半期) を、Quarter は Year を包含しています。このように、この自然ユーザー定義階層を定義することで、Week、Month、Quarter、Year から Day への直接の関連付けを削除することができます。間接的な関係が存在する場合は、直接のリレーションシップを削除できます。

依存する属性は、キーに直接関連付けられないように、削除することができます。これは、これらの属性が、自然階層による属性のリレーションシップを通じてキーに関連付けられているためです。したがって、ディメンションのキー属性から削除することができます。これを示す例として、図 2 の時間ディメンションを参照してください。

暦属性に対する属性リレーションシップの定義方法と、会計属性に対する属性リレーションシップの定義方法を比較してください。暦属性のほとんどはキー属性 (Day) に直接関連付けられておらず、週だけが直接関連付けられています。代わりに、暦属性のほとんどは、暦の自然属性にカスケードされています。会計属性のリレーションシップもありますが、属性はキー属性に直接関連付けられています。

暦属性で使用したアプローチは、推奨されるアプローチです。自然ユーザー定義階層や、カスケード効果があるその他の属性リレーションシップを定義した場合は、直接のリレーションシップを削除する必要があります。これには 2 つの理由があります。1 つ目の理由は、メモリの消費量が少ないことです。2 つ目の理由は、集計のデザイン ウィザードを実行すると、データベースに対してどの集計を設計するかを判断するのに、間接的なリレーションシップが使用されることです。冗長な直接のリレーションシップがあると、最適とは言えない (遅い) 集計デザインになります。

属性のリレーションシップ

図 2 で、属性間に定義されているリレーションシップに注意してください。日と週、週と月、月と四半期、四半期と年の間の属性のリレーションシップに注目してください。このようなリレーションシップを定義することにどのような意味があるのでしょうか。面白いことに、これは多対 1 の自然階層があることを意味します。週がわかれば、それがロール アップされる月が 1 つだけわかります。月がわかれば、それがロール アップされる四半期が 1 つだけわかります。四半期がわかれば、それがロール アップされる唯一の月がわかります。システムは、ロール アップが階層の上に向けて行われるように、計算を最適化することができます。

ベスト プラクティス : じっくりとディメンションを設計し、属性のリレーションシップをディメンションに取り込むようにしてください。

SQL Server 2000 Analysis Services (自然階層だけをサポートしています) は階層ベースであることから、集計は階層を中心にして設計されます。SQL Server 2005 Analysis Services では、集計は属性の組み合わせです。ユーザー定義階層は使用しません。ストレージ デザイン ウィザードは、属性のリレーションシップを使用して、属性のロール アップの組み合わせが有効な場合を判断します (そして、これらの属性に対する集計が設計されます)。リレーションシップがないと、ある属性と他の属性の重要度が同じになるため、ストレージ デザイン ウィザードは単に属性を無視し、ディメンションの ALL レベルを使用します。そのため、有効な集計を設計したければ、属性のリレーションシップを定義する必要があります。属性のリレーションシップがなくても、システムは正しい数値を返しますが、値を実行時に計算する必要があり、集計が役立ちません。

属性のリレーションシップは、複雑な MDX 式を計算するときに、数式エンジンでも使用されます。属性のリレーションシップがないと、空でないクロス ジョインなどの多くの操作が最適化されず、効果的に処理されません。そのため、数式エンジンの実行時の計算を効果的に行いたければ、属性のリレーションシップを定義する必要があります。

最後になりますが、属性のリレーションシップは時間ディメンションでは不可欠です。時間に関係する MDX 関数の多くは、属性のリレーションシップと属性のタイプが正しく設定されている場合にだけ有効な値を返します。通常、BI タイム インテリジェンス ウィザードを使用すれば、適切な構造とリレーションシップを設定することができます。しかし、時間ディメンションを手動で定義し、MDX 時間関数を使用する場合、正しい結果を得たければ、時間ディメンション内で属性のリレーションシップを定義する必要があります。

属性のリレーションシップには 2 種類あります。リレーションシップの種類の 1 つ目は、これまで説明してきたように、ロールアップが有効な場所、つまりいつ集計を設計するかを表すためにシステムによって使用されます。SQL Server 2000 Analysis Services に詳しい読者は、他にもリレーションシップの種類があることに気付くと思います。これは、以前からあるメンバ プロパティです。

たとえば、プロジェクト REAL の論理スキーマには、地域担当マネージャとその電話番号を示す属性があります。これらの属性は、ディメンション テーブル Store 中に非正規化されます。これらの属性は、地域、地区、店舗などの他の属性とは異なり、表示目的で使用され、分析では使用されません。分析は地域に対して行われ、マネージャの電話番号に対して行われるわけではありません。マネージャの名前と電話番号は、分析では一般に使用しませんが、地域に関連付けられます。マネージャの名前か電話番号がわかれば、マネージャがいる地域が自動的にわかります。そのため、これら 2 つの属性の間のリレーションシップを表す必要があります。このようなリレーションシップは分析には有効でありませんが、エンドユーザーのクライアント ツールでは有効です。メンバ プロパティはリレーションシップとして表されるため、エンド ユーザーがメンバを右クリックしたときに、メンバ プロパティの一覧をクライアント ツールが表示できるように、リレーションシップが存在する必要があります。

まとめると、効率よく動作するシステムを設計および実装するためには、属性のリレーションシップを指定してください。これを無視する場合は危険を覚悟してください。

キーの一意性

SQL Server 2005 では、属性をディメンション内の "キー" 属性で識別することが必要です。このキーは、一意であることが必要です。システムがディメンションを処理する際、一意性が保証されている必要があります。これは、SQL Server 2000 Analysis Services に慣れている DBA にとっては特に難しいことです。

SQL Server 2000 Analysis Services は階層ベースであるため、DBA はキーの一意性をさまざまな方法で表すことができます。メンバ キーが、ディメンション全体で一意であると設定することができます。たとえば、ディメンション内のすべてのメンバ間で、キー 42 を持つメンバは 1 つだけの場合があります。また、1 つのレベルで一意性を設定することもできます。たとえば、Subject レベルにキー 42 のメンバがあり、Item レベルにもキー 42 のメンバがあって構いません。また、一意性をオフに設定することもできます。この場合、キー 42 を持つメンバが複数あって構いません。唯一の制限は、キー 42 を持つ 2 つのメンバの親が同じであってはならいということだけです。内部的に、システムは、一意のキーを指定しなくても、ディメンションの階層が 1 つしかないという事実を利用して一意のキーを作成します。システムは、階層を上に向かうキーの組み合わせを使用して一意のキーを作成し、それを使用して一意性を保証します。

SQL Server 2005 Analysis Services では、ディメンションは多数の階層を持つことができます。また、ユーザー定義階層がなく、属性階層しかないこともあります。これは、キーの一意性にとってどのような意味を持つのでしょうか。属性は、フラットな属性階層のように、構造化された階層がなくても自立できなくてはならないため、キー 42 は何を意味するのでしょうか。結局、キーは本当のキーでなくてはならないことを意味します。つまり、キーは属性を 1 つだけ一意に識別する必要があります。

一意のキー属性の他に、システムはすべての属性のキーの一意性も確認します。これは強制ではありませんが、すぐに説明するように、属性キーの一意性を保証しないようにディメンションを設定すると、予期せぬ結果になることがあります。

ベスト プラクティス (必須) : 属性キーの一意性は必ず保証する必要があります。

まず、属性に対する一意のキーを指定する方法について説明します。次に、間違うとどうなるかを説明します。

我々がプロジェクト REAL で行ったように、自分のプロジェクト用に標準の時間ディメンションを作成するとします。そのディメンションの属性として、Month フィールドを考えます。このフィールドには、1 ～ 12 の数値が格納され、1 = 1 月、2 = 2 月、... 12 = 12 月とします。一見すると、一意性の要件が満たされているように見えます。しかし、2003 年の 1 月と 2005 年の 1 月はどのようにして比較するのでしょうか。実際には、キーは 1 から 12 ではなく、月と年の組み合わせです。リレーショナル技術に詳しい人なら、これは新しい概念ではありません。これは主キーであり連結キーです。しかし、SQL Server 2000 Analysis Services に慣れている人にとっては、新しい考え方です。SQL Server 2000 では、あるレベルに一意性を設定して、それについては気にしなくても構いません。しかしSQL Server 2005 では、古い習慣は捨てる必要があります。

幸運にも、一意キーは簡単に指定できます。属性の Key プロパティを参照するだけです。単一のフィールドだけでなく、フィールドのコレクションを指定することもできます。コレクションに追加したフィールドは、一意性が高まります (連結キーができるため)。Month キーについては、図 3 に示すように、Month フィールドと Year フィールドのコレクションを作成する必要があります。

これらの変更を行う前と後で何か起きるかを見てみます。

図 4 は、Quarter、Month、および Week に一意でないキーを使用した場合の、プロジェクト REAL の Time.Calendar 階層を示します。下位の RDBMS では、キーの範囲はそれぞれ (1 ～ 4)、(1 ～ 12)、および (1 ～ 52) です。問題は、これらのキーがディメンション全体で一意でないことです。たとえば、2001 年の 1 月と 2002 年の 1月、2003 年の第 2 四半期と 2004 年の第 4 四半期をどのようにして区別すればいいのでしょうか。キーを一意にするには、Year を使用して連結キーを作成する必要があります。これにより、Quarter キーは (Year, Quarter) となり、Month キーは (Year, Month) となり、Week キーは (Year, Week) となります。ここでは、連結キーを作成していません。この結果を図 4 に示します。年に四半期がなく、年の中の四半期が誤っているなど、結果が正しくありません。

図 5 は、Calendar_Year_ID を、Quarter、Month、および Week に対応するキー コレクションに追加した後の階層を示します。今度は結果が正しくなります。

処理ダイアログ ボックスの行数を見るのも興味深いものがあります。Year を使用して連結キーを作成する前は、Month 属性階層は 13 レコードを返しました。これは、月のキーと不明なメンバに対して SELECT DISTINCT ステートメントを使用した場合に予期されるものです。Year キーを追加した後は、SELECT DISTINCT に年が含まれているため、219 レコードが処理されています。

いくつかの補足を説明します。まず、プロジェクト REAL で提供されているサンプル データベースを使用してこの手順を実行すると、ディメンションを処理するときにエラーが (警告も致命的なエラーも) 報告されないことに気付きます。ディメンションを確認して、メンバが期待したとおりにならない点に注意してください。

キーの一意性は、関係する属性に結びついている点に注意してください。関係する属性を指定しなかった場合は、すべての属性がキーに関連付けられます。キーは一意でなくてはならないため、問題はありません。しかし、関係する属性を指定し始めると、これらの属性に関して何かを暗黙的に指定していることになります。つまり、キーが一意であると言っていることになります。前の例では、キーが一意でないことが問題の原因であることを見つけたときに、関連する属性のリレーションシップがありました。

一意のキーを生成できなかったため、関係する属性を削除することしかできませんでした。そのため、属性はキー属性に直接関連付けられ、キー属性は一意であることが必要でした。関係する属性がないと、質の高い集計のデザインを生成することができません。結局のところ、Analysis Services での "キー" という言葉を尊重する必要があります。設計プロセスでは、慣例としてディメンション全体でキーを一意にしてください。

ベスト プラクティス : ディメンションを調べ、メンバの幅広さや分布が期待どおりであることを常に確認してください。

分布が偏っている場合は、おそらく 2 つの原因のうちどちらかが起きたと考えられます。下位の属性に対して一意キーが見つからなかったか、階層の順序付けが正しくないかのどちらかです。たとえば、間違って Year、Quarter、Month ではなく、Year、Month、Quarter を指定した場合です。これらのどちらの状況でも、処理中はエラーが報告されませんが、異常な階層になります。

属性のリレーションシップを定義する場合は、データの有効なパターンに基づいて定義する必要があります。属性のリレーションシップは、高いパフォーマンスのシステムを設計および実装する上で不可欠です。システムが誤ったロールアップを行うようなキー構造を指定したら、何が起きるでしょうか。たとえば、自然階層のレベルの 2 つを逆にするとどうなるでしょうか。

自然階層 Store の本来のデータの順序は (ALL, District, Region, City, Store) ですが、これを (ALL, Region, District, City, Store) として定義したとします。自然階層は属性のリレーションシップから構築されます。この場合、属性のリレーションシップに組み込まれている、親が 1 つであるという前提が、データによって満たされていません。この例では、地域 (region) が多数の地区 (district) にロール アップされます。そのため、計算が正しくなくなります。実行時に数値が 2 度カウントされ、間違った計算が行われます。システムのパフォーマンスは低下し、誤った答えを返します。そのため、属性のリレーションシップを定義するときには注意してください。定義が間違っていてもエラーにならないためです。

ベスト プラクティス : 属性が関係していることをシステムに伝えるには、関連付けを行う必要があります。

データはリレーションシップをサポートする必要があります。また、キーの一意性によって、こららのリレーションシップを実行できることが必要です。

ここで面白い疑問が湧きます。関係する属性を定義しないとどうなるでしょうか。驚くことに、このようにしても正しい答えが返される有効な階層ができます。図 6 を参照してください。

図 6 の階層は有効に見えます。システムにクエリを発行すると、数値は正しく加算されます。問題は、関係する属性がないため、下位の属性間の強いリレーションシップにシステムが気付かないことです。このリレーションシップは、階層の上に向かってロールアップを事前に計算できることを示します (SQL Server 2000 Analysis Services では、このために集計を使用します)。代わりに、システムは、クエリの計算時に、ランタイム システムを使用してロールアップを行います。集計はデザインされていないため、階層中の属性が関係していることはシステムに伝えてありません。そのため、階層は正しく、正しい数値で階層中を上方向に計算されますが、事前に計算された集計を使用して小計を計算できず、パフォーマンスが低下します。

仮想ディメンションから属性階層への変換

残念ながら、プロジェクト REAL のドキュメントに、SQL Server 2005 に変換する前の、開始当初の SQL Server 2000 Analysis Services の設計を含めることはできません。元の設計は、変換後の最終的な設計よりもはるかに単純で、ほぼ 6 個のディメンションが削除されています。その代わりに属性階層があります。ほとんどの属性階層は、Item の中にあります。たとえば、システムには 5 つの仮想ディメンションがあり、Item ディメンション内のベンダのリレーションシップ上に構築されています。元の設計では、Source Vendor、Return Vendor、Purchase Vendor などのディメンションがありました。SQL Server 2005 用の最終的なプロジェクト REAL の設計では、これらは Item. Source Vendor、Item.Return Vendor、およびその他の属性階層で置き換えられました。元の設計には、通常のディメンション Department が含まれていました (このディメンションは、Item ディメンション テーブルの最上位にあるビューから構築されました)。SQL Server 2005 用の最終的な設計では、属性階層 Item.Department で置き換えられました。

ベスト プラクティス : SQL Server 2000 Analysis Services の仮想ディメンションは、属性階層に変換します。

仮想ディメンションに複数のレベルがある場合は、対応する属性をレベルとしたユーザー定義階層に変換します。この変換は、移行ウィザードによって自動的に行われます。しかし、手動で移行を行う場合や、SQL Server 2005 でシステムを再設計する場合には、自動的な変換について認識しておく必要があります。

仮想ディメンションに 1 つしかレベルがない場合は、仮想ディメンションをデータ モデル (およびアプリケーション コード) から削除し、属性階層を直接使用します。

2005 の設計を調べるときは、各ディメンションをすべて検討し、エンティティが本当に別のディメンションの属性かどうかを自問自答してください。エンティティが別のディメンションの属性である場合は、属性階層で置き換えることができます。これにより複雑さが減り、エンド ユーザーにとって意味あるものになります。エンティティは元のディメンション タイプに対して公開されており、下位のマルチディメンション設計に対する理解が増します。

たとえば、元のプロジェクト REAL の設計には仮想ディメンション Department が含まれていました。これは、その本を購入した店舗の売り場 (ハードカバーの本や特価品の本など) を示します。仮想ディメンション Department は、Item ディメンションのメンバ プロパティに基づいていました。我々は、これを物理ディメンションのままにする代わりに、Item ディメンションの属性階層に変換しました。これによって、エンド ユーザーは、Department が、Stores や別のディメンションではなく Item に関係していることが明確にわかります。

名前の競合の可能性

ユーザー定義階層を使用しているときに、名前が競合しそうになったことがあります。これは、ユーザー定義階層と属性階層が同じ名前空間を共有するためです。

SQL Server 2000 Analysis Services には、ディメンション名または階層名と、レベル名の、2 種類の名前がありました。Analysis Services 2005 には、ディメンション、ユーザー定義階層、属性階層の 3 つの名前空間があります。ユーザー定義階層と属性階層は同じ名前空間を使用するため、名前が競合する可能性があります。

たとえば、Buyer というディメンションがあるとします。多くのフロントエンド ツールは階層名だけを公開するため、Buyer という名前の階層を作成しがちです。これは Analysis Services 2000 でもできました。問題は、Buyer という階層を作成すると、Buyer という属性階層が作成できない点です。プロジェクト REAL では、属性階層を Buyer Name という名前に変更しました。図 7 で丸で囲まれているオブジェクトは、すべて一意に名前を付ける必要があります。

Return Vendor という名前の属性階層や、Return Vendor という名前のユーザー定義階層は使用できません。そのため、我々の設計では、SQL Server 2000 Analysis Services で使用していた一般的な命名規則を拡張しました。名前が頻繁に再利用される複雑な状況では、ユーザー定義階層を "By <name>" という名前にしました (ここで name は、ユーザー定義階層中で分析される属性を表します)。このルールには 2 つの例外があります。場合によっては、明確にする必要がない、よく知られたリレーションシップがあります。良い例は Time.Calendar と 12.50 ドルです。この "By <name>" の規則に従うと、正しくないように感じられ、Time.By Calendar と Time.By Fiscal は、違和感があります。しかし、命名規則は、同じ名前のユーザー定義階層と属性階層を扱うのに便利な手段です。

リレーショナル テーブルのフィールドをマルチディメンション設計の属性に格上げする

プロジェクト REAL のマルチディメンション設計を進めるに際に、特定のリレーショナル フィールドをデータ ソース ビューのディメンション テーブルに属性階層として含めるかどうかを、常に自問自答しました。我々はこれを、データ ソース ビュー内のフィールドの "格上げ" と呼びました。

たとえば、Item ディメンションには、データ ソース ビューのディメンション テーブル内に 144 を超えるフィールドがあります。これらのフィールドの多くは分析で使用されないため、これらのフィールドすべてに対して属性を作成すると、リソースが効率的に使用されません。結局、我々はこれらのフィールドのうち 44 個を属性としてディメンションに追加することにしました。

興味深い設計上の疑問は、ディメンション テーブル中のすべてのフィールドを属性階層に追加 (または格上げ) すべきかどうかということです。ディメンション ウィザードは、既定ではこの処理を行います。すべてのフィールドが属性階層として移動されます。フィールドが格上げされないようにするには、フィールドの横にあるチェック ボックスのチェックを手動で外す必要があります。フィールドを属性に格上げしないことが正しい選択かどうかは、リレーショナル データ ソースの複雑さとデータ量によります。

我々は、フィールドを属性に格上げすべきかどうかの一般的なガイドラインを作成しました。ぜひ参考にしてください。

• まず、そのフィールドに対して分析を行う必要があるかどうかを判断します。エンド ユーザーが分析を行う際には、属性階層を使用するのが主な方法です。属性階層を選択オブジェクトとして使用し、クリックとドラッグ、スライスとピボットを行います。フィールドが属性でなければ、操作や分析を行うことはできません。

  統合ディメンション モデル (UDM : Unified Dimensional Model) の真の力は、分析で使用できる属性階層が豊富なことです。この分析では、ユーザー定義階層などの事前に定義されたオブジェクトや、アド ホックな操作で使用できるオブジェクトのどちらかを使用できます。属性階層は、アド ホックな分析で使用されます。たとえば、ハードカバーの本の地域の販売パターンと、ペーパーバックの本を比較して分析したいとします。ユーザー定義の Store.ByGeography 階層を使用すると、目的の組織レベル (つまり地域と地区) にドリル ダウンできます。その一方、属性階層 Item.Department をグリッド上にドラッグし、地域の内部にネストさせ、ハードカバーの本とペーパーバックの本の小計を参照することができます。Department フィールドを属性階層に格上げしないと、このようなアド ホック分析は実行できません。

• 後でフィールドに対する分析を行う可能性があるなら、格上げする必要があります。

  たとえば、ディメンション テーブル Item に、フィールド Original EAN があります。これは、ある本に割り当てられている最初の EAN 番号です (この番号は後で変更される可能性があります)。我々は、エンド ユーザーがこのフィールドに対して分析を行わないと判断し、これを表示しませんでした。必要なのは現在の EAN 番号だけであるため、ディメンションの属性として格上げしませんでした。しかし、間違っている可能性もあります。将来ユーザーがこのフィールドを使用して分析を行う可能性が十分あるなら、格上げする必要があります。

  大きなテーブルでは、すべてを格上げすることはできません。格上げできるフィールドが多数ある場合は、どれを格上げするかを決定するのも困難です。たとえば、プロジェクト REAL の Item テーブルでは、属性ステータスに格上げできるフィールドが 144 以上もありました。これは、ユーザーが常に監視すると予想される妥当な数をはるかに超えています。

• 属性が階層との関連でのみ有効な場合は (言い換えれば、それ自体が分析で使用される可能性がほとんどないか、可能性がまったくない場合)、属性階層に格上げはするものの (ユーザー定義階層に追加できるようにするため)、非表示にします。こうすることで、ユーザーが混乱することはありません。

  たとえば、我々は属性階層 Buyer.Buyer Alpha を非表示にしました。この属性階層は、購入者名の最初の文字であり、Buyer 階層中での移動を容易にするためだけにあります。我々は、エンド ユーザーが購入者の最初の文字に基づいてスタンドアロン分析を行うことはないと考えました。

  リレーショナル フィールドを属性階層に格上げすべきでない場合については、以下のガイドラインが適用されます。

• フィールドが、ディメンションに対して機能面で依存していない場合は (3NF コンテキストにおいて)、格上げしません。たとえば、Item ディメンション テーブルには、Item に機能面で依存しないフィールドが約 15 個あります。これらのフィールドは、実際にはその商品で利用される流通センターの非正規化されたフィールド (つまりプロパティ) です。これらnフィールドは、Item に機能面で依存していません。そのため、これらの非正規化されたフィールドは、Item の属性階層に含めるべきではありません。最終的に、このデータから流通センターのディメンションを構築することもあるかもしれませんが、現在はプロジェクト REAL の設計に含まれていません。フィールドがソース RDBMS のテーブルに含まれているというだけで、マルチディメンション データ モデルに含める必要は必ずしもありません。

• ディメンションとビジネス上の関係がないフィールドは格上げしません。管理目的で存在するフィールドがテーブルに含まれていることも珍しくありません。これらのフィールドは、実際にはエンティティ (商品や店舗など) を表していません。この種のフィールドには、データが最後に変更された日付が格納されたフィールドや、データを変更したユーザーの名前が格納されたフィールド、レコードが作成された日付が格納されたフィールドなどがあります。これらのフィールドはディメンションと関係ないため、格上げすべきでありません。

tinyint キーに一致しないデータ型

リレーショナル設計における有名なベスト プラクティスの 1 つに、モデル化するオブジェクトの範囲を満たす、できるだけ小さなデータ型を使用するというものがあります。そのため、ビジネス上の理由から特定の値が特定のしきい値を超えることがないとわかっている場合は、大きなデータ型は使用しないでください。大きなデータ型を使用すると、値を格納するためのストレージが無駄になります。

これは、一般には良いアドバイスですが、tinyint 型のキーを扱う際には、SQL Server 2005 でのデータ型の不一致に気をつけてください。Tinyint キーは 1 バイト (1 ～ 255 の間) として格納されます。データ ソース ビューを作成する際、tinyint キーが ID フィールドにあると (代理キーの場合あり)、システムは integer に変換します。そのため、ディメンションのキーは integer になります。しかし、ファクト テーブルでは、値は tinyint として格納されているため、データ型の不一致が発生します。

たとえば、SQL Server 2000 形式の元のプロジェクト REAL では、Department ディメンションは完全なディメンション テーブルでした。Department キーは tinyint (1 ～ 13) であり、ハードカバーやペーパーバックなど、商品の種類が格納されていました。Department キーには Identity プロパティがあったため、次の売り場は 14、15 のようになりました。リレーショナル設計としては、要件を自然に満たす最も小さなデータ型を常に選択するのがベスト プラクティスであるため、tinyint を選択しました。この場合、元の設計者は 255 を超える売り場ができることは予想していませんでした。当然、ファクト テーブルには外部キーがあり、これも tinyint でした。これが原因で、SQL Server 2005 Analysis Services に設計を移行したときに、データ型の不一致が発生しました。

この状況の回避策は、ディメンション テーブルのデータ ソース ビューに、キー フィールドを tinyint に明示的にキャストする計算列を作成することです。次に、ディメンションキー属性階層に対して、元のキー フィールドではなくそのフィールドを使用します。ファクト テーブルの tinyint を integer に変換することもできますが、それではスペースの無駄になります。

これはバグではないかと思うかもしれませんが (我々もそう思いました)、実は Microsoft XML Core Services (MSXML) の以前の開発作業の結果であることがわかりました。MSXML では、不明なデータ型から目的のデータ型への暗黙的なデータ型変換の考え方があります (SQL Server RDBMS が行うものとは似ていません)。tinyint は、SQL Server の古いデータ型であり、MSXML が実際に integer にデータ型の変換を行うことがわかりました。この時点で、MSXML の動作を変更するには、あまりにも多くのコードが既に存在しているため、回避策を受け入れる必要がありました。

不明なメンバ

不明な値をシステムがどのように扱うかを決めるのは、常に興味深い設計上の決定です。SQL Server 2000 では、これは簡単でした。アプリケーション レベルの不明なメンバをユーザーが作成できました。それには、不明なメンバをディメンションに追加し、ファクト テーブルのソースに対して、ISNULL などのリレーショナル手法を追加しますSQL Server 2005 の新機能により、システムが自身の不明なメンバを生成し、自動的にデータを割り当てることができるようになりました。

プロジェクト REAL では、両方のことを行いました。ほとんどのキーに対して、アプリケーションレベルの不明なメンバ ("missing" と名付けました) を作成しましたこの作業のほとんどはビューで行い、通常は、検索が見つからないことを示す代理キー 0 を返しました。このアプローチにより、予期しないデータが見つかったときのシステムの動作がより寛容になりました。論理的な "missing" データ (予期していたデータ) を、予期せぬ無効なデータ (つまりダーティ データ) を使用して追跡することができます。システムによって生成された不明なメンバを使用することの問題は、論理的に整合性のないデータと、ダーディで予期せぬデータの違いを検出できないという点です。

ベスト プラクティス : できるだけユーザー固有の不明なメンバを作成してください。システムが生成した不明なメンバは、控えめに使用してください。

以下のコードは、不明なメンバがリレーショナル ビューで処理される方法を示す例です。

CREATE VIEW [dbo].[vTbl_Dim_Store] AS 
   SELECT store.SK_Store_ID
      ,store.Store_Num
      ,store.Order_Status
      ,store.Store_Desc
      ,COALESCE(store.Division_Num,0) as Merch_Div_Num
      ,store.Status
      ,COALESCE(store.Status_Desc,'Unknown') as Status_Desc
      ,store.Open_Date
      ,store.Close_Date
      ,store.Division_Num
      ,COALESCE(Store.Division,'Unknown') as Division
      ,store.Region_Num
      ,COALESCE(store.Region,'Unknown') as Region
      ,store.District_Num
      ,COALESCE(store.District,'Unknown') as District
      ,store.Market_Area_Num
      ,COALESCE(store.Market_Area,'Unknown') as Market_Area
      ,store.Market_Type
      ,store.Ad_Area_Num
      ,COALESCE(store.Ad_Area_Desc,'Unknown') as Ad_Area
      ,store.Center_Desc
      ,COALESCE(store.City,'Unknown') as City
      ,store.Zip_Code
      ,store.State
      ,store.Mgr_Name
. . . 
FROM dbo.Tbl_Dim_Store store

COALESCE 関数は、データベース中の NULL 値を、ディメンション内の論理的に不明なメンバに投入するために使用しています。上の Transact-SQL コード例の Merch_Div_Num 列では、キー値 0 は不明なメンバに対するアプリケーションの規則です。これらの例は、不明なメンバに対する論理的なクリーンアップ関数です。我々は、いくつかの NULL が存在することを予想しています。これらの NULL は、それらのセマンティクスに対して事前に確立した値 (たとえばキー値 0) を投入します。

システムによって生成される不明なメンバは、SQL Server 2005 の新機能です。プロジェクト REAL の設計では、存在しない顧客を扱うために使用しました。最終的には、このディメンションには多数のダーティ (不適切な) データがありました。システム全体を設計したところで、約 10% の販売データに問題があることに気付きました。Customers の代理キーの検索で見つからないためです。結局、販売が行われた時間と、新しい顧客がロイヤルティ カード システムで有効になった時間の間の、自然の遅延が原因であることがわかりました。顧客データ ソースからのデータ フィードに新規顧客が現れるまでに、1 か月以上かかることもありました。販売データ フィードにはすぐに現れるものの、顧客データ フィードでは遅延がありました。このデータ フィードの不一致は珍しくない状況だと感じました。これについては、みなさんもおそらくすぐに認めることでしょう。そこで、このような活動を追跡するために ETL 全体と Analysis Services 全体を調整しないことにしました。代わりに、システムによって生成される不明なメンバを定義することにしました。次に、Store Sales パーティションのエラー構成を変更し、外部キーの検索エラーが不明なメンバに割り当てられるようにしました。

図 9 に、ディメンションの構成方法を示します。

図 10 に、我々が Store Sales パーティションのエラー構成を変更した方法を示します。

図 11 に、システムによって生成された不明なメンバをクエリで使用するとどうなるかを示します。

タイム インテリジェンス ウィザード

我々は、タイム インテリジェンス ウィザードを使用して新しい時間ディメンションを作成する際に、いくつかの問題に遭遇しました。SQL Server 2005 Analysis Services における新機能に、サーバー側の時間ディメンションがあります。サーバー側の時間ディメンションは使いやすいため (何度かクリックするだけで時間ディメンションが作成できます)、使いたい誘惑に駆られますが、一般的なベスト プラクティスとしては必ずしもお勧めしません。

ベスト プラクティス : できるだけスタンドアロンの時間ディメンション テーブルを作成してください。

時間ディメンション テーブルがあると、複雑な時間の状況を扱う上で柔軟性が増します。

• 独自の時間プロパティを追加することができます。たとえば、特定の日が週末なのか平日なのか、会社が休みかどうか、最盛期なのか季節外れなのか、クリスマス シーズンなのか (これは多くの小売店にとって非常に重要です) といったプロパティが考えられます。

• 複数の階層を明確にして構築するのが簡単になります。たとえば、2 つの企業が合併する場合、新しく合併する企業が 2 つの異なる会計カレンダー (会社ごとに 1 つ) を運用しなくてはならないことがあります。

• 特殊な階層を構成することができます。たとえば、会社の生産月が、必ず暦月の第 1 月曜日から始まる場合は、日付の番号付け体系が特殊になります。

• 複雑な状況では、ビジネス モデルによってレベル全体をスキップする必要があります。たとえば、特殊なカレンダーに、年、週、日しかないことが考えられます。また、年と日しかないこともあります。

• 論理的な "現在の日" に対する値など、必要に応じて追加する計算メンバが使用できます。企業によっては、決算日とデータ フィードが無関係なことがあります。データは毎日入力されますが、決算日の前に帳簿を締めることができるよう、論理的な現在の日が必要になります。

• 一般なベスト プラクティスとしては、リレーショナル設計のエンティティ間で、常に代理キーを使用することをお勧めします。しかし、これは、このベスト プラクティスが意味をなさない 1 つの状況です。タイムスタンプや、日を表すその他の表現 (たとえば、2005 年 3 月 21 日を整数 20050321 で表すなど) が既にある場合は、その方法を使用し続けるのはおそらく意味があります。この場合、考慮すべき 2 つのポイントがあります。まず、日付/時刻スタンプを使用せず、必ず日付スタンプを使用してください (外部キーから時刻を削除してください)。次に、SQL Server RDBMS の DATEPART 関数を使用して、日付スタンプから外部キーを自動的に構築します。

• アプリケーションで、日よりも詳細なドリルダウンが必要な場合は、時刻を 2 つのディメンションに分けることを検討します。1 つ目のディメンションは時刻を日レベルで記録し、2 つ目のディメンションは時刻を記録します。

  たとえば、商品の販売を分単位で追跡したいとします。一見したところ、次のようなユーザー定義階層を持つディメンションを作成したくなります。

  Year -> Quarter -> Month -> Week -> Day -> Hour -> Minute

  ディメンションをこのように実装したとすると、5 年間の履歴を保持した場合、このディメンション中のメンバはいくつになるでしょうか。答えを聞いて驚くかもしれませんが、2,675,795 個のメンバが必要になるのです。時間と分をこのようにモデル化すると、数が多いだけでなく、週や月全体での傾向を見つけるのが難しくなります。代わりに、以下のような2つのディメンションを使用することを検討してください。

  Time:Year -> Quarter -> Month -> Week -> Day

  Time of Day:Period -> Hour -> Minute
  (Period は、勤務時間外、午前、昼下がり、夕方前、夕方など)

  これで、5 年間の履歴でも、メンバの数が 3,640 個で済みます。このアプローチにより、より意味のある分析が可能になります。午前の時間帯の売り上げだけをスライスしたり、月の最初の週の午前中の売り上げと午後の売り上げを比較 (給料日に後の午前中など) することができます。

時間ディメンション テーブルを作成するには 2 つの方法があります。1 つ目の方法は、標準のディメンション ウィザードを使用する方法です。ウィザードを使用して属性、階層、その他のオブジェクトをすべて作成します。その後、適切な時間セマンティクスに合わせて、さまざまな属性の種類を変更します。たとえば、月の属性に、暦月、会計月、報告月、その他の種類のいずれかを表すラベルを付けます。このアプローチは、属性や階層のほとんどに特別な時間セマンティクスがない場合や、時間に依存する関数をまったく使用しない場合に使用します。

2 つ目の方法は、タイム インテリジェンス ウィザードを使用する方法です。このウィザードを起動するには、標準のディメンション ウィザードを開いて、これが時間ディメンションであることを指定します。タイム インテリジェンス ウィザードに切り替わり、図 12 に示すように、時間固有の情報を入力できるようになります。

このウィザードに何か足りないことに気付くでしょう。時間属性の多くは入力できますが、キーとメンバ名を同時に入力することができません。キー フィールドと名前フィールドのどちらかを選択する必要があります。

ベスト プラクティス : タイム インテリジェンス ウィザードでは、年、月、週などのさまざまな属性に、キー フィールド (名前ではなく) を入力します。

キーを選ぶことのメリットは、後からメンバ名だけを変更すれば済むことです。既定では、ウィザードはキーに Order By プロパティを設定します。そのため、ここでキー フィールドを入力しておけば、後から名前フィールドを変更するだけで済みます。

ここでは、ウィザードの実行後に定義を修正することに関して話していますが、メンバ キーの一意性の要件を忘れないでください。時間と共に、従来のキーと名前を使用しがちになります。たとえば、Q1、Q2、Q3、Q4 などの名前とキーを使用するかもしれません。または、January (1 月) は 1、December (12 月) は 12 というキーを使用するかもしれません。DBA なら、月を正しく順序付けるために、月のキーが 1 ～ 12 でなくてはならないことを頻繁に思い出しますが (そうしないと、April が年の最初の月になってしまいます)、我々は、四半期 Q2 や月 4 がキーを一意に指定するのに十分でないことを常に思い出すわけではありません。必ずと言ってよいほど、メンバ キーのコレクションを作成し、一意性を指定するためにコレクションに年を追加する必要があります。

作成したさまざまな階層で属性のリレーションシップを作成するのを忘れないでください。たとえば、日と週、週と月、月と四半期、四半期と年の間に属性のリレーションシップを作成します。キーの一意性と同様に、システムを効率的に動作させるには、属性のリレーションシップが不可欠です。

メンバ名を決定し、時間属性階層のキーの一意性を確認し、定義した階層を支援する属性のリレーションシップを確立したら、週末、休日、シーズンの表示など、時間に関連するその他の属性を追加することができます。

このように、時間は簡単な概念のように思われますが (結局はタイムスタンプに過ぎません)、テーブルや Analysis Services オブジェクトをすべて構築し、適切に構成されていることを確認するには、設計時間のうちかなり多くの時間を費やすことになります。

メジャー グループ

プロジェクト REAL で実装されたリレーショナル データ モデルには、3 つのファクト テーブル Store Sales、Store Inventory、Distribution Center Inventory があります。物理的には、大量のデータ (たとえば、週あたり 1.4 ～ 2 億個のインベントリ レコード) を処理するため、これら 3 つの論理ファクト テーブルは毎週パーティション分割されます。パーティションは、以下の理由からも重要です。

• パフォーマンスが向上します。クエリで必要な期間を含んだパーティションだけがスキャンされます。一般に、パーティションが多くなると、各パーティションが小さくなり、クエリの実行も高速になります。

• データの削除が容易です。パーティションを削除するだけでデータが削除されます。キューブを再処理するする必要はありません。通常、ファクト テーブルからデータを削除するには、キューブの再処理が必要になります。

• ローリング期間を実装するのが簡単になります。たとえば、システムが過去 3 年分のデータを保持するとします。この場合、開始日を固定するのではなく、36 か月分のパーティションをローリングすることができます。週や月が変わったら新しいパーティションを作成し、3 年よりも古いパーティションは単に削除するだけで済みます。

SQL Server 2000 Analysis Services のベスト プラクティスに精通している読者は、すべてのメジャー グループが単一のキューブに含まれているという、プロジェクト REAL の面白い事実に気付くでしょう。メジャー グループは、SQL Server 2000 Analysis Services で "キューブ" と呼ばれていたものを表します。キューブはファクト テーブルの 1 つであり、データベース中の特定の粒度属性で、使用可能なディメンションのサブセットにリンクされています。ファクト テーブルの粒度属性は、さまざまなディメンションの最も下のレベルです。SQL Server 2000 Analysis Services には、"仮想キューブ" と呼ばれる機能があります。この機能を使用すると、異なるキューブを一緒に単一のフレームワークに分類することができます。SQL Server 2005 Analysis Services では、キューブは単一のフレームワークです。メジャー グループは、さまざまな粒度属性でディメンションのサブセットをキャプチャするようになりました。

仮想キューブを使用したい場合は、キューブあたり 1 つのメジャー グループを使用し、複数のキューブ間でリンクされたメジャー グループを使用することで、仮想キューブを作成することができます (古いスタイルのキューブ)。実際、Analysis Services の移行ウィザードで作成されるのはこれです。その理由は、オブジェクトの数と種類を、SQL Server 2000 Analysis Services データベースと SQL Server 2005 データベースで同じにするためです。

メタキューブのアプローチには、良い点と悪い点があります。このアプローチでは、ありとあらゆるものが単一のキューブに格納されます。メタキューブ アプローチの最も大きなメリットは、エンド ユーザーが、人為的な境界なしに単一の構造に接続しクエリを実行することができる点です。これは、キューブの構成方法によるものです。エンド ユーザーにはメジャーの集まりだけが見えます。キューブにクエリを発行する方法に応じて、システムは必要なメジャー グループを動的に調整します。

計算は同様です。すべての計算が格納された単一のキューブを作成するだけで、システムがそれに応じて調整されます。この便利なオブジェクト スペースを実感するには (あらゆるものが単一の "ポット" に格納されているため)、サンプルの Analysis Services データベース AdventureWorksDW を参照してください。このデータベースには、18 個のディメンション、154 個の属性階層、18 個のユーザー定義階層、100 個を超える計算、9 個のメジャー グループ (すべて異なる粒度属性) があり、すべて単一のキューブに含まれています。すごいですね。

しかし、このアプローチには欠点もあります。最も明白な欠点は、検索するオブジェクトの数が多い点です。表示フォルダやパースペクティブなど、SQL Server 2005 の新しいオブジェクトを扱うことができるクライアント アプリケーションが必要です。これらのオブジェクトを使用すると、さまざまなオプションが多数あってエンド ユーザーが困惑しないよう、オブジェクトを機能グループに整理することができます。

このアプローチの 2 つ目の欠点は、ディメンションを完全に処理すると、そのディメンションを使用するすべてのメジャー グループの処理状態がリセットされる点です。これには、メジャー グループとそのすべてのパーティションが含まれます。我々は、テスト ケースの中で、Vendor Type ディメンション (メンバは 5 つしか含んでいません) を間違って完全に処理したときにこれを見つけました。これを行うと、すべての Sales パーティションがリセットされます。そのため、Sales メジャー グループと Item Vendor メジャー グループ内の 153 個のパーティションすべてを再処理する必要がありました。言うまでもありませんが、約 10 時間後にこの処理が完了し、必要がない限り二度と行わないことに決めました。さらにひどいことになっていた可能性もあります。Measures ディメンション (メンバが 1 つだけあります) の完全な処理を実行していたら、すべてのメジャー グループに影響していたところでした。キューブを元に戻すのに、2 TB と 3 日間を要していたことでしょう。

前述のように、ファクト テーブルには 3 種類の情報が格納されています。

• ファクト テーブルの粒度は、そのディメンショナリティで表されます。それぞれのディメンションには外部キーがあり、適切なレベルおよび属性のディメンション テーブルを指しています。プロジェクト REAL では、これらは、"タイプ 2 の緩やかに変化するディメンション" を実装できるように、システムによって生成された代理キーです。これらの代理キーが結合されるレベルは、そのディメンションの粒度属性を表します。たとえば、時間ディメンション キーは Month となります。もう 1 つのファクト テーブルは Day かもしれません。その他のディメンションでは、キーが Item および Store かもしれません。

• すべてのディメンションをメジャー グループ内で表す必要はありません。たとえば、メジャー グループ Store Inventory と DC Inventory では、ベンダに基づく分析を行いません。そのため、Vendor ディメンションや Vendor Type ディメンションを含みません。

  プロジェクト REAL のキューブでは、図 13 に示す粒度属性を使用しています。

  

  図 13     キューブ エディタにおけるディメンションからメジャー グループへのマッピング パネル
  拡大表示する
  
  

• ファクト テーブルには、我々が報告として知りたいメジャーが格納されています。例では、ファクト テーブル Sales に、売り上げ金額、売り上げ量、値引き金額、クーポン金額などのメジャーが格納されています。

• また、ファクト テーブルには、ディメンションを指さない追加情報 ("不完全キー" と呼びます) も格納することができます。逆に、この情報にディメンション自身を格納することもできます。たとえば、Store Sales の場合、ファクト テーブルには商品を販売したレジの番号を格納できます。プロジェクト REAL のファクト テーブルには、不完全キーはありませんでした。

  Analysis Services では、内部的に各メジャー グループの情報が多数格納されています。次のセクションでは、メジャー グループに結びつけられている (かつ、設計情報に含まれている) 情報のサブセットについて説明します。

パーティション

システムはファクト テーブルをパーティションに格納します。パーティションの構造は、メジャー グループと同じです。同じデータ型の同じフィールドが格納されています。しかし、パーティションにはデータのサブセットが格納されています。たとえば、1 週間分のデータしか格納されていないこともあります。

このセクションでは、特に明記しない限り、パーティションが週レベルで保持されていると仮定します。vTbl_Fact_Store_Sales_WE_2004_11_27 という名前のリレーショナル テーブルがあります。

プロジェクト REAL データベースのパーティションは、SQL Server 2000 のベスト プラクティスの 1 つに違反しているように思われます。パーティションにデータ スライスが設定されていないのです。SQL Server 2000 では、ランタイム エンジンがどのパーティションにアクセスすべきかわかるように、パーティションにデータ スライスが設定されている必要があります。これは、リレーショナル クエリ オプティマイザにヒントを指定するのに似ています。SQL Server 2005 では、これは不要になりました。パーティションを処理すると、MOLAP ストレージ中に、ヒストグラムに似た構造が自動的に構築されます。この構造が、すべてのディメンションのどのメンバがパーティションに含まれているかを示します。したがって、格納方法が MOLAP である限りは、データ スライスはオプションの (かつ使用されない) プロパティです。しかし、ROLAP ストレージや、プロアクティブ キャッシュが ROLAP アクセス フェーズを伴う場合には、データ スライスが使用されます。このどちらの状況でも、実際のファクト データは移動されないため、システムはメンバを識別する機会がありません。この場合、パーティションのデータ スライスを設定するステップは、システムのパフォーマンスを高める上で、必要かつ非常に重要なステップです。

MOLAP 構造では、データ スライスが動的に決定されるため、SQL Server 2005 では新しいタイプのパーティション手法が可能です。この手法を説明するには、簡単な例を通じて説明するのが最も良い方法でしょう。

設計しようとしているシステムに、1,000 個の製品の製品ディメンションがあるとします。このうち、上位 5 個の製品が売り上げの 80% を占めているとします (ほぼ均等に分散しているとします)。残りの 995 個の製品が、売り上げの残り 20% を占めています。エンドユーザーのクエリ パターンの分析から、製品に基づく分析は、一般的で有効なパーティション方法であることがわかっています。たとえば、レポートのほとんどには、製品ごとの明細が含まれています。この分析に基づき、6 つのパーティションを作成します。上位 5 個の製品用にパーティションを 1 つずつ作成し、残りの製品用に "汎用の" パーティションを 1 つ作成します。汎用のパーティションを作成するのは簡単です。クエリ バインドで、SQL ステートメントに、次のコードのような WHERE 句を追加します。

上位 5 個のパーティション (1 ～ 5) では、次のコードを使用します。

SELECT * FROM <fact table>
WHERE SK_Product_ID = <SK_TopNthProduct#>

汎用パーティションでは、次のコードを使用します。

SELECT * FROM <fact table>
WHERE SK_Product_ID NOT IN (<SK_TopProduct#>,
                                  <SK_2ndTopProduct#>
                                  <SK_3rdTopProduct#>
                                  <SK_4thTopProduct#>
                                  <SK_5thTopProduct#>)

SQL Server 2000 Analysis Services では、この方法だと多くの管理オーバーヘッドが発生します。SQL Server 2000 では、メンバが何百万個あろうとも、データ スライスでパーティションの各メンバをすべて識別する必要があります。例を実装するには、995 個のメンバが含まれた汎用パーティション データ スライスを作成する必要があります。ディメンションに新しいメンバが追加になるたびにリストを更新するという管理上の課題に加えて、この作業が必要になるわけです。SQL Server 2005 Analysis Services では、パーティション中のデータ スライスの自動バインドにより、管理上のオーバーヘッドがありません。

多数のパーティションを持つシステムを作成する際のもう 1 つの問題が、数百ものパーティションをどうやって作成するかです。SQL Server 2000 では、分析マネージャを使用して 1 つずつ作成するか (非常に時間がかかり、誤りが起こりやすい作業です)、作成を自動化するアプリケーション プログラムを開発者に作成してもらうかのどちらかでした。

SQL Server 2005 では、SQL Server Management Studio によって複数のパーティションを作成でき、一度に数百個のパーティションでも作成することができます。通常、パーティションを作成する際には、データ ソース ビューで定義されたテーブルを使用することを考えるでしょう。結局、データ ソース ビューが存在する理由はそこにあります。しかし、その必要はありません。メジャー グループ Store Sales 内にパーティションを作成し (少なくとも 1 つのパーティションが既に存在し、処理されていることを確認してください)、図 14 に示すようなダイアログ ボックスを調べます。

ダイアログ ボックスでは、既定でデータ ソース ビューが使用されますが、データ ソース内のテーブルとビューをスキャンしたり検索することもできます。図 15 に示すように、データ ソースを選択し、[データの探索] をクリックします。

ダイアログ ボックスには、データ ソース内のテーブルのうち、メジャー グループの "テンプレート" テーブル (またはビュー) に含まれているメタデータ (フィールドとデータ型) に一致したテーブルの一覧が表示されます。図 15 を参照してください。複数の項目を選択すると、チェックが付いている各テーブルに対してパーティションが作成されます。

これには、2 つの重要な副作用があります。1 つ目は、すべてのオブジェクトをデータ ソース ビューから取り込む必要はないということを意味します。プロジェクト REAL では、231 週間分のパーティションすべてを含める必要がないということを意味します。我々は、メジャー グループを作成するにあたり、1 つの "テンプレート" オブジェクトだけを含めました。2 つ目は、一度のステップで何百ものパーティションを作成できることです。これは時間の節約になります。複数のオブジェクトを選択するだけで、1 回の操作で多数のパーティションが作成されます。唯一の要件は、検索操作によって一致するテーブルが見つかるように、少なくとも 1 つのパーティションが処理されていることです。しかし、それだけで、1 度のクリックで数百ものパーティションを作成できます。

格納方法

プロジェクト REAL の作業のこの段階では、すべてのパーティションが MOLAP ストレージを使用しています。通常のテストで見つかった内容に応じてテストを拡大することはありますが、我々はこの分野を積極的にテストしていません。

集計と集計デザイン

集計は、クエリ時間を短縮するためにシステムが使用する、計算済みの小計です。集計自体 (小計) と共に、システムは集計のデザインも保存しています。これは、集計内容を内部的に表したものです。デザインの中には、ディメンションの組み合わせと、小計を計算するレベルが格納されています。集計と集計デザインは、メジャー グループ中のパーティションに格納されます。

SQL Server 2000 Analysis Services では、集計デザインがパーティション自体に格納されていましたが、SQL Server 2005 Analysis Services では、集計デザインはもっとも重要なことです。データベースを表す XMLA スクリプトを見ると、集計デザインがメジャー グループのレベルに格納されていることがわかります。同時に複数の集計デザインがあって構いません。メジャー グループ内で、各パーティションは、使用する集計デザインを指します。集計デザインがないこともあり (そのパーティションで集計が作成されない場合)、すべてのパーティションで集計デザインが同じこともあり、パーティション内で集計デザインが異なることもあります。ほとんどのアプリケーションでは、最初と最後のアプローチを使用します (集計デザインがないか、すべてのパーティションが同じ集計デザインを使用します)。より複雑な設計では、3 番目のアプローチを使用することもあります (パーティション内で異なる集計デザインを使用します)。たとえば、現在の都市のパーティションに対して多数の集計があり、過去 3 年分のパーティションには適度な集計デザインがあり、3 年よりも古いパーティションでは集計がないといった設計も可能です。スクリプトで、パーティションが異なるデザインを指すようにするのは簡単です。XMLA スクリプトを編集するだけで済みます。

処理設定とエラー設定

パーティションをメジャー グループに保持するのに加えて、システムはこれらのパーティションに対する処理設定とエラー設定も記録します。Store Inventory メジャー グループと DC Inventory メジャー グループでは、我々は既定のエラー設定を使用できました。そのため、無効な外部キーを検出するか、NULL やその他のデータの不整合に遭遇すると、システムは処理を停止しました。これは、物理的にすべてのデータが正しいと仮定されるためです。

Store Sales メジャー グループでは、異なるエラー設定を使用する必要がありました。メジャー グループの説明の際に、Customer ディメンションと Store Sales ファクト テーブルの間で見つけたダーティ データについてお話しました。これはデータ フィード間の自然の不整合であることがわかったため、我々は既定のエラー設定を変更しました。

プロアクティブ キャッシュの設定

MOLAP 構造に対する見方の 1 つとして、処理時にはリレーショナル データのキャッシュまたはイメージとして見なすことができます。SQL Server 2005 には、MOLAP キャッシュをいつ再処理するかを制御する新機能が追加されています。いくつかの設定が可能です。たとえば、ハード コーディングされた間隔 (15 分ごとなど)、ストアド プロシージャの戻り値 (ストアド プロシージャを 15 分ごとに実行) を設定できます。また、データが更新されるのを待ってから処理するようシステムに指示することもできます。これらの設定はパーティションに格納されます。そのため、メジャー グループにも含まれています。

現在のプロジェクト REAL システムでは、自動 MOLAP プロアクティブ キャッシュ設定を使用しています。我々は、パフォーマンス テストを実施するときにこれらの設定を調整する予定です。

その他のキューブ オブジェクト

ディメンションの使用、メジャー グループ、パーティションに加え、キューブにはその他のオブジェクトがあります。このセクションでは、プロジェクト REAL のキューブに格納されている、計算、KPI、その他のオブジェクトについて説明します。

計算

現在、プロジェクト REAL システムは、平均の売り上げ金額や手元にある数量を事前に計算しておくなど、さまざまなビジネス メジャーの計算を行います。通常のテストで見つかった内容に応じてテストを拡大することはありますが、我々はこの分野を積極的にテストしていません。

主要業績評価指標

現在、プロジェクト REAL の設計には、主要業績評価指標 (KPI : Key Performance Indicators) が含まれていません。通常のテストで見つかった内容に応じてテストを拡大することはありますが、我々はこの分野を積極的にテストしていません。

アクション

現在、プロジェクト REAL の設計には、アクションが 1 つだけ含まれています。このアクションは、Analysis Services のクライアント ツールと Reporting Services の間の橋渡しとして使用されています。セル レベルのアクションでは、エンド ユーザーがそのアクションを選択すると、現在の座標をパラメータ (現在の商品やベンダなど) としてレポートに渡します。通常のテストで見つかった内容に応じてテストを拡大することはありますが、我々はこの分野を積極的にテストしていません。

パースペクティブ

現在、プロジェクト REAL の設計には、パースペクティブが含まれていません。通常のテストで見つかった内容に応じてテストを拡大することはありますが、我々はこの分野を積極的にテストしていません。

カスタム アセンブリ、ユーザー定義関数 、 MDX スクリプト

現在、プロジェクト REAL の設計には、カスタム アセンブリ、UDF、MDX スクリプトが含まれていません。通常のテストで見つかった内容に応じてテストを拡大することはありますが、我々はこの分野を積極的にテストしていません。

サーバー全体の設定

現在、プロジェクト REAL の設計では、システム全体の設定を既定値から変更していません。唯一の例外として、小規模なシステムのいくつかで、サーバー プロパティ CoordinatorExecutionMode の値を 4 に設定しています。これは、シングル CPU システムまたはデュアル CPU システムで、並列操作が 4 つを超えないようにするためです。Analysis Services では、ディメンションやパーティションの処理など、SQL Server 2000 Analysis Services で逐次処理されていた多くの管理操作を並列に実行しようとするため、問題が発生する可能性があります。Analysis Services では、並列化の度合いを制限する方法が 2 つあります。

並列化の度合いは、要求ごとに制限することができます。それにはまず、パーティションやディメンションなどのオブジェクトを選択します。BI Development Studio でも SQL Server Management Studio でも、複数のオブジェクトを選択できます。Ctrl キーまたは Shift キーを押しながら、操作対象の複数の項目を選択します。オブジェクトを選択し、図 17 に示す [オブジェクトの処理] ダイアログ ボックスが表示されたら、[設定の変更] をクリックします。

次に、たとえば図 18 に示すように、同時に 4 つの要求だけを処理するようにシステムを制限することができます。ドロップダウン リストには、2 の累乗の数だけが含まれていますが、任意の数を入力することができます。

また、処理で使用する XMLA スクリプトで並列化の度合いを制御することもできます。この例を図 19 に示します。

このアプローチの問題点は、オブジェクトを処理するたびに必ず設定する必要があることです。サブオブジェクトが多数ある場合は、非常に難しくなります。たとえば、231 個のパーティションがあるキューブを処理するのは非常に困難です。

実際、処理ダイアログ ボックスで並列化の度合いを 8 に設定し、バッチ ステートメントの内容を参照すると、実行される XMLA バッチ スクリプト中に MaxParallel の設定が反映されています。

また、システム全体の最大同時実行要求数を設定することもできます。これはいくつかの状況で役立ちます (たとえば、毎回忘れずに設定する必要がありません)。しかし、同時に実行する要求の数が少ない場合や、実行する SQL ステートメントのオーバーヘッドが少ない場合には、役に立ちません。

システム全体の設定を変更するには

1. SQL Server Management Studio でサーバーを右クリックし、[プロパティ] を選択します。

2. [CoordinatorExecutionMode] プロパティの値を変更します。

3. [OK] をクリックします。

[プロパティ] ダイアログ ボックスの [再起動] 欄に、このパラメータではサービスの再起動が必要であることが表示されていないことに気付くと思います。新しい値は、直ちに有効になります。

設計の選択肢

ここでは、我々が遭遇した設計上の 2 つの課題について説明します。最初の課題は、設計の中でベンダをどのようにして表現するかです。元の SQL Server 2000 Analysis Services の設計で使用したモデル化手法は、プロジェクト REAL では重大な欠陥がありました。我々はこれを見直し、解決するための 5 つのアプローチを作成しました。

もう 1 つの課題が、インベントリ データに対してどのように計算を行うかです。インベントリ計算の多くは、加法でない (SUM、MIN、MAX、COUNT に基づかない)、手元にある数量と注文済み数量に関係していることに気付きました。我々は、SQL Server 2005 Analysis Services の新しい準加法メジャーを使用してこ問題を解決しました。

ベンダの表現方法

プロジェクト REAL の開始時点の設計では、ベンダは以下の 5 つの SQL Server 2000 Analysis Services 仮想ディメンションでモデル化されていました。

• Return Vendor - 商品を返却するベンダです。

• Purchase Vendor - 商品を購入するベンダです。

• Original Purchase Vendor - 商品が最初に売れたときに購入のために利用したベンダです。

• Source Vendor - 商品が出荷されたベンダです (商品を購入したベンダや返却するベンダと異なる可能性があります)。

• DC Vendor - 商品の流通センターを提供するベンダです。

次の 5 つのセクションでは、SQL Server 2005 Analysis Services を使用してこれらのエンティティをモデル化する 5 つの方法について比較説明します。その中でわかったことは、あらゆる状況でこの種のリレーションシップをモデル化する唯一最善の方法はないということです。どの方法が最善かは、使用可能なストレージ、実行時にどの程度まで計算を許すか、ベンダ分析を実行するために必要な柔軟性に依存します。

アプローチ 1 - 別々の物理ディメンションを作成する

我々が実装した最初のアプローチは、5 個の物理ディメンションを作成し (ベンダごとに 1 つずつ)、ディメンション テーブル Item から読み込むことです。この手法は単純明快で実装が容易です。しかし、いくつかの欠点があります。1 つ目の欠点は、キューブの複雑さが増すことです。エンド ユーザーが使用するディメンションが 5 つ追加になります。ディメンション用のストレージも 5 倍必要になります (各ディメンションは互いに完全に独立しているため)。しかし、この手法の最も大きな欠点は、クロスベンダ分析がまったく行えないことです。たとえば、例としてベンダ "Abrams, Harry N., Inc." を考えます。Abrams 社のメンバは 5 つあるため、5 つのディメンションすべてで Abrams 社の名前のスペルが同じであるという前提で、5 つのディメンションすべてで同じスライサを選択しないかぎり、そのベンダで直接スライスする方法がありません。

アプロ ーチ 2 - 物理ディメンションの代わりに属性階層を使用する

このアプローチでは、1 つ目のアプローチよりも、商品とベンダのリレーションシップが直接的になります。このアプローチでは、物理ディメンションを削除し、Item ディメンション内の 5 つの属性階層またはユーザー定義階層で置き換えます。こうすることで、5 つのディメンションは、Item 階層 Item.Return Vendor、Item.Purchase Vendor、Item.Original Purchase Vendor、Item.Source Vendor、および Item.DC Vendor になります。このアプローチには優れている点がいくつかあります。まず、5 つの階層はすべて同じ属性キーから構築されており、格納場所が 1 つしかありません。その格納場所は Item ディメンションです。そのため、ディメンションはストレージをあまり必要としません。2 つ目に、このアプローチでは、5 つのディメンションを 5 つの属性階層 (またはユーザー定義階層) に変更したため、最終的なキューブの複雑さ (ディメンショナリティ) が減ります。これは、フロントエンド ツールによっては重要であり、エンド ユーザーがキューブ内を移動しやすくなります。しかし、このアプローチではクロスベンダ分析が行えません。

アプローチ 3 - Vendor と Vendor Type に対して多対多のディメンションを作成する

3 番目のアプローチは、3 つのディメンション Item、Vendor、および Vendor Type の間で多対多のディメンションを作成することです。これは特殊なアプローチであり、オーバーヘッドが大きくなりますが、分析のためのセマンティクスが豊富になります。Item ディメンションは、これまでに見てきたものとまったく同じです。Vendor ディメンションは、5 つの異なる種類の考えられるすべてのベンダ (重複を除いたもの) の和集合です。最後に、Vendor Type には、Return、Purchase、Original Purchase、Source、および DC 用にそれぞれ 1 つずつ、合計 5 つのメンバがあります。ディメンションを作成したら、それぞれの商品とその 5 つの種類に対して 1 つのレコードを表すメジャー グループを作成します。たとえば、メジャー グループは図 21 に示すようなものになります。

これを見てわかるように、このメジャー グループはかなり大きくなります。ディメンションは、最大で約 6 百万の商品、商品ごとに 5 つの種類、最大 4 万のベンダになります。そのため、多対多のメジャー グループ (キューブの設計では Item Vendor と呼びます) では、3 千万のレコードになります。

このアプローチの一番の欠点は、ベンダ分析を行うには、分析対象のパーティション データと、多対多のメジャー グループ (これも数百万レコードあります) の、百万対百万のクロス結合を行う必要があることです。これによって、2 番目のアプローチでは数秒かかるクエリが、このアプローチでは大幅に (数十秒) 遅くなります。しかし、このアプローチには大きな利点があります。メンバを表す単一のメンバがあるため、図 22 に示すように、そのメンバで容易にスライスすることが可能であり、直接比較することができます。

これは、5 つのスライサすべてに手動で同じ値を設定しないかぎり、他の手法では不可能です。

アプローチ 4 - 参照または多様ディメンションの使用

4 つ目の手法は、多対多のアプローチに示したように、Item ディメンション テーブルから和集合を作成した単一のメンバを持つ、Vendor という単一のキューブ ディメンションを作成することです。次に、ディメンションを参照または多様ディメンションとしてキューブに 5 回追加します (ロールごとに 1 回)。Vendor ディメンションは、最初は Return Vendor、2 回目は Purchase Vendor というように呼ばれます。このアプローチでもクロスベンダ分析の欠点はありますが、ストレージが最小限で済み、実装が非常に単純明快です。

この手法を使用する場合は、多対多のアプローチと同様に、実際の計算が実行時に行われる点に注意してください。パーティション分割されたデータと多様ディメンションとの間で、結合が行われます。次に、ロールアップが実行されます。そのため、このアプローチでは多対多のアプローチと同じパフォーマンス上の問題がありますが、追加の分析機能はありません。

アプローチ 5 - 参照または多様ディメンションを使用するものの、具現化する

ベンダ をモデル化する 5 つ目の手法は、アプローチ 4 と同様に多様ディメンションを作成するものの、参照ディメンションを指定する際に、タイプを "具現化" として設定する方法です。この設定を行うと、それぞれのロールに対して、ディメンションの個別のコピーが作成されます。これにより、ディメンションのメモリは多く必要になるものの、パフォーマンスが向上します (実行時に結合が行われません)。

ベンダをモデル化するための手法の要約

まとめると、ベンダをモデル化するための 5 つの手法は以下のようになります。

1. ベンダの種類ごとに実際のディメンションを 5 つ実装する。このアプローチでは、ディメンションの数は増えますが、非常に高速で、集計を事前に計算することができます。

2. Item ディメンション内に 5 つの属性階層を実装する。この方法では、ディメンションの複雑さが減り、ベンダと Item ディメンションが関連付けられます (最初のアプローチよりも明確です)。アプローチ 1 とアプローチ 2 には、クロスベンダ分析の点で欠点があります。これは、ロールにかかわらずに一意のベンダを表す単一のメンバがないためです。

3. Item、Vendor、および Vendor Type の間で、多対多のディメンションを実装する。この方法は、実行速度が遅くなりますが、概念的には明解です。メタデータを変更せずにベンダの役割の数を増やすことができます。その結果、構造が柔軟になり、クロスベンダ分析が可能になります。多対多の大規模なメジャー グループがあり、クエリ時の大規模な結合が必要になるため、最も低速なアプローチです。

4. 単一の Vendor ディメンションを持つ、5 つの参照ディメンション (または多様ディメンション) を実装する。これは実装が簡単で、最初の 2 つのアプローチよりも概念的に明解です。このアプローチでは、ロールアップを行う結合を実行時に行う必要があるため、パフォーマンスに影響があります。事前計算のための物理的な集計は行われません。

5. アプローチ 4 を実装しつつ、多様ディメンションを具現化する。これはアプローチ 4 と同様に実装が簡単ですが、各ディメンション メンバが実際に作成されます。これにより、集計のデザインと事前計算が可能になります。アプローチ 4 とアプローチ 5 には、クロスベンダ分析の点で 1 および 2 と同じ欠点があります。これは、ロールにかかわらずに一意のベンダを表す単一のメンバがないためです。ベンダ タイプを頻繁に追加する場合は (6 番目や 7 番目のベンダ タイプを追加するなど)、ベース オブジェクトがあるため、アプローチ 1 や 2 よりもアプローチ 4 および 5 の方が単純明快です。6 番目または 7 番目の参照ディメンションや多様ディメンションを追加するだけで済みます。Vendor ディメンションは既にあります。しかし、4 つのアプローチでは、どれもメタデータの変更が必要であり、既存のレポートや MDX クエリに影響を与えます。メタデータを変更せずに新しいベンダ タイプを追加できるのは、アプローチ 3 だけです。

プロジェクト REAL では、アプローチ 2 および 3 を実装しました。このアプローチの組み合わせを使用したのは、エンド ユーザーが、パフォーマンスと豊富な分析の間で必要なトレードオフに応じて適切なアプローチを選択できるようにするためです。

準加法インベントリ メジャー

店舗および DC の在庫データには、よく知られた問題が内在しています。通常、売り上げ金額や売り上げ数量などのマルチディメンション データを扱う際には、メジャーは加法です。ワシントン州地区の売上金額の合計を得るには、各都市および店舗の売り上げ金額を加算すれば済みます。この計算は加法と呼ばれます。SQL Server 2000 Analysis Services では、4 つの加法関数がサポートされています。その関数とは、Sum、Count、Min、Max です。算術平均 (平均値) は、Sum を Count で割ることで計算されます。

たとえば、図 23 の [Sale Amt] 欄のデータが、ワシントン州カークランドの店舗に対して勤務時間外にロールアップされる様子を見てみます。Sales Amt の集計関数は Sum です。

週の売り上げは、毎日の売り上げの合計であり、月の売り上げのは、週の売り上げの合計です。そのため、売り上げの合計を加法メジャーと呼びます。では、店舗の On-Hand Qty メジャーでもこれが当てはまるでしょうか。答えは明らかに "いいえ" です。手元にある数量、注文済み数量などのメジャーは、準加法メジャーの例です。勤務時間外に報告される値は、合計ではなく、ある期間内に報告された最後の値 (または、準加法関数によって変わるその他の計算) に基づきます。この例では、店舗の在庫は、毎週土曜日に取得されます。カークランドの店舗にある書籍の数が、月の始まりに 10,000 冊あり、月の終わりまでに 4 回ある土曜日にも同じく 10,000 冊の在庫があった場合 (その週に売れたすべての本が金曜日の夕方に補充されると仮定)、月の終わりには在庫はいくらになるでしょうか。40,000 (合計) でしょうか、それとも 10,000 (最後に報告された在庫) でしょうか。

この問題を解決するため、我々は SQL Server 2005 Analysis Services の新しい準加法メジャーを使用しました。時間ディメンションは既にあるため、集計関数を Sum から LastNonEmpty に変更するだけでロールアップが期待どおりに動作するようになりました。

SQL Server 2000 Analysis Services では、加法メジャーだけがサポートされているため、元のプロジェクト REAL の設計では複雑な計算を行っており、これらのロールアップを手動で行っていました。SQL Server 2005 Analysis Services では、準加法演算がネイティブにサポートされています。図 24 に示すように集計関数を変更するだけです。

ベスト プラクティス : 準加法メジャーを使用する場合は、キューブに時間ディメンションを 1 つだけ格納します。

準加法メジャーを使用するには、いくつかの制限があります。まず、準加法メジャーを使用するには、タイプが Time としてマークされたディメンションが必要です。図 25 の例を参照してください。タイム インテリジェンス ウィザードを実行すると、属性とディメンション タイプ プロパティが適切に設定されます。標準のディメンション ウィザードを使用して時間ディメンションを作成する場合は、適切なタイプ プロパティを手動で設定する必要があります。

LastNonEmpty 関数は、すべてのタイプのディメンションに対して計算することはできません。計算できるのは時間ディメンションだけです。また、キューブ内に時間ディメンションが 1 つだけあることが必要です。時間ディメンションに多数の階層があっても構いませんが、キューブ内に存在できる時間ディメンションは 1 つだけです。キューブ内に 2 つ以上の時間ディメンションがあると、システムは最初に見つけた時間ディメンションを使用します。これは必ずしも予測可能な優れた選択とは言えませんが、そのような処理になっています。

まとめ

このホワイト ペーパーでは、プロジェクト REAL で開発された SQL Server 2005 Analysis Services の設計とベスト プラクティスについて、技術的に詳しく説明しました。プロジェクト REAL の中で見つかった多数のベスト プラクティスと所見について説明し、データ ソース、データ ソース ビュー、ディメンション、階層、属性、メジャー グループ、パーティションなど、さまざまな種類のオブジェクトを紹介しました。

リレーショナル パーティション スキームを Analysis Services のメジャー グループ パーティション スキーマと同期させるために使用する SQL Server 2005 Integration Services パッケージについては、付録 A を参照してください。

詳細情報 :
http://www.microsoft.com/japan/sql/

この資料は役に立ちましたか?ご意見をお寄せください。1 (劣) ～ 5 (優) の 5 段階評価で、この資料について採点してください。

付録 A : パーティション作成の自動化

プロジェクト REAL の設計では、パーティションを頻繁に使用しています。実稼働システムには、220 を超える非常に多くのパーティションがあります。サンプル データでは、パーティションあたり数万レコードしかない、125 個強のパーティションを使用しています。完全な実稼働システムでは、パーティションあたり 18 ～ 20 万のレコードが格納されます。これだけ多くのレコードがあると、新しいスキーマを生成するたびに、各バーティションを作成するのに大量の入力が必要になります。

そのため、俗に言うように、"困難が多くなると、プログラマはプログラムを書く" ことになります。

この付録では、SQL Server 2005 Analysis Services データベース中の Analysis Services のメジャー グループの作成を自動化するために我々が作成した、BuildASPartition SQL Server 2005 Integration Services パッケージについて説明します。このパッケージは、リレーショナル パーティション スキームと Analysis Services のパーティション スキームを同期させます。このパッケージは、週のファクト テーブル パーティションを探してリレーショナル データベース中をループします (その際、テーブルの命名規則を使用します)。リレーショナル テーブルが見つかると、Analysis Services のメジャー グループ パーティションが既に存在するかどうかを確認します (同じ命名規則を使用します)。存在しない場合には、それを作成する XMLA スクリプトを作成して実行します。

図 26 にパッケージを示します。

図 27 に変数とその既定値を示します。

次の表に、各変数とその使用目的の説明を示します。

パッケージは以下の 4 つのタスクで構成されます。

• Get Partition List (Execute SQL タスク )

  このタスクは Mask 変数を受け取り、RDBMS データベース接続内で定義されているビューに対する Transact-SQL LIKE パターン マッチとしてその変数を使用します。出力の行セットは Tables 変数に格納されます。図 28 に、パッケージ エディタ内で Get Partition List タスクを構成する様子を示します。

  

  図 28    Get Partition List タスクのプロパティ
  拡大表示する
  
  

• ForEach Partition (For Each Loop タスク )

  このタスクはテーブル中をループし、Table 変数にそれぞれのテーブル名を設定しながら、行セットを繰り返し処理します。図 29 に、パッケージ エディタ内で ForEach タスクを構成する様子を示します。

  

  図 29   ForEach タスクのプロパティ
  拡大表示する
  
  

• Partition Already There? (Script タスク )

  このスクリプトには 2 つの出力があります。パーティションが存在しない場合は、ブール値変数 IsNotPresent を設定し、パーティションを作成するために使用できる XMLA スクリプトを作成します (XMLA Script 変数に格納されます)。

  このスクリプトには興味深い点がいくつかあります。

  • このスクリプトは、AMO を参照し、パーティションが Analysis Services データベースに既に存在するかどうかを決定するために使用する方法を示しています。

  • また、サーバーとデータベース名を接続から抽出する方法を示しています (そのため、後でスクリプト内でこれを使用して、接続に対する AMO コマンドを作成することができます)。

  図 30 に、パッケージ エディタ内で Partition Already There? タスクを構成する様子を示します。

  

  図 30    Partition Already There? タスクのプロパティ
  拡大表示する
  
  

• Create Partition (Analysis Services Execute DDL タスク )

  このタスクは、Partition Already There? タスクで作成された XMLA スクリプトを実行します。図 31 に、パッケージ エディタ内で Create Partition タスクを構成する様子を示します。

  

  図 31    Create Partition タスクのプロパティ
  拡大表示する
  
  

Partition Already There? タスクと Create Partition タスクの間には、優先順位の制約があります。この制約によって、ブール値変数 IsNotPresent が Partition Already There? タスクによって true に設定されている場合にだけ Execute DDL タスクが実行されることが保証されます。優先順位の制約を表示するには、パッケージ エディタ内で 2 つのタスクの間にある矢印をダブルクリックします。図 32 に、パッケージ エディタ内で優先順位の制約が構成されている様子を示します。

このパッケージは、以下の 2 つの接続オブジェクトを使用します。

• AS データベース

  この接続オブジェクトには、SQL Server 2005 Analysis Services データベースが格納されています。メジャー グループ パーティションを確認して存在しない場合は、ここにパーティションが作成されます。

• RDBMS データベース

  この接続オブジェクトには、週ごとのファクト テーブルを検索する SQL Server 2005 リレーショナル データベースが格納されています。

最後に、集計のデザインについて解説したいと思います。パッケージを実行すると、集計デザイン用に集計がデザインされていないことに気付くと思います。多数のパーティションに対して同時に集計デザインを作成するのは非常に簡単です。我々は、数百ものパーティションを使用して同時にこれを行いました。

まず、Ctrl キーまたは Shift キーを押しながら、キューブ ウィンドウに表示されているパーティションを選択します。次に、右クリックしてストレージ デザイン ウィザードを実行し、集計を作成します。ウィザードによって、選択したすべてのパーティションで集計が定義されます。これを、Store Inventory、Store Sales、DC Inventory に対して 1 回ずつ行えば完了です。

以下に Partition Already There? スクリプトのソース コードを示します。

' Microsoft Data Transformation Services (DTS) Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

' NOTE:
' In order to get AMO to be imported you must copy the following
' file from:
'  C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\
'                                       Microsoft.AnalysisServices.dll
' to the folder
'  <windows folder>\Microsoft.NET\Framework\v2.0.<build#>
' And then you must add a reference to the assembly in the project
Imports Microsoft.AnalysisServices

Public Class ScriptMain

    Public Sub Main()

        ' Get Server and Database name from DTS connection object
        Dim oConn As ConnectionManager
        oConn = Dts.Connections("AS database")
        Dim sServer As String = _
            CStr(oConn.Properties("ServerName").GetValue(oConn))
        Dim sDatabase As String = _
            CStr(oConn.Properties("InitialCatalog").GetValue(oConn))

        ' By convention, we know that the database, data source and
        ' cube are the same name
        Dim sDataSource As String = sDatabase
        Dim sCube As String = sDatabase
        Dim oTable As Variable = Dts.Variables("Table")
        Dim sTable As String = CStr(oTable.Value)
        Dim sPartition As String = GetPartition(sTable)
        Dim sMeasureGroup As String = GetMeasureGroup(sTable)

        ' We have all of the information about the partition -- use AMO
        ' to see if it is present. Save in a variable for later reference
        Dts.Variables("IsNotPresent").Value = _
            Not IsPartitionThere(sServer, sDatabase, sCube, _
                                    sMeasureGroup, sPartition)

        ' Generate and save the XMLA script (will be executed in a 
        ' Execute Analysis Services DDL task later on). Save in a
        ' variable for later reference
        Dim sXMLA As String = GenerateXMLAScript(sDatabase, sDataSource, _
                                sCube, sMeasureGroup, sPartition, sTable)
        Dts.Variables("XMLA_Script").Value = sXMLA
        MsgBox(sXMLA, MsgBoxStyle.OkOnly, "XMLA Script")

        Dts.TaskResult = Dts.Results.Success

    End Sub

    Private Function GetMeasureGroup(ByVal sMG As String) As String

        ' All tables are in the format: vTbl_Fact_<mgname>_WE_YYYY_MM_DD
        '   e.g. vTbl_Fact_Store_Sales_WE_2003_12_27
        ' Measure group names (from this) are: <mgname>, e.g. Store Sales
        '   and the name may have embedded undorscores (_) which need to
        '   be replaced with spaces
        Dim i_WE_location As Integer = InStr(sMG, "_WE_")
        Dim i_FACT_location As Integer = Len("vTbl_Fact_")
        sMG = Left(sMG, i_WE_location - 1)
        sMG = Right(sMG, (Len(sMG) - i_FACT_location))
        sMG = Replace(sMG, "_", " ")

        Return sMG

    End Function

    Private Function GetPartition(ByVal sPart As String) As String

        ' All tables are in the format: vTbl_Fact_<mgname>_WE_YYYY_MM_DD
        '   e.g. vTbl_Fact_Store_Sales_WE_2003_12_27
        ' Partition names (from this) are: <mgname> WE YYYY MM DD,
        '   e.g. Store Sales WE 2003 12 27
        '   and the name may have embedded undorscores (_) which need to
        '   be replaced with spaces
        Dim i_FACT_location As Integer = Len("vTbl_Fact_")
        sPart = Right(sPart, (Len(sPart) - i_FACT_location))
        sPart = Replace(sPart, "_", " ")

        Return sPart

    End Function

    Public Function GenerateXMLAScript(ByVal sDatabase As String, _
        ByVal sDataSource As String, ByVal sCube As String, _
        ByVal sMeasureGroup As String, ByVal sPartition As String, _
        ByVal sTable As String) As String

        Dim sX As String
        sX = ""

        '
        ' XMLA script is missing the following clauses:
        ' 1) annotations (not needed)
        ' 2) physical storage, e.g. proactive caching settings, etc. 
        '        Note: not needed since default values used
        ' 3) linkage to aggregation designs (TBD) -- we need a management
        '        utility to control aggregation usage
        '
        ' Note: because of quoting rules conflict between VB.NET and
        '       XMLA scripts, all double-quotes (") are replaced with
        '       uparrows (^)
        '

        sX = sX & "<Create xmlns=^http://schemas.microsoft.com/"
        sX = sX & "analysisservices/2003/engine^>" & vbCrLf
        sX = sX & "    <ParentObject>" & vbCrLf
        sX = sX & "        <DatabaseID>" & sDatabase & _
                                        "</DatabaseID>" & vbCrLf
        sX = sX & "        <CubeID>" & sCube & "</CubeID>" & vbCrLf
        sX = sX & "        <MeasureGroupID>" & sMeasureGroup & _
                                        "</MeasureGroupID>" & vbCrLf
        sX = sX & "    </ParentObject>" & vbCrLf
        sX = sX & "    <ObjectDefinition>" & vbCrLf
        sX = sX & "        <Partition xmlns:xsd=^http://www.w3.org/"
        sX = sX & "2001/XMLSchema^ xmlns:xsi=^http://www.w3.org/2001/"
        sX = sX & "XMLSchema-instance^>" & vbCrLf
        sX = sX & "            <ID>" & sPartition & "</ID>" & vbCrLf
        sX = sX & "            <Name>" & sPartition & "</Name>" & vbCrLf
        sX = sX & "            <Source xsi:type=^TableBinding^>" & vbCrLf
        sX = sX & "                <DataSourceID>" & sDataSource & _
                                        "</DataSourceID>" & vbCrLf
        sX = sX & "                <DbSchemaName>dbo</DbSchemaName>" & _
                                        vbCrLf
        sX = sX & "                <DbTableName>" & sTable & _
                                        "</DbTableName>" & vbCrLf
        sX = sX & "            </Source>" & vbCrLf
        sX = sX & "        </Partition>" & vbCrLf
        sX = sX & "    </ObjectDefinition>" & vbCrLf
        sX = sX & "</Create>" & vbCrLf
        sX = sX & " " & vbCrLf

        ' replace all up-arrows with double-quotes
        sX = Replace(sX, "^", """")

        Return sX

    End Function

    Public Function IsPartitionThere(ByVal sServer As String, _
        ByVal sDatabase As String, ByVal sCube As String, _
        ByVal sMeasureGroup As String, _
        ByVal sPartition As String) As Boolean

        ' By default, we will assume that it isn't there
        ' Only if we get all of the way to the end and everything is
        ' found, will we set it true
        Dim bIsPartitionThere As Boolean = False

        Dim oServer As New Microsoft.AnalysisServices.Server
        ' connect to the server and start scanning down the
        ' object hierarchy
        oServer.Connect(sServer)

        Dim oDB As Database = oServer.Databases.FindByName(sDatabase)
        If oDB Is Nothing Then
            MsgBox("Did not find expected database: " & sDatabase, _
                    MsgBoxStyle.OkOnly, "Error looking for partition")
            GoTo Done
        Else
            Dim oCube As Cube = oDB.Cubes.FindByName(sCube)
            If oCube Is Nothing Then
                MsgBox("Did not find expected cube: " & sCube, _
                        MsgBoxStyle.OkOnly, "Error looking for partition")
                GoTo Done
            Else
                Dim oMG As MeasureGroup = _
                            oCube.MeasureGroups.FindByName(sMeasureGroup)
                If oMG Is Nothing Then
                    MsgBox("Did not find expected Measure Group: " & _
                            sMeasureGroup, _
                            MsgBoxStyle.OkOnly, _
                            "Error looking for partition")
                    GoTo Done
                Else
                    '-- This is the real test -- to see if the partition
                    '   is really there
                    Dim oPart As Partition = _
                        oMG.Partitions.FindByName(sPartition)
                    If Not oPart Is Nothing Then
                        ' This is the only place to set the value to TRUE
                        bIsPartitionThere = True
                    End If
                End If
            End If
        End If

Done:
        oServer.Disconnect() ' disconnect from the server -- we are done

NoConnection:
        Return bIsPartitionThere

    End Function

End Class

ダウンロード

Project REAL ETL Design Practices.doc (英語)
2.24 MB
Microsoft Word ファイル