数据仓库开发实例详解课件

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

2、Pub公司在美国各地均有出版社，出版社负责联系作者、出版图书，已出版的图书将由各地的代销书店进行销售。收集项目需求业务分析需求销售部门最关心销售的业绩，主要通过销售额和销售量进行考核。数据仓库开发实例业务分析需求希望针对每个出版社作图书销售业绩分析。希望针对每个国家的出版社作图书销售业绩分析。希望针对每个州的出版社作图书销售业绩分析。希望针对每城市的出版社作图书销售业绩分析。希望针对每本图书作销售业绩分析。希望针对每类图书做销售业绩分析。希望针对每年出版的图书做销售业绩分析。希望针对每位作者所编写的图书做销售业绩分析。希望针对每个书店作图书销售业绩分析。希望针对每个州的书店作图书销售业绩分析。

3、希望针对每个城市的书店作图书销售业绩分析。希望针对年、月、日作销售业绩分析。 数据仓库开发实例销售业务的总线矩阵业务实体业务过程出版社图书书店出版日期销售XXXX收集项目需求数据资源状况公司的业务数据库为Pubs数据库收集项目需求数据资源状况Pubs中与销售业务有关的表根据总线矩阵建立模型气泡图建立多维数据模型业务实体业务过程出版社图书书店销售日期销售XXXX订单销售日期图书书店出版社销售日期建立多维数据模型订单销售日期图书书店出版社销售日期日期维度的粒度级别通常为一天日期维度表需要专门生成（如利用Excel工具）日期维度表中的时间范围根据业务需求决定建立多维数据模型销售日期日期维度至少要包含

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

5、country国家其中pub_name、pub_city、pub_state、pub_country形成层次。书店建立多维数据模型订单出版日期图书书店出版社书店建立多维数据模型字段名称说明stor_id书店编号stor_name书店名称city所在市state所在州书店书店维度可以直接包含书店名、市、州等属性建立多维数据模型图书建立多维数据模型订单出版日期图书书店出版社图书建立多维数据模型字段名称说明title_id图书编号pub_id出版社编号title图书名type图书类型price图书单价pubdate出版日期图书图书维度可以直接包含图书名、类型、出版年等属性建立多维数据模型DimBoo

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

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

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

9、L Server关系引擎（RDBMS）：实现关系数据库SQL Server Integration Service(SSIS):实现ETL系统SQL Server Analysis Service(SSAS):实现多维数据库，支持OLAP查询，实现数据挖掘SQL Server Reporting Service(SSRS): 定义报表开发和管理工具：SQL Server BI Development Studio（BIDS）和SQL Server Management Studio。Microsoft 数据仓库系统的体系结构源系统数据库ETL聚合(支持OLAP)源系统数据库源系统数据库事实表和

10、维度表度量和维度前端工具核心数据仓库RDBMSSSISSSASSSRSETL过程利用SQL Server Integration Service 对Pubs数据库进行抽取和整理，形成Pubs_DW数据仓库。先将数据从源事务数据中提取到中间数据库Pubs_Stage再对中间数据库的数据进行转换并加载到目标关系数据仓库Pubs_DWETL过程Pubs数据库（业务数据库）PublishersPub_infoEmployeeJobsRoychedTitlesAuthorsTitleauthorSalesStoresDiscountsPubs_Stage（临时数据库）PublishersTitlesTi

11、tleauthorAuthorSalesStoresDatePubs_DW（数据仓库）FactSalesFactBookAuthorDimPublisherDimBookDimAuthorDimStoreDimDateDTSSSISDate.xlsETL过程利用DTS和SQL Server Integration Service 对Pubs数据库进行抽取和整理，形成Pubs_DW数据仓库。先将数据从源事务数据中提取到中间数据库Pubs_Stage(DTS工具)再对中间数据库的数据进行转换并加载到目标关系数据仓库Pubs_DW(SSIS工具)ETL过程利用DTS将pubs数据库和date.xls

12、中的数据直接导出到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表DimPublisher属性名称功能描述Sqlserver数据类型pub_id出版社编号Varchar（4）pub_name出版社名称varchar（40）pub

13、_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数据仓库中的DimBook表DimBook属性名称功能描述Sqlserver数据类型book_id图书编号Varchar（6）book_name图书名var

14、char（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属性名称功能描述Sqlserver数据类型book_id图书编号Varchar（6）au_id作者编号varchar（11）ETL过程pubs_DW数据仓库中的FactSales表

15、FactSales属性名称来源功能描述Sqlserver数据类型pub_idpublishers表出版社编号Varchar（4）stor_idstores表书店编号Varchar（4）book_idtitles表图书编号Varchar（6）ord_datesales表销售日期datetimeqtysales表销售量smallintamount计算生成（qty*price）销售额moneyETL过程新建SSIS项目，并将pubs_stage数据库中的数据转换并加载到pubs_DW数据仓库中。SSIS项目由若干SSIS程序包的构成，其中一个为主程序包（启动）。SSIS程序包由一个控制流和若干个数据

16、流构成。多维数据集和OLAPOLAP的提出（1993年）1981年图灵奖获得者：埃德加科德(Edgar Frank Codd)“关系数据库之父” 多维数据集和OLAP1995年OLAP专门研究机构OLAP Report提出关于OLAP的简明定义FASMI （Fast Analysis of Shared Multidimensional Information）。快速性（Fast）OLAP系统必须能快速响应用户的分析查询要求，对于用户大部分分析要求在5秒钟内作出反应，否则超过30秒用户可能会失去分析的主线索，影响分析质量。多维数据集和OLAP1995年OLAP专门研究机构OLAP Report

17、提出关于OLAP的简明定义FASMI （Fast Analysis of Shared Multidimensional Information）。分析性（Analysis）OLAP系统能处理任何与用户和应用有关的逻辑分析和统计分析，在需要的情况下还允许用户无需编程就可为分析和生成报表定义一些新的特殊运算，将其作为分析的一部分，并以用户理想的方式给出报告。用户可以在OLAP平台上进行数据分析，也可以与其他外部分析工具交互信息，同时应提供灵活开放的报表处理功能，保存分析结果。多维数据集和OLAP1995年OLAP专门研究机构OLAP Report提出关于OLAP的简明定义FASMI （Fast

18、Analysis of Shared Multidimensional Information）。共享性（Shared）OLAP系统应能实现在多用户环境下的安全保密要求和并发控制。多个用户同时使用，能够根据用户所属的安全级别，让他们只能看到自身权限下的信息。多维数据集和OLAP1995年OLAP专门研究机构OLAP Report提出关于OLAP的简明定义FASMI （Fast Analysis of Shared Multidimensional Information）。多维性（Multidimensional）OLAP系统能对数据进行多维视图和分析，是OLAP的关键属性。多维数据集和OLA

19、P1995年OLAP专门研究机构OLAP Report提出关于OLAP的简明定义FASMI （Fast Analysis of Shared Multidimensional Information）。信息性（Information）OLAP系统具有管理数据和获得信息的能力，能管理大量的数据并即时地获得用户所需信息。多维数据集和OLAPOLAP与数据仓库关系紧密OLAP分析与数据仓库的关系十分紧密。数据仓库的建立，解决了依据主题进行数据存储的问题，提高了数据的存取速度；而OLAP分析构成了数据仓库的表现层，将数据仓库中的数据通过不同的维和指标，灵活地展现出来，提高了数据的展现能力，进而提高了数

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

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

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