数据仓库-数据挖掘-XML.docx

补充知识数据仓库设计与使用 教学要点本章内容主要包括数据仓库的概念，数据仓库的设计方法，数据仓库的数据提取与加载方法，在SQL Server平台中创建、访问与操纵数据仓库，以及SQL Server 中的数据挖掘工具与应用等内容。本章既强调了必要的基础知识，又结合具体的系统平台，阐述了在SQL Server 的分析服务器中创建和分析数据仓库的实际技术，做到理论和实际相结合。 要求了解数据仓库和数据挖掘的概念，了解数据仓库的数据提取与加载方法；掌握数据仓库的设计方法，掌握SQL Server 数据仓库开发工具及数据仓库创建技术，掌握SQL Server的决策树分析和聚类分析数据挖掘工具与应用的方法。 建议教学安排：13.1节2学时，13.2节6学时，13.3节4学时，13.4节4学时，13.5节4学时。（资料来源于 SQL SERVER2000实用教程，陈旭东主编，电子工业出版社）进入21世纪，商业活动变得越来越复杂，并且越来越全球化，企业迫切需要大量的信息来支持战略决策。但是传统的数据库技术不能有效地将企业内海量数据转化为有用的战略信息，数据仓库为企业提供了战略信息的系统环境，为商业智能化打下了基础。数据仓库已经成为信息技术的热门领域。SQL Server提供了一套比较完整的创建数据仓库和数据挖掘的解决方案。 13.1 SQL Server 数据仓库开发工具及应用13.1.1 数据仓库的基本概念Inmon(1993)给数据仓库下了如下定义：“数据仓库是面向主题的、综合的、以时间为变量的、非易变性的数据集合，用来支持管理决策”。数据仓库不同于操作型的数据库，操作型的数据库主要用在联机事务处理（OLTP），而数据仓库主要用在联机分析处理（OLAP）和信息分析处理。数据仓库是一种信息系统环境，可以提供企业综合完整的概括，使企业的信息保持一致性，并且能够支持决策处理。13.1.2 数据仓库架构典型的数据仓库架构由一些基本的功能模块组成（参考图13.1 数据仓库的架构图）：最左边是数据源部分；然后是数据提取转换层；中间是数据存储部分，它管理数据仓库的全部数据，并且保存元数据信息；最右边是信息传递部分，包括各种数据仓库信息的显示和分析工具。其中，外部数据源是准备装载到数据仓库的各种各样的数据，包括关系数据库、电子表格、文档等。数据提取转换层（Extraction Transformation Layer, ETL）是从数据源中提取数据并将其转换到仓库服务器中的过程，主要包括数据提取、转换和加载三部分的内容。元数据描述了数据的结构和意义，元数据连接了数据仓库的所有部分，能够为开发者提供数据仓库内容和结构的所有信息，SQL Server提供了比较完善的元数据服务，元数据服务将系统数据库msdb 用作默认知识库（Repository）数据库。数据仓库存储提供分析用的大量历史数据，这些数据的结构和格式适合于各种分析方法。信息传递部分，主要包括报告、查询工具, OLAP分析工具和数据挖掘工具，SQL Server的分析服务提供了比较完善的信息传递工具。图13.1 数据仓库的架构图13.1.3 SQL Server数据仓库开发工具SQL Server有许多应用于数据仓库的工具，为数据仓库的设计、创建、分析和应用提供了完整的解决方案。1. 关系数据库关系数据库不同于数据仓库，关系数据库主要应用于联机事务处理（OLTP），而数据仓库主要应用于联机分析处理（OLAP）和多种分析功能，但是关系数据库是数据仓库最主要的数据源，关系数据库技术是数据仓库设计、结构和维护的基础。 2. 数据转换服务数据仓库应用程序需要将来自许多源的数据转换为聚合在一起的、一致的数据集，这些数据集经过了适当配置可用于数据仓库操作。SQL Server 为这类任务提供了一个强大的工具，即数据转换服务 (DTS)。DTS 可以访问来自各种不同源的数据，并使用内置的自定义转换规范转换这些数据。 3. 复制数据库复制是具有许多用途的强大工具。复制通常用于分发数据和协调联机事务处理系统 (OLTP) 中分布式数据的更新，还可用于数据仓库。一些潜在的数据仓库的复制应用是将数据从中央数据仓库分发到数据集市，以及从数据准备区更新数据仓库数据。4. 分析服务（Analysis Services）数据仓库收集和组织企业数据以支持组织通过分析做出决策。分析服务提供联机分析处理 (OLAP) 技术，用以组织大量的数据仓库数据供客户端工具进行快速分析，并提供先进的数据挖掘技术以分析和发现数据仓库数据内的信息。5. English QueryEnglish Query 通过用英语进行查询，提供对数据仓库数据的访问。English Query 是用于创建客户端应用程序的开发工具，使客户端应用程序可以将英语转换为 SQL 语法以查询关系数据库，或者转换为多维表达式 (MDX) 语法以查询 OLAP 多维数据集。 6. 元数据服务（Meta Data Services）在 SQL Server的多种不同工具中，有许多可将中央知识库的元数据存储在 msdb 系统数据库中。SQL Server 元数据服务提供用于查看这些元数据的浏览器，并提供用于开发自定义元数据应用程序的应用程序接口。对于每个分析服务器，有一个称作"Analysis Services 知识库"的知识库，以便为分析服务器的对象（多维数据集、维度等）存储元数据。可使用迁移知识库向导将该知识库迁移到同一或不同服务器计算机上的 SQL Server (.mdf) 数据库，并可以通过元数据服的元数据浏览器浏览数据仓库的元数据。13.2 SQL Server 数据仓库的创建在着手进行数据仓库的创建前，首先要根据用户的需求和业务流程对目标数据仓库进行精心设计，明确设计的数据仓库目标必须满足用户的最终需求。设计完成后，便可以借助SQL Server的强大的平台创建数据仓库。13.2.1 数据仓库的设计数据仓库的设计必须满足下列要求：综合表达大量用户的经验，不能干扰现有的联机处理系统（OLTP），提供与数据协调一致的中心知识库，快速响应复杂的查询，提供多种多样的强大的分析工具，例如：联机分析处理（OLAP）和数据挖掘。大部分成功的数据仓库都具有下列共同的特征：基于维度模型，保存有历史数据，包括有具体和总揽的数据，能从多种多样的数据来源中提取数据，并且能够保持一致，致力于单一的主题。Ralph Kimball根据数据仓库设计的一般规律，提出了数据仓库设计的九个步骤： 第一步：选择主题的内容 第二步：确定如何表达事实表第三步：识别并确认维度第四步：选择事实第五步：在事实表中存储事先计算的公式第六步：修饰维度表第七步：选择数据库的持续时间第八步：跟踪变化缓慢的维度第九步：决定查询优先级别和查询方式下面根据Ralph Kimball的思想，阐述数据仓库设计的基本要点。1. 构造数据仓库模型对于OLTP系统的逻辑设计一般采用E-R建模方法，而对于数据仓库的逻辑设计需要维度建模方法。维度建模显示出事实表和维度表之间的相互关系，并且允许沿维度的层次结构下钻和上钻。维度模型主要分为星型模型和雪花模型两类，在星型模型中，每个维度表都与中间的事实表有直接联系，这样的模型架构像星形，事实表位于星形的中间，而各个维度表分布在星形的各个角上。在雪花模型中，一个或多个维度表分解为多个表，每个表都有联接到主维度表而不是事实数据表的相关性维度表。雪花模型是一种将星型模型维度表规范化的方法，如果将所有维度表完全规范化，那么将得到一个以事实为中心的雪花模型结构。以销售信息为例，包括一个名为销售的事实数据表，有关销售记录的各种信息可能包括客户、销售人员、销售的时间、销售的产品和产品的产地。这些信息中的每一类都可组织为自己的维度表。客户信息放在客户维度表中，产地信息放在产地维度表中，时间信息放在时间的维度表中，产品信息放在产品维度表中，销售人员信息放在销售人员维度表中。图13.2 星型模型图图13.3雪花模型图星型模型和雪花模型的差异在于它们的物理实现上，由于雪花模型更为规范化，所以更容易维护维度。而星型模型通常简单而有效的查询，因此则更加方便于用户的直接访问。选择何种模型取决于维度自身的特性，雪花模型一般用来构造复杂的维度。最好的方案是为雪花模型构造星型模型的视图。 2. 确定主题根据用户的需求选定数据仓库的主题是非常关键的。在数据仓库中，数据是按主题存储的。例如对于一般的制造企业，销售、发货和存货都是非常重要的商业主题。3. 确定事实表事实表是存储指标的地方。每个数据仓库都包括一个或多个事实数据表。事实表的主要特点是包含数字数据（事实），而这些数字数据可以汇总以提供有关单位运作历史的信息。每个事实表还包括一个由多个部分组成的索引，该索引包含作为外键的相关性维度表的主键，而维度表包含事实记录的特性。事实表不应包含描述性信息，也不应包含数字度量字段以及使事实与维度表中的对应项相关的索引字段之外的任何数据。事实表的列往往就是以之关联的维度表的外键。例如销售事实表的列product_id, customer_id分别是产品维度表和客户维度表的外键。4. 识别并确认维度维度是分类的有组织的层次结构，称为级别，它描述数据仓库事实表中的数据。维度一般描述一个成员的相似集合，用户将基于该集合进行分析，并且维度是多维数据集的基本组件。一般将维度划分为常规维度、虚拟维度、父子维度和数据挖掘维度。从维度表或联接维度表中选择一列或多列。如果选择多列，应使所有列内部相关以便可以将它们的值组织到一个层次结构中。若要定义层次结构，请按照从最常规到最具体的顺序排序列。例如，"时间"维度是由"年"、"季度"、"月"和"日"列创建的。这种方法将产生一个常规维度。从单个维度表中选择两列。其中一列标识维度层次结构的每个组件，另一列标识每个组件的父代。这两列为表中的每一行标识一个父子链接。所有链接组合起来便可确定维度的层次结构。例如，"家族"维度由"个人"列和"父辈"列创建。这种方法将产生一个父子维度。选择另一个维度中的一个或多个成员属性。每个成员属性都基于另一个维度表中的一列。该列包含的值是另一列值的特性。这种方法将产生一个虚拟维度。定义虚拟维度的另一种方法是直接选择另一个维度表中的列。使用这种方法不需要成员属性。选择当在挖掘模型向导中创建 OLAP 挖掘模型时创建维度。可以由挖掘模型分析的结果创建维度，并可创建包含该维度及挖掘模型的源多维数据集的虚拟多维数据集。5. 选择事实事实是包含在事实表中的数字数据，这些数字数据可以汇总以提供有关单位运作历史的信息，需要选择合适的计量单位或指标。星型模型或雪花模型的中心是一个事实表，因此合理地选择事实是数据仓库建模的基础。6. 在事实表中存储事先计算的公式在数据仓库环境中，当数据仓库事实数据表中包含有数十万行时，如果不得不扫描事实数据表以计算答案，则请求某个特定产品系列每周销售总额的查询需花费很长时间。但是，如果用于回答此查询的汇总数据已经预先计算好，则几乎可以立即响应。事先对事实表中的指标做处理，是非常必要的。事实表的指标操作可能是简单的加法，或者计算平均值，甚至是复杂的算法。在事实表中存储事先计算的公式，根据需求预先计算好数据汇总，可以快速响应查询，改进性能。 7. 修饰维度表维度表包含描述事实数据表中的事实记录的特性。在维度表中，每个表都包含独立于其它维度的事实特性。例如，客户维度表包含有关客户的数据，产品维度表包含有关产品的信息，而商店维度表包含有关商店的信息。通过维度建模产生维度表架构，在系统中创建维度表需要注意下列事项： （1）创建代理键 维度表的主键保持稳定很重要。代理键是在数据仓库内部维护的键，而不是从源数据系统中获得的键。为所有维度表的主键创建代理键并使用它们是非常重要的。 （2）保持引用完整性 必须在所有维度表和事实表之间维护引用完整性。每个事实记录都包含与维度表中的主键相关的外键。每个事实记录都必须在与事实表一起使用的每个维度表中有相关记录。 （3）共享维度 数据仓库必须为相似的查询提供一致的信息。保持一致性的一种方法是创建由数据仓库中的所有组件和数据集市共享和使用的维度表。8. 选择数据库的持续时间数据仓库的数据不是用来进行每天的商业交易的，这一点是与OLTP数据的本质区别，数据仓库中的数据是供分析和决策用的。数据仓库中的数据是和时间变化相关的数据，因此可以对过去数据进行分析，也可以对未来进行预测。 OLTP的数据每隔一段时间被存储到数据仓库中，必须根据实际的需要选择存储转移的时间和频率，此外不同类型数据的存储转移的时间和频率也可能不同。例如销售数据每天更新一次，产品属性的变化通常每个星期更新一次。决定数据库应该保留多久的历史数据是数据仓库设计的一个重要环节。9. 跟踪变化缓慢的维度一般情况下，维度表是相当稳定的，绝大部分维度都是不变的，很多维度虽然会变化，但是变化很缓慢，需要对跟踪变化缓慢的维度，并且对维度作修改。针对变化缓慢的维度表有三类修改方法，分别称为第1类修改，第2类修改和第3类修改。第1类修改的方法是用新的值覆盖维度表中的旧数值；属性的旧数值不需要保留；维度表中的键不受影响。第2类修改的方法是在维度表中增加一条新的记录，该记录存有修改后的数值；维度表中可以包含一个有效日期字段；插入新的记录，该记录有一个新的替代键；原来记录的键不受影响。第3类修改的方法是对受影响的属性，维度表中加入“旧的”字段；将“现有”字段值赋值给“旧的”字段；将新的值赋值给“现有”字段；加入一个“现有”有效日期。大多数修改都属于第1类和第2类修改，第1类修改最常见，第2类修改保留了历史数据，第3类修改属于临时性修改。10. 决定查询优先级别和查询方式在数据仓库设计的最后一个阶段是制定数据仓库的信息传递策略，根据用户的需要和用户的水平制定合理的信息传递策略。例如是否需要为用户定制查询和报表？查询频率的估计和查询的优先级别，一般尽量为用户提供多样化的查询/报表和分析工具，以满足多种多样的用户需求。13.2.2 创建数据仓库一旦完成了数据仓库设计后，就可以在分析服务器上建立一个数据库，该数据库充当着定义了想要创建的数据仓库的对象的逻辑存储空间。在SQL Server上创建数据仓库，主要包括下列重要步骤：建立数据库和数据源，创建维，创建多维数据集，创建虚拟多维数据集。下面以SQL Server随机带的数据库范例（FoodMart 2000）为例，阐述创建数据仓库的基本过程。1. 建立数据库和数据源首先为要导入的数据库建立ODBC的系统DSN数据源，然后启动Microsoft SQL Server-à Analysis Services-à Analysis Manager, 在服务器上单击右键，并选择新数据库，在提示栏输入新建数据库名字（例如FoodMart 2000），完成后，在数据库的目录有数据源、多维数据集、共享维度，挖掘模型和数据库角色等项目（如下图13.4的左上部分的控制台根目录）。在数据源的项目上单击右键，选择新数据源，出现数据链接属性的向导（如下图13.4所示），按照向导，选择建立的系统DSN，建立数据源。 图13.4 建立数据源2. 创建维创建维时，需要决定它是共享的还是私有的，私有维不能脱离多维数据集来处理，通常建立的是共享维。维是创建一个维有4个基本步骤：(1). 在数据源中选择一个或多个维表（见图13.5 选择维度）。(2). 指定维的类型。(3). 选择级别。(4). 命名维。对于单表维选择星型结构，多表维选择雪花型结构。建立时间维度，在“选择维度类型”步骤中，选择“时间维度”选项，其他维度，在“选择维度类型”步骤中，选择“标准维度”选项。具体的方法是，在共享维的项目上单击右键，选择新建维度，按向导完成建维。图13.5 选择维度3 创建多维数据集多维数据集是数据的一种多维结构。创建多维数据集主要包含下列5个步骤：选择事实表、量度列和维编辑改进多维数据集的定义创建计算成员（单元）设计聚合添加分区(1) 选择事实表、量度列和维选择事实表：事实表中含有多维数据集，用户要进行分析的数字数据。数据源中的一个表内，为多维数据集选择主事实表（见图13.6 选择事实表）。图13.6 选择事实表选择数字列：选择要用作多维数据集度量值的数字列（见图13.7 选择数字列）。图13.7 选择数字列选择维度：为多维数据集选择维度。可以从以前定义的共享维度（即可供其它多维数据集使用的维度）中选择（见图13.8 选择维度），也可以启动维度向导创建新维度。图13.8 选择维度（2）编辑改进多维数据集的定义如果要编辑改进多维数据集的定义，在 Analysis Manager 树窗格中右击一个要修改的多维数据集，然后单击“编辑”命令，进入编辑器对话框（见图13.9 多维数据集编辑器）。除可以编辑基本的共享维和量度外，还可以添加计算成员、计算单元和私有维。图13.9 多维数据集编辑器（3） 创建计算成员、计算单元可以将多维数据集数据、算术运算符、数字和/或函数组合起来创建自定义度量值或维度成员，这些度量值和维度成员称为计算成员。例如，通过将汇率和现有美元度量值相乘，可以创建将美元转换成马克的计算成员 Marks。然后 Marks 会在一个单独的行或列中显示给最终用户。计算成员的定义将存储起来，而它们的值则只存在于内存中。可以使用计算成员生成器创建计算成员。使用计算单元可以定义一个多维表达式 (MDX) 公式，用来为特定单元组中的每个单元提供一个值。可选择根据逻辑 MDX 语句对单元组中的每个单元进行评估，以决定该公式是否适用于该单元组中的特定单元。与计算成员一样，计算单元的定义也被存储起来。只有当客户端应用程序查询多维数据集时，才评估计算单元的值。例如，可以创建计算单元定义，它从基于当前年份实际值的计算结果提供下一年的预测值，但是仅针对特定的客户和产品。与计算成员不同，计算单元可能影响多维数据集的特定单元而不是一个整个成员；可以只为整个多维数据集中的单个单元创建计算单元定义。可以使用计算单元向导创建计算单元定义。（4）设计存储选项和聚合 聚合是预先计算好的数据汇总，由于在问题提出之前已经准备了答案，聚合可以改进查询响应时间。例如，当数据仓库事实数据表中包含有数十万行时，如果不得不扫描事实数据表以计算答案，则请求某个特定产品系列每周销售总额的查询需花费很长时间。但是，如果用于回答此查询的汇总数据已经预先计算好，则几乎可以立即响应。预先计算汇总数据是 OLAP 技术快速响应时间的基础。使用存储设计向导可以迅速简便地为分区设置存储选项和设计聚合。有三类存储选项：MOLAP，ROLAP，HOLAP。MOLAP是多维 OLAP (MOLAP) 将聚合和分区源数据的一个复本存储到分析服务器计算机中的多维结构中。ROLAP是关系 OLAP (ROLAP) 将聚合存储在一个关系型结构中，并把分区的源数据保留在它现有的关系型结构中。HOLAP是混合 OLAP (HOLAP) 将聚合存储到分析服务器计算机上的多维结构中，并将分区的源数据保留在它现有的关系型结构中。 在设计多维数据集时，必须保持聚合表的存储需要与查询速度和查询性能之间的平衡。能达到此平衡关系的方法有三种： （1）. 设置存储空间大小并确定存储哪个聚合。该方法在存储空间有限时十分有效。（2）. 设置所需的性能提升百分比，使必要的聚合表可以按其所需占用存储空间。（3）. 观察"性能与大小"图表的增长，手工确定最佳平衡关系。（5） 设计分区分区即是进行管理大的多维数据集的强大而灵活的工具。例如，某个包含销售信息的多维数据集可以含有一个存储过去每一年数据的分区和四个针对当年每一季度的分区。在年末这四个针对季度的分区可以合并成一个单独的针对全年的分区。 每个多维数据集都至少有一个用于包含该多维数据集数据的分区；当定义多维数据集时，将自动为其创建一个分区。为多维数据集创建新分区时，该新分区就添加到多维数据集已有的分区集合中。多维数据集反映了其所有分区所包含的组合数据。 可以使用不同的选项组合方式存储分区，这些选项涉及源数据位置、聚合、数据位置、存储模式和聚合设计。这种灵活性使您得以按照需要设计多维数据集的存储策略。可以通过多维数据集下的分区项目，按照向导设计分区。图13.10 存储设计向导4 创建虚拟多维数据集 虚拟多维数据集是一个逻辑多维数据集内多个多维数据集的组合，为多维数据集提供了一个视图，通过限制部分用户查看基础多维数据集时的访问权限，虚拟多维数据集可以提供很有意义的安全性功能。 由于虚拟多维数据集只存储其定义，而不存储其组件多维数据集的数据，因此，它们实际上不需要物理存储空间。 在创建虚拟多维数据集时，需从基础组件多维数据集的维度和度量值的合并集合内选择度量值和维度。最终用户所看到的该虚拟多维数据集是一个多维数据集。 可以通过向导创建虚拟多维数据集，在创建虚拟多维数据集后，必须首先对其进行处理，然后才能通过客户端应用程序进行浏览。处理虚拟多维数据集将建立起指向其基础多维数据集内所指定的维度和度量值的内部链接。13.3 SQL Server 数据仓库访问与操纵13.3.1 多维数据集浏览器 Analysis Manager 提供多维数据集浏览器，使用这个工具可以查看多维数据集中的数据，使您能方便、快捷地访问多维数据集数据。多维数据集浏览器使您得以快速地以平面二维网格格式浏览多维数据。右击已处理过的多维数据集，然后单击"浏览数据"命令，即可显示多维数据集浏览器（见图13.11 多维数据集浏览器）。在浏览多维数据集数据时，可以查看不同的维度，深化到各个成员，以及对维度进行切片。图13.11 多维数据集浏览器13.3.2远程连接访问多维数据集使用该工具使远程客户通过OFFICE2000的EXCEL或ACCESS访问服务器中的多维数据集。在使用之前要求OFFICE加装Microsoft Query工具。以Excel为例，启动 Microsoft Excel， 在“数据”菜单中，单击“数据透视表和数据透视图报表”命令。 单击“外部数据源”，然后单击“下一步”按钮。在向导的第二步，单击“获取数据”。 “选择数据源”对话框打开。单击“OLAP 多维数据集”选项卡。确保选中“<新数据源>”，然后单击“确定”按钮。“创建新数据源”对话框打开，在“请输入数据源名称：” 框中，输入任意名称。在“为您要访问的数据库选定一个 OLAP 供应者”框中，单击“Microsoft OLE DB Provider for OLAP Services 8.0”，单击“连接”。 “多维连接”对话框打开，选择分析服务器，在服务器栏中输入服务器名。这时显示分析服务器中可用的数据库列表。选择需要分析的数据库，然后单击“完成”按钮。 在“创建新数据源”对话框中（见图13.12. 创建新数据源），在“选定包含所需数据的多维数据集：”框中，从清单中选择需要分析的多维数据集，然后单击“确定”按钮。图13.12 创建新数据源返回 Excel 电子表格，在 Excel 电子表格中，可以通过数据透视表方便地分析和展示数据（见图13.13. Excel 电子表格中数据透视表）。图13.13 Excel 电子表格中数据透视表13.3.3 ENGLISH QUERYEnglish Query以英语作为自然语言用于访问由SQL Server 分析服务创建的数据仓库数据库或多维数据集中的数据。English Query是与SQL Server集成的工具，需要独立安装。具体分析过程如下：1. 启动Microsoft English Query, 选择OLAP Project Wizard(见图13.14. 建立项目向导)，命名项目名，打开，2. 进入选择分析服务器的对话框，输入服务器名和选择需要分析的数据库。3. 从数据仓库的列表中选择多维数据集（例如Sales）.4.创建查询模型， 模型是English Query应用的所有信息的集合，包括特定的数据库对象（例如表，域和联结）和语意对象（实体和它们的关系）。可以通过Project Wizard创建（见图13.15. 项目的实体OLAP对象）。5.执行English Query。在菜单栏选择Debug-àStart, 出现“Model Test”对话框，在Query条目中输入English Query语句，按执行按钮，便可显示查询结果。例如需要查询多维数据集中国家的清单，可在Query条目中输入英语句子：list the country，便可显示相关的查询结果（见图13.16 English Query查询结果），非常简单自然。图13.14 建立项目向导图13.15 项目的实体OLAP对象图13.16 English Query查询结果13.3.4 其它1. SQL 查询分析 SQL 查询很复杂，必须具有数据库专业知识才能正确创建。最终用户很少使用结构化查询语言 (SQL) 查询直接访问数据仓库数据。2. 钻取 (drill through) 检索详细数据，多维数据集单元中的数据根据它们进行汇总。3. 自定义应用程序SQL Server及其组件提供了一套丰富的应用程序接口 (API)，可用于开发自定义应用程序以增强和自动化数据仓库管理，或创建适合业务需要的客户端应用程序。13.4 SQL Server 中的数据提取与加载13.4.1数据提取转换层（ETL）数据提取转换层（Extraction Transformation Layer, ETL）是从数据源中提取数据并将其转换到仓库服务器中的过程，主要包括数据提取、转换和加载三部分的内容。具体实施时应该包括下列步骤：1. 创建数据准备区由于实施数据提取转换工作需要创建表和其它数据库对象，因此为了不影响OLTP数据库，一般要建立数据准备区，并可以为数据准备区创建单独的数据库。数据准备区包括传入数据的表、帮助实现代理键的表以及容纳转换数据的表。可能需要其它表协调来自不同数据源的数据；这些表可能包含标识常用实体的交叉引用信息。可能还需要各种临时表执行中间转换。数据准备区的特定设计取决于数据源的多样性、组织数据以装入数据仓库时所需的转换程度以及传入数据的一致性。准备装入数据仓库的数据所在的表与数据仓库中的目标表应具有相同的架构。2. 提取数据数据提取过程的目的是使所有的源数据都具有通用的、一致的格式，以便准备装入数据仓库。提取数据是成功创建数据仓库的关键，需要指定合理的数据提取策略，一般需要事先确认数据的源系统和结构，确定针对每个数据源的提取方法、提取频率、时间序列上的表示方法和异常处理的措施。对于数据仓库，可能需要从不同的系统源提取数据。数据最初在数据仓库创建过程中提取，并且在更新数据仓库的过程中不断地定期提取。如果源数据驻留在单个关系数据库中，则数据提取是简单的操作，但如果源数据驻留在多个异类可操作系统中，则数据提取是复杂的操作。在协调很多格式、数据编码和其它不一致的提取过程中，可以使用 SQL Server 数据转换服务 (DTS) 的强大功能处理复杂的操作。3.数据转换 从源系统提取后，数据应驻留在数据准备区，在这里可以在数据装入数据仓库前完成清理和转换。提取得到的数据是没有经过加工的数据，不能直接装入数据仓库，为了保证数据质量，数据必须要经过清理和转换。 典型的数据转换包括下列步骤： （1）. 合并数据 必须能够将特定项目（例如产品、顾客、职员）的全部相关信息从多个 OLTP 系统合并到一个 OLAP 系统。合并过程必须解决不同 OLTP 系统间的编码差别问题。合并过程还必须能够将各 OLTP 系统中使用不同数据类型存储的数据转换成在某个 OLAP 系统中使用的单一数据类型。您也必须选择 OLTP 系统中的哪些列与 OLAP 系统无关，并将这些列排除在合并过程外。 （2）. 清理数据 清理数据的目的是消除数据的不一致。不同的 OLTP 系统以不同的方式拼写项目，或者合并过程可能没有覆盖以前不知道的拼写错误。 （3）. 聚合数据 OLAP 查询通常需要汇总数据或是已以某种方式聚合的数据。对数据仓库内数据的聚合程度取决于许多设计因素，如 OLAP 查询的速度要求和分析所要求的粒度级。数据转换过程常常包括下列内容： 将多个名称字段组合成一个字段。将日期字段划分成单独的年、月、日字段。将数据从一个表示法映射到另一个，如从 TRUE 到 1 和 FALSE 到 0 或者邮编从数字到文本。将数据从多个表示法映射到单个表示法，如电话号码的常用格式，或不同的信用等级代码到常用的"好、一般、不好"表示法。创建并应用维度表记录的代理键。4. 加载数据 清理数据并将其转换成与数据仓库要求一致的结构后，数据即准备装入数据仓库。将数据装入数据仓库就是在填充将由显示应用程序使用的表。装载数据通常涉及从源可操作系统、数据准备区数据库或数据仓库数据库中的准备区表传输大量的数据。数据装入数据仓库数据库后，验证维度和事实数据表之间的引用完整性，确保所有的记录都与其它表中的适当记录相关。初始装载数据仓库时要填充数据仓库架构中的表，然后验证数据是否准备用于装载。 可以用不同的方法装载数据仓库表，如Transact-SQL、DTS和bcp 实用工具。13.4.2数据提取转换层的应用工具数据转换服务（DTS）是数据提取转换层工作流的最重要的工具，它主要包括下列一些重要的应用：1. 作为提取数据源的重要工具DTS 能从文本文件或 OLE DB 数据源（例如，ORACLE、INFORMIX、SYSBASE、ACCESS、DBASE等其它异质数据库）将数据导入到 SQL Server 中。2. 作为数据转换的重要工具转换数据任务用于在源和目的之间复制数据，并将列级转换选择性地应用于数据。转换数据任务是数据转换服务 (DTS) 中数据抽取引擎的最基本实现。 转换使得在导入和导出进程期间执行复杂的数据验证、数据清理和变换更加容易。对于列数据，可以更改列的类型、大小、小数位数、精度或为空性。3. 作为数据加载的重要工具在数据准备区完成数据提取、清理、转换工作后，通过DTS将数据准备区的数据库迁移到数据仓库。数据提取转换层的应用工具除了数据转换服务 (DTS)工具外，还有SQL Server提供的其它工具，例如复制，Transact-SQL和bcp 实用工具等。 13.5 SQL Server 中的数据挖掘工具与应用13.5.1 数据挖掘的基本概念数据挖掘（Data Mining, DM）是从大量的、不完全的、有噪声的、模糊的、随机的数据中提取其中有用信息和知识的过程。简单来说，数据挖掘是帮助终端用户从大量数据中提取有用信息的过程。数据挖掘的数据源主要是数据仓库和关系数据库。数据挖掘是一门交叉学科涉及数据库、人工智能、数理统计等多学科知识。数据挖掘常用的分析方法有：决策树、分类分析、聚类分析、神经网络、遗传算法、规则推导、关联分析和时间序列分析等多种方法。SQL Server的分析服务提供了建立在数据仓库基础上的数据挖掘应用的接口和常用的数据挖掘技术工具。13.5.2 SQL Server的数据挖掘模型数据挖掘模型是数据挖掘的中央对象。数据挖掘模型是一个虚拟结构，它表示关系或多维数据的分组和预测分析。数据挖掘模型的结构在许多方面与数据库表的结构相似。但是，数据库表代表的是记录的集合（或记录集），而数据挖掘模型将记录的解释表示为规则和模式时，这些统计信息的组成称为事例。数据挖掘模型的结构表示定义数据挖掘模型的事例集，而所存储的数据则表示来自处理事例数据的规则和模式。若要确定数据挖掘模型中每个特性的相对重要性，该模型需经历称为挖掘模型培训的过程。在培训过程中，将向模型提供数据以便进行分析。然后，模型所用的数据挖掘算法以各种方式检查这些培训数据集并进行测试，以便在数据的分类和预测方面得到一些结论，数据挖掘模型可以使用 OLAP 多维数据集或关系数据库的数据进行培训。作为数据挖掘进程的中心，数据挖掘模型算法决定如何分析数据挖掘模型的事例。数据挖掘模型算法提供了用于处理数据挖掘列的分类、分割、关联和分析数据所需的决策制定能力，这些数据挖掘列提供有关事例集的预测、变化或可能性的信息。目前，在SQL Server的分析服务中提供了两类数据挖掘模型，即Microsoft决策树模型和Microsoft聚集模型。13.5.3 决策树分析决策树算法是基于分类概念的算法构造树，此树将基于培训集中的剩余列预测列值。因此，树中的每个节点代表一列的特定事例。具体方法如下： 1.选择需要分析的多维数据集 在 Analysis Manager 树视图中，展开“多维数据集”文件夹，例如在示例数据仓库中选择“Sales”多维数据集。2.建立挖掘模型 执行“新建挖掘模型”命令，选择“Microsoft 决策树”。3.选择事例 确定维度和级别。本例，维度选择Customers 级别选择name。4.选择被预测实体有“源多维数据集的度量值”、“事例级别的成员属性”和其它维度的成员三项选择，在此例，以“事例级别的成员属性”选项，成员属性选择Member Card。5. 选择培训数据在“Customer”维度上，清除“Country”、“State Province”和“City”框（见图13.17 数据挖掘模型向导）。图13.17 数据挖掘模型向导6. 创建维度和虚拟多维数据集可以用OLAP数据的挖掘模型分析结果创建一个新维度，也可以创建一个新的虚拟多维数据集，浏览此虚拟多维数据集以查看数据挖掘分析的结果。7. 命名“模型名称”命名“模型名称”，并且选择“保存并开始处理”，处理完毕后就可以浏览分析结果（见图13.18 决策树分析结果）。图13.18 决策树分析结果13.5.4 聚类分析聚类是按特定的标准将数据归类成组。聚类算法是一种期望方法，它使用迭代完善技术将相似的记录分组到附近区域。具体方法如下：1. 选择需要分析的多维数据集在 Analysis Manager 树视图中，展开“多维数据集”文件夹，例如在示例数据仓库中选择“Sales”多维数据集。2. 建立挖掘模型执行“新建挖掘模型”命令，选择Microsoft 聚集”。3. 选择事例确定维度和级别。本例，维度选择Customers 级别选择name。4. 选择培训数据在“Customer”维度上，清除“Country”、“State Province”和“City”框。5. 命名“模型名称”命名“模型名称”，并且选