Project REAL：业务智能 ETL 设计实施策略
SQL Server 技术文章
技术审阅：Donald Farmer、Grant Dickinson
合作伙伴：Intellinet
适用于：SQL Server 2005
摘要：了解 SQL Server 2005 Integration Services (SSIS) 的使用。在称为 Project REAL 的业务智能引用实现中，SSIS 展示了一个真实的大量提取、转换和加载 (ETL) 过程。此 ETL 解决方案支持几个 TB 的数据仓库，包含大型仓库的典型数据处理、配置和管理机制。
本页内容
 |
简介 |
|
数据配置文件 |
|
SSIS 开发环境 |
简介
成功的业务智能 (BI) 应用程序需要可靠的工具来运行。如果开发人员和管理员具备有关如何执行成功实现方面的有关知识基础（也就是最佳实施策略信息），也会有助于创建这些应用程序。Microsoft 与其多个合作伙伴通过 Project REAL 为基于 Microsoft(R) SQL Server(TM) 2005 的 BI 应用程序找到最佳实施策略。在 Project REAL 中，通过创建基于真实客户方案的引用实现来研究最佳实施策略。这意味着客户数据将被带到企业内部，并用于解决这些客户在部署过程中遇到的相同问题。这些问题包括：
| • |
架构设计 - 关系架构和 Analysis Services 中使用的架构。 |
| • |
数据提取、转换和加载 (ETL) 过程的实现。 |
| • |
客户前端系统的设计和部署（用于报告和交互分析）。 |
| • |
生产系统大小的调整。 |
| • |
系统的持续管理和维护，包括对数据的增量更新。 |
通过使用真实的部署方案，我们能全面了解如何使用这些工具。我们的目标是解决大型公司在进行自身真实部署过程中会遇到的各种问题。
本文主要讨论 Project REAL 的 SQL Server Integration Services (SSIS) 提取、转换和加载 (ETL) 设计。此设计基于 Barnes & Noble 的 ETL 体系结构 - 完全使用 SSIS 构建并且是 SSIS 的第一个 ETL 产品实现。由于此解决方案不是在数据转换服务 (DTS) 或其他 ETL 工具基础上的升级设计，因此所采用的许多方法不同于 DTS 中的典型 ETL 体系结构。此解决方案的目的是利用 SSIS 的全新应用程序体系结构，以另外一种角度思考并设计一种 ETL 过程，使其成为常规的 ETL 设计最佳实施策略的一个模型。在本白皮书中，我们将说明每一个方案的设计决策和 Project REAL SSIS 处理过程的实现细节。
有关 Project REAL 的概述，请参阅名为 Project REAL：技术概述的白皮书。Project REAL 在存续期间将导致大量论文、工具和示例的产生。要查看最新的信息，请退回到以下站点：
http://www.microsoft.com/sql/bi/ProjectREAL
Project REAL 是 Microsoft 和 BI 领域的众多合作伙伴之间的协作尝试。这些合作伙伴包括：Apollo Data Technologies、EMC、Intellinet、Panorama、Proclarity、Scalability Experts 和 Unisys。Project REAL 的业务方案和源数据集由 Barnes & Noble 友情提供。
|
注意：本白皮书是初稿，其中包含我们根据使用 SQL Server 2005 的早期社区技术预览 (CTP) 版本的经验推荐的最佳实施策略。本白皮书在发布时是准确的。本文档中的产品功能可能会改变，将来可能会开发出更好的实施策略信息。 |
Project REAL ETL 目标
为了支持报告和分析要求，任何业务智能 (BI) 系统中都存在 ETL 处理。需要按此支持功能实现 ETL。这没有降低 ETL 的重要功能，因为要报告的数据将通过 ETL 处理过程直接进行处理。ETL 要考虑该处理过程的计时、性能和准确性等方面；ETL 设计的支持、管理、灵活性和可扩展性也非常重要。真实系统通常出现一些影响 ETL 的未知情况和异常情况。这就要求 ETL 处理过程能够轻松处理各种变化并为稳定系统的最终目标服务。
对于 Project REAL，这些关键方面使得 ETL 设计需要实现以下几个主要目标：
| • |
ETL 管理。为了提供管理支持，实现了可以跟踪和报告 ETL 元数据的设计。这为用户进行参考和故障排除提供了清晰的处理过程状态，有助于隔离问题和解决问题。 |
| • |
动态配置。开发这项功能是为了在发布和分发核心组件时支持企业系统。包含根据企业和技术要求变化以及大型支持开发团队的相应环境来考虑设计的适应性。 |
| • |
平台集成。这涉及到设计一种与 BI 解决方案的多个层次进行交互的解决方案。包括安全性、基础结构、关系和 OLAP 结构、获取数据的报告和分析工具。 |
| • |
性能。鉴于数据仓库中处理和管理的数据量，注意性能问题对于 Project REAL 解决方案来说非常重要。数据总共可高达数 TB。 |
内容提要
本文主要介绍几种特定的设计原则以及从设计过程获得的一些经验教训，并说明解决方案的整体体系结构。文中提供了详细参考并包含解决方案的一些设计要点。随着新解决方案的成熟和在 SQL Server 2005 平台上的开发，将会发布更详细的、更全面的信息。以后的文章将扩充概念，提供优化的性能设计，并可能演示一些更好的设计示例。本文提供一个可靠的基于 SSIS 的 BI ETL 引用。在规划和开发 ETL 的重新设计、升级和新的实现过程中，BI 体系结构设计师可以使用该工具。
SSIS 的功能远不止仅仅处理 ETL - 它具备系统集成、信息管理和数据转换等众多其他功能。本文只讨论该产品的几个方面，涉及与 ETL 处理过程有关的几个 SSIS 核心部分。
概述
本文中的示例与 Project REAL 实现直接相关。每个示例都是经过挑选的，分别强调 SSIS 在应用到 ETL 处理时的特定方面。这些示例演示了前文介绍的一部分目标和以下常见的 ETL 方案：
| • |
SSIS 开发环境 |
| • |
ETL 审核和记录 |
| • |
用于属性和数据源管理的动态配置设计 |
| • |
标准和独特方案的维度处理 |
| • |
维度关联的事实数据表处理和事实数据表更新 |
| • |
数据处理体系结构设计 |
| • |
数据处理优化技术 |
Project REAL SSIS 解决方案中实现了所有这些示例，而且该解决方案已用 90 天的每日和每周生产数据成功运行。处理的数据包括一部分假日零售高峰时的数据，数据时间跨度达两年，以证明此数据作为一个真实示例的稳定性和可靠性。就像前面提到的那样，Barnes & Noble 从 2004 年 11 月就已经在生产 Project REAL ETL 所基于的这个“真实”的 SSIS 解决方案了。在这些示例和数据包中，许多可以或将可以用来仔细审阅实现的特定方面。将来会在 Project REAL 网站上发布此类信息。另外，将在行业会议上提供这些数据包以演示 Project REAL SSIS 设计。
数据配置文件
尽管此引用项目是以一个零售系统为核心的，Project REAL ETL 还是代表了许多业务方案的提取要求。对于 ETL 处理来说，通常存在一些日常处理要求，每天都需要将对源数据的更改和添加提取并处理到系统中去。而且，每周都运行一组过程来管理维度模型的每周库存快照。数据配置文件包含每日事务提取、维度更新和每周库存事实数据表管理。
事实数据表
关系模型是一种标准的星型架构设计，具有两类主要的事实数据表：销售和库存。销售事务每天收集，代表所有零售商店的详细产品购买（包括网络订单）。处理的数百万事务需要添加到销售事实数据结构中，多数销售记录来自前一天的销售。另外，有一小部分销售对于系统来说是新的，但却是迟到的历史销售。所有的销售每天都被跟踪，ETL 过程设计成允许对整天的数据进行多重处理。跟踪的主要数据围绕各个项目事务的数量和销售量。
库存结构设计成一个以周为周期的标准快照事实数据表，库存位置每天更新，但随着每周增量进行历史管理。在商店和分发中心按项目对库存进行跟踪，每周都会产生许多行，需要每天进行很多更改。一个主要的报告目标是了解库存和销售趋势，防止出现脱销的情况。因此，除了标准的“手头”数量，还跟踪“库存天数”事实数据，该信息以周为周期说明某项物品在商店或分发中心库存的天数。一周结束时，库存级别被复制并为新一周的开始进行初始化，它是 ETL 需要进行的密集过程。
维度表
支持事实数据表的维度具有一些独特的特性，使设计变得有趣，而且突出了 SSIS 的不同方面。产品维度具有数百万成员，包含标准的更改属性和历史属性，但要求只有在销售后才可以开始进行历史跟踪属性和层次结构更改。本文中讨论了产品维度是如何影响 ETL 的。除了产品维度外，还涉及到其他几种典型的零售维度。由于源数据的管理方式，所有维度要求如果某个维度成员在事实数据处理过程中丢失，将向该维度添加一个具有相关业务键的占位符记录，直到全部维度源可以进行完整更新。这称为“推断成员”，并且对 ETL 具有处理影响。少数维度还可以从主事务或库存源表中直接获取，要将其添加到事实数据表中，需要进行特殊处理。
整个 SSIS 过程借助复杂维度说明了该工具的灵活性和扩展性，旨在为基于 SQL 2005 平台构建的许多 ETL 设计提供好的参考。
SSIS 开发环境
Business Intelligence (BI) Development Studio 基于 Microsoft Visual Studio(R) 2005 (VS) 平台构建。虽然与 SQL Server 2000 中企业管理器 UI 有很多不同的地方，BI Development Studio 的功能是本着让数据库管理员 (DBA)、数据体系结构设计师以及开发人员之类的人士容易上手的思想实现的。简化的、关注 BI 开发人员需求的 UI 配置文件减缓了 VS 带来的不良因素。
对于 Project REAL，单一 BI 解决方案涵盖 Recurring ETL 项目。此项目按增量运行所有的 SSIS 数据包。图 1 显示了具有共享数据源和 SSIS 数据包的解决方案资源管理器。数据包按其功能命名。存在多种类型的数据包；第一种也是最简单的一种是维度数据包。源自其自身源实体的每个维度具有自己的数据包。事实数据表数据包在设计上都类似，只是它们按各自的重复周期（每日或每周）进行命名 - 每日和每周对应不同的数据包是因为两者之间的业务逻辑是不同的。例如，Fact_Daily_Store_Inventory 数据包执行的处理任务与相对应的每周数据包 Fact_Weekly_Store_Inventory 执行的处理任务不同，即使它们都影响相同的事实数据表。
对于处理协调，显示了称为加载组数据包的第三种数据包。这些数据包不包含处理业务逻辑，用于处理前面描述的维度和事实数据包的工作流协调以及一些应用程序审核和可重新启动性。图 2 显示了一个加载组数据包示例。利用“执行数据包”任务执行子维度和事实数据包，使用控制流处理一些处理任务的工作流和并行化。包含有助于过程审核和可重新启动的其他任务，这些将在本文的后面内容中进行更详细的介绍。
源代码管理集成
基于 VS 的 BI Development Studio 具有的优点之一是源代码管理集成。为了帮助避免当多个开发人员同时对相同的过程进行处理时出现开发“死锁”，REAL 的共享开发环境使用 Microsoft Visual Source Safe(R) (VSS)（虽然不限于 VSS）。VSS 标准功能包括：历史记录和备份，锁定签入和签出的数据包、数据源或文件以及进行版本比较以突出差别。大部分源代码管理功能可以在 File（文件）|Source Control（源代码管理）菜单中找到，如图 3 所示。
使用 Visual Source Safe 或其他源代码管理应用程序实现源代码管理后，只要通过右键单击数据包或数据源，就可以访问 BI Development Studio 中的许多与对象相关的功能，如图 4 所示。
命名和布局惯例
值得注意数据包布局、任务命名和注释的惯例。为了保持一致性，所有任务和转换的命名以代表任务或转换类型的 3 或 4 位字母缩写开头，后跟 3 至 4 个字词的对象功能描述。这非常有助于审核和日志记录，因为可以根据对象名称跟踪日志记录详细信息。数据包布局通常采用任务或转换退出主控制流和数据流的顺序：先从上到下，后从左到右。注释给出每个任务的详细信息，有助于说明数据包。
ETL 审核和日志记录
数据包和子数据包是自定义的审核步骤，通过父工作流集成到数据包结构设计中，跟踪高级执行详细信息，包括数据包的开始、结束、失败次数以及帮助验证数据量和处理的行计数。SSIS 本身提供各种日志提供程序类型的详细数据包执行日志记录。这些详细日志条目由事件驱动，并反规格化到选定提供程序的目标中。例如，如果某个数据库被选作日志提供程序，则所有日志条目都会插入一张表中。在整个执行过程中，事件是引擎的参考点，如 On Warning、On Validation、On Execute、On Pre Execute、On Post Execute 等。图 6 突出显示了日志记录事件详细信息选择器。
日志事件中的每项记录与任务或转换运行时所在的相应数据包的执行 ID 相关联。执行 ID 是每次运行数据包时生成的唯一 GUID。我们可以想象，当以非常低的频率进行 SSIS 日志记录时，对于每个数据包都会产生成百上千（如果不是成千上万）的条目。SSIS 日志记录为进行故障排除提供了大量的详细信息。但是，如果对 SSIS 事件不是很了解，又没有通过一种清晰的方式将数据包执行 ID 与引擎执行的特定数据包映射起来，则很难获取和理解这些信息。
对于 Project REAL，目标是通过一些与 BI 相关的特定执行审核来增强内置日志记录提供程序，同时使用日志记录功能生成详细的具有向下钻取功能的报告。审核结构要实现的功能有：
| • |
数据包和加载组的关联和标识。 |
| • |
仓库结构沿袭列的添加。 |
| • |
行计数验证审核。 |
| • |
具有向下钻取功能的 ETL 处理报告。 |
数据包和加载组跟踪
在支持和管理 BI 解决方案中，掌握 ETL 过程使用的数据包协调非常重要。因此，建立了更高级别的跟踪表来实现一起执行的相关数据包的关联。其次，执行单一数据包时还创建了一张表。有了 SSIS 日志记录功能，这可能看起来有点多余，但在以数据包命名的内置日志记录提供程序的执行 ID 与更高级执行工作流的组进程 ID 之间形成了一种直观的映射。由于数据包审核表中数据包的每次执行只存在一条记录，因此也简化了报告并使向下钻取报告功能能够实现。
Project REAL 应用程序审核主要是通过“执行 SQL”任务实现的。对于工作流数据包，控制流中的第一个和最后一个步骤管理加载组审核。在这个示例加载组数据包中，突出显示了处理此过程的控制流中的第一个和最后一个步骤。
数据包级跟踪比数据包工作流审核低一个级别，它也采用类似设计，使用第一个和最后一个控制流步骤。由于加载组数据包本身就是一个数据包，图 7 中的第二个步骤和倒数第二个步骤也审核“执行 SQL”任务。在维度或事实数据表数据包中，它们是第一个和最后一个步骤。
审核的另一个核心方面是快速识别错误。“事件处理程序控制流”概念是 SSIS 的一大特色。这些可以在数据包 UI 的第三个选项卡上找到。为了快速识别错误，使用了 OnError 事件处理程序，该处理程序是在数据包级别上定义的，将捕获出现的任何数据包错误。
而且，使用了一组数据包变量跟踪数据库与这些“执行 SQL”任务涉及到的数据包之间的元数据。使用的主要变量有系统变量 system::PackageExecutionID 和 system::PackageName，以及用户变量 user::ETL_Load_ID。该用户变量是在数据库中创建的标识符，并传递给数据包。使用 SSIS 的父变量配置功能，这些变量还从父数据包传递给子数据包。
仓库的沿袭添加
批处理标识符 ETL_Load_ID 不仅将审核元数据连在一起以进行报告和隔离，而且在仓库中被用来标识记录源。每一个维度和事实数据记录都源自一个特定的数据加载，此列标识该加载。这对数据沿袭和验证以及出现数据崩溃时需要进行的手动更正非常有用。
源数据提取后，通过衍生列转换可以立即将批处理标识符添加到数据流中。因此，任何下游转换都能使用此列进行更新、插入和跟踪，而且使元数据包含这些记录不需要什么开销。
行计数跟踪
数据验证对于 DBA 来说帮助非常大，不仅作为 BI 解决方案的一部分用于管理和故障排除，而且有助于建立用户信任感。如果用户对数据产生怀疑，解决方案就可能失败。Project REAL 使用行计数作为数据验证的一个方面。尽管此级别的验证只代表一部分应该进行的验证，但它是验证中的一个可靠的优先层。
行计数是在数据流中使用行计数转换实现的。行计数转换就是计算通过的行数并将值存储到一个预定义的用户变量中。行计数转换的一大优点是需要的开销和资源非常少。因此，在所有维度和事实数据包的核心数据流中，每个源数据的后面、目标或 OLE DB 命令的前面以及数据流中任何存在高值点的地方都插入了行计数转换。这些计数存储到不同的变量中，并通过数据流转换后紧跟的“执行 SQL”任务在数据库中持续起作用。图 10 显示了数据流中这些行计数转换的实现。
ETL 报告
为了将所有内容联系在一起并形成条理清晰的信息供开发人员管理和进行故障排除时使用，设计了一系列的 Reporting Services 链接报告，这些报告与审核、验证和日志记录相关。让我们回顾一下前面的内容，如果您阅读了审核讨论，就可能了解实现了关联元数据的支持架构。该架构中使用了四个主要表格：三个用户定义的表格和一个内置的 SSIS 日志记录表格。
Project REAL ETL 报告基于这些结构，并通过 Reporting Services 表分组和链接的子报告提供能向下钻取到详细信息的高级加载组执行摘要。包含以下级别的报告：
| • |
加载组执行摘要 – 开始和完成时间、持续时间摘要和执行状态。 |
| • |
数据包执行摘要 – 加载组关联、开始和完成时间、持续时间摘要和执行状态。 |
| • |
行计数详细信息 – 步骤描述、类型和行数。 |
| • |
数据包控制流任务摘要 – 从基本日志表获得的任务摘要汇总，包括任务持续时间和状态。 |
| • |
详细的事件日志分类 – 按选定数据包或任务排序和筛选的详细日志条目。 |
ETL 报告清楚地说明了它在管理 SSIS 解决方案和进行故障排除中的作用。
动态配置设计
SSIS 数据包的可管理性、分发和部署的关键之处在于配置。SSIS 采用多种方法来在运行时配置数据包属性，允许更新连接信息、变量和任何其他需要在执行时动态配置的任务或转换属性。提供了几种内置的配置方法，涵盖不同解决方案可以使用的多种环境要求，包括：
| • |
配置文件 |
| • |
环境变量 |
| • |
SQL 配置表格 |
| • |
父数据包变量 |
当从开发 ETL 数据包转到生产该数据包时，就能看到这项功能的实际价值。在大多数情况下，只要在配置系统中改变几个条目就可以实现这个目的。
SQL 配置
Project REAL 的目标是将配置进行集中化，使 ETL 设计可以部署到向上扩展模型和分布式模型的两种独特环境中。与此类似，还提供了多种版本的 REAL 解决方案（完全版本、示例版本和演示版本）以供参考和演示。因此，通过内置 SQL 配置，根据解决方案的不同数据库版本对配置进行集中化，这样可以将配置属性和映射放到关系表中，数据包之间共享配置属性和映射。要打开集中化的管理工具，请选择 SSIS，然后选择 Configurations（配置）。图 13 显示了 SQL 配置选项。
在配置表中，用多个条目表示不同的属性。第一组条目是有关连接的。需要指出的是如何应用连接的配置条目，以及对于一个数据包来说解决方案数据源是如何工作的。从数据源对象创建连接后，该连接是运行时的数据源，执行数据包时该连接不会根据父数据源而更新。数据源是设计时结构，因此，当在 UI 中打开一个数据包时，那些从 SSIS 解决方案中的数据源创建的连接将被更新。由于这种原因，连接非常适合用于配置，它们通常需要根据环境（开发、测试和生产）动态变化。Project REAL 配置表中的其他条目是可变映射。这些可变映射可以更新 ETL 中用来处理逻辑和管理的可变值。
XML 文件配置
在图 13 中，相应 SQL 配置表的位置由数据包连接决定。但是，如果所有的连接信息都放在配置表中，则将出现循环引用，导致使用硬编码的数据包连接值，而这是我们不希望看到的。为了防止这种情况发生，使用了第二种配置类型 - XML 文件配置。此外，在将配置集中到一个数据库表格的目标下，XML 文件中只需要一个配置条目 - 指向包含 SQL 配置表格的数据库的连接字符串。正如您看到的那样，XML 文件配置实际上只有一个属性 - XML 文件的位置和文件名。
文件配置的一大特色是能够使用定义配置文件位置的服务器环境变量。由于所有的数据包都引用此文件，使用环境变量使得只用一个位置就可以实现文件更改。这对部署也非常有用，执行这些数据包的其他服务器可以使用不同的文件位置和文件名。使用环境变量不同于 SSIS 环境变量配置，后者允许一个服务器包含多个替代任何数据包属性的环境变量。
父变量配置
到现在为止，上述所有 Project REAL 配置使用都是针对全局属性（即指定环境中每次执行一个数据包或一组数据包所用的连接和变量）的。而有些配置需要仅限于数据包的特定执行和数据包参与的工作流组的特定执行。例如，工作流组的批处理标识符 ETL_Load_ID 是在加载组数据包的初始步骤创建的并用于所有的子数据包。数据包的每次执行都是在不同批处理环境下进行的，因此，此变量的配置需要根据执行的数据包动态变化。
SSIS 的父变量配置功能使子数据包可以继承父数据包的变量。这与 SSIS 的前身 DTS 不同，在 DTS 中，变量是从父数据包传给子数据包的。在 SSIS 中，子数据包可以通过名称请求父数据包中的变量，从而能够从任何调用的使用“执行数据包”任务来调用子数据包的父数据包继承变量。
Project REAL 要求存在一种只针对一个数据包或一组数据包执行实例的配置，这完全可以通过父变量配置功能实现。就像提到的那样，所有的维度和事实数据包都继承 ETL_Load_ID，父数据包的执行 ID 也同样可以被继承，这就进一步实现了数据包之间的数据相关。图 15 显示了 vnETL_Load_ID 标识符的变量配置。
数据处理体系结构
到现在为止我们已经讨论了 SSIS 设计的支持结构，但没有深入探究任何核心 ETL 处理逻辑。此概述将基于本文前述章节给出的概念深入探究此处理逻辑。但是，在详细介绍 Project REAL 数据处理实现的细节之前，需要回过头来在重要的 ETL 原则下考虑 SSIS 的一些新功能。
控制流和数据流
控制流和数据流组件中包含用来实现核心业务逻辑的 SSIS 的主要功能。文本在讨论环境以及审核 Project REAL 的结构时已经几次提到过这些组件。
粗略地说，“控制流”是协调数据包的业务处理流逻辑的任务工作流引擎。无论是包含单个步骤还是多个相互关联的任务，每个数据包都只有一个主要控制流（事件处理程序也是一种控制流）。控制流中的任务通过约束（成功、失败、完成和自定义约束表达式以及布尔逻辑）链接在一起。
“数据流”是数据处理引擎，它处理数据移动、转换逻辑、数据组织以及源和目标之间数据的提取和提交。与控制流不同，由控制流编排的数据包中可以定义多个数据流。虽然数据流有与控制流工作流连接器非常相似的绿连接器和红连接器，但是它们的功能完全不同。可以将数据流连接器视为以小批数据从一个转换流到另一个转换的数据管道（称为“缓冲区”）。而这是显示数据流如何工作的最简单方式，事实上，为了获得最佳性能，向数据缓冲区移动的工作实际上主要是定义的转换执行的。
SSIS 体系结构优点
产品 SSIS 除了在互操作性、配置、可重新启动性和登录方面比产品 DTS 更好外，它还引入了转换引擎，这在大规模处理时更显优势，并使 ETL 体系结构设计更加稳定、更加灵活、更加基于性能。对于 Project REAL，核心 ETL 开发中考虑了这些优点，因此某些设计决策并未遵循基于 DTS 的体系结构的现状。
有限的分段
首先，SSIS 通过使复杂的数据转换、数据清洁和数据查询可以在数据流中直接执行（很少依赖 RDBMS 引擎和存储），从而减少了分段环境。源表和仓库表之间的数据对比可以通过具有条件拆分的查询转换和合并转换进行处理，以将结果定向到相应的加载逻辑。为此，数据库引擎的唯一要求是将数据输出到 SSIS 数据流而不是输出到执行查询、连接或行对比的数据库。
管道优点
大多数数据流组件都具有真正的管道并行功能（不过要注意，Sort 转换和 Aggregate 转换并没有此功能），这意味着在小型数据缓冲区中可以同时处理仓库对象，而不需要等待整个上游处理完成后才能进行下一步。这有助于缓解对源系统的提取影响，在 Project REAL 开发过程中，多数情况下，优化 SSIS 数据包后，从源提取原始数据并立即将其传递到数据库所花费的时间大约相当于提取数据并将其传递到在数据流组件中设计的一系列内存中的转换所花费的时间。
数据清洁和转换
现有的 SSIS 数据流转换包含一系列数据清洁工具，例如，模糊查询和连接、字符映射、数据类型转换、派生列以及一组用于数据对比和数据替换的基于布尔值的函数。
多对多源和目标
由于单个数据流可以包含多个不同的源和目标，因此这就使源于单个源的数据可以被拆分到多个目标。反之亦然，多个源对象可以合并到单个目标。通常在 BI 系统中，维度可以源于同一系统或不同系统中的不同表。同样，事实数据可以源于一个表或多个表，或者事务性源可以分裂成多个事实数据表目标。
维度和事实数据粒度及类型更改
多数情况下，仓库对象以与其源 OLTP 对象相同的粒度被加载。但是，也有维度合计到较高粒度或者父子自连接的源降级到标准层次结构的情况。源记录可能需要透视，例如，当源行从第四常规设计统一到相关属性的统一记录集时。合计或分组事实数据表以满足报告要求时，这些事实数据表也可能进行类似的转换。通常使用其他转换（如，Aggregate、Pivot、Un-pivot、Sort、Merge Join 等等）在数据流内处理这些少见的情况。
维度处理
处理维度的历史记录是 ETL 解决方案比较复杂的一个原因。对于 Project REAL，维度加载情况不仅涉及处理历史属性和更改属性，还涉及维度更改类型以及与维度关联无法同步的事实数据。除了考虑 SCD（渐变维度）向导的内置功能外，我们还将考虑项目中包含的一些更特殊的要求：
| • |
推断维度成员，其中因为完整维度记录尚不能加载，所以收到不具有匹配维度成员的事实数据。有时称为“孤立事实数据”。 |
| • |
更改 SCD 类型，其中在一个维度内的成员有不同的历史更改要求，其中的个别要求可能随着时间的推移会有所更改。 |
SSIS 能够同时处理 Project REAL 解决方案的标准情况和特殊情况，如下所示。
渐变维度向导
每个 ETL 设计师都希望拥有一个可以神奇地处理渐变维度的工具。SSIS 可以基本达到这一效果 - 在 SSIS 中有一个向导，它基于源维度架构和目标维度架构，通过一系列步骤使开发人员可以确定更改的特征。然后此向导建立处理该维度所需要的转换。即使要求改变，也可以重新调用该向导，通过允许修改原来的选择以便处理新的过程。
对于 Project REAL，渐变维度 (SCD) 工具很有优势。除了一个星型架构维度表以外，其他所有星型架构维度表都使用 SCD 转换。极大地缩短了维度处理所用的开发时间。为了显示 SCD 向导的工作方式，Store 维度提供了该向导最全面的使用方法。Store 维度的要求包括：
| • |
新建维度成员 – 新维度成员添加到源中 |
| • |
更改维度属性 – 以前的类型 1 列更改，其中每次源列值更改时历史记录被覆盖。 |
| • |
历史维度属性 – 以前的类型 2 列，其中通过添加一个新维度记录将历史记录保存到下一次更改之前，添加的新维度记录与所有新事实数据记录相关联。 |
| • |
推断成员 – 即在事实数据处理运行之前维度成员尚未加载到维度表中的情况，这样会添加一个占位符记录，一旦完整的源维度可用，该占位符将随后更新（类型 1 列和类型 2 列）。 |
浏览 Store 维度的向导，第一个屏幕显示了一系列可供其业务键选择的列，如图 16 所示。
下一步，该向导需要区分出类型更改的列。选项有 Changing 属性、Historical 属性和标识不应当更改的列的 Fixed 属性。图 17 显示了这些属性。
包含历史列或类型 2 列的维度需要一些元数据来管理每个更改的当前和历史性质。下一个屏幕（图 18）有助于处理过程了解 Store 维度是如何跟踪历史记录的。在这种情况下，Current_Row 列将跟踪哪个维度记录是更改的维度行的当前维度记录。
下一步，如果使用推断成员，图 19 中显示的屏幕将标识 SCD 向导如何知道维度记录是推断成员，从而除业务键之外的所有列在处理过程中都会得到更新。有两个选项。第一个选项表示所有的非键列都是 NULL 值，来标识推断成员。第二个选项由标志列驱动，表示成员是否是推断成员。考虑到值为 NULL 的列在 Analysis Services 中不能正确显示，所以我们选择使用称为 Inferred_Member 的列。然后我们就可以用名为 Unknown 值替换在 Analysis Services 层次结构中使用的属性。
上一屏幕后，该向导生成了一系列为向导处理过程中输入的详细信息定制的转换。主要转换称为 Slowly Changing Dimension 转换。它将输入作为维度源记录，无论它们是完整维度源记录或仅仅是部分源记录（在源记录上添加或更改的记录）。将 SCD 任务视为非缓存 Lookup 转换和 Conditional Split 转换的组合，其中针对仓库维度评估源维度记录，然后分发到不同的 SCD 输出。图 20 显示了 Store SCD 转换的最终 UI 图像及其相关联的输出。
唯一维度情况
唯一不使用 SCD 转换的 Project REAL 维度处理数据包是 Item 维度。其要求是唯一，其大小（大约六百万个成员）需要对可伸缩性进行特殊处理。
Item 维度区别于其他维度的一个特征是 SCD 类型更改的种类不同。除了需要推断成员、Changing 属性和 Historical 属性外，要求还指定对于一个给定的成员，其属性更改类型可以从 Changing 属性变为 Historical 属性；从类型 1 转到类型 2。第一次销售某项时将会发生这种情况。第一次销售之前，所有的属性都作为 Changing 类型 1 属性，但是一旦销售出去，一部分属性将变为 Historical 类型 2 更改。这种情况被定义为类型 1.5 更改，并由业务需求驱动来限制添加到维度中的类型 2 的数量，这是因为当某项第一次输入到事务性系统后，在前几天建立其特征的过程将会导致发生多个更改。得出这些初始属性详细信息时，维度成员处于一种状态，其中对任何属性的更改都会在维度中（而非新类型 2 历史记录）导致该属性的更新。此方法所提供的值将使维度表的增加仅限于有意义的历史更改，当某项处于稳定状态并处于销售时正是如此。虽然这种情况由不同的业务要求驱动着，但是这种情况类似于推断成员的工作方式。然而，在这种情况下，源维度记录可用，并且一直要求更新所有属性，直到满足销售要求。
是否使用 SCD 向导的决定性因素是维度所处理的记录量。六百万个成员 Item 维度的 100 个属性在一天内会经历上万次更改。内置 SCD 组件查询过程正在生成对数据库同等数量的调用、查询大范围的表并且在结果行返回许多列。此过程所用的时间达不到窗口所需要的时间，因此采取了另一种方法。
一种办法就是使用 Lookup 转换并将整个维度保存到缓存中，以便所有列将可用于更改类型对比。但是，将大型表的所有列保存在缓存中需要几 GB 的内存，并且还要花大量的时间来加载到内存。因此，使用了左边的 Merge Join 转换，其中左边的源记录通过业务键与右边的当前维度成员匹配，如图 21 所示。此连接的影响是将只输入那些在相关事实数据中实际使用的 Item 记录。更改类型分析的维度所需要的列包含在匹配记录的数据流中。使用左边的合并以便源（左边）的新记录可以继续沿着管道，其中它们将作为新成员添加到维度中。
因为输入列已经进行了排序，所以此情况中的合并进展很顺利 － 进行匹配时，记录将被释放到下游转换以便进行处理。
Conditional Split 转换（位于 Merge Join 转换的下面）评估某些条件，然后将行定向到多个转换输出。这些条件是按顺序进行评估的。满足行的第一个条件指派其输出，以便行不是发送到多个输出。
图 22 中的 Conditional Split 转换首先使用 ISNULL 函数评估连接右侧是否存在匹配的成员。与空检查匹配的源行被输出到将行作为新维度成员进行添加的转换。由于其余的成员都存在与仓库维度表中的成员匹配的成员，因此评估更改类型条件。对于匹配记录，评估的第一个条件就是推断成员和销售标志条件。由于这些都需要对维度属性进行完整更新，因此它们将被合并，并同时进行处理。接下来，将评估历史更改属性。如果一个或多个历史跟踪的属性有更改，那么将生成类型 2 更改记录。最后，其余更改类型列的任何更改都将导致维度更新声明以覆盖该属性（以类型 1 方式）以前的值。
注意：虽然没有指定最终条件，但是 Condition Split 的默认输出说明了这种情况。由于源记录只包括新行和更改的行，因此我们知道如果满足了其他所有要求，则最后一个条件必须适用于其余记录。这就强调了这样一个事实：条件的顺序对于以这种方式正确处理 Item 维度是至关重要的。
条件拆分的转换下游看起来与 Store 示例中 SCD 转换的输出非常类似。这是因为在新添加、更改以及推断成员的 SCD 处理之后输出被模拟（因为类型 1.5 更改，所以称为“完整更新”）。
事实数据表处理
事实数据表处理在很大程度上与维度处理不同。并且，一个事实数据表处理与下一个也有很大不同。但是，大多数事实数据表处理包含事实数据行对比和维度键查询。为了便于说明，一些不同的 Project REAL 事实数据表数据包在本部分中将会重点介绍。这些数据包模拟常见的情况。
递增源提取和全部源提取
Project REAL ETL 有两种类型的事实数据表提取：全部源提取，其中无法标识更改的或新的记录；递增提取，其中只提取新的记录和更改的记录。
全部源提取
每周对地区中心 (DC) 库存位置的八百万个 DC 和项组合跟踪一次。在源系统上，这些记录包含在表中，该表不标识新的或已修改的记录，因此 ETL 过程必须比较库存源和事实数据表之间的记录以标识更改发生的时间。然后正确地处理插入或更新。
采取的方法是对源表和目标表使用全部合并连接和完整数据集。全部连接有助于标识库存记录添加到源中的时间或完全删除的时间。对于此解决方案，要求指定删除的源记录在事实数据表中需要被跟踪为零库存位置 - 使用配置为全部连接的 Merge Join 转换满足此要求。合并如图 24 所示。
下一个下游转换即 Conditional Split 转换，它通过评估连接结果和列值的对比来处理记录更改的标识。
Conditional Split 转换中应用的条件是根据性能排序的，开始于其中连接生成匹配并且属性和测量不变的情况。因为大多数记录满足此条件，所以设计程序中首先列出此输出，但是不使用输出流。由于不变的记录不需要处理，因此这对滤除那些行会有影响。第二个条件是标识新的库存记录 - 即其中源行与现有事实数据表记录没有匹配的那些库存记录。相反，下一评估是全部连接情况，其中因为删除了源行，所以事实数据库存位置需要设置为零。最后，使用默认输出捕获所有其他行，这些行的库存位置进行了更改并且需要在数据库中进行更新。
递增源提取
当提取过程可以在源系统中隔离一组更新和插入时，这可以极大地提高相关 ETL 过程的性能。幸运的是，Project REAL 中的许多大型数据源都能够利用这些增强功能。其中一个示例是存储库存提取。如果需要整个库存源（接近二百万个记录）来处理日常更改，则一天中将没有足够的时间来处理其他任务。但是由于日常库存可以递增提取，因此处理窗口缩至一个非常易于管理的窗口。
对于存储库存，递增提取过程可以使用缓存查询来帮助确定递增记录是插入的记录还是更新的记录。有助于此过程的是中间的分段过程，它用来在查询中过滤记录，优化处理，从而有助于总体实现。有关详细信息，请参阅本文后面的数据处理优化技术。
图 26 显示了存储库存事实数据处理的数据流。重点介绍了将更改的源记录和当前事实数据记录进行比较的查询。
维度查询
每个事实数据表处理都需要一种方法将事实数据与维度表相关联。已使用可以缓存维度的 Lookup 转换对此进行了广泛处理。随着源行的处理，它基于业务键关联快速返回事实数据表所需要的代理键。此过程对于行数较少的维度尤其简单有效。无论何时维度具有历史类型 2 更改，并因此产生当前行标识符，然后将过滤缓存以只使用当前行，以便维度成员的最新版本与事实数据相关联。图 27 显示了 Lookup 转换的 Reference Table（参考表）选项卡。在此 Store 维度示例中，在 Current_Row 上使用了查询以进行过滤。
在 Lookup 转换的 Columns（列）选项卡中，数据流列映射到参考表列。由于维度查询的目标是获得该维度的代理键，因此业务键用作映射 (Store_Num)，且返回了代理键 (SK_Store_ID) 及在数据流下游使用的辅助列。
从数据流的角度看，源行只是从查询到查询，使行与最新的维度代理键相关联。图 29 中的数据流显示了以上所述的 Lookup 转换。还显示了几个其他的转换，包括用于推断成员的 Script 转换和 Union All 转换（将在下一节详细说明）。
对于包含上百万行的 Item 维度，已使用了优化技术以仅查询事实数据表处理执行所需的行。有关详细信息，请参阅本文后面的数据处理优化技术。
处理推断成员添加
由于 Project REAL 要求在不存在维度记录时已创建推断成员，因此我们有很多工作要做。注意：“推断成员”是一个充当只有业务键值的占位符的维度记录，以便全部维度记录可用时，可以使用提供的新值更新所有维度列。更新过程在维度处理过程中进行，但是当 Lookup 转换没有找到匹配成员时，就会在事实数据处理过程中添加推断成员。如果处理缓存中保存的键的保守查询的 Lookup 转换找不到匹配，行实际上就失败了。若要配置输出以便行可以通过失败的管道重新定向，请单击 Lookup UI 中的 Configure Error Output（配置错误输出）按钮并配置要“重新定向”的行。请查看图 30。
通过以这种方式配置主要的 Lookup 转换，重新定向了不匹配的行并且通过另一种方式将推断成员添加到维度中。Project REAL 中的所有替代维度键都是标识列，这使得此过程比如果键是可以由 SSIS 生成的独特标识符时更难。考虑了几种添加推断成员的方法。考虑到数据量，需要使用一种方法，它通过使推断成员添加在主要的数据流中来使处理过程最佳地运行。若要简化处理过程并避免多个数据流，在下一维度查询之前新生成的推断成员键需恢复到数据流中。另一种考虑是由于数据流执行之前加载了主要的 Lookup 转换缓存，因此，当推断成员第一次添加到数据库时，它没有与其余的维度记录一起添加到缓存中。因此，如果上千个事实数据记录遇到同一个未匹配的业务键，那么所有记录在缓存中都将找不到，并且因此将被发送到推断成员添加处理的错误输出。
考虑到以上要求，选择了 Script 转换来添加推断成员。Script 转换与 Script 任务不同，它用于数据流。它可以对经过管道的行和列执行基于脚本的操作。在需要特殊处理的情况下，Script 转换具有更好的灵活性并且可以自定义。此处的具体目标就是采用未匹配的查询输出，将推断成员添加到数据库中的维度表中，并获得新生成的代理键，所有这些都没有对同一个未匹配的业务键的数据库进行多次调用。开发 Visual Basic(R) .NET 脚本之前，整理出了一个代码概要，概述了 Script 转换过程：
|
1. |
首先，在处理第一条记录前需要进行：
| ||||||||||||
|
2. |
对于经过 Script 转换管道的每行，请检查哈希表中是否存在当前业务键。然后：
| ||||||||||||
|
3. |
输入管道的最后一行后，请清除和解除分配。 |
以下 Script 转换是存储查询的一个示例推断成员过程，当 Store_Num 在查询中没有匹配记录时使用。
' Microsoft Data Transformation Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for DTS Script Components
Imports System
Imports System.Data
Imports System.Collections
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
___________________________________
Public Class ScriptMain
Inherits UserComponent
Private htBusinessID As New Hashtable
Private objConnection As New SqlClient.SqlConnection
Private objCommand As New SqlClient.SqlCommand
Private boolInit As Boolean = False
Private strProcedureName As String = "config.up_ETL_DimStore_CreateInferredMember"
Private strBusinessID As String = "@pnStore_Num"
Private strSurrogateID As String = "@pnSK_Store_ID"
Private strETLLoadID As String = "@pnETL_Load_ID"
Private strReturnValue As String = "@RETURN_VALUE"
___________________________________
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If (boolInit = False) Then Me.Connect()
If Not (htBusinessID.Contains(Row.InBusinessID)) Then
Dim strSurrogateKey As String = Me.Execute(Row.INBusinessID, Row.ETLLoadID)
htBusinessID.Add(Row.InBusinessID, strSurrogateKey)
End If
Me.ProcessRow(Row)
End Sub
Public Sub ProcessRow(ByVal Row As Input0Buffer)
Row.OutSurrogateID =
System.Convert.ToInt16(htBusinessID.Item(Row.INBusinessID).ToString())
End Sub
Private Function Execute(ByVal BusinessId As Decimal, ByVal ETLLoadID As Integer, ByVal)
As String
Me.objCommand.Parameters(strBusinessID).Value = System.Convert.ToString(BusinessId)
Me.objCommand.Parameters(strETLLoadID).Value = System.Convert.ToString(ETLLoadID)
Me.objCommand.ExecuteNonQuery()
Execute =
System.Convert.ToDecimal(Me.objCommand.Parameters(strSurrogateID).Value).ToString()
End Function
___________________________________
Private Sub Connect()
If boolInit = False Then
Dim strConnection As String = Connections.SQLRealWarehouse.ConnectionString
Dim x As Integer = strConnection.ToUpper().IndexOf("PROVIDER")
If x >= 0 Then
Dim y As Integer = strConnection.IndexOf(";", x + 1)
If (y >= 1) Then strConnection = strConnection.Remove(x, y - x + 1)
End If
Me.objConnection.ConnectionString = strConnection
Me.objConnection.Open()
Me.objCommand.Connection = Me.objConnection
Me.objCommand.CommandType = CommandType.StoredProcedure
Me.objCommand.CommandText = Me.strProcedureName
Dim Parm As New SqlClient.SqlParameter(strBusinessID, SqlDbType.Decimal)
Parm.Direction = ParameterDirection.Input
objCommand.Parameters.Add(Parm)
Parm = New SqlClient.SqlParameter(strETLLoadID, SqlDbType.Int)
Parm.Direction = ParameterDirection.Input
objCommand.Parameters.Add(Parm)
Parm = New SqlClient.SqlParameter(strSurrogateID, SqlDbType.Int)
Parm.Direction = ParameterDirection.InputOutput
Parm.Value = 0
objCommand.Parameters.Add(Parm)
Parm = New SqlClient.SqlParameter(strReturnValue, SqlDbType.Int)
Parm.Direction = ParameterDirection.ReturnValue
objCommand.Parameters.Add(Parm)
Me.boolInit = True
End If
End Sub
___________________________________
Private Sub Close()
If boolInit = True Then Exit Sub
Me.objCommand.Dispose()
Me.objConnection.Close()
Me.objConnection.Dispose()
Me.htBusinessID = Nothing
MyBase.Finalize()
End Sub
End Class
Script 转换执行的过程检查维度中是否存在业务键，如果不存在该记录，将插入一条新记录（即推断成员）。然后将新添加的标识列返回到脚本以便可以在数据流下游使用。
推断成员脚本转换之后，使用 Union All 转换将记录合并回主要管道。然后它们可用于下一次维度查询。图 31 显示了一系列查询、其关联的推断成员查询以及将结果返回到一起所需要的合并。
数据处理优化技术
经过 Project REAL 的开发过程，已经开发出了一些有助于简化 ETL 的优化技术。这些技术包括基于产品体系结构优点的原则、SSIS 内的设置以及为了处理大量数据的数据流优化。一些优化包括：
| • |
使用高值目标数据分段来过滤查询和数据合并源。 |
| • |
限制使用 Full Rowset（分块）数据流转换，如，合计和排序。 |
| • |
预先进行常用数据的处理，以免发生异常错误。 |
| • |
考虑大量维度的批更新或事实数据表更新。 |
高值目标分段
完全缓存的 Lookup 转换使源之间的数据相互关联，如使维度键和事实数据表记录相关联。但是，对于大维度，将整个维度加载到查询内存缓存将花费很长时间并且会使用用于其他过程的 RAM。对于 Project REAL，我们为大 Item 维度（其中包含六百万到七百万个成员）的业务键创建目标分段表。通过使用一个特殊的数据流在事实数据表中填充将此窄的分段表，该数据流包含单个源和目标并且从事务性源中只提取产品业务键并将其传递到分段表中。图 32 显示了 55 秒内成功完成分段四百四十万个业务键。由于该提取具有极强的针对性，并且范围很窄，因此数据流能够在几秒内完成几百万行。
然后，分段的键用来过滤查询用于加载其缓存的查询。由于已经对维度中的业务键建立了索引，因此限制维度记录的连接执行地很好。对于此示例，本技术将维度查询缓存几乎缩减至完整维度大小的十分之一，但是由于查询使用相同的业务键设置，因此在事实数据处理过程中仍然包含查询需要的所有维度成员。以下 SQL 代码用来填充查询缓存，并涉及用分段表 (tbl_DWS_ETL_Store_Inventory_Log) 中包含的业务键过滤维度 (Tbl_Dim_Item)。
SELECT distinct ITEM.SK_Item_ID , ITEM.SysID , ITEM.SK_Parent_Item_ID , ITEM.Retail_Amt FROM dbo.Tbl_Dim_Item as ITEM INNER JOIN config.tbl_DWS_ETL_Store_Inventory_Log_Keys as INV_LOG ON ITEM.SysID = INV_LOG.SysID AND ITEM.Current_Row = 1
此方法也可用于限制 Merge Join 转换中使用的源记录。合并连接用于多种情况，其方式与查询类似，可以使源和目标数据相关联。当需要比较源和仓库之间的许多列时，因为每行的每列都需要存储到内存中，所以 Lookup 转换可能无法处理查询缓存的大小。另一种方法是使用 Merge Join 转换将源数据和仓库数据合并在一起。如上文所述，虽然 Merge Join 不会消耗内存，但是分段业务键后也可以利用经过筛选的仓库源。
限制 Sort 和 Aggregate 数据流转换
限制 Sort 转换和 Aggregation 转换将会提高性能（因为它们需要处理管道中的所有行并且消耗了大量的时间和资源），当需要或要求使用它们时，性能会下降好几倍。例如，几个 Project REAL 数据包中使用的 Merge Join 要求按定义连接的列对源进行排序。对两个源都使用 Sort 转换将要求合并的两个源中的所有行在释放到 Merge Join 之前都要在 Sort 转换中对其处理。在处理小数据集时这没有什么影响，但是在处理大量数据集时就会产生许多影响。首先，将加载到排序（其他示例中可能是合计）中的记录都存储在内存中。达到阈值后，部分缓存可通过 Sort 转换或虚拟内存管理器临时保存在磁盘上，因此使 I/O 产生了不必要的操作，并使用了其他下游转换处理中可能需要的内存资源。数据流中使用的转换处理数据时，会影响管道中的数据上游处理，如果这过滤了源连接，将会减慢提取的速度，从而增加总处理时间。
但这并不表示我们完全避免使用 Sort 转换或 Aggregation 转换。总体说来，它们操作速度极快并且很多情况下都很有用。但是，当要处理大量数据或内存资源有限时，就不宜使用它们。
对于此 Merge Join 示例，如果可以预先对源数据进行排序，就不必使用 Sort 转换。在所有的 Project REAL 情况中，这种情况下处理的很不错。由于 ETL 过程通常通过维度的业务键或事实数据的代理键使用 Merge Join 使合并关联，因此排序可能会在 Source Connection 查询后进行。对相关的引擎进行排序会需要很大的开销，除非创建了合适的索引或索引组合。由于创建索引通常会使用业务键和代理键，因此将 ORDER BY 子句添加到 SQL 查询可能会很有用和有效。为此，数据流需要注意：源已排序以及排序所应用的列和方向。这是在 Source Connection 的 Advanced Editor（高级编辑器）中完成的。在 Input and Output Properties（输入和输出属性）选项卡上，请查看 OLE DB Source Output（OLE DB 源输出）的顶级属性，有一个名为 IsSorted 的属性，它需要设置为 True。第二，需要通过 Output Columns（输出列）容器（如图 33 所示）中的 SortKeyPosition 属性指定排序的列。然后数据流将认可此排序。
在其他仅需要对一部分数据流数据进行排序或合计的情况下，建议使用另一优化来分离数据（使用 Multicast 转换），然后，如果需要，过滤行（使用 Conditional Split 转换），最后再指定数据处理过程中需要的一部分输出列（在排序或合计内）。
处理常见的情况以防出现异常错误
在管道中使用分离、合并、过滤以及组合功能，有几种方法可以通过分别处理独特的情况来优化处理过程。当使用类似于缓存查询或大批目标这样的简化过程可以完成一个操作的百分之九十时，就可以实现上述优化，但是其余百分之十需要通过不太有效的方法来限制该过程。
OLE DB Command 转换和批更新
大量的更新可能是 ETL 进程的弱点。某些系统不进行事实数据表更新，从而避免该过程的开销成本。创建事实数据表更改记录来抵消测量误差给其自身在报告和处理方面带来了很多挑战。Project REAL 的日常库存处理模拟了此类情况。库存快照事实数据表的周粒度是每周存储二百万条记录。此外，每天对当前库存数据会有高达一千万次的更改。这两点导致了瓶颈问题。
处理如此大的更新有两种主要的方法：
|
1. |
联合使用 OLE DB Command 转换和参数化的查询。 |
|
2. |
将更改记录传递到分段表并执行基于集的 RDBMS 更新。 |
对于第一种方法，SSIS 包含一个可以直接和数据库相互作用的转换来处理各种操作，最常见的是更新语句。OLE DB Command 转换使用对数据流列的映射参数化的 SQL 语句。行传递到转换后，将用行中提供的数据执行操作。由于一次只能对一行执行此操作，因此，用来处理大量更新时会有很多限制。当通过此转换对上百万行执行更新时，会产生严重缺陷，例如对关系数据库的影响、对数据流的影响以及用来完成处理的时间。
第二种方法涉及对数据进行分段和通过在更新语句中连接分段表和维度表使用关系引擎来处理更新。这样将导致过分使用分段环境，但是考虑到成本，使用此方法作为第一种方法的备用方法可能总体上会有益处。此方法的缺点就是使用分段环境带来的资源成本以及更新过程中对仓库数据库的影响，从而可能导致系统资源紧张以及表、页或行锁定。
后一种方法的优点仅当与备用方法相比较时才会显现出来。因为可以优化目标转换，所以通过分段更新所需数据，管道会执行得更好。并且，因为相对于逐行更新，基于集的操作能更有效地被 SQL 处理，所以对目标表的影响总时间会缩短。通过优化索引以及潜在地将更新分为一系列较小的批处理，也有益于更新。
