LOD計算を体系的に理解するための記事を昨年書きましたが、今回は少し踏み込んで「LOD計算の処理を視覚的に理解する」ことを目的に記事を書きます。
（正確にはLOD表現 / LOD Expressionsなのですが、コミュニティ的にはどちらも使われており、また計算機能なので以下LOD計算と表記します）
TableauのPerformance Recordを使用すると、ビューを作成するために必要なクエリを一部確認することができます。
それを利用してLOD計算のクエリを読み解き、Tableau Prepを使用して視覚的に処理フローを理解することを目指します。
（ちなみにビュー / Viewという言葉が以降出てきますが、これはTableauのシート：可視化を作成する場所として読んでください）
今回の記事に特別な前提知識は必要ありませんが、以下の記事を流し読みしておくといいかもしれません。
使用したWorkbookとTableau Prep Flowは以下にあります。
今回の記事はSQLが参考として出てきますが、本質は処理の可視化にありますので、SQLの知識を問いません。
Tableauレベルに関わらず、多くの方のTableau理解に役立てばと思います。
おことわり（SQL分かる方向け）
本記事ではPerformance Recordから抜き出したVizQL/SQLが出てきますが、本質的に同じ処理なのですが、実はTableau必ずしも本記事に記載したクエリで動いていません。
例えばサブクエリ＋JOINを使う代わりにWINDOW関数で処理している…など。
本記事はあくまで概念的、視覚的に処理を理解するための記事ですので、上記ご了承ください。
LOD計算の基本概念
まず初めに、LOD計算の基本的なところをおさらいします。
LOD計算とは端的に言えば「任意の粒度での集計を可能とするTableauの計算機能」です。
Tableauでの、厳密に言えばシートやビューでの集計粒度は、基本的には「ビューに使用されているディメンション」により決まります。
例えば以下の図では、RegionとSub-Categoryがディメンションとして使われています。
その２つのディメンションの組み合わせにより各データ点が作られ、それぞれの点でProfitとSalesのSUM()が集計されます。
またディメンションの組み合わせによるビューの集計粒度をViz-LODと呼びます。
（Visualization's Level of Detailの省略形でしょうか）
一方で、例えば「Regionを無視したSub-CategoryだけのSUM(Sales)をサイズに使いたい」場合に、少し方法を考えなければいけません。
このような集計粒度をViz-LODから変えた集計を行う際に、LOD計算は力を発揮します。
ところで、LOD計算には３種類あります：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ファイルを見て頂くとして、大事な点は以下のステップで処理されることです。
FIXEDのLOD計算を求めるため、Customer IDでOrder Date四半期の最小値を集計するステップ行い
FIXEDに使用したディメンションであるCustomer IDをキーにして、元データと結合する
ビューに使用されているディメンション（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"))
処理が少し複雑になりましたね。順に見ていきましょう。
ビューのディメンション（CategoryとSegment）＋FIXEDで指定されたディメンション（Order ID）でデータを集計する
{ FIXED [Order ID]: SUM([Sales])}を計算し、Order IDごとのSUM(Sales)を求める
１と２をFIXEDで指定されたディメンション（Order ID）をキーにして結合する
ビューのディメンション（CategoryとSegment）で集計し、 AVG({ FIXED [Order ID]: SUM([Sales])})を計算する
元データをビューのディメンション（CategoryとSegment）で集計する
ビューのディメンション（CategoryとSegment）をキーにして、４と５を結合する
要は「FIXEDの計算を実施した後に、ビューのディメンションで集計された集計データにFIXEDの計算結果を結合」しています。
ここでFIXEDがディメンションとして使用された場合と異なり、結合は元データの集計後に行われていることに注意してください。
(2021/5/22 追記)
ところで、なぜFIXEDをメジャーとして使用した場合はビューのディメンションで集計された集計データにFIXEDの計算結果を結合する必要があるのでしょうか。
これを理解するためにはTableauのブレンド機能、リレーション機能との類推が役立ちます。
（以下ブレンドでお話しますが）ブレンドとは基本的に２つ以上のデータソースの結合であり、基本的に粒度の異なる数値データを組み合わせるときに使用します。
例えば元々のSuperStoreデータに、四半期売上目標データを追加する場合を想定してください。
Superstoreは注文商品オーダーごとにレコードが作成されていますが、目標データは四半期ごとにレコードが出来ているとします。
これを元データに四半期をキーにしてJOIN、つまり目標データをSuperStoreの１行１行に追加すると…売上目標の数値がとんでもなく重複しますね。
このような数値の不適切な重複を避けるために、ブレンドまたはリレーションを使用し、集計結果を結合するというアプローチを使用します。
FIXEDの集計結果をメジャーとして使用した場合も同様に、数値の不適切な重複を避けるため「FIXEDの計算を実施した後に、ビューのディメンションで集計された集計データにFIXEDの計算結果を結合」するアプローチをとっています。
最後に比較のため、FIXEDがディメンションとして使用される場合とメジャーとして使用される場合のフロー図を再掲します。
FIXEDがディメンションとして使用された場合
FIXEDがメジャーとして使用された場合
FIXEDがディメンションとして使用された場合は
FIXED計算を実施
FIXED計算結果を元データへ、FIXEDで使用したディメンションをキーにして結合
ビューの集計粒度で集計
の順で実施されている一方で、FIXEDがメジャーとして使用された場合は
FIXED計算を実施
FIXED計算結果をビューの集計粒度で集計
元データをビューの集計粒度で集計
２と３をビューのディメンションをキーにして結合
という順番で実施されています。結合のタイミングが特に異なりますね。
LOD計算を使用する上で、まずは上記のFIXEDをディメンション／メジャーとして使用した場合の２通りの処理イメージを理解いただければと思います。
細かいことは実際に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に比べて処理は簡単です。
ビューのディメンション（CategoryとSegment）＋INCLUDEで指定されたディメンション（Order ID）で{INCLUDE [Order ID]: SUM([Sales])}を計算し、Category, Segment, Order IDごとのSUM([Sales])を計算する
ビューのディメンション（CategoryとSegment）で集計し、 AVG({INCLUDE [Order ID]: SUM([Sales])})を計算する
元データをビューのディメンション（CategoryとSegment）で集計する
ビューのディメンション（CategoryとSegment）をキーにして、３と４を結合する
ちなみに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"
かなり処理が簡単になりましたね。順に見ていきます。
ビューのディメンション（CategoryとSegment）からEXCLUDEで指定されたディメンション（Segment）を除外した粒度で{ EXCLUDE [Segment]: SUM([Sales])}を計算し、CategoryごとのSUM([Sales])を計算する
元データをビューのディメンション（CategoryとSegment）で集計する
EXCLUDEで指定されたディメンションをビューのディメンションから除外した後に残ったディメンション（つまりCategory）をキーにして、２と３を結合する
EXCLUDEが面白いのは、{ EXCLUDE [Segment]: SUM([Sales])}の後に集計するためにクエリを必要としないことです。
{ EXCLUDE [Segment]: SUM([Sales])}は元のビューの集計データよりも粒度が粗いので、そのまま結合しても何ら悪さはしませんよね。
実際に上記３の結合を見ても、EXCLUDEは３件、ビューの集計データは９件なので、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へよろしくお願いします。それでは。