最近、X で Lightdash をお勧めしているポストを複数見かけました。そこで、いろんなメトリクスを作ってみることで雰囲気をつかんでみようと思いました。この記事はその結果を記しておきます。
このようなデータモデルに対して、
- ○ 売上金額/数量合計のような単純な加算型メトリクスは容易に定義可能
- ○ 売上単価のような単純な非可算型メトリクスも容易に定義可能
- ○ 平均在庫数/月初在庫数のような準加算型メトリクスは工夫すれば定義可能
- × ドリル・アクロスが必要なメトリクスは定義不可
Lightdash は dbt で作成したディメンジョンテーブルやファクトテーブルを可視化することに特化した BI ツールです。
もう少し詳しく述べると、
- メトリクス（メジャー、KPI）の計算ロジック
- 複数テーブルの結合ロジック
- テーブル名やカラムの表示名
- テーブルやカラムの説明
などを dbt のモデルプロパティーファイル（例：schema.yml）で定義しておくと、ブラウザ上で可視化する際に利用・参照できることがメリットになっています。モデルプロパティーファイルでこれらを定義できるということは、これらの情報をコード管理することができる（バージョン管理できる、IaC で自動化しやすいなど）というメリットがあります。
本題からはずれますが、このメリットはセマンティックレイヤーに似たものがあります。これが私が Lightdash に興味を持った理由の一つです。ただし、Looker や dbt Semantic Layerと異なり他の BI ツールには今のところ対応していないようですが。
セマンティックレイヤーに関して興味があれば、以前に書いたブログもご確認ください。
今回は以下の組み合わせで試してみました。
- PostgreSQL 17.4
- dbt Core 1.9.3
- Lightdash v0.1573.1 (Self-hosting)
まず、PostgreSQL 上に以下の 4 つの可視化対象のテーブルを作成しておきます。
- ディメンジョンテーブル
- 商品ディメンジョン
- 日付ディメンジョン
- ファクトテーブル
- 販売ファクト
- 在庫ファクト
create table items (
item_id integer not null
, item_name varchar(100) not null
, item_category_name varchar(100) not null
);
create table dates (
date_key date not null
);
create table sales (
sales_id integer not null
, sales_datetime timestamp not null
, item_id integer not null
, sales_quantity integer not null
, sales_amount integer not null
);
create table stock (
stock_date date not null
, item_id integer not null
, stock_quantity integer not null
);
/* 主キー・外部キーの定義は省略 */
ER 図に起こすと以下のようになります。
（販売テーブルは日時 yyyy-mm-dd hh:mi:ss の一方、日付テーブルは日付 yyyy-mm-dd なので、厳密にはリレーションシップではないです。ただし、ここでは分かりやすさのために線を張っています）
次に、これらのテーブルデータを Lightdash で可視化するために dbt のモデルを作成します。今回は dbt でデータ加工・集計を行わないのですが、Lightdash 上で可視化するには dbt モデルである必要があるため、単純に名前を変えたモデルを作成・実行しておきます。
（別途 dbt_project.yml ファイルでデフォルトのマテリアライズド方法をビューに指定しています）
準備の最後として、ディメンジョンテーブル（商品と日付）のモデルプロパティーファイルを作成します。
v_items.yml
version: 2
models:
- name: v_items
description: '商品マスタ'
meta:
label: '商品ディメンジョン'
columns:
- name: item_id
description: '売上対象の商品のID'
meta:
dimension:
label: '商品ID'
- name: item_name
description: '商品の名称'
meta:
dimension:
label: '商品名'
- name: item_category_name
description: '商品のカテゴリ名'
meta:
dimension:
label: '商品カテゴリ'
v_dates.yml
version: 2
models:
- name: v_dates
description: '日付マスタ'
meta:
label: '日付ディメンジョン'
columns:
- name: date_key
description: "日付"
meta:
dimension:
label: '日付キー'
time_intervals: ['DAY', 'MONTH']
いくつか補足説明します。
- Lightdash 上で表示できるテーブルやカラム名の説明は dbt の
descriptionタグの内容を流用します。 - Lightdash に固有の情報は
metaタグの配下に記載します。- Lightdash 上で表示するテーブル名やカラム名に関しては、Lightdash 固有の
labelタグに記載します。 - 日付カラム（日付キー）に関して、Lightdash 上では自動で日、週、月、四半期、年の階層が作成されますが、今回は日、月のみ必要なため、
time_intervalsタグで指定します。
- Lightdash 上で表示するテーブル名やカラム名に関しては、Lightdash 固有の
ちなみに、全てのモデルに対するモデルプロパティーファイルを schema.yml ファイル 1 つにまとめることもできますが、今回は以下の推奨に従って 1 モデル = 1 モデルプロパティーファイルとしています。
We recommend structuring your dbt project with one .yml file per model (or .sql file).
これでメトリクス定義の準備が整いました。
この時点で、Lighdash のクエリページを見てみると定義した 2 つのテーブルが label で指定した名前で表示され、マウスを合わせると description の内容がツールチップに表示されます。
また、テーブルをクリックするとカラム一覧が表示されます。
以下、さまざまなタイプのメトリクスを作成していきます。
（今回の環境では PostgreSQLが JST タイムゾーンで動いている一方、Lightdah は UTC タイムゾーンで動いているため、クエリ結果の月表示が１か月前にずれているのでご注意ください）
3.1. 売上数量/金額合計（単純な加算メトリクス）
まずは単純に合計をとるようなメトリクスを作ります。
v_sales.yml
version: 2
models:
- name: v_sales
description: '日別商品ごとの売上数量/金額の集計結果'
meta:
label: '売上ファクト'
joins:
- join: v_items
type: inner
sql_on: ${v_sales.item_id} = ${v_items.item_id}
- join: v_dates
type: inner
sql_on: date_trunc('day', ${v_sales.sales_datetime}) = ${v_dates.date_key}
columns:
- name: item_id
meta:
dimension:
hidden: true
- name: sales_datetime
meta:
dimension:
hidden: true
- name: sales_quantity
meta:
dimension:
hidden: true
metrics:
total_sales_quantity:
label: '売上数量合計'
description: '売上数量の合計'
type: sum
- name: sales_amount
meta:
dimension:
hidden: true
metrics:
total_sales_amount:
label: '売上金額合計'
description: '売上金額の合計'
type: sum
少し前準備として以下を設定します。
-
joinsタグでv_itemsとv_datesの２つのディメンジョンとの結合条件を指定します。 -
item_idとsales_datetimeの 2 つのカラムはディメンジョンテーブルへの参照キーとなっています。Lightdash の画面上ではディメンジョンテーブル側を参照すればよいので表示不要です。そのためhidden: trueを指定して非表示にします。 -
sales_quantityとsales_amountの 2 つのカラムもデフォルトではディメンジョンとして扱われますが、ディメンジョンとしては扱ってほしくないのでhidden: trueで非表示にします。
ここからがメトリクス作成の本題ですが、meta タグの下に metrics というタグを作成して、その配下で 2 つのメトリクス total_sales_quantity と total_sales_amount を定義します。
- ともに
type: sumで合計であることを指定します。 -
labelとdescriptionはテーブル名/カラム名と同様に指定可能です。
この上で、Lightdash のクエリ画面で 商品カテゴリ、日付キー→Month、売上金額合計、売上数量合計を選択すると、想定通りの結果になります。
ちなみに、DB で実行されている SQL 文は以下になります。
SELECT
"v_items".item_category_name AS "v_items_item_category_name",
DATE_TRUNC('MONTH', "v_dates".date_key) AS "v_dates_date_key_month",
SUM("v_sales".sales_amount) AS "v_sales_total_sales_amount",
SUM("v_sales".sales_quantity) AS "v_sales_total_sales_quantity"
FROM "sales_db"."public"."v_sales" AS "v_sales"
INNER JOIN "sales_db"."public"."v_items" AS "v_items"
ON ("v_sales".item_id) = ("v_items".item_id)
INNER JOIN "sales_db"."public"."v_dates" AS "v_dates"
ON date_trunc('day', ("v_sales".sales_datetime)) = ("v_dates".date_key)
GROUP BY 1,2
ORDER BY "v_dates_date_key_month" DESC
LIMIT 500
3.2. 売上単価（単純な非可算型メトリクス）
次に売上単価（＝売上金額合計 ÷ 売上数量合計）のメトリクスを作成します。
v_sales.yml
version: 2
models:
- name: v_sales
description: '日別商品ごとの売上数量/金額の集計結果'
meta:
label: '売上ファクト'
metrics:
sales_unit_price:
label: '売上単価'
description: '売上金額合計 ÷ 売上数量合計'
type: number
sql: '${total_sales_amount} / ${total_sales_quantity}'
joins:
（以下略）
- 売上単価は特定のカラムに紐付くメトリクスではないため、テーブルの
metaタグ以下に記載します。 - 集約関数は使わないため、
type: numberを指定します。 -
sqlタグに計算ロジックを記述します。
すると、以下のように問い合わせすることができます。
実行される SQL 文も想定通りになっています。
SELECT
"v_items".item_category_name AS "v_items_item_category_name",
DATE_TRUNC('MONTH', "v_dates".date_key) AS "v_dates_date_key_month",
SUM("v_sales".sales_amount) AS "v_sales_total_sales_amount",
SUM("v_sales".sales_quantity) AS "v_sales_total_sales_quantity",
(SUM("v_sales".sales_amount)) / (SUM("v_sales".sales_quantity)) AS "v_sales_sales_unit_price"
FROM "sales_db"."public"."v_sales" AS "v_sales"
INNER JOIN "sales_db"."public"."v_items" AS "v_items"
ON ("v_sales".item_id) = ("v_items".item_id)
INNER JOIN "sales_db"."public"."v_dates" AS "v_dates"
ON date_trunc('day', ("v_sales".sales_datetime)) = ("v_dates".date_key)
GROUP BY 1,2
ORDER BY "v_dates_date_key_month" DESC
LIMIT 500
3.3. 平均/月初在庫数（準加算型メトリクス）
ある平均在庫数や月末の在庫数は、
- 商品ディメンジョン軸では加算（合計）してよい
- 日付ディメンジョン軸では加算してはいけない
- 平均在庫数では平均（
avg） - 月初在庫数では最後の値（
first_value）
- 平均在庫数では平均（
という、一部のディメンジョンのみ加算できるという特徴を持っており、準加算型メトリクスと呼びます。
まず、平均在庫数から。Lightdash では集計するディメンジョン軸によって複数の集約タイプを組み合わせることができないので、ここでは（合計在庫数）÷（日数）から求めることにします。
version: 2
models:
- name: v_stock
description: '日別商品ごとの在庫'
meta:
label: '在庫ファクト'
joins:
- join: v_items
type: inner
sql_on: ${v_stock.item_id} = ${v_items.item_id}
- join: v_dates
type: inner
sql_on: ${v_stock.stock_date} = ${v_dates.date_key}
metrics:
avg_stock_quantity:
label: '平均在庫数'
description: 'ある期間の日々の在庫数の平均'
type: number
sql: ${total_stock_quantity} / ${count_distinct_date}
columns:
- name: stock_date
meta:
dimension:
hidden: true
metrics:
count_distinct_date:
type: count_distinct
hidden: true
- name: item_id
meta:
dimension:
hidden: true
- name: stock_quantity
meta:
dimension:
hidden: true
metrics:
total_stock_quantity:
type: sum
hidden: true
- まず、合計在庫数を表す
total_stock_quantityというメトリクスをtype: sumで作成します。ただし、これは実際の分析では直接的には意味がないためhidden: trueで非表示にします。 - 同様に、日数を表す
count_distinct_dateというメトリクスをtype: count_distinctで作成し、同様に非表示にします。 - 最後にこの 2 つのメトリクスを割って、平均在庫数のメトリクス
avg_stock_quantityを作成します。
画面上では以下のような結果になります。
SQL 文も以下のように想定通りになっています。
SELECT
DATE_TRUNC('MONTH', "v_dates".date_key) AS "v_dates_date_key_month",
"v_items".item_category_name AS "v_items_item_category_name",
(SUM("v_stock".stock_quantity)) / (COUNT(DISTINCT "v_stock".stock_date)) AS "v_stock_avg_stock_quantity"
FROM "sales_db"."public"."v_stock" AS "v_stock"
INNER JOIN "sales_db"."public"."v_items" AS "v_items"
ON ("v_stock".item_id) = ("v_items".item_id)
INNER JOIN "sales_db"."public"."v_dates" AS "v_dates"
ON ("v_stock".stock_date) = ("v_dates".date_key)
GROUP BY 1,2
ORDER BY "v_dates_date_key_month" DESC
LIMIT 500
次に月初在庫数ですが、以下のように定義します。1 日のデータのみ合計するイメージですね。
（これより前は略）
begin_stock_quantity:
label: '月初在庫数'
description: '各月1日の在庫数'
type: sum
sql: (case when to_char(${v_stock.stock_date}, 'DD') = '01' then ${v_stock.stock_quantity} else 0 end)
画面上の結果は以下になります。
SQL 文も想定通りです。
SELECT
DATE_TRUNC('MONTH', "v_dates".date_key) AS "v_dates_date_key_month",
"v_items".item_category_name AS "v_items_item_category_name",
SUM((case when to_char(("v_stock".stock_date), 'DD') = '01' then ("v_stock".stock_quantity) else 0 end)) AS "v_stock_begin_stock_quantity"
FROM "sales_db"."public"."v_stock" AS "v_stock"
INNER JOIN "sales_db"."public"."v_items" AS "v_items"
ON ("v_stock".item_id) = ("v_items".item_id)
INNER JOIN "sales_db"."public"."v_dates" AS "v_dates"
ON ("v_stock".stock_date) = ("v_dates".date_key)
GROUP BY 1,2
ORDER BY "v_dates_date_key_month" DESC
LIMIT 500
3.4. 在庫回転数（ドリル・アクロスが必要なメトリクス）
在庫回転数はある期間で在庫が何回入れ替わったかを表す指標で、一般に売上数量合計 ÷ 平均在庫数で計算します。
ここで
- 売上数量合計は売上ファクトのメトリクス
- 平均在庫数は在庫ファクトのメトリクス
になります。そのため、在庫回転数を求めるには同じ粒度で複数のファクトテーブルのデータを集計し、その後に横に並べる必要があります。この操作をドリル・アクロスといいます。
Lightdash で複数のファクトテーブルを同時に選択するには、それら両方を参照しているディメンジョンのモデルプロパティーファイルに結合条件を指定する必要があります。そこで、v_items.yml に以下を追記します。その後にメトリクス stock_rotation_count を定義します。
v_items.yml
version: 2
models:
- name: v_items
description: '商品マスタ'
meta:
label: '商品ディメンジョン'
joins:
- join: v_sales
type: inner
sql_on: ${v_sales.item_id} = ${v_items.item_id}
- join: v_stock
type: inner
sql_on: ${v_stock.item_id} = ${v_items.item_id}
metrics:
stock_rotation_count:
label: '在庫回転数'
description: '在庫が何回入れ替わったか'
type: number
sql: ${v_sales.total_sales_quantity} / ${v_stock.avg_stock_quantity}
（以下略）
画面上の結果は以下になります。
また、SQL 文は以下になります。
SELECT
"v_items".item_category_name AS "v_items_item_category_name",
SUM("v_sales".sales_quantity) AS "v_sales_total_sales_quantity",
(SUM("v_stock".stock_quantity)) / (COUNT(DISTINCT "v_stock".stock_date)) AS "v_stock_avg_stock_quantity",
(SUM("v_sales".sales_quantity)) / ((SUM("v_stock".stock_quantity)) / (COUNT(DISTINCT "v_stock".stock_date))) AS "v_items_stock_rotation_count"
FROM "sales_db"."public"."v_items" AS "v_items"
INNER JOIN "sales_db"."public"."v_sales" AS "v_sales"
ON ("v_sales".item_id) = ("v_items".item_id)
INNER JOIN "sales_db"."public"."v_stock" AS "v_stock"
ON ("v_stock".item_id) = ("v_items".item_id)
GROUP BY 1
ORDER BY "v_sales_total_sales_quantity" DESC
LIMIT 500
実はこの SQL 文は間違っています。本来は、v_items と v_sales を結合・集計した結果と、v_items と v_stock を結合・集計した結果を結合する必要があるのですが、上の SQL 文は 3 テーブルを普通に結合した後に集計しており、件数爆発しています。（3.1. で売上数量合計が 2,752 であるのに、今回は 239.989 に増幅されている）
このような問題を Chasm Trap または Fan Trap といいドリル・アクロスを行う際のよくある誤りになります。
これを BI ツールで回避するには、リレーションシップがどの向きで 1 対 多であるか分かる必要があります（多 対 1 対 多の結合を検知したら Chasm Trap 回避を行う）。Looker や dbt Semantic Layer、その他の BI ツールではリレーションシップの多重度を指定できるのですが、Lightdash では指定する機能がないため、現時点では Chasm Trap を回避できません。
一応、Lightdash のロードマップには “One-to-many joins in Lightdash (fan-outs & chasm traps)” と含まれているので、そのうち対応されるとは思いますが。
今回は Lightdash で以下のメトリクスを定義しようとしてみました。
- 単純な加算型メトリクス（合計） ⇒ 可能
- 単純な非可算型メトリクス（平均） ⇒ 可能
- 準加算型メトリクス ⇒ 工夫すれば可能
- ドリル・アクロスが必要なメトリクス ⇒ 不可
今回は dbt のモデルプロパティーファイル = Lightdash のセマンティックレイヤー層でメトリクスを作ることを頑張ってみましたが、実際には dbt モデルで作る選択肢や、可視化のレイヤーで作る選択肢もあります。
どこに何の処理を持たせるかは設計上非常に重要なため、どのレイヤーで何が可能か知っておくという点ではいろいろ情報が得られたトライだったかなと思っています。
個人的にはドリル・アクロス未対応やその他細かい部分で嫌いな部分も少なくないので、Lightdash のこれ以上の調査はちょっとペンディングにしようと思っています。
Views: 0
