数据仓库建模和分层

什么是数据仓库?

数据仓库（ Data Warehouse ），是一个为数据分析而设计的企业级数据管理系统，数据仓库中可集成多个数据源的大量数据，借助数仓的分析能力，企业可从数据数据中获得宝贵的信息进而为企业决策提供数据支撑。

数据仓库的输入数据通常包括：业务数据、用户行为数据和爬虫数据等

业务数据：就是各行业在处理事务过程中产生的数据。比如用户在电商网站中登录、下单、支付等过程中，需要和网站后台数据库进行增删改查交互，产生的数据就是业务数据。业务数据通常存储在MySQL、Oracle等数据库中

用户行为数据：用户在使用产品过程中，通过埋点收集与客户端产品交互过程中产生的数据，并发往日志服务器进行保存。比如页面浏览、点击、停留、评论、点赞、收藏等。用户行为数据通常存储在日志文件中。

数据库也能满足上述要求，为什么还需要数据仓库？

1. 业务数据库不能够存储大量的历史数据，这会导致查询性能的降低。
2. 业务数据库需要给外部应用提供服务，如果再做数据分析，数据库不能承受如此并发，会对外部服务造成影响。
3. 数据仓库跟业务数据库数据在一定时间点上的数据一致，并且通过统一标注的方式，能够消除多个业务数据库之间的数据孤岛问题。
4. 业务数据库通常只支持sql进行数据分析，但数据仓库能够支持多种操作进行数据分析(主流是sql)。

	数据库	数据仓库
数据来源	企业中基础核心业务数据	数据库获取的同步数据
数据存储	核心作用是数据的CURD，行式存储，索引，不能存储海量数据	核心作用是数据的统计分析，列式存储，能够存储海量历史数据
数据价值	数据库是为了保障企业业务的正常运行	数仓是为了提供数据分析结果，为企业的决策做数据支持

一、建模理论的选择

数据模型就是数据组织和存储方法，它强调从业务、数据存取和使用角度合理存储数据。只有将数据有序的组织和存储起来之后，数据才能得到高性能、低成本、高效率、高质量的使用。

高性能：良好的数据模型能够帮助我们快速查询所需要的数据。
低成本：良好的数据模型能减少重复计算，实现计算结果的复用，降低计算成本。
高效率：良好的数据模型能极大的改善用户使用数据的体验，提高使用数据的效率。
高质量：良好的数据模型能改善数据统计口径的混乱，减少计算错误的可能性。

1.ER模型

数据仓库之父Bill Inmon提出的建模方法是从全企业的高度，用实体关系（Entity Relationship，ER）模型来描述企业业务，并用规范化的方式表示出来，在范式理论上符合3NF。
实体关系模型
实体关系模型将复杂的数据抽象为两个概念——实体和关系。实体表示一个对象，例如学生、班级，关系是指两个实体之间的关系，例如学生和班级之间的从属关系。

ER模型实体间的关系有一对多、多对一(学生和老师)，多对多(学生和课程)，即使一对多的情况下能够很好的使用两张表建模，但是再多对多的情况，通常需要另外一张表表达出实体间的关系。

数据库规范化
数据库规范化是使用一系列范式设计数据库（通常是关系型数据库）的过程，其目的是减少数据冗余，增强数据的一致性。

ER模型的目的是将整个企业的数据进行组合和合并，并进行规范处理，减少数据冗余性，保证数据的一致性。但这种模型并不适合直接用于分析统计，因此一般不采用这种方式进行数仓建模。

ER模型遵循面向对象，这种建模方式会产生很多的物理表，不利于数据分析，其目的是业务的完整性。

---->>> 数据库范式详细了解

2.维度建模

数据仓库领域的令一位大师——Ralph Kimball倡导的建模方法为维度建模。维度模型将复杂的业务通过事实和维度两个概念进行呈现。事实通常对应业务过程，而维度通常对应业务过程发生时所处的环境。

业务过程可以概括为一个个不可拆分的行为事件，例如在线教育交易中的下单，付款，加购等，都是业务过程。

二、维度建模理论

数据模型就是数据组织和存储方法，它强调从业务、数据存取和使用角度合理存储数据。只有将数据有序的组织和存储起来之后，数据才能得到高性能、低成本、高效率、高质量的使用。

建模就是建表,即使复杂的SQL也能通过建里相关的表变得简单！

1. 高性能：良好的数据模型能够帮助我们快速查询所需要的数据。
2. 低成本：良好的数据模型能减少重复计算，实现计算结果的复用，降低计算成本。
3. 高效率：良好的数据模型能极大的改善用户使用数据的体验，提高使用数据的效率。
4. 高质量：良好的数据模型能改善数据统计口径的混乱，减少计算错误的可能性。

维度模型将复杂的业务通过事实和维度两个概念进行呈现。事实通常对应业务过程，而维度通常对应业务过程发生时所处的环境。

业务过程可以概括为一个个不可拆分的行为事件，例如电商交易中的下单，取消订单，付款，退单等，都是业务过程。

1.事实表

事实表作为数据仓库维度建模的核心，紧紧围绕着业务过程来设计。其包含与该业务过程有关的维度引用（维度表外键）以及该业务过程的度量（通常是可累加的数字类型字段）。

事实表通常需要记录业务过程，比较“细长”，即列较少，但行较多，且行的增速快。

事实表有三种类型：分别是事务事实表、周期快照事实表和累积快照事实表，每种事实表都具有不同的特点和适用场景，下面逐个介绍。

---

事务型事实表

事务事实表用来记录各业务过程，它保存的是各业务过程的原子操作事件，即最细粒度的操作事件。
粒度是指事实表中一行数据所表达的业务细节程度。粒度越小，表示业务细节程度越高，反之亦然。

一旦事务被提交，事实表数据被插入，数据就不再进行更改，其更新方式为增量更新。

用户可以通过事务事实表对事务行为进行特别详细的分析。事实表一般围绕着度量来建立，当度量产生的时候，事实记录就生成了。

度量值（通常是可累加的数字类型的值，例如：次数、个数、件数、金额等）。

事务型事实表可用于分析与各业务过程相关的各项统计指标，由于其保存了最细粒度的记录，可以提供最大限度的灵活性，可以支持无法预期的各种细节层次的统计需求。

事务型事实表构建过程:
选择业务过程→声明粒度→确认维度→确认事实
第一步选择业务过程可以确定有哪些事务型事实表
第二步可以确定每张事务型事实表的每行数据是什么
第三步可以确定每张事务型事实表的维度外键
第四步可以确定每张事务型事实表的度量值字段。

优点
事务型事实表可以保存所有业务过程的最细粒度的操作事件，故理论上其可以支撑与各业务过程相关的各种统计粒度的需求。

缺点
但对于某些特定类型的需求，其逻辑可能会比较复杂，或者效率会比较低下。

存量型指标:例如购物车存量，账户余额等。

加购业务包含的业务过程主要包括加购物车和减购物车，两个业务过程各自对应一张事务型事实表，一张存储所有加购物车的原子操作事件，另一张存储所有减购物车的原子操作事件。

假定现有一个需求，要求统计截至当日的各用户各科目的购物车存量。由于加购物车和减购物车操作均会影响到购物车存量，故需要对两张事务型事实表进行聚合，且需要区分两者对购物车存量的影响（加或减），另外需要对两张表的全表数据聚合才能得到统计结果。可以看到，不论是从逻辑上还是效率上考虑，这都不是一个好的方案。

多事务关联统计:例如，现需要统计最近30天，用户下单到支付的时间间隔的平均值。

统计思路应该是找到下单事务事实表和支付事务事实表，过滤出最近30天的记录，然后按照订单id对两张事实表进行关联，之后用支付时间减去下单时间，然后再求平均值。

逻辑上虽然并不复杂，但是其效率较低，因为下单事务事实表和支付事务事实表均为大表，大表join大表的操作应尽量避免。在使用大表join大表时，效率极其低下，存在内存溢出问题。

---

周期型快照事实表

周期快照事实表以具有规律性的、可预见的时间间隔来记录事实，主要用于分析一些存量型（例如购物车存量，账户余额）或者状态型（空气温度，行驶速度）指标。

对于购物车存量、账户余额这些存量型指标，业务系统中通常就会计算并保存最新结果，所以定期同步一份全量数据到数据仓库，构建周期型快照事实表，就能轻松应对此类统计需求，而无需再对事务型事实表中大量的历史记录进行聚合了。

对于空气温度、行驶速度这些状态型指标，由于它们的值往往是连续的，我们无法捕获其变动的原子事务操作，所以无法使用事务型事实表统计此类需求。而只能定期对其进行采样，构建周期型快照事实表。

例如像游戏点卷存量型指标，每次统计需要使用点卷消费事务事实表和点卷充值记录事务事实表，这样的统计需要大表Join大表，性能效率低下，所以使用周期型快照事实表。

周期型快照事实表就是每隔一段时间对数据进行全量同步，对表数据进行更新，该表只记录了该时间下的业务状态。

---

累积型快照事实表

累积型快照事实表是基于一个业务流程中的多个关键业务过程联合处理而构建的事实表，如交易流程中的试听、下单、支付等业务过程。

累积型快照事实表通常具有多个日期字段，每个日期对应业务流程中的一个关键业务过程。

累积型快照事实表主要用于分析业务过程（里程碑）之间的时间间隔等需求。例如用户下单到支付的平均时间间隔，使用累积型快照事实表进行统计，就能避免两个事务事实表的关联操作，从而变得十分简单高效。

累积型快照事实表能保存事务时间，表中字段允许为空值或无意义值，表明该事务还没有进行，如上表中的，下单日期和支付日期间隔了一天，下单时支付字段为空。

---

1. 事务型事实表: 将一天的业务行为数据存储到对应天数的分区中，通常为增量采集。
2. 周期快照事实表: 为了应对存量型指标(存款)，通常将每天的全量数据状态保存到对应天数的分区中。
3. 累积快照事实表: 为了应对一个业务流程(交易流程)中的多个业务状态，通常将每天的全量数据状态保存到分区中。

累积快照事实表中，我们通常将最后一个业务状态时间作为分区字段，例如上述交易物流中的支付日期字段，因为这个字段能够代表一个业务流程的结束，如果该字段为null，我们通常设置一个时间极大值存储还未完成的业务数据。

2.维度表

维度表是对业务过程的上下文描述，主要包含代理键、文本信息和离散的数字。它是进入事实表的入口，丰富的维度属性给出了对事实表的分析切割能力，它一般是行少列多。维度的构成包括维度的元素，即维度中的各个数据元素的取值。例如，地区维度中具体的成员有英国、法国、德国。

维度表提供数据透视和切片数据分析的维度。它包含了事实数据表中事实记录的特性，有些特性提供描述性信息，有些特性指定如何汇总事实数据表数据，以便为分析者提供有用的信息。维度表还包含帮助汇总数据的特性的层次结构，这使得分析数据变得更加方便。

维度表主要由外键和维度信息组成，一些事务的相关维度数据较少的，例如支付方式(微信，支付宝，银联卡)，可以将这些维度信息冗余到事实表中，称为维度退化

一些维度数据可能会随着时间的推移发生变化，为了获取最新的和历史的维度数据信息，通常将维度表分为以下两种:

全量快照表

离线数据仓库的计算周期通常为每天一次，所以可以每天保存一份全量的维度数据。这种方式的优点和缺点都很明显。

优点是简单而有效，开发和维护成本低，且方便理解和使用。
缺点是浪费存储空间，尤其是当数据的变化比例比较低时。

一些数据量较小的维度表通常使用全量快照表进行记录。

拉链表

拉链表能够记录每条信息的声明周期，一旦一条记录的声明周期结束，就重新开始一条新的记录，并把当前日期记录为生效开始日期。
如果当前信息至今有效，在生效结束日期中插入一个极大值

拉链表使用新增两列时间日期的方式控制记录的有效日期，能够记录一条记录的完整数据变化，但因为数据的每次变化都需要插入一行数据，所以只能适用于缓慢渐变维(数据变化缓慢的维度)。

如何使用拉链表

假设一张维度表拥有100万条数据，其中90万条数据每天都会发生变化，那么应该使用全量快照表，如果每天变化的数据量很小，那么可以使用拉链表进行维度数据的保存。

3.多值维度

如果事实表中一条记录在某个维度表中有多条记录与之对应，称为多值维度。例如，下单事实表中的一条记录为一个订单，一个订单可能包含多个课程，所以课程维度表中就可能有多条数据与之对应。

针对这种情况，通常采用以下两种方案解决。
第一种：降低事实表的粒度，例如将订单事实表的粒度由一个订单降低为一个订单中的一门课程。

第二种：在事实表中采用多字段保存多个维度值，每个字段保存一个维度id。这种方案只适用于多值维度个数固定的情况。(订单ID 课程1 课程2 课程3)仅限在一个订单最多只能购买3个课程的情况下)

也可以使用一些数据结构解决上述问题，例如array

建议尽量采用第一种方案解决多值维度问题。

4.多值属性

维表中的某个属性同时有多个值，称之为“多值属性”，例如课程维度的课程类别，每个课程均有多个属性值。
针对这种情况，通常有可以采用以下两种方案。
第一种：将多值属性放到一个字段，该字段内容为key1:value1，key2:value2或者 value1，value2的形式。如课程类别属性，一个课程可以属于编程技术、后端开发等多个类别。

第二种：将多值属性放到多个字段，每个字段对应一个属性。这种方案只适用于多值属性个数固定的情况。

三、数据仓库的分层规划

数据仓库分层是指将数据按照不同的主题、粒度、类型等进行划分，并存储在不同的层中，以便于后续的数据分析和决策支持。

数仓分层没有定论，根据不同的业务需求，企业特点，数仓分层具有不同的特点。

1.源数据层(Operation Data Store)

ODS层是数据仓库中的操作性数据存储层，主要用于存储来自各个操作系统和业务系统的原始数据，通常是实时或近实时的数据。(离线数仓中存储的是前一天的业务数据)

ODS层的目标是提供对原始数据的快速访问，以支持操作性和实时的业务需求。ODS层通常保留了原始数据的结构和格式，但可能进行一些简单的数据清洗和转换。

①保持数据原貌不做任何修改，起到备份数据的作用
②数据采用压缩，减少磁盘存储空间（如：原始数据100G，可以压缩到10G左右）
③创建分区表，防止后续的全表扫描

ODS层表的构建通常根据外部业务数据库中的表进行映射构建，数据也来源于外部业务数据库。

在整个数仓的分析流程中，上一层的操作必须等待下一层操作的完成。因此，在数据抽取过程的效率也会影响到数仓分析流程的进行，所以需要尽可能的提高数据采集流程的效率，也就是让业务数据到达ODS层的效率提高。

例如:

业务数据库到ODS层数据的转换:

1. 存储方式不改变，业务数据库使用行式存储，ODS层表也需要使用行式存储，减少存储方式的转换。
2. 数据格式不改变，外部数据使用CSV格式存储，ODS层表也需要使用CSV格式存储。
3. 压缩格式不改变，外部数据使用snappy格式压缩，ODS层表也需要使用snappy格式压缩。
4. 异构数据可以改变，不同结构但同类型的数据，例如两条不同结构的json日志数据，可以融合为一张表进行存储，避免产生更多的表数据。
5. 汇总不同时间的数据，数仓需要存储大量历史数据，需要将多个日期数据，汇总到一张表(分区)。

Operation Data Store层，虽然目的是存储原始数据，但是因为数据来源于各种业务系统，如果无法保留格式的存储到数据仓库中，也需要对数据进行一定的处理融合， 统一标准。

2.公共维度层(DIM)

基于维度建模理论进行构建,存放维度模型中的维度表,保存一致性维度信息。
维度表需要经常查询,压缩方式选择速度快的,维度表通常分为全量快照表和拉链表

全量快照表:计算周期通常为每天一次，所以可以每天保存一份全量的维度数据。
通常当一张表的数据量较小，并且数据变化程度高时，非常契合全量快照。

拉链表:通过新增两个start_date和end_date时间字段表明一条数据的有效区间，当一条数据至今有效时，end_date通常为日期最大值9999-12-31 (一些企业禁止使用拉链表，因为拉链表处理复杂，存储开销大)

3.数据仓库层(DW)

DW层的数据是一致的、准确的、干净的数据，即对源数据进行了清洗（去除了杂质）后的数据。主要是用于面向数据分析的，大部分的工作，都是在这一层，写sql。

3.1、明细层(DWD)

根据业务要求，将ODS数据和DIM层数据结合分析后抽取到DWD层，形成一份最详细业务明细数据，同时该层也会做一部分的数据聚合，将相同主题的数据汇集到一张表中，提高数据的可用性。
(本层能够存放事实表,保存各个业务过程最小粒度的操作记录)

本层需要根据业务过程搭建事务事实表，一个业务过程对应一张表，并且粒度的声明应尽量小，越小的粒度能够处理的指标就越多

事务事实表通常按照日期进行分区，一般采用增量同步，但首日同步需采用全量同步。

DWD层会对ODS层数据做加工处理，让数据变得更详细准确，为后边的统计分析做准备。因此为了便于分析处理，通常需要将数据存储方式更改为列式存储。

3.2、业务层(DWS)

根据DWD层的详细业务数据和DIM层的维度数据，按照数据域和数据主题，生成数据汇总表(例如:根据多张DWD层的业务数据表，生成最近1日汇总表，最近n(7/30)日汇总表，历史至今汇总表)

交易域订单明细事务事实表 + 机构维度表 + 地区维度表 = 交易域机构货物类型粒度下单 1 日汇总表

最近n(7/30)日汇总表只需要通过最近1日汇总表筛选出对应日期数据后在进行计算便可得出最近7/30日数据，该类型表不需要进行每日数据加载，因为7d和30d数据存在重复计算。

历史至今汇总表，根据业务分析指标的不同，只需要少数表需要统计历史至今的数据记录，该表的构建对一些指标分析能提供很大方便。

(本层数据存储的是根据上层指标需求所构建出来的表,目的是为了减少重复计算,存储需求公用的数据表)

数据来源是DWD层中构建的数据明细表，将DWD层中的数据根据上层指标做汇总，方便数据的重复使用。

4.数据展示层(ADS层)

前端应用直接读取的数据源；根据报表、专题分析需求而计算生成的数据。数据的报表展示。

ADS层表根据最终业务分析指标构建，该表数据应可直接用来进行Web端的可视化展示，该层的表数据需要导出到外部关系型数据库中供可视化使用。

值得注意的是:数仓的分层从不固定，每个企业的需求不同，数仓的分层构建也可能存在差异。

四、数据仓库的构建流程

1.数据调研

数据调研重点要做两项工作，分别是业务调研和需求分析。这两项工作做的是否充分，直接影响着数据仓库的质量。

业务调研:业务调研的主要目标是熟悉业务流程、熟悉业务数据。

1. 熟悉业务流程要求做到，明确每个业务的具体流程，要将该业务所包含的每个业务过程一一列举。

2. 熟悉业务数据要求做到，将数据（包括埋点日志和业务数据表）与业务过程对应起来，明确每个业务过程会对哪些表的数据产生影响，以及产生什么影响。产生的影响，需要具体到，是新增一条数据，还是修改一条数据，并且需要明确新增的内容或者是修改的逻辑。

2.明确数据域

数据仓库模型设计除横向的分层外，通常也需要根据业务情况进行纵向划分数据域。划分数据域的意义是便于数据的管理和应用。

通常可以根据业务过程或者部门进行划分，需要注意的是一个业务过程只能属于一个数据域。

以下是物流数仓的数据域划分情况:

数据域	业务过程
交易域	下单，支付成功，取消运单
物流域	揽收（接单）、发单、转运完成、派送成功、签收、运输
中转域	入库、分拣、出库

数据域是指数据仓库中的数据来自于不同的业务系统，这些数据在数据仓库中是按照不同的数据主题进行组织的，每个数据主题对应一个数据域。数据域是数据仓库中的基础数据组织方式，它是数据仓库中进行数据分析和决策支持的基础。

主题域是指数据仓库中的数据按照特定的业务主题进行组织，例如客户、产品、销售等。每个主题域都包含多个数据域，这些数据域是围绕特定业务主题的数据集合。主题域是数据仓库中进行业务分析和决策支持的基本单位。

数据域和主题域都是数据组织的方式，但它们的的角度不同。数据域是从数据来源和数据类型的角度进行组织，而主题域是从业务主题和数据分析角度进行组织。在数据仓库中，数据域是基础，主题域是更高层次的组织方式，它使得数据更加贴近业务需求，更加易于分析和决策支持。

3.构建业务总线矩阵

业务总线矩阵中包含维度模型所需的所有事实（业务过程）以及维度，以及各业务过程与各维度的关系。矩阵的行是一个个业务过程，矩阵的列是一个个的维度，行列的交点表示业务过程与维度的关系。

一个业务过程对应维度模型中一张事务型事实表，一个维度则对应维度模型中的一张维度表。所以构建业务总线矩阵的过程就是设计维度模型的过程。但是需要注意的是，总线矩阵中通常只包含事务型事实表，另外两种类型的事实表需单独设计。

4.明确统计指标

明确统计指标具体的工作是，深入分析需求，构建指标体系。构建指标体系的主要意义就是指标定义标准化。所有指标的定义，都必须遵循同一套标准，这样能有效的避免指标定义存在歧义，指标定义重复等问题。

（1）原子指标
原子指标基于某一业务过程的度量值，是业务定义中不可再拆解的指标，原子指标的核心功能就是对指标的聚合逻辑进行了定义。我们可以得出结论，原子指标包含三要素，分别是业务过程、度量值和聚合逻辑。

例如订单总额就是一个典型的原子指标，其中的业务过程为用户下单、度量值为订单金额，聚合逻辑为sum()求和。需要注意的是原子指标只是用来辅助定义指标一个概念，通常不会对应有实际统计需求与之对应。

（2）派生指标
派生指标基于原子指标，其与原子指标的关系如下图所示。

与原子指标不同，派生指标通常会对应实际的统计需求。请从图中的例子中，体会指标定义标准化的含义。

（3）衍生指标
衍生指标是在一个或多个派生指标的基础上，通过各种逻辑运算复合而成的。例如比率、比例等类型的指标。衍生指标也会对应实际的统计需求。

明确统计指标对于数仓建模的意义
绝大多数的统计需求，都可以使用原子指标、派生指标以及衍生指标这套标准去定义。同时能够发现这些统计需求都直接的或间接的对应一个或者是多个派生指标。

当统计需求足够多时，必然会出现部分统计需求对应的派生指标相同的情况。这种情况下，我们就可以考虑将这些公共的派生指标保存下来，这样做的主要目的就是减少重复计算，提高数据的复用性。

这些公共的派生指标统一保存在数据仓库的DWS层。因此DWS层设计，就可以参考我们根据现有的统计需求整理出的派生指标。