数仓建模理论

Kimball和Inmon架构

数据仓库，简称数仓，主要有四种架构，Kimball的DW/BI架构、独立数据集市架构、辐射状企业信息工厂Inmon架构、混合Inmon与Kimball架构。不过不管是那种架构，基本上都会使用到维度建模。

• Kimball 模式从流程上看是是自底向上的，即从数据集市到数据仓库再到数据源(先有数据集市再有数据仓库)的一种敏捷开发方法。对于Kimball模式，数据源每每是给定的若干个数据库表，数据较为稳定可是数据之间的关联关系比较复杂，须要从这些OLTP中产生的事务型数据结构抽取出分析型数据结构，再放入数据集市中方便下一步的BI与决策支持。所以KimBall是根据需求来确定需要开发ETL哪些数据。以需求为导向，一般使用星型模型；
• Inmon 模式从流程上看是自顶向下的，即从数据源到数据仓库再到数据集市的（先有数据仓库再有数据市场）一种瀑布流开发方法。在Inmon模式中，并不强调事实表和维度表的概念，由于数据源变化的可能性较大，须要更增强调数据的清洗工做，从中抽取实体-关系。immon是将整个数据仓库规划好，统一按照范式建模进行开发。以数据源为导向，模型偏向于3NF。

两种架构的简单对比：

特性	Kimball	Inmon
时间	快速交付	路漫漫其修远兮
开发难度	小	大
维护难度	大	小
技能要求	入门级	专家级
数据要求	特定业务	企业级

类比实际项目场景（各自适应不同的应用场景）：

• Kimball，做BI数仓，以BI需求为导向，分析要同步的数据源表、数据加工处理逻辑。
• Inmon，做企业全域数仓，盘点有哪些数据源、哪些业务数据，然后看能基于这些数据去出哪些数据集市和分析结果。

宽表

宽表从字面意义上讲就是字段比较多的表。通常是指业务主题相关的指标、维度、属性关联在一起的表。

通常做法是把很多的维度关联到事实表中，形成一张既包含了大量维度又包含了相关事实的表。

宽表的误区

不是所有层级都适合做宽表即冗余维表数据（比如dwd/dws）。

宽表的使用，有其一定的便利性。使用方不需要再去考虑跟维度表的关联，也不需要了解维度表和事实表是什么东西。

但是随着业务的增长，始终无法预见性地设计和定义宽表究竟该冗余多少维度，也无法清晰地定义出宽表冗余维度的底线在哪里。

一个可能存在的情况是，为了满足使用上的需求，要不断地将维表中已经存在的列增加到宽表中。这会导致宽表的表结构频繁发生变动。

模型的选择：维度模型 VS 宽表

维度模型和宽表的对比：

比较点	维度建模	宽表
扩展性	维度表变更，事实表可能不影响	维度变更可能导致很多宽表都要调整
耦合度	事实表和维度表解藕，某些粒度上不会因为维度表失败而影响聚合表的产出	一个非重要任务失败会导致整个宽表无法产出
组织方式	任务及工作流易组织	因高耦合导致任务之间盘根错节，不利于组织任务和工作流
数据一致性	企业级数据仓库总线架构的基石	底层如果没有维度建模支撑，容易陷入混乱
易用性	维表需要多几个维表关联	宽表一时爽
适用层级	数据公共层(dwd/dws/dwm)	数据应用层(dm/ads)

维度模型和宽表可结合使用，在数仓层(dw)建议选择采用标准的维度建模的方式—星型模型。而宽表则可以存在于更靠后的数据集市层(dm)。

• 扩展性：想要让dw层表设计更有扩展性，即少量新字段需求不用每次都调整表结构，可以提前预留10个或多个attribute扩展字段，少量新字段需求存扩展字段中，按需使用。

• • 表新增字段后，SQL语句好调整，但是涉及表结构调整比如新增字段，一些不规范写法(比如使用select * ：insert into target_table as select * from xxxx）会因xxxx表结构变更后导致原本正常的SQL语句直接报错。

• • • 报错：SemanticException [Error 10044]：Table insclause-0 has 3 columns, but query has 4 columns ， 查询的结果和写入的表列数对不上

• • 所以：insert插入语句中使用select * 其实有风险，尤其是需求开发频繁调整的模块，更容易因为表结构的变更导致SQL语句出错，写明字段会使程序更健壮。

粒度

粒度问题是设计数据仓库的一个最重要方面。粒度是指数据仓库的数据单位中保存数据的细化或综合程度的级别。细化程度越高，粒度级就越小；相反，细化程度越低，粒度级就越大。

笼统的说，粒度就是维度的组合。一般常说的按统计粒度，就是指按哪些维度条件做分析(求和、求平均等)计算。

OLTP和OLAP

OLTP

OLTP（on-line transaction processing），联机事务处理，主要是对数据的增删改，是数据库的应用。

OLTP主要用来记录某类业务事件的发生，如购买行为，当行为产生后，系统会记录是谁在何时何地做了何事，这样的一行（或多行）数据会以增删改的方式在数据库中进行数据的更新处理操作，要求实时性高、稳定性强、确保数据及时更新成功，像公司常见的业务系统如ERP，CRM，OA等系统都属于OLTP。（mysql、oracle、sqlserver等支持事务的数据库）

对事务的理解：

OLAP

OLAP（On-Line Analytical Processing），联机分析处理，主要是对数据的查询，是数据仓库的应用。

当OLTP的数据积累到一定的程度，我们需要对过去发生的事情做一个总结分析时，就需要把过去一段时间内产生的数据拿出来进行统计分析，从中获取我们想要的信息，为公司做决策提供支持，这时候就是在做OLAP了。

因为OLTP所产生的业务数据分散在不同的业务系统中，而OLAP往往需要将不同的业务数据集中到一起进行统一综合的分析，这时候就需要根据业务分析需求做对应的数据清洗后存储在数据仓库中，然后由数据仓库来统一提供OLAP分析。常用的OLAP引擎。

两者对比

比较点	OLTP	OLAP
业务目的	处理业务，如订单、合同等	数据分析，业务支持决策
面向对象	业务处理人员	分析决策人员
主要工作负载	增、删、改	查询
主要衡量指标	事务吞吐量（TPS）	查询响应速度（QPS）
数据库设计	3NF 或 BCNF	星型/雪花模型

OLAP和OLTP之间的关系可以认为OLAP是依赖于OLTP的，因为OLAP分析的数据都是由OLTP所产生的，也可以看作OLAP是OLTP的一种延展，一个让OLTP产生的数据发现价值的过程。

水平分区和垂直分区

水平分区：

• 对表的行数进行分区，比如mysql/oracle的分区表，有分区列；
• 比如一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录，这里的分区列就是年份字段；

垂直分区：

• 拆表，通过对表的垂直划分来减少目标表的宽度
• 比如：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。

以上规则，水平分区同样适用于hive大数据仓库，垂直分区不太适用（ORC格式是列存）。

数仓分层原则

优秀可靠的数仓体系，往往需要清晰的数据分层结构，既要保证数据层的稳定又要屏蔽对下游的影响，并且避免链路过长。一个好的分层架构，需要有以下好处（可结合数据流向参考图理解）：

• 清晰的数据结构：每一个数据分层都有它的作用域，这样我们在使用表的时候能更方便地定位和理解。
• 数据血缘追踪：简单理解，我们最终给业务呈现的是一张能直接使用的张业务表，但是它的来源有很多，如果有一张来源表出问题了，我们希望能够快速准确地定位到问题，并清楚它的危害范围。
• 减少重复开发：规范数据分层，开发一些通用的中间层数据，能够减少极大的重复计算
• 复杂问题简单化：将一个复杂的任务分解成多个步骤来完成，每一层只处理单一的步骤，比较简单和容易理解。而且便于维护数据的准确性，当数据出现问题之后，可以不用修复所有的数据，只需要从有问题的步骤开始修复
• 屏蔽原始数据的影响：简单理解，模型逻辑和字段不变的情况下，原始数据字段的新增、修改和变更只影响到STG/ODS的字段mapping对应关系，不影响后续DW模型层处理逻辑。

各层级典型数据流向参考：

数据贴源层

贴源层一般会细分为：STG(数据缓冲层) 和 ODS(原始数据层)

1. 1. 数据缓冲层：STG：

针对数据源的大数据量表，一般会采用增量同步的策略，增量同步的数据一般会放在数据缓冲层，只存增量数据(即和ODS相比，STG只有部分增量数据)，然后再将数据更新或覆盖到ODS(原始数据层)表中。

该层一般只为ODS层服务。

1. 1. 原始数据层：ODS：Operation Data Store

ODS层是最接近数据源中数据的一层，为了考虑后续可能需要追溯数据问题，因此对于这一层不建议做过多的数据清洗工作，原封不动的接入原始数据即可，数据的去重、去噪、异常值处理等可以放到后面的dwd层来做。

ODS的表结构和数据需要与业务系统基本保持一致，仅做简单整合、非结构化数据结构化处理或者增加标识数据日期描述信息，不做深度清洗加工。

• 字段名：与业务系统字段名保持一致，字段类型也尽可能保持一致
• 对于数据量比较大的业务表，采用增量同步的方式，则要同时建立增量表(STG)和全量表(ODS)。
• 对于日志、文件等半结构数据，不仅要存储原始数据，还要存储结构化之后的数据。

数据公共层

数据公共层是我们在做数据仓库时要核心设计的一层，在这里，从ODS层获得的数据按照主题建立各种数据模型。数据公共层又细分为DWD（明细数据层）、DWS（轻度汇总层）、DIM（维度表）、DWM（中间层，未在分层图中展示）

1. 1. 数据明细层：DWD(数据清洗/DWI) Data Warehouse Detail

对ODS层数据进行数据清洗、整合、规范化，脏数据、垃圾数据、规范不一致的、状态定义不一致的、 命名不规范的数据都会被处理，生成DWD层数据。

同时，为了提高数据明细层的易用性， 该层会采用一些维度退化手法， 将维度退化至事实表中， 减少事实表和维表的关联。基于维表建模，明细宽表，复用关联计算，减少数据扫描。

1. 1. 轻度汇总层：DWS(宽表-用户行为，轻度聚合) Data Warehouse Service

DWS 层为公共汇总层， 会进行轻度汇总， 粒度比明细数据稍粗， 基于 DWD 层上的基础数据， 整合汇总成分析某一个主题域的服务数据， 一般是宽表。

1. 1. 数据中间层：DWM (中间表数据)  Data Warehouse Middle

在实际计算中， 如果直接从 DWD 或者 ODS 计算出宽表的统计指标， 会存在计算量太大并且维度太少的问题， 因此一般的做法是， 在 DWM 层先计算出多个小的中间表， 然后再拼接成一张 DWS 的宽表。可以理解为DWM为中间表层(dwd->dwm->dws)，复杂的计算需要拆表时可以定义DWM中间表用于存放中间结果数据。

1. 1. 维表层：DIM (Dimension)

维度表一般是主数据比如用户数据、配置数据等，如果主数据/维表比较多，可以针对维表单独设计一层，维表层主要包含两部分数据：

• 高基数维度数据：一般是用户资料表、 商品资料表类似的资料表。 数据量可能是千万级或者上亿级别
• 低基数维度数据：一般是配置表， 比如枚举值对应的中文含义， 或者日期维表。数据量可能是个位数或者几千几万

维度表存主数据，DWD/DWS层存维度表主键，最后的DM层如果是宽表需要冗余所有的字段，可在数据落DM表时，通过DW表的维度表主键关联维度表取数据， 设计的好处：维度主数据如果有变更（比如名称、说明等变化），只需要更新dim层表数据即可，中间DW层无需刷新历史数据。

维度表又细分为三种

• 单级维（星形模型）
• 多级维（雪花模型）
• 缓慢变化维

• • 缓慢变化维：维度建模的数据仓库中，有一个概念叫Slowly Changing Dimensions，中文一般翻译成“缓慢变化维”，经常被简写为SCD。缓慢变化维的提出是因为在现实世界中，维度的属性并不是静态的，它会随着时间的流逝发生缓慢的变化。这种随时间发生变化的维度我们一般称之为缓慢变化维，并且把处理维度表的历史变化信息的问题称为处理缓慢变化维的问题，有时也简称为处理SCD的问题。
  • 处理缓慢变化维的方法通常分为三种方式：

• • • 第一种方式是直接覆盖原值。这样处理，最容易实现，但是没有保留历史数据，无法分析历史变化信息。第一种方式通常简称为“TYPE 1”。
    • 第二种方式是添加维度行。这样处理，需要代理键的支持。实现方式是当有维度属性发生变化时，生成一条新的维度记录，主键是新分配的代理键，通过自然键可以和原维度记录保持关联。第二种方式通常简称为“TYPE 2”。
    • 第三种方式是添加属性列。这种处理的实现方式是对于需要分析历史信息的属性添加一列，来记录该属性变化前的值，而本属性字段使用TYPE 1来直接覆盖。这种方式的优点是可以同时分析当前及前一次变化的属性值，缺点是只保留了最后一次变化信息。第三种方式通常简称为“TYPE 3”。

• • 在实际建模中，我们可以联合使用三种方式，也可以对一个维度表中的不同属性使用不同的方式，这些，都需要根据实际情况来决定，但目的都是一样的，就是能够支持方便的分析历史变化情况。

数据应用层

1. 1. DM（数据集市层）：DataMart

在这里， 主要是提供给数据产品和数据分析使用的数据，针对特定业务或者报表需求的结果表，偏定制化，一般是宽表。 一般数据会按需存放在 PostgreSql、Doris、Greenplum、Clcikhouse、Mysql 等系统中供线上系统使用， 也可能会存在 Hive 中供数据分析和数据挖掘使用。 比如我们经常说的报表数据， 一般就放在这里。

数据集市层 根据主题域的不同可以在物理上进行划分——它表现为多个相互独立的库，各个数据集市之间不允许做数据依赖。每个数据集市可以由该主题域的使用方在数据仓库规范下自行开发和建设。

1. 1. ADS(应用层 APP/DAL/DF/DM)- 出报表结果 Application Data Store

基于应用的数据组装：宽表集市、趋势指标，个性化指标加工：定制化、复杂性指标（大部分复合指标），做分析处理同步到RDS数据库里边。

作用等价于DM数据集市层，一般项目上使用其中之一就好了，常用DM层；

数仓存储架构

数仓模型设计原则

高内聚、 低耦合

即主题内部高内聚、 不同主题间低耦合。 明细层(dwd)按照业务/业务过程划分主题， 汇总层(dws)按照“ 实体+ 活动” 划分不同分析主题， 应用层(dm)根据应用需求划分不同应用主题。（Ps：各层级主题也可按照统一的业务/业务过程进行简单划分，按项目情况调整即可）

将业务相近或者相关、粒度相同的数据设计为一个逻辑或者物理模型：将高概率同时访问的数据放一起 ，将低概率同时访问的数据分开存储。

核心模型和扩展模型要分离

建立核心模型与扩展模型体系， 核心模型包括的字段支持常用的核心业务（即相对通用的dw表）， 扩展模型包括的字段支持个性化或少量应用的需要（定制化扩展表，适用于一个dw模型表需要适配N多复杂场景共用的情况，个性化需求添加扩展表的模式）， 不能让扩展模型的字段过度侵入核心模型， 以免破坏核心模型的架构简洁性与可维护性（过度侵入后，导致每次增加新需求，都需要调整dw公共表结构，过于频繁的修改dw公共模型表会导致其他稳定的流程出问题bug）。

• 小tips：

• • 扩展字段：为适配新增的需求，可在设计dw表时增加扩展字段(att1-20)，少部分新增字段需求可存储在扩展字段中，或者新增扩展表处理。
  • 维度字段处理：维度表除主键外的其他易变更的字段(比如说明、备注等)，在dm层落表时再关联获取，dw层只存储维度表id及编码等不可变字段(如果中间dw层会用到这些不可变字段的话)
  • 维度模型VS宽表：dw层更建议使用维度建模，dm层走宽表

公共处理逻辑下沉及单一

越是底层公用的处理逻辑越应该在数据调度依赖的底层进行封装与实现， 不要让公用的处理逻辑暴露给应用实现， 不要让公共逻辑多处同时存在。

成本与性能平衡

适当的数据冗余可换取查询和刷新性能， 不宜过度冗余与数据复制。（这个度，需要项目上结合实际情况把控）

数据可回滚

处理逻辑不变， 在不同时间多次运行数据结果确定不变。（对代码实现的要求，可重运行，结果不变，比如STG到ODS的数据插入，要考虑数据更新/清空机制，避免运行N次数据重复N倍）

一致性

相同含义的字段命名及定义，在不同表中保持一致

命名清晰、可理解

表命名需清晰、一致，表名需易于使用方理解

3种数据模型对比

属性	星型模型（星座模型）	雪花模型
事实表	1张或多张	1张或多张
维表	一级维表	多层级维表
存储空间	多	少
数据冗余度	高	低
可读性	高	低
表个数	少	多
表宽度	宽	窄
查询逻辑	简单	复杂
查询性能	高	低
扩展性	差	好

通过上面的对比分析，可以发现数据仓库大多数时候比较适合使用星型模型来构建底层数据 hive 表，通过大量的数据冗余来减少表查询的次数以提高查询效率。星型模型对OLAP的分析引擎比较友好，而雪花模型在关系型数据库中（MySQL/Oracle）中非常常见 即3NF范式设计，尤其是在电商的数据库表。在具体规划设计时，应结合具体场景及两者的优缺点来进行设计，找到一个平衡点。