SUMX 在 SUMARIZECOLUMNS 总计行的底层计算过程
上一篇介绍了 SUMMARIZECOLUMNS 在特定情况下计算出的结果不符合 DAX 理论预测,这一篇我们开开始探究 DAX 在底层究竟是如何计算出这些结果的,主要的方法是用 DAX 查询复现该现象,通过观察物理查询计划了解底层计算步骤,试图从中定位到问题所在。
SUMMARIZECOLUMNS 的疑似 bug(1)引言
SUMMARIZECOLUMNS 的疑似 bug(2)SUMX 在总计行
SUMMARIZECOLUMNS 的疑似 bug(3)总计行是否关键点
SUMMARIZECOLUMNS 的疑似 bug(4)验证触发条件
SUMMARIZECOLUMNS 的疑似 bug(5)最终结论
现象描述
红框处,2019年和2020年总计行的计算结果为:
- 2019年的总计显示为 30,其计算的是 2019年1月、2019年2月、2020年1月、2020年2月,这4个月的 Amount 之和:1+2+13+14 = 30
- 2020年的总计显示为 70,其计算的是 2019年11月、2019年12月、2020年11月、2020年12月,这4个月的 Amount 之和:11+12+23+24 = 70。
可以用以下 DAX 查询的结果来类比(并不是说本案例实际是这样执行的):
底层计算过程
为了弄清楚 PowerBI 在底层是怎么计算出这个结果的,需要我们深入到查询计划中去。首先提炼核心 DAX 查询代码。
PowerBI 在代码层面通过如下 DAX 查询代码计算报表中的矩阵:
DEFINE
VAR __DS0FilterTable = // 切片器提供
TREATAS(
{(2019, 1),
(2019, 2),
(2020, 11),
(2020, 12)},
'Dates'[Year],
'Dates'[Month Num]
)
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
'Dates'[Year], "IsGrandTotalRowTotal", // 按年汇总,即 矩阵最下方的总计
'Dates'[Month Num], "IsDM1Total"), // 按月汇总,即 年 总计
__DS0FilterTable, // 切片器提供的筛选器
"SumxBug", 'Measure'[SumxBug]
)
VAR __DS0PrimaryWindowed =
TOPN(
502,
__DS0Core,
[IsGrandTotalRowTotal],
0,
'Dates'[Year],
1,
[IsDM1Total],
0,
'Dates'[Month Num],
1
)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
[IsGrandTotalRowTotal] DESC, 'Dates'[Year], [IsDM1Total] DESC, 'Dates'[Month Num]
从该代码中将核心部分提出来,保持按月汇总,取消按年汇总(不是本次观察对象),得到如下的实验代码
DAX 查询
用以下 DAX 代码复现 30 和 70 的错误计算结果
EVALUATE
SUMMARIZECOLUMNS (
'Dates'[Year],
ROLLUPADDISSUBTOTAL ( 'Dates'[Month Num], "IsDM1Total" ),
TREATAS (
{ ( 2019, 1 ), ( 2019, 2 ), ( 2020, 11 ), ( 2020, 12 ) },
'Dates'[Year], 'Dates'[Month Num]
),
"SumxBug", [SumxBug]
)
运行结果中 [IsDM1Total] 为 True 、Year = 2019 和 Year = 2020 这两行,分别得到 30 和 70,符合报表计算结果。
存储引擎
Server Timings 界面记录了 4 次 Scan
存储引擎执行了 4 个 xmSQL 查询,从上往下分别是:
- VQ1,F2V1
// xmSQL VQ1
SELECT
'Dates'[Year],
'Dates'[Month Num],
SUM ( 'DFact'[Amount] )
FROM 'DFact'
LEFT OUTER JOIN 'Dates'
ON 'DFact'[Date]='Dates'[Date]
WHERE
'Dates'[Month Num] IN ( 12, 1, 2, 11 ) VAND
( 'Dates'[Year], 'Dates'[Month Num] ) IN { ( 2020, 12 ) , ( 2019, 2 ) , ( 2020, 11 ) , ( 2019, 1 ) };
从 DFact 的扩展表中,以 Dates[Year]、Dates[Month Num] 分组计算 SUM(DFact[Amount])
,筛选条件为:
1、‘Dates’[Month Num] IN ( 12, 1, 2, 11 )
2、( ‘Dates’[Year], ‘Dates’[Month Num] ) IN { ( 2020, 12 ) , ( 2019, 2 ) , ( 2020, 11 ) , ( 2019, 1 ) }
- VQ2,F2V0
// xmSQL VQ2
SELECT
'Dates'[Year],
'Dates'[Month Num]
FROM 'Dates'
WHERE
( 'Dates'[Year], 'Dates'[Month Num] ) IN { ( 2020, 12 ) , ( 2019, 2 ) , ( 2020, 11 ) , ( 2019, 1 ) };
从 Dates 表中,获取 Dates[Year]、Dates[Month Num] 的不重复值对,筛选条件为:
( ‘Dates’[Year], ‘Dates’[Month Num] ) IN { ( 2020, 12 ) , ( 2019, 2 ) , ( 2020, 11 ) , ( 2019, 1 ) }
- VQ3,F1V0
// xmSQL VQ3
SELECT
'Dates'[Year]
FROM 'Dates'
WHERE
( 'Dates'[Year], 'Dates'[Month Num] ) IN { ( 2020, 12 ) , ( 2019, 2 ) , ( 2020, 11 ) , ( 2019, 1 ) };
从 Dates 表中,获取 Dates[Year] 的不重复值,筛选条件与 VQ2 相同
( ‘Dates’[Year], ‘Dates’[Month Num] ) IN { ( 2020, 12 ) , ( 2019, 2 ) , ( 2020, 11 ) , ( 2019, 1 ) }
- VQ4,F2V1
SELECT
'Dates'[Year],
'Dates'[Month Num],
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 ) ;
与 VQ1 非常相似,从 DFact 的扩展表中,以 Dates[Year]、Dates[Month Num] 分组计算 SUM(DFact[Amount])
,筛选条件与 VQ1 有区别:
‘Dates’[Year] IN ( 2019, 2020 )
‘Dates’[Month Num] IN ( 12, 1, 2, 11 )
注意,从 VQ4 的 xmSQL 代码可以看出,WHERE 子句使用的条件,破坏了报表切片器构造的固化筛选器,该查询得到的结果中包含有 2019 年11、12月以及 2020 年1、2月的数据。
逻辑查询计划
Union: RelLogOp DependOnCols()() 0-4 RequiredCols(0, 1, 2, 3, 4)('Dates'[Year], 'Dates'[Month Num], ''[IsDM1Total], ''[SumxBug], ''[])
├── GroupSemiJoin: RelLogOp DependOnCols()() 0-3 RequiredCols(0, 1, 2, 3)('Dates'[Year], 'Dates'[Month Num], ''[IsDM1Total], ''[SumxBug])
│ ├── TreatAs: RelLogOp DependOnCols()() 0-2 RequiredCols(0, 1)('Dates'[Year], 'Dates'[Month Num])
│ │ └── TableCtor: RelLogOp DependOnCols()() 0-2 RequiredCols(0, 1, 2)(''[Value1], ''[Value2], ''[])
│ ├── Constant: ScaLogOp DependOnCols()() Boolean DominantValue=false
│ └── SumX: ScaLogOp MeasureRef=[SumxBug] DependOnCols(0, 1)('Dates'[Year], 'Dates'[Month Num]) Integer DominantValue=BLANK
│ ├── Scan_Vertipaq: RelLogOp DependOnCols(0, 1)('Dates'[Year], 'Dates'[Month Num]) 3-3 RequiredCols(0, 1, 3)('Dates'[Year], 'Dates'[Month Num], 'Dates'[Year])
│ └── Sum_Vertipaq: ScaLogOp DependOnCols(1, 3)('Dates'[Month Num], 'Dates'[Year]) Integer DominantValue=BLANK
│ ├── Scan_Vertipaq: RelLogOp DependOnCols(1, 3)('Dates'[Month Num], 'Dates'[Year]) 4-12 RequiredCols(1, 3, 6)('Dates'[Month Num], 'Dates'[Year], 'DFact'[Amount])
│ └── 'DFact'[Amount]: ScaLogOp DependOnCols(6)('DFact'[Amount]) Integer DominantValue=NONE
└── GroupSemiJoin: RelLogOp DependOnCols()() 0-3 RequiredCols(0, 1, 2, 3)('Dates'[Year], 'Dates'[Month Num], ''[IsDM1Total], ''[SumxBug])
├── TreatAs: RelLogOp DependOnCols()() 0-2 RequiredCols(0, 1)('Dates'[Year], 'Dates'[Month Num])
│ └── TableCtor: RelLogOp DependOnCols()() 0-2 RequiredCols(0, 1, 2)(''[Value1], ''[Value2], ''[])
├── Constant: ScaLogOp DependOnCols()() Boolean DominantValue=true
└── SumX: ScaLogOp MeasureRef=[SumxBug] DependOnCols(0)('Dates'[Year]) Integer DominantValue=BLANK
├── Scan_Vertipaq: RelLogOp DependOnCols(0)('Dates'[Year]) 3-3 RequiredCols(0, 3)('Dates'[Year], 'Dates'[Year])
└── Sum_Vertipaq: ScaLogOp DependOnCols(0, 3)('Dates'[Year], 'Dates'[Year]) Integer DominantValue=BLANK
├── Scan_Vertipaq: RelLogOp DependOnCols(0, 3)('Dates'[Year], 'Dates'[Year]) 4-12 RequiredCols(0, 3, 6)('Dates'[Year], 'Dates'[Year], 'DFact'[Amount])
└── 'DFact'[Amount]: ScaLogOp DependOnCols(6)('DFact'[Amount]) Integer DominantValue=NONE
逻辑结构图:
从逻辑查询计划来看,DAX 底层认为该计算可以由两部分通过 Union 合并而成:
- Union 左边分支,从 Line 7 的代码可以看出,这部分计算 Date[Year]、Date[Month Num] 分组下的
SUM(DFact[Amount])
,这正是各个非总计行 - Union 右边分支,从 Line 16 的代码可以看出,这部分计算 Date[Year] 分组下的
SUM(DFact[Amount])
,这正是两个总计行
物理查询计划
Union: IterPhyOp LogOp=Union IterCols(0, 1, 2, 3, 4)('Dates'[Year], 'Dates'[Month Num], ''[IsDM1Total], ''[SumxBug], ''[])
├── GroupSemijoin: IterPhyOp LogOp=GroupSemiJoin IterCols(0, 1, 2, 3)('Dates'[Year], 'Dates'[Month Num], ''[IsDM1Total], ''[SumxBug])
│ └── CrossApply: IterPhyOp LogOp=SumX IterCols(0, 1)('Dates'[Year], 'Dates'[Month Num])
│ ├── Spool_UniqueHashLookup: IterPhyOp LogOp=TreatAs LookupCols(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=SumX IterCols(0, 1)('Dates'[Year], 'Dates'[Month Num]) #Records=4 #KeyCols=2 #ValueCols=1
│ └── AggregationSpool<Sum>: SpoolPhyOp #Records=4
│ └── CrossApply: IterPhyOp LogOp=Sum_Vertipaq IterCols(1, 3)('Dates'[Month Num], 'Dates'[Year])
│ ├── Spool_MultiValuedHashLookup: IterPhyOp LogOp=Scan_Vertipaq LookupCols(1, 3)('Dates'[Month Num], 'Dates'[Year]) IterCols(0)('Dates'[Year]) #Records=4 #KeyCols=10 #ValueCols=0
│ │ └── ProjectionSpool<ProjectFusion<>>: SpoolPhyOp #Records=4
│ │ └── Cache: IterPhyOp #FieldCols=2 #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(1, 3)('Dates'[Month Num], 'Dates'[Year]) #Records=4 #KeyCols=10 #ValueCols=1
│ └── ProjectionSpool<ProjectFusion<Copy>>: SpoolPhyOp #Records=4
│ └── Cache: IterPhyOp #FieldCols=2 #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], ''[])
└── GroupSemijoin: IterPhyOp LogOp=GroupSemiJoin IterCols(0, 1, 2, 3)('Dates'[Year], 'Dates'[Month Num], ''[IsDM1Total], ''[SumxBug])
└── Spool_Iterator<SpoolIterator>: IterPhyOp LogOp=SumX IterCols(0)('Dates'[Year]) #Records=2 #KeyCols=1 #ValueCols=1
└── AggregationSpool<Sum>: SpoolPhyOp #Records=2
└── Spool_Iterator<SpoolIterator>: IterPhyOp LogOp=Sum_Vertipaq IterCols(0, 3)('Dates'[Year], 'Dates'[Year]) #Records=4 #KeyCols=2 #ValueCols=1
└── AggregationSpool<AggFusion<Sum>>: SpoolPhyOp #Records=4
└── CrossApply: IterPhyOp LogOp=Sum_Vertipaq IterCols(0, 3)('Dates'[Year], 'Dates'[Year])
├── Spool_MultiValuedHashLookup: IterPhyOp LogOp=TreatAs LookupCols(1)('Dates'[Month Num]) IterCols(0)('Dates'[Year]) #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], ''[])
└── Cache: IterPhyOp #FieldCols=2 #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], ''[])
└── Spool_Iterator<SpoolIterator>: IterPhyOp LogOp=Scan_Vertipaq IterCols(0, 3)('Dates'[Year], '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], ''[])
完整的流程结构图如下:
这个案例的物理查询计划同逻辑查询计划的流程结构相似,也是将整个计算区分成总计行和非总计行的计算最后再合并起来,简化表达为如下流程:
实际计算过程
仔细阅读物理查询计划后,对计算过程有了一定的了解,我把整体流程数据流大致演示一下(省略了几个分组的操作,对理解本案例并没有影响):
(在 DAX 底层计算过程中,经常能看到一些步骤通过合并查询,切换来自不同源的同一列,这些不同来源的列通过列后面的数字来标识,比如 Year(0),可能来自 TreatAS,而 Year(3) 则来自 VQ4,相同来源的列可以通过 Lookup 操作符进行匹配,不同来源的列则要通过 HashLookup 来匹配。 )
30 和 70 这两个计算结果由右侧【计算总计行】分支产生,需要重点理解下该部分的计算过程。
1、该分支从 VQ4 查询开始,前面讲过了,该数据缓存中包含有切片器未选择的 2019年11、12月 和 2020年1、2月相关数据。
2、中间的步骤是在切换 Year(3) 到 Year(0) ,Year(3) 来自 VQ4,Year(0)来自 TreatAs,具体的做法是将 VQ4 与 TreatAs 表构造器{ ( 2019, 1 ), ( 2019, 2 ), ( 2020, 11 ), ( 2020, 12 ) }
通过 [Month Num] 列进行匹配,比如 VQ4 中的 2020年1月 ,通过 [Month Num] = 1,从 TreatAs 中匹配到 Year(0)=2019,于是出现了红框这行数据
3、按 Year(0) 进行分组汇总,于是在 Year(0) = 2019 组内,实际包含的是 2019年1、2月 和 2020年1、2月的数据,最后的计算结果是 1+2+13+14 = 30
问题分析
通过上述分析,可以了解到如下情况:
- SUMMARIZECOLUMNS 分别计算总计行和非总计行,最后合并起来得到最终结果
- VQ4 中的 WHERE 子句使用的 Dates[Year] And Dates[Month Num] ,破坏了切片器构成的固化筛选器,导致查询结果中不仅有 2019年1、2月,2022年11、12月,还包含了 2019年11、12月、2020年1月、2月
- 底层计算时按照固化筛选器中的 [Month Num] 去匹配 [Year] ,导致最后分组计算时,在2019年分组下包含有 2020年的数据。
由此可知,VQ4 WHERE 子句Dates[Year] IN ( 2019, 2020 ) VAND Dates[Month Num] IN ( 12, 1, 2, 11 )
破坏了 TREATAS ({ ( 2019, 1 ), ( 2019, 2 ), ( 2020, 11 ), ( 2020, 12 ) },'Dates'[Year], 'Dates'[Month Num]))
构造的固化筛选器,是导致总计行计算错误的关键原因。
DAX 底层并不会像 DAX 理论那样逐步去分析计算各个筛选上下文之间的关系,而是直接根据代码得出它需要使用的 xmSQL 数据再进行后续处理。DAX 理论是用来指导用户让用户可以预测出于代码的预测结果,应当与 DAX 底层计算结果保持一致。
显然在本案例中,DAX 理论与实际计算结果无法保持一致且无法调和,DAX 理论无法解释 DAX 底层为什么会使用 VQ4 这样的数据去计算本案例中的 DAX 查询代码。
为了定位问题发生的根源,做一些猜测,然后设计实验排查,希望运气够好能够早点定位到关键因素:
- SUMMARIZECOLUMNS 与 迭代器 的配合,本案例中,尤其是在计算总计行时
- SUMMARIZECOLUMNS 中 参数作用于参数表的方式
- …待补充
实验设计
猜想1好验证,从报表中把 [Month Num] 去掉,直接在 [Year] 这个维度上进行计算,看看没有了总计行那部分的计算,结果会不会有所不同。
猜想2不好验证,需要观察更多的数据计算表现,搜集更多信息
那就在下一篇中来验证猜想1吧。