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

Slide 1

Slide 1 text

30分でわかる『アジャイルデータモデリング』 ucchi- 2025-02-14 Forkwell Library #81 1

Slide 2

Slide 2 text

自己紹介 ● アナリティクスエンジニア ● X: @hanon52_ ● 2021年ピクシブ株式会社に新卒入社 ○ 広告のデータ基盤設計、Looker や Salesforce の整備 ○ 社内の様々なプロダクトのデータ基盤構築、助言 ○ データの入口から出口まで何でもやります ● 美味しいご飯を食べたり作ったりするのが好き ● 株式会社風音屋のアドバイザーでもあります打出紘基(ucchi-) 2

Slide 3

Slide 3 text

『アジャイルデータモデリング』を30分で駆け抜ける発表の概要 3

Slide 4

Slide 4 text

発表のゴール対象者理想状態書籍の購入を検討されている方書籍の内容紹介を通じて、購入を後押しする書籍をすでに購入された方 ● 書籍の全体像が把握できるようになる ● 書籍とスライドを併用することで、内容を深く理解できるようになる 4

Slide 5

Slide 5 text

この本は、株式会社風音屋の社員・アドバイザー有志が中心となって出版されました。後半のパネルディスカッションでは、以下の訳者が登壇します。 ● 佐々木江亜（@0610Esa, 株式会社マネーフォワード） ● ゆずたそ（@yuzutas0, 株式会社風音屋）訳者紹介 5

Slide 6

Slide 6 text

Agile Data Warehouse Design ● データ要件の収集や実装をアジャイルに行う ● 最初に全てを設計してから実装するのではなく、 ● ビジネスプロセス（例：「注文」「閲覧」など）ごとに細かく実装し、ステークホルダーからフィードバックを受け、段階的に改善を繰り返す原著の主題：データウェアハウスの設計を「アジャイル」に行う 6

Slide 7

Slide 7 text

Collaborative Dimensional Modeling, from Whiteboard to Star Schema ● データエンジニアが頑張ってソースコードから業務ロジックを読み解くのではなく、 ● ビジネス側のステークホルダーと協調しながら、ホワイトボードでデータ要件を収集し、それをスタースキーマに落とし込むデータエンジニアは、エンジニア側だけでなく、ビジネス側のステークホルダーと会話することが多い。モデリングの技術的な話から、データ要件をヒアリングする方法まで踏み込んで解説されているのがこの本の特徴。原著の副題：ステークホルダーと「協調」しながらデータ要件を収集 7

Slide 8

Slide 8 text

● パート1：「モデルストーミング」 ○ BIやレポートで求められるデータ要件をステークホルダーから収集する一連の方法 ● パート2：「ディメンショナルデザインパターン」 ○ 収集したデータ要件を実装する上で役立つ様々なテクニック ● 日本語版限定コンテンツ：事例集 ○ 国内事例12社を掲載データモデリングに初めて触れる方は、まず事例集を読むのがおすすめ。データを活用することがビジネスにどう役立つのか、解像度を高めることができる。本の章立て：2部構成 + 事例集 8

Slide 9

Slide 9 text

原著の初版が発売されたのは2011年。BigQuery が一般提供された年。それ以降、データウェアハウスの性能は大きく向上した。 ● データ処理速度が向上 ● ストレージ費用が低下 ● Array, Struct, Json など半構造化データのサポートが充実現在は、この本に書かれたベストプラクティスより良い選択肢があるかもしれない。それでも、現代の最適解を議論するための土台として、古典的なアプローチを理解することには意義がある。 ● 課題（Why）をどのような発想（What）で解決するのかを理解する ● 具体的な手段（How）は、現代のデータウェアハウスの性能向上を踏まえて工夫すればよい 2011年時点のベストプラクティスを学ぶ意義 9

Slide 10

Slide 10 text

本発表では3つのテーマについて解説するディメンショナルモデリングデータ分析のためのモデリング手法 BEAM✲ ステークホルダーからデータ要件を収集するためのテクニックデータモデリングのデザインパターン収集したデータ要件を実装する上で役立つベストプラクティス 10

Slide 11

Slide 11 text

本発表では3つのテーマについて解説するディメンショナルモデリングデータ分析のためのモデリング手法 BEAM✲ ステークホルダーからデータ要件を収集するためのテクニックデータモデリングのデザインパターン収集したデータ要件を実装する上で役立つベストプラクティス 11

Slide 12

Slide 12 text

● 分析の対象となるビジネスプロセスを、ファクトとディメンションの2つに分解する ○ ファクト：ビジネスプロセスの指標 ○ ディメンション：ビジネスプロセスの分析軸 ● 右図の場合... ○ ビジネスプロセス：「販売」 ○ ファクト：「売上高」「販売台数」など ○ ディメンション： ■ 何を：「コンピューター製品の」 ■ いつ：「2010年の」「四半期ごとの」 ■ どこで：「アウトレットの」ディメンショナルモデリング：ファクト × ディメンション書籍の「章.節.項」を表します。さらに学びたい方はぜひ書籍をご参照ください！ 1.1.2 ディメンショナルモデリング 12

Slide 13

Slide 13 text

店舗店舗キー店舗コード店舗名 URL スタースキーマ：1つのファクトと多数のディメンションスタースキーマとは、ディメンショナルモデリングに沿ったスキーマを指す。星の形をしている。作成手順は以下。 ● ビジネスプロセスを「ディメンション」と「ファクト」の2つに分解 ● 中央に1つのファクトを配置 ● 周辺に多数のディメンションを配置販売ファクト日付キー製品キー店舗キー従業員キープロモーションキー販売数売上高従業員従業員キー従業員コード従業員名役職カレンダー日付キー日付月年平日フラグ祝日フラグ製品製品キー製品コード製品の種類ブランド名サブカテゴリーカテゴリープロモーションプロモーションキープロモーションコードプロモーション名広告タイプディメンショナルモデリング 1.1.2 13

Slide 14

Slide 14 text

リレーショナルデータモデリング（ERモデリング）とディメンショナルモデリングは、用途が異なる。リレーショナルデータモデリングは、アプリケーションの要求に応える。 ● 正規化を行うことで、書き込みが必要な場所を最小限にし、高速な書き込みを可能にする ● 値が1か所にしか記録されないため、値の一貫性を保ちやすくなるディメンショナルモデリングは、データ分析の要求に応える。 ● 「ディメンション」と「ファクト」の2種類しかないため、ビジネス側のステークホルダーでも理解しやすい ● 複数のテーブルを事前に結合し「ディメンション」「ファクト」にまとめておくことで、分析クエリが簡潔になるディメンショナルモデリングはデータ分析の要求に応える手法リレーショナルデータモデリングディメンショナルモデリング代表的なスキーマ第3正規形スタースキーマ用途アプリケーションデータ分析要求高速な書き込み値の一貫性理解しやすさ分析クエリの簡潔さ特徴値を正規化して持つことで、更新が必要な箇所を減らす値が1か所にしか記録されないため、値の一貫性を保ちやすい「ファクト」と「ディメンション」の2種類だけなので理解しやすい事前に結合するため分析クエリが簡潔になるデータモデリング手法の比較ディメンショナルモデリング 1.1 14

Slide 15

Slide 15 text

ディメンショナルモデリングデータ分析のためのモデリング手法 BEAM✲ ステークホルダーからデータ要件を収集するためのテクニックデータモデリングのデザインパターン収集したデータ要件を実装する上で役立つベストプラクティス 15

Slide 16

Slide 16 text

BEAM✲は、この本で独自に提唱されている、データ要件収集のためのフレームワーク。 Business Event：データ分析の対象となるビジネスプロセスの具体例 Analysis：要件分析 Modeling：モデリング ✲：成果物として手に入るスタースキーマ BEAM✲：Business Event Analysis and Modeling 1.3 BEAM✲ 16 モデルストーマー人物一覧と定義は、書籍の「はじめに」で紹介されています。

Slide 17

Slide 17 text

BEAM✲に基づいた要件収集とモデリングのアプローチを「モデルストーミング」と呼ぶ。「ブレインストーミング」と「データモデリング」のテクニックを組み合わせ、ステークホルダーと協調しながらデータモデルを作り上げる。手順は以下。 1. ビジネスイベントのモデリング 2. ディメンションのモデリング 3. イベントマトリックスに追加 4. タスクをバックログに追加モデルストーミング：データ要件の収集とモデリング 4.2 BEAM✲ > モデルストーミング 17

Slide 18

Slide 18 text

モデルストーミングの手順 1. ビジネスイベントのモデリング 2. ディメンションのモデリング 3. イベントマトリックスに追加 4. タスクをバックログに追加 4.2 BEAM✲ > モデルストーミングの手順 18

Slide 19

Slide 19 text

ステークホルダーに、分析したいビジネスイベントを尋ねる。「「今1番分析したいのは、誰が何をするイベントですか？」「顧客が製品を注文すること、ですかね...」ビジネスイベントのモデリング：対話を通じたイベントの発見ビジネスイベント「顧客の注文」を発見 2.2.1 BEAM✲ > モデルストーミングの手順 > 1.ビジネスイベントのモデリング 19 ステークホルダーあなたアジャイルモデラー（モデルストーミングを促進する人）

Slide 20

Slide 20 text

ビジネスイベントで重要なのは、イベントタイプを見抜くこと。離散型イベント ● 単発のイベント ● 例）「商品の購入」反復型イベント ● 定期的に繰り返されるイベント ● 例）「利息の請求」 ○ 利息は毎月計算される発展型イベント ● 時間をかけて進行するイベント ● 例）「卸売の注文」 ○ 「注文 → 出荷 → 配達 → 支払」と状態が発展していくイベントタイプの種類（離散型、反復型、発展型） ※一部改変 2.1.1 BEAM✲ > モデルストーミングの手順 > 1.ビジネスイベントのモデリング 20

Slide 21

Slide 21 text

イベントタイプを持ち替えた方がよいことがある「データベースに記録されているイベントタイプ」と「分析に必要なイベントタイプ」は異なることがある。その場合、あらかじめイベントタイプを持ち替えた方がよい。以下2つの事例を紹介する。 ● 離散型 → 反復型 ● 離散型 → 発展型「応用」は 2025 年 2 月時点での ucchi- の考えです BEAM✲ > モデルストーミングの手順 > 1.ビジネスイベントのモデリング応用 21 聡明なモデラー

Slide 22

Slide 22 text

離散型イベントから反復型イベントに持ち替えた方がよい例決済日時ユーザー内容金額 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 差額のみ決済

Slide 23

Slide 23 text

広告について考える。離散型イベント「閲覧」と「クリック」を結合して、クリック率の分析を行いたい。しかし、広告のログ数は膨大なため、分析のたびに結合するとコストがかかる。状態が「閲覧」から「クリック」へ変わる発展型イベント「広告配信」に事前に持ち替えることで、分析時のコストを削減できる。閲覧タイムスタンプ広告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

Slide 24

Slide 24 text

イベントタイプを見極め、イベントの振る舞いを記録するため、ステークホルダーに具体例を尋ねる。具体例の収集方法の詳細は 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 ステークホルダーアジャイルモデラー

Slide 25

Slide 25 text

ER図を使ってデータ要件を記録することもできる。しかし、今回はビジネス側のステークホルダーと対話しながら、データ分析の要件収集を行なっている。対話の相手が慣れ親しんだ表形式でデータ要件を記録することで、円滑に要件収集を進めることができる。 BEAM✲テーブルを使う理由：ステークホルダーが理解しやすい ER図 BEAM✲テーブル 1.3.2 BEAM✲ > モデルストーミングの手順 > 1.ビジネスイベントのモデリング 25

Slide 26

Slide 26 text

モデルストーミングの手順 1. ビジネスイベントのモデリング 2. ディメンションのモデリング 3. イベントマトリックスに追加 4. タスクをバックログに追加 4.2 BEAM✲ > モデルストーミングの手順 26

Slide 27

Slide 27 text

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 アジャイルモデラーステークホルダー

Slide 28

Slide 28 text

ディメンションの定義と発見においては、以下の2つが重要。 ● ディメンション階層 ● ディメンションの履歴ディメンションの論点：「ディメンション階層」と「履歴」 3.1.1 BEAM✲ > モデルストーミングの手順 > 2. ディメンションのモデリング 28 聡明なモデラー

Slide 29

Slide 29 text

ディメンションの多くには階層がある。下図の場合... ● 上から順番に「カテゴリー」「サブカテゴリー」「製品」が存在する ● 製品に紐づく「サブカテゴリー」や「カテゴリー」は1つだけディメンション階層の情報を事前に収集して、ドリルダウンやロールアップする際の仕様を事前に取り決める。 ● 例）「サブカテゴリー」別の分析を行う際、「POMServer」のラベルをどうしたい？ ○ 「カテゴリー」を使う、「製品名」を使う、「カテゴリー_製品名」とする、など詳細は（6.3.2）を参照ディメンション階層：ドリルダウンやロールアップの要件を記録 3.4 BEAM✲ > モデルストーミングの手順 > 2. ディメンションのモデリング > ディメンション階層サブカテゴリーが無い 29

Slide 30

Slide 30 text

階層には、以下の3種類が存在する。バランス階層深さの数が固定、それぞれに固有の階層レベル名がある。例：日付。必ず「日→週→月→年」にロールアップされる不規則階層階層レベルの値の一部が欠けている。例：製品。「POMServer」はサブカテゴリーの値がない可変深度階層（再帰的階層）名前が付けられていないレベルが存在し、深さが可変。例：人事階層 - 全てのレベルが従業員 - Aの上司はB、Bの上司はC、という自己結合が社長まで続く階層の種類（バランス階層、不規則階層、可変深度階層） 3.4.2 BEAM✲ > モデルストーミングの手順 > 2. ディメンションのモデリング > ディメンション階層 30

Slide 31

Slide 31 text

可変深度階層は分析しづらいのでなるべく避ける可変深度階層は分析を行いづらい。他の種類の階層に出来ないか、改めてデータを確認する。確認結果対応バランス階層や不規則階層としても捉える事ができるデータを非正規化し、レベル数を整える例）人事階層のレベルを［事業部 > 部 > チーム］の 3つに整理するやはり、可変深度階層としか捉えられない階層マップ（6.1.5）を使う 6.1.5 BEAM✲ > モデルストーミングの手順 > 2. ディメンションのモデリング > ディメンション階層 31 経験豊富なディメンションモデラー

Slide 32

Slide 32 text

階層の種類（単一親階層、複数親階層）階層には、単一親階層と複数親階層がある。下図は複数親階層「製品」の例。 ● 大半の製品は1つの製品タイプに属する ● しかし、「iPip Phone」は「電話」と「メディアプレイヤー」という2つの製品タイプに属する製品タイプ別の売上を集計する際は、二重集計にならないように注意する。 3.4.2 BEAM✲ > モデルストーミングの手順 > 2. ディメンションのモデリング > ディメンション階層 32

Slide 33

Slide 33 text

ディメンションの定義と発見においては、以下の2つが重要。 ● ディメンション階層 ● ディメンションの履歴ディメンションの論点：「ディメンション階層」と「履歴」 3.1.1 BEAM✲ > モデルストーミングの手順 > 2. ディメンションのモデリング 33 聡明なモデラー

Slide 34

Slide 34 text

データ分析の際には、履歴が必要になるかもしれない。そのため、ディメンションの過去の値を分析で使うか確認する。履歴値が必要な場合は「有効日付範囲」を追加する。これにより、効率的に履歴を残すことができる。 SCDタイプ2と呼ばれる形式。ディメンションの値の更新手順は以下。 1. データソースの値の更新を検知 2. 新しい値を持つ、新しいレコードを追加 3. 古いレコードの「有効終了日」と新しいレコードの「有効開始日」に現在の日付を記録ディメンションの履歴：過去の値に関する要件を記録 3.5 BEAM✲ > モデルストーミングの手順 > 2. ディメンションのモデリング > ディメンションの履歴 34

Slide 35

Slide 35 text

操作変更ログを用いた有効日付範囲の生成履歴が重要な項目について、操作変更ログを記録したテーブルが存在することがある。 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

Slide 36

Slide 36 text

モデルストーミングの手順 1. ビジネスイベントのモデリング 2. ディメンションのモデリング 3. イベントマトリックスに追加 4. タスクをバックログに追加 4.2 BEAM✲ > モデルストーミングの手順 36

Slide 37

Slide 37 text

イベントをイベントマトリックスに追加ビジネスイベントとディメンションのモデリング結果をイベントマトリックス（4.1.3）に記録する。時間の都合により、詳細は省略。「イベントマトリックス」を簡略化した「ディメンショナルマトリックス」も存在する。実務的な観点では、こちらの方が役立つと感じたため、代わりに紹介する。 4.1.3 BEAM✲ > モデルストーミングの手順 > 3. イベントマトリックスに追加 37 アジャイルモデラーステークホルダー

Slide 38

Slide 38 text

データ基盤構築プロジェクトを開始する際には、ディメンショナルマトリックスを作ると便利。縦軸にビジネスプロセス、横軸にディメンションを並べた行列。何をどの順番で実装するか議論する際に役立つ。例えば、右図からは以下の事柄が読み取れる。ディメンショナルマトリックス：データ基盤の概観わかったこと考えたこと「製品」や「部品」ディメンションは、多くのビジネスプロセスで使われる（適合ディメンション）「製品」や「部品」を先に実装しておくと、多くのビジネスプロセスの分析で使い回すことができそう「試験」ディメンションは「製造」プロセスでしか使われない（非適合ディメンション）使い回しの観点において、「試験」の実装の優先度は低そう「製造」ビジネスプロセスは重要そうだが、完全なイベントを準備するには 7個のディメンションを整備する必要があるまずは2つのディメンションで済む「製品在庫」プロセスを素早く提供する。すると「製品」ディメンションが作成されるため、「製造」プロセスの実装期間を短縮できそうディメンションビジネスプロセス ※一部改変 4.1.2 BEAM✲ > モデルストーミングの手順 > 3. イベントマトリックスに追加 38

Slide 39

Slide 39 text

モデルストーミングの手順 1. ビジネスイベントのモデリング 2. ディメンションのモデリング 3. イベントマトリックスに追加 4. タスクをバックログに追加 4.2 BEAM✲ > モデルストーミングの手順 39

Slide 40

Slide 40 text

最も重要なイベントと、その全てのディメンションについて、モデリングが終わっていれば作業終了。ステークホルダーと一緒に、タスクの優先順位を評価する。そして、プロダクトバックログに追加する。数字は重要度を表す。「ディメンション → イベント → レポート」の順番で降順に点数を付ける。（点数の付け方の詳細は4.3を参照）実装の際は、重要度が高い順番に着手する。 1. 「製品」「顧客」などのディメンションを実装 2. ディメンションを結合した「顧客の注文」イベントを実装 3. イベントを集計した「大口の注文」「今年の注文」レポートを実装、ステークホルダーに提供タスクをバックログに追加：実装順序を決める重要度 ※一部改変 4.3 BEAM✲ > モデルストーミングの手順 > 4. タスクをバックログに追加 40 アジャイルモデラーステークホルダー

Slide 41

Slide 41 text

ディメンショナルモデリングデータ分析のためのモデリング手法 BEAM✲ ステークホルダーからデータ要件を収集するためのテクニックデータモデリングのデザインパターン収集したデータ要件を実装する上で役立つベストプラクティス 41

Slide 42

Slide 42 text

ファクト ● ファクトの加算性 ● ファクトテーブルの種類 ● 発展型イベントのモデリングディメンション ● ハイブリッドSCDビュー ● 多値ディメンション ● ピボットディメンション収集したデータ要件の実装に役立つ、デザインパターンについて紹介するデータモデリングのデザインパターン 42 パターンユーザー

Slide 43

Slide 43 text

● ファクトにとって最も重要な概念は「加算性」 ○ 加算性：ファクトの値を合計して意味のある答えを生み出せるかどうか ● ファクトの種類は以下の3つファクトの加算性の分類（加算型、非加算型、準加算型）ファクトの種類解説具体例（完全）加算型ファクト利用可能なディメンションをどう組み合わせた上で合計しても、意味のある結果を得ることができる売上、割引金額非加算型ファクトどのディメンションに対しても、値を合計することができない割引パーセント準加算型ファクト値を合計できるディメンションと、できないディメンションがある製品在庫 8.4 データモデリングのデザインパターン > ファクト > ファクトの加算性 43

Slide 44

Slide 44 text

日付店舗売上 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

Slide 45

Slide 45 text

非加算型ファクトは、どのディメンションに対しても、値を合計することができない。よくある例は「中央値」や「ユニークユーザー数」など。例えば、「割引パーセント」は、「製品」に対しても、「日付」に対しても、値を合計する事ができない。非加算型ファクト：値を加算すると意味が保たれない注文日時製品定価売上割引パーセント 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

Slide 46

Slide 46 text

非加算型ファクト「割引パーセント」は扱いづらい。ファクトテーブルには加算型ファクト「割引金額」を保存しておき、分析時に「割合パーセント」に変換する。非加算型ファクトへの対応：加算型ファクトに持ち替えて保存製品割引前売上売上割引金額割引パーセント 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

Slide 47

Slide 47 text

準加算型ファクトには、値を合計できるディメンションと、できないディメンションがある。例えば「在庫」は、「店舗」に対しては合計できるが、「日付」に対しては合計できない。準加算型ファクト：値を加算できる場合とできない場合がある日付店舗在庫 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

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

ファクトテーブルの種類（トランザクションファクトテーブル、定期スナップショット、累積スナップショット）ビジネスイベントのイベントタイプは3種類。 ● 離散型 ● 反復型 ● 発展型これらに対応するファクトテーブルは以下。 ● トランザクション・ファクトテーブル ● 定期スナップショット ● 累積スナップショット 8.1 データモデリングのデザインパターン > ファクト > ファクトテーブルの種類 49

Slide 50

Slide 50 text

ビジネスイベントで重要なのは、イベントタイプを見抜くこと。離散型イベント ● 単発のイベント ● 例）「商品の購入」反復型イベント ● 定期的に繰り返されるイベント ● 例）「利息の請求」 ○ 利息は毎月計算される発展型イベント ● 時間をかけて進行するイベント ● 例）「卸売の注文」 ○ 「注文 → 出荷 → 配達 → 支払」と状態が発展していく【再掲】イベントタイプの種類（離散型、反復型、発展型） ※一部改変 2.1.1 BEAM✲ > モデルストーミングの手順 > 1.ビジネスイベントのモデリング 50

Slide 51

Slide 51 text

トランザクション・ファクトテーブル：離散型イベントの保存トランザクション・ファクトテーブルは、離散型イベントを保存するために使用される。例）顧客の注文。誰が、何の製品を、いつ、どこの店舗で購入したのか 8.1.1 データモデリングのデザインパターン > ファクト > ファクトテーブルの種類 51 データモデリングのデザインパターン > ファクト > ファクトテーブルの種類

Slide 52

Slide 52 text

定期スナップショット：反復型イベントの保存定期スナップショットは、反復型イベントを保存するために使用される。例）在庫。どの店舗に、何の製品の在庫が、いつ、どれくらいあったのか 8.1.2 データモデリングのデザインパターン > ファクト > ファクトテーブルの種類 52

Slide 53

Slide 53 text

累積スナップショットは、発展型イベントを保存するために使用される。例）本の貸出。「貸出 → 返却/紛失/返却期限超過」と状態が発展する累積スナップショット：発展型イベントの保存 8.1.3 53 データモデリングのデザインパターン > ファクト > ファクトテーブルの種類

Slide 54

Slide 54 text

発展型イベントをモデリングし、累積スナップショットを構築する際は、以下の2つを考慮すると便利。 ● タイムライン ● 状態カウント発展型イベントのモデリングに役立つ、2つの仕組み 8.3 データモデリングのデザインパターン > ファクト > 発展型イベントのモデリング 54 聡明なモデラー

Slide 55

Slide 55 text

前提 ● 発展型イベント「顧客の注文」を考える ● 多くの注文は、配達が一度に行われる ● 1つの注文が複数に分割して配達されることもある ○ 渋谷区による「POMBook Air」の大量発注 ■ 注文台数は100台 ■ 現段階の配達完了台数は70台 ● 事前にこのデータ要件を把握したい ○ 状態「配達が部分的に完了」の扱いを議論するため課題「注文が分割して配達されうる」という要件を、どのように記録しておく？課題：状態の発展は、必ずしも一度に起こるとは限らないデータモデリングのデザインパターン > ファクト > 発展型イベントのモデリング > タイムライン 55 8.3.2 顧客製品注文台数配達完了台数一郎さん iPip Blue Suede 1台 1台二郎さん iPOM Pro 1台 0台渋谷区 POMBook Air 100台 70台 … … … … 顧客の注文頭を悩ます人

Slide 56

Slide 56 text

タイムラインを使って、状態の発展に関する要件を記録する。発展型イベントのマイルストーンの時系列を表す。 ● 「注文日」は固定で1つなため、固定マイルストーン「　」として記録 ● 「配達完了日」は複数の値を取りうるため、反復マイルストーン「　」として記録名前の付いた間隔があれば、それも記録しておく（「配達時間」など）。後から分析で使うかもしれない。解決策：タイムラインを用いて、状態の発展に関する要件を記録する 56 8.3.2 データモデリングのデザインパターン > ファクト > 発展型イベントのモデリング > タイムライン注文日配達完了日配達時間「顧客の注文」のタイムライン。図8-6を元に作成時間

Slide 57

Slide 57 text

前提 ● 配達が完了した注文件数を計算したい ● 配達が完了すると「配達日」が付与されるため、「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(配達日)に含まれてしまう配達はまだ完了していない

Slide 58

Slide 58 text

解決策初期値は「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 … … … … … … … 顧客の注文［発展型イベント］

Slide 59

Slide 59 text

ファクト ● ファクトの加算性 ● ファクトテーブルの種類 ● 発展型イベントのモデリングディメンション ● ハイブリッドSCDビュー ● 多値ディメンション ● ピボットディメンションデータモデリングのデザインパターン > ディメンション 59 パターンユーザー

Slide 60

Slide 60 text

課題：従業員の「当時の勤務地」と「現在の勤務地」の両方を分析したい前提「従業員」ディメンションを例に考える。履歴値と現在値の両方を使って分析したい。 ● 履歴値の分析例：従業員は、今まで働いてきた勤務地ごとに、経費をいくら使った？ ● 現在値の分析例：現在東京で働く従業員は、今までに経費をいくら使った？課題「当時の勤務地」と「現在の勤務地」の両方で従業員を分析できるようにするには、「従業員」ディメンションをどのようにモデリングすればいい？ 6.2.1 データモデリングのデザインパターン > ディメンション > ハイブリッドSCDビュー 60 頭を悩ます人

Slide 61

Slide 61 text

解決策：履歴値と現在値の両方をもつ「ハイブリッド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ビューの形式で作成）聡明なモデラー

Slide 62

Slide 62 text

①：「従業員」の履歴値テーブルを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

Slide 63

Slide 63 text

前提 ● 右図のような「注文ファクト」がある ○ 注文明細（注文×商品）ごとに 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

Slide 64

Slide 64 text

「ランチコンボ」を商品ごとに分解することもできる。 ● ハンバーガー：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

Slide 65

Slide 65 text

多値ブリッジテーブル「商品内訳ブリッジ」を作成。商品ごとに、商品内訳を整理し、重み付け係数を決める。解決策：「多値ブリッジテーブル」を作って配分割合を決める商品商品内訳重み付け係数ハンバーガーハンバーガー 1 ポテトポテト 1 ドリンクドリンク 1 ランチコンボハンバーガー 0.7 ランチコンボポテト 0.3 商品内訳ブリッジ 9.2.3 データモデリングのデザインパターン > ディメンション > 多値ブリッジテーブル 65 聡明なモデラー

Slide 66

Slide 66 text

「注文ファクト」に「商品内訳ブリッジ」を結合し、「売上×重み付け係数」を計算する。商品別売上の合計は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

Slide 67

Slide 67 text

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

Slide 68

Slide 68 text

多値ブリッジテーブル「商品内訳ブリッジ」から、ピボットディメンション「商品内訳フラグ」を作る。解決策：多値の行を列に持ち替えた「ピボットディメンション」を作る商品名ハンバーガーを含むかポテトを含むかドリンクを含むかハンバーガーはいいいえいいえポテトいいえはいいいえドリンクいいえいいえはいランチコンボはいはいいいえ商品名商品内訳重み付け係数ハンバーガーハンバーガー 1 ポテトポテト 1 ドリンクドリンク 1 ランチコンボハンバーガー 0.7 ランチコンボポテト 0.3 商品内訳フラグ商品内訳ブリッジ 9.2.5 データモデリングのデザインパターン > ディメンション > ピボットディメンション 68 行を列に持ち替え

Slide 69

Slide 69 text

「商品内訳フラグ」テーブルの「○○を含むか」という列を組み合わせて、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に持ち替え

Slide 70

Slide 70 text

多値ディメンション「商品内訳ブリッジ」を用いたクエリと比べて、ピボットディメンション「商品内訳フラグ」を用いたクエリは、簡潔な表現が可能になる。ピボットディメンションは、複雑な 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

Slide 71

Slide 71 text

まとめ 71

Slide 72

Slide 72 text

【再掲】発表のゴール対象者理想状態書籍の購入を検討されている方書籍の内容紹介を通じて、購入を後押しする書籍をすでに購入された方 ● 書籍の全体像が把握できるようになる ● 書籍とスライドを併用することで、内容を深く理解できるようになる 72

Slide 73

Slide 73 text

まとめ ● 「ディメンショナルモデリング」とは、データ分析のためのデータモデリング手法 ○ 概要：ビジネスプロセスを「ファクト」と「ディメンション」に整理 ○ 利点1：理解しやすい ○ 利点2：データ分析用のクエリを簡略化できる ● 『アジャイルデータモデリング』は、この「ディメンショナルモデリング」について書かれた本 ○ パート1：モデルストーミング ○ パート2：ディメンショナルデザインパターン ○ 事例集：データ基盤構築に関する12の国内事例 ● データモデリングに初めて触れる方は、まず事例集を読むのがオススメ ○ データ利活用に対する解像度を高めることができる 73

Slide 74

Slide 74 text

使いやすいデータの整備を通じて、組織にデータ分析を広めたい方は、ぜひ『アジャイルデータモデリング』をお買い求めください！ 74

Slide 75

Slide 75 text

「書籍を読むだけじゃ分からない」「手を動かして訓練したい」という人、ぜひMENTA講座を受けてください！ハンズオンチュートリアルを提供しています！ ※風音屋のnoteにて受講案内を行っております。ディメンショナルモデリングを実践したいデータエンジニア 75 株式会社風音屋のアドバイザーという立場からの宣伝です。本講座は、株式会社風音屋が提供するものです。ピクシブ株式会社とは一切関係がありません。

Slide 76

Slide 76 text

ピクシブ株式会社ではデータエンジニアを募集しています。 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

Slide 77

Slide 77 text

77 ご清聴ありがとうございました！