データ ウェアハウスの設計: ERP および e コマース分析用のスター スキーマ

ERP データベースは、注文の挿入、在庫の更新、支払いの処理といったトランザクション用に最適化されています。 e コマース プラットフォームは、商品ページの提供とチェックアウトの処理に最適化されています。どちらも、ビジネス上の意思決定を促す質問、つまり返品後に最も収益性が高い製品カテゴリはどれか、という質問に答えるために最適化されていません。生涯価値が増加している顧客セグメントはどれですか?サプライチェーンのボトルネックはどこにあるのでしょうか?

そのギャップを埋めるのがデータ ウェアハウスです。スター スキーマは、分析クエリを高速、直感的、保守可能にする設計パターンです。

重要なポイント

• スター スキーマは、ビジネス メトリクス (ファクト) を説明的なコンテキスト (ディメンション) から分離し、クエリを直感的かつ高速にします。
• ERP および e コマース分析では、ビジネスの中核となる質問をカバーするために、通常 4 ～ 6 個のファクト テーブルと 8 ～ 12 個のディメンション テーブルが必要です。
• ETL パイプラインは、すべてのデータを再処理せずに履歴分析を処理するために、ゆっくりと変化するディメンションによる増分読み込みを使用する必要があります。
• 適切に設計されたスター スキーマにより、正規化された運用データベースに直接クエリを実行する場合と比較して、クエリの複雑さが 60 ～ 80 パーセント軽減されます。

---

ERP に直接クエリしてみませんか?

多くの企業は、別のデータ ウェアハウスに投資する前に、運用データベースに対して分析クエリを実行しようとします。これは 3 つの理由で失敗します。

パフォーマンス。 分析クエリは数百万行をスキャンし、集計を計算し、多くのテーブルを結合します。これらを運用データベースに対して実行すると、すべてのユーザーの ERP の速度が低下します。 6 か月分の注文データをスキャンするレポートでは、テーブルがロックされ、Shopify ストアでのチェックアウトのパフォーマンスが低下する可能性があります。

複雑さ 運用データベースは正規化されており、データの冗長性を最小限に抑えるように設計されています。 「月ごとの製品カテゴリ別の総収益」のような単純な質問では、Odoo の PostgreSQL データベース内の 8 つのテーブルを結合する必要がある場合があります。スター スキーマでは、同じクエリが 2 つのテーブルを結合します。

歴史。 運用システムはデータを上書きします。顧客が住所を変更すると、古い住所は失われます。製品が再分類されると、履歴レポートが遡って変更されます。データ ウェアハウスは、ゆっくりと変化するディメンションを通じて履歴を保存します。

マルチソース 中堅企業は通常、ビジネス データを含む 3 ～ 7 つのシステムを実行しています。データ ウェアハウスはそれらすべてを統合します。 ERP データ用の ETL パイプライン のガイドでは、抽出と読み込みについて詳しく説明しています。

---

スタースキーマの基礎

スター スキーマは、データをファクト テーブルとディメンション テーブルの 2 種類のテーブルに編成します。ファクト テーブルは中心 (スターの本体) に配置され、ディメンション テーブルがその周囲 (スターのポイント) に配置されます。

ファクトテーブル

ファクト テーブルには、測定可能なビジネス イベント、つまり何が起こったかが保存されます。各行は、意味のある最も低い粒度で 1 つのイベントを表します。

特徴:

• 数値メジャー (量、量、期間、カウント) が含まれています。
• ディメンション テーブルへの外部キーを含む
• 通常、ウェアハウス内で最大のテーブルです
• 新しいイベントが発生すると継続的に成長します
• ビジネス上の質問をサポートする最も細かい粒度である必要があります

寸法表

ディメンション テーブルには、ビジネス イベントの誰が、何を、どこで、いつ、どのようにして説明するコンテキストが保存されます。

特徴:

• テキスト属性と階層が含まれます
• 比較的小さい (数十億行ではなく、数千行から数百万行)
• 時間の経過とともにゆっくりと変化します
• クエリを簡素化するために非正規化されています
• レポートのラベル、フィルター、グループ化を提供します。

星の形

                    Dim: Customer
                         |
    Dim: Product ---  Fact: Sales  --- Dim: Time
                         |
                    Dim: Location

「製品カテゴリ別、四半期別、地域別の総収益」のようなクエリは、売上ファクト テーブルを 3 つのディメンション テーブルに結合します。サブクエリや複雑なネストされた結合はなく、単純なスター結合だけです。

---

ERP および e コマース用のファクト テーブルの設計

Odoo ERP と Shopify eCommerce を実行している典型的な中堅企業では、中核となる分析ユースケースをカバーするために 4 ～ 6 個のファクト テーブルが必要です。

事実: 売上

販売ファクトテーブルが基礎となります。各行は、販売注文の 1 つの明細項目を表します。

粒度: 注文品目ごとに 1 日あたり 1 行。

事実: 在庫

在庫レベルをイベントではなく定期的なスナップショットとして追跡します。

粒度: 倉庫ごとに 1 日あたり製品ごとに 1 行。

事実: 生産

製造会社の場合、生産事実は作業指示を追跡します。

粒度: 1 日あたり、製品ごとに作業指示書ごとに 1 行。

追加のファクトテーブル

• 事実: 購入 --- ベンダー、製品、および時間ごとの調達支出。
• 事実: サポート チケット --- エージェント、顧客、カテゴリ別のチケット量、応答時間、解決時間。
• 事実: Web トラフィック --- ページ、ソース、キャンペーンごとのページ ビュー、セッション、コンバージョン。 マーケティング アトリビューション分析 に役立ちます。

---

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

ディメンション テーブルは、ファクト テーブル番号を意味のあるものにするコンテキストを提供します。主な原則は非正規化です。つまり、クエリを簡素化するために冗長データを保存します。

薄暗い: 時間

時間次元はすべてのスター スキーマに存在します。カレンダー属性を事前に計算して、クエリでの複雑な日付関数を回避します。

薄暗い: 顧客

CRM、会計、および e コマース システムからの顧客属性を単一のディメンションに非正規化します。

rfm_segment 列と lifetime_value_tier 列は、RFM 分析 から派生した計算フィールドであり、ETL パイプラインによって定期的に更新されます。

薄暗い: 製品

---

ゆっくりと変化する次元

顧客がニューヨークからロンドンに移動した場合、データ ウェアハウスは何をすべきでしょうか?答えはビジネス上の質問によって異なります。

タイプ 1: 上書き

古い値を新しい値に置き換えます。顧客の都市はロンドンになり、過去のすべての注文にロンドンが表示されるようになりました。属性の履歴の正確性が重要でない場合にこれを使用します。

タイプ 2: 新しい行を追加

新しい都市、発効日、および有効期限を含む顧客用の新しい行を作成します。過去の注文は引き続き古い行 (ニューヨーク) を指し、新しい注文は新しい行 (ロンドン) を指します。これは、分析に影響を与える属性 (顧客セグメント、従業員部門、製品カテゴリ) に対する最も一般的なアプローチです。

タイプ 3: 新しい列の追加

古い値と新しい値の両方を別の列に保存します。前後を比較する必要があるが、完全な履歴は必要ない場合に便利です。実際にはあまり一般的ではありません。

中堅企業の場合は、顧客セグメント、従業員部門、製品カテゴリ、地理的属性にタイプ 2 を使用します。倉庫をシンプルに保つために、その他すべてにタイプ 1 を使用します。

---

ETL デザイン パターン

ETL (抽出、変換、ロード) プロセスは、データをソース システムからウェアハウスに移動します。 ERP および e コマース データに適した設計パターンには次のものがあります。

増分ロード

実行のたびにすべてのデータを再ロードするのではなく、最後に正常にロードされたタイムスタンプを追跡し、それ以降に変更されたレコードのみを処理します。 Odoo の write_date フィールドと Shopify の updated_at パラメーターを使用すると、これが簡単になります。

1. Query source: SELECT * FROM sale_order_line WHERE write_date > last_load_timestamp
2. Transform: Map source fields to warehouse columns, look up dimension keys
3. Load: INSERT new rows, UPDATE changed rows (upsert)
4. Update: Set last_load_timestamp to current run start time

サロゲートキーの管理

ディメンション テーブルでは、ナチュラル キー (Odoo ID、Shopify ID) の代わりにサロゲート キー (自動インクリメント整数) が使用されます。これにより、ソース システムのキー形式からウェアハウスが切り離され、異なるシステムに競合する ID スキームがある場合のマルチソースの統合が処理されます。

遅れて到着するディメンション

場合によっては、ファクト レコードが対応するディメンション レコードより前に到着することがあります。つまり、注文がまだ同期されていない新規顧客を参照している場合です。これは、完全なディメンション レコードが到着したときに更新されるプレースホルダー ディメンション行で処理します。

更新スケジュール

リアルタイム要件については、ストリーミング分析 のガイドをご覧ください。

---

クエリのパフォーマンスの最適化

適切に設計されたスター スキーマは、その単純な結合パターンにより、すでに良好なパフォーマンスを発揮します。追加の最適化には以下が含まれます。

インデックス。 ファクト テーブル内のすべてのディメンション外部キーと、一般的にフィルタリングされるディメンション属性 (日付範囲、顧客セグメント、製品カテゴリ) にインデックスを作成します。

マテリアライズド ビュー 一般的なクエリを事前に集計します: 製品カテゴリ別の日次収益、倉庫別の週次在庫レベル、チャネル別の月次顧客獲得。 ETL をロードするたびにマテリアライズド ビューを更新します。

パーティション化。 大きなファクト テーブルを日付 (月ごとまたは四半期ごと) でパーティション化します。日付範囲でフィルタリングするクエリは、関連するパーティションのみをスキャンします。

列統計。 クエリ プランナーが最適な決定を下せるように、一括読み込み後に ANALYZE を使用して PostgreSQL 統計を最新の状態に保ちます。

これらの最適化により、ビジネス ユーザーがパフォーマンスを懸念することなくアドホック クエリを実行できる セルフサービス BI エクスペリエンスがサポートされます。

---

よくある質問

データ ウェアハウスを正当化するには、会社の規模はどれくらい必要ですか?

最小サイズはありませんが、分析のために結合する必要がある複数のデータ ソースがある場合、運用データベースのクエリによって運用システムの速度が低下している場合、または手動のデータ収集とレポート作成に週 10 時間を超える場合には、投資する価値があります。従業員が 30 人以上で、少なくとも 2 つのシステム (ERP と e コマース) を備えているほとんどの企業は、倉庫の恩恵を受けています。

Snowflake や BigQuery などのクラウド データ ウェアハウスを使用する必要がありますか?

中規模企業の場合、PostgreSQL はほとんどの分析ワークロードを適切に処理し、コストが大幅に低くなります。 Snowflake のようなクラウド ウェアハウスは、データが 1 TB を超える場合、コスト最適化のためにコンピューティングをストレージから分離する必要がある場合、または組織間で複雑なデータ共有要件がある場合に魅力的になります。 PostgreSQL から始めて、規模が大きくなったら移行してください。

データ ウェアハウスの構築にはどれくらいの時間がかかりますか?

1 つのファクト テーブル (売上)、4 つのディメンション テーブル、および Odoo と Shopify を接続する ETL パイプラインを備えた最小限の実行可能なウェアハウスには、経験豊富なチームの場合 4 ～ 8 週間かかります。ファクト テーブルの追加、徐々に変化するディメンション、およびデータ品質の監視には、ファクト テーブルごとにさらに 4 ～ 8 週間かかります。すべての主要なビジネス分野をカバーする包括的な倉庫を構築するには、3 ～ 6 か月の計画を立てます。

---

次は何ですか

適切に設計されたスター スキーマは、セルフサービス ダッシュボード から 予測モデル、埋め込み分析 に至るまで、あらゆる分析機能の基盤です。これは、企業の意思決定方法を変える、より広範な BI 戦略 の一部です。

ECOSIRE は、Odoo、Shopify、GoHighLevel を実行している企業向けにデータ ウェアハウスと分析パイプラインを構築します。当社の Odoo コンサルタント チームは、お客様のビジネス モデルに合わせたウェアハウス スキームを設計し、その上に OpenClaw AI サービス の予測分析を重ねます。

お問い合わせ データ ウェアハウス アーキテクチャについてご相談ください。

---

ECOSIRE によって発行 --- Odoo ERP、Shopify eCommerce、OpenClaw AI にわたる AI を活用したソリューションで企業のスケールアップを支援します。