最近話題になっているセマンティックレイヤーを自分も触れてみようと思って、オープンソースである Cube Core を試してみようと思います。

1. セマンティックレイヤーとは

ざっくり言うと、以下の特徴を持つ機能と理解しています。

データモデルやビジネスロジック（KPI 算出ロジックなど）の一元提供
- データを利活用する様々な BI ツールや分析ツール、プログラムにデータモデルとビジネスロジックを一元的に提供することで、レポートや分析結果の信頼性を向上させます。
データモデルやビジネスロジックの管理がコードベースで可能
- これにより、バージョン管理や CI/CD などの実現を容易にします。

詳しい説明は以下がとても分かりやすいです。

2. Cube Core とは

セマンティックレイヤーと言うと dbt Semantic Layer が一番有名な気がしますが、これは dbt Cloud（SaaS）の機能です。今回は気軽にセルフホストできて気軽に試すことができるオープンソースソフトウェア Cube Core を利用します。

先の 1. で述べたセマンティックレイヤーの特徴に加え、以下のような特徴を持ちます。

事前集計済みキャッシュなどを保持することができ、性能向上に寄与
データを　REST/GraphQL API や SQL API で提供可能
- 利用対象となるデータがどのような DB やサービスなどに保存されていても、同じインターフェースでアクセス可能
- SQL API は PostgreSQL 互換プロトコルを利用（PostgreSQL データベースとしてアクセスできる）

ちなみに、Cube Core をマネージドサービスとして提供している Cube Cloud というサービスもあります。こちらには Semantic Layer Sync という各種 BI ツールにデータモデルを提供する機能があります。

後で触れますが、BI ツールからのアクセスについては SQL API ではなく Semantic Layer Sync を通したアクセスが本来想定している使い方だと思いますが、今回は Cube Core + SQL API を試します。

3. Cube Core を準備する

今回は Snowflake 上のデータに対して、Cube Core でデータモデルとビジネスロジックを実装し、psql コマンドで SQL 文を発行することで試してみます。

3-1. Cube Core インストール

Cube Core は Docker でインストール可能です。今回は、Oracle Linux 9.3 上に docker compose で導入しました。

docker-compose.yml

version: "2.2"

services:
  cube_api:
    restart: always
    image: cubejs/cube:v0.35.42
    ports:
      - 4000:4000
      - 15432:15432
    environment:
      - CUBEJS_DEV_MODE=true
      - CUBEJS_DB_TYPE=snowflake
      - CUBEJS_DB_SNOWFLAKE_ACCOUNT=******
      - CUBEJS_DB_SNOWFLAKE_WAREHOUSE=CUBE_WH
      - CUBEJS_DB_NAME=CUBE_DB
      - CUBEJS_DB_USER=CUBE_USER
      - CUBEJS_DB_PASS=******
    volumes:
      - /cube/conf:/cube/conf

ポート設定
- REST API とブラウザからの UI アクセスにポート 4000 番を利用（今回は使いません）
- SQL API にポート 15432 番を利用
環境変数設定
- CUBEJS_DEV_MODE は今回の環境を開発用(true)/本番用(false)のどちらとして起動するかを指定します。開発用とすると、細かい設定をせずとも基本的な機能は動作します。
- 2行目以降はデータソース（今回は Snowflake）への接続情報です。
ボリュームマウント設定
- コンテナ内の /cube/conf ディレクトリに設定やビジネスロジックの定義コードが配置されます。コンテナ外部から編集できるよう、外部に永続化します。

（version: "2.2" は Cube Core のドキュメントからそのまま引っ張ってきていますが、古いですね）

3-2. Snowflake のテーブル準備

次に Snowflake 側に以下のテーブルを作成しておきます。

create table customers (
	customer_id integer primary key,
	customer_rank varchar(1) not null
);

create table items (
	item_id integer primary key,
	item_category varchar(10) not null
);

create table orders (
    order_id integer primary key,
    order_timestamp timestamp not null,
    customer_id integer not null,
    item_id integer not null,
    order_quantity integer not null,
    order_amount integer not null,
    discount_amount integer not null,
    is_cancel varchar(1) not null
);

ER 図で表現すると以下になります。ごくシンプルなスタースキーマです。

3-3. Cube Core 上でのデータモデルとビジネスロジックの実装

Cube Core ではデータモデルとビジネスロジックを YAML か JavaScript で定義することができます。今回は YAML で定義します。

データソース上のテーブル/ビューにビジネスロジックを追加したものを Cube Core ではキューブと言い、/cube/conf/model/cubes ディレクトリ配下に定義ファイルを配置します。

今回は Snowflake 上の customers、items、orders テーブルそれぞれに対して、d_customers、d_items、f_orders という３つのキューブを作成します。

（d_ はディメンジョンを、f_ はファクトを指します）

まずは d_customers について。

d_customers.yml

cubes:
  - name: d_customers
    sql_table: public.customers
    data_source: default

    joins: []

    dimensions:
      - name: customer_id
        sql: customer_id
        type: number
        primary_key: true

      - name: customer_rank
        sql: customer_rank
        type: string

    measures:
      - name: count
        type: count

cubes 直下の name、sql_table でキューブ名と該当する Snwoflake テーブルを指定します。
dimensions で集計の際の軸となる列を指定します。あとでキューブ間の結合条件を指定するのですが、それを行うために主キー設定が必要になるので、primary_key を設定しています。
メジャー（集計する値）は measures 配下に指定します。今回はディメンジョンテーブルなのでメジャー項目はありませんが、件数カウントできるように count という列を作成しておきます（メジャーにおける type はデータ型ではなく集計関数を指すので注意）。

同様に d_items を定義します。こちらは新たに補足することはありません。

d_items

cubes:
  - name: d_items
    sql_table: public.items
    data_source: default

    joins: []

    dimensions:
      - name: item_id
        sql: item_id
        type: number
        primary_key: true

      - name: item_category
        sql: item_category
        type: string

    measures:
      - name: count
        type: count

最後にファクトテーブルに該当する f_orders を作成します。

f_orders.yml

cubes:
  - name: f_orders
    sql_table: public.orders
    data_source: default

    joins:
      - name: d_customers
        sql: "{CUBE}.customer_id = {d_customers}.customer_id"
        relationship: many_to_one

      - name: d_items
        sql: "{CUBE}.item_id = {d_items}.item_id"
        relationship: many_to_one

    dimensions:
      - name: order_id
        sql: order_id
        type: number
        primary_key: true

      - name: order_timestamp
        sql: order_timestamp
        type: time

    measures:
      - name: order_quantity
        sql: order_quantity
        type: sum

      - name: order_amount
        sql: order_amount
        type: sum

      - name: discount_amount
        sql: discount_amount
        type: sum

      - name: sales_amount
        sql: order_amount - discount_amount
        type: sum
        filters:
          - sql: "{CUBE}.is_cancel = 'N'"

      - name: sales_quantity
        sql: order_quantity
        type: sum
        filters:
          - sql: "{CUBE}.is_cancel = 'N'"

      - name: sales_unit_price
        sql: "{sales_amount} / {sales_quantity}"
        type: number

以下のビジネスロジックを含めます。

joins 配下で、他のキューブとの結合条件を記載します（左外部結合固定で、設定を書いたキューブの方が左になります）。
メジャー設定
- order_quantity（注文数）と order_amount（注文金額）は元の列の合計値として定義します。
- sales_quantity（売上数）に関しては is_cancel = 'N'（未キャンセル）のレコードのみを集計対象にします。
- sales_amount（売上金額）に関しては is_cancel = 'N' のレコードのみを集計対象とすることに加え、discount_amount（値引き金額）を引きます。
- sales_unit_price（売上単価）は先に定義した sales_amount を saes_quantity で割ったものとします（集計後の計算）。

以上で準備が整いました。

ここまで問題がなければ、psql で Cube Core に接続してテーブル一覧を確認できます。

[mabe@cube01 ~]$ PGPASSWORD=pass psql -h localhost -p 15432 -U user cube
psql (14.12、サーバー 14.2 (Cube SQL))
"help"でヘルプを表示します。

cube=> \d
              リレーション一覧
 スキーマ |    名前     |  タイプ  | 所有者 
----------+-------------+----------+--------
 public   | d_customers | テーブル | user
 public   | d_items     | テーブル | user
 public   | f_orders    | テーブル | user
(3 行)

cube=>

（Cube Core の SQL API のデフォルトユーザー名/パスワードは cube/pass です）

4. Cube Core に SQL クエリを実行する

psql で Cube Core に SQL 文を実行し、Snowflake にはどのようなクエリが飛んでいるか確認します。

4-1. １テーブルの集計

まずは単純なクエリから。顧客テーブルから顧客ランクごとの件数を取得するクエリです。

select
	c.customer_rank,
	c.count
from
	d_customers c

これは以下のような SQL 文に変換されて Snowflake に投げられます（整形はしています）。

SELECT
	"d_customers".customer_rank "d_customers__customer_rank",
	count("d_customers".customer_id) "d_customers__count"
FROM
	public.customers AS "d_customers"
GROUP BY 1
ORDER BY 2 DESC
LIMIT 50000

Cube Core への SQL 文では単純に count を参照しているだけですが、主キーを設定したカラムに対するカウントに変換されていますね。あと、GROUP BY 句なども自動で付与されます。

4-2. 結合＋集計

次に顧客テーブルと注文テーブルを結合して、顧客ランクごとに顧客数、注文数、注文金額を集計するクエリです。

select
	c.customer_rank,
	c.count,
	o.order_quantity,
	o.order_amount
from
	d_customers c,
	f_orders o
order by
	c.customer_rank

これは以下のような SQL 文に変換されて Snowflake に投げられます。

SELECT
	"d_customers".customer_rank "d_customers__customer_rank",
	count(distinct "d_customers".customer_id) "d_customers__count",
	sum("f_orders".order_quantity) "f_orders__order_quantity",
	sum("f_orders".order_amount) "f_orders__order_amount"
FROM
	public.orders AS "f_orders"
LEFT JOIN
	public.customers AS "d_customers"
		ON "f_orders".customer_id = "d_customers".customer_id
GROUP BY 1
ORDER BY 1 ASC
LIMIT 50000

Cube Core に投げた SQL 文には結合条件がありませんが、Snowflake に投げる際に必要な結合条件が付与されます。また、集計値のロジックも Cube Core 上で定義したものになっていますね。

１つ注目するのは c.count が 4-1. では count(...) に変換されていたところ、4-2. では count(distinct ...) に変換されているところです。基本は count distinct のようですが、distinct が不要なケースでは除外してくれるようですね。

4-3. 集計値の計算

次に集計値同士の計算（売上金額 / 売上数）を含む SQL を試します。

select
	i.item_category,
	o.sales_unit_price
from
	d_items i,
	f_orders o
order by
	i.item_category

これは以下のような SQL 文に変換されて Snowflake に投げられます。

SELECT
   "d_items".item_category "d_items__item_category",
   sum(CASE WHEN ("f_orders".is_cancel = 'N') THEN order_amount - discount_amount END)
   	/ sum(CASE WHEN ("f_orders".is_cancel = 'N') THEN "f_orders".order_quantity END) "f_orders__sales_unit_price"
FROM
   public.orders AS "f_orders"
LEFT JOIN
   public.items AS "d_items"
   	ON "f_orders".item_id = "d_items".item_id
GROUP BY 1
ORDER BY 1 ASC
LIMIT 50000

is_cancel = 'N' の条件や discount_amount の減算、集計値である売上金額と売上数の割り算のロジックも自動で組み込まれています。

4-4. ディメンジョンによる絞り込み。

もちろん WHERE 句によるディメンジョンの値による絞り込みにも対応しています。

select
	c.customer_rank,
	i.item_category,
	o.sales_amount
from
	d_customers c,
	f_orders o,
	d_items i
where
	o.order_timestamp < '2024-05-20'

これは以下のような SQL 文に変換されて Snowflake に投げられます。

SELECT
   "d_customers".customer_rank "d_customers__customer_rank",
   "d_items".item_category "d_items__item_category",
   sum(CASE WHEN ("f_orders".is_cancel = 'N') THEN order_amount - discount_amount END) "f_orders__sales_amount"
FROM
   public.orders AS "f_orders"
LEFT JOIN
   public.customers AS "d_customers"
   	ON "f_orders".customer_id = "d_customers".customer_id
LEFT JOIN
   public.items AS "d_items"
   	ON "f_orders".item_id = "d_items".item_id
WHERE
   ("f_orders".order_timestamp < ?::timestamp_tz)
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 50000

変換された SQL 文でも指定の条件は WHERE 句に現れます。パラメーター化されているのがちょっと意外でしたが（BI ツールなどは一般にパラメーターを用いないので）。

4-5. メジャーによる絞り込み

メジャーの値による絞り込みもできます。

select
	c.customer_rank,
	i.item_category,
	o.sales_amount
from
	d_customers c,
	f_orders o,
	d_items i
where
	o.sales_amount > 600

これは以下のような SQL 文に変換されて Snowflake に投げられます。

SELECT
	"d_customers".customer_rank "d_customers__customer_rank",
	"d_items".item_category "d_items__item_category",
	sum(CASE WHEN ("f_orders".is_cancel = 'N') THEN order_amount - discount_amount END) "f_orders__sales_amount"
FROM
	public.orders AS "f_orders"
LEFT JOIN
	public.customers AS "d_customers"
		ON "f_orders".customer_id = "d_customers".customer_id
LEFT JOIN
	public.items AS "d_items"
		ON "f_orders".item_id = "d_items".item_id
GROUP BY 1, 2
HAVING (sum(CASE WHEN ("f_orders".is_cancel = 'N') THEN order_amount - discount_amount END) > ?)
ORDER BY 3 DESC
LIMIT 50000

Cube Core に投げた SQL 文の中では WHERE 句の条件が、Snowflake には HAVING 句で投げられているのはちょっと面白いですね。

6. 感想

本記事では、Cube Core で結合条件や KPI 算出ロジックを定義し、Cube Core に発行した SQL 文がバックでどのようなロジックを組み込まれた SQL 文に変換されるかを見てきました。

集計ロジック（どの項目をどの集計関数で集計するか）
テーブル間の結合ロジック
フィルタリングロジック（集計対象に含める/除外する条件）
計算式

などのロジックを Cube Core で定義することで、その詳細を把握せずともクライアント側で KPI が集計できることが確認できたと思います。

今回は psql からの SQL 発行のみを確認しましたが、他のプログラム（スクラッチ、BI ツール）に跨ってロジックが共有できるというのが、セマンティックレイヤーの１つの強みだと思います。

ちなみに、ロジックの共通化というと RDBMS にはビューという仕組みがあります。ただし、ビューは SELECT 文で定義するという前提上、集計ロジック（集計関数）を組み込むためには集計の軸を固定化する必要があります（GROUP BY 句で明示する必要がある）。一方、セマンティックレイヤーは集計の軸を事前に固定する必要がなく、GROUP BY 句は SQL 文を変換する際に元の SQL 文に基づいて動的に決定できます。その点では集計ロジックの共通化はセマンティックレイヤーのビューに対しての強みだと思います（集計値に基づいた計算ロジックの共通化も含む）。

さて、ここまで Cube Core の SQL API を試してみましたが、Cube Core へ投げる SQL 文に違和感がある人も多いと思います。

結合条件がない
inner/outer join が固定されている
GROUP BY がない
WHERE 句と HAVING 句の区別がない

機能的には別に問題がないのですが、このような SQL に対応できるツールは少ないかと思います（動くには動くのでしょうけど）。

実際、BI ツールなどを使う場合、先に述べた Semantic Layer Sync（Cube Core では利用不可、Cube Cloud が必要）を利用することが推奨されます。

It is recommended to use Semantic Layer Sync to connect Cube to Tableau. It automatically synchronizes the data model between Cube and Tableau.

Tableau から SQL API を利用した Cube Core への接続もサポートはされていますが、also と付いていて次善策ですよという感じが強いです。

You can also use the SQL API to connect Cube to Tableau.

実際、BI ツールには集計ロジックを含めたロジックを共通化する仕組みがあり、Cube Core(Cloud) のロジックの定義が複数の BI ツールのロジックに自動的に連携されて初めてセマンティックレイヤーのメリットをフルに活かせるのではないかと思いました。

蛇足ですが、BI ツールにおけるロジックの共通化に比べ、せまんてぃくレイヤーによるロジック共通化の強みは、複数の BI ツールやワークブックでロジックを共通化できるという点に加え、ロジックをコードベースで管理できるというのも大きなメリットな気がします。Looker はそれが出来ますが（というか Looker 自身がセマンティックレイヤーになろうとしている）。

最後になりますが、セマンティックレイヤーに対する SQL 実行は技術的には面白いですが、セマンティックレイヤーは BI ツールや分析ツールなどと密に連携してこそではないかという感想で本記事を締めたいと思います。

（あと、ソフトウェア/サービスの名前はもう少しひねった名前を付けてほしいとは思う）