# 干净的一张宽表，为什么还要拆成事实表和维度表

搞清宽表拆表的理由、一对多关系怎么连，以及筛选方向为什么会让图表的数字算错

> 数据建模 · Power BI 入门 · 报表自动化 · 约 8 分钟 · 10 月 03 日

## 本篇要点

1. 拆表的目的是让“每个对象只有一行”和“每次业务事件只有一行”同时成立：维度的行用于筛选和分组，事实的行用于汇总相加。
2. 一张宽表要付出三笔代价：描述性文本重复几十万遍、同一个对象被写成多条记录、没法和其他粒度的表放在同一根轴上比较。
3. 事实表和维度表不是表格属性，而是关系决定的：一对多关系里“一”端是维度，“多”端是事实。
4. 粒度指事实表里一行代表什么，必须整表一致；混了汇总行和小计行，金额相加就会错。
5. 拆分做法是复制宽表只留标识列和描述列并删除重复项得到维度，再从宽表删掉描述列得到只含键和数字的事实表；去重前必须先清除和修整，否则尾部多一个空格的值会被当成另一个客户。
6. “一”端的键必须唯一，否则关系会变成多对多，同一个对象可能被数到多次，筛选也会沿着不止一条路径传播；两张事实表也不要直接相连，而应共享同一套维度。
7. 筛选靠关系传播，默认只从“一”端流向“多”端；从“多”端反向限制维度表不会自动发生，这正是 `COUNTROWS(客户)` 在按颜色分组时每行都返回总数 2 的原因，而总计看起来又是对的。
8. 反向需求的首选解法是把计数对象放到事实表上，例如用 `DISTINCTCOUNT(销售[CustomerCode])` 数买过该颜色的客户；双向关系或 CROSSFILTER 只在必要时用，代价是性能和多路径带来的不明确筛选。
9. 关系不强制数据完整性，键对不上的事实行会在筛选传播中被排除，表现为 Power BI 合计小于 Excel 加总或维度侧出现空白。

---

上一篇结束时，你手里有了一张干净的表：第一行是列名，每一行是一条订单明细，每列一种类型，合并单元格也补齐了。这张表丢进 Excel 透视表立刻能出结果。那为什么还要再拆一遍？

## 宽表已经在啃你的三件事

**第一，重复。** 假设一个月的销售明细有 10 万行，每一行都写着“江苏省 / 南京市 / 张三 / 华东大区”。这些字在 10 万行里重复了 10 万次。Power BI 把数据压进内存时是按列压的，重复的文本列会把文件撑大、刷新变慢，而明细表偏偏是你查得最多的那张。

**第二，同一个对象被写成了好几个。** 同一个客户，有一行写成“张三”，另一行尾部多了一个空格。在宽表里这就是两个客户，按客户数统计时凭空多算一个。描述信息只存一处（存在维度表里），这种错误也只有一处要修。

**第三，扩展不动。** 下周老板要多看一张“月度目标”，目标表是按“产品类目 + 月份”定的，行数和你的明细完全对不上。宽表里没有一份“产品类目清单”，你没法把目标和明细放到同一根轴上去比。

所以拆表的目的可以一句话说完：**让“每个对象只有一行”和“每次业务事件只有一行”这两件事同时成立。** 前一种表给筛选和分组用，后一种表给汇总用。

## 事实表、维度表分别长什么样

**维度表**：一行代表一个对象——一个客户、一个产品、一天。列是描述这些对象的属性：客户名、城市、大区、产品类目、颜色。行数不多（几千到几万），但必须有一列能唯一认出这个对象，这一列叫**键**。

**事实表**：一行代表一次已经发生的业务事件——一笔销售明细、一条库存快照。列只有两类：指向维度表的键，和可以相加的数字（数量、金额、成本）。行数可以很大，并且随时间一直涨。

有一点容易误会，要专门说清：**事实表和维度表不是你在哪里设置出来的属性，而是关系决定的。** 你建了一条一对多的关系，站在“一”端的表就是维度表，站在“多”端的表就是事实表。判断一张表是什么，看它在关系里站哪一端。

比“事实”“维度”这两个词更该记住的是**粒度**：事实表里一行到底代表什么。是“一张订单”，还是“订单里的一行商品”？必须整表一致。如果一段是订单行、一段又是汇总起来的小计行，金额一加就错——这正是上一篇坚持要删掉小计行的原因。

## 从宽表拆出两张表

在 Power Query 里从那张干净宽表复制出几个查询：

- **客户维度**：只保留客户编号、客户名、城市、大区这些列，然后“删除重复项”，得到每个客户一行。
- **产品维度**：同理，保留产品编号、名称、类目、颜色，去重。
- **事实表**：在原宽表里删掉所有描述性的列（客户名、城市、产品类目），只留下键和数字。

两个细节值得单独提。

**键尽量用编号，不要用名字。** 名字会变（改名、大小写、多一个空格），一改关系就断；名字还可能重复。让源系统给出客户编号、产品 SKU 最省事；实在没有，可以在 Power Query 里用“添加索引列”给自己造一列编号当键。

**去重之前先做“清除（Clean）”和“修整（Trim）”。** 上一篇讲过它们怎么清掉不可见字符和首尾空格。这里必须再做一次的理由很实际：尾部多一个空格的“张三”和正常的“张三”在“删除重复项”眼里是两个不同的值，会给你造出一条假的重复记录，之后它会被一直当成一个真实客户统计进去。

## 一对多怎么连

在“模型”视图里，把维度表里的键拖到事实表里对应的字段上。Power BI 会把这个关系标成“一 → 多”：**1** 那一端是维度，**\*** 那一端是事实。

```mermaid
flowchart LR
  C["维度：客户（一行一个客户）"] -->|"1 → 多"| S["事实：销售明细（一行一笔）"]
  P["维度：产品（一行一个产品）"] -->|"1 → 多"| S
```

连线前，有三件事必须检查。

**一端的键必须唯一。** 一条关系能不能保证“一行一个对象”，取决于“一”端那一列是不是真的没有重复值。有重复值时，Power BI 会把这个关系建成多对多，并给你提示。多对多是能建的，但它的含义是“放弃了一行一个对象这个保证”：同一个对象可能被数到多次，筛选也会沿着不止一条合理路径传播，结果变得难以解释。所以对新手，看到多对多，第一反应应该是回去查键，而不是接受它。

**不要把两张事实表直接连起来。** 比如订单表和发货表，按订单号相连，看起来“逻辑正确”，但订单号在两张表里都重复，只能建成多对多，筛选传播会变得复杂，汇总也容易重复计算。真要比较两张事实（实际 vs 预算），正确做法是让它们共享同一套维度表，而不是互连。

**自动检测出来的关系要亲自看一遍。** Power BI 会凭列名和样本值自动猜，猜出来的方向和基数都可能是错的。初学时宁可手动拖，一根一根确认。

## 筛选方向：图表的数字是哪来的

现在讲本篇的重点：在一张拆好的模型里，你从没写过“筛选”，数字为什么是对的？

因为**关系不只是一条连线，它是筛选传播的通道**。你在视觉对象里放一个字段，等于给那张表加了一个筛选；这个筛选会沿着关系走，走到它该去的地方。默认只走一个方向：**从“一”端流向“多”端，也就是从维度流向事实。**

用三张小表验证一下：

- 客户（维度）：CUST-01 张三（美国），CUST-02 李四（澳大利亚）
- 产品（维度）：CL-01 T恤（绿色），CL-02 牛仔裤（蓝色），AC-01 帽子（蓝色）
- 销售（事实）：1月1日 CUST-01 买 CL-01，数量 10；2月2日 CUST-01 买 CL-02，数量 20；3月3日 CUST-02 买 CL-01，数量 30

**第一个图**：行是产品[颜色]，值是 `SUM(销售[数量])`。绿色 40，蓝色 20，对的。你什么都没写，是因为颜色这个筛选沿着“产品 → 销售”传了下去，把不属于该颜色的销售行剔除了。

**第二个图**：行还是产品[颜色]，值换成一个“客户数”，写成 `COUNTROWS(客户)`。结果是绿色 2、蓝色 2。

蓝色只有张三买过，应该是 1——这个数字错了。实际发生的是：颜色筛选传到销售表就停了，销售表被筛过，客户表没有。`COUNTROWS(客户)` 在“客户表没被任何筛选限制”的上下文里执行，于是每一行都返回全部客户数 2。

更麻烦的是总计。整张表的总计也是 2，看起来“没错”，所以这个错误不去和明细核对是发现不了的。

还有一条边界要说清：这**不是**说不能按客户分组。直接把客户表的字段拖到行标签上完全没问题——那是从维度流向事实，正是默认方向。出问题的只是“用另一张表的相关字段反过来限制这张表”。

这种反向需求有两种解法。

**一是换度量值的写法：在事实表里数键。** 把“客户数”改成 `DISTINCTCOUNT(销售[CustomerCode])`。绿色的两笔销售分别属于 CUST-01 和 CUST-02，得到 2；蓝色的那笔属于 CUST-01，得到 1。数字对了。逻辑是：不要从维度表数行数，而是在已经被筛选的事实表里数不同的客户编号。这是 Power BI 里最常用的一条原则——**要计数的东西，尽量落在已经被筛选的那张表上。**

**二是把关系改成双向（Both）。** 这样筛选能从销售回流到客户。代价有三条：每个视觉对象的查询都要多走一段传播，关系一多会明显变慢；当模型里存在两条以上通往同一张表的路径（最典型的就是两张事实表共享同一套维度），会出现“筛选路径不明确”，Power BI 可能直接不让你这么设，也可能按内部规则替你挑一条走——挑哪条不由你决定，数字因此很难解释；双向还可能改变切片器的行为，让“切过去发现没数据”这类体验问题冒出来。

所以官方建议是：默认保持单向，只在确实需要时才开双向；如果只有某一个度量值需要反向筛选，就用 DAX 的 `CROSSFILTER` 在那一个度量值内部临时打开，把影响锁在那一个数字上，不要动整个模型。

## 数字对不上时，先查键

最后补一个会让人怀疑人生的现象：Power BI 里的合计，比你在 Excel 里把同一张表加出来少。

最常见的原因是**键对不上**。关系不强制数据完整性，它不会阻止你导入一条客户编号在客户表里根本不存在的销售行。而从维度往事实传播筛选时，这类行匹配不上，会被当作不存在排除掉。你通常看到的症状是两种之一：明细和合计加不起来，或者维度那一侧冒出一行空白。查法很简单：看事实表里有没有空的键，或者把事实表的客户编号和客户表的客户编号做一次左反连接，找出哪些键找不到对象。

下一篇会用同样这套关系，把“日期”做成一张正经的维度表——它和事实表之间也只是一条一对多的线，只是多了几条自己的规矩。

## 术语表

- 维度表：一行代表一个对象（客户、产品、日期），列是这个对象的属性，用来筛选和分组。
- 事实表：一行代表一次业务事件，列只有指向维度的键和可相加的数字，用来汇总。
- 粒度：事实表里一行到底代表什么，必须整表一致，并且能和维度的粒度对应上。
- 键：维度表里能唯一认出一个对象的那一列，一对多关系靠它连接；用编号比用名字稳。
- 一对多关系：维度在“一”端、事实在“多”端的连接方式；表的身份由它决定，而不是表格自带的属性。
- 筛选传播：视觉对象上的筛选沿关系自动流向另一张表，不需要你写公式；默认只从“一”端流向“多”端。
- 交叉筛选方向（单向／双向）：关系属性，决定筛选能不能反向回流；双向会带来性能代价和“筛选路径不明确”。
- 孤儿键：事实表里存在但维度表里找不到对应对象的键值，会让相关行在筛选传播中被排除，合计因此变小。

## 来源

1. [Microsoft Learn: Understand star schema and the importance for Power BI — 维度表与事实表的定义、由关系基数决定表身份、事实表粒度须一致](https://learn.microsoft.com/en-us/power-bi/guidance/star-schema)
2. [Microsoft Learn: Model relationships in Power BI Desktop — 筛选传播机制、交叉筛选方向选项、数据完整性不匹配时排除行](https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand)
3. [Microsoft Learn: Bi-directional relationship guidance — 双向关系的适用场景与性能代价，以及用 CROSSFILTER 只在度量值内部打开反向筛选](https://learn.microsoft.com/en-us/power-bi/guidance/relationships-bidirectional-filtering)
4. [Microsoft Learn: Many-to-many relationship guidance — 一端键不唯一时的多对多行为，以及不建议直接关联两张事实表](https://learn.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many)
5. [Microsoft Learn: CROSSFILTER function (DAX) — CROSSFILTER 的用法与参数含义](https://learn.microsoft.com/en-us/dax/crossfilter-function-dax)

---

原文：https://pangzhengboyin.com/articles/star-schema-fact-dimension-relationships-ef35194f

> **庞征博引** · 想学的，慢慢都会
>
> 庞征博引是把想学的东西写成连载的 AI 学习工具。说出想学什么，它会先了解你的基础，再把主题写成一篇篇 5–10 分钟能读完的文章；边读边问，接下来学什么跟着你走。这篇就是这样写出来的。
>
> 开始你自己的连载 → https://pangzhengboyin.com