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

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

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

ディメンショナル モデルを読み込む

ディメンショナル モデルの読み込みには、抽出、変換、読み込み (ETL) プロセスを定期的に実行する必要があります。 ETL プロセスは、他のプロセスの実行を調整します。通常は、ソース データのステージング、ディメンション データの同期、ファクト テーブルへの行の挿入、監査データとエラーの記録に関係します。

Fabric Warehouse ソリューションに関して、データ ファクトリ を使用して ETL プロセスを開発して実行できます。 このプロセスでは、ソース データをステージング、変換、およびディメンショナル モデル テーブルに読み込むことができます。

具体的には次のことができます。

• データ パイプライン を使用して、ETL プロセスを調整するワークフローを構築します。 データ パイプラインでは、SQL スクリプト、ストアド プロシージャなどを実行できます。
• データフロー を使用して、数百のデータ ソースからデータを取り込むロー コード ロジックを開発します。 データフローでは、複数のソースからのデータの結合、データの変換、およびディメンショナル モデル テーブルなどの変換先への読み込みがサポートされます。 データフローは、Microsoft Excel や Power BI Desktop など、多くの Microsoft 製品で現在利用できる使い慣れた Power Query エクスペリエンスを使用して構築されています。

オーケストレーション

ETL プロセスの一般的なワークフローは、次のとおりです:

1. 必要に応じて、ステージング テーブル 読み込みます。
2. ディメンション テーブルの処理。
3. ファクト テーブルの処理。
4. 必要に応じて、依存する Fabric コンテンツの更新 (セマンティック モデルなど) のきっかけとなる後処理タスクを実行します。

ディメンション テーブルは、最後の ETL プロセス以降にソース システムに追加されたものを含め、すべてのディメンション メンバーを確実に格納するために、最初に処理する必要があります。 アウトリガー ディメンション の場合と同様に、ディメンション間に依存関係がある場合、ディメンション テーブルは依存関係の順序で処理する必要があります。 たとえば、顧客ディメンションと販売店ディメンションで使用される地理ディメンションは、他の 2 つのディメンションの前に処理する必要があります。

ファクト テーブルは、すべてのディメンション テーブルが処理されると処理できます。

すべてのディメンショナル モデル テーブルが処理されると、依存セマンティック モデルの更新がトリガーされる場合があります。 また、関連タッフに通知を送信して、ETL プロセスの結果を通知することをお勧めします。

ステージ データ

ステージング ソース データは、データの読み込みと変換の要件をサポートするのに役立ちます。 これには、ソース システム データの抽出とステージング テーブルへの読み込みが含まれます。このテーブルは、ETL プロセスをサポートするために作成されます。 ソース データは次のことが可能であるため、ステージングすることをお勧めします:

• 運用システムへの影響を最小限に抑えます。
• ETL 処理を支援し、最適化するために使用します。
• ソース システムからデータを再読み込みすることなく、ETL プロセスを再起動する機能を提供します。

ステージング テーブル内のデータをビジネス ユーザーが使用できないようにする必要があります。 ETL プロセスにのみ関連します。

staging という名前のスキーマをウェアハウスに作成することをお勧めします。 ステージング テーブルは、列名とデータ型の観点から、可能な限りソース テーブルと共通点がある必要があります。 各テーブルの内容は、ETL プロセスの開始時に削除する必要があります。 ただし、Fabric Warehouse テーブル を 切り捨てることはできない ことに注意してください。 代わりに、各ステージング テーブルを削除して再作成してから、データを読み込むことができます。

ステージング戦略の一部として、データ仮想化の代替手段を検討することもできます。 使用できるもの:

• ミラーリング。これは、OneLake でデータのレプリカを作成できる、低コストで低遅延のターンキー ソリューションです。 詳細については、「Fabric でのミラーリングを使用する理由」を参照してください。
• OneLake ショートカット。これは、ソース データを含むことができる他のストレージの場所を指します。 ショートカットは、T-SQL クエリ テーブルとして使用 できます。
• SQL Server の PolyBase は、SQL Server 用のデータ仮想化機能です。 PolyBase を使用すると、T-SQL クエリで、外部ソースからのデータを SQL Server のインスタンス内のリレーショナル テーブルに結合できるようになります。
• Azure SQL Managed Instance によるデータ仮想化。これにより、Azure Data Lake Storage (ADLS) Gen2 または Azure Blob Storage に一般的なデータ形式でデータを格納するファイルに対してT-SQL クエリを実行し、結合を用いることでローカルに格納されたリレーショナル データと結合することができます。

データを変換する

ソース データの構造は、ディメンショナル モデル テーブルの変換先の構造とは異なっている場合があります。 そのため、ETL プロセスでは、ディメンショナル モデル テーブルの構造に合わせてソース データを作り直す必要があります。

また、データ ウェアハウスは浄化された適合済データを提供する必要があるため、品質と一貫性を確保するためにソース データを変換する必要がある場合があります。

ETL プロセスで実行できる変換をいくつか次に示します。

• データの結合: 一致するキーに基づいて、さまざまなソースのデータを統合 (マージ) できます。 たとえば、製品データはさまざまなシステム (製造やマーケティングなど) に保存され、すべて共通の最小在庫管理単位 (SKU) を使用します。 データは、共通の構造を共有するときに追加することもできます。 たとえば、売上データは複数のシステムに格納されます。 各システムからの販売数の和集合は、すべての販売データの上位集合を生成できます。
• データ型の変換: データ型は、ディメンショナル モデル テーブルで定義されているものに変換できます。
• 計算: 計算は、ディメンショナル モデル テーブルの値を生成するために実行できます。 たとえば、従業員ディメンション テーブルの場合、姓と名を連結してフル ネームを生成できます。 別の例として、販売ファクト テーブルでは、単価と数量の積である総売上高を計算できます。
• 履歴変更を検出して管理する: 変更を検出し、ディメンション テーブルに適切に格納できます。 詳細については、この記事で後述する「履歴変更の管理」を参照してください。
• 集計データ: 集計を使用して、ファクト テーブルの次元性を減らしたり、ファクトの細分性を上げたりすることができます。 たとえば、販売注文番号を格納する必要はありません。 したがって、すべてのディメンション キーによってグループ化された集計結果を使用して、ファクト テーブル データを格納できます。

データの読み込み

次の データ インジェスト オプション を使用して、Fabric Warehouse にテーブルを読み込むことができます。

• COPY INTO (T-SQL): このオプションは、ソース データが外部の Azure ストレージ アカウントに格納されている Parquet または CSV ファイル (ADLS Gen2、Azure Blob Storage など) で構成される場合に便利です。
• データ パイプライン: ETL プロセスの調整に加えて、データ パイプラインには、T-SQL ステートメントを実行するアクティビティ、検索を実行するアクティビティ、データ ソースから貼り付け先にデータをコピーするアクティビティを含めることができます。
• データフロー: パイプラインの代わりに、データフローを使用して、データを変換し、クリーニングするためのコードフリーのエクスペリエンスを行います。
• クロスウェアハウス インジェスト: データが同じワークスペースに格納されている場合、クロスウェアハウス インジェストでは、異なるウェアハウステーブルまたはレイクハウス テーブルを結合できます。 INSERT…SELECT、SELECT INTO、CREATE TABLE AS SELECT (CTAS)などの T-SQL コマンドをサポートしています。 これらのコマンドは、同じワークスペース内のステージング テーブルからデータを変換して読み込む場合に特に便利です。 また、これらはセット ベースの操作であり、ディメンショナル モデル テーブルを読み込む最も効率的で最速の方法である可能性が高くなります。

ログ機能

ETL プロセスには、通常、専用の監視とメンテナンスが必要です。 このような理由から、ETL プロセスの結果をウェアハウス内の非ディメンショナル モデル テーブルに記録することをお勧めします。 ETL プロセスごとに一意の ID を生成し、それを使用してすべての操作の詳細をログに記録する必要があります。

ログ記録を検討してください:

• ETL プロセス:
  • 各 ETL 実行ための一意の ID
  • [開始時刻および終了時刻]
  • 状態 (成功または失敗)
  • 発生したすべてのエラー
• 各ステージング モデル テーブルとディメンション モデル テーブル:
  • [開始時刻および終了時刻]
  • 状態 (成功または失敗)
  • 挿入、更新、および削除された行
  • 最終テーブルの行数
  • 発生したすべてのエラー
• その他の操作:
  • セマンティック モデルの更新操作の開始時刻と終了時刻

ディメンション テーブルの処理

ディメンション テーブルの処理には、データ ウェアハウス データとソース システムの同期が含まれます。 ソース データは最初に変換され、ディメンション テーブルに読み込むための準備が行われます。 このデータは、ビジネス キーで結合することで、既存のディメンション テーブル データと照合されます。 その後、ソース データが新しいデータまたは変更されたデータを表しているかどうかを判断できます。 ディメンション テーブルが、ゆっくり変化するディメンション (SCD) タイプ 1 を適用すると、既存のディメンション テーブルの行を更新することによって変更が行われます。 テーブルが SCD タイプ 2 の変更を適用すると、既存のバージョンの有効期限が切れ、新しいバージョンが挿入されます。

次の図は、ディメンション テーブルの処理に使用されるロジックを示しています。

Product ディメンション テーブルのプロセスについて考えてみましょう。

• 新しい製品がソース システムに追加されると、Productディメンション テーブルに行が挿入されます。
• 製品が変更されると、ディメンション テーブル内の既存の行が更新または挿入されます。
  • SCD タイプ 1 が適用されると、既存の行が更新されます。
  • SCD タイプ 2 が適用されると、現在の行バージョンが 失効する 更新が行われ、現在のバージョンを表す新しい行が挿入されます。
  • SCD タイプ 3 が適用されると、SCD タイプ 1 のようなプロセスが発生し、新しい行を挿入せずに既存の行が更新されます。

代理キー

各ディメンション テーブルには、可能な限り最小の整数型を使用する 代理キー を用意することをお勧めします。 SQL Server ベースの環境では、ID 列を作成することによってこれを行うのが一般的ですが、この機能はFabric Warehouseではサポートされていません。 代わりに、一意識別子を生成する 回避策の技術 を使用する必要があります。

履歴変更の管理

ディメンション テーブルで 履歴変更を格納する 必要がある場合、ゆっくり変化するディメンション (SCD) を実装する必要があります。

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

SCD タイプ 1

SCD タイプ 1 の変更が検出された場合は、次のロジックを使用します。

1. 変更された属性を更新します。
2. テーブルに [最終変更日] 列および [最終更新時刻] 列が含まれる場合は、変更を加えた現在の日付とプロセスを設定します。

SCD タイプ 2

SCD タイプ 2 の変更が検出された場合は、次のロジックを使用します。

1. 終了日の有効期間列を ETL 処理日 (またはソース システムの適切なタイムスタンプ) に設定し、現在のフラグを FALSE に設定することで、現在のバージョンを期限切れにします。
2. テーブルに [最終変更日] 列および [最終更新時刻] 列が含まれる場合は、変更を加えた現在の日付とプロセスを設定します。
3. 開始日の有効期間列が、終了日の有効期間列の値 (以前のバージョンの更新に使用) に設定され、現在のバージョン フラグが TRUE に設定されている新しいメンバーを挿入します。
4. テーブルに [作成日] 列および [作成時刻] 列が含まれる場合、挿入を行った現在の日付とプロセスを設定します。

SCD タイプ 3

SCD タイプ 3 変更が検出された場合は、SCD タイプ 1 の処理 と同様のロジックを使用して属性を更新します。

ディメンション メンバーの削除

ソース データがディメンション メンバーが削除されたことを示す場合は注意してください (ソース システムから取得されていないか、削除済みのフラグが設定されているため)。 ディメンション メンバーがエラーで作成され、関連するファクト レコードがない場合を除き、削除をディメンション テーブルと同期しないでください。

ソースの削除を処理する適切な方法は、論理的な削除 として記録することです。 論理的な削除では、ディメンション メンバーがアクティブまたは有効でなくなったものとしてマークされます。 このケースをサポートするには、ディメンション テーブルに、IsDeletedなどの ビット データ型を持つブール値の属性を含める必要があります。 削除されたディメンション メンバーのこの列を TRUE (1) に更新します。 ディメンション メンバーの現在の最新バージョンも、IsCurrentまたは IsActive 列にブール値 (ビット) 値でマークされている場合があります。 すべてのレポート クエリと Power BI セマンティック モデルは、論理的な削除であるレコードを除去する必要があります。

日付ディメンション

カレンダーディメンションと時間ディメンション は、通常はソース データがないため、特殊なケースです。 代わりに、固定ロジックを使用して生成されます。

毎年の年初に 日付ディメンション テーブルを読み込み、その行を特定の年数まで拡張する必要があります。 その他のビジネス データ (会計年度データ、休日、週番号など) が定期的に更新される場合があります。

日付ディメンション テーブルに相対変位属性が含まれている場合、現在の日付 (今日) に基づいてオフセット属性値を更新するには、ETL プロセスを毎日実行する必要があります。

日付ディメンション テーブルを拡張または更新するロジックは、T-SQL で記述し、ストアド プロシージャにカプセル化することをお勧めします。

ファクト テーブルの処理

ファクト テーブルの処理には、データ ウェアハウス データとソース システムのファクトの同期が含まれます。 ソース データは最初に変換され、ファクト テーブルに読み込むための準備が行われます。 次に、ディメンション キーごとに、ファクト行に格納する代理キー値が検索によって決定されます。 ディメンションが SCD タイプ 2 をサポートしている場合は、ディメンション メンバーの 現在のバージョン の代理キーを取得する必要があります。

ディメンション キーの検索が失敗した場合は、ソース システムの整合性の問題を示している可能性があります。 この場合も、ファクト行をファクト テーブルに挿入する必要があります。 有効なディメンション キーは、引き続き格納する必要があります。 1 つの方法は、特殊なディメンション メンバー ([未確認] など) を格納することです。 この方法では、既知の場合、真のディメンション キー値を正しく割り当てるために、後で更新する必要があります。

ナチュラル キー が有効であるという確信がある場合に関連する、他の方法は、新しいディメンション メンバーを挿入し、その代理キー値を格納することです。 詳細については、このセクションで後述する「予想ディメンション メンバー」を参照してください。

次の図は、ファクト テーブルの処理に使用されるロジックを示しています。

可能な限り、ファクト テーブルを段階的に読み込む必要があります。つまり、新しいファクトが検出されて挿入されます。 増分読み込みの戦略は拡張性が高く、ソース システムと移行先システムの両方のワークロードが軽減されます。

できれば、ソース システム識別子またはタイムスタンプに依存することで、新しいファクトを効率的に検出できます。 たとえば、ソース システムが順番に販売注文を確実に記録する場合、取得した最新の販売注文番号 (高基準値と呼ばれます) を格納できます。 次のプロセスでは、その販売注文番号を使用して新しく作成された販売注文を取得できます。また、次のプロセスで使用するために取得された最新の販売注文番号を格納します。 また、作成日 列を使用して、新しい注文を確実に検出できる可能性もあります。

ソース システム データに依存して新しいファクトを効率的に検出できない場合は、ソース システムの機能に依存して増分読み込みを実行できる可能性があります。 たとえば、SQL Server と Azure SQL Managed Instance には、テーブル内の各行の変更を追跡できる 変更データ キャプチャ (CDC) と呼ばれる機能があります。 また、SQL Server、Azure SQL Managed Instance、Azure SQL Database には、変更された行を識別できる 変更の追跡 と呼ばれる機能があります。 有効にすると、任意のデータベース テーブル内の新しいデータまたは変更されたデータを効率的に検出できます。 挿入、更新、または削除されたテーブル レコードのキーを格納するトリガーをリレーショナル テーブルに追加することもできます。

最後に、属性を使用して、ソース データをファクト テーブルに関連付けることができます。 たとえば、販売注文番号と販売注文明細行番号です。 ただし、大規模なファクト テーブルの場合、新しいファクト、変更されたファクト、または削除されたファクトを検出するのは非常に費用のかかる操作になる可能性があります。 また、ソース システムが運用データをアーカイブする場合にも問題が発生する可能性があります。

予想ディメンション メンバー

ファクトの読み込みプロセスが新しいディメンション メンバーを挿入する場合、これは 予想メンバー として知られています。 たとえば、ホテルのゲストがチェックインすると、ホテル チェーンにロイヤルティ メンバーに加入するように求められます。 会員番号は直ちに発行されますが、ゲストが書類を提出するまで、ゲストの詳細は分からない場合があります (分かることの方が稀です)。

ディメンション メンバーについて知られているのは、そのナチュラル キーです。 ファクト ロード プロセスでは、未確認 属性値を使用して新しいディメンション メンバーを作成する必要があります。 重要なのは、IsInferredMember [監査の属性] を TRUE に設定することです。 こうすることで、到着の遅延に関する詳細がソース化されると、ディメンションの読み込みプロセスでディメンション行に必要な更新を行うことができます。 詳細については、この記事の「履歴変更の管理」を参照してください。

ファクトの更新または削除

ファクト データの更新または削除が必要になる場合があります。 たとえば、販売注文が取り消された場合や、注文数量が変更された場合などです。 ファクト テーブルの読み込みについて前述したように、変更を効率的に検出し、ファクト データに対して適切な変更を実行する必要があります。 取り消された注文のこの例では、販売注文の状態が オープン から キャンセル済 に変更される可能性があります。 この変更には、行の 削除 ではなく、ファクト データの 更新 が必要になります。 数量変更の場合は、ファクト行数量の測定値の更新が必要になります。 論理的な削除 を使用するこの戦略では履歴が保持されます。 論理的な削除では、行がアクティブまたは有効ではなくなったとマークされます。また、すべてのレポート クエリと Power BI セマンティック モデルは、論理的な削除であるレコードを除外する必要があります。

ファクトの更新または削除が予想される場合は、変更するファクト行を識別するのに役立つ属性 (販売注文番号とその販売注文明細行番号など) をファクト テーブルに含める必要があります。 効率的な変更操作をサポートするには、これらの列のインデックスを作成してください。

最後に、特殊なディメンション メンバー (未確認 など) を使用してファクト データを挿入した場合は、このようなファクト行の現在のソース データを取得し、ディメンション キーを有効な値に更新する定期的なプロセスを実行する必要があります。

関連するコンテンツ

Fabric Warehouse へのデータの取得の詳細については、以下を参照してください:

• ウェアハウス内にデータを取り込む
• データ パイプラインを使用してウェアハウスにデータを取り込む
• COPY ステートメントを使用してウェアハウスにデータを取り込む
• Transact-SQL を使用してウェアハウスにデータを取り込む
• チュートリアル: dbt を Fabric Warehouse 用に設定する