PowerBI优化：更快、更小、更高效

多年来，Power BI 一直是市场上领先的数据可视化工具。它可以用作自助式数据分析工具，也可以用作企业管理的商业智能工具。根据官方网站：

“使用端到端 BI 平台创建单一事实来源，发现更强大的洞察，并将其转化为影响力，从而事半功倍。”

图 1：Power BI 示例报表

Power BI 旨在为用户提供友好型服务。只需单击几下，您就可以从各种来源导入数据，将它们组合到一个数据模型中，并使用强大的数据可视化功能开始分析数据。这有时会导致人们只是将数据导入工具而不考虑太多的情况。当您在小型数据集上处理单个项目时，可能不会有太多问题。但是，如果您的报告成功了，并且您想与同事和其他部门分享，该怎么办？或者将更多数据加载到模型中，但刷新花费的时间越来越长？甚至其他数据源也添加到您的模型中，但编写 DAX 公式变得困难，并且报表速度变慢。

在本文中，我们将介绍一些技巧，这些技巧将帮助您缩小 Power BI 模型、加快速度并更易于维护。用 Daft Punk 的不朽名言来说：“更难。更好。更快。更强”。

技巧 #1：使用星型模式对数据进行建模

在数据仓库和商业智能领域，星型架构已经存在了几十年。原则很简单;您有两种类型的表：事实和维度。事实表对其中一个业务流程（如销售、退货、温度测量等）进行建模，而维度包含有关特定业务对象（客户、员工、地理位置等）的描述性信息。事实数据表包含一个或多个度量（数值信息），而维度主要包含文本数据。建模技术称为星形架构，因为如果绘制一个图表，其中事实表位于中间，其周围的维度，则会得到一个星形：

图 2：星型架构示例

解释星形图式的整个理论——通常也称为维度建模——对于一篇文章来说，我们走得太远了。如果您想了解更多信息，可以查看 The Data Warehouse Toolkit：The Definitive Guide to Dimensional Modeling 一书。

但是，为什么星型架构更适合 Power BI？首先，星型模式使用起来非常直观。想象一下：您想要筛选、切片或想要放在图表轴上的所有内容都来自维度。您想要实际可视化的所有内容（表格中的数字、图表中的线条或条形）都来自事实表。让我们用矩阵视觉对象来说明：

图 3：矩阵视觉对象中的维度和度量

Power BI 经过优化，可与星型架构配合使用。当维度列上有筛选器或切片器时，它不必加载许多值，因为维度通常很小。如果你把所有东西都放在一个巨大的表中，你的过滤器需要扫描整个表，只是为了获得可能的值的列表。在 Power BI 中，从小表筛选到大表（事实数据表）非常有效。

不使用星型架构建模的另一个缺点是，用 DAX（Power BI 模型的建模语言）编写的公式会变得更加复杂。当所有数据都位于一个大表中时，可能会出现一些计算错误。例如，计算客户的平均年龄并不像看起来那么简单，因为同一个客户可能会在表中重复多次。或者，如果您从关系数据库导入了一些表，则它们可能已被“规范化”，这是一种用于使数据库快速写入（插入、更新和删除）的设计方法。但是规范化的表更难读取，因为您需要将其中许多表联接在一起才能获得所需的数据。同样，它们使 DAX 公式更难编写。Power BI Desktop 中良好数据模型的重要性一文提供了几个很好的示例，介绍了星型架构如何简化 DAX 公式。使用 Power BI 和 Power Pivot for Excel 分析数据一书也更深入地探讨了此主题，如果您想提高 Power BI 技能，则非常推荐阅读该书。如果时间紧迫，可能会喜欢 Guy in a Cube 的 Patrick 中的 YouTube 视频：为什么 Power BI 喜欢星型架构。

好吧，将 Power BI 模型设计为一个或多个星型架构并不完全是一个“快速技巧”，但如果您从一开始就采用它，从长远来看，它肯定会节省您的时间。

技巧 #2：禁用自动日期表

即使您努力创建具有完美构建的星型架构的 Power BI 模型，仍然有许多方法可以优化模型。更简单的技巧之一涉及自动日期/时间表。这是 Power BI Desktop 中的一项设置，用于指示模型为模型的每个日期/时间列自动创建数据表。

图 4：Power BI Desktop 中的自动日期时间设置

默认情况下，此设置处于启用状态。但是这个时间智能功能实际上是做什么的呢？让我们将单个表导入到 Power BI 模型中。我正在使用 SQL Server 的 Adventure Works 示例数据仓库。您可以免费下载和使用这些示例数据库。使用 Power Query，我从 AdventureWorksDW2019 数据库中提取了一个表，而无需应用任何转换。

图 5：使用 Power Query 导入表

当数据加载到模型中时，我们可以直接对其进行分析。让我们创建一个 y 轴和 x 轴上都有 t 的折线图。

图 6：带有订单日期的折线图

我们获取的不是 y 轴上的单个日期，而是年份级别的聚合数据。这怎么可能呢？这是自动日期/时间功能。对于每个 datetime 列，都会在后台创建一个日期表。此日期表包含额外的列，例如年、季度和月。展开 datetime 列时，您可以在字段列表中看到以下内容：

图 7：自动日期层次结构

为您创建了一个简洁的小层次结构（年 -> 季度 -> 个月 -> 天）。这使没有经验的用户能够立即开始在 Power BI 中分析/可视化数据，而无需任何认真的建模工作。在视觉对象中，当您向下钻取到较低级别（例如月份级别）时，可以看到层次结构的效果：

图 8：向下钻取到月份级别的同一图表

如果您有一个小型模型并且没有那么多日期时间列，那么这是一个很棒的功能，但对于包含许多表的大型模型，这可能会产生深远的影响。Power BI Desktop 扫描 datetime 列，查找最小值和最大值，并创建一个包含整个范围的日期表。如果您有很多日期时间列，则这是一个无声杀手。理想情况下，您有一个自己维护的日期维度。当有 20 个 datetime 列时，您突然有 20 个不可见的日期维度！

让我们打开 Power BI 模型的引擎盖，看看这些表是什么样子的。这可以通过一个名为 DAX Studio 的工具来完成，您可以免费下载该工具。在 Power BI Desktop 模型仍处于打开状态的情况下，启动 DAX Studio 并从下拉列表中选择模型：

图 9：连接到 Power BI Desktop 模型

DAX Studio 将连接到您的模型，并在左侧的对象浏览器中显示一些元数据。在那里我们可以找到隐藏的日期/时间表：

图 10：DAX Studio 中隐藏的日期表

我们的表中有三个日期时间列，因此我们有三个隐藏的本地日期表。甚至还有第 4 个表，这是此日期表的模板。让我们去掉这些表格，这可以通过从选项菜单中取消选择功能来完成（请记住，当前文件有一个选项，所有新文件都有一个通用选项）。

对于具有单个表、只有三个日期时间列且日期范围仅为四年的小模型，影响并不大（约占总文件大小的 4%）。但对于非常大的模型，这可能会产生深远的影响。我见过模型通过禁用此功能而损失了数十甚至数百 MB 的大小。来自 SQLBI 的 Alberto Ferrari 在 YouTube 视频 Power BI 中的自动日期时间中解释了为什么禁用此功能如此重要。

技巧 #3：基数、基数、基数

Power BI 模型的强大之处在于它可以很好地压缩数据，从而允许您将数百万甚至数十亿行加载到模型中。但是，并非所有数据都压缩得一样好。压缩列的数据时，Power BI 会为该列创建一个字典。当此列包含大量唯一值时，压缩将受到影响。这会导致模型更大，并且会消耗更多内存。通过优化最大列的压缩，可以使模型更小，这将使它消耗更少的内存，从而更快。

列中非重复值的数量称为基数。列的基数越高，该列占用的空间就越多。有几种最佳实践允许您减少列的基数：

当您的列包含小数时，请考虑减少小数点后的小数位数。例如，如果您有一列包含百分比值（介于 0 和 1 之间的所有内容），并且小数点后有 5 位数字，则您有 100.001 个唯一的可能值（从 0.00001 到 0.99999 的所有内容 + 数字 0 和 1）。当小数点后只有两位数时，只有 101 个唯一值。这似乎并不多，但是如果您有一个常规的十进制数，例如 1748.845687 与 1478.85 呢？那么压缩的差异将是巨大的，因为唯一值的数量要高得多。除非你需要非常精确的测量，否则两三位数通常就足够了。

datetime 列也是如此。如果您的日期还包括时间部分，则有许多唯一值。如果精度上升到秒级别，则一年中有 31,536,000 个唯一值。如果只保留日期本身，则一年中只有 365 个（或可能 366 个）唯一值。将日期截断为日期级别将再次产生高压缩优势。如果您确实需要 time 部分，最好将其放在单独的列中。单个日期值“2023-02-25 15：47：31”将变为“2023-02-25”和“15：47：31”。时间本身只有 86,400 个唯一值，因为这是一天中的秒数。

由于 Power BI 只允许您创建单列关系，因此将表中的多个列连接在一起以创建唯一键可能很诱人。但是，这将导致列具有非常高的基数（等于表中的行数），如果最终结果是文本字符串，则压缩将非常糟糕。更好的选择是在数据仓库或 Power Query 中创建代理键。代理键是无意义的整数，它们将比大字符串具有更好的压缩率。

说到文本字符串，如果基数很高，它们也不会真正压缩得那么好。与日期和数字不同，您不能只是剪掉片段以减少唯一值的数量。由于维度通常没有那么多行，因此文本列的存在并不是什么大问题。但在具有数百万行的事实表中，文本列可能会产生很大影响。您应该尝试将文本放在一个维度中，或者完全删除该列。

为了在您想要优化现有模型时提供良好的开端，您可以使用名为 Vertipaq Analyzer 的工具（Vertipaq 是驱动 Power BI 模型的列式数据库技术的名称）。您可以从 SQLBI 网站免费下载此工具。Vertipaq Analyzer 是一个 Excel 文件，其中包含一些查询。它可以加载 Power BI 模型的所有元数据，并将其存储在 PowerPivot 模型中。它将为您提供模型中的所有列（以及更多列）的概览以及它们消耗的存储空间。这使您可以专注于最重要的列。让我们看一下我们在上一节中创建的 Power BI 模型。

首先，我们需要使用 DAX Studio 导出模型的元数据。在 高级 功能区中，您可以使用 导出量度 功能。

图 11：在 DAX Studio 中导出指标

这将创建一个 .vpax 文件。将其存储在硬盘驱动器上的某个位置。在 Vertipaq Analyzer 中，转到 Vertipaq Analyzer 功能区，然后选择 Open VPAX。加载您刚刚保存的文件。

图 12：打开您刚刚保存的 VPAX 文件

数据将自动刷新。对于我们的用例，最重要的数据位于 Columns （列） 选项卡中。在那里，我们可以很好地了解每列究竟占用了多少空间。

图 13：Vertipaq Analyzer 中的色谱柱概述

有几个有趣的列，例如列中的行数、基数和字典大小。如前所述，可以通过降低 cardinality 来减小总大小。这对字典大小有影响。如上图 13 所示，模型几乎有一半是单列！具有最高的基数和迄今为止最大的字典大小。如果您的数据可视化并非真正需要此列，则可以从模型中省略它，从而将模型大小切成两半。SalesOrderNumber

另一个有趣的观察结果是，每个数据列在表中包含两次。一次作为实际日期，一次作为整数（列名以 结尾）。尽管数据大小本身相同（96kb，显示的所有数字均以字节为单位），但日期列的字典大小是整数列的两倍。Key

当您处理大型模型时，当您想要优化模型时，像 Vertipaq Analyzer 这样的工具至关重要。它清楚地向您展示了您可以专注于哪些色谱柱以获得最大的收益。

结论

在本文中，我们向您展示了 Power BI 开发人员可以使用的几个设计技巧，以充分利用您的模型。总结一下：

始终使用星型架构建模作为模型设计的起点。它可以为您以后省去很多麻烦。

禁用自动日期/时间功能，以避免为 Power BI Desktop 文件中的每个日期时间列创建隐藏表。

尽可能减少每列的基数，删除不需要的列。

本文译自Koen Verbeeck的博客文章。