Thesis - Técnico Lisboa

Data Profiling
Identificação de problemas de qualidade de dados usando análise de dados e
métodos estatísticos em base de dados de cartões de clientes
Ana Carina Vieira Rodrigues
Dissertação para obtenção do Grau de Mestre em
Engenharia Informática e de Computadores
Júri
Presidente: Prof. Doutor Pedro Sousa - DEI/IST
Orientador: Prof. Doutora Helena Isabel de Jesus Galhardas - DEI/IST
Vogais:
Prof. Doutor Manuel João Fonseca - DEI/IST
Eng. João Carlos Pereira Damásio - Link Consulting
Setembro de 2008
Resumo
O que não se sabe sobre os dados de uma empresa prejudica bastante o negócio da mesma. Ao longo
do tempo grandes iniciativas, tais como a implementação de ERPs, CRMs, fusões, aquisições ou migração de dados, têm uma grande falha em comum: dados errados, corrompidos, incompletos, etc. De
acordo com os relatórios do TDWI, mais de 80% dos gestores de negócio inquiridos acreditam que os
seus dados estão bons. Contudo mais de metade dos seus técnicos reconhecem que os dados estão
em pior estado do que os executivos pensam.
Esta dissertação descreve qual o potencial do Data Profiling na detecção das várias anomalias que os
dados de uma BD podem apresentar e quais as vantagens de utilizar o mesmo. Para que a detecção
de erros seja rigorosa e completa é necessário uma metodologia de DP que permita ao analista avaliar
todos os elementos de uma BD e que disponha de um conjunto de funcionalidades através das quais se
consiga reportar o maior número de erros. Desta forma, nesta dissertação foi reformulada a metodologia DP de forma a concentrar na mesma, todos os objectivos enumerados atrás. Perante grandes
quantidades de dados, o analista só com a metodologia de DP não consegue reportar em tempo útil
e de uma forma completa todas as anomalias. Devido a este factor foi necessário realizar um estudo
de ferramentas de DP existentes no mercado. Como resultado final, foram reportados um conjunto de
anomalias de uma BD relacional de cartões de clientes de operadores de transportes. Estes foram obtidos através de uma auditoria de qualidade de dados realizada através da aplicação das várias etapas
da metodologia DP, auxiliada pelas ferramentas de DP escolhidas.
Palavras-Chave: Data Profiling, Qualidade de Dados, Erros, Auditoria de dados, Base de dados e
Metodologia DP.
i
Abstract
The data of a company that is unknown can harm its business on a large scale. Over time, great initiatives, such as the implementation of an ERP, CRM, mergers, acquisitions or data migration, have one big
flaw in common: incorrect, corrupt, incomplete data, etc. Unfortunately, low quality data causes a slow
death. According to the TDWI reports, over 80% of the interviewed business managers believe their
data is just good. However, over half of their technicians recognize that the data is in worse condition
than the executives believe it to be.
This thesis describes the potential of Data Profiling in detecting several anomalies that can be found in
databases and what are the advantages of using it. To have an accurately and comprehensively detection its necessary one methodology of DP that allows analysts to assess all elements of a BD and
which has a range of features through which it can report the highest number of errors. Thus, this thesis
recast DP methodology in order to concentrate on it, all the objectives listed above. Faced with large
amounts of data, analysts cant report in real time and in comprehensive manner all the anomalies with
only DP methodology. For this reason was necessary to study existing DP Tools. Has final result, were
reported a set of anomalies in a relational database of cards of clients of transports company. These
were obtained through an audit of Data Quality held by applying all stages of DP methodology, aided by
the DP tools chosen.
Keywords: Data Profiling, Data Quality, Errors, Data Audit, Database and DP Methodology.
ii
Índice
Resumo
i
Abstract
ii
Lista de Figuras
vi
Lista de Tabelas
viii
Lista de Acrónimos
ix
1 Introdução
1
1.1 Motivação . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1
1.2 Qualidade de Dados . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2
1.2.1 Problemas de qualidade de dados . . . . . . . . . . . . . . . . . . . . . . . . . . .
2
1.2.2 Dimensões de qualidade de dados . . . . . . . . . . . . . . . . . . . . . . . . . . .
4
1.2.3 Data Profiling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
6
1.3 Problema a Resolver . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
6
1.4 Tecnologia Existente . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
8
1.5 Solução . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
9
1.6 Contribuições . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
9
1.7 Metodologia de Investigação . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
9
1.8 Organização da Dissertação . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
11
2 Trabalho relacionado
13
2.1 Ferramentas de Data Profiling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
13
2.1.1 Actividades das ferramentas de DP . . . . . . . . . . . . . . . . . . . . . . . . . .
14
2.1.2 Classificação das ferramentas de DP . . . . . . . . . . . . . . . . . . . . . . . . .
16
2.2 Algoritmos de detecção de duplicados . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
16
2.2.1 O que são registos duplicados?
. . . . . . . . . . . . . . . . . . . . . . . . . . . .
16
2.2.2 Algoritmos de detecção simples . . . . . . . . . . . . . . . . . . . . . . . . . . . .
19
2.2.3 Algoritmos de detecção complexos . . . . . . . . . . . . . . . . . . . . . . . . . . .
22
2.2.4 Ferramentas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
22
2.2.5 Conclusão . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
23
3 Metodologia de Data Profiling
24
3.1 Análise de cada coluna de uma tabela . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
25
3.1.1 Análise da coluna através das suas propriedades . . . . . . . . . . . . . . . . . .
25
3.1.2 Análise da coluna através de funções estatísticas . . . . . . . . . . . . . . . . . .
27
3.1.3 Análise da coluna através de dicionários de palavras . . . . . . . . . . . . . . . . .
27
3.2 Análise da estrutura da base de dados . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
28
iii
3.3 Análise dos dados através de regras de negócio . . . . . . . . . . . . . . . . . . . . . . .
29
3.4 Análise de registos aproximadamente duplicados . . . . . . . . . . . . . . . . . . . . . . .
30
4 Auditoria da qualidade de dados da BD_OT
32
4.1 Ambiente Experimental . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
32
4.1.1 Tabela Clientes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
33
4.1.2 Tabela Requisições . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
34
4.1.3 Tabela Cartões . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
35
4.2 Aplicação da metodologia de DP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
36
4.2.1 Análise de cada coluna de uma tabela . . . . . . . . . . . . . . . . . . . . . . . . .
37
4.2.2 Análise da estrutura da base de dados
. . . . . . . . . . . . . . . . . . . . . . . .
57
4.2.3 Análise dos dados através de regras de negócio . . . . . . . . . . . . . . . . . . .
61
4.2.4 Análise de registos aproximadamente duplicados . . . . . . . . . . . . . . . . . . .
65
4.3 Avaliação das ferramentas de Data Profiling da Oracle . . . . . . . . . . . . . . . . . . . .
66
4.3.1 OWB - Módulo DP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
67
4.3.2 ODI-DQ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
70
4.3.3 OWB - Módulo DP versus ODI-DQ . . . . . . . . . . . . . . . . . . . . . . . . . . .
70
5 Conclusões
5.1 Trabalho Futuro . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
75
76
Referências
77
6 Anexos
84
iv
6.1 Magic Quadrant for Data Quality Tools, 2007 and 2008 . . . . . . . . . . . . . . . . . . . .
84
6.2 Detecção de registos aproximadamente duplicados no ODI-DQ . . . . . . . . . . . . . . .
85
6.3 Actividades das ferramentas de DP da Oracle . . . . . . . . . . . . . . . . . . . . . . . . .
90
6.3.1 Actividades da ferramenta OWB - Módulo DP . . . . . . . . . . . . . . . . . . . . .
90
6.3.2 Actividades da ferramenta ODI_DQ . . . . . . . . . . . . . . . . . . . . . . . . . .
93
Lista de Figuras
2.1 Métodos para detecção de qualidade de dados [1]. . . . . . . . . . . . . . . . . . . . . . .
13
3.1 À esquerda a metodologia de DP segundo Jack Olson [2] e à direita a metodologia de
DP segundo esta dissertação.
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
24
3.2 Processo de análise da coluna segundo as propriedades da mesma. . . . . . . . . . . . .
26
4.1 Padrões dominantes para o cartão do cidadão (OWB - Módulo DP). . . . . . . . . . . . .
40
4.2 Padrões dominantes para o bilhete de identidade. . . . . . . . . . . . . . . . . . . . . . .
40
4.3 Número de clientes com bilhete de identidade distintos (OWB-Módulo DP). . . . . . . . .
41
4.4 Padrões dominantes para o bilhete de identidade militar. . . . . . . . . . . . . . . . . . . .
41
4.5 Padrões dominantes para a carta de condução. . . . . . . . . . . . . . . . . . . . . . . . .
42
4.6 Padrões dominantes para a cédula pessoal. . . . . . . . . . . . . . . . . . . . . . . . . . .
42
4.7 Padrões dominantes para a cédula consular. . . . . . . . . . . . . . . . . . . . . . . . . .
43
4.8 Padrões dominantes para a autorização de residência. . . . . . . . . . . . . . . . . . . . .
43
4.9 Padrões dominantes para o atestado de residência. . . . . . . . . . . . . . . . . . . . . .
44
4.10 Padrões dominantes para a autorização de permanência. . . . . . . . . . . . . . . . . . .
44
4.11 Padrões dominantes para o no de Ordem CCFL. . . . . . . . . . . . . . . . . . . . . . . .
45
4.12 Padrões dominantes para o passaporte. . . . . . . . . . . . . . . . . . . . . . . . . . . . .
45
4.13 Padrões que o código-postal da tabela Clientes, assume ao longo da sua coluna. . . . .
46
4.14 Padrões que o código-postal da tabela Requisições, assume ao longo da sua coluna. . .
47
4.15 Expressão regular para detectar emails inválidos [3]. . . . . . . . . . . . . . . . . . . . . .
47
4.16 Padrões que o número de telefone da tabela Clientes assume. . . . . . . . . . . . . . . .
48
4.17 Padrões que o número de telefone da tabela Requisições assume. . . . . . . . . . . . . .
49
4.18 Padrões que o número de telemóvel assume na tabela Clientes. . . . . . . . . . . . . . .
50
4.19 Padrões que o número de telemóvel assume na tabela Requisições. . . . . . . . . . . . .
51
4.20 Expressão regular para detectar números de telemóvel errados. . . . . . . . . . . . . . .
51
4.21 Expressão regular para detectar números de telefone errados. . . . . . . . . . . . . . . .
52
4.22 Padrões dominantes para o número de requisição dos clientes. . . . . . . . . . . . . . . .
52
4.23 Data de nascimento mais recente e mais antiga - Tabela Clientes. . . . . . . . . . . . . .
52
4.24 Data de nascimento mais recente e mais antiga - Tabela Requisições. . . . . . . . . . . .
53
4.25 Data de emissão de um documento de identificação mais recente e mais antiga - Tabela
Clientes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
53
4.26 Data de emissão de um documento de identificação mais recente e mais antiga - Tabela
Requisições. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
54
4.27 Data do pedido de um cartão mais recente e mais antiga. . . . . . . . . . . . . . . . . . .
54
4.28 Número de clientes que não têm o género definido, através da ferramenta ODI-DQ. . . .
55
4.29 Número de requisições que não têm o género do cliente definido, através da ferramenta
OWB - Módulo DP. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
55
v
4.30 Query para detectar campos de colunas redundantes com valores distintos. . . . . . . . .
60
4.31 Queries para detectar datas de nascimento inválidas. . . . . . . . . . . . . . . . . . . . .
61
4.32 Query para detectar datas de nascimento posteriores a datas de emissão de documentos
de identificação. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
63
4.33 Query para detectar clientes com perfis incorrectos. . . . . . . . . . . . . . . . . . . . . .
64
4.34 Campos da primeira regra para a formação da chave de detecção. . . . . . . . . . . . . .
65
4.35 Campos da segunda regra para a formação da chave de detecção. . . . . . . . . . . . . .
66
4.36 Campos da terceira regra para a formação da chave de detecção. . . . . . . . . . . . . .
66
4.37 Campos da última regra para a formação da chave de detecção. . . . . . . . . . . . . . .
67
6.1 Magic Quadrant for Data Quality Tools 2007. . . . . . . . . . . . . . . . . . . . . . . . . .
84
6.2 Magic Quadrant for Data Quality Tools 2008. . . . . . . . . . . . . . . . . . . . . . . . . .
84
6.3 Projecto de qualidade de dados para a detecção de registos duplicados.
. . . . . . . . .
85
6.4 Transformer - Parser Inputs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
86
6.5 Customer Data Parser - Options. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
87
6.6 Window Key Generator - Keys. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
88
6.7 Relationship Linker - Process. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
89
6.8 Relationship Linker - Match Rules. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
90
6.9 Relationship Linker - Frequência dos valores únicos da coluna LEV1_MATCHED da tabela
vi
pt rellink p5. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
90
6.10 Relationship Linker - Exemplo de um cliente aproximadamente duplicado. . . . . . . . . .
90
6.11 Estrutura de um projecto de profiling no ODI-DQ. . . . . . . . . . . . . . . . . . . . . . . .
98
Lista de Tabelas
2.1 Classificação das várias ferramentas de DP (1). Legenda: N -> Não, S -> Sim, - ->
Indefinido, O -> Oracle, SQLS -> SQL Server, A -> Acess, FF -> Flat Files, MS -> MySql,
FP -> Formato Próprio.
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
17
2.2 Classificação das várias ferramentas de DP (2). Legenda: N -> Não, S -> Sim, - ->
Indefinido, O -> Oracle, SQLS -> SQL Server, A -> Acess, FF -> Flat Files, MS -> MySql,
FP -> Formato Próprio.
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
18
4.1 Amostra dos resultados de emails inválidos na tabela Clientes. . . . . . . . . . . . . . . .
45
4.2 Amostra dos resultados de emails inválidos na tabela Requisições. . . . . . . . . . . . . .
46
4.3 Amostra dos resultados de telefones errados na tabela Clientes. . . . . . . . . . . . . . .
48
4.4 Amostra dos resultados de telefones errados na tabela Requisições. . . . . . . . . . . . .
48
4.5 Amostra dos resultados de telemóveis errados na tabela Clientes. . . . . . . . . . . . . .
49
4.6 Amostra dos resultados de telemóveis errados na tabela Requisições. . . . . . . . . . . .
50
4.7 Amostra de padrões inválidos do número da requisição. . . . . . . . . . . . . . . . . . . .
53
4.8 Amostra dos resultados de moradas mal distribuídas na tabela Clientes. . . . . . . . . . .
56
4.9 Amostra dos resultados de moradas mal distribuídas na tabela Requisições. . . . . . . .
56
4.10 Amostra dos resultados de códigos-postal inválidos na tabela Clientes. . . . . . . . . . .
57
4.11 Amostra dos resultados de códigos-postal inválidos na tabela Requisições. . . . . . . . .
57
4.12 Amostra de clientes com requisições de outros clientes. . . . . . . . . . . . . . . . . . . .
60
4.13 Amostra de clientes, onde os campos em comum estão preenchidos na tabela Clientes,
mas não o estão na tabela Requisições. . . . . . . . . . . . . . . . . . . . . . . . . . . . .
60
4.14 Amostra de clientes, cujos campos comuns na tabela Requisições estão mal escritos. . .
61
4.15 Amostra dos resultados de datas inválidas na tabela Clientes. . . . . . . . . . . . . . . . .
61
4.16 Amostra dos resultados de datas inválidas na tabela Requisições. . . . . . . . . . . . . .
61
4.17 Amostra dos resultados de datas de emissão inválidas na tabela Clientes. . . . . . . . . .
62
4.18 Amostra dos resultados de datas de emissão inválidas na tabela Requisições. . . . . . .
62
4.19 Amostra dos resultados de datas de requisições inválidas. . . . . . . . . . . . . . . . . . .
62
4.20 Amostra dos resultados de datas de nascimento posteriores às datas de emissão na
tabela Clientes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
63
4.21 Amostra dos resultados de datas de nascimento posteriores às datas de emissão na
tabela Requisições. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
63
4.22 Amostra dos resultados de datas de nascimento posteriores às datas de requisições. . .
63
4.23 Amostra dos resultados de datas de emissão de documentos de identificação posteriores
às datas de requisições. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
64
4.24 Amostra de clientes com cartões que não correspondem ao seu perfil. . . . . . . . . . . .
64
4.25 Amostra de clientes duplicados. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
66
4.26 Actividades da ferramenta OWB - DP que se encontram nas várias etapas da metodologia
DP (1). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
68
vii
4.27 Actividades da ferramenta OWB - DP que se encontram nas várias etapas da metodologia
DP (2). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
69
4.28 Actividades da ferramenta ODI-DQ que se encontram nas várias etapas da metodologia
DP (1). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
71
4.29 Actividades da ferramenta ODI-DQ que se encontram nas várias etapas da metodologia
DP (2). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
72
4.30 Actividades da ferramenta ODI-DQ que se encontram nas várias etapas da metodologia
DP (3). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
73
4.31 Actividades da ferramenta ODI-DQ que se encontram nas várias etapas da metodologia
DP (4). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
74
4.32 Amostra dos tempos de execução das duas ferramentas de DP sobre as principais tabelas
da BD_OT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
viii
74
Lista de Acrónimos
BD
Base de Dados
CRM
Customer Relationship Management
DC
Data Cleaning
DQ
Data Quality
DP
Data Profiling
ERP
Enterprise Resource Planning
ETL
Extract, Transform and Load
ODI-DQ
Oracle Data Quality for Data Integrator
OT
Operadora de Transportes
OWB
Oracle Warehouse Builder
RI
Restrição de Integridade
SI
Sistema de Informação
SGBDRs
Sistemas de Gestão de Base de Dados Relacionais
TDWI
The Data Warehousing Institute
TI
Tecnologias de Informação
ix
1 Introdução
1.1
Motivação
A área da qualidade dos dados num Sistema de Informação (SI) revela o quão longe ou perto os dados
e a sua estrutura estão do padrão definido como óptimo para uma determinada aplicação [4]. Até há
dez anos atrás, esta área era relativamente mal definida dentro da estratégia de uma organização de
Tecnologias de Informação (TI) [5]. De facto, nas aplicações já existentes, a manutenção da qualidade
dos dados era considerada como um acto secundário, pois julgava-se que gerava pouco ou nenhum
valor para o negócio da empresa. A juntar-se a este factor, na integração de várias aplicações, as
equipas de projecto normalmente iniciavam a sua actividade programando a nova aplicação que suportava a integração, em vez de, numa primeira fase, analisarem e interrogarem as Bases de Dados
(BD) que iriam servir a nova aplicação [1]. Ao longo do tempo de vida dos Sistemas de Informação,
os utilizadores e as equipas de desenvolvimento das aplicações, demonstraram uma falta de preocupação sobre os dados, originando um decréscimo da respectiva qualidade. Esta situação surgiu pelos
seguintes motivos [6]:
• Falta de validação na entrada dos dados dos SI;
• Demasiada confiança nos metadados das base de dados, ignorando que uma especificação correcta não invalida a existência de erros nos dados;
• Utilização de métodos primários na detecção de anomalias nos dados, como é o caso das interrogações Structured Query Language (SQL) efectuadas de maneira ad-hoc. Estas interrogações
são normalmente executadas sobre amostras de dados e não cobrem, na totalidade, todos os
registos de uma BD [1].
Devido aos vários problemas provocados pela falta de uma minuciosa e sistemática análise de dados, o
interesse pela qualidade dos mesmos que suportam um SI, tem crescido ao longo do tempo. Como tal
têm sido vários os inconvenientes gerados, mas os mais marcantes podem ser enunciados da seguinte
forma [7]. Em primeiro lugar, o descontentamento demonstrado pelos clientes de SI ao adquirirem
aplicações que não foram pensadas de acordo com os dados e estrutura das suas BDs. Por exemplo,
a existência de uma aplicação que tem na sua interface um formulário em que a morada está separada
em três caixas de texto, tendo também a sua BD três campos a representar a mesma, mas ao longo
da BD existem registos em que a morada se encontra dividida nos três campos e registos em que a
mesma se encontra num só campo. Em segundo encontra-se o descontentamento da administração
de uma empresa, que em certas ocasiões julga ter um conjunto de clientes, mas na realidade, parte
destes estão duplicados nas base de dados que suportam os diferentes SI’s da empresa. Por último,
pode-se enunciar o aumento de custo dos projectos devido ao alargamento do tempo dos mesmos para
a correcção dos vários erros de uma aplicação e/ou dos dados de uma BD, que não foram planeados
nem pensados.
1
1.2
Qualidade de Dados
A qualidade de dados define-se como a actividade que detecta e corrige anomalias nos dados [4]. Os
dados são considerados de alta qualidade se cumprirem os requisitos para as operações a que foram
destinados e se estão de acordo com as decisões tomadas e planeadas [8]. Quando não o são, significa
que existe um conjunto de problemas de qualidade de dados que podem ser encontrados a nível do
esquema de dados e/ou a nível das instâncias dos mesmos.
1.2.1
Problemas de qualidade de dados
Os problemas de qualidade de dados representados a seguir foram retirados do artigo A survey of data
quality tools [9]. No âmbito desta dissertação os problemas a detectar estão focados nas instâncias dos
dados, sendo que parte destes podem ser eliminados pela tecnologia relacional (SGBDRs) e outros
não.
1.2.1.1
Problemas de qualidade de dados eliminados pelos SGBDRs
Os Sistemas de Gestão de Base de Dados Relacionais (SGBDRs) possuem um mecanismo, designado
Restrição de Integridade (RI), que assegura a definição de um esquema de dados. Quando definidas,
as RI evitam um conjunto de problemas de qualidade dos dados [8], tal como apresentado em seguida:
• Not Null: Obriga que uma coluna esteja sempre preenchida. Por exemplo, todos os valores do
campo telefone têm de estar sempre preenchidos;
• Unique e Primary Key : Evitam valores duplicados numa coluna. Existem dados que não fazem
sentido como duplicados, pois identificam univocamente cada registo de uma tabela, como por
exemplo o número do bilhete de identidade;
• Foreign Key : Evita que numa coluna de uma tabela existam valores que não existem numa coluna
chave de outra tabela. Por exemplo: O campo identificador de um departamento é uma chave
estrangeira na tabela dos empregados, ou seja, um empregado tem sempre um departamento
associado que existe;
• Domain: Obriga que uma coluna tenha só valores que foram definidos nos metadados. Por exemplo, uma coluna que define o género de uma pessoa, só tem dois tipos definidos (F e M) não
permitindo o preenchimento de outro valor que não seja um dos dois referidos;
• Check : Evita através de uma condição que sejam colocados valores numa tabela que vão contra
da mesma. Por exemplo, a idade de um empregado deve ser maior do que 18 anos. Todos os
valores que sejam inferiores a este valor não são permitidos;
• Default: Evita valores a null numa coluna, através da inserção de um valor predefinido, por exemplo, se a idade de um colaborador não for inserida o valor colocado será 18;
• Assertion: Evita através de uma condição que sejam colocados valores numa tabela ou várias
que vão contra da mesma. Por exemplo, a soma de todos os empréstimos de uma filial tem de
ser menor do que a soma dos valores de todas suas contas;
2
• triggers: Obrigam a execução de determinados eventos, através de procedimentos que definem
restrições de integridade e são executados antes ou depois de ocorrerem determinados eventos
na base de dados. Por exemplo, a verificação do código do distribuidor de um filme se existe na
tabela de Distribuidores antes de se inserir ou actualizar uma linha da tabela Filmes.
1.2.1.2
Problemas de qualidade de dados que não são evitados pelos SGBDRs
Existem no entanto problemas com dados que não podem ser evitados através da definição de restrições de integridade suportados pelos SGBDRs. Estes problemas podem atingir registos isolados ou
conjuntos de registos. Em termos de registos isolados, os problemas dizem respeito a uma só entidade
do mundo real e são os seguintes [8] [9]:
• Falta de dados correctos: Existem colunas que estão definidas como sendo not null, logo é
necessário preencher a coluna com um valor. Por vezes, o utilizador não dispõe destes valores,
sendo levado a colocar um valor não correcto no registo. Por exemplo: O número do bilhete de
identidade para um determinado cliente não foi preenchido, levando o utilizador a inserir o valor
99999999, para que o sistema não dê erro;
• Dados erróneos: Existem dados que são válidos, mas que não estão de acordo com a entidade
no mundo real. Por exemplo: O atributo Idade de um empregado contém o valor trinta e um
quando, na verdade, a sua idade real é trinta anos;
• Palavras mal escritas: São dados com erros de ortografia. Como por exemplo, o campo nome
com um registo ’Mria’;
• Valores embebidos: São referentes a palavras que se adicionam aos campos mas que não fazem
parte. O campo nome tem o seguinte valor: ’Presidente Jorge Sampaio’. Neste exemplo, a palavra
’Presidente’ não deveria estar contida no nome da pessoa, pois o campo diz respeito ao nome de
uma pessoa e não ao seu cargo na sociedade;
• Dados no campo errado: São todos os dados que são colocados em campos que não são os
respectivos. Por exemplo, no campo cidade não pode existir um valor ’Espanha’, porque o mesmo
não é uma cidade mas sim um país;
• Dados ambíguos: Referem-se a dados que podem ter vários significados. Podem ser encontrados
em abreviaturas ou em contextos incompletos. Por exemplo, o valor ’J. Guerra’ tanto pode ser
interpretado como João Guerra ou como Joaquim Guerra. Outro exemplo poderá ser a palavra
Cuba que pode ser entendida como sendo a vila de Portugal ou como o país da América Central;
• Categoria de dados errada: Diz respeito a um conjunto de valores que saem do intervalo de uma
categoria. Tem-se como exemplo, o atributo país e cidade não fazem parte da mesma categoria;
Os problemas de dados que afectam conjuntos de registos podem ser identificados numa só tabela ou
em várias e são os seguintes:
• Registos aproximadamente duplicados: São registos escritos de uma forma um pouco diferente,
mas que se referem a uma mesma entidade no mundo real. Exemplo: Empregado1 (Nome =
’Maria Simões’, Morada = ’223, praceta A, Lisboa’, Aniversário = 01/01/1945’) e Empregado2
3
(Nome = ’M. Simoes’, Morada = ’23, praceta A, Lx’, Aniversário = 01/01/1945’);
• Registos contraditórios: São registos que se referem à mesma entidade, mas que têm algo de
contraditório na sua informação. Exemplo: Empregado1 (Nome = ’Maria Simões’, Morada = ’223,
praceta A, Lisboa’, Aniversário = 01/01/1945’) e Empregado2 (Nome = ’M. Simoes’, Morada = ’23,
praceta A, Lx’, Aniversário = 01/01/1955’), onde se pode verificar que existem datas de aniversário
diferentes;
• Dados temporais não adequados: São dados que violam um intervalo de tempo. Por exemplo,o
salário de um colaborador deve de deixar de estar disponível quando o mesmo o levanta por
completo. Se isto não acontecer, então existe uma inconsistência a nível do tempo;
• Dados sem padrão: São todos os registos que usam representações diferentes, o que invalida
a sua comparação. Como exemplos tem-se, o campo Nome com os valores: ’Ana Fernandes’
e ’Ferreira Cátia’, que não usam a mesma regra de escrita. O uso de diferentes formatos de
codificação, ASCII e UTF-8. Formatação da representação, 10.5£ e £10.5;
• Unidades de medida inconsistentes: Existe uma falta de coerência na representação das unidades
de medida utilizadas em diferentes valores de uma mesma coluna. Por exemplo, a distância entre
localidades pode ser descrita num valor em quilómetros e noutro em milhas;
• Conflito de nomes: Existem nomes de atributos iguais mas com significado diferente, que são
usados por diferentes tabelas de uma base de dados (homónimos), como por exemplo o atributo
planta, que pode designar um ser vivo ou um desenho de uma habitação. Podem ainda existir
atributos com nome diferente numa mesma tabela mas que dizem respeito à mesma entidade no
mundo real (sinónimos), como é o caso do atributo Idade e do atributo Anos;
• Conflitos de estrutura: Existem diferentes representações de um mesmo objecto em base de
dados distintas. Exemplo: Num registo tem-se a morada num só campo, e noutro a mesma está
separada em vários campos.
1.2.2
Dimensões de qualidade de dados
Raramente os dados manipulados por um SI de grande dimensão se podem classificar como sendo
100% de boa qualidade. O grande objectivo não é garantir que a qualidade seja perfeita, mas que seja
suficiente para que a organização possa sobreviver e tomar decisões com base em dados fiáveis. Uma
qualidade de dados elevada significa que os dados são adequados para serem usados pelos clientes,
ou seja, um dado tem qualidade se satisfaz os requisitos desejados. Estes requisitos podem ser ditados
por dimensões de qualidade de dados. Uma dimensão de qualidade de dados é uma característica que
um dado deve conter, cada dimensão tem uma ou mais medidas para verificar se o dado detém ou não
a dimensão em questão. Os dados para serem de qualidade devem de ser [10] [11] [12] [13] [4]:
• Precisos: A precisão é a característica que define o quão correctos, livres de erros e próximos do
mundo real estão os dados. Para ser preciso, um valor deve ser certo e deve ser representado de
uma forma consistente e sem ambiguidade. A precisão é a dimensão fundamental da qualidade
de dados. Se um dado não é preciso, as outras dimensões são menos importantes;
4
• Completos: A completude é a dimensão que caracteriza se os dados contêm todos os pormenores
necessários ao negócio onde estão inseridos;
• Actualizados: A temporalidade permite verificar se os dados estão suficientemente actualizados
para as tarefas que são precisos;
• Consistentes: A consistência é uma característica que afirma que um dado é consistente se o seu
valor e se a sua estrutura têm sempre a mesma representação.
A dimensão precisão pode ser dividida em duas partes: sintáctica e a semântica. A precisão sintáctica
preocupa-se em avaliar se o valor em questão pertence ou não a um domínio. Por exemplo: o nome
’João’ é correcto, enquanto ’Jão’ não é um nome válido, pois não pertence a nenhum domínio de nomes
de pessoas. Em relação à precisão semântica, esta preocupa-se com o verdadeiro sentido e valor dos
dados no mundo real. Exemplo: filme1 (nome = ’Casablanca’, Director = "Weir’) e filme2 (nome = ’Clube
dos poetas mortos’, Director = ’Curtiz’). Estes dois registos não são precisos semanticamente, pois têm
os directores trocados. Do ponto de vista sintáctico estão correctos.
Em relação à completude, esta dimensão pode ser dividida em três tipos: a nível do esquema, ao
nível da coluna e a nível da população. Ao nível do esquema é necessário verificar se estão definidas
todas as entidades e atributos precisos ao negócio. A nível da coluna, é necessário que não faltem
valores na mesma. E a nível da população verifica que valores estão em falta para que a mesma fique
completa, por exemplo, uma coluna deveria ter pelo menos a ocorrência de 50 países e no fim só tem
43, a população é considerada incompleta.
A temporalidade é composta por um conjunto de dimensões relacionadas com o tempo que permitem verificar a idade dos dados, ou seja, se os mesmos são actualizados ou não com alguma
frequência, se estão actuais, etc. A actualidade avalia a rapidez com que um estado do sistema é
modificado depois de o sistema do mundo real ter mudado. Por exemplo: O Manuel fez 19 anos há um
mês atrás e o sistema demora um mês a mudar o mesmo. A volatilidade preocupa-se com o intervalo
de tempo com que um estado do sistema é modificado. Por exemplo: a data de nascimento de uma
pessoa tem volatilidade 0, pois nunca deve mudar, a pessoa só nasce uma vez. Em contrapartida, o
stock de supermercado muda frequentemente, logo terá uma volatilidade grande. A última dimensão do
tempo enumerada é a intemporalidade que analisa se os dados estão disponíveis para serem usados
numa determinada tarefa, ou seja, podem estar actuais mas serem inúteis, pois estão atrasados para
um uso específico. Por exemplo: O horário de um curso de uma universidade, pode conter a que horas
começa cada disciplina, mas se só é colocado no fim do semestre, é actual mas não foi utilizado quando
preciso, ou seja, no início das aulas.
Por fim, a consistência dos dados é avaliada através de restrições de integridade, onde todos os
dados da BD devem obedecer as mesmas. Existem dois tipos: as restrições intra-relação e as restrições
inter-relação. As primeiras podem olhar para um único atributo (restrições de domínio) ou para vários
de uma relação. Uma restrição de domínio pode ser: A idade de uma pessoa está definida entre 0
e 120 anos. E uma restrição de integridade mais complexa, poderá ser: Se os anos de trabalho de
uma pessoa são inferiores a 3 anos, então o salário não pode exceder um determinado valor por ano.
Em relação às segundas restrições, estas preocupam-se com os diferentes tipos de dependências que
5
podem existir entre os dados, como o caso da dependência de chave (não há duplicados), dependência
de inclusão (chave estrangeira) e dependência funcional.
1.2.3
Data Profiling
A tarefa de Data Profiling (DP) é definida como uma das sub-actividades do processo de qualidade de
dados. O processo de data profiling pretende detectar de forma sistemática, os erros, as inconsistências, as redundâncias e a existência de informação incompleta nos dados e respectivos metadados [2],
[14], [15]. Depois de analisados os dados, o processo de DP deve produzir um conjunto de relatórios
com informação sobre o estado dos dados. Com estes relatórios os analistas podem [16] [17]:
• Avaliar se os metadados descrevem exactamente os valores reais da base de dados. Por exemplo, um campo pode estar a ser definido como alfanumérico, quando deveria ser definido como
numérico [18];
• Ter uma ideia clara da qualidade dos dados. A informação contida nos relatórios, pode servir para
saber se as dimensões da qualidade de dados estão a ser cumpridas ou não. Como por exemplo:
a existência de muitos registos não preenchidos pode significar que o campo ou tabela não estão
completos (dimensão completude);
• Corrigir os dados que têm problemas, através de uma ferramenta de limpeza de dados (Data
Cleaning). Muitas ferramentas de DP geram regras para resolver alguns problemas detectados,
para além de alertarem para um conjunto de erros que precisa de ser resolvido. É aconselhado
fazer-se sempre o ciclo ’detecta, corrige’, até que os resultados dos relatórios de DP sejam atingidos [19];
• Equacionar a alteração dos requisitos da aplicação. Se ao analisar os dados, os erros que estão
a ser detectados não fizerem sentido aos analistas, é provável que a aplicação não tenha sido
bem desenhada, ou seja, a aplicação não está de acordo com os dados que estão na BD. Se a
aplicação tem problemas é necessário rever os requisitos e os respectivos processos de negócio
e se for necessário alteram-se os mesmos [1];
• Avaliar o planeamento de um projecto. Se uma equipa compreender cedo os possíveis erros ou
anomalias que podem estar nos dados de um projecto, pode evitar atrasos e os aumentos de
custo no mesmo [6].
Para além de todas as vantagens enumeradas, o processo de data profiling, permite a qualquer analista
compreender melhor o negócio do projecto onde está envolvido [18].
1.3
Problema a Resolver
Este trabalho foi realizado no Instituto Superior Técnico e na unidade de Business Intelligence da Link
Consulting. O objectivo geral desta dissertação foi a realização de uma auditoria da qualidade de
dados de uma base de dados relacional de cartões de clientes de operadores de transportes, que é
denominada por BD_OT. A informação armazenada na BD_OT diz respeito aos clientes dos operadores
de transporte públicos de uma determinada zona. As suas principais tabelas são: clientes, requisições
6
e cartões. Cada uma destas tabelas contêm aproximadamente dois milhões de registos. A tabela
Clientes suporta os dados pessoais do cliente (nome, morada, etc.) e um conjunto de campos que são
úteis à gestão dos mesmos, como por exemplo a data em que um cliente aderiu ao operador. A tabela
Requisições contém informação relacionada com o tipo de requisição efectuada pelo cliente, qual o
motivo para o pedido de uma requisição (exemplo: cartão caducado), o local onde foi pedida, e alguma
informação pessoal referente ao cliente. Os registos da tabela Cartões contêm informação acerca do
tipo físico de cartão, qual é a sua requisição e a que tipo de cliente pertence consoante a sua idade ou
profissão. Como ponto de partida para a auditoria da BD, foi indicado o seguinte conjunto de problemas
de dados detectados pelos utilizadores da aplicação, são eles:
• Clientes duplicados. Existem um conjunto de clientes que são ditos únicos, mas que na realidade dizem respeito a outro cliente existente na tabela. Por exemplo, cliente1 (Marco António,
19/10/1989, Rua Verde, n14) e cliente2 (MARCO A., 19/10/1989, R. verde, no 14);
• Clientes mal consolidados. São todos os clientes que têm requisições pertencentes a outros
clientes ou requisições com os dados pessoais do cliente errados;
• Números de documentos de identificação não normalizados. Existem identificadores de documentos que no geral são diferentes mas que na realidade são de um mesmo cliente, como por
exemplo, o número de um bilhete de identidade ser ’0532548’ e outro ’N0532548’;
• Números de documentos de identificação inválidos. São todos os identificadores que não seguem
o formato standard definido segundo o documento de identificação do cliente. Por exemplo, o
identificador do bilhete de identidade é um número inteiro, positivo e sequencial, se existirem
valores como ’jkj987676’, estão errados;
• Data de nascimento inválida. Representa todas as datas de nascimento que não podem ser reais,
devido ao intervalo de tempo normal para o nascimento de uma pessoa. Exemplo ’2/09/2050’, a
pessoa ainda não nasceu devido ao ano de nascimento ser superior ao ano actual no mundo real;
• Data de emissão do documento de identificação inválida. São todas as datas que saíam do
intervalo de tempo possível para a realização de um documento de identificação. Por exemplo
todas as datas que não estão compreendidas no intervalo de anos [1900-2008];
• Data da requisição inválida. Existem requisições com datas inferiores a 2001, ano este em que o
sistema de cartões começou a ser utilizado pela operadora em questão;
• Moradas não normalizadas. Existem clientes que têm a sua morada num só campo e outros que
têm a mesma repartida por vários na BD;
• Códigos postais não existentes. São todos os códigos-postais que não fazem parte da lista
disponibilizada pelos CTT, por exemplo ’1495-000’;
• Códigos postais incompletos. São códigos que existem mas que não estão completos, por exemplo ’1900’;
• E-mail inválido. São e-mails que estão mal escritos e que são impossíveis de existir no mundo
real, como por exemplo, o número um ser um e-mail válido;
• Campo sexo não preenchido (M/F). O campo Género do cliente, não tem os valores da sua coluna
todos preenchidos;
7
• Números de telemóvel ou telefone incompletos. São números que não têm na sua composição
nove dígitos;
• Números de telemóvel ou telefone errados. São números de telefone ou telemóvel que não podem
existir. Por exemplo, o número de telemóvel ser ’21’ ou o número de telefone ser ’5’;
• Modelo de cartão não compatível com idade do cliente. São todos os clientes que não têm um
passe correspondente a sua idade, como por exemplo, existirem crianças com passes de idosos
e vice-versa;
• No de requisição inválido. São todas as requisições cujo número não obedecem ao formato
’OOPPPPPSSSSSSS’, onde ’OO’ significa o código do operador, ’PPPPP’ o código do posto e
’SSSSSSSS’ é um número sequencial.
Estes são alguns dos problemas que a BD_OT contém. Com base na metodologia de data profiling
apresentada no Capítulo 3, irão ser detectados de forma rigorosa e sistemática a maior parte dos
problemas que existem nas três tabelas referidas.
1.4
Tecnologia Existente
A auditoria da qualidade de dados armazenados numa BD relacional pode ser efectuada através da realização de interrogações ad-hoc em SQL, utilizando aplicações como o SQL Navigator da Quest Software, SQL Server Management Studio da Microsoft ou o Sqlplus da Oracle, sabendo que uma auditoria
realizada desta forma não detecta de uma forma rigorosa e sistemática todos os problemas existentes
numa BD. Para que isto não aconteça utilizam-se ferramentas desenhadas segundo a metodologia de
DP. Estas últimas, permitem ao analista verificar de uma forma sistemática e automática a maior parte
dos problemas ou anomalias existentes numa determinada base de dados relacional. As ferramentas de
DP estão normalmente incluídas nas soluções de Data Quality (DQ), sendo raramente comercializadas
em separado. Segundo a classificação do quadrante mágico da Gartner de 2007 e 2008 [20] [21], em
termos de ferramentas de Data Quality, as empresas leaders são Trillium Software, Business Objects,
DataFlux, IBM e Informatica. Para mais informação sobre o lugares ocupados pelas empresas com
soluções de Data Quality mais importantes do mercado, consultar o anexo 6.1. Para que se pudesse
utilizar uma ou mais ferramentas de DP foi necessário saber o que existia no mercado. Desta forma
realizou-se um estudo sobre que ferramentas comerciais e de distribuição livre que existiam hoje em
dia. Este estudo pode ser visualizado no Capítulo 2, designado como estado da arte das ferramentas
de DP. Deste estudo foram escolhidas as ferramentas de Data Quality da Oracle, porque:
• Já existiam na Link Consulting;
• Uma das ferramentas para DP disponibilizadas pela Oracle foi comprada à Trillium Software, uma
das empresas leaders das ferramentas de DQ.
8
1.5
Solução
A solução encontrada para efectuar a auditoria à qualidade dos dados da BD_OT, foi a aplicação sistemática de uma metodologia de data profiling utilizando a solução de DP da Oracle. As componentes
da Oracle utilizadas foram as seguintes:
• Oracle Warehouse Builder (OWB) 10G Release 2, módulo data profiling e Oracle Database 10G
Release 2;
• Oracle Data Quality for Data Integrator (ODI-DQ) 10G Release 3;
• SQL Navigator 5.0. para as interrogações que não foram possíveis de realizar nas duas ferramentas anteriores;
• LingPipe 3.5.1, Apache-ant-1.7.1, MySQL Server 5.0 e Navicat for MySQL, para detecção de
anomalias que envolveram dicionários de palavras.
1.6
Contribuições
As principais contribuições desta tese são as seguintes:
• Metodologia de data profiling, que dispõe exaustivamente todos os problemas de dados;
• Análise da qualidade de dados de uma base de dados de clientes de transportes públicos;
• Estado da arte das ferramentas comerciais e de distribuição livre, para data profiling;
• Avaliação do pacote de componentes de software da Oracle para data profiling, no que diz respeito
à sua capacidade para executar as tarefas que compreendem a metodologia DP e o desempenho
conseguido perante um conjunto de dados de grande dimensão.
1.7
Metodologia de Investigação
Esta secção pretende dar a conhecer o método de trabalho adoptado e quais as etapas usadas na
realização desta dissertação, este método de trabalho é explicado através da metodologia de investigação Estudo de Caso. Uma metodologia de investigação pode ser classificada como qualitativa ou
quantitativa. O método qualitativo consiste em coleccionar, analisar e interpretar informação através da
observação do objecto no seu local de acção. Assim sendo, o foco deste método está na descrição do
objecto a analisar usando a interpretação e consequentemente a criação de teorias. Por outro lado, o
método quantitativo consiste em usar um conjunto de questões com respostas pre-definidas para obter
informação sobre o objecto em estudo. Esta informação extraída é mensurável e permite testar teorias,
como exemplo tem-se os questionários. Nesta dissertação a escolha recaiu sobre o Estudo de Caso,
pois trata-se da investigação de um objecto no seu ambiente real (método qualitativo). Esta metodologia permite abordar a problemática dos SI com foco no contexto organizacional onde esta se enquadra.
A falha deste método está no facto de se limitar a uma única instância ou organização, dificultando a
generalização dos resultados, para outras organizações. Qualquer metodologia tem no mínimo uma
iteração. Uma iteração (etapa) é composta normalmente por três passos:
9
• Definição do objectivo, o que se pretende realizar em cada etapa da investigação até se chegar
ao fim da mesma;
• Recolha de dados, através da utilização de técnicas que podem trabalhar em conjunto ou isoladamente;
• Análise e síntese, que permite a consolidação dos dados obtidos, utilizando a metodologia e
abordagem escolhidas. É a estruturação da informação obtida.
Uma metodologia pode ter só uma iteração ou várias, dependendo do tipo de problema que se está a
tentar resolver. O processo de investigação desta dissertação, pode-se descrever nas seguintes etapas
(iterações):
• 1a Etapa - Compreensão do tema e metodologia DP:
– Passo 1: Pesquisa de vários artigos, livros e documentos no geral, relacionados com o tema
Data Profiling;
– Passo 2: Leitura e assimilação dos vários documentos e capítulos do livro de DP relacionados com o data profiling, a sua metodologia e respectivas funcionalidades.
• 2a Etapa - Apresentação do problema do caso prático a resolver:
– Passo 1: Reunião com o cliente, para explicação do negócio e do esquema relacional da BD
a analisar;
– Passo 2: Apresentação de um conjunto de problemas a detectar na BD.
• 3a Etapa - Escolha das ferramentas para solucionar o problema definido na etapa anterior:
– Passo 1: Pesquisa de um conjunto de ferramentas de DP;
– Passo 2: Leitura de vários documentos sobre as ferramentas encontradas;
– Passo 3: Teste e configuração de ferramentas e demos, quando existiam, das ferramentas;
– Passo 4: Comparação das várias ferramentas de DP, pesquisa de classificações de quadros
da Gartner para ferramentas de DP, embora existisse só para as ferramentas de Data Quality;
– Passo 5: Escolha da ferramenta principal a usar para detectar um conjunto de anomalias nos
dados.
a
• 4 Etapa - Utilização da ferramenta escolhida na etapa anterior:
– Passo 1: Instalação e configuração da BD10.2g e OWB10.2g. Resolução dos vários erros
encontrados na configuração do OWB, através da leitura de vários manuais de instalação e
configuração;
– Passo 2: Exportação de um conjunto de dados de teste para usar na ferramenta;
– Passo 3: Estudo aprofundado do módulo de DP do OWB;
– Passo 4: Análise e conclusões sobre a ferramenta, segundo os resultados obtidos da aplicação da mesma aos dados de teste.
a
• 5 Etapa - Escrita do estado da arte:
– Passo 1: Pesquisa e leitura de artigos sobre algumas técnicas de análise de dados que
estavam muito pouco claras no data profiling, como dependências funcionais, detecção de
outliers;
– Passo 2: Leitura dos capítulos de um livro sobre dimensão de qualidade de dados;
10
– Passo 3: Leitura de artigos de problemas de qualidade de dados;
– Passo 4: Realização da tabela de classificação de ferramentas DP encontradas.
a
• 6 Etapa - Apresentação dos primeiros resultados da análise dos dados ao cliente:
– Passo 1: Apresentação da detecção de alguns problemas de dados, através da ferramenta
OWB, ao cliente;
– Passo 2: Mudança de análise de dados de teste para dados reais.
• 7a Etapa - Definição da metodologia de DP:
– Passo 1: Estudo aprofundado da metodologia de DP de Jack E. Olsen [2] ;
– Passo 2: Verificação das funcionalidades da metodologia anterior segundo o problemas apresentados na 2a etapa e os os problemas de qualidade de dados;
– Passo 3: Construção da metodologia DP desta dissertação com base nos passos anteriores
desta etapa.
• 8a Etapa - Auditoria da qualidade de dados da BD_OT:
– Passo 1: Pesquisa e leitura de artigos e capítulos de livros sobre detecção de registos aproximadamente duplicados;
– Passo 2: Instalação do SQL Server 2005 Integration Services para aplicar detecção de registos aproximadamente duplicados;
– Passo 3: Estudo de outra ferramenta de DP da Oracle, o ODI-DQ;
– Passo 4: Análise dos dados da BD_OT segundo a metodologia DP, através das ferramentas
de DP da oracle;
– Passo 5: Estudo dos vários processos do projecto de Quality do ODI-DQ, para realização da
detecção de clientes aproximadamente duplicados;
– Passo 6: Estudo da ferramenta LingPipe para detecção de anomalias nos dados que as
ferramentas da Oracle não tinham funcionalidades para o fazer;
– Passo 7: Reflexão sobre os vários resultados apresentados pelas diferentes ferramentas
para a detecção de anomalias nos dados;
– Passo 8: Aperfeiçoamento da metodologia DP com base na reflexão do passo anterior.
1.8
Organização da Dissertação
Esta dissertação encontra-se organizada da seguinte forma. No Capítulo 2, é apresentado o estado de
arte das ferramentas comerciais e de distribuição livre de data profiling e um estudo sobre os algoritmos
de detecção de registos duplicados. No Capítulo 3, descreve-se a metodologia de data profiling que
foi baseada na de Jack E. Olsen [2], sendo indicado o que de novo foi introduzido na mesma. No
Capítulo 4, é detalhado o estudo do caso, onde a BD_OT é descrita. É explicado como foi aplicada a
metodologia encontrada ao estudo de caso e os resultados da análise feita aos dados do caso prático.
É ainda apresentada uma comparação das duas ferramentas estudadas, quais as suas vantagens e
desvantagens. Por fim, o Capítulo 5 apresenta as principais conclusões desta dissertação salientando
11
o que de mais importante foi executado e os possíveis desenvolvimentos futuros considerados mais
relevantes.
12
2 Trabalho relacionado
Neste capítulo são apresentadas as ferramentas de data profiling, comerciais e de distribuição livre, que
existem no mercado. São também apresentadas as várias actividades que as ferramentas dispõem.
Na segunda parte deste capítulo é descrito um estudo sobre alguns dos algoritmos de detecção de
duplicados desenvolvidos por vários autores interessados neste tema.
2.1
Ferramentas de Data Profiling
Antigamente, os poucos métodos que existiam para detectar o estado dos dados eram manuais. Muitas
vezes construía-se um conjunto de interrogações a executar sobre a base de dados, com base em
eventuais erros que os utilizadores pensavam que os dados poderiam ter [6]. Com estes métodos,
quanto maior era o número de dados a verificar, mais tempo e menos precisão existia na detecção de
anomalias dos dados a usar. Para resolver este processo lento e manual foram criadas ferramentas
com algoritmos específicos de detecção de Data Profiling. Estes algoritmos detectam um conjunto de
características de dados, como por exemplo, o domínio, a estrutura, a dependência entre os dados,
etc. Actualmente, embora exista um conjunto de ferramentas que permite verificar os problemas de
qualidade de dados, na maior parte das vezes estes ainda são detectados através das reclamações
do cliente (62%) e só 17% são detectados através da utilização de ferramentas criadas para o efeito,
como se pode ver na Figura 2.1 [1]. As ferramentas de DP são bastante úteis do ponto de vista que
Figura 2.1: Métodos para detecção de qualidade de dados [1].
permitem evitar um conjunto de atrasos num projecto, pois são detectados erros nos dados de uma
forma automática e rápida, antes de se começar a desenvolver uma aplicação. Obrigam os utilizadores
a perceber aquilo que fazem e o que o negócio faz. Permitem uma rapidez na análise dos dados
quando comparadas com os métodos antigos. Por exemplo: Um utilizador de uma empresa de hightech afirmou que, uma ferramenta de DP permitiu analisar 100 % dos dados, sendo estes 60 milhões
de registos, 22 tabelas e 500 colunas em poucos dias, comparado com o mesmo processo mas feito
manualmente que levou 3 a 4 semanas e foi feito a metade dos registos referidos em cima [22]. Existem
porém alguns factores que precisam de ser melhorados, como por exemplo, o tempo de execução do
processo de DP ainda é muito lento, para milhões de registos estas ferramentas ainda levam muito
tempo a devolver o resultado final da análise aos dados. E técnicas que precisam de ser criadas,
pois ainda existem valores que são inválidos, e não são detectados pois as ferramentas ainda não têm
13
técnicas para os detectar. Por exemplo: Um empregado tem 31 anos no mundo real, mas na realidade
o sistema diz que o mesmo tem 30.
2.1.1
Actividades das ferramentas de DP
As ferramentas de DP têm um conjunto de actividades que permitem auxiliar a metodologia descrita
por Jack Olson [2]. As actividades estão agrupadas segundo as várias etapas da metodologia de Jack
Olson [2].
2.1.1.1
Propriedades de uma coluna
As actividades para testar as propriedades de uma coluna são:
• Nome BD: Refere o nome da BD a analisar;
• Nome da Tabela: Refere o nome da tabela a analisar;
• Nome do campo: Refere o nome do campo a analisar;
• No Ocupa Tabela: Indica a posição do campo na tabela;
• Domínio: Refere qual a gama de valores para um determinado campo;
• Espaços Embebido: Verifica se num campo há espaços;
• Caracteres especiais: Verifica se existem caracteres especiais num determinado campo;
• Max/Min Valores: Devolver o valor mais baixo/alto que encontrar na coluna;
• Verifica Permite Nulls: Verifica se aquela coluna permite valores null ou não;
• Radix: Se esta opção estiver a 2, significa que o tamanho do campo vem em Bytes, se estiver a
10, vem em números decimais;
• No Registos: Diz quantos registos tem a tabela;
• No Brancos: Refere o número de células em branco numa coluna;
• No Zeros: Refere o número de zeros numa coluna;
• No Nulls: Refere o número de nulls numa coluna;
• No Negativos: Refere a quantidade de números negativos que existe na coluna;
• No Repetidos: Indica o número de registos repetidos numa coluna;
• No Tipos de Repetidos: Indica o no que existe de registos repetidos diferentes;
• No Únicos: Indica o no de registos únicos numa coluna;
• No Padrões: Indica o no de padrões diferentes que existe numa coluna;
• No Max Bytes: Indica o no máximo de bytes que um campo ocupa;
• No Max/Min caracteres Preenchidos: Indica o no Max/Min de caracteres preenchidos numa coluna;
• No Max de Caracteres permitido: É o número máximo de caracteres que o utilizador pode usar
nessa coluna;
• Tipo: Indica o tipo de dados de uma coluna;
• Tipo Actual: Indica o tipo que o DP verificou nos dados da aplicação e não o que está definido
nos esquemas ou metadados;
• Outliers: No de elementos que saem fora do padrão da coluna;
14
• Padrões: Indica os padrões de registo de uma coluna.
2.1.1.2
Estrutura de dados
As actividades para testar a estrutura dos dados são:
• Colunas redundantes: Indica que colunas têm dados redundantes;
• Chave Estrangeira: Detecta as chaves estrangeiras nas tabelas;
• Dependência Funcional: Indica que campos fazem parte das dependências funcionais quando
existem;
• Chave Primária: Detecta as chaves primárias nas tabelas;
• Coluna Sinónimas: Indica pares de tabelas sinónimas.
2.1.1.3
Funções de estatística
As actividades para testar os vários dados através de funções estatísticas são:
• Média Caracteres Preenchidos: Indica uma média de quantos caracteres são preenchidos num
registo;
• Média: Indica a média de valores de uma coluna;
• Mediana: Indica a mediana de valores de uma coluna;
• Moda: Indica a moda dos valores de uma coluna;
• Desvio Padrão: Indica o desvio de padrão dos valores de uma coluna;
• Somatório de todos os valores: Soma todos os valores de uma coluna.
• Percentagem de Null: Devolve a percentagem de null que há numa coluna;
• Percentagem de Únicos: Devolve a percentagem de valores únicos que há numa coluna.
2.1.1.4
Regras de negócio
As actividades para testar os dados segundo as várias regras de negócio são:
• Linguagem própria: Verifica se a ferramenta tem linguagem própria para as regras;
• Linguagem externa: Verifica se a ferramenta permite outra linguagem, para se fazer a regra;
• Expressões regulares: Verifica se a ferramenta percebe regras com expressões regulares.
As ferramentas dispõem ainda de outros utilitários:
• Autenticação sobre os dados;
• Movimentação nos dados através do Drill Down;
• Comentários junto dos dados;
• Gráficos para melhorar a visão da situação dos dados;
• Se permite importar e exportar dados.
15
2.1.2
Classificação das ferramentas de DP
Existem um conjunto de ferramentas de data profiling, tanto comerciais como de distribuição livre. De
seguida são apresentadas duas Tabelas 2.1 e 2.2, que pretendem identificar um conjunto de ferramentas e actividades que se podem utilizar para a realização da metodologia do processo de DP. Não são
exaustivas em termos de ferramentas, mas são-no em termos de actividades das mesmas. Para mais
informação sobre as ferramentas de DP consultar os seguintes locais: [23], [24], [25], [26], [27], [28],
[29], [30], [31], [32], [33], [34] e [35].
2.2
Algoritmos de detecção de duplicados
Actualmente as bases de dados criam o dia-a-dia das empresas. Numa era em que a economia se
baseia em Tecnologias de Informação cresce o número de sistemas que dependem da precisão das
mesmas para a execução de determinadas tarefas. Desta forma, a qualidade dos dados guardados é
o elemento fundamental, ainda mais quando se trata de aplicações de suporte ao negócio [36]. Num
ambiente com dados perfeitos (sem erros), um sistema não passa de uma simples vista resultante do
join de tabelas pela sua chave. Infelizmente esta situação é rara, pois são constantes as duplicações
de registos devido à inexistência de um identificador único global. Esta falta de consistência afecta
a qualidade dos dados, ou seja, torna-se um problema para a qualidade. Um caso concreto onde
este problema ocorre regularmente, é quando as empresas para acções de marketing, ou análises
de negócio cruzam várias fontes de dados (várias bases de dados) [4]. Tipicamente esta operação
é realizada de forma inconsistente e incorrecta. Esta situação representa o ponto de partida para a
detecção de duplicados que será descrita nesta secção.
2.2.1
O que são registos duplicados?
O conceito de detecção de duplicados foi iniciado por Newcombe et all [37], e mais tarde formalizado
por Fellegi e Sunter [38]. Inicialmente conhecido por record linkage ou record matching e na actualidade
por duplicate record detection [36] [38]. Este problema da detecção de registos duplicados baseia-se
na ideia em que existem duas ou mais representações para a mesma entidade numa base de dados, e
que não partilham a mesma chave. Usando um caso prático, dadas duas listas de registos a detecção
de duplicados consiste em determinar todos os pares que são aproximados, onde a proximidade é dada
pelo domínio específico dos atributos que definem o registo. Actualmente este problema está inserido
no contexto do Data Cleaning, que normalmente antecede os processos de análise de dados e os
processos de mining [39], [40]. De seguida vão ser apresentados alguns algoritmos para a detecção de
registos duplicados e aproximadamente duplicados. Vão ser referidas também algumas técnicas para
aperfeiçoar a eficiência e escalabilidade dos algoritmos.
16
17
MySql, FP -> Formato Próprio.
Tabela 2.1: Classificação das várias ferramentas de DP (1). Legenda: N -> Não, S -> Sim, - -> Indefinido, O -> Oracle, SQLS -> SQL Server, A -> Acess, FF -> Flat Files, MS ->
Tabela 2.2: Classificação das várias ferramentas de DP (2). Legenda: N -> Não, S -> Sim, - -> Indefinido, O -> Oracle, SQLS -> SQL Server, A -> Acess, FF -> Flat Files, MS ->
MySql, FP -> Formato Próprio.
18
2.2.2
Algoritmos de detecção simples
Na detecção de duplicados é essencial definir qual o tipo de dados em análise, dada a dependência
dos algoritmos existentes. Como principais tem-se os seguintes tipos: dados simplesmente estruturados que correspondem a tabelas relacionais, dados complexos que representam grupos de tabelas
relacionais e dados semi-estruturados, como por exemplo documentos XML [14]. Este problema da detecção pode ser abordado usando a heterogeneidade dos dados, ou seja, os dados devem ser distintos.
A heterogeneidade pode ser do tipo Lexical e Estrutural [36]. Na primeira o foco está em identificar registos na mesma ou em diferentes bases de dados que se referem à mesma entidade no mundo real,
mesmo que os registos não sejam idênticos. Na heterogeneidade Estrutural o foco está na normalização dos dados, ou seja, garantir que os dados seguem o mesmo padrão que está definido. Esta
última é considerada um aspecto auxiliar do algoritmo, na medida em que é utilizada apenas na fase
de preparação dos dados. Os processos de detecção de duplicados normalmente são compostos por
três passos [4] [41]:
1. A preparação dos dados tem como objectivo uniformizar os mesmos na base de dados e resolver
uma parte da heterogeneidade estrutural dos dados. Esta fase, por sua vez está sub-dividida em
parsing, data transformation e standardization [36]. Aos processos que utilizam esta preparação
são igualmente designados de ETL (Extract, Transform and Load).
• Parsing - Localiza, identifica e isola os dados. Este mecanismo facilita a correcção, standardization e comparação dos dados, pois permite a comparação de componentes individuais,
em vez de longas e complexas strings. Como exemplo o parsing apropriado para os nomes
e moradas em informação consistente, é essencial para a posterior detecção de duplicados. Existe uma área activa em investigação nesta área, podendo ser consultados alguns
métodos de parsing nestes locais [42], [43], [44], [45] e [46];
• Data Transformation - Converte os dados de forma a respeitarem os tipos de dados definidos
no domínio, sendo manipulados um de cada vez independente dos valores inseridos. Como
exemplos tem-se: renomear campos, descodificar campos (para se poder comparar), entre
outros;
• Standardization - Converte os dados numa representação uniforme. Sem esta fase a detecção de duplicados avalia os registos como não duplicados, pois não estão uniformizados/normalizados. Como exemplo têm-se os formatos das datas.
Estes passos constituem uma melhoria na qualidade dos dados, contribuindo para o sucesso do
passo seguinte (comparação), tornando os dados comparáveis e utilizáveis. Não esquecer que
mesmo assim a detecção de duplicados continua a ser um problema sem solução trivial.
2. A escolha da função de comparação a ser usada na decisão dos registos duplicados. Primeiro
definem-se métricas ao nível dos campos para determinar a proximidade de valores diferentes e
de seguida definem-se métricas ao nível dos registos também para determinar a proximidade mas
agora de registos;
3. Verificação final das medidas de data quality, de forma a determinar se o resultado foi satisfatório
19
e se é necessário nova iteração com uma nova função de comparação.
As funções de comparação referidas podem ser caracterizadas em três categorias principais. São elas
[14]:
• Probabilística, baseada em métodos estatísticos e teorias de probabilidade, indo desde Bayesian
networks até data mining. São eficientes e com isso perdem no tempo de resposta;
• Empíricas, tais como ordenação, comparação com a vizinhança, entre outras. São funções rápidas e escaláveis para bases de dados com registos na ordem dos milhões;
• Knowledge-based, onde são usadas estratégias extraídas de uma fonte de conhecimento, para
melhorar a eficiência do processo.
De seguida e tendo em vista a detecção em termos de campos individuais e múltiplos serão apresentadas diversas funções para a comparação.
2.2.2.1
Detecção de duplicados em campos individuais
Um dos erros mais comuns na geração de duplicados está na diversidade de formas de escrita, o que
obriga à utilização de técnicas de comparação de strings, não esquecendo que os dados podem ser
numéricos ou alfabéticos. De seguida são apresentados alguns algoritmos agrupados pela forma de
comparação. São eles:
• Métricas de aproximação baseadas em caracteres: utilizadas na detecção de erros de escrita.
Em seguida são apresentados alguns exemplos:
– Edit Distance - consiste em determinar o número mínimo de operações necessárias para
transformar uma palavra A numa palavra B. As operações que existem são: inserção de um
caracter, remoção de um caracter e substituição de um caracter por outro diferente;
– Affine Gap Distance - como a métrica anterior não suporta a detecção entre palavras abreviadas ou cortadas, surgiu esta nova métrica que tem duas novas operações: open gap e
extend gap. Neste caso a operação extend gap tem normalmente um custo menor que a
primeira, sendo o factor custo importante para a minimização;
– Smith-Waterman distance - consiste num aperfeiçoamento das duas anteriores, pois considera o custo das operações no início ou fim da string menor do que no meio, ou seja,
pretende-se assim fazer a detecção de sub-strings;
– Q-Grams - os q-grams significam conjuntos de caracteres e esta métrica tem por base que
duas strings são aproximadamente duplicadas quando partilham vários q-grams. É utilizada
uma janela de tamanho fixo para percorrer e comparar ambas as strings.
• Métricas de aproximação baseadas em tokens: quando duas strings duplicadas apenas diferem
na ordem das palavras as métricas baseadas em caracteres não resultam. É este o fundamento
deste tipo de métricas. Como exemplo tem-se:
– Atomic strings - Uma string atómica é a designação para um conjunto de caracteres alfanuméricos delimitados por caracteres de pontuação. Esta métrica consiste em detectar
strings atómicas duplicadas, ou seja, se são iguais ou se uma é prefixo da outra.
20
• Métricas de aproximação fonética: existem strings aproximadamente duplicadas pela sua fonética,
mesmo que sejam escritas de forma diferente. Como exemplo tem-se:
– Soundex - é baseado na correspondência entre caracteres e o seu similar grupo fonético.
– Metaphone and Double Metaphone - consiste em codificar as consoantes que descrevam a
maior parte dos sons usados nas palavras da língua em estudo. No Double são possíveis
múltiplas codificações, ou seja diversas formas de pronunciar.
• Métricas de aproximação numérica: normalmente os números são tratados como as strings ou
então usam-se queries simples. A investigação nesta sub-área vai no sentido de explorar a distribuição e o tipo de dados numéricos.
Qualquer uma das métricas apresentadas, demonstra ser robusta numa dada situação, que não pode
ser generalizada, ou seja, existem situações em que o resultado é mais fraco.
2.2.2.2
Detecção de duplicados em registos
Na secção anterior estão descritos métodos que podem ser usados na detecção de campos individuais.
Na realidade um registo é um conjunto de campos numa tabela da base de dados. De seguida são
apresentados alguns métodos para detecção de registos com múltiplos campos. São eles:
• Modelo de detecção probabilística - neste modelo é necessário considerar dois conjuntos M
(Match) e U (Non-match) com funções de distribuição conhecidas. Tendo isto como base a detecção de duplicados pode ser formulada das seguintes formas [47] [48]:
– Regra de decisão de Beyes para erro mínimo: consiste em utilizar o Teorema de Beyes
para inferir se os registos são ou não duplicados. Sabe-se qual a P(M|x) em que x é o par
das strings a comparar e através do teorema chegamos a P(x|M). Este método é bastante
limitado pois é necessário saber as funções de distribuição e probabilidades que na via real
é muito raro ser conhecido;
– Decisão com uma região de rejeição: adicionalmente aos conjuntos U e M é adicionado um
novo como nome Reject. O objectivo é colocar todos os pares de strings que não se consegue inferir directamente se são duplicados neste grupo para posterior validação manual.
• Técnicas baseadas em distância: consiste em definir uma métrica de distância para os registos,
bem como o limite para a aproximação dos duplicados. Com base nisto é calculada a distância
entre os registos e inferido se são ou não aproximadamente duplicados.
• Aproximação baseada em regras: é considerado um caso especial da técnica anterior, onde
a distância toma os valores 0 ou 1 consoante respeite a regra. As regras são definidas pelos
analistas com base nos atributos que servem como chave para cada registo.
O objectivo da investigação nesta área é melhorar a eficiência das técnicas que se baseiam em inferência probabilística. A maior dúvida permanece na escolha do método de detecção. Até hoje estão
sempre dependentes dos dados e das suas incorrecções, e assim não existe uma técnica que se
destaque.
21
2.2.3
Algoritmos de detecção complexos
Como ponto de partida para os algoritmos mais complexos tem-se a implementação de joins onde é
executado o produto cartesiano, (operação de tempo de processamento quadrático) e seleccionados
os tuplos relevantes. A optimização óbvia para o join tem variantes e são conhecidas como: sortmerge e hash partitioning [49]. As optimizações mais comuns para os algoritmos de detecção de
duplicados estão na velocidade. Como o foco dos algoritmos está na qualidade das técnicas este
factor é menosprezado. É nesta fase de melhorias que se procura colmatar esta falha e aumentar
a velocidade na detecção dos duplicados. De seguida são apresentadas algumas operações para
melhorar a performance dos algoritmos, reduzindo o número de comparações:
• Blocking: consiste em gerar um hash para cada registo da tabela. Cada um destes valores permite
agrupar os registos em grupos reduzindo assim as comparações a registos intra-grupos. Quanto
a registos aproximadamente duplicados esta técnica não se aplica pois não existem garantias que
o hash de dois registos semelhantes seja o mesmo;
• Aproximação vizinhança ordenada: consiste em criar uma chave para cada registo usando alguns
campos ou partes de campos relevantes, e ordenar os dados usando a mesma chave. Por fim é
utilizada uma janela de tamanho fixa para percorrer os registos e detectar duplicados nos adjacentes. Existe outra variante deste algoritmo, usada no processo merge-purge, que termina com
a partição dos dados em clusters bem definidos, ou seja, são agrupados os dados aproximadamente duplicados, em oposição à janela [49] [50].
2.2.4
Ferramentas
Ao longo dos últimos anos, têm surgido novas aplicações de data cleaning, bem como soluções para dar
suporte à detecção de duplicados. Estas ferramentas permitem não só detecção de registos duplicados,
mas também avaliar a qualidade inicial dos dados e dos registos duplicados. De seguida são analisadas
algumas ferramentas [36]:
• Febrl System (Freely Extensible Biomedical Record Linkage): Ferramenta de open-source para
o data cleaning, que usa as seguintes métricas para a detecção de duplicados: edit distance e
q-gram distance. Para a detecção de nomes aproximadamente duplicados é usada a codificação
fonética (Soundex e Double Metaphone);
• TAILOR: Ferramenta para detecção de duplicados, que permite aplicar diferentes métodos de
detecção nos dados. Esta flexibilidade na aplicação de diversos métodos, é importante nos casos
em que o utilizador não sabe qual o modelo de detecção a usar, contribuindo desta forma para
uns resultados melhores. Para além da flexibilidade disponível na ferramenta, o utilizador também
dispões de estatísticas que ajuda a entender melhor a qualidade dos registos analisados.
• BigMatch: Programa para detecção de registos duplicados usado pela U.S. Census Bureau. O
método de detecção usado é o blocking, para identificar potenciais duplicados entre registos de
duas relações e que tem um bom comportamento com grandes quantidades de informação.
22
2.2.5
Conclusão
A crescente utilização dos sistemas de base de dados é a semente para o fortalecimento da área de
qualidade de dados, profiling e cleaning. A quantidade de erros cresce diariamente e a necessidade
da sua detecção e correcção é eminente. Foram referidos apenas alguns métodos e algoritmos para
detecção de duplicados, pois trata-se de uma área em evolução. Como trabalho futuro existe a necessidade de um repositório com informação sobre os algoritmos para ajudar as equipas que desenvolvem
as ferramentas de detecção de duplicados. Esta explicitação das características dos algoritmos, permitem a avaliação e comparação entre os métodos existentes e melhorar o processo de implementação
das ferramentas [36]. Para além disto, as constantes alterações do negócio, obrigam os algoritmos a
adaptarem-se a novas situações de dados duplicados. Nesta tema o trabalho futuro está em permitir
ao utilizador estimar uma proporção de erros a detectar, para posterior comparação com os resultados.
Desta forma a revisão e adaptação dos algoritmos aplicados é mais fácil. [36]. Por último, o problema dos registos duplicados tem também origem na extracção de dados, ou seja, as fontes de dados
usadas na sua maioria não estão estruturadas, nem precisas. A necessidade de extracção de dados
torna-se assim noutro factor importante para o crescimento das áreas de qualidade de dados, profiling
e cleaning.
23
3 Metodologia de Data Profiling
Neste capítulo apresenta-se a metodologia de data profiling que foi seguida no projecto de auditoria de
qualidade de dados da BD_OT. Esta metodologia baseou-se na proposta por Jack Olson [2], que está
enunciada à esquerda na Figura 3.1. A metodologia apresentada (e representada à direita da Figura
3.1) foi complementada com funcionalidades que a anterior não disponibilizava. O objectivo é ter uma
metodologia que possa ser utilizada em qualquer BD com a finalidade de reportar de uma forma rigorosa
e sistemática o maior número possível de erros. Esta metodologia utiliza uma abordagem bottom-up,
começando num elemento mais elementar (coluna) de uma BD progredindo até aos elementos mais
complexos da mesma (relações entre várias tabelas). Para que se consiga analisar uma BD na sua
totalidade, a metodologia de data profiling é constituída por quatro etapas principais, que podem ser
visualizadas à direita da Figura 3.1. Não é obrigatório seguir esta ordem para se realizar uma auditoria
de qualidade de dados a uma BD relacional. No entanto é aconselhado seguir esta sequência, pois
permite realizar a inspecção à BD de uma forma ordenada, evoluindo de um nível mais atómico para
um mais geral. Ao fazer-se a comparação entre as duas metodologias referidas na Figura 3.1 tem-se
que:
• A análise da coluna na metodologia do autor Jack Olson [2], não está dividida em três sub-passos.
O autor refere um passo para a análise das propriedades da coluna, e outro distinto da análise
da coluna, para reportar os resultados das funções de estatística. O terceiro sub-passo, que
corresponde à análise da coluna através de dicionários, nem existe na metodologia do autor. Esta
dissertação defende a ideia de que as funções de estatística só fazem sentido existirem perante
a análise de uma coluna e não num sentido geral como definido pelo autor.
• A análise dos dados através de regras de negócio, na metodologia do autor Jack Olson [2], está
representada em duas, ou seja, em regras de negócio simples e complexas, o que difere do que
aqui foi apresentado pois esta dissertação defende a ideia de que não importa a distinção entre
regras de negócio simples ou complexas, pois ambas têm o mesmo significado;
• A análise de registos aproximadamente duplicados não existe na metodologia do autor Jack Olson
[2].
Figura 3.1: À esquerda a metodologia de DP segundo Jack Olson [2] e à direita a metodologia de DP segundo
esta dissertação.
24
3.1
Análise de cada coluna de uma tabela
A análise de cada coluna das tabelas de uma base de dados tem três sub-passos:
• Análise das propriedades de uma coluna: o analista tem de verificar se os valores de cada coluna
estão de acordo com um conjunto de propriedades da coluna. Por exemplo, analisar se um
determinado campo deve ser ou não do tipo char ou do tipo string. Neste exemplo a propriedade
que está a ser analisada é o tipo de coluna;
• Análise de cada coluna através de funções de estatística: o analista verifica o significado dos
resultados estatísticos que dizem respeito à coluna em análise. Por exemplo na coluna Idade
realizar a média ou o desvio padrão dos valores da coluna;
• Análise da coluna através de dicionários: o analista valida os valores de uma coluna através da
sua comparação com um dicionário de palavras de um determinado domínio. Por exemplo, o
código-postal ’2345-678’ encontra-se definido na lista de códigos-postais dos CTT.
3.1.1
Análise da coluna através das suas propriedades
Neste sub-passo [2] da metodologia, o analista tem de detectar anomalias nas várias colunas das várias
tabelas de uma BD, através da verificação das propriedades de cada coluna. Uma propriedade é uma
característica que define a coluna de uma tabela. O conjunto de propriedades que o analista deve de
verificar para cada coluna da BD é o seguinte:
• Nome da coluna - Verificar se o nome é adequado segundo os valores que encontra na coluna;
• Domínio - Analisar, ao longo da coluna que valores sem repetição devem fazer parte da mesma.
Por exemplo, o domínio do campo Género deve de ser composto pelos dois valores ’F’ e ’M’;
• Tipo físico de dados - Analisar, ao longo da coluna, que tipo de dados ocorrem com mais frequência. Por exemplo, o tipo do campo Género deve de ser char, pois os seus valores são constituídos
por uma letra;
• Null - Verificar se a maior parte dos valores do campo são null na coluna;
• Unique - Verificar se o campo contém só valores únicos ao longo da tabela;
• Comprimento - Verificar o comprimento que aparece mais vezes ao longo da coluna para os vários
valores da mesma;
• Precisão - Para os campos numéricos, verificar o número de dígitos que existe à esquerda da
vírgula;
• Escala - Para os campos numéricos, verificar o número de dígitos que existem à direita da vírgula;
• Padrão - Verificar quais os vários formatos que os valores assumem ao longo do campo que se
está a analisar. Por exemplo o número ’3523525’ tem o formato ’NNNNNNN’, onde N representa
um número.
O processo que deve de ser seguido para analisar as propriedades de cada coluna encontra-se esquematizado 3.2. É constituído pelas seguintes etapas:
1. O analista deve consultar os vários documentos e metadados que existem sobre a definição propriedades para cada coluna a analisar. Desta forma, o analista consegue saber o que foi ou não
25
Figura 3.2: Processo de análise da coluna segundo as propriedades da mesma.
definido para cada coluna. Por exemplo, no documento de especificações da BD uma coluna pode
estar definida para não permitir que nenhum dos seus valores seja null;
2. O analista deve inferir que propriedades existem para a coluna através da análise dos vários que a
mesma pode tomar. Por exemplo, através da contagem dos valores a null numa coluna, o analista
sabe se a coluna tem ou não muitos valores a null;
3. Neste ponto, o analisa já conhece que propriedades existem no ponto 1 e no ponto 2 para uma
coluna. Se estes dois grupos de propriedades não forem iguais, o analista tem de decidir quais
são as propriedades correctas para a coluna;
4. Todos os valores que não respeitem as propriedades definidas no ponto anterior são considerados
anomalias.
Com este sub-passo os analistas podem chegar à conclusão que as propriedades que estavam definidas
para cada coluna não estão correctas e poderão ter que modificar os metadados ou documentos de
forma a demonstrarem quais são as propriedades pretendidas para cada coluna.
26
3.1.2
Análise da coluna através de funções estatísticas
O segundo sub-passo analisa os dados de uma coluna através da aplicação de funções estatísticas
[2] sobre a mesma. Algumas das funções estatísticas que se considerou são: Cálculo de percentagens, contagens, máximos, mínimos, médias, medianas, modas, desvios de padrão, frequências, distribuições, etc. Um exemplo da análise de uma coluna segundo uma função de estatística é: A média de
valores do campo Idade de um passe de criança é 22 anos. Este valor pode significar duas coisas: (i)as
idades colocadas para a maioria das crianças estão erradas; (ii) foram atribuídos passes de criança a
pessoas que já não o são, porque um passe é dito de criança se for atribuído a uma pessoa até aos 12
anos de idade. Por vezes, as funções de estatística servem de auxílio para verificar certas propriedades
das colunas. Desta forma, o analista pode conjugar um conjunto de propriedades da coluna e funções
de estatística e retirar conclusões sobre possíveis anomalias que existem nos dados de uma coluna.
Alguns exemplos de conjugações que se podem efectuar são:
• A contagem ou percentagem de valores únicos/null que existem numa coluna: o analista verifica
se o campo é candidato a ser definido como Unique/not null, caso ainda não o seja;
• A contagem ou percentagem de valores alfanuméricos/inteiros/decimais: se esta contagem for
marioritária, o analista deve consultar se o tipo definido para esta coluna é o mesmo que foi
encontrado em maior número. Senão o for existem inconsistências entre o que foi definido e o
que realmente existe;
• Tipo físico de dados/Precisão/Escala/Comprimento/Padrão inferido: o analista deve contar todos os tipos/precisões/escalas/comprimentos/padrões dos diferentes valores que existem numa
tabela e verificar a frequência ou a percentagem com que cada um aparece. Desta forma fica a
saber qual é o tipo/precisão/escala/comprimento/padrão marioritário dos dados para uma determinada coluna. Com este resultado, o analista avalia se a maioria dos valores encontrados para
tipo/precisão/escala/comprimento/padrão são os mesmos do que ele estava a espera. Senão
forem sao anomalias encontradas;
• Valor mínimo/máximo: o analista ao verificar estes valores para cada coluna pode afirmar que
existem ou não inconsistências de domínio da coluna;
• Contagem de padrões: ao olhar para o número de padrões devolvidos de uma coluna, o analista
verifica ou não que o formato dos dados da mesma está mal definido, porque os valores de uma
coluna devem de respeitar sempre o formato idealizado como correcto no mundo real;
• Domínio dominante: através da contagem ou percentagem dos valores que mais aparecem numa
coluna, o analista verifica se os mesmos são correctos ou não;
• Contagem ou percentagem de duplicados, através deste resultado o analista sabe quantos valores
a coluna tem exactamente iguais. E decide se esta situação devia de ocorrer ou não.
3.1.3
Análise da coluna através de dicionários de palavras
A última parte da análise da coluna é realizada através da comparação dos valores de uma coluna com
um ou mais dicionários de palavras do domínio da mesma. Este sub-passo é importante se o analista
27
necessita de detectar se todos os valores de uma coluna são válidos e existem no mundo real. Com
o dicionário apropriado, o analista tem de comparar cada valor da coluna com os vários valores do
dicionário a testar. Se o valor não existe no dicionário deve de ser reportado como erro. Desta forma o
analista pode detectar numa coluna:
• Falta de dados correctos. Por exemplo o número de bilhete de identidade ’99999999’, poderá ser
detectado se o analista conseguir uma lista de números de bilhete de identidade nacionais;
• Palavras mal escritas. Por exemplo, nomes de pessoas de nacionalidade portuguesa, mal escritos
podem ser detectados com uma lista de nomes de Portugal;
• Valores embebidos. Se num valor de uma coluna, uma das palavras (token)que o constituem, não
faz parte do mesmo o analista terá de segmentar cada valor e através de um dicionário verificar se
cada palavra (token) existe ou não, no dicionário. Se existirem valores da coluna que têm essas
palavras, os mesmos têm de ser reportados como inválidos. Por exemplo, o valor ’Sr. Manuel
Jacinto’ o ’Sr.’ está a mais;
• Dados no campo errado, é o mesmo que o ponto anterior, só que neste caso o dicionário de
palavras a testar é utilizado nas colunas adjacentes à coluna que deveria conter os valores. Como
exemplo tem-se os campos da morada, onde se no campo Rua aparecer um número da porta,
este valor da rua esta mal.
3.2
Análise da estrutura da base de dados
A segunda etapa da metodologia de DP analisa a estrutura de uma BD relacional. Tem como objectivo
detectar todas as relações que existem entre as colunas de uma tabela e as relações que existem entre
as tabelas de uma BD [2]. Esta etapa relata anomalias ao nível da estrutura da BD e não ao nível dos
valores da mesma. O processo de análise da estrutura dos dados é semelhante ao da análise das propriedades de uma coluna, pois o analista em primeiro lugar deve verificar como está definida a estrutura
da base de dados, nos vários documentos de especificação da BD, nos metadados e nos modelos de
dados da BD. Depois de saber a estrutura da BD tal como está documentada, o analista em segundo
lugar necessita inferir a estrutura da BD através da análise dos dados. Em terceiro lugar, o analista
deve de comparar as duas estruturas da BD, a que está documentada e a que foi inferida através dos
dados da BD. Se as estruturas anteriormente descritas acima forem iguais, não existem anomalias na
estrutura da BD. Caso contrário, o analista tem de decidir qual das duas (ou uma combinação das duas)
estruturas é correcta para a BD. Em último lugar e só nos casos em que as duas estruturas não são
iguais, o analista tem de verificar que inconsistências a estrutura inferida tem em comparação com a
estrutura decidida como correcta pelo analista anteriormente. A estrutura inferida, através dos dados,
pode não ter inconsistências se foi aquela pela qual o analista optou. Neste caso a estrutura que está
documentada e representada nos metadados tem de ser alterada.
O analista quando precisa de analisar a estrutura da BD, dispõe de um conjunto de técnicas que permitem analisar a mesma. São elas:
• Verificação e descoberta de dependências funcionais entre as colunas de uma mesma tabela.
28
Diz-se que existe uma dependência funcional se existe um ou mais campos que determinam os
restantes campos da tabela que se está a analisar [51] [52]. Com esta técnica é possível:
– Confirmar ou detectar a chave primária e chaves candidatas de uma tabela;
– Detectar que colunas não dependem (ou não dependem só) da chave primária. Estas colunas são candidatas a formar novas tabelas;
– Afirmar que a tabela não é normalizada, se existem colunas que não dependem da chave
primária. Já o contrário não é verdade.
• Detecção ou confirmação de chaves estrangeiras, que servem para ligar várias tabelas de modo a
formar objectos de negócio. Um objecto de negócio é uma entidade do mundo real, representada
numa BD, como por exemplo: a encomenda de um cliente é um objecto de negócio. É constituída
por várias características, tais como: a informação do cliente, a data de entrega e os produtos
que a constituem, cada característica é representada por uma ou várias colunas;
• Detecção de colunas sinónimas. Duas colunas são sinónimas se ambas se referem ao mesmo
atributo de negócio. Por exemplo, uma coluna designada de Departamento_numero, na tabela dos
departamentos, outra coluna designada de dep_no na tabela dos colaboradores e por fim outra
coluna chamada de Dept_num na tabela dos projectos. Podem-se encontrar colunas sinónimas
nas seguintes situações:
– A chave primária e a respectiva chave estrangeira têm de ser chaves sinónimas;
– Através das chaves estrangeiras podem-se descobrir colunas sinónimas (redundantes) na
tabela que suporta a chave estrangeira. Por exemplo: na tabela Clientes existe o campo
Telefone e o campo da chave primária Nr_Cliente. Na tabela Requisições existe uma chave
estrangeira para a tabela Clientes e outro campo designado como Telefone. Neste caso
o campo telefone da tabela Requisições é uma coluna sinónima (redundante) da mesma
coluna que existe na tabela Clientes;
– Colunas de tabelas diferentes que não têm relação nenhuma entre elas, mas que têm campos iguais. Por exemplo, a tabela Clientes tem o campo cidade e a tabela Empregados tem
também um campo cidade;
– Através da comparação de colunas que existem em diferentes BD’s e que se está a tentar
consolidar numa BD só estas duas últimas. Por exemplo: Na integração de duas BD’s de
um banco, em que numa existe uma tabela com os campos: Numero_conta, DataAberturaConta e Quantia. E na outra Bd existe uma tabela com os campos: ID_Conta, DataConta e
Montante.
3.3
Análise dos dados através de regras de negócio
O terceiro passo da metodologia permite avaliar os dados segundo um conjunto de condições que estão
definidas no negócio. A detecção de anomalias é efectuada através de regras de negócio [2] e o seu
objectivo é testar se os dados obedecem a determinadas condições. Se os dados não obedecerem a
estas condições, no geral estão errados. Existem casos em que os dados nem sempre estão errados
29
se não obedecerem a uma regra de negócio.
Um exemplo de uma regra de negócio pode ser:
If Idade < 12 then TipoPasse='Criança'
Para analisar os dados através de regras de negócio, o analista deve de seguir o seguinte conjunto de
passos:
1. Conhecer qual a função de cada elemento na BD, ou seja, precisa de perceber porque é que
o negócio precisa de n tabelas para representar os seus objectos e saber também o porquê da
existência dos vários atributos destes objectos de negócio;
2. Depois de compreender os vários objectos e respectivos atributos do negócio, o analista precisa
de procurar regras ou condições que o negócio utiliza para que os dados suportem o mesmo. O
processo de levantamento de regras de negócio pode ser feito das seguintes formas:
• Nos documentos de requisitos da aplicação que vai utilizar os dados da BD;
• No código fonte da aplicação;
• Nos procedimentos SQL que interagem com a BD;
• Nas entrevistas com pessoas seniores que já lidam algum tempo com a BD que se vai analisar.
3. Uma vez reunido o conjunto de regras, estas precisam de ser testadas sobre os respectivos
dados;
4. Se os dados não obedecerem as regras, o analista tem que decidir se os dados têm realmente erros ou se a regra definida já não faz sentido e precisa de ser redefinida. Por exemplo, antigamente
as crianças não podiam tirar o bilhete de identidade antes dos 10 anos de idade. Actualmente já
o podem fazer.
Algumas regras têm de ser obrigatoriamente verdadeiras e qualquer violação é considerada um caso de
dados incorrectos. Por exemplo, a data de um contrato de emprego tem de ser sempre superior à data
de nascimento do colaborador. Mas existem regras que não têm de ser sempre verdadeiras, permitindo
algumas excepções, como por exemplo: a data do contrato de emprego não tem de ser sempre superior
à data de nascimento da pessoa somada de dezoito anos, porque podem existir pessoas que iniciam a
sua actividade profissional antes dos dezoito anos.
3.4
Análise de registos aproximadamente duplicados
Para que a análise feita a uma base de dados fique completa, é necessário introduzir uma nova etapa,
que não é contemplada na metodologia do autor Jack Olson [2]. Este passo permite detectar registos aproximadamente duplicados. Dois registos dizem-se aproximadamente duplicados se ambos se
referem à mesma entidade numa BD e que não partilham a mesma chave, tal como já foi referido na
secção 2.2.1. Por exemplo, os seguintes dois registos de clientes de automóveis:
Cliente1->(Manuel Fonseca, Ford transit, 12-34-AP)
Cliente2->(Manu Fonseca, Ford t., 12-34-AP)
30
Não estão escritos da mesma forma, logo dão a entender que são dois clientes diferentes no mundo
real. Mas quando comparados campo a campo, verifica-se que as diferenças entre os dois são poucas.
Ambas as linhas se referem ao mesmo cliente embora com algumas diferenças de escrita nos primeiros
dois campos dos registos. A detecção de registos aproximadamente duplicados tem como função
identificar registos que diferem no geral, mas que se referem à mesma entidade no mundo real. Este
processo é frequentemente utilizado na limpeza de dados, onde existe uma primeira fase em que são
detectados os registos suspeitos de serem os mesmos, e uma segunda fase onde são retirados os que
se encontram em duplicado. A metodologia do DP foca-se só na primeira fase do processo, deixando
a decisão de retirada dos duplicados para uma metodologia que tenha funcionalidades de cleaning.
Existem vários algoritmos para a detecção de registos aproximadamente duplicados, referenciados
na secção 2.2, portanto qualquer algoritmo que detecte entidades duplicadas serve para ser utilizado
nesta etapa da metodologia. Um dos algoritmos básicos para a detecção de registos aproximadamente
duplicados envolve a comparação de um registo (constituído por dois ou mais campos) com todos os
outros da tabela que se está a analisar [53]. Em [49] é apresentado um outro método mais eficiente que
o anterior, porque reduz o número de comparações necessárias, evitando comparar todos os registos
com todos os outros. Este algoritmo é denominado de método da vizinhança ordenada (em inglês:
sorted neighborhood method) e está explicado na secção 2.2.3.
31
4 Auditoria da qualidade de dados da BD_OT
Neste capítulo vai ser explicado as principais tabelas da BD_OT, na qual foi realizada a auditoria da
qualidade de dados e a forma como foram detectados os vários problemas, através da metodologia DP
com o auxílio das duas ferramentas de DP da Oracle. Através de alguns quadros vai ser visualizado a
que etapa da metodologia DP as actividades das ferramentas de DP pertencem. No fim deste capítulo
vão ser explicadas as principais comparações entre as duas ferramentas.
4.1
Ambiente Experimental
Todo o processo de análise foi realizado num computador com acesso a uma Virtual Machine. O
primeiro disponha de um Windows XP Professional sobre um processador Pentium 4, com 1Gb de
memória RAM e 20Gb de disco livre. Enquanto que a Virtual Machine disponha do Windows Server
2003 Enterprise Edition sobre dois processadores Pentium 4, com 1Gb de memória RAM e 80Gb de
disco livre. A aplicação da metodologia de data profiling sobre os dados foi possível através do auxílio
das ferramentas: Oracle Warehouse Builder (OWB) 10G Release 2, solução data profiling; Oracle Data
Quality for Data Integrator(ODI-DQ) 10G Release 3; SQL Navigator 5.0 e LingPipe 3.5.1.
Para a realização de uma eficiente auditoria de qualidade de dados é necessário que o analista compreenda o negócio e os requisitos que estão representados na BD que se vai analisar, para que seja
capaz de avaliar se os dados têm anomalias. Devido a este factor serão explicados de seguida, os
factores mais importantes da BD_OT.
A BD utilizada é relacional e dispõe de um conjunto de tabelas que permitem guardar a informação
sobre os clientes de cartões pertencentes aos operadores de transporte. Perante isto a auditoria realizada focou-se principalmente em três tabelas: Clientes, Requisições e Cartões. As relações que
existem entre as três tabelas, são:
• Uma entre a tabela Clientes e a tabela Requisições com a cardinalidade de 1 - n, o que transmite
que um cliente pode ter acesso a uma ou mais requisições de cartão (um cliente que tenha pedido
uma 2a via de cartão, já tem pelo menos duas requisições), mas uma requisição só diz respeito a
um cliente, pois é intransmissível;
• Outra entre a tabela Clientes e a tabela Cartões com a cardinalidade de 1 - n, o que significa que
um cliente pode ter um ou mais cartões (porque o cartão caducou, ou porque o cliente perdeu e
precisa de outro, ou porque o cartão se encontra danificado, etc) e por consequente o cliente tem
acesso a várias requisições;
• E uma última entre a tabela Cartões e a Tabela Requisições, com uma cardinalidade de n - 1, onde
um cartão só tem uma requisição mas uma requisição pode ter vários cartões, desde que apenas
um deles esteja activo, isto é, os outros cartões estejam anulados. Por exemplo: uma requisição
pode ter mais do que um registo de cartão, quando no processo de emissão/personalização, o
cartão é mal impresso graficamente ou apresenta um erro e é necessário voltar a imprimir um
novo cartão para essa mesma requisição.
De seguida serão explicados os campos de cada tabela.
32
4.1.1
Tabela Clientes
Esta tabela dispõe de trinta campos, e para uma melhor compreensão, os mesmos vão ser explicados
segundo os dados pessoais do cliente e os que ajudam na gestão dos clientes da tabela. Em termos
de campos com dados pessoais, a tabela Clientes tem os seguintes:
• CUSTOMER_NAME - O nome do cliente;
• PROF_BIRTH_DATE - A data de nascimento;
• GENRE - O género do cliente;
• ADDRESS_STREET - A rua correspondente à morada do cliente, por exemplo: Rua D. Dinis;
• ADDRESS_NR - O número da casa, por exemplo: No 23;
• ADDRESS_FLOOR_NR - O número da porta, como por exemplo, 1o ;
• ADDRESS_FLOOR_LETTER - A letra da porta, por exemplo, ESQ;
• ADDRESS_CITY - A cidade onde o cliente mora;
• REGION_LEVEL1 - O concelho onde se encontra a morar o cliente;
• REGION_LEVEL2 - A freguesia onde se insere a residência do cliente;
• ADDRESS_ZIP_NR - O número do código-postal;
• ADDRESS_ZIP_TEXT - A que localidade o código-postal pertence;
• TELEPHONE_NR - O número de telefone;
• MOBIPHONE_NR - O número de telemóvel;
• EMAIL - O email;
• FISCAL_NR - O número de contribuinte;
• DOCUMENT_NR - O número do documento de identificação do cliente;
• DOCUMENT_ISSU_DATE - A data de emissão do documento de identificação;
• DOCUMENT_TYPE_CODE - O tipo de documento de identificação, como por exemplo: bilhete
de identidade, cartão de condução, passaporte, etc;
• DOCUMENT_ISSUE_LOCATION - Local de emissão do documento de identificação;
• CUSTOMER_TYPE_ID - Tipo de cliente, que poderá ser uma criança, um idoso, um VIP, um
colaborador de um operador, etc. Conforme o tipo de cliente, assim são atribuídos cartões com
preços diferentes;
• PHOTO_PATH - Caminho relativo para o ficheiro da fotografia;
• NOTES - Algumas notas sobre o cliente, que podem ser automaticamente inseridas pelo sistema
ou manualmente pelos utilizadores.
Os campos que ajudam na gestão da tabela são:
• CUSTOMER_ID - O identificador interno do cliente na tabela;
• CUSTOMER_STATE_ID - O estado em que se encontra o cliente. Por exemplo: Anulado, válido,
com erro, etc;
• CUSTOMER_STATE_REASON_ID - O motivo pelo qual o cliente se encontra no estado indicado
em cima. Se o cliente estiver anulado, poderá ser por exemplo, porque o mesmo não tem todos
os dados necessários;
33
• USER_ID - Identificador do último utilizador a alterar o registo do cliente;
• EMPLOYEE_NUMBER - Número de empregado dos operadores de transporte ou de outras entidades;
• SYSTEM_DATE - Data da última actualização do registo;
• CUSTOMER_NR - Um identificador de um cliente gerado pelo sistema, mas que é visível, podendo ser usado como referência para os Operadores.
4.1.2
Tabela Requisições
A tabela Requisições, contém cinquenta e três campos, e os mesmos vão ser explicados segundo: os
que já existem na tabela Clientes; os que dizem respeito aos dados de uma requisição; os que ajudam
na gestão das requisições da tabela e os que dizem respeito aos cartões de clientes. Os campos iguais
à tabela Cliente são: CUSTOMER_NAME, PROF_BIRTH_DATE, GENRE,
ADDRESS_STREET, ADDRESS_NR, ADDRESS_FLOOR_NR, ADDRESS_FLOOR_LETTER,
ADDRESS_CITY, REGION_LEVEL1, REGION_LEVEL2, ADDRESS_ZIP_NR, ADDRESS_ZIP_TEXT,
TELEPHONE_NR, MOBIPHONE_NR, EMAIL, FISCAL_NR, DOCUMENT_NR,
DOCUMENT_ISSUE_LOCATION, DOCUMENT_ISSU_DATE, DOCUMENT_TYPE_CODE, PHOTO_PATH,
USER_ID, SYSTEM_DATE.
Os campos que definem as características de uma requisição, são:
• REQUISITION_TYPE_CODE - Tipo de requisição (1a Via, 2a Via, etc.);
• REQUISITION_NR - É o número que vem preenchido nas requisições em papel, corresponde ao
identificador da requisição para os operadores (contém também alguma informação sobre quem
e onde foi feita a requisição);
• REQUISITION_DATE - Data que é preenchida na requisição em papel;
• REQUISITION_CLERK_NR - Número do funcionário que entregou a requisição em papel;
• REQUISITION_PATH - Localização física da requisição digitalizada;
• DELIVERED_DOCUMENTATION - Documentação entregue. Comprovativos para determinados
perfis de cartões;
• REQUISITION_STATE_ID - Estado da requisição. Pode estar registada, pendente, válida, confirmada, etc;
• REQUISITION_STATE_REASON_ID - A razão pela qual a requisição se encontra no estado que
foi definido. Se a requisição estiver incorrecta pode dever-se, por exemplo, ao facto da mesma
não ter a fotografia do cliente;
• REQUISITION_POST - Posto onde a requisição foi entregue;
• REQUISITION_OPERATOR - Operador onde foi entregue a requisição;
• NOTES - Notas relativas à requisição.
Os campos que ajudam na gestão das várias requisições são:
• REQUISITION_ID - O identificador interno da requisição na tabela;
• REQUISITION_INPUT_DATE - Data da introdução da requisição no sistema;
34
• EXTE_CARD_REQU_IDEN - Está relacionado com a importação das requisições via lote/ficheiro;
• PROF_COMPANY_OPER - Nome do operador a que pertence o tipo de cliente/modelo de cartão,
como por exemplo, tipos globais: Criança, 3a Idade, etc. E tipos específicos de cada operador, por
exemplo: Trabalhadores, familiares de trabalhadores, lojistas, etc;
• LOT_ID - Este campo é referente à identificação do lote pelo qual a requisição foi inserida no
sistema;
• EXTE_CARD_REQU_IDEN - Está relacionado com a importação das requisições via lote/ficheiro
e contém o n.o da requisição.
Os campos que têm informação sobre o cartão de cliente, são:
• PERS_NAME - Nome a colocar no cartão;
• DELIVERY_TYPE_ID - Identificador do tipo de entrega do cartão. Se é entregue ao cliente por
correio, se o mesmo o vem buscar a um posto do operador, etc;
• OLD_CARD_NR - Este campo é para ser usado, quando o cliente já possui cartões antigos. Por
exemplo, quando se pede uma 2a Via do cartão avariado com garantia, neste caso introduz-se o
n.o do cartão avariado (o sistema verifica se o cartão avariado está ou não de facto na garantia);
• MAIL_DELIV_DAYS - N.o de dias de entrega do cartão via correio;
• OPER_DELIVERY_STATION - Estação onde se vai entregar o cartão para o cliente o levantar (a
pedido do mesmo);
• OPER_DELIVERY_CODE - Código do operador onde se vai entregar o cartão;
• OPER_DELIVERY_DATE - Data de entrega do cartão num posto de um operador;
• PRICE - Preço do cartão;
• PAYMENT_STATE_ID - Estado do pagamento do cartão;
• CARD_MAX_VALIDITY_DATE - Data máxima de validade do cartão;
• PROF_COMPANY_WORKER_NR - N.o de colaborador trabalhador/funcionário do titular do cartão;
• CARD_PRODUCTION_OPER - Operador que produziu o cartão.
• CUSTOMER_TYPE_CODE - Tipo de cliente, que poderá ser uma criança, um idoso, um VIP, um
colaborador de um operador, etc. Conforme o tipo de cliente, assim são atribuídos cartões com
preços diferentes;
4.1.3
Tabela Cartões
A tabela dos cartões é constituída por trinta e cinco campos. Novamente, para uma melhor compreensão, os mesmos vão ser justificados segundo: os campos que são iguais aos da tabela Requisições; as características do cartão de cliente e segundo os campos que auxiliam a gestão da respectiva tabela. Os atributos iguais aos da tabela Requisições, são: REQUISITION_ID, PERS_NAME,
PROF_BIRTH_DATE, PROF_COMPANY_OPER, PROF_COMPANY_WORKER_NR, USER_ID, SYSTEM_DATE, EXTE_CARD_REQU_IDEN, PHOTO_PATH, CUSTOMER_TYPE_CODE,
OPER_DELIVERY_CODE, OPER_DELIVERY_STATION.
Os campos que ditam as características de um cartão de cliente, são:
35
• CARD_TYPE_ID - Designa o tipo de cartão. Contém o tipo físico e o tipo do modelo de dados do
cartão;
• SERIAL_NR - N.o de série do cartão, ou seja, n.o de fábrica do cartão;
• CARD_NUMB_ISSU_ENVI_APPL_ISSU - Emissor do cartão;
• CARD_DATE_ISSU - Data de emissão do cartão;
• ENVI_APPL_END_DATE - Data de expiração do cartão;
• PROF_USAGE_CODE_1 - Código do primeiro perfil do cartão, por exemplo, o código 2 diz respeito ao perfil criança;
• PROF_USAGE_DATE_1 - Data de expiração do primeiro perfil do cartão;
• PROF_USAGE_CODE_2 - Código do segundo perfil do cartão;
• PROF_USAGE_DATE_2 - Data de expiração do segundo perfil do cartão;
• PROF_USAGE_CODE_3 - Código do terceiro perfil do cartão;
• PROF_USAGE_DATE_3 - Data de expiração do terceiro perfil do cartão;
• DELIVERY_TYPE_CODE - Tipo de entrega do cartão;
• NOTES - Notas sobre o cartão;
• CARD_STATE_ID - Estado do cartão. Pode estar válido, anulado, caducado, etc;
• CARD_STATE_REASON_ID - A razão pela qual o cartão se encontra no estado que foi definido.
Se o cartão estiver anulado poderá ser por exemplo, porque o mesmo foi roubado;
• RECOVER_STATE_ID - Estado anterior em que o cartão se encontrava.
E os campos que auxiliam na gestão dos cartões da tabela, são:
• CARD_ID - Identificador interno do cartão na tabela;
• LOT_ID - Este campo é referente à identificação do lote pelo qual o cartão foi inserido no sistema;
• CARD_NUMB_ISSU_CARD_NR - N.o sequencial do cartão, ou seja, n.o de cartão gerido pela
aplicação;
• PERSONALIZATION_USER_ID - Identificador de quem personalizou o cartão;
• PERSONALIZATION_CLERK_NR - Número do funcionário que personalizou o cartão;
• PERSONALIZATION_DATE - Data da personalização do cartão;
• EXTE_PHOTO_IDEN_CONV - Identificador externo convertido da fotografia.
4.2
Aplicação da metodologia de DP
Neste capítulo, já foram indicadas as principais características das tabelas a analisar na BD_OT. Nesta
secção vão ser mostrados e explicados como foram encontrados os resultados da aplicação da metodologia de data profiling enunciada na secção 3 a essas mesmas tabelas. Para realizar esta análise foi
necessário o auxílio de ferramentas de DP devido ao grande número de dados a analisar (cerca de 2
milhões de registos em cada tabela), situação já referida na secção 2.1. Ao realizar-se uma auditoria
de qualidade de dados numa BD esta deve ser feita de forma rigorosa e sistemática, chegando a todos
os elementos de uma BD. Neste projecto de auditoria de qualidade de dados da BD_OT, para evitar a
36
repetição das funcionalidades das várias etapas da metodologia, para cada tabela e devido à falta de
tempo, a detecção de anomalias foi orientada segundo os problemas enunciados na secção 1.3.
4.2.1
Análise de cada coluna de uma tabela
Nesta primeira etapa da metodologia e com base nas propriedades das colunas, nas funções de estatística disponíveis e na aplicação do método de detecção de anomalias através de dicionários de
palavras, irá ser realizada uma análise das colunas das três tabelas principais da BD_OT.
4.2.1.1
Análise da coluna através das suas propriedades
Neste sub-passo da análise da coluna é pretendido detectar anomalias nos dados através das propriedades da mesma enunciadas na secção 3.1.1. As anomalias encontradas, ocorreram nos seguintes
campos:
1. DOCUMENT_NR;
Existem determinados identificadores de documentos de identificação que não seguem o formato
que foi estipulado no mundo real, para os mesmo. Para que fosse possível detectar estes erros
foi necessário perceber quais eram os documentos de identificação permitidos pelos operadores
de transporte:
(a) Cartão do Cidadão (CI) - É um documento autêntico que contém todos os dados relevantes
de cada cidadão para a sua identificação e inclui o número de identificação civil, o número de
identificação fiscal, o número de utente dos serviços de saúde e o número de identificação
da segurança social [54];
(b) Bilhete de identidade (BI) - É um documento que serve para provar a identidade civil do seu
titular perante quaisquer autoridades, entidades públicas ou privadas, sendo válido em todo
o território nacional, sem prejuízo da eficácia reconhecida por normas comunitárias e por
tratados e acordos internacionais [55];
(c) Bilhete de identidade Militar (BM) - Documento que substitui, para todos os efeitos legais, em
território nacional, o bilhete de identidade de cidadão nacional(para todos os militares que o
possuam)[56];
(d) Carta de Condução (CC) - É o documento que atesta a aptidão de um cidadão para conduzir
veículos a motor na via pública. Tendo em vista a melhoria da segurança da circulação
rodoviária no espaço comunitário e uma maior facilidade de circulação de pessoas, tornase necessário adoptar uma carta de condução nacional de modelo comunitário mutuamente
reconhecido pelos Estados membros sem obrigação de troca [57].
(e) Cédula Pessoal (CP)- Documento de identificação para menores que não tenham bilhete de
identidade;
(f) Cédula Consular (CO) - Documento emitido pelos consulados do qual consta a morada oficial
do cidadão;
37
(g) Autorização de Residência (AR) - Cada cidadão de um país do Espaço Económico Europeu,
pode residir em qualquer um com certas limitações. Se o mesmo tiver intenção de permanecer 3 meses (ou mais de 6 em alguns países) deve-se solicitar uma autorização de
residência [58];
(h) Atestado de Residência (AT) - Comprova a morada onde um cidadão habita (território nacional);
(i) Autorização de Permanência (AP) - Documento anterior à Autorização de Residência. Em
relação à residência de imigrantes em território nacional, procede-se à substituição dos quatro tipos de visto: de trabalho, de estudo, da prorrogação de permanência com autorização
para trabalhar, da estada temporária com autorização para exercício de actividade profissional subordinada e da autorização de permanência por um único tipo de título habilitante
da fixação de residência em Portugal: a autorização de residência[59];
(j) No de Ordem CCFL (NO) - Número mecanográfico de colaboradores de um tipo específico
de operadores de transporte;
(k) Passaporte(P) - É o documento oficial, emitido pelos Governos Civis, que identifica o cidadão
português perante as autoridades de outros países, permitindo a anotação de entrada e
saída pelos portos, aeroportos e vias de acesso internacionais [60];
(l) Visto (V) - É um documento emitido por um país que visa dar a um certo indivíduo permissão
para entrar por um certo período de tempo e para certas finalidades [61], nesse país.
Depois foi necessário saber qual é o formato standard (em Portugal) do identificador, para cada
um deles:
(a) CI - A cada cartão de cidadão é atribuído um número de documento, constituído por três
caracteres, sendo dois alfanuméricos e um dígito de controlo, antecedidos pelo número de
identificação civil do respectivo titular [54]. O seu padrão é 99999999 AA9 (9 designa um
número e A uma letra);
(b) BI - Ao bilhete de identidade emitido pela primeira vez é atribuído um número sequencial,
seguido de um dígito de controlo, que se mantém nas renovações [55]. Este número tem de
ter sempre 8 dígitos, sequencial, positivo e inteiro. O seu padrão é: 99999999;
(c) BM - Este número tem 8 dígitos, é sequencial, positivo e inteiro. O seu padrão é: 99999999;
(d) CC - Tem como formato uma letra seguida de um no de série sequencial sem comprimento
mínimo, sendo que a letra representa o distrito do serviço emissor. Por exemplo A-9999999;
(e) CP - É composto por um número sequencial referente ao assento de nascimento e o número
de identificação da conservatória. Frequentemente é indicado apenas o número de assento,
por exemplo 999999, mas por questões legais a definição anterior é a correcta;
(f) CO, AR, AT, AP - É numérico, sequencial, positivo e inteiro;
(g) NO - É constituído por um número, cujos últimos três dígitos têm o seguinte significado: 000
é referente ao trabalhador; 001 indica o cônjuge; 002 indica o primeiro filho e 003 indica o
segundo filho;
38
(h) P - Tem como comprimento 7 caracteres, sendo formado por uma letra e seis dígitos. O seu
padrão é A999999. Actualmente existem 3 tipos de passaportes:
• Europeu, emitido de Jan/1989-Dez/2000 e as letras usadas são: D,E,F (Governo Civil e
Gov. Regional) e X,Z (Consulados);
• Digital, emitido de Jan/2001-Agosto/2006 e as letras usadas: G,H (Governo Civil e Gov.
Regional) e R (Consulados);
• Electrónico, emitido a partir de Agosto de 2006 e as letras usadas: J.
Sendo que a letra do passaporte, só pode ser uma das indicadas em cima.
(i) (V) - Tem 10 caracteres e o seu padrão é A99999999.
Uma vez que a tabela Clientes e Requisições têm estes tipos de documentos todos juntos, foi
necessário criar tabelas individuais para cada um dos documentos de identificação, para que a
análise do campo DOCUMENT_NR fosse especializada. Esta divisão foi feita através da ferramenta SQL Navigator. Com as tabelas divididas e com o formato de cada identificador dos
documentos foi possível ver que muitos deles saiam do formato a que estavam destinados. Esta
análise foi possível através da propriedade pattern na ferramenta OWB - Módulo DP e mask
disponível na ferramenta ODI-DQ. O pattern tem um conjunto de algoritmos de expressões regulares que permitem analisar que caracteres constituem os valores de cada registo de uma coluna.
Por exemplo: O número de bilhete de identidade se for ’N0532548’, o respectivo pattern detectado
pela ferramenta é ’ANNNNNNN’(’A’ designa uma letra e ’N’ designa um dígito). É apresentado de
seguida a análise para cada tipo de documento:
• O padrão standard para o cartão do cidadão é ’99999999 AA9’, e através da análise tem-se:
– Dezasseis padrões para o seu identificador;
– Os padrões dominantes podem ser visualizados na Figura 4.1;
– Todos os padrões não seguem o padrão idealizado.
• O padrão standard para o bilhete de identidade é ’99999999’, e através da análise tem-se:
– Mil e quarenta e dois padrões para o seu número;
– Os padrões dominantes podem ser visualizados na Figura 4.2;
– Na figura pode-se verificar que os padrões: sexto, oitavo e nono, não seguem o formato
standard do número de bilhete de identidade porque têm letras na sua constituição;
– Através da propriedade Unique Key, representada na Figura 4.3, o campo deste identificador tem 931 276 clientes com o número do bilhete de identidade distinto. Se esta
tabela tem 938 439 clientes logo conclui-se que existem 7163 clientes com o número de
bilhete de identidade duplicado.
• O padrão standard para o bilhete de identidade militar é ’99999999’, e através da análise
tem-se:
– Vinte e cinco padrões para o seu número;
– Os padrões dominantes podem ser visualizados na Figura 4.4;
39
Figura 4.1: Padrões dominantes para o cartão do cidadão (OWB - Módulo DP).
Figura 4.2: Padrões dominantes para o bilhete de identidade.
– Na figura pode-se verificar que por exemplo os últimos padrões, não seguem o formato
standard do número de bilhete de identidade militar porque os mesmos, têm letras e
espaços na sua constituição.
• O padrão standard para a carta de condução é ’AA-9999999’, e através da análise tem-se:
– Trezentos e oitenta e sete padrões para o seu identificador;
– Os padrões dominantes podem ser visualizados na Figura 4.5;
– Na figura pode-se verificar que por exemplo o quarto padrão, não segue o formato standard da carta de condução porque tem só números na sua constituição.
• O padrão standard para a cédula pessoal é um número sequencial, por exemplo ’999999’, e
através da análise tem-se:
– Quatrocentos padrões para o seu identificador;
40
Figura 4.3: Número de clientes com bilhete de identidade distintos (OWB-Módulo DP).
Figura 4.4: Padrões dominantes para o bilhete de identidade militar.
– Os padrões dominantes podem ser visualizados na Figura 4.6;
– Na figura pode-se verificar que por exemplo o último padrão, não segue o formato standard da cédula pessoal porque tem letras na sua constituição.
• O padrão standard para a cédula consular é um número sequencial, por exemplo ’999999’,
e através da análise tem-se:
– Setenta padrões para o seu identificador;
– Os padrões dominantes podem ser visualizados na Figura 4.7;
– Na figura pode-se verificar que por exemplo o quarto padrão, não segue o formato standard da cédula consular porque tem letras na sua constituição.
• O padrão standard para a autorização de residência é um número sequencial, por exemplo
’999999’, e através da análise tem-se:
– Cento e vinte seis padrões para o seu identificador;
– Os padrões dominantes podem ser visualizados na Figura 4.8;
41
Figura 4.5: Padrões dominantes para a carta de condução.
Figura 4.6: Padrões dominantes para a cédula pessoal.
– Na figura pode-se verificar que por exemplo o primeiro padrão, não segue o formato
standard da autorização de residência porque tem letras na sua constituição.
• O padrão standard para o atestado de residência é um número sequencial, por exemplo
’999999’, e através da análise tem-se:
– Cento e cinquenta padrões para o seu identificador;
– Os padrões dominantes podem ser visualizados na Figura 4.9;
– Na figura pode-se verificar que por exemplo o segundo padrão, não segue o formato
standard do atestado de residência porque tem letras na sua constituição.
• O padrão standard para a autorização de permanência é um número sequencial, por exemplo
’999999’, e através da análise tem-se:
– Trinta e sete padrões para o seu identificador;
– Os padrões dominantes podem ser visualizados na Figura 4.10;
– Na figura pode-se verificar que por exemplo o primeiro padrão, não segue o formato
standard da autorização de permanência porque tem letras na sua constituição.
• O padrão standard para o no de Ordem CCFL é um número sequencial, por exemplo ’999999’,
e através da análise tem-se:
– Trinta e oito padrões para o seu identificador;
– Os padrões dominantes podem ser visualizados na Figura 4.11;
42
Figura 4.7: Padrões dominantes para a cédula consular.
Figura 4.8: Padrões dominantes para a autorização de residência.
– Na figura pode-se verificar que por exemplo o segundo padrão, não segue o formato
standard do no de Ordem CCFL porque tem letras na sua constituição.
• O padrão standard para o passaporte é ’A999999’, e através da análise tem-se:
– Mil cento e vinte e nove padrões para o seu identificador;
– Os padrões dominantes podem ser visualizados na Figura 4.12;
– Na figura pode-se verificar que por exemplo o primeiro padrão, não segue o formato
standard do passaporte porque tem duas letras na sua constituição.
43
Figura 4.9: Padrões dominantes para o atestado de residência.
Figura 4.10: Padrões dominantes para a autorização de permanência.
2. ADDRESS_ZIP_NR;
Existem nesta coluna códigos-postais incompletos, ou seja, que não seguem o formato standard
do código-postal português: 9999-999. A propriedade que dita o formato de um campo é o padrão.
Na ferramenta ODI-DQ a funcionalidade que detecta esta propriedade ao longo dos valores de
uma coluna é o mask e no OWB - Módulo DP é o pattern. Os resultados dos vários padrões encontrados vão ser mostrados segundo a ferramenta ODI-DQ, pois esta mostra todos os padrões
e não só os dominantes como acontece no OWB - Módulo DP. Um exemplo de um código-postal
incompleto é: ’2700’ que corresponde ao padrão ’NNNN’ (quatro dígitos). Os resultados na detecção de padrões para a tabela Clientes pode ser visualizados na Figura 4.13. Relativamente à
tabela Requisições os resultados obtidos podem ser visualizados na Figura 4.14. Como se pode
visualizar nas figuras 4.13 e 4.14, existem 27 padrões que fogem ao padrão normal do códigopostal, sendo que na tabela Clientes 82% dos registos seguem o padrão correcto e na tabela
Requisições 81%.
44
Figura 4.11: Padrões dominantes para o no de Ordem CCFL.
Figura 4.12: Padrões dominantes para o passaporte.
3. EMAIL;
Este campo contém emails que estão mal formados. Um dos motivos pelo qual isto acontece é
quando os utilizadores estão a inserir um novo registo de um cliente e não têm o valor do email,
compensando a falta com valores que não respeitam o formato standard do email, outro dos motivos sucede quando o cliente escreve mal o seu email. Para detectar este tipo de situações foi
utilizada a ferramenta LingPipe, que detém um mecanismo de expressões regulares. A expressão
que permite detectar a existência de emails válidos, está representada na Figura 4.15 [3]. Esta
expressão foi testada em todos os valores do campo EMAIL e as amostras dos resultados de
emails inválidos nas tabelas Clientes e Requisições estão representadas nas Tabelas 4.1 e 4.2
respectivamente.
Tabela 4.1: Amostra dos resultados de emails inválidos na tabela Clientes.
45
Figura 4.13: Padrões que o código-postal da tabela Clientes, assume ao longo da sua coluna.
Tabela 4.2: Amostra dos resultados de emails inválidos na tabela Requisições.
4. TELEPHONE_NR;
5. MOBIPHONE_NR;
Ao analisar estes últimos dois campos, foram encontradas duas anomalias:
(a) Existência de números de telefone e de telemóvel incompletos;
Nas tabelas Clientes e Requisições existem clientes com números de telefone fixo e números
de telemóvel incompletos. Por exemplo, um número de telefone incompleto será ’219 876 98’
e de um telemóvel pode ser ’926 777 99’, porque em ambos lhes falta um dígito. Em Portugal, o padrão dos números de telefone fixos e dos números de telemóvel é: ’NNNNNNNNN’,
onde o comprimento normal para os mesmos é de nove dígitos. Para detectar esta anomalia
foi utilizada a funcionalidade mask da ferramenta ODI-DQ. Nesta situação não foi possível
utilizar a propriedade comprimento porque a mesma nas duas ferramentas de DP da Oracle, só indica o maior e o menor comprimento encontrado e não os diferentes comprimentos
que os valores de uma coluna podem assumir. Os resultados da detecção de padrões de
números de telefone fixo para a tabela Clientes podem ser visualizados na Figura 4.16 e
dos números de telemóvel podem ser consultados na Figura 4.18. Relativamente à tabela
Requisições os resultados obtidos podem ser visualizados na Figura 4.17 para o números
de telefone fixo e na Figura 4.19 para os números de telemóvel.
46
Figura 4.14: Padrões que o código-postal da tabela Requisições, assume ao longo da sua coluna.
Figura 4.15: Expressão regular para detectar emails inválidos [3].
Na tabela Clientes e Requisições pode-se verificar através das Figuras 4.16 e 4.17, que
existem sete e oito padrões respectivamente, de números de telefone fixo que não têm o
comprimento normal do padrão definido como standard. E nas Figuras 4.18 e 4.19, pode-se
verificar que para o número de telemóvel existem oito padrões nas tabelas Clientes e Requisições que não seguem também o comprimento do padrão standard.
(b) Existência de números de telefone e de telemóvel errados.
Nas tabelas Clientes e Requisições existem clientes com números de telefone fixo e números
de telemóvel errados. Um número de telefone fixo ou de telemóvel encontra-se incorrecto
quando não existe no domínio dos mesmos. Por exemplo, um número errado de telefone fixo
será por exemplo ’543 456 345’ e um número de telemóvel errado pode ser ’999 777 899’,
porque os números de telefone e de telemóvel não suportam os prefixos apresentados. Para
detectar estas anomalias foi necessário utilizar o mecanismo de expressões regulares da ferramenta LingPipe. Foram criadas duas expressões regulares, uma para detectar números de
telefone incorrectos, que pode ser visualizada na Figura 4.21 e outra para detectar números
de telemóvel errados, disponível na Figura 4.20.
47
Figura 4.16: Padrões que o número de telefone da tabela Clientes assume.
Estas expressões contêm todos os prefixos possíveis para os números. Por exemplo, a
expressão regular de telefones fixos tem os prefixos 21, 22, 241, etc. A expressão que detecta números de telemóveis errados tem os prefixos 96, 962, 93, etc. Para cada cliente ou
requisição é necessário verificar, se o valor do campo TELEPHONE_NR e do campo MOBIPHONE_NR, começam por algum dos prefixos definidos nas respectivas expressões. Se
isto não acontecer o cliente ou requisição em análise são reportados como tendo o número
de telefone ou de telemóvel errados. As amostras dos resultados para as tabelas Clientes
e Requisições com números de telefone errados encontram-se nas Tabelas 4.3 e 4.4. E
as amostras dos resultados com o número de telemóvel errado podem ser consultadas na
Tabela 4.5, para a tabela Clientes e na Tabela 4.6, para a tabela de requisições.
Tabela 4.3: Amostra dos resultados de telefones errados na tabela Clientes.
Tabela 4.4: Amostra dos resultados de telefones errados na tabela Requisições.
48
Figura 4.17: Padrões que o número de telefone da tabela Requisições assume.
Tabela 4.5: Amostra dos resultados de telemóveis errados na tabela Clientes.
(c) REQUISITION_NR.
Existem clientes que têm números de requisições inválidas. O padrão do número de uma requisição é constituído por ’OOPPPPPSSSSSSSS’, onde ’OO’ designa o código do operador
(numérico), ’PPPPP’ indica o código do posto (alfanumérico) onde a requisição se encontra e ’SSSSSSSS’ designa um número sequencial (numérico). Por exemplo um número de
requisição inválido será ’AB3FR3344444567’, pois o operador não é constituído por letras.
Para detectar esta anomalia foi utilizada a funcionalidade mask da ferramenta ODI-DQ. Todos os números que não respeitem o padrão definido em cima são inválidos. Os valores do
campo REQUISITION_NR formam 58 padrões diferentes. Na Figura 4.22 pode-se verificar
os padrões dominantes no referido campo. Na Tabela 4.7 pode-se visualizar uma amostra
que contém alguns padrões que não respeitam o formato standard definido para o número
de requisição.
4.2.1.2
Análise da coluna através de funções estatísticas
Segundo a análise das colunas através das várias funções estatísticas que se podem encontrar na
secção 3.1.2, foram detectadas anomalias em algumas colunas da tabela Clientes e Requisições. As
colunas analisadas foram:
49
Figura 4.18: Padrões que o número de telemóvel assume na tabela Clientes.
Tabela 4.6: Amostra dos resultados de telemóveis errados na tabela Requisições.
1. PROF_BIRTH_DATE;
Para se ser cliente de um operador de transporte, é necessário ter pelo menos quatro anos de
idade, logo como esta análise foi feita em 2008, todas as datas de nascimento que apareçam
depois de 2004 estão erradas. O mesmo se passa com as datas de nascimento de pessoas
idosas. Portugal está com uma esperança média de vida elevada, sendo que a maioria dos
idosos vivem até aos 75 anos [62], havendo raras excepções de pessoas que podem ir até aos
130 anos. Todas as datas de nascimento que datarem antes de 1878 estão erradas. Para detectar
este problema usou-se as funções estatísticas min e max, indicando a data de nascimento mais
recente e a mais antiga de todos os clientes. Mas estas funções, nas ferramentas de DP da Oracle
só dão o elemento mais antigo ou mais recente, pois não têm forma de mostrar todas as datas
que são anteriores a 1878 e superiores a 2004, sendo que este último caso vai ser mostrado na
secção 4.2.3. A Figura 4.23 indica a data de nascimento mais antiga e mais recente, pertencente a
um determinado cliente. Na Figura 4.24 pode-se verificar qual é a data de nascimento mais antiga
e a mais recente de uma determinada requisição de um cliente. Como se pode verificar existem
clientes que supostamente nasceram nas datas: 23-04-0009, 08-01-3000, 18-06-1111 e 23-01806, o que está errado pois no sistema dos cartões da BD_OT não podem existir actualmente ou
num passado recente clientes com estas datas de nascimento.
50
Figura 4.19: Padrões que o número de telemóvel assume na tabela Requisições.
Figura 4.20: Expressão regular para detectar números de telemóvel errados.
2. DOCUMENT_ISSU_DATE;
Tal como já foi explicado para a data de nascimento de um cliente, também a data de emissão de
um documento de identificação não pode ser mais antiga que 1900 nem mais recente que 2008.
Isto porque não é possível que um documento de identificação seja emitido e válido antes e depois
das datas referidas. Para detectar este problema utilizou-se as funções estatísticas min e max. A
Figura 4.25 indica a data de emissão mais antiga e mais recente, pertencente a um determinado
cliente. Na Figura 4.26 pode-se verificar qual é a data de emissão mais antiga e a mais recente
de uma determinada requisição pertencente a um determinado documento de identificação de um
cliente. Como se pode verificar existem clientes que supostamente têm documentos de identificação que foram emitidos nas datas: 12-01-0401, 21-03-2202, 16-01-0015 e 21-03-2202 o que
está errado pois no sistema dos cartões da BD_OT não podem existir actualmente ou num passado recente clientes com documentos de identificação com estas datas de emissão.
3. REQUISITION_DATE;
Em termos de datas de requisições, não podem existir pedidos de cartões (requisições) que datem
acima de 2008 (ano actual), nem requisições de clientes que datem antes de 2001, ano em que o
sistema de cartões da BD_OT iniciou a sua função. Para detectar a data de uma requisição mais
antiga ou mais recente usou-se as funções estatísticas min e max da ferramenta OWB - Módulo
DP. A Figura 4.27 indica a data de um pedido de cartão mais antiga e mais recente, pertencente
a uma determinada requisição. Como se pode verificar existem duas requisições de cartões que
foram efectuadas nas datas: 06-12-2050 e 12-12-0018 o que está errado pois no sistema dos
cartões da BD_OT não podem existir actualmente nem num passado recente pedidos de cartões
com estas datas de emissão.
51
Figura 4.21: Expressão regular para detectar números de telefone errados.
Figura 4.22: Padrões dominantes para o número de requisição dos clientes.
Figura 4.23: Data de nascimento mais recente e mais antiga - Tabela Clientes.
52
Tabela 4.7: Amostra de padrões inválidos do número da requisição.
Figura 4.24: Data de nascimento mais recente e mais antiga - Tabela Requisições.
Figura 4.25: Data de emissão de um documento de identificação mais recente e mais antiga - Tabela Clientes.
53
Figura 4.26: Data de emissão de um documento de identificação mais recente e mais antiga - Tabela Requisições.
Figura 4.27: Data do pedido de um cartão mais recente e mais antiga.
4. GENRE;
O problema deste campo designa-se pela ausência do preenchimento do género de alguns clientes,
ou seja, existem clientes que não é possível afirmar se são do sexo feminino ou masculino. Esta
anomalia é detectada através da contagem de valores a nulls na coluna GENRE. Para detectar
este tipo de anomalias, tanto se pode utilizar a funcionalidade Null Count da ferramenta ODI-DQ
como a funcionalidade # Nulls da ferramenta OWB - Módulo DP. O número de clientes e requisições com o género não definido pode ser visualizado nas Figuras 4.28 e 4.29.
4.2.1.3
Análise da coluna através de dicionários de palavras
Finalmente, no último sub-passo da análise das colunas, vão ser abordados os problemas existentes
nas principais tabelas da BD_OT segundo aplicação de dicionários de palavras, onde a sua aplicação
já foi explicada na secção 3.1.3. Na análise de dicionários de palavras foram utilizadas as seguintes
colunas, que se encontram nas tabelas Clientes e Requisições:
1. ADDRESS_STREET;
2. ADDRESS_NR;
3. ADDRESS_FLOOR_NR;
4. ADDRESS_FLOOR_LETTER;
5. ADDRESS_CITY;
6. REGION_LEVEL1;
54
Figura 4.28: Número de clientes que não têm o género definido, através da ferramenta ODI-DQ.
Figura 4.29: Número de requisições que não têm o género do cliente definido, através da ferramenta OWB Módulo DP.
7. REGION_LEVEL2;
Todos os campos mencionados em cima foram analisados da mesma forma, pois todos eles
fazem parte da morada de um cliente. O endereço de um cliente está repartido em 6 campos: O
ADDRESS_STREET que representa a rua, praça, praceta, largo, avenida, etc; o ADDRESS_NR
que refere o número da casa; o ADDRESS_FLOOR_NR que indica o número da porta; o ADDRESS_FLOOR_LETTER que representa a letra da porta; o ADDRESS_CITY que refere cidade;
o REGION_LEVEL1 que indica a freguesia e o REGION_LEVEL2 que representa o concelho onde
o cliente reside. Por vezes nem todos os campos são preenchidos, porque não foram recolhidos
todos os dados da morada do cliente, ou porque os valores de determinados campos da morada
se encontram em campos vizinhos. Tem-se por exemplo a morada, rua das amoras no 24 1o Dto
Loures Bobadela Loures, distribuída da seguinte forma:
• ADDRESS_STREET = Rua das amoras no 24 1o Dto;
• ADDRESS_NR = NULL;
• ADDRESS_FLOOR_NR = NULL;
55
• ADDRESS_FLOOR_LETTER = NULL;
• ADDRESS_CITY = Loures;
• REGION_LEVEL1 = Bobadela;
• REGION_LEVEL2 = Loures.
Tal como se pode verificar há campos que não estão preenchidos pois a sua informação está
no campo ADDRESS_STREET. Para detectar este tipo de problemas foi necessário utilizar a
ferramenta LingPipe, usando os seguintes passos:
(a) Criar um dicionário para cada campo mencionado em cima. Cada dicionário contém as
palavras chave do domínio do respectivo campo, como por exemplo: Para o campo ADDRESS_STREET as palavras chave do dicionário são ’Rua’, ’Travessa’, ’Praceta’, entre outras;
(b) Para cada cliente ou requisição é necessário verificar, se em qualquer campo da morada
existe uma palavra chave de um dicionário de outro campo. Se isto acontecer o cliente ou
requisição em análise são reportados como tendo a morada mal distribuída pelos respectivos
campos.
Uma amostra dos resultados de moradas mal distribuídas segundo os respectivos campos para a
tabela Clientes pode ser consultada na Tabela 4.8. Para a tabela Requisições, uma amostra dos
resultados de moradas mal distribuídas pode ser consultada na Tabela 4.9.
Tabela 4.8: Amostra dos resultados de moradas mal distribuídas na tabela Clientes.
Tabela 4.9: Amostra dos resultados de moradas mal distribuídas na tabela Requisições.
8. ADDRESS_ZIP_NR.
Existem códigos-postais de clientes que estão bem formados sintácticamente, mas que na realidade não existem em Portugal, ou seja, não referem nenhuma localidade válida, são portanto
códigos-postais inválidos. Para detectar este erro foi necessário, utilizar a ferramenta LingPipe,
usando os seguintes passos:
(a) Conseguir uma lista com todos os códigos-postais das localidades de Portugal (cedido no
site dos CTT [63]);
(b) Criar um dicionário com base na lista referida em cima;
56
(c) Para cada cliente ou requisição é necessário verificar se o valor do código-postal existe no
dicionário criado. Se não existir, o cliente ou a requisição em análise, é reportado como tendo
o código postal inválido.
Uma amostra dos resultados de códigos-postal inexistentes na tabela Clientes pode ser consultada na Tabela 4.10. Para a tabela Requisições, uma amostra dos resultados de códigos-postal
inexistentes pode ser consultada na Tabela 4.11.
Tabela 4.10: Amostra dos resultados de códigos-postal inválidos na tabela Clientes.
Tabela 4.11: Amostra dos resultados de códigos-postal inválidos na tabela Requisições.
4.2.2
Análise da estrutura da base de dados
Ao analisar-se a estrutura das três tabelas principais da BD_OT, chegou-se à conclusão que a relação
existente entre as tabelas Clientes e Requisições origina dois problemas:
1. Colunas redundantes através da chave estrangeira, que une as duas tabelas;
A tabela Clientes tem uma relação de 1-n com a tabela Requisições, ou seja um cliente pode
ter uma ou várias requisições, mas uma requisição só pode ser adquirida por um cliente. Esta
é a visão funcional da relação entre as duas tabelas. Fisicamente o que existe é uma relação
de n para n entre as mesmas, porque quando foi desenhada a BD_OT pensou-se que no futuro
poderia ser útil uma relação deste género. A tabela que resulta da relação contém os campos:
REQUISITION_ID, CUSTOMER_ID, SYSTEM_DATE. Os dois primeiros são chaves estrangeiras
para a tabela Requisições e Clientes, respectivamente. Para além desta relação que une as duas
tabelas, ambas têm ainda campos iguais que se referem aos dados pessoais do cliente:
• CUSTOMER_NAME - Nome do cliente;
• PROF_BIRTH_DATE - Data de nascimento;
• GENRE - Género;
• ADDRESS_STREET - Nome da rua;
• ADDRESS_NR - Número da residência;
• ADDRESS_FLOOR_NR - Número da porta da residência;
• ADDRESS_FLOOR_LETTER - Letra da porta da residência;
• ADDRESS_CITY - Cidade da residência do cliente;
57
• REGION_LEVEL1 - Concelho;
• REGION_LEVEL2 - Freguesia;
• ADDRESS_ZIP_NR - Número do código-postal;
• ADDRESS_ZIP_TEXT - Localidade do código-postal;
• TELEPHONE_NR - Número de telefone;
• MOBIPHONE_NR - Número de telemóvel;
• EMAIL;
• FISCAL_NR - Número de identificação fiscal (contribuinte);
• DOCUMENT_NR - Número do documento de identificação;
• DOCUMENT_ISSUE_LOCATION - Local do documento de identificação;
• DOCUMENT_ISSU_DATE - Data de emissão do documento de identificação;
• DOCUMENT_TYPE_CODE - Tipo do documento de identificação.
Estes campos na tabela Requisições são redundantes, pois através da chave estrangeira existente, é possível aceder aos mesmos da tabela Cliente.
2. Um cliente com uma requisição, por vezes não tem os mesmos dados pessoais nas duas tabelas.
Existe uma anomalia nos campos referidos anteriormente, pois se um cliente tem uma requisição,
a BD neste momento não é capaz de garantir que os dados pessoais do cliente são iguais em
ambas as tabelas, para uma melhor compreensão veja-se o seguinte exemplo: Um cliente com
o CUSTOMER_ID = ’16484’ e com a respectiva requisição cujo REQUISITION_ID = ’30899’, os
seus campos pessoais na tabela cliente são:
• CUSTOMER_NAME = CRISTIANA CARREIRA CALADO RODRIGUES;
• PROF_BIRTH_DATE = 11-Nov-1974;
• GENRE = F;
• ADDRESS_STREET = R PADRE ALFREDO FERNANDES DE BRITO;
• ADDRESS_NR = 20A;
• ADDRESS_FLOOR_NR = 2;
• ADDRESS_FLOOR_LETTER = D;
• ADDRESS_CITY = VENDA DO PINHEIRO;
• REGION_LEVEL1 = NULL;
• REGION_LEVEL2 = VENDA DO PINHEIRO;
• ADDRESS_ZIP_NR = 2665-527;
• ADDRESS_ZIP_TEXT = VENDA DO PINHEIRO;
• TELEPHONE_NR = 219662617;
• MOBIPHONE_NR = 919014243;
• EMAIL = NULL;
• FISCAL_NR = NULL;
• DOCUMENT_NR = 10294418;
• DOCUMENT_ISSUE_LOCATION = NULL;
58
• DOCUMENT_ISSU_DATE = 01-01-2004;
• DOCUMENT_TYPE_CODE = BI.
A requisição pertencente ao cliente anterior, na tabela requisições tem os seguintes valores nos
campos pessoais do cliente:
• CUSTOMER_NAME = Hugo Alexandre Silva;
• PROF_BIRTH_DATE = 22-06-1985;
• GENRE = M;
• ADDRESS_STREET = Rua Cravos Vermelhos;
• ADDRESS_NR = 255;
• ADDRESS_FLOOR_NR = NULL;
• ADDRESS_FLOOR_LETTER = NULL;
• ADDRESS_CITY = Caparica;
• REGION_LEVEL1 = NULL;
• REGION_LEVEL2 = NULL;
• ADDRESS_ZIP_NR = 2815-314;
• ADDRESS_ZIP_TEXT = Caparica;
• TELEPHONE_NR = NULL;
• MOBIPHONE_NR = NULL;
• EMAIL = NULL;
• FISCAL_NR = NULL;
• DOCUMENT_NR = 12871023;
• DOCUMENT_ISSUE_LOCATION = NULL;
• DOCUMENT_ISSU_DATE = 07-05-2001;
• DOCUMENT_TYPE_CODE = BI.
Como se pode analisar, foi atribuída uma requisição que não diz respeito ao cliente em questão.
Para detectar estas inconsistências foi utilizada seguinte query no SQL Navigator, representada
na Figura 4.30.
Com esta query foram detectados 387 277 registos de clientes que têm req-
uisições cujos campos pessoais não são iguais aos dos clientes, estes campos diferem uns dos
outros porque:
• Existem clientes que têm uma ou várias requisições que são de outros clientes, como se
pode ver na Tabela 4.12;
• Nos registos de clientes, os campos comuns estão preenchidos mas não o estão nos registos
das suas requisições, como se pode ver na Tabela 4.13;
• Existem registos na tabela Clientes cujos campos comuns estão escritos de uma forma e
nas respectivas requisições os mesmos estão escritos de outra forma, com por exemplo,
com falta de palavras, mal escritos, etc. Exemplos destes registos podem ser vistos na
Tabela 4.14.
59
Figura 4.30: Query para detectar campos de colunas redundantes com valores distintos.
Tabela 4.12: Amostra de clientes com requisições de outros clientes.
Tabela 4.13: Amostra de clientes, onde os campos em comum estão preenchidos na tabela Clientes, mas não o
estão na tabela Requisições.
60
Tabela 4.14: Amostra de clientes, cujos campos comuns na tabela Requisições estão mal escritos.
4.2.3
Análise dos dados através de regras de negócio
Esta secção irá analisar os dados segundo um conjunto de regras de negócio, que não foram respeitadas, são elas:
1. Datas de nascimento inferiores ao ano 1878 e superiores a 2004. Tal como referido na secção
4.2.3 existem datas de nascimento que não obedecem ao intervalo de tempo em que são válidas.
Para descobrir todas estas datas [1878-2004] foram utilizadas duas queries no SQL Navigator,
que podem ser visualizadas na Figura 4.31. Existem vinte e três registos de clientes que têm as
datas de nascimento inválidas, na Tabela 4.15 está uma amostra dessas datas. Para a tabela
Requisições existem trinta e um registos de requisições que têm também as datas inválidas, uma
amostra destas datas pode ser consultada na Tabela 4.16.
Figura 4.31: Queries para detectar datas de nascimento inválidas.
Tabela 4.15: Amostra dos resultados de datas inválidas na tabela Clientes.
Tabela 4.16: Amostra dos resultados de datas inválidas na tabela Requisições.
61
2. Datas de emissão de documentos de identificação inferiores a 1900 e superiores a 2008. Existem
datas de emissão de documentos de identificação que não obedecem ao intervalo de tempo
[1900 - 2008], sendo inválidas. Para descobrir todas estas datas foram utilizadas duas queries no
SQL Navigator. Existem sete registos de clientes que têm as datas de emissão dos respectivos
documentos de identificação dos clientes erradas, na Tabela 4.17 está uma amostra dessas datas.
Para a tabela Requisições existem catorze registos de requisições que têm as datas de emissão
inválidas, uma amostra destas datas pode ser consultada na Tabela 4.18.
Tabela 4.17: Amostra dos resultados de datas de emissão inválidas na tabela Clientes.
Tabela 4.18: Amostra dos resultados de datas de emissão inválidas na tabela Requisições.
3. Datas de requisições de cartões inferiores a 2001 e superiores a 2008. Existem requisições
que têm datas que não obedecem ao intervalo de tempo (2001 - 2008), sendo inválidas. Para
descobrir todas estas datas foram utilizadas duas queries no SQL Navigator. Existem quarenta
e seis registos de requisições que têm as datas inválidas, uma amostra destas datas pode ser
consultada na Tabela 4.19.
Tabela 4.19: Amostra dos resultados de datas de requisições inválidas.
4. Datas de nascimento posteriores às datas de emissão de documentos de identificação de clientes.
Para descobrir esta anomalia foi realizada uma query no SQL Navigator, que pode ser visualizada
na Figura 4.32. Existem três mil quarenta e dois registos de clientes onde as datas de nascimento
são posteriores às datas de emissão dos documentos de identificação, na Tabela 4.20 está uma
amostra dessas datas. Para a tabela Requisições existem sete requisições que têm também a
anomalia referida anteriormente, uma amostra das datas pode ser consultada na Tabela 4.21.
62
Figura 4.32: Query para detectar datas de nascimento posteriores a datas de emissão de documentos de
identificação.
Tabela 4.20: Amostra dos resultados de datas de nascimento posteriores às datas de emissão na tabela Clientes.
Tabela 4.21: Amostra dos resultados de datas de nascimento posteriores às datas de emissão na tabela
Requisições.
5. A data de nascimento de um cliente é mais recente do que a data em que o mesmo fez um
pedido de requisição. Para descobrir esta anomalia foi realizada uma query no SQL Navigator.
Existem trezentos e dez registos de clientes onde a data de nascimento é posterior à data de uma
requisição, na Tabela 4.22 está uma amostra dessas datas.
Tabela 4.22: Amostra dos resultados de datas de nascimento posteriores às datas de requisições.
6. Datas de emissão de documentos de identificação mais recentes que a data do pedido de requisição realizado pelo cliente. Novamente foi realizada uma query, para descobrir esta anomalia.
Existem dezanove mil trezentos e setenta registos de clientes onde a data de emissão é posterior
à data de uma requisição, na Tabela 4.23 está uma amostra dessas datas.
7. A existência de clientes com cartões que não são compatíveis com a sua idade. Existem clientes
que possuem um cartão de transporte que não está de acordo com a idade que os mesmos têm.
O negócio da operadora de transportes (OT) indica que um cliente tem um cartão segundo um
perfil:
63
Tabela 4.23: Amostra dos resultados de datas de emissão de documentos de identificação posteriores às datas
de requisições.
• ’Criança’ - Para os clientes com idade compreendida entre os 4 e os 13 anos;
• ’Normal’ - Para os clientes com idade compreendida entre os 14 e os 64 anos;
• ’Terceira idade’ - Para os clientes com idade igual ou superior a 65 anos.
Existem outros perfis que não foram aqui referidos, porque não são atribuídos a um cliente segundo a idade do mesmo. Por exemplo, um cliente com data de nascimento ’12-03-2003’ (5 anos)
com um perfil de ’Normal’, não está correcto. Para detectar estes erros foi realizada uma query
na ferramenta SQL Navigator, representada na Figura 4.33. Com esta query foram detectados
6149 registos em que os clientes têm um perfil de cartão que não corresponde a sua idade. Na
Tabela 4.24 pode-se visualizar uma amostra com estes erros.
Figura 4.33: Query para detectar clientes com perfis incorrectos.
Tabela 4.24: Amostra de clientes com cartões que não correspondem ao seu perfil.
E foram estas as principais violações de regras de negócio encontradas nas tabelas Clientes, Requisições e Cartões.
64
4.2.4
Análise de registos aproximadamente duplicados
Esta última fase da metodologia de data profiling, permite verificar se existem entidades aproximadamente duplicadas, tal como já foi referido na secção 3.4. No caso da BD_OT este problema pode ser
encontrado na tabela Clientes, onde todos os registos de clientes deviam de se referir a pessoas distintas no mundo real, mas quando observados em pormenor, alguns referem-se a um cliente já existente.
Para detectar esta anomalia foi utilizada a ferramenta ODI-DQ. Criou-se um projecto de qualidade de
dados e utilizou-se os processos disponíveis para a detecção deste problema. Toda a detecção de registos aproximadamente duplicados pode ser consultada no anexo 6.2. Devido a problemas de memória,
a detecção de duplicados foi aplicada a uma amostra da tabela clientes. A tabela Clientes tem cerca
de 1,3 milhões de registos e a amostra onde foi realizada a detecção de registos aproximadamente
duplicados tem 250 mil registos. De seguida serão explicadas as etapas mais importantes na detecção
de clientes duplicados, para visualizar todo o processo consultar o anexo 6.2. Em primeiro lugar foi
necessário conseguir uma chave que permitisse detectar o maior número de registos duplicados. Segundo o negócio que gere a BD_OT, um cliente é distinguido pelo tipo de documento de identificação
complementado pelo identificador do documento e data de nascimento. Mas os campos que representam estes três factores de negócio também têm elementos duplicados. Desta forma foi necessário
utilizar outros campos que em conjunto permitissem detectar duplicados. As regras que constituem
a chave utilizada podem ser visualizadas nas Figuras: 4.34, 4.35, 4.36 e 4.37. Não se pode afirmar
que se consigua agrupar na totalidade todos os registos duplicados, mas consegue-se agrupar a maior
parte. Com a chave referida em cima, foram detectados cerca de 25 mil registos aproximadamente
duplicados. Na tabela 4.25 pode-se verificar uma pequena amostragem do que foi detectado.
Figura 4.34: Campos da primeira regra para a formação da chave de detecção.
65
Figura 4.35: Campos da segunda regra para a formação da chave de detecção.
Figura 4.36: Campos da terceira regra para a formação da chave de detecção.
Tabela 4.25: Amostra de clientes duplicados.
4.3
Avaliação das ferramentas de Data Profiling da Oracle
Nesta última secção deste capítulo vão ser descritas as características das duas ferramentas de data
profiling da Oracle: Oracle Warehouse Builder (OWB) - Módulo DP e Oracle Data Quality for Data Integrator (ODI-DQ), utilizadas na auditoria da qualidade de dados da BD_OT. Vão ser mostrados quadros
onde se pode verificar se todas as actividades de cada etapa da metodologia de DP estão contempladas
em cada ferramenta enunciada em cima. No fim é feita uma comparação entre as duas ferramentas.
66
Figura 4.37: Campos da última regra para a formação da chave de detecção.
4.3.1
OWB - Módulo DP
Oracle Warehouse Builder (OWB) é uma ferramenta de ETL (Extract, Transform and Load) da Oracle,
que suporta a integração de dados e a gestão de actividades dos mesmos, tais como [64]:
• Extracção, transformação e carregamento de dados para data warehouses;
• Consolidação dos dados vindos de fontes distintas;
• Migração de dados de sistemas legados;
• Modelação de dados de estruturas relacionais e dimensionais;
• Desenho e gestão de metadados de empresas;
• Limpeza de dados de forma a maximizar a qualidade de informação;
• Data profiling e auditoria da qualidade dos dados.
O OWB disponibiliza um conjunto de módulos que permitem assegurar a qualidade dos dados que
foram movidos de diferentes fontes para um determinado data warehouse. O data profiling é um módulo
que permite descobrir inconsistências e anomalias nas fontes de dados e que posteriormente corrige
as mesmas [30]. Através das tabelas 4.26 e 4.27 pode-se verificar que actividades da ferramenta OWB
- Módulo DP (descritas no anexo 6.3.1), estão contempladas nas diferentes etapas da metodologia de
DP (descrita na secção 3). Os requisitos mínimos de hardware, exigidos para a instalação do OWB e
para o sistema operativo Windows, são [65]:
• Para as bases de dados com mais de dez milhões de registos é necessário que a máquina que
disponibiliza a mesma disponha de múltiplos processadores;
• O espaço em disco precisa de ser pelo menos 700 MB;
• Em termos de memória, deve de existir pelo menos 500 MB, para melhores performances é
necessário 2 a 3 GB de memória.
67
Tabela 4.26: Actividades da ferramenta OWB - DP que se encontram nas várias etapas da metodologia DP (1).
68
Tabela 4.27: Actividades da ferramenta OWB - DP que se encontram nas várias etapas da metodologia DP (2).
69
4.3.2
ODI-DQ
Oracle Data Integration é uma plataforma de integração de dados. Fornece uma solução completa
para construir, carregar e gerir complexos data warehouses. Adicionalmente, a plataforma ajuda na
migração automática dos dados e assegura-se de que a informação que existe entre os vários sistemas complexos é actual, precisa e consistente [66]. O Oracle Data Profiling e o Data Quality for
Data Integrator são uma extensão das funcionalidades de Data Quality do Oracle Data Integration que
fornecem uma avançada gestão dos dados. O Oracle Data Profiling é uma ferramenta de investigação
e monitorização da qualidade dos dados. O Oracle Data Quality for Data Integrator detém um forte
motor baseado em regras, cuja arquitectura robusta e escalável coloca o Data Quality e a limpeza de
nomes e moradas no centro da estratégia de integração de dados das empresas [67]. Através das
tabelas 4.28, 4.29, 4.30 e 4.31 pode-se verificar que actividades da ferramenta ODI-DQ (descritas no
anexo 6.3.2) estão contempladas nas diferentes etapas da metodologia de DP (descrita na secção 3).
Os requisitos mínimos de hardware, exigidos para a instalação do ODI-DQ, para o sistema operativo
Windows, são [68]:
• O espaço em disco deve ser de 3Gb a 7,5Gb de espaço livre por cada 1Gb de dados a analisar;
• Em termos de memória, deve de existir pelo menos 2 GB de memória;
• Uma ligação de rede TCP/IP a 100MB/s.
4.3.3
OWB - Módulo DP versus ODI-DQ
Ao longo da aprendizagem e da realização da auditoria de qualidade de dados da BD_OT, foram notadas desvantagens não só em ambas as ferramentas como também desvantagens de uma ferramenta
sobre outra. Esta secção vai descrever estas desvantagens, e informar alguns dos tempos de execução de cada ferramenta perante a análise dos dados. De seguida são descritas as desvantagens
detectadas em ambas as ferramentas:
• A Oracle dispõe de muita pouca informação sobre as duas ferramentas, como por exemplo: Manuais, tutoriais, exemplos de utilização, fóruns, etc. Para que a compreensão das várias actividades
das ferramentas seja possível e esclarecedora;
• A impossibilidade de se conseguir realizar queries livres sobre os vários elementos da BD a
analisar. O ODI-DQ não permite de todo e o OWB - Módulo DP só permite modificar a claúsula
where que incide sempre sobre uma só tabela;
• Nem uma nem outra permitem a detecção de anomalias através de dicionários de palavras, pois
não têm funcionalidade para tal;
• Não produzem relatórios dos resultados obtidos da detecção dos vários erros, de forma a que um
utilizador comum olhe e veja de uma forma rápida e simples os principais erros detectados nos
dados.
Pode-se enumerar também um conjunto de desvantagens de uma ferramenta em relação a outra:
• Em relação a detecção da data mais recente ou mais antiga de um campo constituído por datas, o
ODI-DQ não revela esta informação como deve de ser, pois indica a data mais recente/antiga por
70
Tabela 4.28: Actividades da ferramenta ODI-DQ que se encontram nas várias etapas da metodologia DP (1).
ordem alfabética e não por ordem de tempo, vê a data como uma string. No OWB - Módulo DP
este indica bem as datas mas não mostra os quatro dígitos do ano, levando assim a uma confusão
por parte do utilizador. Por exemplo: A data ’23-Jan-1987’ é para o OWB como ’23-Jan-87’;
• O OWB - Módulo DP não permite detectar registos aproximadamente duplicados e o ODI-DQ
permite na solução de Data Quality ;
• O ODI-DQ não detecta colunas sinónimas (uma das funcionalidades da etapa 2 da metodologia
de DP) e o OWB - Módulo DP sim;
71
Tabela 4.29: Actividades da ferramenta ODI-DQ que se encontram nas várias etapas da metodologia DP (2).
• O OWB - Módulo DP revela só os padrões dominantes de uma coluna, enquanto que o ODI-DQ
revela todos os que encontrou;
• Os resultados das várias detecções feitas pelas ferramentas podem ser visualizados através de
gráficos. O ODI-DQ só disponibiliza um tipo (diagrama venn para explicação das chaves estrangeiras), enquanto que o OWB - Módulo DP disponibiliza também este tipo de gráfico e mais
72
Tabela 4.30: Actividades da ferramenta ODI-DQ que se encontram nas várias etapas da metodologia DP (3).
um, onde se pode visualizar, os resultados através de frequências dos dados em cada etapa da
metodologia;
• A instalação e configuração do OWB é demorada (leva cerca de um dia) e com passos muito
pouco explícitos. A sequência de passos pode ser visualizada no site [69]. O ODI-DQ não tem
este problema, a sua instalação e configuração é simples e rápida (levando cerca de duas horas);
• Se por algum motivo a aplicação ODI-DQ não é fechada pelos métodos normais, na primeira vez
que o utilizador tentar abrir a mesma não é possível porque pode dar um de dois erros:
– ’Oracle DQ - Scheduler. Error in starting the service. The service TSS - Scheduler does not
exist’. Este problema só foi possível de resolver com uma formatação do pc;
– ’Server connection failed. Invalid metabase. Error: Failed to open the locking environment
error: BDB_Env::open:Resource temporarily unavailable unable to join the environment’. A
solução para este problema foi a instalação de novo da aplicação ODI.
Esta situação não afecta a aplicação OWB;
• Tal como se pode ver na tabela 4.32 o OWB - Módulo DP demora muito mais tempo (cerca de 1
dia) a analisar a mesma informação que o ODI-DQ (cerca de 2 horas).
73
Tabela 4.31: Actividades da ferramenta ODI-DQ que se encontram nas várias etapas da metodologia DP (4).
Tabela 4.32: Amostra dos tempos de execução das duas ferramentas de DP sobre as principais tabelas da
BD_OT.
74
5 Conclusões
Nesta dissertação realizou-se uma auditoria de qualidade de dados a uma base de dados de cartões
de clientes de operadores de transporte.
Raramente os dados manipulados por um SI de grande dimensão se podem classificar como sendo
100% de boa qualidade. O grande objectivo não é garantir que a qualidade seja perfeita, mas que seja
suficiente para que a organização possa sobreviver e tomar decisões com base em dados fiáveis. Desta
forma para se saber o estado dos dados é necessário realizar-se sempre uma auditoria de qualidade de
dados através da metodologia de DP, que de uma forma completa e sistemática detecta o maior número
de erros possíveis em qualquer BD relacional. No Capítulo 1, foi enumerado um conjunto de problemas
de qualidade de dados que se pode encontrar em qualquer BD dos SI mencionados em cima. Perante
este conjunto de problemas e outros que ocorrem sem o conhecimento do utilizador que lida com a BD,
existe a necessidade de utilizar o data profiling para detectar estes problemas.
O DP é um tema muito recente. As suas funções são imprescindíveis para que se saiba o que se passa
com os dados de qualquer aplicação. Actualmente com a sofisticação das ferramentas de DP já não
há razão para que se perca tempo a voltar atrás num projecto, porque a aplicação a desenvolver não
está preparada para aquele tipo de dados. No Capítulo 2 é apresentado um conjunto de ferramentas
existentes no mercado, criadas para este efeito. Todas as ferramentas apresentam actividades que
detectam à sua maneira um conjunto de erros que podem ocorrer nos dados de uma BD. Há ainda
actividades que as ferramentas de DP não detêm. Uma dessas actividades é a detecção de registos
aproximadamente duplicados, como é uma actividade bastante complexa, foi feito um estudo de possíveis algoritmos de detecção de duplicados que existem.
No Capítulo 3 é apresentada uma metodologia de DP que permite realizar uma auditoria de qualidade
de dados de uma forma rigorosa e completa, aplicando sistematicamente um conjunto de funcionalidades apropriadas a cada elemento da BD. Esta metodologia foi baseada na do autor autor Jack Olson
[2] e reformulada com funcionalidades que a anterior não dispunha.
No Capítulo 4 é apresentado o estudo de caso a analisar. A BD_OT dispõe de três tabelas principais
(Clientes, Requisições e Cartões), sobre as quais se realizou uma auditoria de qualidade de dados.
Nesta auditoria foram aplicadas as várias etapas da metodologia do Capítulo 3 auxiliadas por duas
ferramentas de DP da Oracle: o ODI-DQ e o OWB - Módulo DP. No fim deste capítulo é feita uma
comparação entre estas duas ferramentas, chegando-se à conclusão que apesar de uma e de outra
terem desvantagens, a escolha entre cada um é feita com base na funcionalidade necessária em cada
momento.
A auditoria indicada em cima foi um processo iterativo que demorou cerca de seis meses a ser feito.
No período referido, houve um esforço considerável, no sentido de satisfazer da melhor forma as necessidades identificadas pelo cliente, procurando reportar o maior número de erros, anomalias e inconsistências possíveis de encontrar no tempo referido. Inicialmente os dados a analisar eram de
teste, permitindo uma adaptação às ferramentas e a verificação de que as mesmas, correspondiam ou
não aos objectivos definidos. Uma vez concluída a fase de teste das ferramentas, foi disponibilizado
um conjunto considerável de dados de produção, onde foi realizada a auditoria. Convém referir que
75
teoricamente esta análise poderia ter continuado por tempo indefinido, pois estão sempre a ser inseridos, apagados e modificados dados da BD, tornando difícil a medição em cada instante, da qualidade
dos dados. Concluiu-se que a partir de um determinado ponto, continuar o processo de detecção de
anomalias nos dados, não compensa, pois existia o risco de esta análise não terminar.
5.1
Trabalho Futuro
Apesar de todo o trabalho desenvolvido e referido nos capítulos acima, existem melhorias a fazer nos
seguintes pontos:
1. A metodologia de DP enunciada ainda não consegue detectar os seguintes problemas de dados:
• Verificar se cada valor de um campo, que aparentemente não tem anomalias, é o não válido.
Por exemplo: Um empregado tem 31 anos no mundo real, mas na realidade o sistema diz
que o mesmo tem 45;
• Verificar dados ambíguos. São dados que podem ter vários significados. Podem ser encontrados em abreviaturas ou em contextos incompletos. Por exemplo, o valor ’J. Guerra’ tanto
pode ser interpretado como João Guerra ou como Joaquim Guerra;
• Verificar se um valor que falta numa coluna (null) é realmente um erro, ou não o é porque
no mundo real não existe esse valor. Por exemplo: Um cliente pode não ter um endereço de
email, logo a ausência deste valor não pode ser considerada um erro;
Seria interessante no futuro descobrir funcionalidades que pudessem detectar as situações referidas anteriormente;
2. As ferramentas de DP ainda não dispõem das funcionalidades enunciadas a seguir:
• Realização de expressões regulares e queries livres;
• Detecção de registos aproximadamente duplicados;
• Detecção de dados errados através da comparação dos mesmos com um dicionário válido
para o domínio em análise;
• Geração de um relatório, completo e simples de visualizar por qualquer utilizador, com todas
as anomalias detectadas nos dados.
Era importante que no futuro as funcionalidades descritas anteriormente fossem integradas nas
ferramentas de DP, pois desta forma evita o incómodo da utilização de várias ferramentas com
o mesmo objectivo, mas com funcionalidades distintas, numa mesma auditoria de qualidade de
dados;
3. Embora tenham sido vários os problemas encontrados na BD_OT, ainda faltam de certeza um
conjunto de erros a detectar, porque a detecção foi só aplicada às três tabelas principais. De
futuro e com mais tempo é necessário analisar toda a BD_OT para que o analista tenha noção da
maioria dos problemas que esta BD tem;
4. A auditoria de qualidade de dados das três principais tabelas da BD_OT foi realizada e reportou
um conjunto de erros, anomalias e inconsistências, que de futuro precisam de ser corrigidas. A
correcção dos dados não é uma actividade do DP, mas deve de ser executada sempre que são
76
detectados erros nos dados, pois caso contrário cada vez vão ser mais os erros que ao longo do
tempo se formam. Desta forma vão gerando cada vez mais informação incorrecta para todas as
aplicações e utilizadores desta BD. É aconselhado que no futuro, se proceda a uma correcção
dos erros detectados através de uma ferramenta de Data Cleaning, ou de outros meios com o
mesmo fim. O ciclo detecção e correcção deve ser executado de forma iterativa até que o analista
verifique que os dados estão com uma qualidade razoável.
77
Referências
[1] W.
Eckerson,
“Data
profiling:
A
tool
worth
buying
http://www.dmreview.com/issues/20040601/1003990-1.html,
(really!),”
DM
[Citação:
Review,
2004.
22 de Setembro de
2008].
[2] J. E. Olson, Data Quality: The Accuracy Dimension. Morgan Kaufmann, 1a ed., Dezembro 2002.
[3] B. McGyver, Named Entity Tutorial - Rule-Based Named Entity Detection. Alias-I, 2008. http://aliasi.com/lingpipe/demos/tutorial/ne/read-me.html, [Citação: 17 de Setembro de 2008].
[4] C. Batini, Data Quality: Concepts, Methodologies and Techniques (Data-Centric System and Applications). Springer, New York, 1a ed., 1998.
[5] B. Data Quality, “Data profiling best practices,” 2007.
http://www.businessdataquality.com/site_files/BDQ%20Data%20Profiling%20Paper%20v1.2.pdf,
[Citação: 4 de Agosto de 2008].
[6] P. Russom, “Unifying the practices of data profiling, integration, and quality (dpiq),” TDWI Monograph Series, Outubro 2007. http://www.dataflux.com/Resources/resource-list.asp?v=sp, [Citação:
4 de Agosto de 2008].
[7] Novabase,
“Qualidade
de
dados
e
crm
porquê?,”
2002.
http://www.novabase.pt/showNews.asp?idProd=resqdadoscrm, [Citação: 4 de Agosto de 2008].
[8] Wikipedia, “Data quality,” 2008. http://en.wikipedia.org/wiki/Data_quality, [Citação: 4 de Agosto de
2008].
[9] J. Barateiro and H. Galhardas, “A survey of data quality tools,” 2005.
http://www.datenbank-
spektrum.de/pdf/dbs-14-15.pdf, [Citação: 4 de Agosto de 2008].
[10] Y.
Wand
tological
and
R.
foundations,”
Wang,
“Anchoring
Communications
data
of
the
quality
dimensions
ACM,
http://web.mit.edu/tdqm/www/tdqmpub/WandWangCACMNov96.pdf,
in
Novembro
[Citação:
on1996.
4 de Agosto
de 2008].
[11] L. L. Pipino, Y. W. Lee, and R. Y. Wang, “Data quality assessement,” ACM, Abril 2002.
http://web.mit.edu/tdqm/www/tdqmpub/PipinoLeeWangCACMApr02.pdf, [Citação: 4 de Agosto de
2008].
[12] E.
C.
onomic
Dalcin,
“Data
databases,”
quality
School
concepts
of
and
Biological
techniques
applied
Sciences,
Fevereiro
to
tax2005.
http://www.dalcin.org/eduardo/downloads/edalcin_thesis_submission.pdf, [Citação: 4 de Agosto
de 2008].
78
[13] H. Veregin, “Data quality measurement and assessment,” NCGIA Core Curriculum in GIScience,
Março 1998.
http://www.ncgia.ucsb.edu/giscc/units/u100/u100.html, [Citação: 4 de Agosto de
2008].
[14] T. Dasu and T. Johnson, Exploratory Data Mining and Data Cleaning. Wiley, 2003.
[15] A. Mansingh and S. Srikant, “Data profiling in a compliance world,” DMReview.com, Outubro 2005.
http://www.dmreview.com/issues/20051001/1038096-1.html, [Citação: 4 de Agosto de 2008].
[16] Wikipedia, “Data profiling,” 2008. http://en.wikipedia.org/wiki/Data_profiling, [Citação: 4 de Agosto
de 2008].
[17] B. Marshall, “Data quality and data profiling, a glossary,”
http://www.telusplanet.net/public/bmarshal/dataqual.htm#DATA_PROFILING,
[Citação:
4
de
Agosto de 2008].
[18] R. Kimball, “Kimball design tip #59: Surprising value of data profiling,” Kimball Group, Setembro,
2004.
http://www.rkimball.com/html/designtipsPDF/KimballDT59SurprisingValue.pdf, [Citação:
4 de
Agosto de 2008].
[19] R. Sherman, “Five steps for weaving data quality management into your enterprise data integration
processes,” Data Management.com, 2007.
http://searchdatamanagement.techtarget.com/tip/0,289483,sid91_gci1256080,00.html,
[Citação:
27 de Agosto de 2008].
[20] T. Friedman, M. A. Beyer, and A. Bitterer, “Magic quadrant for data quality tools,” 2007.
http://mediaproducts.gartner.com/reprints/businessobjects/149359.html, [Citação: 4 de Agosto de
2008].
[21] T. Friedman, M. A. Beyer, and A. Bitterer, “Magic quadrant for data quality tools,” Junho 2008.
http://www.dataflux.com/Resources/thankyou.asp?a=dl&rid=232, [Citação: 4 de Agosto de 2008].
[22] W. Eckerson, “Data profiling: Minimizing risk in data management projects,” Data Warehousing Institute, 2003. http://www.dataflux.com/Resources/file-stream.asp?rid=52, [Citação: 22 de Setembro de 2008].
[23] P. B. G. . Software, “Data quality connector,” 2007. http://www.g1.com/Resources/Demos/DQC/index.html,
[Citação: 22 de Setembro de 2008].
[24] S. Power, “Power architect,” http://www.sqlpower.ca/page/architect, [Citação: 22 de Setembro de
2008].
[25] Daitiris, “Daitiris profiler,” 2007. http://www.datiris.com/index.shtml, [Citação: 22 de Setembro de
2008].
79
[26] Microsoft, “Sql server 2005 - integration services,” 2007.
http://msdn2.microsoft.com/enus/library/aa964137.aspx#ssis_dqs_topic4, [Citação: 22 de Setembro de 2008].
[27] Microsoft, “Sql server 2005 - integration services,” 2007. http://www.jumpstarttv.com/Media.aspx?vid=72,
[Citação: 22 de Setembro de 2008].
[28] A. Technology, “Aggregate profiler,” 2007. http://www.arrah.in/, [Citação: 22 de Setembro de 2008].
[29] Oracle, 10 Understanding Data Quality Management - Oracle Warehouse Builder User’s Guide 10G
Release 2. Oracle, 2006. http://download.oracle.com/docs/cd/B31080_01/doc/owb.102/b28223/
concept_data_quality.htm, [Citação: 15 de Setembro de 2008].
[30] Oracle, “20 ensuring data quality - oracle warehouse builder user’s guide 10g release 2,” 2006.
http://download.oracle.com/docs/cd/B31080_01/doc/owb.102/b28223/ref_dataprofiling.htm#CEGBHCCF,
[Citação: 15 de Setembro de 2008].
[31] T. Software,
“Data profiling,”
2007.
http://www.trilliumsoftware.com/home/products/data-
profiling.aspx, [Citação: 22 de Setembro de 2008].
[32] IBM, “Websphere information analyzer,” 2007.
http://publib.boulder.ibm.com/infocenter/iisinfsv/v8r0/index.jsp?topic=/com.ibm.swg.im.
iis.productization.iisinfsv.overview.doc/topics/cisoiacloser.html, [Citação: 22 de Setembro de 2008].
[33] IBM, “Websphere information analyzer,” 2007.
http://www-01.ibm.com/common/ssi/cgibin/ssialias?subtype=ca&infotype=an&appname
=iSource&supplier=897&letternum=ENUS207-043, [Citação: 22 de Setembro de 2008].
[34] B. Objects, “Data integrator,” 2008.
http://www.businessobjects.com/pdf/products/dataintegration/data_integrator.pdf, [Citação: 16 de
Setembro de 2008].
[35] DataFlux, “Df power profiler,” http://www.dataflux.com/Resources/flash/profiledemo.html, [Citação:
16 de Setembro de 2008].
[36] A. K. Elmagarmid, P. G. Ipeirotis, and V. S. Verykios, “Duplicate record detection: A survey,” IEEE
Transactions on Knowledge and Data Engineering, 2007.
[37] J. K. Newcombe and J. S. Axford, “Automatic linkage of vital records,” Science, 1959.
[38] P. F. I. and B. S. A., “A theory for record linkage,” Journal of the American Statistical Association,
1969.
[39] J. Liang, C. Li, and S. Mehrotra, “Efficient record linkage in large data sets,” 8th Annual International
Conference on Database Systems for Advanced Applications, 2003.
http://flamingo.ics.uci.edu/pub/dasfaa03.pdf, [Citação: 22 de Setembro de 2008].
80
[40] R. Ananthakrishna, S. Chaudhuri, and V. Ganti, “Eliminating fuzzy duplicates in data warehouses,” In Proceedings of the 28th International Conference on Very Large Data Bases, 2002.
http://www.vldb.org/conf/2002/S17P01.pdf, [Citação: 22 de Setembro de 2008].
[41] S. Sarawagi and A. Bhamidipaty, “Interactive deduplication using active learning,” Very Large, Data
Bases 02, 2002. http://portal.acm.org/citation.cfm?id=775087, [Citação: 22 de Setembro de 2008].
[42] E. Agichtein and V. Ganti, “Mining reference tables for automatic text segmentation,” In Proceedings
of the Tenth ACM SIGKDD International Conference on Knowledge Discovery and Data Mining
(KDD-2004), 2004.
[43] V. R. Borkar, K. Deshmukh, and S. Sarawagi, “Automatic segmentation of text into structured
records,” In Proceedings of the 2001 ACM SIGMOD International Conference on Management
of Data (SIGMOD 2001), 2001.
[44] A. Mcallum, D. Freitag, and F. C. N. Pereira, “Maximum entropy markov models for information
extraction and segmentation,” In Proceedings of the 17th International Conference on Machine
Learning (ICML 2000), 2000.
[45] V. Raman and J. M. Hellerstein, “Potter’s wheel: An interactive data cleaning system,” In Proceedings of the 27th International Conference on Very Large Databases (VLDB 2001), 2001.
[46] C. Sutton, K. Rohanimanesh, and A. Mccallum, “Dynamic conditional random fields: Factorized
probabilistic models for labeling and segmenting sequence data,” In Proceedings of the 21st International Conference on Machine Learning (ICML 2004), 2004.
[47] W. E. Winkler, “Methods for record linkage and bayesian networks,” American Statistical Association, 2002. http://www.cs.cmu.edu/w̃cohen/matching/WinklerAsa02.pdf, [Citação: 22 de Setembro
de 2008].
[48] L. M. Leitão, “Detecção de duplicados em bases de dados xml,” Dissertação para Obtenção do
Grau de Mestrado, 2007.
[49] M.
large
A.
Hernández
databases.,”
and
S.
J.
Stolfo,
Proceedings
of
the
“The
ACM
merge/purge
SIGMOD
problem
Conference,
http://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.55.2700, [Citação:
for
1995.
11 de Agosto de
2008].
[50] S. Chaudhuri, V. Ganti, and R. Motwani, “Robust identification of fuzzy duplicates,” In Proceedings
of the International Conference on Data Engineering (ICDE), 2005.
[51] A. Silberschatz, H. F. Korth, and S. Sudarshan, Database System Concepts. McGraw-Hill Publishing Co., 5th ed., August 2005.
[52] R. Ramakrishnan and J. Gehrke, Database Management Systems. McGraw Hill Higher Education,
3rd ed., November 2002.
81
[53] P. J. Oliveira, F. Rodrigues, and P. R. Henriques, “Limpeza de dados - uma visão geral,”
http://wiki.di.uminho.pt/twiki/pub/Research/Doutoramentos/SDDI2004/ArtigoOliveira.pdf, [Citação:
11 de Agosto de 2008], 2004.
[54] A. República, “Lei no 7/2007 - cartão de cidadão,” Diário da República, 2007.
http://www.anacom.pt/streaming/lei_7.2007.pdf?categoryId=94780
&contentId=461513&field=attached_file, [Citação:16 de Agosto de 2008].
[55] A. República, “Lei da identificação civil,” Diário da República, 1999.
http://www.dgrn.mj.pt/legislacao/lei_id_civil/lei_ident_civ.htm, [Citação: 16 de Agosto de 2008].
[56] M. A. Interna, “Decreto-lei no 15/2002,” Diário da República, 2002.
http://www.proteccaocivil.pt/Legislacao/Documents/Agentes%20PC/DL%20152002%20altera%20estatutos%20GNR.pdf, [Citação:16 de Agosto de 2008].
[57] D. G. Viacção, “Decreto-lei no 45/2005,” Diário da República, 2005.
http://www.dgv.pt/UpLoadedFiles/Dl_45_05.pdf, [Citação:16 de Agosto de 2008].
[58] I. E. F. Profissional, “Check-list,”
http://portal.iefp.pt/pls/gov_portal_iefp/docs/page/eures/eures_pag_portugues/
informacoes_uteis/checklist.pdf, [Citação: 16 de Agosto de 2008].
[59] Governo, “Comunicado do conselho de ministros de10 de agosto de 2006,”
http://www.portugal.gov.pt/portal/pt/governos/governos_constitucionais/gc17/conselho
_de_ministros/comunicados_e_conferencias_de_imprensa/20060810.htm, [Citação: 16 de Agosto
de 2008], 2006.
[60] Wikipedia, “Passaporte português,” http://pt.wikipedia.org/wiki/Passaporte_portugu%C3%AAs,
[Citação: 16 de Agosto de 2008], 2008.
[61] Wikipedia, “Visto,” http://pt.wikipedia.org/wiki/Visto, [Citação: 16 de Agosto de 2008], 2008.
[62] F.
S.
Europeu,
“O
fundo
social
europeu
em
portugal,
http://ec.europa.eu/employment_social/esf/members/pt_pt.htm, [Citação:
2007-2013,”
23 de Setembro de
2008].
[63] CTT, “Pesquisar código postal,”
http://www2.ctt.pt/feapl/jsp/postalcodesearch/public/postalcodesearch.jsf, [Citação: 4 de Agosto de
2008].
[64] O. T. Network, “Oracle warehouse builder 11g,” 2008.
http://www.oracle.com/technology/products/warehouse/index.html, [Citação: 16 de Setembro de
2008].
[65] Oracle, Oracle Warehouse Builder User’s Guide 10g Release 2.
Oracle, Novembro 2006.
http://download.oracle.com/docs/cd/B31080_01/doc/owb.102/b28223.pdf, [Citação: 16 de Setembro de 2008].
82
[66] Oracle, “Oracle data integrator,” 2008. http://www.oracle.com/products/middleware/odi/index.html,
[Citação: 16 de Setembro de 2008].
[67] Oracle, “Oracle data profiling and data quality for data integrator,” 2008.
http://www.oracle.com/products/middleware/oracle-data-quality.html, [Citação: 16 de Setembro de
2008].
[68] Oracle, Oracle Data Integrator Installation Guide, 10g Release 3. Oracle, 2006.
http://www.oracle.com/technology/products/oracle-data-integrator/10.1.3/htdocs/
documentation/oracledi_setup.pdf, [Citação: 16 de Setembro de 2008].
[69] Oracle,
Setting
up
the
Oracle
Warehouse
Builder
Project.
Oracle,
2006.
http://www.oracle.com/technology/obe/11gr1_owb/owb11g_update_extend_knowledge/
less1_setting_up/less1_setting_up.html, [Citação: 16 de Setembro de 2008].
[70] Oracle, Oracle Data Profiling and Oracle Data Quality for Data Integrator Sample Tutorial, 10g
Release 3 (10.1.3) - Introduction to Oracle Data Quality Products.
Oracle, November 2007.
http://www.oracle.com/technology/products/oracle-data-quality/pdf/oracledq_tutorial.pdf, [Citação:
11 de Setembro de 2008].
[71] Oracle, Oracle Data Profiling and Oracle Data Quality for Data Integrator Help. Oracle, 2007.
83
6 Anexos
6.1
Magic Quadrant for Data Quality Tools, 2007 and 2008
Figura 6.1: Magic Quadrant for Data Quality Tools 2007.
Figura 6.2: Magic Quadrant for Data Quality Tools 2008.
84
6.2
Detecção de registos aproximadamente duplicados no ODI-DQ
A detecção de registos aproximadamente duplicados foi aplicada na tabela Clientes e para a sua realização foi utilizada a ferramenta Oracle Data Quality for Data Integrator (ODI-DQ). A detecção de
duplicados no ODI-DQ é realizada num projecto do tipo Quality, em três dos seus processos (Window
Key Generator, Sort for Linking e Relationship Linker ). No total existem cinco processos, mas como
não se pode executar independentemente cada processo é necessário configurar todos que precedem
os que permitem a detecção de registos duplicados. [70].
No início é necessário adicionar a entidade Clientes à aplicação, para posteriormente usar nos projectos de Profiling e nos de Quality. De seguida é criado um novo projecto de qualidade de dados, que
pode ser do tipo Name & Address Project ou Business Data Project. Foi escolhida a primeira opção,
mas qualquer uma delas permite a detecção de duplicados. Na criação do novo projecto é necessário
escolher o país de origem dos dados, para que posteriormente o processo que vai analisar as moradas
o consiga fazer com base no país de origem. Com o projecto criado, são apresentados os estados e
processos por onde os dados passam até a aplicação reportar se existem duplicados, como se pode
ver na Figura 6.3. Os estados aqui representam as tabelas de dados de entrada/saída dos processos,
e os processos são os seguintes:
Figura 6.3: Projecto de qualidade de dados para a detecção de registos duplicados.
Transformer
Suporta um conjunto de funcionalidades que permitem alterar o valor de um ou vários campos de uma
tabela ou tabelas. Neste processo definem-se quais são os campos que o próximo processo irá analisar, através da funcionalidade Parser Inputs, representada na Figura 6.4, onde se definem os campos
que detêm o nome e a morada dos clientes. Depois de definir os campos (nome e morada de cliente),
85
é necessário correr este processo, para que o seu output seja usado como input no próximo processo
[71].
Figura 6.4: Transformer - Parser Inputs.
Customer Data Parser
Tem como objectivo reportar um conjunto de erros segundo a análise dos campos nome e/ou moradas
dos clientes, indicando se os mesmos estão bem escritos e se existem segundo a lista de nomes e
moradas do país de origem. É também neste processo que são criados todos os campos que os processos seguintes irão necessitar para demonstrar a sua análise.
Este processo gera um campo na tabela de output (pt Curparse p2), designado PR_NAME_FORM_01,
que categoriza os registos. Esta categorização é usada pelo processo que detecta registos duplicados
(Relationship Linker ), e indica quais os registos que contêm nomes de pessoas, nomes de empresas
ou erros (quando a aplicação não consegue afirmar se são nomes de pessoas ou de empresas). O
Relationship Linker detecta registos duplicados, mas fá-lo em dois grupos distintos, no primeiro grupo
compara os registos que contêm os nomes de empresas, e no segundo os registos de nomes de pessoas. É daqui que surge a necessidade do campo PR_NAME_FORM_01, e a importância de ter todos
os registos num só grupo de detecção, para não correr o risco de existirem duplicados inter-grupos.
Desta forma a funcionalidade Options -> Parser input address line types, representada na Figura 6.5
deve de ser definida com a opção business name, para poder ter todos os registos no mesmo grupo.
Esta opção consiste em categorizar todos os registos como nomes de empresas, e assim obter todos
os registos no grupo dos nomes de empresas. Na mesma janela é necessário ainda definir outra funcionalidade Options -> Parser Options. Dentro desta é necessário definir os parâmetros Max number
of names to write, Total number of business records e Total number of personal records com o valor
um e o parâmetro Max number of input lines in which to look for names com o valor cinco, para que
este processo não duplique por si próprio linhas que anteriormente não estavam duplicadas. Depois
de definida esta questão é necessário correr este processo, para que o seu output seja usado input no
próximo processo.
86
Figura 6.5: Customer Data Parser - Options.
Window Key Generator
Este processo inicia a detecção de registos duplicados. O primeiro passo é criar uma chave modelo que
vai ser composta por campos da tabela Clientes. Com base nesta chave cada registo da tabela forma
a sua própria chave. A funcionalidade que permite criar chaves é Keys -> Window key attribute name,
que pode ser visualizada na Figura 6.6. Aqui pode-se escolher o campo que vai conter a definição
da ’chave modelo’ que todos os registos vão seguir. Esta funcionalidade disponibiliza três campos
que suportam respectivamente três chaves modelo diferentes. Os campos são: o Window_Key_01, o
Window_Key_02 e o Window_Key_03, independentemente do campo escolhido, cada chave tem até
dez regras que ditam a sua formação. Optando pelo campo Window_Key_01 e visualizando a Figura
6.6, e para cada um deles existem até dez regras que ditam a sua formação. Optando pelo campo
Window_Key_01 e visualizando a Figura 6.6, cada regra é constituída por [71]:
• Um primeiro campo que vai fazer parte da regra;
• Os n caracteres que se vão utilizar do primeiro campo;
• Que parte do campo com os n caracteres é que vai ser seleccionado;
• A condição para utilização do segundo campo como chave;
• Este campo só é escolhido se o anterior não existir;
• Os n caracteres que se vão utilizar desse campo;
• Que parte do campo acima, com n caracteres é que vão ser seleccionados.
Podem-se definir várias regras, que se vão somando umas às outras, de forma a completar a ’chave
modelo’. Depois de definida a chave é necessário correr o processo para gerar a chave de cada registo. Para verificar quais foram as chaves que a aplicação gerou é necessário sobre a tabela de output
(pt winkey p3) fazer Analyze. É necessário fazer refresh no painel que contêm as tabelas e reparar
no campo designado Window_Key_01. Se o número de valores únicos para este campo for igual ao
número de linhas da tabela Cliente, então a chave foi mal escolhida e deve-se definir melhor as regras
da chave modelo. É fundamental que o número de chaves seja inferior ao número de linhas da tabela
Cliente, porque os registos vão ser agrupados segundo a respectiva chave e se as mesmas forem todas
87
Figura 6.6: Window Key Generator - Keys.
distintas nenhum registo vai ser agrupado nem detectado como duplicado.
Sort for Linking
Uma vez que todas as chaves dos registos da tabela Clientes foram formadas é necessário passar-se
ao segundo passo da detecção de registos duplicados. Este segundo passo vai permitir ordenar os
vários registos segundo a chave que possuem, de modo que o próximo processo consiga agrupar de
uma forma rápida todos os registos segundo a respectiva chave. Portanto, basta correr este processo
e passar para o seguinte [71].
Relationship Linker
O último passo da detecção é realizado por este processo. Inicialmente tem de se referir qual é o
campo (PR_NAME_FORM_01) que refere quais os registos com os nomes de pessoas e os nomes de
empresas, para que o processo possa detectar registos duplicados, ao nível dos nomes das pessoas e
ao nível de nomes de empresa. Mas tal como já foi referido atrás, para a tabela Clientes, os registos
só vão ser analisados segundo um grupo, porque só existem pessoas e não empresas na tabela. Depois refere-se o campo (Window_Key_01) que detém as chaves de cada registo. Estas funcionalidades
88
estão representadas na Figura 6.7. É necessário ainda definir a opção Process -> Advanced -> Match
Rules -> Consumer level 1 rules required?, representada na Figura 6.8. Com estas três funcionalidades
[71] os registos são colocados em grupos segundo a chave que detêm. Os registos são todos numerados, porque se existirem duplicados, estes são registados com o número do registo original. Dentro
de cada grupo existe uma janela que engloba um conjunto de registos e dentro desta os registos são
comparados uns com os outros, de modo que, quando se detecta um registo igual a outro, este assume
o número do original. No fim da comparação dos registos que estão dentro da janela, se existirem
linhas que ainda não foram analisadas, a janela desce uma linha e volta a comparar tudo com tudo
até se chegar ao fim da tabela. Se a funcionalidade Process -> Attempt all matches within window
(Figura 6.7), estiver definida existe a possibilidade de se encontrar ainda mais registos duplicados. Por
exemplo: Se o registo ’a’ for igual ao registo ’b’ e o registo ’b’ foi igual ao registo ’z’, então através desta
funcionalidade, pode-se afirmar que o registo ’a’ também é igual ao registo ’z’. De notar que esta função
é só aplicada em registos que no momento não se encontram sobre a mesma janela de comparação.
Por fim é necessário correr o processo, fazer Analize sobre a tabela de output (pt rellink p5) e analisar
o campo LEV1_MATCHED. Este campo indica em cada valor, o número da linha da tabela Clientes no
processo de detecção de duplicados. Se a propriedade unique do campo, representada na Figura 6.9,
for igual ao número de linhas da tabela Clientes, então não existem duplicados e a chave modelo pode
ter sido mal definida (mas também podem não existir registos duplicados, o que raramente é possível).
No caso de existirem registos duplicados os mesmos podem ser visualizados pelo utilizador, através da
janela das propriedades do campo LEV1_MATCHED, carregando na propriedade unique esta remete
o utilizador para a frequência com que cada número da detecção de duplicados aparece ao longo da
tabela Clientes. Um exemplo de um cliente aproximadamente duplicado pode ser visualizado na Figura
6.10.
Figura 6.7: Relationship Linker - Process.
89
Figura 6.8: Relationship Linker - Match Rules.
Figura 6.9: Relationship Linker - Frequência dos valores únicos da coluna LEV1_MATCHED da tabela pt rellink p5.
Figura 6.10: Relationship Linker - Exemplo de um cliente aproximadamente duplicado.
6.3
6.3.1
Actividades das ferramentas de DP da Oracle
Actividades da ferramenta OWB - Módulo DP
O módulo de DP dispõe de várias actividades que podem ser aplicadas a vários elementos de uma
base de dados, são elas [30] [29]:
• Detecção de Domínio - Indica os valores encontrados para as colunas da tabela a analisar. Como
resultado é apresentada a seguinte informação:
– Nome da coluna a analisar;
– Domínio encontrado, a aplicação indica os valores que inferiu para o domínio da coluna em
observação. Esta inferência baseia-se nos valores que mais se repetem ao longo da coluna;
90
– Percentagem do domínio encontrado, é a percentagem dos valores inferidos sobre todos os
valores da coluna;
– Número de colunas da tabela em questão;
– Número de linhas da tabela a analisar.
• Detecção de Chaves únicas - Indica todas as chaves da tabela. Como resultado é apresentada a
seguinte informação:
– Chave única, indica o nome atribuído pela aplicação à chave. No caso em que a chave é
primária tem a designação ’PK’;
– Documentada, refere se o campo já estava nos metadados com a restrição de integridade
unique;
– Descoberta, indica se a aplicação descobriu o campo com valores únicos;
– Nome atributo, indica o nome do campo na tabela a analisar;
– Número de valores únicos no campo
– Percentagem de valores únicos, sobre todos os valores do campo.
• Detecção de Dependências funcionais - Indica todas as dependências funcionais que existe na
tabela a analisar. Como resultado é apresentada a seguinte informação:
– Determinante, indica o nome do campo que foi encontrado para determinar o atributo listado
na dependência funcional;
– Dependente, é o nome do campo que é encontrado para ser determinado pelos valores de
outro atributo (o de cima);
– Número de valores do atributo determinante, que não determinam nenhum dos valores do
atributo dependente;
– Percentagem de valores das duas colunas que vão de encontro com a dependência encontrada;
– Tipo de dependência funcional, é sugerido que a dependência pode ir só num sentido (de um
campo que define o outro), mas também poderá ser nos dois sentidos (ambos os campos se
definem mutuamente).
• Detecção de Referencial - Indica todas as chaves estrangeiras e outras relações entre as várias
tabelas da BD em análise. Como resultado é apresentada a seguinte informação:
– Relação, indica o nome da relação dado pela aplicação;
– Tipo de relação, as relações podem ser chave estrangeiras, ou relações entre linhas de
tabelas diferentes;
– Documentada, indica se a chave estrangeira está definida nos metadados da BD a analisar;
– Descoberta, a aplicação decide se a relação entre as duas colunas é ou não uma relação;
– Nome do atributo local que vai referencial;
– Chave Remota;
– Atributos remotos, indica o ou os atributos referenciados;
– Relação remota, indica a tabela do atributo referenciado;
– Cardinalidade da relação;
91
– Órfãos, indica o número de valores que não têm referência na coluna fonte;
– Percentagens de valores de acordo com o referencial;
– Colunas redundantes, são indicadas as colunas que estão em duplicado através das relações
das chaves estrangeiras, ou seja, numa chave estrangeira existem colunas que se podem
aceder através da mesma, mas que se encontram repetidas nas duas tabelas.
• Detecção de regras de negócio - São condições que podem ser definidas pelo utilizador ou
derivadas pela aplicação. Como resultado é apresentada a seguinte informação:
– Nome da regra de negócio;
– Descrição da regra;
– Origem, indica se a regra foi derivada ou se foi designada pelo utilizador;
– Percentagem de linhas que passaram com a regra;
– Número de defeitos, indica as linhas que não passaram na regra definida.
• Data profile - Esta actividade permite ao utilizador deixar todos os comentários que entender sobre
a análise que está a realizar sobre os dados;
• Profile object - Permite realizar queries sobre a tabela que se está a analisar;
• Agregação - Aplica funções de estatística aos campos de uma tabela. Como resultado é apresentada a seguinte informação:
– Nome da coluna a analisar;
– Mínimo/Máximo, indica o valor máximo/mínimo de todos os valor da coluna;
– Número de valores distintos da coluna;
– Percentagem de valores distintos;
– Not null, indica se a propriedade not null está definida para esta coluna;
– Recomendado ser not null, a aplicação recomenda se o campo deve ou não ser not null;
– Número de valores a null na coluna;
– Percentagem de valores a null no atributo;
– Média, mediana e Desvio padrão: estas funções estatísticas só são calculadas para os campos que são numéricos.
• Detecção dos tipos de dados da coluna - indica os tipos de dados da coluna a analisar. Como
resultado é apresentada a seguinte informação:
– Nome da coluna;
– Documentado, indica qual é o tipo de dados que está documentado nos metadados da coluna;
– Tipo de dados dominante, é o tipo que aparece mais vezes representado na coluna;
– Percentagem do tipo de dados dominante, sobre os outros tipos da coluna (se existirem é
claro);
– Comprimento máximo/mínimo, é indicado o comprimento máximo/mínimos de todos os valores da coluna;
– Comprimento dominante da coluna;
– Percentagem do comprimento dominante;
92
– Precisão Documentada, refere o número de dígitos à esquerda da vírgula, para os campos
numéricos;
– Precisão mínima/máxima, indica o número mínimo/máximo de dígitos que existem em toda
a coluna a analisar;
– Precisão dominante na coluna;
– Percentagem de precisão dominante;
– Escala Documentada, indica o número de casas decimais que foi definido nos metadados da
coluna;
– Escala mínima/máxima, indica o número mínimo/máximo de casas decimais, encontrado nos
valores da coluna;
– Escala dominante dos valores da coluna;
– Percentagem da escala dominante.
• Detecção de Padrões - Indica um conjunto de padrões que os dados da coluna a analisar assumem. Como resultado é apresentada a seguinte informação:
– Padrão do caracter dominante, a aplicação indica qual é a expressão regular dos caracteres
que mais se repetem na coluna;
– Percentagem do padrão do caracter dominante;
– Padrão da palavra dominante, a aplicação indica qual é a expressão regular da palavra que
mais se repete na coluna;
– Percentagem do padrão da palavra dominante;
– Formato comum, a aplicação dispõe de um conjunto de formatos que existem no mundo real,
como por exemplo, o formato do telefone, do número da segurança social, etc. Ao analisar
uma coluna a aplicação infere, sempre que possível, um formato do mundo real para o campo
que está a analisar;
– Percentagem do formato comum.
6.3.2
Actividades da ferramenta ODI_DQ
Esta ferramenta é mais recente em termos de soluções de DP da Oracle. Detém dois tipos de projectos,
um relacionado com o processo de data profiling e outro direccionado para o processo de data quality.
Num projecto de DP o ODI-DQ disponibiliza um conjunto de actividades segundo o objecto que está a
analisar [71]. Na Figura 6.11 pode-se observar a estrutura de um projecto de Profiling no ODI_DQ. De
seguida são descritos os elementos que compõem a estrutura:
• Metadados do projecto - Informação geral sobre o projecto:
– Número do projecto;
– Utilizador que criou o projecto;
– Data de criação do projecto.
• Entidades (tabelas do projecto) - São todas as tabelas que foram escolhidas pelo utilizador para
serem analisadas segundo o processo de DP:
– Número de tabelas.
93
• Tabela a analisar:
– Número de linhas da tabela a analisar;
– Conteúdo de todas as linhas da tabela.
• Metadados da tabela - Dados sobre a tabela a analisar:
– Nome da tabela;
– Número que a tabela ocupa na base de dados;
– Número de atributos que a tabela tem;
– Número de valores únicos que existem em toda a tabela;
– Indica a linha com o menor e maior comprimento da tabela;
– Número de regras de negócio definidas para a tabela em questão;
– Número de regras de negócio aplicadas à tabela;
– Número de regras de negócio, cujas condições passaram nos respectivos dados da tabela;
– Número de regras de negócio, cujas condições não falharam nos respectivos dados da
tabela;
– Nome da fonte de dados da tabela;
– O formato dos caracteres da tabela, normalmente vêm em ascii;
– Número de chaves permanentes, ou seja, número de chaves que foram definidas pelo utilizador;
– Número de chaves descobertas, são chaves que foram descobertas pela aplicação e que
não se encontravam como tal na definição dos metadados da tabela;
– Número de dependências permanentes, é o número de dependências funcionais definidas
pelo utilizador;
– Número de dependências descobertas pela aplicação;
– Número de joins permanentes, ou seja, são joins que foram definidos pelo utilizador;
– Número de joins descobertos pela aplicação.
• Regras de negócio - Indica todas as regras de negócio que foram introduzidas pelo utilizador,
sobre a tabela a analisar:
– Descrição da regra;
– Resultado da regra, ou seja, se passou ou falhou na aplicação da mesma a uma tabela ou
coluna;
– Percentagem de linhas que a regra passou.
• Atributo analisar:
– Nome do atributo;
– Número do atributo na tabela que se está a analisar;
– Número de valores únicos da coluna;
– Percentagem de valores únicos;
– Número de padrões únicos que existem no atributo;
– Indica o valor mínimo e máximo que ocorreu na coluna analisada;
– Indica o comprimento mínimo e máximo de toda a gama de valores do atributo;
94
– Número de registos a null na coluna;
– Percentagem de nulls;
– Atributo a null documentado, indica se a coluna tinha a propriedade de ’campo not null’
definida nos metadados do atributo;
– Número de espaços que ocorrem em toda a coluna;
– Percentagem de espaços;
– Tipo inferido pela aplicação para o atributo em análise;
– Número de valores alfanuméricos na coluna;
– Percentagem de valores alfanuméricos;
– Número de valores decimais na coluna;
– Percentagem de valores decimais;
– Número de valores inteiros no atributo;
– Percentagem de valores inteiros;
– Indica o maior e o menor valor de inteiros que existe na coluna;
– Número de soundexs únicos do atributo. O soundex em DP é um código de identificação que
atribuí o mesmo valor de soundex às palavras que têm um som similar. Com esta propriedade
é possível analisar grupos de palavras que embora sejam escritas de forma diferente têm um
mesmo som. São bastante úteis para a detecção de duplicados;
– Números de metaphones únicos. Um Metaphone é parecido com o soundex mas permite resultados mais refinados porque compara valores que vão de encontro com padrões fonéticos
de multi-caracter. São úteis para descobrir palavras com erros ortográficos;
– No de máscaras únicas. Uma máscara é uma representação de uma palavra, frase ou
número, em que cada caracter é representado como uma letra número ou caracter especial;
– Chave descoberta, indica se o atributo é uma chave descoberta pela aplicação;
– Join descoberto, indica se o atributo é uma join descoberto pela aplicação;
– Join permanente, informa se o atributo é um join definido pelo utilizador;
– Tipo documentado, indica o tipo de atributo que está identificado nos metadados do atributo;
– Precisão inferida, se o campo for do tipo numérico, então a aplicação vai descobrir quantos
dígitos compõem os registos da coluna;
– Escala Inferida, permite verificar quantas casas decimais existe nos registos do atributo;
– Número total de valores na coluna.
• Valor único (indica todos os valores únicos da coluna), mínimo, máximo, comprimento mínimo e
máximo (indica todos os valores com o comprimento mínimo e máximo), Inteiros (indica todos os
valores inteiros da coluna), Strings (indica todos os valores alfanuméricos do atributo):
– Indica o valor do objecto em questão;
– Frequência, indica o número de vezes que o valor do objecto aparece na coluna;
– Distribuição, indica qual é a percentagem que o valor tem sobre toda a população da coluna;
– Comprimento do valor;
95
– Soundex do valor;
– Metaphone do valor;
– Padrão do valor;
– Máscara do valor.
• Padrão - Indica os vários formatos que existem no atributo:
– Valor do padrão;
– Comprimento do padrão;
– Número de valores que existem na tabela com o padrão;
– Frequência, informa o número de vezes que o padrão ocorre no atributo;
– Distribuição, indica qual é a percentagem que o padrão tem sobre o conjunto dos padrões.
• Máscara - Indica todas as máscaras dos valores do atributos:
– Padrão da máscara de cada valor;
– Número de valores da coluna, que têm a máscara em questão;
– Frequência, indica o número de vezes que a máscara ocorre na coluna;
– Distribuição, indica qual é a percentagem que a máscara em questão tem sobre o conjunto
dos padrões.
• Chave descoberta - Indica se o atributo que está a ser analisado é considerado pela aplicação
como uma chave:
– Nome do campo;
– Número de valores únicos que o campo tem;
– Número de valores duplicados (sem repetições) existentes na coluna;
– Número de linhas, que existem na tabela do atributo analisar, com valores duplicados.
• Dependências descobertas - É o conjunto de dependências que a aplicação encontrou na tabela
do atributo a analisar, em que o mesmo faz parte das dependências encontradas:
– Atributo Lh, indica que o atributo que está a ser analisado, sozinho ou em conjunto com
outros atributos, determinam o atributo Rh;
– Atributo Rh, representa o atributo que é determinado pelo campo Lh;
– Número de valores LR, indica o número de vezes que os valores do atributo Lh determinam
os valores do atributo Rh;
– Número de valores LH, indica os valores(sem repetições) do atributo Lh que não determinam
valores do atributo Rh;
– Número de linhas, da tabela que contém os atributos Lh e Rh, onde os valores do atributo Lh
não determina os valores do atributo Rh.
• Joins descobertos - É o conjunto de joins que a aplicação encontrou nas tabelas da BD a analisar.
Em todos os joins encontrados o atributo a analisar faz parte dos mesmos:
– Venn Diagram, é um gráfico que contém um diagrama que indica:
∗ O número de linhas das duas tabelas do join(Outer Join);
∗ O número de valores que fazem parte do join das duas colunas (Inner Join) ;
∗ O número de valores (únicos) que existem em comum entre as duas colunas;
96
∗ Indica o número de valores em cada coluna que não são comuns nos dois atributos, e o
número de linhas em que estes aparecem nas respectivas colunas.
– Tabela da direita/esquerda, indica as tabelas que estão envolvidas no join;
– Atributo da direita/esquerda, indica os campos que estão envolvidos no join;
– Indica a mesma informação que a representada no diagrama, mas por texto;
– Left/Right outer join, indica o número de todas as linhas da tabela da esquerda/direita do join
façam ou não parte do join, com as linhas do join;
– Número de linhas da coluna da esquerda/direita;
– Indica o tipo de Join que foi realizado. Pode-se realizar um Join através do valor das tabelas,
através dos metaphones ou soudexs das mesmas;
– Cardinalidade do join;
– Cardinalidade exacta do join;
– Desenho do diagrama E-R, se o utilizador definiu joins entre as várias tabelas da BD, a
aplicação mostra o diagrama E-R da BD em questão.
Os projectos de Data Quality são constituídos por vários processos relacionados com a metodologia
DQ. No âmbito do DP e desta dissertação, os processos mais importantes são os que se referem à
detecção de registos aproximadamente duplicados referidos no anexo 6.2.
Figura 6.11: Estrutura de um projecto de profiling no ODI-DQ.
97