Data Lake e Integration Services: construindo e carregando o tempo e as fatos

Fonte de dados - Apresentação

Olá! Me chamo Victorino Vila e estamos iniciando mais um curso da planilha de formação em Business Intelligence (Inteligência de Negócios), seguindo o projeto de implementação de BI na AtacaDez. O foco será a carga de dados do tempo e da fato.

Audiodescrição: Victorino é uma pessoa de pele clara e olhos escuros. Seus cabelos são curtos, lisos e grisalhos. Usa barba e bigode e está vestindo uma camiseta azul-marinho.

No curso anterior, carregamos as dimensões. Agora, é o momento de carregar as fatos e preparar a lista de datas para a preparação da dimensão tempo no Data Warehouse (Armazém de Dados).

O que vamos aprender?

Neste curso, aprenderemos alguns aspectos diferentes sobre o Integration Services (Serviços de Integração), tais como ler dados de arquivos externos com nomes genéricos. Lembrando que os dados referentes ao cabeçalho da nota fiscal, do item da nota e de compras possuem diferentes nomes de arquivo.

A questão que se levanta é: como ler um arquivo cujo nome não sei previamente qual será? Essa é uma das respostas que obteremos ao longo deste curso.

Também aprenderemos algumas técnicas de criação de fluxo de dados, como selecionar apenas as colunas distintas usando um critério específico. Por exemplo, como se estivéssemos simulando o comando SELECT distinto de uma tabela.

Também exploraremos como trabalhar com variáveis dentro do Integration Services e criaremos uma estrutura de log. Essa estrutura é essencial para registrar o início, o fim e os possíveis erros que poderão ocorrer durante o processo. Esse arquivo de log será criado externamente, em algum diretório na máquina.

Por fim, aprenderemos como carregar pacotes diferentes, ou seja, como gerenciar um fluxo onde cada pacote do Integration Services é carregado separadamente.

Ao final, com o projeto de criação e carga do Data Lake concluído, faremos a documentação no Notion. Assim, ao terminar o curso, teremos todo o processo do Data Lake finalizado.

Próximos passos

Com isso, poderemos avançar no projeto de implementação do Business Intelligence na empresa AtacaDez em cursos futuros.

Vamos nessa? Agradeço pela atenção e nos vemos no próximo vídeo.

Fonte de dados - Recuperação do projeto

Neste vídeo, vamos discutir a recuperação do projeto. Antes de prosseguir com este curso, é necessário ter instalados em seu computador os seguintes softwares:

• SQL Server 2022;
• SQL Server Management Studio;
• Banco de dados do Data House já criado com as tabelas (mas estas devem estar vazias);
• Banco de dados do Data Lake contendo os dados das tabelas de dimensões da empresa, produto e cliente carregados;
• Fontes de dados de ETL baixados;
• Estrutura de diretórios para o recebimento dos dados de ETL, estrutura essa que será visualizada pelos projetos de extração;
• SQL Server Data Studio;
• Solução da carga Data Lake aberta no Visual Studio.

Tente replicar a mesma configuração e diretórios criados por mim no curso anterior caso você ainda não os tenha. Sendo assim, se você está em um computador sem nenhuma configuração prévia e não participou do curso anterior, sugiro que você interrompa nesse momento e faça primeiro o curso anterior.

Isso porque muitos dos conceitos que nós vamos explorar neste curso foram introduzidos no anterior. Este curso é uma continuação.

Se você realizou o curso anterior, mas está utilizando outra máquina, siga atentamente as informações relativas à configuração do ambiente contidas neste curso. Recomendo novamente que crie todo o seu ambiente seguindo a mesma estrutura de subdiretórios que criei.

Isso será descrito no texto de recuperação do ambiente, presente nesta atividade extra do curso.

Após você recuperar o ambiente e ter todo o projeto na máquina, realize um teste de carga no Data Lake para as dimensões produto, empresa e cliente, para verificar se tudo está funcionando corretamente. Caso você tenha feito o curso anterior e esteja na mesma máquina com tudo já configurado, não há necessidade de preocupações.

Estou no mesmo contexto que estava quando realizei o curso anterior, mas ainda assim irei testar se o meu ambiente está funcionando corretamente. Dessa forma, vamos verificar o ambiente que precisa estar configurado no computador para podermos prosseguir com este curso.

Verificando a configuração do ambiente

No Server Management Studio, iremos observar os bancos de dados SQL Server que precisamos para este curso.

Dentro da área de banco de dados, acessamos clicando no ícone de mais (+) e expandindo a pasta "Banco de dados", onde encontramos o DW_ATACADEZ e o DL_ATACADEZ - o primeiro representa o Data Warehouse e o segundo o Data Lake.

No DW, encontramos as tabelas criadas durante um dos cursos de formação do projeto de Business Intelligence. No DL_ATACADEZ, estão três tabelas criadas no curso anterior, já contendo dados, pois o projeto realizou a carga de dados nesses locais. Para verificar isso, clicamos na tabela dbo.tbl_cliente e depois na opção "Selecionar 1000 Linhas Superiores".

Observem que na área de resultados obtemos a tabela.

Para fins de teste, vamos excluir os dados das primeiras três tabelas usando o comando DELETE seguido do nome das tabelas, conforme o seguinte comando:

DELETE FROM tbl_cliente
DELETE FROM tbl_empresa
DELETE FROM tbl_produto

Selecionamos o comando e executamos.

Quanto à estrutura de diretórios, teremos um chamado ETL, que baixamos durante o curso anterior. Vou abrir na minha máquina o diretório.

Neste curso, também iremos fornecer o link para o download desse mesmo diretório, caso ainda não o tenha feito. Dentro dele, encontramos os dados das fontes de dados e um diretório de scripts que utilizaremos ao longo do curso.

Além disso, temos uma estrutura de diretórios dentro desse subdiretório de "Fontes > DATA", onde temos duas pastas: DIMENSOES e FATOS. E dentro de dimensões, temos as três dimensões que usamos no processo do curso anterior, dentro de cada uma TEMOS UM DIRETÓRIO IN, com as fontes para carregar clientes, empresas e produtos.

• DIMENSOES
  • CLIENTE
    • IN
      • CADASTRO DE CLIENTES.xlsx
      • REGIOS DOS ESTADOS.csv
  • EMPRESA
    • IN
      • EMPRESA.CSV
  • PRODUTO
    • IN
      • FORNECEDORES.XML
      • FORNECEDORES.xsd
      • PRODUTO.JSON

Também temos o projeto inicial do curso, que corresponde ao projeto final do curso passado. Este projeto estará disponível para download por meio de um link que compartilharei.

Vamos abrir o projeto com o Visual Studio 2022. Para isso, clicamos com o botão direito em DatawarehouswAtacaDez.sln dentro de projetos e escolhemos as opções "Abrir com > Visual Studio 2022".

Nosso objetivo é executar este processo para verificar se conseguimos recarregar as três tabelas do Data Lake. Se isso funcionar, estaremos prontos para começar este treinamento.

Agora, estamos aguardando a abertura do projeto no Visual Studio.

Em seguida, ao abrir temos a carga das empresas, clientes e produtos, que foi o que realizamos no curso anterior. Vamos para a tela inicial de fluxo de controle e clicamos em "Iniciar" na parte superior central. A expectativa é que a carga desses três processos ocorra. Aguardaremos um momento e obtemos o seguinte resultado:

Execução do pacote concluída com êxito.

Tudo rodou como esperado. Temos um sinal verde em cada caixa, indicando que a execução do pacote foi concluída com êxito.

Se retornarmos ao Data Lake e executarmos uma consulta na tabela cliente, verificaremos os dados dos clientes que foram carregados. O mesmo se aplica às tabelas empresa e produtos, ambas com seus dados devidamente carregados.

Os comandos abaixo são executados separadamente para ser visualizado os dados de cada tabela.

SELECT * FROM tbl_cliente
SELECT * FROM tbl_empresa
SELECT * FROM tbl_produto

Conclusão

Portanto, é crucial que configuremos nossas máquinas para que esse processo funcione corretamente, permitindo que possamos continuar o curso a partir deste ponto.

Um grande abraço e até o próximo vídeo!

Fonte de dados - Criando a estrutura de diretórios

Vamos criar a estrutura de diretórios para armazenar os arquivos de notas fiscais e de compras provenientes de fornecedores. Dentro do diretório ETL, que criamos ao baixar o arquivo ETL.zip no curso anterior e fornecemos um link para baixar o arquivo compactado no início deste curso.

Dentro do diretório ETL da nossa máquina, encontramos dois subdiretórios importantes neste momento: o subdiretório compra e o subdiretório mov.

• ETL
  • compra
  • mov

No subdiretório compra, há uma série de arquivos com extensão .csv, onde o prefixo é compras e o sufixo é o ano e mês de referência do arquivo. Cada um desses arquivos consolida todas as compras realizadas pelos fornecedores em um mês específico.

Dentro do subdiretório mov, temos dois grupos de arquivos. O primeiro grupo tem como prefixo movimento_itens e também o ano e mês de referência. O segundo grupo apresenta movimento_notas, seguido do ano e mês.

O Movimento_notas representa o cabeçalho da nota fiscal, enquanto movimento_itens representa os itens da nota fiscal.

Uma nota fiscal é composta por informações que são aplicáveis à nota como um todo, tais como o nome do cliente, a data e o número da nota, bem como várias linhas, uma linha para cada item. Observe em uma nota de supermercado, ela possui uma estrutura única que vale para a nota toda diversos subitens.

No arquivo movimento_notas, encontramos as informações que são relevantes para a nota como um todo. No movimento_itens, estão registradas as informações relativas a cada item da nota. Estes são os arquivos que a equipe de TI irá nos enviar, permitindo-nos realizar a leitura dos dados primeiro para o Data Lake.

Dessa forma, é necessário organizar a estrutura para receber esses arquivos.

Organizando a estrutura de recebimento de arquivos

No subdiretório DATA, onde os arquivos serão recebidos, dentro do subdiretório FATOS, vamos criar dois outro subdiretório FATOSs subdiretórios, NOTAS e COMPRAS. Para isso, selecionamos o subdiretório FATOS e escolhemos a opção "Novo > Pasta".

• FATOS
  • NOTAS
  • COMPRAS

Dentro desses subdiretórios, começando pelo COMPRAS, criamos dois subdiretórios mais, IN e OUT. Estamos criando o subdiretório OUT, diferente do que fizemos no subdiretório das dimensões. No subdiretório das dimensões, apenas o diretório IN foi criado. Mas neste caso, também temos um diretório OUT.

• COMPRAS
  • IN
  • OUT

Depois de copiar estes subdiretórios, IN e OUT, para dentro do subdiretório NOTAS.

• NOTAS
  • IN
  • OUT

No diretório mov, onde temos as notas fiscais, vamos pegar o Movimento_Itens_2021_01.csv, que é o primeiro arquivo em ordem cronológica que estamos recebendo, e buscamos o equivalente ao cabeçalho da nota fiscal: Movimento_Notas 2021_01.csv.

Ao selecionarmos esses dois diretórios, iremos copiá-los e colá-los no diretório NOTAS, dentro do diretório IN.

• NOTAS
  • IN
    • Movimento_Itens_2021_01.csv
    • Movimento_Notas_2021_01.csv

Vamos realizar o mesmo procedimento para as compras. No entanto, no diretório ETL, dentro do diretório compra, selecionaremos o arquivo Compras_202101.csv. Posteriormente, copiaremos e colaremos este arquivo dentro do diretório COMPRAS, no diretório IN.

• FATOS
  • COMPRAS
    • IN
      • Compras_202101.csv

Agora, entenderemos por que temos um diretório IN e um diretório OUT neste cenário.

Diretório IN e Diretório OUT

Diferentemente das dimensões, os nomes dos arquivos que irão carregar os movimentos sofrem alterações, pois todos os arquivos possuem um sufixo com a data de referência, correspondendo ao mês de referência daquele arquivo.

No caso das dimensões, o nome dos arquivos sempre será o mesmo: no diretório cliente, a planilha sempre terá o nome CADASTRO DE CLIENTES.xlsx e o .csv sempre será REGIOES DOS ESTADOS.csv. Esse padrão foi acordado com a área de TI da AtacaDez, que enviará os arquivos com esses nomes específicos.

Nossa relação com a equipe de TI, que nos fornece as fontes de dados, é muito importante. Na área de Business Intelligence, precisamos interagir com a equipe de TI e combinar as regras.

Por exemplo, se o departamento de TI enviar um arquivo com um nome diferente, o processo de carregamento pode ser interrompido. Já os arquivos de movimento, como o de compras, devem ter nomes distintos para que possamos identificar a data referida no título do arquivo. Lembrem-se, esse diretório IN de COMPRAS será o diretório de chegada.

Então, o que nosso processo de carga terá que fazer de diferente do que fizemos com a dimensão? Toda vez que um arquivo de movimento chegar, seja no diretório de compras, seja no de notas, nós o leremos e depois moveremos o arquivo do diretório IN para o diretório OUT.

Portanto, no final do processo, o diretório IN estará vazio, pronto para receber novos arquivos. Já no diretório OUT teremos a lista dos arquivos que o processo de extração de dados para o Data Lake já leu. Essa é uma forma de saber o que já foi lido e o que deve ser lido novamente.

É claro que, se movermos os arquivos Movimento_Itens_2021_01.csv e Movimento_Notas 2021_01.csv que estão em OUT dentro de NOTAS para o diretório IN e processá-los, leremos novamente o mês de janeiro de 2021. No entanto, nosso processo não trará problemas.

Lembre-se, quando implementamos as dimensões, gerenciamos a gravação dos dados no Data Lake de tal maneira que, quando encontramos um registro, o substituímos; quando não encontramos, incluímos. Portanto, ler o mesmo mês várias vezes, da maneira como projetaremos o processo no Integration Service, não será um problema.

No entanto, quando temos nomes de arquivos diferentes, a boa prática indica que devemos criar um diretório IN e um diretório OUT. Quando o processo ETL lê os dados do diretório in, ele deve mover esses arquivos para o diretório OUT.

Então a origem das informações, tanto para as notas fiscais, quanto para compras, serão os diretórios "FATOS > NOTAS > IN" e "FATOS > COMPRAS > IN".

Posteriormente, o processo precisará copiar os arquivos do diretório IN para o diretório OUT.

Conclusão

Portanto, mantenha os arquivos compras_202101 no diretório "DATA > FATOS > COMPRAS > IN" e os arquivos Movimento_Itens_2021_01 e Movimento_Notas_202101 no diretório "DATA > FATOS > NOTAS > IN". Combinado?

Vamos encerrar este vídeo por aqui. Até breve!

Sobre o curso Data Lake e Integration Services: construindo e carregando o tempo e as fatos

O curso Data Lake e Integration Services: construindo e carregando o tempo e as fatos possui 193 minutos de vídeos, em um total de 60 atividades. Gostou? Conheça nossos outros cursos de Business Intelligence em Data Science, ou leia nossos artigos de Data Science.

Matricule-se e comece a estudar com a gente hoje! Conheça outros tópicos abordados durante o curso:

• Fonte de dados
• Carga de cabeçalhos de notas fiscais
• Carga dos Itens de notas fiscais
• Carga da dimensão tempo
• Logs de carga