4回にわたり、Power BIでSaaSビジネスに関する指標を作ることを見てきました。今回はその中でも、少しやり方を変更すればDAX式がより最適になる例を紹介したいと思います。最適化を行うメジャーは3回目の記事にありますので、これをベースに進めていきます。
DAXの最適化の前に
最適化というトピックはPower BIを使い始めたばかりの人が気にする必要はないのですが、メジャーやレポート、レポート内で使用されるビジュアル（チャートやテーブル等）の動作が遅くなってしまう場合に調査が必要となります。
なお、DAX式の最適化を行う前に、まずはデータモデルが正しく構築されているかどうかを確認する必要があり、実は多くの場合において、
データモデルを最適化したらDAX式が簡単になった
ということが多いのです。DAX式が簡単になるということは、Power BIの計算エンジンであるVertipaqが単純なクエリを処理するだけで良くなることを意味し、殆どの場合において、
動作が早くなる
ことに繋がります。
今回はDAXの最適化についての記事ですので、「データモデルの最適化」について深く語りませんが、以下2つに留意してデータモデルを構築できると良いのではないかと思います。
- スタースキーマに従うモデル
- データモデルの最適化（インポートモデル）
Power BI（or Excel Power Pivot）は何といってもスタースキーマが大好きで、（通常、殆どこちらになりますが）インポートモードで作ったデータモデルは、上記2のベストプラクティスに従って、モデルを構築（不要列の削除、データ粒度・濃度に留意、データ型の設定、等）していくことで最適なパフォーマンスが得られるはずです。
IF構文の最適化
DAXの最適化に際して、最も多いパターンの1つがIF構文の最適化となります。簡単に言えば、IF構文を使用すると、
DAXクエリが複数回評価されてしまう
ことが起こり、場合によってはパフォーマンスに影響します。例えば、以下のメジャーについて見てみます。
- 顧客チャーン率（Customer Churn）
このメジャーは顧客離反率を計算したものですが、[Customer Retention]というメジャーが空白でなければ、1からそれを差し引いたものが、[Customer Churn]になる、と計算しています。なぜNOT ISBLANKを使用したかと言いますと、もし単純に1から[Customer Retention]を差し引いただけのメジャー（[Customer Churn_NoIF]）だった場合、以下のような形で結果が算出されてしまうからです。
IF + NOT ISBLANKという書き方はよく使用されるテクニックであり、目的は不要なセルに不要な計算をさせないことにあります。上記の例でいえば、2021年2月～3月は全ての顧客がトライアル期間でそもそもCustomer Churnは計算してはいけないことになりますので、この余計な結果を非表示させるのが目的だったわけです。
ここで、このDAX式のパフォーマンスを測定できるDAX Studioで測定してみます。
【測定方法】
- 上記リンクよりDAX Studioをインストールします
- デモファイルにて、メジャー[Customer Churn]があることを確認
※ [Customer Churn]は[Customer Retention]というメジャーが必要になりますが、これを計算するためのメジャーが更に4つ必要になりますので、これら全て揃っていることを確認
-
Power BIのタブ「外部ツール」からDAX Studioを立ち上げます
- DAX Studioの右上にある「Server Timing」をクリックし、左にある「Run」を「Clear Cache then Run」に変更
- Power BIへ戻り、以下のステップで「記録の開始」をクリック
- 以下の順番で「クエリのコピー」をクリック
-
DAX Studioに戻り、コピーしたコードをエディタに張り付け。この時、①～④の状態になっていることを確認
-
ここで③のコードを以下のように変更します
- 5行目を消す
- 9行目から一番下まで全て消して、EVALUATE __DS0Coreに変更
- 左上のRunをクリックし、結果を見る。以下のような結果になりますが、こちらがDAXクエリのパフォーマンス測定結果となります。
まず、クエリの詳細結果については、Queryという列に[Customer Churn]によって生成されたxmSQLという言語（SQLに似ていますが、人間が読解できるようにした構文）が生成されます。こちらを見れば、どのような処理が行われたかが簡単に分かるようになります。一方、左側の赤枠内の項目はそれぞれ以下の通りです。-
Total:
クエリを実行した際にかかった合計時間。この値が大きい場合、DAXクエリの最適化が必要になる。なお、ms = ミリセカンド = 1000ms = 1秒であり、今回の場合、FE（15ms）とSE（3ms）の合計（18ms）となる -
SE CPU:
SE（Storage Engine）において消費した全てのCPU時間（ms = ミリセカンド = 1000ms = 1秒）。×に数字が出た場合、同時処理が実行されたことを意味し、今回の場合、SE = 16msでx5.3と出ているので、その下の■ SE=3msは、通常であれば16msのところ、CPUコアの同時処理により5.3倍も処理時間が早くなったことを意味する - SE: Storage Engine
上述済。SEによる処理速度。右側のDuration列の合計と同じ -
FE: Formula Engine
1コア1スレッドでしか動かず、SEのように同時処理ができないものの、どんな複雑な計算も行うことができる。今回の場合、FEによる処理は15msかかったことを意味し、下のバー■■■■■■■■■はDAXクエリの処理時間のうち、83.3%はFE、16.7%SEによるもの、と解釈される -
SE Queries: :
DAXクエリを実行した際、右側のウィンドウに出ているクエリの数（行数） -
SE Cache:
SEにはキャッシュの概念があり、これが入っていると正確なパフォーマンス測定ができなくなる。通常、DAXクエリのパフォーマンスを測る場合、キャッシュをクリアしてから実行を行う。上記「Clear Cache then Run」に変更することで、クエリを実行する前にキャッシュがクリアされて、より正確にパフォーマンスの測定ができるようになる
-
DAXクエリのパフォーマンス測定はこのように行いますが、当然Totalの値が大きいほど、クエリパフォーマンスが悪いことを意味します。また、今回ではIF構文が入っていることで、SE Queriesの数が13個も出現しており、この数が数百・数千となった場合、1つずつのスキャンが早くても、合計で見た場合の速度は遅くなってしまう。そのため、
SE Queriesを可能な限り削減する
ことがDAXクエリの最適化の重要なポイントの1つとなります。
ここで、Power BI Desktopに戻り、[Customer Churn]を以下のように書き換えます。
前出のDAX式と何が違うかと言えば、[Customer Retention]というメジャーをそのまま使うのではなく、一回VAR（変数）として格納してから、再利用しているところです。このまま、DAX Studioに戻り、「Clear Cache then Run」を実行してみると、クエリパフォーマンスは以下のようになります。
結果はTotalが24msと上の18msよりも長くなってしまいましたが、45ms以下の変動は殆どが誤差であることから、単純にここが長くなったとしてもパフォーマンスが悪くなったことを意味しているのではありません。むしろ、注目すべきところはSE QueriesとSE Cacheの部分で、
- SE Queries: 13 => 7
- SE Cache: 6 => 1
となりました。なぜこのようなことになったかと言いますと、IF構文で[Customer Rentention]というメジャーは通常、2回評価されるのに対して、これをVAR（変数）に格納した場合は一回だけの評価で済み、これによって無駄なクエリが生成される必要がなくなったためです。今回はデータ量が少なく、そもそも60ms以下という非常に速いパフォーマンスであったため、その差をはっきり見ることはできなかったのですが、データ量が増えた場合、このような考え方を持っていないとDAXクエリが遅くなってしまう可能性が出てきます。
そして矛盾するような言い方になってしまいますが、今回のようにデータ量によって、IFを最適化しなくても問題ないという判断も間違いではありませんので、信じられないほどのデータ量でBIモデルを構築していない限り、IFを最適化しないといけないという固定概念を持たなくても良いと思います。
次回は計算列を使った最適化について紹介していきます。
