はじめに
本記事は Microsoft Power BI Advent Calendar 2024 の初日の記事です。
Power BI ベストプラクティス
Power BIは簡単にはじめられるツールではありますが、徐々に規模が大きくなってくると、行き当たりばったりの運用方法ではつらくなります。
データソース、データ処理、リレーションシップ、データモデル、共有の仕方、セキュリティ…🤔
しかし、利用ユーザーが多く、たくさんのコミュニティがあり、Microsoftがドキュメントをきちんと公開してくれているのもPower BIの強みです。数々の先人たちの気づきが機能に落とし込まれ、ベストプラクティスとしてドキュメントに公開されています。
ドキュメントは以下にまとまっています
Power BIの最適化ガイド
ただ、それらを文字面だけで理解していませんか
文字面だけで理解したつもりになるのではなく、その理由を明確しておくことは、Power BIを運用している組織にとって有益です。
ベストプラクティスに沿った運用を進めるために、組織内で声をあげたとき、
「なぜ？」 と聞かれるかもしれません。
そのときに「〇〇だから」と答えられるようにしておきましょう。この記事はその第一歩です✊
クエリフォールディングの重要性
今回取り上げるベストプラクティスは「クエリフォールディング」です。
クエリフォールディングの重要性は以下ドキュメントに書かれています。
クエリがリレーショナル ソースに基づいていて、単一の SELECT ステートメントを作成できる場合は、クエリ フォールディングを確実に発生させるようにすることで、"データ更新の最高のパフォーマンス" を達成できます。 変換を処理するために Power Query マッシュアップ エンジンが引き続き必要な場合は、特に大規模なセマンティック モデルの場合に必要な作業を最小限に抑えるように努める必要があります。
クエリフォールディングの活用
Power BIにはクエリフォールディングという機能があります。この機能は対応しているデータソースであれば、データソース側に処理を委任することができる機能です。データソース側での処理部分を増やし、Power BI Desktopや、Power BI Serviceの処理リソースを最小限にできます。
クエリフォールディング対応データソース
対応データソースは、Microsoft SQL ServerやODataフィード、Amazon Redshift、Dataverse、Google BigQuery、PostgreSQLなどがあります。
クエリフォールディング非対応データソース
一方、以下のデータ ソースではクエリ フォールディングはサポートされていません。
CSVファイルやExcelファイル、SharePointリストなどです。
具体例をみてクエリフォールディングを理解しよう
具体例をみてみると、クエリフォールディングがどういったものなのかわかりやすいです。
サンプルデータ
ContosoのSalasデータを用意します。
中身はまったく一緒ですが、
- データソースがCSVファイル
- データソースがSQLサーバー
の2種類を用意しました。行数は100万行ほどです。
データ処理をしてみる
以下の工程をPower Queryでデータ処理をやってみます。
- ３列にしぼる（OrderKey,OrderDate,Quantity）
- Quantityが5以上にフィルター処理
当然元データは一緒なので、同じデータ加工をすると、同じデータができあがります。
ネイティブクエリの表示
クエリフォールディングに対応したデータソースの場合、対応したデータ操作をしたあとの場合は、PowerQueryのステップを右クリックすると「ネイティブクエリを表示」という項目が表示されます。
クリックすると、こういったかたちでSQL Queryが表示されます。
Power Queryで作成したステップを遅延評価し、SQL文を作成してくれます。
図に書いてみる
さて、このネイティブクエリは何に使うのか。
クエリフォールディング対応データソースの場合は、データソース側にデータ処理を委任できるのは前述したとおりです。つまり、図にすると以下の通りで、Power BI（Power Query）側でデータ処理をつくったあと、実際に適用する際にこのネイティブクエリが利用されます。
SQL Serverにこのネイティブクエリが送信されるわけです。
クエリフォールディングに対応していないデータソース（CSV）の場合は、いったんすべてのデータを取得したあとにデータ処理がはしります。100行程度のデータなら問題ないですが、これが1億行、10億行となっていくと、なかなかつらくなってきます。
一方、クエリフォールディングに対応しているデータソース（SQL Server）の場合は、ネイティブクエリがデータソースに投げられるため、図でいうと処理２まではデータソース側で行われます。
さらにいうと、今回の例でいうとフィルター処理もデータソース側で行われているため、そもそもとりこむ行数を絞り込むことができています。
「データの取得」を実際にしてみると、SQL Serverから取り込んでいる方はすぐに取込みが終わります。もともと100万行あるデータベースから、48万行を取り込んでいます。前述している通り、この処理はすべてデータベース側で行われて、Power BI側のリソースはデータ処理後のデータを取り込むことだけに使われています。
一方、CSVデータを取り込んでいる方は、まだデータ取り込みの途中です。全データを取り込んでからデータ処理が始まるので、今回の場合は 完了時間は10倍以上の差 がありました。
クエリフォールディング非対応の操作
ただ、クエリフォールディング対応データソースでも、クエリフォールディングが無効になってしまうデータ操作もありますので注意しましょう。
カスタム列の追加
カスタム列をPower Queryで追加すると、クエリフォールディングが無効になります。Power Query内で行うカスタム計算や条件に基づく新しい列の作成は、データソースに委任することができません。当たり前ですよね。このため、カスタム列の処理はローカルで実行され、パフォーマンスが低下します。
複雑な変換操作
Power Queryでの複雑な変換操作も、クエリフォールディングが適用されないケースがあります。たとえば、次のような操作はデータソース側での実行が難しく、Power Query側でのローカル処理に頼ることになります。
- 型の変換
- 複雑な文字列操作（例: 正規表現を使った変換）
- 日付の詳細な操作（例: 月単位での集計や動的な日付計算）
- 条件分岐を含む変換（例: IF文による変換）
これらの処理は、Power Queryの内部で処理されるため、データソース側での負荷が軽減されません。とくに、大規模なデータセットを扱う場合には、これらの操作がボトルネックとなる可能性があります。
マージや結合（Join）の処理
複数のデータソースを マージ（結合）する際、すべてのデータソースがクエリフォールディングに対応していない場合、結合処理がローカルで実行されることがあります。この場合、データソース間でのデータ移動や変換が増え、パフォーマンスが低下することが考えられます。
とくに、Excel ファイル や CSV ファイル のような非対応データソースを結合する際には、処理が遅くなることがあります。クエリフォールディングを最大限活用するためには、結合するデータソースの選定に注意が必要です。
グループ化や集計処理
グループ化や集計（例: SUM、COUNT、AVERAGEなど）は、データソース側で処理できる場合が多いですが、これらの処理が複雑になると、クエリフォールディングが無効になることがあります。とくに、複数の変換ステップを経て集計を行う場合、ローカルで集計が行われることがあります。
一部の関数や演算子の使用
Power Queryで使用される一部の関数や演算子が、データソース側で対応していない場合もあります。たとえば、特定の統計関数や高度な数学的計算は、Power BI側で実行され、データソース側では処理されません。これにより、処理速度やリソース使用量が増加する可能性があります。
対応方法
クエリフォールディングを邪魔する操作をしなければならない場合、そのステップはできる限り後ろにもっていきましょう。クエリフォールディングが効かない＝Power BI側での処理になります。フィルター処理や列を減らす操作（SELECT）などが先にきていれば、Power BI側で行う処理が少なくなるので、良いことです。
まとめ
いかがだったでしょうか。クエリフォールディングは、Power BIのパフォーマンスを最適化するために非常に強力な機能です。しかし、すべての操作がクエリフォールディングに対応しているわけではなく、とくに複雑な変換やカスタム列の作成などが非対応となります。
SharePointやExcelなどのデータソースから脱却する必要が出始めている組織は、クエリフォールディングのことも考えながらデータをストアする製品の選定を進めてみてください。
Happy Power BI🎉
参考記事