テクテク日記

テクテク＝テクノロジー&一歩ずつ（テクテク）

Power BIで月次分析をさらにスマートに②_DAXのパフォーマンス解析

DAX / Data Model Power BI

少し前に書いた記事では、Monthlyベースの日付テーブルの違いや、使用する際に留意すべき点、モデリングを変更する必要性についてお話しました。今回は、MonthlyベースとDailyベースの日付テーブルのそれぞれで計算した結果に関するクエリパフォーマンスについて見てみましょう。

クエリパフォーマンスの考察

検証シナリオとしては以下の2つがあります。

  1. dCalendar_monthlyとdCalendar_dailyでSalesテーブルをクエリした場合
    ※データ量が少なく、結果はほぼ誤差の範囲になってしまうため、この検証はスキップ

  2. dCalendar_monthlyとdCalendar_dailyでSalesDailyテーブルをクエリした場合

なお、テスト用のPCスペックは以下の通り。ハードウェアセレクション及びパフォーマンス測定についてはこちらをご参照ください。

ここでDAX Studioでパフォーマンスの測定をやってみます。

  1. Power BI DesktopからDAX Studio*1を立ち上げます
  2. Power BI Desktopに戻り、年月+SalesAmtのビジュアルを作る
  3. パフォーマンスアナライザーを✅
  4. ① 記録の開始 > ② 視覚エフェクトを分析 > ③ クエリのコピー

  5.  DAX Studioに戻り、クエリを貼り付け（④）、⑤～⑦の順番でクリック

これでDAX Studioによって、クエリパフォーマンスを測定することができるようになりました。なお、⑤はクエリパフォーマンスの結果を表示させるために必須であり、⑥はクエリキャッシュをクリアして、コールドキャッシュ*2の状態でテストを可能にしてくれます。

結果であるDAXクエリのパフォーマンスの測定ですが、以下①～⑤の順番で見ると良いでしょう。
※ FEとSEについては、こちらもご参照

  • ① Total: 数字が小さいいほど良い
    • ms（ミリ秒、1000 ms = 1秒）で表されており、数字が大きい程クエリが遅い
    • 60 ms以下は全て誤差扱いとして考えて良い（例：100～200 msを60 msにする場合はGoodだが、60 msを40 msにするのは誤差の範囲内という考え方）
    • データ量によって、200 msであってもパフォーマンスが良いとされる場合もある
      例：同じクエリを使用して合計数億行のデータのサブセットをスキャンした場合の200 msと、同数十万行をスキャンした場合の200 msは前者がより優れていると言える
  • ② SE CPU: 数字が大きくても問題なし。x1.9の青字が重要
    • SE（Storage Engine）が複数のコアで処理を行った場合にx○と出てくることがある。ここで、xは倍率を表し、値が大きいほど複数のコアで同時処理されていることを示す。倍率は、テストに使用しているPCのスペックに左右されるため、異なる環境では値が異なる
  • ③ FEとSE: 構成グラフ
    • 上図の例では、SEのCPU時間は合計で63msであり、複数コアの使用により実際の処理時間は33msとなる（63 ÷ 1.9）
    • 33msは複数のコアを使用したSEによる処理時間。ただし、DAXクエリの処理を完了するためには、必ずFE（Formula Engine）を経由する必要がある。構成グラフの比率は、SEが82.5％（33÷（33 + 7））を占め、残りの時間はFEが費やした時間となる
    • 基本的には、処理をSE側にプッシュすると、Total時間が短くなる傾向がある。ただし、SEは単純な処理や一時的にテーブルをマテリアライズするような処理に限定されるため、全ての処理を効率的に行うことができるFEが100%になる場合も存在する。最も望ましいのは、可能な限り少ないメモリ、かつ少ない回数でSEをマテリアライズさせた結果をFEに渡して最終処理を行ってもらうことである
  • ④ Rows: SEがスキャンした凡その行数
    正しい行数ではないため、あくまで目安として見ること。正確な行数はQuery Planから確認可能


    ④の左側にあるDuration列とCPU列の合計が②と③で解説した値と一致する。通常、スキャンされた行数が多い程、時間が掛かっている可能性が高いため、百万行を超えるようなスキャンが発生している場合には特に留意が必要となる

  • ⑤ SE Queries
    • これはSEが実行したクエリ数であり、クエリの数が多いほどパフォーマンスに影響する可能性がある。一概には言えないが、20クエリ以上の場合は、クエリ数を減らしたり、チューニングを行うことが推奨される
    • SEクエリの数が多いということは、処理するクエリが多いことを意味するため、メジャー同士の参照であったり、複雑なDAXクエリが原因である可能性がある

① Totalの数値からスタートし、パフォーマンスが遅い場合に②～⑤までどこがボトルネックになっているかを判断するのがセオリーとなります。

最初のdCalendar_monthlyに関しては、Salesのデータ量が少なかったため、全てが誤差の範囲内に収まりました。一方、2番目のSalesDailyについては、パフォーマンス的には30〜40msの範囲でMonthlyとDailyの日付テーブルの間に大きな差異は観測されませんでした。

データ量が数百万件という世界では、ベースメジャー（売上数量、売上高、利益額）で計測した場合、Monthlyの日付テーブルであろうが、Dailyベースの日付テーブルであろうが、パフォーマンスに大きな差は出ない

ことが分かりました。人によっては数百万件というデータが大量なデータであるという感覚もあると思いますが、少なくともPower BIの世界ではスモールデータの部類に入ります。

DAX式別のパフォーマンス比較

このままでは面白くないので、ベースメジャー*3ではなく、下記応用メジャーの比較をしてみたいと思います。CALCULATE等を使った応用メジャーは書き方一つでクエリパフォーマンスが大きく異なってしまうことがあります。

典型的な事例に

DISTINCTCOUNTに対するテーブルのフィルター処理

があり、以下のような書き方が非常にBad Practiceとして有名です。

この式は

  1. SalesDailyテーブル（Fact）にあるProductKey列で1000を超えるものを抽出
  2. 抽出されたものをフィルター条件として、ProductKeyをカウントする

というメジャーですが、CALCULATEを使う場合、

テーブル全体ではなく特定の列でフィルター

することがベストプラクティスであることを意識するが重要です。これを遵守していなかった場合、DAXメジャーのパフォーマンスが著しく劣ってしまうことがあります。この式で算出される結果は以下の通りです。

既に解説の通り、①のTotalが大きい程、クエリパフォーマンスが悪いことになりますが、この例ではたった数十行の結果を表示させるのに、約2秒間も掛かっていたことになります。スキャンの行数も約4.6万行となっており、②のSE Queriesも38個発生したことになります。

”塵も積もれば山となる”ということわざの通り、38×Duration（平均50と仮定）≒Total（1,887ms）となることが確認できます。

では、FILTERを使わなかった場合の結果はどうなるのでしょうか？DAX式は以下の通りとなり、同じセマンティックを保つため、KEEPFILTERSが使用されています。

これは下記従来のメジャー（便宜上、「最適化前」と表現）は

# of SalesKey over 1000 =
 CALCULATE(
    DISTINCTCOUNT( SalesDaily[ProductKey] ),
    FILTER(
        SalesDaily, SalesDaily[ProductKey] > 1000
    )
)

以下のように最適化された状態となり、

# of SalesKey over 1000.fast = 
CALCULATE(
    DISTINCTCOUNT( SalesDaily[ProductKey] ),
    KEEPFILTERS( SalesDaily[ProductKey] > 1000 )
)

上記最適化されたメジャーは下記メジャーの糖衣構文（Syntax Sugar）となります。

# of SalesKey over 1000.fast = 
CALCULATE(
    DISTINCTCOUNT( SalesDaily[ProductKey] ),
    KEEPFILTERS( 
        FILTER(
            ALL( SalesDaily[ProductKey] ),
            SalesDaily[ProductKey] > 1000
        )
    )
)

この辺りの詳細については、下記を参考にすると分かりやすいと思います。

最適化された状態のDAXクエリのパフォーマンスは以下の通りとなりました。

Totalは87 ms、SE Queriesはたったの2つに減りました。最適化前のクエリの約22倍速いパフォーマンスとなったことが分かります。DAX式を少し変更するだけでもパフォーマンスが天と地の差になる可能性がある事例でした。

おまけ

DAX式のパフォーマンスの最適化となってしまいましたが、最後に「年月×メジャー」でなく、「年月日×メジャー」で測定した場合のパフォーマンスについて見てみます。従来はdCalendar_daily[YearMonthName]で見ていたのですが、これをデイリーベースのdCalendar_daily[Date]に変更してみた場合は以下の通りです。

まずは最適化されたバージョンはTotalが300 msという結果になりました。

一方、最適化前のバージョンは56,110 msとなり、最適化バージョンの約187倍も遅い結果となりました。

このことから、粒度が細かい程、DISTINCTCOUNTのような負荷のかかるメジャーを誤ってテーブルフィルターで記述した場合のインパクトは計り知れないことが判明しました。

まとめ

月次データに使用する日付テーブルですが、Daily、Monthlyベース別にパフォーマンスに大きな差が出ることはあまりないと言えます。しかしながら、Power BIを使う上で応用メジャーを構築することがあれば、誰しもDAXメジャーのパフォーマンスに遭遇する可能性があります。DAX Studioでクエリパフォーマンスを測定することになりますが、Power BI Desktopのパフォーマンス アナライザーを使うことで誰でも簡単に測定することができます。

DAXを記述する上で最も重要なことは、DAX式はテーブルフィルターではなく、Power BIの分析エンジンがより効率よくスキャンできるよう、列フィルターでDAXメジャーを構築することになります。

なお、DAX Studioの測定結果に対する解釈は本記事でほぼ全て重要な部分についてはカバー済みであり、何度も読み返してみると理解が深まると思います。

*1:無料でダウンロード可能

*2: キャッシュは本来、処理の高速化やデータストアの負荷軽減のために使用しますが、コールドキャッシュは「空っぽのキャッシュ」＝キャッシュがない状態を意味する

*3:単純にSUMといったDAXで作ったメジャーで、後に応用メジャーの参照用となるもの