数据模型中表之间的关系

注意: 我们希望能够尽快以你的语言为你提供最新的帮助内容。 本页面是自动翻译的,可能包含语法错误或不准确之处。 我们的目的是使此内容能对你有所帮助。 可以在本页面底部告诉我们此信息是否对你有帮助吗? 请在此处查看本文的英文版本以便参考。

您的浏览器不支持视频。 安装 Microsoft Silverlight、Adobe Flash Player 或 Internet Explorer 9。

通过创建不同表 amogn 的关系, 为数据分析增加更多的功能。 关系是包含数据的两个表之间的连接: 每个表中的一列是关系的基础。 为了理解关系为什么有用,请想像一下在业务中跟踪客户订单数据的情形。 你可以跟踪具有如下结构的单个表中的所有数据:

CustomerID

名称

电子邮件

折扣率

订单 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 可以根据 [方括号] 中的列来检测这些表之间的关系,并可以在它在后台生成的数据模型中再现这些关系。 有关详细信息,请参阅本文中的关系的自动检测和推理。 如果从多个源导入表,则可以按创建两个表之间的关系中所述手动创建关系。

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

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

虽然有很多类型的密钥, 但这对于我们的用途而言最重要:

  • 主键:唯一标识表中的行, 例如 "客户" 表中的 "客户 id "。

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

  • 外键:引用另一个表中的唯一列的列, 如 "订单" 表中的 "客户id ", 它引用 "客户" 表中的 "客户id "。

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

客户与订单之间的关系是一对多关系。 每个客户可以有多个订单,但一个订单不能对应多个客户。 另一种重要的表关系是一对一关系。 在我们的示例中, CustomerDiscounts表 (定义每个客户的单个折扣费率) 与 "客户" 表具有一对一关系。

下表显示三个表 (客户、CustomerDiscounts订单) 之间的关系:

关系

类型

查找列

客户-客户折扣

一对一

客户.客户 ID

客户折扣.客户 ID

客户-订单

一对多

客户.客户 ID

订单.客户 ID

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

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

数据模型的两个表之间可以有多种关系。 若要生成精确的计算, Excel 需要从一个表到下一个表的单个路径。 因此,每对表之间在某个时刻只能存在一个活动的关系。 尽管其他人处于非活动状态, 您可以在公式和查询中指定非活动关系。

在 "图表" 视图中, 活动关系是一条实线, 非活动关系是一条虚线。 例如, 在 AdventureWorksDW2012 中, 表DimDate包含一个列DateKey, 它与表 FactInternetSales 中的三个不同列相关: "订单日期"、"表达式 iif" 和 " 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 有时可在其在 Excel 中创建的数据模型中检测关系和创建新关系。

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

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

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

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

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

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

自动检测命名集

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

关系的推理

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

Products 和 Category -- 手动创建

Category 和 SubCategory -- 手动创建

Products 和 SubCategory -- 推断出关系

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

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

此信息是否有帮助?

谢谢您的反馈!

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

×