複数スタースキーマ

7 min read

複数スタースキーマ(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_keycustomer_keyproduct_keyは、ディメンションテーブルを参照するための外部キーで、適当な値を入れている。そして、quantity_orderedquantity_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_orderedquantity_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

Product222SUM(quantity_ordered)の結果が400になっている。これは誤りで、本来なら200が返ってくるべきである。これは集計範囲内に1つの注文に対して2つの出荷があるため、素直に結合をしてしまうと重複が発生するからである。また、Product333fact_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つのファクトが異なるプロセスなのか判断が難しい場合は、それらが異なるタイミングに発生しているか、または異なる粒度なのかを確認する。もし、異なればそれは別ファクトテーブルにするべきである。
  • 複数のファクトテーブルを結合する場合、ファントラップ が発生する危険があるため、ドリルアクロスを用いる。

複数スタースキーマにおけるディメンション設計

Conformed Dimensions にまとめました。

参考文献

以下の本を参考に、私なりの理解を書いたものになります。ちゃんと理解を深めたい方は、自分で読むことをおすすめします。
Star Schema: The Complete Reference

Discussion

ログインするとコメントできます