作者:蒋羽中 & 张俊英
部门:数据中台
有赞是一家SaaS公司,更是一家大数据公司。如何从海量数据中高效地挖掘数据的价值,并对数据进行可视化分析与展示,是我们亟待解决的问题。鉴于此有赞BI平台应运而生,BI平台经过多次迭代,使用户可以快速方便地在BI平台进行数据的分析与展示,满足了不同业务的取数需求,目前月均 UV 700+,PV 3W5+,报表总数 5K+。
本文主要从以下三方面进行阐述:
BI平台面向的用户主要包括:
具体的应用场景以及分析如图1.1所示:
图1.1 BI平台应用场景举例
图1.2 维度类型
在BI平台,只需要三步便可完成数据的可视化分析
图1.3 可视化分析步骤
操作路径:添加数据集->选择连接账户->自定义SQL->预览数据->提交。
图1.4 添加数据集
这里的数据集是一段逻辑SQL,相当于后续图表取数的table。
小技巧
1.可在字段的右上角齿轮处标记字段的日期类型及格式,当标记为日期类型时,可使用日期的时间粒度,时间组件等。
操作路径:新建图表->选择刚刚建的数据集->确认,即可进入报表编辑页。
图1.5 图表编辑
小技巧
1.添加计算字段:适合需要二次加工计算的指标
如转化率 = count(XXX) / count(XXX)(相当于Excel pivot里的计算字段)
2.报表复制功能
场景:已有报表"top GMV的营销活动", 需要添加 "top买家数的营销活动",
此时便可使用报表的复制功能
操作路径:进入看板->点击右上角齿轮->添加权限。或者可在"资源管理"处管理权限。
图1.6 权限管理
此步骤是为了保障数据的安全性,只有有相应权限的用户可进行相关的操作。
目前BI平台已经支持超过10种的图表类型,涵盖日常数据可视化分析的绝大部分场景需求,示例如图1.7、1.8、1.9所示。不同类型的图表适用于不同的使用场景,展开来讲:
图1.7 图表类型-1
图1.8 图表类型-2
图1.9 图表类型-3
制作图表时可按需筛选用户所关注的数据,也可从不同的时间维度观察数据,支持按某种排序规则对指标排序,方便用户快速从数据中获取信息。
图2.0 日期类型及格式
对需要二次加工计算的指标,如转化率等,用户可自定义SQL片段对数据集进行加工,生成额外的虚拟字段。支持报表级别、数据集级别的计算字段,报表级别的计算字段只能归本报表使用,当定义数据集级别的计算字段时,使用该数据集的报表均可使用此计算字段。
下钻是在粗粒度的报表数据上,需要查看更细粒度的数据的背景下产生的,旨在解决业务上查看比当前数据更细粒度数据的需求,与数仓概念中的“下钻”同义,如对“各省份的付费商家数据”,查看“某省份市级别的付费商家数据”。
图2.1 下钻图(查看省市级别的统计数据)
支持对同数据集多个报表或不同数据集多个报表的下钻,同数据集多个报表的隔层的父图层条件会自动作用于当前图层,如a->b->c,a层的条件会作用于c层;不同数据集多个报表隔层的父图层条件需要用户配置是否作用于当前图层。
联动是指多个报表之间的关系,当一个报表条件改变时,被联动的报表数据随之变动,如图2.2。
图2.2 联动图-1
当点击“新付费商家数-by城市等级图“的“一线城市”时,结果如下 :
图2.3 联动图-2
对同个报表,不同的人看到的数据或字段信息应该是不一样的,如杭州地区的销售经理只能看到杭州地区的销售情况,上海地区的销售经理只能看到上海地区的销售情况,但销售总监可以看到各个地区的销售情况。这种背景下,数据集上的行列权限便可大展身手了。
图2.4 行列权限-条件模式
图2.5 行列权限-自由模式
自由模式通过写SQL来设置条件,目前按用户的基础属性开放可设置的字段,后续支持不同业务上自定义的条件,e.g.[dep] in {db.table.businessId.value}。多个条件间为OR关系,由此实现了对不同的人或组的数据过滤功能,保证了字段级别的数据安全。
2.8 图表推送
为方便用户对数据的及时了解,用户无需登录BI平台查看报表,只需要在BI平台上配置推送的信息,如何时推送,以什么形式推送等,收件人便可以在配置的时间点收到报表数据的推送,提升用户对数据的感知度。
2.9 外部应用集成
对有数据可视化分析与展示需求的外部产品,都可以利用BI的分析处理工具定制图表,通过BI平台提供的SDK快速嵌入BI看板、报表和BI平台的分析组件,并可以自定义传参实现灵活的图表展示,提升各产品制作图表的效率。
2.10 移动端
有赞BI可以在移动端查看报表,支持与图表进行简单的交互,如筛选、下钻、联动等。支持Android和IOS系统,用户可以随时随地查看报表。
三、实现原理
有赞BI平台的搭建涉及到了许许多多的技术和组件,如何将用户在前端对数据集字段的拖拽翻译成SQL并查询数据是比较重要的一个部分,下面将简要介绍一下实现方式。
3.1 行维度、数值、筛选、排序与分页
图2.6 demo
上图拖拽的语义为:根据“订单类型”的不同取值,统计“买家数量”和“成交金额”,其中统计的订单需要满足其日期符合筛选条件(日期按年计算,只统计今年的订单),对结果集按“成交金额”升序排列,并对结果集做分页处理。拖拽完成后最终生成的SQL为:
SELECT *
FROM (
SELECT *, row_number() OVER () AS bi_rownum
FROM (
SELECT order_type AS bi_rowdim0, COUNT(buyer_id) AS bi_metric0, SUM(gmv) AS bi_metric1
FROM (
SELECT *
FROM dm_zbk.da_self_analysis_demo
) mid_dataset
WHERE the_date >= '2020-01-01'
AND the_date <= '2020-12-31'
GROUP BY order_type
ORDER BY bi_metric1 ASC
)
)
WHERE bi_rownum > 0
AND bi_rownum <= 10
从中可以看出,行维度对应SQL中的group by部分,数值对应SQL中的聚合函数部分,筛选对应SQL中的where部分,排序对应SQL中的order by部分。而分页根据不同的数据库连接,会有不同出的处理方式,例如mysql中对应limit,在presto中需要自己使用row_number over函数自行处理。上述流程大致如下:
图2.7 SQL生成流程图
类似于行维度,列维度也是维度的一种形式,相当于excel表格中的数值列上方的多行表头。
图2.8 行维
图2.9 列维
要分析某个维度下的数值,既可以将维度放在行维度上,也可以将维度放在列维度上。如上图将“订单类型”维度放在列维度上,对比将“订单类型”维度放在行维度上,可以发现数据是一致的,只是将行数据转置到了列上。对于列维度的SQL实现,可能会有许多种实现方式。如可以类似行维度的处理,将列维度也作为group by的部分,然后通过代码组装数据到列上;也可以直接使用部分数据库提供的pivot函数,又或者直接在select数值部分的时候拆分成多列查询等。三种方式对比如下:
图3.0 列维SQL三种方式对比
有赞BI平台使用的是最后一种方法。 上图查询对应的SQL为:
SELECT SUM(if(order_type = '订单类型1', gmv, 0)) AS bi_metric0
, SUM(if(order_type = '订单类型2', gmv, 0)) AS bi_metric1
, SUM(if(order_type = '订单类型3', gmv, 0)) AS bi_metric2
, SUM(if(order_type = '订单类型4', gmv, 0)) AS bi_metric3
FROM (
SELECT *
FROM dm_zbk.da_self_analysis_demo
) mid_dataset
可以看到原本sum(gmv)因为“订单类型”的多种不同取值的原因分割成了多个select if语句。对于有列维度的情况,会先执行一次select distinct列维度的操作,获取列维度的唯一值以后再次拼接真正查询数据的SQL。当列数量过多的时候也可以进行横向的分页,即select distinct列维度取唯一值的时候做分页。列维度中“度量名”虚拟字段代表的是数值与列维度的排序关系,会影响表头的顺序。
图3.1 度量名位于下方的数据
图3.2 度量名位于上方的数据
这是通过控制select if语句的顺序实现的,对比2个图的SQL结构便可知。
订单类型在度量名上时的SQL如下:
SELECT COUNT(if(order_type = '订单类型1', buyer_id, NULL)) AS bi_metric0
, SUM(if(order_type = '订单类型1', gmv, 0)) AS bi_metric1
, COUNT(if(order_type = '订单类型2', buyer_id, NULL)) AS bi_metric2
, SUM(if(order_type = '订单类型2', gmv, 0)) AS bi_metric3
, COUNT(if(order_type = '订单类型3', buyer_id, NULL)) AS bi_metric4
, SUM(if(order_type = '订单类型3', gmv, 0)) AS bi_metric5
, COUNT(if(order_type = '订单类型4', buyer_id, NULL)) AS bi_metric6
, SUM(if(order_type = '订单类型4', gmv, 0)) AS bi_metric7
FROM (
SELECT *
FROM dm_zbk.da_self_analysis_demo
) mid_dataset
度量名在订单类型上时的SQL如下:
SELECT COUNT(if(order_type = '订单类型1', buyer_id, NULL)) AS bi_metric0
, COUNT(if(order_type = '订单类型2', buyer_id, NULL)) AS bi_metric1
, COUNT(if(order_type = '订单类型3', buyer_id, NULL)) AS bi_metric2
, COUNT(if(order_type = '订单类型4', buyer_id, NULL)) AS bi_metric3
, SUM(if(order_type = '订单类型1', gmv, 0)) AS bi_metric4
, SUM(if(order_type = '订单类型2', gmv, 0)) AS bi_metric5
, SUM(if(order_type = '订单类型3', gmv, 0)) AS bi_metric6
, SUM(if(order_type = '订单类型4', gmv, 0)) AS bi_metric7
FROM (
SELECT *
FROM dm_zbk.da_self_analysis_demo
) mid_dataset
有赞BI平台也允许用户自己写一个SQL片段对数据集进行加工,生成额外的虚拟字段。
图3.3 普通与带聚合运算的计算字段
如图3.3,如果用户写的是非聚合类的表达式,就当做一般的字段处理。如果用户写的是聚合类的表达式,当字段被拖拽到数值区域的时候,我们不需要对这个聚合表达式再做一次聚合,使用用户原本的表达式即可。
如果用户写的聚合类的计算字段与列维度共存的时候,需要对用户的表达式进行加工,例如:
图3.4 带聚合运算的计算字段
当一个聚合类型的计算字段与订单类型列维度共存的时候,生成的SQL为:
SELECT try( sum(if(order_type = '订单类型3', gmv , 0)) ) AS bi_metric0, try( sum(if(order_type = '订单类型4', gmv , 0)) ) AS bi_metric1, try( sum(if(order_type = '订单类型2', gmv , 0)) ) AS bi_metric2, try( sum(if(order_type = '订单类型1', gmv , 0)) ) AS bi_metric3
FROM (
SELECT *
FROM dm_zbk.da_self_analysis_demo
) mid_dataset
可以看到我们在用户的sum函数外部添加了try函数,因为表达式在某些情况下,比如除法分母为零的时候可能会报错。在内部添加了if条件,因为订单类型列维度会有不同的取值,根据这些不同的取值结合用户写的sum聚合函数构造sum if结构的查询表达式。对用户SQL节点修改是通过antlr来实现的。antlr的parser在parse sql的时候可以使用观察模式,允许对节点遍历的前后添加自己定义的回调函数。当我们遍历到聚合函数的时候,使用TokenStreamRewriter去replace对应的字符串,对聚合函数前后加上try,同时根据列维度唯一值的不同,添加不同的if条件即可。
有赞BI平台是有赞数据中台对数据可视化能力复用的结晶,利用有赞BI平台可以为内部数据使用与分析提供高效便捷的能力支撑。未来有几个重要的事情:
最后,有赞数据中台长期招聘基础组件、平台研发、数据仓库、数据产品、算法等各方面的人才,欢迎加入我们的团队,一起enjoy~,简历投递renhaichao@youzan.com