关于数据仓库数据质量的问题处理

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

自己用英文写的,在翻译回来,感觉很蹩脚,也做了很多删减!
 

ETL 是数据仓库的最重要的基础,良好的 ETL 从业务系统中抽取数据,转换数据质量,保证数据一致性,这样才能够保证各个独立的不同的数据源能够集成到一起,最终只有这样才能真正达到决策支持的目的。
数据清洗是 ETL系统的一个最重要的步骤,数据的抽取和加载也是很必要的,但是他们只负责数据的迁移和重组格式。只有数据清洗才能真正改变数据,并且为了目标提供高质量的数据保证。
高质量数据意味着:
正确的。数据的值和描述一定是真实的和业务系统保持一致的。 
明确的。数据的值和描述有且只能有一个意思 
一致的。数据的值和描述在全局中也即数据仓库中都表示一个意思 
完整的。这有两个方面。首先确保每一条数据都必须是有意义的(不能为 NULL值),其次要求我们在处理过程中不能有任何信息的“损失” 
通常情况下,当 BI/DW 项目结束时,用户总是会将 BI 报表和 OLTP 报表或者明细报表进行比较,以检验数据仓库报表的准确性。一旦用户发现它们之间是不一致的或者误差超过一定比率,他们往往就会认为 BI 项目很失败。
没有绝对的准确,但是一定要知道为什么不准确,这是数据仓库项目的一个基本要求。
数据仓库的数据来自于多个数据源,所以数据的一致性很难得到保证,既然没有绝对的准确,那么就需要制定一个标准。因此我们建议和客户达成一种相对标准,定义一个可以接受的误差范围。在这个前提下,我们找到误差的原因,并给出分析报告,来提高客户的满意度和对数据仓库项目的信心,从而确保数据仓库项目成功的机率。
1、根源和选择
2、数据质量存在问题的根本原因在于数据源,保证数据质量是很困难的事情。这是事实,但是那一些潜在的问题会带来数据质量问题呢?可以归为以下几类:
数据格式问题 ,l 例如数据缺失,超出数据范围,无效数据格式等等。 
3、数据一致性问题,处于数据库性能考虑,有时候可能会有意的去掉一些外间或者检查约束。 
4、业务逻辑问题,这个很难说正确还是错误,通常是由于数据库设计得不够严格或者谨慎。 
构造数据仓库系统时,完全理解业务系统的业务逻辑和整体情况是不可能的。我们不能完全去研究那些详细的设计文档,同时后来的很多需求变更也并不完全放映到文档上来,因此需要花费大量的时间去定位和分析其中的原因和变化。用户要求在进行 ETL 之前必须了解所有的业务逻辑和规则,显然也是不现实的。个人认为我们只需要了解和处理那些可能遭遇问题的数据。我们必须决定这些数据是拒绝呢还是处理。假如数据质量得不到保证的话,在后续的处理过程中,这样的错误将逐渐被放大。

正是因为数据质量问题贯穿于项目的整个生命周期,而且不能避免,我们必须面对而且给出解决办法,尽量把影响减小到最少。
通常情况下,当我们遇到错误数据, ETL 一般提供以下 4 种解决办法:
1、没有任何处理的通过记录 
2、通过记录,打上错误标记 
3、拒绝记录 
4、停止 ETL 任务

下面就这几种情况进行一下分析:
首先选项 1明显不能保证数据质量,并将最终影响报表质量。
其次选项 3也不能保证数据完整性,因为数据将发生遗弃,也将会影响报表质量。
再次选项 4会影响 ETL处理,导致数据仓库不能正常运行下去。
所以,最常见的处理方式就是选项 2,首先保证这些记录顺利通过,然后记录一些错误标志,并通过报表反映出来。
 
这样做有以下四种好处:
1、通过特殊处理确保了数据的完整性 
2、反映了数据仓库的数据源数据质量 
3、对数据质量可以有一个比较准确的度量 
4、确保了数据仓库的顺利实施和任务的正常调度 
数据质量应该尽量确保在 ETL 环节中进行,因为每一点的错误都会导致后续处理的无限放大,同时数据仓库的处理是线性进行的,当发现错误时,很难回过头来对数据进行重新的处理。因此尽量把错误和数据质量问题消除在靠前的位置。
可测量性 
数据清洗通常根据不同的情况进行处理,在这里没有办法一一列举,只能对常用的几种情况进行分析处理。
维度: NULL值 
假如维度数据为空,在数据处理时可能会导致错误的处理,通过 SQL 处理时事实表中可能会丢失这部分数据。
维度:外键丢失 
前者提到处于数据库性能考虑,业务系统有时候会放弃外间约束或者检查约束,但这样数据的完整性有时无法得到保证,当数据被修改或者删除的时候,这部分数据可能会变成孤儿数据。
度量值:超出范围 
假如没有约束和检查规则,原始数据表中的度量值可能为空或者超出预想的范围,当我们处理和计算这部分数据的时候,也会导致错误的结果。
业务逻辑和录入错误 
很显然,这部分错误,我们基本上是无能为力的,缺乏有效的验证和纠错,实际上数据仓库的流水线作业形式和巨大的数据量,让我们对这些数据的校验变得不太可能了。我们只能祈求业务系统录入人员的责任心了。

处理
实际上对于 NULL 值,缺乏一致性,数据范围的处理,可以通过简单的语句来完成。
Example:
 
 
 
-- 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,
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;
 
 

--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
--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)) 
 

So we will get the result below
Record 
 A.ID 
 B.ID 
 ID 
 Measure1 
 Measure2 
 

 1 
 1 
 1 
 1 
 1 
 

 2 
 2 
 2 
 0 
 2 
 

 3 
 NULL 
 -2 
 3 
 3 
 

 NULL 
 NULL 
 -1 
 4 
 4 
 
加入校验过于复杂,我们也可以求助用户自定义函数或者存储过程进行数据清洗。
数据质量的可度量性 
如果用户对报表质量提出质疑,我们可以比较容易的给出错误度量报表,只需要提供各个维度中 -1 , -2 的数据即可。假如方便的话,还可以提供相关图表。
当然如果在报表仍然存在问题,我们只能求助于 SQL ,通过排除法,来完成报表的质量验证工作。
 

--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; 
 
 









本文转自baoqiangwang51CTO博客,原文链接:http://blog.51cto.com/baoqiangwang/309768 ,如需转载请自行联系原作者
相关文章
|
数据采集 分布式计算 监控
数据仓库之数据质量建设(深度好文)(二)
数仓建设真正的难点不在于数仓设计,而在于后续业务发展起来,业务线变的庞大之后的数据治理,而数据治理的范围非常广,包含数据本⾝的管理、数据安全、数据质量、数据成本等。在这么多治理内容中,大家想下最重要的治理是什么?当然是数据质量治理,因为数据质量是数据分析结论有效性和准确性的基础,也是这一切的前提。所以如何保障数据质量,确保数据可用性是数据仓库建设中不容忽视的环节。
314 0
数据仓库之数据质量建设(深度好文)(二)
|
数据采集 存储 算法
数据仓库之数据质量建设(深度好文)(一)
数仓建设真正的难点不在于数仓设计,而在于后续业务发展起来,业务线变的庞大之后的数据治理,而数据治理的范围非常广,包含数据本⾝的管理、数据安全、数据质量、数据成本等。在这么多治理内容中,大家想下最重要的治理是什么?当然是数据质量治理,因为数据质量是数据分析结论有效性和准确性的基础,也是这一切的前提。所以如何保障数据质量,确保数据可用性是数据仓库建设中不容忽视的环节。
516 0
数据仓库之数据质量建设(深度好文)(一)
|
1月前
|
SQL Cloud Native 数据挖掘
云原生数据仓库产品使用合集之在使用 ADB 进行数据分析处理时,出现分区倾斜的情况,如何解决
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
|
6天前
|
SQL Cloud Native 关系型数据库
云原生数据仓库AnalyticDB操作报错合集之执行sql的进程报错:"unknown connection id",是什么导致的
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
313 3
|
6天前
|
SQL Cloud Native 关系型数据库
云原生数据仓库AnalyticDB操作报错合集之报错代码"[31004, 2023121817001319216817200303151051107] : Compiler failed and interpreter is disabled"是什么导致的
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
229 3
|
6天前
|
Cloud Native 数据管理 OLAP
云原生数据仓库AnalyticDB产品使用合集之是否可以创建表而不使用分区
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
171 2
云原生数据仓库AnalyticDB产品使用合集之是否可以创建表而不使用分区
|
6天前
|
存储 SQL Cloud Native
云原生数据仓库AnalyticDB产品使用合集之热数据存储空间在什么地方查看
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
|
6天前
|
存储 监控 Cloud Native
云原生数据仓库AnalyticDB产品使用合集之如何添加索引
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
148 2
|
6天前
|
SQL Cloud Native 关系型数据库
云原生数据仓库AnalyticDB产品使用合集之如何进行一键诊断
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
151 7
|
6天前
|
Cloud Native 关系型数据库 MySQL
云原生数据仓库AnalyticDB产品使用合集之是否支持修改主键
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
159 3

热门文章

最新文章