ETL设计开发规范文档.docx
《ETL设计开发规范文档.docx》由会员分享，可在线阅读，更多相关《ETL设计开发规范文档.docx（60页珍藏版）》请在冰豆网上搜索。
ETL设计开发规范文档
ETL设计说明书
1.概述
ETL系统的核心功能就是按照本设计说明书的架构，将数据由数据源系统加载到数据仓库中。
其实现的困难在于ETL系统将面临复杂的源数据环境，包括多种多样的数据源平台、繁多的数据种类、巨大的加载数据量、错综复杂的数据关系和参差不齐的数据质量，这些都使ETL的架构和应用设计面临相当的挑战。
通过高效的ETL系统结构、层次化的应用功能划分和标准的程序模板，ETL系统和应用架构设计需要能够达到以下目标：
Ø支持在此框架下实现中心数据库所需要的ETL功能；
Ø支持在规定的批处理时间窗口（BatchWindow）内能够完成数据加载工作，即需要满足日常数据加载的性能需求；
Ø能够支持有效的应用程序开发模式，提高开发效率，尽量减少应用开发成本；
Ø减少系统维护的复杂性，支持后续增加新数据或功能的开发工作。
ETL设计说明书为ETL开发提供指导，着重叙述数据仓库系统ETL系统的架构、功能和实施模板，但未包含针对数据仓库中每个具体数据表的ETL详细设计，由开发人员根据《ETL数据对照》中所规定的ETL规则，按照ETL设计的应用开发流程和规范，参照模板程序，实现每一个ETL应用程序。
再通过DataStage的作业调度功能依据《JOB依赖关系说明书》将所有ETL应用程序有机地联系起来，完成复杂的数据ETL过程。
ETL过程依赖于源数据的准备就绪，本设计说明书并未详细设计具体源数据准备的控制流程，根据项目的实际情况，对于源数据准备就绪的接口设计将体现在单独的文档《ETL源数据就绪接口设计说明书》中。
同时，本设计说明书不包括有关ETL投产运行过程的环境说明、管理方法、流程的说明，这部分的内容将包括在投产准备阶段相应的规范和文档中。
由于ETL的复杂性，本设计尝试从多个层面进行说明，希望能够尽可能回答开发过程中所面临的问题达到指导开发的目的，但实际开发过程中，开发人员仍然可能遇到设计说明书没有涉及的问题，因此，遵循设计的基本思想，通过开发人员的反馈，在开发的过程中不断地完善和修正设计，对于ETL的开发是非常重要的。
对于任何ETL开发过程中遇到的技术问题，开发人员需要与设计人员协商讨论，以迅速解决问题，保证开发顺利进行。
而同时，为保证ETL系统架构的完整、统一、程序的可维护性以及开发的可管理性，对设计的修改必须得到控制，重要的变动必须通过版本管理流程来协调进行。
本设计说明书将包括以下部分：
1．设计策略：
简要叙述本项目ETL开发中包括实现方法、技术选择、工具和平台选择等方面策略性的思路。
2．架构设计：
设计ETL整个应用的架构。
包括逻辑架构和物理架构。
3．应用框架：
按照架构设计，确定ETL应用的基本架构，以及每个步骤的高层设计思想，提出程序开发的模式，作为应用程序设计的指导。
4．开发规范：
定义开发过程中需要遵循的开发标准，如程序和文件的命名规范、文件结构、版本控制等等。
5．系统环境：
规范ETL开发、测试及生产环境
6．应用设计：
参照流程设计和数据对照表，设计每个任务的具体实现，作为开发的基础。
在程序设计中包括任务中每个步骤的命名、功能、输入和输出数据、运行条件和参数、需要使用的中间数据的数据结构、存储格式和存储位置。
7．ETL开发流程及管理：
规范ETL开发的步骤及管理方法
8．ETL质量控制及错误处理：
具体说明如何保证入库数据质量的主要方法及手段
9．附录提供了ETL开发过程中需要产生的相关文档的模板，通过ETL的实施，这些模板需要根据项目的实际状况同步完成
本设计方案基于以下前提：
1．预计每日数据处理量不超过1G，运行效率的要求不高，因此本项目完全采用ETL工具DataStage开发实现ETL过程，本设计完全基于DataStage进行ETL设计
2．由于源系统为同步发开的新系统，新系统并不提供历史数据，对于历史数据的加载将先将数据导入源系统，再从源系统进行ETL加载，以保证ETL接口的统一
2.ETL开发策略
Ø由于DataWarehouse中存储基础数据，需要采用的数据转换比较简单，ETL程序开发效率是开发中需要解决的主要矛盾，因此，为提高开发效率，将完全采用工具来开发ETL程序。
Ø在ETL过程中中间数据文件采用文本文件，为提高运行速度，可以先临时转换成HASH文件再进行Refrence的处理。
Ø对所有数据仓库表，在ETL过程中暂不建立索引，未来是否需要对实际数据仓库建立索引，将根据ETLBatchWindows和ETL系统性能，再行决定。
Ø因为初始数据和历史数据加载是一次性执行，所以初始数据和历史数据的加载主要采用手工方式进行，暂时不需要复杂的工作调度程序。
不实现全自动的初始数据ETL。
Ø对不同的数据表的每个ETL步骤都有一个独立的任务。
不同任务可以是使用不同参数的同一个程序，如数据加载程序。
同一类型的程序，如抽取，转换，加载，等等，尽量采用相同的程序结构，减少设计复杂度。
Ø数据仓库的加载和数据集市的加载将采用相同的ETL架构实现。
差别在于数据集市的数据源就是数据仓库本身。
加载数据仓库的中间文件（CIF）同时也可作为计算相应数据集市的事实表的基础，以提高数据共享，加快处理速度。
Ø所有ETL程序的实现结构上力求单纯统一，例如，所有LoadJob在处理数据入库的时候采用统一的Stage加载。
3.ETL系统架构设计
3.1ETL整体框架
本章从宏观体系结构的高度，概要叙述ETL系统的基本架构和设计思想，着重于描述架构的特点、系统主要组成、ETL各个部分的基本功能和它们之间的关系以及方案选择的出发点。
ETL系统需要能够在限定的时间内完成对日常数据的周期性的自动加载流程，支持对初始数据及历史数据的加载，并满足未来扩充的要求。
数据仓库系统的数十个目标数据表和相应数量的数据源意味着ETL程序的复杂性，庞大的数据量则需要充分考虑系统运行的效率，为开发复杂的程序，就要求灵活而简单明了的程序结构，而程序的效率要求的优化又往往需要针对不同数据的个性化设计，因此，ETL的设计必须在开发的可管理性和程序性能之间平衡，有些实现复杂、个性化突出的做法就要让位于要求一致的程序结构。
这样的平衡是本设计中很重要的一个考虑因素。
在基于此设计思路的ETL架构下，每个数据表的ETL过程按照ETL的特性统一为3个标准步骤，即数据抽取/变换（Extract/Convert）、数据转换（Transform）和数据加载（Load），所有数据的ETL都被纳入到这个标准框架中，因此，所有需开发的ETL程序的流程也就被对应分为3个主要的步骤,每个步骤需要记录完整的处理中间状态及完善的日志信息，对于程序员来说明，遵循统一的架构开发可以保证所有开发的程序的结构一致性，便于程序的管理，同时对于开发和测试人员来说，根据不同步骤的中间状态记录及日志信息很容易定位及修正程序的bug。
3.2ETL系统逻辑架构
上图是ETL系统逻辑架构。
从宏观设计上，历史数据、初始数据加载和日常数据加载的ETL都将按照此架构设计。
该架构将ETL作为一个整体来设计。
对于数据仓库的加载，ETL分为数据抽取（Extract）、数据变换（Convert）、数据转换（Transform）以及数据加载（Load）4个阶段。
每个阶段之间以文本文件作为接口，即数据抽取（Extract）阶段读取数据源产生EXF文件，CSS（Converting/Sort/Split）阶段读取EXF文件产生CIF文件，数据转换（Transform）阶段读取CIF文件产生PLF文件，数据加载（Load）阶段读取PLF文件加载到数据仓库。
此架构将数据抽取、转换和加载分隔开，以CIF（CommonInterfaceFormat）作为数据仓库表和数据源之间的桥梁，从而使每个功能相对独立，减少各功能相互间的耦合度，同时，每个模块的功能被细分后，逻辑更加简单，更容易控制开发错误，提高开发效率。
另外，也便于系统运行过程中的错误追综和异常恢复。
对于数据集市的加载，由于数据质量已经得到保证，ETL过程不再分割，一个目标集市表直接对应一个ETLJob完成从数据仓库表通过Aggregation加载到数据集市，由于从数据仓库到数据集市的加载相对简单，因此本设计说明书着重于从数据源到数据仓库的加载过程。
3.2.1ETL系统的备份和恢复
ETL系统的备份包括两个部分，即ETL运行环境备份及数据库的备份。
运行备份是指为保证如果运行的ETL系统崩溃时可以通过备份的ETL系统继续完成ETL的工作，为达到这个目的，应安装两台DataStage环境，并建立相同的配置，其中一台处于运行状态，而另一台为待机状态。
每日在日常ETL完成后对运行环境的各文件进行备份，即将ETL的运行目录（本项目中为E:
\***ETL）转储到外挂磁盘或外部存储介质。
而数据库的数据备份对于ETL非常重要，建议系统管理员每日做数据的完全备份，即采用Oracle的Export命令对数据仓库的Schema做完全的备份，每天保留一个备份文件，建议至少保留7天。
ETL系统的恢复相应也包括两个部分，即运行恢复及数据恢复
运行恢复是指当运行系统遇到严重故障如硬件故障、操作系统崩溃等无法及时修复时，启用备份的运行系统继续，通过将上一日备份的ETL环境恢复到待机系统，然后启动待机系统运行日常ETL。
数据库恢复通常两种情况下会用到，一种是数据库系统本身出了故障需要重新安装，这时需要将上一日备份的数据恢复到新的数据库环境中。
还有一种是数据加载过程中发现几天以前加载了某些有问题的数据，需要从之前某一天开始重新加载修正后的数据，这时需要将指定日的备份重新恢复到数据仓库中，然后顺序运行每日的日常ETL
4.ETL应用框架设计
4.1ETL应用架构逻辑图
上图是ETL应用程序的架构图，ETL架构从功能上被划分为三个层次：
ØETL作业及作业调度
通过作业调度功能，管理员根据目标数据表的更新周期和源数据就绪时间，制定日常数据的ETL的时刻表。
管理员通过DataStage的作业调度功能进行运行时刻设置，自动在规定条件满足时启动相应的ETL作业。
每个目标数据表ETL过程对应一组顺序执行的Entity作业（包括TransformJOB和LoadJOB）形成的一个Sequence，每个CIF文件的ETL过程则对应一组顺序执行CIF作业（包括ExtractJOB和CSSJOB）形成的一个Sequence。
这些ETL作业将其中的每个步骤，即抽取、CSS、转换、加载等ETL功能模块有机地联系起来。
而作业调度是将CIF逻辑的作业和Entity逻辑的作业按照CIF与Entity的对应关系联系起来，从而控制该ETL过程的运作。
ØETL功能模块
ETL功能模块层次中包含实现每个ETL步骤的程序，即上面所述的Extract、CSS、TR和LOAD程序（LOAD又分为PreLoad、LOAD、PostLoad）。
每个模块实现一个特定的功能。
各模块之间无任何调用关系，它们之间可能的关系仅仅是一个模块的输出文件是另一个模块需要读取和加工的文件。
一个ETL功能模块就是一个DataStageJob，每个功能模块的逻辑都只是ETL过程中一个相对独立的部分。
ØETL控制环境
ETL功能模块的运行需要由相应的参数进行控制，同时在各模块之间也存在很多控制文件及调用一些公共的功能，功能模块在运行过程中可能产生拒绝文件，针对功能模块的运行状况会有产生一些监控信息等等，这些对于ETL功能模块的运行起到控制与支撑的环境以及相应的维护管理程序构成ETL架构环境。
上两层构成ETL应用，而ETL控制环境则为以上层次的每个应用程序提供支持，应用层次上独立的功能模块都通过更上一个层次的逻辑关系联系起来，使每个模块的功能更加清晰、明确。
4.2ETL模式
根据模型的设计和源数据的情况，有四种数据ETL模式：
Ø完全刷新（Refresh，Type1）：
数据仓库数据表中只包括最新的数据，每次加载均删除原有数据，然后完全加载最新的源数据。
如大多数参数表的加载都采用这种模式。
这种模式下，数据抽取程序抽取源数据中的所有记录，在加载前，将目标数据表清空，然后加载所有记录。
为提高删除数据的速度，一般是采用Truncate清空数据表而不采用SQLDelete进行删除。
本项目中由于主要目标是实现Daily报表，因此大部分的表均为此种类型。
如CO_DATE_MF表就是这种类型。
Ø镜像增量（SnapshotAppend，Type2）：
源数据中的记录定期更新，但记录中包括记录时间字段，源数据中保存了数据历史的记录，ETL可以通过记录时间将增量数据从源数据抽取出来以附加的方式加载到数据仓库中，数据的历史记录也会被保留在数据仓库中。
目前项目中没有这种类型的数据。
Ø事件增量（EventAppend，Type3）：
每一个记录是一个新的事件，相互之间没有必然的联系，新记录不是对原有记录数值的变更，记录包括时间字段，可以通过时间字段将新增数据抽取出来加载到数据库中。
如CO_PO_HIST表就是这种类型
Ø镜像比较（SnapshotDelta，Type4）：
数据仓库数据具有生效日期字段以保存数据的历史信息，而源数据不保留历史并且每天都可能被更新。
因此，只能将新的镜像数据与上次加载的数据的镜像进行比较，找出变更部分（即Delta），更新历史数据被更新记录的生效终止日期，并添加变更后的数据。
大多数源数据中需保存历史信息的维表。
如CO_Plant_MF表就是这种类型。
4.3数据抽取（Extract）和数据变换（Convert）
4.3.1数据抽取（Extract）
数据抽取是从数据源获取所需数据的过程。
数据抽取过程会过滤掉数据仓库中不需要的源数据字段或数据记录。
数据抽取可以采用PULL和PUSH两种方式。
PUSH就是指由源系统按照双方定义的数据格式，主动将符合要求的数据抽取出来，形成接口数据表或数据视图供ETL系统使用。
PULL则是由ETL程序直接访问数据源来获取数据的方式。
为提高ETL效率，数据在进入ETL系统后，EXF文件都将转换为FlatText文件格式。
整个ETL过程中，除数据抽取外，都不使用效率较差的SQL方式进行数据处理。
由于本项目ETL系统可以访问所有源系统，而日数据量不大，综合上面的分析，从ETL程序设计的灵活性、可控性和整体结构的一致性考虑，尽量采用Pull的方式，减少对源系统的影响和对其他开发队伍的依赖，并减少网络压力。
4.3.2数据变换（Convert）
Convert的任务是逐条记录的检查数据，将每个字段转换为遵循数据仓库标准的数据格式，即对数据类型和数据格式进行转换，并对空字段赋予适当的缺省值，形成规整的数据结构，对于不符合要求的数据，写入拒绝文件（Reject文件）中。
数据变换主要的工作有：
Ø格式变换，如所有日期格式统一为yyyy-mm-dd；
Ø赋缺省值，在数据仓库中定义取值不为空的字段在源数据对应的字段可能存在没有取值的记录，这时根据业务需要，可能有两种处理办法，一是将该记录写入到Reject文件中，由业务部门根据Reject文件检查并修补源数据，另一种是在Convert阶段直接赋一个缺省值；
Ø类型变换,如将源系统的Number类型转为vahar2类型等
Ø长度变换,如将源系统中定义的vahar2（10）转为vahar2（20）等
Ø代码转换，如源系统的某些字段经过代码升级以后，将老的代码转为新的代码等。
Ø数值转换，如数值单位由万元转为元等。
4.3.3数据分割（Split）
同一个数据源表的数据可能被多个数据仓库表使用，这就需要将一个数据源按不同的条件通过Extract和Convert过程分成多个CIF文件以对应于不同的目标表的转换加载。
正如前面所讨论的，由于系统的数据量并不大，性能并不是第一考虑的指标，而本设计更着重于系统架构的统一性及完整性，因此为了使架构更加单一，对于按条件分割的情况将在Extract的步骤完成，即按不同的条件分别抽取为不同的EXF，然后再由不同的Convert程序变换为各自对应的CIF，而对于需要排序分割的情况，则在Transform时处理，在真正开始Transform之前先将CIF排序为新的临时的CIF文件。
4.4数据转换（Transform）
数据转换（Transform）是按照目标表的数据结构，对一个或多个源数据的字段进行翻译、匹配、聚合等操作得到目标数据的字段。
数据转换主要包括格式和字段合并与拆分、数据翻译、数据匹配、数据聚合以及其他复杂计算等。
4.4.1字段合并与拆分
字段合并是指源数据的多个字段合并为目标数据的一个字段。
字段拆分是指将源数据中一个表的一个字段拆分为目标数据的多个字段。
4.4.2赋缺省值
对于数据仓库中有的字段，在源系统中并没有相对应的源字段，这时根据模型的设计，可能需要缺省赋一个值
4.4.3数据排序（Sort）
TR程序有时需要对于两个或多个CIF文件merge，在merge之前需要将CIF文件按所要求的key排好序，这样可以加快merge的速度，排序的过程在Transform之前进行。
在DataStage中提供了排序的Stage功能，一般在CSJOB的最后调用该Stage，但Stage的性能并不特别高，如果对于性能有进一步的要求，可以采用第三方如CoSort等工具软件。
4.4.4数据翻译（Lookup）
将源系统中一些表示状态、类型等的代码直接翻译为其所表达的意思，或反之。
数据翻译需要用到参考表（ReferenceTable），数据参考表一般是字典表或根据源数据与目标数据的定义手工产生，如果数据翻译时在参考表中找不到对应的对照，根据业务规则，需要将对应的记录Reject出来或赋缺省值。
4.4.5数据合并（Merge）
按一定条件（一般是key值相等）对数据进行合并，找出描述同一对象的分布在不同数据表中的记录，并把这些记录联系起来。
数据合并其实是数据翻译的一种特殊情况，主要用于数据量特别大的情况，数据合并在实现方式上一般先对要合并的两个表分别排序（Sort），然后顺序对两个表的记录进行匹配合并，这样可以大大加快处理的速度。
4.4.6数据聚合（Aggregate）
对数据按照不同分组进行汇总等统计计算，一般是用于汇总表的计算，主要的聚合种类有：
Ø求和
Ø求平均值
Ø求记录数
Ø求最小值
Ø求最大值
Ø取第一行
Ø取最后一行
原则上，ETL只处理规律而重复性大的数据聚合，如汇总、取平均值、找最大最小值等，而不用于复杂计算，以减少开发成本和系统负载。
对于不规律而且复杂的计算，应该由源系统端将数据计算好。
4.4.7文件比较（FileCompare）
对应于四种ETL模式，数据转换为PLF文件后需要进行不同的处理，在Type1、Type2和Type3模式下，PLF可以直接由数据加载过程加载到数据仓库中，而Type4则由于存在有效日期，需要将当日的snapshot数据与历史数据镜像进行比较，找出需要添加和需要更新的记录，然后产生真正可以向数据库加载的PLF文件，再由数据加载过程将PLF文件加载到数据仓库中。
4.4.8其他复杂计算
在数据仓库中定义的某些字段需要按照业务规则进行复杂计算才能得到，主要有两类：
Ø主要针对一些在数据源中找不到直接对应的字段，需要在ETL过程中通过相关字段计算才能得出的字段。
Ø原则上复杂的计算并不在ETL中完成，对于一定需要由ETL来完成的复杂计算字段，采取在ETL加载时该字段先留空，在加载完成以后调用的存储过程来计算，但为了管理与调度的统一，可以在DataStage中调用存储过程以统一调度。
4.5数据加载（Load）
经过数据转换生成的PLF文件的结构与数据仓库数据表的结构完全一致，可以直接通过数据加载工具，以BulkLoad的方式加载到数据仓库中。
数据加载工作将分为3步进行。
4.5.1Pre-Load
在真正进行数据加载之前还可能需要完成以下准备工作：
Ø删除数据仓库中数据表的索引，提高加载效率。
主要是针对detail及fact大表，可以直接调用DBA所创建的索引维护脚本。
DBA调试过数据仓库后，必须更新相应的索引维护脚本，以保证ETL能够正确删除和建立索引。
4.5.2Load
Load主要完成将PLF文件的数据加载到数据仓库的表中，需要用到的加载方式有三种：
ØInsert：
只需要将PLF文件所有数据完全Insert到目标表中。
ØUpdAdd：
需要对目标表同时做Update及Insert操作，根据primarykey，对于已有的记录进行Update操作，对于不存在的记录做Insert的操作，对于数据量大的表，由于此操作的效率非常低，可以采用先将PLF文件分割为Delet文件及Insert文件，然后先将Delete文件中的记录根据primaykey对应从数据仓库中删除，然后再从Insert文件中将所有记录全部Insert到目标表中。
ØRefresh：
即将目标表的数据完全更新，一般的做法是先Truncate目标表的数据，然后再完全Insert要加载的记录
加载过程中，数据仓库关闭数据RI（ReferentialIntegrity）管理功能，数据库的RI检查由ETL应用程序完成。
4.5.3Post-Load
Ø重新生成索引：
在pre-load阶段删除的索引需在此重建，该过程也是调用DBA维护的索引维护脚本。
Ø文件清理：
删除不需要的临时文件及临时表。
4.6ETL进程和进程调度
进程调度的功能比较单纯，就是在规定的时刻启动程序，并记录系统运行情况和运行结果。
不同数据表的更新周期不同，因此，进程调度需要能够支持日周月等多种不同的启动周期，并通过设定启动时间来确定每个任务在何时启动运行。
只有日常数据加载才需要考虑进程调度的问题。
而对于初始数据及历史数据的加载，由于是一次性的工作，将采取手工启动加载的方式，所以无需制定对初始数据及历史数据加载制度化的进程调度。
在ETL程序的抽取过程运行之前一定要保证其抽取的源数据已经准备就绪，本项目关于源数据准备就绪的接口采用数据就绪标识及时间约定相结合的方式，即源系统部门在源数据准备就绪以后向就绪接口表中写入就绪标识，而ETLJob从约定的时间开始不断检查该该标识，直到判断该标识已经就绪，将启动正常的ETLJob开始运行。
4.7管理功能（ManagementInterface）
本系统使用DataStage作为ETL运行及管理工具，DataStage已经提供了比较友好的管理界面供用户可以用来监控系统的运行状况
通过DataStageDirector工具，可以完成以下管理及运行功能：
Ø查看ETLJob运行结果，如finished,finishedwithwaring,abort等；
Ø控制ETLJob的运行，如启动或停止Job等；
Ø查看ETLJob运行日志；
Ø实时监控ETLJob的运行状态
通过DataStageSAPAdministrator，可以配置SAP的连接参数
通过DataStageAdministrator，可以设置ETLJob的运行选项，如自动删除过时的Job日志的选项、运行人员授权等
但依然有部分管理功能无法由DataStage直接提供，需要通过开发来完成，但所有的开发都使用DataStageJob的形式，这样有利于在DataStage中统一进行管理，主要开发的管理功能应包括：
Ø参数设置，通过参数设置JobSetParam设置ETL运行的参数，实际上也就是通过界面实现对于参数文件JobParams.cfg的维护；
ØJob运行情况的Email通知，主要用于每日指定的时间检查当日的Job运行状况并通过Email的形式将检查结果发送给运
升级会员 