🎯 STEP 14: データウェアハウス設計基礎
OLTPとOLAPの違いを理解し、分析用データベースの設計手法を学ぼう
- OLTPとOLAPの違いと特徴
- データウェアハウス（DWH）とは何か
- ファクトテーブルとディメンションテーブル
- スタースキーマとスノーフレークスキーマ
- データマート設計の考え方
学習時間の目安： 2.5時間 ｜ 前提知識： 正規化、インデックス設計の基本を理解していること
🎯 1. OLTPとOLAPの違い
STEP 13からの続き：分析用データベースの世界へ
STEP 13ではインデックス設計を学び、OLTPシステムのパフォーマンス最適化を習得しました。このSTEP 14では、分析用データベース（OLAP）の世界に足を踏み入れます。
データベースシステムには、大きく分けて2つのタイプがあります。これまで学んできた正規化は主にOLTPのためのものでしたが、分析用のOLAPではまったく異なる設計アプローチが必要です。
OLTP（オンライントランザクション処理）
日常的な業務処理のためのデータベースです。
- 📦 例：ECサイトの注文処理、銀行のATM、在庫管理システム
- ⚡ 特徴：リアルタイムで大量のトランザクションを処理
- ✏️ 操作：INSERT、UPDATE、DELETEが頻繁
- 🎯 目的：業務をスムーズに進めること
OLAP（オンライン分析処理）
分析・レポート作成のためのデータベースです。
- 📈 例：売上分析、顧客行動分析、経営ダッシュボード
- 🔍 特徴：大量のデータを集計・分析
- 📖 操作：主にSELECT（読み取り）、複雑な集計
- 🎯 目的：意思決定をサポートすること
OLTPとOLAPの比較
| 項目 | OLTP（業務処理） | OLAP（分析処理） |
|---|---|---|
| 主な用途 | 日常業務の処理（注文、決済、在庫管理） | 分析・レポート作成（売上分析、トレンド分析） |
| データ量 | 少量（現在のデータのみ） | 大量（過去数年分の履歴） |
| クエリパターン | 単純、定型的（特定の注文を検索） | 複雑、アドホック（多次元分析、集計） |
| 更新頻度 | リアルタイムで頻繁 | バッチ処理（日次、週次） |
| 正規化 | 第3正規形（高度に正規化） | 非正規化（スタースキーマ） |
| レスポンス時間 | ミリ秒単位 | 秒〜分単位 |
| ユーザー数 | 多数（数千〜数万人） | 少数（数十〜数百人） |
| 例 | ECサイト、会計システム、顧客管理システム | データウェアハウス、BIツール、分析レポート |
OLTPとOLAPでは求められる性能が全く違うため、同じデータベースで両方を実現するのは困難です。
✅ OLTP：素早い書き込みと読み取りが必要 → 正規化されたテーブル
✅ OLAP：複雑な集計を高速に実行したい → 非正規化されたテーブル
そのため、業務システム（OLTP）からデータを抽出し、分析用のデータウェアハウス（OLAP）を別途構築します。
🏢 2. データウェアハウス（DWH）とは？
データウェアハウスの定義
分析を目的とした大量のデータを統合・蓄積するデータベースです。
複数の業務システム（OLTP）からデータを集約し、分析しやすい形式で保存します。
データウェアハウスの特徴
- Subject-Oriented（主題指向）：業務プロセスではなく、分析したい主題（売上、顧客など）ごとに整理
- Integrated（統合された）：複数のシステムからデータを統合し、一貫性のある形式で保存
- Time-Variant（時系列）：過去のデータを保持し、時系列での分析が可能
- Non-Volatile（非揮発性）：データは削除されず、追加・更新のみ（履歴として保持）
データウェアハウスの構成
ETL処理とは
業務システムからデータウェアハウスにデータを移行するプロセスです。
- Extract（抽出）：複数の業務システムからデータを取り出す
- Transform（変換）：データの形式を統一、クレンジング、集計
- Load（ロード）：変換後のデータをDWHに格納
📊 3. ファクトテーブルとディメンションテーブル
データウェアハウスの設計では、テーブルを2種類に分けて考えます。
ファクトテーブル（Fact Table）
分析の対象となる数値データ（メジャー）を保持するテーブルです。
- 📊 内容：売上金額、数量、利益など、集計したい数値
- 🔑 キー：ディメンションテーブルへの外部キーを複数持つ
- 📏 粒度：「1行 = 1トランザクション」など、分析の最小単位
- 📦 サイズ：通常、非常に大きい（数百万〜数億行）
例：売上ファクトテーブル
ディメンションテーブル（Dimension Table）
分析の軸となる属性情報を保持するテーブルです。
- 📝 内容：商品名、カテゴリ、顧客名、地域など、説明的な情報
- 🔑 キー：サロゲートキー（人工的な連番ID）を主キーとする
- 📏 粒度：「1行 = 1商品」「1行 = 1顧客」など
- 📦 サイズ：ファクトテーブルに比べて小さい（数千〜数万行）
例：商品ディメンションテーブル
例：日付ディメンションテーブル
ファクトとディメンションの関係
| 項目 | ファクトテーブル | ディメンションテーブル |
|---|---|---|
| 役割 | 「何を測るか」数値データ | 「どう分析するか」分析の軸 |
| データ例 | 売上金額、数量、利益 | 商品名、顧客名、日付 |
| 行数 | 非常に多い（数百万〜数億行） | 比較的少ない（数千〜数万行） |
| 更新頻度 | 頻繁（新規追加） | 低頻度（マスタ更新） |
⭐ 4. スタースキーマ
スタースキーマとは
中心にファクトテーブル、周囲にディメンションテーブルを配置したデータウェアハウスの代表的な設計パターンです。
上から見ると星型（Star）に見えることから、この名前がつきました。
スタースキーマの構造
スタースキーマの実例
スタースキーマのメリット
- シンプルで理解しやすい：構造が明確で、ビジネスユーザーにも分かりやすい
- クエリが高速：JOINが少なく、集計クエリのパフォーマンスが良い
- 柔軟な分析：任意のディメンションで切り口を変えて分析できる
- BIツールとの相性が良い：多くのBIツールがスタースキーマに最適化されている
スタースキーマでの集計クエリ例
❄️ 5. スノーフレークスキーマ
スノーフレークスキーマとは
ディメンションテーブルを正規化したデータウェアハウスの設計パターンです。
雪の結晶（Snowflake）のような形に見えることから、この名前がつきました。
スタースキーマ vs スノーフレークスキーマ
スノーフレークスキーマのメリット・デメリット
| メリット | デメリット |
|---|---|
|
|
ほとんどの場合、スタースキーマを推奨します。
✅ スタースキーマ：シンプルで高速、BIツールとの相性が良い（推奨）
⚠️ スノーフレークスキーマ：ストレージが非常に限られている場合のみ検討
現代のストレージコストは安価なため、パフォーマンスとシンプルさを優先してスタースキーマを選ぶのが一般的です。
📦 6. データマート設計の考え方
データマートとは
特定の部門や目的に特化した小規模なデータウェアハウスです。
データウェアハウスから必要なデータを抽出・集約し、特定の分析ニーズに最適化します。
データウェアハウスとデータマートの関係
データマートの設計例
目的：営業実績の把握、目標達成率の確認
特徴：
- 営業部に必要なデータだけを抽出
- 目標金額、達成率など、営業固有の指標を追加
- 営業担当者の属性を詳細化
データマート設計のポイント
- 特定の目的に特化：部門や分析目的に応じて最適化
- スタースキーマ推奨：シンプルで使いやすい構造
- 集計済みデータ：頻繁に使う集計は事前計算して保持
- 適切な粒度：分析に必要な粒度で設計（日次、月次など）
- 定期的な更新：日次バッチでDWHからデータを更新
📝 7. データウェアハウス設計のまとめ
- OLTPとOLAP：業務処理と分析処理は目的が異なるため、別々のデータベースで設計
- データウェアハウス：分析を目的とした大量データの統合・蓄積基盤
- ファクトとディメンション：数値データ（ファクト）と分析軸（ディメンション）に分けて設計
- スタースキーマ：シンプルで高速、DWH設計の基本パターン
- スノーフレークスキーマ：正規化されたパターンだが、通常はスタースキーマを推奨
- データマート：特定部門向けに最適化された小規模DWH
データウェアハウスの設計は、OLTPとは全く異なるアプローチが必要です：
✅ OLTP：第3正規形、データの整合性を最優先
✅ DWH：スタースキーマ、クエリパフォーマンスを最優先
どちらも正しい設計ですが、目的が違うため、設計方針も異なります。
実務では、OLTPで業務データを管理し、それをETL処理でDWHに統合するのが一般的です。
📝 練習問題
OLTPとOLAPの違いを3つ以上挙げて説明してください。
- 主な用途：OLTPは日常業務処理（注文、決済など）、OLAPは分析・レポート作成
- クエリパターン：OLTPは単純で定型的なクエリ、OLAPは複雑な集計・多次元分析
- 更新頻度：OLTPはリアルタイムで頻繁に更新、OLAPはバッチ処理（日次、週次）
- 正規化：OLTPは第3正規形（高度に正規化）、OLAPは非正規化（スタースキーマ）
- データ量：OLTPは少量（現在のデータ）、OLAPは大量（過去数年分の履歴）
ファクトテーブルとディメンションテーブルの違いを説明してください。
ファクトテーブル：
- 分析の対象となる数値データ（売上金額、数量、利益など）を保持
- ディメンションテーブルへの外部キーを複数持つ
- 行数が非常に多い（数百万〜数億行）
- 「何を測るか」を表す
ディメンションテーブル：
- 分析の軸となる属性情報（商品名、カテゴリ、顧客名など）を保持
- サロゲートキーを主キーとする
- 行数は比較的少ない（数千〜数万行）
- 「どう分析するか」を表す
以下のデータを分析するためのスタースキーマを設計してください。ファクトテーブル1つ、ディメンションテーブル3つ以上を設計してください。
- 店舗別の日次売上を分析したい
- 商品カテゴリ別の売上傾向を把握したい
- 時間帯別の売上パターンを分析したい
- 従業員（レジ担当者）別の売上を比較したい
設計のポイント：
- ファクトテーブルには数値（数量、売上金額、原価、利益）を配置
- 各ディメンションには分析に使う属性を豊富に含める
- 日付と時間を分離することで、日次分析と時間帯分析の両方に対応
- 時間ディメンションに「時間帯」「営業区分」を追加して分析しやすくする
以下のスタースキーマを使って、「2025年1月の商品カテゴリ別・店舗別の売上合計」を取得するSQLを書いてください。
実行結果例：
スタースキーマとスノーフレークスキーマの違いを説明し、それぞれのメリット・デメリットを挙げてください。また、どのような状況でスノーフレークスキーマを選ぶべきか説明してください。
違い：
- スタースキーマ：ディメンションテーブルが非正規化されている（1つのテーブルに属性をフラットに格納）
- スノーフレークスキーマ：ディメンションテーブルが正規化されている（階層を別テーブルに分離）
スタースキーマのメリット・デメリット：
- ✅ クエリが高速（JOINが少ない）
- ✅ 構造がシンプルで理解しやすい
- ✅ BIツールとの相性が良い
- ❌ ストレージ使用量が多い（データの重複）
- ❌ ディメンションの更新時に複数行を変更する必要がある
スノーフレークスキーマのメリット・デメリット：
- ✅ ストレージ使用量が少ない
- ✅ データの整合性が高い
- ✅ ディメンションの更新が容易
- ❌ クエリが複雑になる（JOINが多い）
- ❌ パフォーマンスが低下する
- ❌ 理解しにくい
スノーフレークスキーマを選ぶべき状況：
- ストレージコストが非常に限られている場合
- ディメンションの属性が頻繁に変更される場合
- データの整合性が特に重要な場合
- ただし、現代ではストレージコストが安価なため、ほとんどの場合はスタースキーマを推奨
【総合問題】ECサイトのデータウェアハウスを設計してください。以下の分析要件を満たすスタースキーマを設計し、各テーブルの主要カラムを定義してください。
- 日次/月次/年次の売上トレンド分析
- 商品カテゴリ別の売上分析
- 顧客セグメント別の購買行動分析
- 地域別の売上分析
- プロモーション効果の測定
設計のポイント：
- ファクトテーブル：注文明細レベルの粒度で、数量・金額・利益などの数値を保持
- 日付ディメンション：様々な時間軸での分析に対応（年/四半期/月/週/日）
- 顧客ディメンション：セグメント分析、RFM分析に対応する属性を追加
- プロモーションディメンション：プロモーション効果を測定するための属性
分析クエリの例：
❓ よくある質問
Q1: OLTPとOLAPは必ず分ける必要がありますか？
規模が大きい場合は分けることを強く推奨します。小規模なシステム（数万件程度）であれば、OLTPデータベースで分析も行うことは可能です。しかし、データ量が増えると分析クエリが業務処理を遅くしたり、リソースの競合が発生したりします。目安として、トランザクションが月10万件を超えたら、DWHの導入を検討しましょう。
Q2: スタースキーマは正規化されていないので、データの整合性が心配です。
DWHでは問題ありません。理由として、DWHは読み取り専用に近く、データは追加のみで更新・削除はほぼありません。また、元データはOLTPシステムで整合性が保証されており、ETL処理時にクレンジング・検証を行います。DWHの目的は「高速な分析」であり、そのために非正規化は許容されます。
Q3: ファクトテーブルとディメンションテーブルの区別が難しいです。どう判断すればいいですか？
「集計したい数値」が含まれているか？という質問で判断できます。YESならファクトテーブル、NOならディメンションテーブルです。簡単な判断基準として「数値データ = ファクト」「文字データ = ディメンション」と考えるとわかりやすいです。
Q4: データウェアハウスは常にリアルタイムである必要がありますか？
いいえ、通常はリアルタイムである必要はありません。ほとんどの分析では「昨日までのデータ」で十分です。日次バッチで更新（深夜にETL処理）するのが一般的です。リアルタイムDWHが必要な場合もありますが、技術的に複雑で高コストなため、本当に必要か慎重に判断しましょう。
Q5: 小規模なシステムでもデータウェアハウスを導入すべきですか？
規模とニーズによります。複数システムのデータ統合、過去数年分のデータ保持、経営層向けダッシュボードが必要な場合は検討の価値があります。一方、データ量が少ない（数万件程度）場合や、単純な集計だけで十分な場合は不要です。小規模であれば、OLTPデータベースに集計テーブルを追加する「簡易版DWH」で十分な場合もあります。
Q6: ETL処理は自分で作る必要がありますか？
いいえ、ETLツールを使うことを推奨します。主要なETLツールとしては、Apache Airflow（オープンソース）、AWS Glue、Google Cloud Dataflow、Azure Data Factoryなどがあります。これらを使えば、データの抽出・変換・ロードが簡単で、スケジューリング機能も標準装備されています。ただし、シンプルな要件であればSQLやPythonスクリプトで十分な場合もあります。
学習メモ
データベース設計・データモデリング - Step 14