MDX (Multi Dimensional eXpression)
トランザクションのデータや、データウェアハウスに格納されたデータを分析用に多次元の空間に格納し直したデータベース (キューブ) に対し、データを取得する際に利用される言語であるMDXについて簡単に紹介する。SQLがトランザクションデータを格納するのによく用いられる一方で、アナリティクスのためにデータを保存し直したものがキューブであり、レポーティングアプリケーションやExcelからMDXクエリを介して用いられる。
設定
次のような小さな自動車販売代理店があったとする。この会社では、Alpha・Beta・Chi・Delta・Epsilon・Xi・Upsilon・Theta・Zetaという車種を取り扱い、2015年から販売実績がある。この販売データを格納したキューブはSalesと名付けられ、Sales Amountという指標 (Measure)と、DateおよびProductという2つの軸 (Dimension) が設定されている。また、それぞれの軸は次のような階層を持つSnowflakeスキーマを構成しているとする。この企業のキューブに対し、以下の5つの問いとそれに対する答えを得るためのMDXを紹介する。
利用方法
1. 年別販売実績
最初は、年別の総販売金額を抽出する。この企業の業績が伸びているのか、悪化しているのか、売上の推移を見る。このデータを獲得するには、次のステートメントを実行する。
select
[Measures].[Sales Amount] on columns,
non empty [Date].[Year].[Year] on rows
from [Sales]結果として、次のようなテーブルが得られる。MDXではSQLのようにselect文を使うが、columns・rowsにどの指標・軸を置くか指定することで、取得・集計すべきデータの組み合わせを決定する。
| Sales Amount | |
| 2015 | $2,195,000 |
| 2016 | $2,208,000 |
| 2017 | $2,260,000 |
| 2018 | $2,249,000 |
| 2019 | $606,000 |
2. 業績不振車種
車種別に売上の低い方からN個のデータを取得する場合は、次のステートメントを実行する。Sales Amountを列に取ることは同じだが、bottomcount()という関数を行に使うことで、結果をフィルタリングできる。
select
[Measures].[Sales Amount] on columns,
bottomcount( [Product].[Product].[Product].Members, N, [Measures].[Sales Amount]) on rows
from [Sales]例えばN=5とすると次のようなテーブルが得られる。結果は昇順で並べられ、Sales Amountの低い物から5つが取得される。こうすることで、これまでの総実績として、売上の低い車種を特定することができる。
なおここで、キューブはどこに属するか分からないデータがあった場合、Unknownというメンバーに格納していることに注意。これは、キューブのセットアップ時に表示するかどうか設定できる。さらに、non emptyを外し、Zetaのように売上の無い車種を表示している。ここでnon emptyを付けると、UnknownやZetaは外され、値を持つ車種だけが表示され、あたかも全ての車種に売上があるように見えてしまう。
| Sales Amount | |
| Unknown | (null) |
| Zeta | (null) |
| Theta | $607,000 |
| Beta | $920,000 |
| Chi | $993,000 |
3. 月別・車種別販売実績
ここまでに見た時間軸と車種軸を組み合わせて、さらに細かい業績に目を向けたいとする。その場合、軸をcross joinすることで所望の結果を得ることができる。ステートメントはこれまでとよく似ている。
select
[Measures].[Sales Amount] on columns,
non empty ([Date].[Year].[Year],[Date].[Month].[Month]) * [Product].[Product].[Product] on rows
from [Sales]このステートメントを実行すると、次が返される。Cross joinはシンプルに*を使って表現することができ、売上が月、そして車種によってどのような傾向を見せるか知ることができる。
| Sales Amount | |||
| 2015 | Jan | Alpha | $23,000 |
| 2015 | Jan | Beta | $18,000 |
| 2015 | Jan | Chi | $19,000 |
| 2015 | Jan | Delta | $21,000 |
| 2015 | Feb | Alpha | $23,000 |
| … | … | … | … |
4. 車種別販売高・割合
続いて、車種別の売上高が全体の売上高に対して何割を占めるのか、という計算を行う場合には、クエリの中で新しい変数を定義することで求められる。このような計算式を新しい指標として登録しておくことも可能だ。
with
member [Measures].[％ Sales] as iif( ([Product].[Product].[All Products], [Measures].[Sales Amount]) = 0, null, [Measures].[Sales Amount] / ([Product].[Product].[All Products], [Measures].[Sales Amount] ), format_string = "Percent"
select
{ [Measures].[Sales Amount], [Measures].[％ Sales] } on columns,
non empty [Product].[Product].Members on rows
from Sales2.の結果に似ているが、業績不振な車種を取り除くことによって、どの程度売上に対するインパクトがあるかを見ることができる。iif()は条件文であり、条件・真の場合・負の場合という構成になっている。
| Sales Amount | % Sales | |
| Alpha | $1,348,000 | 14.16% |
| Beta | $920,000 | 9.67% |
| Chi | $993,000 | 10.43% |
| Delta | $1,062,000 | 11.16% |
| … | … | … |
5. 四半期別販売成長率
最後に、四半期別の売上成長率について計算する方法を紹介する。先程と同様に、クエリの中で2つの変数を定義し実行時に計算する方法を使う。1つは前期の売上を求め、もう1つは前期と当期の売上から成長率を計算する。
with
member [Measures].[Last Period Sales] as ([Date].[Calendar].PrevMember, [Measures].[Sales Amount]), format_string = "Currency"
member [Measures].[Growth %] as iif( [Measures].[Last Period Sales] = 0, null, ([Measures].[Sales Amount] - [Measures].[Last Period Sales]) / [Measures].[Last Period Sales] ), format_string = "Percent"
select
{ [Measures].[Sales Amount], [Measures].[Last Period Sales], [Measures].[Growth %] } on columns,
non empty [Date].[Calendar].[Quarter].Members on rows
from SalesPrevMemberは階層構造を持った軸の中で、自分と同じレベルの1つ前のメンバーを選出してくれる。この場合は時間軸で、レベルは四半期となる。シーズン毎の売上パターンを見ることができる。
| Sales Amount | Last Period Sales | Growth % | ||
| 2015 | Q1 | $469,000 | null | null |
| 2015 | Q2 | $583,000 | $469,000 | 24.31% |
| 2015 | Q3 | $428,000 | $583,000 | -26.59% |
| 2015 | Q4 | $715,000 | $428,000 | 67.06% |
| 2016 | Q1 | $472,000 | $715,000 | -33.99% |
| … | … | … | … |
結言
キューブおよびMDXはデータを蓄積しアクセスするためのツールであるが、多次元の空間であるため指標と軸の組み合わせは多数に上る。どの指標を計算しどの次元で切り取ることで、結果として得られるデータが何を意味するのか、そしてどのようなアクションを促すことができるか、を明確にすることでデータの有効な活用ができる。
また、効果的なレポートを提供するためには、キューブのセットアップ・得られたデータのビジュアル化・レポートの配信・アラートの設定・予測アルゴリズムの導入と行った様々なステップが考えられる。レポートによって得られる価値と投入可能な資源とを見比べながら、アジャイルに則って柔軟に構築していくことが推奨される。
お問い合わせはこちらまで。
