ディメンショナルモデリング

ストーリー

田

田中VPoE

DWHの設計で最も重要なのがディメンショナルモデリングだ。Ralph Kimballが提唱した手法で、30年以上経った今でもDWH設計の基本中の基本だ

あなた

ファクトテーブルとディメンションテーブルに分ける手法ですよね

あ

田

田中VPoE

そうだ。ファクトは「何が起きたか」を記録し、ディメンションは「誰が、いつ、どこで、何を」を説明する。この分離が、分析クエリの柔軟性と性能を両立する鍵だ

あなた

スタースキーマとスノーフレークスキーマの違いはどこにありますか？

あ

田

田中VPoE

スタースキーマはディメンションを非正規化してシンプルに保つ。スノーフレークスキーマはディメンションを正規化する。モダンDWHではストレージが安いので、スタースキーマが推奨される。JOINが少ない方がクエリも速い

ファクトテーブルの設計

ファクトテーブルの種類

種類	格納するデータ	例	粒度
トランザクションファクト	個別のビジネスイベント	注文、決済、クリック	1行 = 1イベント
周期スナップショットファクト	定期的な状態のスナップショット	日次売上サマリー、月次在庫	1行 = 1期間 × 1ディメンション
累積スナップショットファクト	プロセスの進行状況	注文の受注→出荷→到着	1行 = 1プロセス
ファクトレスファクト	メジャーのないイベント	出席、資格付与	1行 = 1イベント（メジャーなし）

設計原則

原則	説明
粒度を最初に定義	「1行が何を意味するか」を先に決める
加法性の確認	メジャーが集計可能かを確認する
外部キーの設定	全ディメンションへの参照を持つ
デジェネレートディメンション	注文番号など、ファクトに含まれるディメンション

-- トランザクションファクトの例
CREATE TABLE gold.fact_orders (
    -- サロゲートキー
    order_key       INT64 NOT NULL,

    -- ディメンション外部キー
    date_key        INT64 NOT NULL,
    customer_key    INT64 NOT NULL,
    product_key     INT64 NOT NULL,
    promotion_key   INT64 NOT NULL,

    -- デジェネレートディメンション
    order_number    STRING NOT NULL,

    -- メジャー（加法的）
    quantity        INT64,
    unit_price      NUMERIC(10,2),
    discount_amount NUMERIC(10,2),
    net_amount      NUMERIC(10,2),
    tax_amount      NUMERIC(10,2),

    -- メタデータ
    _loaded_at      TIMESTAMP
)
PARTITION BY DATE(_loaded_at)
CLUSTER BY customer_key, date_key;

ディメンションテーブルの設計

Slowly Changing Dimension（SCD）

タイプ	方法	用途	例
Type 0	変更しない	不変の属性	生年月日、初回登録日
Type 1	上書き更新	履歴が不要な属性	メールアドレス、電話番号
Type 2	新行追加	履歴が必要な属性	顧客セグメント、住所
Type 3	列追加	直前の値のみ保持	現在の部門 + 前回の部門

SCD Type 2 の実装

-- SCD Type 2: 顧客ディメンション
CREATE TABLE gold.dim_customer (
    -- サロゲートキー（各バージョンに一意）
    customer_key    INT64 NOT NULL,

    -- ナチュラルキー（ビジネスキー）
    customer_id     STRING NOT NULL,

    -- 属性
    customer_name   STRING,
    email           STRING,
    segment         STRING,    -- Enterprise / SMB / Startup
    industry        STRING,
    country         STRING,

    -- SCD Type 2 メタデータ
    effective_from  DATE NOT NULL,
    effective_to    DATE,          -- NULLは現在のバージョン
    is_current      BOOLEAN NOT NULL,

    -- 監査
    _loaded_at      TIMESTAMP
);

-- SCD Type 2 のクエリ例

-- 現在の顧客セグメント別売上
SELECT
    c.segment,
    SUM(f.net_amount) AS total_revenue
FROM gold.fact_orders f
JOIN gold.dim_customer c
    ON f.customer_key = c.customer_key
    AND c.is_current = TRUE
GROUP BY c.segment;

-- 2024年時点での顧客セグメント別売上（履歴参照）
SELECT
    c.segment,
    SUM(f.net_amount) AS total_revenue
FROM gold.fact_orders f
JOIN gold.dim_customer c
    ON f.customer_key = c.customer_key
    AND f.order_date BETWEEN c.effective_from AND COALESCE(c.effective_to, '9999-12-31')
WHERE EXTRACT(YEAR FROM f.order_date) = 2024
GROUP BY c.segment;

日付ディメンション

設計

CREATE TABLE gold.dim_date (
    date_key        INT64 NOT NULL,       -- YYYYMMDD形式
    full_date       DATE NOT NULL,
    year            INT64,
    quarter         INT64,
    month           INT64,
    month_name      STRING,               -- January, February...
    week_of_year    INT64,
    day_of_week     INT64,
    day_name        STRING,               -- Monday, Tuesday...
    is_weekend      BOOLEAN,
    is_holiday      BOOLEAN,
    holiday_name    STRING,
    fiscal_year     INT64,
    fiscal_quarter  INT64
);

「日付ディメンションは見落とされがちだが極めて重要だ。『前年同期比』『営業日換算』『会計年度ベース』のような分析は、日付ディメンションなしでは実現困難だ」 — 田中VPoE

モデリングのベストプラクティス

Kimball vs Inmon

観点	Kimball（ボトムアップ）	Inmon（トップダウン）
アプローチ	部門ごとのデータマートから構築	全社統合DWHを先に設計
構築速度	速い（段階的に拡張）	遅い（全体設計が先）
整合性	コンフォームドディメンションで担保	統合データモデルで担保
適用場面	アジャイルな組織、段階的導入	大規模エンタープライズ

コンフォームドディメンション

コンフォームドディメンション:
  複数のファクトテーブルで共有される標準化ディメンション

  fact_sales ────┐
                 ├──→ dim_customer (共通定義)
  fact_support ──┘     - customer_id
                       - segment (定義統一: Enterprise/SMB/Startup)
                       - industry (定義統一: IT/製造/金融...)

  これにより、「売上分析」と「サポート分析」で
  同じ顧客セグメント定義を使用できる
  → 「同じ指標なのに数字が違う」問題の解消

まとめ

ポイント	内容
ファクトテーブル	トランザクション、周期スナップショット、累積スナップショットの3種類
ディメンションテーブル	SCD Type 0〜3で変更履歴を管理
日付ディメンション	会計年度、休日、前年同期比などの分析を支援
コンフォームドディメンション	複数ファクト間で統一された定義を使い、指標の不整合を防ぐ

チェックリスト

ファクトテーブルの3つの種類を説明できる
SCD Type 2の実装方法を理解した
日付ディメンションの重要性と設計方法を理解した
コンフォームドディメンションで指標の統一を実現できる

次のステップへ

次は「演習：データウェアハウスを設計しよう」です。Step 2で学んだデータレイク、DWH、レイクハウス、ディメンショナルモデリングの知識を使い、実際のシナリオでDWHを設計します。

推定読了時間: 30分