Fala galera, beleza??
Dando continuidade ao projeto e melhoria contínua dos processos de controle de mudanças, quero mostrar uma forma que desenvolvi de controlar as mudanças de forma híbrida, ou seja, utilizando o tipo 2 (registro histórico) e tipo 1 (sobrescrita).
O processo foi um pouco complexo e não ficou tão bonito, mas funcional. O controle envolve criação histórica, delete de registros e atualização de valores atualizados, mas que não registrarão dados históricos.
CRIANDO A DIMENSÃO
Antes de desenvolver um controle híbrido, gostaria de mostrar a criação da dimensão como um todo e os processos de normalização.
Por ser uma dimensão pequena com poucos vendedores, o processo de normalização em si não foi complexo e apesar de utilizar transformações simples, nesta em si, quero destacar o uso do step Formula.
Embora nunca tenha entrado afundo no seu uso, gostei das opções que ela permite e seu funcionamento lembra muito o Excel. Veremos adiante como utilizar, ou, utilizar de forma básica.
Essa é a estrutura base da normalização da dimensão vendedor. Como na query base eu não coloquei a coluna salário, precisei da transformação stream lookup para buscar em outro arquivo os salários correspondentes.
Essa é a estrutura inicial de pesquisa dos salários dos vendedores. Como é uma base americana, é medido como hora/trabalho e a coluna se chama RATE.
Um step que foi bastante utilizado aqui foi o value mapper. Esta transformação quando identifica um valor, substitui por outro, seja o tipo que for.
Um conselho que deixo antes de utilizar essa transformação é adicionar o string operations. Com esse “pré-tratamento”, removemos qualquer espaço em branco que uma determinada linha pode ter, e isto influencia o value mapper.
Com as linhas previamente normalizadas o value mapper se torna mais preciso. Além, é uma transformação que quando utilizada com valores únicos, funciona melhor do que utilizar update.
Neste processo em si, utilizei para substituir os espaços com nulls das colunas por novos valores baseados em outra coluna.
Nesta imagem, a coluna base foi a JobTitle e a coluna de destino SalesPContinent.
Uma transformação pouco usual que utilizei aqui, devido a falta de informação de Quota e Bonus dos gerentes, foi o Group By.
Como é uma transformação bem simples, seu impacto é quase nulo, não há problema em usar. Como cada gerente atua em um continente diferente (são três), utilizei a coluna “SalesPcontinent” como agregador.
O resultado do agrupamento gerado pela transformação.
UTILIZANDO O STEP FORMULA
O uso da transformação formula foi bem pontual e utilizada para alguns cálculos específicos.
Este step possui diversas opções de uso e é bem versátil, um pouco complicado para dominar e não muito intuitivo para construir suas fórmulas. Olhe sempre a sintaxe e fique atento caso acuse algum tipo de erro.
Os cálculos utilizados foram bem pontuais, um para a quota e outro para bônus. Quis entender também como funcionam as operações de data para cálculo de tempo empregado e a idade do vendedor.
Algumas dicas para este step:
- Sempre que for utilizar coluna de alguma tabela, coloque-a entre colchetes.
- A fórmula maior engloba todos os cálculos “menores” que gerarão o resultado.
Veja na imagem abaixo que tanto as colunas hiredate como birthdate estão dentro de colchetes e a fórmula “menor” TODAY, englobada na datedif.
Após, bastou substituir os valores e carregar o warehouse utilizando o dimension lookup/update para criar as colunas de controle.
Não esqueça de criar o arquivo txt ao final da carga. Este arquivo servirá de base para o processo de slowly changing que será criado.
CRIANDO O MODELO DE CONTROLE HÍBRIDO
O modelo de controle híbrido foi um pouco mais complexo de se criar e talvez seja um pouco confuso ao primeiro olhar, mas irei explicar detalhadamente e por partes, cada etapa do processo.
Meu desejo aqui era melhorar a forma como o Pentaho atualiza e controla mudanças na stream de dados que são enviados ao warehouse.
Este modelo envolve um controle do tipo 2 (histórico), tipo 1(sobrescrita) e o delete de dados. Como eu quero que apenas algumas colunas gerem registros históricos quando atualizarem, quis separar o processo desta forma para maior controle.
A estrutura desenvolvida ficou desta maneira, no job do Pentaho.
Duas situações que preciso explicar antes de avançar no post:
- No duplo traço único em vermelho junto com os dois steps enquadrados, são as fontes que utilizarei aqui. Decidi assim para não correr risco delas divergirem e acabarem criando um processo inconsistente.
Assim como no post anterior, o esquema de comparação é o mesmo. - Em preto, está um step chamado blocking step, que trava o “MERGE_SCD_TP1” até que a última linha seja processada.
- Junto com o blocking step, em marrom,, estão os block this step until steps finish. Mais uma vez, estes são para evitar lock no banco quando houver uma determinada operação de insert/update/delete.
- O primeiro block “ESPERA_DELETE”, bloqueia até que o ramo que deleta registros seja concluído, pelo menos, o step de nome “DELETA_REG”.
- E o segundo chamado “ESPERA_DIMVEND_TP2”, espera o “SCD_DVENDEDOR_TP2”, que é o controle que cria registro histórico.
- E em azul, as transformações que gerenciam as mudanças.
DESENVOLVENDO O CONTROLE HÍBRIDO
Este é o início da comparação entre as duas fontes, como já tratei anteriormente, utilizei o merge diff para categorizar os dados entre idênticos, atualizados e deletados.
O primeiro merge diff “MERGE_SCD_TP_2”, é o que irá gerenciar as tanto os controles históricos quanto os dados que possivelmente serão deletados.
E o “MERGE_SCD_TP1” controlará apenas as colunas que não irão gerar nenhum tipo de histórico.
Como já apresentei a vocês a forma como se desenvolve um modelo de slowly changing dimension tipo II, vou mostrar primeiro quais as configurações utilizar para desenvolver o modelo tipo I utilizando o dimension lookup/step.
Único destacado em vermelho é o dimension lookup/update que iremos utilizar.
Como já sabemos como configurar o merge diff irei mostrar apenas as colunas que escolhi para este ramo do ETL.
Na imagem unificada, as colunas do merge diff e o status do switch/case. Como aqui eu só quero as linhas que sofreram mudanças, selecionei apenas o status changed.
Como não quero bloquear o ETL por transações simultâneas, coloquei o wait ali, como explicado acima. Lembrando que ele só irá carregar os dados quando outro procedimento finalizar.
Agora, vamos explorar o dimension lookup/update e entender sua configuração.
A diferença quando utilizamos esta transformação e não a insert/update (neste post tem a explicação) é a vantagem de poder controlar a hora que tal linha foi atualizada.
Mesmo que não gere um versionamento, o controle é feito pela coluna End_Date que será atualizada sempre que uma mudança ocorrer na coluna configurada.
Enquadrado em vermelho, são as colunas que decidi que serão atualizadas pelo tipo I, sem a necessidade de um versionamento de linha.
Se qualquer outro campo que estiver fora do padrão de seleção criado no merge diff for atualizado, será ignorado.
Irei alterar o csv que vem do stage para mostrar o funcionamento. Irei alterar dois campos, um que é controlado por esse ramo do ETL e outro que não, reparem no resultado final.
Em azul está a alteração da coluna MaritalStatus e em vermelho a alteração do Nome (coluna não comparada). Antes de executar o teste, fiz uma consulta rápida no banco para checar o estado natural.
As linhas destacadas em preto serão alteradas.
Perceba que na pré-visualização do resultado da transformação no Pentaho, somente a primeira linha foi destinada à transformação.
Executando a transformação e uma consulta na dimensão, temos o seguinte resultado.
Como a coluna firstname não estava no merge diff de comparação, foi completamente ignorada pelo processo que atualizou somente a coluna maritalstatus.
Outro ponto importante é a coluna date_end que registra a hora que o update ocorreu. Se eu usasse a transformação insert/update precisaria de uma transformação que extraísse a hora e data do sistema ou uma coluna com constraint de default com sysdatetime na própria tabela.
TESTANDO O MODELO HÍBRIDO
No início do post mencionei que a ideia desta dimensão era trabalhar com um modelo híbrido de controle criando histórico somente para colunas desejadas.
No tópico anterior mostrei o scd I utilizando dimension lookup/update e neste, vamos testar os dois ramos do processo, utilizando mudanças simultâneas.
Primeiro passo foi realizar um truncate na tabela, uma nova carga e nova consulta.
Estas são as alterações no csv que irão para o warehouse. Em marrom estão as mudanças do tipo II, em vermelho do tipo I e em azul, quero mostrar o comportamento quando duas colunas de dois tipos são alteradas de forma simultânea.
Com a conclusão da transformação, vamos analisar como ficou a tabela dimensão vendedor após essa pequena alteração.
Em vermelho e marrom, estão as mudanças padrões que já conhecemos. As colunas de versão e hora de início e fim foram atualizadas com sucesso, mostrando que a classificação está funcionando corretamente.
O que nos resta aqui é entender como o Pentaho gerenciou a alteração simultânea. Perceba que no enquadramento azul, tanto a coluna firstname quanto maritalstatus, constam atualizadas e em ambas as versões.
Para entender isso, vamos olhar para as duas colunas start_date e end_date. Repare que o insert da primeira versão consta na start_date como: “2021-05-09 04:25:02.187” e sua hora de atualização na end_date como: “2021-05-09 04:45:30.277”. Mas se percebemos bem, a linha que contém a versão 2, o start_date começa às “2021-05-09 04:45:28.977” que é antes do end_date da versão 1.
Isso quer dizer que o Pentaho criou o versionamento primeiro, para a coluna firstname, e logo em seguida atualizou a coluna maritalstatus.
Como a transformação do tipo I tem o comando para atualizar a coluna end_date com o horário do último update, acabou sobrescrevendo a alteração feita pelo tipo II em ambas as versões do vendedor com id 278.
O PROBLEMA
Como podemos perceber, precisei eliminar o ramo que deletava os registros que não estavam mais no banco. No caso, que vinham da stage e iam para o warehouse.
Ainda que eu pudesse implementar algumas soluções, como por exemplo, utilizar o append stream o ETL ficaria muito mais complexo e confuso, então preferi deletar e adicioná-lo futuramente em outro job.
E aqui entra um pequeno desafio: vocês conseguem imaginar o motivo pelo qual o Pentaho age desta maneira quando lida com versionamento de linhas?!
Quem quiser responder, pode entrar em contato por qualquer rede e explicarei o meu entendimento.
CONCLUSÃO
Essa foi mais uma dimensão que gostei muito de tratar por apresentar um desafio quanto ao processo de criação do slowly changing híbrido!!
Ao final, detectei uma “falha” no processo que poderia tornar a dimensão inconsistente, que é o caso do delete, removendo e deixando que a estrutura trate apenas dos dados novos e atualizados.
Continuo mantendo o uso do CDC para captura das mudanças, como vimos no post anterior.
Ainda preciso pensar numa forma de automatizar o delete no ETL de forma correta, principalmente quando houver versionamento de linhas.
Por fim, pensarei em uma forma de evoluir ainda mais esse modelo de controle tornando-o ainda mais completo!
Deus os abençoe, saúde!
Espero que gostem do post!
ManageBI 