はじめに
CWE部・Power Platform推進チームの小野(涼)です。
以前の投稿で、Power BIの動的レポートのメリットについて投稿しました。
その際に作成したサンプルレポートを題材に、本稿では、Power BIを使ってローコードでデータを準備する流れについて解説します。この投稿だけだと情報が断片的になってしまうので、前回の投稿と合わせてご覧いただければと思います。
ローコードでデータ準備ができることと、DAXによる拡張性の高さが伝われば幸いです。
サンプルレポートのデータ準備の流れ
データソース作成
売上情報、プロジェクト情報、人員情報の3つのテーブルを作成します。
※本来は他のデータソースからインポートするものなので、あくまでサンプルレポート作成のための工程です。実際の業務では、接続先のシステムを特定し、インポートするデータを把握する工程に置き換わります。
サンプルデータ生成
Microsoftが提供するサンプルデータもありますが、日本のSI企業をイメージしてサンプルデータを用意しました。
架空のデータを作成するために、ChatGPTでVBAのコードを書かせてみました。データは、300人×36か月で10,800レコードです。ランダム・大量のデータを作るのは、生成AIがもともと得意な分野かなと。売上と原価については、「日本のSIerの平均的な感じで！」と依頼したら、下記のような幅で設定してくれました。最終的には、上級のさらに上(150万・90万)も追加して4段階にしています。
対話しながら指示して、VBAのコードを調整していきます。
こうしてできあがった売上情報は下記のとおり。Power BIに読み込ませるために、データの範囲をExcelでテーブルとして設定しておきます。
プロジェクト情報テーブルは、10レコードあります。プロジェクトID・プロジェクトの名前・カテゴリ・所在地の情報を持っています。これは手動で作成。
人員情報テーブルは、300名分です。各メンバーは、10個のプロジェクトのいずれかに所属することになります。こちら↓のサイトを使って架空のデータを生成し、単独のExcelファイルとして保存しました。（結局、サンプルレポートで人員情報は使いませんでした）
サンプルデータを作るために色々な方法を組み合わせてますが、近々、Copilot in Excelでこういうことがサクッとできるようになっちゃうんでしょうね。
データインポート
Power BIに読み込み
準備したExcelファイルをPower BIに読み込みます。データの読み込み元は、Azure系サービスをはじめ、様々な種類のデータソースに対応しています。今回のようにPCローカルのExcelファイルを使うのはレアケースで、実際にはサーバー・クラウド上のデータを使うことが多いでしょう。また、HTMLのテーブルや、Web上で公開されているExcel・CSVも読み込めるので、公的統計などから直接データを取得してくることもできます。
Power Queryでデータ整形
Power BIのETL(データの抽出・変換・格納)は、Power Queryで行います。Power Queryは、Excelにも搭載されている、Microsoftのデータ変換ツールです。
今回は元データを新たに作成しているので、ChatGPTの調整不足だった箇所の修正程度です。列名の変更と、Project IDからProjectの文言を削って整数型に指定しただけで済みました。
Power QueryでPower BIとデータソースの接続を確立できれば、データソースに新しいレコードが増えると、Power BIでも自動的にレコードが増えます。データ範囲を毎月変更する必要はありません。もちろん、データの中身の更新もPower BIに反映されます。
とりあえず読み込んだ
売上情報テーブル(TBL_Sales)を読み込んだ時点で、単純なグラフなら作成可能です。試しに、売上・原価をプロジェクトごとに並べてみましょう。
ただ、このままだとプロジェクト情報テーブルが持つプロジェクト名を表示させたり、プロジェクトカテゴリで集計したりすることができません。他のテーブルもPower BIに読み込んで、テーブル同士のリレーションを設定する必要があります。
データ設定
テーブル同士のリレーション設定
売上情報(TBL_Sales)、プロジェクト情報(TBL_Project)、人員情報(TBL_Person)の3つのテーブルをExcelファイルから読み込みました。さらに、日付データを持つテーブルとして、DateテーブルをPower BI上で作成しておきます。作成方法は後述します。
これら4つのテーブルを、Person IDやProject IDを使ってリレーション設定すると、このような関係になります。
Power BIには、テーブル同士の関係を視覚的に表示することができる「モデルビュー」があります。この画面上でテーブル同士のリレーションを設定したり、リレーションのカーディナリティ(1対1、1対多など)を変更したりできます。データの関係性を自動的に視覚化してくれるモデルビューの存在は、Excelにはない強みだと思います。
また、あるテーブルを中心に、別のテーブルが放射状につながっている、こういった形をスタースキーマと呼びます。スタースキーマは、Power BIにおけるデータモデリングで目指すべき形とされています。スタースキーマに近づくように、テーブルを分割したり、リレーションを組み直したりすることになります。
計算列・メジャー作成
さて、売上情報テーブルには、売上と原価の情報が含まれていますが、粗利高・粗利率の情報は含まれていません。これらは各行の売上・原価をもとに計算で求められるので、Power BI上で計算列として作成します。
計算列の作成には、DAX(Data Analysis Expressions)という表現を使います。Excelの関数と似ていて、割り算にはゼロ除算エラーを回避できるDIVIDE関数を使っています。また、書式や型を指定して、カンマ区切り整数やパーセントで表示されるようにしています。
売上の年間累計など、各行の値を計算した値を作るときは、メジャーを使います。こちらもDAX関数を使いますが、一般的な計算であれば、クイックメジャーというテンプレートを利用してDAX式を生成することができます。ローコードの本領発揮です。
DAXは、時系列の扱いに特に強みがあると感じます。「タイムインテリジェンス」という概念によって、前年比や移動平均なども簡単に確認できます。ここでは、粗利高の前年差と、粗利率の前年比(％)の、2つのメジャーを作成してみました。ローコードでの開発が可能とはいえ、慣れてくると結局コード(DAX)を書くのが手っ取り早かったり…
計算テーブル作成
リレーション設定時に登場した日付テーブルは、計算テーブルとして作成しました。計算テーブルは、DAXを使ってテーブルを作成する機能です。
CALENDAR関数で売上情報テーブルを参照して、最小の日付から最大の日付までの連続した日付をテーブルとして作成しています。また、Year・Month・Quarterは、それぞれ対応する関数があるので、それらの関数で日付を参照しているだけです。
コードをたくさん書いていて大変そうに見えるかもしれませんが、これらのコードは他のデータでレポートを作るときにも流用できます。たとえばCALENDAR関数であれば、TBL_Sales[Date] の部分を、日付データが入った列に置き換えるだけです。
日付テーブルとしてマーク
日付テーブルを作成したら、「日付テーブルとしてマーク」にチェックを入れるのを忘れずに。
日付の階層でドリルダウン・ドリルアップするには、日付テーブルを明示的に設定する必要があります。なお、「日付テーブルとしてマーク」できるのは、ある期間の連続した日付データを持つ、などの条件を満たすテーブルのみです。
データ準備はいったん完了
以上でデータ準備の工程はいったん完了ですが、レポート作成中にデータの不足があれば、データ準備の工程に戻ることもあります。これはプロのデータサイエンティストでも同様で、データ分析とデータ準備の調整を繰り返しながら進めるものなんだとか。
おわりに
前回の投稿の補完として、Power BIでのデータ準備の流れを解説しました。
データ準備は、実際のデータ分析業務の8割を占める、と言われているほどの重要な工程です。それがローコードでできるというところに、私はPower BIの大きな可能性を感じています。
