5步加速时间智能分析之 Power BI 篇【附模型下载】

时间维度是商业分析中常见的分析维度，广泛应用于财务和销售领域，帮助用户了解指标随着时间推移而产生的变化，从而作出预测等等商业行为。当数据量剧增，BI端的实时查询会耗费大量计算资源，不能及时返回结果，影响业务指标分析。借助 Kyligence 的强大 OLAP 引擎，大数据平台的分析效率可大大提高。上期的文章中，介绍了Tableau的加速技巧（戳此查看），今天将与大家分享 Power BI 在 Kyligence 平台上的加速技巧。

 

常见的时间智能分析指标包括：

1. 年累计（YTD），季度累计（QTD）和月度累计（MTD）

2. 上年同期（PY），上季同期（PQ）和上月同期（PM）

3. 上年全部（PYT），上季全部（PQT）和上月全部（PMT）

4. 环比（MOM%），同比（YOY%）

5. 去年年度至今（PYTD）和年度至今同比差异增长率（YTD YOY%）

 

例如年累计（YTD：Year to Date）需要计算从年初至今的指标值，在大数据量级时，由于数据量过大，进行年累计等自助分析响应会比较慢；通常的解决方案是提前在ETL阶段预先计算完成时间智能指标以加快查询时间，但这样又不方便业务部门自主定义时间智能指标，同时开发周期过长无法快速响应业务需求。本文将展现如何使用Kyligence + Power BI助力用户灵活的实现时间智能分析。

 

1. 准备数据

本案例中，我们使用Kyligence Enterprise自带的learn kylin样例数据集建立一个数据模型，该数据集中包含了一份销售数据。其中KYLIN_SALES是事实表，KYLIN_CAL_DT是时间表，我们会针对事实表KYLIN_SALES中的Price进行时间智能分析。

图1 创建模型

 

创建相应的Cube并构建Cube, Cube中定义的sum(price)度量将被用来进行时间智能分析。

图2 创建Cube

图3 创建度量值

 

注：为了实现时间智能分析，在Cube中用户无需定义额外的时间维度相关的度量，在查询时直接使用Power BI的时间智能函数计算即可。如在本例中需要对 sum(price) 做时间智能分析，只需要在Cube中有price的度量  Sum(price) 和相关的时间维度即可。

 

2. 连接Power BI

自2018年10月起的Power BI内置了微软官方认证的Kyligence Data Connector for Power BI，用于支持Power BI与Kyligence间的DirectQuery查询模式，若为2018年10月之前的版本，请先到到Kyligence官网下载相关驱动，详见Kyligence官网（https://kyligence.io/zh/）。本文使用Power BI进行分析，首先需要使用Kyligence Data Connector for Power BI 连接 Power BI，然后进行相关设置，详见Kyligence手册

 

3. 在Power BI中建立 Kyligence 数据模型

配置完毕后，即可在Power BI Desktop中点击获取数据，选择数据源为Kyligence，使用Direct Query模式加载数据，完成数据加载后，即可建立数据模型。

图4 在Power BI中创建数据模型

 

并将KYLIN_CAL_DT中的CAL_DT设置为日期列

图 5 设置日期表

 

 

4. 定义时间智能分析度量值

至此，已经做好了所有准备，然后就可以在Power BI开始定义度量值，下面我们示例业务分析中常用的时间智能分析度量：

年度至今（YTD），季度至今（QTD），月度至今（MTD）

Price_YTD=

//年度至今（YTD）

CALCULATE(SUM(KYLIN_SALES[PRICE]),DATESYTD(‘KYLIN_CAL_DT'[CAL_DT]))

Price_QTD=

//季度至今（QTD）

CALCULATE(SUM(KYLIN_SALES[PRICE]),DATESQTD(‘KYLIN_CAL_DT'[CAL_DT]))

Price_MTD=

//月度至今（MTD）

CALCULATE(SUM(KYLIN_SALES[PRICE]),DATESMTD(‘KYLIN_CAL_DT'[CAL_DT]))

 

上年全部（PYT），上季全部（PQT）和上月全部（PMT）

Price_PYT=

//上年全部（PYT）

CALCULATE(SUM(KYLIN_SALES[PRICE]),PARALLELPERIOD(‘KYLIN_CAL_DT'[CAL_DT],-1,YEAR))

Price_PQT=

//上季全部（PQT）

CALCULATE(SUM(KYLIN_SALES[PRICE]),PARALLELPERIOD(‘KYLIN_CAL_DT'[CAL_DT],-1,QUARTER))

Price_PMT=

//上月全部（PMT）

CALCULATE(SUM(KYLIN_SALES[PRICE]),PARALLELPERIOD(‘KYLIN_CAL_DT'[CAL_DT],-1,MONTH))

 

上年同期（PY），上季同期（PQ），上月同期（PM）

Price_PY=

//上年同期（PY）

CALCULATE(SUM(KYLIN_SALES[PRICE]),DATEADD(‘KYLIN_CAL_DT'[CAL_DT],-1,YEAR))

Price_PQ=

//上季同期（PQ）

CALCULATE(SUM(KYLIN_SALES[PRICE]),DATEADD(‘KYLIN_CAL_DT'[CAL_DT],-1,QUARTER))

Price_PM=

//上月同期（PM）

CALCULATE(SUM(KYLIN_SALES[PRICE]),DATEADD(‘KYLIN_CAL_DT'[CAL_DT],-1,MONTH))

 

环比（MOM%），同比（YOY%）

Price_MOM%=

//环比（MOM%）

DIVIDE((SUM(KYLIN_SALES[PRICE])-[Price_PM]),[Price_PM])

Price_YOY%=

//同比（YOY%）

DIVIDE((SUM(KYLIN_SALES[PRICE])-[Price_PY]),[Price_PY])

 

去年年度至今（PYTD）和年度至今同比差异增长率（YTD YOY%）

Price_PYTD=

//去年年度至今（PYTD）

CALCULATE([Price_YTD],SAMEPERIODLASTYEAR(‘KYLIN_CAL_DT'[CAL_DT]))

Price_YTD_YOY%=

//年度至今同步差异增长率（YTD_YOY%）

DIVIDE((SUM(KYLIN_SALES[PRICE])-[Price_PY]),[Price_PY])

 

最后根据需求选择可视化控件，创建仪表盘即可。

图 6 时间智能分析仪表盘

 

 

5. Kyligence针对Power BI的性能优化

至此，我们已经完成了所有的建模和报表制作，任意选择报表的过滤条件和组合维度，查询结果均可以在秒级返回，相比于使用的SparkSQL等SQL on Hadoop引擎平均有15倍以上的性能提升，关于性能的详细对比，后续将会单独发文详述，此处不做过多赘述。

由于进行时间智能分析的基础度量已在Kyligence的Cube上实现了预计算，任何基于该度量的时间智能分析仅需要根据预计算的结果再进行少量的后聚合就可以，这样就避免了从明细级别对数据进行聚合计算。如当用户计算销量今年至今（YTD）时，Power BI得到的是Kyligence返回的每天销量的聚合结果，Power BI再根据YTD函数的逻辑进行后聚合，此时结果集已缩小到天的颗粒度（即最多365行结果），因此有效的缩短了后聚合的工作量，在海量数据场景上有效的提升用户时间智能分析时的查询体验。

Kyligence Data Connector定制了Power BI DirectQuery的查询语法、复杂方言变种等，使得分析层生成的查询语句最佳适配Kyligence的查询引擎。

点击下载Power BI模型文件