Power BI 傻瓜入门 10. 完善数据模型

本章内容包含：

• 确定中的数据建模过程中影响性能的因素
• 增强数据模型以获得最佳性能
• 优化数据模型以实现企业级报告和可视化

这就是困境。您的报告在测试和开发环境中运行得非常出色。为什么？因为环境是被包容的。但是当您将数据模型部署到生产中时--无论是通过导入、使用DirectQuery还是使用组合模型进行生产，性能问题都会立即出现。例如，报告和可视化加载时间过长，或者更新时间过长。结果是糟糕的用户体验。数据专业人员大部分时间都在扮演侦探的角色，试图追踪数据错误和性能问题。你猜怎么着？大多数情况下，数据模型是罪魁祸首。制作不当的DAX表达式，或者表之间的错误关系，都会极大地降低数据模型的速度。更重要的是，随着数据的增长，问题也随之而来。这就是为什么你想解决开发中的问题，这样你就可以在为时已晚之前纠正问题。在本章中，您将学习在各种条件下为企业级性能优化数据模型所需的步骤、流程和概念。

将查询与容量匹配

根据您使用的Power BI版本，您几乎肯定会遇到吞吐量限制。将模型发布到Power BI时，与底层数据源通信的最大并发查询数会影响数据环境。在Power BI Desktop、Power BI Pro、Power BI Premium甚至Power BI Report Server的情况下，每种都有不同的容量限制。因此，容量会影响您的查询能力。随着容量的不同，性能会有很大差异。这就是为什么修剪掉不必要的元素是提高性能的一种方法。限制试图挤过缺口的数据量可以确保更多的数据真正通过。

删除不必要的列和行

听说过拥有太多美好的东西会很危险吗？最好只加载数据模型和报告实例所需的列和行，或者至少等到准备好增长时再加载。这意味着您应该禁用加载运行报告所不需要的查询。此外，在加载模型之前，您应该只将数据筛选到所需的行或列。

丢弃列

数据模型中的列有一个或多个用途。它们用于支持可视化或计算，或两者兼而有之。除非该列用于特定目的——只需说“不”。如果该列有大量不同的值，请考虑修改模型。如果你想复习一下如何删除列，请翻到第9章，看看如何删除那些无关的列。

如果要从数据仓库导入数据，请评估数据集及其事实表的主键。尽管主键有助于审核数据，但它们可能会产生比您想要的更大的复杂性。因为主键在每一行中都有一个唯一的值，所以关联的事实表在列级别变得不必要地大。你真的想要一张既臃肿又没有什么价值的表吗？

从一开始就限制行数

在Power BI中构建数据模型时，从一开始就必须包含筛选标准。该标准可以是从属性集到一系列日期的任何内容。但是限制行数是非常重要的。假设您只关心分析有限的数据集。为什么不从一开始就减少行数呢？如果在报告中产生无关的性能问题，那么包含值或参数很少的数据是没有意义的，以后可以添加这些值或参数。

用度量值和变量交换数字列

太多的Power BI用户在管理列时感到绝望，但没有充分的理由感到如此沮丧。在DAX公式中创建度量值和编写变量可以为您提供远不那么复杂的代码和计算选项。Power BI Desktop的新手在创建数据模型时未充分利用变量的情况并不少见。

当你戴上数据建模师的帽子时，你通常要承担一项艰巨的任务，那就是成为代码大师、调试侦探和你制作的那些杀手级可视化的萌芽艺术家。但是，要成功地为每一个角色提供服务，你需要想出一些漂亮的代码（有时还需要一些技巧），尤其是在你进行DAX计算时。您已经看过DAX的一些示例，并且知道创建公式需要一些复合表达式和复杂表达式。一个复合表达式可能涉及到许多嵌套函数的使用，以及大量表达式逻辑的可重用性。这就是为什么你会把你方便的花花公子朋友，这个变量加入到组合中（我们在第16章中详细介绍）。变量是一种有效存储DAX计算的方法，目的是重用。变量可以帮助您编写更复杂的计算，具有效率和风格。更好的是，变量可以帮助您增强代码的性能、可靠性、可读性，当然还可以降低复杂性。

你经常被迫使用嵌套函数和重用逻辑——换句话说，与计算数据相关的过程——来产生有效的表达式。为了取得成功，你可以使用的最有效的捷径是利用变量。这意味着处理表达式通常需要很长时间。由于计算通常很难阅读，故障排除自然会变得有点麻烦。使用度量和DAX变量有助于减少处理时间。

数据模型中的变量提供以下好处：

• 提高性能：减少了多次计算表达式的需要。查询结果的时间可以达到处理时间的50%左右。
• 增强可读性：当您希望替换扩展表达式时，变量是理想的选择。如果你需要一种更容易阅读和理解公式的方法，变量可以帮助你。
• 协助调试：变量也是一种调试工具。如果您需要测试公式或表达式，变量是故障排除的首选实用程序。

将传统DAX表达式转换为包含变量的表达式以提高性能、增强可读性并使调试更容易的示例包括：

无变量

SalesGrowth % =
DIVIDE(([ProductSales] - CALCULATE([ProductSales],
PARALLELPERIOD(‘Date'[Date], -12, MONTH))),
CALCULATE([ProductSales], PARALLELPERIOD(‘Date'[Date], -12,
MONTH))
)

有变量

SalesGrowth % =
VAR SalesLastYear = CALCULATE([ProductSales],
PARALLELPERIOD(‘Date'[Date], -12, MONTH))
RETURN
DIVIDE(([Sales] - SalesLastYear), SalesLastYear)

减少基数

你可能没有意识到这一点，但我们在前面的章节中已经变相地谈到了基数。当查看一组数据中元素的数量时，您正在评估基数。举个例子：我住在很多城市，但在每个城市，我都可以住在一栋或多栋房子里。城市代表为多个（M），我在每个城市居住过的地方可以是一个或多个（1或M）。数据模型中描述的基数或关系可能被表示为多对多（M:M）关系，尽管有些人可能会认为这种关系是多对一（M:1）。

当您希望清理更新时，该过程的一部分是减少基数，以创建尽可能可靠、紧密耦合的数据集。当您试图完善模型性能时，您可能从未考虑基数可能会造成性能延迟——尽管它肯定会发挥作用。让这一点非常清楚的一个证据是，当您使用Power Query Editor对实体（表）和属性（字段）进行数据分析时，您会得到列分布选项，这些选项可以为您提供关于每列可用的不同和唯一项的统计信息。

不同的值表示列中的各种值。相反，唯一值是指在一列中只出现一次的值。

当列中有很多值时，尤其是当值重复时，基数级别很可能无关紧要。包含许多唯一值的列具有较高的基数。这就是您想要降低基数的原因——它优化了模型性能。换句话说，您希望尽可能地将列的数量减少到那些有意义的值。

根据关系的创建或编辑方式，列配置会有所不同，并相应地影响基数。通过基数，关系的方向和所述关系的模型由关系类型定义。表10-1说明了四种基数类型以及基数减少的影响

基数	描述
一对一 (1:1)	两个表都只有一个特定值的实例。
多对一（M:1）	最常见的基数，因此也是默认类型。 一个表中的列可以具有一个值的多个实例。另一个相关的表通常是一个只有一个实例的查找表。
一对多（1:M）	当一个表中的列具有特定值的单个实例时。相关表具有一个或多个值。
多对多（M:M）	适用于复合模型，并且可以在表之间多对多地使用。对唯一值没有具体要求。也没有必要为关系建立新的表。

在数据模型开发过程中，创建和编辑关系是必不可少的。无论模型中的关系（或选择的基数）如何，强制的数据类型都是一致的。但是，请记住，如果两列的数据类型不匹配，则关系将失败。

Power BI Desktop提供了不同的技术来帮助减少加载到数据模型中的数据量--例如摘要。减少加载到模型中的数据可以提高关系的基数。这就是为什么你希望你的模型尽可能小，尤其是如果你知道它们会随着时间的推移而增长。

减少查询

在Power BI Desktop的“选项和设置”（位于“文件”菜单下）下，您可以找到整个“查询缩减”页面。（见图10-1。）在这个菜单选项下有几个选项，分为三个主要标题：减少由以下对象发送的查询数、切片器和过滤器发送的查询数量。每个选项的目的如下：

• 减少发送查询的数量：允许您禁用报告上的交叉突出显示。您还可以确保减少后端查询，从而提供更高效的导航体验。除非您希望减少查询，否则默认情况下不要选择禁用交叉突出显示/筛选。
• 切片器：允许您仅在特定条件下显示“应用”按钮，特别是在满足两个条件之一时。立即应用切片器更改，或者在每个切片器上添加“应用”按钮，以便在准备就绪时应用更改。您通常希望使用“立即应用切片器更改”选项，除非在评估查询时涉及多步骤过程。
• 过滤器：允许在选择其中一个选项时显示“应用”按钮。您可以选择立即应用基本筛选器更改，向所有基本筛选器添加“应用”按钮以在准备就绪时应用更改，或者向“筛选器”窗格添加单个“应用”按键以一次应用更改。
  除非需要过滤器来支持更复杂的查询类型，否则通常需要选择“立即应用基本过滤器更改”选项。

当您希望向报告发送更少的查询，或者希望禁用某些导致性能不佳的交互（假设查询所需时间比您希望的稍长）时，强烈建议应用查询减少选项。要启用查询减少选项，请转到Power BI Desktop并按照以下步骤操作：

1. 选择文件 ⇒ 从主菜单中选择“选项”和“设置”，然后从显示的菜单中选择选项。
2. 在屏幕左侧列表中的当前文件标题下，选择当前文件下的查询缩减
3. 主窗口将刷新以显示查询减少选项，如图10-1所示。

转换为复合模型

有时，应该将直接导入和DirectQuery结果组合到一个模型中，以更好地支持存储配置。表存储模型可以是双重的，同时支持直接导入和DirectQuery。当两种模型类型都可用时，最终将创建一个复合模型。作为复习，复合模型允许您组合来自不同源类型的两个或多个数据连接。您可能有一个或多个DirectQuery连接以及一个直接导入连接，也可能有几个DirectQuery连接。也有可能将上述所有选项组合在一起。

那么，为什么要将连接从一种特定的模型类型转换为复合模型呢？关键是性能。您会发现，DirectQuery和直接导入选项的功能和性能体验都得到了显著改善，因为您可以将多个DirectQuery或导入模型集成到复合模型中，从而支持聚合。当处理从聚合源派生的复合模型时，可以减少查询负载，从而更快地产生更好的结果。

不要太快地开发复合模型——您的第一选择应该始终是创建一个直接导入模型。它为您提供了最大的控制、最显著的设计灵活性和最佳的性能选择。当然，这条规则也有例外。仅靠直接导入模型无法解决大数据量和实时报告问题。如果您知道数据将存储在单个数据模型中，那么DirectQuery是一个不错的第二选择。

以下是忽略一般规则并考虑复合建模的明智时机：

• 您需要通过将多个数据源合并为一个真实数据源来提高性能，因此重点放在数据聚合上。
• 您希望将DirectQuery模型与必须导入到新模型中的其他数据集相结合。
• 您需要将两个或多个DirectQuery数据源组合到一个模型中。

DirectQuery方法涉及从Power BI Desktop中直接连接到其源存储库中的数据。因此，它是将数据导入Power BI Desktop的替代方案。问题是，当您使用DirectQuery方法时，整体用户体验在很大程度上取决于底层数据源的性能。问题包括从超时问题到访问源的并发用户数量，影响负载和数据源。

不幸的是，Power BI模型的性能不仅会受到底层数据源性能的影响，还会受到其他不可控因素的影响，包括网络延迟和服务器性能。这两个因素都远远超出了用户的控制范围。因此，如果性能优化是主要目标，则使用DirectQuery会对模型质量造成风险。如果您对源文件或数据库的控制有限，则DirectQuery的有效性有限。

创建和管理聚合

本章的一个主要主题是，过多的数据会导致性能问题。因此，总结数据并将其呈现在更高的级别可能是有益的。例如，可以聚合有关供应商、销售、产品和机构的所有数据——换句话说，获取原始数据并进行汇总。通过聚合数据，可以减少数据模型中的表大小。
您只需要关注几个表，而不是有很多表，这有助于改进查询。

您的组织可能会决定在其数据模型中使用聚合，原因如下：

• 处理大数据：在处理大数据时，聚合是查询性能的更好选择。您可以快速分析和评估大型数据集的见解，尤其是在缓存这些数据集时。因为操作所需的资源数量较少，所以您可能会将更多的数据模型资源转移到这种设计方案中。
• 优化数据刷新：聚合可以帮助降低刷新过程的速度。因为您使用小数据模型来减少缓存大小，所以刷新时间会减少。用户可以更快地访问数据。数据集通常是从一组大的记录中压缩而来的。
• 管理模型大小：有些模型是稳健的。如果您通过聚合的方式合并表，则有助于确保数据模型不会随着数据集的增长而失控。
• 保持模型相关性：聚合是一种积极主动的方法，可以确保数据模型不会出现潜在的性能问题。当您遇到由数据量问题引起的刷新和查询问题时，一种规避问题的方法是聚合数据模型。

创建部分

聚合不是一项可以掉以轻心的活动。您需要做艰苦的工作来决定要聚合数据集的级别。以我的Award数据集为例。我应该在机构级别、NAICS代码级别或其他级别汇总数据吗？我主张在机构层面聚合数据，因为你想选择数据分组，在那里你会发现最大数量的可能协同效应，这些协同效应也可能具有较低级别的相关属性。在Award数据集中，可以有十个机构，每个机构都支持一个或多个NAICS代码。（许多奖项可能重叠。）您可能还拥有一个或多个与该机构和NAICS代码相关的奖项。在数据集中产生最佳洞察力的公共线程是代理，因为它在数据中提供了最高的精度。

一旦决定了粒度，就需要弄清楚如何创建聚合。尽管有许多聚合方法，但每种方法的结果基本相同。

使用Direct Import方法，如果您可以直接访问数据库，则可以创建具有必要聚合的表。这意味着您需要向数据库中添加另一个表，其中包含要合并为单个表的字段。表格完成后，您可以将数据集直接导入Power BI Desktop。然而，现实是，您可能无法访问源数据库；因此，考虑直接在Power BI Desktop中的计划B。

假设您更喜欢在Power BI Desktop中进行工作。在这种情况下，使用Power Query Editor创建聚合可能更容易、更高效。图10-2让您可以一窥这个过程。其想法是在Power query Editor中打开查询，并将数据聚合到Award List表的三列中：AgentID列、VendorID列和NAICSID列。要访问该表，请执行以下步骤：

1. 转到Power Query Editor。
2. 在“查询”窗格中，选择“Awards列表”表。
3. 单击功能区“常用”选项卡的“管理列”区域中的“选择列”按钮。
4. 选择“列”。（图10-2）。
   
   通过选择“Awards列表”表，该表中的所有可用字段将显示在“选择列”屏幕中。
5. 在Choose Columns（选择列）屏幕中选择AgentyID、VendorID和NAICSID。（见图10-3。）
   选择这三列将是创建聚合条目的第一步。
   
6. 您现在已经删除了除三列之外的所有列。如“查询设置”窗格中所述，您可以看到Power Query Editor确认要删除的列（删除其他列）。在屏幕上，您将在Power Query Editor中找到这三列。（图10-4）
   
7. 在Power Query Editor中显示这三列之后，单击Ribbon的Home选项卡的Transform区域中的Group By图标。（见图10-5）
   
8. 在出现的“分组依据”窗口中，选择要分组依据的项目。
   在这种情况下，我选择AgentID。
   您应该为新列创建一个名称。我创建的名称是AgentyAwardedByVendor。在这种情况下，我创建了一个新的列名，选择Sum column作为聚合操作，并选择VendorID作为按Agency ID分组时聚合的项目，如图10-6所示。
   
   结果是按AgentID分组的值的总和，因此现在每个AgentID只有一个聚合列，而不是多个列。您已经将多个列合并为一个列，这支持更好的性能。汇总结果见图10-7。
   
9. 单击功能区“常用”选项卡上的“关闭并应用”按钮以关闭“增强查询编辑器”。
   您现在正在关闭Power Query Editor并返回到“数据模型视图”选项卡。此操作将保存对数据模型的所有更改。数据模型会自动刷新，导致数据模型明显更小，因为您刚刚使用聚合条件缩减了条件。您将在图10-8中看到，所有链接的表现在都是独立的，这意味着它们只在必要时用作查找。该模型中仅有的两个表，即机构联系人表和奖项列表表，现在可以实现重大活动。
   

管理部分

创建聚合后，您必须在Power BI Desktop环境中管理聚合，这包括管理它们的行为，这对您来说并不奇怪。

要从任何Power BI Desktop环境管理聚合，请执行以下步骤：

1. 在数据模型视图中，导航到模型右侧的“数据”窗格。
2. 在“数据”窗格中，右键单击要管理其聚合的表。
   在这种情况下，我选择了Awards List表来管理上一节中创建的聚合。
3. 从显示的菜单中选择管理聚合。（见图10-9。）
   
   此时将显示Manage Aggregations窗口，如图10-10所示。
   
4. 对于您创建的每个聚合，您可以从“摘要”下拉列表中选择一个选项。
   您可以根据Power BI Desktop为所选的每种摘要类型决定的预定义条件更改表和列。
5. 完成后，单击“全部应用”按钮。（再次参见图10-10。）

现在，您已经根据刚刚指定的更新条件更改了原始模型聚合。