有赞BI平台实现原理

2021-01-05 11:29:16 浏览数 (1)

‍‍作者：蒋羽中 & 张俊英

部门：数据中台

一、概述

1.1 背景

有赞是一家SaaS公司，更是一家大数据公司。如何从海量数据中高效地挖掘数据的价值，并对数据进行可视化分析与展示，是我们亟待解决的问题。鉴于此有赞BI平台应运而生，BI平台经过多次迭代，使用户可以快速方便地在BI平台进行数据的分析与展示，满足了不同业务的取数需求，目前月均 UV 700 ，PV 3W5 ，报表总数 5K 。

本文主要从以下三方面进行阐述：

  • BI平台面向的用户及应用场景
  • 如何使用BI平台进行数据的分析与展示
  • BI取数的实现原理

1.2 面向用户及应用场景

BI平台面向的用户主要包括：

  • 报表开发者：一般是BI分析师和数据开发同学
  • 报表使用者：包括但不限于运营、服务、产品等任何对数据有可视化分析需求的同学
  • 其他内部应用：有数据展示或数据分析需求的其他产品

具体的应用场景以及分析如图1.1所示：

图1.1 BI平台应用场景举例

二、可视化分析数据

2.1 术语简介

  • 维度：数据观察的角度，一般为分类数据，如日期、店铺等。具体又可分为行维和列维，如图1.2所示 行维相当于excel表格中的表头列 列维相当于excel表格中数值列上方的多行表头
  • 数值：又称指标，是多维数组的取值。是特定维度下要统计的指标的聚合所得到的的值，一般为数值类型的数据，如销售额、利润等。

图1.2 维度类型

2.2 快速入门

在BI平台，只需要三步便可完成数据的可视化分析

图1.3 可视化分析步骤

step1: 添加数据集

操作路径：添加数据集->选择连接账户->自定义SQL->预览数据->提交。

图1.4 添加数据集

这里的数据集是一段逻辑SQL，相当于后续图表取数的table。

代码语言：javascript复制
小技巧 
1.可在字段的右上角齿轮处标记字段的日期类型及格式，当标记为日期类型时，可使用日期的时间粒度，时间组件等。

step2: 添加报表

操作路径：新建图表->选择刚刚建的数据集->确认，即可进入报表编辑页。

图1.5 图表编辑

代码语言：javascript复制
小技巧   
1.添加计算字段：适合需要二次加工计算的指标
如转化率 = count(XXX) / count(XXX)（相当于Excel pivot里的计算字段）
2.报表复制功能
场景：已有报表"top GMV的营销活动"， 需要添加 "top买家数的营销活动"，
此时便可使用报表的复制功能

step3: 添加权限

操作路径：进入看板->点击右上角齿轮->添加权限。或者可在"资源管理"处管理权限。

图1.6 权限管理

此步骤是为了保障数据的安全性，只有有相应权限的用户可进行相关的操作。

2.3 图表类型

目前BI平台已经支持超过10种的图表类型，涵盖日常数据可视化分析的绝大部分场景需求，示例如图1.7、1.8、1.9所示。不同类型的图表适用于不同的使用场景，展开来讲：

图1.7 图表类型-1

  • 指标卡可以用来描述指标的数值，也可以描述数据的变化趋势。
  • 线图是对数据随时间变化的趋势描述，也可以描述多组数据随时间变化的趋势，如“近一年内某店铺的销售量和销售额趋势变化”。
  • 双轴图是柱状图和线图的组合，适合对业务上关联较强的指标进行对比分析使用。
  • 面积图也是对数据随时间变化的趋势描述，在“线图”的基础上，将线和自变量的轴之间的区域使用颜色填充，对趋势变化的描述更加突出。
  • 堆积面积图和面积图的区别在于，每个指标数据量的起点是基于上一个指标数据量的，因此各个叠起来的面积表示各个指标数据量，堆积面积图整体代表了所有指标数据量的总和。

图1.8 图表类型-2

  • 柱状图是对分类数据的描述，又分为垂直柱状图和水平柱状图，分别用垂直或水平柱子来区分不同类别的数值。
  • 堆积图是在分类数据的基础上，对每个分类再进行小分类的划分，是对大分类下小分类数据的描述，将每个柱子分割，分为垂直堆积图和水平堆积图，形象地表示了相同大分类下的各个小分类的数据分布情况。
  • 饼图是对分类数据的占比情况描述，根据分类数据的占比将圆分为多个区块，通过区块弧度的大小来表示占比的多少，所有区块加和为100%。
  • 漏斗图表示随着业务流程推进数据的流转情况，从上到下有逻辑上的顺序关系，用梯形面积表示各个环境的差异，梯形的上底和下底分别表示数据的流入和流出。

图1.9 图表类型-3

  • 桑基图又称能量分流图，表示一组值向另一组值的流向，可以方便地展现数据流动的方向。其中，边表示流动的数据，流量表示流动数值，节点表示不同的分类，边宽与流量成比例显示，由此可见，无论数据怎样流动，数据总值是不变的，遵循数据的“能量守恒”。
  • 雷达图又称蜘蛛网图，是用于表现多维数据的图表，各维的数据映射到以圆心为起点，以圆周边缘为终点的坐标轴上，将同维的点用线连接，组成雷达图，雷达图所围面积是观察数据的关注点。
  • 词云，又称文字云。将文本数据中词的重要性通过不同的颜色、大小等表示出来，使用户快速感知突出的数据，获取数据的价值。

2.4 筛选与排序

制作图表时可按需筛选用户所关注的数据，也可从不同的时间维度观察数据，支持按某种排序规则对指标排序，方便用户快速从数据中获取信息。

  • 筛选： 可以对字段或数值进行筛选，包括对原生字段、计算字段、及字段聚合后的数据进行过滤，如筛选出“订单状态”为“交易成功”，交易额大于1000的订单信息。 目前支持的筛选条件有基本的>、>=、<、<=、=、<>、is null、is not null、between、like、not like、in等条件。同时可对时间字段按年、月、季度、周等不同的时间维度去观察数据，只需要对字段标记日期类型及相应的日期格式即可。 后续会支持条件表达式，使用户对条件的筛选更加灵活。目前支持如下日期类型及格式：

图2.0 日期类型及格式

  • 排序： 将指标按某种排列顺序进行排序，如按店铺的销售额降序排列出店铺的信息，这时可对指标进行排序。 目前支持对行维、指标的升降序排序。

2.5 计算字段

对需要二次加工计算的指标，如转化率等，用户可自定义SQL片段对数据集进行加工，生成额外的虚拟字段。支持报表级别、数据集级别的计算字段，报表级别的计算字段只能归本报表使用，当定义数据集级别的计算字段时，使用该数据集的报表均可使用此计算字段。

2.6 下钻与联动

下钻是在粗粒度的报表数据上，需要查看更细粒度的数据的背景下产生的，旨在解决业务上查看比当前数据更细粒度数据的需求，与数仓概念中的“下钻”同义，如对“各省份的付费商家数据”，查看“某省份市级别的付费商家数据”。

图2.1 下钻图（查看省市级别的统计数据）

支持对同数据集多个报表或不同数据集多个报表的下钻，同数据集多个报表的隔层的父图层条件会自动作用于当前图层，如a->b->c，a层的条件会作用于c层；不同数据集多个报表隔层的父图层条件需要用户配置是否作用于当前图层。

联动是指多个报表之间的关系，当一个报表条件改变时，被联动的报表数据随之变动，如图2.2。

图2.2 联动图-1

当点击“新付费商家数-by城市等级图“的“一线城市”时，结果如下 ：

图2.3 联动图-2

2.7 行列权限

对同个报表，不同的人看到的数据或字段信息应该是不一样的，如杭州地区的销售经理只能看到杭州地区的销售情况，上海地区的销售经理只能看到上海地区的销售情况，但销售总监可以看到各个地区的销售情况。这种背景下，数据集上的行列权限便可大展身手了。

  • 列权限：对不同的人或组设置不可见的字段，当访问的报表有用户不可见字段时，会友情提示用户申请字段权限。
  • 行权限：对不同的人或组设置可见的数据，可通过“条件模式”，“自由模式”两种模式设置条件。如下图：

图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为：

代码语言：javascript复制
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生成流程图

3.2 列维度

类似于行维度，列维度也是维度的一种形式，相当于excel表格中的数值列上方的多行表头。

图2.8 行维

图2.9 列维

要分析某个维度下的数值，既可以将维度放在行维度上，也可以将维度放在列维度上。如上图将“订单类型”维度放在列维度上，对比将“订单类型”维度放在行维度上，可以发现数据是一致的，只是将行数据转置到了列上。对于列维度的SQL实现，可能会有许多种实现方式。如可以类似行维度的处理，将列维度也作为group by的部分，然后通过代码组装数据到列上；也可以直接使用部分数据库提供的pivot函数，又或者直接在select数值部分的时候拆分成多列查询等。三种方式对比如下：

图3.0 列维SQL三种方式对比

有赞BI平台使用的是最后一种方法。 上图查询对应的SQL为：

代码语言：javascript复制
    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如下：

代码语言：javascript复制
    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如下：

代码语言：javascript复制
    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

3.3 计算字段

有赞BI平台也允许用户自己写一个SQL片段对数据集进行加工，生成额外的虚拟字段。

图3.3 普通与带聚合运算的计算字段

如图3.3，如果用户写的是非聚合类的表达式，就当做一般的字段处理。如果用户写的是聚合类的表达式，当字段被拖拽到数值区域的时候，我们不需要对这个聚合表达式再做一次聚合，使用用户原本的表达式即可。

如果用户写的聚合类的计算字段与列维度共存的时候，需要对用户的表达式进行加工，例如：

图3.4 带聚合运算的计算字段

当一个聚合类型的计算字段与订单类型列维度共存的时候，生成的SQL为：

代码语言：javascript复制
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

sql 数据库 数据可视化

0 人点赞