[Power BI] クエリプランを使ってPower Queryのクエリフォールディングを調べてみる。

# はじめに

クエリプランというプレビュー機能があるので、それを触ってみることでクエリフォールディングの勉強をしてみました。

---

**PBIJP Power Query 秘密特訓「虎の穴」炎の復活編 #11** で発表しました。

https://powerbi.connpass.com/event/238166/

---

## Power Query Onlineからデータフローを新規作成する

---

**Power Query Online** = データフローの作成

ワークスペースからデータフローの新規作成を選択し、空のクエリを選択し、下記のPower Queryを記述（コピペ）します。

---

```
let
  Source = Sql.Database("servername", "database"),
  Navigation = Source{[Schema = "Sales", Item = "SalesOrderHeader"]}[Data],
  #"Removed other columns" = Table.SelectColumns(Navigation, {"SalesOrderID", "OrderDate", "SalesOrderNumber", "PurchaseOrderNumber", "AccountNumber", "CustomerID", "TotalDue"}),
  #"Filtered rows" = Table.SelectRows(#"Removed other columns", each [TotalDue] > 1000),
  #"Kept bottom rows" = Table.LastN(#"Filtered rows", 5)
in
  #"Kept bottom row
```

---

:::note
`servername `と` database `を、ご自身の環境に適した名前へ置き換えます。
:::

---

![new_2022-02-19-09-53-50.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/ed7fde7b-9358-5e26-0c64-e9cfc372b6c9.png)

---

![new_2022-02-19-09-58-31.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/120cca6a-b7db-a740-c534-6debc0f55056.png)

---

ただしこれは、Microsoftが用意してくれている`Adventureworks`のサンプルデータベースを元に記述されているので、アクセス可能なデータベースで試してください。

---

https://docs.microsoft.com/ja-jp/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=ssms

---

これをPower Query Online から**空のクエリ**を呼び出し貼り付けます。自分の環境では下記のように手元のデータで同じ処理を作成してみました。

---

![new_2022-02-19-11-12-14.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/1c414281-37d9-063c-d5ea-fb5d8b8cc199.png)

---

すると、結果はこのようになります。ページ数1000P以上で絞り込んだあとに、`Table.LastN`でラスト5行に絞り込んでいるので、結果は5行のみのデータです。

---

*※Table.LastN関数*

https://docs.microsoft.com/ja-jp/powerquery-m/table-lastn

![new_2022-02-19-11-14-10.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/2ddb19dd-1b66-a0fb-4f2e-301213eefecc.png)

---

## ステップ フォールディング インジケーターを確認

ステップ フォールディング インジケーター 🤔 と言われると難しいですが、「クエリ設定」を押すと右側に出てくるカラムのことです。Power Queryの処理がステップごとに表示されています。

---

![new_2022-02-19-11-19-04.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/615a5613-d299-983a-55b1-6e8536b02e63.png)

---

さらに細かくみると、ステップごとの右側に出てくるマークが異なることがわかります。

緑色のマークはクエリフォールディングが行われている部分、赤色はPower Queryエンジン側で処理が行われている部分を表します。

![new_2022-02-19-11-25-21.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/bfd49e45-8b85-f479-8611-7a9762059556.png)

---

:::note
クエリ フォールディングの目的は、クエリの評価の多くを、クエリの変換を計算できるデータ ソースにオフロードまたはプッシュすることです。
クエリ フォールディング メカニズムでは、Mスクリプトをデータ ソースが解釈して実行できる言語に変換することで、この目的を達成します。
次に、その評価をデータ ソースにプッシュし、その評価の結果をPower Queryに送信します。多くの場合この操作によって、データ ソースから必要なすべてのデータを抽出し、Power Queryエンジンで必要なすべての変換を実行するよりもクエリの実行がはるかに高速になります。
:::

---

## クエリのステップを選択してそのクエリ プランを確認

ステップを右クリックしてクエリプランを確認します。

---

![new_2022-02-19-12-01-52.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/4a51d1b3-883d-7af7-304f-a696f2a69c8b.png)

---

クエリプランがビジュアライズされて表示されます。**詳細を表示する**をクリックすると実際にSQLサーバーに投げられているネイティブクエリを確認することもできます。

---

![new_2022-02-19-12-07-22.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/73406d43-fca6-7bc5-a222-883e88d9ad65.png)

---

![new_2022-02-19-12-10-53.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/d9b2d67c-1f0a-5842-55ef-d54254efdb0f.png)

---

ここで、最後に実行されている`Table.LastN関数`の処理をみると**フルスキャン**という表記があります。これはテーブルに格納されている全行を読み取る処理を行っているということなので、行数が多いデータになればなるほど処理には時間がかかり、制限時間を超えた場合には処理が終わらないということも考えられます。

---

## クエリフォールディングが機能するようにPower Queryを修正する

---

このままでは処理が遅いので、最初に書いたクエリを修正していく必要があります。Table.LastN関数はテーブルの最後5行を検索する処理だったので、それと同じことを別の手法で実装します。

---

#### 降順で並びかえ

![new_2022-02-19-12-50-09.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/f28caf54-68b0-1d5f-b54c-7f20b99d5938.png)

---

#### 上位の行を保持

![new_2022-02-19-12-50-42.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/743a77b8-f9da-1c4a-757d-2c6e4479e1f5.png)

---

![new_2022-02-19-12-50-56.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/2c55da36-0fef-88fb-4a85-bb775196dbd2.png)

---

#### ステップ フォールディング インジケーターを確認

赤いマークが出ていない :+1:

![new_2022-02-19-12-51-07.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/67994384-32ce-6a07-ea80-71f8af8b86bc.png)

---

#### クエリプランを確認

フルスキャンの項目がなく、すべてデータソース側での処理となっています。

---

![new_2022-02-19-12-51-20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/80ac39b1-5326-08cf-460d-d21b6744617b.png)

---

## クエリフォールディングを阻止する処理一覧

これがすべてではない。

https://docs.microsoft.com/ja-jp/power-query/power-query-folding#transformations-that-prevent-folding

---

- 異なるソースに基づいたクエリの結合。
- 異なるソースに基づいたクエリのアペンド (結合)。
- 複雑なロジック によるカスタム列の追加。 複雑なロジックとは、データ ソースに同等の関数を持たない M 関数の使用を意味します。 たとえば、次の式では、OrderDate 列の値を書式設定します (テキスト値を返します)。

```
Date.ToText([OrderDate], "yyyy")
```

- インデックス列の追加。
- 列のデータ型の変更。
- Power Query クエリに複数のデータ ソースが含まれる場合、データ ソースのプライバシー レベルに互換性がないためにクエリ フォールディングを実行できない可能性があります。

---

## パフォーマンス比較

下記Docsではパフォーマンス比較まで行ってくれています。めちゃ参考になるので必読です :goat: 元のソースで処理をしてからデータがくるので、パフォーマンスが段違い！ :tada:

---

https://docs.microsoft.com/ja-jp/power-query/query-folding-examples

---

処理行数比較

![new_2022-02-19-13-08-53.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/88f755b8-cc14-44f6-362b-ad596b452bd2.png)

---

処理時間比較

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/80467/7c2e0e14-5536-ddc5-c622-8cd119dcc842.png)

---

## まとめ

元のソースで処理をするのが大事、クエリフォールディング大事・・・と何度も言われてきたけどわかっていませんでした :cry:
実際に手を動かして、調べみるのがもっと大事・・という話。

---

## 参考資料

https://docs.microsoft.com/ja-jp/power-query/query-plan