Portfólio – Dimensão Vendedor II

Olá pessoal!!

Nesta semana iniciei uma série de posts sobre a criação do meu portfólio e nele fui mostrando a evolução do processo de criação, além de mostrar formas de controlar as mudanças no data warehouse.

Em dado momento me deparei com uma situação que estava me induzindo a uma falha no controle que era a operação de delete dos dados.

Como mostrado no post anterior, ao qual criamos a dimensão vendedor, vimos que o “ramo” que fica responsável por deletar os dados dentro do ETL estava agindo de maneira indesejada e deletando dados que não deveria.

Analisando o comportamento e o motivo pelo qual isso estava acontecendo, cheguei a conclusão, elaborei a solução e neste post, quero mostrar para vocês como solucionei e, além disso, mostrar um modelo de SCD Tipo I mais consistente.

CORRIGINDO A FALHA NO PROCESSO DE DELETE

Antes de mostrar a explicação e o motivo do comportamento, veja abaixo como ficou o esquema final, para um controle híbrido com o delete.

• Linha Vermelha – linhas que enviam os dados para o controle tipo II – registro histórico;
• Linha Preta – linhas que enviam os dados para o controle tipo I – sobrescrita;
• Linha Rosa – linhas que levam os dados para o processo de delete.

Entendendo estes fluxos, posso explicar o que estava acontecendo com o processo de deleção dos dados.

Como sabem, estou utilizando a transformação merge diff para categorizar os dados e enviá-los aos seus respectivos destinos de acordo com a classificação quando comparadas às fontes; e é aqui que reside o problema.

Veja este preview do merge diff e as classificações dos dados. Todos são idênticos pois não realizei nenhuma alteração.

Agora irei realizar uma alteração qualquer, executar o job e uma consulta na tabela a fim de verificar as alterações.

Podemos ver que tanto o registro original quanto o atual constam na tabela. Se formos olhar no arquivo fonte na comparação, ele também consta lá (no post da dimensão vendedor, explico como).

Agora, ao reexecutar o merge diff, veja o que ocorre.

Perceba que a transformação entende que o “novo” registro deve ser deletado pois ele não consta entre as comparações, isto é, quando comparamos o arquivo que vem da stage com o arquivo que vem do warehouse, eles não possuem match. O que é normal, visto que a stage não tem esse dado histórico.

Como o merge compara linha por linha e ainda que utilize a coluna salespersonid como base, quando o vendedor com ID 274 com seu registro alterado é comparado, ele não possui um match, visto que não há outro 274 no arquivo que vem do banco stage. Daí o motivo do delete.

Com essa situação em mente, veja o que acontece quando eu insiro uma linha no meu arquivo que vem do banco stage com o mesmo salespersonid.

Como o Pentaho consegue encontrar as duas linhas com o mesmo salespersonid na comparação, ainda que não sejam os mesmos valores, ele entende a mudança e por isso, encaminhará para o SCD TP II.

Perceba também que o valor original não aparece, para ele, continua constando como mudança. Mas quando bate na transformação dimension lookup/update, o controle é mais apurado.

Ao executar um teste e consultar o banco, as mudanças estarão lá.

• Sublinhado de preto – o registro do vendedor original;
• Sublinhado em vermelho – a primeira mudança;
• Sublinhado em marrom – a última mudança.

E qual foi o meu erro e como eu corrigi, afinal?

O problema aqui foi pensar como um processo transacional, ou seja, quando ele compara a coluna salespersonid, para mim, o Pentaho pegava o vendedor 274 de uma fonte e varria a outra fonte procurando todos os vendedores 274 e então, avaliava.

Como o processo não é transacional e sim uma comparação, eu criei o processo com a configuração errada. O que daria certo apenas na primeira execução, na segunda comparação em diante. Então, quando o job rodasse no segundo dia avaliando as mudanças, bem, iria causar problemas sérios na dimensão.

A solução pensada foi um pouco mais simples e aqui sim, é baseada em joins.

Esta é a estrutura que cuida do delete dos dados enquadrada em vermelho.

Qual foi a linha de raciocínio para elaborar essa solução?

Primeiro, eu queria saber quais dados davam match um com o outro, e para isso, precisaria de uma transformação que realizasse um join. Avaliando as transformações, vi que o merge join se enquadraria melhor.

Utilizei as duas fontes de dados devidamente ordenadas, como já estamos fazendo, e as liguei na task configurando da seguinte forma.

Como eu sempre comparo os dados que vem da stage com o que está no warehouse, preferi o left join. Até pela fonte estar no lado esquerdo e esta configuração me atender.

Para confirmar que os registros deletados seriam devidamente encaminhados para seu destino, deletei algumas linhas do arquivo txt, e executei um preview.

• Destacado em roxo – tenho os dados que oriundos do warehouse;
• Destacado em azul – os dados que vem da stage;
• Enquadrado em vermelho – os dados que foram excluídos do txt da área de stage;
• Enquadrado em preto – os vendedores que possuem match entre as duas fontes.

Agora sim, como estamos utilizando um join todos ID’s dos vendedores são comparados e como o left join retorna tanto os match’s como os não match’s, logo se não retornou, podemos concluir que foi deletado.

Como é left join e a fonte do warehouse está no “lado direito”, o Pentaho está aplicando essa fonte no stage.

Com o  step de join configurado o que precisamos fazer é filtrar esses valores que não possuem correspondência e eliminá-los do data warehouse.

Aplico o filter rows no tratamento filtrando pela coluna SalesPKey_1, que é justamente a coluna do “lado esquerdo” que contém os valores deletados.

Após o filtro, basta criar as transformações que receberão tais dados e configurar o delete.

Aqui cabe esclarecer que como o filtro irá mandar apenas as linhas que serão excluídas, não há problema igualar pela coluna SalesPersonID.

Por fim, irei realizar um teste excluindo e atualizando alguns vendedores para mostrar o funcionamento do ETL.

O registro destacado em vermelho será deletado.

TRATANDO UPDATES SIMULTÂNEOS

Em um dado momento nos meus testes me deparei com a seguinte situação:

Algumas colunas atualizam com o tipo I e outras atualizam com o Tipo II. Elas podem atualizar ao mesmo tempo, mas, o horário de validade fica comprometido.

Irei exemplificar o processo para esclarecer o que ocorre. Em azul temos a coluna tipo I e vermelho, a tipo II.

Uma vez alterado o arquivo do STG, vou executar a transformação e consultar o banco, mostrando o resultado final. Perceba apenas na imagem anterior que as colunas do tipo II, a linha que possui a versão atual, a coluna Date_end possui a data máxima permitida pelo Pentaho.

Veja que por alguns milésimos de segundo a criação da segunda versão ocorreu primeiro, até aqui não há problema. Insert sempre será mais rápido que update.

O problema é que quando houve o update, além de atualizar as duas versões, atualização também a coluna “Date_end” da segunda versão, o que não deveria. Entenda que não é errado, mas pode levar a dúbia interpretação, o que  não é recomendado.

Para sanar essa questão, pensei na seguinte solução:

• Colocar o SCD Tipo I como primeiro;
• Colocar a transformação block this step until steps finish entre o Merge e o Switch e;
• Configurar o wait acima para esperar até que o SCD Tipo I e o delete sejam finalizados.

A nova estrutura com o remanejamento do block this step, destacado em vermelho. Resumida.

E aqui, a nova configuração. A execução desse ramo iniciará somente quando os outros concluírem seus processos por completo.

Utilizando a nova configuração, a coluna Date_end volta ao padrão normal esperado.

Deste modo, para o banco, o insert contendo a atualização dos vendedores para o banco termina primeiro do que o update. Mas podem ficar tranquilos que a atualização acontece antes, tanto que na segunda versão, ela já está atualizada com o novo valor da coluna “GENDER”.

É importante notar que como processos de update em banco de dados deletam primeiro inserir o novo depois, acabam sendo mais lentos do que o insert, como ocorre com o histórico.

CONCLUSÃO

Finalmente concluo o tão trabalhoso processo de controle híbrido de mudanças.

Foi bem desafiador desenvolver esse modelo, principalmente por não entender bem como as transformações responsáveis funcionavam corretamente no início.

Apesar do desafio, aprendi bastante e tenho a sensação de ter desenvolvido um bom processo de ETL e, principalmente, bem consistente.

Apesar de toda a estrutura, tenho a sensação que ainda cabe melhoras e que ele pode ser muito mais robusto e completo.

Outro ponto importante que gosto de destacar é a criação do log de captura de mudanças que dá bastante autonomia e controle das alterações ao desenvolvedor ETL, dispensando o DBA e poupando o banco de acessos triviais.

Por fim, irei me aprofundar mais para tentar melhorar esse ETL.

Espero que gostem, saúde!

Deus os abençoe!