複数のスタースキーマ

スタースキーマを設計する際、分析対象のビジネスプロセスが複数ある場合、それぞれのプロセスに対して独立したファクトテーブルを作成することが重要である。この設計手法は、「Multiple Star Schemas（複数のスタースキーマ）」や「Multiple Fact Tables（複数のファクトテーブル）」と呼ばれる。複数のファクトテーブルを用いることで、各プロセスに特化した分析を行いやすくなる。

複数のスタースキーマを適切に設計することで、以下のようなメリットが得られる。

• プロセスごとの分析の簡略化: 各プロセスに特化したファクトテーブルを用意することで、プロセスごとの詳細な分析がしやすくなる。
• クエリのパフォーマンス向上: プロセスごとにファクトテーブルを分けることで、不要なデータを読み込む必要がなくなり、クエリの実行速度が向上する。
• スキーマの拡張性と柔軟性の向上: 新しいビジネスプロセスが追加された場合、既存のスキーマに影響を与えることなく、新しいファクトテーブルを追加できる。

したがって、複数のビジネスプロセスを分析対象とするスタースキーマを設計する際は、複数のファクトテーブルの採用を検討することが重要である。

本記事では、以下のような注文と出荷の 2 つのプロセスを例にして、複数のスタースキーマの設計について解説する。設計時の留意点やサンプルを通じて、複数のスタースキーマの理解を深めていただきたい。

単一のファクトテーブルが引き起こす問題

注文と出荷は、ビジネスプロセス上、密接に関連しているが、それぞれ独自のタイミングで発生する。顧客が商品を注文した時点では、出荷に関する詳細情報、例えば、いつ、どのように商品が届けられるかは決まっていないことがほとんどである。出荷情報は、在庫の確認や配送手配など、注文後のプロセスを経て確定する。

また、注文数量（ quantity_ordered ）と出荷数量（ quantity_shipped ）は、データとしての詳細度や粒度が異なる場合がある。例えば、顧客は 1 回の注文で複数の商品をまとめて購入することがあるが、商品の数量を分けて出荷したり、商品ごとに異なる倉庫から出荷されたりすることがある。この場合、出荷数量は個々の荷主に関連付けられるが、注文数量はそうではない。

このように、注文と出荷では、発生するタイミングやデータの詳細度が異なるため、単一のファクトテーブルでモデル化すると、様々な問題が生じる可能性がある。

実際に、単一のファクトテーブル（ fct_sales ）で、要件に対応したときにどのような問題が発生するか確認してみよう。

fct_sales

| order_date_key     | shipment_date_key  | customer_key | product_key  | quantity_ordered | quantity_shipped |
| ------------------ | ------------------ | ------------ | ------------ | ---------------- | ---------------- |
| hash("2020-01-01") | null               | hash("U001") | hash("P001") | 100              | null             |
| hash("2020-01-01") | null               | hash("U001") | hash("P002") | 50               | null             |
| hash("2020-01-01") | null               | hash("U001") | hash("P003") | 300              | null             |
| null               | hash("2020-01-02") | hash("U001") | hash("P001") | null             | 100              |
| null               | hash("2020-01-02") | hash("U001") | hash("P002") | null             | 50               |
| null               | hash("2020-01-02") | hash("U001") | hash("P003") | null             | 200              |
| null               | hash("2020-01-03") | hash("U001") | hash("P003") | null             | 100              |

テーブルの内容を説明すると以下の通りである。

• 2020-01-01 に、顧客「U001」が以下の商品を注文した:
  • 商品「P001」を 100 個
  • 商品「P002」を 50 個
  • 商品「P003」を 300 個
• 2020-01-02 に、顧客「U001」に以下の商品を出荷した:
  • 商品「P001」を 100 個出荷
  • 商品「P002」を 50 個出荷
  • 商品「P003」を 200 個出荷
• 2020-01-03 に、顧客「U001」に追加の出荷を行った:
  • 商品「P003」を 100 個出荷

注文と出荷の情報が同じテーブルに混在しているため、以下のような特徴がある。

• 注文の行（ order_date_key が存在する行）では、shipment_date_key と quantity_shipped が NULL になっている。これは、注文時点では出荷情報が確定していないためである。
• 出荷の行（ shipment_date_key が存在する行）では、order_date_key と quantity_ordered が NULL になっている。これは、出荷時点では注文情報が不要になるためである。
• 商品「 P003 」については、2 回に分けて出荷されている。最初の出荷（ 2020-01-02 ）で 200 個、2 回目の出荷（ 2020-01-03 ）で 100 個出荷されており、合計で 300 個出荷されている。

このように、発生するタイミングやデータの詳細度が異なるプロセスを単一のファクトテーブルでモデル化してしまったゆえに、次のような問題が浮き彫りになった。

• データの不整合: 注文時点では出荷情報が確定していないため、出荷関連の属性に NULL 値や仮の値（0 や 9999-12-31 など）を入れざるを得なくなる。
• 分析の複雑化: 注文と出荷の情報が混在することで、各プロセスに特化した分析を行うためのクエリが複雑になる。エンドユーザーはデータ構造を理解し、適切に where 句や having 句で絞り込まなければならない。
• パフォーマンスの低下: 単一のファクトテーブルに大量のデータが蓄積されると、クエリのパフォーマンスが低下する可能性がある。

単一のファクトテーブルは、テーブル構造自体に問題があるわけではないが、注文と出荷という 2 つのプロセスを 1 つのファクトテーブルで管理しようとしているために、エンドユーザーがデータの解釈や分析において混乱を招く可能性が高くなる。

これらの混乱を避けるためには、注文と出荷を別々のファクトテーブルに分割することが望ましい。それぞれのテーブルには、対応するプロセスに関連する情報のみを含める。これにより、データの解釈が容易になり、集計やビジネスルールの適用がシンプルになる。

別々のファクトテーブルでのモデリング

前述したように、注文と出荷という 2 つのプロセスを単一のファクトテーブルでモデル化すると、データの不整合や分析の複雑化など、様々な問題が生じる可能性がある。これらの問題を解決し、各プロセスを適切に分析するためには、注文と出荷を別々のファクトテーブルに分割してモデリングすることが有効である。

注文と出荷の各プロセスを、独自のファクトテーブルでモデル化したスタースキーマは、以下の通りである。

fct_order

| order_date_key     | customer_key | product_key  | quantity_ordered |
| ------------------ | ------------ | ------------ | ---------------- |
| hash("2020-01-01") | hash("U001") | hash("P001") | 100              |
| hash("2020-01-01") | hash("U001") | hash("P002") | 50               |
| hash("2020-01-01") | hash("U001") | hash("P003") | 300              |

fct_shipment

| shipment_date_key  | customer_key | product_key  | quantity_shipped |
| ------------------ | ------------ | ------------ | ---------------- |
| hash("2020-01-02") | hash("U001") | hash("P001") | 100              |
| hash("2020-01-02") | hash("U001") | hash("P002") | 50               |
| hash("2020-01-02") | hash("U001") | hash("P003") | 200              |
| hash("2020-01-03") | hash("U001") | hash("P003") | 100              |

もともと、注文と出荷には親子関係があったので、注文ファクトテーブル（ fct_order ）が持っていた顧客ディメンション（ dim_customer ）が、出荷ファクトテーブル（ fct_shipment ）にも継承されていることがわかる。

また、注文 ID や注文アイテム ID などの識別子を Degenerate Dimension（退化ディメンション）としてファクトテーブルに追加することで、注文と出荷の関連性を維持しつつ、それぞれのプロセスを独立して分析できる。

繰り返しになるが、注文の order_id と order_item_id が、出荷ファクトテーブルにも追加されているので、注文と出荷の関係性が失われることはない。

このように、注文と出荷を別々のファクトテーブルに分割してモデリングすることで、データの不整合を解消し、分析の柔軟性を高めることができる。また、Degenerate Dimension を活用することで、プロセス間の関連性を維持しつつ、より詳細な分析が可能になる。

クロスプロセス分析

個々のビジネスプロセスを詳しく分析することは大切だが、それと同じくらい、複数のプロセスを比較することも重要である。実際、最も価値のある分析の多くは、単一のプロセスに限定されるものではなく、複数のプロセスにまたがって行われるからである。

例えば、販売予測と実際の販売実績を比べたり、生産計画と受注状況を照らし合わせたり、受注内容と出荷実績を突き合わせたりするような分析がそれにあたる。こうした分析は、ビジネスの全体像を把握し、問題点を発見し、改善の機会を見出すために欠かせない。このような複数プロセスにまたがる分析を「クロスプロセス分析」と呼ぶ。

異なるファクトテーブルのデータを比較する際、同じ select 句でクエリを実行しないように注意が必要である。そうしてしまうと、ファントラップを招く可能性がある。代わりに、ドリルアクロス（Drilling Across） と呼ばれる 2 段階のプロセスでデータを収集する必要がある。このプロセスは、データのドリルアップやドリルダウンとは関係ない。

ファクトテーブル同士を結合する危険性

データの粒度が異なるファクトテーブル同士を結合すると、粒度の粗いテーブルが細かいテーブルに引っ張られ、ファントラップ（Fan Trap）と呼ばれる問題が発生することがある。例えば、注文と出荷の関係は 1 対多であるため、ファクトテーブルを直接結合すると、レコードが複製されてしまう危険性がある。

例として、商品ごとの注文数量と出荷数量のレポートを作成する場合を考えてみよう。本来の結果は以下のようになるはずだ。（レポートでは、ID ではなく商品名を表示している。）

product_name	quantity_ordered	quantity_shipped
Product 001	100	100
Product 002	50	50
Product 003	300	300

ところが、ファクトテーブル同士を結合し、同じ select 句で集計してしまうと、以下のような結果になってしまう。（注文数量がダブルカウントされている。）

select
    dim_product.product_name,
    sum(fct_order.quantity_ordered) as quantity_ordered,
    sum(fct_shipment.quantity_shipped) as quantity_shipped
from dim_product
left join fct_order on fct_order.product_key = dim_product.product_key
left join fct_shipment on fct_shipment.product_key = dim_product.product_key
group by 1;

# | product_name | quantity_ordered | quantity_shipped |
# | ------------ | ---------------- | ---------------- |
# | Product 001  | 100              | 100              |
# | Product 002  | 50               | 50               |
# | Product 003  | 600              | 300              |

このように、2 つのファクトテーブルを直接結合したり、共通のディメンションを介して結合したりすると、不正確な結果が生成される可能性がある。こうした問題を避けるためには、次節で紹介するドリルアクロスと呼ばれるアプローチを行う必要がある。

ドリルアクロス

2 つのプロセスを比較する適切な方法は、ドリルアクロスと呼ばれる。これは、クエリを個別のステップに分解することで、プロセスを横断した分析を成功させるためのテクニックである。これは「属性階層」で説明した ドリリング とは全く関係のない分析手法である。

ドリルアクロスは、大きく 2 つのフェーズに分かれる。

• フェーズ 1: 各ファクトテーブルに対して個別のクエリを実行する
  • 必要に応じて、各クエリに条件を付ける
  • 各クエリで同じディメンションを取得する
  • 選択したディメンションでファクトを集約する
• フェーズ 2: 結果セットを結合する
  • 共通のディメンションに基づいて、full join を実行する
  • 必要に応じて、ファクト間の比較や比率を計算する

具体的には、まずフェーズ 1 で、比較したい各プロセスのファクトテーブルに対して、個別のクエリを実行する。この際、各クエリで同じディメンションを取得し、そのディメンションでファクトを集約することが重要だ。これにより、各プロセスのデータを共通の粒度で集約することができる。

次に、フェーズ 2 で、フェーズ 1 で得られた結果セットを結合する。結合には、共通のディメンションに基づいた full join を使用する。これにより、両方のプロセスに存在するデータだけでなく、どちらか一方にしか存在しないデータも結果に含めることができる。最後に、必要に応じて、ファクト間の比較や比率を計算する。

-- Phase 1
with order_query as (
    select
        dim_product.product_name,
        sum(fct_order.quantity_ordered) as quantity_ordered
    from fct_order
    left join dim_product on fct_order.product_key = dim_product.product_key
    group by 1
),

shipment_query as (
    select
        dim_product.product_name,
        sum(fct_shipment.quantity_shipped) as quantity_shipped
    from fct_shipment
    left join dim_product on fct_shipment.product_key = dim_product.product_key
    group by 1
)

-- Phase 2
select * from order_query
full join shipment_query on (product_name)

# | product_name | quantity_ordered | quantity_shipped |
# | ------------ | ---------------- | ---------------- |
# | Product 001  | 100              | 100              |
# | Product 002  | 50               | 50               |
# | Product 003  | 300              | 300              |

このようにドリルアクロスを使うことで、データの粒度の違いによる問題を回避しつつ、複数のプロセスを横断した分析を行うことができる。ただし、ドリルアクロスを正しく機能させるためには、各プロセスのファクトテーブルが共通のディメンションを持っていることが前提となる。これは、データモデリングの段階で、コンフォームドディメンションを適切に設計しておく必要があることを意味している。

まとめ

スタースキーマを設計する際、分析対象のビジネスプロセスが複数ある場合、それぞれのプロセスに対して独立したファクトテーブルを作成することが重要である。これにより、各プロセスに特化した分析が容易になる。

また、個々のビジネスプロセスを詳しく分析することは大切だが、それと同じくらい、複数のプロセスを比較することも重要である。複数のプロセスを比較する分析を「クロスプロセス分析」と呼ぶ。クロスプロセス分析は、ビジネスの全体像を把握し、問題点を発見し、改善の機会を見出すために欠かせない。

クロスプロセス分析を行う際は、ファクトテーブル同士を直接結合すると、ファントラップと呼ばれる問題が発生する可能性がある。これを避けるためには、「ドリルアクロス」と呼ばれる 2 段階のプロセスでデータを収集する必要がある。

ドリルアクロスを正しく機能させるためには、各プロセスのファクトテーブルが共通のディメンションを持っていることが前提となる。これは、データモデリングの段階で、コンフォームドディメンションを適切に設計しておく必要があることを意味している。

参考文献

• Star Schema: The Complete Reference
• The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling Third Edition