テクテク日記

テクテク=テクノロジー&一歩ずつ(テクテク)

DirectQueryのパフォーマンスの変化に関する考察

Power BIを活用している方であればインポートモードとDirectQueryの違いについてもある程度知っているかもしれません。今回はPower BI CATチームのPhilさんに共有してもらったDirectQueryにまつわる謎(留意点)について紹介したいと思います。

PhilさんはDAXerpt(DAX Expert)として、同じCATチームのDarrenさん(DAX Studioの開発者)と同じく、非常に尊敬しています。

dax.tips

Darrenさんはつい最近、DAX Studio v3をデビューさせましたので、まだダウンロードしていない人はぜひこの機会に。DAX Studioについてはまた別途記事にできればと思います。

daxstudio.org

インポートモードとDirectQueryモード

Power BIのストレージモードには2つあり、インポートモードとDirectQuery(以降「DQ」)モードです。このうち、Power BIを使う人の殆どはインポートモード、すなわち、Power Query等で加工されたデータがVertipaq Storage Engineによってメモリに保存されるモードとなります。普段あまり意識しないものですが、インポートモードには以下の特徴があります。

  • RAM*1に保存されるため、データへの最速なアクセスが可能
  • クエリパフォーマンスがDQを大幅に上回る
  • 列データベースのため、ディスクからRAMにデータが読み込まれた場合、数倍~数十倍もの圧縮を実現

BIツールで最も重要な機能の1つがレポートの表示速度(パフォーマンス)であり、これはビジュアルをクリックし、クエリが発行されてその結果(クリックされたビジュアル及び連動するビジュアルを表示させること)が得られるまでに掛かる時間を意味します。

パフォーマンスが悪いレポート(特にデータ量がそれほど多くないのに遅いレポート)はPower BIでデータ分析を行う際のボトルネックとなりますので、場合によっては使い勝手が悪いとレッテルを貼られ、活用されなくなってしまうかもしれません。

とはいえ、インポートモードはDAX*2やデータモデルが最適でなくとも、メモリを活用しているというだけでレポートの反応速度は速いことが殆どだと思います。

Power BIでレポートを作成する場合、インポートモードが最初の選択肢であるべき

と認識しておくと良いでしょう。

一方、DQモードは同じ条件であれば、ほぼ全ての場合においてインポートモードよりもクエリパフォーマンスが劣ります。DQモードの特徴は以下の通り。

  • Power BIでクエリが発行されると、ソースシステムに問い合わせて結果を返す
  • インポートモードのように、データをキャッシュとして読み込まないため、ソース側でどんなに大きなデータサイズであっても、pbixのサイズは概ね数キロバイトから約2MBと小さい
  • インポートモードとは異なり、データは常に最新の状態で保たれる(リアルタイムな分析に重宝)
  • インポートモードでは取り扱えないようなデータサイズ(Power BIサービスでのモデルサイズの上限をヒット)であっても、DQは対応可能
  • 規制またはコンプライアンス要件により、データの保存と処理の場所を制限する法律や企業ポリシーにより、ソースデータを特定の地理的または政治的境界内に留めることが求められる場合がある。データ主権と呼ばれるものですが、DirectQueryであればその要件を満たすことができるものとなり得る
  • セントラルに機能するデータウェアハウスやデータマートに対して既に大きな投資を行っている場合、DirectQueryは一貫性のある最新のビューを提供することが可能になる

DQはメリットが多くあるように見えますが、

  • クエリパフォーマンスがインポートモードよりも著しく劣る
  • 使用可能なDAXが限定されてしまう

等のデメリットも多い。DQについては話すことが多いため、詳細は公式Docsより確認されたい。

ちなみに、Power BIのストレージモードは下図のようになります。

SEやFE等に関する簡単な解説は下記をご参照。

DirectQueryのパフォーマンス事例

まず、先にお断りをさせて頂きますが、これから話をする内容は高い確率でPower BIの製品開発チームによって修正される可能性があります(by Philさん)。あくまで、本日(2022/9/1)時点の状況であるとご認識ください。

今回の記事を書くことになった背景は以下の通り。

  1. Power BI CATのアジアチーム(私、Philさん、Darrenさん)でWeeklyキャッチアップ
  2. Philさんが最近見つけたDQに関する面白い事例を紹介してもらう
  3. ぬぬぬ?これは面白いので、ブログにしても?とPhilさんに聞く
  4. いいね!イケイケGoGo!

というテンションではなかったですが、承諾を得て今回の記事執筆に至りました。さて、ここからDQの不思議な動きについて紹介したいと思います。結論から言いますと、

DQでソースを指定する際、異なるテーブル(例:AとB)でサーバー名をPower Queryエディタで大文字と小文字別に変更すると、DQのクエリパフォーマンスが大幅に落ちてしまう

現象が起こります。

何を言っているかさっぱり!だと思いますので、具体的に事例を紹介していきたいと思います。

まず、この事例の再現環境ですが、DQSQLサーバーといったデータベースをソースとしますので、以下のようにSQLサーバーにつなげます。

  • ホーム > データの取得 > SQL Server
  • サーバー名(ここでは全て小文字)を入力し、DirectQueryを選択

  • データベースを選択し、クエリを2つ選択して「読み込み」

  • 「読み込み」成功を確認
    留意すべきポイントは、左側にあるビューがレポートビューとモデルビューの2つのアイコンだけが出現していることであり、実際にデータがメモリに読み込まれているわけではないため、データビューが消えていることになります。

  • モデルビューを確認
    リレーションシップ(私の環境では自動構築)が作られ、テーブルが2つあることを確認。また、ストレージモードがDirectQueryとなっていることも。

  • DAX Studioを外部ツールから立ち上げます

  • Server Timings > DAXスクリプトの記述 > Run > Server Timingの結果を見る

    上図の通り、各FiscalYearの売上を取得するシンプルなクエリですが、合計444ms掛かっています。Power BIのパフォーマンスを日頃見ている方であればお分かりだと思いますが、このシンプルなクエリをもしインポートモードで実施した場合、10~20msが結果になっているはずです。

    しかしながら、今回はこの結果をインポートモードで比較したいのではなく、DQモードで仮に接続文字列(サーバー名)を一部 or 全て大文字に変更した場合のパフォーマンスについて見ていきたいと思います。
  • Power BI Desktopに戻り、クエリエディタを立ち上げます

  • 表示 > 詳細エディタ > サーバー名を大文字に(もともと大文字の場合、小文字に)

  • 完了 > ホーム > 閉じて適用
    実はSQLサーバー等のデータベースでは大文字・小文字、またはそのミックスのどちらを使用してもエラーは発生せず、データベースにつなげることができるため、Power BIは接続文字列の変更を行う前と何ら変わりません

  • ここでDAX Studioに戻り、Runをそのままクリックし、結果を確認

    すると面白い結果となりました。まったく同じDAXクエリですが、先ほど444msだったのに今度は3,468msとなりました。速度的に約8倍も遅くなったのです。また、SE Querisも本来は1つであったのが、2つに増えているのが分かります。
  • ここでPower BI Desktop > Power Queryエディタ > FactSalesのデータベース名を全て大文字に変更(先ほどはDimDateを全て大文字)して、もう一度DAX Studioでテストを行うと、最初に測ったパフォーマンスに戻ります。

これらのことをまとめると、以下のことが言えると結論付けられます。

  • DQでは数値結果は変わらないのに、クエリのパフォーマンスが大きく変わる
  • (ここでは紹介していませんが)インポートモードで同じことをやっても、DQのようにパフォーマンス上の変化はなし
  • 2つ以上のテーブル(クエリ)で片方を大文字(一部もしくは全て)、片方を小文字(一部もしくは全て)にするとこの問題が起こる

どうやら、パフォーマンスが発生した要因は

インポートモードは大文字・小文字の区別はないが、DQモードは区別される

ようです。この問題は実際にDQを使う企業で知らずのうちに起こってしまう可能性があり、今回紹介した内容を知らないとその原因特定が困難となりそうです。

また、この問題ですが、実はリモートモデル(下記SQLBIの記事参照)のような動きになっています。リモートモデルでは、通常のリレーションシップで期待するDAXクエリ(あるいはパフォーマンス)が返ってこないことがあり、下図の通り、不要なDateKey全てに対してJoinが行われた結果、パフォーマンスが悪化しています。

www.sqlbi.com

リモートモデルを再現するため、試しにデータフローでDQ_DimDateというDimDateと全く同じテーブルを作り、以下のようにモデリングを行ってパフォーマンスを測定してみました。

結果は以下の通り。

DimDate[FiscalYear](DQ)をDQ_DimDate[FiscalYear](データフロー)に変更しただけですが、既に紹介した通り、2つのSEクエリが発行されています。ただし、DQDQのテーブルよりもパフォーマンスが良くなっているのが特徴であり、DQを使う場合、

Dimテーブルは可能な限りインポートモードで行う

ことがベストプラクティスとして考えることができます。

まとめ

  • 殆どの人が遭遇しないケースであるが、DQを使用する際に、接続文字列の大文字・小文字を変更する場合にパフォーマンスの低下に遭遇する可能性があることに留意されたい
  • 今回紹介した事例はバグである可能性が高く、Power BIの製品開発チームもこれを認識している(気づいたら解決済となっていたというパターン)
  • DQのご利用は計画的に

*1:Random Access Memoryの略。Power BIのデータがこの中に読み込まれ、データアクセスの最速化を実現するもの

*2:Data Analysis eXpressionの略で、Power BIの言語