Power BI でのクエリ診断の記録

[アーティクル]
03/23/2023

Power Query で作成するときの基本的なワークフローは、データソースに接続し、いくつかの変換を適用し、必要に応じて Power Query エディターでデータを更新してから、Power BI モデルに読み込むというものです。 Power BI モデルに読み込んだら、サービス内で行う更新とは別に、(Desktop を使って分析を表示している場合に) Power BI Desktop で随時更新できます。

エディターで更新しても、Power BI 本体で更新しても、作成ワークフローの終了後には同じような結果になるかもしれませんが、このソフトウェアで提供されるユーザーエクスペリエンスはさまざまであり、実行できる評価も多岐にわたります。このようなさまざまなワークフローでクエリ診断を行うときにさまざまな診断データに驚かないように、想定すべきことを把握するのが重要です。

クエリ診断を開始するには、Power Query エディターのリボンで [ツール] タブに移動します。ここにはいくつかのオプションが表示されます。

クエリ診断コントロール。

ここには、[ステップを診断する] と [診断の開始] ([診断の停止] と対になっています) という 2 つの主要なオプションがあります。前者は、選んだステップまでのクエリに関する情報を提供するものであり、ローカルまたはリモートで実行されているクエリ内の操作を理解するために最も役立ちます。後者は、後述する他のさまざまなケースについて、より詳細なインサイトを得ることができます。

コネクタの詳細

クエリ診断に表示されるさまざまな組み合わせをすべて網羅する方法はないということに注意してください。結果に表示される内容を変更できるものはたくさんあります。

コネクタ
適用される変換
実行しているシステム
ネットワーク構成
詳細な構成の選択
ODBC の構成

このドキュメントでは、最も広範な範囲をカバーするために、SQL と OData の両方の Northwind Customers テーブルのクエリ診断に焦点を当てます。 OData ノートでは OData.org Web サイトにあるパブリックエンドポイントを使いますが、SQL サーバーはご自身で用意する必要があります。多くのデータソースはこれらとは異なる点が多いため、今後、コネクタ固有のドキュメントを追加する予定です。

診断の開始と停止

[診断の開始] と [診断の停止] は [ステップを診断する] よりも広範囲に適用できますが、提供される情報が多いので、整理する必要があります。たとえば、診断を開始し、プレビューを更新してから停止すると、すべてのステップで [ステップを診断する] を実行するのと同等の情報が得られます (Power Query のエディターでは、各ステップが独立して更新されるしくみであるためです)。

記録を開始するには、[診断の開始] を選択し、必要な評価 (作成、プレビュー更新、完全更新) を実行してから、[診断の停止] を選択します。

Authoring

作成ワークフローの主な違いは、他のワークフローに比べてより多くの個別の評価が生成されることです。主要なクエリ診断の記事で説明したように、これらはナビゲーターやフィルターのドロップダウンなどのさまざまなユーザーインターフェイスに入力した結果です。

これからその例を紹介します。このサンプルでは OData コネクタを使っていますが、出力を確認するときは、同じデータベースの SQL バージョンも確認します。どちらのデータソースでも、[新しいソース]、[最近のソース]、[データの取得] のいずれかを使ってデータソースに接続します。 SQL 接続の場合はサーバーの資格情報を入力する必要がありますが、パブリック OData エンドポイントの場合は前述のリンク先エンドポイントを入力できます。

OData 接続。

接続して認証を選んだら、OData サービスから Customers テーブルを選びます。

Northwind のナビゲーション。

これで Power Query インターフェイスに Customers テーブルが表示されます。さまざまな国/地域に何人の営業担当者がいるかを知りたいとします。まず、[連絡先の肩書き] 列で [営業担当者] を右クリックし、[テキストフィルター] にマウスポインターを合わせ、[次の値と等しい] を選びます。

連絡先の肩書きにテキストフィルターを適用します。

次に、リボンから [グループ化] を選び、[国] でグループ化を行い、[カウント] で集計します。

[グループ化] を適用します。

その結果、次のようなデータが表示されます。

結果。

最後に、リボンの [ツール] タブに戻り、[診断の停止] を選択します。その結果、トレースが停止し、診断ファイルが自動的に作成され、左側に概要と詳細のテーブルが表示されます。

作成セッション全体をトレースした場合、一般には、ソースクエリの評価、関連するナビゲーターに関連する評価、適用した各ステップに対して発行された少なくとも 1 つのクエリ (実行した UX アクションによっては、さらにその他の情報) が表示されます。コネクタによっては、パフォーマンス上の理由から並列評価が実行され、よく似たデータセットが生成されることがあります。

[プレビューの更新]

データの変換が完了すると、一連のステップが 1 つのクエリになります。 Power Query エディターで [プレビューの更新] または [すべて更新] を押した場合、クエリ診断に 1 つのステップだけが表示されることはありません。その理由は、Power Query エディターで更新を実行すると、最後に適用したステップで終わるクエリが明示的に更新され、適用したステップをさかのぼってステップ実行され、ソースからその時点までのクエリが更新されるからです。

つまり、ソースとナビゲーターを含む 5 つのステップがクエリにある場合、診断には 5 つの異なる評価が表示されることになります。時系列で見ると、(常にではありませんが) 多くの場合、最初の評価に最も時間がかかります。これには 2 つの異なる理由があります。

入力データがキャッシュされているため、後から実行されるクエリ ([ユーザークエリ] の前のステップを表します) の方がローカルでより速くアクセスできる可能性があります。
返す必要があるデータ量を大幅に切り捨てる変換が適用されている可能性があります。

[すべて更新] とはすべてのクエリを更新することなので、おわかりとは思いますが、目的のものだけをフィルター処理する必要があることに注意してください。

完全更新

クエリ診断は、Power Query エディターのエクスペリエンスだけではなく、Power BI の更新時に発行される、いわゆる '最終クエリ' の診断にも使用できます。そのためには、まずモデルにデータを一度読み込む必要があります。これを行うことを計画している場合は、[閉じて適用する] を選択するとエディターウィンドウが閉じる (トレースが中断される) ため、2回目の更新で実行する必要があるか、[閉じて適用する] の下にあるドロップダウンアイコンを選択して、代わりに [適用] を選択します。

いずれの場合も、エディターの [ツール] タブの [診断] セクションで [診断の開始] を選択します。これが完了したら、モデル (または必要なテーブルのみ) を更新します。

テーブルを更新します。

モデルへのデータの読み込みが完了したら、[診断の停止] を選択します。

メタデータとデータクエリの組み合わせが表示されます。メタデータの呼び出しを行うと、データソースに関する情報を取得できます。データ取得とは、データソースにアクセスし、複数の操作がフォールディングされた完成したデータソースクエリを発行し、その上で不足している評価をローカルで実行する処理です。

注意すべき点は、診断でリソース (データベース、Web エンドポイントなど) やデータソースクエリが表示されたとしても、必ずしもネットワークアクティビティを実行しているわけではないということです。 Power Query によって、この情報がキャッシュから取得されることがあります。今後の更新では、診断を容易にするために、情報がキャッシュから取得されたものかどうかを示す予定です。

ステップを診断する

[ステップを診断する] は、あるステップまでにどのような評価が行われているかを把握するために便利であり、そのステップまでのパフォーマンスや、クエリのどの部分がローカルまたはリモートで実行されているかを特定するのに役立ちます。

先ほど作成したクエリに [ステップを診断する] を使うと、10 行ほどしか返ってこないことがわかります。データソースクエリで最後の行を確認すると、データソースに最終的に発行されるクエリがどのようになるかが大まかにわかります。このケースでは、Sales Representative (営業担当者) はリモートでフィルター処理されましたが、グループ化はローカルで行われたことが (消去法で) わかります。

フィルター処理され、グループ化された Customers テーブルの診断。