从数据到信息
从信息到洞察

主键、外键和索引

什么是主键

Power BI 使用的表格模型是一种基于关系型数据库的模型,表和表之间通过关系链接,因此表格模型需要遵循关系型数据库的一些基本约束,其中主键约束是最重要的一种约束。它基于这样一种考虑:数据表中任意两条记录不能完全相同,存在某个字段能唯一区分出不同的记录,这个字段被称为主键

主键是每条记录的唯一标识,只要找到记录的标识就能调出关于这条记录的所有信息。

Product 表的主键 ProductKey

主键保证了数据的唯一性,以 Power BI 最常见的一对多关系为例,关系从一端指向多端,其中一端的表用来创建关系的列就具备主键的作用。

产品表的 ProductKey 列就是它的主键

位于关系一端的表(维度表)必须至少有一列可以作为表的唯一标识,否则一对多关系就无法建立。想象一下,如果维度表的 A,B 两个产品 ProductKey 都是 001,假设一对多关系仍然成立,模型在计算的时候就无法确定 Sales 表中所有 001 产品的销售记录到底对应的是 A 产品还是 B 产品,这在一对多关系中是不被允许的。

Power BI 允许创建多对多关系,在这种特殊情况下,用于创建关系的两列可以有重复值,关于多对多关系我们将在后面的文章中详细介绍

主键的选择

数据库对主键的要求中最关键的一点是:记录一旦插入到表中,主键最好不要再修改,因为主键是用来唯一定位记录的,修改了主键,会造成一系列的影响。

由于主键的作用十分重要,如何选取主键会对模型结构产生重要影响。如果我们以用户的身份证号作为主键,似乎能唯一定位记录。然而,身份证号也是一种业务场景,如果身份证号升位了,或者需要变更,作为主键,不得不修改的时候,就会对业务产生严重影响。从存储成本的角度考虑,身份证可以作为 13 亿人的唯一身份标识,如果一家公司用它来做主键,未免有点浪费存储空间。

所以,选取主键的一个基本原则是:不使用任何业务相关的字段作为主键。因此,身份证号、手机号、邮箱地址这些看上去可以唯一的字段,严格来讲均不可用作主键。可用做主键的类型有:

  • 自增整数类型:为表的每一条记录分配一个自增整数,这样我们就完全不用担心主键重复,也不用自己预先生成主键;
  • 全局唯一 GUID 类型:使用一种全局唯一的字符串作为主键,类似 8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID 算法通过网卡 MAC 地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的,大部分编程语言都内置了 GUID 算法,可以自己预算出主键。
以上给出的是主键的严谨用法,在大型的、需要长期维护的 Power BI 模型中建议使用严谨的方式定义主键,以避免后期出现问题对报告产生严重影响。反之,如果是在数据量有限、且对主键字段有充分了解的情况下,可以不必严格遵守以上建议

联合主键

除了可以使用单列作为主键,也可以组合多列作为主键,这种就是联合主键,联合主键允许一列有重复值,只要构成联合主键的所有列的组合不重复即可。

Sales 表的 ProductKey 和 Order Number 可以起到联合主键的效果

比如 Sales 表中本身存在 OnlineSalesKey 这一主键,但是用 ProductKey 和 Order Number 也可以作为联合主键,区分唯一记录。

Power BI 模型不支持联合主键,但是编辑查询的合并查询功能在 Join 的时候可以设置多列进行匹配

如何在表格模型中手动设置主键

Excel Power Pivot 和 SSAS 都支持对任意表设置主键,Power BI Desktop 目前只支持对日期表设置主键,不过你可以通过另外一种方式创建主键,在下文解决循环依赖问题时会提到

Power Pivot:模型视图 – 高级 – 表行为 – 行标识符

Power BI Desktop:建模 – 标记为日期表

SSDT:属性设置 – Row Identifier

Power Pivot 的表行为设置中,行标识符设置起到主键的作用,保持唯一行的作用是将列中的重复值也视为不同记录,此设置只在 PowerView 中生效,无法在透视表中重现

外键

一对多关系中位于关系多端的列就是外键,外键可以包含重复值。第一张图 Sales 表包含的 ProductKey 就是 Product 表的外键。通过设置关系数据库的外键约束,可以控制存储在外键表中的数据,使其不能插入主键表中不存在的记录。

外键可以理解为一种参照(Reference),通过外键你可以查到主键对应的记录在关系的多端是如何分布的

外键

Power BI 不使用外键约束,如果事实表中存在于维表不匹配的记录,基于参照完整性,图表会自动显示一行空值以匹配这个不存在的记录。

数据库中的其他键

  • 超键(Super Key): 在关系中能唯一标识元组的属性集称为关系模式的超键
  • 候选键(Candidate Key): 不含有多余属性的超键称为候选键,也叫最小超键。通常我们会从候选键中选择一个作为主键。
  • 代理键(Surrogate Key):当数据表中的候选键都不适合当主键时,例如数据太长,或是意义层面太多,就会请一个无意义的但唯一的字段来代为作主键。在实践中,代理键值通常是个自动递增的数字。
  • 自然键(Natural Key):与代理键相反,是在业务逻辑中唯一确定一个事物的标识。身份证号(理论上,假设没有因技术原因造成的重复)就是一个自然键,用于确定一个人。
候选键是超键的子集,主键是候选键的子集

索引

在关系数据库中,如果有上万甚至上亿条记录,在查找记录的时候,想要获得非常快的速度,就需要使用索引。索引是关系数据库中对某一列或多个列的值进行预排序的数据结构,可以让数据库系统不必扫描整个表,而是直接定位到符合条件的记录,这样就大大加快了查询速度。

表格模型的 Vertipaq 引擎在读取数据的时候会自动为每列添加索引,在发生计算时,引擎通过扫描索引可以快速定位数据,保证了 DAX 的计算可以高效完成,关于这部分内容在 Vertipaq 引擎一章会做详细介绍。

字典编码过程会为每一列创建字典和索引

字典编码使用的索引在模型中是不可见的,无法在公式中调用,如果你想显式引用索引,编辑查询中提供了自动创建索引的功能,你也可以预先在数据库中生成索引,或者使用 DAX 在计算列中生成索引。

使用主键时的注意事项

操作不当引发的报错

主键不能包含 null 值,否则关系无法创建,如果已创建的关系在后续更新时混入了 null 值,会使整个更新失败。

包含 null 值时的更新失败提示

一对多关系的主键不能包含重复值,否则关系无法创建,如果已创建的关系在后续更新时混入了重复值,会使整个更新失败。

包含重复值的更新失败提示

解决循环依赖问题

本文隐藏内容查看价格为3G币,请先
注:加入VIP会员可享受全站权益,性价比更高。单独购买的内容长期有效,不受时间限制。

发生上下文转换时的可见行

本文隐藏内容查看价格为3G币,请先
注:加入VIP会员可享受全站权益,性价比更高。单独购买的内容长期有效,不受时间限制。
本文隐藏内容查看价格为3G币,请先
注:加入VIP会员可享受全站权益,性价比更高。单独购买的内容长期有效,不受时间限制。

 

扩展阅读

  1. 维基百科:关系键
  2. 外键 – 廖雪峰的官方网站

41
说点什么

1000
 
鼓掌微笑开心憧憬爱你色并不觉得吃瓜doge二哈喵喵思考笑哭捂脸悲伤大哭抓狂汗偷笑打脸捂眼黑线问号晕拜拜闭嘴衰咒骂ok作揖
14 评论数
27 被回复的评论
14 订阅评论的人数
 
查看最近回复
查看最热评论
  订阅本文评论  
最新 最旧 得票最多
提醒
成员
gaoandi

高老师,我有点困惑,我是数据分析专业,会一点python基础。但是看到这里我有点懵x。数据库因为我学过SQL所以主键外键我都明白,但是关于什么上下文,caculate,上下文转换之类,一上来就说我不懂啥意思啊。想问问这本书的阅读顺序。另外这些怎么应用呢?比如如何在power bi里用这个caculate。。我直接看到后面的caculate 那节更不动了。我愿意花时间来学但是有点不知道从哪看起。谢谢!

成员
sankingg

你说的这个“主键”的概念,指的应该是“超键”吧?
因为你在概念里只说了是“存在”,用户选择以后,才是主键。

成员
sankingg

补充一下,再仔细理解一下,你对“主键”的定义,和你对“超键”的定义是一模一样的。

成员
158****6071

vlookup用多列合并生成唯一值,在数据模型里面只能用联合主键解决吗

成员
黑白

你好,我在本地安装了Access Database Engine 2010 Access Database Engine,可是想在pb里转化数据,还是报错我未安装

成员
黑白

“ContosoSalesV31_64bit.accdb”不是一个有效的路径。确定路径名称拼写是否正确,以及是否连接到文件存放的服务器。“
啊,现在是这样提示了,是因为源文件不在我本地电脑上是吗?

成员
Elly

有一个疑惑还请指教:
关于外键定义是一对多关系中多的一端称为外键,Power BI中也能建一对多的关系,我理解就是Power BI中有外键约束。那为什么在注意事项中有“Power BI不使用外键约束”呢?

成员
Denis

关于”Wrong Sales 遍历了销售表,对于每一行,它计算所有相同行的销售额,而 Correct Sales 计算每一行的销售额。因此,如果销售表中有多个相同的行,Wrong Sales 将得到更高的值。” 我发现出来的结果都是一样的,即使我增加了相同的行。不知道我的理解是否有误,希望得到老师的点评,谢谢!

ScreenHunter_104 Mar. 25 14.06.jpg
ScreenHunter_105 Mar. 25 14.07.jpg
成员
188****5515

都是您好,请问添加索引列做自增主键,这个具体怎么用?指的是在维度表中添加一个索引列,事实表中匹维度表中的索引列,然后两个索引列进行一对多的连接吗?如果是的话这样会不会降低性能?还是说采取这样严谨的主键外键的好处大于弊端?

成员
137****2137

上列中:[Sales Amount] 与[Correct Sales]计算过程 和结果有什么区别,谢谢

成员
158****4138

对我的帮助很大,很感谢,请问有些案例有没有源文件呀

成员
muxiaomi

联合主键 定义里最后一句似乎应该是 “只要[~~不是~~]构成联合主键的所有列的组合不重复即可”双重否定表示肯定

> 在介绍主键定义的时候提到过,除了可以使用单列作为主键,也可以组合多列作为主键,这种就是联合主键,联合主键允许一列有重复值,** 只要不是构成联合主键的所有列的组合不重复即可 **。

游客
xuyi75

不好意思,你写的文字我没看懂。
我认为:只要[~~不是~~]构成联合主键的所有列的组合不重复即可,这句话没毛病啊。它强调了合并后的组合不可重复。
我的理解如下:
(1)编制联合主键的目的就是产生唯一码。
(2)所以合并后的联合主键的列里不允许包含重复值,不然没意义。
(3)编制联合主键的原因之一,可能合成联合主键的多列中都各有重复值

成员
跑//

请教!
如果:通过“行标识符”或通过维度表建立关系,均能达到设置主键的作用的话,怎么理解:
假设:1对多关系中,维度表中存在2列均唯一的字段,如果建立关系的字段1是主键的话,我在”表行为”中设置”行标识符”为字段2,那么此时哪个字段是主键呢?
维度表中的字段1是不是在功能上与主键相同,但字段2才是维度表的主键?

成员
跑//

pivot中通过表行为添加主键时,”保持唯一行”的作用是什么?

成员
超哥

谢谢!对我这样的初学者,基本数据管理的知识真是很有帮助!

成员
dony

# 主键是什么

定义:就像是一个不重复的编号,可以作为识别一个数据的唯一标识。

举例:主键就是周星驰电影里的9527

## 注意事项

主键不要有业务相关功能,它的作用只有一个,就是唯一标识

主键尽量用系统函数生成

# 主键的类型

> 主键最好是由系统自动生成

## 自定主键

就是Excel当中的序号、索引

## GUID类主键

系统自动生成的一段唯一标识

## 联合主键

由多个字段构建出的不重复标识。

比如VLOOKUP的多条件查找时,合并两列数据,构建不重复标识。

# 其他主键技巧

外键,主键在多端中

其他看不懂的,还有:超键、候选键、自然键、代理键

# 主键注意事项

主键不能为空

## 避免循环依赖

没看懂,没有案例,没有场景,看不懂

DAX 圣经

导读

初识 DAX

DAX 基础知识

DAX 原理

DAX 高级原理

基础函数类型

迭代函数

CALCULATE 函数

CALCULATE 调节器

基础表函数

条件判断函数

查找匹配函数

时间智能函数

统计类函数

投影函数

分组/连接函数

集合函数

其他函数