基于单表(宽表、CUBE)的多维分析有一整套标准体系,各大BI产品在这方面的差异主要也是体现在界面风格上,在查询功能方面几乎没有差别,并没有多少对比的必要。但多表关联时的多维分析情况就很不一样了,各厂家都对JOIN操作进行了界面可视化,希望业务人员自己拖拖拽拽就能搞定,进而减少具体业务需求对技术人员的依赖。但生成宽表的过程并没有一套业界通行的规范,各个厂家有自己的实现方法,结果会导致查询功能和可用性产生较大的差异,本文将对比国内主流几款BI产品在这方面的不同。
BI中的关联需求
下面以一个很常见的进销存系统的数据为例,看BI系统中可能出现的数据关联类型。
四个有关联的表:订单表orders、(销售)员工表employee、部门表department、区域表area,归类表间的关联,我们能观察到这几类:
1、常规的多级外键关联,如orders.emp_id = employee.emp_id、employee.dept_id = department.dept_id,订单表关联到员工表,员工表关联到部门表;
2、自关联表,如area.pid=area.area_id,department.parent_detp=department.dept_id,这种表的用处是把多级父子关系的同类数据存到一起。
3、互关联表,employee.dept_id = department.dept_id,同时employee.emp_id = department.manager,员工关联到部门,部门经理又反向关联到员工(因为经理也是一种员工)。
4、两表有多个关联,如orders.send_city=area.area_id,orders.recieve_city=area.area_id,订单表里的发货城市、收货城市都指向区域表area。
通常由技术人员事先生成已经关联好的宽表,以避免业务人员理解关联。这存在明显不足,常规多级关联能好点,但也有字段太多难以命名的问题,有重复表的多关联命名时就更容易混淆。自关联和互关联理论上会有无穷多字段,根本就没办法先做出宽表。所以事先生成大宽表的方法不可取,只能是跟随业务需求,有针对性的生成相关宽表。如果这个过程继续依赖于技术人员,就会导致在线分析无法“在线”,所以最好是能让业务人员自己独立完成宽表生成的工作,避免事事都求助技术人员。
基于上面这套数据,我们在一些实际业务需求中观察下不同种类的关联,同时给出相应的SQL。
1、 分析各个销售部门的订单情况。这需要常规的多级关联,订单表关联到员工表,员工表在关联到部门表。这种容易理解,思维是线性的,把需要的表逐级关联起来即可。
SQL1:
select
orders.*,employee.*,department.*
from
orders
join employee on orders.emp_id=employee.emp_id
join department on employee.dept_id= department.dept_id
2、 分析不同地区下各个城市的销售情况。地区、省份、城市都存在区域表(area)中,要生成待分析cube,需要选出area表三次,