业务智能 ETL 设计实施策略.docx
《业务智能 ETL 设计实施策略.docx》由会员分享，可在线阅读，更多相关《业务智能 ETL 设计实施策略.docx（38页珍藏版）》请在冰豆网上搜索。
业务智能ETL设计实施策略
ProjectREAL：
业务智能ETL设计实施策略
发布日期：
2005年12月19日
发布者ErikVeerman
SQLServer技术文章
技术审阅：
DonaldFarmer、GrantDickinson
合作伙伴：
Intellinet
适用于：
SQLServer2005
摘要：
了解SQLServer2005IntegrationServices（SSIS）的使用。
在称为ProjectREAL的业务智能引用实现中，SSIS展示了一个真实的大量提取、转换和加载（ETL）过程。
此ETL解决方案支持几个TB的数据仓库，包含大型仓库的典型数据处理、配置和管理机制。
本页内容
简介
数据配置文件
SSIS开发环境
简介
成功的业务智能（BI）应用程序需要可靠的工具来运行。
如果开发人员和管理员具备有关如何执行成功实现方面的有关知识基础（也就是最佳实施策略信息），也会有助于创建这些应用程序。
Microsoft与其多个合作伙伴通过ProjectREAL为基于Microsoft（R）SQLServer（TM）2005的BI应用程序找到最佳实施策略。
在ProjectREAL中，通过创建基于真实客户方案的引用实现来研究最佳实施策略。
这意味着客户数据将被带到企业内部，并用于解决这些客户在部署过程中遇到的相同问题。
这些问题包括：
•
架构设计-关系架构和AnalysisServices中使用的架构。
•
数据提取、转换和加载（ETL）过程的实现。
•
客户前端系统的设计和部署（用于报告和交互分析）。
•
生产系统大小的调整。
•
系统的持续管理和维护，包括对数据的增量更新。
通过使用真实的部署方案，我们能全面了解如何使用这些工具。
我们的目标是解决大型公司在进行自身真实部署过程中会遇到的各种问题。
本文主要讨论ProjectREAL的SQLServerIntegrationServices（SSIS）提取、转换和加载（ETL）设计。
此设计基于Barnes&Noble的ETL体系结构-完全使用SSIS构建并且是SSIS的第一个ETL产品实现。
由于此解决方案不是在数据转换服务（DTS）或其他ETL工具基础上的升级设计，因此所采用的许多方法不同于DTS中的典型ETL体系结构。
此解决方案的目的是利用SSIS的全新应用程序体系结构，以另外一种角度思考并设计一种ETL过程，使其成为常规的ETL设计最佳实施策略的一个模型。
在本白皮书中，我们将说明每一个方案的设计决策和ProjectREALSSIS处理过程的实现细节。
有关ProjectREAL的概述，请参阅名为ProjectREAL：
技术概述的白皮书。
ProjectREAL在存续期间将导致大量论文、工具和示例的产生。
要查看最新的信息，请退回到以下站点：
ProjectREAL是Microsoft和BI领域的众多合作伙伴之间的协作尝试。
这些合作伙伴包括：
ApolloDataTechnologies、EMC、Intellinet、Panorama、Proclarity、ScalabilityExperts和Unisys。
ProjectREAL的业务方案和源数据集由Barnes&Noble友情提供。
注意：
本白皮书是初稿，其中包含我们根据使用SQLServer2005的早期社区技术预览（CTP）版本的经验推荐的最佳实施策略。
本白皮书在发布时是准确的。
本文档中的产品功能可能会改变，将来可能会开发出更好的实施策略信息。
ProjectREALETL目标
为了支持报告和分析要求，任何业务智能（BI）系统中都存在ETL处理。
需要按此支持功能实现ETL。
这没有降低ETL的重要功能，因为要报告的数据将通过ETL处理过程直接进行处理。
ETL要考虑该处理过程的计时、性能和准确性等方面；ETL设计的支持、管理、灵活性和可扩展性也非常重要。
真实系统通常出现一些影响ETL的未知情况和异常情况。
这就要求ETL处理过程能够轻松处理各种变化并为稳定系统的最终目标服务。
对于ProjectREAL，这些关键方面使得ETL设计需要实现以下几个主要目标：
•
ETL管理。
为了提供管理支持，实现了可以跟踪和报告ETL元数据的设计。
这为用户进行参考和故障排除提供了清晰的处理过程状态，有助于隔离问题和解决问题。
•
动态配置。
开发这项功能是为了在发布和分发核心组件时支持企业系统。
包含根据企业和技术要求变化以及大型支持开发团队的相应环境来考虑设计的适应性。
•
平台集成。
这涉及到设计一种与BI解决方案的多个层次进行交互的解决方案。
包括安全性、基础结构、关系和OLAP结构、获取数据的报告和分析工具。
•
性能。
鉴于数据仓库中处理和管理的数据量，注意性能问题对于ProjectREAL解决方案来说非常重要。
数据总共可高达数TB。
内容提要
本文主要介绍几种特定的设计原则以及从设计过程获得的一些经验教训，并说明解决方案的整体体系结构。
文中提供了详细参考并包含解决方案的一些设计要点。
随着新解决方案的成熟和在SQLServer2005平台上的开发，将会发布更详细的、更全面的信息。
以后的文章将扩充概念，提供优化的性能设计，并可能演示一些更好的设计示例。
本文提供一个可靠的基于SSIS的BIETL引用。
在规划和开发ETL的重新设计、升级和新的实现过程中，BI体系结构设计师可以使用该工具。
SSIS的功能远不止仅仅处理ETL-它具备系统集成、信息管理和数据转换等众多其他功能。
本文只讨论该产品的几个方面，涉及与ETL处理过程有关的几个SSIS核心部分。
概述
本文中的示例与ProjectREAL实现直接相关。
每个示例都是经过挑选的，分别强调SSIS在应用到ETL处理时的特定方面。
这些示例演示了前文介绍的一部分目标和以下常见的ETL方案：
•
SSIS开发环境
•
ETL审核和记录
•
用于属性和数据源管理的动态配置设计
•
标准和独特方案的维度处理
•
维度关联的事实数据表处理和事实数据表更新
•
数据处理体系结构设计
•
数据处理优化技术
ProjectREALSSIS解决方案中实现了所有这些示例，而且该解决方案已用90天的每日和每周生产数据成功运行。
处理的数据包括一部分假日零售高峰时的数据，数据时间跨度达两年，以证明此数据作为一个真实示例的稳定性和可靠性。
就像前面提到的那样，Barnes&Noble从2004年11月就已经在生产ProjectREALETL所基于的这个“真实”的SSIS解决方案了。
在这些示例和数据包中，许多可以或将可以用来仔细审阅实现的特定方面。
将来会在ProjectREAL网站上发布此类信息。
另外，将在行业会议上提供这些数据包以演示ProjectREALSSIS设计。
返回页首
数据配置文件
尽管此引用项目是以一个零售系统为核心的，ProjectREALETL还是代表了许多业务方案的提取要求。
对于ETL处理来说，通常存在一些日常处理要求，每天都需要将对源数据的更改和添加提取并处理到系统中去。
而且，每周都运行一组过程来管理维度模型的每周库存快照。
数据配置文件包含每日事务提取、维度更新和每周库存事实数据表管理。
事实数据表
关系模型是一种标准的星型架构设计，具有两类主要的事实数据表：
销售和库存。
销售事务每天收集，代表所有零售商店的详细产品购买（包括网络订单）。
处理的数百万事务需要添加到销售事实数据结构中，多数销售记录来自前一天的销售。
另外，有一小部分销售对于系统来说是新的，但却是迟到的历史销售。
所有的销售每天都被跟踪，ETL过程设计成允许对整天的数据进行多重处理。
跟踪的主要数据围绕各个项目事务的数量和销售量。
库存结构设计成一个以周为周期的标准快照事实数据表，库存位置每天更新，但随着每周增量进行历史管理。
在商店和分发中心按项目对库存进行跟踪，每周都会产生许多行，需要每天进行很多更改。
一个主要的报告目标是了解库存和销售趋势，防止出现脱销的情况。
因此，除了标准的“手头”数量，还跟踪“库存天数”事实数据，该信息以周为周期说明某项物品在商店或分发中心库存的天数。
一周结束时，库存级别被复制并为新一周的开始进行初始化，它是ETL需要进行的密集过程。
维度表
支持事实数据表的维度具有一些独特的特性，使设计变得有趣，而且突出了SSIS的不同方面。
产品维度具有数百万成员，包含标准的更改属性和历史属性，但要求只有在销售后才可以开始进行历史跟踪属性和层次结构更改。
本文中讨论了产品维度是如何影响ETL的。
除了产品维度外，还涉及到其他几种典型的零售维度。
由于源数据的管理方式，所有维度要求如果某个维度成员在事实数据处理过程中丢失，将向该维度添加一个具有相关业务键的占位符记录，直到全部维度源可以进行完整更新。
这称为“推断成员”，并且对ETL具有处理影响。
少数维度还可以从主事务或库存源表中直接获取，要将其添加到事实数据表中，需要进行特殊处理。
整个SSIS过程借助复杂维度说明了该工具的灵活性和扩展性，旨在为基于SQL2005平台构建的许多ETL设计提供好的参考。
返回页首
SSIS开发环境
BusinessIntelligence（BI）DevelopmentStudio基于MicrosoftVisualStudio（R）2005（VS）平台构建。
虽然与SQLServer2000中企业管理器UI有很多不同的地方，BIDevelopmentStudio的功能是本着让数据库管理员（DBA）、数据体系结构设计师以及开发人员之类的人士容易上手的思想实现的。
简化的、关注BI开发人员需求的UI配置文件减缓了VS带来的不良因素。
对于ProjectREAL，单一BI解决方案涵盖RecurringETL项目。
此项目按增量运行所有的SSIS数据包。
图1显示了具有共享数据源和SSIS数据包的解决方案资源管理器。
数据包按其功能命名。
存在多种类型的数据包；第一种也是最简单的一种是维度数据包。
源自其自身源实体的每个维度具有自己的数据包。
事实数据表数据包在设计上都类似，只是它们按各自的重复周期（每日或每周）进行命名-每日和每周对应不同的数据包是因为两者之间的业务逻辑是不同的。
例如，Fact_Daily_Store_Inventory数据包执行的处理任务与相对应的每周数据包Fact_Weekly_Store_Inventory执行的处理任务不同，即使它们都影响相同的事实数据表。
图1
查看实际尺寸图像。
对于处理协调，显示了称为加载组数据包的第三种数据包。
这些数据包不包含处理业务逻辑，用于处理前面描述的维度和事实数据包的工作流协调以及一些应用程序审核和可重新启动性。
图2显示了一个加载组数据包示例。
利用“执行数据包”任务执行子维度和事实数据包，使用控制流处理一些处理任务的工作流和并行化。
包含有助于过程审核和可重新启动的其他任务，这些将在本文的后面内容中进行更详细的介绍。
图2
查看实际尺寸图像。
源代码管理集成
基于VS的BIDevelopmentStudio具有的优点之一是源代码管理集成。
为了帮助避免当多个开发人员同时对相同的过程进行处理时出现开发“死锁”，REAL的共享开发环境使用MicrosoftVisualSourceSafe（R）（VSS）（虽然不限于VSS）。
VSS标准功能包括：
历史记录和备份，锁定签入和签出的数据包、数据源或文件以及进行版本比较以突出差别。
大部分源代码管理功能可以在File（文件）|SourceControl（源代码管理）菜单中找到，如图3所示。
图3
查看实际尺寸图像。
使用VisualSourceSafe或其他源代码管理应用程序实现源代码管理后，只要通过右键单击数据包或数据源，就可以访问BIDevelopmentStudio中的许多与对象相关的功能，如图4所示。
图4
查看实际尺寸图像。
命名和布局惯例
值得注意数据包布局、任务命名和注释的惯例。
为了保持一致性，所有任务和转换的命名以代表任务或转换类型的3或4位字母缩写开头，后跟3至4个字词的对象功能描述。
这非常有助于审核和日志记录，因为可以根据对象名称跟踪日志记录详细信息。
数据包布局通常采用任务或转换退出主控制流和数据流的顺序：
先从上到下，后从左到右。
注释给出每个任务的详细信息，有助于说明数据包。
图5
查看实际尺寸图像。
ETL审核和日志记录
数据包和子数据包是自定义的审核步骤，通过父工作流集成到数据包结构设计中，跟踪高级执行详细信息，包括数据包的开始、结束、失败次数以及帮助验证数据量和处理的行计数。
SSIS本身提供各种日志提供程序类型的详细数据包执行日志记录。
这些详细日志条目由事件驱动，并反规格化到选定提供程序的目标中。
例如，如果某个数据库被选作日志提供程序，则所有日志条目都会插入一张表中。
在整个执行过程中，事件是引擎的参考点，如OnWarning、OnValidation、OnExecute、OnPreExecute、OnPostExecute等。
图6突出显示了日志记录事件详细信息选择器。
图6
查看实际尺寸图像。
日志事件中的每项记录与任务或转换运行时所在的相应数据包的执行ID相关联。
执行ID是每次运行数据包时生成的唯一GUID。
我们可以想象，当以非常低的频率进行SSIS日志记录时，对于每个数据包都会产生成百上千（如果不是成千上万）的条目。
SSIS日志记录为进行故障排除提供了大量的详细信息。
但是，如果对SSIS事件不是很了解，又没有通过一种清晰的方式将数据包执行ID与引擎执行的特定数据包映射起来，则很难获取和理解这些信息。
对于ProjectREAL，目标是通过一些与BI相关的特定执行审核来增强内置日志记录提供程序，同时使用日志记录功能生成详细的具有向下钻取功能的报告。
审核结构要实现的功能有：
•
数据包和加载组的关联和标识。
•
仓库结构沿袭列的添加。
•
行计数验证审核。
•
具有向下钻取功能的ETL处理报告。
数据包和加载组跟踪
在支持和管理BI解决方案中，掌握ETL过程使用的数据包协调非常重要。
因此，建立了更高级别的跟踪表来实现一起执行的相关数据包的关联。
其次，执行单一数据包时还创建了一张表。
有了SSIS日志记录功能，这可能看起来有点多余，但在以数据包命名的内置日志记录提供程序的执行ID与更高级执行工作流的组进程ID之间形成了一种直观的映射。
由于数据包审核表中数据包的每次执行只存在一条记录，因此也简化了报告并使向下钻取报告功能能够实现。
ProjectREAL应用程序审核主要是通过“执行SQL”任务实现的。
对于工作流数据包，控制流中的第一个和最后一个步骤管理加载组审核。
在这个示例加载组数据包中，突出显示了处理此过程的控制流中的第一个和最后一个步骤。
图7
查看实际尺寸图像。
数据包级跟踪比数据包工作流审核低一个级别，它也采用类似设计，使用第一个和最后一个控制流步骤。
由于加载组数据包本身就是一个数据包，图7中的第二个步骤和倒数第二个步骤也审核“执行SQL”任务。
在维度或事实数据表数据包中，它们是第一个和最后一个步骤。
审核的另一个核心方面是快速识别错误。
“事件处理程序控制流”概念是SSIS的一大特色。
这些可以在数据包UI的第三个选项卡上找到。
为了快速识别错误，使用了OnError事件处理程序，该处理程序是在数据包级别上定义的，将捕获出现的任何数据包错误。
图8
查看实际尺寸图像。
而且，使用了一组数据包变量跟踪数据库与这些“执行SQL”任务涉及到的数据包之间的元数据。
使用的主要变量有系统变量system:
:
PackageExecutionID和system:
:
PackageName，以及用户变量user:
:
ETL_Load_ID。
该用户变量是在数据库中创建的标识符，并传递给数据包。
使用SSIS的父变量配置功能，这些变量还从父数据包传递给子数据包。
仓库的沿袭添加
批处理标识符ETL_Load_ID不仅将审核元数据连在一起以进行报告和隔离，而且在仓库中被用来标识记录源。
每一个维度和事实数据记录都源自一个特定的数据加载，此列标识该加载。
这对数据沿袭和验证以及出现数据崩溃时需要进行的手动更正非常有用。
源数据提取后，通过衍生列转换可以立即将批处理标识符添加到数据流中。
因此，任何下游转换都能使用此列进行更新、插入和跟踪，而且使元数据包含这些记录不需要什么开销。
图9
查看实际尺寸图像。
行计数跟踪
数据验证对于DBA来说帮助非常大，不仅作为BI解决方案的一部分用于管理和故障排除，而且有助于建立用户信任感。
如果用户对数据产生怀疑，解决方案就可能失败。
ProjectREAL使用行计数作为数据验证的一个方面。
尽管此级别的验证只代表一部分应该进行的验证，但它是验证中的一个可靠的优先层。
行计数是在数据流中使用行计数转换实现的。
行计数转换就是计算通过的行数并将值存储到一个预定义的用户变量中。
行计数转换的一大优点是需要的开销和资源非常少。
因此，在所有维度和事实数据包的核心数据流中，每个源数据的后面、目标或OLEDB命令的前面以及数据流中任何存在高值点的地方都插入了行计数转换。
这些计数存储到不同的变量中，并通过数据流转换后紧跟的“执行SQL”任务在数据库中持续起作用。
图10显示了数据流中这些行计数转换的实现。
图10
查看实际尺寸图像。
ETL报告
为了将所有内容联系在一起并形成条理清晰的信息供开发人员管理和进行故障排除时使用，设计了一系列的ReportingServices链接报告，这些报告与审核、验证和日志记录相关。
让我们回顾一下前面的内容，如果您阅读了审核讨论，就可能了解实现了关联元数据的支持架构。
该架构中使用了四个主要表格：
三个用户定义的表格和一个内置的SSIS日志记录表格。
图11
查看实际尺寸图像。
ProjectREALETL报告基于这些结构，并通过ReportingServices表分组和链接的子报告提供能向下钻取到详细信息的高级加载组执行摘要。
包含以下级别的报告：
•
加载组执行摘要–开始和完成时间、持续时间摘要和执行状态。
•
数据包执行摘要–加载组关联、开始和完成时间、持续时间摘要和执行状态。
•
行计数详细信息–步骤描述、类型和行数。
•
数据包控制流任务摘要–从基本日志表获得的任务摘要汇总，包括任务持续时间和状态。
•
详细的事件日志分类–按选定数据包或任务排序和筛选的详细日志条目。
ETL报告清楚地说明了它在管理SSIS解决方案和进行故障排除中的作用。
图12
查看实际尺寸图像。
动态配置设计
SSIS数据包的可管理性、分发和部署的关键之处在于配置。
SSIS采用多种方法来在运行时配置数据包属性，允许更新连接信息、变量和任何其他需要在执行时动态配置的任务或转换属性。
提供了几种内置的配置方法，涵盖不同解决方案可以使用的多种环境要求，包括：
•
配置文件
•
环境变量
•
SQL配置表格
•
父数据包变量
当从开发ETL数据包转到生产该数据包时，就能看到这项功能的实际价值。
在大多数情况下，只要在配置系统中改变几个条目就可以实现这个目的。
SQL配置
ProjectREAL的目标是将配置进行集中化，使ETL设计可以部署到向上扩展模型和分布式模型的两种独特环境中。
与此类似，还提供了多种版本的REAL解决方案（完全版本、示例版本和演示版本）以供参考和演示。
因此，通过内置SQL配置，根据解决方案的不同数据库版本对配置进行集中化，这样可以将配置属性和映射放到关系表中，数据包之间共享配置属性和映射。
要打开集中化的管理工具，请选择SSIS，然后选择Configurations（配置）。
图13显示了SQL配置选项。
图13
查看实际尺寸图像。
在配置表中，用多个条目表示不同的属性。
第一组条目是有关连接的。
需要指出的是如何应用连接的配置条目，以及对于一个数据包来说解决方案数据源是如何工作的。
从数据源对象创建连接后，该连接是运行时的数据源，执行数据包时该连接不会根据父数据源而更新。
数据源是设计时结构，因此，当在UI中打开一个数据包时，那些从SSIS解决方案中的数据源创建的连接将被更新。
由于这种原因，连接非常适合用于配置，它们通常需要根据环境（开发、测试和生产）动态变化。
ProjectREAL配置表中的其他条目是可变映射。
这些可变映射可以更新ETL中用来处理逻辑和管理的可变值。
XML文件配置
在图13中，相应SQL配置表的位置由数据包连接决定。
但是，如果所有的连接信息都放在配置表中，则将出现循环引用，导致使用硬编码的数据包连接值，而这是我们不希望看到的。
为了防止这种情况发生，使用了第二种配置类型-XML文件配置。
此外，在将配置集中到一个数据库表格的目标下，XML文件中只需要一个配置条目-指向包含SQL配置表格的数据库的连接字符串。
正如您看到的那样，XML文件配置实际上只有一个属性-XML文件的位置和文件名。
图14
查看实际尺寸图像。
文件配置的一大特色是能够使用定义配置文件位置的服务器环境变量。
由于所有的数据包都引用此文件，使用环境变量使得只用一个位置就可以实现文件更改。
这对部署也非常有用，执行这些数据包的其他服务器可以使用不同的文件位置和文件名。
使用环境变量不同于SSIS环境变量配置，后者允许一个服务器包含多个替代任何数据包属性的环境变量。
父变量配置
到现在为止，上述所有ProjectREAL配置使用都是针对全局属性（即指定环境中每次执行一个数据包或一组数据包所用的连接和变量）的。
而有些配置需要仅限于数据包的特定执行和数据包参与的工作流组的特定执行。
例如，工作流组的批处理标识符ETL_Load_ID是在加载组数据包的初始步骤创建的并用于所有的子数据包。
数据包的每次执行都是在不同批处理环境下进行的，因此，此变量的配置需要根据执行的数据包动态变化。
SSIS的父变量配置功能使子数据包可以继承父数据包的变量。
这与SSIS的前身DTS不同，在DTS中，变量是从父数据包传给子数据包的。
在SSIS中，子数据包可以通过名称请求父数据包中的变量，从而能够从任何调用的使用“执行数据包”任务来调用子数据包的父数据包继承变量。
ProjectREAL要求存在一种只针对一个数据包或一组数据包执行实例的配置，这完全可以通过父变量配置功能实现。
就像提到的那样，所有的维度和事实数据包都继承ETL_Load_ID，父数
升级会员 