大数据数仓建模基础理论【维度表、事实表、数仓分层及示例】

本文涉及的产品
智能开放搜索 OpenSearch行业算法版,1GB 20LCU 1个月
大数据开发治理平台 DataWorks,不限时长
实时计算 Flink 版,5000CU*H 3个月
简介: 数据仓库建模是组织和设计数据以支持数据分析的过程,包括ER模型和维度建模。ER模型通过实体和关系描述数据结构,遵循三范式减少冗余。维度建模,特别是Kimball方法,用于数据仓库设计,便于分析和报告。事实表存储业务度量,如销售数据,分为累积、快照、事务和周期性快照类型。维度表提供描述性信息,如时间、产品、地点和客户详情。数仓通常分层为ODS(源数据)、DWD(明细数据)、DIM(公共维度)、DWS(数据汇总)和ADS(应用数据),以优化数据管理、质量、查询性能和适应性。

@[toc]

什么是数仓仓库建模?

数据仓库建模(Data Warehouse Modeling)是指在数据仓库(Data Warehouse)中组织和设计数据的过程,以便支持数据分析、报告和决策制定。

数据仓库是一个集成的、主题导向的数据存储,用于存储来自不同来源的数据,经过清洗、转换和集成,以支持业务分析和决策。主要目标是创建一个能够满足用户需求的数据结构,以便用户可以轻松地查询和分析数据。

数据仓库建模的选择取决于业务需求、数据的性质以及可用的技术和工具。建立一个有效的数据仓库模型可以帮助组织更好地理解其业务数据,做出更明智的决策,并提高业务绩效。

ER 模型

实体-关系(ER)模型是一种用于描述数据结构和数据之间关系的概念性数据建模方法。它用于数据库设计和数据建模,帮助人们理解和表示实体(数据对象)以及它们之间的关系。

三范式

关系数据库中的三范式(Third Normal Form, 3NF)是一种数据规范化的方法,目的是为了减少数据冗余并确保数据的一致性和完整性。

这里我不再赘述如何辨别区分三范式,网上的优质文章很多,这里给大家推荐一篇:Mysql - 什么是三大范式(通俗详解)

三范式有助于设计高效且具有数据一致性的数据库模型,减少数据冗余,同时使数据更容易维护和更新。然而,对于某些特定的查询需求,有时需要权衡三范式的完全符合以优化查询性能。

维度建模

维度建模的起源可以追溯到 20 世纪 80 年代末和 90 年代初,当时数据仓库和商业智能的概念开始崭露头角。维度建模的方法论主要由 Ralph Kimball 和他的团队推动和发展,他们在这一领域的工作对商业智能和数据仓库的发展产生了深远的影响。

维度模型是一种用于设计数据仓库的模型,主要用于支持数据分析和报告。这种模型通过组织数据成维度表和事实表的结构,使得用户能够以直观的方式理解和查询数据。

事实表

事实表是数据仓库中存储了业务过程中产生的事实性数据的表格。它包含了数值性能度量或事实,例如销售额、数量、利润、点击次数等,这些数据通常是可以被聚合、分析和计算的。

事实表通常与维度表相结合,通过外键与维度表关联,以提供数据的上下文信息和多维分析的可能性。

举个例子,假设我们有一个在线零售商店,它包含了销售数据。

事实表 - 销售事实表

订单ID 产品ID 日期ID 客户ID 销售额 数量
1001 101 500 200 150 2
1002 102 501 201 75 1
1003 103 502 202 100 3

在这个例子中,事实表包含了几个重要的字段:

  • 订单ID:唯一标识每个销售订单的ID。
  • 产品ID:与产品维度表关联的外键,提供了有关销售的产品信息。
  • 日期ID:与日期维度表关联的外键,提供了销售发生的日期信息。
  • 客户ID:与客户维度表关联的外键,提供了购买客户的信息。
  • 销售额:表示每个订单的销售金额。
  • 数量:表示每个订单中产品的数量。

这个事实表中的数据可以用于进行多种分析,例如按日期分析销售趋势、按产品类别分析销售额、按客户分析购买习惯等。通过与维度表的结合,可以进行复杂的多维分析,帮助企业了解业务运营的各个方面。

事实表类型

事实表可以根据存储的数据类型和所描述的业务过程类型来进行分类。

1.累积事实表
这种事实表包含了事务型数据的累积值,通常用于记录累积的总量或累积的周期性度量。例如,累积销售事实表记录了累计的销售额、总利润、总销售量等数据。

日期ID 产品ID 累计销售额 累计利润 累计销售量
... ... 50000 15000 1000
... ... 52000 16000 1050

2.快照事实表
这种事实表记录了在特定时间点或时间段内的业务度量值。快照通常定期捕获数据的状态,而不是随着每个业务事件的发生而更新。

快照日期 产品ID 当日销售额 当日订单数
日期1 产品1 1000 25
日期2 产品1 1200 30

3.事务事实表
这种事实表记录了每个业务事件的详细事务数据,每一行代表一个独立的事务或事件。它们通常是针对某个特定的业务过程,包含了每次事务的数据。

订单ID 产品ID 日期ID 客户ID 销售额 数量
1001 101 500 200 150 2
1002 102 501 201 75 1

4.周期性快照事实表
类似于快照事实表,但记录了经过一定时间间隔的快照数据。这种类型的事实表通常用于分析跨越不同时间段的变化趋势。

日期范围 产品ID 月销售额 季度销售额
2023年1月 产品1 500 1500
2023年2月 产品1 600 1400

这些不同类型的事实表在数据仓库中有不同的用途和分析方法,根据业务需求选择合适的事实表类型有助于更有效地进行数据分析和洞察。

维度表

维度表是数据仓库中存储描述业务对象的结构化信息的表格,它包含了用于分析的各种维度的详细信息。

维度表通常包含描述业务过程中的人、地点、时间、产品或事件等的属性。这些表格通常具有清晰的层次结构,用于帮助用户理解和分析数据。

维度表类型

1.时间维度表
时间维度表是描述日期、时间和与之相关的层次结构信息的表格,例如年、季节、月份、日期、周几等。它能支持按时间进行数据分析,比如时间趋势分析、周期性分析等。

日期ID 日期 年份 季节 月份 节假日
500 2023-01-01 2023 冬季 1 周一
501 2023-01-02 2023 冬季 1 周二

2.产品维度表
产品维度表包含有关产品的详细信息,例如产品名称、类别、型号、制造商等,有助于对产品销售情况进行分析。

产品ID 产品名称 类别 型号 制造商
101 手机 电子产品 ABC123 公司A
102 电视 家电 XYZ789 公司B

3.地点维度表
地点维度表包含有关地理位置的信息,比如国家、城市、区域、地址等,支持地理位置相关的数据分析。

地点ID 国家 省份 城市 区域
201 中国 广东省 深圳市 南山区
202 美国 加利福尼亚州 洛杉矶 市中心

4.客户维度表
客户维度表包含有关客户的信息,如客户ID、姓名、性别、年龄、职业等,支持对客户行为和特征的分析。

客户ID 姓名 性别 年龄 职业
301 张三 35 工程师
302 李四 28 教师

维度表提供了数据的上下文和描述性信息,与事实表结合使用可以进行复杂的多维分析,帮助用户更好地理解业务数据并作出有效的决策。

数仓分层

在数仓中,通常有五层,分别为:ODS、DWD、DIM、DWS、ADS,每层存储的数据都不一样,负责的业务也不同,下面将展开说说这五层的作用以及示例。

ODS 源数据层

ODS 层是用于存储经过简单清洗、结构化、汇总的源系统数据的地方,它通常直接接收来自各个操作系统的数据,并保留了更接近源系统的数据。

从不同业务系统(如销售系统、库存系统)获取数据,将其整合、清洗并存储在 ODS 中。

ODS 层表示例

订单原始数据表

order_id customer_id product_id order_date order_amount
1001 2001 3001 2023-01-05 150.00
1002 2002 3002 2023-01-06 80.00
1003 2001 3003 2023-01-07 200.00

客户原始数据表

customer_id customer_name customer_address registration_date
2001 Alice 123 Main St, Anytown 2022-12-20
2002 Bob 456 Park Ave, Othertown 2023-01-01
2003 Carol 789 Elm St, New City 2023-01-10

产品原始数据表

product_id product_name product_category product_price
3001 Phone Electronics 500.00
3002 TV Appliances 300.00
3003 Laptop Computers 1200.00

DWD 明细数据层

DWD 层是数据仓库的核心层级,用于存储经过清洗、集成和建模的数据,以支持企业级的数据分析和决策需求。这一层通常包括了清洗后的事实表和维度表,支持多维分析。

在 DWD 中创建了销售事实表、产品维度表、时间维度表等,以支持企业级的销售分析和报表需求。

DWD 层表示例

订单事实表

order_id customer_id product_id order_date order_amount
... ... ... ... ...

客户维度表

customer_id customer_name customer_address registration_date
... ... ... ...

产品维度表

product_id product_name product_category product_price
... ... ... ...

DIM 公共维度层

DIM 层是用于存储维度模型的地方,其中包含了业务维度和度量,以便于进行多维度的分析和查询。这个层级可以支持数据的分组和聚合,以提供更快的查询性能。

在 DIM 中包含了产品、地区、时间等维度,以及关联的度量,为业务用户提供了灵活的多维度分析能力。

DIM 层表示例

时间维度表

date year month day week quarter holiday_flag
... ... ... ... ... ... ...

地域维度表

country state/province city region
... ... ... ...

支付方式维度表

payment_method_id payment_method_name
... ...

DWS 数据汇总层

DWS 层是对数据进行聚合、汇总或预计算的地方,以优化复杂查询的性能,提供快速的汇总结果。它可以包括预聚合的数据或某些业务计算结果。

在 DWS 中,可能包括了每日销售总额、每月客户活跃度等预计算结果,以提高复杂查询的性能。

DWS 层表数据

每日销售总额表

date total_sales_amount
... ...

每月客户活跃度表

month active_customers_count
... ...

ADS 数据应用层

ADS 层是根据特定业务需求定制的数据服务层,它可能会整合来自多个层级的数据,提供给特定应用程序或业务需求使用。

构建了专门为某个特定业务应用程序提供数据支持的数据服务接口,使其能够访问 DWD、DWS 和 DIM 中的数据。

ADS 层接口示例

销售分析应用接口

接口提供从订单事实表和维度表获取信息,支持销售分析和报告。

用户行为分析应用接口

接口提供从订单事实表、客户维度表和时间维度表获取信息,支持用户行为分析和趋势报告。

这五层架构在数据仓库中起着不同但相互补充的作用,以支持各种层次的数据需求,从源系统的操作数据到支持决策分析的维度模型,再到特定应用的定制数据服务。

数仓分层的优势

  1. 数据组织和管理
    分层架构有助于将数据组织成不同的层级,使数据管理更为清晰和有序。每个层级都有特定的功能和目的,有利于更好地管理数据的流动和处理过程。

  2. 数据质量和一致性
    分层能够帮助确保数据的质量和一致性。在不同的层级对数据进行清洗、整合和转换,有助于消除数据中的错误或不一致性,并确保数据的准确性和可靠性。

  3. 多维度分析和查询
    分层架构中的维度模型和数据仓库层支持多维度的数据分析和查询,使得用户能够从不同的角度对数据进行探索和分析,支持更深入的业务理解和决策制定。

  4. 性能优化
    通过在数据仓库汇总层进行数据预计算和汇总,可以提高查询性能和响应速度。预计算结果可以在需要时被快速查询,降低复杂查询的计算成本。

  5. 灵活性和适应性
    分层架构使得数据仓库能够更灵活地适应不同的业务需求和数据访问模式。它能够根据特定需求提供定制化的数据服务,从简单操作到高级分析都能得到支持。

  6. 安全和数据治理
    每个层级都可以进行数据访问控制和安全管理,有利于实现数据的安全性和合规性。此外,分层架构有助于建立数据治理策略,确保数据在整个流程中得到适当的管理和监控。

  7. 业务与技术的分离
    分层架构可以帮助将业务逻辑与技术实现分离,使得业务用户更专注于数据的使用和分析,而技术团队则负责数据的处理和管理。

分层架构使得数据仓库更具可管理性、可扩展性和灵活性,支持了数据的高效管理、多样化使用和高质量分析,对企业的数据驱动决策和业务发展起到了关键作用。

相关实践学习
AnalyticDB MySQL海量数据秒级分析体验
快速上手AnalyticDB MySQL,玩转SQL开发等功能!本教程介绍如何在AnalyticDB MySQL中,一键加载内置数据集,并基于自动生成的查询脚本,运行复杂查询语句,秒级生成查询结果。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库,高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准,可以对海量数据进行即时的多维分析透视和业务探索,快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
相关文章
|
1月前
|
数据采集 大数据
大数据实战项目之电商数仓(二)
大数据实战项目之电商数仓(二)
|
4天前
|
数据采集 运维 Cloud Native
Flink+Paimon在阿里云大数据云原生运维数仓的实践
构建实时云原生运维数仓以提升大数据集群的运维能力,采用 Flink+Paimon 方案,解决资源审计、拓扑及趋势分析需求。
333 0
Flink+Paimon在阿里云大数据云原生运维数仓的实践
|
20天前
|
存储 SQL 分布式计算
MaxCompute产品使用问题之upsert into语句如何在2.0事务表使用
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
27 10
|
20天前
|
分布式计算 DataWorks 大数据
MaxCompute产品使用问题之如何为某个用户授予仅对生产表的SELECT权限
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
20天前
|
机器学习/深度学习 分布式计算 DataWorks
MaxCompute产品使用问题之如何将DataWorks中的Hologres表数据导入到MaxCompute
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
20天前
|
SQL 分布式计算 DataWorks
MaxCompute产品使用问题之dts是否支持传输数据到mc主键表2.0
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
20天前
|
SQL 分布式计算 DataWorks
MaxCompute操作报错合集之表增加字段,提示创建表失败:DDL execute error, OdpsException: ODPS-0130071:[1,60] Semantic analysis exception - column,该怎么办
MaxCompute是阿里云提供的大规模离线数据处理服务,用于大数据分析、挖掘和报表生成等场景。在使用MaxCompute进行数据处理时,可能会遇到各种操作报错。以下是一些常见的MaxCompute操作报错及其可能的原因与解决措施的合集。
|
20天前
|
存储 机器学习/深度学习 分布式计算
MaxCompute产品使用问题之如何根据字段名或者字段注释查找到相关表
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
20天前
|
机器学习/深度学习 分布式计算 DataWorks
MaxCompute产品使用问题之如何恢复误删除生产环境的表
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
20天前
|
机器学习/深度学习 分布式计算 大数据
MaxCompute产品使用问题之ods层离线同步任务,数据源的一张表新增了字段。如何更改可以不影响当前节点和下游任务的运行
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。

相关产品

  • 云原生大数据计算服务 MaxCompute