ER建模
- 实体-联系图(Entity Relationship Diagram),提供了表示实体类型、属性和联系的方法,用来描述现实世界的概念模型。
- **实体:矩形 | 属性:椭圆 | 联系:菱形 4种基数约束 **
- 基数约束
- 一个实体A对应多个实体B
- 一个实体A对应0个或多个实体B
- 一个实体A对应一个实体B
- 一个实体A对应0个或1个实体B
- 复合属性 圆括号 地址属性 包括省份 城市 街道
- 多值属性 双层椭圆 属性有多值,一个职工可能有多个电话号码
- 派生属性 虚线椭圆 从其他属性或者其他数据(如当前日期)派生出来
- 可选属性 属性名后面添加(0)标识分属性可能有也可能没有取值,比如说职工奖金。
- 基数约束上可以加最大最小基数、联系的角色
维度表和事实表
- 1. 维度表 dimension
- 每个维度表都包含单一的主键列。维度表的主键可以作为与之关联的任何事实表的外键
- 表示对分析主题所属类型的描述。比如"昨天早上张三在京东花费200元购买了一个皮包"。那么以购买为主题进行分析,可从这段信息中提取三个维度:时间维度(昨天早上),地点维度(京东), 商品维度(皮包)。通常来说维度表信息比较固定,且数据量小
- 2. 事实表(fact table)
- 发生在现实世界中的操作型事件,其所产生的可度量数值,存储在事实表中。从最低的粒度级别来看,事实表行对应一个度量事件, 反之亦然。
- 表示对分析主题的度量。比如上面那个例子中,200元就是事实信息。事实表包含了与各维度表相关联的外键,并通过JOIN方式与维度表关联。事实表的度量通常是数值类型,且记录数会不断增加,表规模迅速增长。
- 常考虑两个属性:
- 事物标识码(TID),各种订单号、事物编号,不放入维度表是因为数量级太大每次查询都会耗很多资源来join,将某些逻辑意义上的维度放到事实表里的做法称为退化维度
- 事务时间:数量级大,分布式数据仓库工具会对数据进行分区,默认分区字段为日期
维度建模三种模式
-
星形模式
- 由一个事实表和一组维表构成,以事实表为核心,维表围绕核心呈星状分布
- 维表只和事实表关联,维表之间没有关联
- 每个维表的主键为单列,且该主键放在事实表中,作为两边连接的外码
-
雪花模式
- 每个维表可继续向外连接多个子维表
- 雪花模型相当于将星形模式的大维表拆分成小维表,满足规范化设计,再实际中较少
-
星座模式
- 多对多
- 维度空间内的事实表可能不止一个,一个维表可能被多个事实表用到
- 好处:能够共享维度 和 设置细节/聚集事实表
- 共享维度:公司希望用分析销售主题的方法分析劣质产品,不需要重新建模,只需要加入一个新的劣质产品事实表
- 细节事实表:每条记录表示单一事实,通常设置TID属性,查询灵活但速度慢
- 聚集事实表:每条记录聚合多条事实,无TID属性,速度快但查询功能受到一定限制
- 常见做法同时设置这两种事实表
-
三种模式对比
- 雪花模式是将星形模式的维表进一步划分,使维表满足规范化设计
- 星座模式允许星形模式中出现多个事实表
如何维度建模
地区 - 商店 - 交易记录 - 顾客 - 产品 - 种类 - 卖主
- 哪些维度对主题分析(这里是销售额)有用
- 产品product、顾客customer、商店store、日期date 对销售额分析有帮助
- 如何使用现有数据生成维表
- 产品维度 可由产品关系、供应商关系和种类关系得到
- 顾客维度 可由顾客关系得到
- 商店维度 可由商店维度和地理维度得到
- 日期维度 可由交易记录的日期列得到
- 用什么指标来度量主题
- 本例的主题是销售,销售和销售额最能反映销售情况
- 如何用现有数据生成事实表
-
销售和销售额信息可以通过交易记录得到
-
维表不满足3NF,事实表1NF都不满足,各维表的主键由xxID 变成 xxKey,Key这样的字段被称为代理码(surrogate key),它是一个通过自动分配整数生成的主码,没有任何其他意义。使用它主要是为了能够处理"缓慢变化的维度"
-
什么是缓慢变化的维度
- 当业务数据库中的一些数据发送了变化,如顾客的联系方式发生改变,应该如何把这些变化也反映到数据仓库中,一些基本信息的更改可能会引起数据归纳和分析出现的问题。在数据仓库中,其数据主要的特征一是静态历史数据,二是少改变不删除,三是定期增长,其作用主要用来数据分析。
- 缓慢渐变类型一 :不记录历史数据,将旧的业务数据覆盖重写
- 缓慢渐变类型二 :保存多条记录,直接新添记录,新增加一个 Key(代理键),配合时间戳 告诉数据仓库 哪个是最新在用的 ,通常是DW表的主键,用来连接业务数据库 和 数据仓库的,
- 代理键的好处:
- 解决这种缓慢渐变维度,维护历史信息记录
- business key可能较长,代理键可以设置为整形,效率高节省体积
- 业务数据库来自不同的系统,可能出现相同的business key,用代理键可以处理/
- 缓慢渐变类型三:添加历史列,用不同的字段保存变化痕迹.它只能保存两次变化记录.适用于变化不超过两次的维度
最常见的三种数据仓库建模体系
- 规范化数据仓库:规范化设计的分析型数据库,首先对ETL得到的数据进行ER建模,关系建模,得到一个规范化的数据库模式。各部门开发人员大都从这些数据集市提数,通常来说不允许直接访问中心数据库。
- 维度建模数据仓库: 使用交错维度进行建模的数据仓库,创建一个大星座模型表示所有分析型数据
- 独立数据集市:公司的各个组织自己创建并完成ETL,自己维护自己的数据集市,信息分散,效率低
- 三种对比
- 规范化数据仓库:需要全局进行规范化建模,前期花费时间大,投入使用慢,后期容易维护
- 维度建模数据仓库:敏捷性强,适用于业务变化频繁的情况,开发要求没那么高
联机分析处理 OLAP
- OLAP需要以大量历史数据为基础,再配合上时间点的差异,对多维度及汇整型的信息进行复杂的分析。
- Online Analytical Process,以多维度的方式分析数据,而且能够弹性地提供上卷(Roll-up)、下钻(Drill-down)和透视分析(Pivot) 等操作,它是呈现集成性决策信息的方法,多用于决策支持系统、商务智能或数据仓库
- 联机交易处理(OLTP),联机交易处理,更侧重于基本的、日常的事务处理,包括数据的增删改查。
元数据(Metadata)
一个管理元数据信息的系统,能够提供方便的元数据的操作和查询操作