たまに聞かれる [数式に変換] を使ってみよう

Microsoft Japan Data Platform Tech Sales Team

川野 純

Excel の PivotTable から SQL Server Analysis Service 分析データベースに接続し、PivotTable で大量のデータを自由に分析できるのは非常に便利です。

しかし、現場でお客様とお話をしていると以下のようなご要望を頂くことがあります。

・「PivotTable はわかる人にはとても分かりやすいけど、うちの会社で使いこなしている人はそんなに多くはないんですよね。」

・「Excel 帳票をなんかこう見栄えをもっときれいな感じにできないかなぁ？このセルのフォントだけ、もうちょっと大きくしたり... etc」

・「会議資料用に印刷しても見づらいから、帳票っぽくできない？。」

[ピボットテーブル ツール][デザイン]タブ にてある程度レイアウトを調整することができますが、上記のようなご要望を満たすことはできません。

このような場合、PivotTable が SQL Server Analysis Service 分析データベースに対して実際に発行している MDX (Multi-Dimensional eXpression) 式を含む CUBEMEMBER 関数や CUBEVALUE 関数を用いて必要な値だけを指定したセルに抽出し、見栄えを自由に編集することができます。

参考情報

・CUBEMEMBER 関数(Excel)

・CUBEVALUE 関数(Excel)

・MDX の主な概念 (Analysis Services)

上記リンクの情報を勉強するもの有益ですが、実際には PivotTable が何をしているのかを見ていった方が近道です。

Excel から Analysis Services に接続

まずは、[データ]タブの[外部データの取り込み]から[その他のデータソース]-[Analysis Services から(A)]を選択し、分析データベースへの接続情報を入力し、PivotTable を作成しておきましょう。

早速、PivotTable を [数式に変換] してみる！

今回は以下のような PivotTable を作成しました。ではこの PivotTableの各セルを

[ピボットテーブルツール] - [分析]タブ - [OLAPツール] - [数式に変換]

と辿って [数式に変換] してみましょう。

変換された数式を眺めてみよう

では、例として 2016 年度の飲料の合計金額計の式を見ていきましょう。

2016年度の飲料の合計金額計は以下の式で表されています。

=CUBEVALUE("NorthWIndJTabularProject モデル接続",$A$1,$A3,E$2)

接続名                                 メンバー式(MDX)

1つ目のメンバー式は絶対参照 "$A$1" です。$A$1を見ていきましょう。

CUBEMEMBER 関数で NorthWindJTabularモデルのメジャーである、[合計金額計] が定義されています。

=CUBEMEMBER("NorthWIndJTabularProject モデル", "[Measures].[合計金額計]" )

次に2つ目のメンバー式は絶対参照列と相対参照行 "$A3" です。$A3を見ていきましょう。

CUBEMEMBER 関数で NorthWindJTabularモデルの [商品]ディメンションの[区分名]ディメンション属性のメンバー名が[飲料]であるメンバーを取得しています。

=CUBEMEMBER("NorthWIndJTabularProject モデル", "[商品].[区分名].&[飲料]" )

最後に3つ目のメンバー式は相対参照列と絶対参照行 "E$2" です。E$2 を見ていきましょう。

CUBEMEMBER 関数で NorthWindJTabularモデルの [日付マスタ]ディメンションの[時間階層]階層における[年度]ディメンション属性のメンバー名が[2016 年度]であるメンバーを取得しています。

=CUBEMEMBER("NorthWIndJTabularProject モデル", "[日付マスタ].[時間階層].[年度].&[2016 年度]" )

つまり、

=CUBEVALUE("NorthWIndJTabularProject モデル接続",$A$1, $A3, E$2)

=CUBEVALUE("NorthWIndJTabularProject モデル接続","[Measures].[合計金額計]","[商品].[区分名].&[飲料]","[日付マスタ].[時間階層].[年度].&[2016 年度]")

と定義されているため、指定したデータを取得できていることがわかります。

データ更新ができる簡単な定型帳票を作成してみよう

それでは簡単な帳票シートを作成してみましょう。

以下の例では加藤泰江さんの 2016 年度(今年)の売り上げを取得するために B5 のセルに以下の式を定義しています。

=CUBEVALUE("NorthWIndJTabularProject モデル",
"[Measures].[合計金額計]",
{"[社員].[在籍支社].&[東京本社]","[商品].[区分名].&[飲料]","[社員].[氏名].&[加藤泰江]"},
"[日付マスタ].[時間階層].[年度].&[2016 年度]")

C5 と D5 のセルも同様に定義します。
あとは [データ] タブの ”すべて更新” ボタンを押せば、その都度、サーバー上の最新のデータを取得して表示します。

このようにして分析データベースから取得した値を元に、自由にレイアウトした定型帳票シートが作成できました。

MDX はちょっと難解に見えるかもしれませんが、一つ一つ紐解いてみるとご理解いただけるかと思います。
強力なツールですのでぜひチャレンジしてみてください。

参考情報

・難解クエリー言語「MDX」に挑戦