テクテク日記

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

Power BIによるSaaS分析10 -モデリングの重要性

前回はコホート分析の1つである生存分析について見ました。今回は少しテクニカルな話をしたいと思います。テーマは、

データモデルとDAX

になります。

テーブル vs マトリックス

Power BIのレポートを作る場合、必ず必要となるのがビジュアル(チャートやテーブル)とメジャーとなります。生存分析で使用したビジュアルはマトリックスビジュアル(以降「マトリックス」)であり、Power BIアイコンは以下赤枠内のものとなります。

f:id:marshal115:20220326195119p:plain

マトリックスとよく間違われるのが、テーブルビジュアル(以降「テーブル」)ですが、マトリックスが行と列にそれぞれ属性を配置できるのに対して、Power BIにおけるテーブルは全ての属性(行・列・値(メジャー))を「値」の配置として扱います(下図)。

f:id:marshal115:20220326201122p:plain

上図の通りですが、左側のテーブルは全て縦長にデータを表示してくれるのに対して、右側のマトリックスはピボットテーブルのように、行×列×値の3つで集計してくれます。これにより、前回解説した顧客生存率の分析ができるようになっています(下図)。

f:id:marshal115:20220320182002p:plain

マトリックスは複数の列を行や列に配置を行うことで階層にすることができ、下図のように、USでは21年5月と22年1月、22年3月にそれぞれ有料サインアップした顧客が存在していることが分かります。

f:id:marshal115:20220326202031p:plain

テーブルよりも階層別に情報を格納することができるため、マトリックスは使い勝手が非常によく、詳細な数字を見ながら分析をしたい場合の必須ビジュアルの1つです。

データモデルの解説

マトリックスを使った生存分析ビジュアルは上記の通りですが、これからする話に慣れて頂くため、今まで分析をしてきたデータモデルをもう一度おさらいしておきます。

データモデルBefore

f:id:marshal115:20220326202942p:plain

このうち、実は上図左下のdSignupとSalesが、生存分析(Survival Probability by Months Elapsed)というマトリックスビジュアルを作っています(下図)。

データモデルAfter

f:id:marshal115:20220326202726p:plain

言い換えれば、このマトリックス完全あるスタースキーマであるリレーションシップで構成され、それに従って生存確率(Survival Probability)というメジャーが計算されたものとなっています。

一方、上図のB(MonthsPassedという列)がSalesテーブルにあるのですが、このBを追加せず、Disconnectedテーブル(リレーションシップがない、独立したテーブル)でマトリックスを作り、生存確率を計算することも可能です(下図)。

f:id:marshal115:20220326203315p:plain

上図で留意すべき点は以下2つ。

  1. マトリックスの縦軸(A)はdSignupテーブルではなく、dCustomerのStartDateを使用
  2. dCustomerとSalesでは、dCustomer[CustomerID] - Sales[CustomerID]でリレーションシップが構築されている
  3. B(MonthsPassed列)はdCohortMonthという完全に独立したDimensionテーブルに存在し、Salesと何らリレーションシップが張られていない

その結果、新たなデータモデルは以下のようになります。

f:id:marshal115:20220326205058p:plain

単純にdCohortMonthというテーブルが追加されただけですが、こちらをDAXで計算テーブルとして作っておきます。

f:id:marshal115:20220326205703p:plain

このテーブルが出来上がった後、以下のDAX式を記述します。

  • Survival Probability 2(生存確率)
Survival Probability 2 = 
var _first_signup = SELECTEDVALUE( dCustomer[StartDate] ) //縦軸のStartDate
var _month_after = SELECTEDVALUE( dCohortMonth[MonthsPassed] )  //横軸の経過月数
var _customer_start_fixed = [# Customers_Signup]    //有料サインアップした顧客(※留意必要)
var _cohort_filter = 
FILTER(
    dCalendar,
    dCalendar[Date] = EDATE( _first_signup, _month_after )
)   //マトリックスにおける各セルをフィルターするテーブル(1行のみ)
var _customer_by_signup_date = 
CALCULATE(
    [# Customers_Signup],
    _cohort_filter
)   //各月に有料サインアップした顧客(上記変数○○_fixedと異なる)
var _result = DIVIDE( _customer_by_signup_date, _customer_start_fixed )
return
  _result

前回の記事にある同じメジャーで使用されたDAX式と比べて欲しいのですが、かなり複雑なものになってしまったのが分かるかと思います。念の為、それぞれの変数の意味について解説をしておきます。

  • _first_signup
    dCustomerにあるStartDate(各顧客が初めて有料サインアップした年月)を格納

  • _month_after
    dCohortMonthにあるMonthsPassed(初めて有料サインアップした年月からの経過月数として)を格納

  • _customer_start_fixed
    [# Customers_Signup]は有料サインアップ顧客というメジャーを格納。上記解説した「データモデルBefore」と「データモデルAfter」の場合、マトリックスに当該メジャーを入れると、それぞれ以下のようになります。

    f:id:marshal115:20220326212127p:plain

    「データモデルBefore」の場合、縦軸も横軸も全てリレーションシップが効いているため、_customer_start_fixedはそれぞれの開始月(YM)と経過月数でフィルターされ、開始月 = 0ヵ月からどんどん減っていきます。一方で、「データモデルAfter」の場合、横軸のMonthsPassed列がDisconnectedテーブルになっているため、リレーションシップが働かず、_customer_start_fixedは0ヵ月の数字のままになっています(こちらはStartDateのフィルターが加味されているため、縦軸は全て数字がバラバラ)。
    これにより、「データモデルBefore」ではこれらの数字が最後の除算における”分子”であるのに対して、「データモデルAfter」では"分母”扱いとなっていることになります。

  • _cohort_filter
    この変数が実は最も重要ですが、これを理解するためには以下のように分解してみます。まず、_first_signupを「データモデルAfter」のほうに入れると、以下のようになります。

    f:id:marshal115:20220326213448p:plain
    _customer_start_fixedで解説しました通り、横軸はリレーションシップが効いていないため、結果は全てStartDateの年月日と同じになります。求めたいのは、例えば、2021年4月1日の経過月=0の場合は2021/04/01、1は2021/05/01、2は2021/06/01といったものであり、FILTER関数内にあるEDATE( _first_signup, _month_after )がまさにこの役割を果たしています。この_cohort_filterを可視化させるためには、

    FILTER(
        VALUES ( dCalendar[Date] ),
        dCalendar[Date] = EDATE( _first_signup, _month_after )
    )
    に変更させる必要がありますが、この結果が下図になります。上図と比較してみると、セルごとに異なる年月が_cohort_filterとして算出されいるのが分かります。

    f:id:marshal115:20220326214225p:plain

    この算出結果はテーブルであることから、下記_customer_by_signup_dateにおけるCALCULATEのテーブルフィルター(フィルター条件)として使用されます。

  • _customer_by_signup_date
    DAXで最も使用されるCALCULATEが使用され、それぞれのセルにおいてまずは_cohort_filterがテーブルフィルターとして適用され、それを元に[# Customers_Signup]を評価していきます。結果的に、この式で計算された数字は以下のようになります。

    f:id:marshal115:20220326215007p:plain

    実はこれ、リレーションシップが効いている場合の「データモデルBefore」の結果と同じになっていますので、この変数は最後の計算を行うため、「データモデルAfter」では"分子”扱いになります。

  • _result
    省略

これでお分かりになったかと思いますが、Disconnectedテーブル(「データモデルAfter」)を使った場合、実に「データモデルBefore」と逆のことをやっているのです。すなわち、縦軸・横軸のフィルターを効かせるため、dCalendarテーブルからセルそれぞれに対して、テーブルフィルター用の年月日を抽出しているのです。なんとも面倒なことをやっているのですが、最後に可視化すると、めでたく下図のように結果が返ってきました。

f:id:marshal115:20220326215823p:plain

Disconnectedテーブルの弊害

皆さんはこのDAXの記述方法についてどう思いますか?生存分析について知らない場合はともかく、知っていると仮定した場合でもかなりハードルが高いのではないかと思います。実際、このやり方には大きく3つの弊害があります。

  1. DAXが複雑になる
  2. データがない状態のマトリックスは壊れる
  3. 階層が崩壊

1ですが、すでにお分かりの通り、DAXが異様に難しくなってしまいます。何より、通常のフィルターコンテキストに慣れている人であれば、例えば有料サインアップ顧客がマトリックスに配置された瞬間、縦軸と横軸の両方からフィルターされた状態を期待するはずが、横軸の経過月数において、全てが0ヵ月と同じ結果になることに対して、DAXを長年書いてきた人でもなかなかイメージしにくいのではないでしょうか。

リレーションシップがないことで、

常にディメンションに気を取られてしまう

ことになりかねません。あれこれ試して”迷路に迷い込んでしまう”可能性が高くなると思います。

2ですが、「データモデルBefore」の場合、縦軸と横軸をマトリックスに配置すると以下のようになりますが、

f:id:marshal115:20220326222343p:plain

「データモデルAfter」の場合、これを行おうとすると、以下のようにマトリックスは完全に壊れてしまうのです(テーブルも同じく壊れます)。

f:id:marshal115:20220326222456p:plain

理由は画像にある通りですが、メジャーを配置した瞬間、何事もなかったかのように、結果が返ってくるという仕様になっています。エラーが出たマトリックスにメジャーを配置しようと思う人は殆どいないのではないでしょうか。

f:id:marshal115:20220326223051p:plain

最後に3ですが、こちらが最も深刻です。まず、「データモデルBefore」の場合、下図(右上)のように階層を作ります。ここから、”階層内の次のレベル"へ移動をしたい場合は↓↓をクリックすれば、簡単に移動することができます。

f:id:marshal115:20220326224530p:plain

f:id:marshal115:20220326224548p:plain

f:id:marshal115:20220326224639p:plain

ご覧の通り、YMからCountry、OSへと簡単にディメンション単位で移動できます。これは、リレーションシップが効いているためであり、普段の使い方からすると、何ら不思議ではありません。

ところが、これを「データモデルAfter」で同じように実現しようとすると、驚くべき結果が返ってきます。

f:id:marshal115:20220326225310p:plain

そう、なぜか空白のテーブルが返ってくるのです。しかしながら、やり方を変えて、1ヵ月だけドリルダウンしてみると、以下のようにきちんと結果は返ってきます。

f:id:marshal115:20220326225652p:plain

同じように、スライサーで絞っても成功します。

f:id:marshal115:20220326225811p:plain

+ボタンをクリックして、中身を表示させることも可能です。

f:id:marshal115:20220326225910p:plain

不思議なことに、唯一”階層内の次のレベル"への移動だけが、空白として返ってくるのです。非常にショッキングな結果ですが、フィルターコンテキストが分かっていると理由は非常にシンプルです。

メジャー[Survival Probability 2]は、各セルで必ず1つの日付でフィルターされている

そして、

SELECTEDVALUEは、複数のStartDateがある場合、空白を返す

という特性を理解していれば、空白の理由もわかるようになります。

例えば、以下の変数

_first_signup = SELECTEDVALUE( dCustomer[StartDate] ) 

を以下のように書き換えると、

_first_signup = SELECTEDVALUE( dCustomer[StartDate], DATE(2021,4,1) ) 

次のレベルへ移動すると空白のテーブルは返らず、マトリックスは以下のようになります。Countryには複数のStartDateが入っているため、その場合にはSELECTEDVALUEは2021年4月を選択するよう、_first_signupに値を代入している式となります。

f:id:marshal115:20220326231440p:plain

もう少し詳しく見ていくと、各Countryにおいて、横軸の0の各セルには2021/04/01、1のセルには2021/05/01が日付フィルターとして入っていくのですが、国別に全ての顧客が有料サインアップした月が4月スタートであるはずもないので、お気付きの通り、間違った結果が表示されてしまいました(言い換えれば、この方法ではワークしないということになります)。

なお、上記のようにドリルダウンしたり、+ボタンを広げたり、スライサーで選択したりした際に、きちんと値が計算されていますが、これらはStartDateが1つの年月でフィルターされた状態になっていたためです。

最後に、データモデルが変わっていますので、「データモデルBefore」と同じように、dSignupテーブルのYM列でフィルターしようとすると、スタート月を全て0で揃えられなくなってしまいますので、以下のようなマトリックスが出来上がってしまいます。

f:id:marshal115:20220326232440p:plain

こちら、計算結果は間違ってはいないですが、表現方法が間違っているので、下図のように、スタート時期を全て0に揃えてあげる必要があります。

f:id:marshal115:20220326232951p:plain

このように、Disconnectedテーブルを使った生存分析の可視化はディテールにまで考える必要があります。ディメンション別に分析をしたい場合は間違いなく、このやり方ではなく、前回紹介したやり方をお勧めします。

おまけ

ここまで話をしておいてなんですが、実は条件さえ合えば、DisconnectedテーブルはSalesとフィジカルなリレーションシップがなくても使えます、ということを最後にお話しをしたいと思います。ここまでの話は言い換えれば、dCohortMonthとSalesテーブルにリレーションシップがないため、難しいDAX式を記述する必要がある、といったものでした。

であれば、SalesテーブルとdCohortMonthの間にリレーションシップを作ってあげれば、前回の記事で完結するような話になりますが、今回はあえてバーチャルリレーションシップを作って、前回の記事と同様のことを実現してみたいと思います。

ステップは2つ

  1. Salesテーブルに経過月の列を作る(前回の記事で紹介済)
  2. バーチャルリレーションシップが有効なDAX式を記述する

1については前回の記事を参考頂きたいので割愛します。2について、DAX式は以下のようになります。なお、データモデルはdCohortMonthが依然としてDisconnectedテーブルとなっています。

f:id:marshal115:20220326235514p:plain

  • Survival Probability 3(生存確率)
Survival Probability 3  =
VAR _lineage =
    TREATAS ( VALUES ( dCohortMonth[MonthsPassed] ), Sales[MonthsPassed] )
VAR _customer =
    CALCULATE ( [# Customers_Signup], _lineage )
VAR _customer_by_month = [# Customers_Signup]
VAR _result =
    DIVIDE ( _customer, _customer_by_month )
RETURN
    _result

f:id:marshal115:20220326235816p:plain

注目すべき点は、YMがdSignupテーブルを使用し、MonthsPassedはdCohortMonth(本来はリレーションシップがないテーブル)を使用しているところです。TREATAS関数があたかも通常のリレーションシップのように機能してくれたおかげで、難しいDAXを記述する必要もなく、下図のように、”階層内の次のレベル"への移動も問題なくできるようになりました。

f:id:marshal115:20220327000111p:plain

まとめ

今回も長文で恐縮ですが、テイクアウェイは1つだけです。

データモデリングのほうが重要

ということです。このブログを書き始めた頃(2020年4月)、以下のようにコメントしています。2年越しにようやくその意味を伝えることができたのではないかと思います。

f:id:marshal115:20220327000607p:plain

とはいえ、TREATASのようなDAX関数を使用することで、スタースキーマと同じようなことを実現できることも知って頂く良い機会になったのではないでしょうか。