ETL significa Extrair, Transformar e Carregar. É um conceito importante em sistemas de Data Warehousing.Extraction significa extrair dados de diferentes fontes de dados, como sistemas transacionais ou aplicativos. Transformationsignifica aplicar as regras de conversão de dados para que se tornem adequados para relatórios analíticos. oloading O processo envolve mover os dados para o sistema de destino, normalmente um data warehouse.

As três camadas envolvidas em um ciclo ETL são -

• Staging Layer - A camada de teste é usada para armazenar os dados extraídos de diferentes sistemas de dados de origem.

• Data Integration Layer - A camada de integração transforma os dados da camada de teste e move os dados para um banco de dados, onde os dados são organizados em grupos hierárquicos, muitas vezes chamados dimensions, e em fatos e fatos agregados. A combinação de tabelas de fatos e dimensões em um sistema DW é chamada deschema.

• Access Layer - A camada de acesso é usada pelos usuários finais para recuperar os dados para relatórios analíticos.

Uma ferramenta ETL é usada para extrair dados de diferentes fontes de dados, transformar os dados e carregá-los em um sistema DW. Em contraste, uma ferramenta de BI é usada para gerar relatórios interativos e ad hoc para usuários finais, painel para gerenciamento sênior, visualizações de dados para reuniões mensais, trimestrais e anuais do conselho.

As ferramentas ETL mais comuns incluem - SAP BO Data Services (BODS), Informatica, Microsoft - SSIS, Oracle Data Integrator ODI, Talend Open Studio, Clover ETL Open source, etc.

As ferramentas de BI mais comuns incluem - SAP Business Objects, SAP Lumira, IBM Cognos, JasperSoft, Microsoft BI Platform, Tableau, Oracle Business Intelligence Enterprise Edition, etc.

As ferramentas populares de ETL disponíveis no mercado são -

• Informatica - Power Center
• IBM - Websphere DataStage (anteriormente conhecido como Ascential DataStage)
• SAP - Business Objects Data Services BODS
• IBM - Cognos Data Manager (anteriormente conhecido como Cognos Decision Stream)
• Microsoft - SSIS do SQL Server Integration Services
• Oracle - Data Integrator ODI (anteriormente conhecido como Sunopsis Data Conductor)
• SAS - Data Integration Studio
• Oracle - Warehouse Builder
• ABInitio
• Clover ETL de código aberto

A área de teste é uma área intermediária que fica entre as fontes de dados e os sistemas de data warehouse / data marts. As áreas de teste podem ser projetadas para fornecer muitos benefícios, mas as principais motivações para seu uso são aumentar a eficiência dos processos ETL, garantir a integridade dos dados e oferecer suporte às operações de qualidade dos dados.

O armazenamento de dados é um conceito mais amplo em comparação com a mineração de dados. A mineração de dados envolve extrair informações ocultas dos dados e interpretá-las para previsões futuras. Em contraste, o data warehouse inclui operações como relatórios analíticos para gerar relatórios detalhados e relatórios ad-hoc, processamento de informações para gerar painéis e gráficos interativos.

OLTP significa Online Transactional Processing system, que normalmente é um banco de dados relacional e é usado para gerenciar transações do dia-a-dia.

OLAP significa Online Analytical Processing system, que normalmente é um sistema multidimensional e também é chamado de data warehouse.

Suponha que uma empresa venda seus produtos aos clientes. Toda venda é um fato que ocorre dentro da empresa e a tabela de fatos é usada para registrar esses fatos. Cada tabela de fatos armazena as chaves primárias para unir a tabela de fatos para dimensionar tabelas e medidas / fatos.

Example - Fact_Units

Uma tabela de dimensão armazena atributos ou dimensões que descrevem os objetos em uma tabela de fatos. É um conjunto de tabelas complementares a uma tabela de fatos.

Example - Dim_Customer

Um data mart é uma forma simples de data warehouse e está focado em uma única área funcional. Geralmente, ele obtém dados apenas de algumas fontes.

Example - Em uma organização, os data marts podem existir para Finanças, Marketing, Recursos Humanos e outros departamentos individuais que armazenam dados relacionados a suas funções específicas.

Funções agregadas são usadas para agrupar várias linhas de uma única coluna para formar uma medida mais significativa. Eles também são usados ​​para otimização de desempenho quando salvamos tabelas agregadas no data warehouse.

As funções de agregação comuns são -

Example

SELECT AVG(salary) 
FROM employee 
WHERE title = 'developer';

As instruções Data Definition Language (DDL) são usadas para definir a estrutura ou esquema do banco de dados.

Examples -

• CREATE - para criar objetos em um banco de dados

• ALTER - altera a estrutura de um banco de dados

As instruções de linguagem de manipulação de dados (DML) são usadas para manipular dados dentro do banco de dados.

Examples -

• SELECT - recupera dados de um banco de dados

• INSERT - insere dados em uma tabela

• UPDATE - atualiza os dados existentes em uma tabela

• DELETE - exclui todos os registros de uma tabela, o espaço para os registros permanece

As instruções de linguagem de controle de dados (DCL) são usadas para controlar o acesso a objetos de banco de dados.

Examples -

• GRANT - dá ao usuário privilégios de acesso ao banco de dados

• REVOKE - retira os privilégios de acesso dados com o comando GRANT

Os operadores são usados ​​para especificar condições em uma instrução SQL e servir como conjunções para várias condições em uma instrução. Os tipos de operadores comuns são -

• Operadores aritméticos
• Operadores de comparação / relacionais
• Operadores lógicos
• Operadores de conjunto
• Operadores costumavam negar condições

Os operadores de conjunto comuns em SQL são -

• UNION
• UNION ALL
• INTERSECT
• MINUS

A operação de interseção é usada para combinar duas instruções SELECT, mas retorna apenas os registros que são comuns a ambas as instruções SELECT. No caso do Intersect, o número de colunas e o tipo de dados devem ser iguais. O MySQL não oferece suporte ao operador INTERSECT. Uma consulta Intersect tem a seguinte aparência -

select * from First 
INTERSECT 
select * from second

A operação menos combina o resultado de duas instruções Select e retorna apenas os resultados que pertencem ao primeiro conjunto de resultados. Uma consulta menos tem a seguinte aparência -

select * from First 
MINUS 
select * from second

Se você executar origem menos destino e destino menos origem, e se a consulta menos retornar um valor, isso deve ser considerado como um caso de linhas incompatíveis.

Se a consulta negativa retornar um valor e a intersecção da contagem for menor que a contagem de origem ou a tabela de destino, as tabelas de origem e destino conterão linhas duplicadas.

Group-by cláusula é usada com select declaração para coletar tipos semelhantes de dados. HAVING é muito parecido com WHERE exceto as declarações contidas nele são de natureza agregada.

Syntax -

SELECT dept_no, count ( 1 ) FROM employee GROUP BY dept_no;  
SELECT dept_no, count ( 1 ) FROM employee GROUP BY dept_no HAVING COUNT( 1 ) > 1;

Example - Mesa de funcionários

Group by Country

O teste ETL é feito antes que os dados sejam movidos para um sistema de Data Warehouse de produção. Às vezes, também é chamado de Balanceamento de mesa ou reconciliação de produção.

O principal objetivo do teste de ETL é identificar e mitigar defeitos de dados e erros gerais que ocorrem antes do processamento de dados para relatórios analíticos.

A tabela a seguir captura os principais recursos dos testes de banco de dados e ETL e sua comparação -

O teste de ETL pode ser dividido nas seguintes categorias com base em sua função -

• Source to Target Count Testing - Envolve a correspondência da contagem de registros no sistema de origem e de destino.

• Source to Target Data Testing- Envolve validação de dados entre o sistema de origem e o sistema de destino. Também envolve integração de dados e verificação de valor limite e verificação de dados duplicados no sistema de destino.

• Data Mapping or Transformation Testing- Confirma o mapeamento de objetos no sistema de origem e destino. Também envolve a verificação da funcionalidade dos dados no sistema de destino.

• End-User Testing- Envolve a geração de relatórios para os usuários finais para verificar se os dados nos relatórios estão de acordo com a expectativa. Envolve encontrar desvios em relatórios e verificar os dados no sistema de destino para validação do relatório.

• Retesting - Envolve a correção de bugs e defeitos nos dados do sistema de destino e a execução dos relatórios novamente para validação dos dados.

• System Integration Testing - Envolve testar todos os sistemas individuais, e posteriormente combinar o resultado para saber se há algum desvio.

• Perda de dados durante o processo ETL.

• Dados incorretos, incompletos ou duplicados.

• O sistema DW contém dados históricos, portanto o volume de dados é muito grande e realmente complexo para realizar o teste de ETL no sistema de destino.

• Os testadores ETL normalmente não têm acesso para ver os cronogramas de trabalho na ferramenta ETL. Eles dificilmente têm acesso às ferramentas de relatório de BI para ver o layout final dos relatórios e dados dentro dos relatórios.

• Difícil de gerar e construir casos de teste, pois o volume de dados é muito alto e complexo.

• Os testadores de ETL normalmente não têm idéia dos requisitos de relatório do usuário final e do fluxo de negócios das informações.

• O teste de ETL envolve vários conceitos complexos de SQL para validação de dados no sistema de destino.

• Às vezes, os testadores não recebem informações de mapeamento de origem para destino.

• O ambiente de teste instável resulta em atraso no desenvolvimento e no teste do processo.

As principais responsabilidades de um testador ETL incluem -

• Verificação das tabelas no sistema de origem - verificação de contagem, verificação de tipo de dados, chaves não ausentes, dados duplicados.

• Aplicar a lógica de transformação antes de carregar os dados: validação de limite de dados, verificação de ky substituta, etc.

• Carregamento de dados da área de teste para o sistema de destino: valores agregados e medidas calculadas, campos-chave não ausentes, verificação de contagem na tabela de destino, validação de relatório de BI, etc.

• Teste de ferramenta ETL e seus componentes, casos de teste - criar, projetar e executar planos de teste, casos de teste, ferramenta de teste ETL e sua função, sistema de teste DW, etc.

Uma transformação é um conjunto de regras que gera, modifica ou transmite dados. A transformação pode ser de dois tipos - ativa e passiva.

Em uma transformação ativa, o número de linhas criadas como saída pode ser alterado assim que uma transformação ocorrer. Isso não acontece durante uma transformação passiva. A informação passa pelo mesmo número que lhe foi atribuído na entrada.

Particionamento é quando você divide a área de armazenamento de dados em partes. Normalmente é feito para melhorar o desempenho das transações.

Se o seu sistema DW for muito grande, demorará algum tempo para localizar os dados. O particionamento do espaço de armazenamento permite que você encontre e analise os dados de maneira mais fácil e rápida.

A partição pode ser de dois tipos - particionamento round-robin e particionamento Hash.

No particionamento round-robin, os dados são distribuídos uniformemente entre todas as partições, de forma que o número de linhas em cada partição seja relativamente o mesmo. O particionamento de hash ocorre quando o servidor usa uma função de hash para criar chaves de partição para agrupar os dados.

• Um mapplet define as regras de transformação.

• As sessões são definidas para instruir os dados quando eles são movidos do sistema de origem para o de destino.

• Um fluxo de trabalho é um conjunto de instruções que instrui o servidor sobre a execução da tarefa.

• Mapeamento é o movimento de dados da origem ao destino.

A transformação de pesquisa permite acessar dados de tabelas relacionais que não são definidas em documentos de mapeamento. Ele permite que você atualize tabelas de dimensão de alteração lenta para determinar se os registros já existem no destino ou não.

Uma chave substituta é algo que tem números gerados em sequência sem significado e apenas para identificar a linha de forma exclusiva. Não é visível para os usuários ou aplicativo. Também é chamada de chave do candidato.

Uma chave substituta possui números gerados por sequência sem significado. Destina-se a identificar as linhas de maneira única.

Uma chave primária é usada para identificar as linhas exclusivamente. É visível para os usuários e pode ser alterado conforme a necessidade.

Nesses casos, você pode aplicar o método de soma de verificação. Você pode começar verificando o número de registros nos sistemas de origem e de destino. Selecione as somas e compare as informações.

Nesse teste, um testador valida a faixa de dados. Todos os valores limite no sistema de destino devem ser verificados para garantir que estão de acordo com o resultado esperado.

Example - O atributo de idade não deve ter um valor maior que 100. Na coluna DD / MM / AA, o campo mês não deve ter um valor maior que 12.

Select Cust_Id, Cust_NAME, Quantity, COUNT (*)
FROM Customer GROUP BY Cust_Id, Cust_NAME, Quantity HAVING COUNT (*) >1;

Quando nenhuma chave primária é definida, podem aparecer valores duplicados.

A duplicação de dados também pode surgir devido ao mapeamento incorreto e erros manuais durante a transferência de dados da origem para o sistema de destino.

O teste de regressão é quando fazemos alterações nas regras de agregação e transformação de dados para adicionar uma nova funcionalidade e ajudar o testador a encontrar novos erros. Os bugs que aparecem nos dados que vêm no teste de regressão são chamados de regressão.

As três abordagens são - de cima para baixo, de baixo para cima e híbrida.

Os cenários de teste de ETL mais comuns são -

• Validação de estrutura
• Validando documento de mapeamento
• Validar restrições
• Verificação de consistência de dados
• Validação de integridade de dados
• Validação de correção de dados
• Validação de transformação de dados
• Validação de qualidade de dados
• Validação Nula
• Validação Duplicada
• Verificação de validação de data
• Validação de dados completa usando consulta negativa
• Outros Cenários de Teste
• Limpeza de Dados

A limpeza de dados é um processo de exclusão de dados de um data warehouse. Ele remove dados inúteis, como linhas com valores nulos ou espaços extras.

Bug cosmético está relacionado à GUI de um aplicativo. Pode estar relacionado ao estilo da fonte, tamanho da fonte, cores, alinhamento, erros ortográficos, navegação, etc.

É chamado de bug relacionado à Análise de Valor Limite.

Você pode fazer isso criando uma variável de mapeamento e uma transformação filtrada. Pode ser necessário gerar uma sequência para ter o registro classificado especificamente de que você precisa.

Value comparison- Envolve comparar os dados nos sistemas de origem e destino com o mínimo ou nenhuma transformação. Isso pode ser feito usando várias ferramentas de teste de ETL, como Source Qualifier Transformation na Informatica.

As colunas de dados críticos podem ser verificadas comparando valores distintos nos sistemas de origem e destino.

Você pode usar as instruções Minus e Intersect para realizar a validação da integridade dos dados. Quando você executa origem menos destino e destino menos origem e a consulta menos retorna um valor, isso é um sinal de linhas incompatíveis.

Se a consulta negativa retornar um valor e a intersecção da contagem for menor que a contagem de origem ou a tabela de destino, existem linhas duplicadas.

Shortcut Transformationé uma referência a um objeto que está disponível em uma pasta compartilhada. Essas referências são comumente usadas para várias fontes e destinos que devem ser compartilhados entre diferentes projetos ou ambientes.

No Repository Manager, um atalho é criado atribuindo o status 'Compartilhado'. Posteriormente, os objetos podem ser arrastados desta pasta para outra pasta. Esse processo permite um único ponto de controle para o objeto e vários projetos não têm todas as origens e destinos de importação em suas pastas locais.

Reusable Transformation é local para uma pasta. Example- Gerador de sequência reutilizável para alocar IDs de clientes do warehouse. É útil carregar detalhes do cliente de sistemas de origem múltipla e alocar ids exclusivos para cada nova chave de origem.

Quando você associa uma única tabela a ela mesma, ela é chamada de Auto-associação.

A normalização do banco de dados é o processo de organização dos atributos e tabelas de um banco de dados relacional para minimizar a redundância de dados.

A normalização envolve decompor uma tabela em tabelas menos redundantes (e menores), mas sem perder informações.

Uma tabela de fatos sem fatos é uma tabela de fatos que não possui medidas. É essencialmente uma interseção de dimensões. Existem dois tipos de tabelas sem fatos: uma é para capturar um evento e a outra é para descrever as condições.

Dimensões que mudam lentamente se referem ao valor de mudança de um atributo ao longo do tempo. Os SCDs são de três tipos - Tipo 1, Tipo 2 e Tipo 3.