大数据 BI 分析之 2 招加速 Tableau 时间智能分析

Sarah Tang
2019年 4月 09日

MTD(Month To Date),QTD(Quarter To Date)和YTD(Year To Date)是业务分析常用的日期累计方式。 当数据量剧增,BI端的实时查询会耗费大量计算资源,不能及时返回结果,影响业务指标分析。借助Kyligence的强大OLAP引擎,大数据平台的分析效率可大大提高;在此基础上,分析师在使用Tableau时有两种方式可以实现提速:1)查询时通过窗口函数对Kylin Cube二次计算。2)通过Tableau计算字段对Kylin Cube二次计算,下面我们将分别介绍这两种方式。近期我们还将与大家分享Power BI在Kyligence平台上的加速技巧,敬请关注。

准备Kylin Cube

本文使用Kyligence Enterprise自带的learn_kylin样例数据集:KYLIN_SALES是事实表,对应销售订单的明细信息,每一行对应着一笔交易订单,交易记录包含了买家、卖家、商品、数量、订单金额等信息,其它表是与事实表相关的维度表,详细表定义、字段定义、关联键,可参考Kyligence Enterprise官方手册

假设,需要分析各个国家的购买金额,跟进销售MTD、QTD、YTD达成进度,可参考如下步骤完成。

分析以上需求,计算QTD时,需要用到日期辅助字段“KYLIN_CAL_DT.QTR_BEG_DT”,样例Cube kylin_sales_cube上没有该字段,需要要先将该字段添加至kylin_sales_cube的维度中,然后保存、构建Cube,过程可参考如下截图:

1. kylin_sales_model模型定义

2. kylin_sales_cube信息

3. 度量定义

4. 构建cube kylin_sales_cube

Cube完成建立,让我们进入正题!

方法1:通过窗口函数实现MTD,QTD,YTD

1. 窗口函数介绍

本文通过窗口函数SUM() OVER(PARTITION BY… ORDER BY…) 实现MTD,QTD,YTD计算。

该函数先通过PARTITION BY…分组,分组内ORDER BY…排序,SUM() OVER在分组内实现第一行到当前行的累计。

比如,定义sql如下:

Select  c1,c2,sum(c2)

over(partition by c1 order by c2) as c2_sum

–先按c1分组,组内按c2排序,同一组内由第一行到当前行累计c2

from test;

查询结果如下:

在SQL处理中,窗口函数一般都是最后一步执行,利用这一特性,可在查询时先获取cube数据,再通过窗口函数对数据二次聚合,实现MTD,QTD,YTD的加速。

2. 实现MTD,QTD, YTD

通过上文介绍的窗口函数,可定义如下sql查询各个国家的MTD,QTD,YTD 购买金额。

select NAME as BUYER_ACCOUNT_NAME,PART_DT,MONTH_BEG_DT,QTR_BEG_DT,YEAR_BEG_DT,

       SUM(PRICE) as GMV,

       SUM(SUM(PRICE)) over (partition by NAME,MONTH_BEG_DT order by PART_DT )  GMV_MTD,

       SUM(SUM(PRICE)) over (partition by NAME,QTR_BEG_DT order by PART_DT )  GMV_QTD,

       SUM(SUM(PRICE)) over (partition by NAME,YEAR_BEG_DT order by PART_DT )  GMV_YTD

from KYLIN_SALES

inner join KYLIN_CAL_DT

on PART_DT=CAL_DT

inner join KYLIN_ACCOUNT

on BUYER_ID=ACCOUNT_ID

inner join KYLIN_COUNTRY

on ACCOUNT_COUNTRY=COUNTRY

group by NAME,PART_DT,MONTH_BEG_DT,QTR_BEG_DT,YEAR_BEG_DT

order by NAME,PART_DT

(用户可直接复制该SQL进行体验)

查询结果如下,可导出验证,和MTD,QTD,YTD预期一致。

3. 将查询SQL在Tableau中复用

上文的查询SQL支持在BI端复用,本文以Tableau为例,接下来介绍具体的操作步骤。

为了确保Tableau与Kyligence Enterprise连通性,请参考Kyligence Enterprise用户手册,提前配置Kyligence ODBC驱动,Kyligence TDC。 

a.  连接Kyligence Enterprise

b.模型界面,新建“自定义SQL”

将上文的查询SQL添加至Tableau自定义SQL

c.依据分析场景,拖拽维度度量

自定义SQL”添加完成后,可在Tableau工作表页面,拖拽对应的维度、度量、筛选器,获取各个国家的MTD、QTD、YTD购买金额

方法2:使用Tableau计算字段

Tableau 计算字段实现MTD,QTD,YTD的方式,是通过建立辅助日期参数,用户筛选该日期参数指定截止日期,再将交易日期与该截止日期比对,限制交易日期在MTD,QTD,YTD范围内,以获取目标数据,接下来介绍具体的操作步骤。

为了确保Tableau与Kyligence Enterprise连通性,请参考Kyligence Enterprise用户手册,提前配置Kyligence ODBC驱动,Kyligence TDC。 

1. 模型准备

在cube页面,选择“kylin_sales_cube”,点击“更多操作”,在下拉菜单中选择“导出TDS”,即可一键同步Kyligence Enterprise 模型和计算度量至Tableau。


双击.TDS文件,Tableau Desktop查看模型,根据需要将一些维度,度量显示或隐藏。

2. 新建日期参数

新建日期参数“FilterDate”,可从现有的字段中添加

3. 新建MTD,QTD,YTD计算字段

Tableau Desktop创建计算字段:MTD_FLAG,QTD_FLAG,YTD_FLAG,计算逻辑如下:




4. 依据分析场景,动态获取MTD,QTD,YTD

若您想要查询:截至到2012/3/22,各个国家对应的MTD购买金额,可按如下操作获取:

a.显示参数控件 

右键点击“FilterDate”,选择“显示参数控件”,下拉选择参数FilterDate=’2012/3/22’

b. 添加MTD筛选器

将“MTD_FLAG”拖至页面筛选器,并筛选“Y”,再将BUYER_COUNTRY_NAME,GMV_SUM分别拖至列行,即可获取截至到2012/3/23,各个国家对应的MTD购买金额。

c. 验证查询

查看kylin后台日志,可以看出图表数据来源自kylin_sales_cube,响应时间0.177秒。

分析查询SQL, where 语句对应Tableau界面筛选器的筛选内容,限定了查询的日期范围(MTD):2012/3/1~2012/3/23, Group by的维度度量与Tableau图表展示维度度量一致。所以,Tableau MTD逻辑均转换成对应的查询SQL,在Kyligence Enterprise中执行,Tableau直接取执行后的聚合结果。

Tableau界面端可验证逻辑准确性,将交易日期CAL_DATE添加至图表,显示日期范围是2012/3/1~2012/3/22号,与MTD逻辑一致。


d.YTD/QTD查询 

若您想要查询:截至到2012/3/22,各个国家的YTD购买金额,只要将以上MTD_FLAG替换成YTD_FLAG即可,结果如图,QTD也是类似操作。

e. 切换YTD日期

若您想要查询:截至到2012/12/1,各个国家对应的YTD购买金额,只需要重新筛选参数FilterDate=’2012/12/1’,结果如图。