Q&A - GIAC with Marco Russo

先週末、Guy in a CubeYouTubeチャネルにSQLBIMarcoさん(最強DAXコーチ)が登場し、ライブで1時間のQ&Aが行われました。Marcoさんはイタリア人ですが、私が2018年の冬にマイアミで開催されたDAXワークショップに参加したことを契機に、個人的にも親交があり、日本にも非常に興味をお持ちのようで、将来的に日本へ招待することも模索しています。

YouTubeは全て英語ベースでしたが、DAXの最適化に関する多くの質疑応答が行われましたので、重要なものに関しては本記事にて簡単に紹介したいと思います。英語が問題ない方はYouTubeを見て頂きたいのですが、内容が初心者向けではないため、少し咀嚼しながらまとめてみました。

DISTINCTCOUNTについて

Q: 商品レベルのSalesテーブル(メジャーテーブル)でDAX関数の1つであるDISTINCTCOUNTを使うと、レポートのパフォーマンスが非常に遅くなる問題

A: 遅くなるかどうかはケースバイケース(It depends)。
DISTINCTCOUNT ( テーブル[列名] )というメジャーは、下記2つのうちどちらで書き換えることが可能であり、改善される場合もあるのだが、理論上は遅いメジャーとなる。
SUMX ( VALUES ( テーブル[列] ), 1 )
SUMX ( DISTINCT ( テーブル[列] ), 1 )

SUMXを使用したメジャーでテーブル[列]のユニーク値が特に多い場合、この2つの書き方では改善しないだろう。DISTINCTCOUNTというDAX関数はビジュアルにある全てのデータポイントで重い処理を行うため、多対多(M2M)や双方向のリレーションシップ(Bidirectional Relationship)となっている場合、SE(Storage Engine)は1つのSEクエリで実行してくれないため、クエリパフォーマンスが悪くなる。また、タイム・インテリジェンス関数を使った場合でも同じことが言える。

www.sqlbi.com

重要なことは、例えばテーブルやマトリックスビジュアルで最後のセルまで見えなくとも(=見える範囲内だけであっても)、計算エンジンは本来計算が必要ない部分まで計算を行ってしまうため、レポートが遅くなってしまう。

DAX関数について

Q: 最もフル活用されていないと思われるDAX関数は?

A: CALCULATETABLEでしょう(by Marcoさん)。最も見られがちなのが、FILTER関数の代わりに使用すればパフォーマンスも上がるのに、使用しないケースが多い。CALCULATETABLEの使用場面は以下2つ。

  1. メジャー内でフィルターをする時
  2. もう一つは計算テーブルを作る時

2の計算テーブルはデータを更新する時の話であるため、殆ど気にかけなくて良いが、1のメジャーについてはビジュアル等をクエリする時のパフォーマンスに関わるため、考慮すべき重要なものとなる。この場合、例えば複数の列に対して複雑なフィルター条件があった場合、FILTERを使うよりもCALCULATETABLEを使って方が効率的であったり、メモリ使用量が減る可能性があるため、メジャーのパフォーマンスが高まる可能性がある。ただし、CALCULATETABLEを使うにしろ、FILTERを使うにしろ、2パターンのメジャーをそれぞれ作って試すのが良いだろう。

なお、他にもUSERELATIONSHIPTREATASISCROSSFILTERED等の候補があり、個人的にはKEEPFILTERSも数えられるのではないかと思いました。このうち、ISCROSSFILTEREDは高度な関数であり、ISFILTEREDを使ったほうが、どの列に対してフィルターが掛かっているかを直接判定するため、ユーザーとしてはDAXの動きをコントロールしやすいので使い勝手が良いという。目安として、10回のうち1回もしくはさらに低い頻度でISCROSSFILTEREDは使われるだろうとのこと(by Maroさん)。理由の1つとして、ISCROSSFILTEREDはデータモデルを考慮し、フィルターの伝播(Filter Propagation)によって複雑なメジャーになってしまう可能性があるため。

SQLのビデオコースについて

Q: Optimizing DAX Video Course ver.2はいつ頃になりそうでしょうか?

A: やるべきことが多く、新しい発見もどんどん出てきているので何とも言えないが、年末を目指したい。

DAXによる連続値

Q: DAXで連続した数値をどのように作れますか?

A: 1列だけを作る場合はGENERATESERIESという関数を使えばOK。

列を追加して、連続値を出したい場合はRANKXを使うこと。

しかしながら、列にPrimary Key(主キー = 全ての行で重複するものがないもの)がない場合、連続値を算出することができなくなってしまう。

上図①の場合、Value列に10が3つ重複しており、この場合の「連続値_正」は10が3つ重複し、直後に13という数字になってしまう。一方、②の「連続値_Dense」は、10の次が11、12と続くも、やはり10が3つ重複することになってしまう。

ベストプラクティス:Power Queryを使って連続値を出したほうが良い

Power Queryを使用すれば、Table.AddIndexColumnという関数を使用すれば一発でOK。

インポート vs DirectQuery(DQ

Q: インポートモードはDQよりも速い?

A: もちろん。素晴らしいスペックを持つマシーンを使って最適に作られたDQクエリであっても、ユーザー数が増えればやはり遅くなる。これに対する解決策の1つとして、アグリゲーション機能を使って、データの粒度を削ったものをインポートモードとして使用し、細かい粒度のデータをDQで接続するデュアルストレージモードが良い感じである。

docs.microsoft.com

Unified Star Schema

Q: Unified Star SchemaがPBIでも通用するか?

A: ノーとだけ言わせてほしい

参考:The New “Unified Star Schema” Paradigm in Analytics Data Modeling Review | by Andriy Zabavskyy | Towards Data Science

多対多のリレーションシップ

Q: データテーブルのようなファクト・テーブルとディメンションを持つデータモデルで、多対多のリレーションシップがある場合、ブリッジ・テーブルを作成すると、期待しているn:1 と 1:n の代わりに 1:n と n:1 になってしまうことに悩んでいる

A: この質問は2つの違うタイプのリレーションシップについて話をしている。1つはPower BIで使用できるn:1と1: nのようなカーディナリティによる多対多のリレーションシップである一方、実際のBIの世界では長年に渡って1:nとn:1のようなカーディナリティが多対多のリレーションシップであった。ディメンションテーブルが2つある場合、1:nとn:1というリレーションがあり、その中間にブリッジテーブルが存在する。

www.sqlbi.com

www.sqlbi.com

複合モデル(Composite Model)

Q: 複合モデルにおいて。dimとfactが異なるモデルにある場合、dim-to-dimまたはdim-to-factの接続について一般的な推奨事項があれば教えてください。FE/SEに違いはあるのでしょうか?

A: データモデリングの概念として、dimにつなげることをすること。例えば、今自分が作ったモデルにメジャーはあるが、後に誰かがモデルに対してテーブルやモデルと紐づけを行い、あなたが既に作ったメジャーを再利用するかもしれない、ということを想定に入れておくこと。これにより、ユーザーはfactテーブルを直接フィルターするのではなく、dimテーブルをベースにフィルターを行うことを想定した上で自分のメジャーを作ることができる。

パフォーマンスについて話をすると、ユニーク値の非常に多い列でリレーションシップを作るのは得策ではなく、これを行うことでフィルターの伝播コストが非常に高くなってしまう可能性がある。例えば数百万ものユニーク値がある商品マスタでは商品コードではなく、商品ブランドや商品分類と紐づけを行ったほうが、ユニーク値が少ないため、リレーションシップによるパフォーマンスが良くなる。

Power BIの新機能

Q: Marcoさん、Power BIに期待する新機能はなんですか?

A: NDAにより話せるものかどうか分からないが、こう言い換えましょうか。DAXやデータモデルについての最新機能が今後数ヵ月、1年単位で出てくるだろうけど、
The Definitive Guide to DAX 3rdシリーズをまだ執筆していない理由は、それらの新しい機能が登場するのを待っているから。今後、CALCULATION GROUPS(計算グループ)をTabular Editor上ではなく、Power BI Desktopで構築できることは喜ばしいことであるが、個人的には計算グループの使いやすさや体験をもっと改善していくことにフォーカスしていくべきであると思う。単純にエディタ機能を提供しただけでは、消費体験に際してまだ2~3個の重要な機能が欠けているため、まだまだ不十分。

例えば、下記ブログにある通り、任意のメジャーを計算グループに残したい場合、今のPower BIではまだそれを実現できないが、ExcelではOLAP PivotTableでName Setを構築して対応できる。

www.sqlbi.com

もう一つは「計算グループのデフォルト選択」である。

www.sqlbi.com

Webブラウザでのモデリング機能

Q: Webブラウザモデリングを行うことができることに興奮している。今までMacユーザーだった人もPower BIを試すことができるようになるし、何よりPower BI DesktopをインストールしていないPCでPower BI Reportを作ることができるようになる

A: 可能性を増やすという意味では素晴らしい試みであると言える。しかし、正しい期待値を持つことを意識すべきである。なぜならば、Webは常に遅延(Latency)とトレードオフの関係にあるから。Power BI Desktopを使ってモデリングする時よりも遅くなるのを覚悟しておくべきでしょう。

NAMEOF関数

Q: フィールドパラメータで使用されるNAMEOF関数はなぜインポートモードだけにしか使えないのか?一般的になぜライブ接続になると多くの制限を受けてしまうのか?

A: NAMEOFAzure Analysis Service 2022でも使えるし、ライブ接続の制限についてはバックエンドサーバー次第ということになる。例えば、オンプレミスのAnalysis Service 2019サーバーの場合、使用可能な機能は3年前のものとなる。

DirectQuery(DQ

Q: DQユースケースについて教えてほしい

A: DQが向いているユースケースはいくつかあるが、例えば以下のようなものがある。

  • データをインポートすることが非現実的で、データ量が大きい場合
  • データの遅延を最小化させる必要がある場合

Power BI Desktopは素晴らしいBIツールであるが、インターラクティビティ(双方向性)を重視したクライアントツールであるため、本当にサイズが膨大でDQを使用するくらいなら、他のツールを活用したほうが良いかもしれない(あるかどうかは不明であるが)。

顧客と話をしていると、リアルタイムという言葉が常に飛び交うが、この

リアルタイム

という言葉の定義があやふやのケースが多い。本当のリアルタイムというのは、IoTデバイスによって生み出されるテレメトリー*1データを指すことが多いが、データの遅延として許容できる時間(秒、分、時間、日)を知ることで対応できる。通常、

インポートモードは最もコストが低く、リアルタイムになるほど、コスト(コンサル費用等も含む)が高くなっていく

これを認識した上で話をすると次のステップへ進みやすくなるだろう。

なお、DQの問題は、ビジュアルが複数あると仮定して、そのうちの1つが更新されたとしても、他のビジュアルが更新されなかった場合、データがシンクロされた状態にならないリスクがある。インポートモードとは違い、これをどのように管理していくのか。また、DQは1つ or 複数のクエリをデータソースに対して投げることになるため、レポート内のビジュアルが多いと、ビジュアルの動きが非常に雑になってくる。DQを使う場合、アーキテクチャについてしっかり把握しておくことが重要である。

もう一つ、DQについての制限の話をすると、以前ではDQに対してタイム・インテリジェンス(TI)関数を使うことができなかったが、複合モデル(Coomposite Model)が登場してからこれらの制限が解除されることとなった(by Adamさん)。今ではDQでもTI関数を使用することができるようになったが、留意してほしいのは条件によっては非常にクエリパフォーマンスが遅くなってしまう。

最後に、DQについて1つ述べるとしたら、Power BIは列データベースによってデザインかつ最適化されたモデルを用いてレポートを作ることになるが、これはリレーションシップデータベース(RDB)の概念を踏襲したものである。RDBでは1つのクエリを発行した全てのことを実現できるものではないため、作り手のニーズにマッチしたSQLクエリを発行しようとすることがそれほど簡単なことではないことに気づくべきである。もちろん簡単なケースもあるだろうが、データモデルに必要とされる柔軟性及びそれに伴うSQLクエリの複雑性を考慮すると、DQを簡単に考えることが如何に危険であるかが分かるでしょう。

最後に

Power BIを活用して不自由なくビジネス上の要求を満たすためのBIレポートを作ることは簡単ではありません。そのうち、必ずぶち当たる壁がDAX / Data Model、これらに対する最適化を行うことになります。コンテンツは初心者向きではないですが、Marcoさんが出演しているオンラインイベントは必ず押さえておくのが良きです。

 

*1:製品のパフォーマンスデータを収集し、監視と分析のためにそれを遠隔地に伝達するプロセス