【性能优化】面向存储引擎，优化基础度量值得到海量性能提升

BI佐罗

发布于 2020-06-19 14:56:20

6180

发布于 2020-06-19 14:56:20

性能优化，在 DAX 中是很重要的问题，对 DAX 的性能优化大致可以归结为针对 SE（存储引擎）或 FE（公式引擎）的性能优化。

如果可以确保 SE 和 FE 都在最好的状态下工作，那么 DAX 将得到充分的发挥。而往往分析师会更加关注业务逻辑的表达，但我们开始研究写出更快的 DAX 时，我们将成为会修车的分析师了。我们会通过一系列文章来帮助大家在各个角度来体会 DAX 的性能优化技术。

今天我们来看一个案例。

看一个案例，我们想知道大订单的个数，如下：

OrderPurchaseNumber =
CALCULATE(
        DISTINCTCOUNT( 'Order'[OrderID] ) ,

        FILTER(
            'Order' ,
            'Order'[LinePrice] > 1000 && 'Order'[LineProfit] > 0
        )
)

大订单的定义为包含单价大于1000且利润大于0的订单。

这个定义没有问题，放在 PowerBI 中的计算也是正确的，但不久就会发现它的性能问题，于是，通过 DAX Studio 来检查可以看到：

我晕，居然惊现了 779 个查询。

该查询的意义是计算每天的大订单个数。但这种方法显然是不行的。虽然在度量值的定义上非常自然。

我们再来看看从 PowerBI 中拖拽的情况，如下：

如果研究该图表背后的 DAX 查询，其结果和上述内容是一致的。

那么问题来了，我们建立了一个基础度量值叫：OrderPurchaseNumber，其逻辑也很清楚，但却有如此之差的性能，怎么办呢？

原因分析

这里的问题在于发起了对 SE 的多次查询，不难察觉：

        FILTER(
            'Order' ,
            'Order'[LinePrice] > 1000 && 'Order'[LineProfit] > 0
        )

这里使用了 Order 表作为 FILTER 的参数，而且位于基础度量值的位置，导致在迭代日期时，每次都会做单独计算，导致对 SE 的过度重复访问。

改进措施

有一种做法是，可以将度量值改为：

OrderPurchaseNumber =
CALCULATE(
        DISTINCTCOUNT( 'Order'[OrderID] ) ,

        FILTER(
            ALL( 'Order' ) ,
            'Order'[LinePrice] > 1000 && 'Order'[LineProfit] > 0
        )
)

注意，这里用了 ALL( ‘Order’ ) 而非 ‘Order’ ，这显然是不对的，因为它改变了语义。

那么进而想到另一种方式为：

OrderPurchaseNumber =
CALCULATE(
        DISTINCTCOUNT( 'Order'[OrderID] ) ,

        FILTER(
            ALL( 'Order'[LinePrice] , 'Order'[LineProfit] ) ,
            'Order'[LinePrice] > 1000 && 'Order'[LineProfit] > 0
        )
)

这样的方式仅仅使用需要用到的两列，而非整个表，来看下效果：

性能得到了非常恐怖的提升。

但细心的伙伴会发现，这种写法的努力方向是对的，但这种写法还是错误的，因为：

        FILTER(
            ALL( 'Order'[LinePrice] , 'Order'[LineProfit] ) ,
            'Order'[LinePrice] > 1000 && 'Order'[LineProfit] > 0
        )

作为筛选器参数，会覆盖外部的筛选，这也是不正确的逻辑，所以，需要进一步优化为：

OrderPurchaseNumber =
CALCULATE( DISTINCTCOUNT( 'Order'[OrderID] ) ,
    KEEPFILTERS(
        FILTER(
            ALL( 'Order'[linePrice] , 'Order'[LineProfit] ) ,
            'Order'[LinePrice] > 1000 && 'Order'[LineProfit] > 0
        )
    )
)

性能结果为：