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

基于SQLServer2005的数据仓库构建与OLAP分析实验2内容提要一、背景概述基于Northwind数据库实现数据仓库构建与OLAP分析二、准备数据源三、根据业务需求设计数据仓库四、使用IntegrationServices实现数据仓库与ETL五、使用AnalysisService实现数据分析3一背景概述2005年底，微软公司正式推出SQLServer2000的后继产品SQLServer2005。与前一代相比，SQLServer2005不仅提供了更加优秀的数据库管理功能，而且提供了一套完整的数据仓库和数据挖掘技术的解决方案。SQLServer2005负责底层的数据库和数据仓库管理，SQLServer2005集成服务(SSIS)负责数据的抽取、转换和装载(ETL)SQLServer2005分析服务负责OLAP分析和数据挖掘，SQLServer2005报表服务(SSRS)负责前端展示。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多个外键事实维度键数据仓库的星型结构7Cube多维数据集源系统客户端设计数据仓库导入数据仓库建设Cubes查询数据134查询工具报表分析数据挖掘2数据仓库一背景概述8设计数据仓库导入数据仓库建设Cubes查询数据1342Cube多维数据集数据仓库源系统客户端查询工具报表分析数据挖掘一背景概述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举例FNameLNameUnitPriceQtyBarrAdam.552ChaiSean1.13…EmpKeyName1Barr.Adam2Chai.Sean…NameUnitPriceQtyBarr.Adam.552Chai.Sean1.13…NameTotalSalesBarr.Adam1.1Chai.Sean3.3…EmpKeyTotalSales11.123.3…导入合并计算查找二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相应事实中。按照任务创建