# 一个简单的营收问题，客户的销售库却答不了

> 先定粒度，再在 OLTP 系统旁建星型模型，看板两周就能上线，不必拖上两个月。

原文: https://fdetimes.net/zh/guides/star-schema-grain-beside-oltp-database/

驻场第一周，运营负责人问了一个听上去很简单的问题：每个品类、按省份、按月的营收是多少。打开数据库，你发现数据分散在六张表里。最快拿到数字的办法，是在正在接单的同一个库上跑一条很长的 SQL。

客户手里有数据，只是这些数据为*写入*交易而组织，不是为*读出*来做分析而组织。能不能看懂现有模式，并在旁边设计一套分析模型，决定了你是两周交付看板，还是卡上两个月。

## 销售库从来不是为回答老板的问题而建的

交易型（OLTP）系统围绕范式化设计。范式化的目的是消除重复数据、合理存储。在第三范式（3NF）中，每个非主键属性只依赖主键。所以省份名放在省份表里，品类名放在品类表里，订单只存 ID。

这种设计非常适合写入。AWS 的描述是：OLTP 擅长处理大量交易，但不擅长复杂查询。分析人员需要 OLAP 系统来分析多维数据。正因如此，“按省份、按品类、按月”这种三个维度的问题，会逼着你沿一整串范式化的表做 JOIN。

差异一直延伸到数据在磁盘上的存放方式。列式数据库只读取查询需要的列，从而减少分析查询的 I/O 和耗时。设想一张有 20 列的事实表，报表只需要其中 4 列：列式引擎只碰这 4 列。

代价是，列式数据库写入慢，对 ACID 的支持也不如行式数据库。它适合写一次、读多次的数据，替代不了负责接单的 OLTP 系统。

## 事实是数字，维度是上下文

维度模型把数据分成两类。事实是业务活动的度量，通常是数值，事实表经过范式化，冗余很少。维度是描述性上下文：哪个商品、哪家门店、哪一天。

两类表长得很不一样。事实表又窄又长，每行一个事件。维度表经过反范式化，主要是文本和描述性属性。一张事实表居中、周围环绕多个维度，就是 AWS 定义的星型模型。这也是维度模型常常直接被叫作星型模型的原因。

实际项目里很少只有一颗星。完整的模型通常有多张事实表共享维度，称为一致性维度。比如销售事实和库存事实可以共用同一张日期表和同一张商品表。

## 实例：连锁药店与 Kimball 四步法

设想客户经营一家连锁药店。OLTP 库里有 `orders`、`order_items`、`products`、`categories`、`stores` 和 `provinces` 这几张表。在原始模式上，运营负责人的问题写出来是这样：

```sql
SELECT pv.name AS tinh, c.name AS nhom_hang,
       date_trunc('month', o.created_at) AS thang,
       SUM(oi.quantity * oi.unit_price) AS doanh_thu
FROM order_items oi
JOIN orders o      ON o.id = oi.order_id
JOIN stores s      ON s.id = o.store_id
JOIN provinces pv  ON pv.id = s.province_id
JOIN products p    ON p.id = oi.product_id
JOIN categories c  ON c.id = p.category_id
GROUP BY 1, 2, 3;
```

（别名是越南语：*tinh* 指省份，*nhom_hang* 指品类，*thang* 指月份，*doanh_thu* 指营收。）

一个问题要五个 JOIN，还跑在正在接单的库上。Kimball 四步法能帮你走出这个局面：选业务过程，声明粒度，选维度，再选事实。

这里的**业务过程**是柜台销售。**粒度**是该过程记录的最细层级。用一句话写下来：“每行是一张小票上的一个明细项”。

为什么不把整张小票作为粒度？看一笔假设的订单：两盒止痛药，每盒 30,000 越南盾；一瓶维生素，120,000 越南盾；一盒口罩，40,000 越南盾。

按小票粒度，只有一行 220,000 越南盾，没法拆出属于止痛药品类的 60,000 越南盾。按明细粒度，有三行，按品类的任何问题都能回答。

**维度**来自问题里的每一个“按”：`dim_date`、`dim_store`（含省份名）和 `dim_product`（含品类、有效成分和生产厂家），有会员卡的话再加 `dim_customer`。**事实**是这一粒度上的数字：`quantity`、`revenue`、`discount`。

四步走完，再加一道检查：在新模型上重写客户的问题。

```sql
SELECT st.province, pr.category, d.year_month,
       SUM(f.revenue) AS doanh_thu
FROM fact_sales_line f
JOIN dim_store   st ON st.store_key   = f.store_key
JOIN dim_product pr ON pr.product_key = f.product_key
JOIN dim_date    d  ON d.date_key     = f.date_key
GROUP BY 1, 2, 3;
```

现在每个分析维度只要一个 JOIN，SQL 读起来几乎就是老板的原话。这才是星型模型真正的价值：客户的分析人员不用你坐在旁边，也能自己写查询。

**要点:** 画任何一张表之前，先问清楚每行数据代表什么。

## 星型还是雪花：什么时候该把维度再拆开？

IBM 把雪花模型描述为星型模型的逻辑延伸，维度被进一步拆成子表。放到药店的例子里，`dim_product` 会指向 `dim_category`，`dim_store` 会指向 `dim_province`。

| | 星型 | 雪花 |
|---|---|---|
| 维度 | 反范式化，一张扁平表 | 进一步范式化为多张表 |
| 存储 | 重复存放品类名等文本 | 更节省 |
| 查询 | JOIN 更少，分析人员容易写 | JOIN 更多，更复杂 |
| 适用 | 终端用户自助查询、BI 工具 | 超大维度、独立变化的上层属性 |

实用建议是：默认用星型。品类名在 `dim_product` 里重复几千次，很少构成真正的存储问题；而每多一个 JOIN，分析人员就多一处可能写错查询的地方。

## 会逼你推倒重来的错误

代价最高的错误是混用粒度。把按小票收取的配送费放进明细级事实表，每次 SUM 都会按行数把这笔费用重复累加。度量所在的层级不同，就该放进另一张事实表。

第二个错误是处处反范式化。反范式化通过增加冗余数据来加快查询，所以只应用在服务 BI 的派生模型里，不要用在核心数仓。核心层保持干净，客户改需求时，可以重建上层而不动地基。

第三个错误是过早把模型锁死。AWS 指出 OLAP 立方体很僵硬：一旦建模，维度和底层数据就无法更改。如果客户还在摸索自己的问题，先在普通表上建星型模型，再考虑立方体。

最后一个错误是没有讲清项目范围。数据集市是数仓的一个子集，服务于某个业务领域或部门；数仓则覆盖整个组织。

从连锁药店运营部门起步的项目就是数据集市。向客户把这一点讲明白，可以避免对方以为你在为全公司建数据仓库。

## 在简历和面试中展示这项能力

读前线部署工程师（FDE）的岗位描述时，看到“analytics”“data warehouse”“BI”“dbt”这类字眼，就该在投递前把数据建模好好复习一遍。

简历上不要只写“设计了数据仓库”。要这样写：选了什么粒度，有多少事实表和共享维度，哪个业务问题过去在生产库上要五个 JOIN，现在分析人员能自己回答。这样写，读的人能看出你是从业务问题出发推到模型，而不只是罗列工具。

准备面试时，给自己出两道题，并出声作答。第一题：给定 `orders`、`order_items`、`products` 和 `stores` 几张表，用一句话声明销售事实的粒度，并解释为什么不选小票粒度。

第二题：客户想加一项按小票收取的配送费。放在哪里，SUM 才不会把它成倍放大？

下次有人要“按 X、按 Y 的营收”，别急着打开 SQL 编辑器。先问他们：数据里的一行到底是什么。

**本周动手试试:**

- 找一个含 orders 和 order_items 表的示例数据库，用一句话写出销售事实的粒度，再画一个含 1 张事实表、3-4 个维度的星型模型。
- 把同一个报表问题分别在原始模式和星型模型上写成 SQL，数一数两边各有几个 JOIN。
- 加一张共享 dim_date 和 dim_product 的第二事实表（比如库存），检查两张事实表能否相互对比。

## 信源

- [7 data modeling techniques and concepts for business](https://www.techtarget.com/searchdatamanagement/tip/7-data-modeling-techniques-and-concepts-for-business)

- [What is Normalization in DBMS (SQL)? 1NF, 2NF, 3NF, BCNF Database with Example](https://www.guru99.com/database-normalization.html)

- [What is OLAP? - Online Analytical Processing Explained](https://aws.amazon.com/what-is/olap/)

- [What is a Data Mart?](https://www.ibm.com/think/topics/data-mart)

- [Data Mart vs Data Warehouse: a Detailed Comparison](https://www.datacamp.com/blog/data-mart-vs-data-warehouse)

- [What are columnar databases? Here are 35 examples.](https://www.tinybird.co/blog-posts/what-is-a-columnar-database)

- [What is a columnar database?](https://www.techtarget.com/searchdatamanagement/definition/columnar-database)

- [Data Modeling for Mere Mortals – Part 2: Dimensional Modeling Fundamentals](https://data-mozart.com/data-modeling-for-mere-mortals-part-2-dimensional-modeling-fundamentals/)
