Snowflakeで定義したSemantic ViewをOmniでも有効活用！OmniのSemantic View Integration機能を試してみた

CREATE OR REPLACE SEMANTIC VIEW jaffle_shop_analysis
  -- 1. 論理テーブル（同義語を追加）
  TABLES (
    order_items AS SAGARA_JAFFLE_SHOP_JAPANESE.MART.ORDER_ITEMS
      PRIMARY KEY (order_item_id)
      WITH SYNONYMS = ('transactions', 'sales_records', '注文明細', '取引')
      COMMENT = '個々の商品購入ごとのトランザクション明細テーブル',

    products AS SAGARA_JAFFLE_SHOP_JAPANESE.MART.PRODUCTS
      PRIMARY KEY (product_id)
      WITH SYNONYMS = ('items', 'merchandise', 'goods', '商品マスタ', 'カタログ')
      COMMENT = '販売されている商品の詳細情報を持つマスタテーブル'
  )

  -- 2. リレーションシップ
  RELATIONSHIPS (
    order_items_to_products AS
      order_items (product_id) REFERENCES products (product_id)
  )

  -- 3. ファクト（計算用の中間数値）
  FACTS (
    -- 売上
    order_items.f_sales AS product_price
      COMMENT = '値引き前の商品単価',
    -- 原価
    order_items.f_cost AS supply_cost
      COMMENT = '商品の調達原価',
    -- 粗利（行レベル）
    order_items.f_profit AS product_price - supply_cost
      COMMENT = '商品単体での粗利益'
  )

  -- 4. ディメンション（分析軸の強化とシノニム）
  DIMENSIONS (
    -- [時間軸] 自動変換
    order_items.dim_date AS TO_DATE(purchased_at)
      WITH SYNONYMS = ('date', 'day', '日付', '販売日')
      COMMENT = '商品が購入された日付',

    order_items.dim_year AS YEAR(purchased_at)
      WITH SYNONYMS = ('year', 'yr', '年', '年度')
      COMMENT = '購入された年',

    order_items.dim_month AS MONTH(purchased_at)
      WITH SYNONYMS = ('month', 'mo', '月')
      COMMENT = '購入された月（1-12）',

    -- [商品軸] 詳細化
    products.dim_product_name AS product_name
      WITH SYNONYMS = ('item_name', 'sku', '商品名', 'メニュー名')
      COMMENT = '商品の具体的な名前（例: "nutellaphone who dis?"）',

    products.dim_product_type AS product_type
      WITH SYNONYMS = ('category', 'genre', 'type', 'カテゴリ', '商品タイプ', 'ジャンル')
      COMMENT = '商品の分類（jaffle: サンドウィッチ, beverage: ドリンク）',

    -- [フラグ] フィルタ用
    products.dim_is_food AS is_food_item
      WITH SYNONYMS = ('food_flag', '食べ物', 'フード')
      COMMENT = '食べ物かどうかの真偽値',

    products.dim_is_drink AS is_drink_item
      WITH SYNONYMS = ('drink_flag', '飲み物', 'ドリンク')
      COMMENT = '飲み物かどうかの真偽値'
  )

  -- 5. メトリクス（ビジネス指標と計算式）
  METRICS (
    -- 総売上
    order_items.m_total_revenue AS SUM(f_sales)
      WITH SYNONYMS = ('sales', 'gross_sales', 'gtv', 'total_sales', 'amount', '売上', '総売上', '売上金額', '日商')
      COMMENT = '特定の期間やカテゴリにおける売上の合計金額',

    -- 総利益
    order_items.m_total_profit AS SUM(f_profit)
      WITH SYNONYMS = ('profit', 'gross_profit', 'earnings', 'margin', '利益', '粗利', '儲け')
      COMMENT = '売上から原価を引いた粗利益の合計',

    -- 利益率（追加指標）
    order_items.m_profit_margin AS SUM(f_profit) / NULLIF(SUM(f_sales), 0)
      WITH SYNONYMS = ('margin_rate', 'profitability', 'roi', '利益率', '粗利率')
      COMMENT = '売上に占める利益の割合（0～1.0）',

    -- 販売点数
    order_items.m_items_sold AS COUNT(order_item_id)
      WITH SYNONYMS = ('quantity', 'volume', 'units', 'count', '販売数', '個数', '数量')
      COMMENT = '販売された商品の個数',

    -- 注文件数（ユニークオーダー数）
    -- 1回の注文で複数商品買うこともあるため、Order_IDのユニーク数を数える
    order_items.m_order_count AS COUNT(DISTINCT order_id)
      WITH SYNONYMS = ('transactions_count', 'baskets', 'orders', '注文数', '客数', '決済回数')
      COMMENT = '決済が行われた回数（バスケット数）',

    -- 客単価（AOV: Average Order Value）
    order_items.m_aov AS SUM(f_sales) / NULLIF(COUNT(DISTINCT order_id), 0)
      WITH SYNONYMS = ('average_order_value', 'sales_per_order', 'spend_per_customer', '客単価', '平均注文額')
      COMMENT = '1回の注文あたりの平均売上金額'
  )

  COMMENT = 'Jaffle Shopの包括的な販売実績データモデル。売上、利益、客単価などを、商品別・日別に分析可能。';

SELECT * FROM SEMANTIC_VIEW(
    jaffle_shop_analysis
    DIMENSIONS products.dim_product_type
    METRICS order_items.m_total_revenue,
            order_items.m_profit_margin
)
ORDER BY m_total_revenue DESC;

# Reference this view as omni_dbt_mart__jaffle_shop_analysis__order_items
# View is defined in the semantic view JAFFLE_SHOP_ANALYSIS in the data warehouse
schema_label: ""
description: 個々の商品購入ごとのトランザクション明細テーブル

schema: omni_dbt_mart
folder: MART/jaffle_shop_analysis
table_name: ORDER_ITEMS

dimensions:
  order_item_id:
    sql: '"ORDER_ITEM_ID"'
    format: ID
    primary_key: true

  order_id:
    sql: '"ORDER_ID"'
    format: ID

  product_id:
    sql: '"PRODUCT_ID"'
    format: ID

  purchased_at:
    sql: '"PURCHASED_AT"'

  product_name:
    sql: '"PRODUCT_NAME"'

  product_price:
    sql: '"PRODUCT_PRICE"'

  is_food_item:
    sql: '"IS_FOOD_ITEM"'

  is_drink_item:
    sql: '"IS_DRINK_ITEM"'

  supply_cost:
    sql: '"SUPPLY_COST"'

  dim_date:
    # Dimension is defined in the semantic view JAFFLE_SHOP_ANALYSIS in the data warehouse
    sql: TO_DATE(${omni_dbt_mart__jaffle_shop_analysis__order_items.purchased_at})
    description: 商品が購入された日付
    synonyms: [ date, day, 日付, 販売日 ]

  dim_month:
    # Dimension is defined in the semantic view JAFFLE_SHOP_ANALYSIS in the data warehouse
    sql: MONTH(${omni_dbt_mart__jaffle_shop_analysis__order_items.purchased_at})
    description: 購入された月（1-12）
    synonyms: [ month, mo, 月 ]

  dim_year:
    # Dimension is defined in the semantic view JAFFLE_SHOP_ANALYSIS in the data warehouse
    sql: YEAR(${omni_dbt_mart__jaffle_shop_analysis__order_items.purchased_at})
    description: 購入された年
    synonyms: [ year, yr, 年, 年度 ]

  f_cost:
    # Dimension is defined in the semantic view JAFFLE_SHOP_ANALYSIS in the data warehouse
    sql: ${omni_dbt_mart__jaffle_shop_analysis__order_items.supply_cost}
    description: 商品の調達原価

  f_profit:
    # Dimension is defined in the semantic view JAFFLE_SHOP_ANALYSIS in the data warehouse
    sql: ${omni_dbt_mart__jaffle_shop_analysis__order_items.product_price} -
      ${omni_dbt_mart__jaffle_shop_analysis__order_items.supply_cost}
    description: 商品単体での粗利益

  f_sales:
    # Dimension is defined in the semantic view JAFFLE_SHOP_ANALYSIS in the data warehouse
    sql: ${omni_dbt_mart__jaffle_shop_analysis__order_items.product_price}
    description: 値引き前の商品単価

measures:
  count:
    aggregate_type: count

  m_aov:
    # Measure is defined in the semantic view JAFFLE_SHOP_ANALYSIS in the data warehouse
    sql: SUM(${omni_dbt_mart__jaffle_shop_analysis__order_items.f_sales}) /
      NULLIF(COUNT(DISTINCT
      ${omni_dbt_mart__jaffle_shop_analysis__order_items.order_id}), 0)
    description: 1回の注文あたりの平均売上金額
    synonyms: [ average_order_value, sales_per_order, spend_per_customer, 客単価, 平均注文額 ]

  m_items_sold:
    # Measure is defined in the semantic view JAFFLE_SHOP_ANALYSIS in the data warehouse
    sql: COUNT(${omni_dbt_mart__jaffle_shop_analysis__order_items.order_item_id})
    description: 販売された商品の個数
    synonyms: [ quantity, volume, units, count, 販売数, 個数, 数量 ]

  m_order_count:
    # Measure is defined in the semantic view JAFFLE_SHOP_ANALYSIS in the data warehouse
    sql: COUNT(DISTINCT
      ${omni_dbt_mart__jaffle_shop_analysis__order_items.order_id})
    description: 決済が行われた回数（バスケット数）
    synonyms: [ transactions_count, baskets, orders, 注文数, 客数, 決済回数 ]

  m_profit_margin:
    # Measure is defined in the semantic view JAFFLE_SHOP_ANALYSIS in the data warehouse
    sql: SUM(${omni_dbt_mart__jaffle_shop_analysis__order_items.f_profit}) /
      NULLIF(SUM(${omni_dbt_mart__jaffle_shop_analysis__order_items.f_sales}),
      0)
    description: 売上に占める利益の割合（0～1.0）
    synonyms: [ margin_rate, profitability, roi, 利益率, 粗利率 ]

  m_total_profit:
    # Measure is defined in the semantic view JAFFLE_SHOP_ANALYSIS in the data warehouse
    sql: SUM(${omni_dbt_mart__jaffle_shop_analysis__order_items.f_profit})
    description: 売上から原価を引いた粗利益の合計
    synonyms: [ profit, gross_profit, earnings, margin, 利益, 粗利, 儲け ]

  m_total_revenue:
    # Measure is defined in the semantic view JAFFLE_SHOP_ANALYSIS in the data warehouse
    sql: SUM(${omni_dbt_mart__jaffle_shop_analysis__order_items.f_sales})
    description: 特定の期間やカテゴリにおける売上の合計金額
    synonyms: [ sales, gross_sales, gtv, total_sales, amount, 売上, 総売上, 売上金額, 日商 ]

#The info below was pulled from your dbt repository and is read-only.
dbt:
  name: order_items
  target_schema: PROD
  config:
    schema: mart
    materialized: table
  code: |-
    with

    order_items as (

        select * from {{ ref('stg_order_items') }}

    ),

    orders as (

        select * from {{ ref('stg_orders') }}

    ),

    products as (

        select * from {{ ref('stg_products') }}

    ),

    supplies as (

        select * from {{ ref('stg_supplies') }}

    ),

    order_supplies_summary as (

        select
            product_id,

            sum(supply_cost) as supply_cost

        from supplies

        group by 1

    ),

    joined as (

        select
            order_items.*,

            orders.purchased_at,
            -- orders.ordered_at,

            products.product_name,
            products.product_price,
            products.is_food_item,
            products.is_drink_item,

            order_supplies_summary.supply_cost

        from order_items

        left join orders on order_items.order_id = orders.order_id

        left join products on order_items.product_id = products.product_id

        left join order_supplies_summary
            on order_items.product_id = order_supplies_summary.product_id

    )

    select * from joined
  referenced_by: [ orders, product_total_sales ]

# Reference this view as omni_dbt_mart__jaffle_shop_analysis__products
# View is defined in the semantic view JAFFLE_SHOP_ANALYSIS in the data warehouse
schema_label: ""
description: 販売されている商品の詳細情報を持つマスタテーブル

schema: omni_dbt_mart
folder: MART/jaffle_shop_analysis
table_name: PRODUCTS

dimensions:
  product_id:
    sql: '"PRODUCT_ID"'
    format: ID
    primary_key: true

  product_name:
    sql: '"PRODUCT_NAME"'

  product_type:
    sql: '"PRODUCT_TYPE"'

  product_description:
    sql: '"PRODUCT_DESCRIPTION"'

  product_price:
    sql: '"PRODUCT_PRICE"'

  is_food_item:
    sql: '"IS_FOOD_ITEM"'

  is_drink_item:
    sql: '"IS_DRINK_ITEM"'

  dim_is_drink:
    # Dimension is defined in the semantic view JAFFLE_SHOP_ANALYSIS in the data warehouse
    sql: ${omni_dbt_mart__jaffle_shop_analysis__products.is_drink_item}
    description: 飲み物かどうかの真偽値
    synonyms: [ drink_flag, 飲み物, ドリンク ]

  dim_is_food:
    # Dimension is defined in the semantic view JAFFLE_SHOP_ANALYSIS in the data warehouse
    sql: ${omni_dbt_mart__jaffle_shop_analysis__products.is_food_item}
    description: 食べ物かどうかの真偽値
    synonyms: [ food_flag, 食べ物, フード ]

  dim_product_name:
    # Dimension is defined in the semantic view JAFFLE_SHOP_ANALYSIS in the data warehouse
    sql: ${omni_dbt_mart__jaffle_shop_analysis__products.product_name}
    description: '商品の具体的な名前（例: "nutellaphone who dis?"）'
    synonyms: [ item_name, sku, 商品名, メニュー名 ]

  dim_product_type:
    # Dimension is defined in the semantic view JAFFLE_SHOP_ANALYSIS in the data warehouse
    sql: ${omni_dbt_mart__jaffle_shop_analysis__products.product_type}
    description: "商品の分類（jaffle: サンドウィッチ, beverage: ドリンク）"
    synonyms: [ category, genre, type, カテゴリ, 商品タイプ, ジャンル ]

measures:
  count:
    aggregate_type: count

#The info below was pulled from your dbt repository and is read-only.
dbt:
  name: products
  target_schema: PROD
  config:
    schema: mart
    materialized: table
  code: |-
    with

    products as (

        select * from {{ ref('stg_products') }}

    )

    select * from products