数据模型中表之间的关系

您的浏览器不支持视频。 安装 Microsoft Silverlight、Adobe Flash Player 或 Internet Explorer 9。
视频:Power View 和 PowerPivot 中的关系

通过在不同表中的数据之间创建关系,可以增强数据分析的功能。关系是两个数据表之间建立在每个表中的一个列的基础上的联系。为了理解关系为什么有用,请想像一下在业务中跟踪客户订单数据的情形。您可能会在具有以下结构的一个表中跟踪所有数据:

客户 ID

姓名

电子邮件

折扣率

订单 ID

订单日期

产品

数量

1

郭克仪

chris.kuo@contoso.com

.05

256

2010-01-07

小型数字设备

11

1

郭克仪

chris.kuo@contoso.com

.05

255

2010-01-03

SLR 照相机

15

2

康迈克

michal.kang@contoso.com

.10

254

2010-01-03

预算影音制作器

27

这种方法虽然管用,但会存储大量冗余数据,例如每个订单的客户电子邮件地址。虽然存储成本低,但如果电子邮件地址发生更改,就必须确保更新与该客户对应的每一行数据。这一问题的一个解决方法是将数据拆分到多个表中,然后定义这些表之间的关系。关系数据库(如 SQL Server)中使用的就是这种方法。例如,您导入的某个数据库可以使用三个相关表来表示订单数据: 

Customers

[客户 ID]

姓名

电子邮件

1

郭克仪

chris.kuo@contoso.com

2

康迈克

michal.kang@contoso.com

客户折扣

[客户 ID]

折扣率

1

.05

2

.10

订单

[客户 ID]

订单 ID

订单日期

产品

数量

1

256

2010-01-07

小型数字设备

11

1

255

2010-01-03

SLR 照相机

15

2

254

2010-01-03

预算影音制作器

27

您显式创建的或 Excel 在您同时导入多个表时代表您创建的数据模型中存在各种关系。您还可使用 Power Pivot 加载项创建或管理模型。有关详细信息,请参阅在 Excel 中创建数据模型

如果使用 Power Pivot 加载项导入同一数据库中的表,则 Power Pivot 可以根据 [方括号] 中的列来检测这些表之间的关系,并可以在它在后台生成的数据模型中再现这些关系。有关详细信息,请参阅本文中的关系的自动检测和推理。如果从多个源导入表,则可以按创建两个表之间的关系中所述手动创建关系。

返回页首

本文内容

列和键

关系的类型

关系和性能

表之间的多个关系

表关系的要求

表关系中不支持的内容

组合键和查找列

多对多关系

自联接和循环

Power Pivot 中关系的自动检测和推理

自动检测命名集

关系的推理

列和键

关系基于每个表中包含相同数据的列。例如,“客户”表和“订单”表可以彼此相关,因为它们都包含存储客户 ID 的列。在本示例中,列名称相同,但这并不是必需满足的要求。只要“订单”表中的所有行都包含也存储在“客户”表中的 ID,列名称就可以一个是“客户 D”,而另一个是“客户编号”。

在关系数据库中,有几种类型的“键”,键通常是具有特殊属性的列。了解每个键的用途可以帮助您管理为数据透视表、数据透视图或 Power View 报表提供数据的多表数据模型。

下列键最适合我们使用:

  • 主键:唯一标识表中的行,例如“客户”表中的“客户 ID”。

  • 可选键(或候选键):主键之外的唯一列。例如,“员工”表可能存储雇员工 ID 和社会保障号,这两者都是唯一的。

  • 外键:引用另一表中唯一列的列,如“订单”表中的“客户 ID”(它引用“客户”表中的“客户 ID”)。

在数据模型中,主键或可选键称为“相关列”。如果一个表既有主键又有备用键,则主键和备用键都可用作表关系的基础。外键称为“源列”或称为“列”。在我们的示例中,将在“订单”表中的“客户 ID”(列)和“客户”表中的“客户 ID”(查找列)之间定义关系。如果从关系数据库导入数据,则默认情况下 Excel 会从一个表中选择外键,从另一个表中选择相应的主键。但是,您可以使用具有唯一值的任何列作为查找列。 

关系的类型

“客户”和“订单”之间的关系是“一对多关系”。每个客户可以有多个订单,但一个订单不能对应多个客户。其他关系类型是“一对一”和“多对多”。为每个客户定义一个折扣率的“客户折扣”表与“客户”表具有一对一关系。

下表显示了三个表之间的关系:

关系

类型

查找列

客户-客户折扣

一对一

客户.客户 ID

客户折扣.客户 ID

客户-订单

一对多

客户.客户 ID

订单.客户 ID

注意:  数据模型中不支持多对多关系。多对多关系的一个示例是“产品”和“客户”之间的直接关系,在这种关系中,一个客户可以购买多种产品,同一种产品可由很多客户购买。

关系和性能

在创建任何关系之后,如果有任何公式使用新创建的关系中所涉及的表中的列,则 Excel 通常必须对相应公式进行重新计算。处理可能需要一些时间,具体取决于数据量和关系的复杂程度。有关详细信息,请参阅重新计算公式

表之间的多个关系

数据模型的两个表之间可以有多种关系。要创建准确的计算,Excel 需要从一个表到下一个表的单一路径。因此,每对表之间在某个时刻只能存在一个活动的关系。其他关系处于非活动状态,但您可以在公式和查询中指定非活动的关系。在“关系图视图”中,活动的关系为实线,不活动的关系为虚线。例如,在 AdventureWorksDW2012 中,表 DimDate 包含一个列 DateKey,该列与表 FactInternetSales 中的以下三个不同列相关:OrderDate、DueDate 和 ShipDate。如果 DateKey 和 OrderDate 之间存在活动的关系,该关系就是公式中的默认关系,除非您另行指定。

返回页首

表关系的要求

在满足下列要求时,可以创建关系:

条件

说明

每个表的唯一标识符

每个表都必须具有一个用于唯一标识该表中的每一行的列。此列通常称为主键。

唯一查找列

查找列中的数据值必须是唯一的。换句话说,列不能包含重复值。在数据模型中,Null 值和空字符串等同于空白,这是不同的数据值。这意味着无法在查阅列中有多个 Null 值。

兼容的数据类型

源列和查找列中的数据类型必须兼容。有关数据类型的详细信息,请参阅数据模型中支持的数据类型。

表关系中不支持的内容

在数据模型中,如果键是组合键,您就无法创建表关系。还限制您只能创建一对一和一对多关系。不支持其他关系类型。

组合键和查找列

组合键由多列组成。数据模型不能使用组合键;表必须始终仅有一个列来唯一标识表中的每一行。如果您导入的表中包含基于组合键的现有关系,Power Pivot 中的“表导入向导”会忽略该关系,因为在模型中无法创建该关系。

对于具有多列可定义主键和外键的两个表而言,要在它们之间创建关系,必须首先组合这些值创建一个键列,然后才能创建关系。您可以在导入数据之前执行此操作,也可以通过在数据模型中使用 Power Pivot 加载项创建计算列来执行此操作。

多对多关系

数据模型无法使用多对多关系。您不能在模型中简单添加“联接表”。  但您可以使用 DAX 函数为多对多关系建模。

自联接和循环

数据模型中不允许使用自联接。自联接是一个表与其自身之间的递归关系。自联接通常用于定义父子层次结构。例如,您可以将“员工”表联接到它自身,从而生成显示公司中的管理链的层次结构。 

Excel 不允许在工作簿中的关系之间创建循环。换言之,禁止使用以下关系组。

  • 表 1、列 a 到表 2、列 f 

  • 表 2、列 f 到表 3、列 n 

  • 表 3、列 n 到表 1、列 a 

如果您试图创建的关系会导致创建循环,则会生成错误。

返回页首

Power Pivot 中关系的自动检测和推理

使用 Power Pivot 加载项导入数据的一个优点是 Power Pivot 有时可在其在 Excel 中创建的数据模型中检测关系和创建新关系。

当您导入多个表时,Power Pivot 将自动检测表之间的所有现有关系。此外,创建数据透视表时,Power Pivot 将分析表中的数据。它检测尚未定义的可能关系并建议这些关系中应包含的相应列。

检测算法使用有关列的值和元数据的统计数据,对关系的概率进行推断。

  • 所有相关列中的数据类型都应兼容。对于自动检测,只支持整数类型和文本数据类型。有关数据类型的详细信息,请参阅数据模型中支持的数据类型。

  • 为了成功检测到关系,查找列中唯一键的数目必须大于关系中多方的表中的值数。换言之,关系中多方的键列不得包含查找表的键列中没有的任何值。例如,假设您的一个表中列出了产品及其 ID(查找表),而销售表中列出了每个产品的销售额(关系中的多方)。如果您的销售记录包含的一个产品的 ID 在产品表中没有对应的 ID,则不能自动创建关系,但您也许可以手动创建关系。要让 Excel 检测到此关系,您首先需要更新产品查找表以及缺失产品的 ID。

  • 请确保关系中多方的键列的名称类似于查找表中键列的名称。名称不必完全相同。例如,在业务设置中,实质上包含相同数据的列的名称经常会有多个变体:Emp ID、EmployeeID、Employee ID、EMP_ID 等等。此算法可检测相似的名称,并向具有相似名称或完全匹配名称的列分配较高的概率。因此,为了提高创建关系的概率,您可以尝试重命名导入的数据中的列,使其名称与现有表中的列相似。如果 Excel 找到多个可能的关系,则不创建关系。

此信息可以帮助您了解为何并非所有关系都能检测到,或更改元数据(如字段名称和数据类型)可以怎样改善自动关系检测的结果。有关详细信息,请参阅与关系相关的问题的故障排除

自动检测命名集

系统不会自动检测数据透视表中命名集和相关字段之间的关系。您可以手动创建这些关系。如果要使用自动关系检测功能,请删除每个命名集并将命名集中的各个字段直接添加到数据透视表中。

关系的推理

在某些情况下,表之间的关系会自动链接。例如,如果在下面的前两组表之间创建关系,则会推断出其他两个表之间存在关系,进而自动建立一个关系。

  • Products 和 Category -- 手动创建

  • Category 和 SubCategory -- 手动创建

  • Products 和 SubCategory -- 推断出关系

为了使关系自动链接,关系的方向必须相同,如上所示。例如,如果初始关系是在 Sales 和 Products 以及 Sales 和 Customers 之间建立的,则不会推断出关系。这是因为 Products 和 Customers 之间的关系是多对多关系。

返回页首

扩展你的技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×