一个简单的营收问题,客户的销售库却答不了
先定粒度,再在 OLTP 系统旁建星型模型,看板两周就能上线,不必拖上两个月。

要点速览
- OLTP 为快速写入、减少冗余而做了范式化。分析人员需要多维模型,所以要单独建一套分析型模式。
- 先定业务过程和粒度,再选维度和事实。粒度定错,后面每张表都得重建。
- 星型模型更好查询。雪花模型把维度进一步范式化以节省存储,代价是查询更复杂。
驻场第一周,运营负责人问了一个听上去很简单的问题:每个品类、按省份、按月的营收是多少。打开数据库,你发现数据分散在六张表里。最快拿到数字的办法,是在正在接单的同一个库上跑一条很长的 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 这几张表。在原始模式上,运营负责人的问题写出来是这样:
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。
四步走完,再加一道检查:在新模型上重写客户的问题。
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 编辑器。先问他们:数据里的一行到底是什么。
这篇文章对你有帮助吗?
感谢你的反馈!
8 个信源
- 7 data modeling techniques and concepts for business · 2024-07-12
- What is Normalization in DBMS (SQL)? 1NF, 2NF, 3NF, BCNF Database with Example
- What is OLAP? - Online Analytical Processing Explained
- What is a Data Mart?
- Data Mart vs Data Warehouse: a Detailed Comparison · 2025-01-15
- What are columnar databases? Here are 35 examples. · 2023-11-03
- What is a columnar database? · 2024-04-15
- Data Modeling for Mere Mortals – Part 2: Dimensional Modeling Fundamentals · 2023-10-27