Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
ディメンショナルモデリングを軽く語る
Search
Tech Leverages
March 04, 2025
0
58
ディメンショナルモデリングを軽く語る
Tech Leverages
March 04, 2025
Tweet
Share
More Decks by Tech Leverages
See All by Tech Leverages
アクターモデルによる効率的な分散システム設計
leveragestech
0
53
Terraform による運用効率化の取り組みと最新のテストアプローチの紹介
leveragestech
0
55
OpenFGAで拓く次世代認可基盤 〜予告編〜
leveragestech
0
53
リソース・管理効率の向上だけでない、分散システムとしてのTiDBの魅力
leveragestech
0
54
作ってわかる!非同期ランタイム
leveragestech
0
54
レバテック開発部 技術広報 これまでとこれから
leveragestech
0
87
改めて「型」について考えてみよう
leveragestech
1
58
苦しいTiDBへの移行を乗り越えて快適な運用を目指す
leveragestech
0
1.1k
Biome で Format と Lint のストレスをゼロに
leveragestech
0
59
Featured
See All Featured
jQuery: Nuts, Bolts and Bling
dougneiner
63
7.7k
The Myth of the Modular Monolith - Day 2 Keynote - Rails World 2024
eileencodes
21
2.5k
A Modern Web Designer's Workflow
chriscoyier
693
190k
Automating Front-end Workflow
addyosmani
1368
200k
Improving Core Web Vitals using Speculation Rules API
sergeychernyshev
10
520
実際に使うSQLの書き方 徹底解説 / pgcon21j-tutorial
soudai
175
52k
JavaScript: Past, Present, and Future - NDC Porto 2020
reverentgeek
47
5.2k
Designing Dashboards & Data Visualisations in Web Apps
destraynor
231
53k
CoffeeScript is Beautiful & I Never Want to Write Plain JavaScript Again
sstephenson
160
15k
A designer walks into a library…
pauljervisheath
205
24k
Responsive Adventures: Dirty Tricks From The Dark Corners of Front-End
smashingmag
251
21k
Art, The Web, and Tiny UX
lynnandtonic
298
20k
Transcript
ディメンショナルモデリングを軽く語る テクノロジー戦略室 于 原駿 1
お品書き • 自己紹介 • 業務オペレーション(OLTP)と分析オペレーション(OLAP) • ファクトとディメンション • モデリングの方法 •
まとめ • 少し深堀り • まとめ2 2
自己紹介 レバレジーズ株式会社システム本部テクノロジー戦略室所属のゲンシュンです 3
datatech-jpというデータエンジニアのコミュニティで #みん強 というイベントの企画運営とMCしてます 自己紹介 ⬇先週のイベントです 4
自己紹介 テキストのアウトプット少なめですが、qiitaやzennにも一応やってます 5
自己紹介 6
本日のゴール 7
今日のゴール • ディメンショナルモデリングがどういうものかわかる! • データエンジニアリングって奥深い〜ってなる! 8
データ分析とは 9
何のためにデータ分析するの? • 様々なビジネスプロセスを評価する ◦ 様々な切り口を元に、定量的にデータを比較する ◦ 課題の抽出、現状の把握、未来の行動のために意思決定をする • ビジネスプロセスとは、我々が行う日々の業務である
◦ 皆さんも日々の業務、何かしらの数字を見て意思決定してますよね • データドリブンな意思決定をするために欠かせないですね 今日はデータ分析しやすいデータモデリング について話していくよ! 10
モデリングについて 11
2種類のシステムが存在する • 業務オペレーション(OLTP)と分析オペレーション(OLAP)がある ◦ トランザクションのT ◦ アナリティクスのA • 両者は目的が違うので、モデリングの設計が異なる
12
業務オペレーションシステムのデータモデリング • 1つのプロセスを適切に実行されることにフォーカス ◦ データの登録、更新、削除などのCRUD操作 ◦ データ不整合を起こさないよう、きちんとトランザクションを処理 • 正規化が設計手法の一つ
◦ リレーショナル・データベースで実装する時のDBスキーマ設計である ◦ データの整合性を保つために、第三正規化とかよくされる 13
業務オペレーションシステムのデータモデリング 14 注文的なテーブルを第三正規化してみる
分析オペレーションのデータモデリング • 蓄積された大量のデータを高速で処理する ◦ selectしてgroup byして集計する ◦ 特定のトランザクションではなく、大量のトランザクションを捌く効率性が求められ る
• ディメンショナルモデリングが設計手法の1つ ◦ 必要な情報を効率よくクエリできるよ ◦ 具体的には後で話すよ 15
2種類のシステムが存在する • 業務オペレーション(OLTP) ◦ データの冗長性を排除し、整合性を保ちやすい ◦ CRUD操作の1トランザクションを、適切に処理 • 分析オペレーション(OLAP)
◦ 大量のデータを効率よくクエリ処理する ◦ 大量のトランザクションを短期間で捌きたい 16
ディメンショナルモデリングの説明 • 先ほどの第三正規化同様、データやテーブルを例にして説明していきます • 今回は「新卒採用」文脈のプロセスを例にしてみます ◦ 実際にシステムを見たわけじゃないので、完全に架空です ◦ プロセスも適当に考えたものなので、架空です
17
今回の登場人物 18 ※このデータはフィクションです。実在のシステムやデータとは関係ありません。
データ登場人物 • 新卒採用のプロセスを雑に ◦ 学生が、イベントや広告から流入する ◦ 志望業界や就活終了希望時期などを答える ◦ 企業と面談する
◦ 内定、承諾 19
登場人物 • 学生ごとの「志望業界」と「就活終了時期」のテーブル • 志望業界や就活終了時期は、日々更新されうる 20
登場人物 • 学生の学歴情報。MARCHとか関関同立などの学歴カテゴリぽいやつとか 21
登場人物 • 学生との接触イベント。何かしらで流入し、面談し、選考を進める 22
登場人物 • わかんないけど、雰囲気こんな感じっすかね? • 大事なのは ◦ 学生の切り口には志望業界などがあり、変化しうる ◦ 企業との接触は選考フロー上何回か発生する 23
学生 面談/面接 企業 志望業界
ファクトとディメンション 24
ディメンショナルモデリングに登場する概念 • ファクトとは計測値である ◦ 売上高、面談数、内定数など。加算可能なもの ◦ レコード数が多い傾向にある • ディメンションとは切り口である
◦ 期間(月、週、日、会計期間など) ◦ 学歴区分、志望業界、流入経路 ◦ 5W1H(いつ、誰、どこで、どのように)が切り口になりやすい ◦ レコード多くないが、切り口そのものの値の変化に追従すると大変 25
• GROUP BYがdimension、集計値がfactの雑イメージ ディメンショナルモデリングに登場する概念 26 SELECT 月, 経路, SUM(内定)
FROM xxxx GROUP BY 月, 流入経路 dimension fact
• factテーブルとdimテーブルを結合キーでjoinさせてgroup byする(雑) ディメンショナルモデリングに登場する概念 27 SELECT 月, 経路, SUM(内定)
FROM ファクトテーブル LEFT JOIN ディメンション USING 結合キー GROUP BY 月, 流入経路
• リレーショナルデータベースで実装したのが「スタースキーマ」設計 • 真ん中にfact、周囲にdimensionを配置し、分析の切り口でjoinさせる ディメンショナルモデリングに登場する概念 28 fact 内定 dimension
学生 dimension 大学 dimension 企業 dimension 業界 dimension 日付
それぞれで分析してみる 29
OLTPでのデータ分析 • 第三正規化されたテーブルを、分析の切り口になるカラムをjoinさせるために、非正規 化したり、たくさんのテーブルjoinが必要 30
OLTPでのデータ分析 31 • 分析で使うワイドテーブルを作るためのSQLが複雑になる • 分析要求の変化に対して柔軟性が低そう • データソースのテーブル構造変化に弱い
OLAPでのデータ分析 • factテーブルとdimensionテーブルをまず作り、分析軸でjoinさせる • joinするのは結合キーで。あとで詳しく話す 32 内定fact 月
学歴区分 学生dimension 大学dimension 学歴区分 企業dimension 業界dimension 日付dimension 月
OLAPでのデータ分析 33 • 利用者はfactとdimensionを選んで集計するだけで済む • joinが少ないのでSQLがシンプル ◦ 結果的にOLTPでのワイドテーブルと同じになっても、作るまでの工程がシンプ ルに表現しやすい
• 分析の切り口追加など、分析要求に対して柔軟に対応できる • dimensionを非正規化することで、join減らせる • 様々なテーブルjoinが不要なので、大規模処理に向いてる
良さはわかった。じゃ、どうやる? 34
データモデリングの方法 35
データモデリングの方法 • kimball(ディメンショナルモデリング考案者)のページに色々ある • 様々なテクニックが記載されているのでオススメ! 36
データモデリングの方法 1. ビジネスプロセスの選定 2. 粒度を決める 3. ディメンションを特定 4. ファクトを特定
37
データモデリングの方法 1. ビジネスプロセスの選定 ◦ 結局、一番大事である。 ◦ ビジネスプロセスの可視化のためには、そもそもビジネスの理解が必要 2.
粒度を決める 3. ディメンションを特定 4. ファクトを特定 38
データモデリングの方法 1. ビジネスプロセスの選定 2. 粒度を決める ◦ 複数のファクトを横断する際に、粒度が揃ってないと分析しづらい ◦
データの最小単位で設計するのがやりやすい ◦ dailyをmonthlyに丸めるのはできるが、monthlyをdailyには出来ない 3. ディメンションを特定 4. ファクトを特定 39
データモデリングの方法 1. ビジネスプロセスの選定 2. 粒度を決める 3. ディメンションを特定 ◦
分析の切り口、データの属性情報、5W1Hが選ばれやすい ◦ dimensionは奥が深い(後述します) 4. ファクトを特定 40
データモデリングの方法 1. ビジネスプロセスの選定 2. 粒度を決める 3. ディメンションを特定 4. ファクトを特定
◦ ビジネスプロセスで測定している対象 41
ここまで整理してみる 42
ここまでの整理 • 様々な意思決定のためデータ分析が必要で、分析しやすいモデリングが重要 • OLTPとOLAPでデータモデリングが違う ◦ OLTPはデータ整合性担保のため、第三正規化がよく使われる ◦ OLAPは大量のデータを分析しやすいよう、dimensional
modelingが良い • ディメンショナルモデリングは、ビジネスプロセスの可視化に向いてる ◦ ファクト(計測値) とディメンション(切り口) ◦ これらをリレーショナルデータベースで実装したのがスタースキーマ • ディメンショナルモデリングのステップとして、ビジネスプロセスの理解大事 43
少し深堀り 44
ディメンショナルモデリングの深堀り集 • スタースキーマの結合キー • Factless Fact Table • Slow
Change Dimension • Degenerate Dimension • Junk Dimension • Conformed Dimension • Outrigger Dimension • 45
ディメンショナルモデリングの深堀り集 • スタースキーマの結合キー • Factless Fact Table •
Slow Change Dimension • Degenerate Dimension • Junk Dimension • Conformed Dimension • Outrigger Dimension • 46
スタースキーマの結合キー • ナチュラルキーだけでなくサロゲートキー を適切に作るのが重要 • サロゲートキーとは ◦ システムにより生成された、意味を持たないキー ◦
ディメンションのレコードを一意に識別するために使われる • ナチュラルキーとは ◦ 意味を持つキー。ユーザIDとか、商品マスタIDとか 47
スタースキーマの結合キー • なぜサロゲートキーが必要? • この図を見るに、それぞれのナチュラルキーでjoinしてることね? 48 内定fact 志望業界
学歴区分 学生dimension 大学dimension 学歴区分ID 企業dimension 業界dimension 志望業界ID 日付dimension
スタースキーマの結合キー • dimensionの 変更履歴に追従しやすい のが理由。以下雑な例 • ユーザID=10が引っ越して「東京都」から「愛知」にかわった場合 ◦ 大体のシステムって更新すると「上書き」されますよね
• user_idでレコードを識別しながら、dimとfactをjoinすると ◦ user_id=10に「愛知」が上書きされたので、過去の履歴を追えない ◦ サロゲートキーを使えば、過去の履歴追える ◦ でもサロゲートキー使わなくても、ナチュラルキーとバージョン日付等で履歴を追 うことはできる 49
スタースキーマの結合キー • 個人的には、サロゲートキーはmustではないと思う • サロゲートキーはコミュニケーションコストが高い ◦ user_id=10でキャッチボールしたほうがわかりやすいよね ◦
xxx_key=aaa-bbb-cccって誰やねん • ナチュラルキーと履歴判定するカラムで対応はできるっちゃできる。トレードオフ。 • ビジネスルールの変更によってナチュラルキーの扱いが変わると、ナチュラルキー運 用は崩れちゃう! 50
ディメンショナルモデリングの深堀り集 • スタースキーマの結合キー • Factless Fact Table •
Slow Change Dimension • Degenerate Dimension • Junk Dimension • Conformed Dimension • Outrigger Dimension • 51
Factless Fact Table • 測定値を持たないファクト テーブル ◦ ??どういうこと??? •
「あるキャンペーンで、売れなかった商品」を扱いたいケースで発揮する ◦ ビジネスプロセスは「このプロモーションで商品が売れた」である ◦ 「売れなかった」というファクトは無い 52
Factless Fact Table • 全商品を1レコードずつfactless factテーブルにいれる。もちろん測定値は無い • 以下2段階プロセスで「売れなかった商品」を特定 ◦
特定のキャンペーンに関係する商品を、factless factテーブル全商品から特定す る ◦ 通常のfactから、売れた商品を特定する ◦ 上記の差集合で導き出す • 個人的に使ったことないので、ぶっちゃけわからん笑 ◦ ディメンションの粒度が同じファクトを2つ用意すれば、差集合ができる。つまりディ メンションの粒度設計って大事なんかな〜? 53
ディメンショナルモデリングの深堀り集 • スタースキーマの結合キー • Factless Fact Table • Slow
Change Dimension • Degenerate Dimension • Junk Dimension • Conformed Dimension • Outrigger Dimension • 54
Slow Change Dimension • 時間経過と共に変化するdimensionの値 をどう扱うか? • SCDはtype1から7までのパターンが存在する。 ◦
まー、個人的にはtype1とtype2がわかれば十分かな 55
Slow Change Dimension • SCD type1:上書き ◦ 変更履歴追えない 56
Slow Change Dimension • SCD type2:期間やバージョン管理をすることで新規レコード追加 ◦ この期間はこのレコードだよ〜が表現される 57
Slow Change Dimension • SCD type3:変更前と変更後を用意 ◦ 途中の履歴は全部追えないが、最初の値と現在の値のみ把握できる 58
ディメンショナルモデリングの深堀り集 • スタースキーマの結合キー • Factless Fact Table • Slow
Change Dimension • Degenerate Dimension • Junk Dimension • Conformed Dimension • Outrigger Dimension • 59
Degenerate Dimension • 日本語名がわかってない。縮退ディメンション? • ファクトテーブルに直接存在するディメンションのようなもの • 以下通常のディメンションとの差分 ◦
他テーブルへの外部キー ◦ ファクトテーブルの粒度を決めない 60
Degenerate Dimension • 注文ファクトにおける「注文ユーザID」「注文番号」を考えてみる ◦ 注文ユーザIDは、ディメンションあるから外部キーになりえる ◦ 注文番号は、ディメンションテーブルは無いので、外部キーにはならない ◦
注文番号は、分析の切り口にならないので粒度決めない ◦ 注文番号って、注文トランザクションの詳細情報でしかない • 個人的にはあんまり意識出来ていない。 ◦ 分析軸にならない、factテーブルにあるdimensionぐらいのノリじゃね ◦ 詳細分析時のドリルダウン時に使うかも? 61
ディメンショナルモデリングの深堀り集 • スタースキーマの結合キー • Factless Fact Table • Slow
Change Dimension • Degenerate Dimension • Junk Dimension • Conformed Dimension • Outrigger Dimension • 62
Junk Dimension • 無関係な属性を1つのディメンショングループに集約したもの ◦ お互いに関係がない、flag系のディメンションについては、たくさんjoinさせるより、 まとめたほうがシンプル ◦ 属性組み合わせパターンのレコード数で済む。
◦ ナチュラルキー持たない。 63
Junk Dimension • 個々のdimensionはカラム少ないけど、joinめっちゃする 64 注文ファクト 注文id
手段id 発送id 注文形式 dimension 購入手段 dimension 発送状況 dimension
Junk Dimension • joinが少なく済むよ〜 65 注文ファクト key junk
dimensnion key
ディメンショナルモデリングの深堀り集 • スタースキーマの結合キー • Factless Fact Table • Slow
Change Dimension • Degenerate Dimension • Junk Dimension • Conformed Dimension • Outrigger Dimension • 66
Conformed Dimension • これも日本語わかってない。適合されたディメンション? • 複数のファクトに共有されるディメンション 。超重要! ◦ 複数のファクトを比較できるので、一貫性のある分析ができる
◦ 同じようなディメンションを複数用意しなくて良い • 雑な例 ◦ 日付ディメンション。会計期間、営業日などをまとめたディメンション • 結局ディメンションの粒度設計が重要 。結局ディメンションの幅が分析の柔軟性を広 げる 67
ディメンショナルモデリングの深堀り集 • スタースキーマの結合キー • Factless Fact Table • Slow
Change Dimension • Degenerate Dimension • Junk Dimension • Conformed Dimension • Outrigger Dimension • 68
Outrigger Dimensnion • ディメンションから別のディメンションを参照するやーつ ◦ ディメンションテーブルの正規化かな? ◦ クソでかいdimensionを別のdimensionに切り出すようなイメージ 69
ファクト xx_key 国dimension xx_key yy_key 地域詳細dimension yy_key
深堀りまとめ 70
深堀りまとめ • めちゃめちゃたくさんの引き出しがある。わからんものも多い。 • ディメンションが肥大化していくのでそれをケアする設計手法が豊富 • キレイに設計できないかな〜と悩む際のヒントになれば 71
深堀りまとめ • ガッツリ解説されてる本が、洋書しかないよ〜。でも奥が深い!! 72
本日のゴール 73
今日のゴール • ディメンショナルモデリングがどういうものかわかる! • データエンジニアリングって奥深い〜ってなる! 74
さいごに 75
76
さいごに • 自分から課題を解きに行くために、もっと皆さんのこと知りたい • 自分は特定の事業に属していないので、事業や人、組織をまず知りたい • データ周りでお困りごととか、どういうことがやりたいか知りたい〜 • ランチ行きましょ!
• ゲームやボードゲームの話たくさんしたいぜ!!! 77
• レバレジーズは3000人超えても、部署横断してたくさんお声がけもらえて本当に凄 い〜! ワイワイまってます 78
ありがとうございました 79
自己紹介 datatech-jpというデータエンジニアのコミュニティで #みん強 というイベントの企画運営とMCしてます ⬇先週のイベントです 80