商业智能平台研究（九) ETL 中的数据质量控制

数据质量一直是ETL工具的一个高级特性，为了解释清楚这个问题，让我们看看oracle的商业ETL工具Oracle Warehouse Builder 在数据质量上是如何管理的

oracle在官方网站上有一篇专门介绍如何使用oracle warehouse builder的文章，地址为http://www.oracle.com/technology/pub/articles/rittman-owb.html?
rssid=rss_otn_articles?msgid=4931461 ， 是mark rittman所写，rittman公司本身也是一个专业的oracle 数据仓库 和商业智能方面的顾问公司，在oracle 方面非常的有发言权，如果你对oracle和数据仓库，或者oracle商业智能有兴趣的话，可以看一下上面的这篇文章，本文所有图片引自上面的这篇文章。


ETL难以成功有以下几个难点：
1 . 数据仓库的数据来自于多个数据源，所以数据的一致性很难得到保证，很多情况下需要一种硬性的标准来决定数据的取舍问题.
2 . 数据格式问题,例如数据缺失，超出数据范围，无效数据格式等等。
3 . 出现错误之后没有正确的处理问题，导致数据的质量不断的下降。
4 . 数据一致性问题，处于数据库性能考虑，有时候可能会有意的去掉一些外间或者检查约束。
5 . 业务逻辑问题.由于数据库在最初设计时就不够严格和谨慎。

我们怎么判断数据的质量好坏的呢，一般用户拿原有系统的显示方式查看某一查询条件的数据与用商业智能报表所产生出来的数据进行对比，看有多大的出入，这个可能需要原先系统有足够的能力显示这些数据并且商业智能工具的报表有足够强大的查询和报表展示能力，或者是用商业智能的报表与OLAP运行出来的报表进行对比，看有多大的出入，出入一般都是会存在的，因为数据不可能完全的准确，但是一定要搞清楚哪里数据出现了问题，并且尽量不要让这些误差扩大到用户无法接受的地步，否则就认为BI失败了。（咋同是一个工具做出来的，数据的出入就这么大呢？）



oracle warehouse builder 提供三个特性来使ETL的过程简单
1 . Graphical Data Profiler 可以查看数据的结构，语义，内容，异常，和大纲，数据规则 ， 这就是在前一篇说的，kettle的数据管理没有oracle warehouse builder 强大的特性.kettle也提供查看表结构，column的结构，但是它不会判断一个column是不是主键或外键，一个字符串的最小长度是多少，最大长度是多少，一个整数的长度是多少，一个double的精度是多少。
2 . Correction Wizard 把数据规则应用到你的ETL过程中，自动映射并更正，清理，转化数据， 相当于oracle warehouse builder 提供一些默认的值来帮助你更快的创建映射规则，这个功能也比kettle强大。
3 . Data Auditor 获取数据规则并监控数据转换的过程。kettle也提供数据监控的机制，并把log记入下来，并告诉你重复的记录数，读写多少条记录，更新拒绝多少条记录，时间，速度，步骤是否成功等信息.

oracle warehouse builder 提供查看选中表的结构信息和数据信息




数据归档编辑器有很多面板。这些面板显示已归档的对象和归档的结果。为了方便解释，可以将整个面板分成6个部分，分别是左上角的面板（有两个tab)叫做1号面板，左边中间的property面板，叫做2号面板，左下角的monitor面板，叫做3号面板，右上角的Profile Results Canvas 面板（有10个tab)，叫4号面板，右边中间的Data Grid Panal ,叫5号面板，右下角的Data Rule Panal ,叫6号面板。
1号面板显示已归档的表、视图、物化视图（oracle 10g新加的特性）、外部表、维度和事实等对象以及已经创建的任何更正模块的详细信息。
2号面板显示与数据归档关联的属性的列表。使用该属性列表，您可以优化数据归档的参数；启用或禁用某些数据归档组件并启用选定表的数据规则归档。
3号面板是监视器面板。该面板显示已提交的所有归档作业的进度。数据归档需要时间，所以可以在后台完成的作业，同时执行其他 Oracle Warehouse Builder 任务；当作业完成时，Oracle Warehouse Builder 会发出通知，kettle 在执行监控上提供的信息比oracle warehouse builder 多一些，包括速度和时间,还可以看到你启动多少线程组和线程，线程进行到那一步都显示的出来。
4号面板提供最多的信息，所以分成了10格tab,包含大量归档结果汇总的tab,
5号面板显示数据的统计信息，某一个column出现的值，出现的次数，占的百分比，有了这个功能，如果出现了错误的数据，将可以更容易的看到和清除.
6号面板显示数据规则.










其中4号面板有10个tab,其中有几个tab非常有用.(看上面的图）
Data Type tab详细说明表中每列的列名，数据格式，主要的数据类型，主要的数据类型所占的百分比，数据的长度，最大值和最小值，主要的长度，主要的长度所占的百分比，类型的精度。其中所谓的主要长度类型在Date那一列为40%，可见有些值为空，它不光列出最小值和最大值，而且还有主要值的长度和它所占百分比，统计方面功能比较强大，这种初步的数据统计不知道算不算接近于OLAP分析，功能上比kettle强大些.

Unique Key tab显示检测到唯一键或主键的所有列。该选项卡还显示唯一值的数量多得足以建议删除或更正非唯一行时可以定义唯一键的列。你可以看到图中有Six Sigma 列。这是一个 1 到 7 之间的数字，它指明“每千个的缺陷”数量，即对象中未能通过唯一约束的行。

Profile Object tab 相当于kettle中的 sql 编辑器 ，显示所有的数据并加上查询条件。

Domain tab 为归档对象的每列显示建议的域，以及数据与该域的一致程度。域就是列的一组允许的值，Oracle Warehouse Builder 认为出现两次或以上值的列就叫域。从图上看到，PACK_COUNT , PROD_ID , PROD_NAME 都是唯一性比较高的列，所以没有domain,这个面板同样也有 Six Sigma 值。



创建数据转化规则
在它给出的例子中，MANUF_COUNTRY的国家列包含Not Known , Canada ,USA , UK 和 Mexico ,它想把所有的England转化为 UK , 在Product 表中，把所有REORDER_YN为 'N' 的记录删掉，并使MARKET_SEGMENT 表的所有值为 ' Economy ' 或 ' Executive ' .

当你完成了数据规则的建立后，在实际转换的过程中，有可能出现你没有考虑到的情况，oracle warehouse builder 提出了三种操作,
Igore 这个不用解释吧.
Report 在一个新建的ERR$$$表中记录下这条记录。数据继续转化。
Cleanse 应用转化规则来清理数据.


如果没有什么特殊的原因，一般最好不要使用第一种策略，你即不知道有没有数据处错了，也不知道有多少数据出错了，这样很容易让你的数据误差越来越大，第二种Report策略能让我们知道那些数据出错了，并记录下来，当我们需要找到数据误差原因的时候，或者我们需要更正这些数据误差的时候有依据。当然如果我们真正的清楚数据转换规则的话，第三种策略是最好的，把这种误差消失在ETL的过程之中.

其中对于应用了数据转化规则的列，oracle warehouse builder 又提供四种策略来使数据达到我们想要的程度，

1 . Remove ，把我们认为满足特定数据条件的数据删除，例子中就是要把REORDER_YN = Y 的数据删掉。
2 . Similarity Match 把不在我们规定的域内的数据自动更改为最接近的值，这个值是oracle 用特定算法算出来的，我不清楚到底是怎么样的规则。不过我猜是不是按照字典的顺序来排序，谁接近就转化成谁，比如有applet,book ,orange ,如果要转化book的话，就把book转化为applet,因为b 开头的单词比o 开头的单词离的近一些，中文的话可能不会支持吧。当然，这只是我猜的.具体是怎么样只有问那些高手了.
3 . Soundex Match oracle有一个函数叫做SOUNDEX ， 它是应用以下规则，保留首字母，把所有的元音 a , e , i , o , u 和 w , y 删掉。把剩下的字符串按如下数字相加
b , f , p ,v =1
c, g, j, k, q, s, x, z = 2
d, t = 3
l = 4
m, n = 5
r = 6
如果两个或两个以上有相同的数字接近原来的值（在第一步之前），或者接近除去h 和 w 的值 ， 忽略掉除第一个字母以外的数字. 返回4个字节。格式大概如下：
SELECT name, SOUNDEX(namecol) FROM test;
SELECT * FROM test WHERE SOUNDEX(namecol) = SOUNDEX('SMITH');
以上例子摘自http://www.psoug.org/reference/string_func.html，如果你对算法有疑问可以自己去看看。

4 . Custom 使用自定义的转化规则来清理数据，大多数时候都是使用这种方式。例子中的把MANUF_COUNTRY 的 England 转化为 UK 就是这种情况。


在你进行数据转化之后，你对数据的质量满意了，你可以设置Data Auditor 来监控以后传入的数据的质量



Data Auditor 使用定义的数据规则，生成关于数据的一致程度的统计报告，然后将其存储在错误记录表中。还可以对 Data Auditor 进行编程，指定 Data Auditor 在分数低于一定的阈值后向您发出通知，然后在特定情况运行清理映射来清理数据。运行该清理映射后，可以对 Data Auditor 进行编程，使其仅在设计分数高于特定级别或 Six Sigma 值时才继续进行余下的 ETL 过程，避免将错误数据载入数据仓库，使数据仓库的质量能够得到保证。

ETL是非常重要的一步,往往一个项目的成败就是看ETL过程的成功与否.选用一个好的ETL工具会让项目更加的有信心.

下一篇介绍ETL的各种工具.