スノーフレークスキーマ
当記事は、スタースキーマ の基礎的な知識がある人向けとなっています。
Wikipediaの内容を要約すると、スノーフレークスキーマは、ディメンションが正規化されたスキーマ。これを適用すると、正規化することでストレージは節約できるが、クエリは複雑になる。
スノーフレークスキーマは、ディメンションを正規化しているため、ERモデリングに慣れている人は、この設計手法に違和感を覚えないと思うが、ERモデリングは業務システム用に考えられた設計であり、分析用の設計ではないことを忘れてはならない。
仮にスノーフレークを使ったとして、出来上がったスキーマを扱うアプリケーションがスノーフレークに最適化されていなければ、メリットを得るのは難しいだろう。一方で横に長くなりすぎるレコードや、冗長な繰り返しに有効なケースもある。しかし、基本的にスノーフレークは、クエリが複雑になるため、他の様々な方法を考えた上で使うことが望ましい。
本題に入る前に、まずは、スノーフレークスキーマが役立つケースを理解するために、ディメンションテーブルに存在する属性のグループ間の階層について理解を深める。
階層とは何なのか？例えば、日付であれば、1日の積み重ねから月を作れるように、月から四半期、四半期から年が作れる。これが属性の階層である。これらがディメンションの設計に影響を与える。
さらに階層の理解を深めるため不可欠な概念であるドリルについて触れる。
ドリル
分析とは、データを掘る作業と比喩される。例えば、あるレポートを見て、もっと知りたいと思った場合、より詳細なレポートを参照するだろう。逆に詳細度下げて俯瞰して見たいケースもあるだろう。データウェアハウスやデータマートとユーザーの間には、このようなインタラクティブな分析はよくある。これを一般的にドリルと呼ぶ。BIツールによっては機能として提供されているだろう。
ドリルの概念
ドリルという言葉には、何かを深く掘り下げるという意味がある。ここでの何かはファクトのことである。
分析においての一般的なドリルの概念は、ディメンションの詳細度を上げることである。図1の左側にあるレポートは、カテゴリと呼ばれるディメンション属性によって分けられた、注文金額というファクトを示している。
図1
左側のレポートにディメンション月(month)を追加すると、右図のレポートのようになり、各カテゴリの注文が月ごとに分類出来る。例えば、このカテゴリ別の注文が、営業担当者、製品、顧客、または業界別に分類可能だった場合、それぞれがファクトを掘り下げる切り口になる。これをドリルダウンと呼ぶ。逆に図1の右側のレポートから月(month)を削除すると、詳細度が下がる。これをドリルアップと呼ぶ。
属性の階層
多くのディメンションには階層がある。階層の最下層は一番詳細度が高く、最上層は最も詳細度が低い。例えば、製品はブランドに属し、ブランドはカテゴリに属すを図にすると図2の様になる。
図2
ディメンションには詳細レベルがあるということが理解したところで、本題のスノーフレークの話に入ろう。
スノーフレーク
ベーシックなスタースキーマは、ファクトテーブルとディメンションテーブルで構成され、各ディメンションテーブルはファクトテーブルに直接結合されている。
しかし、ERモデリングの経験がある人にとっては、このベーシックなスタースキーマを見た時に、ディメンションの属性間の関係をモデル化したい感じるかもしれない。
例えば、productテーブルには、brand_codeが決まれば分かるbrand_nameとbrand_managerが含まれている。この関係を仮に明示すると、ブランド情報を別のテーブル(brand)に格納し、外部キーでproductに紐付ける。このようにディメンション属性間の関係を明示すると、スノーフレークスキーマと呼ばれる。
スノーフレークの由来は、ファクトテーブルを中心に描いたときの外観から来ている。図のように階層を別々の物理テーブルに分けた場合、各階層にサロゲートキーを追加する必要がある。当然だが各レベルで親を特定する外部キーの追加も必要になる。スノーフレークは見ての通り複雑になることは容易に想像できるが、ERモデリングの訓練を受けた人にとっては、スノーフレークは、業務システムで学んだベストプラクティスを反映しているため自然に見える。しかし、分析データベースにとっては、容量節約することを除けば、ほとんど役に立たない。
図3
基本は採用しない
ディメンションへのスノーフレークスキーマの適用は、業務システムにおける一般的な設計である正規化に似ている。この設計手法は、多種多様な同時トランザクションを捌く業務システムにおいて、データの参照整合性を確保するために考えられたため、分析用データベース向きではない。実際、ディメンションの属性間の関係をモデル化すると、使い勝手が悪くなり、ETLが複雑になり、さらにはパフォーマンスが低下する可能性が高い。
正規化は分析では役に立たない
分析システムの使われ方は、業務システムのとは大きく異なる。ほとんどが読み取りであり、大量のデータを対象とする傾向がある。また、挿入、更新、削除は、多数の個人ユーザーが行うのではなく、ETLプロセスによって行われる。そのため、ETLプロセスを活用してデータの整合性を確保できるため、正規化が必要ない。
使い所
スノーフレークが有効なケースは大きく2つある。1つ目は、スキーマを利用するツールなどがスノーフレークに最適化されているケース。2つ目は、ディメンションを複数のテーブルに分解しないと対応できない特定のモデリング課題である。1つ目の最適化されているケースよりも、遭遇するケースが多い2つ目のケースについて説明する。
モデリング課題の解消
スタースキーマでは対応できない分析要件を満たすために、ディメンションテーブル間に何らかのテーブルを導入する必要がある時にスノーフレークは役に立つだろう。これは属性がファクトに対して1つ以上の値を取る場合や、再帰的な階層からデータをロールアップまたはロールダウンする必要がある場合などに発生する。
複数の値を持つ属性
ほとんどの場合、ディメンションとファクトは1対多に対応する。例えば、特定の製品を特定の営業担当者が発注する。ファクトテーブルには、特定の注文と特定の営業担当者を示す外部キーを含めれば良い。2つの製品が注文された場合は、注文レコードを2つ作成し、それぞれが1つの製品を参照する。しかし、これに当てはまらないケースが起きることもある。例えば、2人の営業担当者が協力して注文するような状況になった場合はどうするか。プライマリとセカンダリの営業担当者を指定するカラムを追加することも出来るが、3人が関わる注文が来たら？
このような場合、ディメンションとファクトの1対多の関係は破綻する。このような場合、ブリッジテーブルを使用することで解決できる。その結果、スノーフレークのような形になる。
再帰的な属性階層
階層の中には単純な属性間の関係として定義できないものがある。これは例えば、ある会社が他の会社から構成されている場合に起こる。それらの会社もまた、さらに多くの会社から構成されている可能性がある。このような再帰的な関係は、nレベルの階層が持ち得る。他の例としては、部門関係、部品の内訳構造などがある。再帰的な階層は、階層内の異なるレベルにファクトをロールアップまたはロールダウンする要件と相まって問題になる。階層をフラットにしようとしても、なかなかうまくいかないことが多い。こういったケースには、ブリッジテーブルを使用するのが有効である。
繰り返される属性グループ
ディメンションテーブルで属性のグループが複数回出現することがある。例えば、顧客に請求先住所と配送先住所がある場合、住所を表す一連の属性がディメンションテーブル内で繰り返されることになる。もし、繰り返される属性のグループが非常に大きい、多くの場所に出現する場合は、問題になることがある。このような状況ではアウトリガーが有効である。
まとめ
ディメンション内の属性間の関係が明示的に表現されている場合、スノーフレークスキーマになる。これは、レポートツールまたはDBMSの機能を最大限に活用する時に役立つだろう。しかし、特定のツールのために設計を変更すると、将来的に他のツールへの変更が困難になるだろう。
ここまで読んだ人なら分かると思うが、スノーフレークを部分適用するブリッジテーブルやアウトリガーの方がやりたいことに近いことが多い。
アウトリガーとブリッジテーブルの話は、気合が入ればまとめる予定。
参考文献
以下の本を参考に、私なりの理解を書いたものになります。ちゃんと理解を深めたい方は、自分で読むことをおすすめします。
Star Schema: The Complete Reference
Discussion