6数据仓库开发实例

数据仓库与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）销售额建立多维数据模型最终，确定数据集市包括以下多维数据模型：•主题——图书销售业绩•度量值——1.图书销售量2.图书销售额•维度——1.出版社（国别-州-城市-出版社）2.作者（作者姓名）3.图书（图书类别-单一图书，图书出版年）4.书店（州-市-书店）5.销售日期（年-月-日）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系统能处理任何与用户和应用有关的逻辑分析和统计分析，在需要的情况下还允许用户无需编程就可为分析和生成报表定义一些新的特殊运算，将其作为分析的一部分，并以用户理想的方式给出报告。用户可以在