SQL Server 2005
Analysis Services によるリアルタイム ビジネス インテリジェンス
公開日: 2005年10月6日
Paul Sanders
このドキュメントでは、今日の企業が直面しているリアルタイム ビジネス インテリジェンス データの収集および分析を妨げる障害を克服する SQL Server 2005 Analysis Services の機能について説明します。
トピック
はじめに
ビジネス インテリジェンス (BI) システムの目的は、より多くの情報に基づいた、より的確で、より迅速な意思決定を可能にすることです。したがって、リアルタイム ビジネス インテリジェンスに関する議論は、このような意思決定を実現するために、情報をどの程度最新のものに近づける必要があるかに集中することになります。リアルタイム情報の構成要素は、1 つの企業の内部でも、事業活動によって大きく異なる可能性があります。たとえば、ある小売のチェーン店について考えてみます。
| • | 次期販売予測の入力として過去のデータを使用するアナリストには、先月までの情報だけが必要と考えられます。 |
| • | キャンペーンの成功を評価し、キャンペーンの継続期間を決定するマーケティング マネージャには、おそらく 1 日前までのさらにタイムリーな情報が必要と考えられます。 |
| • | 各店舗のストア マネージャは、生鮮食品を販売するタイミングの決定など、1 日に何度も意思決定を行っている可能性があります。このマネージャには、賞味期限が切れてから数時間しか経過していないという確実な情報が必要と考えられます。 |
最新の情報と過去の情報を何度も組み合わせる必要があります。たとえば、ストア マネージャにとって、特定の商品の今朝までの販売数を知ることは有益かもしれませんが、その情報と、同じ商品の去年 1 年間の同じ曜日の平均販売数を比較できた方がより有益です。
レポートには、複数のシステムからのデータを含めることができます。小売店の例では、当日の店舗の売上に関するデータは、販売時点で直ちに更新されるソース システムから取得され、企業のデータ ウェアハウス (DW) 内の過去のデータと統合されます。しかし、これは "たった 1 つの真実" がもはや存在しないことを意味します。2 つのソースから取り出されたデータを比較する場合は、データ ウェアハウスのロード中に強制されたものと同じビジネス ポリシーを確実に適用するように、十分な注意を払う必要があります。このビジネス ポリシーには、適用された統合、値引き処理、および通貨換算に対するアプローチが含まれます。これは、DW のロード プロセスに組み込まれているビジネス ルールを、2 つのシステムからのデータを統合するレポートまたはアプリケーションにも組み込む必要があることを意味します。結果として、精度の低い、エラーの多いシステムになる可能性があります。また、誤った情報に基づいて決定を下してしまうことも考えられます。
さまざまなシナリオの中のあらゆる意思決定者を支援できるほど、十分にリアルタイムに近いデータを生成する単一のソースを持つことが理想的です。では、ビジネス インテリジェンス (BI) システムが真なるリアルタイムのデータ提供を妨げている障害とは、何でしょうか。次の節で考えてみましょう。
リアルタイム BI の実現を妨げている障害
リアルタイム BI データの取得には困難な課題が数多くあり、その中にはテクノロジでは解決できないものが含まれていることを、重視する必要があります。多くのエンタープライズ インフォメーション インターチェンジ (EII) テクノロジの提示が行われ、問題の範囲が矮小化されたため、このことに留意しておくことは重要です。真なるリアルタイムのデータ取得を妨げている障害には、以下のようなものがあります。
| • | 過去の情報を提供する必要があります。元のソース システムが過去のデータをまったく提供していないケースがよくあります。そのため、過去のデータの完全なコピーを DW 内に保存しておくことが必要です。 |
| • | ビジネス プロセスとシステム全体で整合性を図る必要があります。特定のビジネス プロセスが完了しないうちに特定の分析を行っても、意味を持たないことがあります。たとえば、単純な小売チェーンの例では、すべての店舗の閉店時間にならないうちに、店舗間の比較をしても意味がないかもしれません。同様に、一部のレガシ システムを更新するメカニズムによって、待ち時間に対して、実現可能な時間よりも低い上限が設定されるかもしれません。 |
| • | データは、頻繁にトランスフォーメーション プロセスを通すことによって、その質を向上させる必要があります。トランスフォーメーションの複雑さとデータの容量にもよりますが、ある程度のレベルまでロード時間を短縮できるほど、簡単にコスト効率は上がらないかもしれません。 |
| • | 大量のトランスフォーメーションが行われなくても、通常のトランスフォーメーション プロセスによって処理された結果としてストアが個別に作成されるときは、複数のデータ ソースからのデータの統合を幾度も実行する必要があります。 |
| • | 多くの場合、十分なユーザー パフォーマンスを実現する目標と、結果を真にリアルタイムで得ることとは両立しません。ソース システムから直接レポートすればリアルタイム情報が確実に提供されますが、一般的な集計レベルのクエリは、頻繁に更新される傾向にあるシステムに対して十分なパフォーマンスを発揮できません。また、トランザクション システム上で、このようなクエリ負荷を許容することはできません。そのため、(Analysis Services などを使用した) 予測集計値の提供に特別の注意を払いながら、個別のストアを保守する必要があります。ソース データが更新されるたびにこれらの集計値を保守するのは、時間がかかります。 |
| • | 絶え間なく更新されるシステムからのデータに関するレポートを連続的に行うには、データ整合性の問題を考慮する必要があります。 |
| • | レポート内に十分に最新なデータを含めることができたとしても、そのレポートを必要としている意思決定者のもとに即座に届けられることをどうしたら保証できるでしょうか。 |
このような考え方は、図 1 に示すように、ほとんどのユーザーが元のソース データから離れた場所にいることに基づくものです。チェーン内のリンクごとに、追加的な時間遅延が発生します。
図 1 リアルタイム BI を妨げる障害
各店舗のソース トランザクション システムは、POS レジスタから直接更新されます。毎日、すべての店舗からのデータが集められ、データを整理するために多数のトランスフォーメーションが適用されて、DW にロードされます。ロードが完了すると、すべてのユーザー アクセスに使用される Analysis Services キューブを加工するための再処理が開始されます。キューブは、ステージング サーバー上で加工された後、有効性が確認されてから、運用サーバーに転送され、ユーザーへのレポートに使用されます。キューブがリフレッシュされると、担当マネージャがそのキューブに対するレポートを実行し、変更された情報を確認して、(最終的な) アクションが開始されます。
障害の克服
テクノロジでは解決が困難な障害もありますが、SQL Server Analysis Services と、SQL Server 製品のその他のコンポーネントは、よりタイムリーな情報をエンド ユーザーに提供することを可能にする多くの機能を備えています。
| • | Analysis Services へのデータのプッシュ転送 変更による Analysis Services の更新タスクは、データを直接 Analysis Services にプッシュ転送できる SQL Server Integration Services (SSIS) トランスフォーメーションを追加することによって、より簡単に実現できます。これは、データの抽出や整理を行うタスクによって、直接キューブをリフレッシュできることを意味します。 |
| • | 異種データ ソースの統合 Analysis Services キューブは、複数の異種ソースから取り出されたデータで構成することができます。データは、Analysis Services によって統合され、一体的にユーザーに提供されます。 |
| • | 一貫性のあるデータの読み込み SQL Server 2005 リレーショナル エンジンには、データのスナップショット ビューの取得を可能にする、付加的なトランザクション分離レベルが含まれます。この分離レベルは、Analysis Services によって利用されます。これは、プロセスに時間がかかったとしても、キューブをとおして一貫性のあるデータ ビューを確認できるように、キューブが処理されることを意味します。 |
| • | Analysis Services キューブの増分更新 Analysis Services 2000 では、パーティションを使用して、実際に影響を受ける部分をキューブの一部に限定することによって、Analysis Services キューブのリフレッシュに必要な時間を短縮できます。さらに、新しいファクト行が既存のキャッシュに追加されるように、キューブを増分的に処理することができます。Analysis Services 2005 では、この Analysis Services 2000 の機能に加えて、ディメンションの増分更新を可能にし、新しいディメンション レコードが追加されるだけで更新されることのないケースを想定しています。 |
| • | キャッシュ ポリシーの定義 Analysis Services 2005 では、キューブをリフレッシュし、変更をソース データに反映させる方法を定義する機能が導入されました。このプロアクティブ キャッシュ ポリシーによって、最新データに対するビジネス要求と高パフォーマンスに対するニーズのバランスを図ることができます。 |
| • | Analysis Services からの通知の受信 Notification Services との統合によって、関心のあるデータ変更に対してサブスクライブすることが可能です。たとえば、マネージャは、興味を引く主要業績評価指標 (KPI : Key Performance Indicator) に対してサブスクライブし、その KPI のステータスが変化したときに電子メールで通知を受け取ることができます。 |
ここに挙げたテクノロジは、単独でまたは組み合わせて利用できます。多くの場合、これらのテクノロジによって、リアルタイム BI の提供が容易になります。以下に、単純なケースに適用できる簡易化したアーキテクチャを例示します。この例では、Analysis Services キューブが直接ソース データベース上に構築されています。
図 2 簡易化したアーキテクチャ
メモ このように単純なケースでも、実際のソース OLTP データベースのレプリケーションであるデータベース上でキューブを構築する場合の参考になります。このようなレプリカの保守コストは、トランザクション システムが余分なオーバーヘッドから開放されることから生じるメリットによって相殺されます。
各店舗のソース トランザクション システムは、POS レジスタから直接更新されます。プロアクティブ キャッシュ ポリシーによってキューブが 20 分ごとに増分的にリフレッシュされ、新しいデータを反映することが保証されます。ユーザーがステータスの変化した KPI にサブスクライブしていた場合は、該当するレポートへの URL を含む電子メール メッセージがユーザーに直接送信されます。
このドキュメントの後半では、Analysis Services 2005 に新しく追加された機能を中心に、機能の詳細について説明します。
Analysis Services へのデータのプッシュ転送
SQL Server 2005 では、SSIS により、データを読み込んで一連のトランスフォーメーションをとおして変換してから転送先に出力するパイプライン タスクが採用されています。Analysis Services は、変換されたデータをキューブに直接転送することを可能にする転送先の 1 つに含まれます。この場合、パーティションおよびディメンションをロードする機能と、データを増分的にロードする機能の実現が可能です。ディメンションを増分的にロードする機能は、Analysis Services 2005 の新機能として追加されたものです。この機能は、特に、緩やかに変化するディメンションのタイプ 2 のように、ディメンション行が追加されるだけで更新されないケースに関係します。
一貫性のあるデータの読み込み
SQL Server 2005 では、"スナップショット分離" として知られている新しいトランザクションの分離レベルを採用しています。このトランザクションを開始するクライアントに対しては、他のユーザーが同時に更新をコミットした場合でも、トランザクションの途中で読み込んだデータがトランザクションの開始時点と同じものであることが保証されます。
データが変化する状況でキューブを処理する場合は、他のユーザーによる変更から分離されていない限り、問題が発生する可能性があります。たとえば、製品のディメンションが処理され、パーティション処理が完了する前に、その製品の売上と共に新しい製品が入力されたとします。その後、売上がパーティション処理で取得されると、明らかな参照整合性エラーが発生します。
Analysis Services 2005 では、開発者の判断で、スナップショット分離を利用できます。データ ソースにこのポリシー セットが適用されている場合は、Analysis Services が処理の開始と同時にスナップショット トランザクションを開始して、すべてのオブジェクトが同じスナップショットから処理されます。スナップショットの取得には、それほどオーバーヘッドは生じませんが、トランザクション途中の更新によるわずかなオーバーヘッドでコストが発生することに注意してください。
異種データソースの統合
Analysis Services では、複数のデータ ソースからキューブを構成することができます。たとえば、製品データと売上データはメインのエンタープライズ DW から取り出し、数量は小規模の部門別データベースから取り出すとします。Analysis Services は、異なるソース上のデータを結合するために必要なクエリを発行します。
Analysis Services は SQL Server の分散クエリ プロセッサを利用するため、いずれかのデータ ソースを SQL Server にすることが必要です。SQL Server データベースには、データを含める必要はありません。たとえば、2 つの Oracle データベースからのデータを 3 つ目の SQL Server データベースのクエリ プロセッサを使って結合することができます。
キャッシュ ポリシーの定義
Analysis Services は、予測集計値を含む分析クエリに最適な形式で、元になるデータのキャッシュを維持することによって、高いクエリ パフォーマンスを実現します。このようなキャッシュの存在によって、次のような重要な疑問が発生します。
| • | 元になるデータが変化して、キャッシュが古くなったときにどうすべきか。 |
| • | キャッシュはどのくらいの頻度でリフレッシュすべきか。キャッシュのリフレッシュを実行中に、クエリにどのように応答すべきか。つまり、古いキャッシュを使用して応答するか、または (クエリ パフォーマンスは低下するが) 最新のデータを持つ元のデータ ソースに戻って応答するか。 |
| • | ソース データが変更されたことをどのように把握するか。どのくらいの頻度でチェックするか。 |
Analysis Services 2005 の新しいプロアクティブ キャッシュ機能は、キューブの設計担当者に、パフォーマンスに対するビジネス ニーズとリアルタイム データに対するビジネス ニーズのバランスを図るポリシーの定義手段を提供します。システムは、"オートパイロット" で動作するため、明示的に処理する必要はありません。
有効なポリシー設定によって、さまざまなシステム動作が可能になります。次の例は、ソース データへの更新が発生した直後にキューブのマルチ ディメンション OLAP (MOLAP) キャッシュのリフレッシュが開始するようにポリシーが指示するキューブを示したものです。この例では、最新データに対する高いニーズに基づき、キャッシュのリフレッシュが短時間で完了しなかった場合は、システムはリレーショナル OLAP (ROLAP) モードに戻り、キャッシュのリフレッシュが完了するまで、元になるデータベースに対してクエリを直接送信します。
1. キューブは、リレーショナル データベースのデータから処理されます。キューブに対するすべてのユーザー クエリが、MOLAP キャッシュの使用によって解決されます。 | |
2. 元になるデータベースに対する更新が発生します。遅れて、Analysis Services がイベント経由で通知を受け取ります。 | |
3. 新しい MOLAP キャッシュを再構築する処理が開始されます。新しいキャッシュの構築中は、古い MOLAP キャッシュを使ってユーザー クエリに応答が返されます。 | |
4. 一定時間が経過しても、まだ、新しい MOLAP キャッシュが完成していません。ポリシーによって、許容可能な最大遅延が指示されます。この上限値に達すると、システムは ROLAP モードに戻り、すべての新しいユーザー リクエストは、元になるリレーショナル ソースに対して最新データを要求する SQL クエリの発行によって解決されます。 | |
5. 最終的に、新しい MOLAP キャッシュが構築され、システムは元の MOLAP モードに戻ります。 | |
ポリシーが異なると、動作も大きく異なります。たとえば、次のようになります。
| • | 自動 MOLAP 一貫した高パフォーマンスに対するニーズがリアルタイム データに対するニーズを上回るため、動作は、システムが ROLAP モードに戻らないことを除いて図 3 のようになります。 |
| • | 定期 MOLAP ソース データに対する更新とは無関係に、キューブは定期的にリフレッシュされます。 |
| • | 増分 MOLAP キューブは、ソース データベース内の新しいデータによって増分的にリフレッシュされます。 |
| • | リアルタイム ROLAP キューブは常に ROLAP モードであり、ソース データに対するすべての更新は即座にクエリ結果に反映されます。 |
プロアクティブ キャッシュ設定は、Analysis Services オブジェクト (ディメンションとパーティション) ごとに設定できます。これによって、パフォーマンスと遅延のトレードオフおよびポリシーがキューブの部分によって異なる場合に、優れた柔軟性が提供されます。
プロアクティブ キャッシュ設定については、「付録」でさらに詳しく説明します。
通知スキーム
Analysis Services が元になるソースに対する更新の通知を受け取ることのできる通知スキームには、次の 3 つがあります。
| • | トレース イベント 元になるデータ ソースが SQL Server である場合にのみ適用されます。Analysis Services が、対象テーブルで発生したトレース イベントを受信するように登録します。このスキームでは、Analysis Services のサービス アカウントが SQL Server データベース上の管理者特権を持っていることが必要です。ネットワーク上の問題などで更新時にデータベースにアクセスできない場合は、イベントの配信は保証されません。 |
| • | クライアント主導 クライアントが、特定のテーブルが更新されたことを知らせるメッセージを Analysis Services に送信できます。このスキーマは、実際に更新を実行しているアプリケーションが、Analysis Services キューブ上の影響を認識している場合に利用されます。 |
| • | ポーリング 最も応用範囲の広いアプローチは、ポーリングを使用することです。ポーリング クエリはテーブルごとに定義され、テーブルに対する更新のステータスを示す単一値 (つまり、単一行の単一列の値) を返します。たとえば、LastUpdated タイムスタンプ列の最大値を返すクエリは、ポーリング クエリに該当します。設計担当者は、クエリと共に、Analysis Services がポーリング クエリを送信する頻度も設定します。 |
増分処理
キャッシュがリフレッシュされるときは、通常、キャッシュ全体をリロードするように、影響を受ける Analysis Services オブジェクトが処理されます。もっと正確に言うと、ディメンションはプロセス オプションの "Update" を使用しますが、パーティションはプロセス オプションの "Full" を使用します。これらのオプションによって、関連するパーティション データの保存が保証されます。ただし、行が追加されるだけで更新されないことがわかっている場合は、パーティションとディメンションのいずれにも増分処理を実行することもできます。
この機能は、ポーリング通知スキームを利用している場合に使用できます。設計担当者は、更新が発生したことを確認するポーリング クエリと共に、更新された行を返す増分処理クエリも発行できます。このクエリはパラメータ化されており、ポーリング クエリによって返される新旧の値を受け取ります。「付録」で例を示します。
Analysis Services からの通知の受信
Notification Services は、SQL Server に新たに追加された機能です。このサービスは、クライアントが、関心のある特定のイベント (クエリの値の変更など) にサブスクライブしたり、イベントが発生したときに通知を受け取る方法 (電子メールやインスタント メッセンジャーなど) を定義したりするためのスケーラブルなインフラストラクチャを提供します。また、Notification Services は、標準で、通知が確実に配信されることと、通知はイベントごとに 1 回だけ発生することを保証します。たとえば、KPI が "Ok" から "Bad" に変更された場合、KPI が "Bad" ステータスである間はユーザーは通知を 1 回しか受け取りません (繰り返し受け取ることはありません)。
SQL Server 2005 の Notification Services には、Analysis Services 用の特別なアダプタが含まれています。このアダプタを使用すると、クライアントは、ポーリングの頻度や通知スキーマなどと共に、興味を引くデータを定義するための MDX クエリを設定することによって、イベントに対してサブスクライブできます。たとえば、ストア マネージャは、特定の製品の時間単位の売上が指定レベルを下回った場合の通知を要求できます。
このように、ユーザー指定のイベントが発生したときにタイムリーな通知を受け取ることを保証する BI アプリケーションの構築が容易になります。
まとめ
Analysis Services 2005 は、他の SQL Server 2005 製品と連携して、リアルタイム BI データを取得するために適用可能な多くの機能を提供します。たとえば、次のような機能があります。
| • | キャッシュ ポリシーを定義し、集計値を明示的に再処理せずに自動パイロット モードでシステムを実行する機能によって、一連の集計値の保守が容易になります。 |
| • | Analysis Services をトランスフォーメーションから直接ロードする機能と、新しいレコードを増分的に追加する機能によって、効率が改善されます。 |
| • | スナップショット分離によって、データが変化している最中でも、一貫性のあるビューを取得できる機能が提供されます。 |
| • | 複数のソースからのデータを統合する Analysis Services キューブの機能によって、複数の異種ソースからのデータ統合が容易になります。 |
| • | Notification Services によって、重要なイベントをユーザーに通知する機能が提供されます。真なるリアルタイムの BI データを取得するには多くの障害がありますが、これらの機能を単独でまたは組み合わせて使用することで、ビジネスに対するより優れた意思決定を、より迅速に下すことを支援する、十分にリアルタイムに近い情報を取得できます。 |
付録
ここでは、プロアクティブ キャッシュ設定、およびプロアクティブ キャッシュを使ってキューブを増分的にリフレッシュするためのポーリング クエリと増分処理クエリの使用方法について詳しく説明します。
プロアクティブ キャッシュ設定
SilenceInterval と SilenceOverrideInterval | 新しいキャッシュの処理中に新しい更新が発生した場合は、通常、処理がキャンセルされて再起動されます。したがって、元になるデータベースに対する更新がバッチで行われることがわかっている場合は、バッチ内の最初の更新ですぐにキャッシュのリフレッシュを開始することは無駄になります。SilenceInterval には、更新からリフレッシュの開始までの非表示期間を設定します。SilenceOverrideInterval には、スリープ期間の最大値を設定します。この時間が経過した後、別のスリープ期間が設定されていなければ、強制的にリフレッシュが開始されます。Silence Interval の値を "infinite" に設定すると、処理は、更新イベントではなく ForceRebuildInterval 設定に基づいて実行されます。 |
ForceRebuildInterval | ForceRebuildInterval が設定されていない状態で、新しいキャッシュの処理中に別の更新が発生した場合は、処理がキャンセルされて再起動されます。ForceRebuildInterval には、更新の有無にかかわらず、最後にキャッシュがリフレッシュされてから新しいキャッシュのリフレッシュが開始して完了するまでの時間間隔を設定します。これによって、連続的に更新が行われる状況下での連続的な再起動を回避する方法が提供されます。ForceRebuildInterval と、値を "infinite" に設定した SilenceInterval を併用すれば、キャッシュの単純な定期リフレッシュが可能になります。 |
Latency | Latency には、キャッシュが古くなって ROLAP モードに戻るまでの時間を設定します。整合性を持った高パフォーマンスが要求される場合は、Latency の値を "infinite" に設定し、クエリが常に MOLAP キャッシュを使用するようにします。 |
OnlineMode | OnlineMode では、初期処理中に、初期キャッシュが構築されていなくても、すぐにキューブを ROLAP モードでオンライン公開するかどうかを制御します。 |
AggregationStorage | ROLAP キューブの場合、集計値は、通常、元になるデータベース内に組み込まれている "具体化されたビュー" に保存されます。この設定は、ROLAP に戻ろうとしている MOLAP キューブの場合にも、このようなビューが構築されるかどうかを制御します。 |
プロアクティブ キャッシュを使用した増分処理
以下の表は、ファクト テーブル内の新しい売上行の存在を検出し、その行を増分的にパーティションに追加するために使用する、ポーリング クエリおよび関連する増分処理クエリの例を示したものです。
ポーリング クエリ | SELECT max(LastUpdated) from Sales |
増分処理クエリ | SELECT * from Sales WHERE LastUpdated > coalesce(?, -1) AND LastUpdated <= ?. メモ "coalese" は、起動時に以前の値を NULL にするために必要です。 |
例 | 最後のポーリング クエリでは、"max(LastUpdated)" の値が "3/18/2005 1pm" でした。 次のポーリング クエリで、この値が "3/18/2005 1.15 pm" に変わります。 Analysis Services によって以下のクエリが送信され、新しい行が処理されます。 SELECT * from Sales WHERE LastUpdated > coalesce(3/18/2005 1pm, -1) AND LastUpdated <= 3/18/2005 1.15 pm |
