OmniではOne to Manyの結合時のfan outをどのように解消しているか確認してみた
さがらです。
BIツールのよくある悩みの1つとして、「One to ManyのリレーションシップでJOINした場合にレコードが重複してしまうfan out現象が起きてしまう」という問題があると思います。
この問題をOmniはどのように解消しているか確認してみたので、内容をまとめてみます。
参考記事
以下のOmniのコミュニティノートに記載があるため、こちらを参考に試していきます。
試してみた
One to ManyのJOIN設定
まず検証のためにOne to ManyのJOIN設定が必要なのですが、下図のようにJOINを設定しました。
fan outが発生するクエリをOmniから実行してみる
One側のメジャーとMany側のメジャーを同時に選択してみると、下図のようにエラーが表示されます。
このエラーについてはOmniのコミュニティノートでも言及があり、対策としてはプライマリキーを各viewに対して設定する必要があります。
プライマリキーの設定
Omniではプライマリキーの設定をGUIベースで行うことが出来ます。
今回はJOINしている以下のviewについて、プライマリキーを設定しました。
CustomersviewのCustomer ID
OrdersviewのOrder ID
fan outが発生するクエリをOmniから実行してみる（プライマリキー設定後）
改めて先程エラーが発生した、One側のメジャーとMany側のメジャーを同時に選択してみると、今度はエラーがなく解消されました！
どのようなSQLが作成されているかを確認してみると、OMNI_SUMという関数で集計されていることがわかります。
実際にDWHに発行されているSQLはInspectorから確認ができます。Inspectorから見ると、下図のようなSQLが実行されていました。
実際にDWHに発行されていたクエリを書き出したものが下記となります。生成AIに解説してもらった内容も添付しておきます。
このクエリは、
customersとordersのOne to Many関係でJOINした際に発生するfan out問題を、UNION ALLとフラグを使った手法で解決しています。通常、顧客テーブルと注文テーブルをJOINすると、1人の顧客に対して複数の注文レコードが結合されるため、customers側のlifetime_spendのようなメジャーが注文件数分だけ重複してカウントされてしまいます。
この問題を避けるため、クエリはUNION ALLで2つの異なる集計パターンを作成しています。1つ目のパターン（$f4=0）では、CUSTOMER_IDをGROUP BY句に含めることで、顧客単位での正確な集計を実現しています。2つ目のパターン（$f4=1）では、CUSTOMER_IDを除外してGROUP BYを行い、orders側の集計を行います。
最終的に外側のクエリで、このフラグ値を使ってCASE文で振り分けを行います。$f4=1の場合はorder_total_sumをMINで取得し、$f4=0の場合はlifetime_spend_sumをSUMで集計します。これにより、それぞれのメジャーが適切な粒度で計算された値から取得されるため、重複計算を防ぎながら異なる粒度のメジャーを同時に表示できます。
WITH "$with_t_0" AS (SELECT *
FROM "MART"."CUSTOMERS" AS "omni_dbt_mart__customers"
LEFT JOIN (SELECT "CUSTOMER_ID" AS "CUSTOMER_ID0",
"ORDER_TOTAL"
FROM "MART"."ORDERS" AS "omni_dbt_mart__orders") AS "t" ON "omni_dbt_mart__customers"."CUSTOMER_ID" = "t"."CUSTOMER_ID0")
SELECT "omni_dbt_mart__customers.customer_name",
MIN(CASE
WHEN "$f4" = 1 THEN "$f2"
ELSE NULL
END) AS "omni_dbt_mart__orders.order_total_sum",
COALESCE(SUM(CASE
WHEN "$f4" = 0 THEN "$f3"
ELSE NULL
END), 0) AS "omni_dbt_mart__customers.lifetime_spend_sum"
FROM (SELECT "CUSTOMER_NAME" AS "omni_dbt_mart__customers.customer_name",
"CUSTOMER_ID" AS "omni_dbt_mart__customers__pk__0",
COALESCE(SUM("ORDER_TOTAL"), 0) AS "$f2",
MIN("LIFETIME_SPEND") AS "$f3",
0 AS "$f4"
FROM "$with_t_0"
GROUP BY 1, 2
UNION ALL
SELECT "CUSTOMER_NAME" AS "omni_dbt_mart__customers.customer_name",
NULL AS "omni_dbt_mart__customers__pk__0",
COALESCE(SUM("ORDER_TOTAL"), 0) AS "$f2",
MIN("LIFETIME_SPEND") AS "$f3",
1 AS "$f4"
FROM "$with_t_0"
GROUP BY 1) AS "t5"
GROUP BY "omni_dbt_mart__customers.customer_name"
ORDER BY 3 DESC NULLS LAST
LIMIT 1000
最後に
OmniではOne to Manyの結合時のfan outをどのように解消しているか確認してみました。
基本的には各viewに対してプライマリキーを設定するだけで、One to Manyの結合もOmniは問題なく対処できます！
