dimensional data model data warehouse tutorial with examples
このチュートリアルでは、データウェアハウスにおけるディメンションデータモデルの利点と神話について説明します。また、例を使用してディメンションテーブルとファクトテーブルについて学習します。
データウェアハウスのテスト 以前のチュートリアルで説明されました すべての人のためのデータウェアハウストレーニングシリーズ 。
膨大なデータは、ディメンションデータモデリング技術を使用してデータウェアハウス(DW)に編成されます。これらのディメンションデータモデリング手法により、エンドユーザーの仕事はビジネスデータについて非常に簡単に問い合わせることができます。このチュートリアルでは、DWのディメンションデータモデルについてすべて説明します。
ターゲットオーディエンス
- データウェアハウス/ ETL開発者およびテスター。
- データベースの概念に関する基本的な知識を持つデータベースの専門家。
- データウェアハウス/ ETLの概念を理解したいデータベース管理者/ビッグデータの専門家。
- データウェアハウスの仕事を探している大学卒業生/フレッシャーズ。
学習内容:
次元データモデル
ディメンションデータモデルは、ETLフローのエンドユーザーがデータをクエリおよび分析するために使用できるデータ構造です。 ETLプロセスは、最終的にターゲットのディメンションデータモデルにデータをロードします。すべてのディメンションデータモデルは、複数のディメンションテーブルに囲まれたファクトテーブルで構築されます。
ディメンションデータモデルを設計する際に従うべき手順:
次元データモデリングの利点
以下に、ディメンションデータモデリングのさまざまな利点を示します。
- それらは、絶えず変化するDW環境を使用するために保護されています。
- 次元データモデルを使用すると、膨大なデータを簡単に構築できます。
- 次元データモデルからのデータは、理解と分析が容易です。
- エンドユーザーは、高性能でクエリを実行するためにすばやくアクセスできます。
- ディメンションデータモデルを使用すると、データを階層的にドリルダウン(または)ロールアップできます。
ERモデリングと次元データモデリング
- ERモデリングは運用システムに適していますが、次元モデリングはデータウェアハウスに適しています。
- ERモデリングは詳細な現在のトランザクションデータを維持しますが、次元モデリングは現在と過去のトランザクションデータの両方の要約を維持します。
- ERモデリングではデータが正規化されていますが、次元モデリングではデータが非正規化されています。
- ERモデリングでは、クエリの取得時に使用する結合の数が多くなりますが、次元モデリングでは使用する結合の数が少なくなるため、次元モデリングではクエリのパフォーマンスが向上します。
次元データモデリングの神話
以下に示すのは、既存の次元データモデリングの神話の一部です。
- ディメンションデータモデルは、データの要約を表すためにのみ使用されます。
- それらは組織内の部門固有です。
- それらはスケーラビリティをサポートしていません。
- これらは、エンドユーザーのレポートとクエリの目的に役立つように設計されています。
- ディメンションデータモデルを統合することはできません。
ディメンションテーブル
ディメンションテーブルは、分析されたすべてのメトリック値を格納することにより、DWシステムで重要な役割を果たします。これらの値は、テーブル内の簡単に選択できるディメンション属性(列)の下に格納されます。 DWシステムの品質は、主にディメンション属性の深さに依存します。
したがって、ディメンションテーブルにそれぞれの値とともに多くの属性を提供するように努める必要があります。
ディメンションテーブルの構造を調べてみましょう。
#1)ディメンションテーブルキー: すべてのディメンションテーブルには、各行を一意に識別するための主キーとして、そのディメンション属性のいずれかがあります。したがって、その属性の個別の数値は主キーとして機能できます。
いずれの場合も属性値が一意でない場合は、順次生成されるシステム番号を主キーと見なすことができます。これらは代理キーとも呼ばれます。
ディメンションデータモデルには、ディメンションとファクトの間の各キーの参照整合性制約が必要です。したがって、ファクトテーブルには、参照整合性を維持するために、ディメンションテーブルの各プライマリ/サロゲートキーの外部キー参照があります。
失敗した場合、そのディメンションキーのそれぞれのファクトテーブルデータを取得できません。
#2)テーブルが広い: DWサイクルの任意の時点でディメンションテーブルに任意の数の属性を追加できるため、ディメンションテーブルは広いと言えます。 DWアーキテクトは、ETLチームにそれぞれの新しい属性をスキーマに追加するように要求します。
リアルタイムのシナリオでは、50(または)以上の属性を持つディメンションテーブルを表示できます。
#3)テキスト属性: 寸法属性は、テキスト(または)数値が望ましいなど、任意のタイプにすることができます。テキスト属性には、コードではなく実際のビジネス用語が含まれます。ディメンションテーブルは計算用ではないため、ディメンション属性に数値が使用されることはめったにありません。
#4)属性は直接関連していない可能性があります: ディメンションテーブルのすべての属性が相互に関連しているとは限りません。
#5)正規化されていません: ディメンションテーブルを正規化すると、より多くの中間テーブルが画像に取り込まれますが、これは効率的ではありません。したがって、ディメンションテーブルは正規化されません。
ディメンション属性は、クエリの制約のソースとして機能し、レポートにラベルとして表示することもできます。ディメンションテーブルから属性を直接選択し、他の中間テーブルに触れることなくそれぞれのファクトテーブルを直接参照すると、クエリが効率的に実行されます。
#6)ドリルダウンとロールアップ: ディメンション属性には、必要に応じてデータをドリルダウン(または)ロールアップする機能があります。
#7)複数の階層: 複数の階層を持つ単一のディメンションテーブルは非常に一般的です。最下位レベルから最上位へのパスが1つしかない場合、ディメンションテーブルは単純な階層になります。同様に、最下位レベルから最上位に到達するために複数のパスが存在する場合、複数の階層があります。
#8)いくつかのレコード: ディメンションテーブルのレコード数(数百)は、ファクトテーブル(数百万)よりも少なくなります。それらはファクトよりも小さいですが、ファクトテーブルへのすべての入力を提供します。
顧客ディメンションテーブルの例を次に示します。
上記の概念を理解することにより、ソース自体からデータを抽出している間ではなく、データフィールドがディメンション属性として機能できるかどうかを判断できます。
寸法の基本的な荷重計画
ディメンションは2つの方法で作成できます。つまり、外部ソースシステムからディメンションデータを抽出することによって(または)ETLシステムは、外部ソースを使用せずにステージングからディメンションを構築できます。ただし、ディメンションテーブルの作成には、外部処理のないETLシステムの方が適しています。
このプロセスに含まれる手順は次のとおりです。
どこで無料のアニメをオンラインで見ることができますか
- データクリーニング: 一貫性を維持するために、ディメンションテーブルにロードする前に、データがクリーンアップされ、検証され、ビジネスルールが適用されます。
- データ適合: データウェアハウスの他の部分からのデータは、ディメンションテーブルの各フィールドに関して、単一の値として適切に集計する必要があります。
- 同じドメインを共有する: データが確認されると、ステージングテーブルに再度保存されます。
- データ配信: 最後に、すべてのディメンション属性値に、割り当てられたプライマリ/サロゲートキーが読み込まれます。
寸法の種類
参考までに、さまざまなタイプの寸法を以下に示します。
はじめましょう!!
#1)小さい寸法
データウェアハウスの小さなディメンションは、行と列の数が少ないルックアップテーブルとして機能します。小さなサイズのデータは、スプレッドシートから簡単に読み込むことができます。必要に応じて、小さな寸法をスーパー寸法として組み合わせることができます。
#2)適合寸法
適合ディメンションは、関連するすべてのファクトテーブルで同じ方法で参照できるディメンションです。
日付ディメンションは、年、月、週、日などの日付ディメンションの属性が任意の数のファクトにわたって同じ方法で同じデータを伝達するため、適合ディメンションの最良の例です。
適合寸法の例。
#3)ジャンク寸法
フラグやインジケーターなどのファクトテーブルのいくつかの属性は、別のジャンクディメンションテーブルに移動できます。これらの属性は、他の既存のディメンションテーブルにも属していません。一般に、これらの属性の値は、単に「はい/いいえ」(または)「真/偽」です。
個々のフラグ属性ごとに新しいディメンションを作成すると、ファクトテーブルへの外部キーの数が増えるため、複雑になります。同時に、これらすべてのフラグとインジケーター情報をファクトテーブルに保持すると、ファクトに格納されるデータの量も増え、パフォーマンスが低下します。
したがって、これに対する最善の解決策は、単一のジャンクディメンションを作成することです。ジャンクディメンションは、任意の数の「はい/いいえ」または「真/偽」のインジケータを保持できるためです。ただし、ジャンクディメンションには、アクティブおよび保留中など、これらのインジケーターの記述値(yes / no(または)true / false)が格納されます。
ファクトテーブルとそのインジケーターの複雑さに基づいて、ファクトテーブルは1つ以上のジャンクディメンションを持つことができます。
ジャンク寸法の例。
#4)ロールプレイングの側面
ファクトテーブルで複数の目的で参照できる単一のディメンションは、ロールプレイングディメンションと呼ばれます。
ロールプレイングディメンションの最良の例は、日付ディメンションテーブルです。これは、ディメンション内の同じ日付属性を、注文日、配達日、取引日、キャンセル日などのさまざまな目的に使用できるためです。等
必要に応じて、ファクトテーブルの4つの異なる日付属性に関して、日付ディメンションテーブルに4つの異なるビューを作成できます。
ロールプレイングディメンションの例。
#5)縮退した寸法
ディメンション(メトリック)でもファクト(メジャー)でもない属性はほとんどないかもしれませんが、分析が必要です。このような属性はすべて、縮退したディメンションに移動できます。
例えば、 注文番号、請求書番号などは、縮退したディメンション属性と見なすことができます。
縮退ディメンションの例。
#6)ゆっくりと変化する寸法
ゆっくりと変化するディメンションは、データが定期的な定期的な間隔ではなく、いつでもゆっくりと変化する可能性がある種類です。ディメンションテーブルの変更されたデータは、以下で説明するようにさまざまな方法で処理できます。
SCDタイプを選択して、ディメンションテーブルの属性ごとに個別に変更に対応できます。
(i)タイプ1 SCD
- タイプ1では、ディメンション属性の値に変更があると、既存の値が新しく変更された値で上書きされます。これは更新にすぎません。
- 古いデータは、履歴参照のために維持されません。
- 古いデータが存在しないため、過去のレポートを再生成することはできません。
- メンテナンスが簡単。
- ファクトテーブルへの影響はさらに大きくなります。
タイプ1SCDの例:
(ii)タイプ2 SCD
- タイプ2では、ディメンション属性の値が変更されると、古い行データを変更せずに、変更された値で新しい行が挿入されます。
- ファクトテーブルのいずれかに古いレコードへの外部キー参照が存在する場合、古いサロゲートキーは、新しいサロゲートキーでどこでも自動的に更新されます。
- 上記の手順を使用すると、ファクトテーブルの変更への影響は非常に少なくなります。
- 変更後、古いデータはどこにも考慮されません。
- タイプ2では、ディメンション属性に発生しているすべての変更を追跡できます。
- 履歴データの保存に制限はありません。
- タイプ2では、変更日、発効日時、終了日時、変更の理由、現在のフラグなど、各行にいくつかの属性を追加することはオプションです。ただし、企業が特定の期間に行われた変更の数を知りたい場合、これは重要です。
タイプ2SCDの例:
(iii)タイプ3 SCD
- タイプ3では、ディメンション属性の値が変更されると、新しい値が更新されますが、2番目のオプションとして古い値が引き続き有効です。
- 変更ごとに新しい行を追加する代わりに、以前に存在していなかった場合は新しい列が追加されます。
- 上記で追加した属性には古い値が配置され、タイプ1と同様に、プライマリ属性のデータが変更された値で上書きされます。
- 履歴データの保存には制限があります。
- ファクトテーブルへの影響はさらに大きくなります。
タイプ3SCDの例:
(iv)タイプ4 SCD
- タイプ4では、現在のデータが1つのテーブルに格納されます。
- すべての履歴データは別のテーブルに保持されます。
タイプ4SCDの例:
(v)タイプ6 SCD
- ディメンションテーブルには、3つのSCDタイプ1、2、および3をすべて組み合わせて使用することもできます。これは、タイプ6(または)ハイブリッドのゆっくりと変化するディメンションとして知られています。
ファクトテーブル
ファクトテーブルには、計算に使用される一連の定量的に測定された値が格納されます。ファクトテーブルの値はビジネスレポートに表示されます。ディメンションテーブルのテキストデータ型とは対照的に、ファクトテーブルのデータ型はかなり数値です。
ファクトテーブルは行数が多く、列数が少ないため、ファクトテーブルは深く、ディメンションテーブルは広くなります。ファクトテーブルで定義されている主キーは、主に各行を個別に識別することです。主キーは、ファクトテーブルでは複合キーとも呼ばれます。
ファクトテーブルに複合キーがなく、2つのレコードに同じデータがある場合、データを区別してディメンションテーブルでデータを参照することは非常に困難です。
したがって、適切な一意のキーが複合キーとして存在する場合は、ファクトテーブルレコードごとにシーケンス番号を生成することをお勧めします。別の方法は、連結された主キーを形成することです。これは、ディメンションテーブルの参照されているすべての主キーを行ごとに連結することによって生成されます。
1つのファクトテーブルを複数のディメンションテーブルで囲むことができます。ファクトテーブルに存在する外部キーを使用して、測定値のそれぞれのコンテキスト(詳細データ)をディメンションテーブルで参照することができます。クエリの助けを借りて、ユーザーはドリルダウンとロールアップを効率的に実行します。
ファクトテーブルに格納できる最低レベルのデータは、粒度と呼ばれます。ファクトテーブルに関連付けられているディメンションテーブルの数は、そのファクトテーブルデータの粒度に反比例します。つまり、最小の測定値を参照するには、より多くのディメンションテーブルが必要です。
ディメンションモデルでは、ファクトテーブルはディメンションテーブルとの多対多の関係を維持します。
セールスファクトテーブルの例:
ファクトテーブルのロードプラン
次のポインタを考慮することにより、ファクトテーブルデータを効率的にロードできます。
#1)インデックスの削除と復元
実際のテーブルのインデックスは、データのクエリ中のパフォーマンスを向上させますが、データのロード中のパフォーマンスを破壊します。したがって、ファクトテーブルに巨大なデータをロードする前に、主にそのテーブルのすべてのインデックスを削除し、データをロードしてインデックスを復元します。
#2)挿入を更新から分離する
ファクトテーブルへのロード中に、挿入レコードと更新レコードをマージしないでください。更新の数が少ない場合は、挿入と更新を別々に処理します。更新の数が多い場合は、ファクトテーブルを切り捨てて再読み込みし、迅速な結果を得ることをお勧めします。
#3)パーティショニング
ファクトテーブルのデータに対するクエリのパフォーマンスを向上させるために、ファクトテーブルを物理的にミニテーブルに分割します。 DBAとETLチームを除いて、事実上のパーティションを誰も認識しません。
として 例 、テーブルを月ごと、四半期ごと、年ごとなどに分割できます。クエリ中は、テーブル全体をスキャンするのではなく、分割されたデータのみが考慮されます。
#4)並列ロード
4年の経験のためのセレンウェブドライバーインタビューの質問
これで、ファクトテーブルのパーティションについてのアイデアが得られました。ファクトのパーティション化は、ファクトに巨大なデータをロードする際にも役立ちます。これを行うには、まず、データを論理的に異なるデータファイルに分割し、ETLジョブを実行して、データのこれらすべての論理部分を並列にロードします。
#5)バルクロードユーティリティ
他のRDBMSシステムとは異なり、ETLシステムは、トランザクションの途中で障害が発生した場合にロールバックログを明示的に維持する必要はありません。ここでは、巨大なデータをロードするための「SQL挿入」ではなく、「バルクロード」がファクトに発生します。単一のロードが失敗した場合、データ全体を簡単にリロードできます(または)バルクロードで中断したところからデータ全体を続行できます。
#6)ファクトレコードの削除
ファクトテーブルレコードの削除は、ビジネスが明示的に必要とする場合にのみ発生します。ソースシステムに存在しなくなったファクトテーブルデータがある場合、そのそれぞれのデータは物理的に(または)論理的に削除できます。
- 物理的な削除: 不要なレコードはファクトテーブルから完全に削除されます。
- 論理的削除: ビット(または)ブール型の「削除済み」などの新しい列がファクトテーブルに追加されます。これは、削除されたレコードを表すフラグとして機能します。ファクトテーブルデータのクエリ中に、削除されたレコードを選択していないことを確認する必要があります。
#7)ファクトテーブルの更新と削除のシーケンス
更新するデータがある場合は、最初にディメンションテーブルを更新してから、必要に応じてルックアップテーブルの代理キーを更新し、その後、それぞれのファクトテーブルを更新する必要があります。ファクトテーブルからすべての不要なデータを削除すると、リンクされた不要なデータをディメンションテーブルから簡単に削除できるため、削除は逆に行われます。
ディメンションテーブルとファクトテーブルは常に参照整合性を維持するため、どちらの場合も上記の順序に従う必要があります。
事実の種類
ファクトテーブルデータの動作に基づいて、トランザクションファクトテーブル、スナップショットファクトテーブル、および累積スナップショットファクトテーブルに分類されます。これら3つのタイプはすべて、異なるデータロード戦略で異なる機能に従います。
#1)トランザクションファクトテーブル
名前が示すように、トランザクションファクトテーブルには、発生する各イベントのトランザクションレベルのデータが格納されます。このような種類のデータは、ファクトテーブルレベル自体で簡単に分析できます。ただし、さらに分析するために、関連するディメンションを参照することもできます。
例えば、 マーケティングWebサイトから発生するすべての販売(または)購入は、トランザクションファクトテーブルにロードする必要があります。
トランザクションファクトテーブルの例を以下に示します。
#2)定期的なスナップショットファクトテーブル
名前が示すように、周期表のデータは、ビジネスニーズに応じて、毎日、毎週、毎月、四半期などの定期的な間隔でスナップショット(画像)の形式で保存されます。
したがって、これが常にデータの集約であることは明らかです。したがって、スナップショットファクトはトランザクションファクトテーブルと比較してより複雑です。 例えば、 パフォーマンス収益レポートのデータは、簡単に参照できるようにスナップショットファクトテーブルに保存できます。
周期表の例を以下に示します。
#3)スナップショットファクトテーブルの蓄積
スナップショットファクトテーブルを蓄積すると、製品の存続期間全体にわたってデータをテーブルに保存できます。これは、上記の2つのタイプの組み合わせとして機能し、任意のイベントによっていつでもスナップショットとしてデータを挿入できます。
このタイプでは、各行の追加の日付列とデータが、その製品のマイルストーンごとに更新されます。
スナップショットファクトテーブルの累積の例。
上記の3つのタイプに加えて、他のいくつかのタイプのファクトテーブルがあります。
#4)ファクトレスファクトテーブル: ファクトはメジャーのコレクションですが、ファクトlessは、メジャーを含まないイベント(または)条件のみをキャプチャします。ファクトのないファクトテーブルは、主にシステムを追跡するために使用されます。これらの表のデータを分析して、レポートに使用できます。
例えば、 休暇を取得した従業員の詳細や1年の休暇の種類などを検索できます。これらの不明確なファクトの詳細をすべてファクトに含めると、テーブルによってファクトのサイズが確実に大きくなります。
ファクトレスファクトテーブルの例を以下に示します。
#5)適合ファクトテーブル: 適合ファクトとは、関連するすべてのデータマートで同じ方法で参照できるファクトです。
ファクトテーブルの仕様
ファクトテーブルの仕様は次のとおりです。
- 事実名: これは、ファクトテーブルの機能を簡単に説明する文字列です。
- ビジネスプロセス: ビジネスについての話し合いは、そのファクトテーブルによって実行される必要があります。
- 質問: そのファクトテーブルによって回答されるビジネス上の質問のリストに言及します。
- 粒: そのファクトテーブルデータに関連付けられている最低レベルの詳細を示します。
- 寸法: そのファクトテーブルに関連付けられているすべてのディメンションテーブルを一覧表示します。
- 対策: ファクトテーブルに保存されている計算値。
- 負荷頻度 データをファクトテーブルにロードする時間間隔を表します。
- 最初の行: ファクトテーブルに初めて入力された初期データを参照してください。
次元データモデリングの例
以下の販売と注文のディメンションデータモデリング図を見ると、システムのディメンションテーブルとファクトテーブルをどのように設計できるかがわかります。
結論
これまでに、ディメンションデータモデリング手法、その利点、神話、ディメンションテーブル、ファクトテーブル、およびそれらのタイプとプロセスに関する優れた知識を習得しているはずです。
データウェアハウススキーマの詳細については、今後のチュートリアルをご覧ください。
=> ゼロからデータウェアハウジングを学ぶには、こちらにアクセスしてください。