数据仓库与OLAP 广东工业大学 艾丹祥 数据仓库开发实例 案例背景 Pub是美国的一家图书出版公司，希望构建数据 仓库。其中销售部门的需求比较急迫，希望能在 短时间内实现对销售业绩的分析。 根据Pub公司的情况，考虑采用金博尔的DMDW 的开发模式，即先为销售部门设计一个数据 集市，其他部门数据集市的设计可参照此模 型。 收集项目需求 准备：研究选定的业务过程（术语、步骤、度量 方法） 采访：钻研选定的业务过程（理解使其运作所需 的分析、数据模型和技术） 分析：业务过程相关的数据资源 归档 对于Pub公司：选定“销售”业务过程后， 通过采访了解其与销售分析相关的问题， 如通过什么方式销售、有哪些销售地区等 。 收集项目需求 基本业务状况 Pub公司在美国各地均有出版社，出版社负责联系作 者、出版图书，已出版的图书将由各地的代销书店进 行销售。 收集项目需求 业务分析需求 销售部门最关心销售的业绩，主要通过销售额和销售 量进行考核。 数据仓库开发实例 业务分析需求 希望针对每个出版社作图书销售业绩分析。 希望针对每个国家的出版社作图书销售业绩分析。 希望针对每个州的出版社作图书销售业绩分析。 希望针对每城市的出版社作图书销售业绩分析。 希望针对每本图书作销售业绩分析。 希望针对每类图书做销售业绩分析。 希望针对每年出版的图书做销售业绩分析。 希望针对每位作者所编写的图书做销售业绩分析。 希望针对每个书店作图书销售业绩分析。 希望针对每个州的书店作图书销售业绩分析。 希望针对每个城市的书店作图书销售业绩分析。 希望针对年、月、日作销售业绩分析。 数据仓库开发实例 销售业务的总线矩阵 业务实业务实 体 业务过业务过 程出版社 图书书 店 出版日 期 销售XXXX 收集项目需求 数据资源状况 公司的业务数据库为Pubs数据库 收集项目需求 数据资源状况 Pubs中与销售业务有关的表 根据总线矩阵建立模型气泡图 建立多维数据模型 业务实业务实 体 业务过业务过 程出版社图书书店销售日期 销售XXXX 订单 销售日期 图书 书店 出版社 销售日期 建立多维数据模型 订单 销售日期 图书 书店 出版社 销售日期 日期维度的粒度级别通常为一天 日期维度表需要专门生成（如利用Excel工具） 日期维度表中的时间范围根据业务需求决定 建立多维数据模型 销售日期 日期维度至少要包含年、月、日期三个属性 建立多维数据模型 DimDate 属性名称功能描述 FullDate日期 DateName日期名 Month月 MonthNa me 月名 Year年 其中 FullDate、 Month、 MonthName 形成层次。 出版社 建立多维数据模型 订单 销售日期 图书 书店 出版社 出版社 建立多维数据模型 字段名称说说明 pub_id出版社编号 pub_name出版社名 city所在市 state所在州 country所在国 出版社 出版社维度可以直接包含出版社名、市、州、国家等 属性 建立多维数据模型 DimPublisher 属性名称功能描述 pub_id出版社编号 pub_name出版社名称 pub_city城市 pub_state州 pub_count ry 国家 其中 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_nam e 图书名 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 数据仓库工具集 核心数据仓库工具集SQL 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) 源系统数据库源系统数据库 事实表和维度表 度量和维度 前端工具 核心数据仓库 RDBMS SSIS SSAS SSRS ETL过程 利用SQL Server Integration Service 对Pubs数据 库进行抽取和整理，形成Pubs_DW数据仓库。 先将数据从源事务数据中提取到中间数据库 Pubs_Stage 再对中间数据库的数据进行转换并加载到目标关系数 据仓库Pubs_DW ETL过程 Pubs数据库 （业务数据库） Publishers Pub_info Employee Jobs Royched Titles Authors Titleauthor Sales Stores Discounts Pubs_Stage （临时数据库） Publishers Titles Titleauthor Author Sales Stores Date Pubs_DW （数据仓库） FactSales FactBookAuthor DimPublisher DimBook DimAuthor DimStore DimDate DTS SSIS Date.xls ETL过程 利用DTS和SQL Server Integration Service 对 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日期Datetime DateName日期名varchar（20） Month月int MonthNam e 月名varchar（20） Year年int ETL过程 pubs_DW数据仓库中的DimPublisher表 DimPublisher 属性名称功能描述Sqlserver数据类 型 pub_id出版社编号Varchar（4） pub_name出版社名称varchar（40） pub_city城市varchar（20） pub_state州varchar（2） pub_countr y 国家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出版年int ETL过程 pubs_DW数据仓库中的DimAuthor表 DimAuthor 属性名称功能描述Sqlserver数据类 型 au_id作者编号varchar（11） au_name作者名 au_fname + au_lname varchar（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表图