30分でわかる『アジャイルデータモデリング』

2025年2月14日 Forkwell Library #81 の登壇資料です https://forkwell.connpass.com/event/343084/

Transcript

1. 30分でわかる 『アジャイルデータモデリング』 ucchi- 2025-02-14 Forkwell Library #81 1
2. 自己紹介 • アナリティクスエンジニア • X: @hanon52_ • 2021年 ピクシブ株式会社に新卒入社 ◦ 広告のデータ基盤設計、Looker や Salesforce の整備 ◦ 社内の様々なプロダクトのデータ基盤構築、助言 ◦ データの入口から出口まで何でもやります • 美味しいご飯を食べたり作ったりするのが好き • 株式会社風音屋のアドバイザーでもあります 打出 紘基(ucchi-) 2
3. 『アジャイルデータモデリング』を30分で駆け抜ける 発表の概要 3
4. 発表のゴール 対象者 理想状態 書籍の購入を検討されている方 書籍の内容紹介を通じて、購入を後押しする 書籍をすでに購入された方 • 書籍の全体像が把握できるようになる • 書籍とスライドを併用することで、内容を深く理解できるようになる 4
5. この本は、株式会社風音屋の社員・アドバイザー有志が中心となって出版されました。 後半のパネルディスカッションでは、以下の訳者が登壇します。 • 佐々木 江亜（@0610Esa, 株式会社マネーフォワード） • ゆずたそ（@yuzutas0, 株式会社風音屋） 訳者紹介 5
6. Agile Data Warehouse Design • データ要件の収集や実装をアジャイルに行う • 最初に全てを設計してから実装するのではなく、 • ビジネスプロセス（例：「注文」「閲覧」など）ごと に細かく実装し、ステークホルダーからフィードバッ クを受け、段階的に改善を繰り返す 原著の主題：データウェアハウスの設計を「アジャイル」に行う 6
7. Collaborative Dimensional Modeling, from Whiteboard to Star Schema • データエンジニアが頑張ってソースコードから業務ロ ジックを読み解くのではなく、 • ビジネス側のステークホルダーと協調しながら、ホワ イトボードでデータ要件を収集し、それをスタース キーマに落とし込む データエンジニアは、エンジニア側だけでなく、ビジネス側 のステークホルダーと会話することが多い。 モデリングの技術的な話から、データ要件をヒアリングする 方法まで踏み込んで解説されているのがこの本の特徴。 原著の副題：ステークホルダーと「協調」しながらデータ要件を収集 7
8. • パート1：「モデルストーミング」 ◦ BIやレポートで求められるデータ要件をステークホルダーから収集する一連の方法 • パート2：「ディメンショナルデザインパターン」 ◦ 収集したデータ要件を実装する上で役立つ様々なテクニック • 日本語版限定コンテンツ：事例集 ◦ 国内事例12社を掲載 データモデリングに初めて触れる方は、まず事例集を読むのがおすすめ。 データを活用することがビジネスにどう役立つのか、解像度を高めることができる。 本の章立て：2部構成 + 事例集 8
9. 原著の初版が発売されたのは2011年。BigQuery が一般提供された年。 それ以降、データウェアハウスの性能は大きく向上した。 • データ処理速度が向上 • ストレージ費用が低下 • Array, Struct, Json など半構造化データのサポートが充実 現在は、この本に書かれたベストプラクティスより良い選択肢があるかもしれない。 それでも、現代の最適解を議論するための土台として、古典的なアプローチを理解することには意義がある。 • 課題（Why）をどのような発想（What）で解決するのかを理解する • 具体的な手段（How）は、現代のデータウェアハウスの性能向上を踏まえて工夫すればよい 2011年時点のベストプラクティスを学ぶ意義 9
10. 本発表では3つのテーマについて解説する ディメンショナルモデリング データ分析のためのモデリング手法 BEAM✲ ステークホルダーからデータ要件を収集するためのテクニック データモデリングのデザインパターン 収集したデータ要件を実装する上で役立つベストプラクティス 10
11. 本発表では3つのテーマについて解説する ディメンショナルモデリング データ分析のためのモデリング手法 BEAM✲ ステークホルダーからデータ要件を収集するためのテクニック データモデリングのデザインパターン 収集したデータ要件を実装する上で役立つベストプラクティス 11
12. • 分析の対象となるビジネスプロセスを、 ファクトとディメンションの2つに分解する ◦ ファクト：ビジネスプロセスの指標 ◦ ディメンション：ビジネスプロセスの分析軸 • 右図の場合... ◦ ビジネスプロセス：「販売」 ◦ ファクト：「売上高」「販売台数」など ◦ ディメンション： ▪ 何を：「コンピューター製品の」 ▪ いつ：「2010年の」「四半期ごとの」 ▪ どこで：「アウトレットの」 ディメンショナルモデリング：ファクト × ディメンション 書籍の「章.節.項」を表します。 さらに学びたい方はぜひ書籍をご参照ください！ 1.1.2 ディメンショナルモデリング 12
13. 店舗 店舗キー 店舗コード 店舗名 URL スタースキーマ：1つのファクトと多数のディメンション スタースキーマとは、ディメンショナルモデリング に沿ったスキーマを指す。星の形をしている。 作成手順は以下。 • ビジネスプロセスを「ディメンション」と 「ファクト」の2つに分解 • 中央に1つのファクトを配置 • 周辺に多数のディメンションを配置 販売ファクト 日付キー 製品キー 店舗キー 従業員キー プロモーションキー 販売数 売上高 従業員 従業員キー 従業員コード 従業員名 役職 カレンダー 日付キー 日付 月 年 平日フラグ 祝日フラグ 製品 製品キー 製品コード 製品の種類 ブランド名 サブカテゴリー カテゴリー プロモーション プロモーションキー プロモーションコード プロモーション名 広告タイプ ディメンショナルモデリング 1.1.2 13
14. リレーショナルデータモデリング（ERモデリング）と ディメンショナルモデリングは、用途が異なる。 リレーショナルデータモデリングは、 アプリケーションの要求に応える。 • 正規化を行うことで、書き込みが必要な場所を最小 限にし、高速な書き込みを可能にする • 値が1か所にしか記録されないため、 値の一貫性を保ちやすくなる ディメンショナルモデリングは、 データ分析の要求に応える。 • 「ディメンション」と「ファクト」の2種類しかな いため、ビジネス側のステークホルダーでも理解し やすい • 複数のテーブルを事前に結合し「ディメンション」 「ファクト」にまとめておくことで、分析クエリが 簡潔になる ディメンショナルモデリングはデータ分析の要求に応える 手法 リレーショナル データモデリング ディメンショナル モデリング 代表的なスキーマ 第3正規形 スタースキーマ 用途 アプリケーション データ分析 要求 高速な書き込み 値の一貫性 理解しやすさ 分析クエリの簡潔さ 特徴 値を正規化して持 つことで、更新が 必要な箇所を減ら す 値が1か所にしか記 録されないため、 値の一貫性を保ち やすい 「ファクト」と「ディ メンション」の2種類 だけなので理解しやす い 事前に結合するため分 析クエリが簡潔になる データモデリング手法の比較 ディメンショナルモデリング 1.1 14
15. ディメンショナルモデリング データ分析のためのモデリング手法 BEAM✲ ステークホルダーからデータ要件を収集するためのテクニック データモデリングのデザインパターン 収集したデータ要件を実装する上で役立つベストプラクティス 15
16. BEAM✲は、この本で独自に提唱されている、データ要件収集のためのフレームワーク。 Business Event：データ分析の対象となるビジネスプロセスの具体例 Analysis：要件分析 Modeling：モデリング ✲：成果物として手に入るスタースキーマ BEAM✲：Business Event Analysis and Modeling 1.3 BEAM✲ 16 モデルストーマー 人物一覧と定義は、書籍の 「はじめに」で紹介されています。
17. BEAM✲に基づいた要件収集とモデリングのアプローチを「モデルストーミング」と呼ぶ。 「ブレインストーミング」と「データモデリング」のテクニックを組み合わせ、 ステークホルダーと協調しながらデータモデルを作り上げる。 手順は以下。 1. ビジネスイベントのモデリング 2. ディメンションのモデリング 3. イベントマトリックスに追加 4. タスクをバックログに追加 モデルストーミング：データ要件の収集とモデリング 4.2 BEAM✲ > モデルストーミング 17
18. モデルストーミングの手順 1. ビジネスイベントのモデリング 2. ディメンションのモデリング 3. イベントマトリックスに追加 4. タスクをバックログに追加 4.2 BEAM✲ > モデルストーミングの手順 18
19. ステークホルダーに、分析したいビジネスイベントを尋ねる。 「 「今1番分析したいのは、誰が何をするイベントですか？」 「顧客が製品を注文すること、ですかね...」 ビジネスイベントのモデリング：対話を通じたイベントの発見 ビジネスイベント 「顧客の注文」を発見 2.2.1 BEAM✲ > モデルストーミングの手順 > 1.ビジネスイベントのモデリング 19 ステークホルダー あなた アジャイルモデラー （モデルストーミングを 促進する人）
20. ビジネスイベントで重要なのは、 イベントタイプを見抜くこと。 離散型イベント • 単発のイベント • 例）「商品の購入」 反復型イベント • 定期的に繰り返されるイベント • 例）「利息の請求」 ◦ 利息は毎月計算される 発展型イベント • 時間をかけて進行するイベント • 例）「卸売の注文」 ◦ 「注文 → 出荷 → 配達 → 支払」 と状態が発展していく イベントタイプの種類（離散型、反復型、発展型） ※一部改変 2.1.1 BEAM✲ > モデルストーミングの手順 > 1.ビジネスイベントのモデリング 20
21. イベントタイプを持ち替えた方がよいことがある 「データベースに記録されているイベントタイプ」と「分析に必要なイベントタイプ」は異なることがある。 その場合、あらかじめイベントタイプを持ち替えた方がよい。 以下2つの事例を紹介する。 • 離散型 → 反復型 • 離散型 → 発展型 「応用」は 2025 年 2 月時点での ucchi- の考えです BEAM✲ > モデルストーミングの手順 > 1.ビジネスイベントのモデリング 応用 21 聡明なモデラー
22. 離散型イベントから反復型イベントに持ち替えた方がよい例 決済日時 ユーザー 内容 金額 2025年1月1日 0:00:00 一郎さん Bronze会員新規 200円 2025年1月5日 10:00:00 一郎さん Silver会員 アップグレード 300円 2025年1月20日 14:20:00 二郎さん Bronze会員新規 200円 2025年1月1日 0:00:00 三郎さん Gold会員継続 800円 2025年2月1日 0:00:00 一郎さん Silver会員継続 500円 … … … … 年月 ユーザー 会員ステータス 金額 2025年1月 一郎さん Silver会員 500円 2025年1月 二郎さん Bronze会員 200円 2025年1月 三郎さん Gold会員 800円 2025年2月 一郎さん Silver会員 500円 … … … … 離散型イベント「決済」 有料会員の決済履歴は、データベース上で決済日時ごとに記録されている。これは離散型イベント。 ただし「アップグレードによる差額決済」が含まれている。これを対処するには高度で複雑なSQLが必要。 反復型イベント「有料会員ステータス」に事前に持ち替えることで、有料会員の分析を簡単に行うことができる。 反復型イベント「有料会員ステータス」 赤：一郎 黄：二郎 緑：三郎 BEAM✲ > モデルストーミングの手順 > 1.ビジネスイベントのモデリング 応用 22 差額のみ決済
23. 広告について考える。離散型イベント「閲覧」と「クリック」を結合して、クリック率の分析を行いたい。 しかし、広告のログ数は膨大なため、分析のたびに結合するとコストがかかる。状態が「閲覧」から「クリッ ク」へ変わる発展型イベント「広告配信」に事前に持ち替えることで、分析時のコストを削減できる。 閲覧タイムスタンプ 広告ID クリックされたか 2025年1月1日 10:00:00 a はい 2025年1月1日 10:10:34 b いいえ … … … タイムスタンプ 広告ID 2025年1月1日 10:00:00 a 2025年1月1日 10:10:34 b … … タイムスタンプ 広告ID 2025年1月1日 10:15:00 a … … 離散型イベント 「閲覧」 離散型イベント 「クリック」 発展型イベント「広告配信」 赤：閲覧ログ由来 黄：クリックログ由来 BEAM✲ > モデルストーミングの手順 > 1.ビジネスイベントのモデリング 離散型イベントから発展型イベントに持ち替えた方がよい例 応用 23
24. イベントタイプを見極め、イベントの振る舞いを記録するため、ステークホルダーに具体例を尋ねる。 具体例の収集方法の詳細は 2.2.3 を参照。 得られた具体例の値を、BEAM✲テーブルに記載する。 BEAM✲テーブル：ビジネスイベントのデータ要件を記録 顧客は 製品を 注文する 注文日に 販売拠点で ［誰が］ ［何を］ ［いつ］ ［どこで］ 一郎さん iPip Blue Suede 2011年5月18日 POMStore 表参道 二郎さん POMBook Air 2011年6月29日 POMStore 表参道 三郎さん iPip Blue Suede 10年前 store.POM.com 四郎さん iPOM Pro 昨日 Amazon.com BEAM✲テーブル「顧客の注文」 最大で10年前まで遡りたい 最短で翌日に分析したい 物理店舗と自社ECと外部EC をまたいで分析したい 2.2.2 BEAM✲ > モデルストーミングの手順 > 1.ビジネスイベントのモデリング 24 ステークホルダー アジャイルモデラー
25. ER図を使ってデータ要件を記録することもできる。 しかし、今回はビジネス側のステークホルダーと対話しながら、データ分析の要件収集を行なっている。 対話の相手が慣れ親しんだ表形式でデータ要件を記録することで、円滑に要件収集を進めることができる。 BEAM✲テーブルを使う理由：ステークホルダーが理解しやすい ER図 BEAM✲テーブル 1.3.2 BEAM✲ > モデルストーミングの手順 > 1.ビジネスイベントのモデリング 25
26. モデルストーミングの手順 1. ビジネスイベントのモデリング 2. ディメンションのモデリング 3. イベントマトリックスに追加 4. タスクをバックログに追加 4.2 BEAM✲ > モデルストーミングの手順 26
27. BEAM✲テーブルの値を用いて、ディメンションテーブルを作成する。 「テーブルの粒度」「テーブルに含めるべき列」の具体例などをステークホルダーに尋ね、追加する。 具体例の収集方法の詳細は 3.2, 3.3 を参照。 顧客は 製品を 注文する … ［誰が］ ［何を］ … 一郎さん iPip Blue Suede … 二郎さん POMBook Air … 三郎さん iPip Blue Suede … 四郎さん iPOM Pro … ディメンションテーブル：ディメンションのデータ要件を記録 製品名 製品コード 製品タイプ iPip Blue Suede IPPBS16G MP3 POMBook Air PBA20G ラップトップ iPOM Pro IPMP35G タワー 顧客の注文 製品 3.1 BEAM✲ > モデルストーミングの手順 > 2. ディメンションのモデリング 27 アジャイルモデラー ステークホルダー
28. ディメンションの定義と発見においては、以下の2つが重要。 • ディメンション階層 • ディメンションの履歴 ディメンションの論点：「ディメンション階層」と「履歴」 3.1.1 BEAM✲ > モデルストーミングの手順 > 2. ディメンションのモデリング 28 聡明なモデラー
29. ディメンションの多くには階層がある。下図の場合... • 上から順番に「カテゴリー」「サブカテゴリー」「製品」が存在する • 製品に紐づく「サブカテゴリー」や「カテゴリー」は1つだけ ディメンション階層の情報を事前に収集して、ドリルダウンやロールアップする際の仕様を事前に取り決める。 • 例）「サブカテゴリー」別の分析を行う際、「POMServer」のラベルをどうしたい？ ◦ 「カテゴリー」を使う、「製品名」を使う、「カテゴリー_製品名」とする、など 詳細は（6.3.2）を参照 ディメンション階層：ドリルダウンやロールアップの要件を記録 3.4 BEAM✲ > モデルストーミングの手順 > 2. ディメンションのモデリング > ディメンション階層 サブカテゴリーが無い 29
30. 階層には、以下の3種類が存在する。 バランス階層 深さの数が固定、それぞれに固有の階層レベル名がある。 例：日付。必ず「日→週→月→年」にロールアップされる 不規則階層 階層レベルの値の一部が欠けている。 例：製品。「POMServer」はサブカテゴリーの値がない 可変深度階層（再帰的階層） 名前が付けられていないレベルが存在し、深さが可変。 例：人事階層 - 全てのレベルが従業員 - Aの上司はB、Bの上司はC、という自己結合が社長まで続く 階層の種類（バランス階層、不規則階層、可変深度階層） 3.4.2 BEAM✲ > モデルストーミングの手順 > 2. ディメンションのモデリング > ディメンション階層 30
31. 可変深度階層は分析しづらいのでなるべく避ける 可変深度階層は分析を行いづらい。他の種類の階層に出来ないか、改めてデータを確認する。 確認結果 対応 バランス階層や不規則階層としても捉える 事ができる データを非正規化し、レベル数を整える 例）人事階層のレベルを［事業部 > 部 > チーム］の 3つに整理する やはり、可変深度階層としか捉えられない 階層マップ（6.1.5）を使う 6.1.5 BEAM✲ > モデルストーミングの手順 > 2. ディメンションのモデリング > ディメンション階層 31 経験豊富な ディメンションモデラー
32. 階層の種類（単一親階層、複数親階層） 階層には、単一親階層と複数親階層がある。下図は複数親階層「製品」の例。 • 大半の製品は1つの製品タイプに属する • しかし、「iPip Phone」は「電話」と「メディアプレイヤー」という2つの製品タイプに属する 製品タイプ別の売上を集計する際は、二重集計にならないように注意する。 3.4.2 BEAM✲ > モデルストーミングの手順 > 2. ディメンションのモデリング > ディメンション階層 32
33. ディメンションの定義と発見においては、以下の2つが重要。 • ディメンション階層 • ディメンションの履歴 ディメンションの論点：「ディメンション階層」と「履歴」 3.1.1 BEAM✲ > モデルストーミングの手順 > 2. ディメンションのモデリング 33 聡明なモデラー
34. データ分析の際には、履歴が必要になるかもしれない。 そのため、ディメンションの過去の値を分析で使うか確 認する。 履歴値が必要な場合は「有効日付範囲」を追加する。 これにより、効率的に履歴を残すことができる。 SCDタイプ2と呼ばれる形式。 ディメンションの値の更新手順は以下。 1. データソースの値の更新を検知 2. 新しい値を持つ、新しいレコードを追加 3. 古いレコードの「有効終了日」と新しいレコード の「有効開始日」に現在の日付を記録 ディメンションの履歴：過去の値に関する要件を記録 3.5 BEAM✲ > モデルストーミングの手順 > 2. ディメンションのモデリング > ディメンションの履歴 34
35. 操作変更ログを用いた有効日付範囲の生成 履歴が重要な項目について、操作変更ログを記録したテーブルが存在することがある。 LEAD 関数を使って、操作変更ログからSCDタイプ2の形式にデータを持ち替えるビューを作ると便利。 記録日 商品名 価格 2025-01-01 ハンバーガー 100円 2025-04-01 ハンバーガー 120円 2025-01-01 ポテト 150円 create view 商品価格 as ( select 商品名, 価格, 記録日 as 有効開始日, ifnull( lead(記録日) over(partition by 商品名 order by 記録日), date(“9999-12-31”) ) as 有効終了日 from 商品価格改訂履歴 ) 商品価格改訂履歴 商品名 価格 有効開始日 有効終了日 ハンバーガー 100円 2025-01-01 2025-04-01 ハンバーガー 120円 2025-04-01 9999-12-31 ポテト 150円 2025-01-01 9999-12-31 商品価格［履歴値］ BEAM✲ > モデルストーミングの手順 > 2. ディメンションのモデリング > ディメンションの履歴 訳者あとがき 35
36. モデルストーミングの手順 1. ビジネスイベントのモデリング 2. ディメンションのモデリング 3. イベントマトリックスに追加 4. タスクをバックログに追加 4.2 BEAM✲ > モデルストーミングの手順 36
37. イベントをイベントマトリックスに追加 ビジネスイベントとディメンションのモデリング結果をイベントマトリックス（4.1.3）に記録する。 時間の都合により、詳細は省略。 「イベントマトリックス」を簡略化した「ディメンショナルマトリックス」も存在する。 実務的な観点では、こちらの方が役立つと感じたため、代わりに紹介する。 4.1.3 BEAM✲ > モデルストーミングの手順 > 3. イベントマトリックスに追加 37 アジャイルモデラー ステークホルダー
38. データ基盤構築プロジェクトを開始する際には、ディ メンショナルマトリックスを作ると便利。縦軸にビジ ネスプロセス、横軸にディメンションを並べた行列。 何をどの順番で実装するか議論する際に役立つ。 例えば、右図からは以下の事柄が読み取れる。 ディメンショナルマトリックス：データ基盤の概観 わかったこと 考えたこと 「製品」や「部品」ディメンション は、多くのビジネスプロセスで使われ る（適合ディメンション） 「製品」や「部品」を先に実装してお くと、多くのビジネスプロセスの分析 で使い回すことができそう 「試験」ディメンションは「製造」プ ロセスでしか使われない （非適合ディメンション） 使い回しの観点において、「試験」の 実装の優先度は低そう 「製造」ビジネスプロセスは重要そう だが、完全なイベントを準備するには 7個のディメンションを整備する必要 がある まずは2つのディメンションで済む 「製品在庫」プロセスを素早く提供す る。すると「製品」ディメンションが 作成されるため、「製造」プロセスの 実装期間を短縮できそう ディメンション ビ ジ ネ ス プ ロ セ ス ※一部改変 4.1.2 BEAM✲ > モデルストーミングの手順 > 3. イベントマトリックスに追加 38
39. モデルストーミングの手順 1. ビジネスイベントのモデリング 2. ディメンションのモデリング 3. イベントマトリックスに追加 4. タスクをバックログに追加 4.2 BEAM✲ > モデルストーミングの手順 39
40. 最も重要なイベントと、その全てのディメンションについて、 モデリングが終わっていれば作業終了。 ステークホルダーと一緒に、タスクの優先順位を評価する。 そして、プロダクトバックログに追加する。 数字は重要度を表す。 「ディメンション → イベント → レポート」 の順番で降順に点数を付ける。 （点数の付け方の詳細は4.3を参照） 実装の際は、重要度が高い順番に着手する。 1. 「製品」「顧客」などのディメンションを実装 2. ディメンションを結合した「顧客の注文」イベントを実装 3. イベントを集計した「大口の注文」「今年の注文」レポー トを実装、ステークホルダーに提供 タスクをバックログに追加：実装順序を決める 重要度 ※一部改変 4.3 BEAM✲ > モデルストーミングの手順 > 4. タスクをバックログに追加 40 アジャイルモデラー ステークホルダー
41. ディメンショナルモデリング データ分析のためのモデリング手法 BEAM✲ ステークホルダーからデータ要件を収集するためのテクニック データモデリングのデザインパターン 収集したデータ要件を実装する上で役立つベストプラクティス 41
42. ファクト • ファクトの加算性 • ファクトテーブルの種類 • 発展型イベントのモデリング ディメンション • ハイブリッドSCDビュー • 多値ディメンション • ピボットディメンション 収集したデータ要件の実装に役立つ、デザインパターンについて紹介する データモデリングのデザインパターン 42 パターンユーザー
43. • ファクトにとって最も重要な概念は「加算性」 ◦ 加算性：ファクトの値を合計して意味のある答えを生み出せるかどうか • ファクトの種類は以下の3つ ファクトの加算性の分類（加算型、非加算型、準加算型） ファクトの種類 解説 具体例 （完全）加算型ファクト 利用可能なディメンションをどう組み合わせた上で合計しても、 意味のある結果を得ることができる 売上、割引金額 非加算型ファクト どのディメンションに対しても、値を合計することができない 割引パーセント 準加算型ファクト 値を合計できるディメンションと、できないディメンションがある 製品在庫 8.4 データモデリングのデザインパターン > ファクト > ファクトの加算性 43
44. 日付 店舗 売上 2024年7月1日 POMStore 表参道 100万円 2024年7月1日 POMStore 銀座 200万円 … … … 2024年7月7日 POMStore 銀座 500万円 売上ファクト 加算型ファクトは、利用可能なディメンションをどう組み合わせた上で合計しても、意味のある結果を得られる。 例えば、「売上」は、「店舗」ごとに合計しても、「週」ごとに合計しても、意味のある結果を得られる。 加算型ファクト：値を加算しても意味が保たれる 店舗 売上 POMStore 銀座 2000万円 POMStore 表参道 700万円 … … 週 売上 2024年7月第1週 5000万円 … … 店舗別売上 週次売上 8.4.1 データモデリングのデザインパターン > ファクト > ファクトの加算性 > 加算型ファクト 44
45. 非加算型ファクトは、どのディメンションに対しても、値を合計することができない。 よくある例は「中央値」や「ユニークユーザー数」など。 例えば、「割引パーセント」は、「製品」に対しても、「日付」に対しても、値を合計する事ができない。 非加算型ファクト：値を加算すると意味が保たれない 注文日時 製品 定価 売上 割引パーセント 2024-07-01 10:00:00 POMBook Air 10万円 8万円 20%オフ 2024-07-01 15:00:00 POMBook Pro 20万円 10万円 50%オフ 2024-07-01 18:00:00 POMBook Pro 20万円 10万円 50%オフ … … … … … 製品 売上 割引パーセント POMBook Pro 20万円 100%オフ？ POMBook Air 8万円 20%オフ … … … 日付 売上 割引パーセント 2024年7月1日 28万 120%オフ？ … … … 売上ファクト 製品別売上 日次売上 8.4.2 データモデリングのデザインパターン > ファクト > ファクトの加算性 > 非加算型ファクト 45
46. 非加算型ファクト「割引パーセント」は扱いづらい。 ファクトテーブルには加算型ファクト「割引金額」を保存しておき、分析時に「割合パーセント」に変換する。 非加算型ファクトへの対応：加算型ファクトに持ち替えて保存 製品 割引前 売上 売上 割引金額 割引パーセント POMBook Pro 40万円 20万円 20万円 50%オフ（20万円 ÷ 40万円） POMBook Air 10万円 8万円 2万円 20%オフ（2万円 ÷ 10万円） 注文日時 製品 定価 売上 割引金額 2024-07-01 10:00:00 POMBook Air 10万円 8万円 2万円 2024-07-01 15:00:00 POMBook Pro 20万円 10万円 10万円 2024-07-01 18:00:00 POMBook Pro 20万円 10万円 10万円 … … … … … 売上ファクト 製品別売上 注文日 割引前 売上 売上 割引金額 割引パーセント 2024年7月1日 50万円 28万円 22万円 44%オフ（22万円 ÷ 50万円） 日次売上 8.4.2 データモデリングのデザインパターン > ファクト > ファクトの加算性 > 非加算型ファクト 46
47. 準加算型ファクトには、値を合計できるディメンションと、できないディメンションがある。 例えば「在庫」は、「店舗」に対しては合計できるが、「日付」に対しては合計できない。 準加算型ファクト：値を加算できる場合とできない場合がある 日付 店舗 在庫 2024年7月1日 POMStore 表参道 10台 2024年7月1日 POMStore 銀座 20台 2024年7月2日 POMStore 表参道 5台 2024年7月2日 POMStore 銀座 18台 … … … 日付 店舗ごとの在庫の合計 2024年7月1日 30台 2024年7月2日 23台 … … 店舗 2024年7月の在庫の合計 POMStore 表参道 15台？ POMStore 銀座 38台？ … … 在庫ファクト 日次在庫（店舗に対して合計） 店舗別在庫（日付に対して合計） 8.4.3 データモデリングのデザインパターン > ファクト > ファクトの加算性 > 準加算型ファクト 47
48. where 日付 = last_day(日付, month) 「2024年7月の在庫の合計」とは「2024年7月の営業が終了した時点における在庫」を指すはず。 そこで、「（日ごとの）在庫ファクト」とは別に、月末のレコードに絞り込んだ「月ごとの在庫ファクト」を作成。 準加算型ファクトへの対応：末日に絞り込んだビューを作成 日付 店舗 在庫 2024年7月1日 POMStore 表参道 10台 2024年7月1日 POMStore 銀座 20台 … … … 2024年7月31日 POMStore 表参道 6台 2024年7月31日 POMStore 銀座 14台 2024年8月1日 POMStore 表参道 7台 … … … 月 店舗 （月末時点の）在庫 2024年7月 POMStore 表参道 6台 2024年7月 POMStore 銀座 14台 2024年8月 POMStore 表参道 12台 2024年8月 POMStore 銀座 16台 … … … （日ごとの）在庫ファクト 月ごとの在庫ファクト データモデリングのデザインパターン > ファクト > ファクトの加算性 > 準加算型ファクト 応用 48
49. ファクトテーブルの種類 （トランザクションファクトテーブル、定期スナップショット、累積スナップショット） ビジネスイベントのイベントタイプは3種類。 • 離散型 • 反復型 • 発展型 これらに対応するファクトテーブルは以下。 • トランザクション・ファクトテーブル • 定期スナップショット • 累積スナップショット 8.1 データモデリングのデザインパターン > ファクト > ファクトテーブルの種類 49
50. ビジネスイベントで重要なのは、 イベントタイプを見抜くこと。 離散型イベント • 単発のイベント • 例）「商品の購入」 反復型イベント • 定期的に繰り返されるイベント • 例）「利息の請求」 ◦ 利息は毎月計算される 発展型イベント • 時間をかけて進行するイベント • 例）「卸売の注文」 ◦ 「注文 → 出荷 → 配達 → 支払」 と状態が発展していく 【再掲】イベントタイプの種類（離散型、反復型、発展型） ※一部改変 2.1.1 BEAM✲ > モデルストーミングの手順 > 1.ビジネスイベントのモデリング 50
51. トランザクション・ファクトテーブル：離散型イベントの保存 トランザクション・ファクトテーブルは、離散型イベントを保存するために使用される。 例）顧客の注文。誰が、何の製品を、いつ、どこの店舗で購入したのか 8.1.1 データモデリングのデザインパターン > ファクト > ファクトテーブルの種類 51 データモデリングのデザインパターン > ファクト > ファクトテーブルの種類
52. 定期スナップショット：反復型イベントの保存 定期スナップショットは、反復型イベントを保存するために使用される。 例）在庫。どの店舗に、何の製品の在庫が、いつ、どれくらいあったのか 8.1.2 データモデリングのデザインパターン > ファクト > ファクトテーブルの種類 52
53. 累積スナップショットは、発展型イベントを保存するために使用される。 例）本の貸出。「貸出 → 返却/紛失/返却期限超過」と状態が発展する 累積スナップショット：発展型イベントの保存 8.1.3 53 データモデリングのデザインパターン > ファクト > ファクトテーブルの種類
54. 発展型イベントをモデリングし、累積スナップショットを構築する際は、 以下の2つを考慮すると便利。 • タイムライン • 状態カウント 発展型イベントのモデリングに役立つ、2つの仕組み 8.3 データモデリングのデザインパターン > ファクト > 発展型イベントのモデリング 54 聡明なモデラー
55. 前提 • 発展型イベント「顧客の注文」を考える • 多くの注文は、配達が一度に行われる • 1つの注文が複数に分割して配達されることもある ◦ 渋谷区による「POMBook Air」の大量発注 ▪ 注文台数は100台 ▪ 現段階の配達完了台数は70台 • 事前にこのデータ要件を把握したい ◦ 状態「配達が部分的に完了」の扱いを議論するため 課題 「注文が分割して配達されうる」という要件を、 どのように記録しておく？ 課題：状態の発展は、必ずしも一度に起こるとは限らない データモデリングのデザインパターン > ファクト > 発展型イベントのモデリング > タイムライン 55 8.3.2 顧客 製品 注文台数 配達完了台数 一郎さん iPip Blue Suede 1台 1台 二郎さん iPOM Pro 1台 0台 渋谷区 POMBook Air 100台 70台 … … … … 顧客の注文 頭を悩ます人
56. タイムラインを使って、状態の発展に関する要件を記録する。 発展型イベントのマイルストーンの時系列を表す。 • 「注文日」は固定で1つなため、固定マイルストーン「　」として記録 • 「配達完了日」は複数の値を取りうるため、反復マイルストーン「　」として記録 名前の付いた間隔があれば、それも記録しておく（「配達時間」など）。後から分析で使うかもしれない。 解決策：タイムラインを用いて、状態の発展に関する要件を記録する 56 8.3.2 データモデリングのデザインパターン > ファクト > 発展型イベントのモデリング > タイムライン 注文日 配達完了日 配達時間 「顧客の注文」のタイムライン。図8-6を元に作成 時間
57. 前提 • 配達が完了した注文件数を計算したい • 配達が完了すると「配達日」が付与されるため、「count(配達日)」を計算すればよさそう 課題 一部しか配達が完了していない注文が、「count(配達日)」に含まれてしまう。 課題：count(配達日) は、配達が完了した注文件数を正しく計算できない 8.3.1 データモデリングのデザインパターン > ファクト > 発展型イベントのモデリング > 状態カウント 57 顧客 製品 注文日 配達日 注文台数 配達完了台数 一郎さん iPip Blue Suede 2025年2月1日 2025年2月3日 1台 1台 二郎さん iPOM Pro 2025年2月3日 null 1台 0台 渋谷区 POMBook Air 2025年2月5日 2025年2月9日 100台 70台 … … … … … … 顧客の注文［発展型イベント］ 分割配達の日付が存在する ↓ count(配達日)に含まれてしまう 配達はまだ 完了していない
58. 解決策 初期値は「0」、配達が全て完了したら「1」になる状態カウント「配達済み」列を追加する。 「sum(配達済み)」を計算することで、配達が完全に完了した注文件数を計算できる。 解決策：状態カウントを用いて、その状態に達したかを正しく判定する 8.3.1 データモデリングのデザインパターン > ファクト > 発展型イベントのモデリング > 状態カウント 58 顧客 製品 注文日 配達日 注文台数 配達完了台数 配達済み 一郎さん iPip Blue Suede 2025年2月1日 2025年2月3日 1台 1台 1 二郎さん iPOM Pro 2025年2月3日 null 1台 0台 0 渋谷区 POMBook Air 2025年2月5日 2025年2月9日 100台 70台 0 … … … … … … … 顧客の注文［発展型イベント］
59. ファクト • ファクトの加算性 • ファクトテーブルの種類 • 発展型イベントのモデリング ディメンション • ハイブリッドSCDビュー • 多値ディメンション • ピボットディメンション データモデリングのデザインパターン > ディメンション 59 パターンユーザー
60. 課題：従業員の「当時の勤務地」と「現在の勤務地」の両方を分析したい 前提 「従業員」ディメンションを例に考える。履歴値と現在値の両方を使って分析したい。 • 履歴値の分析例：従業員は、今まで働いてきた勤務地ごとに、経費をいくら使った？ • 現在値の分析例：現在東京で働く従業員は、今までに経費をいくら使った？ 課題 「当時の勤務地」と「現在の勤務地」の両方で従業員を分析できるようにするには、 「従業員」ディメンションをどのようにモデリングすればいい？ 6.2.1 データモデリングのデザインパターン > ディメンション > ハイブリッドSCDビュー 60 頭を悩ます人
61. 解決策：履歴値と現在値の両方をもつ「ハイブリッドSCDビュー」を作る 解決策 「ハイブリッドSCDビュー」を作る。履歴値と現在値の両方を兼ね備えた、ハイブリッドなビューテーブル。 ステークホルダーは同じ「従業員」テーブルを使って、 「当時の勤務地」と「現在の勤務地」の両方を使った分析ができるようになる。 6.2.1 データモデリングのデザインパターン > ディメンション > ハイブリッドSCDビュー 従業員名 当時の勤務地 現在の勤務地 有効開始日 有効終了日 現在の値か 一郎さん 大阪 東京 2020年4月1日 2024年7月1日 いいえ 一郎さん 東京 東京 2024年7月1日 9999年12月31日 はい 二郎さん 名古屋 名古屋 2021年1月1日 9999年12月31日 はい … … … … … … 61 「従業員」テーブル（ハイブリッドSCDビューの形式で作成） 聡明なモデラー
62. ①：「従業員」の履歴値テーブルをSCDタイプ2で作成 ②：現在値に絞ったビュー「現在の従業員」を作成 ③：②を①に結合 ④：列名を「当時の勤務地」「現在の勤務地」に変更 ハイブリッドSCDビューの作成手順 従業員名 勤務地 有効開始日 有効終了日 現在の値か 一郎さん 大阪 2020年4月1日 2024年7月1日 いいえ 一郎さん 東京 2024年7月1日 9999年12月31日 はい 二郎さん 名古屋 2021年1月1日 9999年12月31日 はい 従業員名 勤務地 一郎さん 東京 二郎さん 名古屋 従業員名 当時の 勤務地 現在の 勤務地 有効開始日 有効終了日 現在の値 か 一郎さん 大阪 東京 2020年4月1日 2024年7月1日 いいえ 一郎さん 東京 東京 2024年7月1日 9999年12月31日 はい 二郎さん 名古屋 名古屋 2021年1月1日 9999年12月31日 はい where 現在の値か = “はい” left join 現在の従業員 on 従業員.従業員名 = 現在の従業員.従業員名 ① 「従業員」の履歴値テーブルを作成 ② 「現在の従業員」テーブルを作成 ③ 結合 ④ 列名を変更 6.2.1 データモデリングのデザインパターン > ディメンション > ハイブリッドSCDビュー 62
63. 前提 • 右図のような「注文ファクト」がある ◦ 注文明細（注文×商品）ごとに 1つの売上が紐づく • 商品を組み合わせたコンボメニューがある 単品購入と比べて50円安くなる ◦ ハンバーガー + ポテト：550円 ◦ ランチコンボ：500円 課題 • 商品別売上を計算し、売れ筋商品を知りたい • 全体の売上を商品ごとに分解するには？ 課題：全体の売上を分解して「商品別売上」を計算したい 注文日 注文ID 商品 個数 売上 2025年1月10日 x ハンバーガー 1個 400円 2025年1月10日 x ドリンク 1個 100円 2025年1月12日 y ポテト 1個 150円 2025年1月15日 z ランチコンボ （ハンバーガーとポテト） 1個 500円 合計 3件 - 4個 1150円 商品 商品別売上 ハンバーガー ？ ドリンク ？ ポテト ？ 合計 1150円 注文ファクト 商品別売上 9.2.3 データモデリングのデザインパターン > ディメンション > 多値ブリッジテーブル 63
64. 「ランチコンボ」を商品ごとに分解することもできる。 • ハンバーガー：400円 • ポテト：150円 • 割引：-50円 しかし、商品別の売上の合計が1200円になってしまう。 割引が考慮されていない。 割引の-50円を商品ごとに分配するには？ 難しい点：「ランチコンボ」の割引を、商品ごとに分配する必要がある 注文日 注文ID 商品 個数 売上 2025年1月10日 x ハンバーガー 1個 400円 2025年1月10日 x ドリンク 1個 100円 2025年1月12日 y ポテト 1個 150円 2025年1月15日 z ハンバーガー 1個 400円 2025年1月15日 z ポテト 1個 150円 2025年1月15日 z 割引 1個 -50円 合計 3件 - 6個 1150円 商品 商品別売上 ハンバーガー 800円 ドリンク 100円 ポテト 300円 合計 1200円 ランチ コンボ 応用 データモデリングのデザインパターン > ディメンション > 多値ブリッジテーブル 64
65. 多値ブリッジテーブル「商品内訳ブリッジ」を作成。 商品ごとに、商品内訳を整理し、重み付け係数を決める。 解決策：「多値ブリッジテーブル」を作って配分割合を決める 商品 商品内訳 重み付け係数 ハンバーガー ハンバーガー 1 ポテト ポテト 1 ドリンク ドリンク 1 ランチコンボ ハンバーガー 0.7 ランチコンボ ポテト 0.3 商品内訳ブリッジ 9.2.3 データモデリングのデザインパターン > ディメンション > 多値ブリッジテーブル 65 聡明なモデラー
66. 「注文ファクト」に「商品内訳ブリッジ」を結合し、 「売上×重み付け係数」を計算する。 商品別売上の合計は1150円。全体の売上と一致する。 使用方法：「重み付け係数 × 売上」の合計を、商品別売上とする select 商品内訳ブリッジ.商品内訳 as 商品, sum(売上 * 重み付け係数) as 商品別売上 from 注文ファクト left join 商品内訳ブリッジ on 注文ファクト.商品 = 商品内訳ブリッジ.商品 group by 商品 order by 商品別売上 desc 商品 商品別売上 ハンバーガー 750円(400円 + 500円×0.7) ポテト 300円(150円 + 500円×0.3) ドリンク 100円 合計 1150円 注文日 注文ID 商品 重み付け係数 個数 売上 2025年1月10日 x ハンバーガー ハンバーガー：1 1個 400円 2025年1月10日 x ドリンク ドリンク：1 1個 100円 2025年1月12日 y ポテト ポテト：1 1個 150円 2025年1月15日 z ランチコンボ （ハンバーガーとポテト） ハンバーガー：0.7 ポテト：0.3 1個 500円 合計 3件 - - 4個 1150円 注文ファクト 商品別売上 9.2.3 データモデリングのデザインパターン > ディメンション > 多値ブリッジテーブル 66
67. with ハンバーガーを含む注文 as ( select 注文ファクト.注文ID from 注文ファクト left join 商品内訳ブリッジ on 注文.商品 = 商品内訳ブリッジ.商品 where 商品内訳ブリッジ.商品内訳 = "ハンバーガー" ) , ポテトを含む注文 as ( select 注文ファクト.注文ID from 注文ファクト left join 商品内訳ブリッジ on 注文.商品 = 商品内訳ブリッジ.商品 where 商品内訳ブリッジ.商品内訳 = "ポテト" ) select ハンバーガーを含む注文.注文ID from ハンバーガーを含む注文 left join ポテトを含む注文 on ハンバーガーを含む注文.注文ID = ポテトを含む注文.注文ID where ポテトを含む注文.注文ID is null -- ポテトが紐づかない注文に絞る 課題：多値ブリッジテーブルは、複雑なWHERE句との相性が悪い 前提 • 複雑な WHERE 句で絞り込みたい ◦ 「ハンバーガーを含み、ポテトを含ま ない注文IDはどれ？」 • 多値ブリッジテーブルで対処可能 ◦ 右図のクエリ 課題 • クエリが長くなってしまう ◦ 右図のクエリ 多値ブリッジテーブルは GROUP BY 句との相性が 良い（「商品別の売上」など）。 しかし、複雑な WHERE 句とは相性が悪い。 9.2.5 データモデリングのデザインパターン > ディメンション > ピボットディメンション クエリが 長い 67
68. 多値ブリッジテーブル「商品内訳ブリッジ」から、ピボットディメンション「商品内訳フラグ」を作る。 解決策：多値の行を列に持ち替えた「ピボットディメンション」を作る 商品名 ハンバーガーを含むか ポテトを含むか ドリンクを含むか ハンバーガー はい いいえ いいえ ポテト いいえ はい いいえ ドリンク いいえ いいえ はい ランチコンボ はい はい いいえ 商品名 商品内訳 重み付け係数 ハンバーガー ハンバーガー 1 ポテト ポテト 1 ドリンク ドリンク 1 ランチコンボ ハンバーガー 0.7 ランチコンボ ポテト 0.3 商品内訳フラグ 商品内訳ブリッジ 9.2.5 データモデリングのデザインパターン > ディメンション > ピボットディメンション 68 行を列に 持ち替え
69. 「商品内訳フラグ」テーブルの「◦◦を含むか」という列を組み合わせて、WHERE 句を作成する。 これにより、「ハンバーガーを含み、ポテトを含まない注文IDはどれ？」という複雑な問いに回答できる。 使用方法：「〇〇を含むか」という WHERE 句を作成する select 注文ID from 商品内訳フラグ付きの注文ファクト where ハンバーガーを含むか = "はい" and ポテトを含むか = "いいえ" 9.2.5 データモデリングのデザインパターン > ディメンション > ピボットディメンション 分析クエリ 69 注文日 注文 ID 売上 ハンバーガーを 含むか ポテトを 含むか ドリンク を含むか 2025年1月10日 x 500円 はい いいえ はい 2025年1月12日 y 150円 いいえ はい いいえ 2025年1月15日 z 500円 はい はい いいえ … … … … … … 商品内訳フラグ付きの注文ファクト ※ 説明の都合上、粒度を商品明細から注文IDに持ち替え
70. 多値ディメンション「商品内訳ブリッジ」を用いたクエリと比べて、 ピボットディメンション「商品内訳フラグ」を用いたクエリは、簡潔な表現が可能になる。 ピボットディメンションは、複雑な WHERE 句を簡潔に表現できる select 注文ID from 商品内訳フラグ付きの注文ファクト where ハンバーガーを含むか = "はい" and ポテトを含むか = "いいえ" 9.2.5 データモデリングのデザインパターン > ディメンション > ピボットディメンション クエリが短い！ with ハンバーガーを含む注文 as ( select 注文ID from 商品内訳ブリッジ付きの注文ファクト where 商品内訳 = "ハンバーガー" ) , ポテトを含む注文 as ( select 注文ファクト.注文ID from 商品内訳ブリッジ付きの注文ファクト where 商品内訳 = "ポテト" ) select ハンバーガーを含む注文.注文ID from ハンバーガーを含む注文 left join ポテトを含む注文 on ハンバーガーを含む注文.注文ID = ポテトを含む注文.注文ID where ポテトを含む注文.注文ID is null -- ポテトが紐づかない注文に絞る 商品内訳ブリッジを用いたクエリは冗長 商品内訳フラグを用いたクエリは簡潔 70
71. まとめ 71
72. 【再掲】発表のゴール 対象者 理想状態 書籍の購入を検討されている方 書籍の内容紹介を通じて、購入を後押しする 書籍をすでに購入された方 • 書籍の全体像が把握できるようになる • 書籍とスライドを併用することで、内容を深く理解できるようになる 72
73. まとめ • 「ディメンショナルモデリング」とは、データ分析のためのデータモデリング手法 ◦ 概要：ビジネスプロセスを「ファクト」と「ディメンション」に整理 ◦ 利点1：理解しやすい ◦ 利点2：データ分析用のクエリを簡略化できる • 『アジャイルデータモデリング』は、この「ディメンショナルモデリング」について書かれた本 ◦ パート1：モデルストーミング ◦ パート2：ディメンショナルデザインパターン ◦ 事例集：データ基盤構築に関する12の国内事例 • データモデリングに初めて触れる方は、まず事例集を読むのがオススメ ◦ データ利活用に対する解像度を高めることができる 73
74. 使いやすいデータの整備を通じて、組織にデータ分析を広めたい方は、 ぜひ『アジャイルデータモデリング』をお買い求めください！ 74
75. 「書籍を読むだけじゃ分からない」「手を動かして訓練したい」という人、 ぜひMENTA講座を受けてください！ハンズオンチュートリアルを提供しています！ ※風音屋のnoteにて受講案内を行っております。 ディメンショナルモデリングを実践したいデータエンジニア 75 株式会社風音屋のアドバイザーという立場からの宣伝です。 本講座は、株式会社風音屋が提供するものです。 ピクシブ株式会社とは一切関係がありません。
76. ピクシブ株式会社ではデータエンジニアを募集しています。 7.8PiBのデータ（2023年9月時点）と BigQuery, dbt, Looker を活用したデータ基盤を、 一緒に作っていきましょう。 アーキテクチャ紹介：『ピクシブのデータインフラと組織構造』 https://speakerdeck.com/kashira/pixiv-meetup-2023-pikusibunodeta-inhuratozu-zhi-gou-zao 採用ページ https://hrmos.co/pages/pixiv/jobs/2011757103743516699 宣伝：ピクシブ株式会社ではデータエンジニアを募集しています 76
77. 77 ご清聴ありがとうございました！