schema types data warehouse modeling star snowflake schema
このチュートリアルでは、さまざまなデータウェアハウススキーマタイプについて説明します。スタースキーマとスノーフレークスキーマとは何か、スタースキーマとスノーフレークスキーマの違いを学ぶ：
これで 初心者向けの日付ウェアハウスチュートリアル 、私たちは詳細に調べました データウェアハウスのディメンションデータモデル 前のチュートリアルで。
このチュートリアルでは、データマート（または）データウェアハウステーブルの構造化に使用されるデータウェアハウススキーマについてすべて学習します。
ソフトウェアテストの面接の質問と回答を新鮮に
はじめましょう！！
ターゲットオーディエンス
- データウェアハウス/ ETL開発者およびテスター。
- データベースの概念に関する基本的な知識を持つデータベースの専門家。
- データウェアハウス/ ETL領域を理解したいデータベース管理者/ビッグデータの専門家。
- データウェアハウスの仕事を探している大学卒業生/フレッシャーズ。
学習内容：
データウェアハウススキーマ
データウェアハウスでは、スキーマを使用して、すべてのデータベースエンティティ（ファクトテーブル、ディメンションテーブル）とそれらの論理的な関連付けを使用してシステムを編成する方法を定義します。
DWのさまざまなタイプのスキーマは次のとおりです。
- スタースケジュール
- SnowFlakeスキーマ
- 銀河図
- 星団スキーマ
＃1）スタースケジュール
これは、データウェアハウスで最も単純で最も効果的なスキーマです。複数のディメンションテーブルに囲まれた中央のファクトテーブルは、スタースキーマモデルのスターに似ています。
ファクトテーブルは、すべてのディメンションテーブルと1対多の関係を維持します。ファクトテーブルのすべての行は、外部キー参照を使用してディメンションテーブルの行に関連付けられています。
上記の理由により、このモデルのテーブル間のナビゲーションは、集計データのクエリに簡単に使用できます。エンドユーザーはこの構造を簡単に理解できます。したがって、すべてのビジネスインテリジェンス（BI）ツールは、スタースキーマモデルを大幅にサポートします。
スタースキーマを設計する際、ディメンションテーブルは意図的に非正規化されます。それらは広く、より良い分析とレポートのためにコンテキストデータを保存するための多くの属性を備えています。
スタースキーマの利点
- クエリはデータの取得中に非常に単純な結合を使用するため、クエリのパフォーマンスが向上します。
- レポート用のデータをいつでも、どの期間でも簡単に取得できます。
スタースキーマのデメリット
- 要件に多くの変更がある場合、既存のスタースキーマを変更して長期的に再利用することはお勧めしません。
- テーブルが階層的に分割されていないため、データの冗長性はさらに高くなります。
スタースキーマの例を以下に示します。
スタースキーマのクエリ
エンドユーザーは、ビジネスインテリジェンスツールを使用してレポートを要求できます。このようなリクエストはすべて、「SELECTクエリ」のチェーンを内部で作成することによって処理されます。これらのクエリのパフォーマンスは、レポートの実行時間に影響を与えます。
上記のスタースキーマの例から、ビジネスユーザーが2018年1月にケララ州で販売された小説とDVDの数を知りたい場合は、スタースキーマテーブルに次のようにクエリを適用できます。
SELECT pdim.Name Product_Name, Sum (sfact.sales_units) Quanity_Sold FROM Product pdim, Sales sfact, Store sdim, Date ddim WHERE sfact.product_id = pdim.product_id AND sfact.store_id = sdim.store_id AND sfact.date_id = ddim.date_id AND sdim.state = 'Kerala' AND ddim.month = 1 AND ddim.year = 2018 AND pdim.Name in (‘Novels’, ‘DVDs’) GROUP BY pdim.Name 結果：
| 商品名 | 数量_販売済み | |
|---|---|---|
| 7 | 誰でも簡単にスキーマを理解して設計できます。 | スキーマを理解して設計するのは困難です。 |
| 小説 | 12,702 | |
| DVD | 32,919 |
スタースキーマのクエリがいかに簡単かを理解していただければ幸いです。
＃2）スノーフレークスキーマ
スタースキーマは、SnowFlakeスキーマを設計するための入力として機能します。スノーフレーキングは、スタースキーマからすべてのディメンションテーブルを完全に正規化するプロセスです。
ディメンションテーブルの複数の階層に囲まれた中央のファクトテーブルの配置は、SnowFlakeスキーマモデルのSnowFlakeのように見えます。すべてのファクトテーブルの行は、外部キー参照を使用してディメンションテーブルの行に関連付けられています。
SnowFlakeスキーマを設計する際、ディメンションテーブルは意図的に正規化されます。親属性にリンクするために、外部キーがディメンションテーブルの各レベルに追加されます。 SnowFlakeスキーマの複雑さは、ディメンションテーブルの階層レベルに正比例します。
SnowFlakeスキーマの利点：
- 新しいディメンションテーブルを作成することで、データの冗長性が完全に排除されます。
- スタースキーマと比較すると、SnowFlakingディメンションテーブルで使用されるストレージスペースが少なくなります。
- Snow Flakingテーブルを更新（または）維持するのは簡単です。
SnowFlakeスキーマのデメリット：
- 正規化されたディメンションテーブルのため、ETLシステムはテーブルの数をロードする必要があります。
- 追加されたテーブルの数が原因で、クエリを実行するために複雑な結合が必要になる場合があります。したがって、クエリのパフォーマンスが低下します。
SnowFlakeスキーマの例を以下に示します。
上記のスノーフレーク図のディメンションテーブルは、以下で説明するように正規化されています。
- 日付ディメンションは、日付テーブルに外部キーIDを残すことにより、四半期、月次、および週次のテーブルに正規化されます。
- ストアディメンションは、Stateのテーブルを構成するように正規化されます。
- 製品ディメンションはブランドに正規化されます。
- Customerディメンションでは、Customerテーブルに外部キーIDを残すことにより、都市に接続されている属性が新しいCityテーブルに移動されます。
同様に、1つのディメンションで複数レベルの階層を維持できます。
上の図とは異なるレベルの階層は、次のように参照できます。
- 四半期ID、月ID、および週IDは、日付ディメンション階層用に作成され、日付ディメンションテーブルに外部キーとして追加された新しい代理キーです。
- 状態IDは、ストアディメンション階層用に作成された新しい代理キーであり、ストアディメンションテーブルに外部キーとして追加されています。
- ブランドIDは、製品ディメンション階層用に作成された新しい代理キーであり、製品ディメンションテーブルの外部キーとして追加されています。
- City idは、Customerディメンション階層用に作成された新しい代理キーであり、Customerディメンションテーブルに外部キーとして追加されています。
スノーフレークスキーマのクエリ
SnowFlakeスキーマを使用したスタースキーマ構造と同じ種類のレポートをエンドユーザー向けに生成できます。しかし、ここではクエリが少し複雑です。
上記のSnowFlakeスキーマの例から、スタースキーマクエリの例で設計したものと同じクエリを生成します。
つまり、ビジネスユーザーが2018年1月にケララ州で販売された小説とDVDの数を知りたい場合は、SnowFlakeスキーマテーブルに次のようにクエリを適用できます。
SELECT pdim.Name Product_Name, Sum (sfact.sales_units) Quanity_Sold FROM Sales sfact INNER JOIN Product pdim ON sfact.product_id = pdim.product_id INNER JOIN Store sdim ON sfact.store_id = sdim.store_id INNER JOIN State stdim ON sdim.state_id = stdim.state_id INNER JOIN Date ddim ON sfact.date_id = ddim.date_id INNER JOIN Month mdim ON ddim.month_id = mdim.month_id WHERE stdim.state = 'Kerala' AND mdim.month = 1 AND ddim.year = 2018 AND pdim.Name in (‘Novels’, ‘DVDs’) GROUP BY pdim.Name 結果：
| 商品名 | 数量_販売済み |
|---|---|
| 小説 | 12,702 |
| DVD | 32,919 |
Star（または）SnowFlakeスキーマテーブルをクエリするときに覚えておくべきポイント
以下の構造でクエリを設計できます。
SELECT句：
- select句で指定された属性は、クエリ結果に表示されます。
- Selectステートメントもグループを使用して集計値を検索するため、where条件でgroupby句を使用する必要があります。
FROM句：
- すべての重要なファクトテーブルとディメンションテーブルは、コンテキストに従って選択する必要があります。
WHERE句：
- 適切なディメンション属性は、ファクトテーブル属性と結合することにより、where句に記載されています。ディメンションテーブルの代理キーは、ファクトテーブルのそれぞれの外部キーと結合され、クエリされるデータの範囲を修正します。これを理解するには、上記のスタースキーマクエリの例を参照してください。 SnowFlakeスキーマの例で説明されているように、内部/外部結合を使用している場合は、from句自体でデータをフィルタリングすることもできます。
- ディメンション属性は、where句のデータに対する制約としても言及されています。
- 上記のすべての手順でデータをフィルタリングすることにより、レポートに適切なデータが返されます。
ビジネスニーズに応じて、上記の構造に従って、スタースキーマ（または）SnowFlakeスキーマクエリにファクト、ディメンション、属性、および制約を追加（または）削除できます。サブクエリを追加（または）異なるクエリ結果をマージして、複雑なレポートのデータを生成することもできます。
＃3）銀河図
銀河スキーマは、ファクトコンステレーションスキーマとも呼ばれます。このスキーマでは、複数のファクトテーブルが同じディメンションテーブルを共有します。ファクトテーブルとディメンションテーブルの配置は、Galaxyスキーマモデルの星のコレクションのように見えます。
このモデルの共有ディメンションは、適合ディメンションと呼ばれます。
このタイプのスキーマは、高度な要件や、スタースキーマ（または）スノーフレークスキーマでサポートされるより複雑な集約ファクトテーブルに使用されます。このスキーマは複雑であるため、保守が困難です。
GalaxySchemaの例を以下に示します。
＃4）星団スキーマ
多くのディメンションテーブルを持つSnowFlakeスキーマでは、クエリ中にさらに複雑な結合が必要になる場合があります。ディメンションテーブルが少ないスタースキーマでは、冗長性が高くなる可能性があります。したがって、上記の2つのスキーマの機能を組み合わせることで、星団スキーマが登場しました。
スタースキーマは、スタークラスタースキーマを設計するためのベースであり、スタースキーマのいくつかの重要なディメンションテーブルはスノーフレークであり、これにより、より安定したスキーマ構造が形成されます。
星団スキーマの例を以下に示します。
スノーフレークスキーマとスタースキーマのどちらが優れていますか？
DWシステムで使用されるデータウェアハウスプラットフォームとBIツールは、設計する適切なスキーマを決定する上で重要な役割を果たします。 StarとSnowFlakeは、DWで最も頻繁に使用されるスキーマです。
BIツールを使用すると、ビジネスユーザーが簡単なクエリでテーブル構造を簡単に操作できる場合は、スタースキーマが推奨されます。より多くの結合と複雑なクエリのために、ビジネスユーザーがテーブル構造と直接対話するためにBIツールがより複雑な場合は、SnowFlakeスキーマが推奨されます。
ストレージスペースを節約したい場合、またはDWシステムにこのスキーマを設計するための最適化されたツールがある場合は、SnowFlakeスキーマを使用できます。
スタースキーマとスノーフレークスキーマ
以下に、スタースキーマとスノーフレークスキーマの主な違いを示します。
| S.No | スタースケジュール | スノーフレークスキーマ |
|---|---|---|
| 1 | データの冗長性はそれ以上です。 | データの冗長性は少なくなります。 |
| 二 | ディメンションテーブルのストレージスペースはさらに多くなります。 | ディメンションテーブルのストレージスペースは比較的少なくなります。 |
| 3 | 非正規化されたディメンションテーブルが含まれます。 | 正規化されたディメンションテーブルが含まれます。 |
| 4 | 単一のファクトテーブルは、複数のディメンションテーブルに囲まれています。 | 単一のファクトテーブルは、ディメンションテーブルの複数の階層に囲まれています。 |
| 5 | クエリは、ファクトとディメンション間の直接結合を使用してデータをフェッチします。 | クエリは、ファクトとディメンションの間の複雑な結合を使用してデータをフェッチします。 |
| 6 | クエリの実行時間が短縮されます。 | クエリの実行時間は長くなります。 |
| 8 | トップダウンアプローチを使用します。 | ボトムアップアプローチを使用します。 |
結論
このチュートリアルから、さまざまなタイプのデータウェアハウススキーマとその長所と短所を十分に理解していただければ幸いです。
また、スタースキーマとスノーフレークスキーマをクエリする方法と、これら2つのスキーマのどちらを選択するかとその違いについても学びました。
ETLのデータマートについて詳しくは、今後のチュートリアルにご注目ください。
=> ここで簡単なデータウェアハウジングトレーニングシリーズをご覧ください。