Power-BI-聚合终极指南

Power BI 聚合终极指南

Power BI 聚合终极指南

如果你曾经在 Power BI 中使用过复合模型功能，你可能已经听说过另一个极其重要且强大的概念——聚合！这是因为，在许多场景中，尤其是在企业级模型中，聚合是复合模型的一个自然“成分”。

然而，由于复合模型功能也可以在不涉及聚合的情况下使用，我认为单独解释聚合概念是有意义的。

在我们解释 Power BI 中聚合的工作原理并查看一些特定用例之前，让我们首先回答以下问题：

我们为什么需要聚合？在模型中拥有两个相同数据表有什么好处？

在我们澄清这两个要点之前，重要的是要记住，在 Power BI 中有两种不同类型的聚合。

用户定义的聚合在几年前是 Power BI 中唯一的聚合类型。在这里，你负责定义和管理聚合，尽管 Power BI 在执行查询时后来会自动识别聚合。
自动聚合是 Power BI 中较新的功能之一。当启用自动聚合功能时，你可以喝杯咖啡，坐下来放松，因为机器学习算法将收集关于你报告中运行最频繁的查询的数据，并自动构建聚合以支持这些查询。

这两种类型之间的重要区别，当然除了自动聚合不需要你做任何事情，只需在你的租户中打开此功能之外，还有许可限制。虽然用户定义的聚合可以与 Premium 和 Pro 一起使用，但自动聚合目前需要 Premium 许可证。

从现在开始，我们将只讨论用户定义的聚合，请记住这一点。

好的，这里是对聚合以及在 Power BI 中如何工作的简短解释。这里是场景：你有一个非常大的事实表，可能包含数亿行数据。那么，你是如何处理如此大量数据的分析请求的呢？

图片由作者提供

你只需简单地创建聚合表！实际上，这种情况非常罕见，或者说它更像是一个例外而不是规则，即分析需求是查看单个交易或单个记录作为最低级别的细节。在大多数情况下，你希望对汇总数据进行分析：比如，我们特定的一天有多少收入？或者，产品 X 的总销售额是多少？进一步，客户 X 总共花费了多少？

此外，您还可以对多个属性进行数据聚合，这通常是情况，并总结特定日期、客户和产品的数据。

图片由作者提供

如果你在想聚合数据的意义何在……嗯，最终的目标是通过提前准备数据来减少行数，从而减少整体数据模型的大小。

因此，如果我想查看客户 X 在第一季度为产品 Y 花费的总销售额，我可以利用这些数据已经提前汇总的优势。

关键“成分”——让 Power BI“意识到”聚合！

好的，这是故事的一方面。现在，更有趣的部分来了。仅仅创建聚合本身并不能加快您的 Power BI 报告的速度——您需要让 Power BI 意识到聚合！

在我们进一步进行之前，先提一句：聚合意识只有在原始事实表使用 DirectQuery 存储模式时才会有效。我们很快就会解释如何设计和管理聚合以及如何设置表的正确存储模式。现在，只需记住原始事实表应该处于 DirectQuery 模式。

让我们开始构建我们的聚合！

图片由作者提供

如您在上述插图中所见，我们的模型相当简单——由一个事实表（FactOnlineSales）和三个维度（DimDate、DimStore 和 DimProduct）组成。所有表目前都使用 DirectQuery 存储模式。

让我们去创建两个额外的表，我们将使用它们作为聚合表：第一个将按日期和产品分组数据，而另一个将使用日期和店铺进行分组：

/*Table 1: Agg Data per Date & Product */
SELECT DateKey
       ,ProductKey
       ,SUM(SalesAmount) AS SalesAmount
       ,SUM(SalesQuantity) AS SalesQuantity 
FROM FactOnlineSales 
GROUP BY DateKey
        ,ProductKey

/*Table 2: Agg Data per Date & Store */
SELECT DateKey
       ,StoreKey
       ,SUM(SalesAmount) AS SalesAmount
       ,SUM(SalesQuantity) AS SalesQuantity 
FROM FactOnlineSales 
GROUP BY DateKey
        ,StoreKey

图片由作者提供

我已经将这些查询重命名为“销售产品聚合”和“销售店铺聚合”分别，并关闭了 Power Query 编辑器。

由于我们希望为大多数查询（这些查询检索按日期和/或产品/店铺汇总的数据）获得最佳性能，我将新创建的聚合表的存储模式从 DirectQuery 切换到 Import：

图片由作者提供

现在，这些表已加载到缓存内存中，但它们仍然没有连接到我们现有的维度表。让我们在维度和聚合表之间创建关系：

图片由作者提供

在我们继续之前，让我停下来片刻，解释一下我们创建关系时发生了什么。如果你还记得我们之前的文章，我提到过 Power BI 中有两种类型的关系：常规和限制性。这很重要：当不同源组（导入模式是一个源组，直接查询是另一个源组）的表之间存在关系时，你将有一个限制性关系！带着所有它的限制和约束。

但是，我有好消息要告诉你！如果我将我的维度表（如 DimDate、DimProduct、DimStore）的存储模式切换到 Dual，这意味着它们也将被加载到缓存内存中，并且根据在查询时哪个事实表提供数据，维度表将表现为导入模式（如果查询针对导入模式的事实表），或直接查询（如果从原始事实表检索数据）：