Microsoft Fabric Warehouse でのディメンショナルモデリング: ディメンションテーブル

[アーティクル]
06/25/2024

適用対象:✅ Microsoft Fabric の SQL 分析エンドポイントおよびウェアハウス

Note

この記事は、ディメンショナルモデリングに関するシリーズ記事の一部を構成します。このシリーズでは、Microsoft Fabric Warehouse でのディメンショナルモデリングに関連するガイダンスと設計のベストプラクティスに焦点を当てています。

この記事では、ディメンションモデルで ディメンション テーブルを設計するためのガイダンスとベストプラクティスについて説明します。 Microsoft Fabric の Warehouse の実用的なガイダンスを提供します。これは、テーブルの作成やテーブル内のデータの管理など、多くの T-SQL 機能をサポートするエクスペリエンスです。そのため、ディメンションモデルテーブルの作成し、データをそこに読み込むのを完全に制御できます。

Note

この記事では、データウェアハウス という用語は、組織全体で重要なデータの包括的な統合を提供するエンタープライズデータウェアハウスを指します。これに対し、スタンドアロンの用語 warehouse は Fabric Warehouse を指します。これは、Data Warehouse の実装に使用できるサービスとしてのソフトウェア (SaaS) リレーショナルデータベースオファリングです。わかりやすくするために、この記事では後者を Fabric Warehouse として説明します。

ヒント

ディメンショナルモデリングに慣れてない場合は、この一連の記事を最初のステップで検討してください。これは、ディメンショナルモデリング設計に関する完全な説明を提供することを目的としていません。詳細については、Ralph Kimball その他による「The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling (2013 年第 3 版)」など、広く採用されている公開コンテンツを直接参照してください。

ディメンショナルモデルでは、ディメンションテーブルは、ビジネス要件と分析要件に関連するエンティティを説明します。大まかに言えば、ディメンションテーブルは、モデル化するものを表します。日付と時刻を含む、製品、人、場所、その他の概念などがあります。ディメンションテーブルを簡単に識別するには、通常、名前の先頭に d_ または Dim_ を付けます。

ディメンションテーブルの構造

ディメンションテーブルの構造を説明するには、d_Salesperson という名前の営業担当者ディメンションテーブルの次の例を考えます。この例では、適切な設計プラクティスを適用します。以下のセクションでは、列の各グループについて説明します。

CREATE TABLE d_Salesperson
(
    --Surrogate key
    Salesperson_SK INT NOT NULL,
    
    --Natural key(s)
    EmployeeID VARCHAR(20) NOT NULL,
    
    --Dimension attributes
    FirstName VARCHAR(20) NOT NULL,
    <…>
    
    --Foreign key(s) to other dimensions
    SalesRegion_FK INT NOT NULL,
    <…>
    
    --Historical tracking attributes (SCD type 2)
    RecChangeDate_FK INT NOT NULL,
    RecValidFromKey INT NOT NULL,
    RecValidToKey INT NOT NULL,
    RecReason VARCHAR(15) NOT NULL,
    RecIsCurrent BIT NOT NULL,
    
    --Audit attributes
    AuditMissing BIT NOT NULL,
    AuditIsInferred BIT NOT NULL,
    AuditCreatedDate DATE NOT NULL,
    AuditCreatedBy VARCHAR(15) NOT NULL,
    AuditLastModifiedDate DATE NOT NULL,
    AuditLastModifiedBy VARCHAR(15) NOT NULL
);

代理キー

サンプルディメンションテーブルには、Salesperson_SK という名前の代理キーがあります。代理キーは、ディメンションテーブルに生成および格納される単一列の一意識別子です。これは、ディメンショナルモデル内の他のテーブルに関連付けるために使用される主キー列です。

代理キーは、ソースデータの変更からデータウェアハウスを隔離するよう努めています。また、他にも多くの利点があり、次の機能を利用できます:

複数のデータソースを統合する (重複する識別子の競合を回避する)。
複数列のナチュラルキーを、より効率的な単一列キーに統合します。
ゆっくりと変化するディメンション (SCD) タイプ 2 でディメンション履歴を追跡します。
(可能な限り最小の整数型を選択して) ストレージの最適化のためにファクトテーブルの幅を制限します。

代理キー列は、ナチュラルキー (次に説明) が容認された候補と思われる場合でも、推奨される方法です。また、キー値に意味を与えないようにする必要があります (後で説明するように、日付と時刻のディメンションキーを除く)。

ナチュラルキー

サンプルディメンションテーブルには、EmployeeID という名前のナチュラルキーもあります。ナチュラルキーは、ソースシステムに格納されているキーです。これにより、ディメンションデータをソースシステムに関連付けることができます。これは通常、ディメンションテーブルを読み込むための抽出、読み込み、および変換 (ETL) プロセスによって行われます。ナチュラルキーが ビジネスキー と呼ばれる場合があり、その値はビジネスユーザーにとって意味がある場合があります。

ディメンションにナチュラルキーがない場合があります。それは、日付ディメンションやルックアップディメンションの場合や、フラットファイルを正規化してディメンションデータを生成する場合に当てはまります。

[ディメンションの属性]

サンプルディメンションテーブルには、FirstName列のような ディメンション属性 もあります。ディメンション属性は、関連するファクトテーブルに格納されている数値データにコンテキストを提供します。通常は、分析クエリでフィルター処理とグループ化 (スライスとサイコロ) に使用されるテキスト列ですが、それ自体は集計されません。ディメンションテーブルの中には、いくつかの属性が含まれるものもあれば、多くの属性が含まれているものもあります (ディメンショナルモデルのクエリ要件をサポートするために必要な数です)。

ヒント

必要なディメンションと属性を決定する良い方法は、適切なユーザーを見つけて、適切な質問をすることです。具体的には、別という単語のメンションに関するアラートを維持します。たとえば、営業担当者別、月別、製品カテゴリ別に売上を分析する必要があると言われると、それらの属性を持つディメンションが必要であるとユーザーに通知されます。

Direct Lake セマンティックモデルを作成する場合は、フィルター処理とグループ化に必要なすべての列をディメンション属性として含める必要があります。これは、Direct Lake セマンティックモデルが計算列をサポートしていないためです。

外部キー

サンプルディメンションテーブルには、SalesRegion_FK という名前の外部キーもあります。他のディメンションテーブルは外部キーを参照できます。そしてディメンションテーブル内に存在することは特殊なケースです。これは、テーブルが別のディメンションテーブルに関連付けられていることを示します。つまり、テーブルが Snowflake ディメンションの一部を形成する可能性があるか、アウトリガーディメンションに関連しています。

Fabric Warehouse 外部キー制約をサポートしますが、適用することはできません。そのため、データが読み込まれるときに、ETL プロセスで関連テーブル間の整合性をテストすることが重要です。

外部キーを作成することをお勧めします。強制されていない外部キーを作成する理由の 1 つは、Power BI Desktop などのモデリングツールがセマンティックモデル内のテーブル間のリレーションシップを自動的に検出して作成できるようにすることです。

履歴の追跡属性

サンプルディメンションテーブルには、さまざまな履歴の追跡属性もあります。履歴の追跡属性は、ソースシステムで発生した特定の変更を追跡する必要に応じて省略可能です。これにより、データウェアハウスの主要な役割をサポートする値を格納できます。これは、過去を正確に記述することです。具体的には、ETL プロセスが新しいデータまたは変更されたデータをディメンションに読み込むにつれて、これらの属性には履歴コンテキストが格納されます。

詳細については、この記事で後述する「履歴変更の管理」を参照してください。

監査属性

サンプルディメンションテーブルには、さまざまな監査属性もあります。監査属性は省略可能ですが、おすすめです。これにより、ディメンションレコードを作成または変更したタイミングと方法を追跡でき、ETL プロセス中に発生した診断またはトラブルシューティングの情報を含めることができます。たとえば、行を更新したユーザー (またはどのプロセス) をいつ更新したかを追跡する必要があります。監査属性は、ETL プロセスが予期せず停止した場合など、困難な問題を診断するのにも役立ちます。また、エラーとしてのディメンションメンバーや予想メンバーにフラグを設定することもできます。

ディメンションテーブルのサイズ

多くの場合、ディメンショナルモデルで最も便利で汎用性の高いディメンションは、大きく広いディメンションです。これらは行数が(数百万を超える) 多く、ディメンション属性の数 (数百もの可能性がある) は幅広く表示されます。サイズはそれほど重要ではありません (ただし、可能な限り小さいサイズに合わせて設計および最適化する必要があります)。重要なのは、ディメンションが必要なフィルター処理、グループ化、およびファクトデータの正確な履歴分析をサポートしていることです。

大規模なディメンションは、複数のソースシステムからソース化される場合があります。この場合、ディメンション処理では、データを結合、マージ、重複除去、標準化する必要があります。サロゲートキーを割り当てます。

これに対して、いくつかのディメンジョンは小さいです。複数のレコードと属性のみを含むルックアップテーブルを表す場合があります。多くの場合、これらの小さなディメンションは、ファクトテーブルのトランザクションに関連するカテゴリ値を格納し、ファクトレコードに関連する代理キーを持つディメンションとして実装されます。

ヒント

小さいディメンションが多数ある場合は、それらをジャンクディメンションに統合することを検討してください。

ディメンション設計の概念

このセクションでは、さまざまなディメンション設計の概念について説明します。

非正規化と正規化

ほとんどの場合、ディメンションテーブルを 非正規化する 必要があります。 正規化 は、繰り返しデータを減らす方法で格納されるデータを記述するために使用される用語ですが、非正規化とは、事前計算された冗長データが存在する場所を定義するために使用される用語です。通常、冗長データは階層のストレージ (後述) が原因で存在します。つまり、階層はフラット化されます。たとえば、製品ディメンションには、サブカテゴリ (およびその関連属性) とカテゴリ (およびその関連属性) を格納できます。

ディメンションは一般的に小さいため (ファクトテーブルと比較すると)、ほとんどの場合、クエリのパフォーマンスと使いやすさの改善が、冗長なデータを格納するコストを上回ります。

スノーフレークディメンション

非正規化の 1 つの例外は、Snowflake ディメンション を設計することです。 Snowflake ディメンションは正規化され、複数の関連テーブルにディメンションデータが格納されます。

次の図は、Product、Subcategory、Categoryの 3 つの関連ディメンションテーブルで構成される Snowflake ディメンションを示しています。

図は、前の段落で説明した Snowflake ディメンションの図を示しています。

次の場合は、Snowflake ディメンションの実装を検討してください:

ディメンションは非常に大きく、ストレージコストが高いクエリパフォーマンスの必要性を上回ります。 (ただし、この問題が引き続き残っていることを定期的に再評価します。)
ディメンションを粒度の高いファクトに関連付けるキーが必要です。たとえば、販売ファクトテーブルには製品レベルで行が格納されますが、販売対象のファクトテーブルにはサブカテゴリレベルの行が格納されます。
より高いレベルの細分性で、履歴の変更を追跡する必要があります。

Note

Power BI セマンティックモデルの階層は、単一のセマンティックモデルテーブルの列にのみ基づくことができます。したがって、Snowflake ディメンションは、Snowflake テーブルを結合するビューを使用して非正規化された結果を提供する必要があります。

Hierarchies (階層)

一般に、ディメンション列は階層を生成します。階層を使用すると、異なるレベルの集計でデータを探索できます。たとえば、マトリックスビジュアルの初期ビューには年単位の売上が表示される場合があり、レポートコンシューマーは四半期および月次の売上を表示するためにドリルダウンを選ぶことができます。

ディメンションに階層を格納するには、3 通りの方法があります。使用できるもの:

非正規化された単一のディメンションの列。
複数の関連テーブルで構成される Snowflake ディメンション。
ディメンション内の親子 (自己参照) リレーションシップ。

階層は、均衡が取れているか、不均衡である可能性があります。また、一部の階層が不規則であることを理解することも重要です。

均衡階層

均衡階層 は最も一般的な階層の種類です。均衡階層には、同じ数のレベルがあります。均衡階層の一般的な例は、年、四半期、月、および日付のレベルで構成される日付ディメンションのカレンダー階層です。

次の図は、販売地域の均衡階層を示しています。これは、販売地域グループと販売地域の 2 つのレベルで構成されます。

図は、[グループ] 列と [販売地域] 列を含む販売地域ディメンションメンバーのテーブルを示しています。

均衡階層のレベルは、単一の非正規化ディメンションの列、または Snowflake ディメンションを形成するテーブルの列に基づいています。単一の非正規化ディメンションに基づく場合、上位レベルを表す列には冗長データが含まれます。

均衡階層の場合、ファクトは常に階層の 1 つのレベル (通常は最下位レベル) に関連付けられます。こうすることで、ファクトを階層の最上位レベルに集計 (ロールアップ) できます。ファクトは、ファクトテーブルの粒度によって決定される任意のレベルに関連付けることができます。たとえば、販売ファクトテーブルは日付レベルで格納され、売上目標ファクトテーブルは四半期レベルで格納される場合があります。

不均衡階層

不均衡階層 は、あまり一般的ではない種類の階層です。不均衡階層には、親子関係に基づくレベルがあります。このため、不均衡階層のレベルの数は、特定のディメンションテーブル列ではなく、ディメンション行によって決まります。

不均衡階層の一般的な例として、従業員ディメンションの各行が同じテーブル内のレポートマネージャー行に関連 従業員階層 があります。この場合、すべての従業員は、レポートの従業員を持つマネージャーにすることができます。当然ながら、階層の一部のブランチには、他のブランチよりも多くのレベルがあります。

次の図は、不均衡階層を示しています。これは 4 つのレベルで構成され、階層内の各メンバーは営業担当者です。営業担当者の報告先に応じて、階層内の先祖の数が異なっていることに注意してください。

[レポート先] 列を含む営業担当者ディメンションメンバーのテーブルを示す図。

不均衡階層の他の一般的な例としては、部品表、会社の所有権モデル、および一般会計があります。

不均衡階層の場合、ファクトは常にディメンショングレインに関連付けられます。たとえば、販売ファクトは、レポート構造が異なるさまざまな営業担当者に関連します。ディメンションテーブルには、代理キー (Salesperson_SK という名前) と、主キー列を参照する ReportsTo_Salesperson_FK 外部キー列があります。管理する人がいない各営業担当者は、必ずしも階層のどのブランチでも最下位レベルにいるとは限りません。最下位レベルでない場合、営業担当者は製品を販売し、製品も販売するレポート営業担当者を持つ可能性があります。そのため、ファクトデータのロールアップでは、個々の営業担当者とそのすべての子孫を考慮する必要があります。

親子階層のクエリは、特に大きなディメンションでは複雑かつ遅くなる可能性があります。ソースシステムではリレーションシップが親子として格納される場合があります。階層を順応させることをお勧めします。この場合、順応とは、階層レベルを変換し、ディメンションに列として格納することを意味します。

ヒント

階層を順応させない場合でも、Power BI セマンティックモデルの親子関係に基づいて階層を作成できます。ただし、この方法は大きなディメンションには推奨されません。詳細については、「DAX の親子階層の関数について」を参照してください。

不規則階層

階層内のメンバーの親が、そのすぐ上にないレベルに存在するため、階層がに 不規則 になることがあります。このような場合、レベル値がない場合は、親の値が繰り返されます。

均衡地理階層の例を考えてみましょう。国/地域に州や県がない場合、不規則階層が存在します。たとえば、ニュージーランドには州も県もありません。そのため、ニュージーランドの行を挿入するときは、StateProvince列にも国/地域の値を格納する必要があります。

次の図は、地理的リージョンの不規則階層を示しています。

図は、国/地域、都道府県、都市の列を含む地理ディメンションメンバーのテーブルを示しています。

履歴変更の管理

必要に応じて、ゆっくり変化するディメンション (SCD) を実装することで履歴変更を管理できます。 SCD は、履歴コンテキストを新しいデータまたは変更されたデータとして保持します。

最も一般的な SCD タイプは以下のとおりです。

タイプ 1: 既存のディメンションメンバーを上書きします。
タイプ 2: 新しい時間ベースの バージョン管理 ディメンションメンバーを挿入します。
タイプ 3: 属性を使用して制限された履歴を追跡します。

ディメンションでは、SCD タイプ 1 と SCD タイプ 2 の両方の変更をサポートできる可能性があります。

SCD タイプ 3 は、セマンティックモデルで使用するのが難しいという理由から、一般的には使用されません。 SCD タイプ 2 のアプローチの方が適しているかどうかを慎重に検討してください。

ヒント

頻繁に変化する属性を持つディメンションである 急速に変化するディメンション を使用する場合は、代わりにその属性をファクトテーブルに追加することを検討してください。属性が製品価格などの数値の場合は、ファクトテーブルに測定値として追加できます。属性がテキスト値の場合は、すべてのテキスト値に基づいてディメンションを作成し、そのディメンションキーをファクトテーブルに追加できます。

SCD タイプ 1

SCD タイプ 1 の変更は、変更を追跡する必要がないため、既存のディメンション行を上書きします。この SCD タイプは、エラーの修正にも使用できます。これは共通の SCD タイプであり、顧客名、電子メールアドレスなど、ほとんどの変化する属性に使用する必要があります。

次の図は、電話番号が変更された営業担当者ディメンションメンバーの前後の状態を示しています。

図は、営業担当者ディメンションテーブルの構造と、1 人の営業担当者の変更された電話番号の前後の値を示しています。

この SCD タイプでは、既存の行が更新されるため、履歴の全体像は保持されません。つまり、SCD タイプ 1 の変更により、異なる高レベル集計につながる可能性があります。たとえば、営業担当者が別の販売地域に割り当てられている場合、SCD タイプ 1 の変更によってディメンション行が上書きされます。営業担当者の過去の販売実績を地域にロールアップすると、新しい現在の販売地域が使用されるため、別の結果が生成されます。これは、その営業担当者が常に新しい販売地域に割り当てられている状況と類似していました。

SCD タイプ 2

SCD タイプ 2 の変更により、ディメンションメンバーの時間ベースのバージョンを表す新しい行が生成されます。常に現在のバージョンの行があり、ソースシステムのディメンションメンバーの状態を反映します。ディメンションテーブルの履歴追跡属性には現在のバージョン (現在のフラグが TRUE ) とその有効期間を識別できる値が格納されます。複数のバージョンが格納されている場合、重複するナチュラルキーが存在するため、代理キーが必要です。

これは一般的な種類の SCD ですが、履歴の全体像を保持する必要がある属性用に予約する必要があります。

たとえば、営業担当者が別の販売地域に割り当てられている場合、SCD タイプ 2 の変更には更新操作と挿入操作が含まれます。

更新操作により、現在のバージョンが上書きされ、履歴追跡属性が設定されます。具体的には、終了有効性列は ETL 処理日 (またはソースシステムの適切なタイムスタンプ) に設定され、現在のフラグは FALSE に設定されます。
挿入操作により、新しい現在のバージョンが追加され、開始の有効期間列が終了の有効期間列の値 (以前のバージョンを更新するために使用) に設定され、現在のフラグが TRUE に設定されます。

関連するファクトテーブルの細分性は、営業担当者レベルではなく、営業担当者バージョン レベルであることを理解しておくことが重要です。過去の販売実績を地域にロールアップすると、正しい結果が生成されますが、分析する営業担当者メンバーバージョンが 2 つ (またはそれ以上) あります。

次の図は、販売地域が変更された営業担当のディメンションメンバーの前後の状態を示しています。組織は、営業担当者の労力を割り当てられているリージョン別に分析したいので、SCD タイプ 2 の変更をトリガーします。

図は、

ヒント

ディメンションテーブルで SCD タイプ 2 の変更がサポートされている場合は、メンバーとバージョンを説明するラベル属性を含める必要があります。 Adventure Works の営業担当者 Lynn Tsoflias が、オーストラリアの販売地域から英国の販売地域に割り当てを変更する場合の例を考えてみましょう。最初のバージョンのラベル属性は "Lynn Tsoflias (オーストラリア)" を読み取り、新しい現在のバージョンのラベル属性には "Lynn Tsoflias (英国) " と読み取ることができました。役立つのであれば、ラベルにも有効期間を含めることができます。

履歴の精度か使いやすさ、および効率の必要性のバランスを取る必要があります。ディメンションテーブルに SCD タイプ 2 の変更が多くなりすぎないようにしてください。これは、アナリストにとって理解するのが難しくなるバージョンの数が圧倒的に多い可能性があるためです。

また、バージョンが多すぎると、変更された属性がファクトテーブルに格納される方が良い場合があることを示している可能性があります。前の例を拡張すると、販売地域の変更が頻繁に発生した場合、販売地域は SCD タイプ 2 を実装するのではなく、ファクトテーブルにディメンションキーとして格納できます。

次の SCD タイプ 2 の履歴追跡属性について検討してみましょう。

CREATE TABLE d_Salesperson
(
    <…>

    --Historical tracking attributes (SCD type 2)
    RecChangeDate_FK INT NOT NULL,
    RecValidFromKey INT NOT NULL,
    RecValidToKey INT NOT NULL,
    RecReason VARCHAR(15) NOT NULL,
    RecIsCurrent BIT NOT NULL,

    <…>
);

履歴追跡属性の目的を次に示します。

RecChangeDate_FK 列には、変更が有効になった日付が格納されます。変更が行われたときにクエリを実行できます。
RecValidFromKey 列と RecValidToKey 列には、行の有効日が格納されます。最初のバージョンを表す RecValidFromKey の日付ディメンションに最も古い日付を格納し、現在のバージョンの RecValidToKey の 01/01/9999 を格納することを検討してください。
RecReason 列の列は省略可能です。これにより、バージョンが挿入された理由を文書化できます。変更された属性をエンコードすることも、特定のビジネス上の理由を示すソースシステムのコードである可能性もあります。
RecIsCurrent 列を使用すると、現在のバージョンのみを取得できます。これは、ETL プロセスがファクトテーブルを読み込むときにディメンションキーを検索するときに使用されます。

Note

一部のソースシステムでは履歴の変更が格納されないため、ディメンションを定期的に処理して変更を検出し、新しいバージョンを実装することが重要です。そうすることで、変更が発生した直後に検出でき、その有効期間は正確になります。

SCD タイプ 3

SCD タイプ 3 の変更では、属性を持つ限られた履歴が追跡されます。この方法は、最終変更や最新の変更を記録する必要がある場合に役立ちます。

この SCD タイプは、制限された 履歴の全体像を保持します。初期値と現在の値のみを格納する必要がある場合に便利です。この場合、暫定の変更は必要ありません。

たとえば、営業担当者が別の販売地域に割り当てられている場合、SCD タイプ 3 の変更によってディメンション行が上書きされます。前の販売地域を具体的に格納する列は前の販売地域として設定され、新しい販売地域は現在の販売地域として設定されます。

次の図は、販売地域が変更された営業担当のディメンションメンバーの前後の状態を示しています。組織は以前の販売地域の割り当てを決定する必要があるため、SCD タイプ 3 の変更をトリガーします。

図は、

特殊なディメンションメンバー

不明、未確認、N/A、またはエラー状態を表すディメンションに行を挿入できます。たとえば、次の代理キー値を使用するとします。

キーの値	目的
0	不明 (ソースシステムでは使用できません)
-1	未確認 (ファクトテーブルの読み込み中の検索エラー)
-2	N/A (適用なし)
-3	エラー

カレンダーと時刻

ほとんど例外なく、ファクトテーブルには特定の時点で測定値が格納されます。日付 (および場合によっては時刻) による分析をサポートするには、カレンダー (日付と時刻) ディメンションが必要です。

ソースシステムにカレンダーディメンションデータが含まれるのは珍しいので、データウェアハウスで生成する必要があります。通常、1 回生成されますが、カレンダーディメンションの場合は、必要に応じて将来の日付で拡張されます。

日付ディメンション

日付 (またはカレンダー) ディメンションは、分析に使用される最も一般的なディメンションです。日付ごとに 1 行を格納し、年、四半期、月などの特定の期間でフィルター処理またはグループ化する一般的な要件をサポートします。

重要

日付ディメンションには、一日の時刻まで拡張される粒度を含めてはいけません。時刻分析が必要な場合は、日付ディメンションと時間ディメンション (次に説明します) の両方が必要です。時刻ファクトを格納するファクトテーブルには、これらの各ディメンションに対して 1 つずつ、計 2 つの外部キーが必要です。

日付ディメンションの自然キーでは、日付データ型を使用する必要があります。代理キーは YYYYMMDD 形式と int データ型を使用して日付を格納する必要があります。代理キー値が意味を持ち、人間が判読できる場合は、この受け入れられたプラクティスが (時間ディメンションとともに) 唯一の例外である必要があります。 YYYYMMDD を int データ型として格納することは、効率的で数値的に並べ替えられるだけでなく、明確な国際標準機構 (ISO) 8601 日付形式にも準拠しています。

日付ディメンションに含める一般的な属性を次に示します。

Year、Quarter、Month, Day
QuarterNumberInYear、MonthNumberInYear– テキストラベルの並べ替えに必要な場合があります。
FiscalYear、FiscalQuarter- 一部の企業会計スケジュールは、カレンダーの年の開始/終了と会計年度が異なるように、中間期に開始されます。
FiscalQuarterNumberInYear、FiscalMonthNumberInYear– テキストラベルの並べ替えに必要な場合があります。
WeekOfYear – 52 週間または 53 週間の ISO 標準を含め、年の週にラベルを付ける方法は複数あります。
IsHoliday、HolidayText– 組織が複数の地域で活動している場合は、各地域が個別のディメンションとして観察するか、日付ディメンションの複数の属性で順応する複数の休日リストのセットを保持する必要があります。 HolidayText 属性を追加すると、レポートの休日を識別するのに役立ちます。
IsWeekday – 同様に、一部の地域では、標準の稼働日は月曜日から金曜日ではありません。たとえば、多くの中東地域では稼働日は日曜日から木曜日で、他の地域では 4 日または 6 日間が採用されています。
LastDayOfMonth
RelativeYearOffset、RelativeQuarterOffset、RelativeMonthOffset、RelativeDayOffset- 相対日付フィルター処理 (前月など) をサポートするために必要な場合があります。現在の期間では、ゼロ (0) のオフセットが使用されます。前の期間には、-1、-2、-3... のオフセットが格納されます。今後の期間には、1、2、3...のオフセットが格納されます。

他のディメンションと同様に、重要なのは、既知のフィルター処理、グループ化、階層の要件をサポートする属性が含まれていることです。ラベルの翻訳を他の言語に格納する属性もあります。

ディメンションを使用して粒度の高いファクトに関連付ける場合、ファクトテーブルでは、日付期間の最初の日付を使用できます。たとえば、四半期ごとの営業担当者ターゲットを格納する売上目標ファクトテーブルでは、四半期の最初の日付が日付ディメンションに格納されます。別の方法として、日付テーブルにキー列を作成します。たとえば、四半期キーは、YYYYQ形式と smallint データ型を使用して四半期キーを格納できます。

ディメンションには、すべてのファクトテーブルで使用される既知の日付範囲が設定されている必要があります。また、データウェアハウスがターゲット、予算、または予測に関するファクトを格納する将来の日付も含める必要があります。他のディメンションと同様に、不明、未確認、N/A、またはエラーの状況を表す行を含めることができます。

ヒント

インターネットで「日付ディメンションジェネレーター」を検索し、日付データを生成するスクリプトとスプレッドシートを検索します。

通常、次の年の初めに、ETL プロセスでは、日付ディメンション行を特定の年数まで延長する必要があります。ディメンションに相対オフセット属性が含まれている場合、現在の日付 (今日) に基づいてオフセット属性値を更新するには、ETL プロセスを毎日実行する必要があります。

時間ディメンション

場合によっては、特定の時点 (1 日のうちの時刻など) にファクトを格納する必要があります。この場合は、時間 (またはクロック) ディメンションを作成します。分単位 (24 x 60 = 1,440 行) または秒単位 (24 x 60 x 60 = 86,400 行) の場合があります。その他の可能な粒度には、30 分または 1 時間が含まれます。

時間ディメンションのナチュラルキーでは、時間データ型を使用する必要があります。サロゲートキーは、適切な形式を使用し、意味を持ち、人間が判読できる値を格納できます(たとえば、HHMMまたは HHMMSS 形式を使用します)。

時間ディメンションに含める一般的な属性を次に示します。

Hour、HalfHour、QuarterHour, Minute
時間帯ラベル (朝、午後、夕方、夜)
勤務シフト名
繁忙期フラグまたは閑散期フラグ

コンフォームド・ディメンション

一部のディメンションは、コンフォームド・ディメンション である場合があります。コンフォームド・ディメンションは多くのファクトテーブルに関連するため、ディメンションモデル内の複数のスターで共有されます。これらは一貫性をもたらし、継続的な開発とメンテナンスを減らすのに役立ちます。

たとえば、ファクトテーブルには少なくとも 1 つの日付ディメンションキーが格納されるのが一般的です (アクティビティはほとんどの場合、日付や時刻によって記録されるため)。そのため、日付ディメンションは一般的なコンフォームド・ディメンションです。したがって、日付ディメンションに、すべてのファクトテーブルの分析に関連する属性が含まれていることを確認する必要があります。

次の図は、Salesファクトテーブルと Inventory ファクトテーブルを示しています。各ファクトテーブルは、コンフォームド・ディメンションである Date ディメンションと Product ディメンションに関連付けられます。

図は、前の段落で説明したように、コンフォームド・ディメンションの図を示しています。

別の例として、従業員とユーザーは同じユーザーのセットである可能性があります。この場合、各エンティティの属性を組み合わせて、1 つのコンフォームド・ディメンションを生成することが意味を成すことがあります。

多様ディメンション

ファクトテーブルでディメンションが複数回参照される場合、ディメンションは 多様ディメンション と呼ばれます。

たとえば、販売ファクトテーブルに注文日、出荷日、納品日のディメンションキーがある場合、日付ディメンションは 3 つの方法で関連しています。それぞれの方法は個別の ロール を表しますが、物理的な日付ディメンションは 1 つだけです。

次の図は、Flightファクトテーブルを示しています。 Airport ディメンションは、Departure Airportディメンションと Arrival Airport ディメンションとしてファクトテーブルに 2 回関連するため、多様ディメンションです。

図は、前の段落で説明した航空便のファクトのスタースキーマの図を示しています。

ジャンクディメンション

ジャンクディメンション は、独立したディメンションが多数存在する場合、特に少数の属性 (おそらく 1 つ) で構成されており、これらの属性のカーディナリティが低い (値が小さい) の場合に、有用です。ジャンクディメンションの目的は、多数の小さなディメンションを単一のディメンションに集約することです。この設計アプローチでは、ディメンションの数を減らし、ファクトテーブルキーの数とファクトテーブルのストレージサイズを減らすことができます。また、ユーザーに表示されるテーブルが少なくなるため、データペインクラッターを減らすのに役立ちます。

ジャンクディメンションテーブルは、通常、すべてのディメンションの属性値のデカルト積を、代理キー列とともに格納します。

適切な候補には、フラグとインジケーター、注文の状態、顧客の人口統計状態 (性別、年齢グループなど) が含まれます。

次の図は、注文状態の値と納品状態の値を組み合わせた Sales Status という名前のジャンクディメンションを示しています。

図は、注文状態と納品状態の値と、それらの値のデカルト積によって [販売状態] ディメンション行がどのように作成されるかを示しています。

逆ディメンション

逆ディメンション は、ディメンションが関連するファクトと同じ粒度にある場合に発生する可能性があります。逆ディメンションの一般的な例は、販売ファクトテーブルに関連する販売注文番号ディメンションです。通常、請求書番号はファクトテーブル内にある唯一の非階層属性です。そのため、このデータをコピーして別のディメンションテーブルを作成することは認められていません。

次の図は、販売ファクトテーブルの SalesOrderNumber 列に基づく、逆ディメンションである Sales Order ディメンションを示しています。このディメンションは、個別の販売注文番号の値を取得するビューとして実装されます。

図は、前の段落で説明したように、逆ディメンションを示しています。

ヒント

クエリ目的のディメンションとして逆ディメンションを表示するビューを Fabric Warehouse に作成できます。

Power BI セマンティックモデリングの観点から、Power Query を使用して、逆ディメンションを別のテーブルとして作成できます。このように、セマンティックモデルは、フィルター処理またはグループ化に使用されるフィールドがディメンションテーブルから取得され、ファクトの集約に使用されるフィールドがファクトテーブルから取得されるというベストプラクティスに従います。

アウトリガーディメンション

ディメンションテーブルが他のディメンションテーブルに関連付けられている場合は、アウトリガーディメンション と呼ばれます。アウトリガーディメンションは、ディメンションモデルの定義に適合して再利用するのに役立ちます。

たとえば、郵便番号ごとに地理的な場所を格納する地理ディメンションを作成できます。そのディメンションは、顧客ディメンション および 営業担当者ディメンションが参照できます。このディメンションには、地理ディメンションの代理キーが格納されます。これにより、顧客と営業担当者は、一致した地理的な場所を使用して分析できます。

次の図は、アウトリガーディメンションである Geography ディメンションを示しています。 Sales ファクトテーブルには直接関係しません。代わりに、Customerディメンションと Salesperson ディメンションを介して間接的に関連付けられます。

図は、前の段落で説明したアウトリガーディメンションの図を示しています。

他のディメンションテーブル属性に日付が格納されている場合、日付ディメンションをアウトリガーディメンションとして使用できることを検討してください。たとえば、顧客ディメンションの生年月日は、日付ディメンションテーブルの代理キーを使用して格納できます。

多値ディメンション

ディメンション属性に複数の値を格納する必要がある場合は、多値ディメンション を設計する必要があります。複数値ディメンションを実装するには、ブリッジテーブル (結合テーブルとも呼ばれます) を作成します。ブリッジテーブルには、エンティティ間の多対多リレーションシップが格納されます。

たとえば、営業担当者ディメンションがあり、各営業担当者が 1 つまたは複数の販売地域に割り当てられているとします。この場合、販売地域ディメンションを作成するのが賢明です。そのディメンションは、各販売地域を 1 回だけ格納します。ブリッジテーブルと呼ばれる個別のテーブルには、営業担当者と販売地域のリレーションシップごとに 1 行格納されます。物理的には、営業担当者ディメンションからブリッジテーブルへの一対多リレーションシップと、販売地域ディメンションからブリッジテーブルへの一対多リレーションシップがあります。必然的に、営業担当者と販売地域の間には多対多リレーションシップがあります。

次の図では、Accountディメンションテーブルは Transaction ファクトテーブルに関連しています。顧客は複数のアカウントを持つ可能性があり、アカウントには複数の顧客を含めることができるため、Customerディメンションテーブルは Customer Account ブリッジテーブルを介して関連付けられます。

図は、前の段落で説明した複数値ディメンションの図を示しています。

このシリーズの次の記事では、ファクトテーブルのガイダンスと設計のベストプラクティスについて説明します。

次の方法で共有

Microsoft Fabric Warehouse でのディメンショナルモデリング: ディメンションテーブル

ディメンションテーブルの構造