数据仓库-数据建模-003
通过业务模型的建设，我们应该能够全面了解该单位的业务架构图和整个业务的运行情况，能够将业务按照特定的规律进行分门别类和程序化，同时，帮助我们进一步的改进业务的流程，提高业务效率，指导我们的业务部门的生产。通过数据仓库的模型建设，能够为企业提供一个整体的数据视角，不再是各个部门只是关注自己的数据，而且通过模型的建设，勾勒出了部门之间内在的联系，帮助消灭各个部门之间的信息孤岛的问题，更为重要的是，通过
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总结
处理多值维度最好的办法是降低事实表的粒度。这种处理方式也是维度建模的一个原则，即事实表应该建立在最细粒度上。这样的处理，需要对事实表的事实进行分摊。
但是有些时候，事实表的粒度是不能降低的，多值维度的出现是无法避免的。如上述交叉维度，事实表与用户维度没有直接的关系，不能将数据粒度进行细分，即使细分的话帐户余额也很难分摊。这时，可以采用桥接表技术进行处理。在帐户维度表和用户维度表之间建立个帐户-用户桥接表。这个桥接表可以解决掉帐户维度和用户维度之间的多对多关系，也解决掉的帐户维度表的多值维度问题。
总之，多值维度是应该尽量避免的，它给数据处理带来了很大的麻烦。如果多值维度不能避免的话，应该建立桥接表来进行处理。
3.6.13 码表规范
码表(编码表)：
- 是一种代码说明表格。
- 用来帮助用户明确无解释数据和字符 代码的含义。类似于数据字典
3.6.13.1码表注册
| disease_code | disease_name |
| 7863 | 糖尿病 |
| 6575 | 高血压 |
| ...... | |
3.6.13.2码值统一
一个疾病编码只有一个对应的疾病名称
3.6.14 开发规范
3.6.14.1 开发原则
- 支持重跑
- 数据生命周期合理
- 任务迭代不会严重影响任务产出时间
3.6.14.2 开发规范
3.16.14.2.1 DDL语句规范
- 建表规范
- 新增单个字段规范
- 新增多个字段规范
- 修改表注释规范
- 修改字段注释规范
- 删除表规范
- DDL语句维护规范
3.16.14.2.2 临时表命名规范
3.16.14.2.3 sql编写规范
原则定义
- 要求代码行清晰、整齐，具有一定的可观赏性
- 代码编写要充分考虑执行速度最优的原则
- 代码行整体层次分明、结构化强
- 代码中应有必要的注释以增强代码的可读性
- 规范要求非强制性约束代码开发人员的代码编写行为，在实际应用中在不违反常规要求的前提下允许存在可理解的偏差
大小写规则
- 所有的SQL语句中的保留字：全部小写
- 表名、视图名、宏和存储过程名：全部小写
- 字段名：全部小写
缩进与换行
- 整个的SQL语句最好按照子句进行分行编写，SELECT、FROM、WHERE、UPDATE、INSERT 等每个关键字都要另起一行
- 同一级别的子句间要对齐
- 逗号放在每行的开头
- 分号放在SQL语句的最后，单独占一行
运算符前后间隔要求
- 算术运算符、逻辑运算符的前后保留一个空格
注释
- 每个字段后面使用字段中文名作为注释
- 针对复杂的SQL语句，请尽量增加相应的注释说明，以便自己和其它同事事后可以比较容易的读懂和修改。
- 无效脚本可采用单行/多行注释。（-- 或 /* */ 方式）
连接的使用
- 表中的字段若是从其它表引用的，要确保该字段在被引用的表中存在
- 要求所有的连接都写成join的形式，写完整 比如 inner join
- 连接符or、in、and、以及＝、=等前后加上一个空格。
- 多表连接时，使用表的别名来引用列。如 t1.user_id t2.user_name
其他
- 整表删除时使用，TRUNCATE效率高于DELETE
- 尽量避免使用NOT IN语句，而改用LEFT JOIN方式进行判断筛选，除非是特殊情况
- JOIN时对于关联字段值可能为NULL时，应利用COALESCE函数将NULL值转换为缺省值后再进行关联
- 表名前面需要加上项目名
3.6.15 清洗规范
单位统一
比如金额单位统一为 人民币，元
字段类型统一
注释补全
时间格式统一
如2020-10-16，2020/10/16，20201016统一格式为20201016
枚举值统一
1:男 2:女
0:男 1女
统一为：1:男 2:女
复杂数据解析
空值替换
使用默认值或者中位数填充
数据脱敏
比如身份证号，手机号，邮箱等需要用加密函数进行脱敏
3.6.16 业务域
3.6.16.1主题域
主题域是对某个主题进行分析后确定的主题的边界。
如用户主题，日志主题
3.6.16.2数据域
数据域是指面向业务分析，将业务过程或者维度进行抽象的集合
如订单域，业务过程可能为：加入购物车->支付->发货->收货，整个业务过程的数据都属于订单域
3.7 名词概念
3.7.1 宽表
宽表从字面意义上讲就是字段比较多的表。通常是指业务主题相关的指标、维度、属性关联在一起的表。
3.7.2 粒度
粒度问题是设计数据仓库的一个最重要方面。粒度是指数据仓库的数据单位中保存数据的细化或综合程度的级别。细化程度越高，粒度级就越小；相反，细化程度越低，粒度级就越大。
笼统的说，粒度就是维度的组合
3.7.3 退化维度
将一些常用的维度属性直接写到事实表中的维度操作称为维度退化
3.7.4 维度层次
维度中的一些描述属性以层次方式或一对多的方式相互关联，可以
被理解为包含连续主从关系的属性层次。层次的最底层代表维度中描述
最低级别的详细信息，最高层代表最高级别的概要信息。维度常常有多
个这样的嵌入式层次结构。
3.7.5 下钻
数据明细，粗粒度到细粒度的过程，会细化某些维度
下钻是商业用户分析数据的最基本的方法。下钻仅需要在查询上增加一个维度属性，附加在SQL的GROUP BY语句中。属性可以来自任何与查询使用的事实表关联的维度。下钻不需要存在层次的定义或是下钻路径。
3.7.6 上卷
数据的汇总聚合，细粒度到粗粒度的过程,会无视某些维度
3.7.8 规范化
按照三范式形成设计是事实和纬度表的方式管理数据称为规范化
规范化常用于OLTP系统的设计
3.7.9反规范化
将维度的属性层次合并到单个维度中的操作称为反规范化
反规范化会产生包含全部信息的宽表，形成数据冗余；实现用维表的空间换取简明性和查询性能的效果，常用于OLAP系统的设计
3.8 总线矩阵
3.8.1 一致性维度
3.8.1.1共享维表
维表公用，所以基于这些 公共维度进行的交叉探查不会存在任何问题
3.8.1.2一致性上卷
其中一个维度的属性是另一个维度的维度属性的子集，且两个维度的公共维度属性结构和内容相同
3.8.1.3 交叉属性
两个维度具有部分相同的维度属性
3.8.2 一致性事实
3.8.3 总线架构
3.9 模型评价及优化
3.9.1 完善度
- 汇总数据能直接满足多少查询需求，即应用层访问汇总层数据的查询比例
- 跨层引用率：ODS 层直接被 DWS/ADS/DM 层引用的表，占所有 ODS 层表（仅统计活跃表）比例
- 同层level>2的占比等量化指标
- 快速响应业务方的需求
比较好的模型，使用方是可以直接从该模型获取所有想要的数据的，如果dws，ads，dm层直接引用ods层的表比例太大，即跨层引用率太高，则该模型不是最优，可以继续优化
3.9.2 复用度
模型引用系数：模型被读取并产出下游模型的平均数量
dw，dws下游直接产出的表的数量
3.9.3 规范度
- 主题域归属
- 脚本及任务明明规范
表需要关联上主题域并且需要分层
表命名符合规范(清晰、一致，表名需易于使用方理解)
字段命名是依赖于词根
3.9.4 稳定性
能否保证日常的sla（时效保障）
3.9.5 扩展性
新增加的模型是否和老的模型出现冲突
3.9.6 准确性&一致性
输出的指标数据质量能够保证
3.9.7 健壮性
业务快速更新迭代的情况下不会太影响底层模型
3.9.8 低成本
- 时间成本
- 计算资源成本
- 存储成本
3.10 一致性维度
3.11 指标一致性
3.11.1 事件背景
业务方反馈：
- 同样的指标取自2张表，结果不一样
- 同样的指标，取自同一张表，但是是2个需求，指标口径不统一
- 同一个指标，命名不一样，导致重复计算
- 不同的两个指标，命名一样，导致产生误解
3.11.2 目标
从设计、开发、部署和使用层面，避免重复建设和指标冗余建设，从而保障数据口径的规范和统一，最终实现数据资产全链路关联、提供标准数据输出以及建立统一的数据公共层。
3.11.3 解决方案
3.11.4统一定义
- 指标定义一致性
- 数据来源一致性
- 统计口径一致性
- 维度一致性
- 维度和指标数据出口唯一
3.11.4.1 词根规范
词根评审
需要新增一个词根的时候，需要部门评审，看看是否有必要新增，并且如果确定下来需要新增的话如何命名
如 cnt 代表的意思是count 数量，如果之前词根里面没有的话，理论上来说，新增该词根是没问题的
词根大全
3.11.4.2指标规范
指标评审及定义
每定一个指标都是需要业务方(或其他部门)，运营人员与数据部门一起评审决定的，包括指标是否有必要新增，如何定义等
现列举一些常用流量指标：
- 日活跃度=当日启动用户/累计用户*100%
- 周活跃度=周活跃用户/累计用户*100%
- 月活跃度=月活跃用户/累计用户*100%
- 页面访问次数： 页面被打开的次数，同一页面的多次访问均会被计数。
- 页面平均停留时长： 每一次页面访问的停留时长的平均值
指标命名规范
基础指标
主要是指不能再拆解的指标，通常表达业务实体原子量化属性的且不可再分的概念集合，如订单数
单个基础指标词根+修饰词
复合指标
建立在基础指标之上，通过一定运算规则形成的计算指标集合，如人均费用=总费用/人数
派生指标
指的是基础指标或复合指标与维度成员，统计属性，管理属性等相结合产生的指标，如最近7天医生接单量=医生在过去7天一共接到的订单
多个基础指标词根+修饰词
3.11.5标准建模
建立数据公共层对模型架构进行标准规范设计和管理
3.11.6规范研发
整个开发过程需要严格遵守开发规范
3.11.7工具保障
3.11.7.1指标管理系统(指标库)
设计原则
- 指标口径一致性
- 使用便捷性
- 数据处理高性能，以及智能化
- 开发维护高效性
指标体系
指标分层级
一级分类：投资类，运营类 等
二级分类：
三级分类：
待定
可以通过自研WEB系统来进行展示
展示内容可以有：
- 指标编码
- 指标名称
- 业务口径
- 指标类型
- 存储的表
- 责任人
- 创建时间
- 状态
3.11.7.2 程序稽核保障
开发程序对指标进行监控
3.11.8 数据交付
数据交付标准化
3.12 数据探查
- 数据开发，模型建设之前，先了解数据结构，数据内容，数据特性，对数据有一个整体把控
- 探查一下本次需求能不能实现，怎么实现，有没有隐藏bug，数据质量如何
永洪科技，致力于打造全球领先的数据技术厂商，具备从数据应用方案咨询、BI、AIGC智能分析、数字孪生、数据资产、数据治理、数据实施的端到端大数据价值服务能力。
更多推荐
23
0- 0
已为社区贡献2条内容
所有评论(0)