ETL系列专题4——ETL之T

ETL系列专题4——ETL之T

转换（Transform），是ETL过程中最复杂的部分，ETL中E和L都非常容易理解，Extract从源系统中提取数据，Load将数据载入星型模型。而转换的过程涉及到更多的内容，Kimball把这个过程拆解为清洗（Clean）和统一化(Conform), 这样更容易从数据流的角度去理解ETL过程，实际工作中，我们一般把这两项工作在物理上作为一个过程来处理，比如就Staging Area设计，我们一般只设置一个Transform Staging Area而不是设计两个Staging Area。当然这只是经验之谈，正如笔者之前提及的Staging Area的设计要权衡ETL两方面原则。不同复杂度的业务处理，也必将影响Staging Area的层级。

数据质量

数据质量的验证将在T过程中完成，确保数据质量意味着至少单不限于做到下面的工作：

1、数据是正确的。数据要和业务一致，简单例子，交易额不能经过ETL之后放大或缩小。

2、无二义性，举个例子，不同厂商开发的了两种游戏，但是游戏的名字却意外相同，比如都叫做Call of duty，这时为了消除二义性可以添加一个开发商信息。

3、数据是一致的，同一表达，在不同位置的描述应该是一致的，由于多维模型是冗余的，没有范式化的E-R模型那样包含有强约束，可以确保参照完整性，而且考虑性能因素，架构师通常有意识地在DBMS级别设置减少约束。这种一致性，需要在T过程中得以控制，比如在不同系统中对现金交易类型的描述可能有Monetary，Cash或Credit Card，这些不同表述在数据加载之前必须统一，统一的依据是企业业务层的表述统一，比如以后业务表述时统一使用Monetary。

4、数据是完整的，确保每条记录的每个列的值不缺失；确保整体记录数不缺少。

数据质量检查

在完成E之后立刻做质量检查，即在执行数据清洗之前，在Extracted Staging table上做数据检查，检查数据是否被完好无损地从源系统被抽取过来。记录行数是否正确，记录数据的完整性，每列和源比较？这无疑是最好的方法，但是可以使用一些方便的方法，比如checksum，CRC方法。

数据经过上面的简单检查之后会进入数据清洗阶段

DQ的烦恼

在数据彻底加载到DW/DM之前的所有操作过程，包括数据质量，都将面对很多矛盾的问题，最容易想到的是：数据能不能快速加载到DW/DM。过多的质量验证，无疑挑战ETL快速加载的原则，但是一旦DW出现数据质量问题，又直接影响业务支持，矛盾！

因此DQ过程需要综合考虑一下几个方面的因素：

1、数据质量检查要全面；

2、ETL快速加载原则的压力；

3、数据修正；

4、搁置问题。

数据质量的高要求势必影响ETL的整体处理速度，因此在考量这个平衡时必须要考察数据的延迟容忍度，比如要求ETL每天刷新数据，如果周一的数据在周二之后才可以被访问，会怎样？

修正数据还是搁置问题，某种意义上说ETL是源系统的测试系统，源系统中的很多问题都将在ETL过程中暴露出来，一旦出现数据质量问题，如何修正，如果是源系统的核心问题，又该如何处理。比如发现业务系统存在重大问题需要修改业务处理过程？通常企业没有数据质量验证系统，只有在DW/BI系统中才出现这样的子系统，因此这种数据修正工作和可能交由DW相关团队来处理，大部分是ETL团队。发现问题是好事还是坏事，需要衡量。出现问题后采取的方法也需要考究。

数据质量问题分类及处理

数据质量问题的修正处理场所有两处：源系统修正或ETL过程修正。不同类别的数据质量问题归结到不同的处理场所。

类别A

信息丢失，比如用户信息不完整，产品信息缺失等，这样的数据在ETL过程中无法通过技术手段进行补充修正，只能由源系统修改，重新加载。

类别D

这里先提及第4类，然后根据A，D划分B，C类别。第四类的数据问题是只能在ETL过程中处理的数据质量问题。这类数据质量问题在DW环境中比较少见。比如，数据来自于不太稳定的第三方数据系统，提供过来的数据本身缺乏质量，而且从业务/法律方面数据无法再次提供，这类数据问题只能有ETL处理，并且需要对ETL授权这样的处理，ETL处理结果和处理方式应该方便让终端用户知晓。

类别B

如果数据质量问题既可以在source端处理也可以通过ETL处理，而且，从技术上考量解决方式在source端处理更方便，更可信些。这类数据问题应该放在Source端处理。

类别C

如果数据质量问题既可以在source端处理也可以通过ETL处理，而且，从技术上考量解决方式在ETL过程中处理更方便，更可信些。这类数据问题应该放在ETL过程中处理。

C，D两类的划分主要冲技术角度划分。

数据清洗

在ETL数据流概念结构中，数据清洗过程是紧跟数据抽取之后的处理过程。但实际上数据清洗的工作早在ETL开始之前就已经开始展开了，正如之前提及的数据探查工作，其实就是对数据质量的一个前期工作。

下面主要介绍数据清洗过程中的需要关注的几个方面工作。

错误处理

数据清洗阶段发现数据问题采用的处理方式主要有：通过，该数据问题由ETL过程处理；拒绝，该数据问题需要由业务系统进行数据修正。如果数据问题严重，也可能试ETL停止。错误处理的同时也需要对错误进行有效的跟踪，有效的错误跟踪机制，为改善ETL系统提供有效的依据。

错误跟踪模型需要记录的信息主要有（不限于下列）：

处理进程

执行用户

ETL Server

ETL运行阶段

处理进程启动时间

处理对象

相应的数据源

源表

采取的错误处理方式

现场运行的SQL/package

实际的ETL系统还应该设计错误通知机制，比如即时通知ETL相关团队查找错误原因的机制。实践表明，将错误现场信息对于ETL相关团队发现错误根源非常有帮助，因此在错误通知内容中包含现场信息是不错的选择。

审计

审计是一种事后过程，良好的审计数据是优化ETL处理的基础。审计数据模型应该注意收集如下信息（但不限于这些信息）：

处理进程

进程类型

处理内容

处理阶段

初始化行数

处理行数

拒绝行数

起始时间

结束时间

处理时长（秒）

抽样，Checksum ，CRC

对DW这样级别的数据进行数据质量检查确实是一个挑战性课题，比如事实型数据，每天的增量已经非常庞大，假设对20GB数据进行数据验证，会怎样的效率？这里提出几个实践中使用的方法。

抽样

抽样是个统计学概念，这里不深统计学，只强调抽样的随机性。糟糕的方法是取某个时间段的一组数据，这样的抽样很可能带来样本的局部个性。在数据中添加一个随机列，只要随机列的随机数值设置得好，按照随机列进行排序不影响随机性质。

Checksum与CRC

Checksum 和 CRC是不错的校验方式，比如从源数据取数据时生成Checksum或CRC校验列，在经过ETL处理之后，再次在ETL中计算校验值与在源端计算的校验数据比较以判断是否出现数据缺失，但是该方法不能确定具体缺失的数据。实践中经常用到这种检验方式。

数据质量检查应基于元数据

在数据质量的业务规则应该在元数据层面上定义，并用来支持DQ工作。没一项数据质量验证都应该在元数据库中明确定义。比如数据类型的验证定义，字段长度，不允许NULL的属性，业务规则的验证。这里强调一点，DW中通常不应该包含NULL，Null是不确定的代表，而且在计算过程中容易出现异常，任何Null参与的计算结果都将为Null。通常我们在DW指定一种表述方式给Null，比如Unknown。对于数值型的用0等，具体有业务规则确定。

统一化

统一化过程主要包括维度统一化和事实统一化，统一即是数据的集成统一，也是业务层面上的统一。有了DW，今后在企业级的术语都应该遵行DW系统。A系统中用(gg, mm)表示性别，B系统中用（man，woman），C中用（Male，Female），从今以后都用（gg，mm）啦！

统一维度

什么是统一维度？统一维度从技术上讲，就是说所有事实表涉及到的相同的描述信息都将参照同一个维度表。举例，库存事实需要订单信息，销售数据也需要订单信息，那么统一的订单维度，意味着在DW中销售事实和库存事实都参照统一个订单维度，而不像在业务系统中订单信息同时存在于两个系统中，而且有些属性表述不相同。

统一的维度在DW中至关重要，不但在技术上保证了数据的一致性，而且在业务上保证了口径统一。统一维度设计工作在DW建模过程中所占比重可以达到80%，余下的20%工作可归结到设计统一事实过程中。

统一事实

实际工作中，当我们把统一维度确定之后，通常发现事实也随之确定了。因为我们在模型设计的统一维度过程中，会从业务出发并邀请业务专员，乃至高层业务领导参与统一维度的讨论与验证。验证过程中，必然讨论的主题就是如何从统一维度出发分析事实数据。因此说，统一事实的过程基本上在完成统一维度的的同时完成。这正是为什么很多数据仓库文章都强调统一维度模型（UDM），而很少明确指出统一事实的概念，其实UDM中不可能不涉及统一事实的。

统一过程

统一维度，首先要确定维度属于SCD中的哪一类型，并采取相应的处理方法type1，2，3。统一维度过程需要完成如下步骤：

1、  插入新的维度记录到统一维度中，并产生surrogate key；

2、  对于Type2类型维度，插入新记录，注明历史信息的有效期限，最新信息的起始时间点，并为新的记录产生surrogate key；

3、  对于Type1类型，直接覆盖原先信息，对于Type3类型设置最新版本维度信息，并把先前版本推入历史字段；

统一事实的过程需要完成如下步骤：

1、  获取最新维度信息

2、  处理维度数据迟到情况

3、  查找Surrogate key并替换Natural Key，并将记录插入Fact table

4、  错误数据的修改操作

 

此外，在统一过程中需要做的另一项重要工作就是去重复，重复的维度数据会导致Surrogate key查找失效（统一条fact数据的某个维度信息返回多个surrogate key），重复的事实数据会造成量度叠加，导致错误的汇总结果。

总结

Transform过程是ETL最为复杂的过程，本章只概括讨论了过程中需要注意的事项和需要设置的数据结构，但没有给出具体的结构。Kimball倾向于使用Star-Schema作为错误跟踪和审计过程的数据模型。但笔者在实践中更倾向于使用基于3NF的数据模型来设计ETL的日志型或控制型元数据。当然各有所长，目的都是为了提高ETL的健壮性，可控性。实践中可根据实际情况选取易于操作的模型。