| 【3-1】ストレージのデザイン
昨日（前回）は「売上分析」キューブを作成しましたが、このキューブの「ストレージのデザイン」を設定していません。「ストレージのデザイン」を行わなくても「キューブの処理」は行うことができるので、キューブのデータを参照することはできますが、実データでのキューブ作成を考えると「ストレージのデザイン」を把握しておいたほうがよさそうです。
そこで、A君は「ストレージのデザイン」を行ってみることにしました。
分析マネージャの「売上分析キューブ」を右クリックして、「ストレージのデザイン」を選択すると、「ストレージデザインウィザード」が起動します。(その他の操作でも「ストレージデザインウィザード」を開くことができます。)
＜画面1：ストレージデザインウィザード＞
ウィザードを開始すると「データストレージの種類の選択」を行うことになります。
＜画面2：データストレージの種類の選択＞
ここでは、MOLAP／ROLAP／HOLAPからストレージの種類 (データの持ち方) を選択することができます。MOLAP／ROLAP／HOLAPの特徴を整理しておきましょう。
MOLAP
(Multi-dimensional OLAP)
| 格納先 |
リレーショナルデータベース |
多次元データベース |
| 詳細データ |
|
○ |
| 集計データ |
|
○ |
キューブから直接読み取れるので検索速度は速い。
全てのデータをキューブ（多次元データベース）に格納するため、キューブサイズが大きくなる。
ROLAP (Relational OLAP)
| 格納先 |
リレーショナルデータベース |
多次元データベース |
| 詳細データ |
○ |
|
| 集計データ |
○ |
|
リレーショナルデータベースからデータを読み出す必要があるため、検索速度は遅くなる。
キューブにはデータを持たない。（集計の定義を持つ。）
HOLAP (Hybrid OLAP)…MOLAPとROLAPのハイブリッド(複合型)
| 格納先 |
リレーショナルデータベース |
多次元データベース |
| 詳細データ |
○ |
|
| 集計データ |
|
○ |
集計データの検索速度は、MOLAPと同じ程度の速さ。
詳細データの検索速度は、ROLAPと同じ程度の遅さ。
集計データだけをキューブに持つので、キューブサイズはMOLAPに比べて小さい。
ストレージの種類はどれを選ぶ？
ストレージの種類 (MOLAP／ROLAP／HOLAP) は、主に以下を考慮して選択します。
- 検索速度に関する要求
- 集計データ／詳細データの参照頻度
- キューブの規模 (ディメンションの数やファクトデータの量)
例えば、キューブの規模が小さく高速な検索速度が要求される場合は、MOLAPが適しています。
ある程度キューブの規模が大きく詳細データはほとんど参照されないようなケース場合は、HOLAPが適しています。
ROLAPは検索速度の面では有利には働きませんが、キューブの規模が大きく参照頻度がかなり少ない場合に選択することで、ストレージを節約できます。また、SQL
Server Enterprise Editionで使用できる「リアルタイム更新」機能を採用する場合はROLAPを選択します。
|
今回は、少量のサンプルデータで作成しているのでMOLAPを選択することにします。
次は、集計オプションの設定です。ここでは、集計のストレージサイズ (あまり大きくならないように) と、検索速度 (あまり遅くならないように)
とのバランスを調整します。
＜画面3：集計オプションの選択＞
ストレージの見積もりサイズや要求するパフォーマンス到達率 (※1) を設定してから「開始」を押すと、ストレージサイズの使用量と確保されるパフォーマンスの到達率の関係が予測され、右側のグラフに表示されます。
※1 パフォーマンス到達率 (Books On
Lineから)
Books On Lineによると、「パフォーマンス到達率」はキューブに対する
「クエリの最長実行時間と最短実行時間の差に対する向上率」
と説明されており、以下の式で定義されています。
パフォーマンス到達率 = (最長実行時間−目標の実行時間)
÷ (最長実行時間−最小実行時間) × 100
事前集計を全く行っていない場合に平均22秒程度、十分に事前集計を行っておいた場合に平均2秒程度かかるとしたときに、パフォーマンス到達率75%を設定すると
75 = (22−目標の実行時間) ÷ (22−2) ×
100
この式から、クエリの平均的な実行時間が7秒程度になるように集計をデザインする、ということです。
今回は細かいことは気にせずに、“事前集計しておく割合”ぐらいに捉えておきましょう。 |
それではデフォルトのまま (ストレージサイズ100MByte) 、「開始」してみます。
＜画面4：集計オプションの選択 (100MByetで予測)＞
サンプルデータの件数が少ないため、0.6MByteの使用で100%のパフォーマンス到達率が確保されると予測されました。
もし、ストレージサイズが大きすぎる場合は、「リセット」ボタンを押して現在のデザイン結果を廃棄してから、ストレージの見積もりサイズを指定して、もしくはパフォーマンス到達率を指定して集計のデザインを「開始」します。
例えば、パフォーマンス到達率90%を設定してデザインを生成してみます。
＜画面5：集計オプションの選択 (90%で予測)＞
この場合、11集計でストレージサイズ0.1MByte、パフォーマンス到達率91%というデザインが生成されました。
このように、「ストレージデザインウィザード」を使って、どの程度の集計を行って、ストレージをどれだけ使用するかをキューブごとに定義しておくことができます。
今回は、先ほど作成した [0.6MByte 100%]のデザインを採用することにします。集計のデザインが右側のグラフの下の部分に表示されている状態で、「次へ」を押します。
＜画面6：ストレージデザインウィザード (完了)＞
これで、「ストレージデザインウィザード」の完了です。「すぐに処理」のまま「完了」すると、前回行った「キューブの処理」の、「完全な処理」が実行されます。
【3-2】データマートにデータを格納する
「ストレージのデザイン」は把握できたので、A君は次に、自社の実際のデータでキューブを生成しようと考えました。
そのためには、キューブのデータソースにあたる「売上分析ソース」データベースに実際のデータを格納すればよいのですが、どうすればいいのでしょうか？
A君は、SQL Serverビジネスインテリジェンス機能を調査した際に、データ変換サービス(DTS)がこのようなデータの変換処理や転送処理に利用できることを思い出しました。
それでは、DTSを使って「売上分析ソース」データベースに実際のデータを格納してみましょう。 まず分析対象のデータですが、これはAS/400上の販売管理システムにあります。これをSQL
Server上の「売上分析ソース」データベースに転送する処理をDTSで作成すればいいわけです。
業務システムのテーブル構造
売上データ（売上番号、顧客コード、商品コード、売上日、売上金額、売上数量、粗利金額、…）
顧客マスタ（顧客コード、顧客名、性別、生年月日、郵便番号、都道府県、住所、…）
商品マスタ（商品コード、商品名、商品分類コード、…）
商品分類マスタ（商品分類コード、商品分類名、…） |
売上分析」キューブ用のテーブル構造
売上ファクト（売上番号、顧客コード、商品コード、売上日、売上金額、売上数量、粗利金額）
顧客ディメンション（顧客コード、顧客名、性別、生年月日、都道府県）
商品ディメンション（商品コード、商品名、商品分類コード、商品分類名） |
【3-3】DTSパッケージの作り方・データ転送の自動化
DTSの機能を整理してみましょう。DTSの構成要素は主に3つあります。
DTSの構成要素
「接続」
- OLE DBプロバイダを介した、多様なデータソースへの接続
- DTS組み込みのフラットファイルOLE DBプロバイダを介してテキストファイルへの接続も可能
「タスク」
- 指定されたルールに基づいた、データ変換／転送処理の実行タスク
- （表１）のようなタスクが用意されている
「ワークフロー」
- DTS パッケージの中での、タスクのワークフロー（実行条件や実行順序など）の定義
- 実行条件として「完了時」／「成功時」／「失敗時」から選択できる
|
＜表１：代表的なDTSタスク＞
| DTSタスク |
概要 |
| データ変換タスク |
接続間でデータの変換・転送を行う |
| SQL 実行タスク |
接続先に対してSQL ステートメントを実行する |
| プロセス実行タスク |
実行可能プログラム・バッチファイルを実行する |
| Analysis Services 処理タスク |
Analysis Servicesオブジェクト(キューブ、ディメンション等)の処理を行う |
| パッケージ実行タスク |
他の DTS パッケージを実行する |
| メール送信タスク |
メールを送信する
DTSパッケージステップが失敗した場合の電子メール通知などに利用できる |
これらを組み合わせて、「売上分析ソース」データベースへのデータ転送処理を作成することにしましょう。
【3-4】キューブ処理の自動化
ところで、「売上分析ソース」データベースにデータを格納しただけではキューブには反映されません。反映するためには「キューブの処理」の実行が必要です。この「キューブの処理」も、DTSのタスクとして用意されているので、このタスクを含んだDTSパッケージを作成することによって自動化できます。
画面７が、「接続」「タスク」「ワークフロー」を組み合わせてデータ転送処理からキューブの処理までを実行するDTSパッケージです。左上がAS/400への「接続」、そこから伸びている黒い矢印が「データ転送タスク」、矢印の行き先がSQL
Serverへの「接続」です。ここでデータ転送処理を行います。
次に、SQL実行タスクで転送済みのデータのクレンジング処理（データをきれいにしたり、整備したりすること）を行います。そして、「Analysis
Services 処理タスク」でキューブの処理を行い、最後に失敗した場合だけ「メール送信タスク」で管理者にメール送信します。緑の縞々の矢印は「成功時」のワークフロー、赤い縞々の矢印は「失敗時」のワークフローです。
＜画面7：DTSパッケージの例＞
「接続」「タスク」「ワークフロー」の組み合わせで、AS/400からSQL Server上の「売上分析ソース」データベースへデータを転送し、さらに「売上分析」キューブの処理も実行するDTSパッケージが完成しました。
基本的にノンプログラミングで可能ですが、商品マスタと商品分類マスタをジョインして取得し、商品ディメンションに格納する部分で少々SQLを記述する必要がありました。
これで、いつでもAS/400からデータが取得できます。
【3-5】SQL Server Agentによるスケジューリング
さらに、A君はDTSパッケージを定期的に自動実行するようにスケジューリングしました。
DTSパッケージを右クリックすると「定期ジョブのスケジュール編集」画面が表示されるので、ここでスケジュールを設定できます。設定されたスケジュールはSQL
Server Agentの機能によって指定どおりに実行されます。
このように、DTSとSQL Server AgentというSQL Serverを構成する機能同士の組み合わせでELTツールとしての機能を果たします。
明日（次回）は、ExcelによるOLAP分析を実際に行いながら、OLAP分析の方法を学習します。
|
