数据仓库开发实例

数据仓库与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时，，切切块块操操作作就就退退化