⭐

複数スタースキーマ

2021/01/02に公開

モデリング

データウェアハウス

スタースキーマ

tech

複数スタースキーマ(Multiple star schema)

1つのファクトで、全ての分析対象がカバー出来ることは稀である。ほとんどのケースで複数のファクトテーブルが必要になるだろう。本当に価値ある分析は複数のプロセスを横断した分析である。これを誤った方法で実現するとどうなるか？どうすれば良いのかを見ていく。

スタースキーマの作り方に関しては、別の記事にまとめている。

発生タイミングが異なるファクト

2つ以上のファクトがあったとする。それらは同時に発生しないファクトである場合、異なるファクトテーブルに配置するべきである。誤って単一ファクトテーブルにまとめられると、個々の分析が困難になる。もし分けていれば個々に分析が可能になる。

ある営業部門で以下のような分析要件があったとする。

日付、顧客、製品別注文数量の分析
日付、顧客、製品別出荷量の分析

ディメンションは日付と顧客。ファクトは製品別注文数量と製品別出荷量である。
ディメンションは同じものを共有している。ファクトは同タイミングでは発生しない。これは例えば、顧客が製品を注文した日に、すぐに出荷がない場合があることを指している。

誤った単一ファクトテーブルによる弊害

単一のファクトテーブルで、上記の要件を対応するとどうなるか見てみよう。

図1

図1のファクトテーブルは、粒度が注文数量(quantity_ordered)と出荷量(quantity_shipped)、またはその両方の日別(day_key)、製品別(product_key)、および顧客別(customer_key)に記録する。このような「または」、「および」が出てくるテーブルは地雷である。

day_key	customer_key	product_key	quantity_ordered	quantity_shipped
123	777	111	100	0
123	777	222	200	0
123	777	333	50	0
456	777	111	0	100
456	777	222	0	75
789	777	222	0	125

上記はファクトテーブルの内容である。day_keyとcustomer_keyとproduct_keyは、ディメンションテーブルを参照するための外部キーで、適当な値を入れている。そして、quantity_orderedとquantity_shippedはファクトである。

さて、このレコードが表している事実を紐解いてみよう。day_keyが123の時にcustomer_key 777は3つの製品を購入した様である。しかし、ここで問題が起きる。注文はしたが出荷はまだ完了していないため、出荷量を示すquantity_shippedには何を入れるべきかが悩ましい。恐らくNULLか0を入れるだろう。今回は0を入れることを想定してみよう。
次にday_keyが456の時に、product_keyの111と222の出荷が発生した。この時、注文はないが出荷は存在することになり、quantity_orderedにNULLを入れるか0を入れるか考える必要がある。今回は0を入れる想定でやろう。
さらに最後にday_key 789にproduct_keyの出荷が発生している。これも注文に何を入れるべきか悩ましいが、今回はquantity_orderedに0を入れておこう。

例えば、customer_keyが777の出荷に関する調査をしたいとする。このファクトテーブルからレポートを作成してみよう。1つのファクトテーブルが、2つ以上のプロセスを記録している場合、誰かが1つのプロセスのみを調査する場合に問題が起きる。そして、今回の調査対象は2つのプロセス(quantity_orderedとquantity_shipped)の内の1つだけに興味があり、この問題が浮き彫りになる。

product	quantity_shipped
Product111	100
Product222	200
Product333	0

※一般的にはレポートにproduct_keyは出さず、product_nameを出すため、レポート結果にはproduct_nameを出力している。

上記のレポートを見ればすぐに分かると思うが、最終行にproduct333の出荷量に0になった意味のないレコードがある。これはレポートを取得した時点で、product_key333が出荷はされてないが、quantity_shippedに0が入ってることによる弊害である。これが実業務となるともっと意味のないレコードが大量に並ぶだろう。こうなっていると、恐らく担当者からは、「この0は何ですか？」と聞かれるだろう。データ構造的に仕方ないが、エンドユーザーからすると混乱の種でしかない。

基本的に2つ以上のファクトがあった時に、それらが同時に発生しないのであれば、それぞれファクトテーブルを用意することで問題は起きない。例えば、HAVING SUM(quantity_shipped) > 0 のようなことをして、設計の欠点をクエリで解決したとしよう。これはクエリが複雑になっているため、本質的な解決にはなっていない。当たり前だが、これを前提とした設計をしてしまうと、他のレポートクエリも同様にHAVING SUM(quantity_shipped) > 0で必ず回避する必要が出てくる。例えば、集計や平均値を取るとか、副問合せなどいろんな全ての場面でこの制約が生まれる。これが辛いのは火を見るより明らかである。

ファクトテーブルを分けたモデリング

最も好ましい解決策は素直に、プロセスごとにファクトテーブルを作ることである。

図2

複数のファクトテーブルの分析

個々のプロセスの分析は有用だが、業務においてはそれらを比較することも重要だろう。例えば、生産と受注の比較、受注と出荷の比較などである。適切に設計すると複数プロセス分析が可能になる。
ここで注意したいのが、異なるファクトテーブルを比較するには、同じSQLのSELECT句で収集してはいけない。これは重複したレコードが発生する危険性がある。これについては、ファントラップの記事で解説している。では、どうすれば良いか？それはドリルアクロスと呼ばれる手法で収集する必要がある。これを誤った手法で行った場合どうなるか？正しく行うとどうなるかをこれから解説していく。

扱うテーブルは、図2で示したものを使って、次のようなレポートを作るとする。

product	quantity_ordered	quantity_shipped
Product111	100	100
Product222	200	200
Product333	50

ファクトテーブルの結合の危険性

ファクトテーブル間で同じディメンションを共有した場合、各ファクトテーブルは兄弟関係にあると考えられる。
以下にテーブルに入っているレコードの例を示した。まずは、誤った結合の例を示す。

fact_orders

day_key	customer_key	product_key	quantity_ordered
123	777	111	100
123	777	222	200
123	777	333	50

fact_shipments

day_key	customer_key	product_key	quantity_shipped
456	777	111	100
456	777	222	75
789	777	222	125

例えば、以下のようなクエリを投げると誤った結果が返ってくる。

SELECT
    dimmension_products.product_name
    ,SUM(fact_orders.quantity_ordered)
    ,SUM(fact_shipments.quantity_shipped)
FROM
    fact_orders
    ,fact_shipments
    ,dimmension_products
WHERE
    fact_orders.product_key = dimension_products.prodcut_key
    AND fact_shipments.product_key = dimension_products.prodcut_key
    -- 必要に応じて日付でフィルター
GROUP BY
    dimension_products.product_key

クエリ結果。

product_name	SUM(quantity_ordered)	SUM(quantity_shipped)
Product111	100	100
Product222	400	200

Product222のSUM(quantity_ordered)の結果が400になっている。これは誤りで、本来なら200が返ってくるべきである。これは集計範囲内に1つの注文に対して2つの出荷があるため、素直に結合をしてしまうと重複が発生するからである。また、Product333はfact_ordersにあるがレポートには表示されていないことも注目してほしい。これはfact_shipmentsに対応するレコードがないからである。
※ディメンションテーブルとの結合には、外部結合を代用するのがおすすめするが、根本的な問題は解決されるわけではない。

ドリルアクロス

では、早速2つのプロセスを比較してみよう。ここに対して有効な手法はドリルアクロスである。これはドリルアップやドリルダウンといったドリルスルー機能とは無関係である。これは複数のプロセスを横断して分析することを意味した用語である。
ドリルアクロスの具体的なやり方としては、各スタースキーマのファクトをまず共通の粒度でまとめる。そして、それらを結合する。これだけである。

実際にドリルアクロスをしたSQLを以下に示した。ここで大事なポイントは、それぞれのファクトが同じ粒度になっていることである。これによって別々の結果であっても全く同じ粒度になり、重複なしで結合することが出来る。結合には完全な外部結合(FULL OUTER JOIN)を使うことで、片方の結果しかない場合でも結果を返すことが出来る。

WITH orders AS (
SELECT
    dimension_products.product_key
    ,dimension_products.product_name
    ,SUM(fact_orders.quantity_ordered) as quantity_ordered
FROM
    fact_orders
LEFT JOIN dimension_products ON dimension_products.product_key = fact_orders.product_key
GROUP BY
    dimesion_products.product_key
) , shipments AS (
SELECT
    dimension_products.product_key
    ,dimension_products.product_name
    ,SUM(fact_orders.quantity_shipped) as quantity_shipped
FROM
    fact_shipments
LEFT JOIN dimension_products ON dimension_products.product_key = fact_shipments.product_key
GROUP BY
    dimesion_products.product_key
) 

SELECT
    orders.product_name
    ,orders.quantity_ordered
    ,quantity_shipped
FROM
    orders
FULL OUTER JOIN shipments ON shipments.product_key = orders.product_key

product	quantity_ordered	quantity_shipped
Product111	100	100
Product222	200	200
Product333	50

ドリルアクロスの手順

全てのドリルアクロス操作は、以下の手順になる。手順に従えばファクトテーブルはいくつでも実現可能である。

フェーズ1 各ファクトテーブルに対してクエリする。
- 各クエリで同じディメンションを使う。
- 必要に応じて値を加工する。
フェーズ2 結果を組み合わせる。
- 共通のディメンションで完全な外側結合(FULL OUTER JOIN)で結果をマージする。
- 必要に応じてファクトの比較や計算する。

まとめ

単一のスタースキーマの世界から、実業務で要求される複数のスタースキーマをどうやって扱うかを見てきた。ドリルアクロスを使うことで、ファクトを横断した分析を可能になる。

複数のプロセスを扱う場合、別々のファクトテーブルを作ることで、個々のプロセスの分析が簡単になる。
2つのファクトが異なるプロセスなのか判断が難しい場合は、それらが異なるタイミングに発生しているか、または異なる粒度なのかを確認する。もし、異なればそれは別ファクトテーブルにするべきである。
複数のファクトテーブルを結合する場合、ファントラップが発生する危険があるため、ドリルアクロスを用いる。