ETL系列专题3——ETL之E

ETL系列专题3——ETL之E

从本章开始介绍基于ETL的数据流架构，首先介绍E（Extract）过程。

抽取（Extract）

没有数据，DW/BI的模型再好也没有任何用处。数据集成的第一个步骤就是从业务系统中抽取（Extract）数据。伴随着企业的蓬勃发展，业务的不断扩张，相应的信息系统也随之多种多样：销售管理系统，供应链系统，库存系统，产品控制系统……这些系统通常来自于不同软件供应商，运行于不同操作系统平台和数据库环境，而且情况更让人痛苦的是这些系统通常各自为政，互不相关。通常它们在逻辑上和物理上都不兼容。ETL系统的任务就是要把这些运行于不同软硬件环境的系统在数据层面上集成起来。

在着手开始创建抽取系统之前，需要完成逻辑映射文档，用来标识业务系统和目标系统（DW/DataMart）字段之间的对应关系。逻辑映射文档将贯穿整个ETL开发过程

逻辑映射文档

经验表明，急于着手物理层面的映射工作通常会浪费更多的时间并使整个开发过程失去可跟踪性。完备的文档将使ETL乃至整个DW/BI的实施过程有章可依，生命周期有条不紊。因此在实际操作数据之前，最好完成一些基础工作，避免日后回过头来再做标记性记录。

映射文档包括DW源系统的数据定义，DW多维模型的数据定义，抽取操作后用作转换过程的数据格式（Staging数据之间的映射）

制定ETL处理计划

ETL过程必须要在逻辑层面上抽象表述处理并制定相应的文档。逻辑数据映射文档一般由DW架构师制作并作为ETL开发团队开发ETL作业的功能文档，同时它也是测试团队完成测试工作的重要参考文档，它也描述了ETL在从源系统到DW/DM过程中完成了哪些工作。

选择数据源

根据业务模型定义并标识可能被DW/DM是用的数据源，同时标识各个数据源中支持业务的重要数据元素，比如维度的相关属性数据，事实的量度信息。这些数据将被抽取并交付给数据核查步骤（非ETL数据流步骤）。

数据核查

数据核查要以用户的视角去查验数据，源系统的数据必须进行仔细核查，标识各系统中对相同业务的不同描述方式，比如实际业务中都是指网上现金交易A系统中可能描述为Credit Card，B系统中可能是Monetary。在数据核查阶段任何数据异常的都必须要以文档跟踪。数据据核查是基于业务的核查，因此这个环节将发现数据源不能满足业务模型的某些需求，这时可能要从新选择数据源或者移除业务模型的相应设计（如果，目前业务系统没有支持这些需求）。

这里简单列出数据核查阶段的工作项目：

1、数据的唯一标识和键值

2、数据类型

3、数据表之间的关联关系

4、表/字段之间的映射关系，1-1，1-m，m-m

5、null数据

6、日期数据，注意日期数据可能没有存储在日期型字段中，这是日期型数据不同于其他数据类型的一个特点

复查数据与业务规则之间是否吻合

该过程需要DW架构师，业务分析师合同ETL架构师和ETL开发人员一同完成。在逻辑上“彩排”数据依据业务需求经过ETL系统最终顺利到达DW/DM。这一过程将帮助ETL团队更好地理解业务规则和业务模型，同时帮助DW模型师和业务分析师理解数据处理过程。最终达到全体DW团队都彻底了解DW架构和数据流处理架构的目的。为今后工作中的合作交流打下坚实基础。

ETL团队必须彻底理解DW多维模型

ETL开发人员必须完全理解维度结构，维度属性的意义；事实结构和量度的意义，以及维度和事实如何协作完成对业务的支持内涵。单纯知晓数据的“表-表”映射关系是远远不够的。

理解计算成员和各种转换公式

任何人都不想在最终报表层面上出现违背业务规则的信息，所以在着手实现ETL转换过程前，反复验证转换算法是非常有必要的。

映射文档的结构

映射文档应该包括以下内容：

目标表：多维模型中的表名

目标字段：多维模型中表字段

目标列数据类型：

表类型：维度/事实/桥接

缓慢变化维：3类SCD

源数据库：源数据库名/文件路径，

源类型：DBMS类型，文件类型

源表：源表名，可能多个表对应一个DW/DM中的表

源列： 源列名

源列数据类型：

转换规则：定义源信息将经过怎样转换到DW/DM，通常用伪代码描述

一般ETL工具都提供了源与目标的映射功能，但是笔者依然强烈建议单独编制mapping文档，以方便查阅。

深入探查源系统

源系统名称

用户群/部门

功能

业务范围

数据库类型

数据库容量

增量

运行平台

系统负责人/团队

确定SOR

在着手开发抽取过程之前需要确定的另一项重要内容就是System-Of-Record(SOR),这个名词大家应该都听说过，但是具体如何理解SOR呢？SOR到底是什么涵义？它为什么至关重要？

SOR可以理解为企业多个业务系统的一个统一或中心系统（听起来怎么像数据仓库），这里不要和DW混淆，DW是企业级的数据中心，而SOR是局部的统一，比如销售系统中有订单，库存系统中也有订单，他们可能是同一个订单，但是表述可能完全不同，在数据进入DW系统之前必须进行统一定义，这个定义过程就是确定SOR。有些企业有类似的中心系统，比如企业还有一个信息系统，这个信息系统为企业统一提供订单业务报告支持。那么，这时ETL应该选择该系统的订单数据作为抽取源，以避免订单业务出现二义性。二义性的数据进入DW，势必影响最终使用者对数据的理解。

实践表明，越是从深层数据源抽取数据，越容易抽取到坏数据。经验之谈，尽可能选择离入口最近的数据源。SoR的确定是技术和业务的协作结果。

异构数据源

企业信息系统的多样化现状是ETL团队必须面对异构数据的抽取问题。但是数据集成的工作内容就是要把不同数据源，不同数据格式的数据统一化，一致化，标准化。因此异构数据集成是ETL的挑战，而且这个挑战也必须成功应对。

一个问题：如果一个维度的数据来自于多个源系统会出现什么问题？可能出现“重复”，这个重复有两种可能，一个是业务上的重复：这个维度信息在两个系统中表述的业务实际是相同的；另一个是逻辑重复，两个系统的键值一样，但是实践表述的业务是不同的维度内容。

需要考虑并解决的问题：

1、标识源系统，即SoR为最终的source

2、为非键值属性定义抽取业务规则，比如同一个维度中的键值属性来自于SoR，但是某些非键值属性可能无法从SoR中获取，而且可能同时存在于多个系统中，这时需要根据业务表述的需求确定这类属性的source。

3、加载统一维度，这个环节需要注意SCD。

异构平台数据抽取

企业数据可能存在于不同的DBMS中，文件系统中，或运行于不同操作系统上。而且不同系统和平台都有其特定的处理语言，比如COBOL，Perl，VBScript，T-SQL，PL/SQL等等。当某种操作方式操作ETL工具提供的功能时，我们需要源系统所有者把数据抽取到平面文件形式以供ETL处理。

ODBC访问方式

ODBC访问方式的优点：可以移植性好，如果数据源数据性质发生改变，比如从DB2迁移到Oracle,那么应用程序无须改变，只要把ODBC驱动程序从DB2驱动修改到Oracle驱动即可。

缺点也非常明显：不能使用DBMS特有的功能，这些功能通常性能非常优越。比如只能使用标准SQL去访问数据库。

大型机上的数据源

大型机和普通微机不同，外围设备单独负责I/O处理，而CPU只负责数据处理，普通微型机CPU负责这两方面工作。这里简单介绍一下编码转换。

大型机系统的字符集编码方式采用扩展二进制编码（EBCDIC）与Unix或windows的编码方式（ASCII）不同，因此文件格式也就完全不同，如果需要从大型机系统抽取数据，需要注意编码转换，即从EBCDIC->ASCII。

不过操作系统都提供相关的转换命令如UNIX/Linux中的dd命令，运行dd命令时指定conv= ASCII选项，即可实现EBCDIC->ASCII。另外，FTP可能是更好的选择，FTP可以文件传输过程中完成主机与客户机编码的转换。具体参照相关文档。

平面文件

平面文件是DW环境中常见的文件格式，而且很多情况下我们无法避免地要使用平面文件。平面文件用在ETL系统中的情形更是常见。

1、  之前提及的用作Staging table，因为平面文件有更好的I/O性能

2、  上面提及的从大型机系统环境中抽取数据，需要使用平面文件作中转

3、  很多DBMS的批量插入需要用平面文件作为输入

平面文件主要有两种格式，固定列宽的平面文件和带有分隔符的平面文件。目前的ETL工具都支持这两种平面文件的处理。

关于源的类型还有很多，比如XML，weblog，ERP（比如SAP），这里告一段落。下面介绍关于增量抽取的相关内容。

增量抽取

在初始点，我们并不关心ETL增量加载问题，因为我们会把我们需要的历史数据全部加载到我们的多维模型中去。但是初始工作之后，我们必须解决增量提取问题，即只抽取加载变化的数据。但是，如果把这个问题放在初始加载之后才去考虑，可能不是明智之举，因为这个问题的解决必将破坏之前的架构。因此增量抽取问题必须在抽取系统设计时期得到解决。如何检测数据变化是实现增量加载的核心问题。

变化检测

使用检测列

很多系统中设置了检测列，用以检测记录是否有更新，这种检测列通常由应用程序来维护，而不是使用数据库的触发器来维护，以避免影响DBMS性能。但是，在使用检测列之前，一定要反复验证和求证检测列的可信度，为什么？因为很多源系统允许DBA在后台直接修改数据，这种修改通常不会重新设置检测列的值。这样可能造成ETL系统无法根据检测列获得变更的数据。

另外一种常用的检测列是数据的创建/更新时间，这种检测列非常好用，也是常用的增量提取依据。我们只要记录ETL上次成功运行的起始时间（LastETLRunTime），然后每次运行都抽取源中更新日期大于LastETLRunTime的数据即可。

数据库日志信息

数据库的日志跟踪了数据库的所有变更，理论上根据日志实现增量加载是最可信的，但是这个操作的技术性也是非常复杂的，除非DBA可以辅助ETL团队，专门提供一个日志为ETL系统服务，否则在庞大的log中寻找增量数据，是个非常挑战的问题，如果ETL工具没有提供这种功能，笔者强烈建议不要自己去写这样的程序，以免因有考虑不周之处，而带进bug。

提高抽取效率

1、请求DBA在source段对抽取条件字段上创建索引；

2、只抽取需要的数据，需要的数据就是最终会被加载到多维模型的数据，多余的不要提取；

3、慎用Distinct，非常耗时

4、操作数据库时使用集合操作命令，集合概念是数据库的基础理论，努力把非集合的操作转化为集合操作，将充分利用DBMS的长处

5、不要在where条件中使用函数

6、避免使用NOT子句，它将导致全表扫描

事实数据的变化处理

业务系统对事实数据的修改或删除，将给DW系统带来巨大的挑战，如果源系统中没有一个有效的通知机制，那么将直接影响DW的可信度，可用性。理论上，事实数据是不会被重新加载的，事实表的操作一般只做插入或批量移除（维护需要）。

当发现源系统的事实数据发生更改或删除时，应该与源系统负责人商讨相关变化，并确定该变化的性质，以决定在DW中做怎样的操作。

实际应用中，一旦检测到事实数据发生变化通常的做法有两种，具体操作不再赘述：

1、反相抵消，并插入新的事实记录

2、做逻辑删除，插入新的事实记录