6数据仓库开发实例课件.ppt

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

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

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

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

5、明说明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形成层次。形成层次。书店建立多维数据模型订单出版日期图书书店出版社书店建立多维数据模型字段名称字段名称说明说明stor_id书店编号stor_name书店名称city所在

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

7、多维数据模型作者建立多维数据模型订单出版日期图书书店出版社作者图书作者关系作者建立多维数据模型字段名称字段名称说明说明au_id作者编号au_lname作者名au_fname作者姓作者作者维度可以直接包含作者名等属性建立多维数据模型DimAuthor属性名称属性名称功能描述功能描述au_id作者编号au_name作者名au_fname + au_lname图书作者关系建立多维数据模型订单出版日期图书书店出版社作者图书作者关系图书作者关系建立多维数据模型字段名称字段名称说明说明au_id作者编号title_id图书编号建立多维数据模型图书作者关系图书作者关系事实表只包含键值列，不包含度量值列，其

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

9、_idpublishers表出版社编号stor_idstores表书店编号book_idtitles表图书编号ord_datesales表销售日期度度量量值值列列qtysales表销售量amount计算生成（qty*price）销售额建立多维数据模型最终，确定数据集市包括以下多维数据模型：主题主题图书图书销售业绩销售业绩度度量值量值1. 1. 图书图书销售量销售量2. 2. 图书图书销售额销售额维度维度1. 1. 出版社（国别出版社（国别- -州州- -城市城市- -出版社）出版社）2. 2. 作者（作者姓名）作者（作者姓名）3. 3. 图书（图书类别图书（图书类别- -单一图书，图书出版年）

10、单一图书，图书出版年）4. 4. 书店（州书店（州- -市市- -书店）书店）5. 5. 销售销售日期日期（年年- -月月- -日日）Microsoft 数据仓库工具集核心数据仓库工具集SQL Server关系引擎（RDBMS）：实现关系数据库SQL Server Integration Service(SSIS):实现ETL系统SQL Server Analysis Service(SSAS):实现多维数据库，支持OLAP查询，实现数据挖掘SQL Server Reporting Service(SSRS): 定义报表开发和管理工具：SQL Server BI Development Stu

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

12、oEmployeeJobsRoychedTitlesAuthorsTitleauthorSalesStoresDiscountsPubs_Stage（临时数据库）PublishersTitlesTitleauthorAuthorSalesStoresDatePubs_DW（数据仓库）FactSalesFactBookAuthorDimPublisherDimBookDimAuthorDimStoreDimDateDTSSSISDate.xlsETL过程利用DTS和SQL Server Integration Service 对Pubs数据库进行抽取和整理，形成Pubs_DW数据仓库。先将数据从

13、源事务数据中提取到中间数据库Pubs_Stage(DTS工具)再对中间数据库的数据进行转换并加载到目标关系数据仓库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过程

14、pubs_DW数据仓库中的DimPublisher表DimPublisher属性名称属性名称功能描述功能描述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城市

15、varchar（20）store_state州varchar（2）ETL过程pubs_DW数据仓库中的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（8

16、0）ETL过程pubs_DW数据仓库中的FactBookAuthor表FactBookAuthor属性名称属性名称功能描述功能描述Sqlserver数据类型数据类型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表销

17、售日期datetimeqtysales表销售量smallintamount计算生成（qty*price）销售额moneyETL过程新建SSIS项目，并将pubs_stage数据库中的数据转换并加载到pubs_DW数据仓库中。SSIS项目由若干SSIS程序包的构成，其中一个为主程序包（启动）。SSIS程序包由一个控制流和若干个数据流构成。多维数据集和OLAPOLAP的提出（1993年）1981年图灵奖获得者：埃德加科德(Edgar Frank Codd)“关系数据库之父” 多维数据集和OLAP1995年OLAP专门研究机构OLAP Report提出关于OLAP的简明定义FASMI （Fast A

18、nalysis of Shared Multidimensional Information）。快速性（快速性（FastFast）OLAPOLAP系统系统必须能快速响应用必须能快速响应用户的分析查询要求，对于用户大部分分析要求在户的分析查询要求，对于用户大部分分析要求在5 5秒钟内作出反应，否则超过秒钟内作出反应，否则超过3030秒用户可能会失去分秒用户可能会失去分析的主线索，影响分析质量析的主线索，影响分析质量。多维数据集和OLAP1995年OLAP专门研究机构OLAP Report提出关于OLAP的简明定义FASMI （Fast Analysis of Shared Multidimens

19、ional Information）。分析性（分析性（AnalysisAnalysis）OLAPOLAP系统能处理任何与用系统能处理任何与用户和应用有关的逻辑分析和统计分析，在需要的情户和应用有关的逻辑分析和统计分析，在需要的情况下还允许用户无需编程就可为分析和生成报表定况下还允许用户无需编程就可为分析和生成报表定义一些新的特殊运算，将其作为分析的一部分，并义一些新的特殊运算，将其作为分析的一部分，并以用户理想的方式给出报告。用户可以在以用户理想的方式给出报告。用户可以在OLAPOLAP平平台上进行数据分析，也可以与其他外部分析工具交台上进行数据分析，也可以与其他外部分析工具交互信息，同时应提

20、供灵活开放的报表处理功能，保互信息，同时应提供灵活开放的报表处理功能，保存分析结果。存分析结果。多维数据集和OLAP1995年OLAP专门研究机构OLAP Report提出关于OLAP的简明定义FASMI （Fast Analysis of Shared Multidimensional Information）。共享性（共享性（SharedShared）OLAPOLAP系统应系统应能实现在多用能实现在多用户环境下的安全保密要求和并发控制。多个用户同户环境下的安全保密要求和并发控制。多个用户同时使用，能够根据用户所属的安全级别，让他们只时使用，能够根据用户所属的安全级别，让他们只能看到自身权限

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

22、f Shared Multidimensional Information）。信息性（信息性（InformationInformation）OLAPOLAP系统具有管理系统具有管理数数据和获得信息的能力，能管理大量的数据并即时地据和获得信息的能力，能管理大量的数据并即时地获得用户所需信息获得用户所需信息。多维数据集和OLAPOLAP与数据仓库关系紧密OLAPOLAP分析与数据仓库的关系十分紧密。数据仓库分析与数据仓库的关系十分紧密。数据仓库的建立，解决了依据主题进行数据存储的问题，提的建立，解决了依据主题进行数据存储的问题，提高了数据的存取高了数据的存取速度；而速度；而OLAPOLAP分析构成

23、了数据仓分析构成了数据仓库的表现层，将数据仓库中的数据通过不同的维和库的表现层，将数据仓库中的数据通过不同的维和指标，灵活地展现出来，提高了数据的展现能力，指标，灵活地展现出来，提高了数据的展现能力，进而提高了数据的分析能力。进而提高了数据的分析能力。多维数据集和OLAP多维数据集多维数据多维数据集（集（cubecube）OLAPOLAP中的主要对象，通中的主要对象，通常也称作多维常也称作多维立方体。立方体。cubecube是一个数据集合，通常是一个数据集合，通常由数据仓库的子集构造，把一组维度和度量值合理由数据仓库的子集构造，把一组维度和度量值合理组织，最后汇总成多维结构。组织，最后汇总成多

24、维结构。多维数据集和OLAP多维数据集销售数量10000玩具电器销售地区北京上海江苏 1 2 3 4服装化妆品产品时间（月）以时间、销售地区、产品三个维度构成的多维数据集多维数据集和OLAP的实现以Pubs_DW数据库为基础，利用SQL Server Analysis Service开发Pubs AS多维数据库以支持OLAP分析。Pubs_DW（关系数据库）FactSalesFactBookAuthorDimPublisherDimBookDimAuthorDimStoreDimDateSSASPubs_AS（多维数据集）Sales Cube（Sales多维数据集）Sales度量值组：度量值组

25、： Sales Amount Sales QtyBookAuthor中间度量值组中间度量值组维度：维度： Book Author Store Publisher Date多维数据集和OLAP的实现多维数据库的数据源（DS）pubs_DW数据源视图（DSV）多维数据库pubs_AS多维数据集（Cube）度量值和维度聚合聚合SSAS多维数据库体系结构多维数据集和OLAP的实现分析服务项目开发步骤1.创建数据源2.创建数据源视图3.创建维度（公共维度）4.创建多维数据集（度量值）5.处理、部署项目6.浏览多维数据集多维数据集和OLAP的实现分析服务项目开发要点每个分析服务项目必须至少包含一个数据源每

26、个分析服务项目必须至少包含一个数据源视图每个分析服务项目必须包含至少一个维度和多维数据集多维数据集必须包含在一个分析服务项目中每个分析服务项目可以包含多个多维数据集多维数据集和OLAP多维数据分析方法OLAPOLAP多维分析多维分析对多维数据集中的对多维数据集中的数据进行数据进行上卷上卷、下钻、切片、切块、旋转下钻、切片、切块、旋转等各种分析操作，以便剖等各种分析操作，以便剖析数据，析数据，使用户从使用户从多个角度、多个多个角度、多个侧面、侧面、多个层次多个层次来观察多维数据，从而发掘数据中蕴涵的对自己有来观察多维数据，从而发掘数据中蕴涵的对自己有用的信息。用的信息。多维数据集和OLAP多维数

27、据分析方法多维数据集和OLAP多维数据分析方法切片切片（切片（SliceSlice）对对多维多维数据集的某个维选定数据集的某个维选定一个一个维维成员，这种选择操作成员，这种选择操作就称为切片。就称为切片。例如：例如：CubeCube（维（维1 1，维，维2 2，维，维i i，维，维n n，度量值），度量值）对对维维i i选定了某个维成员选定了某个维成员，则，则（维维1 1，维，维2 2，维维i i成员成员，维，维n n，度量值度量值）就是该就是该CubeCube在在维维i i上的一个上的一个切片切片，切片切片的的数量取决数量取决于于维维i i上的维成员的上的维成员的个数。个数。多维数据集和OL

28、AP多维数据分析方法切片“时间第1季度” 在三维立方体上切片在三维立方体上切片的结果是的结果是得到一得到一个二维的个二维的平面数据平面数据4.1 OLAP概述多维数据分析方法切块切块（切块（DiceDice）在在一个多维数据集中对两个及两一个多维数据集中对两个及两个以上的维选定维成员的操作可以称为个以上的维选定维成员的操作可以称为切块。切块。例如：例如：CubeCube（维（维1, 1, 维维2, , 2, , 维维i i, , , , 维维k, , k, , 维维n, n, 度量值）度量值）对对维维i i，维，维k k，选定了维成员，选定了维成员，则则（维（维1, 1, 维维2, , 2,

29、, 维维i i成员成员, , , , 维维k k成员成员, , , , 维维n, n, 度度量值量值) )就是该就是该CubeCube在维在维i i, , , , 维维k k上的一个切块上的一个切块。显然显然，当，当i=ki=k时，切块操作就退化成切片操作。时，切块操作就退化成切片操作。多维数据集和OLAP多维数据分析方法切块(贷款“正常” or “次级”）And（时间“1季度” or “2季度”）And（经济性质=“集体“or”个人”or”其它“） 切块的结果是得到了一个子立方体切块的结果是得到了一个子立方体多维数据集和OLAP多维数据分析方法上卷上卷（上卷（Roll-upRoll-up）上

30、卷上卷是对数据进行更为宏观的是对数据进行更为宏观的观察观察。通过在维的等级中上升或通过消除某个或某通过在维的等级中上升或通过消除某个或某些维来观察更概括的数据。些维来观察更概括的数据。多维数据集和OLAP多维数据分析方法上卷上卷（上卷（1 1）在在维的等级中上升维的等级中上升沿着时间维上卷，由“季度”上升到半年 4.1 OLAP概述多维数据分析方法上卷上卷（上卷（2 2）消除一个或多个维消除一个或多个维消除“经济性质”维度 多维数据集和OLAP多维数据分析方法下钻下钻下钻（Drill-downDrill-down）下钻是对数据进行更为详下钻是对数据进行更为详细的观察。通过在维的等级细的观察。通过在维的等级中中下降或通过引入某个下降或通过引入某个或某些维来更细致的观察或某些维来更细致的观察数据。数据。多维数据集和OLAP多维数据分析方法下钻沿时间维下钻 多维数据集和OLAP多维数据分析方法旋转旋转旋转（PivotPivot）旋转又称为旋转又称为转轴（转轴（RotateRotate），将），将多维数据集中各个多维数据集中各个维的角度进行维的角度进行转动，将不同转动，将不同维进维进行交换显示行交换显示，以，以使用户更加直观地观察数据集中不使用户更加直观地观察数据集中不同维之间的关系同维之间的关系。多维数据集和OLAP多维数据分析方法旋转交换“时间”和“经济性质”轴