ディメンショナルモデリングとは、Ralph Kimballが提唱した分析用データベース設計の技法で、「ビジネスプロセスの計測(ファクト)」と「分析の切り口(ディメンション)」の2軸でデータを整理します。スタースキーマの代表的な設計手法であり、ファクトテーブルの3タイプ、ディメンションテーブルの設計パターン、SCD(Slowly Changing Dimension)による履歴管理が3つの柱です。本記事では定義、ファクトとディメンションの設計、SCDの使い分け、ECサイトを例にした実践設計、よくある落とし穴までをSQL例付きで解説します。
ディメンショナルモデリングとは――分析に最適化したデータ構造の設計技法
ディメンショナルモデリングはRalph Kimballが1990年代に体系化した、DWH向けのデータベース設計技法です。業務システムで使われる正規化(第三正規形)とは正反対の思想で、「分析のしやすさ」を最優先に非正規化を許容します。なぜなら、業務系のトランザクション処理と分析系の集計処理では、最適なデータ構造が根本的に異なるからです。
ディメンショナルモデリングの基本思想は極めてシンプルです。「ビジネスプロセスの計測値」をファクトテーブルに、「分析の切り口となる属性」をディメンションテーブルに分け、両者をキーで結合する――この2軸の組み合わせでスタースキーマを構成します。売上分析なら、売上金額・数量がファクト、顧客・商品・日付・店舗がディメンションです。複雑な業務要件も、この2軸で分解することで整理されていきます。
ファクトテーブルの設計
ファクトテーブルは数値指標を格納するテーブルで、行数が非常に多くなるのが特徴です。Kimballは用途に応じて3つのタイプを定義しました。
タイプ1：トランザクションファクト
1行が1イベント(売上1件、注文1件、クリック1回)に対応するファクトです。最も一般的で直感的なタイプで、新規イベント発生時に追記するだけで運用できます。「いつ・誰が・何を・いくらで」を記録する形になり、時系列分析やコホート分析に強みがあります。
タイプ2：周期的スナップショットファクト
1行が一定期間(日次、週次、月次など)の集計値に対応するファクトです。「2024年11月の在庫残高」「2024年第3四半期の顧客ARPU」のように、タイミング単位の状態を記録します。増え続けるイベントログではなく、周期的な状態を保存したい場合に使います。
タイプ3：累積スナップショットファクト
1行が1つのライフサイクル全体(注文から配送完了までなど)に対応するファクトです。プロセスの進捗に応じてレコードが更新されていきます。受注→出荷→配達→返品可能期間終了など、段階を持つワークフローの分析に向いています。
-- トランザクションファクトテーブルの例
CREATE TABLE fct_orders (
order_key BIGINT PRIMARY KEY,
customer_key BIGINT NOT NULL,
product_key BIGINT NOT NULL,
date_key INT NOT NULL,
quantity INT NOT NULL,
amount DECIMAL(12, 2) NOT NULL,
created_at TIMESTAMP NOT NULL
);| タイプ | 粒度 | 更新パターン | ユースケース例 | 適する指標 |
|---|---|---|---|---|
| トランザクション | 1行=1イベント | 追記のみ | 売上ログ、クリックログ | 回数、金額、数量 |
| 周期的スナップショット | 1行=期間の状態 | 周期的追加 | 月次在庫残高、日次アクティブユーザー数 | 残高、指標値 |
| 累積スナップショット | 1行=ライフサイクル | 段階更新 | 注文プロセス、採用パイプライン | 所要時間、通過率 |
ディメンションテーブルの設計
ディメンションテーブルは分析の切り口となる属性情報を格納します。ファクトと対照的に、行数は少なく(数千〜数百万程度)、1行あたりのカラム数は多く、変更頻度は低い、という性質を持ちます。主要なディメンションパターンを紹介します。
- 日付ディメンション：ほぼすべての分析で使われる最重要ディメンション。年・月・四半期・曜日・祝日フラグ・会計年度などを事前計算して保持します。ファクトに日付カラムではなく日付キーを持たせ、日付ディメンションと結合するのが定石です。
- 顧客ディメンション：顧客の属性(氏名、住所、セグメント、会員ランクなど)を格納します。変更履歴の管理方法はSCD(後述)で決めます。
- 商品ディメンション：商品名、カテゴリ、ブランド、価格帯などを格納します。カテゴリ階層を階層的に持つか、非正規化して1テーブルに収めるかで設計が分かれます。
- ジャンクディメンション：独立したフラグ(例: 返品フラグ、プロモーションコードなど)をまとめたディメンション。少数のフラグをファクトに持つのではなく、フラグの組み合わせをジャンクディメンションとして抽出することで、ファクトをスリムに保てます。
- ロールプレイングディメンション：1つのディメンションを複数の役割で使うパターン。例えば日付ディメンションを「注文日」「配送日」「返品日」の3つの役割で同じ注文ファクトに結合するケースです。
-- 日付ディメンションテーブルの例
CREATE TABLE dim_dates (
date_key INT PRIMARY KEY, -- YYYYMMDD
full_date DATE NOT NULL,
year INT NOT NULL,
quarter INT NOT NULL,
month INT NOT NULL,
month_name VARCHAR(20) NOT NULL,
day INT NOT NULL,
day_of_week INT NOT NULL,
day_name VARCHAR(20) NOT NULL,
is_weekend BOOLEAN NOT NULL,
is_holiday BOOLEAN NOT NULL,
fiscal_year INT NOT NULL,
fiscal_quarter INT NOT NULL
);SCD(Slowly Changing Dimension)――ディメンションの変更をどう管理するか
ディメンションの属性は変化します。顧客が引っ越した、商品の価格が変わった、会員ランクが昇格した――こうした変更をどう記録するかを決めるのがSCD(Slowly Changing Dimension)です。
- SCD Type 1: 上書き更新：古い値を捨てて最新値だけを保持します。履歴は残りませんが、シンプルでストレージ負荷が最小。変更履歴が分析に不要な属性(誤字訂正など)に使います。
- SCD Type 2: 新行追加：変更のたびに新しい行を追加し、有効開始日と終了日で履歴を管理します。過去時点の正確な状態を再現できる反面、行数が増えます。住所や会員ランクなど、履歴が分析に重要な属性に使います。
- SCD Type 3: 別カラム追加：前回の値を別カラムに保持します。直前の値だけ参照できればよい場合に使いますが、長期履歴には向きません。
| タイプ | 履歴保持 | 実装複雑度 | ストレージ影響 | ユースケース |
|---|---|---|---|---|
| Type 1 | なし(上書き) | 低 | 最小 | 誤字訂正、補助属性 |
| Type 2 | 完全な履歴 | 中 | 大 | 住所、会員ランク、セグメント |
| Type 3 | 直前の値のみ | 低 | 中 | 直前比較が必要な属性 |
-- SCD Type 2 の実装イメージ(dbt snapshot 相当のSQL)
SELECT
customer_id,
customer_name,
address,
effective_from,
effective_to,
is_current
FROM (
SELECT
s.customer_id,
s.customer_name,
s.address,
s.updated_at AS effective_from,
LEAD(s.updated_at) OVER (PARTITION BY s.customer_id ORDER BY s.updated_at) AS effective_to,
CASE WHEN LEAD(s.updated_at) OVER (PARTITION BY s.customer_id ORDER BY s.updated_at) IS NULL THEN TRUE ELSE FALSE END AS is_current
FROM customer_source s
);スタースキーマの設計実践――ECサイトの売上分析を例に
理論だけでは抽象的なので、ECサイトの売上分析を題材にスタースキーマを設計してみましょう。分析要件は「月別・商品カテゴリ別・顧客セグメント別の売上とマージンを見たい」です。
- ファクト：fct_orders(注文ID、顧客キー、商品キー、日付キー、数量、金額、原価、マージン)
- ディメンション：dim_customers(顧客キー、顧客ID、セグメント、居住地)、dim_products(商品キー、商品名、カテゴリ、ブランド)、dim_dates(日付キー、年、月、四半期)、dim_channels(チャネルキー、チャネル名)
【ECサイトのスタースキーマ構成図】
[dim_dates]
|
[dim_customers]---[fct_orders]---[dim_products]
|
[dim_channels]
dim_customers: customer_key, customer_id, segment, region
dim_products: product_key, product_name, category, brand
dim_dates: date_key, year, month, quarter
dim_channels: channel_key, channel_name, channel_type
fct_orders: order_id, customer_key, product_key, date_key, channel_key, quantity, amount, marginこのスタースキーマに対して「月別×カテゴリ別の売上集計」を行うクエリは次のようになります。JOINがシンプルで、BIツールからも同じ構造で呼び出せます。
SELECT
d.year,
d.month,
p.category,
SUM(f.amount) AS total_sales,
SUM(f.margin) AS total_margin
FROM fct_orders f
JOIN dim_dates d ON f.date_key = d.date_key
JOIN dim_products p ON f.product_key = p.product_key
GROUP BY d.year, d.month, p.category
ORDER BY d.year, d.month, total_sales DESC;ディメンショナルモデリングの落とし穴
- ファクトテーブルにディメンション属性を入れてしまう：例えばfct_ordersに「顧客名」を直接持たせると、顧客名が変更されたときの整合性が取れなくなります。属性はディメンション、キーだけファクトというKimball流の原則を守りましょう。
- サロゲートキーを省略する：業務キー(customer_idなど)を直接ファクトに入れると、業務キーの変更や重複に弱くなります。サロゲートキー(customer_keyなど)を別途発行することで、ビジネス変化への耐性が得られます。
- 日付ディメンションを作らずファクトにタイムスタンプだけ持つ：日付の属性(曜日、祝日、会計年度など)をクエリで都度計算することになり、BIのパフォーマンスと保守性が落ちます。日付ディメンションは最初に作るべきテーブルです。
- ディメンションの粒度が不統一：dim_customersの粒度が「顧客」だったり「契約」だったり、途中で曖昧になるケース。1テーブルは1つの明確な粒度で設計する原則を守ります。
まとめ――ディメンショナルモデリングは「分析しやすさ」の設計
- ディメンショナルモデリングはファクトとディメンションの2軸でDWHを設計するKimball流の技法です。
- ファクトテーブルにはトランザクション、周期的スナップショット、累積スナップショットの3タイプがあります。
- ディメンションテーブルは日付、顧客、商品、ジャンク、ロールプレイングなどのパターンが典型です。
- SCD Type 1/2/3によってディメンションの変更履歴管理方法を使い分けます。
- 落とし穴は属性混入、サロゲートキー省略、日付ディメンション欠落、粒度不統一の4つです。
次のステップとして、A-12 データモデリング、B-01 dbt入門、A-07 データマートをご参照ください。DE-STKでは、スタースキーマ設計のレビューから、dbtによる実装、運用定着までを支援しています。
よくある質問(FAQ)
Q. ファクトテーブルとディメンションテーブルの違いは?
ファクトテーブルはビジネスの計測値(売上金額、数量等の数値)を格納し、ディメンションテーブルは分析の切り口(顧客名、商品カテゴリ、日付等の属性)を格納します。ファクトは行数が多く、ディメンションは行数が少なく変更頻度が低い、という性質の違いがあります。
Q. SCD Type 2はいつ使うべきですか?
顧客の住所変更や商品の価格改定など、過去の状態を分析に使いたい場合に必要です。「以前の住所で集計した売上」を正確に再現するにはType 2が不可欠です。全ディメンション属性にType 2を適用すると行数が爆発するため、履歴が分析に重要な属性だけに限定するのが定石です。
Q. スタースキーマの設計にツールは必要ですか?
必須ではありませんが、dbtを使うとモデルの依存関係管理・テスト・ドキュメント生成が自動化でき、設計の品質と保守性が大幅に向上します。手動運用で始めても後からdbtに移行は可能なので、規模に応じて段階的に導入するのがおすすめです。