Microsoft SQL Server 2016 自習書シリーズ （HTML 版）
「No.4 Analysis Services の新機能」

2.6　計算テーブルで日付ディメンションの作成

計算テーブルを利用すれば、日付ディメンション（受注日などの日付を使った分析に役立つディメンション）を作成することも簡単に行えます。SQL Server 2016 からは、日付データを生成することができるCALENDAR および CALENDARAUTO という DAX 関数が提供されたからです。CALENDAR 関数は、次のように利用することで、開始日から終了日までの日付データを生成することができます。

◆ Let's Try

それでは、これを試してみましょう。

1．まずは、新しく計算テーブルを作成するために、［DAX 式から計算された新しいテーブルを作成する］ボタンをクリックします。

2．DAX エディターが表示されたら、次のように CALENDAR 関数を記述します。

CALENDAR 関数の開始日と終了日には、DATE 関数を利用していますが、DATE(2014,1,1) と指定することで 2014年 1月 1日、DATE(2016,12,31) と指定することで 2016年 12月 31日を与えることができます。これで、2014年 1月 1日から、1日ごとに 1行のデータが生成されて（365日 * 3年分＋うるう日=1,096行）、最後のデータが 2016年 12月 31日になります。

3．次に、テーブルの名前を「日付」に変更します。

4．次に、計算列として「年」を追加して、年ごとに売上データを分析できるようにします。計算列を追加するには、次のように［列の追加］の任意の空白行を選択してから、DAX 式を記述します。

DAX 式には、FORMAT 関数を利用して、"yyyy" と指定することで、日付データから 年 を取得することができます。

5．追加した計算列は、名前を「年」に変更しておきます。

6．次に、もう1つ計算列を追加して、月ごとに売上データを分析できるようにします。［列の追加］で、次のように DAX 式を記述します。

FORMAT 関数を利用して、"MM" と指定することで、日付データから 月 を取得できます。

7．追加した計算列は、名前を「月」に変更しておきます。

◆ リレーションシップの作成（受注日から日付ディメンションへ）

次に、計算テーブルとして作成した日付ディメンションと受注日（売上のあった日にち）を関連付けるために、リレーションシップを作成します。

1．リレーションシップを作成するには、［ダイアグラム ビュー］を表示します。

［ダイアグラム ビュー］には、作成した日付ディメンションが追加されていることを確認できます。

2．次に、［受注］テーブルの［受注日］列を、［日付］テーブルの［Date］列にドラッグ＆ドロップします。

これでリレーションシップが作成されて、作成されたリレーションシップは次のように表示されます。

受注日と日付は、多対1の関係なので、多は「*」、1は「1」と表示されています。

3．リレーションシップの作成後は、ツールバーの［Excel で分析］をクリックして、Excel で動作を確認します。

4．Excel が起動したら、［受注金額計］メジャーをクリックして、［Σ 値］に配置します。

5．次に、［日付］テーブルの［年］をチェックします。

［年］が［行］に配置されて、年ごとの受注金額の合計が表示されることを確認できます。

6．次に、［商品区分］テーブルの［区分名］列を、［列］にドラッグ＆ドロップして配置します。

これで、年ごとの売上を、商品区分ごとに確認できるようになります。

7．次に、［日付］テーブルの［月］を、［行］の［年］の下にドラッグ＆ドロップして配置します。

これで、年ごと、月ごと、区分ごとに売上を確認できるようになります。

Excel では、次のように［ホーム］タブの［条件付書式］からカラー スケールを設定すれば、どの月の、どの区分の売上が良かったのか／悪かったのかを簡単に確認できるようになります。

例えば、2014年の［調味料］の 5月の売上が一番大きいこと（緑色が濃いものが一番大きい）や赤色で表示されるものが売上が小さいといったことが一目瞭然で分かるようになります。

このように、日付ディメンションを作成することで、年ごとや月ごとの売上分析を簡単に行えるようになります。これまでの Analysis Services では、多次元モデルでは日付ディメンションを作成する機能がありましたが、Tabular Model にはありませんでした（リレーショナル データベース上に別途手動で作成しておく必要がありました）。SQL Server 2016 からは、計算テーブルと CALENDAR 関数が提供されたことによって、日付ディメンションを作成できるようになったので便利です。

なお、年ごと、月ごと、区分ごとに売上を、Analysis Services を利用せずに、SQL ステートメントを利用して SQL Server データベース エンジンに対して直接実行する場合は、次のように 4つのテーブルを JOIN した GROUP BY 演算が必要になります。

◆ 日付テーブルとしてマーク／タイム インテリジェンス関数で前年金額の取得

日付ディメンション（日付データがあるテーブル）は、「日付テーブルとしてマーク」を設定しておくことで、タイム インテリジェンス関数という日付に関する便利な関数を利用できるようになります。これを利用すれば、前年金額／前年比を取得したり、累積金額を取得したりすることも簡単にできるようになります。

1．日付テーブルとしてマークするには、次のように「日付」テーブルを選択して、［テーブル］メニューから［日付］で［日付テーブルとしてマーク］をクリックします。

［日付テーブルとしてマーク］ダイアログでは、［日付］に「Date」列を選択して、［OK］ボタンをクリックします。これで、日付テーブルとして設定することができます。

2．次に、タイム インテリジェンス関数の SAMEPERIODLASTYEAR を利用して、前年の金額を取得してみましょう。この関数は、次のように利用できます。

このメジャーは、受注明細テーブルのメジャーとして作成し、メジャーの名前は「前年金額」に設定します。CALCULATE 関数の第1引数には「受注金額計」メジャー、第2引数には SAMEPERIODLASTYEAR 関数で「日付」テーブルの「Date」列（日付データが格納されている列）を指定することで、「受注金額計」に対応した前年（Last Year）の金額を取得できるようになります。Same Period は、同じ期間という意味で、年 を選択した場合は 年、月 を選択した場合は 月 といった形で、同じ期間の Last Year（前年）のデータを集計してくれる非常に便利な関数です。

3．作成したメジャーは、［プロパティ］ウィンドウで［形式］を「通貨」に変更して、通貨形式（\マークを付けて、3桁ごとにカンマを入れる書式）で表示するようにします。

4．設定後は、ツールバーの［Excel で分析］をクリックして、Excel で動作を確認します。

「受注金額計」と「前年金額」メジャーをチェックして、［Σ 値］に配置します。

5．次に、［日付］テーブルの「年」をチェックして、［行］に配置します。

年ごとに、前年金額が表示されて、2015年の前年金額「11,599,700円」は、2014年の受注金額と同じであることを確認できます。

なお、2014年に対する前年金額のデータはないので、2014年の前年金額は空白で表示されています。

6．次に、［日付］テーブルの［月］を、［行］の［年］の下にドラッグ＆ドロップして配置します。

前年同月の金額が表示されて、2015年 1月の前年金額「820,900円」が、2014年の 1月の受注金額と同じであることを確認できます。

このように SAMEPERIODLASTYEAR 関数を利用すると、同じ期間（年なら年、月なら月）の前年金額を計算することができるので大変便利です。

7．次に、［商品区分］テーブルの［区分名］列を、［列］にドラッグ＆ドロップして配置します（［Σ値］の上に配置します）。

区分名を追加しても、前年同月の金額が表示されることを確認できます。このようにタイム インテリジェンス関数は便利なので、ぜひ活用してみてください。タイム インテリジェンス関数を利用するには、日付テーブルとしてマークしたテーブル（日付ディメンション）が必要になりますが、SQL Server 2016 からは、計算テーブルで日付ディメンションを作成できるようになったので、簡単にこの関数を利用できるようになりました。

◆ 日付ディメンションの複製 ～出荷日での分析～

今回作成した日付ディメンションは、受注日に関連付けて、商品の売上があった日にちでの分析に利用しましたが、出荷日（商品の出荷を行った日）など他の日にちで分析したい場合もあります。これを行うには、これまでの Analysis Services では、SQL Server 側に日付テーブルを複数作成しておくか、Tabular Model を分ける（Analysis Services 上にデータベースを複数作成する）といった方法しかとれませんでした。SQL Server 2016 からは、計算テーブルのサポートによって、テーブルの複製ができるようになったので、この問題を解決することができます。これも試してみましょう。

1．まずは、日付テーブルが複数必要になることを確認するために、［ダイアグラム ビュー］を開いて、［受注］テーブルの［出荷日］列を、［日付］テーブルの［Date］列にドラッグ＆ドロップして、リレーションシップを作成します。

作成したリレーションシップは、次のように表示されます。

「Date」列に対しては、既に「受注日」列が関連付けられているので、2つ目のリレーションシップということで、"点線" で表示されてしまいます。この点線で表示されたリレーションシップは、データ分析に利用することはできず、リレーションシップをダブルクリックしてプロパティを表示すると［アクティブ］のチェックが外れて、非アクティブに設定されています。

このような状態のリレーションシップは、［受注日］を非アクティブにしないと利用することができません（データ分析で利用できるリレーションシップは、列に対して１つのみになります）。

これを解決する手段が、計算テーブルを利用したテーブルの複製になります。

2．テーブルの複製を行うには、まず、グリッド ビューに戻って、［DAX 式から計算された新しいテーブルを作成する］ボタンをクリックして、新しく計算テーブルを作成します。

3．DAX 式には「=’」を入力すると、テーブルの一覧が表示されるので、「日付」テーブルを選択して、Enter キーを押下します。

このように DAX 式に「='テーブル名'」と記述することで、指定したテーブルの複製を作成することができます。複製したテーブルの名前は、次のように「出荷日用」に変更しておきます。

4．次に、ダイアグラム ビューを開いて、［受注］テーブルの［出荷日］列を、［出荷日用］テーブルの［Date］列にドラッグ＆ドロップして、リレーションシップを作成します。

5．リレーションシップの作成が完了したら、ツールバーの［Excel で分析］をクリックして、Excel で動作を確認します。

［Σ値］に「受注金額計」メジャー、［行］に「出荷日用」テーブルの「年」と「月」を配置して、出荷年月ごとの受注金額が表示されることを確認できます。

なお、これと同じことを Analysis Services を利用せずに、SQL ステートメントを利用して SQL Server データベース エンジンに対して直接実行する場合は、次のように GROUP BY 演算を実行する必要があります。

6．次に、「日付」テーブルの「年」と「月」を、［行］に配置します。

「日付」テーブルの「年」と「月」は、受注日に関連付けられているので、同じ出荷年月のうち、受注年月が異なるものを表示できるようになります。

なお、今回の「日付」テーブルでは作成しませんでしたが、計算列として「年月」列を追加しておけば、上の例のように 2つの列を配置することなく、1つの列を配置するだけで済みます。「年月」列は、「FORMAT([Date], "yyyy/MM")」と指定して作成することができます。

なお、これを同じことを Analysis Services を利用せずに、SQL ステートメントを利用して SQL Server データベース エンジンに対して直接実行する場合は、次のように GROUP BY 演算を実行する必要があります。