跳至正文

【产品对比】Tableau VS. Power BI: LOD与SUMARIZE案例对比

最近阅读“the Definitive Guide to DAX”英文原著(中文译本《DAX权威指南》),第四章中讲解了一个“客户平均年龄”的典型案例,借此介绍了SUMMARIZE函数。在Tableau中,我们可以用LOD函数完成相同的计算。

Tableau 的LOD表达式,可以与SQL的“嵌套聚合查询”相对应,它首先是嵌套查询(nested query),其实还必须是聚合查询(aggregate query)。因此,在Power BI中,类似的案例就需要使用Summarize、AddColumns或SummarizeColumns等具有分组聚合功能的函数才能实现。

“多维分析”是业务分析的经典、高级场景,而Tableau和Power BI作为商务智能领域的“BI双擘”,可以说走向了明显不同的方向——喜乐君在之前总结了“Power BI向左,Tableau向右”。为了帮助更多人理解二者处理“多个详细级别问题”时差异,本文结合书中的案例整理如下。

  • 问题分析
  • 方案1:Power BI Summarize函数
  • 方案2:Tableau Include LOD表达式

新用户慎入,欢迎大家留言。

一、问题解析:问题结构与业务背景

基于超市销售的交易数据明细(订单ID*产品ID),分析指标“客户平均年龄”(the average age of customers)。

完整的问题是:每个产品类别 客户平均消费年龄

按照喜乐君在《数据可视化分析》中介绍的问题解析方法,这里维度是“的”左侧的“产品类别”,度量是“的”字右侧的“客户平均购买年龄”,这里没有筛选范围。

分析的本质是聚合,在这个问题中,“平均年龄”仅需要对客户的年龄做平均值聚合(average)。但是,“平均值”其实隐含了“数量”这一条件,只有在一个客户、一个年龄仅仅购买过一次时,这种直接的聚合才能解决问题,否则一个客户一个月中的多次购买,就会被多次聚合,相当于为它的年龄增加了权重(来的次数越多,对“平均年龄”的影响越大)。书中的解释如下:

Compute the average age of customers at the time of sale, counting each customer only once if they made multiple purchases at the same age .

the Definitive Guide to DAX

为了解决这个问题,技术上有两个方案:

  • 要么预先把交易明细表聚合到足够简单的详细级别(比如,预先生成“每个客户、每个年龄的销售总和、订单次数”这样的聚合表),用事先准备的数据表再次平均完成上述题目。这是缺乏高级计算的BI工具的方案,相当于用ETL的方式弥补高级计算的不足。如果用SQL,也可以把这个预先聚合过程保存为view视图,本质也是ETL。
  • 要么,在分析过程中,借助于高级计算创建临时的聚合表,然后二次聚合。在SQL中,可以用“嵌套聚合查询”完成这个过程,在Power Bi中,可以使用Summarize函数创建临时数据表(表函数),它们都是“query table from table”的思路,对于业务用户较难理解。在Tableau中,可以使用LOD表达式完成“指定详细级别的预先聚合”;

可见,这个问题的难点是,如何通过高级计算消除重复;或者说,在最终的问题聚合之前,还隐藏包含了一个预先的聚合。

接下来的案例结束中,Power BI数据中采用color字段作为维度,Tableau超市数据中使用子类别字段作为维度,从而完成“各个产品类别的客户平均消费年龄”。

读者可以使用SQL完成类似的操作,欢迎好评,喜乐君将选择最佳答案,赠送图书一本。

二、DAX的Summarize临时聚合表方案

Power BI中,计算分为“计算列”和“度量值”,前者可以理解为数据表明细级别的数据准备,后者可以理解为围绕问题的聚合。在这个题目中,我们先要计算每个客户在每次消费时的当下年龄,这个计算是和最终问题无关的、是在数据表明细行中有意义的、可以对应到具体业务过程的计算,因此要用计算列(calculated column)完成。

在PBI中,先使用计算列计算“每个客户消费时的当下年龄”。它等于出生日期和订单日期的年数间隔。如果销售交易数据表中没有出生日期(通常是没有的),还需要可以使用RELATE函数从其他数据表中引用。如下所示:

SALES[customer Age] = 
  DATEDIFF(                      --compute the difference between  计算如下的日期差异:   
  RELATED(Customer[birth Date] ),     -- the customer's birth date    客户出生日期  
  Sales[Order Date],                  -- and the order date        和订单日期
  YEAR                                -- in year               查看间隔年度
)

从功能上看,“计算列”的结果字段,接下来可以等价于数据表字段,既可以作为维度,又可以被聚合成为度量。 很多初学者会尝试直接用用如下的计算作为最终问题的度量:

-- 错误方案1
Avg Customer Age Wrong := 
AVERAGE (SALES[customer Age] )

不过,直接计算上述字段的平均是错误的,它会重复计算一个客户在同一时间的多次购买——一个人会被计算很多遍。这个前面已经介绍。

官方还介绍了一种错误的做法:使用DISTINCT函数,返回年龄的不重复值,而后计算平均。

-- 错误方案2 
Avg Customer Age Wrong :=    
AVERAGEX (             -- iterate on the distinct values of customer age 客户年龄平均值
    DISTINCT (Sales[customer Age]),    -- 返回客户年龄的唯一数据集
    SALES[customer Age] 
)

相比上一种错误的做法,它会把相同年龄的多个客户作为一个值计算,相比上一种错误做法(客户年龄被多次计算),这里的数据就会太少(相同年龄的客户会被忽视)。

比如,喜乐君和小王同年同月出生(年龄一样18岁),并在同一个时间多次购买了同一个产品类别(比如家具)的产品,那么第一种直接平均的方法中,喜乐君和小王都会被计算多遍;而在第二种DISTINCT的计算中,喜乐君和小王两个人会在“家具”分类下,仅仅出现一个18被平均。可见,前者太多,后者太少;或者说,前者的计算过于具体,后者的计算过于抽象,以至于失真。

正确的方案是,先使用“DAX表函数”消除重复,预先完成“每个客户、不同年龄的交叉表”,如下使用了SUMMARIZE函数;而后在上述临时聚合表基础上,再次聚合平均值。如下所示:

-- 书中的正确方法
Avg Customer Age correct:= 
AVERAGEX (                    -- iterate on  迭代:
   SUMMARIZE(                 --all the existing combination 针对如下所有组合的数据表:
       Sales,                  -- that exist in Sales   在sales明细表中,
        Sales[customer ID],    -- of the customer id and  每一个客户ID 和
        Sales[customer Age],    -- the customer age      客户年龄
    )
SALES[customer Age] )          -- and average the customer's age 计算客户年龄的均值
)

注意,这里SUMMARIZE的变量是数据表和维度字段(客户ID和客户年龄——来自calculated column)。 而当视图中行字段是Color时,行字段也会影响上述的聚合——因此,一个人在不同类别有购买,年龄就会在不同类别下分别计算。用DAX的话说,视图的维度也是DAX度量值计算的“上下文/背景”(filter context),上述Summarize中已经预先包含了客户ID和客户年龄两个维度,此时再引用视图中的分类字段,就变成了三个维度字段的预先聚合。

喜乐君把这个过程划分为三个步骤,其中最难理解的是第三步,涉及到“上下文转换”“嵌套”等多个专业知识。

  • 第一步:计算每一行中,客户的消费年龄 (计算列)
  • 第二步:生成临时表:维度是产品类别(color)*客户ID*客户年龄 (表函数)
  • 第三步:在上述临时表基础上,以产品类别(color)为依据,计算客户年龄均值(忽略客户ID) (上下文转换)

如下图所示,介绍了原书中的说明。

相信很多IT用户看到这里也是一头雾水,业务用户则鲜有半小时内能完整理解题目的DAX实现的——至少喜乐君本人就花了半个小时以上的时间。

相比之下,如果改用SQL的嵌套查询或者view视图,这个问题会更加简单;而使用Tableau的LOD表达式,“表函数”降维变成了更易于理解的“字段函数”,所以更容易被业务用户理解和接受。

三、LOD表达式:Tableau 的解答方法

如果是Tableau中,这个问题的过程也是一样的,首先行级别计算计算订单消费时的客户年龄;其次聚焦客户年龄,避免多次计算。

  • 第1步,使用datediff计算客户消费时的年龄 :
  • 第2步,创建视图(这里使用子类别代替上面的color)。

这里的难点也是计算。

年龄直接平均显然是不对的(一个客户同一年的多次购买会全部参与平均值聚合)(图中第一列)。

如果指定“每个子类别、每个客户”呢,假设喜乐君在20岁买过3次打印机,小王在18岁买过4次打印机此时的计算,指定子类别、客户ID计算年龄均值时,喜乐君和小王会被分别聚合3次和4次——购买频次变成了权重,这不是我们这里想要的(下图第二列)。

正确的逻辑是,先获得“同一个客户同一年龄中的购买”问题,即指定子类别、客户、年龄返回唯一值——这里使用AVG或者MIN都可以——目的仅仅在于去重。假设喜乐君在20岁买过3次打印机,小王在18岁买过4次打印机,在计算“打印机的客户平均消费年龄”时,喜乐君和小王分别都被计算一次,不多也不少。既要去重,又不能进一步简化到无意义。

考虑到LOD的结果还要二次聚合,使用AVG或者MIN返回去重的年龄。所以正确的计算是:

AVG( {FIXED [Sub-Category],[Customer Name],[消费年龄]: AVG([消费年龄])})

基于这样的计算,一个客户、同一年龄的多次购买会被简化为一个值;而一个客户、多个年龄的购买会被保留不重复的多个值。不同客户之间遵循相同的逻辑,最终获得聚合值。

在Tableau中,这里的子类别正是视图维度,因此可以用INCLUDE代替,如下:

AVG( {INCLUDE [Customer Name],[消费年龄]: avg([消费年龄])})

和前面的DAX表达式类似,INCLUDE函数自动引用了视图的维度作为聚合的依据,而DAX中的SUMMARIZE自动引用了视图维度构建“临时表”的依据——这里的依据都是产品分类、客户、年龄三个维度字段。在Tableau中,只需要理解计算的优先级,不同LOD表达式的差异,而在DAX还要理解“上下文转换”等更复杂的技术细节。

这样一看,这一简单的一行,就相当于PBI中的冗长的一大段,这里重复一下,强烈对比:

-- 书中的正确方法
Avg Customer Age correct:= 
AVERAGEX (                    -- iterate on  迭代:
   SUMMARIZE(                 --all the existing combination 针对如下所有组合的数据表:
       Sales,                  -- that exist in Sales   在sales明细表中,
        Sales[customer ID],    -- of the customer id and  每一个客户ID 和
        Sales[customer Age],    -- the customer age      客户年龄
    )
SALES[customer Age] )          -- and average the customer's age 计算客户年龄的均值
)

四、小结

基于这样的对比,读者应该能理解不同工具背后,对相同问题的处理方式有相同之处,但是技术实现又有明显的差异。Tableau的LOD表达式必须是建立在聚合基础上的,而DAX的临时表则可以是没有聚合的“交叉表”——DISTINCT和VALUES函数就是最简单的数据表。

DAX高级的计算,足以把数据准备、临时表、聚合都完全置于代码中完成,甚至可以脱离可视化存在,这就是面向IT的思路——高度可定制,充分的灵活性,同时带来的是“专业性门槛”。

Tableau采用的“详细级别”思路,任何聚合必然是相对于特定详细级别的聚合,预先的聚合用LOD表达式实现——而且是基于字段逻辑理解,而非表逻辑,虽然也有专业难度,但相比DAX明显更容易被接受。获句话说,Tableau把复杂的“表逻辑”,转换为了“字段逻辑”。

读者可以在下面的文章中,了解两个工具更深层次的差异:如何选择BI工具:Power BI“向左”,Tableau“向右”

简洁与否,一目了然。


喜乐君

Nov 29, 2022 V1 关键案例
Apr 8,2023 V2 大幅度调整本文