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

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

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

ETL的设计分为三个部分:数据抽取、数据清洗和转换、数据加载。ETL是从这三个部分设计的。数据抽取是从不同的数据源到ODS(这个过程也可以做一些数据清理和转换)。在抽取过程中，应选择不同的抽取方法，尽可能提高ETL的效率。在ETL的三个部分中，T(清洗和转换)花费的时间最长。一般这部分的工作量是整个ETL的2/3。一般来说，数据加载是在数据清理之后直接写入DW的。

ETL的实现方式有很多种，常用的有三种。第一种是通过Oracle的OWB、SQL server 2000的DTS、SQL Server2005的SSIS服务、informatic等ETL工具实现的。第二个是用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的错误日志，还应该向系统管理员发送警告。发送警告的方式有很多。常见的方式是给系统管理员发邮件，附上错误的信息，方便管理员排除错误。