数据仓库构建方法论和简单实践

数据仓库的价值

构思一个主题讨论数据仓库的构建方法论，包括数据仓库的价值、选型、构建思路，随着数据规模膨胀和业务复杂度的提升，大型企业需要构建企业级的数据仓库（数据湖）来快速支撑业务的数据化需求，与传统的数据库构建不通，数据仓库即是OLAP场景，偏于历史数据的存储/分析，用冗余存储换取数据价值；

一、数据仓库和数据库的区别

所有的应用系统都会涉及到数据库，针对数据库归纳和存放，也就是数据库的常见操作：增、删、改、查，那么为什么想要对企业数据进行分析，不能直接查询数据库，非要大费力气的去创建所谓的数据仓库和数据湖呢？

数据仓库主要是为了能够支持管理决策，而数据库是为了满足系统的及时性、一致性，好比你去银行存钱，你希望刚把钱存进去就能够查询到余额增加，你不希望第二天才能够确认，但是若是银行想对你的流水和余额进行分析，这个时候希望知道你在不同时期的银行余额，那么从数据库里是查不到你的历史余额信息的，因为数据库在设计的时候就需要满足一定的范式，为的就是满足及时性和去冗余，一个客户对应的余额只有一个，你在存款的时候，新的余额就会覆盖掉老的余额，所以你的历史信息在数据库中是没有记录的。

如果银行希望查询客户的历史信息，比如说你需要查询个人客户信息，他可能在柜台交易系统中有记录，也可以在网上银行交易系统也有记录，而你希望查询客户柜台交易信息以及网银交易信息，这个时候需要跨系统查询，有可能两个系统所使用的数据库都不一样，各自权限负责人员也不一样，跨系统查询显得非常困难，这个时候需要一个系统能够将所有业务系统的数据汇总起来并能够跨系统查询，这个时候数据仓库定义中的集成的，反映历史变化的正好解决了此问题，数据库和数据仓库的对比：

其中OLTP（On-Line Transaction Processing）指的是传统的关系型数据库，针对实时业务处理，面对的主要操作是随机读写，采用满足三范式的实体关系模型存储数据，从而在事务处理中解决数据的冗余和一致性问题；而OLAP（On-Line Analytical Processing）系统面向数据批量读写，olap不关注事务处理的一致性，主要在数据的整合，以及复杂大数据查询和处理性能，OLAP能够支持复杂的分析操作。

综上， 数据库的设计是为了完成交易而设计的，不是为了而查询和分析的便利设计的。而数据仓库是为了大量的查询和分析所设计的。

二、数据仓库对于企业的价值

数据仓库能实现跨业务条线、跨系统的数据整合，为管理分析和业务决策提供统一的数据支持，从根本上把运营数据转化成为高价值的可以获取的信息（或知识），并且在恰当的时候通过恰当的方式把恰当的信息传递给恰当的人。

数据仓库定义：面向主题的（Subject Oriented）、集成的（Integrate）、相对稳定的（Non-Volatile）、反映历史变化（Time Variant）的数据集合；

• 面向主题：是指用户使用数据仓库进行决策时所关心的重点方面，如：收入、客户、销售渠道等；所谓面向主题，是指数据仓库内的信息是按主题进行组织的，而不是像业务支撑系统那样是按照业务功能进行组织的。
• 集成的：是指数据仓库中的信息不是从各个业务系统中简单抽取出来的，而是经过一系列加工、整理和汇总的过程，因此数据仓库中的信息是关于整个企业的一致的全局信息。
• 反映历史变化：数据仓库内的信息并不只是反映企业当前的状态，而是记录了从过去某一时点到当前各个阶段的信息。通过这些信息，可以对企业的发展历程和未来趋势做出定量分析和预测。

如下图，数据仓库并非是面向生产的数据系统，而是集成并加工了多个数据库系统数据，并面向各个业务条线构建的数据存储体系，能够提供快速的运营/决策支撑能力；

• 高级管理进行业务分析和绩效考核的数据整合、分析和展现的工具
• 主要用于历史性、综合性和深层次数据分析
• 数据来源是ERP（例:SAP）系统或其他业务系统
• 能够提供灵活、直观、简洁和易于操作的多维查询分析
• 不是日常交易操作系统，不能直接产生交易数据

数据仓库系统能够为企业的数据化提供：

数据仓库的模型构建

一、数据仓库构建需要考虑的问题

与数据库的单表基于ER模型构建思路不同，其面向特定业务分析的特性，决定了它的构建需要整合多套数据输入系统，并输出多业务条线的、集成的数据服务能力，需要考虑更全面的因素，包括：

• 业务需求：从了解业务需求着手分析业务特点和业务期望；
• 系统架构：从系统架构和数据分布、数据特性等角度，分析系统架构设计上是否有问题；
• 逻辑设计：从数据模型逻辑设计出发是否设计合理，是否符合数据库开发和设计规范等；
• 物理设计：从库表类型、库表分区、索引、主键设计等维度，主要针对性能，可扩展性进行物理模型设计审查

二、什么是数仓的数据模型

数据仓库模型构建的宗旨能够直观地表达业务逻辑，能够使用实体、属性及其关系对企业运营和逻辑规则进行统一的定义、编码和命名，是业务人员和开发人员之间沟通的一套语言，数据仓库数据模型的作用：

• 统一企业的数据视图；
• 定义业务部门对于数据信息的需求；
• 构建数据仓库原子层的基础；
• 支持数据仓库的发展规划；
• 初始化业务数据的归属；

常用数据模型的是关系模型和维度模型，关系模型从全企业的高度设计一个3NF模型的方法，用实体加关系描述的数据模型描述企业业务架构，在范式理论上符合3NF，其站在企业角度进行面向主题的抽象，而不是针对某个具体业务流程的，它更多是面向数据的整合和一致性治理；

维度建模以分析决策的需求为出发点构建模型，直接面向业务，典型的代表是我们比较熟知的星形模型，以及在一些特殊场景下适用的雪花模型，大多数据仓库均会采用维度模型建模；

维度建模中的事实表客观反应整个业务的流程，比如一次购买行为我们就可以理解为是一个事实，订单表就是一个事实表，你可以理解他就是在现实中发生的一次操作型事件，我们每完成一个订单，就会在订单中增加一条记录，订单表存放一些维度表中的主键集合，这些ID分别能对应到维度表中的一条记录，用户表、商家表、时间表这些都属于维度表，这些表都有一个唯一的主键，然后在表中存放了详细的数据信息：

如果是采用ER模型，需要设计出一个大宽表，将订单-商家-地址-时间等信息囊括在内，比较直观、细粒度，但也存在设计冗余，如果数据量很大，对于查询和检索将是一个灾难；

三、如何构建数仓的数据模型

概念模型设计（业务模型）：界定系统边界；确定主要的主题域及其内容；逻辑模型设计：维度建模方法（事实表、维度表）；以星型和雪花型来组织数据；物理模型设计：将数据仓库的逻辑模型物理化到数据库的过程；

1、概念模型设计

数据仓库中数据模型设计顺序如上，数据仓库是为了辅助决策的，与业务流程（Business Process）息息相关，数据模型的首要任务便是选择业务流程，为数据仓库的建立提供指导方向，这样才能反过来为业务提供更好的决策数据支撑，让数据仓库价值的最大化，对于每个业务流程，都需要进行独立的数据建模，将业务系统中的 ER 模型转化为数据仓库中的维度数据模型，以便更好的查询与分析。

2、逻辑模型设计

事实表一般由两部分组成，维度（Dimension）和度量（Measurement），事实表可以通俗的理解为「什么人在什么时间做了什么事」的事实记录或者场景上下文，拥有最大的数据量，它是业务流程的核心体现，比如电商场景中的订单表，其主键为一个联合主键，由各个维度的外键组成，外键不能为空值，事实表一般不包含非数字类型字段，虽然数据量大，但占用的空间并不大，保证更高的查询效率。

维度表用于对事实表的补充说明，描述和还原事实发生时的场景，如电商订单中定义用户、商品、地址、时间、促销5个维度，通过这5个维度还原订单发生时的场景，什么人在什么时间在什么地方购买了什么商品，以及购买该商品的促销方式。对于每一个维度而言，都有若干个属性来描述，比如用户有性别、年龄、所在地等信息。这些维度的属性就是之后数据统计的依据，比如我们可以统计不同性别，不同年龄，不同地区在订单中的差异，从向用户制定更精细的营销策略。

在关系型数据库三范式（3NF）设计极力避免数据的冗余，达到数据的高度一致性，但在数据仓库中3NF并不是最佳实践，反而让系统复杂不已，不利于理解和维护，所以在维度建模中，维度表一般采取反范式的设计，在一张维度表中扁平化存储维度的属性，尽量避免使用外键。

3、物理模型设计

在完成数据仓库的概念模型和逻辑模型设计之后，物理模型设计就是落地实施环节，根据数据的粒度和对于业务支撑能力将数据进行分层存储，数据分层存储简化了数据清洗的过程，每一层的逻辑变得更加简单和易于理解，当发生错误或规则变化时，只需要进行局部调整；

ODS层：全称是Operational Data Store,又叫数据准备层，数据来源层，主要用于原始数据在数据仓库的落地，这些数据逻辑关系都与原始数据保持一致，在源数据装入这一层时，要进行诸如业务字段提取或去掉不用字段，脏数据处理等等。可以理解为是关系层的基础数据；

DIM层：Dimension层，主要存放公共的信息数据，如国家代码和国家名，地理位置等信息就存在DIM层表中，对外开放，用于DWD，DWS和APP层的数据维度关联。

DWD层：全称是Data Warehouse Detail，用于源系统数据在数据仓库中的永久存储，用以支撑DWS层和DM层无法覆盖的需求，该层的数据模型主要解决一些数据质量问题和数据的完整度问题，比如商场的会员信息来与不同表，某些会员的的和数据可能不完整等等问题；

DWS层：全称是Data Warehouse Service，主要包含两类汇总表：一是细粒度宽表，二是粗粒度汇总表，按照商场订单例子，包含基于订单、会员、商品、店铺等实体的细粒度宽表和基于维度组合(会员日进场汇总、会员消费汇总、商场销售日汇总、店铺销售日汇总等)的粗粒度汇总表。这层是对外开放的，用以支撑绝大部分的业务需求，汇总层是为了简化源系统复杂的逻辑关系以及质量问题等，这层是的业务结构容易理解，dws层的汇总数据目标是能满足80%的业务计算。

其上根据业务需求可以继续构建ADS层（Application Data Store）和面向指标和报表的高度汇总层。

案例解读：招标采购系统的数据仓库构建

按照数据仓库的构建思路，顺序是概念模型-->逻辑模型-->物理模型，最重要和复杂度较高的是概念模型的设计，需要结合业务，并根据业务特性设计事实表、维度表、顶层数据汇总表；

一、概念模型设计

概念模型需要结合生产系统的ER关系模型，梳理业务逻辑，当前生产交易系统使用的是ORACLE数据库，将数据分成多个库：业务库（包含招标采购项目流程）、主体+组织库（招标人、投标人、评标专家、代理机构）、财务库（标书费、平台服务费、招标保证金、CA办理费用等），项目表即是一个招标流程表，该表会记录关于招标过程中的，招标、投标、开标、评标、定标相关的数据：

• 招标：招标流程是招标人发起的，招标人将招标过程委托给代理机构，代理机构会发布招标公告，投标人在报名、响应阶段产生数据，响应后需要付投标保证金；
• 投标：投标人给代理机构缴纳标书费并下载招标文件，开标之前需要响应，并缴纳投标保证金；发售招标文件和投标人购买标书后，如果投标人对招标文件提出质疑，或招标人要修改招标文件，此时要在规定时间内发布一个澄清公告。
• 开标：开标一般是线下进行，代理机构把投标人召集到开标室，公开宣读投标人关于投标人报价、工期、质量、工程项目经理等投标人有实质要求的内容，此阶段拆封投标文件，解密电子的投标文件；
• 评标：评标一般是线下进行，代理机构把监督人、投标人、专家召集到评标室，专家对投标人资质及投标书打分，分为技术、商务、报价分；
• 定标：专家对投标人综合打分后，做一个总体排名，排名第1即为中标候选人，评标结束后需要发布预中标公告，将前3名公布，公告期间接受社会监督，期间产生的疑问、质疑需要代理机构/招标人澄清，澄清伴随着澄清公告，若质疑生效则可能废标和流标（评标成本高，一般不废标）；
• 合同：若预中标发布后，质疑期间对于预中标候选人无影响，在预中标发布xxx天后，招标人需要同中标候选人签订合同，同时招标人需要退还其他没有中标单位的保证金；

对于整个流程的梳理和业务了解后，客户更加关注流程的监管预警，以此为准整理一些监管维度：

二、逻辑模型设计

逻辑模型采用上一篇博文提及的维度建模模型，雪花模型，项目ID、投标人ID、招标人ID、代理机构ID、专家ID分别是整个招、投、开、评、定标流程的主要参与主体，数据抽取工具使用kettle：

数据表命名规范：tb_模型层次_主题域_业务域_汇总粒度

kettle命名规范：kt_模型层次_主题域_业务域_汇总粒度

三、物理模型设计

构建ODS-->DWD-->DWS-->ADS的分层模型，这里ODS只抽取oracle库中源数据，不做任何清洗和变动，DWD层开始做数据的清洗和数据工程，DWS作轻度汇总，ADS面向应用查询提供更上层的汇总；以项目和供应商的汇总维度为例，项目流程是模型设计主体，供应商是类似维度表的数据，两者结合能够得到业务需要的一些投/中标相关的汇总维度（比如中标率排行、某个项目的投标人的注册金额相关统计、某投标人参与投标相关统计等）：

在项目流程表中（定标流程），将招标人的编号设计在内，定标流程的统计项从该类ADS汇总维度出结果：

数据仓库的产品

前面讲了数据仓库的价值、构建思路、实例，完成数据仓库的概念、逻辑、物理模型设计后，数仓的产品选型也是需要考虑的部分，根据数据存储量、查询效率、并发能力可以选用MPP数仓和基于Hadoop的分布式数仓等。

一、MPP还是Hadoop

这里继续用之前用到的图讲解，数据仓库的特性是处理温数据和冷数据，面向业务分析提供偏于离线分析能力，因此一般选用Hadoop+MPP数仓结合的解决方法，Hive能够提供大批量历史数据的存储计算能力，Hbase能够提供半结构化文档的快速检索能力，MPP能够提供强大高压缩比基础上的快速查询能力；

二、MPP数仓特性

在MPP解决方案中目前我已接触过的是vertica和GP，在teradata实习期间没有用到td数仓；

数仓的特性是大批量的查询和索引，少量的改查工作，MPP (Massively Parallel Processing)，即大规模并行处理数据库的一般特性：

① 列式存储意味着高压缩比、高IO能力、快速查询能力、智能索引（数据写入时）；

② shared nothing意味着节点的相互独立、数据的冗余备份；

③ 分布式存储/计算、存储/计算的高扩展性、高安全；

MPP的架构分为3种，GP是master/slave模式，具备统一的查询入口（master），vertica是无中心架构，所有节点都提供查询服务，gbase是存储/管理双中心架构；

shared nothing 模式：x86机器构建计算/存储的高扩展集群，数据拆分多份并备份。shared disk 模式：专用小型机，存储1份数据。