【Power BI入門】家計簿分析レポート#5 リレーションシップの設定とカレンダーテーブルの作成方法
この記事は「Power BI入門 家計簿レポート」シリーズの第5回目で、前回Power Queryで作成したテーブル同士をつなぎ、データモデルを完成させます。
前回4つのテーブルを作りましたが、まだバラバラのままですな…。どうやってつなげるのですかな？
Power BIの「リレーションシップ」機能を使えば、テーブル同士を線でつないで、1つのデータモデルとして扱えるようになります。
前回は、Power Queryを使って3種類のCSVからファクトテーブルとディメンションテーブルを作成しました。
今回はその続きとして、残っていた日付テーブル「dim_Date」をDAXで作成し、テーブル間のリレーションシップを設定して、スタースキーマのデータモデルを完成させます。
- リレーションシップとは何か？（1対多・フィルターの方向）
- カレンダーテーブルがなぜ必要なのか？と、DAXでの作り方
- モデルビューでのリレーションシップの設定方法
Power BIについて詳しく学びたい方は、ぜひパワ実のPower BI入門書もご覧ください！
YouTube動画で見たい方は、こちらからどうぞ！
今回実施するところ
このシリーズでは、Power BIを使ったデータドリブンな意思決定の流れを学ぶことができます。
今回はステップ③、データの準備の「仕上げ」です。
前回作った4つのテーブルに、カレンダーテーブル「dim_Date」を加え、リレーションシップを設定してデータモデルを完成させます。
ここが終われば、次回からはいよいよ④Power BIで可視化・分析、レポート作成に進みます。
レポート作成前の、最後のデータ準備ですぞ！
リレーションシップとは？
1対多の関係
リレーションシップとは、異なるテーブル間の関係性を、キーとなる列を使って定義するものです。
以下の例を見てみましょう。
カテゴリのマスタである「dim_Category」、取引明細の「fact_Cashflow」は、両方に「中項目」という列があり、この中項目をキーにして2つのテーブルを結びつけることができます。
ポイントは、この2つのテーブルが1対多の関係になっていることです。
- dim_Category側：「食料品」という値は1行しかない（マスタなので一意）
- fact_Cashflow側：「食料品」の取引が何行も登場する（明細なので繰り返し出てくる）
このように、マスタ側が「1」、明細側が「多」となる関係性を、1対多のリレーションシップと呼びます。
リレーションシップを定義すると、Power BIで可視化したときに、dim_Categoryで「食料品」を選ぶだけで、fact_Cashflowが食料品の行だけに絞り込まれるようになります。
フィルターの方向
こちらが、今回完成させるデータモデルの全体図です。
真ん中に取引と資産の2つのファクトテーブル、その周りに3つのディメンションテーブルが並ぶ、スタースキーマの形です。
ディメンション（マスター）テーブル側は、キー列が一意の値になるのが基本ということですな！
カレンダーテーブルの作成方法
なぜカレンダーテーブルが必要なのか？
データモデルの図をよく見ると、「dim_Date」だけまだ作成していません。
前回のPower Queryでは4つのテーブルしか作りませんでしたね。
Power BIで日付を切り口とした分析をする場合、必ずカレンダーテーブルを作成します。
理由は以下の3つです。
- 記録がない日付が欠損する
fact_Cashflowの日付列には、取引があった日しか入っていません。
連続した日付軸が作れないため、カレンダーテーブルで期間内のすべての日付を隙間なく持ちます。 - タイムインテリジェンス関数の前提になる
前月比やYTD（年初来累計）といった計算に使うタイムインテリジェンス関数は、連続した日付テーブルがあることが前提です。 - 日付の切り口を1か所で管理できる
年・四半期・月といった切り口の列を、カレンダーテーブルにまとめて持たせられます。
カレンダーテーブルの作り方は、「DAX式」で作る方法と、「Power Query」で作る方法の2つがあります。
どちらもよく使われますが、今回はPower BI Desktop上で簡単にできるDAX式で作ります。
祝日フラグなど外部データと組み合わせたい場合は、Power Queryで作る方法が向いています。
操作方法
１．前回までのPower BI Desktopファイルを開き、「テーブルビュー」タブをクリックし、「テーブルツール」か「ホーム」タブの「新しいテーブル」をクリックします。
２．数式バーが開くので、以下のDAX式を入力します。
dim_Date =
VAR _StartDate = MIN(fact_Cashflow[日付])
VAR _EndDate = MAX(fact_Cashflow[日付])
VAR _BaseCalendar = CALENDAR(_StartDate, _EndDate)
RETURN
ADDCOLUMNS(
_BaseCalendar,
"年No", YEAR([Date]),
"年", FORMAT([Date], "YYYY年"),
"月No", MONTH([Date]),
"月", FORMAT([Date], "M月"),
"年月", FORMAT([Date], "YYYY/MM")
)
このDAX式のポイントは3つです。
- 日付の範囲を動的に取得
MINとMAXで、fact_Cashflowの日付の最小値と最大値を取っています。
開始日・終了日を「2024/1/1」のように固定で書くこともできますが、こうしておけば来年データが増えてもカレンダーが自動的に伸びてくれます。 - CALENDAR関数
開始日から終了日までの連続した日付を、1日も欠けずに生成してくれる関数です。
これがカレンダーテーブルの本体になります。 - ADDCOLUMNSで切り口の列を追加
年・月・年月について、表示用の列（「2024年」「1月」）と、並べ替え用の番号の列（年No・月No）をセットで作っています。
番号列が必要な理由は、この後すぐ分かります。
３．Enterキーで確定すると、テーブルが作成されます。
2024年1月から、日付が1日も欠けずに並んでいることを確認します。
４．少しだけ手直しをします。「Date」列は「日付と時刻」のデータ型になっているので、「日付」型に変更します。
５．CALENDAR関数が作る列は「Date」という英語の名前なので、他のテーブルに合わせ、列名をダブルクリックして「日付」にリネームします。
６．ここで大事な設定があります。「月」の列は「1月」「2月」といった文字列なので、そのままグラフの軸に使うと「10月、11月、12月、1月…」と文字の順番で並んでしまいます。
「月」列を選択し、「列ツール」の「列で並べ替え」から「月No」を指定します。
これで表示は「1月」のまま、並び順は数字の1～12になります。
同様に「年」列も、並べ替え基準を「年No」にしておきますぞ。
先ほど番号の列をセットで作ったのは、このためだったのですな！
７．最後に、「テーブルツール」タブから「日付テーブルとしてマークする」をクリックし、日付列に「日付」を選択して保存します。
これで、このテーブルがPower BIに「正式なカレンダーテーブル」として認識され、タイムインテリジェンス関数を安心して使える状態になりました。
リレーションシップの設定
自動検出に注意
カレンダーテーブルができたので、いよいよリレーションシップの設定です。
テーブル間の関係性の定義は、画面左端の「モデルビュー」タブで行います。
１．「モデルビュー」タブに切り替えると、5つのテーブルが並んでいます。
便利ではありますが、列名やデータから自動で作成されるため、意図しないリレーションシップが設定されることもあります。
自動検出をそのまま信用するのではなく、必ず自分の目で確認しましょう。
２．今回は、自分でリレーションシップを設定する手順を学ぶため、自動作成されたリレーションシップを右クリックで一旦すべて削除します。
4つのリレーションシップを設定
設定するリレーションシップは、全部で4つです。
| ディメンション（1側） | ファクト（多側） | キー列 |
|---|---|---|
| dim_Date | fact_Cashflow | 日付 |
| dim_Category | fact_Cashflow | 中項目 |
| dim_Account | fact_Cashflow | 保有金融機関 |
| dim_Account | fact_Assets | 保有金融機関 |
１．まず、dim_Dateの「日付」を、fact_Cashflowの「日付」までドラッグ＆ドロップします。
２．設定画面で、2つのテーブルとキー列が正しく選択されていることを確認し、カーディナリティは「一対多」、クロスフィルターの方向は「単一」で、「保存」をクリックします。
３．同じ要領で、残りの3つのリレーションシップも作成します。
それぞれ「一対多」になっていることを確認しながら進めましょう。
４．最後に見た目を整えます。テーブルをドラッグして、設計図と同じように、真ん中にファクトテーブル、周りにディメンションテーブルを並べてみます。
第3回で描いたスタースキーマが、そのまま画面に再現できましたぞ！
ちなみに、fact_Assetsとdim_Dateの間には、リレーションシップを設定していません。
fact_Assetsは最新時点での資産のスナップショットで日付列がないため、日付でのフィルターは不要、という設計でしたね。
使わない列を非表示にする
リレーションシップの設定が完了したら、レポートで使わない列やテーブルは非表示にしておきましょう。
基本的に、ファクト側でキーに使っている列は非表示にします。
ディメンションテーブル側に同じ列があり、フィルターなどはディメンションテーブルで行うためです。
１．fact_Cashflowの「日付」「中項目」「保有金融機関」など、キーに使った列を非表示にします。
dim_Dateの並べ替えに使った「月No」「年No」も非表示にします。
２．「レポートビュー」タブで、データを見てみると、不要な列が表示されず、データがスッキリして使いやすくなっています。
動作確認
最後に、データモデルが本当に機能しているか、簡単なグラフで確認してみましょう。
１．「レポートビュー」タブで、集合縦棒グラフを配置します。
X軸にdim_Dateの「年」「月」、Y軸にfact_Cashflowの「金額（円）」をドラッグ＆ドロップします。
年月ごとの棒グラフが表示されました。
軸に使っているのがfact_Cashflowの日付ではなく、dim_Dateの年と月です。
別々のテーブルの列を組み合わせてグラフが表示できている、つまり、先ほど設定したリレーションシップがちゃんと機能している証拠です。
２．もう1つ、スライサーのビジュアルを置いて、dim_Categoryの「大項目」「中項目」を表示します。
スライサーで食費の「食料品」を選ぶと、グラフが食料品だけの金額に変わりました。
「マスタで選ぶと明細が絞られる」という、リレーションシップの動きがまさにこれです。
３．動作確認ができたので、ファイルを上書き保存しておきます。
これでデータモデルの完成です！
お疲れ様でした！第3回で設計したスタースキーマが、ついに形になりました。
さいごに
この記事では、DAXでカレンダーテーブル「dim_Date」を作成し、モデルビューで4つのリレーションシップを設定して、家計簿データのデータモデルを完成させました。
- リレーションシップは、ディメンションとファクトを一対多でつなぐのが基本
- カレンダーテーブルは日付分析の必須テーブルで、CALENDAR関数で動的に自動生成できる
- リレーションシップの自動検出は鵜呑みにせず、自分の目で確認する
今回学んだ「1側は一意」「フィルターは一方向」という考え方は、家計簿に限らず、あらゆるデータモデル設計に応用できます。
次回はいよいよレポート作成ですぞ！
完成したデータモデルの上に、収支サマリーページを作っていくので、楽しみにしていてくだされ！
