Power BIではモデリングのベストプラクティスの1つに、不要な列をデータモデルに読み込まないルールが有名です。不要な列が減れば、データモデルのサイズが軽くなり、その結果DAXエンジンが計算を行うためにデータをスキャンする時間も短くなります。当たり前のことですが、モデリング担当者からは
後から必要なデータがあるかもしれないので、列を削れない
という声を度々聞きます。ユーザーが最初から分かっている場合は既に作成しているレポートを参考に、必要な属性列を確認できますが、そうでない場合は柔軟に列を追加・削除できるようなロジック(データの変換)が必要となってきます。今回はこれを実現できる(かもしれない)手法を1つ紹介したいと思います。
※ 無音動画にしていますので、動画を観ながらやり方を学びたい方はこちらをご参考ください
サンプルデータ
今回の事例はSQLサーバーのデータを使用しますが、同じようにExcelやCSVでも対応することができます。
- データソース
SQLサーバーのContoso Retail DW - 対象テーブル
FactSalesテーブル - 行数
約340万行 - 列数(未加工時)
25列(レコード型列を含む)
列選択の手順
- データを読み込む
- テーブルを選択し、「データの変換」をクリック
- 読み込まれたFactSalesの名前をsqSales(sq=staging query)に変更し、右クリック > 「読み込みを有効にする」をOFF
-
sqSalesをもう一度右クリック >「参照」> 名前をSchema_Salesに変更し、同じように「読み込みを有効にする」をOFF
- 数式バーにあるfxをクリックし、Table.Schemaで囲う
sqSalesにある列名およびその他メタデータ情報(例:列のポジションやデータ型等)が取得されます。 - Name列をコピーし、「データの入力」に張り付け、名前をlist_salesに変更
- 列を追加し、列名をSelectedに変更し、必要な列には1を入力していく
- list_salesというテーブルが生成される
- Selected列でnullを除外し1を選択する
- Name列を右クリック > 「ドリルダウン」
- list_salesはリスト型クエリになる。このlist_salesは必要な列となりますので、これをこの後活用して、列を選択していきます
- sqSalesを右クリック > 「参照」> Salesというクエリを作成
この段階ではまだ25列あることが確認できる。 - SalesKeyを右クリック >「他の列の削除」
Power Queryの特性ですが、「他の列の削除」を行うと、Table.SelectColumnsとなりますので、結果は上の通り、SalesKey列だけが残ります。 - {"SalesKey"}をlist_salesに置き換え
- 必要な列だけを抽出した最終的なSalesクエリの完成!
{"SalesKey"}はリストであり、Table.SelectColumns(Source, {"SalesKey", "DateKey"})のような形式で、列を選択していきます。{..}をlist_salesというリストに置き換えただけですが、これだけでステップ7で指定した列をまとめて抽出できたことになります。
この方法は非常に効率が良く、上記SalesクエリでUnitCost, UnitPrice, ReturnQuantity等の列が不要となった場合、list_salesのSourceに戻り、1にすべき列を変更します。
すると、この状態でもう一度Salesクエリに戻ると、削除した列は消え、追加された列が正しく選択されていることが分かります。
まとめ
今回は短めですが、ファクトテーブルの列を効率よく選択・削除するやり方を紹介しました。以下今回のまとめです。
- モデリングを行う前に、必要な列を確認しておく
- ExcelやCSVでも今回と同じこと実行することが可能
- 読み込んだ状態のクエリ(今回ではFactSales)をそのまま最終クエリとして使用するのではなく、sq○クエリ(ステージングクエリ)として使用し、列に関する必要な情報を取得してから、列の選択を行う
ちなみに、今回は紹介していないですが、クエリフォールディングが可能なデータソースは何ら影響を受けないため、クエリパフォーマンスは最適化された状態でデータモデルへ読み込まれるようになります。