MySQL-数据仓库指南

MySQL数据仓库指南

、，一、.

前百

欢迎使用mysql多维数据仓库指南。

数据仓库可以通过集成各种多样的数据实现信息的统一，这些数据来源包括

现行的事务操作和管理信息系统，以及外部各种信息源。这些源数据将被整合,

清洗，转换，如果需要直接从这些数据来源读取数据的话，该数据将会在数据仓

库中以更有利于操作的方式存储。

数据仓库的数据构造方式使你可以存储当前和历史的数据。当前数据是实际

事务操作活动必要的，一般是定期的硬拷贝（比如打印出的报表）或者是联机报

表。而历史数据，往往不是那么容易利用，可以提供基于时间点分析的商业信息,

比如：追踪、推论分析，以及对比，这些对于长期计划和战略性的市场决策都是

很重要的。

如何从多个信息源整合信息，定期积聚存储，有效的需求设计和发展技术，

所有这些都和事务型管理信息系统所应用的技术有很大的不同。本书都是关于数

据仓库的设计和开发技术，它涵盖了大部分建立数据仓库所涉及到的大部分技

术问题。更为重要的是，本书提供了一个易懂的操作指南，便于开发一个实际的

数据仓库。

本书的适用范围

〃第〃多维投游仓库揩礴是一个实战的书籍。你将使用mysql数据库，但该

书不是关于mysql的。本书不涉及任何硬件架构方面的问题。

本书主要关心数据仓库的设计和开发技术。它不涉及开发项目的管理，理

论，以及如何领导开发等相关的技术问题。

本书用一个数据仓库开发的例子来展示技术如何应用。提供数据模型和sql

脚本，将能够适用于实际的数据仓库开发。这些脚本已经在Windowsxp

professionalsp2平台上用mysql5。0。21版本测试通过。

另外，本书不再对以下的几个主题进行特别的论述：

・数据仓库的概念

・Sql

MySQL数据库

适合阅读本书的人:

数据仓库应用于各种组织和商业机构，从政府部门，非盈利组织到学校，从

制造业到零售店，从金融机构到医疗机构，从传统公司到互联网商家。

本书首先面向数据仓库开发人员。但是，it经理和其他it职业，特别是那

些对mis（经营报告）和dss（决策支撑应用）感兴趣的人将发现本书同样有用。

总的说来，本书是给那些为分析应用准备数据工作相关的人，以及那些需要递交

信息的人，比如打印报表，联机报表。

本书同样适用于那些数据仓库初学者。它将直接快速的为那些正在准备开发

他们第一个数据仓库的人提供帮助。

教师和学生可以将该书作为教科书，以便于明确他们对于数据仓库原理,概

念的理解。大部分章节可以被定制用于实验练习。

预备的技能

本书不是面向it新人，为了更有效的利用本书，读者必须有一些系统开发

经验。但是，不要求先有数据仓库的建设经验。

需要实践操作本书例子的人需要有rdbms（关系数据库管理系统）和sql的

实际操作技能。

你可以从本书得到

你将可以仅仅用一个示例，一个最先存储商业销售相关数据的数据仓库，并

且进行实践操作，就可以磨练你的数据仓库知识和实际技能。该示例是一个精简

版的实际数据仓库，可以在很多种商业类型中找到它的原型。

你将基于mysql数据库用本书介绍的技术一步步的开发该示例中销售相关数

据仓库。这些技术是在通常数据仓库开发中将碰到的问题的分解技巧。通过完成

本文的学习并且完成所有的练习，你将获得相关工作经验并可以为负责第一个实

际数据仓库项目做好准备。

章节总览

本书包含25个章节和一个附录。所有章节组织为四个部分。第一部分涵盖

数据仓库基础。第二部分描述从原始数据到数据仓库的迁移。第三部分讨论如何

控制数据仓库的发展演化。第四部分涉及到一些高级的多维技术。接下来的部分

将给出每个章节的预览。

第一篇基本原理

第一篇，涵盖多维数据仓库的基础原理，有四个章节。

第1章，“基本组成”，介绍星型模式（一种数据库模式，有一个事实表，周围

围绕着多个维表）以及解释该模式的基本组成。

第2章，”维度历史“介绍用代理健来维护维成员的历史记录。

第3章，“度量可加性”，包含维度数据仓库中最重要的特性之一，也就是存储

在数据仓库事实表中的度量的可加性。

第4章，“维度查询”，介绍最适合应用于星型模式的一种sql查询，维度查询

可以用来证明一个维度数据仓库是否具备两个最基本的设计指标：简单和高效。

第二篇：提取、转换和加载

该篇的所有5个章节涉及了数据集成，事实表，维表。

第5章，“源数据提取”介绍了不同类型数据的提取。

第6章“导入时间维”涵盖了三种最通用的加载时间维的技术。

第7章“初始化导入“和第8章，“定期导入”涉及到两种类型的导入技术，

初始化和周期性。

第9章，“周期导入计划”，作为第二篇的总结，提供一个进阶式的指导，关于

如何利用Windows的任务管理器实现定期导入计划。

第三篇：成长

第三篇介绍了不同的处理技术，这些技术主要处理一个成功的多维数据仓库

成长过程中碰到的相关问题。该部分有十个章节。

第10章，“增加字段”讨论关于向现有数据仓库中的表增加一个字段的技

术问题。

第11章，“按需载入”涉及按需载入的技术。

第12章，“维表子集”，介绍相关技术以帮助用户处理维表子集问题。

第13章，“维度角色扮演”，关于在一个事实表中多次使用同一个维度的

问题。

第14章，“快照”让你在需要作出汇总数据的时候，能提供快速性能的查

询。

第15章，“唯度层次”和第16章“多路径和不齐整的维度层次”是关于简

单和多路径维度技术，相应的，这些技术帮助人们进行汇聚和钻取分析。

第17章，“维度退化”，展示了如何用维度退化技术去减少一个数据仓库

模式的复杂性。

第18章，“垃圾维”，是关于垃圾维的技术，即将表面上不相关的但是经

常会被用户进行要求分析的数据，用维的方式组织起来。

第19章，“多星型模式”展示了如何在模式中增加多个星型模式。

第四篇：高级技术

包含六个章节。

第20章，“不齐整数据源”，介绍如何在数据仓库中处理那些结构无法直

接映射到目标表的数据源。

第21章，“无事实的事实表”，帮助你为客户建立一种可供分析的辅助数

据，一种无事实字段的事实表，该数据无法从数据源直接取得度量。

第22章，“迟来的事实”，包含一个技术，用于处理当数据源中某个特殊

的事实没有在计划载入时间之前产生的情况。

第23章，“外部数据源和维度合并“，包括两个主题：处理外部数据源和

如何将多个维中分散的属性合并到一个维的技术。

第24章，“累积度量”，讨论两个相关的问题：计算度量和累积度量的不

可相加性。

第25章，“分段维度”，阐述一种技术，可以帮助使用者针对那些有连续

值的属性进行数据分析。

附录

附录a,“格式文件数据源”，介绍如何使用本书例子中的格式文件数据源。

源代码下载

可以从http:〃jtute.com.上面下载本书的示例的程序脚本。

DimensionalDataWarehousingwithMySQL:ATutorial

byDjoniDarmawikarta

BrainySoftwareCorp.2007(432pages)

ISBN:9780975212820

Computerprogrammerswhoneedtobuildadatawarehousewillfindrelevant

examplesandinformationwritteninathorough,easy-to-followstylein

thisstep-by-steptutorial.

封底BackCover

将Mysql这个最流行的开源数据库软件进行数据仓库应用，在本书之前还从

来没有被在创建多维数据仓库方面用这种入门指南的方式进行介绍。主题包括

星型模式建模，数据载入(数据的提取、转换和加载：etl),测试检验，以及

多维查询。本书由始至终应用一个可实践，相对精简的真实的项目。其全面，易

于接受的写作形式使得那些需要建立数据仓库的程序员可以得到相关的示例和

资料。

关于作者

DjoniDarmawikarta曾就职于ibm,现在是一个加拿大保险公司的数据仓库、

商业智能团队中的技术专家。目前生活在Toronto,Ontario。

第一篇基本原理

章节列表：

第1章：基本组成

第2章：维度历史

第3章：维度可加性

第4章：维度查询

本篇概述

你将运用关系数据库来实施一个维度数据仓库。事实表和维表这两种类

型的关系表构成了一个数据仓库模式的基本部分，在本书的第一部分，你将

用mysql数据库建立这些基本部分。

第1章:基本组成

概述

本章将了解两个重要的主题：星型模式和代理键。星型模式是一种维度数据

仓库的数据结构。代理键是在数据仓库中添加到事实表以作为主键的字段。

在本章你将开始一个关于建立真实数据仓库的漫长旅程。一些任务需要在本

章完成：

■建立一个数据库用户。

■建立两个关系数据库，一个作为数据仓库，另外一个作为源数据库。

■为数据仓库建立数据库表。

■产生代理键：

你需要建立一个源数据库是因为你的数据仓库还没有源数据。实际应用中这

一步并不是必要的，因为你的数据仓库很有可能是基于已有的数据源建立的。

你将在本书的第二篇开始使用这个源数据库。

第1节星型模式

一个好的维度数据仓库需要有简洁的数据结构。从技术角度看，一个简洁的

结构将意味着更快速的查询。在一个维度数据仓库，关系数据库的实施中有两种

类型的表，事实表和维度表。事实表包含商业事实数据（或者称为度量值）。维

度表包含查询该数据库的种类。

注意在看完本书前面两个章节后，你对事实表和维度表将有更好的理解。

这些表在数据仓库中的联系模式看起来象星星状，因此该模式的术语就称为

星型模式。

注意除了星型模式外，雪花模式也会用于数据仓库中。但是较之星型结构，

其建模更为困难。而且，雪花模式不容易理解和应用，而且其查询性能也低于星

型模式。这些缺陷是雪花模式不适合建立维度数据仓库，因此本书只是涉及星型

模式。

一个星型模式包含一个事实表，该事实表环绕着两个甚至更多的维表。单星

结构只有一个事实表，而多星结构则是每个星有多个的事实表,另外，维度表可

以为多个事实表所共享。本章只包括单星结构，读者将会在19章“多星模式”

中学习至u多星结构。

图1-1展示了一个单星结构的维度模式，这是一个我们将会在本书中扩展的

销售订单的数据仓库。

图1.1一个单星型的维度模式

事实表名称的后缀通常是fact,而dim（维度dimension的缩写）通常用于维

度表的后缀。根据这种命名规则，很明显，图1-1中有一个事实表（销售订单事

实表sales_order_fact）和四个维度表（客户维customejdim,订单维order_dim,

产品维product_dim,以及日期维date_dim）。事实袤包含一个或者多个可测

的事实值（一个可以测的事实值简称为度量），而维表则对度量进行分类。

每个维表有且只有一个代理键字段，代理键字段名称的后缀是sk。维表中的

每个代理键字段在事实表中都会有对应的相同的字段，这样使得数据库的查询更

为容易。但是，事实表中以sk为后缀的字段并不涯代表一个代理键字段。

图1-1中连接事实表sales_order_fact和四个维表的线指明了查询这些表的连

接关系。这些连接是基于维表的代理键字段的。

当建立一个维度数据仓库的时候，你将会在数据仓库中自己产生代理键的值,

这些代理键值不是从源数据得到的。代理键的值是连续的数值。

注意后面关于“代理键”的章节将对代理键进行详细的说明。

现在你已经知道了星型模式，事实表，维表的概念，我们来看一个例子，如

果我们对订单的订单金额感兴趣，并且决定用图1-1中sales_order_fact表的

ordejamount字段作为度量。表1-1显示sales_order_fact表的一个抽样记录。

蓑1-1：一个事实表的抽样

将数据用电子表格形式展现：

customer_skproduct_skdate_skorder_skorder_amount

11111000

君T-l所关联的维袤中相应的数据Q录痛在袤1-2到1-5中展示。

表1-2:客户维表中关联的行

customerskcustomernocustomername

customer_skcustomer_nocustomer_name

11DonsLimited

表1-3:产品维表中关联的行

product_skproduct_codeproduct_name

11CangcungHardDisk

表1-4:时间维表中关联的行

skdate

12007-02-01

表1-5:订单维表中关联的行

order_skorder_number

11

事实表的行说明该订单的金额是$1,000。这是事实的度量。sales_order_fact

表中的customer_sk字段值为1表示对应customer_dim维表中customer_sk字

段值为1的行。这个关联表示，提交这个订单的是另为DonsLimited的客不。用

事实表中的product_sk字段的值可以在产品维表中追踪到产品信息。通过将事

实表和date.dim时间表的date_sk字段进行关联，你可以得到订单日期。同样，

通过将事实表和order_dim订单维表中的order_sk字段进行关联，你可以得到

订单号。

第2节代理键

维表中的代理键字段是维表的主键。代理键的值通常是连续的数值而不代表

任何商业意义。相比之下，许多来自源数据的键值都有商业含义。

你将在你的数据仓库内部产生代理键值；你不可以从源数据得到代理键值,

在第2章“维度历史”中我将说明这么做的目的。

在mysql中，你可以通过设置代理键字段的AUTOINCREMENT自增属性

产生代理值。通过将自增字段用null值代替来插入一个递增的整数。

已经有足够的原理让我们可以开始建立我们的数据仓库了。接下来的这节，

“任务”阐述了你需要在本章完成的任务的详细步骤。

第3节任务

有四个任务需要在本章完成：

1.建立一个数据库用户。

2.建立两个关系数据库，一个作为数据仓库，另外一个作为源数据库。

3.为数据仓库建立数据库表。

4.产生代理键。

每个任务将在独立的小节中说明。

建立一个数据库用户id

第一步产生一个数据库用户，你可以用该帐户进行操作数据仓库和数据源。

在你开始前，确信你已经将本书随附的脚本文件放置在mysql的安装目录下面。

例如，我的安装目录是C:\mysql,所以我将我的脚本文件放在C:\mysql\scripts目

录中。

我们开始将用root用户登陆mysql,请输入下面的命令：

C:\>mysql-uroot-p

你将被提示输入密码：

Enterpassword:********

输入密码后，将看到控制台的欢迎信息：

WelcometotheMySQLmonitor.Commandsendwith;or\g.

YourMySQLconnectionidis6toserverversion:5.0.21-community-nt

Type'help;"or'\h'forhelp.Type'\c'toclearthebuffer.

mysql>

敲入密码后的这些信息是当一个root用户登陆mysql时典型的回应信息。列表

1-1所示的脚本create_user_id.sql产生一个用户iddwid其密码是pw。

列表1-1:创建dwid用户id

*/

/*create_user_id.sql*/

/**/

GRANTALLON*.*TOdwid@localhostIDENTIFIEDBY'pw';

/*endofscript

用下面的命令方式运行该脚本：

mysql>\.c:\mysql\scripts\create_user_id.sql

回车后，你将看到回应的信息：

QueryOK,0rowsaffected(0.03sec)

用showgrants命令，你可以确认用户id：dwid是否已经被成功创建，你应该

在命令中包含你的mysql服务器名称：

mysql>showgrantsfordwid@localhost;

假如存在用户dwid,你可以看到如下信息：

Grantsfordwid@localhost

GRANTALLPRIVILEGESON*.*TO'dwid'@'localhost'IDENTIFIEDBY

PASSWORD'*D821809F681A40A6E379B50D0463EFAE20BDD122，

1rowinset(0.00sec)

注意密码部分在你的显示屏上显示的方式将不同于上面的描述。

现在你需要退出mysq然后用dwid用户登陆，退出mysql用exit命令：

mysql>exit

为了以dwid身份登陆mysql,可以用下面的命令：

c:\>mysql-udwid-p

然后输入dwid的密码，记住，密码是pw。

建立数据库

有两个数据库需要建立，源数据(source)和数据仓库(dw)□Source数据

库存储你的数据，也就是这些数据将成为你的数据仓库的源数据。Dw数据库是

为了作为数据仓库建立的。

可以用列表1-2中的create_databases.sql脚本进行创建数据库。

列表1.2:建立dw和source数据库

/**/

/*create_databases.sql*/

/**/

CREATEDATABASEdw

J

CREATEDATABASEsource

»

运行create_databases.sql脚本的命令如下：

mysql>\.c:\mysql\scripts\create_databases.sql

在你的控制台界面上，你将看到：

QueryOK,1rowaffected(0.00sec)

QueryOK,1rowaffected(0.00sec)

你可以用showdatabases命令来确认上面的命令是否成功运行。为了确认dw

数据库被创建用下面命令：

mysql>showdatabaseslike'dw';

回应信息将如下面所示：

++

|Database(dw)

++

|dw|

++

1rowinset(0.00sec)

为了确认source数据库被成功创建用下面命令：

mysql>showdatabaseslike'source';

回应信息将如下面所示：

++

Database(source)

++

Isource

++

1rowinset(0.00sec)

创建数据仓库表

第二步是在dw数据库中创建数据仓库表。你可以用列表1-3所示的

create_dw_tables.sql脚本来创建图1-1中的sales_order_fact表和四个维表。

列表1-3:而建数据仓库表

/**/

/*create_dw_tables.sql*/

/**/

/*defaulttodwdatabase*/

USEdw;

/*creatingcustomer_dimtable*/

CREATETABLEcustomer_dim

(customer_skINTNOTNULLAUTO_INCREMENTPRIMARYKEY

,customer_numberINT

,customer_nameCHAR(50)

,customer_street_addressCHAR(50)

,customer_zip_codeINT(5)

,customer_cityCHAR(30)

,customer_stateCHAR(2)

,effective_dateDATE

,expiry_dateDATE)

/*creatingproduct_dimtable*/

CREATETABLEproduct_dim

(product_skINTNOTNULLAUTO_INCREMENTPRIMARYKEY

,product_codeINT

,product_nameCHAR(30)

,product_categoryCHAR(30)

,effective_dateDATE

,expiry_dateDATE)

/*creatingorder_dimtable

CREATETABLEorder_dim

(order_skINTNOTNULLAUTO_INCREMENTPRIMARYKEY

,order_numberINT

,effective_dateDATE

,expiry_dateDATE)

/*creatingdate_dimtable*/

CREATETABLEdate_dim

(date_skINTNOTNULLAUTO_INCREMENTPRIMARYKEY

,dateDATE

,month_nameCHAR(9)

,monthINT(1)

,quarterINT(1)

,yearINT(4)

,effective_dateDATE

,expiry_dateDATE)

/*creatingsales_order_fact_table

CREATETABLEsales_order_fact

(order_skINT

,customer_skINT

,product_skINT

,order_date_skINT

,order_amountDECIMAL(10,2))

现在运行create_dw_tables.sql脚本：

mysql>\.c:\mysql\scripts\create_dw_tables.sql

你的控制台将显示如下类似的信息：

Databasechanged

QueryOK,0rowsaffected(0.13sec)

QueryOK,0rowsaffected(0.12sec)

QueryOK,0rowsaffected(0.12sec)

QueryOK,0rowsaffected(0.10sec)

QueryOK,0rowsaffected(0.11sec)

你可以用showcreatetable命令确认某个表是否被创建成功，例如，为了验证

customerdim表是否创建成功，用这个命令：

mysql>showcreatetablecustomer_dim\G

在你的控制台界面上，将可以看到：

Table:customer_dim

CreateTable:CREATETABLE，customer_dim，(

Jcustomer_skint(11)NOTNULLauto_increment,

Jcustomer_numberint(11)defaultNULL,

'customer_name，char(50)defaultNULL,

'customer_street_address，char(50)defaultNULL,

Jcustomer_zip_code，int(5)defaultNULL,

，customer_city，char(30)defaultNULL,

'customer_state，char(2)defaultNULL,

，effective_date，datedefaultNULL,

'expiry_date，datedefaultNULL,

PRIMARYKEYCcustomer_sk，)

)ENGINE=InnoDBDEFAULTCHARSET=latinl

1rowinset(0.00sec)

用相同的方式，可以确认其他表是否成功创建。

产生代理键

本节的最后一个任务是用列表1-4所示的customer_sk.sql脚本创建代

理键，这个脚本将向customer_dim表插入3条记录。

列表1-4:产生客户代理键值

/**/

/*customer_sk.sql*/

/**/

/*defaulttodw

USEdw;

INSERTINTOcustomer_dim

(customer_sk

,customer_number

,customer_name

,customer_street_address

,customer_zip_code

,customer_city

,customer_state

,effective_date

,expiry_date)

VALUES

(NULL,1,,BigCustomers",'7500LouiseDr.','17050,,

'Mechanicsburg','PA',CURRENT_DATE,'9999-12-31))

,(NULL,2,'SmallStores*,'2500WoodlandSt.",17055',

(Pittsburgh',‘PA',CURRENT_DATE,(9999-12-31))

,(NULL,3,'MediumRetailers),'1111RitterRd.',"17055,

(Pittsburgh",'PA',CURRENT_DATE,(9999-12-31))

/*endofscript*/

运行customer_sk.sql脚本之前，你必须将你的mysql时间设置为：2007-02-01,

这是因为customer_sk.sql脚本中的CURRENT_DATE函数用操作系统的时间作为

载入effective_date字段的值,而我就是在这个时间运行customer_sk.sql脚

本的。你可以通过将mysql运行的机器的系统的时间修改为特定的府来修改

mysql时间。

注意要牢记，这里你改变mysql的时间只是为了能比较平滑结合这个学

习教程，实际的生产环境中，你不能在运行脚本的前后改变你的数据库或者系统

的时间。事实上，你应该在数据库仓库环境中安排部署你的各个脚本定期的运行。

如何定期安排将在第8节“定期载入”中讨论。

注意你的mysql监控端将在系统时间被修改后断开和mysql服务端的连

接，为了确保你运行脚本不会有什么问题，在调整时间后，试着运行一下诸如

“usedw”的简单的命令，你将得到一个错误的信息提示，但是你的监控端将重

新连接上mysql。你可以再次运行“usedw”命令来确定已经连接上myslq服务

器，这次，你不会再得到任何错误提示了。

现在，你己经将mysql的时间调整到2007-02-01,可以用如下的命令运

行列表1-4中所示的脚本，

mysql>\.c:\mysql\scripts\customer_sk.sql

你将在你的控制台上看到如下信息：

Databasechanged

QueryOK,3rowsaffected(0.06sec)

Records:3Duplicates:0Warnings:0

查询该表，你可以看到这个脚本己经准确的插入代理键值了。

mysql>select*fromcustomer_dim\G

customer_sk:1

customer_number:1

customer_name:BigCustomers

customer_street_address:7500LouiseDr.

customer_zip_code:17050

customer_city:Mechanicsburg

customer_state:PA

effective_date:2007-02-01

expiry_date:9999-12-31

customer_sk:2

customer_number:2

customer_name:SmallStores

customer_street_address:2500WoodlandSt.

customer_zipcode:17055

customer_city:Pittsburgh

customer_state:PA

effective_date:2007-02-01

expiry_date:9999-12-31

customer_sk:3

customer_number:3

customer_name:MediumRetailers

customer_street_address:illlRitterRd.

customer_zip_code:17055

customer_city:Pittsburgh

customer_state:PA

effective_date:2007-02-01

expiry_date:9999-12-31

3rowsinset(0.00sec)

mysql>

现在运行列表1-5中的脚本more_customer_sk.sq来增加更多的行。

列表15:插入更多客户资料

/**/

/*more_customer_sk.sql*/

/*

USEdw;

INSERTINTOcustomer_dim(

customer_sk

,customer_number

,customer_name

,customer_street_address

,customer_zip_code

,customer_city

,customer_state

,effective_date

,expiry_date

)

VALUES

(NULL,4,'GoodCompanies*,'9500ScottSt.','17050',

Mechanicsburg,'PA',CURRENT_DATE,'9999T2-31')

,(NULL,5,JWonderfulShops，,'3333RossmoyneRd.','17050',

'Mechanicsburg','PA',CURRENT_DATE,'9999-12-31')

,(NULL,6,'LoyalClients',*7070RitterRd/,'17055',

'Pittsburgh',‘PA',CURRENT_DATE,'9999-12-31')

下面说明如何运行more_customer_sk.sql脚本

mysql>\.c:\mysql\scripts\more_customer_sk.sql

你将看到：

Databasechanged

QueryOK,3rowsaffected(0.06sec)

Records:3Duplicates:0Warnings:0

如果你查询customer_dim表，将发现有6条记录在该表中：

mysql>select*fromcustomer_dim\G

customer_sk:1

customer_number:1

customer_name:BigCustomers

customer_street_address:7500LouiseDr.

customer_zipcode:17050

customer_city:Mechanicsburg

customer_state:PA

effective_date:2007-02-01

expiry_date:9999-12-31

customer_sk:2

customer_number:2

customer_name:SmallStores

customer_street_address:2500WoodlandSt.

customer_zip_code:17055

customercity:Pittsburgh

customer_state:PA

effective_date:2007-02-01

expiry_date:9999-12-31

customersk:3

customer_number:3

customer_name:MediumRetailers

customer_street_address:1111RitterRd.

customer_zipcode:17055

customer_city:Pittsburgh

customer_state:PA

effective_date:2007-02-01

expiry_date:9999-12-31

customer_sk:4

customer_number:4

customer_name:GoodCompanies

customer_street_address:9500ScottSt.

customer_zip_code:17050

customer_city:Meehanicsburg

customer_state:PA

effective_date:2007-02-01

expiry_date:9999-12-31

customer_sk:5

customer_number:5

customer_name:WonderfulShops

customer_street_address:3333RossmoyneRd,

customer_zip_code:17050

customercity:Mechanicsburg

customer_state:PA

effective_date:2007-02-01

expiry_date:9999-12-31

customer_sk:6

customer_number:6

customer_name:LoyalClients

customer_street_address:7070RitterRd.

customer_zip_code:17055

customer_city:Pittsburgh

customer_state:PA

effective_date:2007-02-01

expiry_date:9999-12-31

6rowsinset(0.01sec)

不要删除这些客户记录，在下个章节中，你将用到它们。

第4节小结

这一个节，你学习了星型模式和代理键。你还建立两个数据库以及操作这两个数据库

的mysql用户。下面的章节，你将使用和扩展这些数据库。

第二章维的历史记录

存储在维表中得数值叫维成员。比如第1章的product_dim维表就存储关于

产品的维成员。

很多维成员会随着时间而变化。客户改变地址，产品更名和重新规类，销售

订单修正，等等。当一个维成员发生改变时，比如产品建立一个新的类别，你将

必须维护维成员的历史记录。例如：一旦发生产品的重新归类，你必须维护该产

品维的历史记录，在这个例子中，你必须在product_dim维表中存储该产品的以

前的类别和现在新类别信息。另外，以往的订单记录中，产品类别将保持对应的

旧类别。

缓慢变化维(SCD)是在维度数据仓库中记录维历史的技术。本章将教你如何

用SCD技术维护维度的历史记录。你将学习不同的SCD脚本，并且用实验进行

验证这些脚本是如何正确地维护维历史记录的。

缓慢变化维技术

有三种不同的处理缓慢变化维的技术：第一类缓慢变化维(SCD1),第二

类缓慢变化维(SCD2),第三类缓慢变化维(SCD3)。SCD1更新维成员记录而

不存储维成员的历史记录。SCD1用于直接校正错误的维成员。

SCD2维护维历史记录，当一个维成员改变时，将产生一个新版本的维成员。

SCD2不删除或者改变现有的维成员记录。

SCD3只是保存一个版本的维成员记录。它使用多个字段来保存不同维成员

值的方法来维护维的历史记录。比如记录客户地址，customejdim维表就必须有

customer_address字段和previous_customer_address字段来分别存储前后两次的

地址信息。相比较SCD2维护全部历史记录而言，SCD3只能维护有限的历史记

录。SCD3很少被应用。它只能用在那种对数据库空间有限制的场合，并且数据

仓库的使用者可以接受有限的维成员历史记录。

注意本书只涉及SCD1和SCD2o

第一类缓慢变化维技术(SCD1)

如果你不需要维护维度变化历史，你可以用SCD1。当源数据改变，你更新

相应维表中现有的数据记录。

可以举例子：在第1章中，我就应用SCD1为customejdim表进行建表和载

入数据。回顾一下，该表有表2-1所示的6条记录。

表格2-1未发生改变的customer_dimtable表

customercustomer_namecustomer_streetcustomer_zipcustomer_citycustomer

_number_address_code_state

1BigCustomers7500LouiseDr.17050MechanicsburgPA

2SmallStores2500WoodlandSt.17055PittsburghPA

3Medium1111RitterRd.17055PittsburghPA

Retailers

4GoodCompanies9500ScottSt17050MechanicsburgPA

5Wonderful3333RossmoyneRd.17050MechanicsburgPA

Shops

6LoyalClients7070RitterRd.17055PittsburghPA

假设客户详细信息发生改变，现在源数据中，客户表的内容如表2-2所示:

表2-2修正后的客户信息

customercustomer_namecustomer_streetcustomer_zipcustomer_citycustomer

_number__address_code_state

1ReallyLarge7500LouiseDr.17050MechanicsburgPA

Customers

2SmallStores2500WoodlandSt.17055PittsburghPA

3MediumRetailers1111RitterRd.17055PittsburghPA

4GoodCompanies9500ScottSt.17050MechanicsburgPA

5WonderfulShops3333RossmoyneRd.17050MechanicsburgPA

6LoyalClients7070RitterRd.17055PittsburghPA

7Distinguished9999ScottSt.17050MechanicsburgPA

Partners

正如你所看到的，第一个客户的名称发生变化，并且多了一个编号是“7”的客户

记录。

你可以运行列表2-1中所示的脚本将SCD1应用于数据仓库中的

customer_dim表中，这里假设新增的客户信息已经被载入到中间（临时）表

customer_stgo

列属2-1:在表customer_dim中对客户名称应用SCDk

/**/

/*scdl.sql*/

/**/

!***************************************************************¥*/

/*defaultdatabasetodw*/

USEdw;

/*updateexistingcustomers*/

UPDATEcustomerdima,customerstgb

SETa.customer_name=b.customer_name

WHEREa.customer_number=b.customernumber

ANDa.expiry_date=*9999-12-31*

ANDa.customer_name<>b.customer_name

/*addnewcustomers*/

INSERTINTOcustomer_dim

SELECT

NULL

,customernumber

,customer_name

,customerstreetaddress

,customer_zip_code

,customer_city

,customer_state

,CURRENT_DATE

,,9999-12-3V

FROMcustomer_stg

WHEREcustomer_numberNOTIN(

SELECTb.customer_number

FROMcustomer_dima,customer_stgb

WHEREa.customernumber=b.customernumber)

/*endofscript*/

列表2-1中的脚本包含两个SQL语句，一个更新语句和一个插入语句。更新语

句将临时表的customer_name字段复制到customer_dim表的customer_name字段。

插入语句则将临时表中有而customer_dim表中没有的记录插入到customer_dim

表中。运行该脚本将在customer_dim表中更新临时表中的第一个客户的名称并

插入临时表中第七个客户的信息。

尽管在你运行这个脚本文件之前我们还没有临时表，但是在customer.csv文件中

我们有当前客户资料表(在source数据库中)的内容。因此，在customer_dim表中

应用SCD1将需要两个步骤：

1.建立临时表customer_stg,并将customer.csv文件中的记录载入该表。

2.运行列表2-1中所示质的脚本。

这些步骤将在下面小节中说明。

建立、载入客户临时表

customer.csv文件包含当前的客户资料信息，可以在本书附带的Zip文件中找到

它。其内容如下所示：

CUSTOMERNO,CUSTOMERNAME,STREETADDRESS,ZIPCODE,CITY,STATE

1,ReallyLargeCustomers,7500LouiseDr.,17050,Mechanicsburg,PA

2,SmallStores,2500WoodlandSt.,17055,Pittsburgh,PA

3,MediumRetailers,1111RitterRd.,17055,Pittsburgh,PA

4,GoodCompanies,9500ScottSt.,17050,Mechanicsburg,PA

5,WonderfulShops,3333RossmoyneRd.,17050,Mechanicsburg,PA

6,LoyalClients,7070RitterRd.,17055,Pittsburgh,PA

7,DistinguishedPartners,9999ScottSt.,17050,Mechanicsburg,PA

列表2-2所示的脚本在数据仓库(dw)中建立customejstg表，并把customer.csv

文件的信息载入表中。

列表2-2:建立并载入customerstg表

/**/

/*create_customer_stg.sql*/

/**/

/*defaultdatabasetodw*/

USEdw;

/*createcustomer_stgtable*/

CREATETABLEcustomer_stg

(customernumberINT

customerenameCHAR(30)

customer_street_addressCHAR(30)

customer_zip_codeINT(5)

customer_cityCHAR(30)

customer_stateCHAR(2))

/*cleanupcustomer_stgtableandloadcustomer,csv*/

TRUNCATEcustomer,stg;

LOADDATAINFILEfcustomer,csv*

INTOTABLEcustomer_stg

FIELDSTERMINATEDBY','

OPTIONALLYENCLOSEDBY

LINESTERMINATEDBY'\r\n'

IGNORE1LINES

(customernumber

,customer_name

,customerstreetaddress

,customer_zip_code

,customer_city

,customer_state)

/*endofscript*/

用以下命令运行列表2-2脚本：

mysql>\.c:\mysql\scripts\create_customer_stg.sql

应用SCD1

现在你可以开始准备运行列表2-1中的SCD1脚本，在此之前,

你需要将mysql数据库时间调整到2007-02-02（一个比你在第1章所设置的日期

更后面的日期）。设置完日期后就可以运行该scdLsq1脚本了。

mysql>\.c:\mysql\scripts\scdl.sql

现在可以查询customer_dim表来确认列表2-1是否以及成功运

行了。

mysql>select*fromcustomer_dim\G

你将会看到如下结果：

customer_sk:1

customer_numbcr:1

customer_name:ReallyLargeCustomers

customer_street_address:7500LouiseDr.

customer_zip_code:17050

customer_city:Mechanicsburg

customer_state:PA

effect!ve_date:2007-02-01

expiry_date:9999-12-31

***************************2row***************************

customer_sk:2

customer_number:2