补充知识数据仓库设计与使用 教学要点本章内容主要包括数据仓库的概念，数据仓库的设计方法，数据仓库的数据提取与加载方法，在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. 修饰维度表维度表包含描述事实数据表中的事实记录的特性。在维度表中，每个表都包含独立于其它维度的事实特性。例如，客户维度表包含有关客户的数据，产品维度表包含有关产品的信息，而商店维度表包含有关商店的信息。通过维度建模产生维度表架构，在系统中创建维度