スタースキーマ

どのようなものか

スタースキーマは、分析のためのテーブル配置です。中央の一つのテーブル
に、売上、出荷、支払いなど、業務上の出来事を数値として記録します。そ
の周囲には、出来事を説明するテーブルを置きます。どの商品、どの店舗、
どの日、どの顧客なのかを表すものです。中央のテーブルから周囲の各テー
ブルへ線を引いて図にすると、星のような形になります。

中央にあるのがファクトテーブルで、周囲にあるのがディメンションテーブ
ルです。Kimball
Groupは、「スタースキーマは、関連するディメンションテーブルにつなが
るファクトテーブルで構成されることが特徴」と簡潔に説明しています。こ
の接続に使うのは、普通のキーです。各ディメンションテーブルは一つの主
キーを持ち、各ファクト行は、出来事を説明するディメンションごとに一つ
ずつ、そのキーを外部キーとして保持します。

この分け方は、Kimball
Groupが示す測定値と文脈の区別に基づきます。ディメンショナルモデルの
中心にあるのは、業務プロセスにおける測定の対象となる出来事です。レジ
で商品を読み取る、注文を出荷する、保険金を支払う、といった出来事で
す。数量や金額など、その出来事が生む数値がファクトです。誰が、何を、
どこで、いつ、なぜ、どのように、を説明するものはすべて文脈であり、
ディメンションに入ります。

二種類のテーブルは、対照的な形をしています。最も詳細なレベルでは、
ファクトテーブルは出来事ごとに一行を持ち、数値の測定値、各ディメン
ションへの外部キー、ときには請求書番号やレシート番号などの参照情報を
保持します。文書の日付、顧客、店舗をディメンションへ移すと、その文書
から残るのは、その番号だけです。そのため、対応するディメンションテー
ブルを作らず、ファクト行に番号を置きます。Kimball
Groupは、これを縮退ディメンションと呼びます。一方、ディメンション
テーブルは幅が広く、フラットで、意図的に非正規化され、説明用のテキス
ト列を多数持ちます。商品ディメンションでは、ブランド、カテゴリー、部
門を別々のテーブルに分けず、各商品の行に繰り返し記録します。

スタースキーマは、ディメンショナルモデルをリレーショナルな形で表した
もので、通常のSQLデータベースの通常のテーブルで構築します。Kimball
Groupが説明するもう一つの形が、同じ属性とファクトを保持する多次元
データベース、OLAPキューブです。MDXなど、SQLより分析能力の高い言語で
問い合わせ、アクセス用のプロトコルはXMLAが提供します。キューブは、利
用者へデータを公開する直前の最終工程や、集約値の層として、スタース
キーマから作ることもよくあります。どちらにも同じモデリングのルールが
当てはまります。Kimball
Groupが、それらをアーキテクチャーに依存しないとするのは、Kimball方式
のデータウェアハウスにも、Bill
Inmonのハブアンドスポーク型Corporate Information Factoryにも適用でき
るためです。

なぜ重要なのか

この形にすると、分析の質問が同じような構造になります。合計するファク
トを選び、それを説明するディメンションへファクトテーブルを結合し、一
部のディメンション列で絞り込み、別の列でグループ化します。Kimball
Groupは、計算や動的な集約の主な対象がファクトテーブルであり、問い合
わせの制約条件、グループ分け、レポートのラベルの主な取得元がディメン
ション属性だと説明しています。一度このパターンを学んだ分析者は、助け
を借りずに新しいファクトテーブルへの問い合わせを書けます。どのスター
スキーマも同じ配置なので、レポート作成ツールも、フィールドの一覧から
問い合わせを組み立てられます。

二つ目の理由は、読みやすさです。業務用のデータベースは、各事実を一度
だけ保存し、一貫性を保つよう設計します。そのため、商品のカテゴリーは
通常、別の参照テーブルに置き、idで結合します。注文を受けるシステムに
は適切ですが、カテゴリー別の売上を知りたい人は、まず、どのテーブルを
どの順で結合するかを見極めなければなりません。その用途には不向きで
す。フラットなディメンションなら、説明用の全列が、言葉で書かれた形で
一か所に集まります。Kimball
Groupは、その逆、つまりディメンションの階層をさらに別のテーブルへ正
規化する方法を避けるよう注意しています。業務部門の利用者には、結果を
理解したり、たどったりしにくくなるためです。

三つ目の理由は、適切に設計したスタースキーマなら、設計時には想定しな
かった質問にも答えられることです。ファクトテーブルの粒度、つまり一行
が正確に何を意味するかは、ディメンションやファクトを決める前に宣言し
ます。Kimball
Groupは、業務プロセスが記録する最も細かなレベル、アトミックな粒度か
ら始めるよう推奨しています。レシートの明細一行ごとに一行を持つテーブ
ルなら、日、店舗、ブランド、あるいはその組み合わせで集計できます。店
舗ごとの日次合計しか保持しないテーブルは、質問の仕方をどう変えても、
商品別に分解できません。Kimball
Groupの言い方では、アトミックなデータは予測不能な問い合わせに耐えら
れますが、集計済みデータは、後から問われる質問を前提にしています。

その代償は、意図的な重複です。部門名は各商品の行に繰り返され、スター
スキーマを構築するロード処理は、それらの重複の整合性を保ち、説明が変
わった際の扱いも決めなければなりません。スタースキーマは業務モデルを
置き換えるものでもありません。ロード処理によって業務モデルから作るた
め、最新性は最後にロードした時点までに限られます。

実際の活用例

小売業者が、商品カテゴリー別、月別の売上を求めています。業務プロセス
はレジのレシートで、粒度は、レシートの明細一行につき一行と宣言しま
す。商品ディメンションは一つのフラットなテーブルです。ファクトテーブ
ルは、各ディメンションのキー、縮退ディメンションであるレシート番号、
二つの測定値を持ちます。

```sql
CREATE TABLE dim_product (
    product_key  bigint PRIMARY KEY,   -- surrogate key, owned by the warehouse
    sku          text NOT NULL,        -- the code in the source system
    product_name text NOT NULL,
    brand        text NOT NULL,
    category     text NOT NULL,
    department   text NOT NULL
);

CREATE TABLE fact_sales (
    date_key       integer NOT NULL REFERENCES dim_date,
    store_key      integer NOT NULL REFERENCES dim_store,
    product_key    bigint  NOT NULL REFERENCES dim_product,
    receipt_number text    NOT NULL,   -- degenerate dimension
    quantity       integer NOT NULL,
    sales_amount   numeric(12, 2) NOT NULL
);

SELECT d.year_month, p.category, sum(f.sales_amount) AS sales
FROM fact_sales AS f
JOIN dim_date AS d USING (date_key)
JOIN dim_product AS p USING (product_key)
WHERE p.department = 'Grocery'
GROUP BY d.year_month, p.category
ORDER BY d.year_month, p.category;
```

問い合わせは、どのスター型の問い合わせにも共通する形をしています。
ファクトテーブル、ディメンションごとの一つの結合、ディメンションのテ
キストに対する絞り込みとグループ化、ファクトに対する合計です。レポー
トに店舗の地域を追加するなら、結合を一つと、`GROUP
BY`に列を一つ追加するだけで、ファクトテーブルは変わりません。レシー
ト番号はファクト行に残るため、一つの買い物の明細をまとめて数えること
もできます。それ以外に説明すべきことがないため、番号用のディメンショ
ンテーブルは作りません。

スタースキーマは、データベース設計・モデリングサービスが約束する、業
務の実態に合い、後で必要になる質問にも答えられる構造を実現する一つの
方法です。その元になる業務モデルが、この仕事のもう半分です。
NextMarinerでの現在の業務では、創業者が、専門職、企業、船舶、求人、
レビューを含む海事分野を、34個のPostgreSQLスキーマにまたがる348個の
正規化されたテーブルとしてモデル化しました。そこでは、状態、種類、カ
テゴリーなどの小さく安定した一覧をSMALLINTの参照テーブルにし、行を小
さく、結合を低コストに保っています。実際の外部キーと制約によって、整
合性はデータベースの責務になります。これは、一行ずつ書き込み、正しさ
を保つ必要のあるシステムに合った形です。その上にレポート用のスタース
キーマを構築する場合は、逆を行います。読むのはアプリではなく人なの
で、フラットなディメンション内で、コードを言葉へ戻します。

問題が起きるところ

ディメンションを正規化し、スタースキーマがスノーフレークになる。
商品ディメンションからブランド、カテゴリー、部門を別々のテーブルへ分
けると、業務用の設計に慣れた人には整然と見えます。Kimball
Groupは、この結果をスノーフレークと呼び、避けるよう助言しています。
業務部門の利用者には理解してたどることが難しくなり、結合が増えると問
い合わせが遅くなる場合もありますが、フラットなディメンションでも、
まったく同じ情報を保持できるためです。各ディメンションは一つの幅広い
テーブルに保ち、ロード処理で各行に階層を繰り返し書き込みます。

最初のレポートに合わせて粒度を決める。
店舗ごとの日次売上を持つファクトテーブルは、想定したダッシュボードに
は答えられますが、それより細かな問いには答えられません。単一の商品や
買い物かごについて最初に質問された時点で、ソースからの再ロードが必要
になります。日次合計と個別明細を混ぜたテーブルは、さらに悪く、合計を
取るたびに同じ売上を二重に数えます。まず粒度を宣言し、そのプロセスが
記録するアトミックなレベルから始め、粒度ごとに専用のファクトテーブル
を設けます。速度が必要なら、その上に別のテーブルとして集計テーブルを
追加できます。

ディメンション属性がソースシステムのコードになっている。
業務システムは短いコードを保存します。それをディメンションへそのまま
コピーすると、行のラベルを理解できるのがソースシステムだけ、というレ
ポートになります。Kimball
Groupは、属性としてコードを持つことを認めていますが、最も役に立つ属
性は詳しい説明だと述べています。レポートのラベルになるのは、それらの
説明だからです。ロード時にすべてのコードを言葉へ展開し、行をソースま
でさかのぼって追跡する必要のある人のため、説明の隣の列にコードも残し
ます。

ファクトテーブルがレポートの形をしている。
業務で既に使っている表計算シートから設計を始め、月ごとに列を設けた
り、経営レポートの一行ごとに行を作ったりしたくなります。Kimball
Groupは、ファクトテーブルの設計は、後で作るレポートではなく、測定さ
れる実際の活動に基づくと明言しています。レポートのレイアウトは変わっ
ても、レジのレシートは変わりません。宣言した粒度で発生一回につき一行
として出来事をモデル化し、レイアウトは問い合わせに生成させます。

参考資料

  * Star Schemas and OLAP Cubes —
定義、測定値と文脈としてのファクトとディメンション、キューブとスター
スキーマの関係を説明しています。

  * Grain — 一つのファクト行が意味するものの宣言と、アトミックな粒度
を先に決める理由を説明しています。

  * Fact Table Structure —
レポートではなく活動に基づいて設計し、出来事ごとの行に数値の測定値と
外部キーを置く構造を説明しています。

  * Dimension Table Structure —
絞り込み、グループ化、ラベルに使う説明属性を持つ、幅広く、フラット
で、非正規化されたテーブルを説明しています。

  * Snowflaked Dimensions — ディメンションの階層を正規化すると、使い
にくくなる理由を説明しています。

  * Microsoft: Multidimensional model data access —
問い合わせと計算の言語としてのMDXと、クライアントがMDX文を送るための
インターフェースであるXMLAを説明しています。

hこのページのウェブ版	URL:https://engineer.company/ja/glossary/star-schema/	engineer.company	70
