澎湃Logo
下载客户端

登录

  • +1

告别宽表,用DQL成就新一代BI

2022-10-13 08:33
来源:澎湃新闻·澎湃号·湃客
字号

BI 商业智能这个概念已经提出好几十年了,这个概念本身比较宽泛,不同人也有不同的理解和定义,但落实到技术环节,特别是面向业务用户的环节,所称的 BI,基本就是指的多维分析或者自助报表。

不管是叫自助报表还是多维分析,也都是一回事,都是让用户自己去通过拖拽的方式查询数据或制作报表。

用户想通过 BI,实现查询和报表自由,也就是可以灵活地分析自己想要的数据,挖掘出更大的价值。

厂商想通过 BI,给用户赋能,盘活用户数据价值的同时,也能体现出 BI 产品本身的价值。

那实际的情况如何呢,BI 有没有发挥出它预期的作用呢,我们就来探究一下。

做技术的都清楚,要查询分析数据,其实就是编写 SQL 语句去查询(我们假设要分析的数据都在关系数据库中,这是绝大多数 BI 的实际场景),那给业务人员使用的BI 多维分析的技术本质,其实就是通过页面拖拽出这个 SQL。

对于单表的查询,并不是很难理解和实施,选出字段再配上过滤条件及排序,和用 Excel 差不太多,分组汇总会稍复杂些,但也不是多难懂。

BI多维分析的本质

但是,有业务意义的查询经常涉及多表关联,比如查询存储余额 10 万元以上的储户中本地人的比例,看看某月回款额与发票额的对比。这些都需要多表关联,也就是要用到 SQL 的 JOIN。

业务人员很难理解 SQL 的 JOIN,多个表及其关系是个网状形式,要指定关联字段,还会涉及自关联、递归关联还有子查询再关联的复杂情况。三五个关联表之间的数据关系连技术人员都可能会晕,就更别说业务人员了,这时候,界面再炫丽、操作再流畅都没有什么意义了。

多表的 JOIN 拖拽把用户难住了,BI 厂商就只能绕路解决,总不能和用户说我们的分析只能基于单表进行吧(毕竟相当多有业务意义的分析都是多表的,世界是普遍关联的嘛),目前采用的变通手段就是建模,当前市场上的产品,基本都是这么做的。

所谓建模,就是把表间关联运算做成逻辑视图或物理宽表,这样业务人员在查询时相当于面对的还是逻辑上的单表,这就又变的简单了,又可以拖拽了。

分析被禁锢在宽表内

问题完美解决?不,并没有,宽表并不是一个好的解决方案。

宽表的局限性很明显,数据冗余,维护麻烦这些就不说了。

单单是:分析也只能基于宽表现有的关联去做这一条,就让用户和厂商都无法忍受了。

用户分析需求超出范围,或者有变化,就得技术人员修改或者重新再做一次宽表,用户不自由,啥也得厂商帮忙,今天想做的分析,可能得一周以后才能做;厂商更不乐意,每一次修改和重做,都是人工成本,可是自己产品提供的自助关联又不好用,也只能任用户摆布了。

当然有的 BI 厂商的建模,不叫宽表,事实上他们也确实比宽表做了更多的准备和优化,但归根结底,不管是 CUBE, 还是立方体,还是其他名字,本质都还是一个宽表,逻辑上并没有脱离宽表的范畴,分析需求变动时,还是得技术人员去改。

在一个数据系统中,BI 的作用本来就有限,然后还被死死的限制在了需要技术人员介入的宽表上,所谓的自由灵活就更得打折扣了。

那为什么这么多厂商都做不好多表的 JOIN,提供的 JOIN 功能,用户根本不会用,只能被迫用宽表呢?

BI 厂商为什么做不好 JOIN

造成这些难题的根本原因是,SQL 本身对于 JOIN 的定义过于简单了,用来描述复杂的关联场景时,就会很难理解,容易犯晕,就像用加法来描述乘法一样。

我们通过两个例子来看下:

查询:北京号码打给上海号码的通话记录

涉及通话记录表和电话帐户表以及地区表的多次关联

查询:中国经理的美国员工

人事系统里员工表,还有部门表。员工表中有所属部门的字段与部门表关联,部门会有经理,而经理也是个员工,部门表中的经理字段会再和员工表关联。这就发生互相关联的情况,转圈了。

这俩例子是很正常,很普遍的查询,但是即使是技术人员来写这个 SQL,也得费点劲儿,这是 SQL 本身的局限性造成的。

BI 厂商们也没有在数据模型层面针对这个难题进行优化封装,只是简单的把表对业务人员做了可视,把技术人员都觉得难的问题丢给了没有技术能力的业务人员,那当然没人能用的起来了。

更多的关于 BI 厂商做不好 JOIN 的分析,可以参考:为什么 BI 软件都搞不定关联分析

要解决这个难题,就需分析研究 SQL 的 JOIN 运算,突破 SQL 的局限才可以。

重新定义 JOIN 的 DQL

我们发现,BI 多维分析中需要的 JOIN,都属于这么3+1种情况:

外键关联,多对 1 的 JOIN 和 LEFT JOIN

同维表关联,1 对 1 的 LEFT JOIN 或 FULL JOIN

主子表关联,1 对多的 JOIN 和 LEFT JOIN

按维对齐,1 对 1 的 FULL JOIN 或 JOIN,LEFT JOIN 较少见

第四种维度对齐,稍有特殊,但也并没有超出前三种情况的范围,所以我们说成3+1。

这里说的是 BI 中的 JOIN,并不是 SQL 中全部的 JOIN,有些关联计算仍然需要原始的 JOIN 定义来描述,比如做矩阵乘法,但在 BI 中碰不到。

我们针对这3+1种情况,重新定义 JOIN 运算,改造 SQL 语法形成另一种类似的查询语言,也就是这里所说的 DQL,它是润乾开发出的新一代 BI 多维分析引擎,D 是即 Dimensional 维度的意思。

我们来分别看一下这几种情况下的 SQL 的复杂度以及 DQL 是怎么解决的。

外键属性化

我们用前面提到的那个查询中国经理的美国员工的例子来看一下 SQL 要怎么写,员工表里有个部门外键字段指向部门表的主键,部门表里又有经理外键字段指回员工表,这是很常见的数据结构设计。

SQL 写出来是这样的:

员工表和部门表 JOIN,再 JOIN 回员工表,也就是同一个表要连接两次,这就起个别名。在 WHERE 中写上 JOIN 的条件和最终我们希望的条件。整个句子要看一会才能明白。

使用 DQL 会写成这样:

这个句子中,美国员工好理解,中国经理的条件稍复杂一点,字段有了子属性,子属性又有子属性,但并不难理解,也就是部门的经理的国籍是中国

在 DQL 的语法体系中,外键被看成了属性,外键指向表的字段可直接用子属性的方式引用,也允许多层和递归引用

同维表等同化

这是两个一比一的表,主键相同,在数据库设计中经常有这种情况,字段的业务分类不同,不适合都放在一个表里,太宽的表在各字段丰满度相差较大时还会造成空间冗余浪费,访问性能也下降,因此常常会分到多个主键相同的表中

现在我们要查询计算所有员工的收入

SQL 中需要做 JOIN:

DQL 则可以把这两个表看成一个表访问:

" 工资 + 津贴”的的部分实际上来自两个表,DQL 把主键同维的表等同化,视为一个宽表,访问其中任何一个均可引用其它表的字段

子表集合化

订单及订单明细是典型的主子表,前者的主键是后者的一部分

现在我们想计算每张订单的总金额

用 SQL 写出来会是这样:

要完成这个运算,不仅要用到 JOIN,还需要做一次 GROUP BY,否则选出来的记录数太多。

如果我们把子表中与主表相关的记录看成主表的一个字段,那么这个问题也可以不再使用 JOIN 以及 GROUP BY:

与普通字段不同,订单明细被看成订单表的字段时,其取值将是一个集合,因为两个表是一对多的关系。所以要在这里使用聚合运算把集合值计算成单值。这种简化方式称为子表集合化

这样看待主子表关联,不仅理解书写更为简单,而且不容易出错

如果有多个子表时,SQL 需要分别先做 GROUP, 然后在一起和主表 JOIN 才行,会写成子查询的形式,但是 DQL 则仍然很简单,SELECT 后直接再加字段就可以了

按维对齐

这里有三个表:合同表、回款表和库存表

我们希望按日期统计合同额、回款额和库存金额

用 SQL 写出来是这样的:

用子查询把每个表分组汇总后再 JOIN 起来,如果偷懒不用子查询先 JOIN 后 GROUP,那结果是错误的,统计值会变多。这个问题必须使用子查询

这里涉及的三个子查询都要连接上,SQL 的 JOIN 关系要写成若干个两表关联,在表比较多时,增删关联表有可能把某个表漏掉而没有连接条件,出现完全叉乘

用 DQL 写出来是这样的:

在 DQL 中,只要把这几个表分别按日期对齐分别汇总就行了,而不必关心这些表之间的关系,在增删表时也不容易发生遗漏

如果按维对齐再与外键搅到一起,情况就会更复杂:

我们希望按地区统计销售员人数和合同额

用 SQL 写出来是这样:

这个子查询很复杂

而在 DQL 中,可以和外键属性化结合,这样查询会写成:

这里又出现了子属性,但整个句子仍然很简单,DQL 允许每个表独立设定统计维度,无须关心表间关联,还可以与属性化的外键配合使用

对这些 JOIN 更深入的探讨,可以参考 连接运算 1-SQL 中的 JOIN

http://c.raqsoft.com.cn/article/1619562709132

解决关联

前面讲的这几个 JOIN 的例子,都是在实际应用中常见的,具有业务意义的查询需求,

这些例子都是可以用来检验 BI 产品的“自助”灵活程度的,能否不需要技术人员更新模型就由完成这些查询。结果会发现,业内的大部分 BI 产品,无论界面多炫丽、操作多流畅,都经不起这个检验。

原因就在于,低层模型上,并没有解决好 JOIN 问题。

有了 DQL 之后,我们就能解决 BI 中的 JOIN 问题了

从前面的 DQL 例子中可以明显的看出,前 3 个查询用 SQL 的 JOIN 都没有了,多表变成单表了,只是字段变成有子属性了,而这并不难理解,业务人员可以轻车熟路地搞定。最后一个按维对齐的情况虽然还有 JOIN,但也很简单,用户无需关心这些表之间的关联关系,只要向统一的目标维度对齐就行了。

重新定义 JOIN 后,就彻底把不易于理解和拼写的 JOIN 变的简单易懂了,再做一个拖拽的前端界面,能让业务人员做 JOIN 的 BI 就做成了。

有人可能会问,多表变一表,那不还是宽表吗?那不也还得技术人员做吗?

DQL 和宽表大有不同!!!

DQL 当然也需要技术人员提前定义好元数据,但是用到技术人员的地方也仅此一次。

元数据中预先定义好了各种关联关系,但并没有做实际关联,当用户在前端拖拽分析的时候,才实时生成关联查询,不需要像宽表一样预先关联,占用数据库资源。

它的关联关系只要数据表本身结构不变,就不用修改元数据,不需要像宽表一样总得重新生成,相当于一次定义可以适应无数次不同的分析需求,它拥有宽表的优势但从根本上解决了宽表的各种弊端。

这就是所谓的非按需建模,建模只要考虑数据结构本身,而与用户需求无关。宽表(无论逻辑还是物理的)则是按需建模,需求一变就要再建模。

用 DQL 语法还能降低出错率

很多程序员习惯用 WHERE 来写 JOIN 运算的过滤条件,表少的时候没有问题,表多的时候漏写了 JOIN 条件意味着将发生多对多的完全叉乘,而这个 SQL 却还可以正常执行,一方面计算结果会出错,另一方面,如果漏写条件的表很大,笛卡尔积的规模将是平方级的,这极有可能把数据库直接“跑死”!

采用 DQL 的 JOIN 语法,就不可能发生漏写 JOIN 条件的情况了。因为对 JOIN 的理解不再是以笛卡尔积为基础,而且设计这些语法时已经假定了多对多关联没有业务意义,这个规则下写不出完全叉乘的运算。

对于多个子表分组后与主表对齐的运算,在 SQL 中要写成多个子查询的形式。但如果只有一个子表时,可以先 JOIN 再 GROUP,这时不需要子查询。有些程序员没有仔细分析,会把这种写法推广到多个子表的情况,也先 JOIN 再 GROUP,可以避免使用子查询,但计算结果是错误的。

使用维度对齐的写法就不容易发生这种错误了,无论多少个子表,都不需要子查询,一个子表和多个子表的写法完全相同。

DQL 还能让数据结构显得更为清晰

这是我们平时看到的 E-R 图,它是个网状结构的,表与表之间可能都有关联,表多了就会显得很零乱,增删表的时间很容易遗漏或重复表间的关联。

而在 DQL 体系下看到的表间关联是总线式的:

表与表之间没有直接的关联,都只处在中间地位的维度关联,增删表的时候不会影响到其它表,数据结构耦合度低。

不过,要说明的是,无论是 E-R 图还是后面的总线图,其中连线的数量都是相当的,这是数据关系本身决定的,不会因为改变了看待方法而变少,只是总线式看着更清晰些。

DQL 让 BI 告别了宽表,实现了更大程度的自由自助;也拓宽了 BI 分析的边界,让分析可以应对更多的数据场景,让 BI 成了更自由更好用的新一代的 BI。

DQL 从低层模型上解决了 JOIN 的问题后,前端的界面要怎么来做其实也就变的简单了,不需要再费心去想怎么样设计才能让用户更好的理解数据了,因为不管怎么做,都能轻松理解拖拽了。

告别宽表的新一代 BI

下面是润乾基于 DQL 实现的一套界面,我们还是按前面的例子,挨个看看每个 JOIN 是怎么呈现给业务人员,怎么拖拽的。

外键关联 --- 中国经理的美国员工

经过 DQL 解析后,数据就都变成业务人员可以理解的清晰的树状结构了。

原先的两个表变到一个表里了,业务人员已经完全不用去管后台是几个表,怎么关联了,直接拖拽员工姓名,再拖拽部门经理姓名,然后再设置一下两个的国籍,就可以了。

同维表关联

同样的,多表变一表,主键相同的表,像员工表,经理表;客户表,VIP 客户表,直接同化到一个表中了。

主子表关联 --- 每个订单的总金额

主子表,被视为一个表了,拖出订单,再选择求和方式拖出明细金额就可以了,不操心怎么关联的。

按维对齐汇总 --- 按日期统计 3 个不同表的汇总金额

这个虽然还是三个表,但业务人员也不用管各个表之间有什么关联关系,找到对应的金额指标,选择求和,然后直接拖拽就可以,再选一个“日”当做共同的统计条件,那就是按日期汇总了。

而且查询控件还会自动把和已选择数据不匹配的数据项过滤隐藏掉,有汇总的还会自动建立汇总项与统计维度之间的匹配关系,使用起来就更加智能了,不仅避免了出错,保证了拖拽分析的业务正确性,也使得查询分析更加流畅了。

润乾基于 DQL 引擎的全新一代 BI,突破宽表的限制,真正做到自由灵活分析,让业务人员能能轻松应对各种数据 JOIN 场景的 BI。

DQL 引擎会把 DQL 语句翻译成 SQL 执行,所以可以基于任何关系数据库工作。这款 DQL 引擎目前是免费提供的哦,前端界面部分还开源,你可以轻松把这些强大的功能集成到自己的 BI 应用中。

总结

BI 的定位是自由的分析,它可以隐忍一时的因为技术限制而带来的不自由,但它绝不会永远这样逆来顺受,技术是需要革新的,也会有人去革新,当新的技术突破瓶颈,捅破限制它的天花板以后,新一代的 BI 就到来了。

    本文为澎湃号作者或机构在澎湃新闻上传并发布,仅代表该作者或机构观点,不代表澎湃新闻的观点或立场,澎湃新闻仅提供信息发布平台。申请澎湃号请用电脑访问http://renzheng.thepaper.cn。

    +1
    收藏
    我要举报
            查看更多

            扫码下载澎湃新闻客户端

            沪ICP备14003370号

            沪公网安备31010602000299号

            互联网新闻信息服务许可证:31120170006

            增值电信业务经营许可证:沪B2-2017116

            © 2014-2024 上海东方报业有限公司

            反馈