浅谈数据仓库模型设计

目录

一、背景

1.引言

2.目的与范围

3.使用对象

4.分层意义

二、数据仓库(ETL的四个操作)

三、数据仓库的技术架构

四、数仓分层架构

1.贴源层(ODS: Operational Data Store)

1.数据主要来源

2.数据存储策略(增量、全量)

3.数据抽取

1. 增量抽取

2.全量抽取

3.命名规范

2.数仓层(DW: data warehouse)

设计基本原则

1.公共维度层(DIM:Dimension)

2.DWD(data warehouse detail)数据明细层

3. DWS(data warehouse service)数据服务层，汇总层宽表

1. 公共汇总事实表设计原则

2.公共汇总事实表规范

4.应用层ADS(application Data Service)应用数据服务层

1.方案

五、层次调用规范

一、背景

1.引言

         数据模型将数据有序的组织和存储起来之后，大数据才能得到高性能、低成本、高效率、高质量的使用

2.目的与范围

        数据模型用于有效组织企业的数据资产，其设计工作应当在一定的规范约束下进行，这是建设高质量数据模型的前提条件。

3.使用对象

1. 数据架构师
2. 数据开发者
3. 数据应用人员
4. 企业数据管理者

4.分层意义

1)清晰数据结构:每一个数据分层都有它的作用域，这样我们在使用表的时候能更方便地定位和理解。

数据关系条理化:源系统间存在复杂的数据关系，数据仓库会对相同主题的数据进行统一建模，把复杂的数据关系梳理成条理清晰的数据模型。

2)数据复用,减少重复开发：规范数据分层，开发一些通用的中间层数据，能够极大减少的重复计算。数据的逐层加工原则，下层包含了上层数据加工所需要的全量数据，这样的加工方式避免了每个数据开发人员都重新从源系统抽取数据进行加工。通过汇总层的引人，避免了下游用户逻辑的重复计算，节省了用户的开发时间和精力，同时也节省了计算和存储。极大地减少不必要的数据冗余，也能实现计算结果复用，极大地降低存储和计算成本。

3)把复杂问题简单化:把一个复杂的任务分解成多个步骤来完成，每一层只处理单一的步骤，比较简单和容易理解。而且便于维护数据的准确性，当数据出现问题之后，可以不用修复所有的数据，只需要从有问题的步骤开始修复。

4)屏蔽原始数据的(影响) 屏蔽业务系统的影响(例如系统数据切源):业务或系统发生变化时，不必每次调整业务系统就需要大规模的调整报表逻辑只需要重新接入数据即可。提高数据稳定性和连续性。

屏蔽源头业务系统的复杂性：源头系统可能极为繁杂，而且表命名、字段命名 、字段含义等可能五花八门，通过 DW 层来规范和屏蔽所有这些复杂性，保证下游数据用户使用数据的便捷和规范。如果源头系统业务发生变更，相关的变更由 DW 层来处理，对下游用户透明，无须改动下游用户的代码和逻辑。

数据仓库的可维护性:分层的设计使得某一层的问题只在该层得到解决，无须更改下一层的代码和逻辑。大数据系统需要数据模型方法来帮助更好地组织和存储数据，以便在性能、成本、效率和质量之间取得最佳平衡。

二、数据仓库(ETL的四个操作)

ETL(extraction transformation loading)负责将分散的、异构数据源中的数据抽取到临时中间层后进行清洗、转换、集成，最后加载到数据仓库或数据集市中。ETL 是实施数据仓库的核心和灵魂，ETL规则的设计和实施约占整个数据仓库搭建工作量的 60%～80%。

1. 数据抽取（extraction），包括初始化数据装载和数据刷新：初始化数据装载主要关注的是如何建立维表、事实表，并把相应的数据放到这些数据表中。
2. 数据清洗（cleaning）主要是针对源数据库中出现的二义性、重复、不完整、违反业务或逻辑规则等问题的数据进行统一的处理。即清洗掉不符合业务或者无效的的数据。
3. 数据转换（transformation）主要是为了将数据清洗后的数据转换成数据仓库所需要的数据：来源于不同业务系统的同一数据字段的数据字典或者数据格式可能不一样(比如A表中叫id,B表中叫ids)，在数据仓库中需要给它们提供统一的数据字典和格式，对数据内容进行归一化；另一方面，数据仓库所需要的某些字段的内容可能是源系统所不具备的，而是需要根据源系统中多个字段的内容共同确定。
4. 数据加载（loading）是将最后上面处理完的数据导入到对应的存储空间里（hbase，mysql等）以方便给数据集市提供，进而可视化。

三、数据仓库的技术架构

数据中台包含的内容很多，对应到具体工作中的话，它可以包含下面的这些内容：

• 系统架构：以大数据等组件为中心的架构体系
• 数据架构：顶层设计，主题域划分，分层设计，ODS-DW-ADS
• 数据建模：维度建模，业务过程-确定粒度-维度-事实表
• 数据管理：资产管理，元数据管理、主数据管理、数据标准、数据安全管理
• 辅助系统：调度系统、ETL系统、监控系统
• 数据服务：数据门户、机器学习数据挖掘、数据查询、分析、报表系统、可视化系统、数据交换分享下载

四、数仓分层架构

数据仓库标准上可以分为四层。但是这种划分和命名不是唯一的，一般数仓都是四层，也有分层大于四层，但是核心的理念都是从四层延伸出来

数据引入层（ODS，Operational Data Store，又称数据基础层）

将原始数据几乎无处理地存放在数据仓库系统中，结构上与源系统基本保持一致，是数据仓库的数据准备区。这一层的主要职责是将基础数据同步、存储。一般来说 ODS 层的数据和源系统的数据是同构的，主要目的是简化后续数据加工处理的工作。从数据粒度上来说 ODS 层的数据粒度是细的。ODS 层的表通常包括两类，一个用于存储当前需要加载的数据，根据具体需求设置生命周期(如果是库存数据建议不设置生命周期，便于回溯历史数据)。一个用于存储处理完后的历史归档数据。历史数据一般保存 3-6个月后根据清除，以节省空间。但不同的项目要区别对待，如果来源系统的数据量不大，可以保留更长的时间，甚至全量保存。

注意：

• 在这层，理应不是简单的数据接入，而是要考虑一定的数据清洗，比如异常字段的处理、字段命名规范化、时间字段的统一、无效数据删除等，一般这些很容易会被忽略，但是却至关重要。特别是后期我们做各种特征自动生成的时候，会十分有用。

• 离线方面：每日定时任务型：跑批任务，业务库，如我们典型的日计算任务，这里经常会使用Datax、Flume来抽取，比如我们每天定时抽取一次。每天凌晨算前一天的数据，早上起来看报表。这种任务经常使用 ODPS、Hive、Spark 来计算，最终结果写入 Hive、Hbase、Mysql、Es 或者 Redis 中。

1.数据主要来源

• 数据源是业务数据库，公司所有的系统产生的业务数据

• 是通过在客户端埋点上报，收集用户的行为日志，以及一些后端日志的日志类型数据源。对于埋点行为日志来说，一般会经过一个这样的流程，首先数据会上报到 Nginx 然后经过 Flume 收集，然后存储到 Kafka 这样的消息队列，然后再由实时或者离线的一些拉取的任务，拉取到我们的离线数据仓库 HDFS

• 外部数据（包括合作数据以及爬虫获得的数据），将所采集的数据汇总到一起

2.数据存储策略(增量、全量)

        实际应用中，可以选择采用增量、全量存储

• 增量存储

为了满足历史数据分析需求，在ODS层表中以天为单位的增量存储，以业务日期作为分区，每个分区存放日增量的业务数据。

说明:日志等事务性较强的ODS表适合增量存储方式。这类表数据量较大，采用全量存储的方式存储成本压力大。

• 全量存储

以天为单位的全量存储，以业务日期作为分区，每个分区存放截止到业务日期为止的全量业务数据。

3.数据抽取

实际应用中，可以选择采用增量、全量存储上云方式。

1. 增量抽取

   通过时间戳方式抽取增量数据，业务系统在源表上增加创建时间、修改时间字段，在创建、修改表记录时，同时更新时间戳字段的值。抽取任务运行时，进行全表扫描根据DataX、Flume 等同步工具通过限定创建时间、更新时间是当天来抽取数据。通过业务时间戳抽取增量数据到今日增量分区表，再将今日增量分区表merge前一日全量分区表，写入今日全量分区表中(使用full join + case when/union all + row_number over() )

-- merge1：
select
	 case when di.id is not null then di.id else df.id as id
	,case when di.id is not null then di.name else df.name as name
	,case when di.id is not null then di.age else df.age as age
from(
	select
		id
		,name
		,age
	from table_di -- 增量表
	where ds = '20231227'
) di
full join(
	select
		id
		,namea
		,age
	from table_df -- 全量表
	where ds = '20231226'
) df on di.id = df.id;

------------merge2----------------
select 
	 t1.id
	,t1.name
	,t1.age
	,t1.create_time
	,t1.update_time
from(
	select
		 b1.id
		,b1.name
		,b1.age
		,b1.create_time
		,b1.update_time
		,row_number() over(partition by b1.id order by b1.update_time desc) as num
	from(
		select
			 id
			,name
			,age
			,create_time
			,update_time
		from table_di -- 增量表
		where ds = '20231227'
		union all
		select
			 id
			,name
			,age
			,create_time
			,update_time
		from table_df -- 全量表
		where ds = '20231226'
	) b1
) t1
where t1.num = 1

优点:

1. 每天只抽取部分增量数据，数据量较少，定时任务提高执行效率。

弊端:

1. 只能获取最新的状态，无法捕获过程变更信息，比如电商购物场景，如果客户下单后很快支付，隔天抽取增量数据时，只能获取最新的支付状态，下单时的状态有可能已经丢失。针对此种问题，需要根据业务需求来综合判定是否需要回溯状态。
2. 会丢失已经被delete的记录。如果在业务系统中，将记录物理删除。也就无法进行增量抽取。一般情况下，要求业务系统不进行物理删除，只进行逻辑删除。
3. 表必须有主键、时间戳字段必须增加索引，可以提供增量抽取数据效率

2.全量抽取

        对于系统小表、枚举值表、可以使用DataX、Flume 进行全量抽取数据

3.命名规范

       可以考虑使用来源系统的库名+表名的形式来命名，便于运维中快速定位的表所在系统对于的数据库及表名 s_[database]_[table]

2.数仓层(DW: data warehouse)

        数据仓库层(DW)层：数据仓库层是我们在做数据仓库时要核心设计的一层，本层将从 ODS 层中获得的数据建立各种数据模型，每一个主题对应一个宏观的分析领域，数据仓库层排除对决策无用的数据，提供特定主题的简明视图。在DW层会保存BI系统中所有的历史数据。DW层又细分为维度层（DIM）、明细数据层（DWD/FCT）和汇总数据层（DWS），采用维度模型方法作为理论基础，可以定义维度模型主键与事实模型中外键关系，减少数据冗余，也提高明细数据表的易用性。在汇总数据层同样可以关联复用统计粒度中的维度，采取更多的宽表化手段构建公共指标数据层，提升公共指标的复用性，减少重复加工。

1. 维度层（DIM:Dimension）：以维度作为建模驱动，基于每个维度的业务含义，通过添加维度属性、关联维度等定义计算逻辑，完成属性定义的过程并建立一致的数据分析维表。为了避免在维度模型中冗余关联维度的属性，基于雪花模型构建维度表。
2. 明细数据层（DWD:Data Warehouse Detail）：以业务过程作为建模驱动，基于每个具体的业务过程特点，构建最细粒度的明细事实表。可将某些重要属性字段做适当冗余，也即宽表化处理。
3.  汇总数据层（DWS:Data Warehouse Summary）：以分析的主题对象作为建模驱动，基于上层的应用和产品的指标需求，构建公共粒度的汇总指标表。以宽表化手段物理化模型，构建命名规范、口径一致的统计指标，为上层提供公共指标，建立汇总宽表、明细事实表。

设计基本原则

1.         高内聚和低耦合：一个逻辑和物理模型由哪些记录和字段组成，应该遵循最基本的软件设计方法论的高内聚和低耦合原则。主要从数据业务特性、访问特性、计算特性、时间特性等方面来考虑：将业务相近或者相关的数据、粒度相同数据设计为一个逻辑或者物理模型；将高概率同时访问的数据放一起，将低概率同时访问的数据分开存储，针对计算依赖的数据产出时间是否相近，计算是否能同时产出等原则确定组合在一起还是拆分。
2.         核心模型与扩展模型分离：建立核心模型与扩展模型体系，核心模型包括的字段支持常用核心的业务，扩展模型包括的字段支持个性化或是少量应用的需要，必要时让核心模型与扩展模型做关联，不能让扩展字段过度侵入核心模型，破坏了核心模型的架构简洁性与可维护性。
3.         公共处理逻辑下沉及单一：越是底层公用的处理逻辑更应该在数据调度依赖的底层进行封装与实现，不要让公共的处理逻辑暴露给应用层实现，不要让公共逻辑在多处同时存在。公共处理逻辑下沉及单一，既有利于数据复用，也有利于变更修改。
4.         成本与性能平衡：适当的数据冗余换取查询和刷新性能，但是不宜过度冗余与数据复制。
5.         数据可回滚：数据计算处理逻辑不变，在不同时间多次运行数据结果确定不变。
6.         一致性：相同的字段在不同表字段命名和定义相同。
   1.         命名清晰可理解：表命名规范需清晰、一致，易于下游理解和使用。

1.公共维度层(DIM:Dimension)

        公共维度汇总层（DIM）主要由维度表（维表）构成。维度是逻辑概念，是衡量和观察业务的角度。维表是根据维度及其属性将数据平台上构建的物理化的表，采用宽表设计的原则。因此，构建公共维度汇总层（DIM）首先需要定义维度。

• 高基数维度数据：一般是用户资料表、商品资料表类似的资料表。数据量可能是千万级或者上亿级别。

设计维表

完成维度定义后，您就可以对维度进行补充，进而生成维表了。

• 维表中数据的稳定性。
• 是否需要垂直拆分。如果一个维表存在大量属性不被使用，或由于承载过多属性字段导致查询变慢，则需考虑对字段进行拆分，创建多个维表。
• 核心的维表产出时间通常有严格的要求(执行时间？每天运行结束时间)。

设计维表的主要步骤如下：

1. 完成维度的初步定义，并保证维度的一致性。
2. 确定主维表（中心事实表，本教程中采用星型模型）。此处的主维表通常是数据引入层（ODS）表，可以与业务系统同步。例如，mdm_shop是主数据门店表，此表即是主维表。
3. 确定相关维表。数据仓库是业务源系统的数据整合，不同业务系统或者同一业务系统中的表之间存在关联性。根据对业务的梳理，确定哪些表和主维表存在关联关系，并选择其中的某些表用于生成维度属性。
4. 确定维度属性，主要包括两个阶段。第一个阶段是从主维表中选择维度属性或生成新的维度属性；第二个阶段是从相关维表中选择维度属性或生成新的维度属性。

公共维度汇总层（DIM）维表规范

• 命名规范：dim_[{自定义命名标签}]

2.DWD(data warehouse detail)数据明细层

DWD(Data Warehouse Detail 明细粒度事实层)

        是业务层与数据仓库的隔离层，这一层主要解决一些数据质量问题和数据的完整度问题。以业务过程作为建模驱动，基于每个具体的业务过程特点，构建最细粒度的明细层事实表。可以结合企业的数据使用特点，将明细事实表的某些重要维度属性字段做适当冗余，即宽表化处理(维度退化)。明细粒度事实层的表通常也被称为逻辑事实表。该层一般保持和ODS层一样的数据粒度，并且提供一定的数据质量保证，在ODS的基础上对数据进行加工处理，提供更干净的数据。同时，为了提高数据明细层的易用性，该层会采用一些维度退化手法，当一个维度没有数据仓库需要的任何数据时，就可以退化维度，将维度退化至事实表中，减少事实表和维表的关联。
例如：订单id,这种量级很大的维度，没必要用一张维度表来进行存储，而我们一般在进行数据分析时订单id又非常重要，所以我们将订单id冗余在事实表中，这种维度就是退化维度。

DWD层核心操作？

①数据清洗过滤

• 去除废弃字段,去除格式错误的信息
• 去除丢失了关键字段的信息
• 过滤核心字段无意义的数据，比如订单表中订单id为null，支付表中支付id为空
• 去除不含时间信息的数据(这个看公司具体业务,但一般数据中都会带上时间戳,这样方便后续处理时,进行时间维度上信息分析处理和提取)
• 数据规范化,因为大数据处理的数据可能来资源公司不同部门,不同项目,不同客户端,这时候可能相同业务数据字段,数据类型,空值等都不一样,这时候需要在DWD层做抹平.否则后续处理使用时,会造成很大的困扰
  • 如boolean ,有使用0 1标识,也有使用true false标识的
  • 如字符串空值,有使用"",也有使用null,的,统一为null即可
  • 如日期格式,这种就差异性更大,需要根据实际业务数据决定,不过一般都是格式化为YYYY-MM-dd HH:mm:ss 这类标准格式

明细粒度事实表设计原则：

• 尽可能包含所有与业务过程相关的事实 。
• 只选择与业务过程相关的事实。
• 分解不可加性事实为可加的组件。
• 在选择维度和事实之前必须先声明粒度。
• 在同一个事实表中不能有多种不同粒度的事实。
• 事实的单位要保持一致。粒度
• 谨慎处理Null值。
• 使用退化维度提高事实表的易用性。

明细粒度事实表整体设计流程

方案

• 概念：是数据仓库的细节数据层，是对STAGE层数据进行沉淀，减少了抽取的复杂性，同时ODS/DWD的信息模型组织主要遵循企业业务事务处理的形式，将各个专业数据进行集中，明细层跟stage层的粒度一致，属于分析的公共资源
• 库与表命名。库名：ods,表名：初步考虑格式为ods日期业务表名,待定。
• 旧数据更新方式：直接覆盖

明细粒度事实层（DWD）规范

• 命名规范为：dwd_{业务板块/pub}_{数据域缩写}_[{自定义表命名标签缩写}] 

3. DWS(data warehouse service)数据服务层，汇总层宽表

        基于 DWD 明细数据层，我们会按照一些分析场景、分析实体等去组织我们的数据，组织成一些分主题的汇总数据层 DWS。明细粒度 ==> 汇总粒度DWS层（数据汇总层）宽表，面向主题的汇总，维度相对来说比较少，DWS是根据DWD层基础数据按各个维度ID进行粗粒度汇总聚合，如按交易来源，交易类型进行汇合。整合汇总成分析某一个主题域的服务数据，一般是宽表。以DWD为基础，按天进行轻度汇总。该层数据表会相对比较少，大多都是宽表(一张表会涵盖比较多的业务内容，表中的字段较多)。按照主题划分，如订单、用户等，生成字段比较多的宽表，用于提供后续的业务查询，OLAP分析，数据分发等。融合多个中间层数据，基于主题形成事实表，比如用户事实表、渠道事实表、终端事实表、资产事实表等等，事实表一般是宽表，在本层上实现企业级数据的一致性。

DWS层做了哪些事？

• DWS将DWD层的数据按主题进行汇总，按照主题放到一个表中，
• 比如用户主题下会将用户注册信息、用户收货地址、用户的征信数据放到同一张表中，而这些在DWD层是对应多张表的,按照业务划分，如流量、订单、用户等，生成字段比较多的宽表。

方案

• 概念：又称数据集市或宽表。按照业务划分，如流量、订单、用户等，生成字段比较多的宽表，用于提供后续的业务查询，OLAP分析，数据分发等。
• 数据生成方式：由轻度汇总层和明细层数据计算生成。
• 表schema：一般按天创建分区，没有时间概念的按具体业务选择分区字段。
• 库与表命名。库名：dws, 表名：初步考虑格式为：dws日期业务表名,待定。
• 旧数据更新方式：直接覆盖

1. 公共汇总事实表设计原则

• 聚集是指针对原始明细粒度的数据进行汇总。DWS公共汇总层是面向分析对象的主题聚集建模。最终的分析目标为：最近一天某个类目（例如：厨具）商品在各省的销售总额、该类目Top10销售额商品名称、各省用户购买力分布。因此，我们可以以最终交易成功的商品、类目、买家等角度对最近一天的数据进行汇总。
• 注意：
  • 聚集是不跨越事实的。聚集是针对原始星形模型进行的汇总。为获取和查询与原始模型一致的结果，聚集的维度和度量必须与原始模型保持一致，因此聚集是不跨越事实的。
  • 聚集会带来查询性能的提升，但聚集也会增加ETL维护的难度。当子类目对应的一级类目发生变更时，先前存在的、已经被汇总到聚集表中的数据需要被重新调整。

此外，进行DWS层设计时还需遵循以下原则：

• 数据公用性：需考虑汇总的聚集是否可以提供给第三方使用。您可以判断，基于某个维度的聚集是否经常用于数据分析中。如果答案是肯定的，就有必要把明细数据经过汇总沉淀到聚集表中。
• 不跨数据域。数据域是在较高层次上对数据进行分类聚集的抽象。数据域通常以业务过程进行分类，例如交易统一划到交易域下， 商品的新增、修改放到商品域下。

2.公共汇总事实表规范

• 命名规范：dws_{业务板块缩写/pub}_{数据域缩写}_{数据粒度缩写}[_{自定义表命名标签缩写}]。

4.应用层ADS(application Data Service)应用数据服务层

        数据应用层（ADS，Application Data Store）：存放数据产品个性化的统计指标数据，报表数据。主要是提供给数据产品和数据分析使用的数据，通常根据业务需求，划分成流量、订单、用户等，生成字段比较多的宽表，用于提供后续的业务查询，OLAP分析，数据分发等。从数据粒度来说，这层的数据是汇总级的数据，也包括部分明细数据。从数据的时间跨度来说，通常是DW层的一部分，主要的目的是为了满足用户分析的需求，而从分析的角度来说，用户通常只需要分析近几年的即可。从数据的广度来说，仍然覆盖了所有业务数据。

1.方案

• 概念：应用层是根据业务需要，由前面三层数据统计而出的结果，可以直接提供查询展现，或导入至数据库中使用。
• 数据生成方式：由明细层、轻度汇总层，数据集市层生成，一般要求数据主要来源于集市层。
• 表schema：一般按天创建分区

五、层次调用规范

ADS应用层优先调用数据仓库公共层数据。如果已经存在CDM层数据，不允许ADS应用层跨过CDM中间层从ODS层重复加工数据。CDM中间层应该积极了解应用层数据的建设需求，将公用的数据沉淀到公共层，为其他数据层次提供数据服务。同时，ADS应用层也需积极配合CDM中间层进行持续的数据公共建设的改造。避免出现过度的ODS层引用、不合理的数据复制和子集合冗余。

总体遵循的层次调用原则如下：文章来源地址https://www.toymoban.com/news/detail-769835.html

• ODS层数据不能直接被应用层任务引用。如果中间层没有沉淀的ODS层数据，则通过CDM层的视图访问。CDM层视图必须进行封装，保持表的可维护性与可管理性。
• CDM层任务的深度不宜过大（建议不超过10层）。
• 一个计算刷新任务只允许一个输出表，特殊情况除外。
• 如果多个任务刷新输出一个表（不同任务插入不同的分区），DataWorks上需要建立一个虚拟任务，依赖多个任务的刷新和输出。通常，下游应该依赖此虚拟任务。
• CDM汇总层优先调用CDM明细层，可累加指标计算。CDM汇总层尽量优先调用已经产出的粗粒度汇总层，避免大量汇总层数据直接从海量的明细数据层中计算得出。
• CDM明细层累计快照事实表优先调用CDM事务型事实表，保持数据的一致性产出。
• 有针对性地建设CDM公共汇总层，避免应用层过度引用和依赖CDM层明细数据。

到了这里，关于浅谈数据仓库模型设计的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！