スタースキーマ(基礎)
スタースキーマ または 星型スキーマ はデータウェアハウスに利用される最も単純なスキーマである。スタースキーマには唯1つもしくは少数のファクト表と複数のディメンション表が含まれる。スタースキーマはスノーフレークスキーマの一種であるが、多くの用途で利用されている。
スタースキーマは、ディメショナル・モデリングをリレーショナル・データベースで実装したものになる。
詳しくは、ディメンショナル・モデリング にまとめている。
この記事は、あなたが「様々な指標を様々な軸で、レポートを見たい」類の要望に応えるためのスキーマ設計に困っている場合に役立つだろう。
ディメンションテーブル設計
サロゲートキー
スタースキーマでは、各ディメンションテーブルに、サロゲートキーを割り当てる。このキーは、業務システムで使われているキー（ナチュラルキー）とは別のものを使用し、データウェアハウスまたはデータマートのために作成される。
サロゲートキーは、スタースキーマの作成工程であるETLなどのプロセスの一部で作成され、キーの値（適当な連番など）は本質的な意味はない。データウェアハウスを利用するユーザーにとっては無価値なものである。
ディメンションテーブルには、このサロゲートキーとは別にナチュラルキーを持っているのが一般的である。ナチュラルキーとは、業務システムのエンティティを識別することが出来るキーで、データウェアハウス内の特定行を一意に識別するものではない。
図1に具体的な例を示した。xxx_key がサロゲートキー、yyy_id がナチュラルキーである。
図1
何故、ナチュラルキーとサロゲートキーを分けるのか？という疑問を持ったと思う。理由は簡単である。
例えば、注文入力システムがあったとする。注文内容を管理したテーブルには、恐らく注文者情報を示す customer_id というカラムがあり、そこに 184184 といった値が入ってるだろう。
もし、この顧客が住所の更新や名前の更新があったとする。顧客ディメンションテーブルが、ナチュラルキーである customer_id を使用して一意に行を識別していた場合、 customer_id の 184184 の行にだけ顧客情報が管理されているため、履歴を追うことはできなくなるが、サロゲートキーを使っていた場合、過去の分のデータも維持することが可能となる。
過去の分のデータ保持に関しては、スロー・チェンジ・ディメンション にて、詳しく解説している。
ナチュラルキーを使っても良いか？
一般的には、ディメンションテーブルにはサロゲートキーを割り当てる。しかし、ナチュラルキーを使うことも可能である。
もし、ナチュラルキーを使いつつ、顧客の履歴を追いたい場合はどうすればよいか？例えば、図2に示したような customer_id とは別に、 version_no のようなものを用意する。しかし、この手法には面倒な点がある。当然だがディメンションを特定できる様にするために、ファクトテーブルにも、この customer_id と version_no のカラムを用意する必要がある。これはJOINのためのSQLが複雑になる。また、クエリの実行パフォーマンスも落ちる可能性がある。
であれば、タイムスタンプを保持したカラムを用意すればよいのか？しかし、これも分析クエリを非常に難しくする。なぜなら、毎回どのディメンションと繋げるべきかを判断する必要があるからである。もし、様々な注文と顧客をくっつける必要があるとどうなるか？クエリパフォーマンスは著しく落ちるだろう。
図2
基本的には、サロゲートキーを用意することで、スキーマ設計は簡素化され、簡単な結合を可能にしてくれる。
ディメンションの豊富さで分析幅が決まる
ディメンションは、ファクトの文脈を提供する。逆に文脈が無ければ、ファクトは解釈することが出来ない。例えば、「注文金額が400万です」は何の話をしているか分からない。なぜなら、それはどういう文脈で生まれたものか分からないからである。文脈が存在することで、ファクトは初めて有用なものになる。
詳しくは、ディメンショナル・モデリング にまとめている。
ディメンションの具体的なユースケース
- クエリやレポートでのフィルタリング
- ファクトの集計範囲を制御する
- 情報の順序付け、並び替えに使用する
- レポートに文脈を提供する
- グループ化、小計化など
ディメンションは組み合わせることで、様々な要望に応えることが出来る。ディメンションが豊富であればあるほど、ファクトの分析可能な幅も当然広がる。
第3正規化まで行わない
業務システムでは、第3正規化まで行うのが一般的である。しかし、ディメンションテーブルでは全て正規化しない方が良いケースもある。
例えば、図3の様な顧客の種類を示した customer_type カラム。これは数値を受け付けるとする。レポートで出す場合に、このカラムの参照先である customer_types テーブルを見に行けば、レポートでの表示名が決まるようなものがあるとする。
図3
ほとんどのレポートで、ここの表示名を出す必要があるのであれば、図4の様に正規化せずにまとめてしまう。これによりクエリの効率化が期待できる。
図4
数値 ≠ ファクト
ディメンションには数値が含まれていることがある。例えば、以下はディメンションである可能性が高い。
- 会員番号
- 電話番号
- 郵便番号
ファクトなのかディメンションなのか判断が難しい例を出そう。例えば、商品の単価などがそうである。1個100円の商品。100円はファクトなのか？もし、この数値が集約(Aggregate)やサマ（Summary）ったものである場合は、ファクトであると言える。しかし、それが集約やサマるために使われるものである場合はディメンションである。
Junk ディメンション
ディメンションの設計パターンはいくつか存在するが、今回はそのうちの一つを今回は紹介する。
小さなディメンションがたくさんあり、ディメンションテーブルとして一つずつ作るほどでもないが、ディメンションとしては使いたいケースには、Junkディメンションが有用なケースがある。
例えば、図5に示した注文の種類を表すクレジットカード決済か(is_credit_clearing)、決済済みか(is_settled)、などのフラグをまとめたものをディメンションテーブルとして切り出すなどである。これらをわざわざディメンションテーブルを一つずつ作るよりは、 order_info の様なディメンションテーブルを作ることが有効なケースもある。
図5
ディメンションテーブルの冗長性の排除
ここまでのディメンションテーブルの設計を見れば、冗長なテーブル設計になっていることに気付くだろう。場合によっては、一般的な正規化のパターンを適用するのが有効なケースもある。これを適用すると、スノーフレークスキーマと呼ばれるものになる。ここではスノーフレークについて深く掘り下げないが、正規化が有効なケースもあるが、基本的にはディメンションテーブル設計に正規化の原則を適用することは例外である。
スノーフレークスキーマ にて、詳しい設計についてまとめてある。
ファクトテーブル設計
ファクトテーブルは、ビジネスプロセスを測定するためのテーブルである。ファクトにはディメンションへの外部キーが定義され、場合によってはファクトテーブルにディメンションが含まれていることもある。
全ての測定値をファクトテーブルに格納する
各ファクトテーブルは、ビジネスプロセスを記述する。一部の冗長性があっても、関連する測定値をテーブルにまとめる必要がある。各ファクトを明示的に格納することで、様々なクエリやツールを使ったとしても、一貫した測定が可能になる。
例えば、以下のようなファクトテーブルが存在していたとする。ここでのファクトは quantity_ordered と order_yens と cost_yens と margin_yens だ
| product_key | customer_key | sales_person_key | quantity_ordered | order_datetime | order_yens | cost_yens | margin_yens |
|---|---|---|---|---|---|---|---|
| 10001 | 4649 | 8822 | 238 | 2020-10-10 10:00:00 | 55050 | 50050 | 5000 |
| 13001 | 4649 | 2311 | 200 | 2020-10-11 10:00:00 | 113132 | 110132 | 3000 |
| 14001 | 118 | 3452 | 100 | 2020-10-12 10:00:00 | 100000 | 89000 | 11000 |
| 10501 | 329 | 1111 | 382 | 2020-10-13 10:00:00 | 113213 | 20000 | 93213 |
注目してほしいのが、 margin_yens は、order_yens から cost_yens を引けば計算可能なことである。人によっては、margin_yens のカラムを排除するだろう。しかし、ファクトテーブルに計算済みのカラムを持つことで、使用するツールに関係なく margin_yens の一貫した表現を保証することが可能である。基本的には計算すれば出すことが出来る測定値であっても、明示的に格納することで、一貫性とパフォーマンスを実現可能になる。
粒度
ファクトテーブルは、それぞれ粒度が存在する。粒度はスキーマ設計において重要な部分である。これによりファクトテーブルの行が表している意味について混乱が生じず、全てのファクトが同じ粒度で格納されていることが保証される。
多くの場合、ディメンションを列挙することで、粒度を示そうとする。例えば、以下のテーブルの場合、 product_key と customer_key と sales_person_key と order_datetime の粒度でまとめられている。これは自明だが、重要なことである。
| product_key | customer_key | sales_person_key | quantity_ordered | order_datetime | order_yens | cost_yens | margin_yens |
|---|---|---|---|---|---|---|---|
| 10001 | 4649 | 8822 | 238 | 2020-10-10 10:00:00 | 55050 | 50050 | 5000 |
| 13001 | 4649 | 2311 | 200 | 2020-10-11 10:00:00 | 113132 | 110132 | 3000 |
| 14001 | 118 | 3452 | 100 | 2020-10-12 10:00:00 | 100000 | 89000 | 11000 |
| 10501 | 329 | 1111 | 382 | 2020-10-13 10:00:00 | 113213 | 20000 | 93213 |
このテーブルが表しているのは、 「ある時間(order_datetime)に、ある顧客(customer_key)が、同じ販売員(sales_person_key)から同じ製品(product_key)を複数注文(quantity_ordered)した場合に、1行に統合されている」と言える。
実はこの集約によって、潜在的には有用だった情報を失っている可能性はある。そのため、データの集約は出来るだけ避け、可能な限り粒度の細かい状態でデータを保持をしたい。なぜなら、初期のビジネス要件では、詳細なデータを必要としないが、分析の要件は変化する。集約されすぎたスキーマを構築した場合、将来的に詳細な情報が必要になった時には、最初からやり直す必要が出てくる。
これらのことから、出来る限り、粒度が細かいレベルで保持しておくことが望ましい。
全てのディメンションを含めなくてもよい
ファクトテーブルには、表現可能なディメンションの組み合わせの全てが表現されているわけではないため、実際の可能な組み合わせ数よりも少ないことがある。このようなファクトテーブルは、Sparsityと呼ばれる。
例えば、図1の fact_orders は、特定の日(order_datetime)に特定の顧客(customer_key)が特定の製品(product_key)を特定の販売員(sales_person_key)から注文しなかった場合に、ファクトテーブルにレコードが追加されない。これはこれで良い。
もし、全ての考えられるディメンションの組み合わせをファクトテーブルで表現した場合、そのテーブルはあっという間に膨大なレコード数を抱えることになるだろう。
まとめ
ディメンション
- ナチュラルキーとは別にサロゲートキーを含めることで、ソースデータから分析スキーマが独立し、簡単に履歴が追跡可能になる。
- ディメンションの豊富さで分析の幅は決まるため、ディメンションテーブルは出来る限りたくさんのカラムを持ってることが望ましい。
- 第3正規化では設計されない。
ファクト
- ディメンションへの外部キー参照とファクトから構成される。
- プロセスに関する全てをファクトに含むべき。例えば、計算して得られる結果であってもである。
- 集約によって失われる情報があるため、集約しすぎない。
Discussion
参考になる記事ありがとうございます！
多忙な中読む人向けにできるだけ簡潔な記載をしていただけるとなお良くなると思います！
例えば、 数値 ≠ ファクト のくだりは当たり前すぎて余計。
冒頭のこの行ですが、何故か単語が消えてしまっている様ですね。
恐らくは"ファクトとディメンション"か"ディメンショナル・モデル"、でしょうか？
修正しておきます！！