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

自己紹介

自己紹介渡辺ブルーノ 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 ● データマネジメント知識体系ガイド