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

基于SQLServer2005

的数据仓库构建与OLAP分析实验2内容提要一、背景概述基于Northwind数据库实现数据仓库构建与OLAP分析二、准备数据源三、根据业务需求设计数据仓库四、使用IntegrationServices实现数据仓库与ETL五、使用AnalysisService实现数据分析3一背景概述4SQLServer

数据仓库的BI架构数据转换中央数据仓库信息展现业务用户源数据数据获取数据管理数据使用项目管理数据仓库实施指导方法论业务规划信息评估逻辑数据模型设计物理数据模型设计数据库管理元数据管理应用开发顾问咨询知识转移TMIS系统客运系统外部信息MIS系统抽取转化过滤加载随即查询报表多维分析数据挖掘Portal集成RelationalTransformationOLAPDataMining数据集市SQLServer2005直接用户(客户端)Olap用户Web用户应用服务器决策者分析员研究员查询人员一背景概述5一背景概述SSISSSRS报表/随即查询SSASPortalTMIS系统客运系统外部信息RelationalTransformationOLAPDataMiningSQLServer2005MIS系统

多维分析/数据挖掘门户集成ETL工具数据转换中央数据仓库

信息展现应用系统源数据数据获取数据管理数据使用SQLServer

数据仓库的BI架构6一背景概述Employee_DimEmployeeKeyEmployeeID...EmployeeKeyTime_DimTimeKeyTheDate...TimeKeyProduct_DimProductKeyProductIDProduceNameProductBrandProductCategory...ProductKeyCustomer_DimCustomerKeyCustomerID...CustomerKeyShipper_DimShipperKeyShipperID...ShipperKeySales_FactTimeKeyEmployeeKeyProductKeyCustomerKeyShipperKeyUnitsPrice...TimeKeyCustomerKeyShipperKeyProductKeyEmployeeKey多个外键事实维度键数据仓库的星型结构9

设计数据仓库 导入数据仓库

建设Cubes

查询数据1342Cube多维数据集数据仓库源系统客户端查询工具报表分析数据挖掘一背景概述10

设计数据仓库 导入数据仓库

建设Cubes

查询数据1423Cube多维数据集数据仓库源系统客户端查询工具报表分析数据挖掘一背景概述11内容提要一、背景概述基于Northwind数据库实现数据仓库构建与OLAP分析二、准备数据源三、根据业务需求设计数据仓库四、使用IntegrationServices实现数据仓库与ETL五、使用AnalysisService实现数据分析二准备数据源

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

详见“03联机分析处理.ppt”中的数据仓库案例分析包括：了解Northwind数据库明确业务需求（即数据仓库主题）识别事实与维度设计事实表设计维度表在Northwind_DW中创建相应的事实表与维度表（或运行createnorthwinddw.sql即可）15内容提要一、背景概述基于Northwind数据库实现数据仓库构建与OLAP分析二、准备数据源三、根据业务需求设计数据仓库四、使用IntegrationServices实现数据仓库与ETL五、使用AnalysisService实现数据分析16三IntegrationServices实现数据仓库与ETL数据从数据源向目标数据仓库抽取（Extract）、转换（Transform）、装载（Load）的过程构建数据仓库的重要一环，用户从数据源抽取出所需的数据，经过数据清洗，最终按照预先定义好的数据仓库模型，将数据加载到数据仓库中去。抽取：将数据从各种原始的业务系统中读取出来。转换：按照预先设计好的规则将抽取得数据进行转换、清洗，以及处理一些冗余、歧义的数据，使本来异构的数据格式能统一起来。装载：将转换完的数据导入到数据仓库中。17ETL举例导入合并计算查找二IntegrationServices实现数据仓库与ETL使用SQLServer图形设计界面来创建SSIS包

SQLServer商务智能开发平台是一个图形工具，它使创建和编辑SSIS包的工作变得更加简单和轻松，而且它提供了比导入，导出向导更为强大的功能。可以向SSIS包中添加控制流、数据流任务和事件处理程序。SQLServer2005集成服务包（SSIS包）中的控制流由不同类型的控制流元素构造而成：容器、任务和优先约束。容器提供包中的结构并给任务提供服务，任务在包中提供功能，优先约束将容器和任务连接成一个控制流。SQLServer2005SSIS包中的数据流由下列不同类型的数据流元素构造而成：提取数据的源、修改和聚合数据的转换、加载数据的目标以及将数据流组件的输出和输入连接为数据流的路径。事件处理程序与包类似。事件处理程序可以像包一样为变量提供作用域，并且包含控制流和可选数据流。使用SQLServer图形设计界面来创建SSIS包

1.创建包创建SSIS包的步骤如下：（1）打开SQLServerBusinessIntelligenceDevelopmentStudio，使用菜单项文件—>新建—>项目，新建IntegrationServices项目，将名称修改为“订单数据集成”，并选择项目保存位置，创建同名解决方案。使用SQLServer图形设计界面来创建SSIS包

1.创建包（2）可使用菜单视图—>解决方案资源管理器，查看已经创建的解决方案。右键删除SSIS包中的默认包，并右键创建新包“NorthwindtoDW”“创建包”对话框

使用SQLServer图形设计界面来创建SSIS包

1.创建包（3）在集成服务项目对话框中，可以向包中添加控制流、数据流任务和事件处理程序。如控制流设计器用来创建包中的控制流。工具箱的“控制流项”节点列出多种类型的任务和容器“创建包”对话框

使用SQLServer图形设计界面来创建SSIS包

2.定义和设置数据转换任务（1）创建源数据连接：右键单击连接管理器区域中的任意位置，再单击“新建OLEDB连接”。在“配置OLEDB连接管理器”对话框中，单击“新建”。在“服务器名称”中，输入本地服务器名称或输入localhost，在“配置OLEDB连接管理器”对话框的“数据连接”窗格中，确认选择了相应的服务器及数据库Northwind“SQLServer连接管理器”对话框

配置OLEDB连接管理器窗口使用SQLServer图形设计界面来创建SSIS包

2.定义和设置数据转换任务（2）创建目标数据连接：右键单击连接管理器区域中的任意位置，再单击“新建OLEDB连接”。在“配置OLEDB连接管理器”对话框中，单击“新建”。在“服务器名称”中，输入本地服务器名称或输入localhost，在“配置OLEDB连接管理器”对话框的“数据连接”窗格中，确认选择了相应的服务器及数据库Northwind_DW

“SQLServer连接管理器”对话框

配置OLEDB连接管理器窗口使用SQLServer图形设计界面来创建SSIS包

2.定义和设置数据转换任务（3）为源数据和目标数据创建了连接管理器后，

下一个任务是在包中添加一个数据流任务。具体如下：

向包中添加一个数据流源并对其进行配置（必选）。

向包中添加一个数据流转换并对其进行配置（可选）。

向包中添加一个数据流目标并对其进行配置（必选）。

则数据流任务可表示为下图所示。

使用SQLServer图形设计界面来创建SSIS包

2.定义和设置数据转换任务（3）为源数据和目标数据创建了连接管理器后，一个数据流任务完成对一张表/视图的转换操作完成对多张表的转换操作需要多个数据流任务可在控制流中创建多个数据流任务，如下图所示双击控制流中的某个数据流任务，可进入对应的数据流任务设计窗口

使用SQLServer图形设计界面来创建SSIS包

2.定义和设置数据转换任务（4）对包进行调试和运行。若执行控制流中的单个任务，则选中该任务，右键，执行任务若运行整个包，则选中解决方案资源管理器中的SSIS包，右键，执行包任务；或点击工具栏中的绿色三角形按钮进行调试运行，运行成功则控制流中的任务显绿色，失败显红色，正在运行显黄色使用SQLServer图形设计界面来创建SSIS包

3.定义和设置多任务

如果包中包含两个或更多任务，则可以通过将它们的连接线从一项拖动到其他项而将它们连接成控制流。两个项之间的连接器表示优先约束。优先约束定义了两个连接项之间的关系。它指定了运行时任务的执行顺序以及任务的运行条件。例如，优先约束可以指定某任务必须成功，才能运行控制流中的下一个任务。如果在前面的数据转换任务成功完成后，需要完成另一个任务“执行T-SQL语句”，则在控制流界面中，可将这两个任务连接成控制流，。使用SQLServer图形设计界面来创建SSIS包

3.定义和设置多任务

双击两个任务之间的连接线，打开“优先约束编辑器”窗口。在此窗口中可定义两个连接项之间的关系，例如前一个任务执行成功后，可执行后一个任务。双击“执行T-SQL语句”任务图标，可编辑要执行的T-SQL语句。点击“确定”按钮完成编辑。“优先约束编辑器”窗口编辑“执行T-SQL语句”任务窗口使用SQLServer图形设计界面来创建SSIS包

4.实验任务要求

可在控制流设计中：首先，创建一个“执行T-SQL语句”任务，将任务重命名为“createTables”；其T-SQL语句，即为创建Northwind_DW中的各个表(如果数据库中已创建表，这一步就不做)其次，创建一系列的针对维表的数据流转换任务，用于将northwind中的相应数据抽取到northwind_DW相应维表中。最后，创建一个针对事实表的数据流流转换任务，用于将northwind中的相应数据抽取到northwind_DW相应事实中。按照任务创建次序，顺序连接已创建的各个任务调试并执行整个包使用SQLServer图形设计界面来创建SSIS包

4.实验任务要求

实验结果：31内容提要一、背景概述基于Northwind数据库实现数据仓库构建与OLAP分析二、准备数据源三、根据业务需求设计数据仓库四、使用IntegrationServices实现数据仓库与ETL五、使用AnalysisService实现数据分析32Cube多维数据集源系统客户端设计数据仓库

导入数据仓库 建设Cubes

查询数据134查询工具报表分析数据挖掘2使用AnalysisService实现数据分析33“嘿…2003年4月份我在北京卖掉了价值十万美元的可乐”

RegionWSN维度的层次概念：

产品

地域

时间

类别国家年产品名称省月

市日

$10万使用AnalysisService实现数据分析使用AnalysisService实现数据分析在MicrosoftSQLServer2005AnalysisServices（SSAS）中，可以方便地创建复杂的联机分析处理（OLAP）和数据挖掘解决方案。AnalysisServices工具提供了设计、创建和管理来自数据仓库的多维数据集和数据挖掘模型的功能，还提供对OLAP数据和挖掘数据的客户端访问。35使用SQLServer图形设计界面实现分析服务

1.创建AnalysisServices项目步骤如下：（1）打开SQLServerBusinessIntelligenceDevelopmentStudio，使用菜单项文件—>新建—>项目，新建AnalysisServices项目，将名称修改为“订单分析”，并选择项目保存位置，创建同名解决方案。一、创建数据仓库36使用SQLServer图形设计界面实现分析服务

1.创建AnalysisServices项目步骤如下：（2）使用菜单视图—>解决方案资源管理器，查看已经创建的解决方案。一、创建数据仓库37使用SQLServer图形设计界面实现分析服务

2.创建数据源

在解决方案资源管理器中，选择订单分析项目下的数据源，右键，选择“新建数据源”。按数据源向导选择数据源。在选择如何连接数据源中，选择“基于现有连接或新连接创建数据源”，单击新建按钮，在打开的连接管理器窗口，选择数据库服务器名与数据库（Northwind_DW）在下一步账户选择中，选择“使用服务账户”一、创建数据仓库38使用SQLServer图形设计界面实现分析服务

3.创建数据源视图

在解决方案资源管理器中，选择订单分析项目下的数据源视图，右键，选择“新建数据源视图”。按数据源视图向导选择相应表。

一、创建数据仓库39使用SQLServer图形设计界面实现分析服务

4.创建多维数据集

在解决方案资源管理器中，选择订单分析项目下的多维数据集，右键，选择“新建多维数据集”。按向导选择相应的事实表，事实表的度量字段，维表。得到如下所示多维数据集结构

一、创建数据仓库40使用SQLServer图形设计界面实现分析服务

5.创建维度、指定属性与层次选定某一某一维度，编辑维度，包括指定维度属性，设置维度属性关系，构建用户自定义层次等右键，新建维度，如创建时间维度表新建度量值，编辑度量值的聚集/计算方式一、创建数据仓库41使用SQLServer图形设计界面实现分析服务

维度属性在”维度结构”选项卡中看属性在”浏览”选项卡中看属性成员维度属性成员用户自定义层次结构属性5.创建维度、指定属性与层次在进入某一维度的维度结构视图后，可在“维度结构”项查看属性、自定义用户层次结构，在“属性关系”项查看与设置属性关系，在浏览器项目查看维度各层次维成员一、创建数据仓库42使用SQLServer图形设计界面实现分析服务

5.生成并部署所创建的数据仓库选择菜单中的生成——生成“订单分析”数据仓库——部署“订单分析”数据仓库一、创