ディメンショナルモデリングを軽く語る

by Tech Leverages

Slide 1

Slide 1 text

ディメンショナルモデリングを軽く語る   テクノロジー戦略室   于　原駿  1 

Slide 2

Slide 2 text

お品書き  ● 自己紹介  ● 業務オペレーション（OLTP）と分析オペレーション（OLAP）  ● ファクトとディメンション  ● モデリングの方法  ● まとめ  ● 少し深堀り  ● まとめ2      2 

Slide 3

Slide 3 text

自己紹介  レバレジーズ株式会社システム本部テクノロジー戦略室所属のゲンシュンです    3 

Slide 4

Slide 4 text

datatech-jpというデータエンジニアのコミュニティで  #みん強というイベントの企画運営とMCしてます  自己紹介  ⬇先週のイベントです  4 

Slide 5

Slide 5 text

自己紹介  テキストのアウトプット少なめですが、qiitaやzennにも一応やってます  5 

Slide 6

Slide 6 text

自己紹介  6 

Slide 7

Slide 7 text

本日のゴール   7 

Slide 8

Slide 8 text

今日のゴール   ● ディメンショナルモデリングがどういうものかわかる！  ● データエンジニアリングって奥深い〜ってなる！  8 

Slide 9

Slide 9 text

データ分析とは   9 

Slide 10

Slide 10 text

何のためにデータ分析するの？   ● 様々なビジネスプロセスを評価する  ○ 様々な切り口を元に、定量的にデータを比較する  ○ 課題の抽出、現状の把握、未来の行動のために意思決定をする  ● ビジネスプロセスとは、我々が行う日々の業務である  ○ 皆さんも日々の業務、何かしらの数字を見て意思決定してますよね  ● データドリブンな意思決定をするために欠かせないですね    今日はデータ分析しやすいデータモデリングについて話していくよ！  10 

Slide 11

Slide 11 text

モデリングについて   11 

Slide 12

Slide 12 text

2種類のシステムが存在する   ● 業務オペレーション（OLTP）と分析オペレーション（OLAP）がある  ○ トランザクションのT  ○ アナリティクスのA  ● 両者は目的が違うので、モデリングの設計が異なる  12 

Slide 13

Slide 13 text

業務オペレーションシステムのデータモデリング   ● 1つのプロセスを適切に実行されることにフォーカス  ○ データの登録、更新、削除などのCRUD操作  ○ データ不整合を起こさないよう、きちんとトランザクションを処理  ● 正規化が設計手法の一つ  ○ リレーショナル・データベースで実装する時のDBスキーマ設計である  ○ データの整合性を保つために、第三正規化とかよくされる  13 

Slide 14

Slide 14 text

業務オペレーションシステムのデータモデリング   14  注文的なテーブルを第三正規化してみる 

Slide 15

Slide 15 text

分析オペレーションのデータモデリング   ● 蓄積された大量のデータを高速で処理する  ○ selectしてgroup byして集計する  ○ 特定のトランザクションではなく、大量のトランザクションを捌く効率性が求められる  ● ディメンショナルモデリングが設計手法の1つ  ○ 必要な情報を効率よくクエリできるよ  ○ 具体的には後で話すよ    15 

Slide 16

Slide 16 text

2種類のシステムが存在する   ● 業務オペレーション（OLTP）  ○ データの冗長性を排除し、整合性を保ちやすい  ○ CRUD操作の1トランザクションを、適切に処理  ● 分析オペレーション（OLAP）  ○ 大量のデータを効率よくクエリ処理する  ○ 大量のトランザクションを短期間で捌きたい  16 

Slide 17

Slide 17 text

ディメンショナルモデリングの説明   ● 先ほどの第三正規化同様、データやテーブルを例にして説明していきます  ● 今回は「新卒採用」文脈のプロセスを例にしてみます  ○ 実際にシステムを見たわけじゃないので、完全に架空です  ○ プロセスも適当に考えたものなので、架空です  17 

Slide 18

Slide 18 text

今回の登場人物   18  ※このデータはフィクションです。実在のシステムやデータとは関係ありません。    

Slide 19

Slide 19 text

データ登場人物   ● 新卒採用のプロセスを雑に  ○ 学生が、イベントや広告から流入する  ○ 志望業界や就活終了希望時期などを答える  ○ 企業と面談する  ○ 内定、承諾    19 

Slide 20

Slide 20 text

登場人物  ● 学生ごとの「志望業界」と「就活終了時期」のテーブル  ● 志望業界や就活終了時期は、日々更新されうる  20 

Slide 21

Slide 21 text

登場人物  ● 学生の学歴情報。MARCHとか関関同立などの学歴カテゴリぽいやつとか  21 

Slide 22

Slide 22 text

登場人物  ● 学生との接触イベント。何かしらで流入し、面談し、選考を進める  22 

Slide 23

Slide 23 text

登場人物  ● わかんないけど、雰囲気こんな感じっすかね？  ● 大事なのは  ○ 学生の切り口には志望業界などがあり、変化しうる  ○ 企業との接触は選考フロー上何回か発生する  23  学生  面談/面接  企業  志望業界 

Slide 24

Slide 24 text

ファクトとディメンション   24 

Slide 25

Slide 25 text

ディメンショナルモデリングに登場する概念   ● ファクトとは計測値である  ○ 売上高、面談数、内定数など。加算可能なもの  ○ レコード数が多い傾向にある  ● ディメンションとは切り口である  ○ 期間（月、週、日、会計期間など）  ○ 学歴区分、志望業界、流入経路  ○ 5W1H（いつ、誰、どこで、どのように）が切り口になりやすい  ○ レコード多くないが、切り口そのものの値の変化に追従すると大変  25 

Slide 26

Slide 26 text

● GROUP BYがdimension、集計値がfactの雑イメージ  ディメンショナルモデリングに登場する概念   26  SELECT  月,  経路,  　　SUM(内定)   FROM xxxx  GROUP BY 月, 流入経路  dimension  fact 

Slide 27

Slide 27 text

● factテーブルとdimテーブルを結合キーでjoinさせてgroup byする（雑）  ディメンショナルモデリングに登場する概念   27  SELECT  月,  経路,  　　SUM(内定)   FROM ファクトテーブル   LEFT JOIN ディメンション   USING 結合キー  GROUP BY 月, 流入経路 

Slide 28

Slide 28 text

● リレーショナルデータベースで実装したのが「スタースキーマ」設計  ● 真ん中にfact、周囲にdimensionを配置し、分析の切り口でjoinさせる  ディメンショナルモデリングに登場する概念   28  fact  内定  dimension  学生  dimension  大学  dimension  企業  dimension  業界  dimension  日付 

Slide 29

Slide 29 text

それぞれで分析してみる   29 

Slide 30

Slide 30 text

OLTPでのデータ分析   ● 第三正規化されたテーブルを、分析の切り口になるカラムをjoinさせるために、非正規化したり、たくさんのテーブルjoinが必要    30 

Slide 31

Slide 31 text

OLTPでのデータ分析   31  ● 分析で使うワイドテーブルを作るためのSQLが複雑になる  ● 分析要求の変化に対して柔軟性が低そう  ● データソースのテーブル構造変化に弱い   

Slide 32

Slide 32 text

OLAPでのデータ分析   ● factテーブルとdimensionテーブルをまず作り、分析軸でjoinさせる  ● joinするのは結合キーで。あとで詳しく話す  32  内定fact    月  学歴区分         学生dimension  大学dimension  学歴区分   企業dimension  業界dimension  日付dimension  月 

Slide 33

Slide 33 text

OLAPでのデータ分析   33  ● 利用者はfactとdimensionを選んで集計するだけで済む  ● joinが少ないのでSQLがシンプル  ○ 結果的にOLTPでのワイドテーブルと同じになっても、作るまでの工程がシンプルに表現しやすい   ● 分析の切り口追加など、分析要求に対して柔軟に対応できる  ● dimensionを非正規化することで、join減らせる  ● 様々なテーブルjoinが不要なので、大規模処理に向いてる   

Slide 34

Slide 34 text

良さはわかった。じゃ、どうやる？   34 

Slide 35

Slide 35 text

データモデリングの方法   35 

Slide 36

Slide 36 text

データモデリングの方法   ● kimball（ディメンショナルモデリング考案者）のページに色々ある  ● 様々なテクニックが記載されているのでオススメ！  36 

Slide 37

Slide 37 text

データモデリングの方法   1. ビジネスプロセスの選定  2. 粒度を決める  3. ディメンションを特定  4. ファクトを特定  37 

Slide 38

Slide 38 text

データモデリングの方法   1. ビジネスプロセスの選定   ○ 結局、一番大事である。  ○ ビジネスプロセスの可視化のためには、そもそもビジネスの理解が必要  2. 粒度を決める  3. ディメンションを特定  4. ファクトを特定  38 

Slide 39

Slide 39 text

データモデリングの方法   1. ビジネスプロセスの選定  2. 粒度を決める   ○ 複数のファクトを横断する際に、粒度が揃ってないと分析しづらい  ○ データの最小単位で設計するのがやりやすい  ○ dailyをmonthlyに丸めるのはできるが、monthlyをdailyには出来ない  3. ディメンションを特定  4. ファクトを特定  39 

Slide 40

Slide 40 text

データモデリングの方法   1. ビジネスプロセスの選定  2. 粒度を決める  3. ディメンションを特定   ○ 分析の切り口、データの属性情報、5W1Hが選ばれやすい  ○ dimensionは奥が深い（後述します）  4. ファクトを特定  40 

Slide 41

Slide 41 text

データモデリングの方法   1. ビジネスプロセスの選定  2. 粒度を決める  3. ディメンションを特定  4. ファクトを特定   ○ ビジネスプロセスで測定している対象  41 

Slide 42

Slide 42 text

ここまで整理してみる   42 

Slide 43

Slide 43 text

ここまでの整理   ● 様々な意思決定のためデータ分析が必要で、分析しやすいモデリングが重要  ● OLTPとOLAPでデータモデリングが違う  ○ OLTPはデータ整合性担保のため、第三正規化がよく使われる  ○ OLAPは大量のデータを分析しやすいよう、dimensional modelingが良い  ● ディメンショナルモデリングは、ビジネスプロセスの可視化に向いてる  ○ ファクト(計測値) とディメンション(切り口)   ○ これらをリレーショナルデータベースで実装したのがスタースキーマ  ● ディメンショナルモデリングのステップとして、ビジネスプロセスの理解大事  43 

Slide 44

Slide 44 text

少し深堀り   44 

Slide 45

Slide 45 text

ディメンショナルモデリングの深堀り集   ● スタースキーマの結合キー  ● Factless Fact Table  ● Slow Change Dimension  ● Degenerate Dimension  ● Junk Dimension  ● Conformed Dimension  ● Outrigger Dimension  ●   45 

Slide 46

Slide 46 text

ディメンショナルモデリングの深堀り集   ● スタースキーマの結合キー   ● Factless Fact Table  ● Slow Change Dimension  ● Degenerate Dimension  ● Junk Dimension  ● Conformed Dimension  ● Outrigger Dimension  ●   46 

Slide 47

Slide 47 text

スタースキーマの結合キー   ● ナチュラルキーだけでなくサロゲートキーを適切に作るのが重要  ● サロゲートキーとは  ○ システムにより生成された、意味を持たないキー  ○ ディメンションのレコードを一意に識別するために使われる  ● ナチュラルキーとは  ○ 意味を持つキー。ユーザIDとか、商品マスタIDとか  47 

Slide 48

Slide 48 text

スタースキーマの結合キー   ● なぜサロゲートキーが必要？  ● この図を見るに、それぞれのナチュラルキーでjoinしてることね？  48  内定fact    志望業界   学歴区分         学生dimension  大学dimension  学歴区分ID   企業dimension  業界dimension  志望業界ID   日付dimension 

Slide 49

Slide 49 text

スタースキーマの結合キー   ● dimensionの変更履歴に追従しやすいのが理由。以下雑な例  ● ユーザID=10が引っ越して「東京都」から「愛知」にかわった場合  ○ 大体のシステムって更新すると「上書き」されますよね  ● user_idでレコードを識別しながら、dimとfactをjoinすると  ○ user_id=10に「愛知」が上書きされたので、過去の履歴を追えない  ○ サロゲートキーを使えば、過去の履歴追える  ○ でもサロゲートキー使わなくても、ナチュラルキーとバージョン日付等で履歴を追うことはできる  49 

Slide 50

Slide 50 text

スタースキーマの結合キー   ● 個人的には、サロゲートキーはmustではないと思う   ● サロゲートキーはコミュニケーションコストが高い  ○ user_id=10でキャッチボールしたほうがわかりやすいよね  ○ xxx_key=aaa-bbb-cccって誰やねん  ● ナチュラルキーと履歴判定するカラムで対応はできるっちゃできる。トレードオフ。  ● ビジネスルールの変更によってナチュラルキーの扱いが変わると、ナチュラルキー運用は崩れちゃう！  50 

Slide 51

Slide 51 text

ディメンショナルモデリングの深堀り集   ● スタースキーマの結合キー  ● Factless Fact Table   ● Slow Change Dimension  ● Degenerate Dimension  ● Junk Dimension  ● Conformed Dimension  ● Outrigger Dimension  ●   51 

Slide 52

Slide 52 text

Factless Fact Table   ● 測定値を持たないファクトテーブル  ○ ？？どういうこと？？？  ● 「あるキャンペーンで、売れなかった商品」を扱いたいケースで発揮する  ○ ビジネスプロセスは「このプロモーションで商品が売れた」である  ○ 「売れなかった」というファクトは無い  52 

Slide 53

Slide 53 text

Factless Fact Table   ● 全商品を1レコードずつfactless factテーブルにいれる。もちろん測定値は無い  ● 以下2段階プロセスで「売れなかった商品」を特定  ○ 特定のキャンペーンに関係する商品を、factless factテーブル全商品から特定する  ○ 通常のfactから、売れた商品を特定する  ○ 上記の差集合で導き出す  ● 個人的に使ったことないので、ぶっちゃけわからん笑  ○ ディメンションの粒度が同じファクトを2つ用意すれば、差集合ができる。つまりディメンションの粒度設計って大事なんかな〜？  53 

Slide 54

Slide 54 text

ディメンショナルモデリングの深堀り集   ● スタースキーマの結合キー  ● Factless Fact Table  ● Slow Change Dimension   ● Degenerate Dimension  ● Junk Dimension  ● Conformed Dimension  ● Outrigger Dimension  ●   54 

Slide 55

Slide 55 text

Slow Change Dimension   ● 時間経過と共に変化するdimensionの値をどう扱うか？  ● SCDはtype1から7までのパターンが存在する。  ○ まー、個人的にはtype1とtype2がわかれば十分かな  55 

Slide 56

Slide 56 text

Slow Change Dimension   ● SCD type1：上書き  ○ 変更履歴追えない  56 

Slide 57

Slide 57 text

Slow Change Dimension   ● SCD type2：期間やバージョン管理をすることで新規レコード追加  ○ この期間はこのレコードだよ〜が表現される  57 

Slide 58

Slide 58 text

Slow Change Dimension   ● SCD type3：変更前と変更後を用意  ○ 途中の履歴は全部追えないが、最初の値と現在の値のみ把握できる  58 

Slide 59

Slide 59 text

ディメンショナルモデリングの深堀り集   ● スタースキーマの結合キー  ● Factless Fact Table  ● Slow Change Dimension  ● Degenerate Dimension   ● Junk Dimension  ● Conformed Dimension  ● Outrigger Dimension  ●   59 

Slide 60

Slide 60 text

Degenerate Dimension   ● 日本語名がわかってない。縮退ディメンション？  ● ファクトテーブルに直接存在するディメンションのようなもの  ● 以下通常のディメンションとの差分  ○ 他テーブルへの外部キー  ○ ファクトテーブルの粒度を決めない  60 

Slide 61

Slide 61 text

Degenerate Dimension   ● 注文ファクトにおける「注文ユーザID」「注文番号」を考えてみる  ○ 注文ユーザIDは、ディメンションあるから外部キーになりえる  ○ 注文番号は、ディメンションテーブルは無いので、外部キーにはならない  ○ 注文番号は、分析の切り口にならないので粒度決めない  ○ 注文番号って、注文トランザクションの詳細情報でしかない  ● 個人的にはあんまり意識出来ていない。  ○ 分析軸にならない、factテーブルにあるdimensionぐらいのノリじゃね  ○ 詳細分析時のドリルダウン時に使うかも？  61 

Slide 62

Slide 62 text

ディメンショナルモデリングの深堀り集   ● スタースキーマの結合キー  ● Factless Fact Table  ● Slow Change Dimension  ● Degenerate Dimension  ● Junk Dimension   ● Conformed Dimension  ● Outrigger Dimension  ●   62 

Slide 63

Slide 63 text

Junk Dimension   ● 無関係な属性を1つのディメンショングループに集約したもの  ○ お互いに関係がない、flag系のディメンションについては、たくさんjoinさせるより、まとめたほうがシンプル  ○ 属性組み合わせパターンのレコード数で済む。  ○ ナチュラルキー持たない。  63 

Slide 64

Slide 64 text

Junk Dimension   ● 個々のdimensionはカラム少ないけど、joinめっちゃする  64  注文ファクト    注文id   手段id   発送id   注文形式 dimension  購入手段  dimension  発送状況 dimension 

Slide 65

Slide 65 text

Junk Dimension   ● joinが少なく済むよ〜  65  注文ファクト    key  junk dimensnion    key 

Slide 66

Slide 66 text

ディメンショナルモデリングの深堀り集   ● スタースキーマの結合キー  ● Factless Fact Table  ● Slow Change Dimension  ● Degenerate Dimension  ● Junk Dimension  ● Conformed Dimension   ● Outrigger Dimension  ●   66 

Slide 67

Slide 67 text

Conformed Dimension   ● これも日本語わかってない。適合されたディメンション？  ● 複数のファクトに共有されるディメンション。超重要！  ○ 複数のファクトを比較できるので、一貫性のある分析ができる  ○ 同じようなディメンションを複数用意しなくて良い  ● 雑な例  ○ 日付ディメンション。会計期間、営業日などをまとめたディメンション  ● 結局ディメンションの粒度設計が重要。結局ディメンションの幅が分析の柔軟性を広げる  67 

Slide 68

Slide 68 text

ディメンショナルモデリングの深堀り集   ● スタースキーマの結合キー  ● Factless Fact Table  ● Slow Change Dimension  ● Degenerate Dimension  ● Junk Dimension  ● Conformed Dimension  ● Outrigger Dimension   ●   68 

Slide 69

Slide 69 text

Outrigger Dimensnion   ● ディメンションから別のディメンションを参照するやーつ  ○ ディメンションテーブルの正規化かな？  ○ クソでかいdimensionを別のdimensionに切り出すようなイメージ  69  ファクト    xx_key   国dimension    xx_key   yy_key   地域詳細dimension    yy_key        

Slide 70

Slide 70 text

深堀りまとめ   70 

Slide 71

Slide 71 text

深堀りまとめ   ● めちゃめちゃたくさんの引き出しがある。わからんものも多い。  ● ディメンションが肥大化していくのでそれをケアする設計手法が豊富  ● キレイに設計できないかな〜と悩む際のヒントになれば  71 

Slide 72

Slide 72 text

深堀りまとめ   ● ガッツリ解説されてる本が、洋書しかないよ〜。でも奥が深い！！   72 

Slide 73

Slide 73 text

本日のゴール   73 

Slide 74

Slide 74 text

今日のゴール   ● ディメンショナルモデリングがどういうものかわかる！  ● データエンジニアリングって奥深い〜ってなる！  74 

Slide 75

Slide 75 text

さいごに  75 

Slide 76

Slide 76 text

Slide 77

Slide 77 text

さいごに  ● 自分から課題を解きに行くために、もっと皆さんのこと知りたい   ● 自分は特定の事業に属していないので、事業や人、組織をまず知りたい  ● データ周りでお困りごととか、どういうことがやりたいか知りたい〜  ● ランチ行きましょ！  ● ゲームやボードゲームの話たくさんしたいぜ！！！    77 

Slide 78

Slide 78 text

● レバレジーズは3000人超えても、部署横断してたくさんお声がけもらえて本当に凄い〜！    ワイワイまってます   78 

Slide 79

Slide 79 text

ありがとうございました   79 

Slide 80

Slide 80 text

自己紹介  datatech-jpというデータエンジニアのコミュニティで  #みん強というイベントの企画運営とMCしてます  ⬇先週のイベントです  80 