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

第一讲

数据仓库系统的设计与实现姓名：陈令江QQ：453579383电话：Email：内容1、数据仓库系统设计概述2、业务系统数据库设计方法3、数据仓库系统设计方法5、使用SQLServer2005建立多维数据模型6、开源数据仓库工具介绍数据仓库4、数据仓库设计实例1.1数据仓库系统的建设过程收集和分析业务需求建立数据模型定义数据源选择技术和平台抽取数据选择访问工具更新数据库1.1.1收集和分析业务需求收集和分析业务需求建立数据模型定义数据源选择技术和平台抽取数据选择访问工具更新数据库在数据仓库环境中，用户大多是企业或事业组织的中上层管理人员，他们对决策分析的需求不能预先做出规范说明。他们经常对开发人员说：“让我看看能得到什么，然后我才能告诉你我真正需要什么”。因此，数据仓库应该在海量的数据中为用户提供有用、及时、全面的信息，以帮助用户做出正确的决策。1.1.2建立数据模型收集和分析业务需求建立数据模型定义数据源选择技术和平台抽取数据选择访问工具更新数据库建立数据模型和数据仓库的物理设计。通过设计数据仓库的概念模型、逻辑模型、物理模型，可以得到企业或事业数据的完整而清晰的描述信息。数据仓库的核心内容包括组织的各个主题域、主题域之间的联系、描述主题的码和属性组等。1.1.3定义数据源收集和分析业务需求建立数据模型定义数据源选择技术和平台抽取数据选择访问工具更新数据库定义数据源。也叫做定义记录系统，往往会形成一个操作型数据的存储区（ODS）。它主要解决如下两方面的问题：一、各个业务系统的数据都是面向应用的，不能完整的描述企业中的主题域；二、多个数据源的数据之间存在着许多不一致，如命名、结构、单位等；记录系统具有如下特点：1）结构最适合于数据仓库；2）与外部数据源最为接近；1.1.4选择技术和平台收集和分析业务需求建立数据模型定义数据源选择技术和平台抽取数据选择访问工具更新数据库技术和平台选型对数据仓库来说非常重要，一旦选定，在实施完成后很难改变，平台及技术的切换成本非常高，所以，选型一定要充分重视和高度谨慎。1.1.5数据抽取收集和分析业务需求建立数据模型定义数据源选择技术和平台抽取数据选择访问工具更新数据库从操作型数据库中抽取、清洗及转换数据到数据仓库中。详细参见第一讲ETL部分。1.1.6选择访问工具收集和分析业务需求建立数据模型定义数据源选择技术和平台抽取数据选择访问工具更新数据库根据用户的具体情况、分析需求以及数据量大小等因素，选择各种访问工具，如报表工具、分析工具，仪表盘等。1.1.7更新数据库收集和分析业务需求建立数据模型定义数据源选择技术和平台抽取数据选择访问工具更新数据库确定数据仓库的更新策略，开发或配置数据仓库更新子系统，实现数据仓库数据的自动更新。1.2数据仓库系系统的生命命周期生命周期为为螺旋上升升模型1.3数据仓库系系统的建设设方法自顶向下（（top-down）自底向上（（bottom-up）1.4数据仓库系系统的设计计步骤内容1、数据仓库库系统设计计概述2、业务系统统数据库设设计方法3、数据仓库库系统设计计方法5、使用SQLServer2005建立多维数数据模型6、开源数据据仓库工具具介绍数据仓库4、数据仓库库设计实例例2.1数据库的设设计步骤数据库设计计的好，可可以减少数数据存储量量，提高数数据的完整整性和一致致性。数据库的设设计一般分分四步：1、概念模型型的设计2、逻辑数据据库的设计计3、规范化理理论的应用用4、物理数据据库的设计计概念模型设设计概念模型设设计的描述述最常用的的工具是E-R图，具体步步骤如下：：确定实体；；确定实体的的属性；确定实体的的主键；确定实体间间的联系类类型；画出E-R图；编号姓名性别学历职称教师讲授课程m教师n教师1系部聘任n概念模型设计计（整体ER图）mnn11n系部学院设置nm1n1课程聘任教师讲授选修n班级有有学生逻辑模型设计计将概念模型转转换成数据库库管理系统所所支持的数据据模型。如果是关系型型数据库管理理系统，就应应将概念模型型转换为关系系模型，即将将E-R图中的实体和和联系转换为为关系模式。。学院（学院代码、学院名称、、院长姓名、、学院地址、、学院电话、、学院简介））系部（系部编号、学院代码、、系部名称、、主任、地址址、电话、专专业介绍）教师（教师编号、系部编号、、姓名、性别别、学历、职职称、办公电电话、手机、、E-mail、照片）班级（班级编号、班级名称、、班长、联系系电话、奖励处罚、系系部编号）学生（学号、姓名、性别别、出生年月月、籍贯、个个人简历、照照片、奖励处处罚、班级编编号）规范化理论实实施在逻辑数据库库设计中，形形成了一组关关系模式。如如果关系模式式没有设计好好，就会出现现数据冗余、、数据更新异异常、数据删删除异常、数数据插入异常常等问题。规规范化理论的的目的就是设设计好的关系系模式。规范化理论的的基本概念（（一）1．函数依赖设在关系R中，X、Y为R的两个属性子子集，如果每每个X值只有一个Y值与之对应，，则称属性Y函数依赖于属属性X；或称属性X惟一确定属性性Y，记作X→Y。如果X→Y，同时Y不包含于X，则称X→Y是非平凡的函函数依赖。例子如果每个X值只有一个Y值与之对应。。S2钱尔182C7

70SNOSNAGEXFCNOSCORES1赵亦173C190S1赵亦172C285S2钱尔183C157S2钱尔183C680XYXYXY规范化理论的的基本概念（（二）2．完全函数依依赖设在关系R中，X、Y为R的两个属性子子集，若X→Y，且对于X的任何一个真真子集X’，都有X’→Y,则称Y完全函数依赖赖于X。若X’Y，则称Y部分函数依赖赖于X。S2钱尔182C7

70SNOSNAGEXFCNOSCORES1赵亦173C190S1赵亦172C285S2钱尔183C157S2钱尔183C680XYXY规范化理论的的基本概念（（三）3．传递函数依依赖设在关系R中，X、Y、Z为R的三个属性子子集，若X→Y，Y→Z，且X不依赖于Y，则称Z传递函数依赖赖于X。学号姓名院系编号院系名称院系地址01004张辉春01计算机学院科教楼三层02001陈玉茜01计算机学院科教楼三层02002张一燕03会计学院科教楼十一层XYZ第一范式第一范式（FirstNormalForm）是最基本的的规范形式，，即关系中每每个属性都是是不可再分的的简单项。每每个规范化的的关系都属于于1NF，这也是它之之所以称为““第一”的原原因。编号姓名电话家庭电话办公电话01004张辉春667788998733421102001陈玉茜672123458656633302002张一燕6789123482334212编号姓名家庭电话办公电话01004张辉春667788998733421102001陈玉茜672123458656633302002张一燕6789123482334212第二范式定义如果果关系模式R1NF，且每个非主主属性都完全全函数依赖于于任意一个侯侯选关键字，，则称R属于第二范式式，简称2NF，记作R2NF。学号课程号成绩学分01002C176401002C288502002C190402002C286502002C393405007C3964带来问题的原原因是：非主主属性“学分分”仅仅依赖赖于“课程号号”，也就是是说只是部分分依赖于主关关键字（学号号，课程号）），而不是完完全依赖。解决方法96C30500793C30200286C20200290C10200288C20100276C101002成绩课程号学号3计算机绘图C63大学美育C54大学物理C44英语C35计算机文化基础C24高等数学C1学分课程名课程号解决方法：将将关系模式进进一步分解第三范式定义如果果关系模式R2NF，且每个非主主属性都不传传递依赖于R的任何候选关关键字，则称称R属于第三范式式，简称3NF，记作R3NF。如果R3NF，则R也是2NF。如果R2NF，则R不一定是3NF。解决方法学号姓名院系编号院系名称院系地址01004张辉春01计算机学院科教楼三层02001陈玉茜01计算机学院科教楼三层02002张一燕03会计学院科教楼十一层03张一燕0200201陈玉茜0200101张辉春01004院系编号姓名学号科教楼二层经济学院02科教楼十一层会计学院03科教楼三层计算机学院01院系地址院系名称院系编号物理模型设计计专业软件开发发中使用，不不是必须步骤骤。主要建模工具具：PowerDesignerERWin内容1、数据仓库系系统设计概述述2、业务系统数数据库设计方方法3、数据仓库系系统设计方法法5、使用SQLServer2005建立多维数据据模型6、开源数据仓仓库工具介绍绍数据仓库4、数据仓库设设计实例3.1数据仓库系统统的设计步骤骤数据仓库的数数据库设计，，不以减少数数据存储量为为目标，而是是以提供数据据查询效率为为首要目标。。数据仓库的数数据库的设计计一般分三步步：1、概念模型的的设计（信息息包图法）2、逻辑数据库库的设计（星星型模型）3、物理数据库库的设计概念模型设计计数据仓库的概概念模型通常常采用信息包图法进行设计，要要求将信息包包图的5个组成部分全全面的描述出出来。名称维度类别层次度量信息包图法信息包图法也也叫用户信息息需求表，就就是在一张平平面表格上描描述元素的多多维型，一个个维度用一列列表示，细化化本列对象就就是类别，最最后一行为指指标度量值，，如下表：维度信息包图：销售分析类别时间维区域维产品维客户维广告维年度（5）国家（10）产品类别（500）年龄分组（7）广告费分组（5）季度（20）省/州（100）产品（9000）收入分组（8）月（60）城市（500）信用分组（2）日（1800）销售点（8000）度量指标：实际销售额、计划销售额、计划完成率主题及主题域域的划分商品商品ID商品信息商品主题供应商供应商ID有关信息供应商主题顾客顾客ID有关信息顾客主题仓库仓库ID仓库信息仓库主题供应购买储存划分了主题域域的原始概念念模型商品ID商品名称商品类型商品规格……供应商ID联系电话联系人地址……顾客ID顾客姓名联系电话地址……仓库ID仓库地址仓库电话……仓库ID(FK)商品ID(FK)仓库数量顾客ID(FK)商品ID(FK)购买价格购买数量供应商ID(FK)商品ID(FK)仓库数量供应商表仓库表顾客表商品表逻辑模型设计计根据用户需求求和信息包图图设计星形模模型或雪花模模型。从信息包图转转换成星形模模型，需要定定义如下三个个实体：1、指标实体。。即度量，使使用每一个指指标，同事确确定是否存储储经过计算的的指标。2、维度实体。。对应信息包包图中的一个个类别（一列列）。3、详细类别实实体。对应现现实世界的某某一实体。星形模型销售分析时间客户产品广告区域雪花模型销售分析时间客户产品广告区域产品类别事实表事实表是星形形结构模型的的核心，包含含了基本业务务事务的详细细信息。事实表一般包包括两个部分分：1、由主键和外外键所组成的的键部分。2、用户希望了了解的数值指指标，称为事事实或度量指指标。销售分析时间产品客户区域时间ID（FK）客户ID（FK）产品ID（FK）区域ID（FK）销售额利润销售事实表主键（PK）度量指标数据的粒度所谓粒度，是是指数据仓库库中数据单元元的详细程度度和级别。数据越详细，，粒度就越小小，级别也就就越低；数据据综合度越高高，粒度就越越大，级别也也就越高。时间ID（FK）客户ID（FK）产品ID（FK）区域ID（FK）销售额利润日销售事实表时间ID（FK）客户ID（FK）产品ID（FK）区域ID（FK）销售额利润月销售事实表粒度低粒度高粒度对数据仓仓库的影响粒度对数据仓仓库最直接的的影响就是存存储容量。还还以销售事实实表为例：日日事实表的记记录数为月事事实表的30倍。粒度对数据查查询统计的影影响。时间ID（FK）客户ID（FK）产品ID（FK）区域ID（FK）销售额利润日销售事实表时间ID（FK）客户ID（FK）产品ID（FK）区域ID（FK）销售额利润月销售事实表汇总到日20090901汇总到月200909粒度的设计步步骤由前面的分析析可知，数据据仓库分析功功能和存储空空间是一对矛矛盾体。如果果粒度设计过过小，则事实实表需记录所所有细节，存存储空间将会会急剧膨胀；；若设计过大大，则无法观观察到细节数数据。粒度设计可以以按以下两个个步骤进行：：1、粗略估算数数据量，确定定合适的粒度度级的起点。。如估算1年到5年内表中最少少和最多记录录数。2、确定粒度级级别。需综合合考虑分析需需求类型、数数据最低粒度度、存储数据据量的因素。。数据的聚合在事实表中存存放的度量数数据，根据其其实际意义，，可以分成可加性度量和非可加性度量量。可加性度量是指数据相加加后得到的结结果仍然具有有实际意义，，比如销售金金额，3个月月相相加加为为一一个个季季度度，，四四个个季季度度相相加加为为一一年年。。这这样样的的数数据据可可以以将将其其计计算算后后存存储储到到表表中中，，可可以以显显著著提提高高系系统统性性能能，，这这样样的的表表称称为为聚聚合合表表。。非可可加加性性度度量量是指指数数据据相相加加后后无无意意义义，，比比如如利利润润率率，，同同比比、、环环比比等等。。数据据的的分分割割数据据分分割割是是把把数数据据分分散散到到各各自自的的存存储储单单元元中中去去，，是是它它们们能能独独立立的的处处理理。。数据据分分割割主主要要是是解解决决数数据据量量过过于于庞庞大大的的问问题题，，提提高高系系统统效效率率。。数据据分分割割可可以以按按各各种种方方式式划划分分，，如如：：按按时时间间、、按按区区域域、、按按业业务务、、按按组组织织等等。。全部部销销售售记记录录分割割20052006200720082009维度度表表维度度表表应应有有的的数数据据特特征征：：1、维维度度通通常常使使用用解解析析过过的的名名字字，，这这样样可可以以是是分分析析查查询询更更加加灵灵活活和和快快速速。。时时间间可可分分为为年年、、季季、、月月、、日日等等；；2、维维度度表表通通常常不不使使用用业业务务数数据据库库的的键键值值作作为为主主键键，，而而是是使使用用自自定定义义的的字字段段做做主主键键，，字字段段类类型型主主要要为为数数值值型型和和字字符符型型。。如如时时间间维维表表的的主主键键类类型型为为数数值值型型，，20090901、200909等；3、维度表表应包含含随时间间变化的的数据记记录字段段，当事事实表中中的数据据随时间间变化而而产生改改变时，，维表应应有标识识此变化化的字段段；常用维表表处理（（时间维维）时间维是是最常用用的维度度，时间间维有多多种划分分方式，，如最常常用的年年->季->月->日，有时时会增加加半年和和旬等，，国外也也常用年年->周，还有有按财政政年度（（FY）的等，，下表为为通常的的时间维维结构。。数值型字符型数值型字符型数值型字符型日期编码(主键)日期名称月份编码月份名称季度编码季度名称200909012009-09-012009092009年9月2009032009年Q3200909022009-09-022009092009年9月2009032009年Q3……200910012009-10-012009102009年10月2009042009年Q4常用维表表处理（（雪花模模型维））如前面讲讲过的产产品维和和产品类类别维。。之所以以产生雪雪花模型型，是由由于设计计规范化化的结果果，在实实际应用用中，通通常建议议不要使使用雪花花模型，，这是因因为：1、影响查查询效率率；2、有些工工具不支支持；因此，建建议将雪雪花模型型转换为为星形模模型，转转换方式式如下：：字符型字符型字符型型字符型产品编码(主键)产品名称产品类别编码产品类别名称B0101交