教程：使用 Excel、Power Pivot 和 DAX 扩展数据模型关系

本教程包括以下几部分内容：

• 使用 Power Pivot 中的关系图视图添加关系

• 使用计算列扩展数据模型

• 创建层次结构

• 在数据透视表中使用层次结构

• 检查点和测验

本教程最后提供了一个测验，以检验您的学习成效。

本系列使用描述奥运会奖牌、主办国家/地区和各种奥运会赛事的数据。 本系列包含以下几个教程：

1. 将数据导入 Excel 并创建数据模型

2. 使用 Excel 扩展数据模型关系， Power Pivot 和DAX

3. 创建基于地图的 Power View 报表

4. 整合 Internet 数据并设置 Power View 报表默认值

5. PowerPivot 帮助

6. 创建美观的 Power View 报表 - 第 2 部分

建议您依次学习以上各教程。

这些教程使用启用了 Power Pivot 的 Excel 2013。 有关 Excel 2013 的详细信息，请单击此处。 有关启用 Power Pivot 的指导信息，请单击此处。

使用 Power Pivot 中的关系图视图添加关系

在此部分中，您将使用 Microsoft Office Excel 2013 中的 Power Pivot 加载项扩展模型。 使用关系图视图可在 Microsoft SQL Server Power Pivot for Excel 中轻松创建关系。 首先，您需要确认已经启用 Power Pivot 加载项。

注意： Microsoft Excel 2013 中的 Power Pivot 加载项是该Office Professional Plus的一Office Professional Plus。 有关详细信息，请参阅启动 Power Pivot in Microsoft Excel 2013 加载项。

将 Power Pivot 添加到 Excel 功能区，方法是启用 Power Pivot 加载项

启用 Power Pivot 后，您将在 Excel 2013 中看到名为“POWERPIVOT”的功能区选项卡。 若要启用 Power Pivot，请执行以下步骤。

1. 转到“文件”>“选项”>“加载项”。

2. 在靠近底部的“管理”框中，单击“COM 加载项”>“执行”。

3. 选中“Microsoft Office Microsoft Excel 2013 中的 Power Pivot”框，然后单击“确定”。

此时，Excel 功能区中将出现一个“POWERPIVOT”选项卡。

使用 Power Pivot 中的“关系图视图”添加关系

Excel 工作簿包含一个名为“主办地”的表格。 我们将“主办地”复制粘贴到 Excel 中，随后将数据设置为表格格式。 要将“主办地”表格添加到数据模型，我们需要建立关系。 我们将使用 Power Pivot 直观地展现数据模型中的关系，然后创建关系。

1. 在 Excel 中，单击“主办地”选项卡，使之成为活动工作表。

2. 在功能区上选择“PowerPivot”>“表格”>“添加到数据模型”。 此步骤会将“主办地”表格添加到数据模型。 它还将打开 Power Pivot 加载项，您将使用它执行此任务的其他步骤。

3. 请注意，Power Pivot 窗口显示该模型中的所有表，包括“主办地”表格。 连续单击几个表。 在 Power Pivot 中可以查看您的模型包含的全部数据，即使其未显示在任何 Excel 工作表时也是如此，例如下面的“分项”、“小项”和“奖牌”数据，以及“S_Teams”、“W_Teams”和“大项”。
   
   

4. 在 Power Pivot 窗口的“视图”部分中，单击“关系图视图”。

5. 使用滑动条调整关系图大小，以便您可以查看关系图中的所有对象。 通过拖动标题栏来重新排列表，使之可见并且彼此相邻。 请注意，有四个表与其他表无关：“主办地”、“小项”、“W_Teams”和“S_Teams”。
   
   

6. 您会注意到，“奖牌”表和“小项”表均有一个名为“DisciplineEvent”的字段。 进一步检查可以确定，“小项”表中的 DisciplineEvent 字段包含唯一、非重复的值。

1. 在“奖牌”表与“小项”表之间创建关系。 在关系图视图中，将“小项”表中的表中的 DisciplineEvent 字段拖动到“奖牌”表中的 DisciplineEvent 字段。 此时两者之间将显示一条直线，表示已建立关系。

2. 单击“小项”和“奖牌”的连接线。 突出显示的字段定义关系，如以下屏幕所示。
   
   
    

3. 要将“主办地”连接到数据模型，我们需要一个字段，其中包含唯一标识“主办地”表中各行的值。 然后，我们可以搜索数据模型，确定另一个表中是否存在相同数据。 在关系图视图中查找无法执行此操作。 选定“主办地”后，切换回数据视图。

4. 检查列之后，我们注意到“主办地”并没有包含唯一值的列。 因此必须要使用计算列和数据分析表达式 (DAX) 来创建此列。

理想情况下，数据模型中的数据已经具备全部必要字段，可以直接创建关系，并且汇聚要在 Power View 或数据透视表中显示的数据。 但表并非总是能如此配合您的工作，因此下一节将介绍如何使用 DAX 创建可用于创建表间关系的新列。

使用计算列扩展数据模型

要在“主办地”表与数据模型之间建立关系，并借此将数据模型扩展以包含“主办地”表，“主办地”必须具有唯一标识各行的字段。 此外，该字段必须与数据模型中的字段相对应。 分别处于两个表中、彼此对应的两个字段使得两个表的数据得以关联。

由于“主办地”表并没有这样的字段，因此需要创建。 为保持数据模型的完整性，不能使用 Power Pivot 编辑或删除现有数据。 但您可以利用基于现有数据的计算字段创建新列。

观察“主办地”表和其他数据模型表，可以确定在“主办地”表中可创建最适合的唯一字段，然后与数据模型中的一个表关联。 两个表都需要一个新的计算列，以满足建立关系时的必要条件。

在“主办地”中，我们可以通过组合“届”字段（举办奥运会的年份）和“季节”字段（夏季或冬季）来创建一个唯一计算列。 在“奖牌”表中同样有一个“届”字段和一个“季节”字段，因此只要在这两个表中同时创建组合“届”字段和“季节”字段的计算列，即可在“主办地”和“奖牌”表之间建立关系。 以下屏幕显示“主办地”表，其“届”和“季节”字段处于选中状态

使用 DAX 创建计算列

我们从“主办地”表开始。 目标是依次在“主办地”表和“奖牌”表中创建一个计算列，以便随后用于在两个表之间建立关系。

在 Power Pivot 中，您可以使用数据分析表达式 (DAX) 创建计算。 DAX 是用于 Power Pivot 和数据透视表的一种公式语言，专为 Power Pivot 中的关系数据和上下文分析而设计。 您可以在新的 Power Pivot 列以及 Power Pivot 的计算区域中创建 DAX 公式。

1. 在 Power Pivot 中，选择“开始”>“视图”>“数据视图”，确保选择数据视图，而非关系图视图。

2. 在 中选择“主办地”表。 与现有列相邻的是标题为"添加列 "的空列。 Power Pivot 该列为占位符。 在 Power Pivot 中， 可以通过多种方法向表添加新列，其中一种方法就是直接选择标题为“添加列”的空列。
   
   
   

3. 在公式栏中，键入以下 DAX 公式。 CONCATENATE 函数可将两个或更多字段组合为一个字段。 在您键入的过程中，自动完成功能将帮助您键入列和表的完全限定名称，并列出可用的函数。 使用 Tab 键可选择自动完成建议。 您还可以在键入公式时单击列，Power Pivot 会将列名插入到公式中。
   
   =CONCATENATE([Edition],[Season])

4. 在您完成公式构建后，按 Enter 以接受该公式。

5. 将为计算列中的所有行填充值。 向下滚动表，您会看到每行都是唯一的 - 因此我们已成功创建唯一标识“主办地”表中各行的字段。 这类字段就称为主键。

6. 让我们将计算列重命名为 EditionID。 您可以通过双击重命名任何列，或右键单击列并选择 “重命名列”。 完成时，Power Pivot 中的“主办地”表应如以下屏幕所示。
   
   

“主办地” 表准备就绪。 下面我们在“奖牌”中创建一个计算列，与“主办地”中创建的 EditionID 列格式相同，以便在两个表之间创建关系。

1. 首先在“奖牌”表中创建一个新列，如在“主办地”中一样。 在 Power Pivot 中选择“奖牌”表，并单击“设计”>“列”>“添加”。 请注意，此处选择了“添加列”。 这与直接选择“添加列”具有相同的效果。

2. “奖牌”中的“届”列与“主办地”中的“届”列格式不同。 在合并或连接“届”列与“季节”列来创建 EditionID 列之前，我们需要创建一个中间字段，将“届”转为恰当的格式。 在表上方的编辑栏中，键入以下 DAX 公式。

   = YEAR([Edition])
   

3. 在您完成公式构建后，按 Enter。 根据您输入的公式，将为计算列中的所有行填充值。 将此列与“主办地”中的“届”列相对比，您会看到两列的格式相同。

4. 右键单击 CalculatedColumn1 并选择“重命名列”以重命名列。 键入年份，然后按 Enter。

5. 新建一列时，Power Pivot 会添加另一个名为“添加列”的占位符列。 接下来我们要创建 EditionID 计算列，因此选择“添加列”。 在编辑栏中，键入以下 DAX 公式，然后按 Enter。
   
   =CONCATENATE([Year],[Season])

6. 通过双击 CalculatedColumn1 并键入 EditionID，为该列重新命名。

7. 对该列进行升序排序。 Power Pivot 中的“奖牌”表现在将类似于以下屏幕。
   
   
    

注意，“奖牌”表 EditionID 字段中的许多值是重复的。 不过这种情况合情合理，因为每届奥运会（这里用 EditionID 值表示）都会颁发许多奖牌。 “奖牌”表中独特的内容是所颁发的每一块奖牌。 “奖牌”表中每条记录的唯一标识符及其指定主键是 Medalkey 字段。

下一步是在“主办地”和“奖牌”之间创建关系。

使用计算列创建关系

接下来我们使用计算列在“主办地”和“奖牌”之间建立关系。

1. 在 Power Pivot 窗口的功能区中，选择“开始”>“视图”>“关系图视图”。 您也可以使用 PowerView 窗口底部的按钮在网格视图和关系图视图之间切换，如以下屏幕所示。
   
   

2. 展开“主办地”，以便可以查看它的所有字段。 我们创建了作为“主办地”表的主键（唯一、非重复字段）的 EditionID 列，还在“奖牌”表中创建了一个 EditionID 列，以支持建立两个表之间的关系。 我们需要找到两列并创建关系。 Power Pivot 功能区上提供了一个“查找”功能，因此您可以搜索数据模型中的对应字段。 以下屏幕上显示“查找元数据”窗口，“查找内容”字段中输入了 EditionID。
   

3. 调整“主办地”表的位置，使之位于“奖牌”表旁边。

4. 将“奖牌”中的 EditionID 列拖到“主办地”中的 EditionID 列。 Power Pivot 将在基于 EditionID 的表之间创建关系，并在两列之间绘制一条表明关系的直线。
   
   

在这一节中，您学习了一种添加新列、使用 DAX 创建计算列并使用该列在表间建立新关系的新方法。 “主办地”表现已集成到数据模型，其数据可用于 Sheet 1 中的数据透视表。 您也可以使用相关数据创建其他数据透视表、数据透视图、Power View 报表等。

创建层次结构

大多数数据模型都包含具有继承关系层次结构的数据。 常见示例包括日历数据、地理位置数据和产品类别。 在 Power Pivot 中创建层次结构非常有用，因为您可以将一个项目拖动到报表（即层次结构），而不必反复组合和排序相同字段。

奥运会数据也属于层次结构。 了解奥运会层次结构（包括大项、分项和小项）较为有帮助。 每个大项都有一个或多个相关分项（有时有很多个分项）。 每个分项有一个或多个小项（同样，有时每个分项包含很多小项）。 下面的图像展示了层次结构。

在本节中，您将在本教程所用的奥运会数据内创建两个层次结构。 随后，您可以利用这些层次结构，了解层次结构如何使数据透视表中整理数据更加简化，后续教程还会介绍层次结构如何简化在 Power View 中整理数据。

创建“大项”层次结构

1. 在 Power Pivot 中，切换到关系图视图。 展开“小项”表，以便可以轻松地查看该表的所有字段。

2. 按住 Ctrl，然后单击“大项”、“分项”和“小项”字段。 选中这三个字段，右键单击然后选择“创建层次结构”。 一个父层次结构节点（即“层次结构 1”）创建于表的底部，并且所选列将作为子节点复制到该层次结构的下方。 确认“大项”出现在该层次结构中的第一位，然后是“分项”，最后是“小项”。

3. 双击标题“Hierarchy1“，键入“SDE”来为新层次结构重命名。 现在，您得到了一个包含“大项”、“分项”和“小项”的层次结构。 现在，“小项”将类似于下面的屏幕。
   
   

创建“位置”层次结构

1. 在 Power Pivot 的关系图视图中，选择“主办地”表，单击表格标题中的“创建层次结构”按钮，如以下屏幕所示。
   
   
   空的层次结构父节点显示在表的底部。

2. 键入“位置”作为新层次结构的名称。

3. 向层次结构添加列有多种方法。 将“季节”、“城市”和 NOC_CountryRegion 字段拖动到层次结构名称（本例中为“位置”字段），在突出显示层次结构名称时松开鼠标按键，添加这些字段。

4. 右键单击 EditionID，选择“添加到层次结构”。 选择“位置”。

5. 确保层次结构子节点的顺序正确。 从上到下的顺序应为：季节、NOC、城市、EditionID。 如果子节点顺序不正确，只需在层次结构中拖动调整为正确顺序即可。 您的表应该类似于下面的屏幕。
   

现在，您的数据模型就有了可以在报表中利用的层次结构。 在下一节中，您将了解如何利用这些层次结构更加迅速、一致地创建报表。

在数据透视表中使用层次结构

现在，我们已经有了“大项”和“位置”层次结构，可将其添加到数据透视表或 Power View，迅速获得包含有用数据分组的结果。 在创建层次结构之前，您必须向数据透视表添加各字段，并按照所需显示顺序排列这些字段。

在这一节中，您将使用在上一节创建的层次结构，快速优化数据透视表。 随后，您将使用层次结构中的各字段创建相同的数据透视表视图，以便对比使用层次结构与使用单独字段的效果。

1. 返回到 Excel。

2. 在 Sheet1中，从“数据透视表字段”的“行”区域中删除字段，然后删除“列”区域中的所有字段。 确保选中数据透视表（现在很小，因此您可以选择单元格 A1，确保选中数据透视表）。 数据透视表字段中仅存的字段就是“筛选器”区域中的“奖牌”以及“值”区域中的“奖牌数”。 数据透视表现在几乎是空白的，如以下屏幕所示。

   

3. 在“数据透视表字段”区域中，将 SDE 从“小项”表拖动到“行”区域。 然后将“位置”从“主办地”表拖动到“列”区域。 只需拖动这两个层次结构即可在数据透视表中填入大量数据，所有这些数据都排列在您在先前的步骤中定义的层次结构中。 您的表应该类似于下面的屏幕。
   

4. 对数据略作筛选，仅查看小项的前十行。 在数据透视表中，单击“行标签”中的箭头，单击“（全选）”清除所有选择，然后单击前十个大项旁边的复选框。 现在，您的数据透视表应该类似于下面的屏幕。
   

5. 您可以在数据透视表中展开任何大项（大项是 SDE 层次结构中的顶级），并查看层次结构中下一级的信息（分项）。 如果在层次结构中，该分项之下还有其他级别，您可以展开分项来查看小项。 您可以对“位置”层次结构执行相同的处理，其中最顶级是“季节”，在数据透视表中显示为夏季和冬季。 展开“水上运动”大项时，可以看到其全部子项元素和数据。 展开“水上运动”下方的“跳水”分项时，也可以看到它的子项，如以下屏幕所示。 我们可以对“水球”执行相同的操作，此时可以看到它仅有一个小项。
   

拖动这两个层次结构，即可快速创建数据透视表，其中包含您关注的结构化数据，支持向下钻取、筛选和排列。

现在，我们在不使用层次结构的情况下创建相同的数据透视表。

1. 在“数据透视表字段”区域中，从“列”区域中删除“位置”。 然后从“行”区域中删除 SDE。 这样就回到了基本的数据透视表。

2. 从“主办地”表中，将“季节”、“城市”、“NOC_CountryRegion”和“EditionID”拖动到“列”区域中，从上到下依次排列。

3. 从“小项”表中，将“大项”、“分项”和“小项”拖动到“行”区域，从上到下依次排列。

4. 在数据透视表中，筛选行标签，仅显示前十个大项。

5. 折叠所有行和列，然后展开“水上运动”，再依次展开“跳水”和“水球”。 工作簿如以下屏幕所示。
   

屏幕看起来与之前极为相似，差别在于您要将七个单独的字段拖动到“数据透视表字段”区域中，而不像之前那样只需拖动两个层次结构。 如果您是唯一根据此数据创建数据透视表或 Power View 报表的用户，创建层次结构实际上可能并不是那么方便。 但如果有很多用户共同创建报表，并且必须判断出正确的字段顺序以保证视图的正确性，层次结构就能切实有效地提高生产力、加强一致性。

在另一个教程中，您将了解如何在 Power View 中使用层次结构和其他字段创建赏心悦目的报表。

检查点和测验

回顾您学习的内容

您的 Excel 工作簿现在带有一个数据模型，其中包含来自多个源的数据，这些数据与现有字段和计算列彼此关联。 您还可以通过层次结构体现表中的数据结构，从而迅速、一致、便捷地制作极具吸引力的报表。

您已了解到，创建层次结构可指定数据的固有结构，并在报表中快速利用层次结构数据。

在本系列的下一个教程中，将使用 Power View 创建有关奥运会奖牌的美观报表。 您还可以执行更多计算，为了快速创建报表优化数据，并导入其他的数据来使这些报表更加有趣。 链接如下：

教程 3：创建基于地图的 Power View 报表

小测验

希望了解您记住了多少已学知识？ 这里为您提供了一个机会。 以下测验重点强调您在本教程中学到的相关特性、功能或要求。 您可以在页面底部找到答案。 祝您好运！

问题 1： 以下哪些视图允许创建两个表之间的关系？

A：在 Power View 中创建表之间的关系。

B：使用 Power Pivot 中的设计视图创建表之间的关系。

C：使用以下插件中的网格视图创建表之间的关系： Power Pivot

D：以上全部

问题 2： TRUE 或 FALSE：可以基于使用 DAX 公式创建的唯一标识符在表之间建立关系。

A：正确

B：错误

问题 3： 可以在以下哪个公式中创建 DAX 公式？

A：Power Pivot 的计算区域。

B ：Power Pivot 的新列。

C：Excel 2013 中的任意单元格。

D：A 和 B 全对。

问题 4： 下列哪一项与层次结构有关？

A：创建层次结构时，所包含的字段将无法再单独使用。

B：创建层次结构时，所包含的字段（包括其层次结构）可在客户端工具中使用，只需将层次结构拖动到 Power View 或数据透视表区域中即可。

C：创建层次结构时，数据模型中的基础数据将合并为一个字段。

D：在 Power Pivot 中无法创建层次结构。

测验答案

1. 正确答案：D

2. 正确答案：A

3. 正确答案：D

4. 正确答案：B