数据仓库OLTPOLAP维度讲解

✨博客主页： https://blog.csdn.net/m0_63815035?type=blog

💗《博客内容》：大数据、Java、测试开发、Python、Android、Go、Node、Android前端小程序等相关领域知识
📢博客专栏： https://blog.csdn.net/m0_63815035/category_11954877.html
📢欢迎点赞 👍 收藏 ⭐留言 📝
📢本文为学习笔记资料，如有侵权，请联系我删除，疏漏之处还请指正🙉
📢大厦之成，非一木之材也；大海之阔，非一流之归也✨

一、在线事务处理（OLTP）和在线分析处理（OLAP）的区别【重点】

OLTP与OLAP概述

1.OLTP（在线事务处理）和OLAP（在线分析处理）是数据处理方式的两种主要类型。

2.OLTP强调事务的ACID特性（原子性、一致性、隔离性、持久性）和实时响应。

3.OLAP侧重于数据分析，不强调事务性操作，查询速度较慢。

OLAP的特点

1.OLAP查询通常比较慢，因为需要处理海量数据。

2.OLAP查询结果常用于决策支持，如报表和数据分析。

3.OLAP系统通常存储历史数据，并对其进行规范化处理。

OLAP与OLTP的比较

1.OLAP适合进行数据分析，OLTP适合处理实时事务。

2.OLAP查询速度较慢，OLTP查询速度快。

3.OLAP存储历史数据，OLTP处理当前数据。

ETL过程

1.ETL（提取、转换、加载）是将数据从数据源提取并转换后加载到数据仓库的过程。

2.ETL过程包括数据清洗、整理等操作。

3.ETL后的数据用于OLAP分析

报表与即席查询

1.报表是数据库中的表，用于展示统计指标结果。

2.即席查询允许用户通过页面请求快速获取数据仓库中的信息。

3.即席查询通常需要借助大数据组件实现快速查询。

MOLAP与ROLAP

1.MOLAP（多维查询）通过预计算和构建立方体来提高查询速度。

2.ROLAP（请求时计算）采用MPP分布式架构进行查询。

3.HOLAP（混合查询）结合了MOLAP和ROLAP的特点。

OLTP和OLAP的对比总结

二、数仓的定义【了解】

数据仓库的必要性

1.没有数据仓库的情况下，直接从业务库中获取数据进行分析会面临诸多问题。

2.数据仓库可以统一存储、计算和展示多个异构数据源，缓解资源竞争问题。

3.数据仓库提供历史数据，支持环比和同比分析，同时整合多个语言系统的数据。

数据仓库的技术组件

1.数据仓库不是某一种技术，而是由一系列大数据软件组件组合而成。

2.数据仓库包括数据抽取、清洗、计算和报表展示等技术。

3.常见的组件包括Flume、DataX、Hadoop、Hive、Spark等。

数据仓库的架构和组件

1.数据仓库的底层架构包括Hadoop和HDFS，提供高可用的存储和计算能力。

2.Hadoop的MapReduce和Hive的SQL分析引擎用于数据处理和分析。

3.Spark基于内存计算，提供更快的处理速度。

4.数据仓库还包括调度、监控、数据治理和权限控制等组件。

数据仓库的优缺点

1.数据仓库的优点包括统一存储、计算和展示，支持历史数据分析和分布式计算。

2.数据仓库的缺点包括搭建和维护的复杂性，以及需要专业的技术和团队来支持。

三、数仓与数据库的区别

1.数据仓库不是大型数据库，也不是用来替代数据库的。

2.数据库面向事物，数据仓库面向主题。

3.数据库存储业务数据，数据仓库存储历史数据。

4.数据库设计尽量避免冗余，数据仓库会刻意引入冗余。

5.数据库针对某一业务应用进行设计，数据仓库确定业务线后再确定维度。

6.数据库是为了捕获数据，数据仓库是为了分析数据。

数仓发展史概述

1.数仓发展史分为三个时代：1.0时代（传统数仓）、2.0时代（Hadoop生态）、3.0时代（云端数仓）。

传统数仓1.0时代(传统数仓使用关系型数据库搭建数据仓库。 )

1.概念阶段（1978-1988年）：麻省理工学院研究员意识到业务系统与分析型系统的区别，提出采用完全不同的架构和设计。 2.萌芽阶段（20世纪80年代中后期）：EDC公司定义了TI2规范，包括数据获取、访问目录和用户服务。 3.集成阶段（1988年）：IBM公司在TI2规范基础上定义了新的规范，提出了信息仓库的概念，并定义了85种组件。 4.确立阶段（1991年）：比尔恩门撰写《构建数据仓库》一书，提出了数据仓库的指导性意见，包括面向主题、集成历史、包含历史、稳定和面向决策等概念。 5.数据集市阶段（1996年）：金伯尔提出数据集市的概念，自下而上地构建数据仓库，采用维度建模。 6.合并阶段（1998年）：比尔恩门提出CRF企业信息工厂，将数据集市包含进来，握手言和。 7.传统数仓阶段（2003年以前）：传统数仓主要使用关系型数据库，特别是支持OLAP的大规模数据库

数仓2.0时代(时代引入Hadoop，解决了大数据分析的需求。)

1.Hadoop的诞生（2006年）：道格卡廷基于谷歌的分布式存储和计算技术，开发了Hadoop并开源。 2.Hadoop的普及（2008年以后）：Hadoop解决了大数据分析的需求，但计算速度较慢。 3.SQL on Hadoop（2012年以后）：Facebook开发了Hive，允许在Hadoop上编写SQL，降低了使用门槛。 4.MPP时代（2014年以后）：基于内存的OLAP分析时代到来，满足了快速分析大数据的需求。

数仓3.0时代(时代通过上云，减少了环境搭建和运维维护的人力。)

1.云端数仓（2016年以后）：通过上云，减少了环境搭建和运维维护的人力，提高了开发效率。 2.阿里云、百度云、华为云和亚马逊云等提供了完整的云端数仓平台，包括监控、告警、原数据管理和权限管理等功能。

四、数据模型建设【了解】

数据模型建设是数据仓库理论的一部分，主要介绍数据模型建设的基本概念和流程。

数据建模的定义和重要性

1.数据建模是数据组织和存储的方式，强调从业务数据存取和使用的角度合理存储数据。

2.数据模型需要体现业务，用于存储和获取数据，并且要合理设计，避免资源浪费。

3.数据模型是数据分析中的重要概念，用于抽象和描述数据，帮助理解数据中的规律和关系。

模型的定义和特征

1.模型是现实世界中某个对象的特征模拟。

2.例子包括地图、建筑设计沙盘和航模飞机，它们都模拟了现实世界的某些特征。

3.数据模型也是对现实世界中某些对象的特征进行抽象，方便后续操作。

数据模型的重要性

1.数据模型是数据库系统的核心和基础，反映现实世界中的对象和概念。

2.数据模型的真实性、可理解性和实现性是其三要素。

3.真实性要求模型完全模拟现实世界；可理解性要求模型构建细致且具体，方便沟通和理解；实现性要求模型具有具体的可执行落地方案。

数据模型的作用（为什么需要数据模型？）

1.数据模型可以更好地组织存储数据，在性能、成本、效率和数据质量之间找到平衡点。

2.数据模型解决业务梳理、改进业务流程、建立全方位数据视角、消灭信息孤岛等问题。

3.数据模型帮助解决业务的变动和数据仓库的灵活性问题，加快数据仓库系统的建设速度

数据模型建设流程

1.通用数据模型建设包括概念数据模型、逻辑数据模型和物理数据模型三个层次。

2.概念数据模型类似于建筑规划图，确定实体和关系；逻辑数据模型类似于建筑设计图，丰富实体属性和关系；物理数据模型类似于施工方案，转换具体类型字段和约束。

3.数据仓库模型建设包括业务概念、逻辑物理四个步骤。业务建模确定业务主线和主题；领域建模确定实体；逻辑建模完善实体属性和关系；物理建模落地执行具体方案。

数据模型方法论

1.数据模型方法论包括ER建模和维度建模，重点介绍这两种方法论。

2.ER建模在OLTP领域使用较多，强调实体关系；维度建模在数据仓库构建中应用广泛，强调事实表和维度表。

五、关系数据模型（E-R模型建模）【重点】

ER建模概述

1.ER建模用于OLTP事务性属性，由比尔·恩文提出，但因专业性要求高导致大面积失败。

2.维度建模由King BO推出，强调适当的冗余，解决数据膨胀和查询慢的问题。

ER模型的基本概念

1.ER模型由美籍华裔计算机科学家陈平山提出，强调实体、属性和关系。

2.实体用长方形表示，属性用椭圆形表示，关系用菱形表示。

3.关系可以具有自己的属性，用于连接实体

ER模型的设计步骤

1.抽象出实体，找出实体之间的关系。

2.完善实体的属性。

3.根据ER图转化成数据库表。

三范式及其应用

1.三范式包括第一范式、第二范式和第三范式，是数据库表设计的重要规范。 2.第一范式：列不可再分。

3.第二范式：建立在第一范式基础上，要求有主键，非主键属性依赖主键。

4.第三范式：建立在第二范式基础上，要求属性不依赖主键外的其他属性。

5.三范式解决了数据冗余和查询性能问题。

第零范式及其应用

1.第零范式允许表中某列存储集合或对象，减少数据冗余。

2.适用于大数据领域，需要数据库支持存储对象或集合。

逆规范化及其应用

1.逆规范化通过增加冗余数据减少关联查询，提高查询性能。

2.适用于大数据和数仓领域，通过适当的冗余提升查询速度。

六、维度数据建模（维度建模）【重点】

维度建模概述

1.维度建模是数据仓库构建过程中采用的一种重要方法，由kimball大师提出。

2.维度建模认为在数据仓库构建中不应使用ER模型，以避免不必要的冗余并提升查询速度。

数据集市与数据仓库

1.数据集市是数据仓库的组成部分，通过逐步构建数据集市来逐步完善数据仓库。

2.维度建模中包含事实表和维度表，事实表用于存储度量值，维度表用于描述观察数据的角度。

事实表与维度表

1.事实表用于存储度量值，通常包含大量数字数据，并与其他维度表通过外键关联。 2.维度表用于描述观察数据的角度，如时间、客户、产品等，包含主键和外键。

度量的概念

1.度量值是事实表中的可度量值，通常为数字，但也可能为其他类型。

2.度量值用于计算如数量和金额等，是事实表的核心内容。

指标的概念

原子指标：

派生指标：

衍生指标

七、维度表【重点】

基本概念

1.维度表是维度建模的基础和灵魂，用于描述事实表中各个维度属性的信息。

2.维度表包含维度的主键和维度的属性，用于快速准确地过滤和统计事实表中的数据。

3.维度表可以帮助用户进行钻取、过滤和统计事实表中的数据，方便深入分析理解数据。

维度表的来源和设计方法

1.维度表的设计包括背景调查、需求分析、数据域划分、业务系统表结构分析等步骤。

2.通过与业务人员的交流和业务系统的表结构分析，可以发现维度并进行落地建表。

3.维度表的主键设计有自然键和代理键两种方式，选择主键需考虑维表的修改需求和历史数据保留策略。

维度表的属性和作用

1.维度表的属性是查询约束条件、分组和报表标签生成的基本来源，是数据应用性的关键。

2.维度表的属性用于查询约束、分类汇总、排序等操作。

3.维度表的设计应丰富维度属性，以便于后期查询约束、汇总和排序。

维度表的主键设计

1.维度表的主键有自然键和代理键两种方式。

2.自然键使用维度的自然唯一标识，如身份证号，优点是有上下文关系，缺点是占用空间大。

3.代理键是系统生成的主键，优点是简单节省空间，缺点是没有上下文关系。 4.选择主键需考虑维表的修改需求和历史数据保留策略。

维度表的设计总结

1.根据具体情况选择自然键或代理键作为维度表的主键。

2.丰富维度表的属性，便于查询约束、分类汇总和排序。

八、维度设计方法概述

1.维度设计方法主要分为四大步，参考自阿里巴巴和美团的实践。

2.这些定义来自权威的大厂实践，具有较高的参考价值。

维度设计过程

1.维度设计过程就是确定维度属性的过程。

2.选择维度或新建维度，确定维度表，并选择合适的主维表。

3.完善维度的属性，通过业务系统确定主维表和相关维表。

选择维度或新建维度

1.选择维度或新建维度是一个抽象的概念，涉及确定范围较大的维度。

2.在企业级数据仓库中，必须保证维度的唯一性。

3.以淘宝商品维度为例，商品维度表下有类目属性。

确定主维表

1.主维表一般是ODS表，与前台商品中心系统同步的商品表。

2.主维表的确定涉及业务系统的同步和数据仓库的分层。

3.通过与业务人员交谈或查询业务库，可以发现维度和维度属性。

确定相关维表

1.确定相关维表，如类目、SPU、卖家店铺等，与主维表相关联的维度。

2.通过查询分析主维表的关联关系，确定相关维表。

确定维度属性

1.确定维度属性，将主维表和相关维表中的属性抽取到一张表中。

2.考虑维度表的拆分和合并，以生成丰富的维度。

3.以淘宝商品维度为例，从主维表和类目、SPU、卖家店铺等表中抽取属性，生成新的商品维度表。

九、维度统一&维度拆分&维度设计原则

维度统一

1.维度统一的目的：将来源于多个系统的数据维度进行统一，确保表名字段名的一致性。

2.公共编码的统一：如性别编码（男：1，女：0）。

3.业务含义相同的表合并：将业务含义相同的表合并到一张表中，或采用主从表的方式。

维度拆分

1.维度拆分的目的：优化查询性能，将不常用的维度数据分离，减少join操作。

2.水平拆分：将维度表拆分为子表，通过主键关联。

3.垂直拆分：将维度表的属性拆分为独立的表，事实表需要关联多个维度表。

维度设计原则

1.生成丰富的维度属性：尽可能多的生成维度属性，提供丰富的观测数据角度。

2.给出富有意义的文字性描述：避免数字编码，采用文字描述，减少数据冗余。

3.区分数值型属性和事实：区分维度属性和度量值，确保数据使用的准确性。

4.沉淀通用维度属性：将通用的维度属性抽取出来，提高下游使用的便利性。

5.退化维度：适当退化维度，减少join操作，提高查询效率。

6.缓慢变化维：采用插入新行的方式处理维度变化，保留历史数据。

十、事实表

事实表的定义和特性

1.事实表定义：记录现实世界中发生的真实事件，反映业务的真实情况。

2.事实表特性：具有真实性，记录现实中的操作和度量值。

3.度量值：未来用于分析的数据值，如订单金额、商品数量。

4.事实表通过维度进行分析：通过不同的维度（如时间、地区、商品等）对度量值进行分组和汇总。

事实类型的分类

1.可加性事实：可以与任意维度进行汇总，结果有意义。

2.半可加性事实：只能按特定维度汇总，如库存。

3.不可加性事实：通过计算得出，如优惠率。

事实表在ER建模中的角色

1.事实表包含与业务过程相关的维度引用和业务过程度量。

2.维度表：观察数据的角度，不包含度量值。

3.事实表总结：包含与业务过程相关的维度引用和业务过程的度量值。

事实表的修改和历史变化

1.事实表一般不修改，以反映历史变化。

2.拉链表：一种节省空间的技术，通过两个时间键反映历史变化。

3.事实表随着业务发展不断产生新的数据，但一般不再修改旧数据。

事实表的设计原则

1.原子性：事实表中的数据应是最小不可分割的单位。

2.可累加性：度量值应支持累加操作。

3.时间戳：记录数据的时间信息，以反映历史变化。

【设计原则】※

1.尽可能包含所有与业务相关过程的事实。

2.只选择与业务过程相关的事实。

3.分解不可加事实为可加组件。

4.在选择维度和事实之前，先声明力度。

5.在同一个事实表中，不能有多种不同力度的事实。

6.事实的单位要保持一致。

7.对事实的null值要处理。

8.使用退化维度提高事实表的应用性。

事实表设计方法

1.选择业务过程，确定事实表的类型。

2.声明力度，确定事实表的存储方式。

3.确定维度，观测数据的角度。

4.确定事实，选择度量值。

5.融余，将常用维度退回到事实表中。

十一、事实表的分类

1.事实表分为事物事实表、周期快照事实表和累积快照事实表。

2.事物事实表：记录用户单次操作事件，力度细，稀疏。

3.周期快照事实表：按固定时间段汇总数据，稠密。

4.累积快照事实表：记录生命周期事件，周期不固定。

事实表与维度表的数据来源

1.事实表和维度表的数据来源于公司的业务数据库。

2.业务数据库记录用户操作，如增删查改行为。

3.数据仓库通过ETL过程复制业务数据库的数据。

4.ETL过程包括数据抽取、转换和加载。

数据抽取方式

1.数据抽取方式有全量和增量。

2.全量抽取：复制表中的所有信息。

3.增量抽取：只复制新增的数据。

4.抽取频率通常为每天。

事物事实表

1.事物事实表力度细，每行记录为用户的一次操作事件。

2.事物事实表是稀疏的，可能没有数据。

3.常见于需要高精度数据的情况。

事务事实表概述

1.事务事实表是数据仓库中最常见的表类型，用于存储业务过程中的原子操作事件。

2.事务事实表保留原始数据，反映历史操作，主要用于数据分析。

3.事务事实表记录业务对象相关的事务性数据，如交易流水、操作日志等。

事务事实表设计流程

1.选择业务过程，如下单、支付、发货、收货。

2.确定力度，如下单为子订单，支付为子订单，发货为物流单。

3.确定维度，如买家维度、卖家维度、商品类目、地区维度等。

4.确定事实，如下单金额、支付金额、折扣红包等。

5.淘宝设计融余维度，提高查询性能。

订单事务事实表

1.订单事务事实表存储每天所有用户在下应用里下的订单，按天增量导入数据仓库。 2.每条记录表示一个细粒度的原子操作事件，记录订单的详细信息。 3.为了提高查询性能，通常设计订单周期快照实时表。

单事务事实表和多事务事实表

1.单事务事实表：每个业务一个事实表，如下单事实表、支付事实表。

2.多事务事实表：多个业务放到一个事实表中，使用不同的事实字段或相同的字段但用不同的标签。

3.多事务事实表的缺点是存在大量的空值。

事务事实表的不足之处

1.存量型指标计算：如余额、库存、累计交易额，计算成本高，查询时间长。

2.多事物关联统计计算：如统计最近30天下单到支付的时间间隔，效率低。

3.解决方案：使用周期快照实时表和累积快照实时表。

周期快照事实表

1.周期快照事实表按固定时间段（如天、周、月）汇总数据。

2.每行记录一个周期的数据，稠密。

3.常见于需要汇总数据的情况。

周期快照事实表概述

1.周期快照事实表定义：按照指定的周期聚集事务事实表的记录进行插入。

2.常见用途：记录实体的度量值，不需要聚集长期事务历史。

3.设计建议：事务事实表与周期快照事实表成对设计。

周期快照事实表示例

1.示例：在线人数日活统计。

2.无周期快照事实表时，需每天统计事务事实表数据。

3.使用周期快照事实表，每天插入一行数据，记录当天的日活。

周期快照事实表特点

1.记录事实的规律性和可预见的时间间隔。

2.适用于存量性和状态性指标。

3.存量性：如账户余额、商品库存。

4.状态性：如温度、行驶速度。

周期快照事实表与事务事实表比较

1.事务事实表：记录原子事务操作，数据量大。

2.周期快照事实表：按周期聚集数据，数据量较小。

3.查询效率：周期快照事实表提高查询速度。

周期快照事实表的聚集操作

1.聚集操作：对周期快照事实表进行再次聚集，优化数据。

2.常见聚集间隔：年、月、日。

3.注意事项：结合业务需求确定聚集间隔。

周期快照事实表的特性

1.用快照采样状态：按预定间隔采样状态度量。

2.快照力度：多维声明，包括时间周期和度量维度。

3.密度和稀疏性：周期快照事实表稠密，事务事实表稀疏。

4.半可加性：状态度量半可加，不能无脑汇总。

实际案例分析

1.单维和混合维：单维度表简单，混合维度表详细。

2.数据加工方式：从事务事实表汇总或直接从业务库加工。

3.淘宝卖家快照表：包括自然年至今和财年至今的下单金额、买家数等。

周期快照事实表的注意事项

1.事物与快照成对设计：事务事实表和快照事实表成对设计，互相补充。

2.附加事实：通过融余设计，减少多次使用快照表。

3.周期到日期的度量：设计时考虑多种周期，如天、周、月、年等。

累积快照事实表

1.累积快照事实表记录生命周期事件，周期不固定。

2.每行记录一个生命周期的事件，减少空值存储。

3.常见于需要记录生命周期事件的情况。

今天这篇文章就到这里了，大厦之成，非一木之材也；大海之阔，非一流之归也。感谢大家观看本文