数据仓库-数据建模-003

最新推荐文章于 2024-08-02 09:53:21 发布

code36

最新推荐文章于 2024-08-02 09:53:21 发布

阅读量601

点赞数 21

文章标签：大数据数据仓库数据库数据库开发 etl

本文链接：https://blog.csdn.net/pujungong/article/details/140257753

版权

3.1 前言

3.1.1 什么是数据建模

数据建模简单来说就是基于对业务的理解，将各种数据进行整合和关联，并最终使得这些数据可用性，可读性增强，让使用方能快速的获取到自己关心的有价值的信息并且及时的作出响应，为公司带来效益。

3.1.2 为什么要数据建模

数据建模是一套方法论，主要是对数据的整合和存储做一些指导，强调从各个角度合理的存储数据。

进行全面的业务梳理，改进业务流程：

在业务模型建设的阶段，能够帮助我们的企业或者是管理机关对本单位的业务进行全面的梳理。通过业务模型的建设，我们应该能够全面了解该单位的业务架构图和整个业务的运行情况，能够将业务按照特定的规律进行分门别类和程序化，同时，帮助我们进一步的改进业务的流程，提高业务效率，指导我们的业务部门的生产。

建立全方位的数据视角，消灭信息孤岛和数据差异：

通过数据仓库的模型建设，能够为企业提供一个整体的数据视角，不再是各个部门只是关注自己的数据，而且通过模型的建设，勾勒出了部门之间内在的联系，帮助消灭各个部门之间的信息孤岛的问题，更为重要的是，通过数据模型的建设，能够保证整个企业的数据的一致性，各个部门之间数据的差异将会得到有效解决。

解决业务的变动和数据仓库的灵活性：

通过数据模型的建设，能够很好的分离出底层技术的实现和上层业务的展现。当上层业务发生变化时，通过数据模型，底层的技术实现可以非常轻松的完成业务的变动，从而达到整个数据仓库系统的灵活性。

帮助数据仓库系统本身的建设：

通过数据仓库的模型建设，开发人员和业务人员能够很容易的达成系统建设范围的界定，以及长期目标的规划，从而能够使整个项目组明确当前的任务，加快整个系统建设的速度。

有了合适的数据模型，是会带来很多好处的：

查询使用性能提升
用户效率提高，改善用户体验
数据质量提升
降低企业成本

所以大数据系统需要数据模型方法来更好的组织和存储，以便在性能,成本，效率和质量之间取的平衡。

3.2 建模工具

PowerDesigner：

Power Designer 是Sybase公司的CASE工具集，使用它可以方便地对管理信息系统进行分析设计，他几乎包括了数据库模型设计的全过程。利用Power Designer可以制作数据流程图、概念数据模型、物理数据模型，还可以为数据仓库制作结构模型，也能对团队设计模型进行控制。他可以与许多流行的软件开发工具，例如PowerBuilder、Delphi、VB等相配合使开发时间缩短和使系统设计更优化。

power designer是能进行数据库设计的强大的软件，是一款开发人员常用的数据库建模工具。使用它可以分别从概念数据模型(Conceptual Data Model)和物理数据模型(Physical Data Model)两个层次对数据库进行设计。在这里，概念数据模型描述的是独立于数据库管理系统(DBMS)的实体定义和实体关系定义；物理数据模型是在概念数据模型的基础上针对目标数据库管理系统的具体化。

3.3 Kimball和Inmon架构

3.3.1 Inmon架构

辐射状企业信息工厂(CIF) 方法由Bill Inmon及业界人士倡导的。在这个环境下，数据从操作性数据源中获取，在ETL系统中处理，将这一过程称为数据获取，从这一过程中获得的原子数据保存在满足第三范式的数据库中，这种规范化的原子数据的仓库被称为CIF架构下的企业级数据仓库(EDW)

与Kimball方法相似，CIF提倡企业数据协调与集成，但CIF认为要利用规范化的EDW承担这一角色，而Kimball架构强调具有一致性维度的企业总线的重要作用

Inmon企业级数据仓库的分析数据库通常以部门为中心（而不是围绕业务过程来组织），而且包含汇总数据，并不是原子级别数据，如果ETL过程中数据所应用的业务规则超越了基本概要，如部门改名了或者其他的类似计算，要将分析数据库与EDW原子数据联系起来将变得很困难

3.3.2 Kimball架构

Kimball架构利用了CIF中处于中心地位的EDW，但是此次的EDW完全与分析与报表用户隔离，仅作为数据来源，其中数据是维度的，原子的，以过程为中心的，与企业级数据仓库总线结构保持一致。

3.3.3 架构对比

3.3.3.1流程

Inmon架构是自顶向下，即从数据抽取-->数据仓库-->数据集市，以数据源为导向，是一种瀑布流开发方法，模型偏向于3NF，

Kimball:架构是自下向上，即从数据集市(主题划分)-->数据仓库--> 数据抽取，是以需求为导向的，一般使用星型模型

3.3.3.2事实表和维表

Inmon架构下，不强调事实表和维表的概念，因为数据源变化可能会比较大，更加强调的是数据清洗的工作

kimball架构强调模型由事实表和维表组成，注重事实表与维表的设计

3.3.3.3数据集市

Inmon架构中，数据集市有自己的物理存储，是真实存在的。

Kimball数据仓库架构中，数据集市是一个逻辑概念，只是多维数据仓库中的主题域划分，并没有自己的物理存储，也可以说是虚拟的数据集市。是数据仓库的一个访问层，是按主题域组织的数据集合，用于支持部门级的决策。

3.3.3.4中心

Inmon架构是以部门为中心，而Kimball架构是以业务过程为中心

3.3.3.5 EDW的访问

Inmon架构中用户可以直接访问企业数据仓库(EDW)

Kimball架构中用户不可以直接访问企业数据仓库(EDW)，只能访问展现区数据

企业开发中一般选择Kimball维度建模

3.4 数仓建模阶段划分

3.4.1 业务模型

生成业务模型，主要解决业务层面的分解和程序化

划分整个单位的业务，一般按照业务部门的划分，进行各个部分之间业务工作的界定，理清各业务部门之间的关系。
深入了解各个业务部门的内具体业务流程并将其程序化。
提出修改和改进业务部门工作流程的方法并程序化。
数据建模的范围界定，整个数据仓库项目的目标和阶段划分。

3.4.2 领域模型

生成领域模型，主要是对业务模型进行抽象处理，生成领域概念模型。

抽取关键业务概念，并将之抽象化。
将业务概念分组，按照业务主线聚合类似的分组概念。
细化分组概念，理清分组概念内的业务流程并抽象化。
理清分组概念之间的关联，形成完整的领域概念模型。

3.4.3 逻辑模型

生成逻辑模型，主要是将领域模型的概念实体以及实体之间的关系进行数据库层次的逻辑化。

业务概念实体化，并考虑其具体的属性
事件实体化，并考虑其属性内容
说明实体化，并考虑其属性内容

3.4.4 物理模型

生成物理模型，主要解决，逻辑模型针对不同关系型数据库的物理化以及性能等一些具体的技术问题。

针对特定物理化平台，做出相应的技术调整
针对模型的性能考虑，对特定平台作出相应的调整
针对管理的需要，结合特定的平台，做出相应的调整
生成最后的执行脚本，并完善之。

3.5 模型建设方法

3.5.1 ER模型

ER模型是属于三范式的，是企业级的主题抽象而不是单独描述某个业务

3.5.1.1 什么是范式

当分类不可再分时，这种关系是规范化的，一个低级范式分解转换为更高级的范式时，就叫做规范化

数据表可以分为1-5NF，第一范式是最低要求，第五范式则是最高要求

最常用的范式有第一范式（1NF）、第二范式（2NF）、第三范式（3NF）

3.5.1.2 第一范式

表中的每一列都是不可拆分的原子项

由上图可知，phone字段里面存了2个值，具有可分割性，不符合1NF，可以改成：

3.5.1.3 第二范式

第二范式要同时满足下面两个条件：

满足第一范式
没有部分依赖

上图可以看出，如果一个用户下了很多订单，则用户名，收获地址和手机号有重复出现的情况造成数据冗余，很明显不太符合第二范式，可以改成：

3.5.1.4 第三范式

第三范式要同时满足下面两个条件：

满足第二范式
没有传递依赖

简单点说，关系重复，能互相推导出来

如上图所示，如果知道了zip邮编，其实是能推出来省市区的，相反，知道了省市区，也是可以推出邮编的，有传递依赖，造成了冗余，不符合第三范式，需要改造:

3.5.1.5 小结

在关系数据模型设计中，一般需要满足第三范式的要求。如果一个表有良好的主外键设计，就应该是满足3NF的表。

规范化带来的好处是通过减少数据冗余提高更新数据的效率，同时保证数据完整性。然而，我们在实际应用中也要防止过度规范化的问题。规范化程度越高，划分的表就越多，在查询数据时越有可能使用表连接操作。

而如果连接的表过多，会影响查询的性能。关键的问题是要依据业务需求，仔细权衡数据查询和数据更新的关系，制定最适合的规范化程度。还有一点需要注意的是，不要为了遵循严格的规范化规则而修改业务需求。

3.5.2 维度建模

维度建模是一种将大量数据结构化的逻辑设计手段，包含维度和指标，它不像ER模型目的是消除冗余数据，维度建模是面向分析，最终目的是提高查询性能，所以会增加数据冗余，并且违反三范式。

维度建模也是重点关注让用户快速完成需求分析且对于复杂查询及时响应，维度建模一般可以分为三种：

星型模型
雪花模型
星座模型

其中最常用的其实是星型模型

3.5.2.1 背景

在多维分析的商业智能解决方案中，根据事实表和维度表的关系，又可将常见的模型分为星型模型，雪花型模型及星座模型。在设计逻辑型数据的模型的时候，就应考虑数据是按照星型模型，雪花型模型还是星座模型进行组织。

3.5.2.2 事实表和维度表

3.5.2.2.1事实表（Fact Table）

指存储有事实记录的表，如系统日志、销售记录等；事实表的记录在不断地动态增长，所以它的体积通常远大于其他表。

事实表作为数据仓库建模的核心，需要根据业务过程来设计，包含了引用的维度和业务过程有关的度量。

作为度量业务过程的事实，一般为整型或浮点型的十进制数值，有可加性，半可加性和不可加性三种类型

3.5.2.2.2可加

最灵活最有用的事实是完全可加，可加性度量可以按照与事实表关联的任意维度汇总。比如订单总金额

3.5.2.2.3半可加

半可加度量可以对某些维度汇总，但不能对所有维度汇总。差额是常见的半可加事实，除了时间维度外，他们可以跨所有维度进行操作。(比如每天的余额加起来毫无意义)

3.5.2.2.4不可加

一些度量是完全不可加的，例如：比率。对非可加事实，一种好的方法是，分解为可加的组件来实现聚集

3.5.2.2.5维度表

维度表（Dimension Table）或维表，有时也称查找表（Lookup Table），是与事实表相对应的一种表；它保存了维度的属性值，可以跟事实表做关联；相当于将事实表上经常重复出现的属性抽取、规范出来用一张表进行管理。常见的维度表有：日期表（存储与日期对应的周、月、季度等的属性）、地点表（包含国家、省／州、城市等属性）等。维度是维度建模的基础和灵魂。

3.5.2.2.6 优点

缩小了事实表的大小。
便于维度的管理和维护，增加、删除和修改维度的属性，不必对事实表的大量记录进行改动。
维度表可以为多个事实表重用，以减少重复工作。

3.5.2.2.7下钻

下钻是商业用户分析数据的最基本的方法。下钻仅需要在查询上增加一个行头指针，新行的头指针是一个维度属性，附加了sql语言的group by表达式，属性可以来自任何与查询使用的事实表关联的维度，下钻不需要预先存在层次的定义，或者是下钻路径。

3.5.2.2.8退化维度

有时，维度除了主键外没有其他内容，例如，当某一发票包含多个数据项时，数据项事实行继承了发票的所有描述性维度外键，发票除了外键无其他项，但发票数量仍然是在此数据项级别的合法维度键。这种退化维度被放入事实表中，清楚的表明没有关联的维度表，退化维度常见于交易和累计快照事实表中。

3.5.2.3事实表和维表的关系

3.5.2.3 星型模型

星形模型中有一张事实表，以及零个或多个维度表，事实表与维度表通过主键外键相关联，维度表之间没有关联，当所有维表都直接连接到“ 事实表”上时，整个图解就像星星一样，故将该模型称为星型模型。星形模型是最简单，也是最常用的模型。由于星形模型只有一张大表，因此它相比于其他模型更适合于大数据处理。其他模型可以通过一定的转换，变为星形模型。

星型架构是一种非正规化的结构，多维数据集的每一个维度都直接与事实表相连接，不存在渐变维度，所以数据有一定的冗余，如在地域维度表中，存在国家 A 省 B 的城市 C 以及国家 A 省 B 的城市 D 两条记录，那么国家 A 和省 B 的信息分别存储了两次，即存在冗余。

3.5.2.4 雪花模型

当有一个或多个维表没有直接连接到事实表上，而是通过其他维表连接到事实表上时，其图解就像多个雪花连接在一起，故称雪花模型。雪花模型是对星型模型的扩展。它对星型模型的维表进一步层次化，原有的各维表可能被扩展为小的维度表，形成一些局部的 " 层次 " 区域，这些被分解的表都连接到主维度表而不是事实表。如图，将地域维表又分解为国家，省份，城市等维表。它的优点是 : 通过最大限度地减少数据存储量以及联合较小的维表来改善查询性能。雪花型结构去除了数据冗余。

3.5.2.5 星座模型

星座模型是由星型模型延伸而来，星型模型是基于一张事实表而星座模式是基于多张事实表，并且共享维度表信息，这种模型往往应用于数据关系比星型模型和雪花模型更复杂的场合。星座模型需要多个事实表共享维度表，因而可以视为星形模型的集合，故亦被称为星系模型

3.5.2.6 对比

星型模型因为数据的冗余所以很多统计查询不需要做外部的连接，因此一般情况下效率比雪花型模型要高。
星型结构不用考虑很多正规化的因素，设计与实现都比较简单。
雪花型模型由于去除了冗余，有些统计就需要通过表的联接才能产生，所以效率比较低。
正规化也是一种比较复杂的过程，相应的数据库结构设计、数据的 ETL、以及后期的维护都要复杂一些。

3.5.2.7 小结

通过对比，我们可以发现数据仓库大多数时候是比较适合使用星型模型构建底层数据Hive表，通过大量的冗余来减少表查询的次数从而提升查询效率，星型模型对OLAP的分析引擎支持比较友好，这一点在Kylin中比较能体现。而雪花模型在关系型数据库中如MySQL，Oracle中非常常见，尤其像电商的数据库表。在数据仓库中雪花模型和星座模型的应用场景比较少，但也不是没有，所以在具体设计的时候，可以考虑是不是能结合两者的优点参与设计，以此达到设计的最优化目的。

3.5.2.8 建模原则

高内聚和低辑合：将业务相近或者相关、粒度相同的数据设计为一个逻辑或者物理模型：将高概率同时访问的数据放一起，将低概率同时访问的数据分开存储。
核心模型与扩展模型分离：建立核心模型与扩展模型体系，核心模型包括的宇段支持常用的核心业务，扩展模型包括的字段支持个性化或少量应用的需要，不能让扩展模型的宇段过度侵人核心模型，以免破坏核心模型的架构简洁性与可维护性。
公共处理逻辑下沉及单一：越是底层公用的处理逻辑越应该在数据调度依赖的底层进行封装与实现，不要让公用的处理逻辑暴露给应用层实现，不要让公共逻辑多处同时存在。
成本与性能平衡：适当的数据冗余可换取查询和刷新性能，不宜过度冗余与数据复制。
数据可回滚：不改变处理逻辑，不修改代码的情况下重跑任务结果不变
一致性：字段命名及定义必须一致
命名清晰、可理解：表命名需清晰、一致，表名需易于使用方理解

3.5.2.9星型模型设计步骤

选择需要进行分析决策的业务过程比如下单
选择粒度。在事件分析中，我们要预判所有分析需要细分的程度，从而决定选择的粒度。比如订单粒度，粒度是维度的一个组合。
识别维表。选择好粒度之后，就需要基于此粒度设计维表，包括维度属性，用于分析时进行分组和筛选。
选择事实。确定分析需要衡量的指标

3.5.3 Data Vault模型

Data Vault Dan Linstedt 发起创建的一种模型，它是模型的衍生，其设计的出发点也是为了实现数据的整合，但不能直接用于数据分析决策。它强调建立一个可审计的基础数据层，也就是强调数据的历史性、可追溯性和原子性，而不要求对数据进行过度的一致性处理和整合；

同时它基于主题概念将企业数据进行结构化组织，并引入了更进一步的范式处理来优化模型，以应对源系统变更的扩展性。Data Vault 型由以下几部分组成。

Hub ：是企业的核心业务实体，由实体 key 、数据仓库序列代理键、装载时间、数据来源组成。
Link ：代表 Hub 之间的关系。这里与模型最大的区别是将关系作为一个独立的单元抽象，可以提升模型的扩展性。它可以直接描述 1:1 1:n n:n 的关系，而不需要做任何变更。它由 Hub 的代理键、装载时间、数据来源组成。
Satellite ：是 Hub 的详细描述内容，一个 Hub 可以有多个 Satellite它由 Hub 的代理键、装载时间、来源类型、详细的 Hub 描述信息组成。

Data Vault 模型比 ER 模型更容易设计和产出，它的 ETL 加工可实现配置化。

3.5.4 Anchor模型

进一步规范化处理，其核心思想是所有的扩展只添加而不是修改，因此将模型规范到6NF，基本编程了K-V结构化模型。

　　那么总的来说，分为三个阶段：

将数据以源表结构相同的方式同步到Oracle，数据工程师基于ODS数据进行统计。
通过一些模型技术改变烟囱式的开发模型，消除一些冗余，提升数据的一致性。（经验）在不太成熟、快速变化的业务面前，构建ER模型的风险非常大，不太适合去构建ER模型。
选择了以Kimball的维度建模为核心理念的模型方法论，同时对其进行了一定的升级和扩展，构建了公共层模型数据架构体系。

3.6 规范文档

3.6.1 开发流程

需求分析调研(数据调研，需求调研，业务调研)：明确口径，评估排期，需求正规流程提交
指标管理：完善指标命名规范，指标同名同义，指标与业务强相关，明确指标构成要素
模型设计：完善开发流程规范，标准化业务调研，知识库文档集中管理，建立模型评审机制
ETL开发：ODS->DWD->DW->DWS->ADS
数据验证：制定数据测试标准
任务调度：规范化调度参数配置
上线管理

3.6.2 分层规范

3.6.2.1前言

数据仓库一般分为三层，自上而下分别为数据贴源层（ODS，Operation Data Store）、数据公共层（CDM，Common Data Model）和数据应用层（ADS，Application Data Service）。

3.6.2.2 ODS层

贴源层，与业务库保持一致，不做任何处理

3.6.2.3 CDM层

数据公共层CDM（Common Data Model，又称通用数据模型层），包括DIM维度表、DWD,DW和DWS，由ODS层数据加工而成。主要完成数据加工与整合，建立一致性的维度，构建可复用的面向分析和统计的明细事实表，以及汇总公共粒度的指标

公共维度层（DIM）：基于维度建模理念思想，建立企业一致性维度。降低数据计算口径和算法不统一风险。公共维度层的表通常也被称为逻辑维度表，维度和维度逻辑表通常一一对应。

明细粒度事实层（DWD）：对数据进行规范化编码转换，清洗，统一格式，脱敏等，不做横向整合

主题宽表层(DW) 对dwd各种信息进行整合，输出主题宽表(面向业务过程，不同业务过程的信息不冗余建设，采用外键形式)

公共汇总粒度事实层（DWS）：以分析的主题对象作为建模驱动，基于上层的应用和产品的指标需求，构建公共粒度的汇总指标事实表，以宽表化手段物理化模型。构建命名规范、口径一致的统计指标，为上层提供公共指标，建立汇总宽表、明细事实表。

公共汇总粒度事实层的表通常也被称为汇总逻辑表，用于存放派生指标数据。

3.6.2.4 ADS层

数据应用层ADS（Application Data Service）：面向业务需求定制开发，存放数据产品个性化的统计指标数据。

3.6.2.5逻辑分层架构

3.6.2.6 分层的好处

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

3.6.2.7 数据流向

正常流向：ODS->DWD->DW->DWS->ADS，当出现ODS->DWD->DWS->ADS这种关系时，说明主题域未覆盖全。应将DWD数据落到DW中，对于使用频度非常低的表允许DWD->DWS。
尽量避免出现DWS宽表中使用DWD又使用（该DWD所归属主题域）DW的表。
同一主题域内对于DW生成DW的表，原则上要尽量避免，否则会影响ETL的效率。
DW、DWS和ADS中禁止直接使用ODS的表， ODS的表只能被DWD引用。
禁止出现反向依赖，例如DW的表依赖DWS的表。

3.6.3 表命名规范

表名、字段名采用下划线分隔词根（consultorder->consult_order）
每部分使用小写英文单词，属于通用字段的必须满足通用字段信息的定义。
表名、字段名需以字母为开头。
表名、字段名最长不超过64个英文字符。
优先使用词根中已有关键字（数仓标准配置中的词根管理），定期Review新增命名的不合理性。
在表名自定义部分禁止采用非标准的缩写。

3.6.3.1 ods dwd层

建表表名一律小写

表名命名规则： [层次].[业务]_[表内容]_[周期+处理方式]

3.6.3.2 dw dws层

建表表名一律小写

表名命名规则： [层次].[主题]_[表内容]_[周期+处理方式] 主题在dw层以后，表内容参考业务系统表名，做适当处理，分表规则可以没有

如：ods.test_order_info_df

ods表示层次，test表示主题，order_info表示表内容，d表示周期(天)，f表示处理方式(全量抽取)

3.6.3.3 临时表 tmp

临时表命名规则：[层次].tb_目标表名_程序开始执行时间_序号

3.6.4 字段命名规范

有实际意义
根据词根组合而成

如： order_amt 订单金额

3.6.5 注释规范

能实际说明字段意义
字典需要注明 id和desc

如： order_id varchar comment ‘订单id’

order_status tinyint comment ‘1：已支付 2：已发货 3：已签收 ’

3.6.6 数据类型规范

根据实际需求选择字段类型

3.6.6.1 数字类

3.6.6.2 日期时间类

3.6.6.3 字符串类

3.6.6.4 Misc类

3.6.6.5 复合类

3.6.7 分区规范

什么情况需要分区
分区字段选择
分区字段命名规范

目前90%以上的分区表都是以日期分区的，当然，一些日志表还是有二级分区，如三端日志

3.6.8 词根规范

3.6.8.1 词根评审

需要新增一个词根的时候，需要部门评审，看看是否有必要新增，并且如果确定下来需要新增的话如何命名

比如 cnt 这个词代表的意思是count 数量，如果之前词根里面没有的话，理论上来说，新增该词根是没毛病的

3.6.8.2 词根大全

3.6.9 指标规范

3.6.9.1 指标定义

指标的定义(口径)需要与业务方，运营人员或者数据分析师综合确定

现列举一些常用流量指标：

日活跃度=当日启动用户/累计用户*100%
周活跃度=周活跃用户/累计用户*100%
月活跃度=月活跃用户/累计用户*100%
页面访问次数：页面被打开的次数，同一页面的多次访问均会被计数。
页面平均停留时长：每一次页面访问的停留时长的平均值

3.6.9.2 指标命名

3.6.9.2.1基础指标

主要是指不能再拆解的指标，通常表达业务实体原子量化属性的且不可再分的概念集合，如订单数

单个基础指标词根+修饰词

3.6.9.2.2 复合指标

建立在基础指标之上，通过一定运算规则形成的计算指标集合，如人均费用=总费用/人数

3.6.9.2.3派生指标

指的是基础指标或复合指标与维度成员，统计属性，管理属性等相结合产生的指标，如最近7天医生接单量=医生在过去7天一共接到的订单

多个基础指标词根+修饰词

3.5.9.3 指标评审

每定一个指标都是需要业务方(或其他部门)与数据部门一起评审决定的，包括指标是否有必要新增，如何定义等

3.5.9.4 指标存储展示

可以通过自研WEB系统来进行展示

展示内容可以有：

指标名称
指标编码
业务口径
指标类型
责任人
创建时间
状态

3.6.10 数据抽取规范

Mysql数据准备

第一天 9月10号数据

第二天 9月11号数据

对比mysql第一天和第二天的数据发现，第二天新增了订单id为4和5这两条数据，并且订单id为2和3的状态更新为了已支付

3.6.10.1全量表

每天的所有的最新状态的数据。

全量表，有无变化，都要报
每次上报的数据都是所有的数据（变化的 + 没有变化的）9月10号全量抽取到ods层

9月11号全量抽取到ods层

全量抽取，每个分区保留历史全量快照。

3.6.10.3 拉链表

维护历史状态，以及最新状态数据
数据量比较大
表中的部分字段会被更新
需要查看某一个时间点或者时间段的历史快照信息
查看某一个订单在历史某一个时间点的状态
某一个用户在过去某一段时间，下单次数
更新的比例和频率不是很大

如果表中信息变化不是很大，每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费

优点

满足反应数据的历史状态
最大程度节省存储

9月10号全量抽取到ods层

建立dwd层拉链表

增加两个字段：start_dt(表示该条记录的生命周期开始时间——周期快照时的状态)end_dt(该条记录的生命周期结束时间)

end_dt= ‘9999-12-31’ 表示该条记录目前处于有效状态

注：第一次加工的时候需要初始化所有数据，start_time设置为数据日期2020-09-10 ，end_time设置为9999-12-31

9月11号抽取更新的数据及当天新增的数据到ods层，即订单id为2,3,4,5的数据

查询当前的所有有效记录：

查询9月10号历史快照:

查询9月11号历史快照:

3.6.10.4 流水表

对于表的每一个修改都会记录，可以用于反映实际记录的变更

3.6.10.5 总结

在工作中，其实上述3种表都是很有可能会用到的，那么我们应该怎么选择呢？
如果数据量不是很大(不超过20W)且预估后续增长的非常慢，可以考虑全量表抽取，这是最简便的方法
如果数据量目前来说不是很大，但是业务发展很快，数据量一段时间后就会上来，建议增量抽取哦
目前数据量本身就非常大，肯定是需要增量抽取的，比如现在有10亿数据，如果你每天全量抽取一遍，相信我，你会抽哭的
对于历史状态需要保存的，这个时候就需要使用拉链表了，实际工作中，使用拉链表的场景并不会太多，比如订单表，保存订单历史状态，维表(缓慢变化维的处理)

3.6.11 缓慢变化维处理

3.6.11.1背景

众所周知，虽然维度表属性相对稳定，但是并不是一成不变的，尽管相当缓慢，维度值仍会随时间而变化。比如商品类目的改变，医院等级的改变

在一些情况下，保留历史数据没有什么分析价值，而在另一些情况下,保留历史数据是非常重要的

3.6.11.2 解决方案

3.6.11.2.1重写维度值

在维度表中，仅需以当前值重写先前存在的值，不需要触碰事实表

缺点：如果业务需要准确的跟踪历史变化，这种方案是没法实现的，并且在以后想改变是非常困难的

修改之前

修改后：

3.6.11.2.2插入新的维度行

插入新的维度行。采用此种方式，保留历史数据，

维度值变化前的事实和过去的维度值关联，维度值变化后的事实和当前的维度值关联

缺点：虽然此方案能够区分历史情况，但是该方式不能将变化前后记录的事实归一为变化前的维度或者归一为变化后的维度

3.6.11.2.3添加维度列

有些是只保留最新的维度值和最近的维度值，也有的是维度值一有变化就新增一个属性字段。都不是很好的解决方案

变化前：

变化后：

3.6.11.2.4拉链表处理

这是精确跟踪缓慢变化维度属性的主要技术，因为新维度行能够自动划分事实表的历史，所以这是一项非常好的技术

变化前：

变化后：

3.6.12 多值维度及多值属性(交叉维度)

3.6.12.1背景

正常情况下，维表和事实表之间是一对多的关系，维表中的一行记录会连接事实表中的多行记录，事实表中的一行记录在维度表中只能关联上一条记录，不会发生数据发散的现象

想法是美好的，但是事实总是不尽人意。因为现实中不但事实表和维度表之间存在多对多的关系，维度表和维度表之间也存在多对多的关系

这两种情况本质是相同的，但事实表和维度表之间的多对多关系少了唯一描述事实和维度组的中间维度。

对于这两种情况，一种称为桥接表的中间表就需要派上用场了，并且还可以支持更为复杂的多对多的关系

3.6.12.2事实表与维度表多对多(多值维度)

比如下单了一套学习课程，但是这套课程并不是某一个用户买的，而是好几个用户合买的，所以为了处理这种情况，需要创建一个桥接表，将这些合买的用户组成一个组

ETL过程需要对每条事实表中的用户组，在桥接表中查找相应的用户主键,上图所示的桥接表有重复计数的风险。如果按用户累加购买金额，对某些分析而言结果是正确的，但对于其他情况仍会有重复计数的问题。要解决这个问题，可以向桥接表中添加权重。

权重是一个分数值，所有的用户组的权重累加起来为1。将权重和累加事实相乘，以按照每个用户在分组中的比重分配事实。

优点：

灵活简化了生成报表的难度

借权重避免了多重计算

缺点：

桥接表的维护比较复杂，当出现一个新组合时，得先判断桥接表中是否已存在

3.6.12.3维表与维表多对多(交叉维度)

从分析的角度来看，维度之间的多对多关系是一个很重要的概念，大多数维度都不是完全相互独立的。

在银行系统中，账户和顾客之间有直接关系，但不是一对一的关系。一个账户可以有一个或多个签名确认的用户，一个用户也可有多个账户

有2种方案解

和多值维度一样，创建账户-用户组桥接表来连接事实表

还有一种方法是利用账户和用户之间的关系，如下图

桥接表可以捕获多对多关系，并且由于源系统中的关系是已知的，因此创建桥接表比多值维度手动构建维度表(桥接表)更容易

3.6.12.4总结

处理多值维度最好的办法是降低事实表的粒度。这种处理方式也是维度建模的一个原则，即事实表应该建立在最细粒度上。这样的处理，需要对事实表的事实进行分摊。

但是有些时候，事实表的粒度是不能降低的，多值维度的出现是无法避免的。如上述交叉维度，事实表与用户维度没有直接的关系，不能将数据粒度进行细分，即使细分的话帐户余额也很难分摊。这时，可以采用桥接表技术进行处理。在帐户维度表和用户维度表之间建立个帐户-用户桥接表。这个桥接表可以解决掉帐户维度和用户维度之间的多对多关系，也解决掉的帐户维度表的多值维度问题。

总之，多值维度是应该尽量避免的，它给数据处理带来了很大的麻烦。如果多值维度不能避免的话，应该建立桥接表来进行处理。