Portfólio – Dimensão Produto II

Olá galera, como estão!?

Continuação direta do post anterior sobre a dimensão produto ao qual preferi dividir para falar exclusivamente do processo de controle de mudança.

Neste post quero mostrar o desenvolvimento de um controle mais assertivo para o tipo dois que cria um registro histórico de mudanças.

Além, planto as bases de um CDC (change data capture) no formato XLS, arquivo Excel, para que o próprio desenvolvedor ETL não precise requisitar consultas ao DBA ou qualquer outro administrador do banco de dados, caso haja. 

Neste arquivo, constará apenas as mudanças feitas na transformação. Seja atualização, delete ou novas inserções.

CONSTRUINDO O MODELO DE CONTROLE

Como dito, este post focará apenas no controle de mudanças da dimensão, então, mostrarei apenas seu funcionamento e como desenvolvi.

A princípio, esta foi a estrutura base criada. Irei explicar cada uma delas. Ela não termina aqui, há mais uma transformação externa que será acrescida ao final e que contém o arquivo de comparação.

Essa é a estrutura base, ao qual irei destrinchar por partes. O primeiro passo será explicar a comparação entre as fontes.

Na estrutura abaixo, utilizei duas transformações text file input para utilizar dois arquivos TXT’s como base. Um que é resultado da transformação do post anterior e outro que é da próxima transformação que mostrarei mais à frente.

O select values  na transformação é para garantir que a estrutura de dados de ambas as fontes possuam o mesmo tipo. Isso porque, quando comparadas no Merge Diff (“COMPARA_FONTES”), se forem diferentes, podem resultar em comparações inconsistentes.

NOTA: essa inconsistência ocorre muito quando estamos comparando decimais. O número de casas e o ‘tamanho’ do campo podem influenciar. Não é garantido que possa interferir, mas como eu identifiquei essa falha no meu teste, decidi manter desta forma, garantindo o resultado esperado.

A estrutura configurada no select values de ambas as fontes ficou desta forma.

Meu próximo passo foi configurar o Merge diff. O step compara as colunas desejadas categorizando de acordo. O campo chave serve para dar o match inicial entre as linhas e após comparar com as colunas escolhidas.

Utilize a transformação de sort antes de comparar as fontes para evitar problemas de match entre os key fields.

Esta foi a minha configuração. No link do post que deixei acima, vocês podem acessar a explicação de como configurar o merge diff de maneira correta.

NOTA2: para configurar de maneira correta o merge diff, aconselho a fazer teste unitário em cada coluna que desejar capturar a mudança e criar o registro histórico. No meu caso, precisei fazer um teste por coluna que está em “values to compare”; funcionou melhor assim.

DESTINANDO OS DADOS

Agora que já estamos com a primeira parte que irá comparar os campos de duas fontes diferentes, vamos atribuir a estes registros seus destinos apropriados.

A segunda parte consiste em distribuir os registros alterados para seus destinos corretos, e como disse no início do post, independe se são novos, deletados ou alterados.

Na transformação switch/case conseguimos destinar corretamente baseado no campo flagfield, que foi o campo criado pelo merge diff com as categorias atribuídas.

Nesta estrutura temos uma transformação para deletar produtos que não estão mais sendo fabricados e foram excluídos das tabelas, temos a transformação para realizar as mudanças e o default, para os valores que não sofreram nenhuma alteração.

O step nomeado “WAIT_DELETE”  é para evitar lock entre o “DELETA_REG” e o “SCD_TIPOII_DIMPRODUTO”. Ele não precisa estar necessariamente ligado ao delete basta configurar para que ele espere da seguinte forma.

Voltando o switch/case aqui eu mostro como configurei o destino de cada classificação. Veja que o campo base é a coluna flagfield, que atribuímos na opção: Field name do switch.

Não esqueça de atribuir o data type correto para o flagfield. Otimiza o processo.

CAPTURANDO AS MUDANÇAS

Para capturar as mudanças criei dois arquivos xls para servirem de log, um para os dados que foram deletados e o outro para os que foram inseridos  ou alterados durante o processo de ETL.

O pipeline é bem simples, mas captura os dados necessários. Ambos são iguais, o que mudou foi apenas o destino final para o arquivo Excel  que será criado. 

Abaixo o pipeline em destaque.

Por fim, o arquivo Excel configurado para anexação de transformação no Append, pois utilizarei um arquivo único para outras dimensões que utilizam esta estrutura.

TESTANDO O FUNCIONAMENTO DO CONTROLE DE MUDANÇAS

Até o presente momento, organizamos apenas a estrutura de fluxo de dados que virão de duas fontes e serão classificadas e destinadas para seus respectivos locais de processo.

Agora, o que iremos fazer é testar se as mudanças serão processadas de acordo ou não. E caso não, detectar as falhas e corrigi-las. 

Para este teste, alterarei o arquivo gerado na transformação anterior que trata da normalização dos dados e conformidade da dimensão produto. Esse arquivo é um csv que foi gerado ao final de todo aquele processo e claro, após a primeira carga.

A segunda fonte, que é a  que deu origem ao arquivo de referência, foi extraída do warehouse após a primeira carga e será a nossa base.

Irei alterar apenas as colunas que o merge diff controla e compara, deletar alguns registros e copiar outros alterando apenas o ProductID. Com o ID alterado, constará como novo na classificação.

Após as alterações, irei mostrar as classificações em cada um dos destinos, para facilitar.

A imagem abaixo reflete as classificações que o step merge diff criou para cada linha comparada entre as colunas que escolhi para o controle.

Podemos perceber que até aqui, a classificação funcionou. Então, avançando para o próximo teste, vamos verificar se tais linhas foram destinadas corretamente pelo switch/case.

A primeira imagem reflete o destino para os dados que foram alterados e inseridos no arquivo csv. A coluna ProductWeight não é controlada.

Conferindo o dummy  de delete, notamos que todos os dados que deletei do arquivo constam no destino.

E antes de executar toda a transformação, uma conferência no banco de dados consultando a tabela produto, vemos que todos estão devidamente gravados.

Os dados não se encontram alterados ou com outra versão de ETL.

Após toda esta conferência, executei o pipeline para teste e observação dos resultados.

O primeiro aspecto que observei foi o log gerado pelo PDI com a conclusão.

Circulado em maior e na cor vermelha, destaco que o primeiro processo executado foi o processo de delete, o que era de se esperar, já que bloqueamos o outro ramo da transformação com o block step.

Perceba que destacado na caixa marrom, o WAIT_DELETE segura todo o processo, inclusive a escrita no xls de log do próprio delete. Isso mostra que atendeu nossas expectativas de evitar o lock.

Já sublinhado em azul, o SCD_DIMPRODUTO vai após toda a conclusão do “ramo” do delete e realiza as suas alterações de acordo com o configurado.

Notamos na imagem acima que a transformação está funcionando corretamente e que os registros foram propriamente criados. 

E podemos conferir também, os novos registros que foram inseridos na dimensão produto. Os dados constam como se fossem novos.

Ao fim da execução e conferência, vimos que os testes foram bem sucedidos e que todo o processo saiu conforme o desejado, podemos agora avançar e conferir os logs de execução com as linhas que foram processadas.

CHANGE DATA CAPTURE – GERENCIANDO MUDANÇAS COM EXCEL

O processo de change data capture (cdc) é um processo antigo e que já foi muito utilizado em bancos relacionais. Muito comum ser disparado via trigger, o controle de alterações era mapeado desta forma.

O problema de gerenciar as mudanças utilizando este recurso é que ele pode se tornar oneroso com o tempo e causar bastante lentidão no ambiente. Como cada alteração dispara uma trigger que irá gerar um comando, dependendo da quantidade de dados, o consumo de recursos de hardware pode ser elevado.

Pensando nisso e para evitar ao máximo lentidão no banco transacional, criei o esquema de captura de log que mostrei em imagens anteriores. Agora, podemos conferir se e como essas alterações foram capturadas pelo Pentaho.

Aqui, os dois arquivos criados na minha pasta destino, como configurado nas transformações excel writer. 

Veja que criei uma para insert e update e outra para o delete. 

A primeira imagem que mostrarei com o resultado do log será do delete dos registros da dimensão.

A imagem está resumida, mas se criarem o processo em casa, verão que todas as colunas da dimensão são gravadas no arquivo xls.

O procedimento ainda tem o benefício de puxar a coluna flagfield do merge diff informando que tipo de processo determinado registro sofreu.

O mesmo é claro, também se aplica ao step dimension lookup/update.

As duas categorias do flagfield constam no arquivo de log. O único ponto negativo que identifiquei é que em ambos os casos não foi possível puxar os campos de controle criado pelo dimension lookup/update. Não até a conclusão deste post.

EXTRA – ARQUIVO FINAL

No início do post eu mostrei que comparava duas fontes diferentes para o controle de alterações; sendo uma resultante do processo de transformação dos dados que vêm da área de stage e a outra fonte, que vem do warehouse  em si.

Para criar esse esquema, pensei em uma solução bem simples e que é executada ao final da carga inicial, ou seja, a primeira carga. Como as comparações serão criadas da segunda carga em diante, não há problema em deixar neste modelo.

Assim sendo, esta é a estrutura final do meu job para o tratamento da dimensão produto.

.

Dentro deste último job faço uma consulta na tabela dimensão produto, e os valores retornados, crio um arquivo csv que será uma das fontes de comparação na transformação anterior “CHECK_SCD_DIM_PRODUCT”.

CONCLUSÃO

Neste post tentei evoluir mais o processo de controle de mudanças de uma dimensão apresentando um modelo mais robusto e completo.

Aproveitei essa base para dar o start no processo de change data capture que é extremamente importante e permite a manutenção de um ETL mais transparente e auditável. Carece de melhorias.

A princípio, ainda não pensei em como posso criar uma comparação entre todas as linhas da dimensão, independente da versão daquele determinado registro; embora já tenha uma breve ideia.

Consegui mostrar que dentro do processo, é possível inclusive deletar registros que não estão mais no banco OLTP sem causar problemas no desenvolvimento do ETL.

O único problema aparente está na normalização da coluna ProductSize por conta do execute sql script; sua execução está um tanto quanto inconsistente e isso está atrapalhando o resultado; ainda pensando em uma forma de corrigir tal problema.

No mais, foi um tratamento que gostei de desenvolver e que serviu de base para o próximo processo de ETL e controle que postarei em breve!

Espero que gostem, Deus os abençoe!

Saúde!