Semantic layer，商業世界與資料世界溝通的橋樑（二）

Published in

dbt-local-taipei

25 min readJan 30, 2024

大家一定覺得「Semantic layer，商業世界與資料世界溝通的橋樑」這句話似曾相似。在資料分析師、資料工程師這些職缺剛出來時，確實有這麼一句話：資料團隊是商業團隊以及工程團隊間的橋樑。

因此如果要了解 semantic layer 擁有哪些元素，不妨借鑒資料分析師從接收需求到撈取資料經過了哪些步驟。這些步驟便是 semantic layer 裡的各個元素，他們將模糊多元的商業用語轉換成一致的 SQL 邏輯，讓商業使用者能一步步朝著自助撈取資料的方向邁進。

Semantic layer 的前世今生

資料分析師的工作流程

資料分析師在接到 stakeholder 們的需求後，通常需要經過以下步驟：

跟 stakeholder 確認指標計算方法，以及需要的欄位、視覺化：
👩‍💼：我要一份 A 客戶上個月的業績報表。
🧑🏻‍💻：要怎麼樣的業績呢？營業收入嗎，還是銷貨數量？需要 by 銷售管道嗎，還是 by 商品呢？
👩‍💼：by 產品好了，還要各個產品的價格。
🧑🏻‍💻：是要定價呢，還是售後價格？
👩‍💼：那個月的售前平均定價，以及售後平均價格。
🧑🏻‍💻：那只需要一般的資料表就好嗎？還是需要樞紐表或其他視覺化？
👩‍💼：給我一般的表，畫圖我自己在 Excel 做就可以了，然後我只需要蔬菜類的價格。
將 stakeholder 的需求記錄，並思考如何轉換成 SQL 撈取資料。
到資料庫/資料倉儲的操作介面用 SQL 撈取資料：

/* 假設所有產品在上個月都至少有一筆訂單 */
-- 銷售業績
With sales as (
  select 
    product_name,
    product_id,
    --aggregateion function
    sum(sales_revenue) as total_sales, 
    sum(sales_quantity) as total_quantity,
    sum(sales_revenue)/sum(sales_quantity) as avg_sales_price
  FROM fct_orders
  --跟產品維度做關聯
  inner join dim_products using(product_id)
  --只篩選出需要的資料
  WHERE EXTRACT(MONTH FROM sales_date) = EXTRACT(MONTH FROM NOW()) - 1
  AND EXTRACT(YEAR FROM sales_date) = EXTRACT(YEAR FROM NOW() 
  AND product_category = 'vegetable'
  --定義顆粒度
  group by 1,2
), avg_regular_price
  select 
    product_name,
    product_id,
    --aggregation function
    avg(regular_price),0) as avg_regular_price
  FROM fct_daily_product_price
  --跟產品維度做關聯
  inner join dim_products using(product_id)
  --只篩選出需要的資料
  WHERE EXTRACT(MONTH FROM price_date) = EXTRACT(MONTH FROM NOW()) - 1
  AND EXTRACT(YEAR FROM price_date) = EXTRACT(YEAR FROM NOW())
  AND product_category = 'vegetable'
  --定義顆粒度
  group by 1,2
)

select * from sales
left join avg_regular_price using(product_name, product_id)

4. 撈完資料後將資料匯出到 stakeholder 們熟悉的工具，像是Excel，方便轉換成資訊使用。

從以上步驟不難看出，資料分析師在：

溝通時，需要理解業績報表的內容，確認指標定義（定價以及售價都會被籠統地稱為價格）資料範圍以及顆粒度。
溝通完，要把內容記在自己的腦袋或筆記中進行思考，避免撈取資料時撈錯或少撈。
撈取資料時，需要：跟維度表 (dimension table) 做關聯、篩選需要的資料、定義顆粒度、計算指標幾個步驟。而如果該需求需要用到兩個以上的事實表 (fact table)，類似的程式碼則需要重複兩到三次。
將資料傳輸到使用者指定的 BI 工具 / Excel ，再進行視覺化。

用工程完成頻繁重複的工作

我想多數的資料工作者們都很熟悉上述的工作流程，有時一天重複個兩三次都不在話下。 Semantic layer 便是一項工具，能讓商業使用者只需要在自己熟悉的介面操作，便能直接從資料庫中撈取需要的資料，減輕了資料工作者們重複溝通、寫 SQL 的頻率。因此對應到上述資料分析師工作的四個步驟，可以將 semantic layer拆解為：

A. Data catalogue （步驟 1）: 當實踐 semantic layer 後，不再有資料分析師居中釐清每個需求，data catalogue 必需能讓商業使用者找到需要的資料存放在哪裡，也能透過列舉的功能以及與時俱進的文件提醒使用者，semantic layer 中的詞彙，與他所想的語義可能不同，讓使用者能順利找到自己真正需要的指標。

B. API（步驟 2 及步驟 4 ）: 當使用者操作 BI 工具的圖形介面時，便會丟出 request 到 semantic query engine 生成對應的 SQL code，而這段 SQL code 會在資料庫中執行運算好後，將結果回傳到工具中。

C. Semantic query engine（步驟 3）: Semantic query engine 是一套軟體系統，能將使用者透過 BI 工具傳來的指令轉換成 SQL code，而這一套轉換需要將商業用語以及 SQL 語法之間的對應關係定義清楚。而該怎麼定義清楚，便是擁有好的 data model 以及 metrics layer¹。

Data Model 以及 Metrics Layer

Metrics layer 是眾多商業指標的表來源以及運算方式的定義集合，舉例來說：平均售價 (avg_sales_price)這個指標是對 fct_orders 這張 table 的 sales_revenue 以及 sales_quantity 這兩個欄位分別相加之後再相除。

Data model 則是在現實世界中發生的商業活動在資料庫中的投影。從敘事的角度來說其中每張表都是現實世界中的某個商業實體（人時地物，又稱 dimension table）或是某個商業流程/活動（事，像是消費者下單訂購、廣告受眾點擊廣告，以及業務訂定的業績目標等等，又稱 fact table）等等。另外 data model 也會去定義表與表之間的關係。像是：

表的定義：包括了每一筆資料所代表的意義，以及有哪些資料會被包含在這張表中（什麼是一，什麼是全部）。舉例來說，fct_orders 這張表的每一列是一筆訂單。那尚未收到貨款的訂單要放在裡面嗎？又或是已經退貨的訂單呢？如果 fct_orders 包含已經退貨的訂單，那上述平均售價的指標定義是不是也應該改寫了呢？
關係的定義：定義 dimension table 以及 fact table 之間的關係。舉例來說一位使用者可以下很多筆訂單 dim_users 就跟 fct_orders 有一對多的關聯 ( fct_orders.customer_id = dim_users.user_id )。不在 fact table 就先合併在一起的原因是，一名使用者可以參與不同的商業活動。如果預先將 table 們都合併在一起了，反而不好做不同商業活動的交叉分析，最後反而不好合併 (join），影響了 Semantic Layer 的彈性以及效能。因此不如將「關係」定義好，使用者查詢時，交給 semantic query engine 生成程式碼時再執行合併查詢就好。

資料分析說穿了就是透過資料盡可能地去優化各式各樣的商業活動。而 data model 是針對商業活動的結構性敘事，指標（metrics）則是針對各種商業活動進行的量化量測，因此 semantic layer（尤其是 data model 以及 metrics layers) 也可以說是將「資料分析」這一商業活動盡量一致化、規格化的一項工具。

實例呈現

像是 Airbnb 或是 91APP 都從無到有建構出了自己的 semantic layer，但儘管 semantic layer 被拆解成以上的幾個元素後，相信在實作上來說還是有點抽象。現在也有不少的 semantic layer 工具提供詳盡的 documentation 以及範例給大家使用（像是 Accio，Cube, Looker, Malloy, PowerBI (shared semantic models) 等等），這裡就以 Accio 為例讓大家看看程式碼經過 semantic query engine 的前後對照吧。

以 Accio 提供的教學素材為例，在使用者定義好 .mdl 檔後（也就是 data model 以及 metrics layer ）後，藉由指令查詢 Accio ，Accio 便會生成 SQL 進到 data warehouse 中撈取資料。

定義好的 .mdl 檔：在 Accio 中就是將上面的 schema 以文字形式定義下來，並賦予對應的 SQL 程式碼 ( @sql ) 以及文字敘事 ( @description )。

// 這是 mdl 檔
// Model Orders：每筆資料為一筆訂單
Model Orders @sql('select * from orders') {
  orderkey: INTEGER! @primaryKey @expr("o_orderkey")
  custkey: INTEGER! @expr("o_custkey")
  orderstatus: OrderStatus @expr("o_orderstatus")
  totalprice: REAL @expr("o_totalprice")
  orderdate: VARCHAR @expr("o_orderdate")
  orderpriority: VARCHAR @expr("o_orderpriority")
  clerk: VARCHAR @expr("o_clerk")
  shippriority: INTEGER @expr("o_shippriority")
  comment: VARCHAR @expr("o_comment")
  // 訂單與消費者有關聯，詳請請見 Relation OrderCustomer
  customer: Customer @relation(OrdersCustomer)
  // 訂單與訂單細項有關係，詳情請見 Relation OrdersLineitem
  lineitems: Lineitem[] @relation(OrdersLineitem)
}

// Model Line Item：每筆資料為該訂單細項
Model Lineitem @sql('select * from lineitem') {
  orderkey: INTEGER @expr("l_orderkey")
  linenumber: INTEGER @expr("l_linenumber")
  orderkey_linenumber: VARCHAR @expr("concat(l_orderkey, l_linenumber)") @primaryKey
  partkey: INTEGER @expr("l_partkey")
  suppkey: INTEGER @expr("l_suppkey")
  partkey_suppkey: VARCHAR @expr("concat(l_partkey, l_suppkey)")
  quantity: REAL @expr("l_quantity")
  extendedprice: REAL @expr("l_extendedprice")
  discount: REAL @expr("l_discount")
  tax: REAL @expr("l_tax")
  returnflag: VARCHAR @expr("l_returnflag")
  linestatus: VARCHAR @expr("l_linestatus")
  shipdate: VARCHAR @expr("l_shipdate")
  commitdate: VARCHAR @expr("l_commitdate")
  receiptdate: VARCHAR @expr("l_receiptdate")
  shipinstruct: VARCHAR @expr("l_shipinstruct")
  shipmode: VARCHAR @expr("l_shipmode")
  comment: VARCHAR @expr("l_comment")
  // 細項與訂單有關聯，詳情請見 Relation OrdersLineitem
  orders: Orders @relation(OrdersLineitem)
  // 細項與供應商組件有關聯，詳情請見 Relation PartSuppLineitem
  partsupp: PartSupp @relation(PartSuppLineitem)
}

// Model PartSupp：每筆資料為一供應商組件
Model PartSupp @sql('select * from partsupp') {
  partkey: INTEGER @expr("ps_partkey")
  suppkey: INTEGER @expr("ps_suppkey")
  partkey_suppkey: VARCHAR @expr("concat(ps_partkey, ps_suppkey)") @primaryKey
  availqty: INTEGER @expr("ps_availqty")
  supplycost: REAL @expr("ps_supplycost")
  comment: VARCHAR @expr("ps_comment")
  part: Part @relation(PartSuppPart)
  supplier: Supplier @relation(PartSuppSupplier)
}

// Model Part: 每筆資料為一組件
Model Part @sql('select * from part') {
  partkey: INTEGER! @primaryKey @expr("p_partkey")
  name: VARCHAR @expr("p_name")
  mfgr: VARCHAR @expr("p_mfgr")
  brand: VARCHAR @expr("p_brand")
  type: VARCHAR @expr("p_type")
  size: INTEGER @expr("p_size")
  container: VARCHAR @expr("p_container")
  retailprice: REAL @expr("p_retailprice")
  comment: VARCHAR @expr("p_comment")
  partsupps: PartSupp[] @relation(PartSuppPart)
}

// Model Supplier：每筆資料為一供應商
Model Supplier @sql('select * from supplier') {
  suppkey: INTEGER! @primaryKey @expr("s_suppkey")
  name: VARCHAR @expr("s_name")
  address: VARCHAR @expr("s_address")
  nationkey: INTEGER @expr("s_nationkey")
  phone: VARCHAR @expr("s_phone")
  acctbal: REAL @expr("s_acctbal")
  comment: VARCHAR @expr("s_comment")
  partsupps: PartSupp[] @relation(PartSuppSupplier)
  nation: Nation @relation(NationSupplier)
}

// Relation OrdersLineitem: 定義 Orders 與 Lineitem 的關係，一筆訂單可以有多個細項
Relation OrdersLineitem @condition(Orders.orderkey = Lineitem.orderkey) @desc("Orders of Lineitem") {
  models: [Orders, Lineitem]
  type: "ONE_TO_MANY"
}

//Relation PartSuppLineitem 某供應商組件可以出現在不同筆的訂單細項中
Relation PartSuppLineitem @condition(PartSupp.partkey_suppkey = Lineitem.partkey_suppkey) @desc("PartSupp of Customer") {
  models: [PartSupp, Lineitem]
  type: "ONE_TO_MANY"
}

//Relation PartSuppSupplier：一個供應商可以提供多種零組件
Relation PartSuppSupplier @condition(Supplier.suppkey = PartSupp.suppkey) @desc("PartSupp of Supplier") {
  models: [Supplier, PartSupp]
  type: "ONE_TO_MANY"
}

//Metrics Revenue: Revenue是 對 Order 這張 table 的 totalprice 這個 column 做加總。
Metric Revenue @model(Orders) @preAgg {
    total_revenue: INTEGER @measure(sum(totalprice))
    orderstatus: OrderStatus @dim
    custkey: INTEGER @dim
    orderdate_grain: date @time_grain(orderdate, [DAY, MONTH])
}

2. 使用者想知道「每個供應商各提供了多少的訂單細項」時，便可以透過 (BI工具傳出 API Request 產生）以下 SQL 向 Accio 查詢：

SELECT
  partsupp.supplier.name,
  count(*)
FROM Lineitem
WHERE orders.orderdate BETWEEN DATE '1996/01/01' AND DATE '1996/12/31'
GROUP BY 1
ORDER BY 2 desc
LIMIT 10

Accio 則會透過自己的 Query Engine 以及上面定義好的 mdl檔，將其轉換成以下程式碼向 data warehouse 做查詢：

WITH
  Lineitem AS (
    SELECT
      l_orderkey "orderkey"
    , l_linenumber "linenumber"
    , concat(l_orderkey, l_linenumber) "orderkey_linenumber"
    , l_partkey "partkey"
    , l_suppkey "suppkey"
    , concat(l_partkey, l_suppkey) "partkey_suppkey"
    , l_quantity "quantity"
    , l_extendedprice "extendedprice"
    , l_discount "discount"
    , l_tax "tax"
    , l_returnflag "returnflag"
    , l_linestatus "linestatus"
    , l_shipdate "shipdate"
    , l_commitdate "commitdate"
    , l_receiptdate "receiptdate"
    , l_shipinstruct "shipinstruct"
    , l_shipmode "shipmode"
    , l_comment "comment"
    , 'relationship<OrdersLineitem>' "orders"
    , 'relationship<PartSuppLineitem>' "partsupp"
    FROM
      (
      SELECT *
      FROM
        lineitem
    )  t
) 
, Orders AS (
    SELECT
      o_orderkey "orderkey"
    , o_custkey "custkey"
    , o_orderstatus "orderstatus"
    , o_totalprice "totalprice"
    , o_orderdate "orderdate"
    , o_orderpriority "orderpriority"
    , o_clerk "clerk"
    , o_shippriority "shippriority"
    , o_comment "comment"
    , 'relationship<OrdersCustomer>' "customer"
    , 'relationship<OrdersLineitem>' "lineitems"
    FROM
      (
      SELECT *
      FROM
        orders
    )  t
) 
, PartSupp AS (
    SELECT
      ps_partkey "partkey"
    , ps_suppkey "suppkey"
    , concat(ps_partkey, ps_suppkey) "partkey_suppkey"
    , ps_availqty "availqty"
    , ps_supplycost "supplycost"
    , ps_comment "comment"
    , 'relationship<PartSuppPart>' "part"
    , 'relationship<PartSuppSupplier>' "supplier"
    FROM
      (
      SELECT *
      FROM
        partsupp
    )  t
) 
, Supplier AS (
    SELECT
      s_suppkey "suppkey"
    , s_name "name"
    , s_address "address"
    , s_nationkey "nationkey"
    , s_phone "phone"
    , s_acctbal "acctbal"
    , s_comment "comment"
    , 'relationship<PartSuppSupplier>' "partsupps"
    , 'relationship<NationSupplier>' "nation"
    FROM
      (
      SELECT *
      FROM
        supplier
    )  t
) 
, rs_1usi104oox (partkey_suppkey, partkey, suppkey, availqty, supplycost, comment, part, supplier, bk) AS (
    SELECT DISTINCT
      t.partkey_suppkey
    , t.partkey
    , t.suppkey
    , t.availqty
    , t.supplycost
    , t.comment
    , t.part
    , t.supplier
    , s.orderkey_linenumber bk
    FROM
      (Lineitem s
    LEFT JOIN PartSupp t ON (s.partkey_suppkey = t.partkey_suppkey))
) 
, rs_1n0rqppeyi (suppkey, name, address, nationkey, phone, acctbal, comment, partsupps, nation, bk) AS (
    SELECT DISTINCT
      t.suppkey
    , t.name
    , t.address
    , t.nationkey
    , t.phone
    , t.acctbal
    , t.comment
    , t.partsupps
    , t.nation
    , s.bk bk
    FROM
      (rs_1usi104oox s
    LEFT JOIN Supplier t ON (s.suppkey = t.suppkey))
) 
, rs_1en9jip1hj (orderkey, custkey, orderstatus, totalprice, orderdate, orderpriority, clerk, shippriority, comment, customer, lineitems, bk) AS (
    SELECT DISTINCT
      t.orderkey
    , t.custkey
    , t.orderstatus
    , t.totalprice
    , t.orderdate
    , t.orderpriority
    , t.clerk
    , t.shippriority
    , t.comment
    , t.customer
    , t.lineitems
    , s.orderkey_linenumber bk
    FROM
      (Lineitem s
    LEFT JOIN Orders t ON (s.orderkey = t.orderkey))
) 
SELECT
  rs_1n0rqppeyi.name
, count(*) count
FROM
  ((Lineitem
LEFT JOIN rs_1n0rqppeyi ON (Lineitem.orderkey_linenumber = rs_1n0rqppeyi.bk))
LEFT JOIN rs_1en9jip1hj ON (Lineitem.orderkey_linenumber = rs_1en9jip1hj.bk))
WHERE (rs_1en9jip1hj.orderdate BETWEEN DATE '1996/01/01' AND DATE '1996/12/31')
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10

由於 Accio 尚未提供跟 BI 工具串連的服務，因此還沒辦法完全達到讓商業使用者自助撈取資料，但從讓 data analyst 寫 SQL 的角度來說，也已經減少許多寫代碼的時間，以及SQL 過於複雜導致資料出錯的可能性了。

小結

採用 semantic layer 後，data analyst 的工作量好像就變少了，商業端的工作者也能透過自助服務的方式撈取自己想要的資料了，那資料分析師會失業嗎？

我覺得不妨從兩個角度來思考：

資料分析師的職責：不知道大家從上述的例子中有沒有發現一件奇怪的現象，也就是資料分析師在溝通的過程，並不需要分析任何事情，就能完成工作。他不知道要看這些指標的目的是什麼，也不知道這些指標要用來回答哪些問題，更遑論這些指標對於要回答的那些問題來說是不是合理的。資料分析師的職責究竟是撈取資料呢？還是透過自己的思考能力給出分析上的建議以及判斷呢？這是個值得深思的問題。
組織文化以及資料素養：當要推行一項工具，且有可能改變使用者習慣時，如何讓使用者願意改變是最困難的。當 semantic layer 想要作為所有商業端使用者的唯一資料來源時，勢必會有相當大的阻力。另外，即使商業使用者透過 semantic layer 得到自己想要的資料了，我們還是得問問所有人的資料素養都足夠了嗎？所有人都可以利用工具撈出正確的資料嗎？所有人都有辦法正確解讀撈出來的資料嗎？所有人都有辦法問出正確的商業問題，並正確地決定相對應的指標來解答自己的疑惑嗎？大選剛過，「藍白合對民調指標的各自解讀以及統計知識的缺乏」對大家來說更是歷歷在目吧。

Semantic layer 是商業世界與資料世界溝通的橋樑。因此就像要解決交通問題需要從「工程、教育、執法」三個角度切入一樣。Semantic layer 只能從工程面解決一部份的資料問題，如何教育使用者使用這項工具並具備足夠的資料素養進行資料的解讀，以及該如何透過制度的建立，讓 semantic layer 在組織內落地，都是不可或缺的一環。

寫著寫著，跟 semantic layer 相關內容的規劃也來到足足三篇之多²。接下來的最後一篇系列文，我會從資料分析師的角度講一下我認為實作 semantic layer 後，能解決哪些問題，以及可能會產生哪些問題。

這一系列的文章牽涉到不少對於語義學的理解以及不熟悉的軟體詞彙，常常自覺寫的不夠精確，流量以及閱畢率目前也不太好，但隨著下一篇系列文的完成，也算是我自己對於理解 ETL 知識告一段落的小小里程碑，真是令人期待呢！（希望各位讀者也一樣期待！）

[1] Metrics layer: 據筆者了解，Airbnb 可以說是這波 metrics layer 的先驅，他們為了解決內部的資料問題，打造了 Minerva 這套工具。雖然是以 metrics layer 作為稱呼，但其實已經具備 semantic layer 的功能。 Semantic layer 則是之後才出現的比較潮的詞彙。多數文章也都將兩者交替使用。但筆者認為不論是 semantic layer 或是 Airbnb 的 metrics layer 其範疇都已遠超定義指標的唯一性這麼簡單，其目的更接近於將所有商業敘事從商業語言順利地轉換為資料語言（SQL），因此筆者傾向於使用 semantic layer 這個詞彙。

[2] 第一篇的內容，主要在釐清 semantic 以及 semantic layer 的定義，有興趣請點這裡。

Semantic layer，商業世界與資料世界溝通的橋樑 （二）