数据仓库开发实例

数据仓库与OLAP

广东工业大学艾丹祥数据仓库开发实例

案例背景Pub是美国的一家图书出版公司，希望构建数据仓库。其中销售部门的需求比较急迫，希望能在短时间内实现对销售业绩的分析。根据Pub公司的情况，考虑采用金博尔的DMDW的开发模式，即先为销售部门设计一个数据集市，其他部门数据集市的设计可参照此模型。收集项目需求准备：研究选定的业务过程（术语、步骤、度量方法）采访：钻研选定的业务过程（理解使其运作所需的分析、数据模型和技术）分析：业务过程相关的数据资源归档对于Pub公司：选定“销售”业务过程后，通过采访了解其与销售分析相关的问题，如通过什么方式销售、有哪些销售地区等。收集项目需求基本业务状况Pub公司在美国各地均有出版社，出版社负责联系作者、出版图书，已出版的图书将由各地的代销书店进行销售。收集项目需求业务分析需求销售部门最关心销售的业绩，主要通过销售额和销售量进行考核。数据仓库开发实例业务分析需求希望针对每个出版社作图书销售业绩分析。希望针对每个国家的出版社作图书销售业绩分析。希望针对每个州的出版社作图书销售业绩分析。希望针对每城市的出版社作图书销售业绩分析。希望针对每本图书作销售业绩分析。希望针对每类图书做销售业绩分析。希望针对每年出版的图书做销售业绩分析。希望针对每位作者所编写的图书做销售业绩分析。希望针对每个书店作图书销售业绩分析。希望针对每个州的书店作图书销售业绩分析。希望针对每个城市的书店作图书销售业绩分析。希望针对年、月、日作销售业绩分析。数据仓库开发实例销售业务的总线矩阵业务实体业务过程出版社图书书店出版日期销售XXXX收集项目需求数据资源状况公司的业务数据库为Pubs数据库收集项目需求数据资源状况Pubs中与销售业务有关的表根据据总总线线矩矩阵阵建建立立模模型型气气泡泡图图建立立多多维维数数据据模模型型业务实体业务过程出版社图书书店销售日期销售XXXX订单销售日期图书书店出版社销售售日日期期建立立多多维维数数据据模模型型订单销售日期图书书店出版社销售售日日期期日期期维维度度的的粒粒度度级级别别通通常常为为一一天天日期期维维度度表表需需要要专专门门生生成成（（如如利利用用Excel工具））日期维维度表表中的的时间间范围围根据据业务务需求求决定定建立多多维数数据模模型销售日日期日期维维度至至少要要包含含年、、月、、日期期三个个属性性建立多多维数数据模模型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_country国家其中pub_name、pub_city、pub_state、pub_country形成层层次。。书店建立多多维数数据模模型订单出版日期图书书店出版社书店建立多多维数数据模模型字段名称说明stor_id书店编号stor_name书店名称city所在市state所在州书店书店维维度可可以直直接包包含书书店名名、市市、州州等属属性建立多多维数数据模模型图书建立多多维数数据模模型订单出版日期图书书店出版社图书建立多多维数数据模模型字段名称说明title_id图书编号pub_id出版社编号title图书名type图书类型price图书单价pubdate出版日期图书图书维维度可可以直直接包包含图图书名名、类类型、、出版版年等等属性性建立多多维数数据模模型DimBook属性名称功能描述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图书作作者关关系建立多多维数数据模模型订单出版日期图书书店出版社作者图书作作者关关系图书作作者关关系建立多多维数数据模模型字段名称说明au_id作者编号title_id图书编号建立多多维数数据模模型图书作作者关关系图书作作者关关系事事实表表只包包含键键值列列，不不包含含度量量值列列，其其目的的是为为了记记录图图书和和作者者之间间的关关系事事实。。FactBookAuthor（中间事实表）属性名称功能描述键值列book_id图书编号au_id作者编号订单建立多多维数数据模模型订单出版日期图书书店出版社作者图书作作者关关系订单建立多多维数数据模模型字段名称说明stor_id书店编号title_id图书编号qty订购数量ord_date订购日期建立多多维数数据模模型订单订单事事实表表中要要包含含日期期、出出版社社、书书店、、图书书等键键值列列，还还要包包含销销售额额、销销售量量2个度量量值列列，其其目的的是为为了记记录销销售事事实。。建立多多维数数据模模型订单FactSales属性名称来源功能描述键值列pub_idpublishers表出版社编号stor_idstores表书店编号book_idtitles表图书编号ord_datesales表销售日期度量值列qtysales表销售量amount计算生成（qty*price）销售额建立多多维数数据模模型最终，，确定定数据据集市市包括括以下下多维维数据据模型型：主题——图书销销售业业绩度量值值——图书销销售量量图书销销售额额维度——出版社社（国国别-州-城市-出版社社）作者（（作者者姓名名）图书（（图书书类别别-单一图图书，，图书书出版版年））书店（（州-市-书店））销售日日期（（年-月-日）Microsoft数据仓仓库工工具集集核心数数据仓仓库工工具集集——SQLServer关系引引擎（（RDBMS）：实实现关关系数数据库库SQLServerIntegrationService(SSIS):实现ETL系统SQLServerAnalysisService(SSAS):实现多多维数数据库库，支支持OLAP查询，，实现现数据据挖掘掘SQLServerReportingService(SSRS):定义报报表开发和和管理理工具具：SQLServerBIDevelopmentStudio（BIDS）和SQLServerManagementStudio。Microsoft数据仓仓库系系统的的体系系结构构源系统数据库ETL聚合(支持OLAP)源系统数据库源系统数据库事实表和维度表度量和维度前端工具核心数据仓库RDBMSSSISSSASSSRSETL过程利用SQLServerIntegrationService对Pubs数据库库进行行抽取取和整整理，，形成成Pubs_DW数据仓仓库。。先将数数据从从源事事务数数据中中提取取到中中间数数据库库Pubs_Stage再对中中间数数据库库的数数据进进行转转换并并加载载到目目标关关系数数据仓仓库Pubs_DWETL过程Pubs数据库（业务数据库）PublishersPub_infoEmployeeJobsRoychedTitlesAuthorsTitleauthorSalesStoresDiscountsPubs_Stage（临时数据库）PublishersTitlesTitleauthorAuthorSalesStoresDatePubs_DW（数据仓库）FactSalesFactBookAuthorDimPublisherDimBookDimAuthorDimStoreDimDateDTSSSISDate.xlsETL过程利用DTS和SQLServerIntegrationService对Pubs数据库库进行行抽取取和整整理，，形成成Pubs_DW数据仓仓库。。先将数数据从从源事事务数数据中中提取取到中中间数数据库库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过程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城市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（80）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表销售日期datetimeqtysales表销售量smallintamount计算生成（qty*price）销售额moneyETL过程新建SSIS项目，，并将将pubs_stage数据库库中的的数据据转换换并加加载到到pubs_DW数据仓仓库中中。SSIS项目由由若干干SSIS程序包包的构构成，，其中中一个个为主主程序序包（（启动动）。。SSIS程序包包由一一个控控制流流和若若干个个数据据流构构成。。多维数数据集集和OLAPOLAP的提出出（1993年）1981年图灵奖奖获得者者：埃德加·科德(EdgarFrankCodd)—“关系数据据库之父父”多维数据据集和OLAP1995年OLAP专门研究究机构OLAPReport提出关于于OLAP的简明定定义FASMI（FastAnalysisofSharedMultidimensionalInformation）。快速性（（Fast）——OLAP系统必须须能快速速响应用用户的分分析查询询要求，，对于用用户大部部分分析析要求在在5秒钟内作作出反应应，否则则超过30秒用户可可能会失失去分析析的主线线索，影影响分析析质量。。多维数据据集和OLAP1995年OLAP专门研究究机构OLAPReport提出关于于OLAP的简明定定义FASMI（FastAnalysisofSharedMultidimensionalInformation）。分析性（（Analysis）——OLAP系统能处处理任何何与用户户和应用用有关的的逻辑分分析和统统计分析析，在需需要的情情况下还还允许用用户无需需编程就就可为分分析和生生成报表表定义一一些新的的特殊运运算，将将其作为为分析的的一部分分，并以以用户理理想的方方式给出出报告。。用户可可以在OLAP平台上进进行数据据分析，，也可以以与其他他外部分分析工具具交互信信息，同同时应提提供灵活活开放的的报表处处理功能能，保存存分析结结果。多维数据据集和OLAP1995年OLAP专门研究究机构OLAPReport提出关于于OLAP的简明定定义FASMI（FastAnalysisofSharedMultidimensionalInformation）。共享性（（Shared）——OLAP系统应能能实现在在多用户户环境下下的安全全保密要要求和并并发控制制。多个个用户同同时使用用，能够够根据用用户所属属的安全全级别，，让他们们只能看看到自身身权限下下的信息息。多维数据据集和OLAP1995年OLAP专门研究究机构OLAPReport提出关于于OLAP的简明定定义FASMI（FastAnalysisofSharedMultidimensionalInformation）。多维性（（Multidimensional）——OLAP系统能对对数据进进行多维维视图和和分析，，是OLAP的关键属属性。多维数据据集和OLAP1995年OLAP专门研究究机构OLAPReport提出关于于OLAP的简明定定义FASMI（FastAnalysisofSharedMultidimensionalInformation）。信息性（（Information）——OLAP系统具有有管理数数据和获获得信息息的能力力，能管管理大量量的数据据并即时时地获得得用户所所需信息息。多维数据据集和OLAPOLAP与数据仓仓库关系系紧密OLAP分析与数数据仓库库的关系系十分紧紧密。数数据仓库库的建立立，解决决了依据据主题进进行数据据存储的的问题，，提高了了数据的的存取速速度；而而OLAP分析构成成了数据据仓库的的表现层层，将数数据仓库库中的数数据通过过不同的的维和指指标，灵灵活地展展现出来来，提高高了数据据的展现现能力，，进而提提高了数数据的分分析能力力。多维数据据集和OLAP多维数据据集多维数据据集（cube）——OLAP中的主要要对象，，通常也也称作多多维立方方体。cube是一个数数据集合合，通常常由数据据仓库的的子集构构造，把把一组维维度和度度量值合合理组织织，最后后汇总成成多维结结构。多维数据据集和OLAP多维数据据集销售数量10000玩具电器销售地区北京上海江苏1234服装化妆品产品时间（月）以时间、销售地区、产品三个维度构成的多维数据集多维数据据集和OLAP的实现以Pubs_DW数据库为为基础，，利用SQLServerAnalysisService开发PubsAS多维数据据库以支支持OLAP分析。Pubs_DW（关系数数据库））FactSalesFactBookAuthorDimPublisherDimBookDimAuthorDimStoreDimDateSSASPubs_AS（多维数数据集））SalesCube（Sales多维数据据集）Sales度量值组组：SalesAmountSalesQtyBookAuthor中间度量量值组维度：BookAuthorStorePublisherDate多维数据据集和OLAP的实现多维数据库的数据源（DS）pubs_DW数据源视图（DSV）多维数据库pubs_AS多维数据集（Cube）度量值和维度聚合SSAS多维数据据库体系系结构多维数据据集和OLAP的实现分析服务务项目开开发步骤骤创建数据据源创建数据据源视图图创建维度度（公共共维度））创建多维维数据集集（度量量值）处理、部部署项目目浏览多维维数据集集多维数据据集和OLAP的实现分析服务务项目开开发要点点每个分析析服务项项目必须须至少包包含一个个数据源源每个分析析服务项项目必须须至少包包含一个个数据源源视图每个分析析服务项项目必须须包含至至少一个个维度和和多维数数据集多维数据据集必须须包含在在一个分分析服务务项目中中每个分析析服务项项目可以以包含多多个多维维数据集集多维数据据集和OLAP多维数据据分析方方法OLAP多维分析析对多维维数据集集中的数数据进行行上卷、下下钻、切切片、切切块、旋旋转等各种分分析操作作，以便便剖析数数据，使使用户从从多个角角度、多多个侧面面、多个个层次来来观察多多维数据据，从而而发掘数数据中蕴蕴涵的对对自己有有用的信信息。多维数据据集和OLAP多维数据据分析方方法多维数据据集和OLAP多维数据据分析方方法——切片切片（Slice）——对多维数数据集的的某个维维选定一一个维成成员，这这种选择择操作就就称为切切片。例如：Cube（维1，维2，…，维i，…，维n，度量量值））对维i选定了了某个个维成成员，，则（维1，维2，…，维i成员，…，维n，度量量值））就是该该Cube在维i上的一一个切切片，，切片片的数数量取取决于于维i上的维维成员员的个个数。。多维数数据集集和OLAP多维数数据分分析方方法——切片“时间＝＝第1季度”在三维维立方方体上上切片片的结结果是是得到到一个个二维维的平平面数数据4.1OLAP概述多维数数据分分析方方法——切块切块（（Dice）——在一个个多维维数据据集中中对两两个及及两个个以上上的维维选定定维成成员的的操作作可以以称为为切块块。例如：：Cube（维1,维2,……,维i,……,维k,……,维n,度量值值）对维i，…，维k，选定定了维维成员员，则则（维1,维2,……,维i成员,……,维k成员,……,维n,度量值值)就是该该Cube在维i,……,维k上的一一个切切块。。显然，，当i=k时，切切块操操作就就退化化成切切片操操作。。多维数数据集集和OLAP多维数数据分分析方方法——切块(贷款＝＝“正常”or“次级”）And（时间间＝“1季度”or“2季度”）And（经济济性质质=“集体“or”个人”or”其它“）切块的的结果果是得得到了了一个个子立立方体体多维数数据集集和OLAP多维数数据分分析方方法——上卷上卷（（Roll-up）——上卷是是对数数据进进行更更为宏宏观的的观察察。通通过在在维的的等级级中上上升或或通过过消除除某个个或某某些维