第二讲数据仓库系统的设计与实现

1、第一讲数据仓库系统的设计与实现姓名：陈令江QQ：453579383电话mail：chenlingjiang内容1、数据仓仓库系统统设计概概述2、业务系系统数据据库设计计方法3、数据仓仓库系统统设计方方法5、使用SQLServer2005建立多维维数据模模型6、开源数据据仓库工工具介绍绍数据仓库4、数据仓仓库设计计实例1.1数据仓库库系统的的建设过过程收集和分分析业务需求求建立数据模型定义数据源选择技术和平台台抽取数据选择访问工具更新数据库1.1.1收集和分分析业务务需求收集和分分析业务需求求建立数据模型定义数据源选择技术和平台台抽取数据选择访问工具更新数据库在数据仓仓

2、库环境境中，用用户大多多是企业业或事业业组织的的中上层层管理人人员，他他们对决决策分析析的需求求不能预预先做出出规范说说明。他他们经常常对开发发人员说说：“让让我看看看能得到到什么，然后我我才能告告诉你我我真正需需要什么么”。因因此，数数据仓库库应该在在海量的的数据中中为用户户提供有有用、及及时、全全面的信信息，以以帮助用用户做出出正确的的决策。1.1.2建立数据据模型收集和分分析业务需求求建立数据模型定义数据源选择技术和平台台抽取数据选择访问工具更新数据库建立数据据模型和和数据仓仓库的物物理设计计。通过过设计数数据仓库库的概念念模型、逻辑模模型、物物理模型型，可以以得到企企业或事事业数据据的

3、完整整而清晰晰的描述述信息。数据仓仓库的核核心内容容包括组组织的各各个主题题域、主主题域之之间的联联系、描描述主题题的码和和属性组组等。1.1.3定义数据据源收集和分分析业务需求求建立数据模型定义数据源选择技术和平台台抽取数据选择访问工具更新数据库定义数据据源。也也叫做定定义记录录系统，往往会会形成一一个操作作型数据据的存储储区（ODS）。它主主要解决决如下两两方面的的问题：一、各个个业务系系统的数数据都是是面向应应用的，不能完完整的描描述企业业中的主主题域；二、多个个数据源源的数据据之间存存在着许许多不一一致，如如命名、结构、单位等等；记录系统统具有如如下特点点：1）结构最最适合于于数据仓仓

4、库；2）与外部部数据源源最为接接近；1.1.4选择技术术和平台台收集和分分析业务需求求建立数据模型定义数据源选择技术和平台台抽取数据选择访问工具更新数据库技术和平平台选型型对数据据仓库来来说非常常重要，一旦选选定，在在实施完完成后很很难改变变，平台台及技术术的切换换成本非非常高，所以，选型一一定要充充分重视视和高度度谨慎。1.1.5数据抽取取收集和分分析业务需求求建立数据模型定义数据源选择技术和平台台抽取数据选择访问工具更新数据库从操作型型数据库库中抽取取、清洗洗及转换换数据到到数据仓仓库中。详细参见见第一讲讲ETL部分。1.1.6选择访问问工具收集和分分析业务需求求建立数据模型定义数据源选择

5、技术和平台台抽取数据选择访问工具更新数据库根据用户户的具体体情况、分析需需求以及及数据量量大小等等因素，选择各各种访问问工具，如报表表工具、分析工工具，仪仪表盘等等。1.1.7更新数据据库收集和分分析业务需求求建立数据模型定义数据源选择技术和平台台抽取数据选择访问工具更新数据库确定数据据仓库的的更新策策略，开开发或配配置数据据仓库更更新子系系统，实实现数据据仓库数数据的自自动更新新。1.2数据仓库库系统的的生命周周期1.2.1生命周期期为螺旋旋上升模模型1.3数据仓库库系统的的建设方方法自顶向下下（top-down）自底向上上（bottom-up）1.4数据仓库库系统的的设计步步骤内容1、数据

6、仓仓库系统统设计概概述2、业务系系统数据据库设计计方法3、数据仓仓库系统统设计方方法5、使用SQLServer2005建立多维维数据模模型6、开源数据据仓库工工具介绍绍数据仓库4、数据仓仓库设计计实例2.1数据库的的设计步步骤数据库设设计的好好，可以以减少数数据存储储量，提提高数据据的完整整性和一一致性。数据库的的设计一一般分四四步：1、概念模模型的设设计2、逻辑数数据库的的设计3、规范化化理论的的应用4、物理数数据库的的设计2.1.1概念模型型设计概念模型型设计的的描述最最常用的的工具是是E-R图，具体体步骤如如下：确定实体体；确定实体体的属性性；确定实体体的主键键；确定实体体间的联联系类型

7、型；画出E-R图；编号姓名性别学历职称教师讲授课程m教师n教师1系部聘任n2.1.1概念模型型设计（整体ER图）mnn11n系部学院设置nm1n1课程聘任教师讲授选修n班级有有学生2.1.2逻辑模型型设计将概念模模型转换换成数据据库管理理系统所所支持的的数据模模型。如果是关关系型数数据库管管理系统统，就应应将概念念模型转转换为关关系模型型，即将将E-R图中的实实体和联联系转换换为关系系模式。学院（学院代码码、学院名名称、院院长姓名名、学院院地址、学院电电话、学学院简介介）系部（系部编号号、学院代代码、系系部名称称、主任任、地址址、电话话、专业业介绍）教师（教师编号号、系部编编号、姓姓名、性性别

8、、学学历、职职称、办办公电话话、手机机、E-mail、照片）班级（班级编号号、班级名名称、班班长、联联系电话话、奖励处罚罚、系部部编号）学生（学号、姓名、性别、出生年年月、籍籍贯、个个人简历历、照片片、奖励励处罚、班级编编号）2.1.3规范化理理论实施施在逻辑数数据库设设计中，形成了了一组关关系模式式。如果果关系模模式没有有设计好好，就会会出现数数据冗余余、数据据更新异异常、数数据删除除异常、数据插插入异常常等问题题。规范范化理论论的目的的就是设设计好的的关系模模式。2.1.3.2规范化理理论的基基本概念念（一）1函数依依赖设在关系系R中，X、Y为R的两个属属性子集集，如果果每个X值只有一一个

9、Y值与之对对应，则则称属性性Y函数依赖赖于属性性X；或称属属性X惟一确定定属性Y，记作XY。如果XY，同时Y不包含于于X，则称XY是非平凡凡的函数数依赖。2.1.3.2例子如果每个个X值只有一一个Y值与之对对应。S2钱尔182C770SNOSNAGEXFCNOSCORES1赵亦173C190S1赵亦172C285S2钱尔183C157S2钱尔183C680XYXYXY2.1.3.2规范化理理论的基基本概念念（二）2完全函函数依赖赖设在关系系R中，X、Y为R的两个属属性子集集，若XY，且对于X的任何一一个真子子集X，都有XY,则称Y完全函数数依赖于于X。若XY，则称Y部分函数数依赖于于X。S2钱

10、尔182C770SNOSNAGEXFCNOSCORES1赵亦173C190S1赵亦172C285S2钱尔183C157S2钱尔183C680XYXY2.1.3.2规范化理理论的基基本概念念（三）3传递函函数依赖赖设在关系系R中，X、Y、Z为R的三个属属性子集集，若XY，YZ，且X不依赖于于Y，则称Z传递函数数依赖于于X。学号姓名院系编号院系名称院系地址01004张辉春01计算机学院科教楼三层02001陈玉茜01计算机学院科教楼三层02002张一燕03会计学院科教楼十一层XYZ2.1.3.3第一范式式第一范式式（FirstNormalForm）是最基基本的规规范形式式，即关关系中每每个属性性都是

11、不不可再分分的简单单项。每每个规范范化的关关系都属属于1NF，这也是是它之所所以称为为“第一一”的原原因。编号姓名电话家庭电话办公电话01004张辉春667788998733421102001陈玉茜672123458656633302002张一燕6789123482334212编号姓名家庭电话办公电话01004张辉春667788998733421102001陈玉茜672123458656633302002张一燕67891234823342122.1.3.3第二范式式定义如如果关关系模式式R1NF，且每个个非主属属性都完完全函数数依赖于于任意一一个侯选选关键字字，则称称R属于第二二范式，简称2N

12、F，记作R2NF。学号课程号成绩学分01002C176401002C288502002C190402002C286502002C393405007C3964带来问题题的原因因是：非非主属性性“学分分”仅仅仅依赖于于“课程程号”，也就是是说只是是部分依依赖于主主关键字字（学号号，课程程号），而不是是完全依依赖。2.1.3.3解决方法法96C30500793C30200286C20200290C10200288C20100276C101002成绩课程号学号3计算机绘图C63大学美育C54大学物理C44英语C35计算机文化基础C24高等数学C1学分课程名课程号解决方法法：将关关系模式式进一步步分解2

13、.1.3.3第三范式式定义如果关系系模式R2NF，且每个非非主属性性都不传传递依赖赖于R的任何候候选关键键字，则则称R属于第三三范式，简称3NF，记作R3NF。如果R3NF，则R也是2NF。如果R2NF，则R不一定是是3NF。2.1.3.3解决方法法学号姓名院系编号院系名称院系地址01004张辉春01计算机学院科教楼三层02001陈玉茜01计算机学院科教楼三层02002张一燕03会计学院科教楼十一层03张一燕0200201陈玉茜0200101张辉春01004院系编号姓名学号科教楼二层经济学院02科教楼十一层会计学院03科教楼三层计算机学院01院系地址院系名称院系编号2.1.4物理模型型设计专业

14、软件件开发中中使用，不是必必须步骤骤。主要建模模工具：PowerDesignerERWin内容1、数据仓仓库系统统设计概概述2、业务系系统数据据库设计计方法3、数据仓仓库系统统设计方方法5、使用SQLServer2005建立多维维数据模模型6、开源数据据仓库工工具介绍绍数据仓库4、数据仓仓库设计计实例3.1数据仓库库系统的的设计步步骤数据仓库库的数据据库设计计，不以以减少数数据存储储量为目目标，而而是以提提供数据据查询效效率为首首要目标标。数据仓库库的数据据库的设设计一般般分三步步：1、概念模模型的设设计（信信息包图图法）2、逻辑数数据库的的设计（星型模模型）3、物理数数据库的的设计3.1.1

15、概念模型型设计数据仓库库的概念念模型通通常采用用信息包图图法进行设计计，要求求将信息息包图的的5个组成部部分全面面的描述述出来。名称维度类别层次度量3.1.1.1信息包图图法信息包图图法也叫叫用户信信息需求求表，就就是在一一张平面面表格上上描述元元素的多多维型，一个维维度用一一列表示示，细化化本列对对象就是是类别，最后一一行为指指标度量量值，如如下表：维度信息包图：销售分析类别时间维区域维产品维客户维广告维年度（5）国家（10）产品类别（500）年龄分组（7）广告费分组（5）季度（20）省/州（100）产品（9000）收入分组（8）月（60）城市（500）信用分组（2）日（1800）销售点（8

16、000）度量指标：实际销售额、计划销售额、计划完成率3.1.1.2主题及主主题域的的划分商品商品ID商品信息商品主题供应商供应商ID有关信息供应商主题顾客顾客ID有关信息顾客主题仓库仓库ID仓库信息仓库主题供应购买储存3.1.1.3划分了主主题域的的原始概概念模型型商品ID商品名称商品类型商品规格供应商ID联系电话联系人地址顾客ID顾客姓名联系电话地址仓库ID仓库地址仓库电话仓库ID(FK)商品ID(FK)仓库数量顾客ID(FK)商品ID(FK)购买价格购买数量供应商ID(FK)商品ID(FK)仓库数量供应商表表仓库表顾客表商品表3.1.2逻辑模型型设计根据用户户需求和和信息包包图设计计星形模

17、模型或雪雪花模型型。从信息包包图转换换成星形形模型，需要定定义如下下三个实实体：1、指标实实体。即即度量，使用每每一个指指标，同同事确定定是否存存储经过过计算的的指标。2、维度实实体。对对应信息息包图中中的一个个类别（一列）。3、详细类类别实体体。对应应现实世世界的某某一实体体。3.1.2.1星形模型型销售分析析时间客户产品广告区域3.1.2.2雪花模型型销售分析析时间客户产品广告区域产品类别别3.1.2.3事实表事实表是是星形结结构模型型的核心心，包含含了基本本业务事事务的详详细信息息。事实表一一般包括括两个部部分：1、由主键键和外键键所组成成的键部部分。2、用户希希望了解解的数值值指标，称

18、为事事实或度度量指标标。销售分析析时间产品客户区域时间ID（FK）客户ID（FK）产品ID（FK）区域ID（FK）销售额利润销售事实实表主键（PK）度量指标标3.1.2.4数据的粒粒度所谓粒度度，是指指数据仓仓库中数数据单元元的详细细程度和和级别。数据越详详细，粒粒度就越越小，级级别也就就越低；数据综综合度越越高，粒粒度就越越大，级级别也就就越高。时间ID（FK）客户ID（FK）产品ID（FK）区域ID（FK）销售额利润日销售事实表时间ID（FK）客户ID（FK）产品ID（FK）区域ID（FK）销售额利润月销售事实表粒度低粒度高3.1.2.5粒度对数数据仓库库的影响响粒度对数数据仓库库最直接接

19、的影响响就是存存储容量量。还以以销售事事实表为为例：日日事实表表的记录录数为月月事实表表的30倍。粒度对数数据查询询统计的的影响。时间ID（FK）客户ID（FK）产品ID（FK）区域ID（FK）销售额利润日销售事实表时间ID（FK）客户ID（FK）产品ID（FK）区域ID（FK）销售额利润月销售事实表汇总到日日20090901汇总到月月2009093.1.2.6粒度的设设计步骤骤由前面的的分析可可知，数数据仓库库分析功功能和存存储空间间是一对对矛盾体体。如果果粒度设设计过小小，则事事实表需需记录所所有细节节，存储储空间将将会急剧剧膨胀；若设计计过大，则无法法观察到到细节数数据。粒度设计计可以按

20、按以下两两个步骤骤进行：1、粗略估估算数据据量，确确定合适适的粒度度级的起起点。如如估算1年到5年内表中中最少和和最多记记录数。2、确定粒粒度级别别。需综综合考虑虑分析需需求类型型、数据据最低粒粒度、存存储数据据量的因因素。3.1.2.7数据的聚聚合在事实表表中存放放的度量量数据，根据其其实际意意义，可可以分成成可加性度度量和非可加性性度量。可加性度度量是指数据据相加后后得到的的结果仍仍然具有有实际意意义，比比如销售售金额，3个月相加加为一个个季度，四个季季度相加加为一年年。这样样的数据据可以将将其计算算后存储储到表中中，可以以显著提提高系统统性能，这样的的表称为为聚合表表。非可加性性度量是指

21、数据据相加后后无意义义，比如如利润率率，同比比、环比比等。3.1.2.8数据的分分割数据分割割是把数数据分散散到各自自的存储储单元中中去，是是它们能能独立的的处理。数据分割割主要是是解决数数据量过过于庞大大的问题题，提高高系统效效率。数据分割割可以按按各种方方式划分分，如：按时间间、按区区域、按按业务、按组织织等。全部销售售记录分割200520062007200820093.1.2.9维度表维度表应应有的数数据特征征：1、维度通通常使用用解析过过的名字字，这样样可以是是分析查查询更加加灵活和和快速。时间可可分为年年、季、月、日日等；2、维度表表通常不不使用业业务数据据库的键键值作为为主键，而是

22、使使用自定定义的字字段做主主键，字字段类型型主要为为数值型型和字符符型。如如时间维维表的主主键类型型为数值值型，20090901、200909等；3、维度表表应包含含随时间间变化的的数据记记录字段段，当事事实表中中的数据据随时间间变化而而产生改改变时，维表应应有标识识此变化化的字段段；3.1.2.10常用维表表处理（时间维维）时间维是是最常用用的维度度，时间间维有多多种划分分方式，如最常常用的年年-季-月-日，有时时会增加加半年和和旬等，国外也也常用年年-周，还有有按财政政年度（FY）的等，下表为为通常的的时间维维结构。数值型字符型数值型字符型数值型字符型日期编码(主键)日期名称月份编码月份名称季度编码季度名称200909012009-09-012009092009年9月2009032009年Q3200909022009-09-022009092009年9月2009032009年Q3200910012009-10-012009102009年1