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

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’，结果如图。