不同数据类型对 DAX 计算性能的影响

本文翻译自Marco Russo & Alberto Ferrari的文章—《Impact of data types in DAX arithmetical calculations》来源：SQLBI数据类型的选择可能会因为内部转换而影响精度和性能。本文展示了选择合适的数据类型和DAX代码所带来的显著差异。

VertiPaq引擎基本上与数据类型无关。这意味着一列数据是字符串、浮点数还是日期并不重要：由于VertiPaq内部会进行字典编码，所有这些数据类型都会使用大约相同数量的内存，并且以几乎相同的速度运行。

然而，当在同一表达式中混合使用不同的数据类型时，DAX可能需要在数据类型之间进行转换。其中一些转换几乎是没什么代价的，而其他转换则需要公式引擎的介入，从而影响性能。

我们之前已经写过关于数据类型转换期间可能发生的错误的文章：理解DAX中的数值数据类型转换，以及DAX中不同数据类型引起的舍入错误。转换错误的问题主要是由于固定小数（也称为货币）和小数（也称为浮点数）的精度不同。本文将从性能的角度开始讨论。

数据类型转换对性能的影响

数据类型转换是一个微妙的话题。由于计算机的性质，实数无法以全部细节表示。计算机总是通过限制可用数字的数量来存储实数的近似值。在几乎所有情况下，这都不是问题。例如，Decimal数字是一个浮点数，使用15位有效数字，这对于任何语义模型来说都过于冗余。在将一个数据类型转换为另一个数据类型时，必须格外小心，以确保转换引入的误差尽可能小。误差几乎总是存在的。但是，如果它足够小，那么它并不重要。大多数开发人员只是信任他们的公式能产生正确的数字，尽管在任何由计算机执行的计算中几乎都存在舍入误差。大多数结果是错误的，但它们的错误程度是人们永远不会注意到的。因此，我们认为它们是正确的。

说了这么多，每当进行数学运算时，Power BI都需要确保引入的误差很小且一致；也就是说，如果发生错误，它必须始终是相同的。为了实现这一目标，某些操作的代码集中在公式引擎中。在某些情况下，简单的乘法需要从存储引擎回调到公式引擎，这会对性能产生负面影响。

我们将以Contoso示例数据库中的货币转换为例进行分析。销售交易以美元存储。每笔交易都存储原始货币代码和用于生成美元值的汇率。

以下是此示例中使用的表。

我们希望生成一份报告，同时显示美元金额和原始货币金额。为了避免在查询时根据交易日期查找汇率，直接在销售表中存储汇率似乎是一个明智的想法。因此，我们将汇率存储在销售表的[汇率]列中。由于汇率需要较高的精度，我们使用了Decimal数据类型，以避免仅使用四个小数位。然后，我们创建了一个简单的度量值：

销售表中的度量值

Original Amount =
SUMX (
    Sales,
    Sales[Exchange Rate] * Sales[Quantity] * Sales[Net Price]
)

尽管看起来很简单，但这项计算的成本可能非常高。其中，Sales[Quantity]（销售量）是整数，Sales[Net Price]（净价）是定点小数，Sales[Exchange Rate]（汇率）是小数。执行乘法运算需要公式引擎的介入，因为存储引擎无法完成小数与定点小数的乘法运算。让我们通过一个测试查询来看看其表现：

EVALUATE
ADDCOLUMNS (
    SUMMARIZE (
        Sales,
        'Currency'[Currency Code]
    ),
    "Original Amount", [Original Amount],
    "USD Amount", [Sales Amount]
)

结果是正确的：它报告了原始货币和美元两种货币的值。

然而，查看服务器时间时，我们遇到了一个令人不快的惊喜。

第一个存储引擎查询包含一个回调，即RoundValueCallback，这是一个请求回调到公式引擎以执行需要四舍五入的计算的请求。

尽管运行得相当快（在我们的2.25亿行数据模型上，它在249毫秒内完成），但我们注意到这种速度是由于大量的并行处理所致。存储引擎CPU（SE CPU）的总成本超过四秒（4,422毫秒）。因此，速度之快并非因为算法优秀：速度之所以能达到这么快，完全得益于大量可用的核心。

一个简单的涉及小数和固定小数的乘法运算已经超出了存储引擎的处理能力。问题是乘法运算必须由公式引擎执行。因此，由于回调被调用了2.25亿次，所以需要大量时间。

一个使代码运行更快的简单方法是更改Sales[Exchange Rate]（销售额[汇率]）的数据类型。将其更改为固定小数会使代码运行得更快。根据计算所需的精度，引入的误差可能过大，也可能在可接受范围内。以下是一个用于测试误差的查询：

DEFINE
    MEASURE Sales[Original Amount] =
        SUMX (
            Sales,
            Sales[Exchange Rate]  * Sales[Quantity] * Sales[Net Price]
        )
    MEASURE Sales[Original Amount Currency] =
        SUMX (
            Sales,
            CURRENCY ( Sales[Exchange Rate] ) * Sales[Quantity] * Sales[Net Price]
        )

EVALUATE
ADDCOLUMNS (
    SUMMARIZE (
        Sales,
        'Currency'[Currency Code]
    ),
    "Original Amount", [Original Amount],
    "Original Amount Currency", [Original Amount Currency]
)

如您所见，这些数字是不同的。

引入的误差是否可接受取决于业务的具体情况。请注意，尽管这种方法看似不妥，但在这种特定情况下，它可能完全没问题。稍后您会看到，误差总是存在的；您只需要决定您能容忍的误差水平。在这种情况下，逐行转换引入的误差是可以接受的。一般来说，在单个交易层面降低值的精度不是最佳选择，因为误差是在计算过程的早期阶段引入的，然后通过最终度量中的乘法和求和进一步扩散。

更好的选择是减少乘法的次数。实际上，由于公式的编写方式，度量可以不必遍历Sales的每一行，而是遍历汇率的不同值，进行乘法运算，然后通过SUMX迭代聚合结果：

销售表中的度量值

Original Amount =
SUMX (
    VALUES ( Sales[Exchange Rate] ),
    Sales[Exchange Rate] * [Sales Amount]
)

这段代码的第二个版本遍历了Sales[Exchange Rate]（销售额[汇率]）的不同值。因此，存储引擎将按Sales[Exchange Rate]对销售额进行分组，并要求公式引擎执行乘法运算。从服务器计时情况可以明显看出这一点。

存储引擎的CPU使用时间从四秒多降到了五分之一秒。同时，数据缓存中生成的行数大幅增加：从八行增加到了六千多行。原因是公式要求按Sales[Exchange Rate]（销售额[汇率]）进行额外的分组。

因此，在进行小数和固定小数之间的乘法运算时，注意这些小细节可能会产生重大影响。

数据类型转换对精度的影响

从性能的角度来看，已经无需再多说什么。然而，我们想在文章结尾向勇敢阅读到这里的读者提出一个警告。如果有人想检查两个公式是否计算出相同的数字，为了安全起见，他们可能会遇到另一个令人不快的惊喜。尽管这两个公式在语义上是相同的，但它们显示出相当大的差异。实际上，执行以下查询就能发现问题所在：

DEFINE
    MEASURE Sales[Original Amount 1] =
        SUMX (
            Sales,
            Sales[Exchange Rate] * Sales[Quantity] * Sales[Net Price]
        )
    MEASURE Sales[Original Amount 2] =
        SUMX (
            VALUES ( Sales[Exchange Rate] ),
            Sales[Exchange Rate] * [Sales Amount]
        )

EVALUATE
ADDCOLUMNS (
    SUMMARIZE (
        Sales,
        'Currency'[Currency Code]
    ),
    "Original Amount 1", [Original Amount 1],
    "Original Amount 2", [Original Amount 2]
)

“Original Amount 1”（原始金额1）和“Original Amount 2”（原始金额2）的结果相差很大。在这个特定情况下，因为我们处理的是固定小数，所以引入的误差与在交易层面进行转换时产生的误差相同。但是，这只是我们计算的一个特例。一般来说，这两个数字可能大相径庭。

你可能会感到惊讶、愤怒，甚至困惑。然而，这种行为是完全符合预期的。我们有点理所当然地认为“Original Amount 1”的值是正确的，但这仅仅因为它是我们看到的第一个数字。两个值都是正确的，也就是说，它们都包含了一些舍入误差。而且，这两个值都来源于本身就是经过舍入的值。当在数据库中进行原始货币之间的转换时，就已经产生了舍入误差。这个误差会一直传播下去。然后，每当我们进行一次乘法运算，误差就会变大。当我们对包含微小误差的2.25亿个值进行求和时，结果肯定是不正确的。到目前为止，你看到的所有数字都是不正确的。说实话，由于数据库层面最初的舍入，我们无法得到一个完美的数字。

此外，与最初的2.25亿次乘法相比，最新的公式将乘法次数减少到了6000次。因此，最后一个公式的值比原始公式的值更准确（或者说错误更少？）。

在大型模型中，这些问题经常发生。在过程的某个阶段引入的微小误差最终可能会导致更大的误差。这是可以预见的，优秀的商业智能（BI）专业人员需要充分了解这些问题，以便向用户解释。

如果你仍然感到惊讶，请查看以下查询，它显示了同一代码的两个不同版本，唯一的区别是乘法运算的顺序：

DEFINE
    MEASURE Sales[Original Amount 1] =
        SUMX (
            Sales,
            Sales[Exchange Rate] * Sales[Quantity] * Sales[Net Price]
        )
    MEASURE Sales[Original Amount 2] =
        SUMX (
            Sales,
            Sales[Exchange Rate] * Sales[Net Price] * Sales[Quantity]
        )

EVALUATE
ADDCOLUMNS (
    SUMMARIZE (
        Sales,
        'Currency'[Currency Code]
    ),
    "Original Amount 1", [Original Amount 1],
    "Original Amount 2", [Original Amount 2],
    "USD Amount", [Sales Amount]
)

以下是结果：

再次强调，这些数字是不同的，而且它们可能更准确。关于舍入问题，还有很多可能会让你惊讶的例子。

然而，请注意，如果这些数字让你感到惊讶……这意味着你还没有了解计算机如何存储数字、如何进行数学运算，以及工程师为了解决即使是简单的计算问题而不得不面对的许多极其困难的挑战。一旦你掌握了这些概念，这些微小的误差就不再令人惊讶了。了解现实情况只会让你成为一个更好的开发者，并让你有能力避免浪费时间去修复一个——简单地说——无法修复的数字。

​

---

如果您想深入学习微软Power BI，欢迎登录网易云课堂试听学习我们的“从Excel到Power BI数据分析可视化”系列课程。或者关注我们的公众号（PowerPivot工坊）后猛戳”在线学习”。



​

​
长按下方二维码关注“Power Pivot工坊”获取更多微软Power BI、PowerPivot相关文章、资讯，欢迎小伙伴儿们转发分享~

Power Pivot工坊