| 【5-1】「固定帳票」の情報をOLAP分析に取り入れる
A君は昨日（前回）、ディメンションのプロパティで表示を調整する方法と、Excelからキューブに接続してOLAP分析する方法を把握しました。
現在までに作成したキューブは指定したディメンションごとに「メジャーの合計値」を表示することしかできていないので、「情報から意味を読み取る」OLAP分析をするのにはまだまだ不十分です。
そこで今日（今回）は、キューブの作り方を工夫して利用価値の高いキューブにする方法を考えていきます。
A君は「売上データ」を例に、OLAP分析する場合に有用な、付加情報をついて考えてみることにしました。現在のPASSJ商事ではどんな仕組みで売上の分析をしているのでしょうか。
PASSJ商事ではまだBIの仕組みが整備されていませんが、「売上データ」に関する情報は基幹システム上で一定期間ごとに集計され、自動的にExcelやWordに資料としてまとめられ、必要な社員にメール等で配布される仕組みになっています。紙に印字して配布するものもあります。
例えば、週ごと／月ごとに、前年実績や予算と今年の実績を対比させた資料や、商品グループ別や支社別の売上の構成比の資料が作成されています。また、その他の不定形な資料も必要に応じて作成されています。この場合は複雑な情報が必要なケースが多いため、システム部員が作業を担当することになります。
PASSJ商事ではこのような「固定帳票」を担当者が閲覧することで、意思決定しています。
これらの「固定帳票」に記載されている情報は、OLAP分析においても大変役立つと考えられます。「前年比」「予算実績対比」「商品グループごとの構成比」といった情報をOLAP分析時に同時に参照することがえきれば、分析の価値はより高いものになるでしょう。
また、このような情報の表示をBIで実現できれば、「固定帳票」自体を廃止することもできます。
SQL Server Analysis Serviceでは、このような情報の表示をキューブの設定で行うことができます。
【5-2】「計算されるメンバ」を使ってみよう
それでは実際に、キューブを設定してみましょう。今回は「前年比」を設定してみます。
「前年比」を参照できるようにするには、「計算されるメンバ」を使用する方法が簡単です。「計算されるメンバ」とはSQL Server
RBDMSでの「ユーザ定義関数」のようなもので、対象のデータをある一定の式や関数で変換して、その結果を表示します。「ユーザ定義関数」はTransact-SQLで定義しますが、「計算されるメンバ」はMDX(Multidimensional
Expressions：マルチディメンション式)で定義します。
MDXはRDBMSにおけるSQLのようなもので、多次元データベースに対してオブジェクトの定義やデータのクエリなどを行う言語のことです。
それでは、キューブエディタを開いて「計算されるメンバ」を作成します。
「計算されるメンバ」を右クリックして「計算されるメンバの新規作成」を選択します。
＜画面１：キューブエディタ (計算されるメンバの作成)＞
計算されるメンバビルダが起動します。
＜画面２：計算されるメンバビルダ (定義前) ＞
これに対してMDXで変換式を定義します。以下のように設定します。
親ディメンション：Measures
メンバ名：売上金額（前年）
値式：(ParallelPeriod([売上日].Levels(1),1),[Measures].[売上金額])
ParallelPeriodは指定したディメンション、レベルの以前の期間のメンバを取得する関数です。今回は[売上日]ディメンションのLevels(1)(=[年]レベル)の、1つ前の期間、つまり1年前のメンバを取得します。さらに[Measures].[売上金額]を指定しているので、1年前の売上金額を取得するメンバとして構成されています。MDXはいろいろな書き方ができるようになっています。レベルをレベル名で記述することもできるので、(ParallelPeriod([売上日].[年],1),[Measures].[売上金額])も同じ意味になります。
＜画面３：計算されるメンバビルダ (定義後) ＞
それでは「キューブの処理」を行ってからExcelから接続して確認してみましょう。 年のレベルでも、四半期のレベルでも前年の値が表示されています。（またここでは、前回に売上額の減少を指摘した「プロ野球ミニタオル」「女性」を表示条件にしたので、前年からの減少が確認できます。）
＜画面４：Excelでの前年比の表示＞
また、同様のMDX式を定義することで他の値を取得することもできます。
2年前：(ParallelPeriod([売上日].Levels(1),2),[Measures].[売上金額])
1ヶ月前：(ParallelPeriod([売上日].Levels(3),1),[Measures].[売上金額])
※参考までに、他の例を示しておきます。
| 内容 |
値式 |
| 1年前の売上金額 |
(ParallelPeriod([売上日].Levels(1),1),[Measures].[売上金額]) |
| 同年の、売上金額の累計 |
Sum(PeriodsToDate([売上日].Levels(1)),[Measures].[売上金額]) |
| 現在選択している[商品]ディメンションのレベルにおける、1つ上位のレベルに対する売上金額の構成比 |
([商品].CurrentMember,[Measures].[売上金額])
/([商品
].CurrentMember.Parent,[Measures].[売上金額]) * 100 |
| 現在選択している[売上日]ディメンションのレベルにおける売上金額の平均値 |
Avg([売上日].CurrentMember.Level.AllMembers,[Measures].[売上金額]) |
|
【5-3】「仮想キューブ」を使ってみよう
では次に、「予算実績対比」を行うにはどうしたらよいでしょうか。
現在の「売上分析」キューブには「売上予算」のデータは含まれていないので、「売上予算」と「売上実績（売上金額）」の両方を含んだキューブを別途作成する必要があるように思われますが、SQL
Server Analysis Serviceの「仮想キューブ」を利用することで、サイズの大きい「売上分析」キューブを複数作成せずに済ますことができます。
「仮想キューブ」とはRDBMSにおける「ビュー」のようなもので、複数のキューブを連結して1つのキューブとして外部から参照することができます。
データ量が大変多くキューブの処理時間がかかりすぎる場合に、セグメント別に（例えば地域別に）キューブを分割することで処理時間を抑え（サーバを分けることもできます）、全体としてのキューブを「仮想キューブ」として提供するような用途や、既に作成済みのキューブに付加的な情報（今回のように予算など）を加える場合での使用が想定されます。
それでは実際に作成してみましょう。
まず、データソースに「売上予算ファクト」テーブルを作成して、データを作成します。
商品分類ごと、日ごとに予算が設定されていると仮定します。
365日×数年分×商品分類数
という計算になるので、せいぜい数千件のデータ量です。売上実績のデータ量と比較すると少量のデータを対象にすることになります。
「売上予算」キューブ用のテーブル構造
売上予算ファクト（商品分類コード、売上日、売上予算） |
|
次に、キューブ「売上予算」を作成します。
ファクト：「売上予算ファクト」テーブル
メジャー：「売上予算」列
ディメンション：既に作成されている共有ディメンション「商品」「売上日」
キューブ名：売上予算
最後に、ストレージデザインとキューブの処理を行っておきます。
これで、キューブ「売上分析」とキューブ「売上予算」が存在することになります。ここから、この2つのキューブを利用して「予算実績対比」用の「仮想キューブ」を作成します。
キューブエディタで「キューブ」を右クリックして「仮想キューブの新規作成」を選択します。
＜画面５：仮想キューブウィザード＞
仮想キューブに含めるキューブは「売上分析」「売上予算」を選択します。
＜画面６：仮想キューブに含めるキューブの選択＞
仮想キューブに含めるメジャーは「売上金額」「予算金額」を選択します。
「売上金額」メジャーは「売上分析」キューブから、「予算金額」メジャーは「売上予算」キューブから取得されます。
＜画面７：仮想キューブに含めるメジャーの選択＞
仮想キューブに含めるディメンションは「商品」「売上日」を選択します。
今回は両方とも「共有」のディメンションなので問題になりませんが、特定キューブ専用のディメンションを仮想キューブで利用する場合、同名のディメンションを同時に使用することはできません。
＜画面８：仮想キューブに含めるディメンションの選択＞
最後に、キューブ名を「売上予算実績対比」にして完了します。
＜画面９：仮想キューブウィザード(完了)＞
さっそくExcelから接続して確認してみましょう。今回は「売上予算実績対比」キューブに接続するのでExcelのデータ接続ウィザードで接続先キューブを指定します。
＜画面１０：データ接続ウィザード＞
「仮想キューブ」を利用することで「予算」のメジャーを簡単に付け加えることができました。
＜画面１１：Excelでの予算実績対比の表示＞
このように、SQL Server Analysis Serviceではキューブやディメンションの設定や作り方に工夫を加えることで、データを加工した形でユーザに提供したり、パフォーマンスを考慮した形式でBIシステムを作成したりすることができます。
今回が最終回になりますが、5回の連載でSQL Server BIの考え方と実装方法の基礎部分、またちょっとしたノウハウをご理解いただけると幸いです。
紹介した内容以外にも
- Office Web Componentや、.NETのWebフォーム上で動く「Web Control for Business
Intelligence」などのクライアントツール
- 「SQL Server Accelerator for BI」に代表されるマイクロソフト社提供の追加ツール群
など、より良いBI環境を構築するために把握しておきたい情報もまだ多数ありますので、マイクロソフト社のホームページやPASSJのBI分科会での情報収集をお勧めします。
さて、これで終了です。はじめはBIに全く縁のなかったA君も、これからはますますBIに興味を持ってPASSJ商事のIT戦略を担っていってくれることでしょう。SQL
Server BIの第一歩を踏み出した皆さんも、PASSJのBI分科会での活動を通してSQL Server BIのスキルを高めていってください。
|
