数据模型中表之间的关系

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

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

视频:Power View 和 PowerPivot 中的关系

视频:Power View 和 PowerPivot 中的关系

视频: Power View 和 Power Pivot 中的关系

创建关系 amogn 不同表数据分析中添加更多 power。关系是包含数据的两个表之间的连接: 每个表中的一列是关系的基础。若要查看为什么关系非常有用,假设您在您的企业跟踪客户订单的数据。您可以跟踪时遇到的结构如下所示的单个表中的所有数据:

客户 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)中使用的就是这种方法。例如,您导入的某个数据库可以使用三个相关表来表示订单数据: 

客户

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

关系基于每个表中包含相同的数据的列。例如,您可与相关与每个订单客户表包含存储客户 ID的列。在示例中,列名称相同,但不要求。一个可能是客户 id 和其他 CustomerNumber,只要订单表中的行的所有包含也会将其存储在客户表中的 ID。

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

尽管有许多类型的键,以下是最重要出于下面我们目的:

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

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

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

在数据模型,为主键或替代键被称为相关的列。如果表格具有主要和备用,您可以使用任一作为基础表关系。外键的源列或只需列称为。在我们的示例中,将在订单表 (列) 中的客户 id客户表 (查阅列) 中的客户 id之间定义关系。如果您从关系数据库导入数据,默认情况下 Excel 选择从一个表的外键和相应的主键从另一个表。但是,您可以使用任何列包含唯一值的查阅列。

客户和订单之间的关系是一个一对多关系。每个客户可以有多个订单,但顺序不能有多个客户。另一种重要的表关系是一对一。在此处我们示例中, CustomerDiscounts表中,为每个客户定义单个贴现率,具有一对一关系的客户表。

此表显示了三个表 (客户、 CustomerDiscounts,和订单) 之间的关系:

关系

说明

查找列

客户-客户折扣

一对一

客户.客户 ID

客户折扣.客户 ID

客户-订单

一对多

客户.客户 ID

订单.客户 ID

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

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

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

关系图视图中的活动关系是实线,处于非活动状态的虚线。例如,在 AdventureWorksDW2012,表DimDate 中包含的一列, DateKey,与FactInternetSales表中的三个不同列:订单日期,函数的示例。发货日期。DateKey 和订单日期之间的活动关系时,这是在公式中的默认关系,除非您指定。

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

条件

说明

每个表的唯一标识符

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

唯一查找列

查找列中的数据值必须是唯一的。换句话说,列不能包含重复值。在数据模型中,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 支持专员。

×