Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... ·...
Transcript of Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... ·...
![Page 1: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/1.jpg)
Data Quality Services (DQS)Integração de Dados e Warehousing
Pedro Henrique de Queiroz Lima
![Page 2: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/2.jpg)
Roteiro
● Motivação● Qualidade de dados
○ O que é?○ Aspectos
● Data Quality Services○ Workflow○ Limpeza de dados○ Correspondência de dados○ Profiling○ Admnistração
● Recapitulando...● Referências
![Page 3: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/3.jpg)
Motivação
![Page 4: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/4.jpg)
BIG DATA
![Page 5: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/5.jpg)
4.4ZBQuantidade de informação digital gerada acumulada até hoje (IDC/Forbes)
1 Zetabyte = 1021 bytes
![Page 6: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/6.jpg)
44ZBProjeção da quantidade de informação digital gerada acumulada até 2020
1.7MB/s/pessoa
![Page 7: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/7.jpg)
BUSINESS INTELLIGENCE
![Page 8: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/8.jpg)
US$ 3,1 tri/anoEram US$600 bi/ano, com dados de 2003 (IDC)
EUA, 2015
Causados por decisões de negócio baseadas em dados errados, retrabalho em TI e postagem de mala direta (US$ 600 bi nestes dois últimos).
![Page 9: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/9.jpg)
15%dos executivos confiam na qualidade
geral dos dados
27%dos executivos tem certeza da
qualidade dos dados
![Page 10: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/10.jpg)
Qualidade de Dados
![Page 11: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/11.jpg)
O que é?Adequação dos dados para as atividades de operações, decisões de negócios e planejamento da instituição.
Qualidade de Dados
![Page 12: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/12.jpg)
Dimensões de Qualidade de Dados
Fator Exemplo
Completude 25% dos registros não contém “Sobrenome”
Conformidade “Rua” e “r.”; “avenida” e “Ave.” ou “Av.”
Consistência Sexo representado por “0/1” e “M/F”
Precisão Casas decimais necessárias ou dado atual
Validade Altura: -15,5m
Unicidade Registros devem representar entidades únicas
![Page 13: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/13.jpg)
Dificuldades em Qualidade de Dados
“Difusividade” Volatilidade
![Page 14: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/14.jpg)
Problemas no design de software
![Page 15: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/15.jpg)
Validação de dados
Erros de entrada podem ser transferidos para a base de
dados
![Page 16: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/16.jpg)
Fusão e aquisição de empresasPossíveis duplicações, dados em formatos diferentes, incompletude de informações
![Page 17: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/17.jpg)
Formatação
Espaçamentos, abreviações, apelidos
![Page 18: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/18.jpg)
Alterações de endereços, telefones
Mudanças de atributos
![Page 19: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/19.jpg)
Data Quality Services
![Page 20: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/20.jpg)
![Page 21: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/21.jpg)
Enterprise Information Management
● Master Data Services (MDS)● SQL Server Integration Services (SSIS)● Data Quality Services (DQS)
Tira carga de trabalho do fluxo ETL, com interface simplificada
![Page 22: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/22.jpg)
Concorrentes
![Page 23: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/23.jpg)
Ferramentas
Monitoramento Limpeza
CorrespondênciaProfiling
Monitoramento e rastreamento dos estados das atividades de qualidade e da qualidade de dados
Correção, remoção ou enriquecimento de dados que estejam incorretos ou incompletos.
Identificação, ligação ou fusão de registros duplicados entre conjuntos de dados.
Análise dos dados de origem para prover entendimento da qualidade de dados e seus problemas.
![Page 24: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/24.jpg)
Workflow
![Page 25: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/25.jpg)
SQL Server Data Quality Client
![Page 26: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/26.jpg)
● Amostras da base de dados● Bases de dados de Referência (Azure Marketplace)● Descoberta de conhecimento● Domínios
○ Leading Values○ Regras
● Base de conhecimento cresce com iterações e entradas de usuário
Knowledge Base
![Page 27: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/27.jpg)
Knowledge Discovery
![Page 28: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/28.jpg)
Domínios de dados
![Page 29: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/29.jpg)
Projeto de Qualidade de Dados
![Page 30: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/30.jpg)
Limpeza de Dados
![Page 31: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/31.jpg)
Limpeza de Dados
![Page 32: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/32.jpg)
Limpeza de Dados
![Page 33: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/33.jpg)
Limpeza de Dados
![Page 34: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/34.jpg)
Correspondência (Matching)
![Page 35: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/35.jpg)
Exemplo MatchingDados
Resultados do matching
![Page 36: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/36.jpg)
Exemplo MatchingResolução de Correspondências
![Page 37: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/37.jpg)
Propriedades de correspondência
● Similaridade● Peso● Pré-requisito● Pontuação mínima de correspondência
![Page 38: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/38.jpg)
Correspondência de dados
![Page 39: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/39.jpg)
Correspondência de dados
![Page 40: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/40.jpg)
Correspondência de dados
![Page 41: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/41.jpg)
Correspondência de dados
![Page 42: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/42.jpg)
Recapitulando...● Remove carga do processo ETL● Oferece ferramentas de validação e estatísticas sobre regras de domínio● Pode limpar e remover duplicações● Pode ser integrada ao SSIS com o DQS Transform● Baseado em base de conhecimento● Pode usar dados de terceiros para conhecimento
![Page 43: Integração de Dados e Warehousing Pedro Henrique de ...if696/aulas/Data_Quality_Services... · Enterprise Information Management Master Data Services (MDS) SQL Server Integration](https://reader034.fdocument.pub/reader034/viewer/2022042805/5f5d54df92f53179271c72b5/html5/thumbnails/43.jpg)
Referências
1. Big Data: 20 Mind-Boggling Facts Everyone Must Read2. Integrating and governing Big Data3. Extracting Value from Chaos4. The Cost of Poor Data Quality5. Data Quality Services Demo