BI中 关于OLTP 和OLAP 干货知识分享

OLTP 和 OLAP 这两个概念在十来年前、十几年前BI这个词还不是那么普及的时候，还经常放在一起做比较，现在已经很少再单独拿出来做对比了，但也总还是有人会问到，我在这里大概讲下两个概念的差别和联系。

什么是OLTP

OLTP 英文全称是 Online Transaction Processing System，在线事务处理系统。OLAP 英文全称是 Online Analytical Processing System，在线分析处理系统。从名词上看差异就是一个是事务处理，一个是分析处理。这个名词从英文翻译过来还是有些生硬，换种简单的方式来理解 OLTP 就可以理解为日常的业务系统，比如像 ERP、OA、CRM 等等，这些业务系统主要是管理企业的基本业务流程，对数据的处理方式主要是以增、删、改为主。也有查询，但查询的SQL的结构相对比较简单。

CRM可视化分析 - 派可数据商业智能BI可视化分析平台

什么是OLAP

OLAP就可以理解为分析型系统，比如在BI应用中，支撑到前端可视化分析的数据仓库。BI 底层使用到的数据库通常我们会称为数据仓库，数据仓库的主要目的一个是打通各个业务系统即OLTP的数据库，整合之后提供给前端BI可视化分析工具或者报表工具来使用。如果只是把BI定义为数据可视化或者可视化的工具，就有些过于狭义了。实际上BI不仅仅只包含数据可视化，更应该包含数据仓库，数据仓库是整个BI的核心部分，所以谈到OLAP的时候就一定会谈到BI。

商业智能BI系统 - 派可数据商业智能BI可视化分析平台

OLTP与OLAP的关系

第一，在底层数据处理层面，OLTP 以SQL增删改处理为主，OLAP以SQL查询操作为主。数据来源层面，OLTP 的数据来源就是它们前端的应用，就是B/S架构或者C/S架构的 B Browser 浏览器或者 C Client，就理解为用户在各种系统上录入数据就可以了。

第二，OLAP的数据来源就是不同的 OLTP数据库，所以OLAP本身是不产生数据的，通过ETL从OLTP抽取数据到OLAP数据库即数据仓库中做整合清洗达到可分析的数据标准。

第三，OLTP数据处理的时间相对较短，增、删、改操作，就像在页面上点击一个提交案例、下一步操作等等；但是OLAP数据处理的时间可能就会很长，比如一个大查询可能查询的数据量非常长，相对增删改时间周期会拉的更长一些，取决于OLAP数据结构的规范性以及返回数据量的大小。

第四，OLTP 也有查询操作，但查询的操作都相对比较简单；OLAP 的查询相对可以很复杂；

雪花型建模 - 派可数据商业智能BI可视化分析平台

第五，OLTP系统在底层数据库的设计上通常采用3NF设计方式，避免数据冗余，很适合频繁的增删改操作；OLAP系统主要是面向分析型应用准备的，因此在底层数据库即数据仓库的设计上通常会采用反三范式的方式，比如Kimball 的维度建模方式，刻意的保留数据冗余，很适合分析查询操作。当然，在OLAP系统底层数据仓库的架构中也有采用3NF建模的，主要目的是为了统一业务数据标准，但真正面向分析服务的时候还是会在3NF的基础上再构建一套反三范式的Kimball星型模型或者雪花型模型的数据架构。

第六，OLTP由于采用3NF建模，所以对数据的完整性要求很高，必须采用完整性约束。但是OLAP本身就不是面向业务交易信息的，不对业务过程负责，并且数据也不会频繁修改，所以是没有完整性约束这一说的。比如OLTP里面一个事务没有提交成功，或者失败了，事务是要回滚的。OLAP里面没有这种处理，跑不成功再重新跑一遍就可以了。

CUBE是什么

大家可以想象一下，BI前端可视化分析工具，或者报表工具从数据仓库取数去分析展现，会不会遇到一些查询性能的问题，这些问题都是怎么来的。

简单来说，分析页面刷新，前端浏览器不管是报表数据集模式，还是BI分析模型模式都会有一条SQL语句跑到服务器端去做数据查询，这个查询如果是BI的话就是到数据仓库上面去查，如果是数据集报表的话可以是从数据仓库，也可以是原始的业务系统数据库，总之有一条SQL语句要执行。

SQL - 派可数据商业智能BI可视化分析平台

第一种比如方式A返回的是大宽表到前端，数据量很大，前端再计算函数、慢慢渲染数据才展现出来。

第二种比如方式B返回的查询汇总之后的结果，数据量很小，前端基本上不用做什么渲染数据就出来了。

方式A的时间损耗在哪里呢？不是在数据库服务器查询上，因为SQL可能很简单，时间的损耗大部分是在从服务器端往浏览器通过HTTP连接返回、IO开销上，以及前端函数聚合汇总、解析和渲染上。B的时间损耗在查询阶段，因为SQL有大量的汇总，时间损耗在这个地方，减少了数据的返回量，前端函数基本上不用怎么处理，页面渲染也会很快。

所以，大家看到了没有，方式B是对方式A的一种性能优化。如果把这种优化提前的比如在ETL调度中实现，头一天晚上先算好，把该聚合的数据聚合好先存到数据仓库中的某一张表里面。除了需要看明细数据的这种查询场景，其它的任何查询就直接从这张已经提前算好的表里面取数就可以了。整个的复杂的聚合过程不是在BI报表分析的时候再来计算，而是提前算好、存储，用的时候直接把聚合后的结果拉出来使用。大家看，多了一张表、多了一份存储空间，但是却把整个查询、聚合计算的时间给省下来了，这个过程就是我们经常讲到的“空间换时间”的概念。

数据可视化 - 派可数据商业智能BI可视化分析平台

但是也有一个问题啊，数据聚合的结果存放到数据仓库中，这种数据的格式、形式是不是也相当于提前固化了。比如之前发过去的SQL查询返回的就是一张事实表，里面的度量是固定的，分析的维度属性也是固定的。如果现在用户改变分析维度或者指标呢？这张事实表就不能用了，新发起的查询就得像前面方式A提到的一样来处理，这样性能就又下降了，于是又得为这种新的查询聚合结果集再提前固化一张数据集市表。这样的场景多了，维护就非常的麻烦。

所以数据人员就在想，如果我们能够提前把所有可能分析的维度和维度属性Dimension and Attribute 和所有可能分析的度量Measure 全都组合好，全部算出来把结果提前存储起来，这样后面不管什么样的用户用什么样的维度和度量（指标）组合分析，都不需要临时计算，直接去结果，这样性能是不是就可以实现百倍、千倍甚至万倍的提升了？确实如此，因为你还要考虑到并发查询的问题。

这样一做，就是一个更大范围的用空间换时间的过程，这个过程就是OLAP CUBE多维立方体的设计思想来源和原理。

OLAP CUBE是如何来实现的

比如时间、区域、产品和销售收入这三个维度和指标的组合。它会先跑一遍SELECT SUM（收入）FROM 表 GROUP BY 时间，接着就是SELECT SUM（收入）FROM 表 GROUP BY 时间、区域，接着就是SELECT SUM（收入）FROM 表 GROUP BY 时间、区域、产品，然后就可以是SELECT MAX（收入）FROM 表 GROUP BY 时间、区域、产品，就是把各种聚合函数、各种指标、各种维度、各种维度属性的查询SQL全都执行一遍，把结果存储起来管理起来，就变成了一个多维立方体就是CUBE。

这个CUBE本身的描述是通过一个或者一组XML文件来组成的，把里面所有可能用到的SQL在XML文件里面组织起来。真正处理这个CUBE的时候，实际上跑的是这些SQL语句，在关系型数据库中比如数据仓库中把数据取出来进行存储。所以CUBE的空间有时比数据仓库还要大，各种数据的组合都考虑到了。

数据可视化 - 派可数据商业智能BI可视化分析平台

当然，实际开发中并不会是所有的维度、所有的属性、所有的指标都有组合分析的必要，因此还可以提前做一些配置，把哪些认为可能组合分析的维度、指标关联上就可以了。

在CUBE里面就可以很灵活的做各种透视分析，数据都是秒出的。但是有一些非直接通过维度和指标组合就可以出来的数据结果就需要通过查询的方式把数据给查询出来，这个时候就要用到MDX语句。在关系型数据库上的数据操作我们通过SQL语句去搞定，在多维分析数据库CUBE上的数据操作就要使用MDX的语句去搞定。从代码量上比，MDX比SQL要少很多。比如分析去年在TOP 10消费的客户今年不在的客户有哪些，MDX可能两句话就搞定了，但是SQL就需要写一堆。

但是从便利性上来说，MDX语法更加复杂，三个月不写基本上就可以忘记差不多了，因为CUBE它是一个多维空间，不像关系型数据库是一个二维的、行列交叉一眼就能看明白。学习CUBE还是需要有一定的想象力空间，跟关系型数据库取数的逻辑思考方式完全不一样。

CUBE在一些海量数据，特别是大维度表，比如百万级别的维度、千万级的维度这种场景下分析优势还是比较明显的。

但是现在也有很多MPP数据库、列式数据库，再结合对数据仓库建模的优化，也可以解决一部分场景下的分析性能问题。现在OLAP的引擎也已经很多了，比如ClickHouse、Impala、Doris、Kylin 等等。

Kimball - 派可数据商业智能BI可视化分析平台

OLAP CUBE 的数据来源一般是来自规范的数据仓库，最好是基于Kimball 维度建模的数据仓库，本身就是标准的维度和事实，CUBE处理起来就更加的简单方便。但是在ETL调度的时候，周期就会拉的比较长，因为要先处理数据仓库的数据，再才能处理OLAP CUBE里面的数据。

OLAP 里面还有一些分类比如MOLAP、HOLAP、ROLAP，这些查查资料基本上就看明白，大概理解了就可以了。