LOD計算を視覚的に理解する

LOD計算を体系的に理解するための記事を昨年書きましたが、今回は少し踏み込んで「LOD計算の処理を視覚的に理解する」ことを目的に記事を書きます。

(正確にはLOD表現 / LOD Expressionsなのですが、コミュニティ的にはどちらも使われており、また計算機能なので以下LOD計算と表記します)


TableauのPerformance Recordを使用すると、ビューを作成するために必要なクエリを一部確認することができます。

それを利用してLOD計算のクエリを読み解き、Tableau Prepを使用して視覚的に処理フローを理解することを目指します。

(ちなみにビュー / Viewという言葉が以降出てきますが、これはTableauのシート:可視化を作成する場所として読んでください)


今回の記事に特別な前提知識は必要ありませんが、以下の記事を流し読みしておくといいかもしれません。

LOD計算を本気で理解する


使用したWorkbookとTableau Prep Flowは以下にあります。

Google Drive - 20210515_LOD_with_Query


今回の記事はSQLが参考として出てきますが、本質は処理の可視化にありますので、SQLの知識を問いません。

Tableauレベルに関わらず、多くの方のTableau理解に役立てばと思います。


おことわり(SQL分かる方向け)

本記事ではPerformance Recordから抜き出したVizQL/SQLが出てきますが、本質的に同じ処理なのですが、実はTableau必ずしも本記事に記載したクエリで動いていません。

例えばサブクエリ+JOINを使う代わりにWINDOW関数で処理している…など。


本記事はあくまで概念的、視覚的に処理を理解するための記事ですので、上記ご了承ください。

LOD計算の基本概念

まず初めに、LOD計算の基本的なところをおさらいします。

LOD計算とは端的に言えば「任意の粒度での集計を可能とするTableauの計算機能」です。


Tableauでの、厳密に言えばシートやビューでの集計粒度は、基本的には「ビューに使用されているディメンション」により決まります。


例えば以下の図では、RegionとSub-Categoryがディメンションとして使われています。

その2つのディメンションの組み合わせにより各データ点が作られ、それぞれの点でProfitとSalesのSUM()が集計されます。

またディメンションの組み合わせによるビューの集計粒度をViz-LODと呼びます。

(Visualization's Level of Detailの省略形でしょうか)


一方で、例えば「Regionを無視したSub-CategoryだけのSUM(Sales)をサイズに使いたい」場合に、少し方法を考えなければいけません。

このような集計粒度をViz-LODから変えた集計を行う際に、LOD計算は力を発揮します。


ところで、LOD計算には3種類あります:FIXED、INCLUDE、EXCLUDEです。

LOD計算の基本構文と合わせて見ていきましょう。

上述の通りLOD計算は「任意の粒度での集計を可能とするTableauの計算機能」です。

したがって集計に使用するデータ粒度(ディメンション)と集計関数を指定しなければいけません。


FIXED、INCLUDE、EXCLUDEの違いは以下です。

  • FIXED:任意のディメンションで集計

  • INCLUDE:ビューのディメンション+指定されたディメンションで集計

  • EXLUDE:ビューのディメンション-指定されたディメンションで集計


それぞれを後ほど見ていきますが、まずは上記の基本概念と基本構造を理解してください。

FIXEDを理解する

まずはFIXEDを扱います。INCLUDEとEXCLUDEはFIXEDが理解できれば分かります。


FIXEDをディメンションとして使用する場合

FIXEDは任意のディメンションで集計できるLOD計算でした。

以下の例を見てみましょう。

上図ではSegmentごと、顧客の初回注文四半期ごとの顧客数を表示しています。

こちらを作成するには「顧客ごとの初回注文四半期」を求める必要があり、それは以下の計算フィールドで作成されます。


(FIXED) Customer's First Order Quarter

{ FIXED [Customer ID]: MIN(DATETRUNC('quarter',[Order Date]))}


このLOD計算は「FIXEDで」「Customer IDごとに」「Order Date四半期の最小値」を求めています。

それぞれの「」がLOD計算の基本構文の要素に対応していることに注意してください。


このLOD計算をビューではディメンションとして使用していますが、Tableauはこれを以下のクエリで求めています。

(参考として載せている程度ですので、飛ばして頂いて大丈夫です)

SELECT 
  CAST(TABLEAU.NORMALIZE_DATETIME("t0"."__measure__0") AS DATE OR NULL) AS "Calculation_430093807112658944",
  "Orders"."Segment" AS "Segment",
  COUNT(DISTINCT "Orders"."Customer ID") AS "ctd:Customer ID:ok"
  
FROM "TableauTemp"."Orders$" "Orders"

INNER JOIN (
  SELECT 
    "Orders"."Customer ID" AS "Customer ID",
    MIN(TABLEAU.TO_DATETIME(DATE_TRUNC('QUARTER', TABLEAU.NORMALIZE_DATETIME("Orders"."Order Date")), "Orders"."Order Date")) AS "__measure__0"
  FROM "TableauTemp"."Orders$" "Orders"
  GROUP BY 1
) "t0" 
ON ("Orders"."Customer ID" IS NOT DISTINCT FROM "t0"."Customer ID")
GROUP BY 1,2

このLOD計算を使用したビューを表示するまでの処理のフローの模式図は以下のようになります。

具体的にはGoogle Driveに置いてあるPrep Flowファイルを見て頂くとして、大事な点は以下のステップで処理されることです。

  1. FIXEDのLOD計算を求めるため、Customer IDでOrder Date四半期の最小値を集計するステップ行い

  2. FIXEDに使用したディメンションであるCustomer IDをキーにして、元データと結合する

  3. ビューに使用されているディメンション(Segmentと顧客ごとの初回注文四半期)を用いて、COUNTD(Customer ID)を集計する


FIXEDをメジャーとして使用する場合

さて、上記はFIXEDをディメンションとして使用した場合の処理フローでした。

次はFIXEDを計算した上で、ビューで集計値(メジャー)として使用する場合を考えます。

下記はCategory, SegmentごとにSUM(Sales)とオーダー毎売り上げの平均値を表示しています。


ここでは以下のFIXEDを使用したメジャーを使用しています。


(FIXED) AVG Order Sales

AVG({ FIXED [Order ID]: SUM([Sales])})


メジャーはViz-LODに対しての集計値、つまり今回ではCategoryとSegmentの各組合せに対して集計されますが、LOD計算を使用したメジャーがどのように処理されるかを見ていきましょう。

SELECT 
  "t0"."Category" AS "Category",
  "t0"."Segment" AS "Segment",
  "t0"."sum:Sales:ok" AS "sum:Sales:ok",
  "t3"."__measure__0" AS "usr:Calculation_765330504378691584:ok"
  
FROM (
  SELECT 
    "Orders"."Category" AS "Category",
    "Orders"."Segment" AS "Segment",
    SUM("Orders"."Sales") AS "sum:Sales:ok"
  FROM "TableauTemp"."Orders$" "Orders"
  GROUP BY 1,
    2
) "t0"

INNER JOIN (
  SELECT 
    MIN("t1"."Category") AS "Category",
    "t1"."Segment" AS "Segment",
    AVG(CAST("t2"."__measure__1" AS DOUBLE PRECISION OR NULL)) AS "__measure__0"
	
  FROM (
    SELECT 
	  "Orders"."Category" AS "Category",
      "Orders"."Order ID" AS "Order ID",
      "Orders"."Segment" AS "Segment"
    FROM "TableauTemp"."Orders$" "Orders"
    GROUP BY 1,2,3
  ) "t1"
  
  INNER JOIN (
    SELECT 
	  "Orders"."Order ID" AS "Order ID",
      SUM("Orders"."Sales") AS "__measure__1"
    FROM "TableauTemp"."Orders$" "Orders"
    GROUP BY 1
  ) "t2" 
  ON ("t1"."Order ID" IS NOT DISTINCT FROM "t2"."Order ID")
  GROUP BY 2
) "t3" 
ON (("t0"."Category" = "t3"."Category") AND ("t0"."Segment" IS NOT DISTINCT FROM "t3"."Segment"))

処理が少し複雑になりましたね。順に見ていきましょう。

  1. ビューのディメンション(CategoryとSegment)+FIXEDで指定されたディメンション(Order ID)でデータを集計する

  2. { FIXED [Order ID]: SUM([Sales])}を計算し、Order IDごとのSUM(Sales)を求める

  3. 1と2をFIXEDで指定されたディメンション(Order ID)をキーにして結合する

  4. ビューのディメンション(CategoryとSegment)で集計し、 AVG({ FIXED [Order ID]: SUM([Sales])})を計算する

  5. 元データをビューのディメンション(CategoryとSegment)で集計する

  6. ビューのディメンション(CategoryとSegment)をキーにして、4と5を結合する

要は「FIXEDの計算を実施した後に、ビューのディメンションで集計された集計データにFIXEDの計算結果を結合」しています。

ここでFIXEDがディメンションとして使用された場合と異なり、結合は元データの集計後に行われていることに注意してください。

(2021/5/22 追記)

ところで、なぜFIXEDをメジャーとして使用した場合はビューのディメンションで集計された集計データにFIXEDの計算結果を結合する必要があるのでしょうか。


これを理解するためにはTableauのブレンド機能、リレーション機能との類推が役立ちます。

データのブレンド

関係(リレーション)を気軽に使ってみる


(以下ブレンドでお話しますが)ブレンドとは基本的に2つ以上のデータソースの結合であり、基本的に粒度の異なる数値データを組み合わせるときに使用します。


例えば元々のSuperStoreデータに、四半期売上目標データを追加する場合を想定してください。

Superstoreは注文商品オーダーごとにレコードが作成されていますが、目標データは四半期ごとにレコードが出来ているとします。

これを元データに四半期をキーにしてJOIN、つまり目標データをSuperStoreの1行1行に追加すると…売上目標の数値がとんでもなく重複しますね。


このような数値の不適切な重複を避けるために、ブレンドまたはリレーションを使用し、集計結果を結合するというアプローチを使用します。

FIXEDの集計結果をメジャーとして使用した場合も同様に、数値の不適切な重複を避けるため「FIXEDの計算を実施した後に、ビューのディメンションで集計された集計データにFIXEDの計算結果を結合」するアプローチをとっています。

最後に比較のため、FIXEDがディメンションとして使用される場合とメジャーとして使用される場合のフロー図を再掲します。

 

FIXEDがディメンションとして使用された場合


FIXEDがメジャーとして使用された場合

FIXEDがディメンションとして使用された場合は

  1. FIXED計算を実施

  2. FIXED計算結果を元データへ、FIXEDで使用したディメンションをキーにして結合

  3. ビューの集計粒度で集計

の順で実施されている一方で、FIXEDがメジャーとして使用された場合は

  1. FIXED計算を実施

  2. FIXED計算結果をビューの集計粒度で集計

  3. 元データをビューの集計粒度で集計

  4. 2と3をビューのディメンションをキーにして結合

という順番で実施されています。結合のタイミングが特に異なりますね。


LOD計算を使用する上で、まずは上記のFIXEDをディメンション/メジャーとして使用した場合の2通りの処理イメージを理解いただければと思います。


細かいことは実際にLOD計算を使いながら覚えていけば良いですが、まずは概念的に上記のフロー図を理解しておくと、LOD計算がどのように働くのかのイメージが身につくと思います。


以下INCLUDEとEXCLUDEを扱いますが、本質的にはFIXEDをメジャーとして使用する場合と同じです。

まずはFIXEDを理解することをおすすめします。

INCLUDEを理解する

INCLUDEとは「ビューのディメンション+指定されたディメンションで集計」するLOD計算でした。

例えば以下はCategoryとSegmentで集計されたビューですが、そこに「Category, Segmentごとの、オーダー毎売り上げの平均値」を求めるLOD計算を追加しています。


(INCLUDE) AVG Order Sales in Category and Segment

AVG({ INCLUDE [Order ID]: SUM([Sales])})


このビューがどのように処理されているかを見てみましょう。

SELECT 
  "t0"."Category" AS "Category",
  "t0"."Segment" AS "Segment",
  "t0"."sum:Sales:ok" AS "sum:Sales:ok",
  "t2"."__measure__0" AS "usr:Calculation_765330504378691584:ok"
  
FROM (
  SELECT 
    "Orders"."Category" AS "Category",
    "Orders"."Segment" AS "Segment",
    SUM("Orders"."Sales") AS "sum:Sales:ok"
  FROM "TableauTemp"."Orders$" "Orders"
  GROUP BY 1,2
) "t0"

INNER JOIN (
  SELECT 
    "t1"."Category" AS "Category",
    "t1"."Segment" AS "Segment",
    AVG(CAST("t1"."$temp0_output" AS DOUBLE PRECISION OR NULL)) AS "__measure__0"
	
  FROM (
    SELECT "Orders"."Category" AS "Category",
      "Orders"."Segment" AS "Segment",
      SUM(SUM("Orders"."Sales")) OVER (PARTITION BY "Orders"."Order ID" ) AS "$temp0_output"
    FROM "TableauTemp"."Orders$" "Orders"
    GROUP BY 1,"Orders"."Order ID",2
  ) "t1"
  GROUP BY 1,2
) "t2" 
ON (("t0"."Category" IS NOT DISTINCT FROM "t2"."Category") AND ("t0"."Segment" IS NOT DISTINCT FROM "t2"."Segment"))

INCLUDEはFIXEDに比べて処理は簡単です。

  1. ビューのディメンション(CategoryとSegment)+INCLUDEで指定されたディメンション(Order ID)で{INCLUDE [Order ID]: SUM([Sales])}を計算し、Category, Segment, Order IDごとのSUM([Sales])を計算する

  2. ビューのディメンション(CategoryとSegment)で集計し、 AVG({INCLUDE [Order ID]: SUM([Sales])})を計算する

  3. 元データをビューのディメンション(CategoryとSegment)で集計する

  4. ビューのディメンション(CategoryとSegment)をキーにして、3と4を結合する

ちなみにFIXEDをメジャーとして使用した場合とINCLUDEの処理を比較すると、INCLUDEの方が処理が少ない分、基本的にはパフォーマンスが良くなります。


ただしビューのディメンションに依存するLOD計算のため、FIXEDと異なりINCLUDEとEXCLUDEはディメンションを作成する計算には使えません


またINCLUDE/EXCLUDEとFIXEDでは処理タイミングが違ってきます。詳細はこちらの記事の「Tableau操作の順序でみるLOD計算の違い」を参照ください。

EXCLUDEを理解する

最後にEXCLUDEを見ていきます。

EXCLUDEをおさらいすると「ビューのディメンション+指定されたディメンションで集計」するLOD計算でした。

以下の例ではCategoryとSegmentで集計しつつ、Segmentを集計粒度から除外し、CategoryごとのSUM(Sales)を表示させています。


(EXCLUDE) Sales by Category

{ EXCLUDE [Segment]: SUM([Sales])}


このビューの処理は以下です。

SELECT 
  "t2"."Category" AS "Category",
  "t2"."Segment" AS "Segment",
  "t2"."$temp0_cse" AS "TEMP(attr:Calculation_1378383003694723072:qk)(2163768221)(0)",
  "t2"."$temp0_cse" AS "TEMP(attr:Calculation_1378383003694723072:qk)(3556503941)(0)",
  "t2"."sum:Sales:ok" AS "sum:Sales:ok"
  
FROM (
  SELECT 
    "t0"."Category" AS "Category",
    "t0"."Segment" AS "Segment",
    "t0"."sum:Sales:ok" AS "sum:Sales:ok",
    "t1"."__measure__0" AS "$temp0_cse"
	
  FROM (
    SELECT 
	  "Orders"."Category" AS "Category",
      "Orders"."Segment" AS "Segment",
      SUM("Orders"."Sales") AS "sum:Sales:ok"
    FROM "TableauTemp"."Orders$" "Orders"
    GROUP BY 1,2
  ) "t0"
  
  INNER JOIN (
    SELECT "Orders"."Category" AS "Category",
      SUM("Orders"."Sales") AS "__measure__0"
    FROM "TableauTemp"."Orders$" "Orders"
    GROUP BY 1
  ) "t1" 
  ON ("t0"."Category" IS NOT DISTINCT FROM "t1"."Category")
) "t2"

かなり処理が簡単になりましたね。順に見ていきます。

  1. ビューのディメンション(CategoryとSegment)からEXCLUDEで指定されたディメンション(Segment)を除外した粒度で{ EXCLUDE [Segment]: SUM([Sales])}を計算し、CategoryごとのSUM([Sales])を計算する

  2. 元データをビューのディメンション(CategoryとSegment)で集計する

  3. EXCLUDEで指定されたディメンションをビューのディメンションから除外した後に残ったディメンション(つまりCategory)をキーにして、2と3を結合する


EXCLUDEが面白いのは、{ EXCLUDE [Segment]: SUM([Sales])}の後に集計するためにクエリを必要としないことです。

{ EXCLUDE [Segment]: SUM([Sales])}は元のビューの集計データよりも粒度が粗いので、そのまま結合しても何ら悪さはしませんよね。

実際に上記3の結合を見ても、EXCLUDEは3件、ビューの集計データは9件なので、EXCLUDEを本来であれば集計しようがありません。

(ビュー上はEXCLUDEに何らかの集計関数を使用できますが、どの集計でも同じ結果を返します)

LOD計算を行レベル計算で使用した場合

(2021/5/22 追記)

最後にLOD計算を行レベル計算内で使用した場合の処理について言及します。


まずFIXEDを使用した以下の例を見てみます。


(FIXED) Customers Purchased in the Last 90 Days

COUNTD(

IF DATE('2021-12-31') - { FIXED [Customer ID]: MAX([Order Date])} <= 89

THEN [Customer ID]

END

)


上記の式ではFIXEDを使用しCustomer IDごとの最終Order Dateを求め、2021/12/31含め90日以内に購入のあった顧客数を求めています。


このビューの処理は以下です。

SELECT 
  "Orders"."Category" AS "Category",
  "Orders"."Segment" AS "Segment",
  SUM("Orders"."Sales") AS "sum:Sales:ok",
  COUNT(DISTINCT (CASE WHEN ((TABLEAU.DATE('2021-12-31') - "t0"."__measure__0") <= 89) THEN "Orders"."Customer ID" ELSE null::text END) COLLATE "ja") AS "usr:Calculation_756041832373567488:ok"

FROM "TableauTemp"."Orders$" "Orders"

INNER JOIN (
  SELECT 
    "Orders"."Customer ID" AS "Customer ID",
    MAX("Orders"."Order Date") AS "__measure__0"
  FROM "TableauTemp"."Orders$" "Orders"
  GROUP BY 1
) "t0" ON ("Orders"."Customer ID" IS NOT DISTINCT FROM "t0"."Customer ID")

GROUP BY 1,2

行レベル計算なのでFIXEDの結果を行に付与する必要があるので当たり前ですが、FIXEDの計算結果はディメンションとして使用した場合と同様に、元データの行に付与されます。

そして各行に付与されたFIXEDの結果を使用して必要な計算を実施し、最後にメジャーとして集計をかけています。


これはINCLUDE/EXCLUDEを使用した場合も同様の処理になり、行レベル計算に使用されるLOD計算は、結果を各行(各レコード)に付与するため、元データに結合される形になります。

最後に

今回はLOD計算を視覚的に理解することを目標に解説をしました。

実際にTableau Prepでフローファイルを触ってみること、またLOD計算を書いてみること、そして作ったLOD計算をTableau Prep等で再現してみることをオススメします。

理解して実践することが習得には肝要です。


長い記事となりましたが、この記事がLOD計算を習得する上での助けになれば幸いです。

ご質問などはTwitterかLinkedinへよろしくお願いします。それでは。