数据仓库设计基础
2018-08-22 本文已影响85人
千反田爱瑠爱好者
关系数据模型
数据结构
- 关系（表，实体）
- 属性（列，字段）
- 属性域（约束）
- 元组（行，记录）
- 键（超键，候选键，主键，外键）
主键选取原则
- 尽可能小、数字类型（如AUTO_INCREMENT）
- 不可修改（避免外键引用失效）
- 没有业务含义（避免随业务改变而修改）
- 可多列组成，但尽可能少
完整性约束
- 实体完整性（主键不为空）
- 参照完整性（外键值与主表某些记录的候选键值相同，或外键值全为空，即无关联）
业务规则：包括属性域和完整性规则（check）
关系数据库语言（SQL）：
- DDL（数据定义语言）：create，alter，drop，truncate，comment，rename
- DML（数据操纵语言）：select，insert，update，delete，merge，call，explain，lock
- DCL（数据控制语言）：grant，revoke
- TCL（事务控制语言）：commit，rollback，savepoint，set transaction
规范化（减少数据冗余度、提高更新效率，但联表过多降低查询性能）
- 1NF（原子性列）
| id | name | mobile | dept_no | dept_name | province | city | zip |
|---|---|---|---|---|---|---|---|
| 1 | 张三 | 13800138000 13800138001 |
d1 | 部门1 | 广东 | 广州 | 110 |
| 2 | 李四 | 13800138002 | d2 | 部门2 | 北京 | 北京 | 111 |
mobile列可存放多个号码，应该拆分：
| id | name | mobile | dept_no | dept_name | province | city | zip |
|---|---|---|---|---|---|---|---|
| 1 | 张三 | 13800138000 | d1 | 部门1 | 广东 | 广州 | 110 |
| 1 | 张三 | 13800138001 | d1 | 部门1 | 广东 | 广州 | 110 |
| 2 | 李四 | 13800138002 | d2 | 部门2 | 北京 | 北京 | 111 |
- 2NF（满足1NF条件下消除部分依赖）
上表中候选键{id, name, dept_no}，dept_name依赖dept_no、name依赖id，因此应该把表拆分为如下三个表：
部门表：
| dept_no | dept_name |
|---|---|
| d1 | 部门1 |
| d2 | 部门2 |
员工-部门表：
| id | dept_no |
|---|---|
| 1 | d1 |
| 2 | d2 |
员工-号码表：
| id | mobile | province | city | zip |
|---|---|---|---|---|
| 1 | 13800138000 | 广东 | 广州 | 110 |
| 2 | 13800138002 | 北京 | 北京 | 111 |
- 3NF（满足2NF条件下消除传递依赖）
上面的员工表中province、city依赖zip，而zip依赖id即为传递依赖，应把表再拆分成员工表和地区表：
员工表：
| id | mobile | zip |
|---|---|---|
| 1 | 13800138000 | 110 |
| 2 | 13800138002 | 111 |
地区表：
| zip | province | city |
|---|---|---|
| 110 | 广东 | 广州 |
| 111 | 北京 | 北京 |
优势
- 非冗余性
- 稳定性
- 一致性
- 灵活性
维度数据模型
事实表，维度表（规范化级别低于关系模型），数据粒度
构建流程
- 选择业务流程（UML）
- 声明粒度（订单/消费流水）
- 确认维度（日期：年/月/日）
- 确认事实（金额/数量）
维度规范化
把一个维度映射成多个维度表（维度-子维度连接，如地址表拆分为省份表-城市表-区域表）。
避免规范化处理的情况：
- 增加表数量使结构复杂
- 多表连接使查询复杂
- 不适合使用位图索引
- 降低查询性能
优势
- 易理解
- 高性能（非规范化，如原来有订单明细表和订单表，可以从订单明细状态中提取出订单状态维度表、再把订单表和订单明细表整合到一起）
- 可扩展
星型模型
以事实表为中心，多个维度表与事实表相连。
其中事实表包括：
- 事务事实表（如销售行为）
- 快照事实表（如用户月底余额）
- 累积事实表（如月总营收）
优点：
简化查询，简化业务报表逻辑，提升查询性能，快速聚合，便于向OLAP提供数据；但不能保证数据完整性（通过批处理和实时处理抵消），分析需求不灵活；
雪花模型
对星型模型的维度表再作规范化处理（如把机器的门店维度表拆分成门店表和门店地址表），比星型模型节省存储空间但同时降低查询性能，也可两者结合（底层使用雪花模型，配置视图模拟星型模型）。
Data Vault模型
由中心表（HUB）、链接表（Link）、附属表（Satelite）组成，综合了3NF和星型模型的优势；
存放原始数据（所有时间所有数据），不遵照任何业务规则，不同数据源的数据冲突时可存放多个版本数据，数据解析延迟到数据集市阶段。
- 中心表（如用户、机器、订单表等）：主键（系统生成的代理键）、业务主键（如用户id，机器id，可能用于多个系统，但只保留一份）、装载时间、数据源
- 链接表（中心表之间的链接关系，如订单-用户关联表）：主键、外键、装载时间、数据源
- 附属表（中心表和链接表的附属信息，如订单详情表）：主键、外键、装载时间、失效时间、数据源、属性xxx
特点
- 基于时间存储
- 依赖越少越好
- 与源系统越独立越好
- 适应源系统的数据变化、支持扩展
- ETL作业可反复执行
- 数据可追踪
构建流程
- 设计中心表
- 设计链接表
- 设计附属表
- 设计必要的PIT表
数据集市
存放单一主题（财务、销售部门等）、数据仓库/事务系统的少量数据源的粗粒度数据（汇总数据），只保留一段时间范围内（如数月），通常使用星型模型、雪花模型，存放的是ETL的输出数据。
数据仓库实施
- 定义范围
- 确定需求
- 逻辑设计
- 物理设计
- 装载数据
- 访问数据
- 管理维护
