Zenn
Open9

dbt-dimensional-modelling を使ってディメンショナルモデリングに入門

dbt
データエンジニアリング
hiracky16hiracky16

とは

  • dbt-dimensional-modelling とは dbt を使ったディメンショナルモデリングを実現するためのチュートリアル
  • GitHub リポジトリに 1 章ごとに説明書きがあるのでやりながら学んでいく
  • コンテンツ自体は英語だが自分なりに訳して日本語にしていく（訳間違ってたらすみません 🙏）
hiracky16hiracky16

0 章: ディメンショナルモデリングの概念

  • ディメンショナルモデリングはデータプラクティショナー（データエンジニア？）が分析のためにデータを整理して提供するための手法の一つ
  • その他には Data Vault や第三正規化（3NF）、One Big Table などがある

  • ディメンションモデリングは、1996 年に Ralph Kimball が著書『 The Data Warehouse Toolkit』で紹介した手法
  • ディメンションモデリングの目的は、生データを取得し、それをビジネスを表すファクトテーブルとディメンションテーブルに変換すること
  • ディメンショナルモデリングの利点
    • 分析用にシンプルなデータモデリングが実現可能
      • ディメンションモデルをを使用するとき複雑な結合が不要
      • ファクトテーブルとディメンションテーブルの結合のみで分析が可能
    • 分析クエリが DRY になる
      • ディメンションは他のファクトで再利用可能できるためさぎょうとコードロジックの重複を回避できる
    • 高速にデータ取得ができる
      • すでに結合、集計済みのテーブルを扱うため計算が早い
    • 実際のビジネスプロセスとの緊密な連携
      • ビジネスプロセスとメトリックスはディメンショナルモデリングにおいてモデル化及び計算される
hiracky16hiracky16

1 章: dbt プロジェクトとデータベースのセットアップ

> python
>>> import duckdb
>>> con = duckdb.connect('target/adventureworks.duckdb')
>>> print(con.execute("select * from sales.salesorderheader limit 10;").fetchall())

DuckDB

  • （公式サイトより）
  • 高速なインプロセスの分析用データベース
  • 特徴としては以下が挙げられる
    • インストールやデプロイ方法がシンプル
    • Linux や macOS, Windows で動く
    • OSS, MIT ライセンスで無料
hiracky16hiracky16

2 章: ビジネスプロセスを特定する

  • ビジネスプロセスの特定はビジネスユーザーとの共同作業で行われる
  • ビジネスユーザーは目標とプロセスに関するコンテキストを把握しておりその情報を提供できる
  • 例となるビジネスである AdventureWorks の CEO と話をすると、次の情報が得られたとする

AdventureWorks は自転車を製造し、消費者 (B2C) と企業 (B2B) に販売しています。自転車は世界中の顧客に出荷されます。私は会社の CEO として、2011 年度末までに当社が生み出した収益を次の項目別に知りたいと思います。
製品カテゴリとサブカテゴリ
お客様
注文の状況
配送先の国、州、都市

  • 問題となるビジネスプロセスは販売プロセスだとわかったので販売プロセスのディメンションモデルを設計する
hiracky16hiracky16

3 章: ファクトテーブルとディメンションテーブルを識別する

  • 前提となる情報

AdventureWorks は自転車を製造し、消費者 (B2C) と企業 (B2B) に販売しています。自転車は世界中の顧客に出荷されます。私は会社の CEO として、2011 年度末までに当社が生み出した収益を次の項目別に知りたいと思います。
製品カテゴリとサブカテゴリ
お客様
注文の状況
配送先の国、州、都市

  • ビジネスの販売プロセスを表すディメンションモデルを作成し次の方法でデータを細分化する
    • 製品カテゴリとサブカテゴリ
    • お客様
    • 注文の状況
    • 配送先の国、州、都市
    • 日付（年、月、日）

ファクトテーブル

  • 現実世界のビジネスプロセスを表すデータベーステーブル
  • ファクトテーブルの各レコードは次のビジネスイベントを表す
    • アイテム販売
    • ウェブサイトのクリック
    • 生産作業指示書
  • sales.salesorderheader テーブルには注文に使用されたクレジットカード、配送先住所など顧客に関する情報が含まれている
  • sales.salesorderdetail には注文された製品に関わる情報、注文数量、単価が含まれており収益計算に使用できる
  • この 2 つを結合して fct_sales を作成する 
with stg_salesorderheader as (
    select
        salesorderid,
        customerid,
        creditcardid,
        shiptoaddressid,
        status as order_status,
        cast(orderdate as date) as orderdate
    from {{ ref('salesorderheader') }}
),

stg_salesorderdetail as (
    select
        salesorderid,
        salesorderdetailid,
        productid,
        orderqty,
        unitprice,
        unitprice * orderqty as revenue
    from {{ ref('salesorderdetail') }}
)

select
    {{ dbt_utils.generate_surrogate_key(['stg_salesorderdetail.salesorderid', 'salesorderdetailid']) }} as sales_key,
    {{ dbt_utils.generate_surrogate_key(['productid']) }} as product_key,
    {{ dbt_utils.generate_surrogate_key(['customerid']) }} as customer_key,
    {{ dbt_utils.generate_surrogate_key(['creditcardid']) }} as creditcard_key,
    {{ dbt_utils.generate_surrogate_key(['shiptoaddressid']) }} as ship_address_key,
    {{ dbt_utils.generate_surrogate_key(['order_status']) }} as order_status_key,
    {{ dbt_utils.generate_surrogate_key(['orderdate']) }} as order_date_key,
    stg_salesorderdetail.salesorderid,
    stg_salesorderdetail.salesorderdetailid,
    stg_salesorderdetail.unitprice,
    stg_salesorderdetail.orderqty,
    stg_salesorderdetail.revenue
from stg_salesorderdetail
inner join stg_salesorderheader on stg_salesorderdetail.salesorderid = stg_salesorderheader.salesorderid

サロゲートキー

ディメンションテーブル

  • ビジネスプロセス中のイベントのコンテキスト情報または説明情報を表すために使用
  • ディメンションの例には次のものがある
    • 顧客の詳細
    • ウェブサイトのクリック場所の詳細
    • 商品の詳細

スノーフレークスキーマ

  • ファクトテーブルが中心にありスノーフレークの中心から多数のディメンションテーブルが分岐している
  • このときディメンションテーブルは正規化されてているため多数の結合が発生する

スタースキーマ

  • ファクトテーブルが中心にあるのは変わりないがディメンションテーブルを非正規化して保持する形

  • スタースキーマを採用すると以下のような 6 つのディメンションテーブルができる

hiracky16hiracky16

4 章: ディメンションテーブルを作成する

select
    {{ dbt_utils.generate_surrogate_key(['stg_product.productid']) }} as product_key,
    stg_product.productid,
    stg_product.name as product_name,
    stg_product.productnumber,
    stg_product.color,
    stg_product.class,
    stg_product_subcategory.name as product_subcategory_name,
    stg_product_category.name as product_category_name
from stg_product
left join stg_product_subcategory on stg_product.productsubcategoryid = stg_product_subcategory.productsubcategoryid
left join stg_product_category on stg_product_subcategory.productcategoryid = stg_product_category.productcategoryid
hiracky16hiracky16

5 章: ファクトテーブルを作成する

select
    {{ dbt_utils.generate_surrogate_key(['stg_salesorderdetail.salesorderid', 'salesorderdetailid']) }} as sales_key,
    {{ dbt_utils.generate_surrogate_key(['productid']) }} as product_key,
    {{ dbt_utils.generate_surrogate_key(['customerid']) }} as customer_key,
    {{ dbt_utils.generate_surrogate_key(['creditcardid']) }} as creditcard_key,
    {{ dbt_utils.generate_surrogate_key(['shiptoaddressid']) }} as ship_address_key,
    {{ dbt_utils.generate_surrogate_key(['order_status']) }} as order_status_key,
    {{ dbt_utils.generate_surrogate_key(['orderdate']) }} as order_date_key,
  • ファクトテーブルはデータボリューム的に大きくなることも予想されるので incremental を採用することも考慮する
hiracky16hiracky16

6 章：ディメンションモデルの関係を文章化する

  • ER 図を作成してディメンションモデルの関係を理解しやすくする
  • こちらを参考にdbt で ER 図を生成してみる

https://zenn.dev/kyami/articles/6c86f3e8ff39c0

  • ディメンションモデルでテーブル間のリレーションは簡素になっているはず
  • だが ER 図を簡略的に作る仕組みが無いと運用的にはつらそう…
hiracky16hiracky16

7 章: ディメンションモデルの使用

  • 最後にデータウェアハウスを Tableau や PowerBI、Looker などの BI に接続することで使用
  • 最新の BI ではテーブル間のリレーションをサポートするセマンティックレイヤーが組み込まれている
  • 例えば Looker では LookML を使用して関係を定義できる
explore: fct_order {
  join: dim_user {
    sql_on: ${fct_order.user_key} = ${dim_user.user_key} ;;
    relationship: many_to_one
  }
}
  • BI ツールにリレーションシップをサポートするセマンティクスレイヤーがない場合はファクトテーブルをそのすべてのディメンションテーブルに結合する大きな 1 つのテーブル One Big Table（OBT）を作成してリレーションを反映
  • dbt_utils.star() は代理キー列を除くすべての列が選択される