Todos gostamos de registrar os momentos da vida e o tempo todo e tiramos "fotos instantâneas" com nossos smartphones sempre pensando que no futuro podemos voltar e relembrar o que foi vivido.
E porque não podemos fazer isso com os dados da empresa?
A prática de Snapshot com os dados nos permite voltar a uma data para avaliarmos como as coisas estavam no momento. Parece uma tarefa simples mas não é pois voltar os dados no tempo normalmente exige várias cabeças pensantes ainda mais quando não se tem o Delorean à disposição.
Segue um case e sua solução que poderá lhe ajudará em algum de seus projetos de BI.
Requisito do cliente: voltar no tempo e saber como os contratos estavam em determinado fechamento ou data de modo que seja possível consultar o contrato no momento.
Dados disponíveis: situação atual dos contratos e "aparentemente" uma lista do histórico de tudo o que houve com o contrato ao longo do tempo.
Solução aplicada:
Obviamente pensei em criar uma rotina de ETL que registrasse uma cópia atual dos contratos a partir de hoje - pois seria mais fácil, mas o cliente desejava o passado então foi necessário usar a tabela de histórico que nos ajudou a voltar no tempo.
Passo 1. Elaboramos um ETL que extrai todo histórico de registro dos contrato registrados no ERP e armazenamos em uma tabela fato. Entendemos que os dados a nossa disposição teríamos liberdade para manuseá-los no processo além de utilizar outros recursos de banco de dados a nosso favor como criação de índices para otimizar o processamento.
Tabelas fato_contrato e fato_contrato_historico.
Passo 2. Estudamos qual era o histórico que a tabela registrava e constatamos que não tinha registro do status do contrato versionado e sim apenas quais as operações o mesmo passou e outros registros que nos interessavam.
Então definimos com o keyuser qual status cada operação representa na linha do tempo através de uma planilha auxiliar. Esse foi um momento de muita discussão pois iríamos gerar os snapshots apenas das operações definidas.
Passo 3. Definimos com o cliente quais seriam as datas dos snapshots: a cada sábado e no último dia de cada mês. Então criamos uma SQL que nos retornava essa lista de datas.
Passo 4. Elaboramos uma consulta SQL com uma subquery com a estrutura abaixo.
E a consulta ficou conforme a seguir:
Considerações:
Consulta externa: aqui filtramos para trazer apenas os contratos emitidos até a data desejada.
Consulta interna: os pontos críticos são: a) reduzir os dados do histórico para retornar os registros até a data desejada; b) ordenamos os dados em ordem decrescentes c) aplicando um limit 1 - retornando apenas o último status desejado. d) Incluir o filtro para retornar somente os status <> de N/I pois estes não me interessavam.
Resultado: Aqui apresentamos o status correspondente a operação definido junto com o keyuser no passo 2.
Passo 5 e último. Após isso automatizamos a solução utilizando o Pentaho Data Integration e gravando os snapshots diários dos contratos.
Com essa solução conseguimos voltar os contratos com seu status e outros registros da tabela de histórico na linha do tempo permitindo o cliente avaliar diversos indicadores históricos.
Se esse artigo lhe trouxe insights para possíveis soluções de seus projetos não deixe de compartilhar, isso nos motiva a compartilhar ainda mais nossas experiências com a comunidade.
Boa