PowerBI DAX处理复杂业务到性能优化1000倍

zifa2003293

本文考察对DAX的真正掌握程度。

本文将带你完成一次 PowerBI DAX 的神奇之旅，如果您是 DAX 的熟练选手，可以试试以下题目。
本文将从很有业务价值的问题出发，抽象出模式进而设计算法并用DAX（在空中：几乎无法测试，必须非常熟练）完成计算；随后发现性能不足的问题，然后通过仔细观察和优化，将性能提升恐怖的 1000 倍。
DAX 是 PowerBI 中的函数语言，并非通用类编程语言，对于很多问题，无法像编程语言一样设计解决思路，需要另辟蹊径。而使用 DAX 设计的算法是否可以达到性能最优也是一个问题。本文达成两个预期：

• 编写一个解决复杂业务问题的DAX算法
  
• 对该算法进行性能优化并展示一个好玩的现象：
  
• 普通算法与优化算法的性能对比
  
• 10000行逻辑查询的性能是可能由于1000行查询逻辑的
  

这里的每个问题都十分惊艳，让我们一起来了解。
问题重述

在很多情况下，我们会遇到以下场景：

• 对于某员工，最近一个月，连续迟到的最大日数是多少？
  
• 对于某会员，最近12个月，连续每月购买的最大月数是多少？
  
• 对于某企业，最近10年中，每年发展都增长的最大连续年数是多少？
  

大家可以自行考虑或尝试实现以上问题的 PowerBI 中 DAX 实现。这并不是一个简单的问题。
问题抽象

为了更好地理解本问题，并为未来扩展留有机会，这里对上述问题进行抽象，如下：


可以看出对于上述问题，均可以描述成由核心两列完成计算的过程。因此，可以对该问题做进一步优化，得到：


对问题进行进一步加工抽象，可以得到：

• Index 列，与行号类似。
  
• Flag 列，指明该用户或产品在当期有效（真实环境中）。
  

于是问题转化成了从Index与Flag构成的表中寻找答案。
DAX 算法设计

本案例中描述的问题比较复杂，由于DAX中是没有循环结构，导致无法使用循环结构来处理问题。欢迎 DAX 高手提供你想到的好方法。
在 PowerBI DAX 中，我们可以通过技巧来实现类似循环结构的效果，我们将这个效果用于本案例，首先来看下算法示意图：


大家可以思考本问题的本质是几层循环结构？
按照上图的算法思路，我们考虑如下：

• 对于[Index]的每一行
  
• 建立从起始位置到当前[Index]位置 n 的结构
  
• 对于该结构的每行 m
  
• 建立从 m 到 n 的结构
  
• 如果 m 到 n 全是 1 ，则该行为连续满足行
  
• 获取连续满足行的最大值，则得到连续满足条件的最大值
  
• 再获取连续满足条件的最大值的最大值
  

因此，可以发现对于这里的业务问题涉及3层循环结构，在DAX中很可惜是不支持循环结构的。
在本文为订阅会员录制的视频中将详细描述该算法的内容以及DAX实现。
DAX 算法实现

这里使用技巧来实现需求，直接上 DAX 算法如下：


如果没有算法设计，光靠肉眼阅读，很难理解该DAX表达式，何况把它写出来了。如果您有更好的实现方式，欢迎留言交流。
Source 的示意结构以及计算完成的结构为：


通过对 Source 表加入一个 Value 列来计算每行的结果。
DAX 性能评估及优化

如果将下图的面积部分视作 DAX工作的负荷，则：


可以看出，凡是出现 1 的位置，都会做一个从头到当前位置的迭代，因此总的算法规模大致在：
n ( 1 + n ) n / 2 ，大致为 n 的三次方规模，其中 n 为行数。
通过增加行数来看看算法的可用性随着时间的变化：


也就是说，当迭代行数达到1000行时，所需时间规模在6分钟（原单位为毫秒，1秒=1000毫秒）。这是一个不可接受的性能。当然在实际的操作中，可能并不需要有大到1000规模的迭代。
算法的优化设计

对于上述的算法，其实已经做了少许优化，算法并不考察每一行，而是仅仅考察Flag=1的行，这样已经减小了计算规模，但远远不够。其实还可以在优化，我们仔细再研究该问题后，可以得到这样的算法思路：


对比之前的算法，从观察面积表示了算法的计算规模（消耗时间）可以看出优化的算法，可以大幅提升性能。其思路是：不从开始位置迭代，不然会产生大量无效迭代计算，优化的算法从1的位置开始迭代，因此可以大幅度缩减计算规模。
如果再进一步仔细观察，会发现如果数据中存在大量的独立点1，也就是说：几乎都是偶尔迟到1次，很少出现连续多次迟到，这是一种稀疏情形，那么还可以做更进一步的优化，将针对第一个 1 的迭代全部去除，以降低大量稀疏的 1 带来的运算量，这种运算也是意义不大的，算法进一步改进如下：


可以再次通过面积来直观对比，可以发现所需面积大幅度下降，也就是性能再次大幅提升。
如果原问题是带有大量的稀疏的 1 的，全部排出后的算法复杂度大致为：
k ( 1 + k ) k / 2 ，其中 k << n ，n 为行数，k 为最终的答案值， 且远远小于 n。
DAX 改进算法的实现

我们看看它的DAX表达式：


高亮圈选的内容就是优化的核心所在。会员视频提供详细讲解，实在不好用文字表达。强烈推荐研究本算法，是提升DAX水平的绝佳案例。
用 DAX Studio 观测性能优化效果

首先来比较一下优化前后，DAX引擎对DAX表达式的处理，也就是翻译成DAX引擎可以执行的逻辑，改良前的逻辑查询达1000行；而改良后的逻辑查询达10000行；问题来了：1000行的效率会比10000行更高吗？截图如下：
优化前：


优化后：


我们分别记录不同量级下的查询耗时来进行分析。
性能实际测试分析

如下所示：


这是在 100 行数据以内，两种算法效果的对比。这反应了在 60个元素以内，优化算法反而看不出优化。
随着数据量的增长，优化算法的优化被慢慢显现出来，如下所示：


这两条曲线太有意思了。可以看出随着时间的变化，优化算法可以保持很好的稳定性，但普通算法在 60 个元素以后就会大幅来到性能瓶颈。优化算法可以处理5000元素在10秒以内完成。也就是说500个用户在过去12个月的最大连续购买月数。我们在DAX中运行可以看到非常明显的差异。
其中，在 超过1200个 元素时，普通算法耗时：


优化算法耗时：


性能差距超过1000 倍。如此大的性能提升，完全依靠算法设计的改进，这在实际业务中也是不多见的，并非所有的解决方案都有很大的改进空间。
为何优化后的查询更复杂，而效率反而更高

大家可以留意到优化后的查询多达10000行；而优化前的查询大致是1000行。由于查询复杂度增加了10倍，因此，表现出：

• 60以内的元素，普通算法胜出；
  
• 100以上元素，优化算法大幅胜出。
  

这也是本案例的一个很有意思的地方。
总结

本文通过实际案例讲述了：

• 复杂DAX的算法设计流程：形象的图示法。
  
• 算法优化流程：避免不必要的计算开销。
  
• 算法性能的优化：在一定数据量级下可以达到1000倍的差距。
  

因此，本文内容在有着巨大的实际业务价值的同时还有着巨大的示范意义。虽然本文给出了算法示意与DAX表达式，但强烈建议读者自行思考并实践本案例，本案例从复杂度及适用性来讲都是DAX中不可多得的好案例。
我的知乎号主页也会经常分享很多关于PowerBI 优质内容，以及我平时直播预告信息，可以点击我号主页查看。