データ分析を⽀える技術 データモデリング再⼊⾨ 2022/07/27 データアナリティクス事業本部 ⽯川 覚

2 ⾃⼰紹介 名前︓⽯川 覚（いしかわ さとる） 所属︓データアナリティクス事業本部 インテグレーション部 コンサルティングチーム 担当︓コンサルタント、ブログ・登壇等 経歴︓メーカーでSE、研究開発 →ITベンチャーで製品開発、受託研究 →クラスメソッド（2014/6〜） 好きなサービス︓Amazon Redshift/Athena、Google BigQuery 2022 ALL Certified & APN AWS Top Engineers Sapporo

Amazon.com, Inc. – Press Room 3 AWSのプレスリリースにて、Redshift ServerlessのPrivate Previewに参加したクラスメソッドのコメントが掲載されました Classmethod, Inc. is a leading cloud integrator with expertise in big data, mobile, and artificial intelligence. “Our data integration platform service, called Customer Story Analytics (CSA), integrates Amazon Redshift, Amazon S3, Amazon Aurora, and other services to avoid data silos and provide powerful, unified governance between data services,” said Satoru Ishikawa, Solution Architect, Data Integration Division at Classmethod. “Amazon Redshift Serverless automates the sizing of compute and storage and quickly scales to meet demand. This elastic serverless experience mitigates manual operational costs, expands data access among departments, and accelerates autonomy on data analytics and machine learning, allowing us to scale the CSA business in new and exciting ways.” https://press.aboutamazon.com/news-releases/news-release-details/aws-announces-new-serverless-options-three-analytics-services/

関連動画 4 事前に下記のセッションも合わせてご覧ください。 https://dev.classmethod.jp/articles/20200619-devio2020- connect-primer-of-dwh/ https://dev.classmethod.jp/articles/20211015_devio2020- decade-data-analysis-platform/

アジェンダ 5 • DWHのデータモデリング • ビル・インモンさんのDWHのデータモデリング • ラルフ・キンボールさんのディメンショナルモデリング • DWHアプローチの選択 • Data Vault モデリング（Data Vault 2.0）

DWHのデータモデリング

DWHのデータモデリングとは 7 データ分析⽤途のDBであるDWH(データウェアハウス)を 分析しやすいテーブル構造や構成にする⼿法

DWHのデータモデリングとは 8 データ分析⽤途のDBであるDWH(データウェアハウス)を 分析しやすいテーブル構造や構成にする⼿法 • 基幹DBは、正規化されておりテーブルの関連が複雑で、アナリスト が分析しやすい形式ではない • 基幹DBのデータを分析対象の⽬的別に再編する • データを分かり易く、分析しやすいテーブル構造や構成にする

DWHのデータモデリングとは 9 「仏作って魂⼊れず」ということ わざがありますが、データモデリ ングせずにDWHという「器」を 導⼊しただけでは、データ分析は 捗りません。

DWHのデータモデリングとは 10 データモデリングを考えずに導⼊したデータ分析基盤は、 データマートの乱⽴、もしくは、データが分かりにくく、 分析に⼿間がかかるため、あまり使われなくなってしまっ たり、、、 ৸ͯΔ΍Μ Z z z . . . .

DWHのデータモデリングとは 11 分析に適さないテーブル構造 や構成による性能低下、オー バープロビジョニングによる コスト上昇などの問題が考え られます。 ͰͬɺσΧ

分かりそうで分からない、その理由 12 私の経験でまとめますと、 1. データモデリングというと、⼀般的には基幹システムで⽤いられる 「ERモデリング（第3正規化）」を意味する 2. DWHの誕⽣と、その発展に貢献したの⼆⼈のアーキテクトの意⾒ の相違がありました。（こちらについては後で解説します。 ） 3. DWHのデータモデリングよりも、DWH製品やサービスなどに特化 した情報ばかりが多く、データの中⾝についてフォーカスされてい ない。

分かりそうで分からない、その理由 13 この３つの情報が、時系列に関係なく 混在しており、ベテランのDBエンジニ アに限って混乱してしまいがちです。 Ͳ͕ͬͪ ͑͑Μ΍

代表的なDWHのモデリング⼿法 14 DWHの誕⽣と発展に貢献した2⼤アーキテクトと新しい潮流 • ビル・インモンさんが提唱するDWH（データウェアハウス） • ラフル・キンボールさんが提唱するディメンショナルモデリング • 新しいデータモデリング⼿法 Data Vault モデリング（Data Vault 2.0）

ビル・インモンさんの DWHのデータモデリング

インモンさんが提唱するDWH 16 DWH（データウェアハウス）の以下の4つの特性に従い、 データを横断的に分析するため⼀箇所に集める 1. 統合化（integrated） • 各部⾨のデータの表記揺れや意味を統⼀ 2. サブジェクト指向（subject oriented） • データを⽬的毎に分類して、分析の軸（次元）ごとに集計して蓄積した データである「データマート」を作成 3. 恒常性（nonvolatile） • 変更せずに参照可能な状態で保存 4. 時系列（time variant） • ⽇々⽣成されるデータ、その時点のデータ状態を保存

インモンさんが提唱するDWH 17 DWHの特⻑ • データを正規化したトップダウンアプローチ • データを正規化して冗⻑性を可能な限り回避することで、ビジネス 要件の明確化とデータ更新の不正を防⽌する • 正規化された構造は、データの読み込みがシンプルで、ビジネスの 変化に対して堅牢であり、データマート全体のデータの次元的な視 点が含まれる • DWHは、データマートの「信頼できる唯⼀の情報源（SSOT: Single Source Of Truth）」であり、企業全体の整合性と⼀貫性 を確保する

インモンさんが提唱するDWH 18 しかし、、、 正規化したデータは、様々な更新が⽣じて も⼀貫性を保つことができますが、複雑な 参照関係と多数のテーブルができてしまい、 アナリストが理解し、活⽤するのは困難で す。 ࣌ؒΛ ཁ͢Δ͔΋

ラルフ・キンボールさんの ディメンショナルモデリング

キンボールさんが提唱するディメンショナルモデル 20 ラルフ・キンボールさんが提唱するディメンショナルモデル とは、スタースキーマでDWHを構築すること • 分析したい値を持つファクトテーブルと、分析の軸（次元）となる 値を持つディメンションテーブルから構成される • ⾮正規化されているため、アナリストは複雑なテーブルを結合せず にドリルアップおよびドリルダウンできます。 • ERモデル（第3正規化）はアナリストが分析するデータとして最適 ではない

キンボールさんが提唱するディメンショナルモデル 21 エンタープライズDWH（データウェアハウス）とは反対に、ディメン ショナルデータモデルは、ボトムアップアプローチで、最初にデータ マートがビジネス要件に基づいて作られます。 Stanford. 2003. “Data Warehousing Concepts” https://web.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10736/concept.htm#i1006297

スタースキーマとは 22 売上 時間ID 店舗ID 商品ID 顧客ID 担当者ID 売上⾦額 時間 時間ID 年⽉⽇ 時間 休⽇ 店舗 店舗ID 店舗名 地域 顧客 顧客ID 顧客名 顧客分類 商品 商品ID 商品名 カテゴリ 担当者 担当者ID 担当者名 部⾨ テーブル同⼠の関係性を可視化 すると、ファクトテーブルを中 ⼼にその周りをディメンション テーブルが取り囲む形になる。

スタースキーマ 23 テーブル同⼠の関係性を可視化 すると、ファクトテーブルを中 ⼼にその周りをディメンション テーブルが取り囲む形になる。 売上 時間ID 店舗ID 商品ID 顧客ID 担当者ID 売上⾦額 時間 時間ID 年⽉⽇ 時間 休⽇ 店舗 店舗ID 店舗名 地域 顧客 顧客ID 顧客名 顧客分類 商品 商品ID 商品名 カテゴリ 担当者 担当者ID 担当者名 部⾨

ファクトテーブル 24 ファクトテーブルは、分析した い値（メジャー）の列と、その 周りのテーブル（ディメンショ ンテーブル）を参照する外部 キーの列がある 売上 時間ID 店舗ID 商品ID 顧客ID 担当者ID 売上⾦額 時間 時間ID 年⽉⽇ 時間 休⽇ 店舗 店舗ID 店舗名 地域 顧客 顧客ID 顧客名 顧客分類 商品 商品ID 商品名 カテゴリ 担当者 担当者ID 担当者名 部⾨ ファクト

ディメンションテーブル 25 ディメンションテーブルは分析 の軸（次元）を持つテーブル 売上 時間ID 店舗ID 商品ID 顧客ID 担当者ID 売上⾦額 時間 時間ID 年⽉⽇ 時間 休⽇ 店舗 店舗ID 店舗名 地域 顧客 顧客ID 顧客名 顧客分類 商品 商品ID 商品名 カテゴリ 担当者 担当者ID 担当者名 部⾨ ディメンション

ファクトとディメンションの関係 26 売上 時間ID 店舗ID 商品ID 顧客ID 担当者ID 売上⾦額 時間 時間ID 年⽉⽇ 時間 休⽇ 店舗 店舗ID 店舗名 地域 顧客 顧客ID 顧客名 顧客分類 商品 商品ID 商品名 カテゴリ 担当者 担当者ID 担当者名 部⾨ • ディメンションテーブルに対し てファクトテーブルは圧倒的に レコード数が多い傾向がある • あえて第3正規形まで正規化せず、 第2正規形で留めておくことで テーブルの結合するコストを抑 える

スノーフレークスキーマ 27 売上 時間ID 店舗ID 商品ID 顧客ID 担当者ID 売上⾦額 商品 商品ID 商品名 カテゴリID • ディメンションが更に正規化されている構成を雪の結晶に例えて、ス ノーフレークスキーマと呼ばれる • スタースキーマが望ましいが、ディメンションを事前結合するとレ コード数が爆発的に増える場合に有⽤ • 複雑なテーブル結合によるパフォーマンス低下 • テーブル間の関係把握を必要する カテゴリ カテゴリID カテゴリ ディメンションが 更に正規化

スローリーチェンジングディメンション（SCD） 28 DWHで使⽤するディメンションテーブルの更新の対応⽅法で、 Type0〜Type4と、2種類のHybrid SCDsがある • Type0 SCD • データが更新しても何もしない • Type1 SCD • 値が変化したときに、現在のテー ブルの値を上書き変更する • Type2 SCD • 値が変化したときに、新しいレ コードを追加して、レコードに開 始⽇と終了⽇を⼊れる • Type3 SCD • 現在の値と前の値のフィールドを 事前に⽤意しておき、変更があっ た場合にそれぞれに更新する • Type4 SCD • 最新の情報に更新し、古いデータ は履歴テーブルへ追加する

スローリーチェンジングディメンション（SCD） 29 DWHで使⽤するディメンションテーブルの更新の対応⽅法で、 Type0〜Type4と、2種類のHybrid SCDsがある • Hybrid SCDs（複数バージョンによる予期可能な変更） 事前に複数バージョンのデータを保存するカラムを⽤意する⽅法。 開催国2024 開催国2020 開催国2016 ⽇本 ブラジル 開催国2024 開催国2020 開催国2016 フランス ⽇本 ブラジル

スローリーチェンジングディメンション（SCD） 30 • Hybrid SCDs（単⼀バージョンによる予測不可能な変更） 予測できない変更に対して、現在の状態とそのデータの開始⽇と終 了⽇のレコードを追加する⽅法。 DWHで使⽤するディメンションテーブルの更新の対応⽅法で、 Type0〜Type4と、2種類のHybrid SCDsがある 現住所 前住所 開始⽇ 終了⽇ 北海道 北海道 20000101 99991231 現住所 前住所 開始⽇ 終了⽇ 東京 北海道 20000101 20171231 福岡 東京 20180101 99991231

ファクトの設計 31 ファクトデータの格納⽅法は、利⽤⽬的によって4つある。 • トランザクション データをテーブルに追加して保存す る。売上のように積み上げたデータ を集計して利⽤する。 • スナップショット ある時点での状況をテーブルに保存 する。在庫などある時点での数量や 状態を分析するために利⽤する。 • ファクトレスファクトテーブル ファクトの値（メジャー）を持たな いが、レコード数を⽤いて集計する。 例えば、会員マスタのレコード数か ら会員数を把握する • アキュムレーションスナップショット 1レコードの中に関連するデータを保存 する。関連するデータを分析したい場 合に⽤います。 発注⽇ 納品⽇ ⽀払⽇ 売上 20220101 100000 発注⽇ 納品⽇ ⽀払⽇ 売上 20220101 20220531 100000 発注⽇ 納品⽇ ⽀払⽇ 売上 20220101 20220531 20220630 100000

ディメンショナルデータモデルの特⻑ 32 • 正規化を意識せずに、DWH設計プロセスの初期段階を迅速に実⾏で きるため、構築が⾼速です。 • スタースキーマの利点は、クエリと分析が簡素化される⾮正規化さ れた構造のため、簡単に理解できます。 • 企業全体ではなく個々のビジネス分野とプロセスに焦点を当ててい るため、DB内のリソースが少なくて済み、システム管理が簡素化 されるため、DWHからのデータ取得が⾼速になる

キンボールさんが提唱するディメンショナルモデル 33 しかし、 、 、 • 「信頼できる唯⼀の情報源（SSOT: Single Source Of Truth）」は、失われており、 企業全体の整合性と⼀貫性を確保できない • ⾮正規化⼿法では、データが更新されると きに冗⻑データがテーブルに追加される可 能性やビジネスニーズの変化に応じたデー タ変更が困難 • ファクトに列を追加すると、テーブルの分 析軸（次元）が拡張され、パフォーマンス に悪影響を与える可能性がある Ұ௕Ұ୹ ͳͷͶ

DWHアプローチの選択

どちらのDWHアプローチが良いのか︖ 35 共通点 • データを⼀箇所に集めて横断的にデータを分析したり、BIレポート をサポートするための中央リポジトリとして機能する • データの読み込みにETL（Extract Transform Load）の概念を⽤いる • 事前にテーブルを結合することで、データの使い勝⼿やBIダッシュ ボードのパフォーマンス改善する⼤福帳テーブルを作成する 相違点 • ディメンショナルデータモデルは、データをモデル化してDWHに読 み込み、スタースキーマを構成する • ディメンショナルデータモデルは、データを素早く、より簡単に扱 えるようにするため、あえて第2正規化に留める

DWHアプローチの⽐較 36 特徴 インモンさんのアプローチ キンボールさんのアプローチ DWHの構築 時間を要する より少ない時間で済む メンテナンス 企業全体の整合性と⼀貫性 を確保できるため、簡単 難しく、冗⻑になりがちで、 変更される可能性あり コスト 初期コストが⾼いが、プロ ジェクトの開発コストが削 減される 初期コストが低く、その後の 各フェーズもほぼ同じコスト 時間 起動時間が⻑くなる 初期設定時間が短くて済む 難易度 専⾨家チーム ゼネラリストチーム データ統合要件 企業全体 個別事業領域

DWHアプローチ選択のポイント 37 DWH構築までのスピード感やETL設計の将来のバリエーショ ンに耐える能⼒を考慮する。 • 組織のビジネス⽬標 • ビジネスの性質 • 関連する時間とコスト • さまざまな機能間の依存関係

DWHアプローチ選択のポイント 38 インモンさんのアプローチ 設計にかかる時間と関連するコ ストに余裕がある安定した状況 に適している。ビジネス条件が 変化するたびに、設計は変更さ れません。 キンボールさんのアプローチ DWHを迅速に構築することが求め られ、データを素早く、アナリス トがより簡単に扱えることが優先 される状況に適している。

DWHアプローチ選択のポイント 39 インモンさんのアプローチ 設計にかかる時間と関連するコ ストに余裕がある安定した状況 に適している。ビジネス条件が 変化するたびに、設計は変更さ れません。 キンボールさんのアプローチ DWHを迅速に構築することが求め られ、データを素早く、アナリス トがより簡単に扱えることが優先 される状況に適している。 すべてのデータが相互に関連し ており、全体像を把握すること が重要な保険業や、関連する予 算に関係なく、複数の機能が関 係する製造業に向いている。

DWHアプローチ選択のポイント 40 インモンさんのアプローチ 設計にかかる時間と関連するコ ストに余裕がある安定した状況 に適している。ビジネス条件が 変化するたびに、設計は変更さ れません。 キンボールさんのアプローチ DWHを迅速に構築することが求め られ、データを素早く、アナリス トがより簡単に扱えることが優先 される状況に適している。 すべてのデータが相互に関連し ており、全体像を把握すること が重要な保険業や、関連する予 算に関係なく、複数の機能が関 係する製造業に向いている。 顧客ごとに販売したサービスを 部⾨間でリンクする必要がなく、 範囲が限られるCRMや、主に データマートのみを必要とする マーケティングに向いている。

DWHアプローチ選択のポイント 41 インモンさんのアプローチ 設計にかかる時間と関連するコ ストに余裕がある安定した状況 に適している。ビジネス条件が 変化するたびに、設計は変更さ れません。 キンボールさんのアプローチ DWHを迅速に構築することが求め られ、データを素早く、アナリス トがより簡単に扱えることが優先 される状況に適している。 取り扱うデータや分析要件に応じて、 2つのアプローチ使い分けることが多 く、排他的なアプローチではない。

Data Vault モデリング（Data Vault 2.0）

Data Vault 2.0とは 43 DAN LINSTEDT.COM. “Data Vault Basics” the data vault is a detail oriented, historical tracking and uniquely linked set of normalized tables that support one or more functional areas of business. it is a hybrid approach encompassing the best of breed between 3rd normal form (3nf) and star schema. the design is flexible, scalable, consistent and adaptable to the needs of the enterprise. it is a data model that is architected specifically to meet the needs of todayʼs enterprise data warehouses. Data Vaultは、詳細指向で、履歴を追跡し、⼀意にリンクされた正 規化テーブルのセットであり、1 つまたは複数のビジネス機能領域 をサポートします。これは、第3正規形（3NF）とスタースキーマ の良いところを取り⼊れたハイブリッドなアプローチです。これは、 今⽇のエンタープライズデータウェアハウスのニーズを満たすため に特別に設計されたデータモデルです。 DAN LINSTEDT.COM. “Data Vault Basics” https://danlinstedt.com/solutions-2/data-vault-basics/

Data Vault のアーキテクチャ 44 Data Vaultのアーキテク チャは、3つの層で構成され る。 Sales Finance Contracts Staging Area (relational) Business Vault Information Delivery Operational Vault Metrics Vault Start Schemas Cube Report collection Meta mart Metrics mart Enterprise Data Warehouse Staging Master Data Data Vault (relational EDW)

Data Vault のアーキテクチャ 45 Staging ソースデータをロードして、 データ型やサイズ、⽂字 コードの変換、ハッシュ化 したプライマリキー、デー タロード⽇時、データソー ス元のカラムの追加などの 変換を適⽤する。 Sales Finance Contracts Staging Area (relational) Business Vault Information Delivery Operational Vault Metrics Vault Start Schemas Cube Report collection Meta mart Metrics mart Enterprise Data Warehouse Staging Master Data Data Vault (relational EDW)

Data Vault のアーキテクチャ 46 Sales Finance Contracts Staging Area (relational) Business Vault Information Delivery Operational Vault Metrics Vault Start Schemas Cube Report collection Meta mart Metrics mart Enterprise Data Warehouse Staging Master Data Data Vault (relational EDW) Enterprise Data Warehouse Data Vault データの履歴付きコピーを保 持する。ソースに依存しない データを格納することを除い て、フィルターやビジネス変 換は⾏わない。（Hub、 Link、Satelliteを含む） Business Vault PIT(Point in Time) Table やBridge Tableを作り、SQL の結合処理の複雑化を回避。

Data Vault のアーキテクチャ 47 Sales Finance Contracts Staging Area (relational) Business Vault Information Delivery Operational Vault Metrics Vault Start Schemas Cube Report collection Meta mart Metrics mart Enterprise Data Warehouse Staging Master Data Data Vault (relational EDW) Information Delivery BIダッシュボードや抽出など、 ユーザーが最もアクセスする データを保存する場所 ⼀般にスタースキーマが⽤い られ、データマートや⼤福帳 テーブルなどを保存する Data Vaultでは、いわゆる データマートは、インフォ メーションマートと呼ばれる

Data Vault のアーキテクチャ 48 Sales Finance Contracts Staging Area (relational) Information Delivery Start Schemas Cube Report collection Meta mart Metrics mart Staging Master Data

Data Vault のアーキテクチャ 49 Sales Finance Contracts Staging Area (relational) Business Vault Information Delivery Operational Vault Metrics Vault Start Schemas Cube Report collection Meta mart Metrics mart Enterprise Data Warehouse Staging Master Data Data Vault (relational EDW) Enterprise Data Warehouse が、Data Vaultの特徴です。 すべての属性は、キンボール さんのディメンションモデリ ングのSCD Type2に似た⽅法 で管理されます。

Data Vault の特徴 50 Hub、Satellite、Linkと呼ばれる3種類のテーブルで作られたエン タープライズデータモデルの領域（Data Vault）をEnterprise Data Warehouseに作る。 • Hub ソースデータのビジネスキーにより⽣成されるハッ シュキー、ビジネスキー、データロード⽇時、デー タソース元を持つ。 • Satellite ソースデータのHubが持たなかったカラムと、履歴 管理⽤のカラムを持つ。履歴管理⽤のカラムは、 Hub のプライマリキー、データロード⽇時、データ ソース元、ハッシュ値（hash_diff）を持つ。 • Link Hub間やSatelliteを含めたビジネスオブジェクトの リレーションシップを持つ。 Link Hub Hub Satellite Satellite Satellite Satellite Hub

Data Vault が特⻑ 51 •ビジネスキーの管理をビジネスエンティティ (Hub、Satellite、Link) のすべての属性から切り離し、テーブル間の独⽴性を確保できる •すべてのデータを保持して、「信頼できる唯⼀の情報源（SSOT: Single Source Of Truth）」として機能するため、リエンジニアリン グなしでバリエーションや変更に、柔軟に対応できる •履歴レコードの追跡による更新管理と監査可能性を提供します •フォールトトレラントなインジェストパイプラインを⽤いて、⾼度な 並列処理でデータの読み込みが可能です •データソースで更新があった場合、レコード追加時の重複管理により 値の変更の有無が判別（hash_diff）が可能です

Data Vault の課題 52 しかし、 、 、 • Data Vaultモデルはより複雑になる可 能性がある • Data Vaultを正しく実装する⽅法を知 るデータエンジニアが必要です • データの完全な履歴を維持するために ⼤容量のストレージが必要になる場合 がある • データがData Vaultに取り込まれれて も、BIツールなどで利⽤するためには、 スタースキーマを作成する必要がある αΫοͱ෼ੳ͍ͨ͠ ͚ͩͳͷΑ

Data Vault 2.0 を実現するためのフレームワーク 53 • Data Vault 2.0 データウェアハウスを構築するために必要なETLを ⽣成および実⾏する dbt パッケージ • SQLとテンプレート、マクロを⽤いてテーブルなどを構成を管理 • 現在は、Snowflake、MS SQL Server、BigQueryをサポート • 将来的に、Redshift、Datatabricks、PostgreSQLもサポート予定 GitHub - Datavault-UK / dbtvault ※ とは、 ETL（Extract Transform Load）プロセスの中で、DWHに既 に読み込まれているデータの「 Transform （変換）」を担うツール。

まとめ

データモデリング⼿法を採⽤する⽅針 55 • 素早くデータ分析基盤を⽴ち上げる場合 • キンボールさんのディメンショナルデータモデリングを採⽤する • 具体的な分析対象・データに対するボトムアップ・アプローチ • 全てのデータを蓄積するデータ分析基盤を⽴ち上げる場合 • Data Vault 2.0もしくはインモンさんの第3正規化で累積デー タを維持する • BIや抽出などのプレゼンテーション領域は必須、ディメンショナ ルデータモデリングとのハイブリットになる • スタースキーマやデータマート作成に必要な累積データが明らか な段階でのトップダウン・アプローチ

データモデリング⼿法を採⽤する順番 56 • データ分析基盤を新規で⽴ち上げる場合 • まずは、ディメンショナルデータモデリングを採⽤、スタース キーマでDWHを構築する • データの活⽤を通じてユースケースを⾒極める • 既存のデータ分析基盤を改善したい場合 • 課題（As-is、To-be）を明らかする • スタースキーマや⼤福帳テーブルなどを改善する • 分析要件に柔軟に対応できるようにするため、 Data Vault 2.0 もしくはインモンさんの第3正規化で累積データを維持を検討 • モデリングに最適なアーキテクチャ（データレイク、DWH、 データ共有、スケーリング戦略）の⾒直し

DWHのデータモデリングの必要性を 感じて頂けたら幸いです。

No content