汇率转换中,有很多问题,当然这个场景基本发生在有国际化运营情况的企业中。
大家好,我是 BI 佐罗,这里和大家一起探讨这个问题的通用解决方案。
先来抽象出该场景的通用结构。大致如下:
从本质上来说,交易值的问题在于:
问题就是值的单位是不统一的。
这在业务上带来的问题就是:
日本市场和欧洲市场的总交易是 1.2 亿,什么,单位是什么,如果不对单位做处理,那单位相当于是混合的,也就失去了意义。
因此,汇率转换的基本业务意义和核心业务意义在于:
将值的单位统一在一个单位。
另一方面,随着看报告的角色不同,可能是不同国家的有权限的人,可能会存在用不同单位视角来看结果的诉求,例如:
分别按 RMB 或 USD 来看结果。
综上,汇率转换的本质在于统一单位,实现:
相通了这些问题,再来看看其中的难点。
由于结算发生在交易时,因此,随着汇率的时效性,在统一单位时,与 K,M,B 的不同在于,这种统合单位是随着时间而变化的。
某天的交易额要按当天(或业务上合规的结算汇率)来统一到统一单位尺度。
根据以上分析,可知必须具备涵盖以上信息的相关表,如下:
以及以上两表涉及的规范化形态。具体数据模型大致如下:
这个数据模型的搭建,非常有学问。这里先知道用到的表,后续再来进一步解释这个设计。
汇率表的最小通用结构,如下:
这里强调了一种演示,具体汇率值可能并不真实(例如:JPY 日元对人民币 RMB 的汇率),但并不影响对解决方案的理解。
为了表示更复杂的场景以及更清晰的逻辑,该表所在案例的业务含义如下:
这样的处理使得该案例有更一般的假设,可以满足任何场景。
交易表的最小通用结构,如下:
其中,CurrencyId 表示汇率的 Id,可与汇率定义进行匹配。
汇率定义表的最小通用结构,如下:
这里将 RMB 作为统一的基准,并称为 Normal,而其他的货币都称为 Extend,以便于操作。
表示交易的基础指标,定义为:
KPI.Value.Base =
SUM( Fact_Sales[Value] )
其中,.Base 后缀暗示了该指标的基础性,且不能直接使用,并需要进一步改进,以便可以计算。
有过 “面向对象设计” 程序设计基础的伙伴可以理解一个概念,在面向对象设计的语言中,有三个特性:
DAX 与面向对象设计的机制是毫无关系的,但并不影响我们借用某些思想来设计和思考问题,以便让解决方案具有:
💡提示 这里首次提出:利用 DAX 的基本特性,可以完全按照封装,继承,多态的思想来设计解决方案。这为学习,体验 DAX 的美以及用于实际工程项目具有重要意义。
DAX 的封装,通过度量值完成。度量值依赖于数据模型,或者准确的说,是某个必然存在的数据模型中的字段,以确保某度量值是密闭的,不会发生变化的。同时,由于 Power BI Desktop 的软件特性,当度量值依赖的字段名称变化时,度量值会自动更新,用户无需维护。从这个意义上来说:
因此,DAX 的度量值支持用封装的思想进行设计。
例如:
KPI.Value.Base =
SUM( Fact_Sales[Value] )
这里定义的度量值 KPI.Value.Base 就是一种封装,它不会发生改变。
DAX 的继承,通过度量值完成。例如:
KPI.Value =
[KPI.Value.Base] * [Currency.To.Normal] * [Currency.To.Extend]
可以看到,度量值支持链式计算,从某种角度来说,如果遵循一定的设计原则,那么,下游度量值可以继承上游(依赖的)度量值的计算逻辑。
也就是说,DAX 的继承,更强调了一种业务逻辑的继承,这也是自然的,合理的。
不难想象,可以设计这样一个业务逻辑继承链路,如下:
当用户在使用 KPI.Value.MTD 这个度量值的时候,其本质上,正在使用:
按 MTD 筛选出的交易按汇率转换后并以 K 做为单位计算后的结果。
以上这句话说得相当拗口,但它应该让你感受到这种设计的魅力所在。
💡提示 小白进入到以 Power BI 作为泛型的自助商业智能分析领域,对技术的初步不适就是来自于这种逻辑的强大统一和输出,因为,这种逻辑是内涵式的,在 Excel 中,往往每一步就显性化地摆在那里,用户的大脑和眼睛可以看到每一步的结果;而在 DAX 中的计算,是被强大而清晰又优美的逻辑链条设计出来的,这种结合算力,逻辑,脑力为一体的全新体验,要真正感受到它的魅力后,就再也回不去了。
多态性,是实现可复用以及动态执行的最关键特性。
先不去纠结其技术含义,来看这样一个例子:
问:KPI.Value 表示什么?
答:表示,啊,,,,没有说表示什么啊,,,
对了,就是这种感觉,可以看出:
KPI.Value 是一种抽象的字面含义,Value 既不是销售额,也不是销量,但它既可以是销售额,也可以是销量,但到底是什么呢?
如果给用户提供一个切片器,用户选了 “销售额”,则计算出销售额;用户选了 “销量”,则计算出销量。
因此,我们可以感受到:
KPI.Value 具有可变的多种含义,这种可变的含义,取决于用户的选择,这种计算并不发生在定义它的时候,而是发生在用户选择以后。这种随着环境动态可变而又可以被提前设计出来的机制,就是:多态性(Polymorphism)。
💡插曲 多态性,是出现在计算机专业,编程中的专业术语,大概在科班中的大学二年级会学到,但在那时可以准确理解这个概念的学生并不多。直到大家未来从事了编程开发的工作,才能真正理解。但在这里,你已经理解了这个问题的内涵。
从这个意义上说,很多小伙伴问,DAX 到底强在什么地方,那么,你现在就可以感受到 DAX 可以支持设计出一种:
这是一种倒置的结构。
还记得这个场景吗:
IT:你要什么?
业务:我要看销售额。
IT:什么的销售额。
业务:嗯,,,都有可能。
IT:那不行,你必须先说要看什么,才可能帮你查什么
业务:也有道理啊,但的确什么都有可能啊。
IT:那做不了的啊。
业务:那就看 XXXXX 吧。
如果你是业务,你现在就可以明白,你要的东西的确可以根据未来的可能而动态给出的。不能实现这种业务的诉求的 IT,实则是没有选择带有多态性机制的商业智能工具;
如果你是 IT,你现在就可以明白,并非现有需求,才有答案;需求是可以在可变的空间内,全部准备好的,等需求变了,结果自然改变。
多态性,如此重要,在数据分析中,你可能接触过很多技术工具,但能在这一层面轻松设计出动态可变具有多态性的技术解决方案的机制,DAX 天生如此。
从这个意义上来说,我们选择支持多形性的自助商业智能分析工具,DAX 具备这个特点,其他的,可以自行评估。
再来看这个经典场景,如下:
设计这样一个业务逻辑继承链路,如下:
可见,在实际中,不是一个个特性单独使用,而往往是一连串一起用,这种优美,你体会到了吗?
有了封装继承多态,我们就可以更好更本质地思考和进行设计了。以汇率转换为例,可以做出这样的设计:
KPI.Value.ByCurrency =
// 意图将原始值转换为统一货币汇率值,再转为目标汇率货币值。
[KPI.Value.Base] * [Currency.To.Normal] * [Currency.To.Extend]
KPI.Value.ByCurrency 封装了一种不变的逻辑结构,就是:
交易转换为统一货币再转出为目标货币。
仔细思考,由于每天的汇率是不同的,在考虑汇率计算的时候,具体逻辑应该是:
不难看出,这里出现了一个二重循环迭代的逻辑。
这说明两件事:
为此,将上述设计调整为:
KPI.Value.ByCurrency =SUMX( VALUES('Calendar'[Date] ) , SUMX( VALUES( Fact_Sales[CurrencyId] ) ,
[KPI.Value.Base] * [Currency.To.Normal] * [Currency.To.Extend]
)
)
其中,两个嵌套的 SUMX 实现了二重迭代。
在最内层的迭代,计算基本聚合转为通用货币再转为目标货币的过程。
具体的实现,后文再做介绍。
数据模型的设计,就像一种棋局,如何搭建数据模型,会都后续设计产生影响,主要包括:
本案例中的数据模型采用了如下设计:
具备这样的好处:
汇率表具有的结构如下:
从某种角度来看,汇率每天都需要进行记录,所以是汇率的记录,这符合事实表的定义,因此,是事实表。但另一方面,除非用户去分析汇率的走势,否则,在没有分析意图下,汇率是一种参考查找的作用。
从使用的目的去界定事实表,维度表,可以启发设计师看到某表时应该怎样思考,具有很好的作用。在这里,汇率的目的不是事实,而是参考,因此,不作为事实表看待。将其命名为:Ref_Currency 可以体现使用它的目的。
观察这里的数据模型,可以看出,汇率定义维度,并未与交易事实表连接,实际的设计,也可以进行连接。这里不进行连接的设计思路是:
使用汇率的场景是很单一的,因此,尽量将这个部分隔离在主体数据模型之外,以凸显要分析的主体部分,而弱化辅助部分对主体部分的侵入。
这是:非侵入式设计的典型思维。(《PBI - 高级》课程中详细讲解了非侵入式设计,这里就不再重复)
再来观赏这个结构设计,如下:
汇率完全被边缘化到主体数据模型之外,仅仅复用主体数据模型的时间维度,这是合理的。
这样,设计师就是不受汇率的干扰,聚精会神继续考虑如何设计交易的相关分析。
💡提示 数据模型的设计不是唯一的,但好的数据模型,可以极度降低后续思考设计的难度,这是数据建模的精妙和魅力所在。从一定意义上来看,如果设计师在后续的工作中,得益于一开始有意义的数据模型设计,会有一种显著的成就感,因为这种预判就是下棋的布局,虽然未见胜负,但整个棋局却了然于胸。
既然汇率维度没有侵入到主体数据模型,那么,对于每笔交易级别的汇率,必然要查找对应的汇率。设计如下:
Currency.LookUpValue =
VAR vCurrentDate = MAX( 'Calendar'[Date] ) // 最近日期VAR vResult = // 最近一天有汇率的那天的汇率 CALCULATE(
LASTNONBLANKVALUE( 'Calendar'[Date] , SUM( Ref_Currency[RateValue] ) ) ,
'Calendar'[Date] <= vCurrentDate
)RETURN
vResult
查找汇率采用度量值给出,基于考虑如下:
业务逻辑是:查找与汇率维度选择一致且小于等于该日期的最后可用汇率。
考虑到汇率转换设计模式,如下:
为此,将上述设计调整为:
KPI.Value.ByCurrency =SUMX( VALUES('Calendar'[Date] ) , SUMX( VALUES( Fact_Sales[CurrencyId] ) ,
[KPI.Value.Base] * [Currency.To.Normal] * [Currency.To.Extend]
)
)
考虑到在最内层迭代已经存在:
则以上模式可以具体化为:
KPI.Value.ByCurrency.RawToNormalToExtend =
// 构建二重迭代,对于每一天分别计算,其中,对每种汇率转换分别计算SUMX( VALUES('Calendar'[Date] ) , SUMX( VALUES( Fact_Sales[CurrencyId] ) ,
[KPI.Value.Base] *
COALESCE( CALCULATE( [Currency.LookUpValue] , TREATAS( { [CurrencyId] } , Dim_Currency[Index] ) ) , 1 ) /
COALESCE( CALCULATE( [Currency.LookUpValue] , TREATAS( { SELECTEDVALUE( Option_Currency[Index] , 0 ) } , Dim_Currency[Index] ) ) , 1 )
)
)
这里使用的技巧是:
其中,用到了一个新的参数表:Option_Currency。它是用户的选择,如果用户选择了目标货币,则转出;如果用户没有选择,则保持统一货币。
这里的命名 RawToNormalToExtend 启发含义为原始值转为统一货币值再转为目标货币值。
其中,DAX 函数 COALESCE 实现查找不到汇率时,默认返回 1 以表示这是不需要找转换汇率的本币。
按照这些的设计,实现了通用的计算效果,如下:
其含义为:
关于什么时候使用度量值和计算列有很多争论,然而,理论上是不需要计算列的。计算列存在的一个真正重要意义,就是预计算。预计算,就是预先进行计算,将计算的结果存放起来,以便后续使用。
很多人不曾理解预计算的意义,但现在理解了。
很多人曾理解预计算的意义,但现在将更加透彻。
前文内容讲述了 “多态性”。可以发现:
预计算与多态性,就是天平的两端。
从多态性的角度:
因此,
倾向于多态性,则应该使用度量值。
倾向于高性能,则应该尽量预计算。
但需要注意的是:
某些需求由于必须借助度量值的链式传导来描述其计算逻辑,是无法预计算的。
也就是说,
在某些场景下,同一问题,可以用度量值或计算列解决,没有什么差别。
在某些场景下,必须用度量值,是因为必须要保证多态性。
在某些场景下,由于性能极度降低,需要牺牲多态性来预计算。
具体的例子就不列举了,随着对数据模型使用的深入,会体会到上述三点是一种平衡,权衡,以及深度实践。
由于在汇率计算场景下,任何一笔交易,都已经成为历史,在交易表中,是可以通过计算列先来将交易值参考汇率表,转换到统一货币的。如下:
ValueByCurrencyNormal =
[Value] *
COALESCE( CALCULATE(
[Currency.LookUpValue] , TREATAS( { [Date] } ,'Calendar'[Date] ) , TREATAS( { [CurrencyId] } , Dim_Currency[Index] ) , ALL( )
) ,
1
)
在交易表中新建计算列,并定义以上逻辑,即可完成预计算。
💡提示 在计算列中使用度量值或 CALCULATE 应该注意防止上下文转换的副作用。这里运用了这个技巧。
这样就可以得到一个更加快速的度量值,如下:
KPI.Value.ByCurrency.Normal =
// 原始聚合计算SUM( Fact_Sales[ValueByCurrencyNormal] )
原有汇率计算的公式可以优化如下:
KPI.Value.ByCurrency.NormalToExtend =
// 针对每种汇率货币,转换为通用货币SUMX( VALUES( 'Calendar'[Date] ) , SUMX(
VALUES( Fact_Sales[CurrencyId] ) ,
[KPI.Value.ByCurrency.Normal] /
COALESCE(
CALCULATE(
[Currency.LookUpValue] ,
TREATAS( { SELECTEDVALUE( Option_Currency[Index] , 0 ) } , Dim_Currency[Index] )
)
,
1
)
)
)
这里借助了已经预计算的值,再计算转出结果,减少了一个步骤,理论上会提升性能。
实际上,经过测试,这样的设计带来的性能优化是显著的。
考虑到用户的选择以及度量值的有效性,用户可能选择,也可能不选择,综上,可以封装出一个度量值,如下:
KPI.Value.ByCurrency =
IF( ISFILTERED( Option_Currency[Currency] ) ,
[KPI.Value.ByCurrency.NormalToExtend] ,
[KPI.Value.ByCurrency.Normal]
)
这样就得到了一个通用的度量值,可以适配于任何场景。
使用日期或更高粒度的计算时,这个模式也可以确保正确的计算。如下:
可以看出,选择了不同的日期,在不同时间的粒度,都可以确保计算正确。
上述具备了多态性的度量值 KPI.Value.ByCurrency 可以自动适配这些变化。
同理可以推测,在施加了各种时间维度计算后,仍然有效。
本文给出了汇率转换的通用方案。该通用方案,已经几乎考虑了最基本的抽象,并可以适配几乎任何情况。同时,给出了性能优化后的版本。
更重要的是,本文解释了高级设计背后的思想,这些思想是自然和简单的。如果技巧让人不断的记忆,那么思想则让人不断的享受。因为,每次设计都是同一思想的不断使用,重复。
本文同时揭示和抽象了 DAX 以及数据模型设计中蕴含的封装,继承,多态性质以及如何将这些性质用于设计的实际案例。