ディメンショナルモデルの実導入と実装について

30.3K Views

April 03, 23

スライド概要

2023年3月22日(水) に「TECH PLAY Data Conference 2023」で ソリューション事業本部データ統括部データ基盤部ゲームデータグループ 渡辺 ブルーノ / 濱田 識 が登壇した資料です。

概要:
今までのDeNAのデータモデリングの課題とディメンショナルモデルを採用した経緯、その実装から得られた知見を紹介しております。

イベント:
https://techplay.jp/event/892259

profile-image

DeNA が社会の技術向上に貢献するため、業務で得た知見を積極的に外部に発信する、DeNA 公式のアカウントです。DeNA エンジニアの登壇資料をお届けします。

シェア

またはPlayer版

埋め込む »CMSなどでJSが使えない場合

関連スライド

各ページのテキスト
1.

ディメンショナルモデルの実導入 と実装について

2.

自己紹介

3.

自己紹介 渡辺 ブルーノ DeNA データ本部データエンジニアリング第4グループ 2021年入社 ゲームのデータ分析基盤の保守運用を中心 にしつつ今は、アナリティクスエンジニア としてデータモデリングからパイプライン の実装に取り組んでいる 最近の興味は、データモデリングとワーク フローエンジン © DeNA Co., Ltd. 3

4.

自己紹介 QA 対応をするので、 お気軽にご質問くださ い! 濱田 識 DeNA データ本部データエンジニアリング第4グループ 2022年入社 ゲーム事業のデータ分析基盤の構築・運用 チームにてデータアーキテクト業務に取り 組んでいる 色々なデータアーキテクチャやシステム構 成を考えるのが好き © DeNA Co., Ltd. 4

5.

今回話すこと

6.

話すこと 話すこと 話さないこと ● ディメンショナルモデル導入の経緯 ● データモデリングの入門的な話 ● スタースキーマ ● データモデリング別のまとめ、比較 ● スローチェンジディメンション (以降、SCD) ● モデリング・実装の流れ ● ディメンショナルモデルのメリデメ ● 得られた効果 ● 課題・展望

7.

弊社のパイプラインの課題

8.

弊社のパイプラインの課題 分析で使うデータ 分析業務 Raw Raw pipelines DWH DWH Datamart Datamart 課題 ● 運用に連れてパイプラインが増えの全容がつかみにくくなる ● アナリスト側で分析するのに必要なテーブルが増えていき大福帳形式の ようなテーブルが求めるようになり今の設計と噛み合わなくなってきた

9.

弊社のパイプラインの課題 解決案 ● 運用でデータマートの追加要望に合わせてパイプライン増やさくても対応可能 ● KPIの追加対応するためのテーブルを増やさず既存のテーブルにKPIをまとめる 大福帳は以下のような形式が取れるため分析軸やKPIをまとめることができる ユーザーの課金関係の大福帳 ユーザーID デバイス名 デバイスOS ユーザー登録日 直近のログインからの経過日数 合計課金額 購入アイテム数 user_1 iPhone12 iOS 2023/03/01 2 1000 5 user_2 Pixel 7 Android 2023/03/01 1 400 2 user_3 iPhone14 iOS 2023/03/04 0 0 0 ※大福帳をそのまま作るのはカラム間の粒度やら分析軸にブレがでるため安全な拡張を担保しづらい 大福帳を作るためのデータモデルとして大福帳と噛み合っていて分析軸の拡張性のあるものを 採用すればアナリストも使いやすく分かりやすいパイプラインを運用できるのでは?

10.

大福帳に噛み合うデータモデル

11.

大福帳に噛み合うデータモデル 考慮したこと ● 大福帳の分析軸の追加を設計したモデリングから自由に行いたい ● 大福帳を作るのに使うテーブルの粒度を合わせたい ● 今まで通り過去データの再集計は冪等性が担保されている状態を保ちたい ディメンショナルモデルを採用 特性 ● 最小粒度以上からの粒度の変更が容易である ● 分析軸の組み合わせの自由度が高い ● SCD Type2を使うことで更新の有った属性情報を過去の値のままに集計できる ● ディメンショナルモデルのテーブル間をJOINして集計すると大福帳が作れる

12.

ディメンショナルモデルとは

13.

ディメンショナルモデルとは 大量データに対する問い合わせと分析に最適化されるように構造化されたデータモデルのことでデータウェアハウスにデータを格納するのに 適した方法でもある ディメンショナルモデルはファクトテーブルとディメンションテーブルで構成される ファクトテーブル: 行毎に特定の集計値とディメンションテーブルと結合するためのキーを持つ ディメンションテーブル: 分析軸となる対象を格納し、ファクトテーブルと結合することで分析軸に沿った集計していく また、ディメンションテーブルにSCDという手法を使ってモデリングができる ディメンショナルモデルのイメージ: ディメン ション ディメン ション ファクト ディメン ション

14.

設計・モデリング/実装の流れ

15.

設計・モデリング/実装の流れ データエンジニアのみ アナリストと協働 1. 大福帳 4. 物理モデル& データリネージュ アナリストと協働して分析で必要 なものをまとめる この段階で粒度と必須の分析軸 が決まる 論理モデルを元にER図のPKと なるサロゲートキーの設計やカ ラム別にデータ型を決める 設計・モデリングのサ イクル 3. 論理モデル 概念モデルを元にファクト・ディ メンションテーブルのER図を作 る 2. 概念モデル 分析軸表記法を用いてファクト・ ディメンションテーブルの関係を 図示して 設計からモデリングのループを回して実装に向けてディメンショナルモデルをブラッシュアップしていく

16.

設計・モデリング/実装の流れ 設計・モデリング編(大福帳): データエンジニア ・最小の粒度は時間 単位がいいかも 課金の大福帳(たたき台 ————————— ・Date ・ユーザー情報 ・デバイス情報 ・課金アイテム情報 ・購入数 ・単価 ・あとユーザー情報 でRRとか算出できる 分析軸欲しいかも アナリスト 大福帳テーブル設計で以下が決まってくる 粒度: 時間 分析軸: ユーザー、デバイス、課金アイテム 集計値: 購入数、単価 課金の大福帳(決定版 ————————— ・Hour ・ユーザー情報 (最終アクセス日時を追加) ・デバイス情報 ・課金アイテム情報 ・購入数 ・単価

17.

設計・モデリング/実装の流れ 設計・モデリング編(概念モデル): アイテム 単価 アイテム タイプ 機種名 ユーザー 課金 デバイス OS 登録日 機種名 ユーザー 行動 デバイス OS 課金の集計 ————————— ・Hour ・ユーザー分析軸 ・デバイス分析軸 ・課金アイテム分析軸 ・購入数 ・単価 ユーザー 登録日 ユーザーの集計 ————————— ・Hour ・ユーザー分析軸 ・デバイス分析軸 ・ユーザー集計値 課金の大福帳 ————————— ・Hour ・ユーザー情報 (最終アクセス日時を追加) ・デバイス情報 ・課金アイテム情報 ・購入数 ・単価

18.

設計・モデリング/実装の流れ 設計・モデリング編(論理モデル): 論理モデルで以下が大まかに決まる ユーザー ユーザー ユーザー 行動 デバイス 結合キー ・ユーザー ・デバイス 課金 デバイス アイテム ● 結合キー ● 分析軸の詳細なカラム ● 集計したい値

19.

設計・モデリング/実装の流れ 設計・モデリング編(物理モデル): 物理モデルで以下が決まる ユーザー情報 : ——————— ユーザーキー ユーザー ID ユーザー名 ユーザー作成日時 ユーザー行動 : ——————— Hour ユーザーキー デバイスキー アクセス数 最終アクセス時間 デバイス情報 : ——————— デバイスキー 機種名 OS 課金: ——————— アイテムキー アイテム名 アイテムタイプ 単価 課金アイテム情報 : —————— Hour ユーザーキー デバイスキー アイテムキー アイテム購入数 ● サロゲートキーの生成方法 ● 各テーブルのデータ型 ● 集計したい値

20.

設計・モデリング/実装の流れ 設計・モデリング編(データリネージュ): データリネージュで以下が決まる ユーザー行動ログ ユーザー・デバイスDWH デバイス情報 : ——————— デバイスキー 機種名 OS ユーザー情報 : ——————— ユーザーキー ユーザー ID ユーザー名 ユーザー作成日時 ユーザー行動 : ——————— Hour ユーザーキー デバイスキー アクセス数 最終アクセス時間 ● データ生成に必要なデータソース ● ファクト・ディメンションを作るためのDWH ● 各テーブルの生成時の依存関係

21.

設計・モデリング/実装の流れ 設計・モデリング編(物理モデル&データリネージュ): ユーザー情報 : ——————— ユーザーキー ユーザー ID ユーザー名 ユーザー作成日時 ユーザー行動ログ ユーザー行動 : ——————— Hour ユーザーキー デバイスキー アクセス数 最終アクセス時間 デバイス情報 : ——————— デバイスキー 機種名 OS 課金: ——————— Hour ユーザーキー デバイスキー アイテムキー アイテム購入数 ユーザー・デバイスDWH 同時に作る 課金アイテム情報 : —————— Hour アイテムキー アイテム名 アイテムタイプ 単価 デバイス情報 : ——————— デバイスキー 機種名 OS ユーザー情報 : ——————— ユーザーキー ユーザー ID ユーザー名 ユーザー作成日時 ユーザー行動 : ——————— Hour ユーザーキー デバイスキー アクセス数 最終アクセス時間

22.

設計・モデリング/実装の流れ データエンジニアのみ アナリストと協働 1. 大福帳 4. 物理モデル& データリネージュ アナリストと協働して分析で必要 なものをまとめる この段階で粒度と必須の分析軸 が決まる 論理モデルを元にER図のPKと なるサロゲートキーの設計やカ ラム別にデータ型を決める 設計・モデリングのサ イクル 3. 論理モデル 概念モデルを元にファクト・ディ メンションテーブルのER図を作 る 2. 概念モデル 分析軸表記法を用いてファクト・ ディメンションテーブルの関係を 図示して 何度かループすることでファクト・ディメンションの結合や分析軸、粒度がブラッシュアップされていく

23.

設計・モデリング/実装の流れ 実装編: ファクト・ ディメンション 前段の中間集計 構築 ファクト・ ディメンション 構築 大福帳 構築 ワークフロー 構築

24.

設計・モデリング/実装の流れ 実装編: ファクト・ディメンションの構築 実装の詳細 ● ディメンションでサロゲートキー生成ロジックと分析軸の定義 ● ディメンションのデータ更新で過去の状態を残す必要のあるものにはSCD Type2を実装 ● ディメンションの最新のサロゲートキーと集計値を組み合わせてファクトテーブルを構成する

25.

設計・モデリング/実装の流れ 実装編: SCD Type2の実装について ディメンションテーブル(ユーザーを例にあげる)にfrom_dateとend_dateのカラムを追加してデータが生成されてからいつ まで使えるかを定義する ユーザーディメンションテーブル ユーザーキー ユーザーID ユーザー名 ユーザー登録日 from_date end_date xxx-xxx-xxx user_1 ユーザー1 2023/03/01 2023/03/01 9999/12/31 yyy-yyy-yyy user_2 ユーザー2 2023/03/01 2023/03/01 9999/12/31 zzz-zzz-zzz user_3 ユーザー3 2023/03/04 2023/03/04 9999/12/31 最新データのend_dateを9999/12/31の超未来とすることで最新データへのアクセスを簡単にする

26.

設計・モデリング/実装の流れ 実装編: SCD Type2の実装について user_1のデータに更新が有った場合、新しくユーザーキーを発行した最新のデータを追加しつつ過去のuser_1の end_dateに最新のuser_1のfrom_dateを入れることで最新のデータでないことを表現する ユーザーディメンションテーブル ユーザーキー ユーザーID ユーザー名 ユーザー登録日 from_date end_date xxx-xxx-xxx user_1 ユーザー1 2023/03/01 2023/03/01 2023/03/05 yyy-yyy-yyy user_2 ユーザー2 2023/03/01 2023/03/01 9999/12/31 zzz-zzz-zzz user_3 ユーザー3 2023/03/04 2023/03/04 9999/12/31 xxx-xxx-xxx-1 user_1 ユーザー1-1 2023/03/01 2023/03/05 9999/12/31

27.

設計・モデリング/実装の流れ 実装編: SCD Type2の実装について ファクトテーブルの生成は必ず最新のディメンションを参照すればデータの整合性も担保される (例を元にいうとend_dateが9999/12/31で絞りこみをかける) ユーザーディメンションテーブル ユーザーキー ユーザーID ユーザー名 ユーザー登録日 from_date end_date xxx-xxx-xxx user_1 ユーザー1 2023/03/01 2023/03/01 2023/03/05 yyy-yyy-yyy user_2 ユーザー2 2023/03/01 2023/03/01 9999/12/31 zzz-zzz-zzz user_3 ユーザー3 2023/03/04 2023/03/04 9999/12/31 xxx-xxx-xxx-1 user_1 ユーザー1-1 2023/03/01 2023/03/05 9999/12/31 ユーザー行動 : ——————— Hour ユーザーキー デバイスキー アクセス数 最終アクセス時間

28.

設計・モデリング/実装の流れ 実装編: ワークフローでの冪等性を担保する実装 実装の詳細 ● 前段のDWHについてはパーティションテーブルを分けられるため再実行時にパーティション別でテーブルを 上書きするようにすれば冪等性を担保できる ● データ更新を伴わないディメンションテーブルはそのテーブルに存在しないデータだけ追記するように処理 すればよい ● データ更新のあるディメンションテーブルは実行日時のサロゲートキーが最新となるように実行日時より新し いデータは一度削除する処理を行う ● ファクトテーブルを生成する時点のサロゲートキーがディメンションテーブルと整合性が取る必要があるため 上記ディメンションテーブルの処理後に開始するようにしつつパーティションテーブルで区切って上書きする

29.

設計・モデリング/実装の流れ 実装編: ワークフローでの冪等性を担保する実装 実装の詳細 ● 前段のDWHについてはパーティションテーブルを分けられるため再実行時にパーティション別でテーブルを 上書きするようにすれば冪等性を担保できる ● データ更新を伴わないディメンションテーブルはそのテーブルに存在しないデータだけ追記するように処理 すればよい ● データ更新のあるディメンションテーブルは実行日時のサロゲートキーが最新となるように実行日時より新し いデータは一度削除する処理を行う ● ファクトテーブルを生成する時点のサロゲートキーがディメンションテーブルと整合性が取る必要があるため 上記ディメンションテーブルの処理後に開始するようにしつつパーティションテーブルで区切って上書きする

30.

設計・モデリング/実装の流れ 実装編: ワークフローでの冪等性を担保する実装 ● データ更新のあるディメンションテーブルは実行日時のサロゲートキーが最新となるように実行日時より新し いデータは一度削除する処理を行う 2023/03/04時点の処理をしたい場合(↓2023/03/05時点のテーブル) ユーザーディメンションテーブル ユーザーキー ユーザーID ユーザー名 ユーザー登録日 from_date end_date xxx-xxx-xxx user_1 ユーザー1 2023/03/01 2023/03/01 2023/03/05 yyy-yyy-yyy user_2 ユーザー2 2023/03/01 2023/03/01 9999/12/31 zzz-zzz-zzz user_3 ユーザー3 2023/03/04 2023/03/04 9999/12/31 xxx-xxx-xxx-1 user_1 ユーザー1-1 2023/03/01 2023/03/05 9999/12/31

31.

設計・モデリング/実装の流れ 実装編: ワークフローでの冪等性を担保する実装 ● データ更新のあるディメンションテーブルは実行日時のサロゲートキーが最新となるように実行日時より新し いデータは一度削除する処理を行う 2023/03/04時点のテーブルを再現 ユーザーディメンションテーブル ユーザーキー ユーザーID ユーザー名 ユーザー登録日 from_date end_date xxx-xxx-xxx user_1 ユーザー1 2023/03/01 2023/03/01 9999/12/31 yyy-yyy-yyy user_2 ユーザー2 2023/03/01 2023/03/01 9999/12/31 zzz-zzz-zzz user_3 ユーザー3 2023/03/04 2023/03/04 9999/12/31 2023/03/04より新しい発行日のデータを削除して最新のデータ発行日のデータのデータ有効日を9999/12/31にす ることで2023/03/04時点のディメンションテーブルを再現する

32.

ディメンショナルモデルのメリデメ

33.

ディメンショナルモデルのメリデメ 従来のモデル メ リ ット デ メ リ ット ディメンショナルモデル ・モデリングコストが比較的低い ・モデリングされたテーブルなら拡張性を担保できる ・モデリング時の制約(粒度など)が少なく要望に柔軟に ・モデリングに沿った定義、粒度決めがされるため 対応可能 これらのテーブルを使って安全なクエリを作れる ・拡張性を担保するのが難しい ・モデリングのコストが高い ・モデリングの沿った定義、粒度決めがされないため ・ファクト・ディメンションの冪等性担保が複雑 複雑化しやすい

34.

得られた効果

35.

得られた効果 データ自体の使い勝手 ● ファクトとディメンションをJOINするだけですぐに大福帳テーブルを作れる データ加工をした際 ● 加工するデータがファクトとディメンションテーブルに集約されるため結果的に パイプラインの本数も減らせた

36.

課題・展望

37.

課題・展望 課題 ● アナリストへのファクト・ディメンションを扱った分析の習熟 ● 他のチームのデータエンジニアにどのように展開していくか ● 既存の分析基盤への安全なリプレース方法 展望 ● アナリストの習熟・チームへの展開を含めた活動、ドキュメント作り ● 既存の分析結果に影響を与えないリプレース方法の考案 ● ディメンショナルモデルを取り入れた基盤の運用の安全な拡張

38.

参考資料

39.

参考資料 ● ディメンショナルモデル ● スタースキーマ ● スロー・チェンジ・ディメンション(Slowly Changing Dimensions) ● Slowly changing dimension ● データマネジメント知識体系ガイド

40.

告知

41.

We are Hiring!!! DeNAではデータ活用の課題を共に解決に導く仲間を募集しています ● チームは変革期 ● 様々なデータ活用の課題を共に解決しませんか? QRコードはこちら TEAMの紹介ページにアクセス可能です ↓検索はこちら DeNA データエンジニア

42.

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

43.

Q&A

44.

ディメンショナルモデルを実装したデータ分析基盤の構成 元資料のリンク:https://speakerdeck.com/dena_tech/googleclouddataplatformday-2_2020-03-31?slide=2 1