本人理解之五：ETL的抽取策略(ZZ)

本文所提到的数据加载策略为OLTP系统作为源系统，并进行ETL数据加载到OLAP系统中所采用的一般数据加载策略。依循数据仓库的工作方式，原始资料由源数据库被抽取出来后，将在中间过程被写入到” Operational Data Store”(ODS)，ODS是被设计用来存储中间数据和核查校验数据的。通过ODS，数据将被萃取、预先被计算及整理，而后被导入数据仓库作进一步的报表生成与分析。所以通常意义的ETL过程涵盖了两个方面的内容：1. 从源数据库到中间步骤的ODS; 2. 从ODS到最终的数据仓库。目标数据库存放数据的时间区间比源数据库长。例如源数据库存放的是3个月的交易生产数据，ODS存放了1年左右的数据，而数据仓库存放了3－5年的历史数据。

一般ETL数据加载存在以下几种方案：

1、 时戳方式

需要在OLTP系统中业务表中统一添加时间字段作为时戳（如表中已有相应的时间字段，可以不必添加），每当OLTP系统中更新修改业务数据时，必须同时修改时戳字段值。当作ETL加载时，通过系统时间与时戳字段的比较来决定进行何种数据抽取。

优点：ETL系统设计清晰，源数据抽取相对清楚简单，速度快。可以实现数据的递增加载。

缺点：时戳维护需要由OLTP系统完成，需要修改原OLTP系统中业务表结构；且所有添加时戳的表，在业务系统中，数据发生变化时，同时更新时戳字段，需要对原OLTP系统业务操作程序作修改，工作量大，改动面大，风险大。但如果业务表在最初设计的时候考虑到这点，应用此方案是最好的选择。

2、 日志表方式

在OLTP系统中添加系统日志表，当业务数据发生变化时，更新维护日志表内容，当作ETL加载时，通过读日志表数据决定加载那些数据及如何加载。

优点：不需要修改OLTP表结构，源数据抽取清楚，速度较快。可以实现数据的递增加载。

缺点：日志表维护需要由OLTP系统完成，需要对OLTP系统业务操作程序作修改，记录日志信息。日志表维护较为麻烦，对原有系统有较大影响。工作量较大，改动较大，有一定风险。

3、 全表比对方式

在ETL过程中，抽取所有源数据，并进行相应规则转换，完成后先不插入目标，而对每条数据进行目标表比对。根据主键值进行插入与更新的判定，目标表已存在该主键值的，表示该记录已有，并进行其余字段比对，如有不同，进行Update操作，如目标表没有存在该主键值，表示该记录还没有，即进行Insert操作。

优点：对已有系统表结构不产生影响，不需要修改业务操作程序，所有抽取规则由ETL完成，管理维护统一，可以实现数据的递增加载，没有风险。

缺点：ETL比对较复杂，设计较为复杂，速度较慢

4、 全表删除插入方式

每次ETL操作均删除目标表数据，由ETL全新加载数据。

优点：ETL加载规则简单，速度快。

缺点：对于维表加代理键不适应，当OLTP系统产生删除数据操作时，OLAP层将不会记录到所删除的历史数据。不可以实现数据的递增加载。

5、 设置触发器方式

通过在源系统的数据库中设置触发器，每当有Update、Insert、Delete操作时触发一个事件将发生改变的记录抽取到相应的临时表中。

优点：ETL加载规则简单，速度快，不需要修改OLTP表结构，可以实现数据的递增加载。

缺点：对源系统性能有一些影响。需建立一张临时表。

6、 利用Oracle变化数据捕捉（Change Data Capture，CDC）特性

CDC特性是在Oracle9i数据库中引入的，它简化了识别自上次提取后发生变化的数据的过程。CDC能够帮助识别从上次提取之后发生变化的数据。利用CDC，在对源表进行Update、Insert或Delete等操作的同时就可以提取数据，并且变化的数据被保存在数据库的变化表中。这样就可以捕获发生变化的数据，然后利用数据库视图以一种可控的方式提供给目标系统。

4@5l@4@11@9@11@9@5xe" o:preferrelative="t" filled="f" stroked="f" coordsize="21600,21600" o:spt="75" />

一旦数据库做好了CDC准备，就可以按照以下步骤设置CDC环境:
1. 确定源表。
2. 设置发布者。
3. 创建变化表。
4. 设置订阅者。
5. 订阅源表并激活订阅过程。
6. 设置CDC窗口。
7. 准备订阅者视图。
8. 访问变化表中的数据。
9. 删除订阅者视图，并清除CDC窗口。
10. 重复第6到第9步，以查看新数据。
优点：提供了易于使用的API来设置CDC环境，缩短ETL的时间。不需要修改OLTP表结构,可以实现数据的递增加载。
缺点：对此特性还需研究。

对数据抽取方法的选择，首先要考虑的有哪些方法具有实施的条件，哪些方法没有实施的条件。方法实施的效率，因为每个源系统给予数据抽取的时间窗口有限，可以采用并行处理的方式，将一个大的数据抽取分成几个小的数据抽取进行并行处理。对于初始抽取采用静态数据抽取。对以后的抽取而言，如果用时间戳的方式可以实现，用时间戳的方式，因为这种方式根据时间标记比较容易判断出哪些数据已经抽取到临时存储区，哪些数据还没有进行抽取，我们只需要抽取哪些没有进行抽取的数据记录。如果不能采用时间戳的方式，可以考虑采用交易日志的方式，交易日志处理需要专门的程序。但是对一个数据库而言它的日志格式是固定的，目前主要是Informix、SQL Server等数据库，只需要编写这几种数据库日志格式处理的程序即可，而且不需要关心源系统，也不会影响到源系统。如果交易日志不能实现，采用多次抽取文件进行比较的方式。对于源程序中捕获的方式基本上不予考虑，如果涉及到的源系统很多，不可能修改每一个系统来为数据捕获。当然，也有可能是多种数据抽取方法的综合选择。

（1）、如果所集成OLTP系统为其他产商产品，则应尽量的降低因ETL而对现有系统产生的影响，及系统风险性。而性能的影响则可以通过两方面解决，一部分由硬件的升级进行解决，因为ETL除读表及写表操作外，所有转换均由ETL服务器在内存中完成，故高配置服务器将大大提升ETL运行速度；一部分由加载时机进行控制，加载时机采取在系统较为空闲时加载，同时并行多个加载等，可以降低对运行系统的影响。所以可以使用全表比对递增加载数据的方式作为此类系统的ETL数据加载规则。

（2）、如果原OLTP系统为自己开发产品，此次所作OLAP系统为在原系统上的系统，则可以考虑使用时辍或日志表方式，区别仅为对原系统的影响大小。

（3）、当数据实现递增加载时，OLAP系统中的聚合表，可由OLAP中的事实表数据二次ETL产生，此时由于OLAP数据的完整性与准确性，可以使用全表删除插入方式。

（4）、当数据实现递增加载时，对Oracle变化数据捕捉（Change Data Capture，CDC）特性非常了解的情况下，建议采用此方法。

本人理解之四：ETL的主要步骤(ZZ)

赞一个！很精辟实在的文章。
http://www.bicubes.com/modules/newbb/viewtopic.php?topic_id=237&forum=9

ETL(Extract Transform Loading, 数据抽取转化装载规则)是负责完成是数据源数据向数据仓库数据的转化的过程。是实施数据仓库中最重要的步骤。可以形象的说，ETL的角色相当于砖石修葺成 房子的过程。在数据仓库系统设计中最难的部分是用户需求分析和模型设计，那么工作量最大的就是ETL规则的设计和实施了，它要占到整个数据仓库设计工作量 的60%-70%，甚至更多。
　　下面是本人对ETL的几个重要步骤理解，和大家分享！

一、ODS区的数据采集：　 　最主要作用为了尽量减少对业务系统的影响。表结构可以不必和DW一致。根据具体业务需求和数据量情况，将数据源的数据放入ODS有各种不同的方法，比如 Oracle的数据库链路，表复制，SQL*LOADER，Teradata的Fastload，Sysbase的BCP等等。

　　需要解决的问题包括：
a、数据的时间差异性问题
　　在抽取旧有数据时，要将不同时期的数据定义统一，较早的数据不够完整或不符合新系统的数据规范，一般可以根据规则，在存入中转区的过程中予以更新或补充。

b、数据的平台多样性问题
　 　在抽取旧有数据时，大部分数据都可采用表复制方式直接导入数据中转区集中，再做处理，但有部分数据可能需要转换成文本文件或使用第三方工具如 Informatica等装载入数据中转区。这部分数据主要是与数据中转区数据库平台不一致的数据库数据，或非存储于数据库内的文本、excel等数据。

c 、数据的不稳定性问题
　　对于重要信息的完整历史变更记录，在抽取时可以根据各时期的历史信息，在抽取需要信息等基本属性的旧有数据时，要与相应时段的信息关联得到真实的历史属性。

d 、数据的依赖性问题
　　旧有业务系统的数据关联一般已有约束保证，代码表和参照表等数据也比较准确，但仍有少量数据不完整，对这部分数据，需根据地税的需求采取清洗策略，保证数据仓库各事实表和维表之间的关联完整有效。
　　数据仓库各事实表和维表的初始装载顺序有先后关系，要有一个集中的数据装载任务顺序方案，确保初始数据装载的准确。这可以通过操作系统或第三方工具的任务调度机制来保证。

二、数据转换、清洗：
　 　将ODS中的数据，按照数据仓库中数据存储结构进行合理的转换，转换步骤一般还要包含数据清洗的过程。数据清洗主要是针对源数据库中出现二义性、重复、 不完整、违反业务或逻辑规则等问题的数据数据进行统一的处理，一般包括如：NULL值处理，日期格式转换，数据类型转换等等。在清洗之前需要进行数据质量 分析，以找出存在问题的数据，否则数据清洗将无从谈起。数据装载是通过装载工具或自行编写的SQL程序将抽取、转换后的结果数据加载到目标数据库中。

　　数据质量问题具体表现在以下几个方面：
a、正确性（Accuracy）：数据是否正确的表示了现实或可证实的来源?
b、完整性（Integrity）：数据之间的参照完整性是否存在或一致?
c、一致性（Consistency）：数据是否被一致的定义或理解?
d、完备性（Completeness）：所有需要的数据都存在吗?
e、有效性（Validity）：数据是否在企业定义的可接受的范围之内?
f、时效性（Timeliness）：数据在需要的时侯是有效的吗?
g、可获取性（Accessibility）：数据是否易于获取、易于理解和易于使用?

　　以下综合说明数据仓库中数据质量要求，包括格式、完整性要求。
a、业务描述统一，对数据模型的不同版本融合、映射为唯一版本。包括：
　　1、在业务逻辑没有变化的前提下，旧的业务数据映射在新模型上。
　　2、 遗留系统的人事信息、考核相关信息与业务系统、行政其他模块要一致。
b、信息描述规范、完整。
　　1、不存在格式违规
　数据类型不存在潜在错误。
　　2 、参照完整性未被破坏
　数据不会找不到参照。
　　3 、不存在交叉系统匹配违规，数据被很好集成
　相同的数据存在于多个系统中，数据之间要匹配。
　　4 、数据在内部一致
　同样的纪录字段在同一个表中重复出现，不能有差别。

　　以下是对主要数据质量问题的清洗策略：

主要问题	表现形式	产生原因	清洗策略
数据完整性问题	大量的空值字段的出现	原OLTP系统中对很多字段没有做非空限制	1. 交由OLTP系统重新录入, 补齐 2. 在数据仓库对应的维表中建立一个新的字段, 将这些空值字段的值统一的赋值
	超出字典表范围	填写这些值的时候是直接让用户填写而非下拉框选择	1. 交由OLTP系统重新录入, 补齐 2. 在数据仓库对应的维表中建立一个新的字段, 将这些空值字段的值统一的赋值
数据一致性问题	一个特定的字段在不同的表中内容不同	录入, 同步的问题	1. 选取最可靠的表中的字段为确定值
	应该成为主键的值不唯一	原OLTP系统中未建立有效的主键关系	1. 消除错误, 重复的主键

三、数据加载：
　　将转换和清洗完的数据按照数据仓库的结构进行数据加载。需要考虑初始数据装载、数据刷新、加载顺序等等问题。
　　a、针对数据现状，初始导入有这样一些问题需要考虑：
1、如何解决时间差异性？
2、如何解决平台差异性？
3、如何适应数据的不稳定性？
4、如何解决数据依赖性？

　　b、数据刷新的策略要根据业务需求和应用系统的承受能力和数据情况决定。主要有这样一些问题需要考虑：1、如何解决时间差异性？
2、如何适应数据的不稳定性？
3、如何解决平台差异性？
4、如何解决数据依赖性？
5、如何减少对业务系统的影响？

　　c、不同的刷新任务类型，对业务系统的影响不同，刷新任务有以下种归类特性：
1、刷新频率：
实时刷新、每数小时、每日、每周、每月、不定期手动刷新。

2、刷新方式：
数据库表复制、文本文件ftp再装载、物化视图、数据库trigger。

3、数据加工方式：
简单插入更新、增加计算项字段、多表关联更新、汇总、多表关联汇总计算。

　　并可针对各种异常情况做处理：回滚，重新装载，断点重新装载等等，还可在任务完成后（或失败后）将日志以Email方式发给数据仓库管理人员。

四、汇总层、CUBE加载：
　　ODS加载进入数据仓库的数据只是底层详细层数据，还需按定义的汇总规则进行汇总，生成数据集市用的汇总表或CUBE。ETL流程是指完成每个维表数据及事实表数据导入的顺序, 其包括两个部分, 初始导入数据时的ETL流程, 及增量导入时的ETL流程。

初始导入数据时的ETL流程
第一步: 自动生成维的数据装载
　 　自动生成维一般来说就是日期，年度月份，年度等时间类维度（年度月份，年度其实都是日期维的一个层次，但某些事实表中没有日期信息，只有月份信息，所以 需额外建立此二维度），几乎数据仓库中每个数据模型都需使用时间类维度，在加载其它维度和事实之前，需要先将时间维度生成出来。

第二步: 手工维护维度装载
　　实际数据仓库开发中，很可能会有些维度的数据在业务系统中无发得到，典型的是一些外部信息指表的类型代码，是由数据仓库开发人员设计的。所以需要手工方式建立这些信息，然后导入数据仓库。

第三步: 缓慢变化维表数据装载
　　这些维度可以从业务系统中找到来源，但变化比较缓慢。对于初始装载时，需要考虑对缓慢变化维的处理方式要和增量刷新方式一致。
　　在装载事实表数据之前，需要先装载这些维表。需要注意的是，有些维本身就是事实表，其所依赖的维必须先装载完成。

第四步: 事实表数据装载
　　然后是初始装载所有的事实表数据。事实表之间也有依赖关系，某些事实表需在其他事实表装载之前装载。（如果ETL程序已保障了数据的完整性，也可以在将关联约束禁用的情况下不考虑先后顺序，但一般不建议）。

第五步：聚合表初始生成
　　许多数据仓库的前端应用，并非直接使用主题星型模型中的事实表数据，而是聚合表中汇总，运算好的数据。（Oracle OLAP Service所建立的ROLAP 和数据集市实际上也是使用一系列的经过大量预先计算得到的聚合表）

增量导入
第一步: 缓慢变化维表数据装载
　　每天将所有变化过的维度信息刷新到数据仓库中，维表数据的刷新必须现于事实表。

第二步: 事实表数据装载阶段
　　每天新增事实数据的导入，如同初始化导入一样，需要考虑任务之间的先后顺序。

第三步: 数据汇总和聚合
　　根据设定的聚合规则和时间段对数据进行聚合。

第四步: 作业调度和异常情况处理


五、任务调度策略
驱动策略
前导Job驱动：只有满足另外一个JOB成功后，自己才运行。文件驱动：当下传的文件到达，并经过检验准确后JOB才运行。时间驱动：当到达某个时点时，Job便开始运行。事件驱动：如人工参与，导致JOB执行。
通知设计：重要信息（成功/失败）的通知
1、成功退出
　　分段提交方式，当分段提交的当次任务都正确完成，即Job运行状态临时表中登记的作业状态全部为完成时，退出ETL调度。
　　自动提交方式，当当期所有的任务都正确完成，即Job运行状态表中登记的作业状态全部为完成时，退出ETL调度。
2、失败退出
　　关键作业异常，关键作业运行异常时，影响剩下的作业不能运行时，则退出ETL调度。
　　超过ETL时限，当超过预先设定的ETL?时限时，退出ETL调度。
　　数据库异常，当不能正常操作数据库时，退出ETL调度。
　　操作系统异常，当发生操作系统异常，导致程序不能正常运行，如文件系统异常导致读写文件错时，需要退出ETL调度。

3、手工退出，需要人为干预ETL调度的时候，能以手工操作的方式退出ETL调度。

数据抽取、清洗与转换 BI项目中ETL设计(ZZ)

http://tech.ccidnet.com/art/19215/20080807/1536767_1.html

ETL是将业务系统的数据经过抽取、清洗转换之后加载到数据仓库的过程，目的是将企业中的分散、零乱、标准不统一的数据整合到一起，为企业的决策提供分析的依据。

ETL是BI项目最重要的一个环节，通常情况下ETL会花掉整个项目的1/3的时间，ETL设计的好坏直接关接到BI项目的成败。ETL也是一个长期的过程，只有不断的发现问题并解决问题，才能使ETL运行效率更高，为项目后期开发提供准确的数据。

ETL的设计分三部分：数据抽取、数据的清洗转换、数据的加载。在设计ETL的时候 也是从这三部分出发。数据的抽取是从各个不同的数据源抽取到ODS中(这个过程也可以做一些数据的清洗和转换)，在抽取的过程中需要挑选不同的抽取方法， 尽可能的提高ETL的运行效率。ETL三个部分中，花费时间最长的是T(清洗、转换)的部分，一般情况下这部分工作量是整个ETL的2/3。数据的加载一 般在数据清洗完了之后直接写入DW中去。

ETL的实现有多种方法，常用的有三种，第一种是借助ETL工具如Oracle的 OWB、SQL server 2000的DTS、SQL Server2005的SSIS服务、informatic等实现，第二种是SQL方式实现，第三种是ETL工具和SQL相结合。前两种方法各有优缺点， 借助工具可以快速的建立起ETL工程，屏蔽复杂的编码任务，提高速度，降低难度，但是欠缺灵活性。SQL的方法优点是灵活，提高ETL运行效率，但是编码 复杂，对技术要求比较高。第三种是综合了前面二种的优点，极大的提高ETL的开发速度和效率。

数据的抽取

数据的抽取需要在调研阶段做大量工作，首先要搞清楚以下几个问题：数据是从几个业务系统中来?各个业务系统的数据库服务器运行什么DBMS?是否存在手工数据，手工数据量有多大?是否存在非结构化的数据?等等类似问题，当收集完这些信息之后才可以进行数据抽取的设计。

1、与存放DW的数据库系统相同的数据源处理方法

这一类数源在设计比较容易，一般情况下，DBMS(包括SQLServer，Oracle)都会提供数据库链接功能，在DW数据库服务器和原业务系统之间建立直接的链接关系就可以写Select 语句直接访问。

2、与DW数据库系统不同的数据源的处理方法

这一类数据源一般情况下也可以通过ODBC的方式建立数据库链接，如SQL Server和Oracle之间。如果不能建立数据库链接，可以有两种方式完成，一种是通过工具将源数据导出成.txt或者是.xls文件，然后再将这些 源系统文件导入到ODS中。另外一种方法通过程序接口来完成。

3、对于文件类型数据源(.txt,，xls)，可以培训业务人员利用数据库工具将这些数据导入到指定的数据库，然后从指定的数据库抽取。或者可以借助工具实现，如SQL SERVER 2005 的SSIS服务的平面数据源和平面目标等组件导入ODS中去

4、增量更新问题

对于数据量大的系统，必须考虑增量抽取。一般情况，业务系统会记录业务发生的时间， 可以用作增量的标志，每次抽取之前首先判断ODS中记录最大的时间，然后根据这个时间去业务系统取大于这个时间的所有记录。利用业务系统的时间戳，一般情 况下，业务系统没有或者部分有时间戳。

数据的清洗转换

一般情况下，数据仓库分为ODS、DW两部分，通常的做法是从业务系统到ODS做清洗，将脏数据和不完整数据过滤掉，再从ODS到DW的过程中转换，进行一些业务规则的计算和聚合。

1、数据清洗

数据清洗的任务是过滤那些不符合要求的数据，将过滤的结果交给业务主管部门，确认是否过滤掉还是由业务单位修正之后再进行抽取。不符合要求的数据主要是有不完整的数据、错误的数据和重复的数据三大类。

A、不完整的数据，其特征是是一些应该有的信息缺失，如供应商的名称，分公司的名 称，客户的区域信息缺失、业务系统中主表与明细表不能匹配等。需要将这一类数据过滤出来，按缺失的内容分别写入不同Excel文件向客户提交，要求在规定 的时间内补全。补全后才写入数据仓库。

B、错误的数据，产生原因是业务系统不够健全，在接收输入后没有进行判断直接写 入后台数据库造成的，比如数值数据输成全角数字字符、字符串数据后面有一个回车、日期格式不正确、日期越界等。这一类数据也要分类，对于类似于全角字符、 数据前后有不面见字符的问题只能写SQL的方式找出来，然后要求客户在业务系统修正之后抽取;日期格式不正确的或者是日期越界的这一类错误会导致ETL运 行失败，这一类错误需要去业务系统数据库用SQL的方式挑出来，交给业务主管部门要求限期修正，修正之后再抽取。

C、重复的数据，特别是维表中比较常见，将重复的数据的记录所有字段导出来，让客户确认并整理。

数据清洗是一个反复的过程，不可能在几天内完成，只有不断的发现问题，解决问题。对 于是否过滤、是否修正一般要求客户确认;对于过滤掉的数据，写入Excel文件或者将过滤数据写入数据表，在ETL开发的初期可以每天向业务单位发送过滤 数据的邮件，促使他们尽快的修正错误，同时也可以作为将来验证数据的依据。数据清洗需要注意的是不要将有用的数据过滤掉了，对于每个过滤规则认真进行验 证，并要用户确认才行。

2、数据转换

数据转换的任务主要是进行不一致的数据转换、数据粒度的转换和一些商务规则的计算。

A、不一致数据转换，这个过程是一个整合的过程，将不同业务系统的相同类型的数据统一，比如同一个供应商在结算系统的编码是XX0001，而在CRM中编码是YY0001，这样在抽取过来之后统一转换成一个编码。

B、数据粒度的转换，业务系统一般存储非常明细的数据，而数据仓库中的数据是用来分析的，不需要非常明细的数据，一般情况下，会将业务系统数据按照数据仓库粒度进行聚合。

C、商务规则的计算，不同的企业有不同的业务规则，不同的数据指标，这些指标有的时候不是简单的加加减减就能完成，这个时候需要在ETL中将这些数据指标计算好了之后存储在数据仓库中，供分析使用。

ETL日志与警告发送

◆1、ETL日志，记录日志的目的是随时可以知道ETL运行情况，如果出错了，出错在那里。

ETL日志分为三类。第一类是执行过程日志，是在ETL执行过程中每执行一步的记 录，记录每次运行每一步骤的起始时间，影响了多少行数据，流水账形式。第二类是错误日志，当某个模块出错的时候需要写错误日志，记录每次出错的时间，出错 的模块以及出错的信息等。第三类日志是总体日志，只记录ETL开始时间，结束时间是否成功信息。

如果使用ETL工具，工具会自动产生一些日志，这一类日志也可以作为ETL日志的一部分。

◆2、警告发送

ETL出错了，不仅要写ETL出错日志而且要向系统管理员发送警告，发送警告的方式有多种，常用的就是给系统管理员发送邮件，并附上出错的信息，方便管理员排查错误。