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的开发速度和效率。

数据的抽取

---

所谓数据抽取，就是从源数据系统抽取目标数据系统需要的数据，拿到数据数据以后再根据业务需求去对数据进项处理。之前我们介绍了数据的全量抽取、增量抽取的概念。今天我们来讲讲全量抽取与增量抽取的应用。

全量抽取应用较为广泛，因为我们在做业务处理分析过程中往往是需要对以前的历史数据结合分析的。但是全量抽取也不意味着就是把数据库里的所有历史数据都抽取过来，这样的方式太过于浪费资源。增量抽取也会分多种方式，具体的要和自己公司的的业务结合。比如我们可能把全量抽取分为全量覆盖和全量切片。全量覆盖不用再多说，全量分片的话，可以是截至某个时间点的所有数据，这样是为了把时间太久价值较低的数据或者是业务不需要的某段数据排除在外。

增量抽取机制相对于全量抽取较为复杂。我们介绍过增量抽取机制主要有这么几种：触发器方式、时间戳方式、全表删除插入方式、全表比对方式、日志表方式。其中触发器方式、时间戳方式应用的相对较多。

下面我们来介绍这两种增量抽取的应用：

触发器方式：该方式是根据抽取要求，在要被抽取的源表上建立插入、修改、删除3个触发器，每当源表中的数据发生变化，就被相应的触发器将变化的数据写入一个增量日志表，ETL的增量抽取则是从增量日志表中而不是直接在源表中抽取数据，同时增量日志表中抽取过的数据要及时被标记或删除。另外，增量日志表一般不存储增量数据的所有字段信息，而只是存储源表名称、更新的关键字值和更新操作类型(INSERT、UPDATE或DELETE)，ETL增量抽取进程首先根据源表名称和更新的关键字值，从源表中提取对应的完整记录，再根据更新操作类型，对目标表进行相应的处理。

例如，对于源表为ORACLE类型的数据库，采用触发器方式进行增量数据捕获的过程如下：

对表Itstar的所有DML操作记录在增量日志表DML_LOG中，注意增量日志表中并没有完全记录增量数据本身，只是记录了增量数据的来源。进行增量ETL时，只需要根据增量日志表中的记录情况，反查源表得到真正的增量数据。

1.创建增量日志表DML_LOG：

CREATE TABLE DML_LOG(ID NUMBER PRIMARY KEY，--自增主键TABLE_NAME VARCHAR2(200)，--源表名称RECORD_ID NUMBER，--源表增量记录的主键值DML_TYPE CHAR(1)，--增量类型，I表示新增；U表示更新；D表示删除EXECUTE DATE DATE --发生时间);

2. 为DML_LOG创建一个序列SEQ_DML_LOG上，以便触发器写增量日志表时生成ID值。

3. 针对要监听的每一张表，创建一个触发器，例如对表TEST创建触发器如下：

CREATE OR REPLACE TRIGGER Itstar BEFORE INSERT OR UPDATE OR DELETE ON itstar FOR EACH ROWDECLARE 1 DML TYPE VARCHAR2(1);BEGINIF INSERTING THEN L_DML TYPE：＝'I';ELSEIF UPDATING THEN L_DML TYPE：＝'U';ELSEIF DELETING THEN L_DML TYPE：＝'D';END IF;IF DELETING thenINSERT INTO DML_LOG(ID,TABLE_NAME,RECORD_ID,EXECUTE_DATE,DML_TYPE)VALUES(SEQ_DML_LOG.NEXTVAL,'TEST',:OLD.ID,SYSDATE,L_DML_TYPE);ELSEINSERT　INTO DML_LOG　(ID , TABLE_NAME , RECORD_ID , EXECUTE_DATE , DML_TYPE)(SEQ_DML_LOG.NEXTVAL,’TEST’,:NEW.ID,SYSDATE,L_DML_TYPE);END IF;END;

时间戳方式:指增量抽取时，抽取进程通过比较系统时间与抽取源表的时间戳字段的值来决定抽取哪些数据。这种方式需要在源表上增加一个时间戳字段，系统中更新修改表数据的时候，同时修改时间戳字段的值。有的数据库(例如SQL SERVER)的时间戳支持自动更新，即表的其它字段的数据发生改变时，时间戳字段的值会被自动更新为记录改变的时刻。在这种情下，进行ETL实施时就只需要在源表加上时间戳字段就可以了。对于不支持时间戳自动更新的数据库，这就要求业务系统在更新业务数据时，通过编程的方式手工更新时间戳字段。使用时间戳方式可以正常捕获源表的插入和更新操作，但对于删除操作则无能为力，需要结合其它机制才能完成。

数据的清洗转换

---

一般情况下，数据仓库分为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出错日志而且要向系统管理员发送警告，发送警告的方式有多种，常用的就是给系统管理员发送邮件，并附上出错的信息，方便管理员排查错误。

专业的报表解决方案，轻松搭建报表平台和报表中心。

(部分整理自网络，侵删)

--往期精彩回顾--

听说一键三连的人都开单了!