データ ウェアハウス スキーマを設計する
すべてのリレーショナル データベースと同様に、データ ウェアハウスには、分析するデータが格納されているテーブルが含まれています。 通常、これらのテーブルは多次元モデリング用に最適化されたスキーマに編成され、"ファクト" と呼ばれるイベントに関連付けられた数値メジャーは、複数の "ディメンション" にまたがる関連エンティティの属性によって集計できます。 たとえば、販売注文に関連付けられたメジャー (支払金額や注文された品目の数量など) は、販売が行われた日付、顧客、店舗などの属性によって集計できます。
データ ウェアハウス内のテーブル
リレーショナル データ ウェアハウスの一般的なパターンは、"ディメンション" テーブルと "ファクト" テーブルの 2 種類のテーブルを含むスキーマを定義することです。
ディメンション テーブル
ディメンション テーブルは、製品、人物、場所、日付などのビジネス エンティティを記述します。 ディメンション テーブルには、エンティティの属性の列が含まれています。 たとえば、顧客エンティティには、名、姓、電子メール アドレス、住所 (番地、市区町村、郵便番号、国または地域で構成される場合があります) などがあります。 ディメンション テーブルには、属性列に加えて、テーブル内の各行を一意に識別する一意のキー列が含まれています。 実際、ディメンション テーブルには通常、次の "2 つの" キー列が含まれています。
- "代理" キー: これは、データ ウェアハウスに固有のもので、データ ウェアハウス内のディメンション テーブルの各行を一意に識別します。通常は増分整数値です。
- "代替" キー: 多くの場合、エンティティ レコードの発信元であるトランザクション ソース システム内のエンティティの特定のインスタンスを識別するために使用される "ナチュラル" または"ビジネス" キー。製品コードや顧客 ID などです。
注意
なぜ 2 つのキーがあるのでしょう? 以下のようないくつかの理由があります。
- データ ウェアハウスには、複数のソース システムからのデータが設定される可能性があり、重複しているか互換性のないビジネス キーのリスクにつながる可能性があります。
- 一般に、単純な数値キーは、多数のテーブルを結合するクエリ (データ ウェアハウスの一般的なパターン) でパフォーマンスが向上します。
- エンティティの属性は時間の経過と共に変化する可能性があります。たとえば、顧客が住所を変更する場合があります。 データ ウェアハウスは履歴レポートをサポートするために使用されるため、エンティティの各インスタンスのレコードを複数の時点で保持したい場合があります。たとえば、特定の顧客の販売注文が、注文が行われた時点で居住していた都市でカウントされるようにします。 この場合、複数の顧客レコードが、その顧客に関連付けられた同じビジネス キーを持ちますが、その一方で、顧客がさまざまな時期に住んでいた個々の住所ごとに異なる代理キーを保持します。
顧客のディメンション テーブルの例には、次のデータが含まれる場合があります。
| CustomerKey | CustomerAltKey | 名前 | 番地 | 市区町村 | PostalCode | CountryRegion | |
|---|---|---|---|---|---|---|---|
| 123 | I-543 | Navin Jones | navin1@contoso.com | 1 Main St. | Seattle | 90000 | アメリカ合衆国 |
| 124 | R-589 | Mary Smith | mary2@contoso.com | 234 190th Ave | Buffalo | 50001 | United States |
| 125 | I-321 | Antoine Dubois | antoine1@contoso.com | 2 Rue Jolie | Paris | 20098 | フランス |
| 126 | I-543 | Navin Jones | navin1@contoso.com | 24 125th Ave. | ニューヨーク | 50000 | アメリカ合衆国 |
| ... | ... | ... | ... | ... | ... | ... | ... |
Note
テーブルに Navin Jones の 2 つのレコードが含まれていることを確認してください。 どちらのレコードも同じ代替キーを使用してこの人物 (I-543) を識別しますが、各レコードには異なる代理キーがあります。 これにより、顧客がシアトルからニューヨークに移動したと推測できます。 シアトルに住んでいる間に顧客に対して行われた販売はキー 123 に関連付けられており、ニューヨークに移動した後に行われた購入は、レコード 126 に対して記録されます。
ビジネス エンティティを表すディメンション テーブルに加えて、データ ウェアハウスには "時間" を表すディメンション テーブルを含めるのが一般的です。 データ アナリストは、この表を使用して複数の時間間隔でデータを集計できます。 分析する必要があるデータの種類に応じて、時間ディメンションの最も低い粒度 ("グレイン" と呼ばれます) は、時間 (時、秒、ミリ秒、ナノ秒、またはそれより低い値) または日付を表すことができます。
日付レベルのグレインを持つ時間ディメンション テーブルの例には、次のデータが含まれる場合があります。
| DateKey | DateAltKey | DayOfWeek | DayOfMonth | 平日 | Month | MonthName | Quarter | Year |
|---|---|---|---|---|---|---|---|---|
| 19990101 | 01-01-1999 | 6 | 1 | 金曜日 | 1 | January | 1 | 1999 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 20220101 | 01-01-2022 | 7 | 1 | 土曜日 | 1 | January | 1 | 2022 |
| 20220102 | 02-01-2022 | 1 | 2 | 土曜日 | 1 | January | 1 | 2022 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 20301231 | 31-12-2030 | 3 | 31 | Tuesday | 12 | 12 月 | 4 | 2030 |
テーブル内のレコードの対象となる期間には、関連するファクト テーブルに記録されたあらゆる関連イベントの最初と最後の時点を含める必要があります。 通常、その間にすべての間隔のレコードが適切なグレインで存在します。
ファクト テーブル
ファクト テーブルには観測値やイベントの詳細が格納されます。たとえば、販売注文、在庫量、為替レート、記録された気温などです。 ファクト テーブルには、ディメンションで集計できる数値の列が含まれています。 ファクト テーブルには、数値列に加えて、関連するディメンション テーブルの一意のキーを参照するキー列が含まれています。
たとえば、販売注文の詳細を含むファクト テーブルには、次のデータが含まれている場合があります。
| OrderDateKey | CustomerKey | StoreKey | ProductKey | OrderNo | LineItemNo | Quantity | UnitPrice | 税 | ItemTotal |
|---|---|---|---|---|---|---|---|---|---|
| 20220101 | 123 | 5 | 701 | 1001 | 1 | 2 | 2.50 | 0.50 | 5.50 |
| 20220101 | 123 | 5 | 765 | 1001 | 2 | 1 | 2.00 | 0.20 | 2.20 |
| 20220102 | 125 | 2 | 723 | 1002 | 1 | 1 | 4.99 | 0.49 | 5.48 |
| 20220103 | 126 | 1 | 823 | 1003 | 1 | 1 | 7.99 | 0.80 | 8.79 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
ファクト テーブルのディメンション キー列により、そのグレインが決定されます。 たとえば、販売注文ファクト テーブルには、日付、顧客、店舗、製品のキーが含まれます。 注文には複数の製品が含まれる場合があるため、グレインは、特定の日に顧客に店舗で販売される個々の製品の品目を表します。
データ ウェアハウス スキーマの設計
ビジネス アプリケーションで使用されるほとんどのトランザクション データベースでは、重複を減らすためにデータが "正規化" されます。 ただし、データ ウェアハウスでは、データのクエリに必要な結合の数を減らすために、ディメンション データは通常 "非正規化" されます。
多くの場合、データ ウェアハウスは "スター スキーマ" として編成されます。この場合、次の例に示すように、ファクト テーブルがディメンション テーブルに直接関連付けられます。
エンティティの属性を使用して、複数の階層レベルでファクト テーブルのメジャーを集計できます。たとえば、国または地域、市区町村、郵便番号、または個々の顧客別の総売上高を確認できます。 各レベルの属性は、同じディメンション テーブルに格納できます。 ただし、エンティティに多数の階層属性レベルがある場合、または複数のディメンションで一部の属性を共有できる場合 (たとえば、顧客と店舗の両方に地理的な住所がある場合)、次の例に示すように、ディメンション テーブルにいくつかの正規化を適用し、"スノーフレーク" スキーマを作成することが理にかなっています。
この場合、製品カテゴリと仕入先用に個別のディメンション テーブルを作成するように DimProduct テーブルが正規化され、顧客と店舗の両方の地理的属性を表すために DimGeography テーブルが追加されています。 DimProduct テーブルの各行には、DimCategory と DimSupplier のテーブルの対応する行のキー値が含まれています。DimCustomer と DimStore のテーブルの各行には、DimGeography テーブルの対応する行のキー値が含まれています。