宽表在BI业务中比比皆是。每次构建BI系统，首先要做的就是准备宽表。有时系统中一个宽表可能有几千个字段，经常会因为“太宽”超过数据库表字段数的限制而被拆分。你为什么一直做宽表？主要有两个原因。一是提高查询性能。现代BI通常使用关系数据库作为后台，而SQL通常使用HASHJOIN算法。当关联表的数量和关联级别增加时，计算性能会急剧下降。当有七八张表关联三四级时可以观察到。这种现象，而BI业务中关联的复杂度远超这个规模，直接使用SQLJOIN无法满足前端的查询需求。为了避免关联带来的性能问题，需要先消除关联，即提前关联多个表，使用单表存储（即宽表），这样当关联消除时再次查询，从而达到提高查询性能的目的。二是降低经营难度。因为多表关联，尤其是复杂的关联，在BI前端很难表达和使用。如果使用自动关联（根据字段类型等信息进行匹配），遇到相同维度的字段时（比如一个表有2个以上的region字段），会“晕”，不知道关联哪个,表之间的循环关联或自关联也无法处理；如果很多表开放给用户选择关联，业务用户将无法理解表之间的关系，因此几乎没有可用性；分步关联可以描述复杂的关联需求，但是一旦上一步出错，就得从头再来。因此无论采用哪种方式，工程实现和用户使用都非常麻烦。但是基于单表来做要容易得多，业务用户使用起来也没有障碍，所以将多张表组织成宽表就成了一件“自然而然”的事情。然而，任何事情都有两个方面。我们在看到宽米的好处并大量应用的同时，也不能忽视它们的缺点，其中一些会对应用产生很大的影响。看看下面。宽表的缺点数据冗余能力大宽表不符合范式的要求。多表合并为一张表时，会出现大量的冗余数据。冗余度与原表的数据量和表之间的关系有关。通常，如果有多层外键表，冗余度会呈指数级增长。大量的数据冗余不仅会给存储带来压力（多个表组合起来的宽表数量可能会非常大），导致数据库容量问题，而且由于大量的冗余数据参与其中，也会影响计算性能查询计算期间的计算。使用了宽表，但查询仍然很慢。数据错误由于宽表不满足三种范式的要求，在数据存储过程中可能会出现一致性错误（脏写）。例如，同一个销售人员可能在不同的记录中存储不同的性别，同一个供应商在不同记录中的位置可能是矛盾的。基于此类数据的分析结果显然是错误的，而且这种错误非常隐蔽，很难发现。另外，如果构建的宽表不合理，也会出现汇总错误。比如根据一对多的A表和B表构建宽表，如果A中有计算指标（比如金额），在宽表中会重复出现，汇总时就会出错基于重复的指标。灵活性差宽表本质上是一种按需建模的手段，根据业务需要构建宽表（虽然理论上可以把所有的表组合成宽表，这个只是理论上存在，如果要实际操作的话会发现需要的存储空间太大，完全不能接受），这就导致了一个矛盾：BI系统建设的初衷主要是为了满足灵活的业务查询需求，即业务需求不是事先知道的，有的查询是在业务发展过程中，有的是业务用户的临时查询。这种灵活的需求采用宽表，需要提前处理，矛盾极大。想要得到一张好的宽台就不得不牺牲灵活性，可以说是不可兼得。可用性问题除了上述问题外，宽表也会因为字段过多而导致可用性低的问题。一个事实表对应多个维表，维表有维表，表与表之间可能存在自关联/循环关联。这种结构在数据库系统中很常见。宽表就是基于这些结构的表构建的，尤其是在表达多层次的时候，宽表的字段数会急剧增加，往往会达到几百甚至上千（有些数据库表对字段数有限制，这时候就需要对表进行横向划分）。试想一下，在用户访问界面中如果有上千个字段怎么用？这就是宽表带来的易用性差的问题。总的来说，宽表在很多场景下往往弊大于利，那么为什么宽表依然猖獗呢？因为没有办法。解决上述查询性能和业务难度的问题，一直没有比宽表更好的方案。其实只要解决了这两个问题，宽表就可以用了，宽表带来的各种问题也就迎刃而解了。SPL+DQL借助开源集算器SPL消除宽表来实现这一目标。SPL（StructuredProcessLanguage）是一个开源的结构化数据计算引擎，提供强大的独立于数据库的计算能力。SPL内置了很多高性能算法，特别针对关联操作进行了优化，针对不同的关联场景采用不同的方法。该方法可以极大地提高关联性能，从而可以在不需要宽表的情况下进行实时关联，满足多维分析时效性的需要。同时，SPL还提供了高性能的存储，可以通过高效的算法进一步发挥其性能优势。只有高性能是不够的。SPL原生的计算语法不适合多维分析应用接入（生成SPL语句将极大改造BI系统）。目前大部分多维分析前端都是基于SQL开发的，但是SQL系统（不使用宽表时）很难描述复杂的关联计算。为此，SPL设计了一种特殊的类似SQL的查询语法DQL（DimensionalQueryLanguage）用于构建语义层。前端生成DQL语句，DQL服务器将其转化为SPL语句，然后基于SPL计算引擎和存储引擎完成查询返回给前端，实现全链路BI查询。需要注意的是，SPL只是作为一个计算引擎存在，前端接口还是由用户自行实现（或者选择相应的产品）。SPL：关联实现技术SPL如何在不使用宽??表的情况下实现实时关联来满足性能需求？BI业务中的JOIN绝大部分是等价JOIN，即关联条件相等的JOIN。SPL将等价关联分为外键关联和主键关联。外键关联是指用一张表的非主键字段关联另一张表的主键。前者称为事实表，后者称为维度表。两个表是多对一的关系，比如订单表和客户表。.主键关联是指用一张表的主键关联另一张表的主键或部分主键，如客户表与VIP客户表（一对一）、订单表与订单明细表（一对多）。两种类型的JOIN都涉及主键。如果充分利用这个特性，使用不同的算法，就可以实现高性能的实时关联。不幸的是，SQL中JOIN的定义并没有涉及到主键，而是两个表的笛卡尔积，然后根据一定的条件进行过滤。这个定义简单而广泛，足以描述几乎所有事物。但是如果JOIN严格按照这个定义来实现的话，理论上是没有办法在计算的时候利用主键的特性来提高性能的。工程上只能做有限的优化。当情况比较复杂时（很多表和级别）经常无效。SPL改变了JOIN的定义。通过分别处理这两种类型的JOIN，可以利用主键的特性来减少计算量，提高计算性能。外键关联不同于SQL，SPL明确区分维度表和事实表。BI系统中的维表通常都不大，可以提前读入内存建立索引，这样在关联的时候可以计算出一半的HASH值。对于多层维度表（维度表和维度表），还可以使用外键寻址技术做预关联。即把维表（本表）的外键字段值转换成对应维表（外键表）记录的地址。这样就可以直接通过地址获取关联的维表数据，而无需计算和比较HASH值。多层维表只是多次按地址取值，单层维表的关联性能基本相同。同样，如果事实表不够大，无法完全读入内存，也可以通过预关联解决事实表与维表的关联问题，提高关联效率。预关联可以在系统启动时读取并准备一次，以后可以直接使用。当事实表过大无法存满内存时，SPL提供了一种外键序列化的方法：将事实表中的外键字段值转换为维表中对应记录的序号。在关联计算时，使用序号检索对应的维表记录，可以达到类似外键寻址的效果，也可以避免HASH值的计算和比较，大大提高关联性能。一些与主键关联的事实表也有明细表，比如订单和订单明细。两者通过主键和一些主键关联。可以看作是主从表的特例）。主分表都是事实表，涉及的数据量比较大。为此，SPL采用了有序合并的方法：预先按照主键顺序存储外存表，关联时依次取出数据进行合并，不产生临时缓存，即可完成计算只有少量内存。但是SQL采用的HASH分区算法复杂度比较高。不仅需要计算HASH值进行比较，还会产生临时的缓存读写动作，导致计算性能较差。HASH分堆技术很难实现并行。多线程必须同时缓存数据到一个子堆，造成共享资源冲突；关联子堆时，会消耗大量内存，无法实现大量并行。有序回归易于分割和并行化。当数据有序时，子表可以根据主表的key值进行同步对齐切分，保证正确性，不需要缓存，而且由于占用内存少，可以使用更大的并列数获得更高的性能。预排序虽然成本高，但是可以一次性完成，以后可以一直使用merge算法实现JOIN，性能可以有很大的提升。同时，SPL也提供了在有额外数据时保持数据整体顺序的解决方案。对于主子表关联SPL，可以采用更有效的存储形式，将主子表统一存储。子表作为主表的一个集合字段，其值由多个与主表数据相关的子表记录组成。这样相当于提前实现了关联，直接取数重新计算就可以了，不需要比较，存储量更少，性能更高。存储机制的高性能离不开有效的存储。SPL还提供了列式存储，可以大大减少BI计算中读取的数据量，提高读取效率。SPL列存储采用了独特的乘法和分段技术。与传统的列存块并行方案相比，在数据量大的时候会发挥不同的作用（否则并行度会受限）。这种技术可以使SPL列在数据量不是很大的时候，也可以获得很好的并行切分效果，充分发挥并行的优势。SPL还提供了数据类型的优化机制，可以显着提高多维分析中切片操作的性能。比如枚举维度转化为整数，切片条件在查询时转化为布尔值组成的对齐序列。比较时，可以直接从序列的指定位置取切片判断结果。还有一种标签位维度技术，将多个标签维度（取yes或no值的维度，在多维分析中大量存在）存储在一个整型字段（一个整型字段可以存储16个标签）中，不仅大大减少了存储量，计算时还可以同时对多个标签进行按位计算，从而大大提高计算性能。有了这些高效的机制，我们在BI分析中就可以不再使用宽表，而是使用SPL存储和算法进行实时关联，比宽表具有更高的性能（无冗余读取的数据量更小，更多快的）。然而，仅有这些还不够。原生SPL语法不适合BI前端直接访问。这就需要一种合适的语义转换技术，将用户操作以合适的方式转换成SPL语法进行查询。这需要DQL。DQL：关联描述技术DQL是SPL之上的语义层构建工具。在这一层完成SPL数据关联关系的描述（建模），为上层应用服务。即把SPL存储映射成DQL表，然后根据表描述数据关联关系。在描述了数据表之间的关系之后，就形成了一个以维度为中心的总线结构（区别于E-R图中的网络结构），维度居中，所有不直接相关的表都通过维度进行过渡。基于这种结构的关联查询（DQL语句）将得到很好的表达。例如根据订单表（orders）、客户表（customer）、销售员表（employee）、城市表（city）查询：全国各销售区域今年华东地区销售人员的销售额。它在SQL中看起来像这样：SELECTct1.area,o.emp_id,sum(o.amount)somtFROMordersoJOINcustomercONo.cus_id=c.cus_idJOINcityct1ONc.city_id=ct1.city_idJOINemployeeeONo.emp_id=e.emp_idJOINcityct2ONe.city_id=ct2.city_idWHEREct2.area='east'ANDyear(o.order_date)=2022GROUPBYct1.area,o.emp_id多表关联需要JOIN很多时候，同一个区域表要关联两次才能找到营业员和客户所在的区域。这样的话，BI前端就很难表达了。如果打开关联，用户就很难理解。那么DQL是如何处理的呢？DQL写法：SELECTcus_id.city_id.area,emp_id,sum(amount)somtFROMordersWHEREemp_id.city_id.area=="east"ANDyear(order_date)==2022BYcus_id.city_id.area,emp_idDQL不需要JOIN多个tables，只根据订单单表查询就可以了。外键指向表的字段直接作为属性，可以引用尽可能多的层，便于表达。比如查询客户的位置可以通过cus_id.city_id.area来完成，这样就消除了关联，将多表关联查询转化为单表查询。而且，我们基于DQL开发BI前端界面也非常容易。比如可以这样写：用树结构来表示多级维度表关联。这样的多维分析页面，不仅易于开发，而且普通业务用户也易于使用。很容易理解，这就是DQL的功效。总结一下，宽表的目的是为了解决BI查询性能和前端工程实现的问题，但是宽表会带来数据冗余、灵活性差等问题。SPL的实时关联技术和高效存储可以解决性能问题，性能比宽表更高。同时没有数据冗余，存储空间更小（压缩）；DQL构建的语义层解决了多维分析前端工程的问题。实现问题，让实时关联成为可能，具有更高的灵活性（不再局限于宽表的按需建模），接口更容易实现，应用范围更广。SPL+DQL继承（超越）了宽表的优点，同时改进了它的缺点。这才是BI应该有的样子。
