MySQL_数据仓库指南.docx

MySQL数据仓库指南前言欢迎使用 mysql多维数据仓库指南。数据仓库可以通过集成各种多样的数据实现信息的统一，这些数据来源包括现行的事务操作和管理信息系统，以及外部各种信息源。这些源数据将被整合，清洗，转换，如果需要直接从这些数据来源读取数据的话，该数据将会在数据仓库中以更有利于操作的方式存储。数据仓库的数据构造方式使你可以存储当前和历史的数据。当前数据是实际事务操作活动必要的，一般是定期的硬拷贝（比如打印出的报表）或者是联机报表。而历史数据，往往不是那么容易利用，可以提供基于时间点分析的商业信息,比如：追踪、推论分析，以及对比，这些对于长期计划和战略性的市场决策都是很重要的。如 何从多个信息源整合信息，定期积聚存储，有效的需求设计和发展技术，所有这些都和事务型管理信息系统所应用的技术有很大的不同。本书都是关于数据仓库的设 计和开发技术，它涵盖了大部分建立数据仓库所涉及到的大部分技术问题。更为重要的是，本书提供了一个易懂的操作指南，便于开发一个实际的数据仓库。本书的适用范围mysql多维数据仓库指南是一个实战的书籍。你将使用mysql数据库，但该书不是关于mysql的。本书不涉及任何硬件架构方面的问题。本书主要关心数据仓库的设计和开发技术。它不涉及开发项目的管理，理论，以及如何领导开发等相关的技术问题。本书用一个数据仓库开发的例子来展示技术如何应用。提供数据模型和sql脚本，将能够适用于实际的数据仓库开发。这些脚本已经在Windows xp professional sp2 平台上用mysql 5。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，“格式文件数据源”，介绍如何使用本书例子中的格式文件数据源。源代码下载可以从.上面下载本书的示例的程序脚本。=Dimensional Data Warehousing with MySQL: A TutorialbyDjoni DarmawikartaBrainy Software Corp. 2007 (432 pages)ISBN:9780975212820Computer programmers who need to build a data warehouse will find relevant examples and information written in a thorough, easy-to-follow style in this step-by-step tutorial.封底 Back Cover将Mysql这个最流行的开源数据库软件进行数据仓库应用，在本书之前还从来没有被在创建多维数据仓库方面用这种入门指南的方式进行介绍。主题包括 星型模式建模，数据载入（数据的提取、转换和加载：etl），测试检验，以及多维查询。本书由始至终应用一个可实践，相对精简的真实的项目。其全面，易于接受的写作形式使得那些需要建立数据仓库的程序员可以得到相关的示例和资料。关于作者Djoni Darmawikarta 曾就职于ibm，现在是一个加拿大保险公司的数据仓库、商业智能团队中的技术专家。目前生活在Toronto, Ontario。第一篇基本原理章节列表:第1章：基本组成第2章：维度历史第3章：维度可加性第4章：维度查询本篇概述你将运用关系数据库来实施一个维度数据仓库。事实表和维表这两种类型的关系表构成了一个数据仓库模式的基本部分，在本书的第一部分，你将用mysql数据库建立这些基本部分。第1章:基本组成概述本章将了解两个重要的主题：星型模式和代理键。星型模式是一种维度数据仓库的数据结构。代理键是在数据仓库中添加到事实表以作为主键的字段。在本章你将开始一个关于建立真实数据仓库的漫长旅程。一些任务需要在本章完成：n 建立一个数据库用户。n 建立两个关系数据库，一个作为数据仓库，另外一个作为源数据库。n 为数据仓库建立数据库表。n 产生代理键：你需要建立一个源数据库是因为你的数据仓库还没有源数据。实际应用中这一步并不是必要的，因为你的数据仓库很有可能是基于已有的数据源建立的。你将在本书的第二篇开始使用这个源数据库。第1节 星型模式一个好的维度数据仓库需要有简洁的数据结构。从技术角度看，一个简洁的结构将意味着更快速的查询。在一个维度数据仓库，关系数据库的实施中有两种类型的表，事实表和维度表。事实表包含商业事实数据（或者称为度量值）。维度表包含查询该数据库的种类。注意 在看完本书前面两个章节后，你对事实表和维度表将有更好的理解。这些表在数据仓库中的联系模式看起来象星星状，因此该模式的术语就称为星型模式。注意 除了星型模式外，雪花模式也会用于数据仓库中。但是较之星型结构，其建模更为困难。而且，雪花模式不容易理解和应用，而且其查询性能也低于星型模式。这些缺陷是雪花模式不适合建立维度数据仓库，因此本书只是涉及星型模式。一个星型模式包含一个事实表，该事实表环绕着两个甚至更多的维表。单星结构只有一个事实表，而多星结构则是每个星有多个的事实表,另外，维度表可以为多个事实表所共享。本章只包括单星结构，读者将会在19章“多星模式”中学习到多星结构。图1-1 展示了一个单星结构的维度模式，这是一个我们将会在本书中扩展的销售订单的数据仓库。图1-1 一个单星型的维度模式事实表名称的后缀通常是fact，而dim（维度dimension的缩写）通常用于维度表的后缀。根据这种命名规则，很明显，图1-1 中有一个事实表（销售订单事实表sales_order_fact）和四个维度表（客户维customer_dim,订单维 order_dim,产品维 product_dim, 以及 日期维date_dim）。事实表包含一个或者多个可测的事实值（一个可以测的事实值简称为度量），而维表则对度量进行分类。每个维表有且只有一个代理键字段，代理键字段名称的后缀是sk。维表中的每个代理键字段在事实表中都会有对应的相同的字段，这样使得数据库的查询更为容易。但是，事实表中以sk为后缀的字段并不是代表一个代理键字段。图1-1中连接事实表sales_order_fact和四个维表的线指明了查询这些表的连接关系。这些连接是基于维表的代理键字段的。当建立一个维度数据仓库的时候，你将会在数据仓库中自己产生代理键的值，这些代理键值不是从源数据得到的。代理键的值是连续的数值。注意 后面关于“代理键”的章节将对代理键进行详细的说明。现在你已经知道了星型模式，事实表，维表的概念，我们来看一个例子，如果我们对订单的订单金额感兴趣，并且决定用图1-1 中sales_order_fact表的order_amount字段作为度量。表1-1显示sales_order_fact表的一个抽样记录。表1-1：一个事实表的抽样将数据用电子表格形式展现：customer_sk product_sk date_sk order_sk order_amount 11111000表1-1所关联的维表中相应的数据记录 将在表1-2到1-5中展示。表1-2:客户维表中关联的行customer_sk customer_no customer_name 11Dons Limited表1-3:产品维表中关联的行product_sk product_code product_name 11Cangcung Hard Disk表1-4:时间维表中关联的行sk date 120070201表1-5:订单维表中关联的行order_sk order_number 11事实表的行说明该订单的金额是$1,000。这是事实的度量。sales_order_fact表中的customer_sk字段值为1表示对应customer_dim维表中customer_sk字段值为1的行。这个关联表示，提交这个订单的是名为Dons Limited的客户。用事实表中的product_sk字段的值可以在产品维表中追踪到产品信息。通过将事实表和date_dim时间表的date_sk字段进行关联，你可以得到订单日期。同样，通过将事实表和order_dim订单维表中的order_sk字段进行关联，你可以得到订单号。第2节 代理键维表中的代理键字段是维表的主键。代理键的值通常是连续的数值而不代表任何商业意义。相比之下，许多来自源数据的键值都有商业含义。你将在你的数据仓库内部产生代理键值；你不可以从源数据得到代理键值，在第2章“维度历史”中我将说明这么做的目的。在mysql中，你可以通过设置代理键字段的AUTO INCREMENT自增属性产生代理值。通过将自增字段用null值代替来插入一个递增的整数。已经有足够的原理让我们可以开始建立我们的数据仓库了。接下来的这节，“任务”阐述了你需要在本章完成的任务的详细步骤。第3节 任务有四个任务需要在本章完成：1 建立一个数据库用户。2建立两个关系数据库，一个作为数据仓库，另外一个作为源数据库。3为数据仓库建立数据库表。4产生代理键。每个任务将在独立的小节中说明。建立一个数据库用户id第一步产生一个数据库用户，你可以用该帐户进行操作数据仓库和数据源。在你开始前，确信你已经将本书随附的脚本文件放置在mysql的安装目录下面。例如，我的安装目录是C:mysql,所以我将我的脚本文件放在C:mysqlscripts目录中。我们开始将用root用户登陆mysql，请输入下面的命令：C:mysql -uroot -p你将被提示输入密码： Enter password: *输入密码后，将看到控制台的欢迎信息：Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 6 to server version: 5.0.21-community-ntType help; or h for help. Type c to clear the buffer.mysql敲入密码后的这些信息是当一个root用户登陆mysql时典型的回应信息。列表1-1 所示的脚本create_user_id.sql产生一个用户id dwid 其密码是 pw 。列表1-1: 创建dwid 用户 id /*/ */* create_user_id.sql */* */*/GRANT ALL ON *.* TO dwidlocalhost IDENTIFIED BY pw;/* end of script 用下面的命令方式运行该脚本：mysql . c:mysqlscriptscreate_user_id.sql回车后，你将看到回应的信息：Query OK, 0 rows affected (0.03 sec)用show grants命令，你可以确认用户id：dwid 是否已经被成功创建，你应该在命令中包含你的mysql服务器名称：mysql show grants for dwidlocalhost;假如存在用户 dwid，你可以看到如下信息：Grants for dwidlocalhostGRANT ALL PRIVILEGES ON *.* TO dwidlocalhost IDENTIFIED BY PASSWORD *D821809F681A40A6E379B50D0463EFAE20BDD1221 row in set (0.00 sec)注意 密码部分在你的显示屏上显示的方式将不同于上面的描述。现在你需要退出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 */* */*/CREATE DATABASE dw;CREATE DATABASE source;运行create_databases.sql脚本的命令如下：mysql . c:mysqlscriptscreate_databases.sql在你的控制台界面上，你将看到：Query OK, 1 row affected (0.00 sec)Query OK, 1 row affected (0.00 sec)你可以用show databases命令来确认上面的命令是否成功运行。 为了确认dw数据库被创建用下面命令：mysql show databases like dw;回应信息将如下面所示：+-+| Database (dw) |+-+| dw |+-+1 row in set (0.00 sec)为了确认source数据库被成功创建用下面命令：mysql show databases like source;回应信息将如下面所示：+-+| Database (source) |+-+| source |+-+1 row in set (0.00 sec)创建数据仓库表第二步是在dw数据库中创建数据仓库表。你可以用列表1-3所示的create_dw_tables.sql脚本来创建图1-1中的sales_order_fact表和四个维表。列表1-3:创建数据仓库表 /*/* */* create_dw_tables.sql */* */*/* default to dw database */USE dw;/* creating customer_dim table */CREATE TABLE customer_dim( customer_sk INT NOT NULL AUTO_INCREMENT PRIMARY KEY, customer_number INT, customer_name CHAR (50), customer_street_address CHAR (50), customer_zip_code INT (5), customer_city CHAR (30), customer_state CHAR (2), effective_date DATE, expiry_date DATE );/* creating product_dim table */CREATE TABLE product_dim( product_sk INT NOT NULL AUTO_INCREMENT PRIMARY KEY, product_code INT, product_name CHAR (30), product_category CHAR (30), effective_date DATE, expiry_date DATE );/* creating order_dim table */CREATE TABLE order_dim( order_sk INT NOT NULL AUTO_INCREMENT PRIMARY KEY, order_number INT, effective_date DATE, expiry_date DATE );/* creating date_dim table */CREATE TABLE date_dim( date_sk INT NOT NULL AUTO_INCREMENT PRIMARY KEY, date DATE, month_name CHAR (9), month INT (1), quarter INT (1), year INT (4), effective_date DATE, expiry_date DATE );/* creating sales_order_fact_table */CREATE TABLE sales_order_fact( order_sk INT, customer_sk INT, product_sk INT, order_date_sk INT, order_amount DECIMAL (10, 2) );现在运行create_dw_tables.sql脚本：mysql . c:mysqlscriptscreate_dw_tables.sql你的控制台将显示如下类似的信息：Database changedQuery OK, 0 rows affected (0.13 sec)Query OK, 0 rows affected (0.12 sec)Query OK, 0 rows affected (0.12 sec)Query OK, 0 rows affected (0.10 sec)Query OK, 0 rows affected (0.11 sec)你可以用show create table命令确认某个表是否被创建成功，例如，为了验证customer_dim表是否创建成功，用这个命令：mysql show create table customer_dim G在你的控制台界面上，将可以看到：* 1. row * Table: customer_dimCreate Table: CREATE TABLE customer_dim ( customer_sk int(11) NOT NULL auto_increment, customer_number int(11) default NULL, customer_name char (50) default NULL, customer_street_address char (50) default NULL, customer_zip_code int (5) default NULL, customer_city char (30) default NULL, customer_state char (2) default NULL, effective_date date default NULL, expiry_date date default NULL, PRIMARY KEY (customer_sk) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)用相同的方式，可以确认其他表是否成功创建。产生代理键本节的最后一个任务是用列表1-4所示的customer_sk.sql脚本创建代理键，这个脚本将向customer_dim表插入3条记录。 列表 1-4: 产生 客户代理键值 /*/* */* customer_sk.sql */* */*/* default to dw */USE dw;INSERT INTO customer_dim( customer_sk, customer_number, customer_name, customer_street_address, customer_zip_code, customer_city, customer_state, effective_date, expiry_date )VALUES (NULL, 1, Big Customers, 7500 Louise Dr., 17050, Mechanicsburg, PA, CURRENT_DATE, 9999-12-31), (NULL, 2, Small Stores, 2500 Woodland St., 17055, Pittsburgh, PA, CURRENT_DATE, 9999-12-31), (NULL, 3, Medium Retailers, 1111 Ritter Rd., 17055 Pittsburgh, PA, CURRENT_DATE, 9999-12-31);/* end of script */运行customer_sk.sql脚本之前，你必须将你的mysql时间设置为：2007-02-01,这是因为customer_sk.sql脚本中的CURRENT_DATE函数用操作系统的时间作为载入effective_date字段的值,而我就是在这个时间运行customer_sk.sql脚本的。你可以通过将mysql运行的机器的系统的时间修改为特定的值来修改mysql时间。注意要牢记，这里你改变mysql的时间只是为了能比较平滑结合这个学习教程，实际的生产环境中，你不能在运行脚本的前后改变你的数据库或者系统的时间。事实上，你应该在数据库仓库环境中安排部署你的各个脚本定期的运行。如何定期安排将在第8节“定期载入”中讨论。注意你的mysql监控端将在系统时间被修改后断开和mysql服务端的连接，为了确保你运行脚本不会有什么问题，在调整时间后，试着运行一下诸如“use dw”的简单的命令，你将得到一个错误的信息提示，但是你的监控端将重新连接上mysql。你可以再次运行“use dw”命令来确定已经连接上myslq服务器，这次，你不会再得到任何错误提示了。现在，你已经将mysql的时间调整到2007-02-01，可以用如下的命令运行列表1-4中所示的脚本，mysql . c:mysqlscriptscustomer_sk.sql你将在你的控制台上看到如下信息：Database changedQuery OK, 3 rows affected (0.06 sec)Records: 3 Duplicates: 0 Warnings: 0查询该表，你可以看到这个脚本已经准确的插入代理键值了。mysql select * from customer_dim G* 1. row * customer_sk: 1 customer_number: 1 customer_name: Big Customerscustomer_street_address: 7500 Louise Dr. customer_zip_code: 17050 customer_city: Mechanicsburg customer_state: PA effective_date: 2007-02-01 expiry_date: 9999-12-31* 2. row * customer_sk: 2 customer_number: 2 customer_name: Small Storescustomer_street_address: 2500 Woodland St. customer_zip_code: 17055 customer_city: Pittsburgh customer_state: PA effective_date: 2007-02-01 expiry_date: 9999-12-31* 3. row * customer_sk: 3 customer_number: 3 customer_name: Medium Retailerscustomer_street_address: 1111 Ritter Rd. customer_zip_code: 17055 customer_city: Pittsburgh customer_state: PA ef