数据仓库101
数据仓库
按照 W. H. Inmon,一位数据仓库系统构造方面的大神的说法,“数据仓库是一个面向主题的、集成的、时变的、非易失的数据集合,支持管理决策制定”。数据仓库的目的是构建面向分析的集成化数据环境，为企业提供决策支持。
除供企业内部使用外，像Google Analytics和淘宝数据魔方等提供继承数据和多维分析的应用也属于数据仓库类型。
事实和度量
事实（Fact）是可以被量化的最详细的业务信息。例如：一次网站的点击；购物网站上一个客户的一次购买行为；Github上一个程序员的一次提交。我们可以量化下面的数据：
- 网站点击：点击次数、停留时间。
- 网上购物：销售额、利润、购买产品数量。
- 提交代码：commit数量、修改代码行数、删除代码行数。
- 这些可以量化的属性，如点击数、利润、提交数量，我们称之为度量（measures），是我们关注的业务过程的衡量指标。
在数据仓库中，我们更关心的是汇总后的度量，例如：网站3月的点击量是多少；05年销售额是多少；本周commit数量是多少。
维度
维度（Dimension）是指分析的各个角度。例如我们希望按照时间,或者按照地区,或者按照产品进行分析,那么这里的时间、地区、产品就是相应的维度。基于不同的维度,我们可以看到各量度的汇总情况,也可以基于所有的维度进行交叉分析。
维度层次
我们可能已经非常熟悉Web网站的面包屑导航和层级菜单了。当你在某个类目浏览的时候，接下来更可能要去浏览当前类目的子类或回退到你来的位置。与此类似，我们在分析的时候按年聚合之后可能更想要分析按月聚合的数据；分析了国家的数据之后想要分析各个省的数据。也就是说，维度是有层次(Hierarchies)关系的。
+--------+ +--------+ +----------+
| Canada +---> | Quebec +--> | Montreal |
+--------+ +--------+ +----------+
下钻（Drill-down）
在维的不同层次间的变化，从上层降到下一层，或者说是将汇总数据拆分到更细节的数据，比如通过对2010年第二季度的总销售数据进行钻取来查看2010年第二季度4、5、6每个月的消费数据，如下图；当然也可以钻取Quebec省来查看Montreal、Lachine等城市的销售数据。
^
|
| +----------------+
| | 190 |
| | |
| | |
+--------+----------------+---------->
Q2
+
|
|
|
v
^ +---+
| | 80|
| | | +---+
| +---+ | | | 70|
| | 40| | | | |
| | | | | | |
+------+---+----+---+----+---+------->
M4 M5 M6
上卷（Roll-up）
下钻的逆操作，即从细粒度数据向高层的聚合，如将Montreal、Lachine等的销售数据进行汇总来查看Quebec地区的销售数据。
切片（Slice）
选择维中特定的值进行分析，比如只选择电子产品的销售数据，或者2010年第二季度的数据。
切块（Dice）
选择维度中特定区间的数据或者某批特定值进行分析，比如选择2010年第一季度到2010年第二季度的销售数据，或者是电子产品和日用品的销售数据。
OLTP vs OLAP
分析型系统和操作型系统具有完全不同的目的。操作型系统支持业务的执行过程，而分析型系统支持对业务过程的评价。因此，指导种系统的设计原则也不同。
操作型系统直接支持业务过程的执行。它通过获取业务的事件和细节来构建业务的活动记录。例如，销售系统的订单、发货、支付等；人力系统的雇员雇佣和升迁信息；代码托管系统的commit和pull request信息等。由这些系统记录的活动通常成为事务，而这类系统本身通常成为联机事务处理（OLTP）系统，或简称为事务系统。
操作型系统关注执行过程，因此在事情发生改变时可能需要更新相关数据，并在数据操作有效期结束后清除或归档数据。例如，当一个客户地址发生变动时，地址数据被简单的重写了。
在关系数据库设计领域，广泛被认可的最佳操作型系统模式设计方法是第三范式。
与操作型系统关注业务执行过程不同，分析型系统主要支持对业务过程的评价。
- 本月订单趋势与上个月相比有何不同？
- 与本季度的目标相比，这种趋势说明什么问题？
- 某一个营销策略对销售有何影响?
- 谁是我们的最佳客户？
这些问题涉及到对整个订单流程的度量，无法从单个订单中获得答案。
在分析系统中，不需要创建或修改信息。在操作型系统中不再使用的历史数据对分析型系统来说仍然很重要，这一点之后要提到的缓慢变化维度问题中会有更详细的解释。
下面表格是对OLTP和OLAP主要差别的总结：
| 操作型系统 OLTP | 分析型系统 OLAP | |
|---|---|---|
| 设计优化 | 更新并发性 | 高性能查询 |
| 主要交互类型 | 插入、更新、查询、删除 | 查询 |
| 目的 | 执行业务过程 | 度量业务过程 |
| 交互范围 | 单个事务 | 聚合事务 |
| 设计原则 | 基于第三范式（3NF）的实体-关系（ER）设计 | 维度设计（星型模型） |
| 时间关注 | 当前的 | 当前的和历史的 |
星型模式
针对关系型数据库的维度设计被称为星型模式。相关的维度组合成维度表中的列，事实则存储在事实表的各个列中。星型模式的这个称谓来自于其表现形式：当与置于中心位置的事实表连线时，整个模式看起来像星状物，如图：
在星型模型基础上，将维度表做规范化设计，又衍生出了雪花模型，如图：
下面拿一个简单的订单系统举例，来看一下维度和事实表的设计：
# 地址维度表(adderss_dimension)
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| 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年销量最好的前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可以发现，设计成星型模式之后，几乎所有多维分析报表问题都可以通过上面一种查询方式得到答案。
Why ETL
星型模型让分析查询变得容易，但存在以下问题：
- 一般先有OLTP系统，后有OLAP系统，分析系统只是辅助工具。
- 我们的事务系统不是按星型模型设计的，数据来源是OLTP类型应用。
- 数据源除了OLTP系统的数据，还需要与其他来源数据进行组合。
所以，需要引入一套ETL流程来把OLTP数据转化为星型模型，然后才能方便分析。
+------------------+ +-----------+ +-----------+ +---------+
|OLTP Data +--------->+ | | | | |
+------------------+ | | | | | |
| +----> | +----> | |
+------------------+ | Extract | | | | |
|Google Analytics +--------->+ | | | | |
+------------------+ | | | | |Analytics|
| Transform | |Star Schema| | && |
+------------------+ | | | | |Reporting|
|CRM Data +--------->+ | | | | |
+------------------+ | Load | | | | |
| +----> | +----> | |
+------------------+ | | | | | |
|Others +--------->+ | | | | |
+------------------+ +-----------+ +-----------+ +---------+
那么，ETL是什么？
- Extract data from a source
- Transform the data for storing it in proper format or structure for querying and analysis purpose
- Load it into the target
其实ETL工作我们并不陌生，正像 thbar 所说的 Rubyists — Are you doing ETL unknowingly?,下面这些都属于ETL范畴：
- Write a script to migrate a legacy database to a new schema.
- Automate processing of your data to generate a report.
- Synchronize all or part of the data between 2 systems on a regular basis.
- Prepare your data for indexing/searching.
- Aggregate heterogeneous data sources into one consistent database.
- Clean-up dirty or bogus data.
- Geocode rows in an app to present them through a map app.
- Implement a data export process for your users.
ETL Tools
和 Ruby 相关的ETL开源工具只有以下几个：
- 与AR结合紧密的activewarehouse-etl：https://github.com/activewarehouse/activewarehouse-etl
- Square出品的轻量级ETL工具：https://github.com/square/ETL
- activewarehouse-etl维护者thbar新作：https://github.com/thbar/kiba
值得说明一下的是，由于ActiveRecord完全是针对OLTP场景设计的ORM工具，我们用AR来导数据一定会觉得巨慢，即使做了各种优化手段。这不是AR的错，是你没选对工具。ETL工具针对这种场景做了优化，一般都是批量加载数据，速度可以说是有了质的提升。可以参考之前写过的Fastest way to load data in MySQL。
如果想了解Activewarehosue ETL，这里有一个专门介绍Activewarehouse ETL的slide也值得一看：https://speakerdeck.com/thbar/transforming-data-with-ruby-and-activewarehouse-et
Incremental Update & MySQL stream
针对大数据场景，Activewarehouse ETL会提供增量更新和流读取的方式，只需要简单配置：
source :in, {
:type => :database,
:target => :operational_database
:table => "people",
:join => "addresses on people.address_id = addresses.id",
:select => "people.email, addresses.city, addresses.state, people.created_at"
:conditions => "people.unsubscribed = 0 AND people.date_of_death IS NULL"
:order => "people.created_at",
:new_records_only => "people.updated_at", # 增量导入
:mysqlstream => true # 按流的方式
},
[
:email,
:city,
:state
]
处理缓慢变化维度
维度建模的数据仓库中，有一个概念叫Slowly Changing Dimensions，中文一般翻译成“缓慢变化维”，经常被简写为SCD。缓慢变化维的提出是因为在现实世界中，维度的属性并不是静态的，它会随着时间的流失发生缓慢的变化。这种随时间发生变化的维度我们一般称之为缓慢变化维，并且把处理维度表的历史变化信息的问题称为处理缓慢变化维的问题，有时也简称为处理SCD的问题。常见类型有两种（Wikipedia里面介绍了6种）：
类型1 (Type 1): 覆盖旧记录。
有些 Dimension 表从业务上讲不需要保存历史记 录或者只需要对原有记录进行修改。比如说 Customer 表中有 Customer 地址的属性,原有的地址输入错误我们需要修改这个属性而 不需要对原有的错误地址进行保存,这个时候 就可以使用 SCD Type 1
类型2 (Type 2): 增加新记录。
Type 2 是精确 捕获Dimension 表历史变化的一种标准的方法,它通过对数据源表的Change Data Capture (CDC) 机制来捕获数据源的变化,然后在 Dimension 表中插入一个新的记录再使旧的相应的记录时效。
缓慢变化维度一般设计成这样的结构：
# customer dimension
| id | int(11) | NO | PRI | NULL | auto_increment |
|---|---|---|---|---|---|
| customer_key | int(11) | YES | MUL | NULL| |
| customer_type | int(11) | YES | MUL | 1 | |
| effective_start | datetime | YES | | NULL | |
| effective_end | datetime | YES | | NULL | |
| latest_version | tinyint(1) | YES | | 1 | |
Activewarehouse ETL 对SCD问题也提供了解决方案。
## 案例
https://github.com/hooopo/etltest
上面是一个基于Activewarehouse ETL和Activewarehouse的Rails项目，演示从ETL到分析报表展示功能如何实现。 最后，如果你了解了ETL的概念，以后看什么都是ETL了...