关于数据仓库数据质量问题探讨

数据仓库的数据来自于多个数据源，所以数据的一致性很难得到保证，既然没有绝对的准确，那么就需要制定一个标准 字串1

一、 数据质量和清洗

字串4

 

　　ETL是数据仓库的最重要的基础，良好的ETL从业务系统中抽取数据，转换数据质量，保证数据一致性，这样才能够保证各个独立的不同的数据源能够集成到一起，最终只有这样才能真正达到决策支持的目的。

字串6

 

　　数据清洗是ETL系统的一个最重要的步骤，数据的抽取和加载也是很必要的，但是他们只负责数据的迁移和重组格式。只有数据清洗才能真正改变数据，并且为了目标提供高质量的数据保证。

字串1

 

　　高质量数据意味着： 字串4

• 　　正确的。数据的值和描述一定是真实的和业务系统保持一致的。
• 　　明确的。数据的值和描述有且只能有一个意思
• 　　一致的。数据的值和描述在全局中也即数据仓库中都表示一个意思
• 　　完整的。这有两个方面。首先确保每一条数据都必须是有意义的(不能为NULL值)，其次要求我们在处理过程中不能有任何信息的“损失”

　　通常情况下，当BI/DW项目结束时，用户总是会将BI报表和OLTP报表或者明细报表进行比较，以检验数据仓库报表的准确性。一旦用户发现它们之间是不一致的或者误差超过一定比率，他们往往就会认为BI项目很失败。

字串6

 

　　没有绝对的准确，但是一定要知道为什么不准确，这是数据仓库项目的一个基本要求。 字串5

　　数据仓库的数据来自于多个数据源，所以数据的一致性很难得到保证，既然没有绝对的准确，那么就需要制定一个标准。因此我们建议和客户达成一种相对标准，定义一个可以接受的误差范围。在这个前提下，我们找到误差的原因，并给出分析报告，来提高客户的满意度和对数据仓库项目的信心，从而确保数据仓库项目成功的机率。

字串2

 

　　二、 原因分析和解决思路 字串2

　　数据质量存在问题的根本原因在于数据源，保证数据质量是很困难的事情。这是事实，但是那一些潜在的问题会带来数据质量问题呢?可以归为以下几类： 字串3

• 　　1. 数据格式问题,l例如数据缺失，超出数据范围，无效数据格式等等。
• 　　2. 数据一致性问题，处于数据库性能考虑，有时候可能会有意的去掉一些外间或者检查约束。
• 　　3. 业务逻辑问题，这个很难说正确还是错误，通常是由于数据库设计得不够严格或者谨慎。

　　构造数据仓库系统时，完全理解业务系统的业务逻辑和整体情况是不可能的。我们不能完全去研究那些详细的设计文档，同时后来的很多需求变更也并不完全放映到文档上来，因此需要花费大量的时间去定位和分析其中的原因和变化。用户要求在进行ETL之前必须了解所有的业务逻辑和规则，显然也是不现实的。个人认为我们只需要了解和处理那些可能遭遇问题的数据。我们必须决定这些数据是拒绝呢还是处理。假如数据质量得不到保证的话，在后续的处理过程中，这样的错误将逐渐被放大。 字串4

　　正是因为数据质量问题贯穿于项目的整个生命周期，而且不能避免，我们必须面对而且给出解决办法，尽量把影响减小到最少。 字串7

　　通常情况下，当我们遇到错误数据，ETL一般提供以下4种解决办法：

字串7

 

• 　　1. 没有任何处理的通过记录
• 　　2. 通过记录，打上错误标记
• 　　3. 拒绝记录
• 　　4. 停止ETL任务下面就这几种情况进行一下分析：

  　　首先选项1明显不能保证数据质量，并将最终影响报表质量。 字串4

  　　其次选项3也不能保证数据完整性，因为数据将发生遗弃，也将会影响报表质量。

  字串2

   

  　　再次选项4会影响ETL处理，导致数据仓库不能正常运行下去。 字串6

  　　所以，最常见的处理方式就是选项2，首先保证这些记录顺利通过，然后记录一些错误标志，并通过报表反映出来。

  字串8

   

  　　这样做有以下四种好处： 字串4

  • 　　1. 通过特殊处理确保了数据的完整性
  • 　　2. 反映了数据仓库的数据源数据质量
  • 　　3. 对数据质量可以有一个比较准确的度量
  • 　　4. 确保了数据仓库的顺利实施和任务的正常调度

  　　数据质量应该尽量确保在ETL环节中进行，因为每一点的错误都会导致后续处理的无限放大，同时数据仓库的处理是线性进行的，当发现错误时，很难回过头来对数据进行重新的处理。因此尽量把错误和数据质量问题消除在靠前的位置。

  字串2

   

  　　三、 处理对策

  字串4

   

  　　数据清洗通常根据不同的情况进行处理，在这里没有办法一一列举，只能对常用的几种情况进行分析处理。

  字串8

   

  　　1. 维度：NULL值 字串4

  　　假如维度数据为空，在数据处理时可能会导致错误的处理，通过SQL处理时事实表中可能会丢失这部分数据。 字串5

  　　2. 维度：外键丢失

  字串3

   

  　　前者提到处于数据库性能考虑，业务系统有时候会放弃外间约束或者检查约束，但这样数据的完整性有时无法得到保证，当数据被修改或者删除的时候，这部分数据可能会变成孤儿数据。 字串1

  　　3. 度量值：超出范围

  字串2

   

  　　假如没有约束和检查规则，原始数据表中的度量值可能为空或者超出预想的范围，当我们处理和计算这部分数据的时候，也会导致错误的结果。
        4. 业务逻辑和录入错误 字串8

  　　很显然，这部分错误，我们基本上是无能为力的，缺乏有效的验证和纠错，实际上数据仓库的流水线作业形式和巨大的数据量，让我们对这些数据的校验变得不太可能了。我们只能祈求业务系统录入人员的责任心了。 字串6

  　　四、 处理脚本 字串7

  　　实际上对于NULL值，缺乏一致性，数据范围的处理，可以通过简单的语句来完成。 字串4

  　　Example:

  字串5

   

   

  -- Create a original table, only include ID and two measure fields. -- here ID is one of attribute columns only, so it can be Null values. -- two measure fields have no constraint or check rule, they may be -- NULL or out of Scope, on the assumption that the scope value of -- measure1 and measure2 field is between 0 and 100. Create table table_original (   ID         INT,   Measure1   LONG,   Measure2   LONG ) -- Create a dimension table, only include ID and Name fields. CREATE TABLE table_dimension (   ID        INT CONSTRAINT Pri_Table_Dimension PRIMARY KEY, 字串9   Name      VARCHAR2 (20) ) -- Create a fact table, Only include ID and two Measure. It will save -- compute or count result. Create table table_fact (   ID         INT   NOT NULL,   Measure1   LONG,   Measure2   LONG ) --Insert a sample data into dimension table and original table. INSERT INTO table_dimension VALUES (1,'a'); INSERT INTO table_dimension VALUES (2,'b'); COMMIT; INSERT INTO table_original VALUES (1,1,1); INSERT INTO table_original VALUES (2,101,2); INSERT INTO table_original VALUES (3,3,3); INSERT INTO table_original VALUES (null,4,4); COMMIT;

  字串3

   

   

  --Common OLTP Report Usage, Query SQL is like SELECT A.ID ID,SUM(A.Measure1) Measure1,SUM(A.Measure2) Measure2   FROM table_original A,table_dimension B  WHERE A.ID = B.ID  GROUP BY A.ID; --The Result is below:   ID       Measure1 Measure2   1        1        1   2        101      2 So we can get the count value of 1 and 2 in ID field only, it means two Records are lost, in fact the Measure1 value of ID 2 is out of scope. --If in OLAP report, we will make Data Clean --If ID is NULL, we will use -1 replace it 字串6 --If ID is not exist in original table, we will use -2 replace it --We also need check the validity and scope of measures field, we will --give a default value or average value, it depend on our need. --So query SQL is like SELECT  -- A.ID,B.ID,    -- the transformed field names ID   DECODE(NVL(A.ID,-2),-2,-2, DECODE(NVL(B.ID,-1),-1,-1,B.ID)) AS ID,   SUM(CASE A.Measure1>0 AND A.Measure1<100 THEN Measure1 ELSE 0 END      ) Measure1,   SUM(CASE A.Measure2>0 AND A.Measure2<100 THEN Measure2 ELSE 0 END      ) Measure2, FROM table_original A,table_dimension B WHERE A.ID = B.ID(+) GROUP BY DECODE(NVL(B.ID,-2),-2,-2, DECODE(NVL(B.ID,-1),-1,-1,B.ID))

  字串9

   

So we will get the result below 字串5

字串9

 

　　加入校验过于复杂，我们也可以求助用户自定义函数或者存储过程进行数据清洗。 字串5

　　五、 可度量性 字串8

　　如果用户对报表质量提出质疑，我们可以比较容易的给出错误度量报表，只需要提供各个维度中-1，-2的数据即可。假如方便的话，还可以提供相关图表。

字串9

 

　　当然如果在报表仍然存在问题，我们只能求助于SQL，通过排除法，来完成报表的质量验证工作。 字串7

 

--First count the record number of original table. SELECT COUNT(*)   FROM table_original A --Relate with the first dimension table, and capture the record number.  SELECT COUNT(*)   FROM table_original A,table_dimension1 B  WHERE A.ID = B.ID; --Relate with the second and more dimension table, and capture the --record number, to verify the result. SELECT COUNT(*)   FROM table_original A,table_dimension1 B, table_dimension2 C  WHERE A.ID = B.ID    AND A.ID = C.ID;