数据仓库学习与实践(二)- 如何做好数据模型设计

我们知道,需求总是不断变化的,数据仓库也一样,统计分析的需求也多种多样,并非所有需求都能事先预知,在这样的前提下,作为数据仓库的建设着,唯一能做的就让系统有能够应对变化的能力,以不变应万变。

那么,如何做到以不变应万变呢?

做好数据模型建设,可能是手段之一,也是最重要的手段之一。

本文基于关系型数据库(RDBMS),介绍目前数据仓库主流建模理念维度建模的核心要点,主要包含如下几个方面:

1. 选择业务流程
2. 维度建模基本概念
3. 事实表粒度选择
4. 维度的设计
5. 统计查询与分析

选择业务流程

上一篇文章我们提到了,数据仓库是为了辅助决策的,与业务流程(Business Process)息息相关。所以建设数据模型的首要任务便是选择业务流程,为数据仓库的建立提供指导方向,这样才能反过来为业务提供更好的决策数据支撑,让数据仓库价值的最大化。

一个企业的运作,或者一个成熟的产品,一般都会多个业务流程相互配合而完成,它们彼此协作,相辅相成。对于每个业务流程,都需要进行独立的数据建模,将业务系统中的 ER 模型转化为数据仓库中的维度数据模型,以便更好的查询与分析。

为了方便理解,本文选取电商网站的订单业务流程作为案例,阐述维度数据模型建设的一些要点。

维度建模基本概念

首先解释维度建模两个基本概念,一个是事实表(Fact Table),一个是维度表(Dimension Table)。

事实表

事实表一般由两部分组成,维度(Dimension)和度量(Measurement)。

事实表可以通俗的理解为「什么人在什么时间做了什么事」的事实记录或者场景上下文,拥有最大的数据量,它是业务流程的核心体现。比如订单的场景:

张三于2017年5月6日在亚马逊花费1w元买了某款笔记本电脑

记录了张三购买笔记本电脑的场景,主人公是张三,购买的商品是笔记本电脑,发生的时间是2017年5月6日。

而度量则是可量化的,比如这里的1w元。度量在统计分析是一般会参与求和、求平均之类的运算。

以本文的订单流程为例,一个简化的事实表可以设计成这样:

1
2
3
4
5
6
7
8
9
10
11
12
create table `f_orders` (
user_id int not null default 0, # 用户ID
product_id int not null default 0, # 商品ID
address_id int not null default 0, # 地址ID
date_id int not null default 0, # 日期ID
promotion_id int not null default 0, # 促销ID
sales float not null, # 商铺订单价格
primary key (
`user_id`, `product_id`, `address_id`,
`date_id`, `promotion_id`
)
);

在这个表中,其主键为一个联合主键,由各个维度的外键组成,外键不能为空值。事实表一般不包含非数字类型字段,虽然数据量大,但占用的空间并不大,保证更高的查询效率。

维度表

维度表用于对事实表的补充说明,描述和还原事实发生时的场景。

如本文订单的例子,我们定义了用户、商品、地址、时间、促销5个维度,通过这5个维度还原订单发生时的场景,什么人在什么时间在什么地方购买了什么商品,以及购买该商品的促销方式。

对于每一个维度而言,都有若干个属性来描述,比如用户有性别、年龄、所在地等信息。这些维度的属性就是之后数据统计的依据,比如我们可以统计不同性别,不同年龄,不同地区在订单中的差异,从向用户制定更精细的营销策略。

事实表的粒度

在事实表选取的时候,我们需要选择最细粒度的操作数据作为事实表。因为其他所有的统计数据都可以根据这样的事实表通过统计计算生成,也只有使用最细粒度数据作为事实表,数据仓库才具有最大的灵活性和可扩展性,满足未知的查询统计需求,从而做到以不变应万变。

维度的设计

反范式设计

在关系型数据库三范式(3NF)设计中,我们极力避免数据的冗余,达到数据的高度一致性。但在数据仓库中,这样的设计并不是最佳实践,它反而让系统复杂不已,不利于理解和维护。

所以在维度建模中,维度表一般采取反范式的设计,在一张维度表中扁平化的存储维度的属性,尽量避免使用外键。

比如商品(d_products)这张维度表,在三范式的设计中,一般商品的分类作为单独的分类表而存在,然后在商品表使用外键关联。

但维度建模一般直接将分类放在商品维度表上,比如这样:

1
2
3
4
5
6
7
create table `d_products` (
id int not null, # 主键
category1 varchar(255) not null, # 一级分类
category2 varchar(255) not null, # 二级分类
name varchar(255) not null, # 商品名称
primary key(`id`)
);

这里直接使用 category1 和 cateory2 存储商品的一级和二级分类。

日期维度

日期维度是所有事实表都不可或缺的一个维度,日期维度将原本的一个简单时间戳进行多方位的分解,比如按年、按月、按周等,让统计分析更灵活高效。

比如说,这是一张最简单的日期维度表:

1
2
3
4
5
6
7
8
create table `d_dates` (
id int not null, # 主键,一般存储形式是 YYMMDD,如20170506
year int not null, # 年
mouth int not null, # 月
week int not null, # 周
day int not null, # 日
primary key(`id`)
);

通过与日期维度的联合查询,我们可以方便进行按年、按月、按周和按天的统计分析。

空值的处理

在业务系统中,如果相关联的信息为空,我们一般采用 0 或者 null 表示,但在数据仓库中,这并不是一个好的方式,因为会让查询的编写变得困难,尤其在各种条件相互组合的时候,很难保证结果的正确性。

比如本文订单场景的促销维度(promotion_id),大部分商品可能都没有关联的促销信息。在数据仓库中,我们的处理方式是在促销维度表中添加一行特殊的记录,表示没有促销。然后事实表的 promotion_id 外键则指向这条记录。然后通过如下的方式查询:

1
2
3
4
5
6
select 
count(*) as 无促销总数
from f_orders as order
inner join d_promotions as promotion
on promotion.id=order.promotion_id
where promotion.type='None'

还能方便的组合其他维度,实现更多样化的统计查询需求。

统计查询与分析

进行了纬度建模之后,我们就可以通过 SQL 就行统计查询分析了。

对于基于关系型数据库的数据仓库系统,由于反范式的应用,事实表趋于扁平,只需要掌握简单的 SQL 联合与聚合查询,变成完成很大一部分分析需求。比如要查询「获取2017年 Computer 分类每个月的销售量和销售额」,我们可以通过如下的 SQL 完成:

1
2
3
4
5
6
7
8
9
10
11
select 
date.mouth as 月份,
count(*) as 销售量,
sum(order.sale) as 销售额
from f_order as order
inner join d_date as date
on date.id=order.date_id
inner join d_product as product
on product.id=order.product_id
where date.year=2017 and product.category1='Computer'
group by date.mouth

这样的 SQL 很简单,稍加修改便实现成各种各样的统计分析需求,而且对编写着要求不高,只需要其理解业务流程的各个维度就可以了,无需了解原始数据结构,降低了统计分析的难度,不需要专业开发人员参与。

经过维度建模后形成事实表与维度表已经高度标准化,我们还可以更进一步开发元数据系统和BI 报表系统,让一些常规的统计分析工作可配置化,从而无需开发人员参与。