数据仓库构建与OLA实验教材

基于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举例导入合并计算查找二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.生成并并部署署所创创建的的数据据仓库库选择菜菜单中中的生生成———生生成““订单单分析析”数数据仓仓库———部部署““订单单分析析”数数据仓仓库一、、创创建建数数据据仓仓库库43使用用SQLServer图形形设设计计界界面面实现现分分析析服服务务1.打开开已已经经创创建建的的数数据据源源视视图图，，右右键键““sale””表表，，在在弹弹出出的的快快捷捷菜菜单单中中选选择择““浏浏览览数数据据””命命令令，，即即可可对对数数据据进进行行浏浏览览,下下图图为为浏浏览览原原始始数数据据表表二、、OlAP实实现现44使用用SQLServer图形形设设计计界界面面实现现分分析析服服务务2.选择择““透透视视表表””选选项项卡卡，，可可选选择择需需要要的的维维度度对对原原始始数数据据进进行行分分析析，，如如分分““订订单单时时间间””和和““客客户户””维维度度进进行行分分析析二、、OlAP实实现现453.选择择““图图表表””选选项项卡卡，，可可选选择择需需要要的的维维度度对对原原始始数数据据进进行行分分析析，