首页 > 其他分享 >DAX 底层计算过程演示——ADDCOLUMNS + 复杂上下文环境

DAX 底层计算过程演示——ADDCOLUMNS + 复杂上下文环境

时间:2024-05-28 12:58:01浏览次数:25  
标签:ADDCOLUMNS Dates Month Num 2019 Year 上下文 DAX

关于 DAX 理论的资料很多,DAX 理论帮助我们理解和预测 DAX 的计算结果,但 DAX 在底层实际是怎么计算的却少有人谈及。本篇以一段在复杂上下文环境中通过 ADDCOLUMNS 进行计算的 DAX 查询代码为观察对象,使用 DAX Stuido 查看查询计划,展示其在存储引擎中执行了哪些数据查询,揭示公式引擎如何组装这些数据最终得出计算结果。

数据环境

数据文件 下载

1、DFact——数据表,由 Date 和 Amount 两列组成

image

2、Dates——日期表,由 DAX 代码生成

image

3、两张表通过 [Date] 列建立关系,Dates[Date](1) — (*) DFact[Date]

image

4、度量值

SumAmount = SUM('DFact'[Amount])
SumxBug = SUMX(VALUES(Dates[Year]),[SumAmount])

DAX 查询代码

EVALUATE
CALCULATETABLE (
    ADDCOLUMNS ( VALUES ( Dates[Year] ), "SumxBug", [SumxBug] ),
    TREATAS (
        { ( 2019, 1 ), ( 2019, 2 ), ( 2020, 11 ), ( 2020, 12 ) },
        'Dates'[Year],
        'Dates'[Month Num]
    )
)

CALCULATETABLE​ 以 TREATAS​ 作为调节器,为ADDCOLUMNS​ 提供筛选上下文,ADDCOLUMNS​ 在该上下文中扫描 Dates[Year]​并计算度量值[SumxBug]

运行结果如下:

image

DAX 理论

先用 DAX 理论分析一下计算结果。

下面的图中使用红色数字标记了某些列,这是故意的,后面有用处。

1、CALCULATETABLE​ 以 TREATAS​ 作为调节器,为 ADDCOLUMNS​提供外部筛选器。

image

2、在外部筛选上下文中计算 ADDCOLUMNS​ 一参 VALUES(Dates[Year])

image

3、ADDCOLUMNS​以 2# 数据集为迭代对象,逐行读取行上下文,在外部筛选上下文和行上下文中计算[SumxBug]
SumxBug = SUMX(VALUES(Dates[Year]),[SumAmount])

[SumxBug] 将行上下文转换为筛选上下文,并覆盖外部筛选上下文。

image

4、计算 SUMX(VALUES(Dates[Year]),[SumAmount])

迭代 Dates[Year]​ 并计算 [SumAmount]​ ,这里出现第二层行上下文

image

5、合并成最终的结果

完整的分析流程如下

image

注意在这个过程中存在多层行上下文的事实。

DAX 底层计算过程

以下内容将涉及 DAX 引擎和查询计划,相关资料可查看《权威指南》第二版第17、19章。

存储引擎部分

在 DAX Studio 中通过 Server Timings 窗口,了解到存储引一共执行了 3个 xmSQL 查询。

image

注意 xmSQL 与标准 SQL 不同,xmSQL 语句中的 SELECT 子句中出现的列会自动进行分组,举个例子,有 xmSQL 代码

// xmSQL
SELECT customer[country], customer[state], SUM ( customer[amount] )
FROM customer

与下面的标准 SQL 代码的作用是一样的

// 标准 SQL
SELECT customer.country, customer.state, SUM ( customer.amount )
FROM customer
GROUP BY customer.country, customer.state

为了方便后文指代,这里将这 Server Timings 窗口中的 3 次查询从上往下分别命名为 VQ1、VQ2、VQ3,每个查询的代码、作用和数据如下。

1、VQ1

( 'Dates'[Year], 'Dates'[Month Num] ) IN { ( 2020, 12 ) , ( 2019, 2 ) , ( 2020, 11 ) , ( 2019, 1 ) }​为条件,从 Dates 表中分组获取 Year 列上的值

// xmSQL VQ1
SELECT
    'Dates'[Year]
FROM 'Dates'
WHERE
     ( 'Dates'[Year], 'Dates'[Month Num] ) IN { ( 2020, 12 ) , ( 2019, 2 ) , ( 2020, 11 ) , ( 2019, 1 ) };

查询结果包含1个字段列

image

2、VQ2

'Dates'[Year] IN ( 2019, 2020 ) VAND 'Dates'[Month Num] IN ( 12, 1, 2, 11 ) ​为条件,从 DFact 的扩展表中按 Dates[Year] 分组汇总 SUM(DFact[Amount])

// xmSQL VQ2
SELECT
    'Dates'[Year],
    SUM ( 'DFact'[Amount] )
FROM 'DFact'
    LEFT OUTER JOIN 'Dates'
        ON 'DFact'[Date]='Dates'[Date]
WHERE
    'Dates'[Year] IN ( 2019, 2020 ) VAND
    'Dates'[Month Num] IN ( 12, 1, 2, 11 ) ;

查询结果包含1个字段列,1个值列,这里用 Row1 命名该值列

image

3、VQ3,F1V0

'Dates'[Year] IN ( 2019, 2020 ) VAND 'Dates'[Month Num] IN ( 12, 1, 2, 11 ) ​为条件,从 Dates 表中分组获取 Year 列上的值

// xmSQL VQ3
SELECT
    'Dates'[Year]
FROM 'Dates'
WHERE
    'Dates'[Year] IN ( 2019, 2020 ) VAND
    'Dates'[Month Num] IN ( 12, 1, 2, 11 ) ;

查询结果包含1个字段列
image

注意在 3 次查询中, VQ1 与 VQ3 的查询结果完全相同,但 WHERE 子句内容不同。

公式引擎部分

在 DAX Studio 中通过 Query Plan 窗口可以查看逻辑查询计划和物理查询计划
image

逻辑查询计划略过,物理查询计划完整截图如下:

在这里插入图片描述

按照缩进关系画出树状线,方便了解结构层次。

AddColumns: IterPhyOp LogOp=AddColumns IterCols(3, 4)('Dates'[Year], ''[Total])
├── Spool_Iterator<SpoolIterator>: IterPhyOp LogOp=Scan_Vertipaq IterCols(3)('Dates'[Year]) #Records=2 #KeyCols=10 #ValueCols=0
│   └── ProjectionSpool<ProjectFusion<>>: SpoolPhyOp #Records=2
│       └── Cache: IterPhyOp #FieldCols=1 #ValueCols=0
│           └── Spool_Iterator<SpoolIterator>: IterPhyOp LogOp=TreatAs IterCols(0, 1)('Dates'[Year], 'Dates'[Month Num]) #Records=4 #KeyCols=2 #ValueCols=0
│               └── AggregationSpool<GroupBy>: SpoolPhyOp #Records=4
│                   └── TreatAs: IterPhyOp LogOp=TreatAs IterCols(0, 1)('Dates'[Year], 'Dates'[Month Num])
│                       └── TableCtor: IterPhyOp LogOp=TableCtor IterCols(0, 1, 2)(''[Value1], ''[Value2], ''[])
└── SpoolLookup: LookupPhyOp LogOp=SumX LookupCols(3)('Dates'[Year]) Integer #Records=2 #KeyCols=1 #ValueCols=1 DominantValue=BLANK
    └── AggregationSpool<Sum>: SpoolPhyOp #Records=2
        └── CrossApply: IterPhyOp LogOp=Sum_Vertipaq IterCols(4)('Dates'[Year])
            ├── Spool_MultiValuedHashLookup: IterPhyOp LogOp=Scan_Vertipaq LookupCols(4)('Dates'[Year]) IterCols(3)('Dates'[Year]) #Records=2 #KeyCols=10 #ValueCols=0
            │   └── ProjectionSpool<ProjectFusion<>>: SpoolPhyOp #Records=2
            │       └── Cache: IterPhyOp #FieldCols=1 #ValueCols=0
            │           ├── Spool_Iterator<SpoolIterator>: IterPhyOp LogOp=TreatAs IterCols(0, 1)('Dates'[Year], 'Dates'[Month Num]) #Records=4 #KeyCols=2 #ValueCols=0
            │           │   └── AggregationSpool<GroupBy>: SpoolPhyOp #Records=4
            │           │       └── TreatAs: IterPhyOp LogOp=TreatAs IterCols(0, 1)('Dates'[Year], 'Dates'[Month Num])
            │           │           └── TableCtor: IterPhyOp LogOp=TableCtor IterCols(0, 1, 2)(''[Value1], ''[Value2], ''[])
            │           └── Spool_Iterator<SpoolIterator>: IterPhyOp LogOp=Scan_Vertipaq IterCols(3)('Dates'[Year]) #Records=2 #KeyCols=10 #ValueCols=0
            │               └── ProjectionSpool<ProjectFusion<>>: SpoolPhyOp #Records=2
            │                   └── Cache: IterPhyOp #FieldCols=1 #ValueCols=0
            │                       └── Spool_Iterator<SpoolIterator>: IterPhyOp LogOp=TreatAs IterCols(0, 1)('Dates'[Year], 'Dates'[Month Num]) #Records=4 #KeyCols=2 #ValueCols=0
            │                           └── AggregationSpool<GroupBy>: SpoolPhyOp #Records=4
            │                               └── TreatAs: IterPhyOp LogOp=TreatAs IterCols(0, 1)('Dates'[Year], 'Dates'[Month Num])
            │                                   └── TableCtor: IterPhyOp LogOp=TableCtor IterCols(0, 1, 2)(''[Value1], ''[Value2], ''[])
            └── Spool_Iterator<SpoolIterator>: IterPhyOp LogOp=Sum_Vertipaq IterCols(4)('Dates'[Year]) #Records=2 #KeyCols=10 #ValueCols=1
                └── ProjectionSpool<ProjectFusion<Copy>>: SpoolPhyOp #Records=2
                    └── Cache: IterPhyOp #FieldCols=1 #ValueCols=1
                        └── Spool_Iterator<SpoolIterator>: IterPhyOp LogOp=TreatAs IterCols(0, 1)('Dates'[Year], 'Dates'[Month Num]) #Records=4 #KeyCols=2 #ValueCols=0
                            └── AggregationSpool<GroupBy>: SpoolPhyOp #Records=4
                                └── TreatAs: IterPhyOp LogOp=TreatAs IterCols(0, 1)('Dates'[Year], 'Dates'[Month Num])
                                    └── TableCtor: IterPhyOp LogOp=TableCtor IterCols(0, 1, 2)(''[Value1], ''[Value2], ''[])

为了更好地了解物理查询计划的结构,按照层次关系绘制流程结构图。
在这里插入图片描述

图中每个方块各代表一行代码,对应关系如下图所示。

image

绿色方块代表数据操作,棕色方块代表 xmSQL 查询的数据缓存,蓝色方块代表其他

底层计算过程

在物理查询的流程结构图上标出各部分生成和组装的数据,从下往上看。

建议右键点击图片,选择【在新标签页中打开图像】以查看大图,详细了解各个部分的说明。

image

几个说明

1、VQ2 的 WHERE 子句

回顾一下存储引擎执行的 xmSQL VQ2 的内容,WHERE 子句以 Dates[Year] IN ( 2019, 2020 )​ 和 Dates[Month Num] IN ( 12, 1, 2, 11 )​做条件

SELECT
    'Dates'[Year],
    SUM ( 'DFact'[Amount] )
FROM 'DFact'
    LEFT OUTER JOIN 'Dates'
        ON 'DFact'[Date]='Dates'[Date]
WHERE
    'Dates'[Year] IN ( 2019, 2020 ) VAND
    'Dates'[Month Num] IN ( 12, 1, 2, 11 ) ;

DAX 查询代码中,CALCULATETABLE​ 使用 TREATAS​ 构建的固化筛选器作为 ADDCOLUMNS​ 的筛选上下文,为什么这里的 xmSQL 没有使用固化筛选器,而是将 Year 和 Month Num 拆开了?

如果以固化筛选器为条件,这里的 WHERE 子句应该是这样:

WHERE ( 'Dates'[Year], 'Dates'[Month Num] ) IN { ( 2020, 12 ) , ( 2019, 2 ) , ( 2020, 11 ) , ( 2019, 1 ) }

对照上一节的 DAX 理论分析,真正需要的计算发生在 [SumAmount] = SUM(DFact[Amount])​ 这部分,这一部分的计算上下文是什么?

是下图中红框标记出来的这部分,包括 Year = {2019,2020} 和 Month Num = {1, 2, 11, 12},DAX 底层根据对代码的理解,对存储引擎申请的数据查询直接使用了该条件作为 WHERE 子句。

image

2、为什么 VQ1 和 VQ3 重复查询 Dates[Year]

理论分析部分出现过两个迭代器,均以 Dates[Year] 为迭代对象,形成两套行上下文。

回顾下这两个迭代器的计算上下文:

1)第一个迭代器 ADDCOLUMNS计算一参时

image

以 TREATAS 构建的固化筛选器为条件,这对应了 VQ1 的 WHERE 子句

// xmSQL VQ1
SELECT
    'Dates'[Year]
FROM 'Dates'
WHERE
     ( 'Dates'[Year], 'Dates'[Month Num] ) IN { ( 2020, 12 ) , ( 2019, 2 ) , ( 2020, 11 ) , ( 2019, 1 ) };

2)第二个迭代器 SUMX计算一参时

image

以 [Year] = {2019,2020} AND [Month Num] = {1, 2, 11, 12} 为条件,对应了 VQ3 的 WHERE 子句

// xmSQL VQ3
SELECT
    'Dates'[Year]
FROM 'Dates'
WHERE
    'Dates'[Year] IN ( 2019, 2020 ) VAND
    'Dates'[Month Num] IN ( 12, 1, 2, 11 ) ;

由此可知:

  • VQ1 对应了 ADDCOLUMNS(VALUES(Dates[Year]),...)​中的 VALUES(Dates[Year])​,对应第一层行上下文

  • VQ3 对应了 SUMX(VALUES(Dates[Year]),...)​ 中的 VALUES(Dates[Year])​,对应第二层行上下文

存储引擎需要执行两个 xmSQL 查询,重复获取 Dates[Year] 的值,正是基于有两个迭代器需要在不同的上下文中扫描Dates[Year]。

3、物理查询计划流程结构图中 3# 号框在做什么?

image

从数据表来看,这一步的作用是将 VQ2 与 VQ3 通过 [Year] 的 hash 值进行匹配合并,在本案例中,VQ2 和 VQ3 是一对一的关系( VQ2(1)—(1)VQ3),合并后的结果有2行。

4、为什么要将 Year(4) 和 Year(3) 合并到一张表里?

因为再往上,ADDCOLUMNS​ 操作符需要使用 Year(3) 查找对应值,而 VQ2 数据缓存中保存的是 Year(4),不是 Year(3),无法直接进行查找。虽然是同一列,但 DAX 底层会区分来自不同来源的同一列,Year(4) 来自 VQ2,Year(3) 来自 VQ1。

3#号框将 Year(4) 和 Year(3) 匹配合并到一张表中,使得 ADDCOLUMNS​ 可以从其中通过 Year(3) 找到对应的 Row1 值,形成最终的结果表。

image

DAX 理论与实际计算过程的关系

有了以上分析后,现在可以将 DAX 理论、存储引擎、公式引擎3者的关系,标记在同一张图上。
(使用鼠标右键点击该图片,选择 “在新标签页中打开图像” 以查看大图)
在这里插入图片描述
​‍
以上便是关于这段 DAX 查询代码,CALCULATETABLE​ 以 TREATAS​ 作为调节器,为ADDCOLUMNS​ 提供筛选上下文,ADDCOLUMNS​ 在该上下文中扫描 Dates[Year]​并计算度量值[SumxBug]​ 的过程。

其他

这里我给度量值取的名称叫 SumxBug,是因为如果在 PowerBI 搭建报表完成完全相同的计算时会出现 bug,得到不一样的结果,如下图所示

image

在计算 2019 年的 [SumxBug] 时,实际是将 2019 年 1、2 月和 2020 年 1、2 月的 Amount 累加了起来,1+2+13+14 = 30

在计算 2020 年的 [SumxBug] 时,实际是以 2019 年 11、12 月和 2020 年 11 月、12 月的 Amount 来算的,11+12+23+24 = 70

PowerBI 计算出的结果与 DAX 理论预测的结果不符,与 CALCULATETABLE​ + ADDCOLUMNS​ 计算得到的结果不一致,这是由于 PowerBI 中计算报表时使用了 SUMMARIZECOLUMNS​,关于 SUMMARIZECOLUMNS​ 的 bug,可以跳转到下面这篇文章进行了解。

《SUMMARIZECOLUMNS 的疑似 bug(1)引言》

标签:ADDCOLUMNS,Dates,Month,Num,2019,Year,上下文,DAX
From: https://blog.csdn.net/Randvalue/article/details/139251092

相关文章

  • 原子上下文与中断上下文
    原子上下文与中断上下文目录原子上下文与中断上下文原子上下文(AtomicContext)中断上下文(InterruptContext)差异与联系原子上下文(AtomicContext)原子操作是一个在多线程环境中“不可分割”的操作,即该操作要么全部完成,要么完全不执行,不会出现只执行一部分的情况。原子操作在多线......
  • EAS_WEB获取传参,获取上下文,获取控制单元
    varimp=JavaImporter();imp.importPackage(Packages.java.lang);imp.importPackage(Packages.org.apache.commons.lang3);imp.importPackage(Packages.com.kingdee.bos.webframework.context);imp.importPackage(Packages.com.kingdee.eas.util.app);imp.importPackage(......
  • 原子上下文.中断上下文
    原子上下文在计算机科学和操作系统设计中,"原子上下文"(AtomicContext)通常指的是一个操作或一组操作,它们在执行时不会被其他操作中断或分割。这意味着这些操作要么完全执行,要么根本不执行,不会出现中间状态。原子操作是并发编程和多线程编程中的一个关键概念,它确保了数据的一致......
  • 原子上下文和中断上下文
    原子上下文内核的一个基本原则就是:在中断或者说原子上下文中,内核不能访问用户空间,而且内核是不能睡眠的。也就是说在这种情况下,内核是不能调用有可能引起睡眠的任何函数。一般来讲原子上下文指的是在中断或软中断中,以及在持有自旋锁的时候。内核提供了四个宏来判断是否处于这几......
  • 原子上下文、进程上下文和中断上下文
    进程上下文、中断上下文及原子上下文转自:进程上下文、中断上下文及原子上下文_知秋一叶-CSDN博客​谈论进程上下文、中断上下文、原子上下文之前,有必要讨论下两个概念:a--上下文​上下文是从英文context翻译过来,指的是一种环境。相对于进程而言,就是进程执行时的环......
  • 中断上下文和原子上下文
    目录中断上下文1、定义2、组成3、特点4、中断上下文的实现5、中断上下文和进程上下文的区别原子上下文1、定义2、特点3、应用场景4、实现方式5、与进程上下文和中断上下文的区别中断上下文1、定义中断上下文(InterruptContext)是指当CPU接收到硬件设备的中断请求时,CPU会暂停当......
  • 鸿蒙HarmonyOS实战-Stage模型(应用上下文Context)
    ......
  • .net6中使用EF core多个上下文迁移方式
    正常上下文在复制一个一模一样的上下文appsettings.json添加两个数据库连接字符串 Program.cs里边一样添加两个 控制台迁移命令必须加上-Context后边跟的是我们上下文的名称Add-MigrationMyMigration-ContextMYDBContext222保存到数据库命令一样Update-Data......
  • JavaScript中执行上下文和执行栈是什么?
    一、执行上下文简单的来说,执行上下文是一种对Javascript代码执行环境的抽象概念,也就是说只要有Javascript代码运行,那么它就一定是运行在执行上下文中执行上下文的类型分为三种:全局执行上下文:只有一个,浏览器中的全局对象就是 window对象,this 指向这个全局对象函数执行上下......
  • dbt fromyaml 上下文方法简单说明
    fromyaml上下文方法可以用来加载yaml内容,属于一个工具类,比如automate-dv就使用了不少方法参考使用{%-setinfo-%}source_model:raw_staging:"raw_customer"derived_columns:SOURCE:"!1"LOAD_DATETIME:"CRM_DATA_INGESTION_TIME"E......