⭐ STEP 34: スタースキーマとスノーフレークスキーマ

データモデルの2大パターン！最適な設計を選ぼう

📋 このステップで学ぶこと

スタースキーマ（星型）の概念と構造
スノーフレークスキーマ（雪型）の概念と構造
2つのスキーマの違いと選択基準
非正規化の考え方とメリット
実務でのモデル設計例
Power BIでの実装方法

ゴール：Power BIに最適なスタースキーマを設計・実装できるようになる

⭐ 1. スタースキーマ（Star Schema）

スタースキーマとは

スタースキーマは、データウェアハウスやBIツールで最もよく使われるデータモデルの設計パターンです。中心に1つのファクトテーブルを置き、その周りに複数のディメンションテーブルを配置します。上から見ると星（★）の形に見えることから、この名前が付けられました。

✅ スタースキーマの基本構造

要素	役割	具体例
ファクトテーブル（中心）	ビジネスの「事実」を記録する	売上データ、注文データ、アクセスログ
ディメンションテーブル（周囲）	分析の「切り口」を提供する	商品マスタ、顧客マスタ、日付マスタ
リレーションシップ	テーブル間の関係を定義する	ファクト → 各ディメンション（1:*）

💡 スタースキーマの図解

              商品マスタ
                  │
                  │ (1:*)
                  │
  顧客マスタ ──── 売上（ファクト） ──── 地域マスタ
                  │
                  │ (1:*)
                  │
              日付マスタ

【特徴】
・ファクトテーブルが中心にある
・各ディメンションがファクトと直接つながる
・ディメンション同士は直接つながらない
・上から見ると「星」の形に見える

※モバイルでは横スクロールできます

スタースキーマの具体例

ECサイトの売上分析を例に、スタースキーマの構造を見てみましょう。ファクトテーブルには取引データ、ディメンションテーブルには各マスタデータが入ります。

📊 ファクトテーブル：売上明細

売上ID	日付ID	商品ID	顧客ID	数量	金額
1	20240115	P001	C001	2	5,000
2	20240115	P002	C002	1	3,000
3	20240116	P001	C001	1	2,500

ポイント：IDと数値（メジャー）のみを持つシンプルな構造

📊 ディメンションテーブル：商品マスタ（非正規化）

商品ID	商品名	カテゴリ	ブランド	単価
P001	iPhone 15	スマートフォン	Apple	2,500
P002	AirPods Pro	イヤホン	Apple	3,000
P003	Galaxy S24	スマートフォン	Samsung	2,200

重要：カテゴリとブランドも商品マスタ内に含まれています（非正規化）。これがスタースキーマの特徴です。

🔧 ディメンションテーブル：顧客マスタ（非正規化）

顧客ID	顧客名	都道府県	地域	会員ランク
C001	山田太郎	東京都	関東	ゴールド
C002	佐藤花子	大阪府	関西	シルバー

ポイント：都道府県→地域の階層も、顧客マスタ内に含めています

✅ スタースキーマのメリット

メリット	説明	ビジネスへの効果
シンプル	構造が理解しやすく、設計が容易	開発・保守コストの削減
高速	結合（JOIN）が少なくクエリが速い	レポートの高速表示
使いやすい	ビジュアル作成時に直感的に操作できる	分析の生産性向上
Power BI最適化	Power BIのエンジンがこの構造に最適化されている	メモリ効率・パフォーマンス向上

⚠️ スタースキーマのデメリット

デメリット	説明	対処法
データ重複	同じカテゴリ名が複数行に存在	圧縮技術でカバー
更新コスト	カテゴリ名変更時に多くの行を更新	ディメンションは静的なので影響小
ストレージ増	非正規化でデータサイズが増加	メモリ圧縮で軽減

❄️ 2. スノーフレークスキーマ（Snowflake Schema）

スノーフレークスキーマとは

スノーフレークスキーマは、スタースキーマをさらに正規化した構造です。ディメンションテーブルが複数のテーブルに分割され、階層構造を持ちます。雪の結晶（❄️）のように枝分かれすることから、この名前が付けられました。

📊 スノーフレークスキーマの特徴

特徴	説明
ディメンションが分割される	商品マスタ → カテゴリマスタ → 大カテゴリマスタのように階層化
ディメンション間にもリレーション	ディメンション同士がリレーションシップで結ばれる
完全に正規化されている	データの重複がない、データベース理論に忠実な設計

💡 スノーフレークスキーマの図解

    ブランドマスタ
          │
          │ (1:*)
          │
    カテゴリマスタ ──── 商品マスタ
                            │
                            │ (1:*)
                            │
                        売上（ファクト）
                            │
                            │ (1:*)
                            │
                      顧客マスタ ──── 地域マスタ
                                          │
                                          │
                                     都道府県マスタ

【特徴】
・ディメンションがさらに分割されている
・「商品→カテゴリ→ブランド」のように枝分かれ
・雪の結晶のような形になる

※モバイルでは横スクロールできます

スノーフレークスキーマの具体例

スタースキーマと同じECサイトの例で、スノーフレークスキーマの構造を見てみましょう。商品マスタからカテゴリとブランドが別テーブルに分離されています。

📊 商品マスタ（正規化版）

商品ID	商品名	カテゴリID	ブランドID	単価
P001	iPhone 15	CAT01	BR01	2,500
P002	AirPods Pro	CAT02	BR01	3,000

注意：カテゴリ名やブランド名ではなく、IDだけを持っています（正規化）

✅ カテゴリマスタ（分離されたテーブル）

カテゴリID	カテゴリ名	大カテゴリ
CAT01	スマートフォン	電子機器
CAT02	イヤホン	オーディオ

📊 ブランドマスタ（分離されたテーブル）

ブランドID	ブランド名	国
BR01	Apple	アメリカ
BR02	Samsung	韓国

✅ スノーフレークスキーマのメリット

メリット	説明	具体例
データ重複なし	同じデータが複数箇所に存在しない	「Apple」は1行だけ
ストレージ効率	データサイズが小さくなる	大規模データで有効
更新が楽	1箇所の変更で済む	ブランド名変更が1行で完了
データ整合性	データの一貫性が保たれる	矛盾のないデータ

⚠️ スノーフレークスキーマのデメリット

デメリット	説明	影響
複雑	テーブル数が多く理解が難しい	開発・保守コスト増
遅い	結合（JOIN）が多くクエリが遅くなる	レポート表示が遅延
使いにくい	ビジュアル作成時に複数テーブルをまたぐ	分析の生産性低下
Power BIでは非推奨	Power BIのエンジンに最適化されていない	パフォーマンス低下

⚖️ 3. スタースキーマ vs スノーフレークスキーマ

詳細比較

2つのスキーマの違いを一覧で比較してみましょう。Power BIでは、ほとんどの場合スタースキーマが推奨されます。

📊 スタースキーマ vs スノーフレークスキーマ比較表

項目	スタースキーマ ⭐	スノーフレークスキーマ ❄️
構造	シンプル	複雑
正規化	部分的（非正規化）	完全
テーブル数	少ない	多い
結合数	少ない（速い）	多い（遅い）
データ重複	あり	なし
ストレージ	大きい	小さい
クエリ速度	◎ 速い	△ 遅い
メンテナンス	△ やや面倒	◎ 楽
理解しやすさ	◎ 簡単	△ 難しい
Power BI推奨	◎ 推奨	× 非推奨

✅ Power BIではスタースキーマを選ぶ理由

理由	詳細説明
パフォーマンスが圧倒的	結合が少ないため、クエリが高速。大量データでも快適に分析できる
ビジュアル作成が簡単	ディメンションの属性が1つのテーブルにまとまっているため、直感的に操作できる
DAXメジャーが書きやすい	テーブル間の関係がシンプルなため、計算式が簡潔になる
メモリ最適化	Power BIのエンジンがスタースキーマ向けに最適化されている
ユーザーに理解しやすい	エンドユーザーがレポートを作成する際も、構造が把握しやすい

💡 スノーフレークを選ぶケース（稀）

状況	説明
ストレージが極端に制限	クラウドのストレージコストが非常に高い場合
データ更新頻度が非常に高い	マスタデータが頻繁に変更される場合
既存システムがスノーフレーク	レガシーシステムとの互換性が必要な場合
組織の要件	完全な正規化がルールとして定められている場合

結論：Power BIでは99%のケースでスタースキーマを選択してください

🏗️ 4. 実務でのモデル設計例

例1：小売業の売上分析

実際のビジネスでスタースキーマをどのように設計するか、小売業の売上分析を例に見てみましょう。

📊 小売業スタースキーマ設計

テーブル	主な列	ポイント
売上（ファクト）	売上ID、日付ID、商品ID、店舗ID、顧客ID、売上金額、数量、利益	IDと数値（メジャー）のみ
商品マスタ	商品ID、商品名、カテゴリ、サブカテゴリ、ブランド、仕入先、標準価格	カテゴリ等も含む（非正規化）
店舗マスタ	店舗ID、店舗名、都道府県、地域、店舗タイプ、開店日	地域階層も含む（非正規化）
顧客マスタ	顧客ID、顧客名、年齢層、性別、会員ランク、登録日	年齢層・ランクも含む（非正規化）
日付マスタ	日付ID、日付、年、四半期、月、週、曜日	時系列分析に必須

✅ 設計のポイント

ポイント	説明
すべて非正規化	カテゴリ、地域、ランクなどの階層情報は、ディメンション内に含める
ファクトはシンプルに	ファクトテーブルにはIDと数値のみ。商品名などは入れない
日付テーブルは必須	時系列分析やYTD計算に必要。必ず作成する
結合は最小限に	ファクト→ディメンションの1:*結合のみにする

💻 5. Power BIでの実装

スタースキーマの実装手順

Power BIでスタースキーマを実装する手順を、ステップバイステップで説明します。

📊 実装手順

手順	作業内容	詳細
1	データソースの準備	ファクトテーブルとディメンションテーブルを用意
2	Power Queryで前処理	不要列削除、データ型設定、ディメンションの非正規化（必要に応じて結合）
3	Power BIへの読み込み	すべてのテーブルを読み込み→「閉じて適用」
4	モデルビューでリレーションシップ作成	ファクト→各ディメンション、すべて1対多、単一方向フィルター
5	レイアウト整理	ファクトを中心に配置、ディメンションを周囲に配置（星型に見えるように）
6	検証	リレーションシップが正しいか、カーディナリティが1:*か、フィルターが動作するか確認

スノーフレークをスターに変換する方法

既存のデータがスノーフレーク構造になっている場合、Power Queryでスタースキーマに変換できます。「クエリのマージ」機能を使って、複数のテーブルを1つに統合します。

✅ スノーフレーク → スター変換手順

手順	操作	補足
1	Power Queryエディタで商品マスタを開く	統合先のテーブルを選択
2	ホーム→「クエリのマージ」をクリック	テーブル結合のダイアログが開く
3	商品マスタとカテゴリマスタを結合	商品マスタ.カテゴリID = カテゴリマスタ.カテゴリID
4	カテゴリマスタの列を展開	カテゴリ名を商品マスタに追加
5	不要な列（カテゴリID）を削除	IDは不要になる
6	ブランドマスタも同様に結合	必要に応じて繰り返す
7	「閉じて適用」	統合された商品マスタが完成

日付テーブルの作成

スタースキーマにおいて、日付テーブルは非常に重要です。時系列分析やYTD（年初来累計）などの計算に必要です。DAXまたはMコードで作成できます。

💡 方法1：DAXで日付テーブルを作成（入力するコード）

日付マスタ = 
ADDCOLUMNS(
    CALENDAR(DATE(2020,1,1), DATE(2030,12,31)),
    "年", YEAR([Date]),
    "月", MONTH([Date]),
    "月名", FORMAT([Date], "MMMM"),
    "四半期", "Q" & QUARTER([Date]),
    "曜日番号", WEEKDAY([Date]),
    "曜日", FORMAT([Date], "dddd"),
    "年月", FORMAT([Date], "YYYY-MM")
)

※モバイルでは横スクロールできます

📊 DAXで日付テーブルを作成する手順

手順	操作	補足
1	ホームタブ→「新しいテーブル」をクリック	DAX式入力欄が表示される
2	上記のDAX式を入力	日付範囲は必要に応じて変更
3	Enterキーで確定	日付テーブルが作成される
4	テーブルを右クリック→「日付テーブルとしてマーク」	時系列インテリジェンス関数が使えるようになる
5	日付列を選択して確定	[Date]列を選択

📊 方法2：Power Query（Mコード）で日付テーブルを作成（入力するコード）

let
    開始日 = #date(2020, 1, 1),
    終了日 = #date(2030, 12, 31),
    日数 = Duration.Days(終了日 - 開始日) + 1,
    日付リスト = List.Dates(開始日, 日数, #duration(1,0,0,0)),
    テーブル化 = Table.FromList(日付リスト, 
        Splitter.SplitByNothing(), {"日付"}),
    型変更 = Table.TransformColumnTypes(テーブル化, 
        {{"日付", type date}}),
    年追加 = Table.AddColumn(型変更, "年", 
        each Date.Year([日付]), Int64.Type),
    月追加 = Table.AddColumn(年追加, "月", 
        each Date.Month([日付]), Int64.Type),
    四半期追加 = Table.AddColumn(月追加, "四半期", 
        each "Q" & Number.ToText(Date.QuarterOfYear([日付])), type text),
    曜日追加 = Table.AddColumn(四半期追加, "曜日", 
        each Date.DayOfWeekName([日付]), type text)
in
    曜日追加

※モバイルでは横スクロールできます

✅ Power Queryで日付テーブルを作成する手順

手順	操作	補足
1	ホームタブ→「データを取得」→「空のクエリ」	新しいクエリが作成される
2	ホームタブ→「詳細エディタ」を開く	Mコードの編集画面が開く
3	上記のMコードを貼り付け	日付範囲は必要に応じて変更
4	「OK」→「閉じて適用」	日付テーブルがPower BIに読み込まれる
5	テーブルを右クリック→「日付テーブルとしてマーク」	日付列を選択して確定

📝 STEP 34 のまとめ

✅ このステップで学んだこと

スタースキーマ：⭐星型、シンプル、高速、Power BI推奨
スノーフレークスキーマ：❄️雪型、複雑、正規化、Power BIでは非推奨
非正規化：ディメンション内にカテゴリやブランドなどの階層を含める
設計のポイント：ファクトを中心に、周りにディメンションを配置
日付テーブル：時系列分析に必須、DAXまたはMコードで作成
変換方法：Power Queryの「クエリのマージ」でスノーフレーク→スター

💡 最重要ポイント

Power BIではスタースキーマ一択です！

シンプルで速くて使いやすい構造です。ディメンションは非正規化して、カテゴリもブランドも全部1つのテーブルに入れましょう。

「正規化すべき」という固定観念を捨てて、パフォーマンス優先で設計することが、Power BIのベストプラクティスです！

📝 実践演習

演習 1 基礎

簡単なスタースキーマを設計してください。1つのファクトテーブルと3つのディメンションテーブルで構成してください。ECサイトまたは小売業を想定してください。

【スタースキーマ設計例】

ファクトテーブル：注文

列名	説明
注文ID（主キー）	注文を一意に識別
日付ID（外部キー）	日付マスタへの参照
商品ID（外部キー）	商品マスタへの参照
顧客ID（外部キー）	顧客マスタへの参照
数量（メジャー）	購入数量
金額（メジャー）	売上金額

リレーションシップ：

商品マスタ(1) → (*)注文
顧客マスタ(1) → (*)注文
日付マスタ(1) → (*)注文

演習 2 応用

スノーフレークスキーマをスタースキーマに変換してください。商品マスタ→カテゴリマスタ→大カテゴリマスタの3層構造を、1つの商品マスタに統合する手順を説明してください。

【変換手順】

元の構造（スノーフレーク）：

商品マスタ：商品ID、商品名、カテゴリID
カテゴリマスタ：カテゴリID、カテゴリ名、大カテゴリID
大カテゴリマスタ：大カテゴリID、大カテゴリ名

Power Queryでの変換手順：

1	商品マスタを開く
2	「クエリのマージ」→カテゴリマスタと結合
3	カテゴリ名と大カテゴリIDを展開
4	再度「クエリのマージ」→大カテゴリマスタと結合
5	大カテゴリ名を展開
6	不要な列（カテゴリID、大カテゴリID）を削除

結果（スター）：

商品マスタ：商品ID、商品名、カテゴリ名、大カテゴリ名

演習 3 発展

DAXまたはMコードを使って、2020年から2025年までの完全な日付テーブルを作成してください。年、月、四半期、曜日の列を含めてください。

【DAXで作成する場合】

1	ホームタブ→「新しいテーブル」
2	DAX式を入力（本文のコード参照）
3	テーブルを右クリック→「日付テーブルとしてマーク」
4	日付列（Date）を選択して確定

入力するDAX式：

日付マスタ = 
ADDCOLUMNS(
    CALENDAR(DATE(2020,1,1), DATE(2025,12,31)),
    "年", YEAR([Date]),
    "月", MONTH([Date]),
    "四半期", "Q" & QUARTER([Date]),
    "曜日", FORMAT([Date], "dddd")
)

❓ よくある質問

Q1: 本当にスノーフレークは使わないのですか？

Power BIではほとんど使いません。

従来のデータウェアハウスではスノーフレークが使われることもありますが、Power BIのようなインメモリツールでは、スタースキーマの方が圧倒的にパフォーマンスが良いです。

非正規化によるストレージ増は、Power BIのメモリ圧縮技術でカバーされます。

Q2: ディメンションを非正規化すると、更新が大変では？

ディメンションは比較的静的なので問題ありません。

商品カテゴリやブランドは頻繁に変わらないため、非正規化のデメリットは小さいです。

むしろ、クエリパフォーマンスの向上という大きなメリットがあります。

Q3: 日付テーブルは必須ですか？

時系列分析をするなら必須です。

日付テーブルを「日付テーブルとしてマーク」すると、以下のことが可能になります：

年初来累計（YTD）の計算
前年同期（PY）との比較
会計年度やカスタムカレンダーの実装

Q4: ファクトテーブルは1つだけですか？

複数のファクトテーブルを持つこともできます。

例えば、「売上」と「在庫」は別々のファクトテーブルになります。

ただし、初心者はまず1つのファクトテーブルから始めることをおすすめします。複数ファクトの場合も、それぞれがスタースキーマの中心になります。

Q5: 「非正規化」はデータベース設計の原則に反しませんか？

BIツールでは「分析用設計」が優先されます。

トランザクション処理用のデータベース（OLTP）では正規化が重要ですが、分析用のデータウェアハウス（OLAP）では非正規化が一般的です。

Power BIは分析ツールなので、パフォーマンスと使いやすさを優先した設計が正解です。

📝

学習メモ

BIツール入門 - Step 34

📋 過去のメモ一覧 ▼