2- 数据建模
2.1 维度建模
2.1.1 维度建模的基本概念
2.1.1.1 事实表 (Fact Table)
- 包含业务过程的度量值
- 通常是数值型的、可累加的
- 例如: 销售金额、数量等
2.1.1.2 维度表 (Dimension Table)
- 包含描述业务实体的属性
- 通常是文本类型的描述性信息
- 例如: 产品、顾客、时间、地点等
2.1.1.3 维度 (Dimension)
2.1.1.4 度量 (Measure)
- 事实表中的数值型数据
- 可以进行数据运算(如: 求和、平均等)
2.1.2 维度建模的主要模型
2.1.2.1 星型模型 (Star Schema)
- 中心是事实表, 周围是维度表
- 维度表直接与事实表相连
- 优点: 查询性能好, 易于理解
- 缺点: 可能存在数据冗余
2.1.2.2 雪花模型 (Snowflake Schema)
- 星型模型的变体, 维度表被进一步规范化
- 减少了数据冗余, 增加了表的数量
- 优点: 节省存储空间, 方便维护
- 缺点: 查询性能可能下降, 结构复杂
2.1.2.3 星座模型 (Constellation Schema)
2.1.3 维度设计技术
2.1.3.1 缓慢变化维 (Slowly Changing Dimensions, SCD)
- 处理维度属性随时间变化的方法
- 类型1: 直接覆盖旧值
- 类型2: 增加新记录, 保留历史
- 类型3: 增加新属性列, 保留当前和历史
- 类型4: 增加历史表
- 类型5: 结合1、2、3的混合方法
2.1.3.2 退化维度 (Degenerate Dimension)
- 存储在事实表中的维度属性
- 通常是业务过程产生的标识符
- 例如: 订单号、发票号等
2.1.3.3 巨型维度 (Junk Dimension)
- 将多个低基数的标志或属性组合成一个维度
- 减少维度数量, 简化模型
2.1.3.4 角色扮演维度 (Role-Playing Dimension)
- 同一个维度表在事实表中扮演多个角色
- 例如: 日期维度可以是订单日期、发货日期等
2.1.3.5 桥接表 (Bridge Table)
2.1.4 维度建模的步骤
- 选择业务过程: 确定要建模的具体业务活动
- 声明粒度: 定义事实表中单条记录代表的最小细节级别
- 确定维度: 识别描述该业务过程的所有可能角度
- 确定事实: 确定与该业务过程相关的可度量指标
- 存储预计算结果: 在适当的情况下, 预先计算并存储聚合数据
2.1.5 维度建模的最佳实践
- 使用代理键: 为每个维度表使用独立的整数型代理键
- 创建一致性维度: 垮多个事实表使用相同的维度定义
- 创建日期维度: 包含丰富的日期相关属性, 便于时间分析
- 避免过度规范化: 在性能和易用性之间找平衡
- 考虑未来的扩展性: 设计时预留空间为将来可能的变化
2.1.6 维度建模的优势
- 直观易懂, 便于业务用户理解
- 查询性能优秀, 特别是对于复杂的分析查询
- 灵活性高, 易于应对需求变化
- 支持自助式BI和OLAP分析
2.1.7 维度建模的挑战
- 可能存在数据冗余
- 初次设计时可能难以确定最佳粒度
- 处理快速变化的维度可能比较复杂
- 需要权衡存储空间和查询性能
2.2 星型模型
星型模型是一种经典的数据仓库设计模式, 因其结构类似于星星而得名 ; 它由一个中心事实表和多个围绕它的维度表组成, 并通过外键关联 ;
这种简单而直观的结构使其易于理解、查询效率高, 被广泛应用于数据仓库和商业智能领域 ;
2.2.1 组成部分
- 事实表 (Fact Table): 位于星型模型的中心, 存储关键业务指标的数值数据, 例如销售额、订单数量、网站访问量等 ; 每个事实表都与一个特定的业务过程相关联, 例如销售、订单、网站访问等 ;
- 事实表通常包含大量数据行, 因为他们记录了每个业务事件的详细信息 ;
- 事实表的结构相对简单, 通常包含一下两种类型的列:
- 度量值 (Measures): 表示业务指标的数值数据, 例如销售额、数量、成本、例如等 ;
- 外键 (Foreign Keys): 用于链接到维表, 表示与该事实相关的维度信息 ;
- 维度表 (Dimension Table): 围绕在事实表周围, 提供关于事实的上下文, 例如时间、地点、产品、客户等 ; 维度表回答了关于事实的 “谁、什么、何时、何地、为什么” 等问题 ;
- 维度表通常包含较少的数据行, 因为他们描述的是业务体的属性, 例如产品类别、地区、客户类型等 ;
- 维度表的结构可以比较复杂, 可以包含多个层次结构, 例如时间维度可以包含年、季度、月、日等层次结构 ;
- 维度表通常包含以下两种类型的列:
- 主键 (Primary Key): 唯一标识维度表中的每一行数据 ;
- 属性 (Attributes): 描述维度信息的文本或数值数据, 例如产品名称、类别、颜色、尺寸、地区名称、邮编、客户姓名、性别、年龄、地址等 ;
2.2.2 优点
- 易于理解: 星型模型的结构简单直观, 及时是非技术人员也能轻松理解, 方便业务人员理解数据结构和进行数据分析 ;
- 查询性能高: 星型模型采用去规范化的设计, 将数据冗余存储在维度表中, 避免复杂的多表连接, 因此查询效率非常高 ;
- 易于扩展: 可以很容易地添加新的维度表或事实表, 以适应不断变化的业务需求, 例如增加新的产品线、开拓新的市场等 ;
2.2.3 应用场景
星型模型适用于一下场景:
- 数据分析和报表: 星型模型非常适合用于构建报表和仪表板, 因为它可以快速地对数据进行切片、切块、钻取等操作 ;
- 商业智能(BI): 星型模型是许多BI工具的基础, 因为它可以提供对数据的快速访问和分析 ;
- 数据挖掘: 星型模型可以用于构建数据挖掘模型, 因为它可以提供结构化的数据, 方便算法进行训练和预测 ;
2.2.4 示例
以一个电商网站的销售数据为例, 我们可以设计一下的星型模型:
- 事实表: 销售事实表 (FactSales)
- 销售额 (SalesAmount)
- 成本 (Cost)
- 利润 (Profit)
- 订单 ID (外键) (OrderID)
- 产品ID (外键) (ProductID)
- 时间ID (外键) (TimeID)
- 客户ID (外键) (CustomerID)
- 维度表:
- 订单维度表 (DimOrders): 订单ID (OrderID), 订单日期 (OrderDate), 订单状态 (OrderStatus)等
- 产品维度表 (DimProducts): 产品ID (ProductID), 产品名称 (ProductName), 产品类别 (ProductCategory), 产品价格 (Price) 等
- 时间维度表 (DimTime): 时间ID(TimeID), 日期 (Date), 星期 (Week), 月份 (Month), 季度 (Quarter), 年份 (Year) 等
- 客户维度表 (DimCustomers): 客户ID (CustomerID), 客户姓名 (CustomerName), 客户性别 (Gender), 年龄 (Age), 客户地区 (Region) 等
通过将这些表连接起来, 我们可以轻松地查询各种销售数据, 例如:
- 2024 年第一季度华东地区各个产品类别的销售额
- 2024 年 3 月份女性客户购买的各个产品的销售数量
- 不同年龄段客户的平均订单金额
2.2.5 总结
星型模型是一种简单、高效的数据仓库设计模型, 使用与各种数据分析和商业智能应用 ; 其易于理解、查询性能高和易于扩展的特性使其成为构建数据仓库的首选方案之一 ;
2.3 雪花模型
雪花模型是星型模型的一种扩展, 它进一步将维度表规范化, 将具有层次关系的维度属性分离到不同的表中, 形成类似雪花的分支结构 ;
2.3.1 定义
雪花模型是一种数据库设计模型, 其中维度表被进一步规范化, 形成多层结构, 看起来像雪花的形状 ;
2.3.2 结构组成
- 事实表
- 主要维度表
- 直接与事实表相连
- 包含部分描述性属性和指向次级维度表的外键
- 次级维度表
2.3.3 雪花模型的特点
- 高度规范化: 维度表被分解成多个相关的表
- 减少数据冗余: 通过规范化降低了数据重复
- 层次结构清晰: 明确展示了维度之间的层次关系
- 灵活性: 可以详细描述复杂的维度关系
2.3.4 设计步骤
- 创建星型模型: 先设计基本的星型结构
- 识别维度层次: 确定维度内的层次关系
- 规范化维度: 将维度表拆分成多个相关表
- 建立关系: 在拆分后的维度表之间建立关系
2.3.5 示例 以零售销售为例:
- 事实表: 销售事实
- 包含: 日期ID、产品ID、商品ID、销售额、销售数量
- 主要维度表:
- 产品维度: 产品ID、产品名称、类别ID、品牌ID
- 商店维度: 商店ID、商店名称、城市ID
- 次级维度表:
- 产品类别: 类别ID、类别名称
- 品牌: 品牌ID、品牌名称
- 城市: 城市ID、城市名称、州ID
- 州: 州ID、州名称、国家ID
- 国家: 国家ID、国家名称
2.3.6 优势
- 数据一致性: 减少数据冗余, 提高了一致性
- 节省存储空间: 规范化结构减少了数据重复
- 维护方便: 更新某些属性时只需要再一个地方修改
- 支持复杂的维度层次: 可以表示更复杂的维度关系
2.3.7 局限性
- 查询性能可能下降: 需要更多的表连接操作
- 复杂度增加: 模型结构比星型模型更复杂
- 不太直观: 对非技术用户来说可能较难理解
2.3.8 与星型模型的对比
- 雪花模型: 更规范化, 节省空间, 但查询可能较慢
- 星型模型: 非规范化, 查询性能好, 结构简单
2.3.9 实施考虑
- 性能优化: 可能需要更复杂的索引策略
- 视图: 考虑使用物化视图来提高查询性能
- ETL复杂性: 数据加载和转换过程可能更复杂
2.3.10 使用场景
- 大型数据仓库: 当数据量巨大, 需要严格控制冗余时
- 复杂的维度层次: 当维度有多层次结构需要表示时
- 频繁更新的维度属性: 当某些维度属性经常变化时
2.4 事实表和维度表设计
在数据仓库设计中, 事实表和维度表是两个最基本的概念, 采用维度模型设计 ; 事实表存储时间度量值, 维度表描述业务实体 ; 两者协同工作, 为分析和报表提供完整的数据视图 ;
2.4.1 事实表设计
2.4.1.1 确定业务过程和粒度
首先需要明确要分析的业务过程是什么, 例如: 销售、订单、网站访问等 ;
然后确定事实表的粒度, 即每行数据代表什么级别的业务事件 ;
- 粒度越细, 数据越详细, 但也意味着数据量越大, 查询销量越低 ;
- 粒度越粗, 数据越汇总, 数据量越小, 但可能会丢失一些细节信息 ;
选择合适的粒度需要权衡数据量、查询性能和业务需求 ;
2.4.1.2 确定度量值
度量值是事实表中最重要的部分, 它们是可以计量的数值指标, 用于描述业务过程的关键特征; 常见的度量值包括:
- 数量: 例如销售数量、订单数量、访问次数等 ;
- 金额: 例如销售额、成本、利润等 ;
- 时间: 例如订单处理时间、页面加载时间等 ;
- 比率: 例如转化率、点击率、满意度等 ;
选择度量值时需要考虑业务需求和数据可获得性 ;
2.4.1.3 设计外键
事实表通过外键与维度表关联, 每个外键都对应一个维度表的主键, 表示该事实发生在哪个维度下 ;
- 外键数量: 取决于事实表需要关联的维度数量 ;
- 外键类型: 通常使用整数类型, 以提高查询效率 ;
2.4.1.4 其它设计考虑
- 添加时间戳: 记录每行数据的创建时间和更新时间, 方便数据跟踪和审计 ;
- 预留扩展字段: 为未来可能增加的度量值预留空间, 提高数据模型的可扩展性 ;
2.4.2 维度表设计
2.4.2.1 确定维度
维度是描述业务实体的属性, 例如时间、地点、产品、客户等; 每个维度对应一个维度表, 用于存储该维度的所有可能取值 ;
2.4.2.2 设计属性
维度表的属性用于描述维度的特征, 例如:
- 时间维度: 年、月、日、小时、分钟、秒等
- 地点维度: 国家、省份、城市、地区等
- 产品维度: 产品类型、名称、品牌、型号、规格等
- 客户维度: 客户姓名、性别、年龄、地址、联系方式等
选择属性时需要考虑业务需求和数据可获得性 ;
2.4.2.3 设计层次结构
一些维度可以按照层次结构组织, 例如:
- 时间维度: 年 > 季度 > 月 > 日 > 小时 > 分钟 > 秒
- 地点维度: 国家 > 省份 > 城市 > 区县
层次结构可以方便进行多层次的钻取分析, 例如从年度销售额下钻到月度销售额, 再下钻到每日销售额 ;
2.4.2.4 其他设计考虑
- 使用代理键: 使用自增整数作为维度表的主键, 而不是使用业务主键, 可以提高查询效率 ;
- 添加描述性字段: 为每个属性添加简短的描述信息, 提高数据可读性 ;
- 处理缓慢变化维度: 对于随时间缓慢变化的维度, 例如客户地址, 需要采用合适的策略来处理历史数据 ;
2.4.3 总结
事实表和维度表的设计是数据仓库建设的基础, 需要根据具体的业务需求和数据特点进行设计 ;
一个良好的数据模型可以提高数据查询效率, 方便进行多维分析, 并支持业务决策 ;
end