离线数仓6.0--- 数据仓库 ER模型-范式理论,维度模型、维度建模理论之事实表、维度建模理论之维度表

简介: 离线数仓6.0--- 数据仓库 ER模型-范式理论,维度模型、维度建模理论之事实表、维度建模理论之维度表

这是本人的学习过程,看到的同道中人祝福你们心若有所向往,何惧道阻且长;

但愿每一个人都像星星一样安详而从容的,不断沿着既定的目标走完自己的路程,一起做星光下的赶路人;

最后想说一句君子不隐其短,不知则问,不能则学。

如果大家觉得我写的还不错的话希望可以收获关注、点赞、收藏(谢谢大家)

1.1 ER模型缺点

image.png

ER模型不适合数据仓库的建模操作,ER模型在创建的时候强调我们在分析问题是如何构建对象,如何构建对象的关系,不在乎性能问题,可能是业务变得特别复杂

这种建模方法的出发点是整合数据,其目的是将整个企业的数据进行组合和合并,并进行规范处理,减少数据冗余性,保证数据的一致性。这种模型并不适合直接用于分析统计。

2维度模型

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

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

下图为一个典型的维度模型,其中位于中心的SalesOrder为事实表,其中保存的是下单这个业务过程的所有记录。位于周围每张表都是维度表,包括Date(日期),Customer(顾客),Product(产品),Location(地区)等,这些维度表就组成了每个订单发生时所处的环境,即何人、何时、在何地下单了何种产品。从图中可以看出,模型相对清晰、简洁

image.png

image.png

维度建模以数据分析作为出发点,为数据分析服务,因此它关注的重点的用户如何更快的完成需求分析以及如何实现较好的大规模复杂查询的响应性能。


image.png

image.png

统计2022年下半年各个省份的订单销售总额

image.png

3、维度建模理论之事实表


3.1 事实表概述


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


3.1.1 事实表特点


事实表通常比较“细长”,即列比较少,但行比较多,且行的增速快。


3.1.2 事实表分类


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


3.2 事务性事实表


3.2.1 概述


事务型事实表用来记录个业务过程,它保存的是各业务过程的原子操作事件,即最细粒度的操作事件。粒度是指事实表中一行数据所表达的业务细节程度。


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


3.2.2 设计流程


设计事务事实表时一般遵循以下四个步骤。

选择业务过程->声明粒度->确认维度->确认事实


1、选择业务过程

在业务系统中,挑选我们感兴趣的业务过程,业务过程可以概括为一个个不可拆分的行为事件,例如电商交易中的下单,取消订单,付款,退单等都是业务过程。通常情况下,应该业务过程对应一张事务性事实表。


2、声明粒度

业务过程确定后,需要为每个业务过程声明粒度。即精确定义每张事务型事实表的每行数据表示是什么,应该尽可能选择最细粒度,以此来应对各种细节程度的需求。

典型的粒度声明如下:

订单事实表中一行数据表示的是一个订单中的一个商品项


3、确定维度

确定维度是指确定每张事务型事实表相关的维度有哪些。

确定维度时应该尽量选择与业务过程相关的环境信息。因为维度的丰富程度就决定了维度模型能够支持的指标丰富程度。


4、确定事实

此处的“事实”一词,指的是每个业务过程中的度量值(通常是可累加的数字类型的值,例如:次数、个数、件数、金额等)。


结果上述四个步骤,事务型事实表就基本设计完成了。第一步选择业务过程可以确定有哪些事务型事实表,第二步可以确定每张事务型事实表的每行数据是什么,第三步可以确定每张事务型事实表的维度外键,第四步可以确定每张事务型事实表的度量值字段。


3.2.3 不足


事务型事实表可以保存所有业务过程的最细粒度的操作事件,故理论上其可以支持与各种业务过程相关的各种统计粒度的需求。但对于某些特定类型的需求,其逻辑可能会比较复杂,或者效率可能会比较低下。例如:


1、存量型指标

例如商品库存,账户余额等。此处以电商中的虚拟货币为例,虚拟货币业务包含的业务过程主要包括获取货币和使用货币,两个业务过程各自对应这个事务型事实表,一张储存所有的获取货币的原子操作事件,另一张储存所有使用货币的原子操作事件。

假定现有一个需求,要求统计截至当日的各用户虚拟货币余额。由于获取货币和使用货币均会影响到余额,故需要对两张事务型事实表进行聚合,且需要区分两者对余额的影响(加或减),另外需要对两张表的全表数据聚合才能得到统计结果。

可以看到,不论是从逻辑上还是效率上考虑,这都不是一个好的方案。

2、多事务关联统计

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


逻辑上虽然并不复杂,但是其效率较低,因为下单事务事实表和支付事务事实表均为大表,大表join大表的操作应尽量避免。


可以看到,在上述两种场景下事务型事实表的表现并不理想。下面要介绍的另外两种类型的事实表就是为了弥补事务型事实表的不足的。


3.3 周期型快照事实表

3.3.1 概述


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


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


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


3.3.2 设计流程


1、确认粒度

周期型快照事实表的粒度可由采样周期和维度描述,故确定采样周期和维度后即可确定粒度。

采样周期通常选择每日。

维度可根据统计指标决定,例如指标为统计每个仓库中每种商品的库存,则可确定维度为仓库和商品。

确定完采样周期和维度后,即可确定该表粒度为每日-仓库-商品。


2、确认事实

事实也可根据统计指标决定,例如指标为统计每个仓库中每种商品的库存,则事实为商品库存。


3.3.3 事实类型


此处的事实类型是指度量值的类型,而非事实表的类型。事实(度量值)共分为三类,分别是可加事实,半可加事实和不可加事实。


1、可加类型

可加事实是指可以按照与事实表相关的所有维度进行累加,例如事务型事实表中的事实。


2、半可加事实

半可加事实是指只能按照与事实表相关的一部分维度进行累加,例如周期型快照事实表中的事实。以上述各仓库中各商品的库存每天快照事实表为例,这张表中的库存事实可以按照仓库或者商品维度进行累加,但是不能按照时间维度进行累加,因为将每天的库存累加起来是没有任何意义的。


3、不可加事实

不可加事实是指完全不具备可加性,例如比率型事实。不可加事实通常需要转化为可加事实,例如比率可转化为分子和分母。

3.4 累积型快照事实表


3.4.1 概述


累计快照事实表是基于一个业务流程中的多个关键业务过程联合处理而构建的事实表,如交易流程中的下单、支付、发货、确认收货业务过程。

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

1696926910254.jpg

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


3.4.2 设计流程


累积型快照事实表的设计流程同事务型事实表类似,也可采用以下四个步骤,下面重点描述与事务型事实表的不同之处。

选择业务过程→声明粒度→确认维度→确认事实。


1、选择业务过程

选择一个业务流程中需要关联分析的多个关键业务过程,多个业务过程对应一张累积型快照事实表。


2、声明粒度

精确定义每行数据表示的是什么,尽量选择最小粒度


3、确认维度

选择与各业务过程相关的维度,需要注意的是,每各业务过程均需要一个日期维度。


4、确认事实

选择各业务过程的度量值。


4、维度建模理论之维度表


4.1 维度表概述


维度表式维度建模的基础和灵魂。上面提到的事实表是紧紧围绕业务过程进行设计的,而维度表则围绕业务过程所处的环境进行设计的。维度表主要包含一个主键和各种维度字段,维度字段称为维度属性。


4.2 维度表设计步骤


1、确认维度表

在设计事实表时,已经确定了与每个事实表相关的维度,理论上每个相关维度均需对应一张维度表。要注意到,可能存在多个事实表与同一个维度都相关的情况,这种情况需保证维度的唯一性,即只创建一张维度表。另外,如果某些维度表的维度属性很少,例如只有一个某某名称,则可不创建该维度表,而把该表的维度属性直接增加到与之相关的事实表中,这个操作称为维度退化。


2、确定主维表和相关维表

此次的主维表和相关维表均指业务系统中与某维度相关的表,例如业务系统中与商品相关的表有sku_info,spu_info,base_trademark,base_category3,base_category2,base_category1等,其中sku_info就称为商品维度的主维表,其余表称为商品维度的相关维表。维度表的粒度通常与主维表相同。


3、确定维度属性

确定维度属性即确定维度表字段。维度属性主要来自于业务系统中与该维度对应的主维表和相关维表。维度属性可直接从主维表或相关维表中选择,也可通过进一步加工得到。

确定维度属性时,需要遵循以下要求:

(1)尽可能生产丰富的维度属性

维度属性是后续做分析统计时的查询约束条件、分组字段的基本来源,是数据易用性的关键。维度属性的丰富程度直接影响到数据模型能够支持的指标的丰富程度。

(2)尽量不使用编码,而使用明确的文字说明,一般可以编码和文字共存。

(3)尽量沉淀出通用的维度属性

有些维度属性的获取需要进行比较复杂的逻辑处理,例如需要通过多个字段拼接得到。为避免后续每次使用时的重复处理,可将这些维度属性沉淀到维度表中。


4.3 维度设计要点


4.3.1 规范化和反规范化


规范化是指使用一系列范式设计数据库的过程,其目的是减少数据冗余,增强数据的一致性。通常情况下,规范化之后,一张表的字段会拆分到多张表。


反规范化是指将多张表的数据冗余得到一张表,其目的是减少join操作,提高查询性能。在设计维度表时,如果对其进行规范化,得到的维度模型称为雪花模型,如果对其进行反规范化,得到的模型称为星型模型。

1696927026000(1).jpg

数据仓库系统的主要目的是用于数据分析和统计,所以是否方便用户进行统计分析决定了模型的优劣。采用雪花模型,用户在统计分析的过程中需要大量的关联操作,使用复杂度高,同时查询性能很差,而采用星型模型,则方便、易用且性能好。所以出于易用性和性能的考虑,维度表一般是很不规范化的。


4.3.2 维度变化


维度属性通常不是静态的,而是会随着时间变化的,数据窗口的一个重要特点就是反映历史的变化,所以如何保存维度的历史状态是维度设计的重要工作之一。保存维度数据的历史状态,通常有以下两种做法,分别是全量快照表和拉链表。

1、全量快照表

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

优点是简单而有效,开发和维护成本低,且方便连接和使用

缺点是浪费储存空间,尤其是当数据变化比例比较低的时候。


2、拉链表

拉链表的意义就在于能够更加高效地保存维度信息的历史状态。

(1)什么是拉链表

拉链表,记录每条信息的生命周期,一旦一条记录的生命周期结束,就重新开始一条新的记录,并把当前日期放入生效开始日期。

如果当前信息至今有效,在生效结束日期中填入一个极大值(如9999-12-31)

1696927090003.jpg

2)为什么要做拉链表

拉链表适合于:数据会发生变化,但是变化频率并不高的维度(即:缓慢变化维)

比如:用户信息回发生变化,但是每天变化的比例不高,如果数据量有一定规模,按照每日全量的方式保存效率很低。

1696927116050.jpg

(3)如何使用拉链表

通过,生效日期<=某个日期且生效日期>=某个日期,能够得到某个时间段的数据全量切片。

1696927141924.jpg


4.3.3 多值维度


维度表中的某个属性同时有多个值,称之为“多值属性”。例如商品维度的平台属性和销售属性,每个商品均有多个属性值。

针对这种情况,通常有可以采用以下两种方案。

第一种:将多值属性放到一个字段,该字段内容为key1.value1,key2-value2的形式,例如一个手机商品的平台属性值为“品牌:华为,系统:鸿蒙,CPU:麒麟990”。

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


恭喜大家看完了小编的博客,希望你能够有所收获,我一直相信躬身自问和沉思默想会充实我们的头脑,希望大家看完之后可以多想想喽,编辑不易,求关注、点赞、收藏(Thanks♪(・ω・)ノ)


相关实践学习
数据库实验室挑战任务-初级任务
本场景介绍如何开通属于你的免费云数据库,在RDS-MySQL中完成对学生成绩的详情查询,执行指定类型SQL。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库,高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准,可以对海量数据进行即时的多维分析透视和业务探索,快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
相关文章
|
1月前
|
SQL 存储 大数据
数据仓库(10)数仓拉链表开发实例
拉链表是数据仓库中特别重要的一种方式,它可以保留数据历史变化的过程,这里分享一下拉链表具体的开发过程。 维护历史状态,以及最新状态数据的一种表,拉链表根据拉链粒度的不同,实际上相当于快照,只不过做了优化,去除了一部分不变的记录,通过拉链表可以很方便的还原出拉链时点的客户记录。
165 13
数据仓库(10)数仓拉链表开发实例
|
1月前
|
存储 JSON 大数据
大数据离线数仓---金融审批数仓
大数据离线数仓---金融审批数仓
122 1
|
3月前
|
SQL 关系型数据库 MySQL
在云数据仓库AnalyticDB MySQL版中,有几个参数可能影响SELECT查询的执行及其稳定性
在云数据仓库AnalyticDB MySQL版中,有几个参数可能影响SELECT查询的执行及其稳定性【1月更文挑战第16天】【1月更文挑战第80篇】
290 4
|
1月前
|
分布式计算 大数据 Hadoop
数据仓库(13)大数据数仓经典最值得阅读书籍推荐
从事数仓工作,在工作学习过程也看了很多数据仓库方面的数据,此处整理了数仓中经典的,或者值得阅读的书籍,推荐给大家一下,希望能帮助到大家。建议收藏起来,后续有新的书籍清单会更新到这里。
159 2
数据仓库(13)大数据数仓经典最值得阅读书籍推荐
|
1月前
|
SQL 数据采集 存储
数据仓库(12)数据治理之数仓数据管理实践心得
这边文章聊聊自己对数据治理开发实践的一些思路,就是聊聊怎么开始去做数据治理这件事情。说起数据治理,有时候虽然看了很多文章,看了很多的介绍,了解数据治理的理论,但是实际上需要我们去搞的时候,就会踩很多的坑。这里记一下自己做数据治理的一些思路,做做笔记,也分享给需要的同学。 当然,想要做数据治理,想要学习了解,一下数据治理的范围,理论等,最好可以看看别人怎么做的,了解数据治理可以参考:[数据仓库(11)什么是大数据治理,数据治理的范围是哪些](https://zhuanlan.zhihu.com/p/467433967)。
231 0
|
1月前
|
存储 SQL HIVE
金融审批数仓(离线)--DWD层、ADS层
金融审批数仓(离线)--DWD层、ADS层
65 4
|
1月前
|
存储 大数据 数据管理
数据仓库(09)数仓缓慢变化维度数据的处理
数据仓库的重要特点之一是反映历史变化,所以如何处理维度的变化是维度设计的重要工作之一。缓慢变化维的提出是因为在现实世界中,维度的属性并不是静态的,它会随着时间的流逝发生缓慢的变化,与数据增长较为快速的事实表相比,维度变化相对缓慢。阴齿这个就叫做缓慢变化维。
210 2
数据仓库(09)数仓缓慢变化维度数据的处理
|
1月前
|
SQL Cloud Native 关系型数据库
AnalyticDB MySQL湖仓版是一个云原生数据仓库
【2月更文挑战第15天】AnalyticDB MySQL湖仓版是一个云原生数据仓库
24 2
|
2月前
|
SQL 分布式计算 数据库
离线数仓--大数据技术之DolphinScheduler
离线数仓--大数据技术之DolphinScheduler
138 2
|
2月前
|
C++
数仓模型建设
数仓模型建设
92 1