对证券行业离线数仓的思考

最新推荐文章于 2023-10-09 15:07:11 发布

爱慕。

最新推荐文章于 2023-10-09 15:07:11 发布

阅读量992

点赞数 7

文章标签：数据仓库数据库大数据数据库架构 hive

本文链接：https://blog.csdn.net/weixin_48171883/article/details/129241259

版权

对证券行业离线数仓的思考

呕心沥血，熬了一周的通宵才完成的万字长文

一、数据仓库基本概念

数据仓库
- 概念
  - 数据仓库（Data Warehouse）是一个面向主题的（Subject Oriented）、集成的（Integrated）、相对稳定的（Non-Volatile）、反映历史变化（Time Variant）的数据集合，用于支持管理决策(Decision Making Support)。
- 特点
  - 首先，数据仓库用于支持决策，面向分析型数据处理，它不同于企业现有的操作型数据库;
  - 其次，数据仓库是对多个异构的数据源有效集成，集成后按照主题进行了重组，并包含历史数据，而且存放在数据仓库中的数据一般不再修改。
- 数据仓库是一个功能概念，是将企业的各业务系统产生的基础数据，通过维度建模的方式，将业务数据划分为多个主题（集市）统一存储，统一管理。
数据集市
- 概念
  - 数据集市可以理解为是一种"小型数据仓库"，它只包含单个主题，且关注范围也非全局。
- 分类
  - 独立数据集市，这类数据集市有自己的源数据库和ETL架构。
  - 非独立数据集市，这种数据集市没有自己的源系统，它的数据来自数据仓库。
- 应用场景
  - 数据集市是数仓之上更聚焦的业务主题合集，更偏向于应对业务数据快速高效应用的需求。
- 数据集市是一个结构概念，它是数据仓库的一个子集，主要面向BU级业务，并且只面向某个特定的主题。
数据孤岛
- 业务系统之间各自为政、相互独立造成的数据孤岛，体现在业务不集成、流程不互通、数据不共享。
数据中台
- 数据中台是指通过企业内外部多源异构的数据采集、治理、建模、分析，应用，使数据对内优化管理提高业务，对外可以数据合作价值释放，成为企业数据资产管理中枢。数据中台建立后，会形成数据API，为企业和客户提供高效各种数据服务。
- 数据中台是一个逻辑概念，为业务提供服务的主要方式是数据API，它包括了数据仓库，大数据、数据治理领域的内容。
数据模型：以数学的方式对现实事物的一种抽象表达，数据（Data）是描述事物的符号记录，模型（Model）是现实世界的抽象。数据模型按不同的应用层次分成三种类型，分别是概念数据模型、逻辑数据模型、物理数据模型。
ETL，是英文Extract-Transform-Load的缩写，用来描述将数据从来源端经过抽取（extract）、转换（transform）、加载（load）至目的端的过程。ETL一词较常用在数据仓库，但其对象并不限于数据仓库。
数据治理（Data Governance）是组织中涉及数据使用的一整套管理行为。由企业数据治理部门发起并推行，关于如何制定和实施针对整个企业内部数据的商业应用和技术管理的一系列政策和流程。
数据处理（data processing）是对数据的采集、存储、检索、加工、变换和传输。数据处理的基本目的是从大量的、可能是杂乱无章的、难以理解的数据中抽取并推导出对于某些特定的人们来说是有价值、有意义的数据。
数据资产是指由个人或企业拥有或者控制的，能够为企业带来未来经济利益的，以物理或电子的方式记录的数据资源。数据资产是拥有数据权属（勘探权、使用权、所有权）、有价值、可计量、可读取的网络空间中的数据集。
数据质量，是指在业务环境下，数据符合数据消费者的使用目的，能满足业务场景具体需求的程度。
数据域，面向业务分析，将业务过程或者维度进行抽象的集合。其中，业务过程可以概括为一个个不可拆分的行为事件，在业务过程之下，可以定义指标维度是指度量的环境，如买家下单事件，买家是维度。为保障整个体系的生命力，数据域是需要抽象提炼、并且长期维护和更新的，但不轻易变动。在划分数据域时，既能涵盖当前所有的业务需求，又能在新业务进入时无影响的被包含进已有的数据域和扩展新的数据域。
原子指标，即是业务过程的度量，就是明细事实表中的度量值。比如成交表，那么某比交易对应的成交金额就是一个原子指标，这个指标是伴随着交易的业务过程而产生的。
派生指标，即由统计周期+修饰词+统计粒度+原子指标组合加工而成的指标
- 统计周期：指的是想要统计的时间周期，比如天、周、月
- 修饰词：指的是业务的约束，通常出现在SQL的where条件中，比如交易的渠道等等
- 统计粒度：指的是维度组合，通常出现在SQL的group by中，比如统计不同类型证券的成交金额，那证券类型就是统计粒度

二、模型设计

数据模型的维度设计主要以维度建模理论为基础，基于维度数据模型总线架构，构建一致性的维度和事实。

数据模型的事实表设计在维度模型事实表的基础上，结合数据使用场景的具体实践，进行一定扩展，采用宽表设计方法。所谓宽表：为了提升访问便利性和访问性能，在维度模型的事实表基础上，将部分常用维度退化（冗余）到事实表，或者将一些可枚举型的维度和度量，采用多指标、多字段方式实现在事实表中。适当的数据冗余换取查询和刷新性能，但是不宜过度冗余与数据复制。

在指标定义中，采取组件化的形式，进行指标标准化定义，先规范定义，后生产，全生命周期控制，保障数据口径统一，减少重复建设，强调数据复用和共享。

在处理数据逻辑时，底层公用的处理逻辑更应该在数据调度依赖的底层进行封装与实现，既有利于数据复用，也有利于变更修改。

维度建模按数据组织类型划分可分为星型模型、雪花模型、星座模型。

星型模型

是一种多维的数据关系，它由一个事实表（Fact Table）和一组维表（Dimension Table）组成。
每个维表都有一个维作为主键，所有这些维的主键组合成事实表的主键。
星型架构是一种非正规化的结构，多维数据集的每一个维度都直接与事实表相连，所以有一定的数据冗余。

在这里插入图片描述

雪花模型

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

在这里插入图片描述

星座模型

星座模型需要多个事实表共享维度表，因而可以视为星形模型的集合，故亦被称为星系模型。
星座模型是很多数据仓库的常态，因为很多数据仓库都是多个事实表的。所以星座模型只反映是否有多个事实表，他们之间共享一些维度表。

三、数仓分层

数据大致划分为三层：

ODS（Operational Data Store）：原始操作层，主要完成业务系统、日志等结构化和半结构化数据引入到数据中台，保留业务系统原始数据，包括增量明细和全量明细。
CDM（Common Data Model）：通用数据模型，主要完成公共数据加工与整合，建立一致性的维度，构建可复用面向分析和统计的明细事实表以及汇总公共粒度的指标。
ADS（Application Data Service）：应用层数据，提供直接面向业务应用的数据。为方便实现数据应用、数据消费的诉求，进行数据形式的组装，进行面向应用逻辑的数据加工处理。

其中在CDM层，由以下几部分组成：

DIM（Dimension），公共维度层，基于维度建模理念思想，建立企业的一致性维度。
DWD（Data Warehouse Detail），明细粒度事实层，以业务过程作为建模驱动，基于每个具体的业务过程特点，构建最细粒度的明细层事实表，结合企业的数据使用特点，将明细事实表的某些重要维度属性字段做适当冗余，也就是宽表化处理。
DWS（Data Warehouse Summary），公共汇总粒度事实层：以分析的主题对象作为建模驱动，基于上层的应用和产品的指标需求，构建公共粒度的汇总指标事实表，以宽表化手段物理化模型。

数仓分层原因

用空间换时间
- 通过大量的预处理来提升应用系统的用户体验（效率），因此数据仓库会存在大量的冗余数据。
增强扩展性
- 不分层的话，如果源业务系统的业务规则发生变化将会影响整个数据清洗过程，工作量巨大。
分层管理
- 通过数据分层管理可以简化数据清洗的过程，因为把原来的一步工作分到了多个步骤去完成，相当于把一个复杂的工作拆成了多个简单的工作，把一个大的黑盒变成了一个白盒，每一层的处理逻辑都相对简单和容易理解，这样我们比较容易保证每一个步骤的正确性，当数据发生错误时，往往我们只需要局部调整某个步骤即可。

合理的数据仓库分层一方面能够降低耦合性，提高重用性，可读性可维护性，另一方面也能提高运算的效率。建立数据分层可以提炼公共层，避免烟囱式开发，可见一个合适且合理的数仓分层是极其重要。

数仓分层好处

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

四、开发规范

各层级依赖
- ADS层应优先调用CDM层，不允许应用层从ODS层重复加工数据，尽量将公用的数据沉淀到CDM，为其他层级提供数据服务，必须避免出现过度的ODS层引用和不合理的数据复制和子集合冗余。
- DWS优先调用DWD明细层（相对ODS）。可累加类指标计算，DWS层尽量优先调用已经产出的DWS层的粗粒度数据，避免大量汇总都直接从海量的明细数据层计算，但要注意任务深度不宜过大，一般不超过10层。
- DWD层累计快照事实表优先调用事务型事实表，保持数据一致性产出。避免ADS层过度引用和依赖DWD层数据。
数据类型
- 为保证ODS层数据和源业务系统的兼容性，ODS层统一采用String类型。
数据冗余
- 宽表冗余时，应遵循以下准则：
  - 冗余字段与表中其它字段高频率同时访问。
  - 冗余字段的引入不应造成其本身的更新完成时间过多后延。
  - 公共层的数据不允许字段重复率大于60%的相同粒度数据表冗余，可以选择原表基础上拓宽或者下游应用通过JOIN方式实现。
数据拆分
- 在物理上划分核心模型和扩展模型，将其字段进行垂直划分。
- 将访问相关度较高的列放在一个表存储，将访问相关度较低的字段分开存储。
- 将经常用到的WHERE条件按记录行进行水平切分或者冗余，水平切分可以考虑二级分区方法，避免多作的数据复制与冗余。

五、指标体系

派生指标的种类

派生指标可以分为三类：事务型指标、存量型指标和复合型指标。按照其特性不同，有些必须新建原子指标，有些可以在其他类型原子指标基础上增加业务限定形成派生指标。

事务型指标是指对业务活动进行衡量的指标。
- 例如，新增有效户数，交易成交金额，这类指标需维护原子指标及业务限定，在此基础上根据指定的统计粒度创建派生指标。
存量型指标是指对实体对象(如产品、有效户)某些状态的统计，它最典型的特点是它的总量不是一个统计时间范围内的增量，而是历史累计到统计时间点的全量。
- 例如，当前深交所上市股票总数，当前有效户总数，这类指标维护原子指标及修饰词，在此基础上创建派生指标，对应的时间周期为“历史截止到当前”。
比较衍生型指标
- 是在事务性指标和存量型指标基础上进一步衍生而成的，例如，营业部最近一天APP成交金额按板块降序排名、本月成交额与去年同期同比变化率等。
- 比较衍生型指标的规则：
  - 排名型：在已有的派生指标上衍生创建。
    
    其定义方式为：派生指标+排名范围（例如：营业部、省份、证券类别等）+排名方式（例如：升序排名ark，降序排名drk）。举例：“营业部最近一天APP成交金额按板块降序排名”，派生指标为店铺最近一天APP端成交金额，排名范围为板块，排名方式为降序排名。
  - 排名对象集合型：对象集合型主要是数据产品和应用需要展现数据时，将一些对象以KV对的方式存储在一个字段中，方便前端展现。
    其定义方式为：派生指标+排名范围（例如：营业部、省份、证券类别等）+排名方式（例如：升序排名ark，降序排名drk）+topN+对象名+s（s代表指标为字符串）。
  - 变化量型：变化量型指标分为同比变化量、环比变化量和滑动窗口变化量三种类型。
    指标定义方式为：派生指标+变化量类型。例如：最近7天成交额与昨日统计的最近7天成交额变化量，本月成交额与去年同期同比变化量，本月成交额与去年同期同比变化量等等。
  - 变化率型：变化率型指标分为同比变化率、环比变化率和滑动窗口变化率三种类型。
    指标定义方式为：派生指标+变化率类型。例如：最近7天成交额与昨日统计的最近7天成交额变化率，本月成交额与去年同期同比变化率，本月成交额与去年同期同比变化率等等。
  - 比例型比例型指标定义方式为：派生指标+rb（rationby）+占比组，用于例如：“分公司最近一天成交金额在全公司占比” 。

命名规范

命名所用术语

指标命名：使用英文简写，其次是英文，当指标英文名太长时，考虑用汉语拼音首字母命名，如融资融券，用rzrq。
业务过程
- 英文名：用英文的缩写或者英文、中文拼音简写
- 中文名：具体的业务过程中文描述即可
原子指标
- 英文名：业务动作+度量
- 中文名：业务动作+度量
- 原子指标必须挂靠在某个业务过程下
派生指标
- 英文名：原子指标英文名+统计周期+修饰词英文名
- 中文名：统计周期+修饰词+统计粒度+原子指标

六、设计规范

数据建模，毫无疑问是数仓建设的重中之重，然后，在实际的开发过程中，会把大量的时间都投入到了需求开发，往往会忽略数据建模(尤其是DWS层的建模)，长此以往，数据模型变得越来越杂乱，指标口径无法统一，造成的结果就是：虽然表很多，但是却很难取数。

数仓建模的步骤：梳理业务流程、垂直切分、指标体系梳理、实体关系调研、维度梳理、数仓分层、物理模型构建

ODS层设计规范

同步策略

小数据量表
- 抽取处理策略
  - 数据库直连方式全量抽取，每日全量一个分区。数据量大时可以做增量合并的方式进行抽取。
- 存储策略
  - 全量表：按天全量存储，生命周期根据业务需要可设置长周期（如366天或永久保存）。
大数据量表
- 维表
  - 抽取处理策略
    - 数据库直连方式通过业务时间戳抽取增量数据到增量表，再从增量表merge入全量表。
    - 有些表的数据量随业务的发展越来越大，如果按周期全量同步的方式会影响处理效率。在这种情况下，选择改为每次只同步新变更的增量数据，然后与上一同步周期获得的全量数据进行合并，从而获得最新版本的全量数据。
  - 存储策略
    - 增量表：可设置长周期（如366天或永久保存）
    - 全量表：根据业务需求及存储资源考虑设置较长周期（如183天或366天或732天，若需要永久保存则要使用历史拉链处理）。
- 日志型事务表
  - 抽取处理策略
    - 原始日志增量抽取到增量表。按天增量存储。因为日志数据表现为只会有新增不会有修改的情况，因此不需要保存全量表。
  - 存储策略
    - 增量表：可设置长周期（如建议永久保存）
- 非流水型事务表
  - 抽取处理策略
    - 从源数据库通过时间戳抽取增量数据到增量表，再从增量表merge入最近N天（例如最近200天）全量表。
    - 具体使用的方式可用全外连接（full outer join）+数据全量覆盖重新加载（insert overwrite）的方式，即如日调度，则将当天增量数据和前一天全量数据做全外连接，重新加载为最新的全量数据。
  - 存储策略
    - 增量表：可设置长周期（如永久保存）。
    - 最近N天全量表：根据业务需求及存储资源保留合适周期（如生命周期设置为7天或33天，若要对全量表快照保留长周期并做极限存储需要仔细评估计算与存储代价）。

命名规范

表命名规范
- 日增量数据：ODS_源系统编号_ 源系统表名_DELTA
- 日全量数据：ODS_源系统编号_ 源系统表名_ALL
字段命名规范
- 字段默认使用源系统字段名称
- 字段名与平台关键字冲突时处理规则：加一个“col”后缀，即：源字段名_col
同步任务命名规范
- IMPORT_表名

数据质量

全量表必须配置唯一性字段标识
全量表必须做分区空数据监控
对重要表的重要枚举类型字段做枚举值变化、及枚举值分布监控
只对有监控要求的表才创建数据质量管控层
每个ODS层全表都必须要有注释

数据脱敏

CDM层设计规范

DIM层设计规范

表命名规范
- dim_{数据板块缩写}_{维度定义缩写}
字段命名规范
- 字段命名遵循词根缩写
维度种类划分
- 普通维度
  - 在一些父子结构的数据，如部门的组织关系、多级树形结构归类，在数据仓库中需要将其加工为扁平结构的维度模型，方便用户在数据计算和分析时进行任意层级的汇总和查询。
  - 基于本身的父子结构关键信息建议维表后，会存在多级维度结构数据。之所以为生产出多个分级类目维度是因为后续的粒度必须是由维度属性组成，否则如果统计是按二级、三级类目统计，那么二级、三级类目必须独立定义为一个维度。
- 枚举维度
  - 枚举维度主要是用于一些用户定义的归类数据进行定义，一方面它可以进行数据内容格式的标准化，一方面可以方便用户快速录入。
  - Code：部分建议使用英文的代码标识，最好使用能够让你能理解的有业务含义的代码，value值表达的是实际的中文业务含义。
- 虚拟维度
  - 虚拟维度是用于表达退化到事实表中的维度信息，如果此维度的值域范围是不可枚举，且需要作为统计指标的粒度，因此我们有必要对维度进行定义，但是实际底层物理实现并不会物理化一张维度表。如果在事实逻辑表中的维度值域范围是可枚举的，规范建议优先选择为枚举维度定义，这样更有利于建维度的值域范围标准化。

DWD层设计规范

设计原则
- DWD模型设计可以允许视图，但是该视图仅对其来源做简单的清洗和过滤，不做JOIN和group by
表命名规范
- dwd{数据板块缩写/pub}{业务过程缩写}[{自定义表命名标签缩写}]{刷新周期标识}{单分区增量全量标识}
- ODS视图的和ODS的结构保持一致()，命名规范同dwd层规范
字段命名规范
- 字段命名原则上遵循词根法，相同的字段含义在不同表中字段命名必须相同
数据存储
- 事务型事实表一般永久保留周期性快照事实表根据业务需求设置生命周期管理或者极限存储策略

DWS层设计规范

设计原则
- 以分析的主题对象作为建模驱动，基于上层的应用和产品的指标需求，构建公共粒度的汇总指标表。以宽表化手段物理化模型，构建命名规范、口径一致的统计指标，为上层提供公共指标，建立汇总宽表。如：形成日，周，月粒度汇总明细，或者基于某一个维度，统计便于下一步报表数据结构的组织。
- 避免多个层级的数据应该避免将不同层级的数据放在一起，比如，如果存在7天和30天的事实，我们可以选择用两列存放7天和30天的事实，但是需要在列名和字段注释上说明清楚。
- DWS层是面向分析维度进行设计的，分析维度通常是业务经常需要的看数据的角度。
- DWS层的表服务于数据报表和数据产品的指标需求
- ADS层的指标数据会存在交叉探查的情况，所以DWS层的指标要保持命名和口径一致，避免ADS层的指标数据混乱
- DWS是公共汇总层，提供不同维度的统计指标，指标的口径要保持一致，并且要提供详细的描述
- 以宽表的形式进行设计，比如相同粒度的统计指标可以放在一起，避免创建太多的表
- 公共汇总层的一个表通常会对应一个派生指标
- DWS存储派生指标(统计周期+修饰词+统计粒度+原子指标)，原子指标存储在DWD层的事实表中
表命名规范
- dws_{数据板块缩写/pub}{数据域缩写}{数据粒度缩写}[{自定义表命名标签缩写}]{统计时间周期范围缩写}
设计步骤
- 首先，确定聚集维度，即确定统计粒度，比如证券粒度
- 然后，确定统计周期，比如天
- 最后，确定聚集事实，即派生指标
数据存储
- 按日分区存储
- 可以选择保留月初数据，以及特定日期数据，可以根据业务定更长的保留周期

ADS层设计规范

表命名规范
- ads_{数据板块缩写}_ [_业务应用缩写]{_数据粒度}[自定义标签缩写]{刷新周期标识}。
- 刷新周期标识：d/天，w/周，m/月，r/实时（准实时）
字段名规则：
- 字段命名可以遵循dwd\dws层规则
- 个性化指标及字段按照词根命名

维度表与事实表设计规范

维度表

维度表概念

维度是维度建模的基础和灵魂。在维度建模中，将度量称为“事实”，将环境描述为“维度”，维度是用于分析事实所需要的多样环境。
- 例如，在分析交易过程时，可以通过客户、产品和时间等维度描述交易发生的环境。
维度所包含的表示维度的列，称为维度属性。维度属性是查询约束条件、分组和报表标签生成的基本来源，是数据易用性的关键。
- 在查询请求中，获取客户是否为有效户，是通过约束客户的开户、销户时间和是否销户状态属性来实现的
- 统计不同证券交易市场的每日交易金额，是通过交易所维度的交易市场属性进行分组的
- 我们在报表中看到的普通账户、融资融券等，都是维度属性。
- 维度的作用一般是查询约束、分类汇总以及排序等。
维度表特征
- 维度表的范围很宽（具有多个属性、列比较多）
- 跟事实表相比，行数较少（通常小于10万条）
- 内容相对固定

维度表设计原则

维度属性尽量丰富，为数据使用打下基础
给出详实的、富有意义的文字描述
- 属性不应该是编码，而应该是真正的文字。
区分数值型属性和事实
- 数值型宇段是作为事实还是维度属性，可以参考字段的一般用途。
- 如果通常用于查询约束条件或分组统计，则是作为维度属性。
  - 查询资产在10-20万元的客户数量，此时是作为度量在使用。
- 如果通常用于参与度量的计算，则是作为事实。
  - 统计客户的资产在1-10万元的客户平均资产，此时是作为事实在使用。
沉淀出通用的维度属性，为建立一致性维度做好铺垫
- 有些维度属性获取需要进行比较复杂的逻辑处理，有些需要通过多表关联得到，或者通过单表的不同字段混合处理得到，或者通过对单表的某个字段进行解析得到。此时，需要将尽可能多的通用的维度属性进行沉淀。
  - 一方面，可以提高下游使用的方便性，减少复杂度;。
  - 另一方面，可以避免下游使用解析时由于各自逻辑不同而导致口径不一致。
退化维度（冗余，上文有提过）
- 在维度类型中，有一种重要的维度称作为退化维度。这种维度指的是直接把一些简单的维度放在事实表中。
缓慢变化维
- 维度的属性并不是始终不变的，它会随着时间的流逝发生缓慢的变化，这种随时间发生变化的维度我们一般称之为缓慢变化维。例如，客户的年龄，员工的部门调换
  - 常用处理方式
    - 直接覆盖原值，不管历史数据
    - 拉链表
      - 需要在维度行再增加三列：有效日期、截止日期、行标识（可选）
    - 增加新的字段，将新值与旧值一起保存

维度设计

维度整合
- 垂直整合，即不同的来源表包含相同的数据集，只是存储的信息不同
  - 比如，客户基本信息，客户详细信息，客户开户信息，客户风险等级表，这些都属于客户相关信息，尽量整理至维度模型中，丰富维度属性。
- 水平整合，即不同的来源表包含不同的数据集，不同子集之间无交叉，也可以存在部分交叉。
  - 比如，我们在整理集中交易、融资融券、OTC，是否需要将客户各类交易信息整合至一张表中，如果进行整合，首先需要考虑各个系统下客户是否有交叉，如果存在交叉，则需要去重。如果不存在交叉，则需要考虑不同子集的自然键是否存在冲突，如果不冲突，则可以考虑将各子集的自然键作为整合后的表的自然键另一种方式是设置超自然键，将来源表各子集的自然键加工成一个字段作为超自然键。
维度拆分
- 水平拆分
  - 维度通常可以按照类别或类型进行细分,如集中交易和融资融券。
  - 两种解决方案：
    - 方案1：是将维度的不同分类实例化为不同的维度，同时在主维度中保存公共属性
    - 方案2：是维护单一维度，包含所有可能的属性。
  - 参考原则
    - 扩展性：当源系统、业务逻辑变化时，能通过较少的成本快速扩展型，保持核心模型的相对稳定性。软件工程中的高内聚、低稠合的思想是重要的指导方针之一。
    - 效能：在性能和成本方面取得平衡。通过牺牲一定的存储成本，达到性能和逻辑的优化。
    - 易用性：模型可理解性高、访问复杂度低。能够方便地从模型中找到对应的数据表，并能够方便地查询和分析。
  - 根据数据模型设计思想，在对维度进行水平拆分时，主要考虑如下两个依据。
    - 第一个依据是维度的不同分类的属性差异情况。
      - 维度属性随类型变化较大时，将所有可能的属性建立在一个表中是不切合实际的，也没有必要这样做，此时建议定义一个主维度用于存放公共属性同时定义多个子维度，其中除了包含公共属性外，还包含各自的特殊属性。
    - 第二个依据是业务的关联程度。
      - 两个相关性较低的业务，稠合在一起弊大于利，对模型的稳定性和易用性影响较大。
垂直拆分
- 维度是维度建模的基础和灵魂，维度属性的丰富程度直接决定了数据仓库的能力。
- 在进行维度设计时，依据维度设计的原则，尽可能丰富维度属性，同时进行反规范化处理。
- 对于具体实现时可能存在的问题：
  - 一是在“水平拆分”中提到的，由于维度分类的不同而存在特殊的维度属性，可以通过水平拆分的方式解决此问题。
  - 二是某些维度属性的来源表产出时间较早，而某些维度属性的来源表产出时间较晚或者某些维度属性的热度高、使用频繁，而某些维度属性的热度低、较少使用或者某些维度属性经常变化，而某些维度属性比较稳定。在“水平拆分”中提到的模型设计的三个原则同样适合解决此问题。
- 出于扩展性、产出时间、易用性等方面的考虑，设计主从维度。主维表存放稳定、产出时间早、热度高的属性从维表存放变化较快、产出时间晚、热度低的属性。

事实表

事实表概念

事实表中的每行数据代表一个业务事件。“事实”表示的是业务事件的度量值（可以统计次数、个数、金额等）
事实表作为数据仓库维度建模的核心，紧紧围绕着业务过程来设计，通过获取描述业务过程的度量来表达业务过程，包含了引用的维度和与业务过程有关的度量。
事实表中一条记录所表达的业务细节程度被称为粒度。通常粒度可以通过两种方式来表述：
- 一种是维度属性组合所表示的细节程度
- 一种是所表示的具体业务含义
作为度量业务过程的事实，一般为整型或浮点型的十进制数值，有可加性、半可加性和不可加性三种类型。
- 可加性事实是指可以按照与事实表关联的任意维度进行汇总。
- 半可加性事实只能按照特定维度汇总，不能对所有维度汇总，比如客户持仓情况可以按照证券类型和交易所进行汇总，而按时间维度把一年中每个月的持仓情况累加起来则毫无意义。
- 不可加性，比如比率型事实。对于不可加性事实可分解为可加的组件来实现聚集。
相对维表来说，通常事实表要细长得多，行的增加速度也比维表快很多。
维度属性也可以存储到事实表中，这种存储到事实表中的维度列被称为“退化维度”。与其他存储在维表中的维度一样，退化维度也可以用来进行事实表的过滤查询、实现聚合操作等。

事实表设计原则

原则 1：
- 尽可能包含所有与业务过程相关的事实
- 分析哪些事实与业务过程相关，是设计过程中非常重要的关注点
- 在事实表中，尽量包含所有与业务过程相关的事实，即使存在冗余，由于事实通常是数字型，存储开销不会太大
原则 2：只选择与业务过程相关的事实
- 如，买入卖出的委托业务过程，事实表中不应该存在成交金额这个表示成交业务过程的事实
原则 3：分解不可加性事实为可加的组件
- 如，收益率，应分解为成交金额与当前市值两个事实存储在事实表中
原则 4：在选择维度和事实之前必须先声明粒度
- 粒度用于确定事实表中一行所表示业务的细节层次，决定了维度模型的扩展性
- 每个维度和事实必须与所定义的粒度保持一致
- 设计事实表时，粒度定义越细越好，一般从最低级别的原子粒度开始
- 因为原子粒度提供了最大限度的灵活性，可以支持无法预期的各种细节层次的需求
原则 5：在同一个事实表中不能有多种不同粒度的事实
- 事实表中的所有事实需要与表定义的粒度保持一致,在同一个事实表中不能有多种不同粒度的事实。
原则 6：事实的单位要保持一致
- 如，各种金额类事实，应该采用统一的计量单位，统一为元或者分，以方便使用
原则 7：对事实的 null 值要处理
- 原因：在数据库中，null 值对常用数字型字段的 SQL 过滤条件都不生效如，大于、小于、等于、大于或等于、小于或等于
- 处理：用 0 代替 null
原则 8：使用退化维度提高事实表的易用性
- 事实表中存储各种类型的常用维度信息，较少下游用户使用时关联多个表的操作
- 通过退化维度，可以实现对事实表的过滤查询、控制聚合层次、排序数据、定义主从关系等
- 易用性：对事实表，更较少关联操作、过滤查询、控制聚合层次、排序数据、定义主从关系等

事实表设计方法

选择业务过程以及确定事实表类型
- 比如买入卖出的业务过程：创建订单，付款，委托成功，成交
明确了业务过程后，根据具体业务需求来选择与维度建模有关的业务过程。
- 比如付款这个业务过程，那么事实表应只包括付款这一个业务过程的单事务事实表
- 总而言之就是选择了哪些业务过程，那么所建立的事实表应为包含了所有业务过程的累积快照事实表（下文有解释）
声明粒度
- 粒度声明非常重要，尽量选择最细级别的原子粒度，以确保事实表的应用具有最大的灵活性
确定维度
- 完成粒度声明意味着声明了主键，对应的维度组合就可以确定了
- 应该选择能够清楚描述业务过程的维度信息
确定事实
- 应该选择与业务过程有关的所有事实，且事实的粒度要和声明的粒度一致
冗余维度
- 大数据的事实表设计中，冗余尽可能多的维度让下游方便使用，减少连表数量

事实表分类

事务型事实表
- 以每个事务或事件为单位，例如一比成交记录，一笔委托记录等，作为事实表里的一行数据。
- 一旦事务被提交，事实表数据被插入，数据就不再进行更改，其更新方式为增量更新。
- 单事务事实表：
  - 单事务事实表，顾名思义，即针对每个业务过程设计一个事实表，这样设计的优点不言而喻，可以方便地对每个业务过程进行独立的分析研究。
- 多事务事实表：
  - 多事务事实表，将不同的事实放到同一个事实表中，即同一个事实表包含不同的业务过程，多事务事实表在设计时有两种方法进行事实的处理：
    - 以订单过程为例，一个订单业务过程可以简化为委托和成交两个业务事件，两个事件共享一个Order_Id及相应商品的维度信息，但这两个事件可以有不同的业务度量，举例来讲就是委托时有委托金额，成交时有实际成交金额等等。
    - 对于数仓建设来讲，在描述订单业务过程，设计订单事实表模型时，就会面临一个选择：将委托和成交两个业务事件拆开，分别构建单事务事实表还是构建一张多事务事实表。
    - 构建单事务事实表
      - 优点在于各个事实表的业务语义明确，表分区字段直接采用表达的业务时间即可，方便下游使用方理解
      - 缺点则是会冗余订单维度相关的数据，并且对于同时查询委托和成交度量时，会有表连接的计算开销。
    - 构建多事务事实表
      - 此时表分区字段就不由具体的业务语义，需要在表中增加相应字段用明确时间语义。举例来讲对于2023-02-24的分区，需要有 ‘是否当天委托’&'是否当天成交’两个字段来辅助判断分区的时间语义，然后在表中包含委托和成交的度量，对于尚未发生的度量，可以置零。采用这种方案，对于下游使用有一定的理解难度，且度量中可能会有很多0值，但可以避免表连接问题。
周期型快照事实表
- 周期型快照事实表中不会保留所有数据，只保留固定时间间隔的数据，以具有规律性的、可预见的时间间隔记录事实。
- 快照事实表的设计有些区别于事务事实表设计的性质。事务事实表的粒度能以多种方式表达，但快照事实表的粒度通常以维度形式声明事务事实表是稀疏的，但快照事实表是稠密的事务事实表中的事实是完全可加的，但快照模型将至少包含一个用来展示半可加性质的事实。
- 快照采样状态
  - 快照事实表以预定的间隔采样状态度量。这种间隔联合一个或多个维度，将被用来定义快照事实表的粒度，每行都将包含记录所涉及状态的事实。
- 快照粒度
  - 事务事实表的粒度可以通过业务过程中所涉及的细节程度来描述，但快照事实表的粒度通常总是被多维声明，可以简单地理解为快照需要采样的周期以及什么将被采样。
  - 当然，快照周期不一定都按天来进行，也可以按照月或者季度来统计。
- 密度与稀疏性
  - 快照事实表和事务事实表的一个关键区别在密度上。事务事实表是稀疏的，只有当天发生的业务过程，事实表才会记录该业务过程的事实，如委托、成交等。
  - 而快照事实表是稠密的，无论当天是否有业务过程发生，都会记录一行，比如针对客户的历史至今的委托和成交金额，无论当天卖家是否有委托事实，都会给该卖家记录一行。稠密性是快照事实表的重要特征，如果在每个快照周期内不记录行，比如和事务事实表一样，那么确定状态将变得非常困难。
- 半可加性
  - 在快照事实表中收集到的状态度量都是半可加的。与事务事实表的可加性事实不同，半可加性事实不能根据时间维度获得有意义的汇总结果。
累积型快照事实表
- 累积快照事实表用于跟踪业务事实的变化，覆盖过程的整个生命周期，通常具有多个日期字段来记录关键时间点。
- 例如数据仓库中可能需要累积或者存储订单从创建订单开始，到委托、撤单、成交等各个业务阶段的时间点数据，来跟踪交易的生命周期。
- 当这个业务过程进行时，事实表的记录也要不断更新。

总结

	事务事实表	周期快照事实表	累计快照事实表
时间/日期	离散时间事务点	以有规律的、可预测的间隔产生快照	用于时间跨度不确定的不断变化的工作流
日期维度	事务日期	快照日期	相关业务过程涉及的多个日期
粒度	每行代表实体的一个事务	每行代表时间周期的一个实体	每行代表代表一个实体的生命周期
事实	事务事实	累积事实	相关业务过程事实和时间间隔事实
事实表加载	插入	插入	插入与更新
事实表更新	不更新	不更新	业务过程变更时更新

事务事实表记录的事务层面的事实，用于跟踪业务过程的行为，并支持几种描述行为的事实，保存的是最原子的数据，也称为“原子事实表”。事务事实表中的数据在事务事件发生后产生，数据的粒度通常是每个事务一条记录。一旦事务被提交，事实表数据被插人，数据就不能更改，其更新方式为增量更新。
周期快照事实表以具有规律性的、可预见的时间间隔来记录事实。周期快照事实表的日期维度通常记录时间段的终止日，记录的事实是这个时间段内一些聚集事实值或状态度量。事实表的数据一旦插人就不能更改，其更新方式为增量更新。
累积快照事实表被用来跟踪实体的一系列业务过程的进展情况，它通常具有多个日期字段，用于研究业务过程中的里程碑过程的时间间隔。另外，它还会有一个用于指示最后更新日期的附加日期字段。由于事实表中许多日期在首次加载时是不知道的，而且这类事实表在数据加载完成后，可以对其数据进行更新，来补充业务状态变更时的日期信息和事实。

爱慕。

关注

7
点赞
踩
22

收藏

觉得还不错? 一键收藏
打赏
1
评论
对证券行业离线数仓的思考

事务事实表周期快照事实表累计快照事实表时间/日期离散时间事务点以有规律的、可预测的间隔产生快照用于时间跨度不确定的不断变化的工作流日期维度事务日期快照日期相关业务过程涉及的多个日期粒度每行代表实体的一个事务每行代表时间周期的一个实体每行代表代表一个实体的生命周期事实事务事实累积事实相关业务过程事实和时间间隔事实事实表加载插入插入插入与更新事实表更新不更新不更新业务过程变更时更新。
复制链接

扫一扫