Hakky Handbook
業界・業務から探す▼
導入目的・課題から探す
データ・AIについて学ぶ▼
News
Hakkyについて▼
ウェビナーコラム
◆トップ【データ基盤】
データレイクとはデータメッシュとはデータ基盤構築の流れデータ分析基盤関連おすすめの本メタデータ管理とはデータマネジメントとはデータの品質担保とはデータウェアハウスとはData FabricとはData Lake・Data MeshとはETLとは3NFとはデータ分析基盤の3層構造について
基本概念運用・構築
データファブリック
データの保守運用
AI

執筆者：Hakky AI

DWHテーブル設計｜スタースキーマで売上分析を高速化する方法

tip
記事のポイント
  • スタースキーマはデータ分析基盤。ファクトとディメンションテーブルで構成される。
  • ファクトテーブルは売上実績を記録。メジャーと外部キーで分析を支援。
  • ディメンションテーブルは分析軸。属性設計で多角的な分析を可能にする。

はじめに

データウェアハウスにおけるテーブル設計は、効率的なデータ分析と意思決定を支える基盤です。

本記事では、データウェアハウスの設計で重要な役割を果たすスタースキーマに焦点を当て、その構成要素であるファクトテーブルとディメンションテーブルの設計方法について解説します。

スタースキーマの基礎概念から具体的な設計例、注意点までを網羅し、読者がデータウェアハウスのテーブル設計を実践できるようになることを目指します。データ分析基盤の構築、改善に役立つ情報を提供します。

データウェアハウスとスタースキーマの基礎

データウェアハウスとスタースキーマは、データ分析を効率化するための基盤技術です。ここでは、データウェアハウスの概念、スタースキーマの構造、そしてその利点について解説します。

データウェアハウスとは

データウェアハウス（DWH）は、ビジネスにおける意思決定を支援するために、様々なデータソースから集約されたデータを一元的に管理するシステムです。従来のデータベースがトランザクション処理を目的とするのに対し、DWHは分析処理に特化しています。DWHは、過去のデータを長期にわたり保管し、トレンド分析や予測モデルの構築に利用されます。

例えば、ある小売業者がDWHを導入したとします。POSシステム、顧客管理システム（CRM）、オンラインストアなど、複数のシステムからデータを収集し、DWHに統合します。これにより、売上データ、顧客データ、製品データなどを組み合わせて分析し、売れ筋商品の特定、顧客セグメントの分析、キャンペーンの効果測定などを行うことができます。

DWHの設計では、データの整合性と品質を保つために、ETL（抽出、変換、ロード）プロセスが重要になります。ETLプロセスを通じて、データのクレンジング、変換、統合を行い、分析に適した形式でDWHに格納します。

DWHは、ビジネスインテリジェンス（BI）ツールと連携することで、データの可視化やレポート作成を支援し、経営層やビジネスアナリストがデータに基づいた意思決定を行うことを可能にします。DWHの導入により、企業はデータドリブンな文化を醸成し、競争優位性を確立することができます。

スタースキーマの構造

スタースキーマは、データウェアハウスで広く利用されるデータモデリング手法の一つで、中心となるファクトテーブルと、それを取り囲むディメンションテーブルで構成されます。ファクトテーブルは、売上や注文などのビジネスイベントに関する数値データ（メジャー）と、ディメンションテーブルへの参照キーを持ちます。ディメンションテーブルは、ファクトテーブルのコンテキストを提供する属性情報（例：顧客、製品、地域、時間）を持ちます。

例えば、ある企業の売上分析を目的としたスタースキーマを考えてみましょう。ファクトテーブルは「売上」テーブルとし、各売上トランザクションの売上金額、数量、売上日、顧客ID、製品ID、地域IDなどの情報を含みます。

ディメンションテーブルとしては、「顧客」テーブル（顧客ID、氏名、住所など）、「製品」テーブル（製品ID、製品名、カテゴリなど）、「地域」テーブル（地域ID、地域名、国など）、「時間」テーブル（日付、年、月など）を用意します。これらのテーブルを組み合わせることで、例えば「特定の地域における特定の製品の月別売上」といった複雑な分析クエリを効率的に実行できます。

スタースキーマのシンプルな構造は、クエリのパフォーマンスを向上させ、データ分析を容易にします。また、スタースキーマは、BIツールとの親和性が高く、データの可視化やレポート作成を容易にします。スタースキーマは、データウェアハウスの設計において、最も基本的なパターンの一つであり、多くの企業で採用されています。

スタースキーマの利点

スタースキーマは、データウェアハウスの設計において、クエリのパフォーマンス向上とデータ分析の効率化という二つの大きな利点をもたらします。スタースキーマのシンプルな構造は、複雑な結合処理を減らし、クエリの実行時間を短縮します。ファクトテーブルとディメンションテーブル間の関係が明確であるため、データベースエンジンは効率的なクエリ実行計画を立てることができます。

例えば、ある企業の売上データを分析する場合、スタースキーマを使用することで、「特定の製品カテゴリの売上合計」や「特定の地域の顧客の売上平均」といった集計クエリを高速に実行できます。

また、スタースキーマは、データ分析の効率化にも貢献します。ディメンションテーブルは、分析に必要なコンテキスト情報を提供し、データの多角的な分析を可能にします。例えば、「顧客」ディメンションテーブルには、顧客の属性（年齢、性別、居住地など）が含まれており、これらの属性に基づいて売上データを分析することで、顧客セグメントごとの売上傾向を把握できます。

さらに、スタースキーマは、BIツールとの連携が容易であり、データの可視化やレポート作成を効率化します。BIツールは、スタースキーマの構造を理解し、自動的にクエリを生成したり、ダッシュボードを作成したりすることができます。スタースキーマは、データウェアハウスの設計において、最も基本的なパターンの一つであり、多くの企業で採用されています。

  • データ基盤
DWHスキーマ徹底比較｜データ分析を成功させる設計とは？ | Hakky Handbook
データウェアハウスのスキーマ選定は分析効率を左右します。スタースキーマとスノーフレークスキーマを比較し、ビジネスインテリジェンスへの貢献を解説します。最適なスキーマを選択することで、データ分析が効率化され、迅速な意思決定を支援します。さあ、データ分析基盤を強化しましょう。

ファクトテーブルの設計

ファクトテーブルは、データウェアハウスの中心的な役割を担い、ビジネスの成果を測定するための重要な要素です。適切な設計により、効率的なデータ分析と意思決定を支援します。

ファクトテーブルの構成要素

ファクトテーブルは、ビジネスプロセスの結果を記録するテーブルであり、主にメジャー（数値データ）とディメンションテーブルへの外部キーで構成されます。メジャーは、売上金額、数量、利益など、分析対象となる数値データです。

外部キーは、ディメンションテーブルと連携し、ビジネスのコンテキストを提供します。例えば、売上ファクトテーブルでは、売上金額がメジャー、顧客ID、製品ID、店舗IDなどがディメンションキーとなります。

主キーの設定は、ファクトテーブルの整合性を保つために重要です。一般的には、複数の外部キーを組み合わせた複合キーが使用されます。これにより、一意なレコードを識別し、データの重複を防ぎます。

また、主キーには、ビジネスプロセスが発生した日時を示すタイムスタンプを含めることもあります。これにより、時間経過に伴うデータの変化を追跡し、時系列分析を可能にします。

適切な主キーの設定は、データウェアハウスのパフォーマンスと分析の正確性を向上させるために不可欠です。ファクトテーブルの設計においては、メジャーとディメンションキーのバランスを考慮し、ビジネス要件に合致した最適な構成を検討することが重要です。

粒度の決定

ファクトテーブルの粒度は、データウェアハウスで分析可能な最小の詳細レベルを指し、設計において重要な決定事項です。粒度を決定する際には、ビジネス要件と分析ニーズを慎重に考慮する必要があります。

例えば、売上分析を行う場合、日次、週次、月次など、どのレベルでデータを集計するかを決定します。粒度が細かすぎると、データ量が膨大になり、パフォーマンスに影響を与える可能性があります。一方、粒度が粗すぎると、詳細な分析ができなくなる可能性があります。適切な粒度を選択することで、データ量と分析能力のバランスを取ることができます。

粒度の決定には、以下の要素を考慮することが重要です。

  • まず、ビジネス上の意思決定に必要な詳細レベルを明確にします。
  • 次に、データの可用性と品質を評価します。
  • さらに、ストレージ容量とパフォーマンス要件を考慮します。

例えば、商品ページの分析を行う場合、商品ID、ページID、時刻などのデータがファクトテーブルに格納されます。粒度の選択は、データウェアハウスの柔軟性と拡張性にも影響を与えます。将来的な分析ニーズの変化に対応できるように、適切な粒度を選択することが重要です。

パフォーマンス最適化

ファクトテーブルのパフォーマンス最適化は、データウェアハウスの効率的な運用に不可欠です。データ量が増加するにつれて、クエリの実行速度が低下する可能性があるため、適切な対策を講じる必要があります。

パフォーマンスを最適化するためのテクニックとしては、インデックスの活用、パーティショニング、集計テーブルの作成などが挙げられます。インデックスは、特定のカラムに対する検索速度を向上させるために使用されます。ファクトテーブルでは、ディメンションキーや日付カラムなど、クエリで頻繁に使用されるカラムにインデックスを作成することが効果的です。

パーティショニングは、テーブルをより小さなセグメントに分割することで、クエリのパフォーマンスを向上させます。例えば、日付に基づいてテーブルを分割することで、特定の期間のデータを効率的に検索できます。

集計テーブルは、事前に計算された集計データを格納することで、クエリの実行時間を短縮します。例えば、日次の売上データを集計した月次の売上テーブルを作成することで、月次レポートの生成を高速化できます。

これらのテクニックを組み合わせることで、ファクトテーブルのパフォーマンスを大幅に向上させることができます。また、定期的なメンテナンスと監視を行い、パフォーマンスのボトルネックを特定し、適切な対策を講じることが重要です。

  • データ基盤
DWH構築｜データ分析を加速するファクトテーブル設計・活用術 | Hakky Handbook
データ分析基盤の中核、DWHのファクトテーブルに焦点を当て、設計、構築、活用を解説します。ZOZO社のDWH活用事例を基に、データ分析の効率と精度向上が期待できます。本記事でDWHを最大限に活用し、ビジネスの意思決定を支援しましょう。

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

ディメンションテーブルは、データウェアハウスにおける分析の軸となる情報を保持し、ファクトテーブルと組み合わせて使用​​されます。適切なディメンションテーブルの設計は、効率的なデータ分析と意思決定を支援するために不可欠です。

ディメンションテーブルの属性

ディメンションテーブルの属性を設計する上で、分析に必要な属性を洗い出すことは非常に重要です。属性は、データの粒度と階層構造を考慮して選択する必要があります。

例えば、顧客ディメンションテーブルでは、顧客ID、氏名、住所、電話番号、メールアドレスなどの属性が考えられます。これらの属性は、顧客のセグメント分析や購買行動の分析に利用できます。

また、時間ディメンションテーブルでは、日付、四半期、年などの属性を持つことが一般的です。これらの属性を使用することで、売上データの時間的な傾向を分析できます。

属性の粒度を適切に設定することで、詳細な分析から集約された分析まで、柔軟に対応することが可能です。例えば、地域ディメンションテーブルにおいて、国、都道府県、市区町村といった階層構造を持たせることで、地域ごとの売上分析を多角的に行うことができます。

データ品質を保つために、NOT NULL制約やUNIQUE制約を適切に設定することも重要です。例えば、customer_keyに対してUNIQUE制約を設定することで、顧客IDの一意性を保証し、データの整合性を維持できます。

サロゲートキーの利用

サロゲートキーは、データウェアハウス内でディメンションテーブルの各レコードを一意に識別するために使用される人工的なキーです。

サロゲートキーの主なメリットは、業務システムで使用されるナチュラルキー（自然キー）の変更に影響を受けないことです。例えば、顧客IDが業務システムの変更によって変わった場合でも、サロゲートキーを使用していれば、データウェアハウス側のデータ整合性を維持できます。

また、サロゲートキーは通常、整数値やハッシュ値で構成されるため、結合処理のパフォーマンス向上にも寄与します。

一方、サロゲートキーのデメリットとしては、キーの意味がビジネス的に理解しにくい点が挙げられます。ナチュラルキーは、業務上の意味を持つため、データの理解やデバッグが容易ですが、サロゲートキーはそうではありません。

したがって、サロゲートキーとナチュラルキーは、適切に使い分ける必要があります。一般的には、データウェアハウス内での一意性保証やパフォーマンスを重視する場合はサロゲートキーを使用し、業務システムとの連携やデータの可読性を重視する場合はナチュラルキーを使用します。

例えば、顧客情報ディメンションテーブルでは、customer_idをナチュラルキー、customer_keyをサロゲートキーとして併用することが推奨されます。サロゲートキーは、データウェアハウスの整合性を保つ上で重要な役割を果たします。

スノーフレークスキーマとの比較

スノーフレークスキーマは、スタースキーマの正規化された拡張であり、ディメンションテーブルがさらに複数のテーブルに分割される点が特徴です。

スタースキーマでは、ディメンションテーブルは非正規化されているため、冗長性を持つ場合がありますが、スノーフレークスキーマでは、冗長性が排除され、データの整合性が高まります。

例えば、製品ディメンションテーブルにおいて、製品カテゴリとサプライヤー情報を別のテーブルに分割することで、スノーフレークスキーマを構成できます。

スノーフレークスキーマの主なメリットは、データの更新が容易になることと、ストレージ容量を節約できることです。一方、デメリットとしては、テーブル結合が増えるため、クエリのパフォーマンスが低下する可能性があります。

スタースキーマは、クエリのパフォーマンスが高く、スキーマの理解が容易であるため、多くの場合に適しています。スノーフレークスキーマは、ディメンションテーブルの属性が非常に多く、データの整合性を厳密に管理する必要がある場合に適しています。

例えば、大規模なデータウェアハウスにおいて、製品情報の変更が頻繁に行われる場合や、サプライヤー情報の管理が重要な場合には、スノーフレークスキーマが有効です。スキーマの選択は、データの特性と要件に基づいて慎重に行う必要があります。

  • データ基盤
【DB設計】設計書のサンプル｜SE向け実践ガイド | Hakky Handbook
データベース設計書はシステム開発の基盤です。データ構造を明確にし、効率的な運用を支えます。例えば、適切な設計で開発の手戻りを減らし、パフォーマンスを向上できます。この記事では、基本概念から実践的な活用方法、作成ステップまでを解説します。設計に役立つツールやサンプルも紹介しますので、ぜひお読みください。

▶ Hakkyのデータ基盤構築支援とは | 詳細はこちら

スタースキーマ設計の実践例：売上分析

ここでは、スタースキーマを用いた売上分析の具体的な設計例を紹介します。ファクトテーブルとディメンションテーブルをどのように関連付けるかを解説します。

売上ファクトテーブルの設計

売上ファクトテーブルの設計では、売上実績を測るためのメジャーと、関連するディメンションへの外部キーを定義します。メジャーとしては、売上金額、数量、利益などが考えられます。外部キーとしては、時間ディメンション、商品ディメンション、顧客ディメンションなどがあります。例えば、以下のような構成が考えられます。

CREATE TABLE sales_fact (
    sales_id INT PRIMARY KEY,
    date_key INT REFERENCES date_dim(date_key),
    product_key INT REFERENCES product_dim(product_key),
    customer_key INT REFERENCES customer_dim(customer_key),
    store_key INT REFERENCES store_dim(store_key),
    quantity INT,
    unit_price DECIMAL(10, 2),
    sales_amount DECIMAL(20, 2)
);

sales_factテーブルは、date_key、product_key、customer_key、store_keyを外部キーとして持ち、それぞれdate_dim、product_dim、customer_dim、store_dimテーブルを参照します。quantity、unit_price、sales_amountは売上に関する数値データです。

この設計により、売上実績を様々な角度から分析することが可能になります。例えば、特定の日付、特定の商品、特定の顧客、特定の店舗における売上を分析できます。

また、これらのディメンションを組み合わせることで、より詳細な分析も可能です。例えば、特定の日付に特定の顧客が購入した特定の商品に関する売上を分析できます。

このように、ファクトテーブルとディメンションテーブルを適切に設計することで、データウェアハウスの分析能力を最大限に引き出すことができます。

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

ディメンションテーブルは、ファクトテーブルのコンテキストを提供します。時間ディメンション、商品ディメンション、顧客ディメンションは、それぞれ売上分析において重要な役割を果たします。

時間ディメンションは、日付、曜日、四半期、年などの属性を持ちます。商品ディメンションは、商品名、カテゴリ、ブランドなどの属性を持ちます。顧客ディメンションは、顧客ID、氏名、住所、年齢、性別などの属性を持ちます。これらの属性は、売上分析における様々な切り口を提供します。例えば、以下のようなテーブル設計が考えられます。

CREATE TABLE date_dim (
    date_key INT PRIMARY KEY,
    date DATE,
    year INT,
    quarter INT,
    month INT,
    day INT,
    day_of_week INT
);

CREATE TABLE product_dim (
    product_key INT PRIMARY KEY,
    product_name VARCHAR(255),
    category VARCHAR(255),
    brand VARCHAR(255),
    price DECIMAL(10, 2)
);

CREATE TABLE customer_dim (
    customer_key INT PRIMARY KEY,
    customer_id INT,
    name VARCHAR(255),
    address VARCHAR(255),
    age INT,
    gender CHAR(1)
);

これらのテーブルは、それぞれ日付、商品、顧客に関する属性を持ちます。これらの属性を用いることで、売上データを様々な角度から分析できます。

例えば、特定の商品カテゴリの売上推移を分析したり、特定の年齢層の顧客の購買行動を分析したりできます。

また、これらのディメンションテーブルには、階層構造を持たせることも可能です。例えば、商品ディメンションには、カテゴリ、サブカテゴリ、商品名という階層構造を持たせることができます。これにより、より詳細な分析が可能になります。

クエリ例

設計したスタースキーマを用いて、売上分析を行うためのクエリ例をいくつか紹介します。これらのクエリは、集計、ドリルダウン、スライス、ダイスといったOLAP操作を具現化するものです。例えば、年ごとの売上を集計するクエリは以下のようになります。

SELECT
    d.year,
    SUM(f.sales_amount)
FROM
    sales_fact f
JOIN
    date_dim d ON f.date_key = d.date_key
GROUP BY
    d.year
ORDER BY
    d.year;

このクエリは、sales_factテーブルとdate_dimテーブルを結合し、年ごとの売上金額を集計します。

また、特定の商品カテゴリの売上推移を分析するクエリは以下のようになります。

SELECT
    d.year,
    p.category,
    SUM(f.sales_amount)
FROM
    sales_fact f
JOIN
    date_dim d ON f.date_key = d.date_key
JOIN
    product_dim p ON f.product_key = p.product_key
WHERE
    p.category = 'Electronics'
GROUP BY
    d.year,
    p.category
ORDER BY
    d.year;

このクエリは、sales_factテーブル、date_dimテーブル、product_dimテーブルを結合し、特定の商品カテゴリ（ここでは'Electronics'）の年ごとの売上金額を集計します。

これらのクエリ例は、スタースキーマを用いたデータ分析のほんの一例です。スタースキーマを適切に設計し、SQLを駆使することで、様々な角度からデータを分析し、ビジネス上の意思決定に役立てることができます。

スタースキーマ設計の注意点とトラブルシューティング

スタースキーマの設計では、パフォーマンス問題やデータ量の増加など、様々な課題が発生します。これらの問題に適切に対応し、データウェアハウスの効率的な運用を実現するための注意点とトラブルシューティングについて解説します。

パフォーマンス問題の特定

クエリの実行速度が遅い場合、まずは原因を特定する必要があります。データウェアハウスにおけるパフォーマンスボトルネックの診断方法について解説します。

クエリの実行速度低下は、不適切なテーブル設計、インデックスの欠如、またはハードウェアリソースの制約など、様々な要因によって引き起こされます。例えば、大規模データウェアハウスで顧客シナリオ分析を実行する際に、100万レコードの顧客データと複数のディメンションテーブルを統合するクエリが1分以上かかる場合、非正規化によるデータの冗長化が原因である可能性があります。

このような場合、クエリの実行計画を確認し、どのテーブルへのアクセスに時間がかかっているかを特定します。また、リソース競合もパフォーマンス低下の要因となり得ます。シャーディング技術がサポートされていない環境では、クエリが並列処理されず、リソースの競合が発生しやすくなります。

大規模なデータセットを扱う場合、ユーザー数の増加に伴いリソース競合が増加し、クエリ処理が遅延することがあります。このような状況では、クエリの実行状況を監視し、リソースの使用状況を分析することが重要です。

インデックスの最適化

インデックスは、データウェアハウスにおけるクエリのパフォーマンスを向上させるための重要な要素です。適切なインデックスを作成し、管理することで、データの検索速度を大幅に改善できます。

例えば、データウェアハウス内のファクトテーブルとディメンションテーブルの結合列や検索条件に基づいてインデックスを再構築することで、データの検索や結合操作が効率化され、クエリの実行速度が向上します。顧客データの検索を高速化するために、顧客IDや顧客NAMEのインデックスを再構築した場合、顧客データの検索時間が平均で50%削減されることがあります。

しかし、過剰なインデックスは、データの書き込み速度を低下させる可能性があるため、注意が必要です。インデックスの種類と使い分けも重要です。B-treeインデックスは、範囲検索やソート処理に適していますが、ビットマップインデックスは、カーディナリティの低い列に対して有効です。

また、インデックスの定期的なメンテナンスも重要です。データの追加や削除が頻繁に行われるテーブルでは、インデックスが断片化し、パフォーマンスが低下する可能性があります。定期的にインデックスを再構築することで、最適な状態を維持できます。

データ量の増加への対応

データウェアハウスのデータ量は、時間の経過とともに増加し、パフォーマンスに影響を与える可能性があります。データ量の増加に伴うパフォーマンス劣化への対策として、パーティショニングや集約テーブルの検討が重要です。

パーティショニングは、データを一定の基準で分割して格納する方法であり、クエリの対象範囲を絞り込むことで、パフォーマンスを向上させることができます。例えば、日付に基づいてデータをパーティション分割することで、特定の期間のデータを効率的に検索できます。

集約テーブルは、事前に集計されたデータを持つテーブルであり、複雑な集計クエリの実行時間を短縮することができます。例えば、日次の売上データを集計した月次売上テーブルを作成することで、月次売上分析のクエリを高速化できます。

大規模なスタースキーマ環境のためには、複数のサーバーを活用することでリソース競合が軽減され、クエリの実行速度が向上します。書籍販売データベースの場合、ファクトテーブルとディメンションテーブルをそれぞれ別のサーバーに配置し、クエリを並列処理することでリソース競合を軽減し、クエリの実行時間が50%削減されました。

また、ハードウェアの増強も有効な対策です。ユーザー数やデータセットのサイズが大きい場合、大規模なハードウェア増強が推奨されます。

  • データ基盤
Redshiftデータマート構築｜データアナリスト向け最適化戦略 | Hakky Handbook
AWS Redshiftでのデータマート構築を解説します。データマートは特定のビジネスに最適化されたデータベースです。Redshiftで構築することで高速なクエリと高いスケーラビリティを実現し、迅速な意思決定を支援します。Redshiftを活用してデータ分析基盤を強化しませんか。

おわりに

データウェアハウスとスタースキーマの設計は、ビジネスの成長に不可欠なデータ分析基盤を構築するための重要なステップです。この記事で解説した設計原則や実践例を参考に、自社のデータ構造を最適化し、より迅速かつ正確な意思決定を実現してください。

Hakkyでは、お客様のビジネスに合わせたデータ基盤構築を支援しています。データ活用を加速させたい方は、ぜひお気軽にご相談ください。

tip
お知らせ

データ基盤の構築でお困りではありませんか？Hakkyなら、お客様のビジネスに最適なデータ基盤を構築し、データ活用を加速させます。 データ分析基盤の構築にご興味のある方は、まずはこちらのバナーから詳細をご覧ください。


関連記事

  • データ基盤
3層スキーマとは？｜DB管理者が知るべき構造・役割・課題 | Hakky Handbook
データベース管理の3層スキーマは、データの独立性を高める設計概念です。外部、概念、内部スキーマの役割と連携を解説し、効率的な管理を支援します。3層構造は変更容易性が高く、UI変更時のコストを15時間削減した事例も。本記事でデータベース設計を深く理解し、日々の業務に活かしましょう。

参考文献

2025年07月06日に最終更新
読み込み中...

資料請求

Hakkyの案件事例や提供するソリューションを確認する

メールマガジン

データ・AIに関するHakky独自の考察を受け取る

お問い合わせ

AIプロダクトやデータ活用のお悩みをHakkyに無料相談する