OLAP設計とディメンショナルモデリング

ストーリー

佐

佐藤CTO

分析クエリを速くするには、分析に最適化されたデータ構造が必要だ

佐

佐藤CTO

ディメンショナルモデリングは、Kimballが提唱した分析のためのデータモデリング手法だ。スタースキーマ、ディメンションテーブル、ファクトテーブル、そしてSCD（Slowly Changing Dimensions）を学ぼう

ディメンショナルモデリングの基本

スタースキーマ

graph LR
    Customer["dim_customer<br/>─────────<br/>customer_key<br/>name<br/>segment<br/>region"]
    Product["dim_product<br/>─────────<br/>product_key<br/>name<br/>category<br/>brand"]
    Date["dim_date<br/>─────────<br/>date_key<br/>date<br/>day_of_week<br/>month / quarter / year<br/>is_holiday"]
    Store["dim_store<br/>─────────<br/>store_key<br/>store_name<br/>city<br/>prefecture"]

    Fact["fct_sales<br/>─────────<br/>sale_id<br/>date_key（FK）<br/>product_key（FK）<br/>customer_key（FK）<br/>store_key（FK）<br/>quantity（measure）<br/>unit_price（measure）<br/>discount_amount（measure）<br/>total_amount（measure）"]

    Fact --> Customer
    Fact --> Product
    Fact --> Date
    Fact --> Store

    classDef fact fill:#fee2e2,stroke:#ef4444,font-weight:bold
    classDef dim fill:#dbeafe,stroke:#3b82f6
    class Fact fact
    class Customer,Product,Date,Store dim

ファクトテーブルの種類

種類	説明	例
トランザクションファクト	1行=1イベント	売上明細、注文明細
定期スナップショットファクト	定期的な断面	月次在庫、日次残高
累積スナップショットファクト	プロセスの進捗	注文→出荷→配送のマイルストーン

-- トランザクションファクト
CREATE TABLE fct_sales (
  sale_id BIGINT NOT NULL,
  date_key INT NOT NULL REFERENCES dim_date(date_key),
  product_key INT NOT NULL REFERENCES dim_product(product_key),
  customer_key INT NOT NULL REFERENCES dim_customer(customer_key),
  store_key INT NOT NULL REFERENCES dim_store(store_key),
  -- メジャー（計測値）
  quantity INT NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  discount_amount DECIMAL(10,2) DEFAULT 0,
  total_amount DECIMAL(10,2) NOT NULL,
  cost_amount DECIMAL(10,2) NOT NULL,
  profit_amount DECIMAL(10,2) GENERATED ALWAYS AS (total_amount - cost_amount) STORED
);

-- 定期スナップショットファクト
CREATE TABLE fct_inventory_daily (
  date_key INT NOT NULL,
  product_key INT NOT NULL,
  store_key INT NOT NULL,
  -- 日次スナップショットのメジャー
  quantity_on_hand INT NOT NULL,
  quantity_on_order INT NOT NULL,
  quantity_reserved INT NOT NULL,
  days_of_supply DECIMAL(5,1),
  PRIMARY KEY (date_key, product_key, store_key)
);

-- 累積スナップショットファクト
CREATE TABLE fct_order_fulfillment (
  order_key INT PRIMARY KEY,
  order_date_key INT,
  payment_date_key INT,       -- NULL = まだ決済されていない
  ship_date_key INT,           -- NULL = まだ出荷されていない
  delivery_date_key INT,       -- NULL = まだ配達されていない
  -- マイルストーン間の日数
  days_to_payment INT,
  days_to_ship INT,
  days_to_delivery INT,
  current_status VARCHAR(20)
);

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

日付ディメンション

-- 日付ディメンション: 分析の基本軸
CREATE TABLE dim_date (
  date_key INT PRIMARY KEY,        -- YYYYMMDD形式
  full_date DATE NOT NULL,
  day_of_week INT NOT NULL,        -- 0=日, 6=土
  day_name VARCHAR(10) NOT NULL,   -- '月曜日'
  day_of_month INT NOT NULL,
  day_of_year INT NOT NULL,
  week_of_year INT NOT NULL,
  month_number INT NOT NULL,
  month_name VARCHAR(10) NOT NULL,
  quarter INT NOT NULL,
  year INT NOT NULL,
  fiscal_year INT NOT NULL,
  fiscal_quarter INT NOT NULL,
  is_weekend BOOLEAN NOT NULL,
  is_holiday BOOLEAN NOT NULL,
  holiday_name VARCHAR(100),
  is_business_day BOOLEAN NOT NULL
);

-- 事前にデータ投入（10年分）
INSERT INTO dim_date
SELECT
  TO_CHAR(d, 'YYYYMMDD')::INT AS date_key,
  d AS full_date,
  EXTRACT(DOW FROM d) AS day_of_week,
  TO_CHAR(d, 'Day') AS day_name,
  EXTRACT(DAY FROM d) AS day_of_month,
  EXTRACT(DOY FROM d) AS day_of_year,
  EXTRACT(WEEK FROM d) AS week_of_year,
  EXTRACT(MONTH FROM d) AS month_number,
  TO_CHAR(d, 'Month') AS month_name,
  EXTRACT(QUARTER FROM d) AS quarter,
  EXTRACT(YEAR FROM d) AS year,
  -- 4月始まりの会計年度
  CASE WHEN EXTRACT(MONTH FROM d) >= 4
       THEN EXTRACT(YEAR FROM d)
       ELSE EXTRACT(YEAR FROM d) - 1
  END AS fiscal_year,
  CASE WHEN EXTRACT(MONTH FROM d) >= 4
       THEN CEIL((EXTRACT(MONTH FROM d) - 3) / 3.0)
       ELSE CEIL((EXTRACT(MONTH FROM d) + 9) / 3.0)
  END AS fiscal_quarter,
  EXTRACT(DOW FROM d) IN (0, 6) AS is_weekend,
  FALSE AS is_holiday,
  NULL AS holiday_name,
  EXTRACT(DOW FROM d) NOT IN (0, 6) AS is_business_day
FROM generate_series('2020-01-01'::DATE, '2030-12-31'::DATE, '1 day') AS d;

Slowly Changing Dimensions (SCD)

Type	戦略	説明	使い所
Type 1	上書き	古い値を新しい値で置き換え	修正、重要でない変更
Type 2	履歴保持	新しい行を追加、有効期間を管理	重要な属性の変更追跡
Type 3	前回値保持	現在値と前回値のカラムを持つ	直前の値のみ必要

-- SCD Type 2: 顧客セグメントの変更履歴を追跡

CREATE TABLE dim_customer (
  customer_key SERIAL PRIMARY KEY,     -- サロゲートキー
  customer_id VARCHAR(36) NOT NULL,    -- ビジネスキー（ナチュラルキー）
  name VARCHAR(200) NOT NULL,
  email VARCHAR(255),
  segment VARCHAR(50) NOT NULL,        -- 変更追跡対象
  region VARCHAR(50) NOT NULL,         -- 変更追跡対象
  -- SCD Type 2 メタデータ
  effective_from DATE NOT NULL,
  effective_to DATE DEFAULT '9999-12-31',
  is_current BOOLEAN NOT NULL DEFAULT TRUE
);

-- SCD Type 2 更新プロセス
-- Step 1: 変更のあるレコードを特定
WITH changes AS (
  SELECT
    s.customer_id,
    s.name,
    s.email,
    s.segment AS new_segment,
    s.region AS new_region,
    d.customer_key,
    d.segment AS old_segment,
    d.region AS old_region
  FROM staging_customers s
  JOIN dim_customer d ON s.customer_id = d.customer_id AND d.is_current = TRUE
  WHERE s.segment != d.segment OR s.region != d.region
)
-- Step 2: 現在の行を終了
UPDATE dim_customer SET
  effective_to = CURRENT_DATE - 1,
  is_current = FALSE
FROM changes
WHERE dim_customer.customer_key = changes.customer_key;

-- Step 3: 新しい行を追加
INSERT INTO dim_customer (customer_id, name, email, segment, region, effective_from, is_current)
SELECT customer_id, name, email, new_segment, new_region, CURRENT_DATE, TRUE
FROM changes;

SCD Type 2 のクエリパターン

-- 現在のセグメント別売上
SELECT
  c.segment,
  SUM(f.total_amount) AS total_revenue
FROM fct_sales f
JOIN dim_customer c ON f.customer_key = c.customer_key
WHERE c.is_current = TRUE
GROUP BY c.segment;

-- 過去のある時点でのセグメント別売上
-- 「2023年Q4時点のセグメントで2023年Q4の売上を集計」
SELECT
  c.segment,
  SUM(f.total_amount) AS total_revenue
FROM fct_sales f
JOIN dim_customer c ON f.customer_key = c.customer_key
JOIN dim_date d ON f.date_key = d.date_key
WHERE d.full_date BETWEEN c.effective_from AND c.effective_to
  AND d.year = 2023 AND d.quarter = 4
GROUP BY c.segment;

スノーフレークスキーマ

graph LR
    Fact["fct_sales<br/>product_key<br/>..."] --> Product
    Product["dim_product<br/>product_key<br/>brand_key（FK）<br/>subcategory_key（FK）"]
    Product --> Brand["dim_brand"]
    Product --> SubCat["dim_subcategory"]
    SubCat --> Cat["dim_category"]

    classDef fact fill:#fee2e2,stroke:#ef4444,font-weight:bold
    classDef dim fill:#dbeafe,stroke:#3b82f6
    classDef norm fill:#f0fdf4,stroke:#22c55e
    class Fact fact
    class Product dim
    class Brand,SubCat,Cat norm

スタースキーマ: ディメンションが非正規化（JOINが少ない、クエリが速い）スノーフレーク: ディメンションが正規化（ストレージ効率、更新が容易）

比較	スタースキーマ	スノーフレークスキーマ
JOIN数	少ない	多い
クエリ性能	高速	やや遅い
ストレージ	冗長	効率的
保守性	ディメンション更新が広範囲	正規化で更新が局所的
推奨	分析クエリ最優先	ストレージ制約がある場合

まとめ

ポイント	内容
スタースキーマ	ファクトを中心にディメンションを配置する基本形
ファクトテーブル	トランザクション/定期/累積の3種類
ディメンション	分析の切り口、日付ディメンションが最重要
SCD	Type 2 で属性変更の履歴を追跡

チェックリスト

スタースキーマとスノーフレークスキーマの違いを説明できる
ファクトテーブルの3種類を適切に使い分けられる
SCD Type 2 の設計と実装方法を理解した
日付ディメンションの設計ポイントを理解した

次のステップへ

次はリアルタイム分析基盤を学び、秒単位での分析を実現するOLAPエンジンの設計を理解します。

推定読了時間: 40分