6数据仓库开发实例

1、数据仓库与OLAP广东工业大学 艾丹祥数据仓库库开发实实例案例背景景Pub是美国的的一家图图书出版版公司，希望构构建数据据仓库。其中销销售部门门的需求求比较急急迫，希希望能在在短时间间内实现现对销售售业绩的的分析。根据Pub公司的情情况，考考虑采用用金博尔尔的DMDW的开发模模式，即即先为销销售部门门设计一一个数据据集市，其他部部门数据据集市的的设计可可参照此此模型。收集项目目需求准备：研研究选定定的业务务过程（术语、步骤、度量方方法）采访：钻钻研选定定的业务务过程（理解使使其运作作所需的的分析、数据模模型和技技术）分析：业业务过程程相关的的数据资资源归档对于Pub公司：选选定“销销售”业业务

2、过程程后，通通过采访访了解其其与销售售分析相相关的问问题，如如通过什什么方式式销售、有哪些些销售地地区等。收集项目目需求基本业务务状况Pub公司在美美国各地地均有出出版社，出版社社负责联联系作者者、出版版图书，已出版版的图书书将由各各地的代代销书店店进行销销售。收集项目目需求业务分析析需求销售部门门最关心心销售的的业绩，主要通通过销售售额和销销售量进进行考核核。数据仓库库开发实实例业务分析析需求希望针对对每个出出版社作作图书销销售业绩绩分析。希望针对对每个国国家的出出版社作作图书销销售业绩绩分析。希望针对对每个州州的出版版社作图图书销售售业绩分分析。希望针对对每城市市的出版版社作图图书销售售业

3、绩分分析。希望针对对每本图图书作销销售业绩绩分析。希望针对对每类图图书做销销售业绩绩分析。希望针对对每年出出版的图图书做销销售业绩绩分析。希望针对对每位作作者所编编写的图图书做销销售业绩绩分析。希望针对对每个书书店作图图书销售售业绩分分析。希望针对对每个州州的书店店作图书书销售业业绩分析析。希望针对对每个城城市的书书店作图图书销售售业绩分分析。希望针对对年、月月、日作作销售业业绩分析析。数据仓库库开发实实例销售业务务的总线线矩阵业务实体业务过程出版社图书书店出版日期销售XXXX收集项目目需求数据资源源状况公司的业业务数据据库为Pubs数据库收集项目目需求数据资源源状况Pubs中与销售售业务有有

4、关的表表根据总线线矩阵建建立模型型气泡图图建立多维维数据模模型业务实体业务过程出版社图书书店销售日期销售XXXX订单销售日期图书书店出版社销售日期期建立多维维数据模模型订单销售日期图书书店出版社销售日期期日期维度度的粒度度级别通通常为一一天日期维度度表需要要专门生生成（如如利用Excel工具）日期维度度表中的的时间范范围根据据业务需需求决定定建立多维维数据模模型销售日期期日期维度度至少要要包含年年、月、日期三三个属性性建立多维维数据模模型DimDate属性名称功能描述FullDate日期DateName日期名Month月MonthName月名Year年其中FullDate、Month、Mont

5、hName形成层次次。出版社建立多维维数据模模型订单销售日期图书书店出版社出版社建立多维维数据模模型字段名称说明pub_id出版社编号pub_name出版社名city所在市state所在州country所在国出版社出版社维维度可以以直接包包含出版版社名、市、州州、国家家等属性性建立多维维数据模模型DimPublisher属性名称功能描述pub_id出版社编号pub_name出版社名称pub_city城市pub_state州pub_country国家其中pub_name、pub_city、pub_state、pub_country形成层次次。书店建立多维维数据模模型订单出版日期图书书店出版社书店

6、建立多维维数据模模型字段名称说明stor_id书店编号stor_name书店名称city所在市state所在州书店书店维度度可以直直接包含含书店名名、市、州等属属性建立多维维数据模模型图书建立多维维数据模模型订单出版日期图书书店出版社图书建立多维维数据模模型字段名称说明title_id图书编号pub_id出版社编号title图书名type图书类型price图书单价pubdate出版日期图书图书维度度可以直直接包含含图书名名、类型型、出版版年等属属性建立多维维数据模模型DimBook属性名称功能描述book_id图书编号book_name图书名book_type图书类型pubyear出版年其中b

7、ook_name、book_type形成层次次结构。图书图书维度度无法直直接包含含作者属属性建立多维维数据模模型作者建立多维维数据模模型订单出版日期图书书店出版社作者图书作者者关系作者建立多维维数据模模型字段名称说明au_id作者编号au_lname作者名au_fname作者姓作者作者维度度可以直直接包含含作者名名等属性性建立多维维数据模模型DimAuthor属性名称功能描述au_id作者编号au_name作者名au_fname + au_lname图书作者者关系建立多维维数据模模型订单出版日期图书书店出版社作者图书作者者关系图书作者者关系建立多维维数据模模型字段名称说明au_id作者编号ti

8、tle_id图书编号建立多维维数据模模型图书作者者关系图书作者者关系事事实表只只包含键键值列，不包含含度量值值列，其其目的是是为了记记录图书书和作者者之间的的关系事事实。FactBookAuthor（中间事实表）属性名称功能描述键值列book_id图书编号au_id作者编号订单建立多维维数据模模型订单出版日期图书书店出版社作者图书作者者关系订单建立多维维数据模模型字段名称说明stor_id书店编号title_id图书编号qty订购数量ord_date订购日期建立多维维数据模模型订单订单事实实表中要要包含日日期、出出版社、书店、图书等等键值列列，还要要包含销销售额、销售量量2个度量值值列，其其目

9、的是是为了记记录销售售事实。建立多维维数据模模型订单FactSales属性名称来源功能描述键值列pub_idpublishers表出版社编号stor_idstores表书店编号book_idtitles表图书编号ord_datesales表销售日期度量值列qtysales表销售量amount计算生成（qty*price）销售额建立多维维数据模模型最终，确确定数据据集市包包括以下下多维数数据模型型：主题图书销售售业绩度量值图书销售售量图书销售售额维度出版社（国别-州-城市-出版社）作者（作作者姓名名）图书（图图书类别别-单一图书书，图书书出版年年）书店（州州-市-书店）销售日期期（年-月-日）M

10、icrosoft数据仓库库工具集集核心数据据仓库工工具集SQLServer关系引擎擎（RDBMS）：实现现关系数数据库SQLServer Integration Service(SSIS):实现ETL系统SQLServer Analysis Service(SSAS):实现多维维数据库库，支持持OLAP查询，实实现数据据挖掘SQLServer ReportingService(SSRS):定义报表表开发和管管理工具具：SQLServer BI Development Studio（BIDS）和SQLServer ManagementStudio。Microsoft数据仓库库系统的的体系结结构源

11、系统数据库ETL聚合(支持OLAP)源系统数据库源系统数据库事实表和维度表度量和维度前端工具核心数据仓库RDBMSSSISSSASSSRSETL过程利用SQLServer Integration Service对Pubs数据库进进行抽取取和整理理，形成成Pubs_DW数据仓库库。先将数据据从源事事务数据据中提取取到中间间数据库库Pubs_Stage再对中间间数据库库的数据据进行转转换并加加载到目目标关系系数据仓仓库Pubs_DWETL过程Pubs数据库（业务数据库）PublishersPub_infoEmployeeJobsRoychedTitlesAuthorsTitleauthorSale

12、sStoresDiscountsPubs_Stage（临时数据库）PublishersTitlesTitleauthorAuthorSalesStoresDatePubs_DW（数据仓库）FactSalesFactBookAuthorDimPublisherDimBookDimAuthorDimStoreDimDateDTSSSISDate.xlsETL过程利用DTS和SQLServer Integration Service对Pubs数据库进进行抽取取和整理理，形成成Pubs_DW数据仓库库。先将数据据从源事事务数据据中提取取到中间间数据库库Pubs_Stage(DTS工具)再对中间间数据库

13、库的数据据进行转转换并加加载到目目标关系系数据仓仓库Pubs_DW(SSIS工具)ETL过程利用DTS将pubs数据库和和date.xls中的数据据直接导导出到pubs_stage数据库中中。Pubs_Stage的结构ETL过程新建pubs_DW数据仓库库ETL过程pubs_DW数据仓库库中的DimDate表DimDate属性名称功能描述Sqlserver数据类型FullDate日期DatetimeDateName日期名varchar（20）Month月intMonthName月名varchar（20）Year年intETL过程pubs_DW数据仓库库中的DimPublisher表DimPub

14、lisher属性名称功能描述Sqlserver数据类型pub_id出版社编号Varchar（4）pub_name出版社名称varchar（40）pub_city城市varchar（20）pub_state州varchar（2）pub_country国家varchar（30）ETL过程pubs_DW数据仓库库中的DimStore表DimStore属性名称功能描述Sqlserver数据类型store_id书店编号Varchar（4）store_name书店名称varchar（40）store_city城市varchar（20）store_state州varchar（2）ETL过程pubs_DW数据

15、仓库库中的DimBook表DimBook属性名称功能描述Sqlserver数据类型book_id图书编号Varchar（6）book_name图书名varchar（80）book_type图书类型varchar（12）pubyear出版年intETL过程pubs_DW数据仓库库中的DimAuthor表DimAuthor属性名称功能描述Sqlserver数据类型au_id作者编号varchar（11）au_name作者名au_fname + au_lnamevarchar（80）ETL过程pubs_DW数据仓库库中的FactBookAuthor表FactBookAuthor属性名称功能描述Sql

16、server数据类型book_id图书编号Varchar（6）au_id作者编号varchar（11）ETL过程pubs_DW数据仓库库中的FactSales表FactSales属性名称来源功能描述Sqlserver数据类型pub_idpublishers表出版社编号Varchar（4）stor_idstores表书店编号Varchar（4）book_idtitles表图书编号Varchar（6）ord_datesales表销售日期datetimeqtysales表销售量smallintamount计算生成（qty*price）销售额moneyETL过程新建SSIS项目，并并将pubs_sta

17、ge数据库中中的数据据转换并并加载到到pubs_DW数据仓库库中。SSIS项目由若若干SSIS程序包的的构成，其中一一个为主主程序包包（启动动）。SSIS程序包由由一个控控制流和和若干个个数据流流构成。多维数据据集和OLAPOLAP的提出（1993年）1981年图灵奖奖获得者者：埃德加科德(EdgarFrankCodd)“关系数据据库之父父”多维数据据集和OLAP1995年OLAP专门研究究机构OLAP Report提出关于于OLAP的简明定定义FASMI（Fast Analysis of SharedMultidimensional Information）。快速性（Fast）OLAP系统必

18、须须能快速速响应用用户的分分析查询询要求，对于用用户大部部分分析析要求在在5秒钟内作作出反应应，否则则超过30秒用户可可能会失失去分析析的主线线索，影影响分析析质量。多维数据据集和OLAP1995年OLAP专门研究究机构OLAP Report提出关于于OLAP的简明定定义FASMI（Fast Analysis of SharedMultidimensional Information）。分析性（Analysis）OLAP系统能处处理任何何与用户户和应用用有关的的逻辑分分析和统统计分析析，在需需要的情情况下还还允许用用户无需需编程就就可为分分析和生生成报表表定义一一些新的的特殊运运算，将将其作为

19、为分析的的一部分分，并以以用户理理想的方方式给出出报告。用户可可以在OLAP平台上进进行数据据分析，也可以以与其他他外部分分析工具具交互信信息，同同时应提提供灵活活开放的的报表处处理功能能，保存存分析结结果。多维数据据集和OLAP1995年OLAP专门研究究机构OLAP Report提出关于于OLAP的简明定定义FASMI（Fast Analysis of SharedMultidimensional Information）。共享性（Shared）OLAP系统应能能实现在在多用户户环境下下的安全全保密要要求和并并发控制制。多个个用户同同时使用用，能够够根据用用户所属属的安全全级别，让他们们只

20、能看看到自身身权限下下的信息息。多维数据据集和OLAP1995年OLAP专门研究究机构OLAP Report提出关于于OLAP的简明定定义FASMI（Fast Analysis of SharedMultidimensional Information）。多维性（Multidimensional）OLAP系统能对对数据进进行多维维视图和和分析，是OLAP的关键属属性。多维数据据集和OLAP1995年OLAP专门研究究机构OLAP Report提出关于于OLAP的简明定定义FASMI（Fast Analysis of SharedMultidimensional Information）。信息性

21、（Information）OLAP系统具有有管理数数据和获获得信息息的能力力，能管管理大量量的数据据并即时时地获得得用户所所需信息息。多维数据据集和OLAPOLAP与数据仓仓库关系系紧密OLAP分析与数数据仓库库的关系系十分紧紧密。数数据仓库库的建立立，解决决了依据据主题进进行数据据存储的的问题，提高了了数据的的存取速速度；而而OLAP分析构成成了数据据仓库的的表现层层，将数数据仓库库中的数数据通过过不同的的维和指指标，灵灵活地展展现出来来，提高高了数据据的展现现能力，进而提提高了数数据的分分析能力力。多维数据据集和OLAP多维数据据集多维数据据集（cube）OLAP中的主要要对象，通常也也称

22、作多多维立方方体。cube是一个数数据集合合，通常常由数据据仓库的的子集构构造，把把一组维维度和度度量值合合理组织织，最后后汇总成成多维结结构。多维数据据集和OLAP多维数据据集销售数量10000玩具电器销售地区北京上海江苏 1 2 3 4服装化妆品产品时间（月）以时间、销售地区、产品三个维度构成的多维数据集多维数据据集和OLAP的实现以Pubs_DW数据库为为基础，利用SQLServer Analysis Service开发Pubs AS多维数据据库以支支持OLAP分析。Pubs_DW（关系数数据库）FactSalesFactBookAuthorDimPublisherDimBookDimA

23、uthorDimStoreDimDateSSASPubs_AS（多维数数据集）SalesCube（Sales多维数据据集）Sales度量值组组：SalesAmountSalesQtyBookAuthor中间度量量值组维度：BookAuthorStorePublisherDate多维数据据集和OLAP的实现多维数据库的数据源（DS）pubs_DW数据源视图（DSV）多维数据库pubs_AS多维数据集（Cube）度量值和维度聚合SSAS多维数据据库体系系结构多维数据据集和OLAP的实现分析服务务项目开开发步骤骤创建数据据源创建数据据源视图图创建维度度（公共共维度）创建多维维数据集集（度量量值）处理

24、、部部署项目目浏览多维维数据集集多维数据据集和OLAP的实现分析服务务项目开开发要点点每个分析析服务项项目必须须至少包包含一个个数据源源每个分析析服务项项目必须须至少包包含一个个数据源源视图每个分析析服务项项目必须须包含至至少一个个维度和和多维数数据集多维数据据集必须须包含在在一个分分析服务务项目中中每个分析析服务项项目可以以包含多多个多维维数据集集多维数据据集和OLAP多维数据据分析方方法OLAP多维分析析对多维维数据集集中的数数据进行行上卷、下下钻、切切片、切切块、旋旋转等各种分分析操作作，以便便剖析数数据，使使用户从从多个角角度、多多个侧面面、多个个层次来来观察多多维数据据，从而而发掘数数据中蕴蕴涵的对对自己有有用的信信息。多维数据据集和OLAP多维数据据分析方方法多维数据据集和OLAP多维数据据分析方方法切片切片（Slice）对多维数数据集的的某个维维选定一一个维成成员，这这种选择择操作就就称为切切片。例如：Cube（维1，维2，维i，维n，度量值值）对维i选定了某某个维成成员，则则（维1，维2，维i成员，维n，度量值值）就是该Cube在维i上的一个个切片，切片的的数量取取决于维维i上的维成成员的个个数。多维数据据集和OLAP多维数据据分析方方