SQL Server 2005 中的商务智能和数据仓库（1）

　　本文概述了 sql server 2005 beta 2 中“商务智能”平台的增强功能。本文并非实施指南，而是为读者提供了关于“商务智能”平台增强功能的信息。

　　一、简介

　　microsoft sql server 2005 是一个完整的商务智能 (bi) 平台，其中为用户提供了可用于构建典型和创新的分析应用程序所需的各种特性、工具和功能。本文简要介绍了您在构建分析应用程序时将要用到的一些工具，并着重介绍了一些新增功能，这些新增功能使复杂 bi 系统的构建和管理比以往更加轻松。

　　下表概述了商务智能系统的组件，以及与之相应的 microsoft sql server 2000 和 sql server 2005 组件。

　　sql server 2005 新增了两个组件：sql server management studio 和 sql server business intelligence development studio。其他主要的 bi 组件——dts、analysis services olap、analysis services data mining 和 reporting services——在 sql server 2005 中得到了改进，与以前有很大的不同。sql server 2005 关系数据库包含一些重要的新增功能。虽然 microsoft office 查询和门户工具并没有包含在 sql server 中，但当前的发行版本力争在 sql server 2005 之前实现这一功能。office 工具的 bi 功能将在 office 产品发行周期内将得到逐步实现。

　　sql server 2005 business intelligence 工具集提供了一种端到端的 bi 应用程序集成：

　　sql server 2005 business intelligence 组件的主要目标是支持在各种规模的企业中开发和使用商务智能，并使其能够供所有员工使用，不仅包括管理层和分析师，还包括操作人员和外部委托人。就此目标而言，sql server 2005 具有完整、集成、易用的特点，它以 web 服务的形式发布数据，而且仅通过日常硬件便可提供极佳的性能，另外它还包含许多新增功能，您可以使用这些新增功能开发创新的分析应用程序。

　　二、sql server2005 beta 2 入门

　　在安装 sql server 2005 时第一点要注意的就是它的集成安装体验。您不再需要为某些功能（如 analysis services）而分别运行安装程序。如果某个功能（如 reporting services）不可安装，则说明您的计算机不满足该功能的安装要求。您可以查看说明文件，以获得有关功能必要条件的完整讨论。在大多数配置得当的机器上，安装过程中应接受所有默认设置，安装所有的主要功能：

　　reporting services 要求在机器上安装并妥善配置 iis。由于 reporting services 是 2005 business intelligence 功能组的一个重要组成部分，我们强烈建议您花费一定的时间，执行这些配置和安装步骤。

　　熟悉 analysis services 的客户可能会因缺少 analysis services 元数据仓库而感到迷惑。在 sql server 2000 中，analysis services 仓库被作为 microsoft access 数据库发行。analysis services 2005 不包含元数据仓库。相反，analysis services 数据库元数据信息被存储为 xml 文件格式，由 analysis services 进行管理。如果需要，还可以将这些 xml 文件放置在源代码控制之下。

　　我们建议您使用 business intelligence development studio 进行开发，同时使用 sql server management studio 来操作和维护 bi 数据库对象。虽然您能够在 sql server management studio 中设置 dts 包以及 analysis services 多维数据集和数据挖掘模型，但 business intelligence development studio 却为设计和调试 bi 应用程序提供了更好的体验。

　　对于 beta 2 而言，建议您从掌握新的应用程序入手，因为与升级现有 dts 包或 analysis services 数据库相比，这样可以学到更多东西。如果您已有一个可用的包或数据库，您会发现，“重新创建”现有的包或数据会十分有用。在您熟悉了这些新增工具、功能和概念之后，便可试着升级现有对象。

　　许多客户都借助 sql server 工具，使用熟悉的来自一个或多个源系统的商务智能结构来开发新的系统，使用 dts 填充维度关系型数据仓库，然后再用数据仓库来填充 analysis services 数据库。但是，sql server 2005 提供了许多选项，通过消除或淡化不同的组件使其背离了这种一般化设计。

　　三、关系型数据仓库

　　sql server 2005 关系数据库引擎包含一些对数据仓库样式应用程序设计和维护大有帮助的功能。这些功能包括：

　　要想将关系操作报告从事务处理数据库中分离出来，经常采用的一项技术便是维护一台报告服务器。报告服务器对事务处理数据库映像的维护一般都有一定的时间延迟，通常截止到前一天。报告服务器多用于报告功能和数据仓库提取。

　　microsoft sql server 2005 新增了两项功能，使报告服务器的创建和维护过程变得更加简单。sql server 报告服务器的延迟时间与以前相比大大缩短。同时，报告服务器被设计为充当事务处理系统的备选系统。

　　要创建报告服务器，先要创建一个数据库镜像，这是 sql server 2005 的新增功能，它为系统的高可用性提供了一个紧急备用系统。更多信息，请阅读联机丛书的“数据库镜像概念”主题。数据库镜像不能够直接查询，这时第二个新增功能就能派上用场了。

　　在镜像上创建一个数据库视图。数据库视图是数据库在某个时点的只读副本。数据库视图并非数据库的完整副本；极为节省空间。多个数据库视图还是可以同时共存，虽然维护数据库视图会对数据库视图所基于的事务处理数据库产生一定的影响。更多信息，请阅读联机丛书的“了解数据库视图”主题。

　　通过在数据库镜像上创建数据库视图，您可轻松为系统的高可用性创建备用服务器，此服务器还可用作报告服务器，起着双重作用。

　　分区表和分区索引将数据分割到多个水平单元中，以便于将行组映射到单独的分区中。而对数据执行操作（如查询）时，又可以将整个表或索引作为一个整体来执行。

　　分区可以：

　　
在关系型数据仓库中，事实数据表比较适合应用表分区，而按日期范围分区又是最常见的分区策略。

　　正如联机从书的“创建分区表和索引”主题中所描述的，定义分区表可分为三个步骤：

　　多个表可以使用同一个分区方案。

　　本文讨论了事实数据表的“范围”分区，但其目的并非是针对表分区的完整讨论或教程。有兴趣的读者请参阅 sql server 联机丛书。

　　最常用的分区方案是按日期范围（如年、季、月或甚至天）对事实数据表进行分区。在大多数情况下，对大型事实数据表进行日期分区可以提供良好的可管理性收益。为了改善查询性能，应尽量使用相同的分区方案对时间维度表进行分区。

　　许多数据仓库应用程序都力求在越来越小的加载窗口中加载越来越多的数据量。典型的流程是这样的，先从几个源系统中提取数据开始，接下来便是在这些系统间清理、转换、合成和合理化数据。数据管理应用程序被限制为在加载窗口中完成整个提取、转换和加载流程。通常，系统的业务用户都强烈要求将数据仓库查询时的不可用时间降至最低。在设计时，数据管理应用程序的“写入”步骤（即将新数据插入到现有数据仓库的步骤）必须在短时间内完成，且要最小化对用户造成的影响。

　　为了非常快速地加载数据，数据库恢复模型必须为“批量记录”恢复模式或“简单”恢复模式，而数据表必须为空，或是包含数据但不包含索引。如果满足这些条件，不作记录的加载便成为可能。在 sql server 2000 中，分区表出现以前，这些条件通常只在初始历史数据仓库加载中才能满足。一些具有大型数据仓库的客户已通过在分散的物理表上搭建 union all 视图，构建了一个准分区结构；这些数据表都使用不记录技术填充每个加载周期。这一方法并不尽如人意，而 sql server 2005 分区表则提供了更为优秀的功能。

　　在 sql server 2005 中，您不能直接在分区中执行不记录加载。但是，却可以将数据加载到将调用伪分区的单独表中。在特定条件下，您可以用执行速度极快的元数据操作将伪分区切换到分区表中。此技术可满足我们的两个要求：

　　此外，伪分区还可作为单独的表进行备份，从而改善系统的可管理性。

　　许多数据仓库在数据仓库中保留了一个详细活动数据的滑动窗口。例如，事实数据表可能包含三年、五年或十年的数据。每到一个时间周期，便从数据表中删除最旧的数据。持续删除数据的主要原因在于要提高查询性能并最小化存储成本。

　　sql server 2005 分区使大型分区事实数据表中旧数据的删除倍加轻松。如上所述，简单地创建一个空白伪分区，然后将其切换到分区表中。分区表在其曾植入分区的地方有一个空白分区；伪分区在其曾为空白的地方包含数据。用户可以根据需要对伪分区进行适当的备份、截断或删除。

　　或者，您还可以选择重新定义分区函数，将所有空白分区合并到一个分区中。

　　sql server 2005 中有一些很重要的新类型，这些类型对数据仓库大有裨益：

　　许多新分析功能都提供了 transact-sql 中的基本分析功能。这些功能在那些允许用户查询关系数据库，而不是通过 analysis services 排他查询数据的数据仓库中非常有用。另外，在数据中转过程中，这些复杂的计算常被用来开发有价值的数据属性。

　　row_number。返回结果集的连续行号。

　　rank。返回行在结果集中的等级。在通常情况下，rank 值与有序数据集上的 row_number 值相同。但对于那些彼此之间有关联的行来说，则是所有具有相同排序值的行都有相同的等级。而下一个等级则又与 row_number 值相同。换句话说，如果在第一个位置存在双向关联，那么行 1 和行 2 的 rank 就都为 1，而行 3 的 rank 则为 3。不存在 rank 为 2 的行。

　　dense_rank。返回行在结果集中的等级。dense_rank 函数与 rank 相似，只是去除了 rank 函数所留下的空隙。在上面的示例中，行 1 和行 2 的 rank 为 1，而行 3 的 rank 则为 2。

　　ntile。将有序集分成指定数量、大小近似相等的组。

　　在 sql server 2005 beta 2 中还不能使用这些函数。

　　pivot 操作符可以按查询中的中断值旋转结果集，从而使您可以生成交叉数据报告。例如，如果表中在两个不同的行中包含 "actuals" 和 "budgets" 数据，则使用 pivot 操作符将可以生成带有 [actuals] 和 [budgets] 列的交叉数据报告。

　　与之相似，unpivot 操作符可以将一行拆分为若干行。在此示例中，带有 [actuals] 和 [budgets] 列的行集可以被转换为包含这些值的多个行。

　　在以前的 sql server 版本中，用户能够编写复杂的 transact-sql select 语句来旋转数据。pivot 和 unpivot 操作符则为数据旋转提供了更为简单的机制。

　　在许多方案中，“递归查询”都是非常有用的。sql server 2005 中的新增功能令递归查询成为可能，虽然此项功能还不是十分简单易用。

　　递归查询是针对自联接表的查询。自联接表的两个常见示例有保存员工及其经理信息的数据表，和保存材料清单的数据表。在 adventureworks 数据库的 employee 表中对自联接数据表进行了说明。

　　查询自联接表的直接关系通常十分简单，如查询直接向经理报告的员工数量。但是，如果要回答“经理的组织中有多少名员工？”这样一个问题却十分困难。

　　sql server 2005 中的关系数据库功能解决了这一问题，这一功能被称为“递归通用表表达式”。“附录”中包含一个递归查询的示例，该示例回答了以上定义的问题。联机丛书的 "with <common_table_expression>" 主题中包含更多的相关信息。

　　四、提取、转换和加载 (etl)

　　数据转换服务 (dts) 对于 sql server 2005 而言，是一项全新的技术。dts 是 sql server 2000 中很受欢迎的一项功能，但 dts 2005 已被重新设计成企业 etl 平台。dts 为构建企业级 etl 应用程序提供了大量必需的功能，以及非常高的扩展性能。dts 是完全可编程的、嵌入式的、可扩展的——所有这些特性都使它成为理想的 etl 平台。

　　下表总结了 dts 2005 的这些功能。有关 etl 系统开发 dts 适用性更加完整的讨论，请参阅 sql server 联机丛书。

　　dts 2000 用户已经开发出了一套执行复杂操作的技巧。这些技巧，尤其是编写自修改包，在 dts 2005 中不再有用武之地。在 dts 2005 中要使用变量和配置基础结构来编写动态包、不要再试图编写自修改包。

　　配置良好的变量和配置基础结构还可以减少创建复杂子包系统的需求。如果设计完善，单一包便可满足多种需求；例如，单一包可以在多种不同配置中重复使用，以用来在维度数据仓库中加载许多维度表。在 dts 2000 中，一个复杂的 dts 包网络可能包括 50-100 个包；而在 dts 2005 中，一个复杂的网络可能只包含 10 个包。

　　五、analysis services

　　sql server 2000 analysis services 由两个主要的互补功能组成：联机分析处理 (olap) 和数据挖掘。这两个组件在 analysis services 2005 中仍然存在，并且是分析应用程序的基石。

　　analysis services 2005 olap 中的功能改进主要可以归纳为两类改进：

　　构建分析数据库的途径主要有两个：

　　不管采用哪种方法，基本的系统设计都假设使用当前熟悉的、来自一个或多个源的商务智能结构来填充维度关系型数据仓库，然后再用数据仓库来填充 analysis services 数据库。但是，sql server 2005 提供了许多选项，通过消除或淡化不同的组件使其背离了这种常规设计。在下面“统一维度”模型中讨论了一些其他的备选系统。

　　创建 analysis services 数据库的第一种方法最为 sql server 2000 的用户所熟悉。即从任意结构的源数据库开始着手创建数据库：

　　sql server 2005 中可从标准化数据库寻源的能力是对 analysis services 2000 的一大突破，在 analysis services 2000 中，执行此操作需要一个维度结构，此结构或是星型的，或是雪花型的，或是拉伸型的。此功能使您可以轻松地开发具有极低延迟时间的商务智能应用程序。

　　通过直接在事务数据库内构建 analysis services 数据库，而不需要先构建正式的数据仓库，可以用较低的成本，轻松有效地满足许多用户的要求。如果您需要仅对数据执行最低的数据转换、清理和集成便投入使用，则可考虑使用一个 analysis services 数据库来补充或替换现有的关系报告。您可以充分利用 analysis services 的功能和交互性，更好地管理事务系统中的负载。

　　虽然可以直接从事务系统构建和维护 analysis services 数据库，但只有先构建关系型数据仓库才能最好地满足许多企业分析的要求。复杂的数据集成和数据更改管理问题可以通过典型的数据仓库体系结构得到最好的解决，其中 analysis services 数据库充当着查询和分析引擎的角色。

　　构建分析应用程序的第一步就是在 business intelligence development studio 中创建一个新的 analysis services 项目。创建了空项目之后，应当创建一个“数据源”并将其与源数据库建立连接，此源数据库可以是任何受支持的关系数据库管理系统中的数据库。对于 beta 2 版本，建议您将 sql server 2000 或 sql server 2005 关系数据库作为源。

　　“数据源”负责为源数据连接存储信息。“数据源视图”中包含着源数据库表相关子集的信息。此信息不只局限于源数据库中表的物理结构；您还可以添加诸如关系、表和列的友好名称、计算列和命名查询之类的信息。

　　“数据源视图”可以在 bi 项目和 dts 项目之间共享。“数据源视图”很有用处，尤其是在以下几种情况中：

　　您为“数据源视图”设置良好名称和关系所作的投资将换来分析应用程序的轻松开发。

　　创建了“数据源视图”之后，便可以右击“解决方案资源管理器”窗格中的“多维数据集”图标，选择“新建多维数据集”，创建一个多维数据集。您可以启用 intellicube 检测和建议。如果您选择使用 intellicube，则必须决定是否构建一个已为报告经过旋转优化的多维数据集。intellicube 技术会对“数据源视图”中的数据库和数据基数关系进行检查，并按事实数据表、维度表或用于解析多对多关系的维度-事实桥接表来智能呈现表特征。对于 beta 2 版本来说，选择是为旋转还是为报告优化多维数据集和维度存在一些微小的差别。唯一的差别就是 intellicube 是否会尝试在维度属性之间创建层次关系。由于层次易于创建，也易于毁坏，因此无须担心会花费太多时间和精力。

　　建议您在此“多维数据集向导”的初始屏幕后立即点击“完成”按钮。这样会一次定义好所需的 analysis services 数据库、维度、层次、属性和多维数据集。您可以对此设计进行编辑，但通常情况下，仔细一点儿走完向导，并在过程中作出一些明智的选择会更加有效。

　　实施完“多维数据集向导”之后，您可能会发现您更喜欢用“维度向导”来逐一地创建复杂的维度，要启动“维度向导”，只需在“解决方案资源管理器”窗格中右击“维度”即可。仔细定义完大型维度（例如“产品”、“客户”和“时间”）后，启动“多维数据集向导”，并确保在适当的位置包括这些预定义的维度。

　　到此为止，前面执行的这些步骤已在您的开发机器上以 xml 文件轻松创建了维度和多维数据集定义和结构。business intelligence development studio 和“配置管理器”使您可以对目标服务器上的项目构建和部署过程进行管理。默认情况下，“部署”目标服务器就是您的本地服务器。您可以创建适合其他环境部署的备选配置。项目的主要属性，如目标服务器的名称和数据源连接字符串等，可能会因配置而不同。

　　要在开发循环过程中预览和测试多维数据集和维度，请从 business intelligence development studio 的菜单中选择“部署”，在指定的目标服务器上构建和部署项目。或者，单击 f5，或选择“调试”（位于 business intelligence development studio 主菜单中）。这样会启动几个调试和浏览工具中的一个，具体启动哪个，要取决于您所执行的操作以及您选择“部署”的时间。根据此上下文，“部署”过程会启动多维数据集浏览器、mdx 脚本调试器或 kpi 浏览器。

　　您可能想在定义完系统的维度、度量值和多维数据集后查看一下系统原型。请使用相对较少的数据针对开发数据库进行处理，以验证数据和结构的行为是否与预期的行为相一致。

　　作为原型的一部分，您可能想设计一些更为复杂的“analysis services 数据库”、“关键绩效指标”、“操作”和“计算”组件。如果您的数据库是被对不同数据视图感兴趣的不同用户团体使用的话，请深入查看“透视”和备选的安全计划。如果您计划部署可供国际上不同语言的用户使用的数据库，则可以使用“翻译”功能引入本地化项目名称。最终，原型会评估备选的物理配置，例如“分区”和不同的“主动缓存”选项。

　　在 analysis service 数据库开发完成之后，便可以部署数据库对象，以便于进行最终测试、临时过渡并投入生产服务器。在构建阶段的项目输出可以用作 analysis services 部署实用工具的输入。此实用工具可以帮助您部署和处理数据库。

　　我们刚刚描述了从已知源创建自定义 analysis services 数据库的基本步骤。这种通过“多维数据集向导”和“维度向导”创建的方法与创建 analysis services 2000 数据库的标准方法十分类似。

　　创建 2005 分析应用程序的另外一种备选方法就是选择“多维数据集向导”第二个屏幕上的“在不具备数据源的前提下设计商务智能模型”选项。这种通过向导创建的方法与 sql server 2000 accelerator for business intelligence 的设计体验十分类似。这种设计体验会从模板生成一个完全可自定义的应用程序，此处的模板：具有丰富的维度结构和分析功能，还有可能包括一个关系型数据仓库和 dts 包。microsoft、集成商或独立软件供应商都可以提供这种模板。

　　不管采用哪种通过向导创建的方法，是从源数据库创建，还是从模板创建，都可以设计相同的 analysis services 数据库。第一种选项假设您将创建一个完全自定义的系统。对象名称和结构都是可以完全自定义的，初始设计是受源数据库中的名称和结构所驱动的。模板选项也可以创建一个完全自定义的数据库，但是初始设计是受专家主题区域模板所驱动的。

　　许多用户都喜欢将这两种方法结合使用。一个非常常见的方法就是用现有源创建 analysis services 数据库中的大部分内容，而用模板法生成“时间”维度。

　　analysis services 2005 使关系数据库与多维度 olap 数据库之间的界线变得更加模糊。olap 数据库分析应用程序一直以来都具有着巨大的优势，这些优势主要体现在以下几个方面：

　　不过，在实现这些功能的同时也带来了一定的负面效应。到目前为止，已经发现的问题就有 olap 数据库（包括 analysis services 2000 在内）很难交付以下内容：

　　通过将传统 olap 分析与关系报告二者的优点相结合，analysis services 2005 能够提供一个可以同时覆盖这两方面需求的统一维度模型。在 sql server 2005 中定义的一套多维数据集和维度被称为统一维度模型 (unified dimensional model)，或 udm。udm 的优势和灵活性引发了设计领域的巨变。过去，bi 架构师会权衡备选基础结构的收益和成本，并在关系数据库和 olap 数据库之间作出选择。现在，架构师可以设计一个“统一维度模型”，然后从传统极限中确定一点用于放置 analysis services 系统逻辑设计和物理配置。

　　analysis services 2005 围绕维度的属性，而非维度的层次构建多维数据集。在 analysis services 2000 中，维度设计由层次主宰，层次的示例有 {年、月、日} 或 {国家、地区、城市}。这些层次要求各层之间存在密切的数据关系。作为成员属性和虚拟维度公开的“属性”是“二等公民”。虽然有可能在物理维度中生成属性，但性能因素却使这一技术的广泛使用大打折扣。熟悉关系结构的用户对 olap 数据库中对层次的过度侧重深感困惑。

　　analysis services 2005 结构与关系型维度结构更为类似。一个维度可包含多个属性，每个属性都可用于切片和筛选查询，同时每个查询又可以合并到层次中，而不必考虑数据的相互关系。

　　有 olap 背景的用户都知道强大的层次结构的价值，有一点您可以肯定，那就是“城市”清晰地汇总为“地区”和“国家”。这种自然层次结构依然存在，并应在适当的位置进行定义：查询性能会因为这种层次结构而得到提高。