分享 Data Warehouse Schema Design

hooopo for Shopper+ · 2015年04月13日 · 最后由 yqwoe 回复于 2018年04月22日 · 8115 次阅读

本帖已被管理员设置为精华贴

之前介绍了数据仓库相关的一些基本概念：维度、事实、维度层次、上钻、下钻等。这篇主要来说一下数据仓库系统如何建模。

OLTP vs OLAP

分析型系统和操作型系统具有完全不同的目的。操作型系统支持业务的执行过程，而分析型系统支持对业务过程的评价。因此，指导种系统的设计原则也不同。

操作型系统直接支持业务过程的执行。它通过获取业务的事件和细节来构建业务的活动记录。例如，销售系统的订单、发货、支付等；人力系统的雇员雇佣和升迁信息；代码托管系统的 commit 和 pull request 信息等。由这些系统记录的活动通常成为事务，而这类系统本身通常成为联机事务处理（OLTP）系统，或简称为事务系统。

操作型系统关注执行过程，因此在事情发生改变时可能需要更新相关数据，并在数据操作有效期结束后清除或归档数据。例如，当一个客户地址发生变动时，地址数据被简单的重写了。

在关系数据库设计领域，广泛被认可的最佳操作型系统模式设计方法是第三范式。

与操作型系统关注业务执行过程不同，分析型系统主要支持对业务过程的评价。

本月订单趋势与上个月相比有何不同？
与本季度的目标相比，这种趋势说明什么问题？
某一个营销策略对销售有何影响？
谁是我们的最佳客户？

这些问题涉及到对整个订单流程的度量，无法从单个订单中获得答案。

在分析系统中，不需要创建或修改信息。在操作型系统中不再使用的历史数据对分析型系统来说仍然很重要，这一点之后要提到的缓慢变化维度问题中会有更详细的解释。

下面表格是对 OLTP 和 OLAP 主要差别的总结：

	操作型系统	分析型系统
目的	执行业务过程	度量业务过程
交互类型	插入、更新、查询、删除	查询
交互范围	单个事务	聚合事务
时间关注	当前的	当前的和历史的
设计优化	更新并发性	高性能查询
设计原则	基于第三范式（3NF）的实体 - 关系（ER）设计	维度设计（星型模型）

星型模式（Star Schema）

针对关系型数据库的维度设计被称为星型模式。相关的维度组合成维度表中的列，事实则存储在事实表的各个列中。星型模式的这个称谓来自于其表现形式：当与置于中心位置的事实表连线时，整个模式看起来像星状物，如图：

star schema

在星型模型基础上，将维度表做规范化设计，又衍生出了雪花模型，如图：

flake schema

下面拿一个简单的订单系统举例，来看一下维度和事实表的设计：

# 地址维度表(adderss_dimension)
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| country | varchar(255) | YES  | MUL | NULL    |                |
| state   | varchar(255) | YES  | MUL | NULL    |                |
| city    | varchar(255) | YES  | MUL | NULL    |                |
+---------+--------------+------+-----+---------+----------------+

# 发货方式维度表(shipping_method_dimension)：
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | YES  | MUL | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

# 订单来源维度表（source_dimension）:
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | YES  | MUL | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

# 日期维度表(date_dimension)：
+-------------------------------+--------------+------+-----+---------+----------------+
| Field                         | Type         | Null | Key | Default | Extra          |
+-------------------------------+--------------+------+-----+---------+----------------+
| id                            | int(11)      | NO   | PRI | NULL    | auto_increment |
| date                          | varchar(255) | YES  | MUL | NULL    |                |
| full_date_description         | text         | YES  |     | NULL    |                |
| calendar_week                 | varchar(255) | YES  | MUL | NULL    |                |
| calendar_week_number_in_year  | int(11)      | YES  | MUL | NULL    |                |
| calendar_month_name           | varchar(255) | YES  | MUL | NULL    |                |
| calendar_month_number_in_year | int(11)      | YES  | MUL | NULL    |                |
| calendar_year_month           | varchar(255) | YES  | MUL | NULL    |                |
| calendar_quarter              | varchar(255) | YES  | MUL | NULL    |                |
| calendar_year_quarter         | varchar(255) | YES  | MUL | NULL    |                |
| calendar_year                 | varchar(255) | YES  | MUL | NULL    |                |
| sql_date_stamp                | date         | YES  | MUL | NULL    |                |
+-------------------------------+--------------+------+-----+---------+----------------+

# 订单事实表(order_facts)：
+--------------------+--------------+------+-----+---------+----------------+
| Field              | Type         | Null | Key | Default | Extra          |
+--------------------+--------------+------+-----+---------+----------------+
| id                 | int(11)      | NO   | PRI | NULL    | auto_increment |
| date_id            | int(11)      | YES  | MUL | NULL    |                |
| shipping_method_id | int(11)      | YES  | MUL | NULL    |                |
| customer_id        | int(11)      | YES  | MUL | NULL    |                |
| payment_method_id  | int(11)      | YES  | MUL | NULL    |                |
| zip_id             | int(11)      | YES  | MUL | NULL    |                |
| address_id         | int(11)      | YES  | MUL | NULL    |                |
| source_id          | int(11)      | YES  | MUL | NULL    |                |
| tax                | decimal(8,2) | YES  |     | 0.00    |                |
| shipping_cost      | decimal(8,2) | YES  |     | 0.00    |                |
| total              | decimal(8,2) | YES  |     | 0.00    |                |
| sub_total          | decimal(8,2) | YES  |     | 0.00    |                |
| refund_amount      | decimal(8,2) | YES  |     | 0.00    |                |
| coupon_discount    | decimal(8,2) | YES  |     | 0.00    |                |
| gross_profit       | decimal(8,2) | YES  |     | 0.00    |                |
+--------------------+--------------+------+-----+---------+----------------+

最终的星型结构是这样：

                                                            
 +---------------+                                 +------+ 
 |        address+----------+      +---------------+date  | 
 +---------------+        +-v------v--+            +------+ 
                          |order facts|                     
 +---------------+        +-^------^--+            +------+ 
 |shipping method+----------+      +---------------+source| 
 +---------------+                                 +------+

这里比较特殊的是时间维度表，时间维度在星型模型里是一个通用维度，粒度只精确到天，这也是由分析型系统对实时性要求不高的特性决定的。其中地址也有做类似处理，在 ETL 过程中将街道等细节信息去掉，只精确到了城市级别。

星型模式的查询

数据库结构已经设计完毕，我们来做一些简单的查询，比如，想知道 2013 年 11 月销量最好的前 10 个城市是什么？

SELECT address_dimension.country, address_dimension.state, address_dimension.city, SUM(total) as sum_total, date_dimension. calendar_year_month
FROM `order_facts`
INNER JOIN `date_dimension` ON `date_dimension`.`id` = `order_facts`.`date_id`
INNER JOIN `address_dimension` ON `address_dimension`.`id` = `order_facts`. `address_id`
WHERE calendar_year_month = '2013-11'
GROUP BY country, state, city, calendar_year_month
ORDER by sum_total DESC
LIMIT 10

结果：

+---------+------------------+-------------+-------------+---------------------+
| country | state            | city        | sum_total   | calendar_year_month |
+---------+------------------+-------------+-------------+---------------------+
| Canada  | Ontario          | Toronto     | 23914512.00 | 2013-11             |
| Canada  | Quebec           | Montreal    | 22403280.00 | 2013-11             |
| Canada  | Alberta          | Calgary     | 13671801.00 | 2013-11             |
| Canada  | British Columbia | Vancouver   |  9201099.00 | 2013-11             |
| Canada  | Ontario          | Ottawa      |  8771859.00 | 2013-11             |
| Canada  | Ontario          | Mississauga |  8627691.00 | 2013-11             |
| Canada  | Manitoba         | Winnipeg    |  8308380.00 | 2013-11             |
| Canada  | Alberta          | Edmonton    |  7773006.00 | 2013-11             |
| Canada  | British Columbia | Richmond    |  6602364.00 | 2013-11             |
| Canada  | Quebec           | Lachine     |  5624025.00 | 2013-11             |
+---------+------------------+-------------+-------------+---------------------+

从上面的 SQL 可以发现，设计成星型模式之后，几乎所有多维分析报表问题都可以通过上面一种查询方式得到答案。

via:http://shopperplus.github.io/blog/2015/04/12/data-warehouse-schema-desgin.html

39 个赞

Data Warehouse Concepts and Overview

ETL with Ruby

billy #1 2015年04月13日

长知识了。多谢！

2 楼已删除

kayakjiang #3 2015年04月13日

很有意思，我们现在的电商系统里很多上百行的统计 sql，可以用这种设计简化下

hooopo #4 2015年04月13日

#3 楼 @kayakjiang 直接在 OLTP 系统上做统计既复杂又效率低，因为事务系统设计原则是 3NF，表的粒度小。而 OLAP 系统一般面向主题，直接在 OLTP 上分析就需要各种联表。

我们现在的做法是分析系统单独抽出一个项目，通过 ETL 把 OLTP 系统的数据转化成面向主题的结构，ETL 作用在从库上，即使很耗时的查询也不影响业务系统的运行。

luikore #5 2015年04月13日

感觉星形表和直接扔到 influxdb 差不多... 还没 influxdb 的实时性好

hooopo #6 2015年04月13日

#5 楼 @luikore influxdb 是专为这种场景设计的，当然比 RDBMS 合适.. 还有一些列存储数据库 infobright、redshift 等查询更快一些，但当普通数据库用的时候会缺失一些功能。据说 postgresql 有 bitmap 索引，对这种查询也更快一些。

quakewang #7 2015年04月13日

你们有使用 Mondrian 这种 OLAP engine 吗，用 MDX 来查询，还是直接用 RDBMS 和 sql 来处理？

1 个赞

hooopo #8 2015年04月13日

#7 楼 @quakewang https://github.com/rsim/mondrian-olap 这种吗？看过一下，JRuby 的就没用，直接 MySQL 处理的。数据量不大，每天跑一次 ETL。

quakewang #9 2015年04月13日

#8 楼 @hooopo 明白了。文章写得很棒。

Mondrian 也可以不用那个 jruby gem 的，直接用他的集成环境就可以了。时间纬度没有到分、秒的，确实直接 sql 就够了。

1 个赞

merlinran #10 2015年04月13日

activewarehouse看起来不错的样子。正在选型的孩子表示很开心。

rubyu2 #11 2015年04月13日

:plus1:

crosspass #12 2015年04月15日

涨姿势了，很有意思。

xiaoronglv #13 2015年04月16日

@hooopo 的文章都好赞啊。

lgn21st #14 2015年04月16日

#13 楼 @xiaoronglv 基本上，看到 @hooopo 头像，就条件反射加精了。

1 个赞

redvoilin #15 2015年04月20日

不错，了解了不少

chagel #16 2015年04月28日

从 SQL Server 7 的 OLAP Services 玩到 MSAS 2k/2k5, Pentaho.. 满满都是泪啊..

ane #17 2015年08月19日

我这个 java 写多的人，不经一问，难道不应该是这样设计表结构的？

ane #18 2015年08月19日

不过确实，这样的设计，插入数据和查询数据是个麻烦的事情

zoowii #19 2015年11月07日

#18 楼 @ane 在线业务的数据库不是这样方式设计的，这是为了查询分析，数据经过了 ETL 过程整理。

hooopo 在统计平台思路分析提及了此话题。 06月14日 11:30

hooopo 在 ETL with Ruby 提及了此话题。 06月14日 11:30

22 楼已删除

yqwoe #23 2018年04月22日

@hooopo

大佬，请教一下关于 activewarehouse 的问题：

如果使用 activewarehouse 必须是 rails3 吗？
#8 图中你所展示的是 activewarehouse 帮你生成的页面吗？
如果使用 rails 做类似 superset、redash、metabase、国产 CBoard 数据可视化产品，有没有什么好的思路？
尝试了这几个开源工具，个人理解是这些工具是做了 OLAP 多维分析 + 数据集 + 更友好的图形可视化的工作。维护好数据集市，非技术数据分析人员通过拖拽配置的形式完成一张报表的展示，缩短了传统的报表开发->展现的工期。
刚接触这方面，不太有经验，表达能力也有限，请见谅。

需要登录后方可回复, 如果你还没有账号请注册新账号

39 个赞

共收到 19 条回复

打赏作者

收到新回复，点击立即加载