基于SQLServer2005的数据仓库构建及OLAP多维分析实验.ppt

1、基于SQL Server 2005的数据仓库构建与OLAP分析实验,2,内容提要,一、背景概述 基于Northwind数据库实现数据仓库构建与OLAP分析 二、准备数据源 三、根据业务需求设计数据仓库 四、使用Integration Services实现数据仓库与ETL 五、使用Analysis Service实现数据分析,3,一 背景概述,2005年底，微软公司正式推出SQLServer 2000的后继产品SQLServer 2005。与前一代相比，SQLServer 2005不仅提供了更加优秀的数据库管理功能，而且提供了一套完整的数据仓库和数据挖掘技术的解决方案。 SQLServer 20

2、05负责底层的数据库和数据仓库管理， SQL Server 2005集成服务(SSIS)负责数据的抽取、转换和装载(ETL) SQLServer 2005分析服务负责OLAP分析和数据挖掘， SQLServer 2005报表服务(SSRS)负责前端展示。,4,SQLServer 数据仓库的BI架构,一 背景概述,5,一 背景概述,SSIS,SSRS 报表/ 随即查询,SSAS,Portal,TMIS系统,客运系统,外部信息,SQLServer2005,MIS系统,多维分析/ 数据挖掘,门户集成,ETL工具,SQLServer 数据仓库的BI架构,6,一 背景概述,Employee_Dim,Em

3、ployeeKey,EmployeeID . . .,EmployeeKey,Time_Dim,TimeKey,TheDate . . .,TimeKey,Product_Dim,ProductKey,ProductID ProduceName ProductBrand ProductCategory . . .,ProductKey,Customer_Dim,CustomerKey,CustomerID . . .,CustomerKey,Shipper_Dim,ShipperKey,ShipperID . . .,ShipperKey,Sales_Fact,TimeKey Employee

4、Key ProductKey CustomerKey ShipperKey,Units Price . . .,TimeKey,CustomerKey,ShipperKey,ProductKey,EmployeeKey,维度键,数据仓库的星型结构,9,设计数据仓库 导入数据仓库 建设 Cubes 查询数据,1,3,4,2,Cube多维数据集,数据仓库,源系统,客户端,查询工具 报表 分析 数据挖掘,一 背景概述,10,设计数据仓库 导入数据仓库 建设 Cubes 查询数据,1,4,2,3,Cube多维数据集,数据仓库,源系统,客户端,查询工具 报表 分析 数据挖掘,一 背景概述,11,内容提要

5、,一、背景概述 基于Northwind数据库实现数据仓库构建与OLAP分析 二、准备数据源 三、根据业务需求设计数据仓库 四、使用Integration Services实现数据仓库与ETL 五、使用Analysis Service实现数据分析,二 准备数据源,打开SqlServer2005 Manegement Studio,查看是否已经存在Northwind数据库 如果不存在，通过数据库任务附加，创建并导入Northwind数据库 （或运行instnwnd.sql即可） 创建新数据库Northwind_DW,13,内容提要,一、背景概述 基于Northwind数据库实现数据仓库构建与OLA

6、P分析 二、准备数据源 三、根据业务需求设计数据仓库 四、使用Integration Services实现数据仓库与ETL 五、使用Analysis Service实现数据分析,二 根据业务需求设计数据仓库,详见“03联机分析处理.ppt”中的数据仓库案例分析 包括：,了解Northwind数据库 明确业务需求（即数据仓库主题） 识别事实与维度 设计事实表 设计维度表 在Northwind_DW中创建相应的事实表与维度表 （或运行createnorthwinddw.sql即可）,15,内容提要,一、背景概述 基于Northwind数据库实现数据仓库构建与OLAP分析 二、准备数据源 三、根据业

7、务需求设计数据仓库 四、使用Integration Services实现数据仓库与ETL 五、使用Analysis Service实现数据分析,16,三 Integration Services实现数据仓库与ETL,数据从数据源向目标数据仓库抽取（Extract）、转换（Transform）、装载（Load）的过程,构建数据仓库的重要一环，用户从数据源抽取出所需的数据，经过数据清洗，最终按照预先定义好的数据仓库模型，将数据加载到数据仓库中去。 抽取：将数据从各种原始的业务系统中读取出来。 转换：按照预先设计好的规则将抽取得数据进行转换、清洗，以及处理一些冗余、歧义的数据，使本来异构的数据格式能

8、统一起来。 装载：将转换完的数据导入到数据仓库中。,17,ETL举例,导入,合并,计算,查找,二 Integration Services实现数据仓库与ETL,使用SQLServer图形设计界面来创建SSIS包,SQL Server商务智能开发平台是一个图形工具，它使创建和编辑SSIS包的工作变得更加简单和轻松，而且它提供了比导入，导出向导更为强大的功能。可以向SSIS包中添加控制流、数据流任务和事件处理程序。 SQL Server 2005集成服务包（SSIS包）中的控制流由不同类型的控制流元素构造而成：容器、任务和优先约束。容器提供包中的结构并给任务提供服务，任务在包中提供功能，优先约束将

9、容器和任务连接成一个控制流。 SQL Server 2005 SSIS包中的数据流由下列不同类型的数据流元素构造而成：提取数据的源、修改和聚合数据的转换、加载数据的目标以及将数据流组件的输出和输入连接为数据流的路径。 事件处理程序与包类似。事件处理程序可以像包一样为变量提供作用域，并且包含控制流和可选数据流。,使用SQLServer图形设计界面来创建SSIS包,1.创建包 创建SSIS包的步骤如下： （1）打开SQL Server Business Intelligence Development Studio，使用菜单项文件新建项目，新建Integration Services 项目，将名称

10、修改为“订单数据集成”，并选择项目保存位置，创建同名解决方案。,使用SQLServer图形设计界面来创建SSIS包,1.创建包 （2）可使用菜单视图解决方案资源管理器，查看已经创建的解决方案。右键删除SSIS包中的默认包，并右键创建新包“NorthwindtoDW”,“创建包”对话框,使用SQLServer图形设计界面来创建SSIS包,1.创建包 （3）在集成服务项目对话框中，可以向包中添加控制流、数据流任务和事件处理程序。如控制流设计器用来创建包中的控制流。工具箱的“控制流项”节点列出多种类型的任务和容器,“创建包”对话框,使用SQLServer图形设计界面来创建SSIS包,2. 定义和设置

11、数据转换任务 （1）创建源数据连接：右键单击连接管理器区域中的任意位置，再单击“新建 OLE DB 连接”。在“配置OLE DB连接管理器”对话框中，单击“新建”。在“服务器名称”中，输入本地服务器名称或输入localhost，在“配置OLE DB连接管理器”对话框的“数据连接”窗格中，确认选择了相应的服务器及数据库Northwind,“SQL Server连接管理器”对话框,配置OLE DB连接管理器窗口,使用SQLServer图形设计界面来创建SSIS包,2. 定义和设置数据转换任务 （2）创建目标数据连接：右键单击连接管理器区域中的任意位置，再单击“新建 OLE DB 连接”。在“配置O

12、LE DB连接管理器”对话框中，单击“新建”。在“服务器名称”中，输入本地服务器名称或输入localhost，在“配置OLE DB连接管理器”对话框的“数据连接”窗格中，确认选择了相应的服务器及数据库Northwind_DW,“SQL Server连接管理器”对话框,配置OLE DB连接管理器窗口,使用SQLServer图形设计界面来创建SSIS包,2. 定义和设置数据转换任务 （3）为源数据和目标数据创建了连接管理器后， 下一个任务是在包中添加一个数据流任务。 具体如下： 向包中添加一个数据流源并对其进行配置（必选）。 向包中添加一个数据流转换并对其进行配置（可选）。 向包中添加一个数据流目

13、标并对其进行配置（必选）。 则数据流任务可表示为下图所示。,使用SQLServer图形设计界面来创建SSIS包,2. 定义和设置数据转换任务 （3）为源数据和目标数据创建了连接管理器后， 一个数据流任务完成对一张表/视图的转换操作 完成对多张表的转换操作需要多个数据流任务 可在控制流中创建多个数据流任务，如下图所示 双击控制流中的某个数据流任务，可进入对应的数据流任务设计窗口,使用SQLServer图形设计界面来创建SSIS包,2. 定义和设置数据转换任务 （4）对包进行调试和运行。 若执行控制流中的单个任务，则选中该任务，右键，执行任务 若运行整个包，则选中解决方案资源管理器中的SSIS包，

14、右键，执行包任务；或点击工具栏中的绿色三角形按钮进行调试运行，运行成功则控制流中的任务显绿色，失败显红色，正在运行显黄色,使用SQLServer图形设计界面来创建SSIS包,3.定义和设置多任务 如果包中包含两个或更多任务，则可以通过将它们的连接线从一项拖动到其他项而将它们连接成控制流。两个项之间的连接器表示优先约束。优先约束定义了两个连接项之间的关系。它指定了运行时任务的执行顺序以及任务的运行条件。例如，优先约束可以指定某任务必须成功，才能运行控制流中的下一个任务。 如果在前面的数据转换任务成功完成后，需要完成另一个任务“执行T-SQL语句”，则在控制流界面中，可将这两个任务连接成控制流，。

15、,使用SQLServer图形设计界面来创建SSIS包,3.定义和设置多任务 双击两个任务之间的连接线，打开“优先约束编辑器”窗口。在此窗口中可定义两个连接项之间的关系，例如前一个任务执行成功后，可执行后一个任务。 双击“执行T-SQL语句”任务图标，可编辑要执行的T-SQL语句。点击“确定”按钮完成编辑。,“优先约束编辑器”窗口,编辑“执行T-SQL语句”任务窗口,使用SQLServer图形设计界面来创建SSIS包,4.实验任务要求,可在控制流设计中： 首先，创建一个“执行T-SQL语句”任务，将任务重命名为“createTables”；其T-SQL语句，即为创建Northwind_DW中的各

16、个表(如果数据库中已创建表，这一步就不做) 其次，创建一系列的针对维表的数据流转换任务，用于将northwind中的相应数据抽取到northwind_DW相应维表中。 最后，创建一个针对事实表的数据流流转换任务，用于将northwind中的相应数据抽取到northwind_DW相应事实中。 按照任务创建次序，顺序连接已创建的各个任务 调试并执行整个包,使用SQLServer图形设计界面来创建SSIS包,4.实验任务要求,实验结果：,31,内容提要,一、背景概述 基于Northwind数据库实现数据仓库构建与OLAP分析 二、准备数据源 三、根据业务需求设计数据仓库 四、使用Integratio

17、n Services实现数据仓库与ETL 五、使用Analysis Service实现数据分析,32,Cube多维数据集,源系统,客户端,设计数据仓库 导入数据仓库建设Cubes 查询数据,1,3,4,查询工具 报表 分析 数据挖掘,2,使用Analysis Service实现数据分析,33,“嘿 2003年4月份我在北京卖掉了价值十万美元的可乐”,维度的层次概念： 产品 地域 时间 类别 国家 年 产品名称 省 月 市 日,$10万,使用Analysis Service实现数据分析,使用Analysis Service实现数据分析,在Microsoft SQL Server 2005 Ana

18、lysis Services（SSAS）中，可以方便地创建复杂的联机分析处理（OLAP）和数据挖掘解决方案。Analysis Services工具提供了设计、创建和管理来自数据仓库的多维数据集和数据挖掘模型的功能，还提供对OLAP数据和挖掘数据的客户端访问。,35,使用SQLServer图形设计界面实现分析服务,1.创建Analysis Services项目 步骤如下： （1）打开SQL Server Business Intelligence Development Studio，使用菜单项文件新建项目，新建Analysis Services 项目，将名称修改为“订单分析”，并选择项目保存位

19、置，创建同名解决方案。,一、创建数据仓库,36,使用SQLServer图形设计界面实现分析服务,1.创建Analysis Services项目 步骤如下： （2）使用菜单视图解决方案资源管理器，查看已经创建的解决方案。,一、创建数据仓库,37,使用SQLServer图形设计界面实现分析服务,2.创建数据源 在解决方案资源管理器中，选择订单分析项目下的数据源，右键，选择“新建数据源”。按数据源向导选择数据源。 在选择如何连接数据源中，选择“基于现有连接或新连接创建数据源”，单击新建按钮，在打开的连接管理器窗口，选择数据库服务器名与数据库（Northwind_DW） 在下一步账户选择中，选择“使用

20、服务账户”,一、创建数据仓库,38,使用SQLServer图形设计界面实现分析服务,3.创建数据源视图 在解决方案资源管理器中，选择订单分析项目下的数据源视图，右键，选择“新建数据源视图”。按数据源视图向导选择相应表。,一、创建数据仓库,39,使用SQLServer图形设计界面实现分析服务,4.创建多维数据集 在解决方案资源管理器中，选择订单分析项目下的多维数据集，右键，选择“新建多维数据集”。 按向导选择相应的事实表，事实表的度量字段，维表。得到如下所示多维数据集结构,一、创建数据仓库,40,使用SQLServer图形设计界面实现分析服务,5.创建维度、指定属性与层次,选定某一某一维度， 编

21、辑维度， 包括指定维度属性， 设置维度属性关系， 构建用户自定义层次等,右键，新建维度， 如创建时间维度表,新建度量值， 编辑度量值的聚集/计算方式,一、创建数据仓库,41,使用SQLServer图形设计界面实现分析服务,维度属性,在”维度结构”选项卡中看属性,在”浏览”选项卡中看属性成员,维度属性成员,用户自定义层次结构属性,5.创建维度、指定属性与层次 在进入某一维度的维度结构视图后，可在“维度结构”项查看属性、自定义用户层次结构，在“属性关系”项查看与设置属性关系，在浏览器项目查看维度各层次维成员,一、创建数据仓库,42,使用SQLServer图形设计界面实现分析服务,5.生成并部署所创建的数据仓库 选择菜单中的生成生成“订单分析”数据仓库部署“订单分析”数据仓库,一、创建数据仓库,43,使用SQLServer图形设计界面实现分析服务,1.打开已