Business Intelligence

ETL com Pentaho

Estamos quase concluindo o Business Intelligence – Do Problema Ao Dashboard. Hoje, entenderemos qual o mecanismo por trás do ETL, utilizando a ferramenta Pentaho. Se faltou ver alguma etapa anterior, pode conferir nos links abaixo o caminho que percorremos até chegar aqui:

Veremos agora o que nos aguarda neste artigo. Primeiramente veremos alguns conceitos de ETL, conhecer um pouco mais a ferramenta Pentaho, aprender a criar as transformações de dimensões e fatos com os componentes do Pentaho e por fim, construiremos a ETL do projeto de BI do Dados ao Cubo.

1. O que é ETL?

O ETL (Extract, Transform e Load), em tradução livre Extração, Transformação e Carga, são três atividades básicas. A primeira atividade é extrair os dados da origem (banco de dados, planilhas, arquivos de texto entre outros). A segunda atividade é transformar os dados de acordo com a necessidade e especificações (por exemplo, no banco de dados está salvo sexo como M e F e vamos transformar para Masculino e Feminino). E por fim, a terceira atividade é carregar os dados no destino (por exemplo, carga no data warehouse). Abaixo a ilustração desse processo.

ETL

Para dar agilidade nesse processo e facilitar a manutenção, surgem as ferramentas de ETL. Com as ferramentas de ETL, podemos construir pipelines de uma ponta a outra de uma forma mais simples. O Pentaho é uma das ferramentas que fazem esse processo. Assim poderemos criar um modelo de ETL e executar sempre que for preciso carregar novos dados ou atualizar os dados já carregados.

2. O que é o Pentaho?

O Pentaho é uma suíte de soluções para BI open source. Uma dessas soluções é o Pentaho Data Integration ou PDI (para os mais íntimos), também conhecido pelo codinome Kettle. Com o PDI é possível criar soluções completas de ETL. 

Dessa forma, poderemos extrair os dados do banco relacional. Em seguida, transformar conforme a necessidade do negócio. E por fim, carregar os dados no modelo multidimensional. Dessa forma, será possível gerar as dimensões e fatos. Agora, veremos na prática os componentes do Pentaho.

3. Componentes do Pentaho

Para construir o ETL do projeto de BI do Dados ao Cubo, precisaremos de componentes do Pentaho. Isso é só uma pontinha do que o Pentaho faz.

3.1. Repository Manager

O primeiro componente que veremosé a configuração do repositório do pentaho. É neste repositório que ficarão salvos todos os arquivos criados pelo pentaho. Então vejamos como realizar esta configuração.

Configurando o repositório: Connect > Repository Manager > Add > Other Repositories > File Repository

Pentaho

No pop-up acima, se faz necessário informar o nome que será apresentado para conexão e o caminho onde os arquivos do pentaho serão salvos. Quando acessar novamente, basta se conectar ao repositório já criado. Finalizada a configuração do repositório, já podemos criar uma transformação.

3.2. Transformation

As transformações, são o coração do ETL. Após sua criação, adicionaremos os steps (componentes do Pentaho) e cada um tem a sua função na transformação dos dados. Para criar uma transformação: File > Novo > Tranformação

Pentaho


Após criada a transformação poderemos inserir os steps (componentes) que realizam as transformações. Veremos a seguir os detalhes de alguns componentes. Mas antes, vamos configurar a conexão com o banco de dados, pois precisaremos deles mais à frente nas transformações.

3.3. Database Connection

A conexão é responsável por fazer a comunicação com o banco de dados, tornando possível fazer as operações de inserção, atualização, delete e muitas outras. Criando uma conexão com banco de dados: File > Novo > Database Connection

Pentaho

Em seguida, as configurações de acordo com o tipo de banco de dados:

Pentaho

Após selecionar o tipo do banco de dados e a forma de acesso, será preciso informar alguns detalhes importantes: o nome da conexão, do host, do banco de dados, a porta de conexão, além de usuário e senha. 

Resumindo o que fizemos até aqui, configuramos o repositório do Pentaho, criamos uma transformação e configuramos a conexão com o banco de dados, podemos seguir então para os componentes.

3.4. CSV File Input

Pode ser preciso inserir dados de arquivos de texto, como o conhecido .csv. Para isso o Pentaho tem um componente de nome CSV File Input para essa operação. Vejamos os detalhes.

Pentaho

Para este caso acima, inserimos algumas informações necessárias para extração dos dados. Step name, aqui informamos o nome a ser exibido como identificação do step na transformação que estamos criando. No campo Filename, iremos informar o caminho completo do arquivo, inclusive a sua extensão. O Delimiter, vai identificar os delimitadores das colunas do arquivo. Um outro campo importante é o File encoding, onde informamos a codificação do texto.

Para finalizar, o botão Obtém campos, vai buscar as colunas com os tipos de dados e mais algumas configurações dos dados. Pode ser necessário utilizar outras opções da configuração, conforme os dados para extração.

3.5. Microsoft Excel Input

As planilhas de Excel são muito comuns nas empresas. Sempre que houver necessidade de input de dados que estão em planilhas, para esse problema vamos usar o step Microsoft Excel Input. Vejamos os detalhes do componente.

Pentaho

Mais uma vez, o Step name leva o nome a ser exibido como identificação do step na transformação. Para localizar o arquivo, vamos informar o File or directory em seguida, utilizar o botão Add e o arquivo vai aparecer em Selected Files. Na aba Sheets vamos informar o nome da planilha dentro do arquivo. Para finalizar, em Fields, vamos informar as colunas da planilha.

3.6. Table Input

Os bancos de dados são as maiores fontes de dados para projetos de BI. Dessa forma, podemos extrair informações de diversos sistemas das empresas, por exemplo: ERP, CRM ou qualquer outro sistema que tenha um banco de dados. Temos no Pentaho o componente Table Input para auxiliar nessa tarefa. Vamos aos detalhes.

Pentaho

Começamos com o Step name informando o nome a ser exibido como identificação do step. Lembra da conexão com o banco de dados que mostramos no item 6.3.3.? Agora podemos selecioná-la no campo Connection. Com a conexão informada, podemos informar o código SQL que vai extrair do banco de dados as informações que precisamos. Podemos utilizar o botão Preview para visualizar uma prévia dos dados, é também uma forma de validar se a consulta SQL está com algum erro.

3.7. Select Values

Ao extrair dados, seja com o Microsoft Excel Input, Table Input ou qualquer outro componente de input de dados, pode ser necessário selecionar apenas alguns valores, ou simplesmente renomear algum campo. Para isso utilizamos o Select Values, vejamos os detalhes do componente.

Pentaho

Como sempre, o Step name para o nome a ser exibido como identificação. O botão Get fields to select, exibe todos os Fields que estão ligados ao componente. Então, é possível fazer a seleção necessária, ou os ajustes que forem precisos (Renomear, tamanho do campo e precisão).

3.8. Dimension Lookup/Update

Para as cargas das dimensões o Pentaho tem um componente específico para essa tarefa. O Dimension Lookup/Update, faz tanto a inserção de novos dados nas dimensões, quanto atualizações dos dados das dimensões já carregadas. Vejamos os detalhes da configuração do componente.

Pentaho

Sempre o Step name para configurar o nome a ser exibido como identificação. Depois, precisamos checar se o campo Update the dimension está marcado, informando que a dimensão vai ser atualizada. A Connection é para informar qual conexão com o banco de dados que vamos utilizar. Os campos Target schema e Target table, são as informações da identificação da tabela no banco de dados. Na aba Keys, vamos identificar a coluna chave da dimensão, já na aba Fields, temos a identificação das colunas que podem ser atualizadas. Ainda não acabamos com step da dimensão.

Pentaho

Agora precisamos definir uma chave para a tabela de dimensão, através do campo Technical key field. A forma como vamos preencher essa chave vai ser escolhida no campo Creation of technical key, selecionamos a forma auto incremental. Os campos, Version field, Date range start field e Table date range end são para controle de atualizações da dimensão.

3.9. Database Lookup

Quando vamos inserir dados nas tabelas fatos, precisamos identificar as dimensões presentes. Dessa forma, podemos inserir os dados da tabela fato e todas as relações com as dimensões. Para isso vamos utilizar o componente Database Lookup. Veremos como configurar.

Pentaho

Identificamos o nome a ser exibido no Step name. Em seguida, a Connection é para informar qual conexão com o banco de dados que vamos utilizar. Os campos Target schema e Target table, são as informações da identificação da tabela no banco de dados. Observe que os campos Connection, Target schema e Target table já vimos em componentes anteriores e tem a mesma função. Em A chave(s) para examinar o valor(s): é onde vamos informar os campos a serem checados e em Valores a serem retornados da tabela lookup: vamos informar o valor que vai ser retornado.

Para o nosso exemplo acima, utilizamos a tabela dim_especie onde temos a coluna especie_florestal, é a mesma coluna que temos na consulta para gerar a tabela fato. Mas precisamos gravar na tabela fato a identificação da espécie (id_especie), então esse componente faz exatamente operação.

3.10. Table Output

Finalmente, o último componente que iremos utilizar. O Table Output insere os dados da transformação em um banco de dados. É o componente que será utilizado para inserir os dados em nossa tabela fato. Vamos às configurações.

Pentaho

Configura o Step name informando o nome a ser exibido como identificação. Seguimos para a Connection, Target schema e Target table que já vimos em alguns componentes anteriores e tem exatamente a mesma função. A função Truncate table, pode ser útil caso seja necessário limpar a tabela antes de inserir novos dados.

Agora, já conhecemos todos os componentes que precisaremos. Vendo cada um de forma separada podem ter surgido algumas dúvidas, mas veremos que na prática são de fácil entendimento. Sendo assim, juntaremos todos eles na ETL do nosso projeto.

4. ETL na Prática no Projeto de Business Intelligence do Dados ao Cubo

O nosso projeto ficou estruturado em três etapas. Como pegamos os dados no portal de dados abertos em CSV. Primeiramente iremos carregar esses dados no postgreSQL. Em seguida, faremos a carga na staging area, seguindo as boas práticas de ETL. E por fim, carregaremos nossas dimensões e a fato. Vamos às transformações do projeto.

4.1. Carga CSV para Banco de Dados

Após o download do arquivo CSV no site dos dados abertos, utilizaremos os componentes: CSV File Input, para extrair os dados de um arquivo CSV; Select Values, para selecionar as colunas desejadas e Table Output, para carregar os dados em uma base de dados.

ETL com Pentaho

Dessa forma temos os dados carregados em um banco de dados. Já podemos seguir para o próximo passo.

4.2. Carga Banco de Dados Staging Area

Com os dados carregados no banco de dados, vamos usar os componentes: Table Input, para extrair os dados de um banco de dados; Select Values, para selecionar as colunas desejadas e Table Output, para carregar os dados em uma base de dados.

ETL com Pentaho

Usamos a seguinte consulta SQL no componente Table Input, para extrair os dados:

select * from rf_florestasplantadas_ibge

Dessa forma carregamos os dados em um banco de dados da staging area. Vamos para o próximo nível.

4.3. Carga Dimensão Espécie

Com a nossa staging area carregada, vamos usar os componentes: Table Input, para extrair os dados de um banco de dados; Select Values, para selecionar as colunas desejadas e Dimension Lookup/Update, para carregar os dados em uma dimensão.

ETL com Pentaho

Precisamos de uma consulta SQL no componente Table Input, para identificar os dados da nossa dimensão:

select 
	especie_florestal 
from stg_florestas_plantadas_ibge
group by
	especie_florestal 

Então, já temos a nossa primeira dimensão carregada. Vamos a próxima.

4.4. Carga Dimensão Localização

Mais uma vez extraindo dados da staging area, vamos usar os componentes: Table Input, para extrair os dados de um banco de dados; Select Values, para selecionar as colunas desejadas e Dimension Lookup/Update, para carregar os dados em uma dimensão.

ETL com Pentaho

Mais uma consulta SQL no componente Table Input, agora para a dimensão de localização.

select 
	municipio 
	,municipio_uf 
	,estado 
	,uf 
	,regiao 
	,pais 
	,latitude 
	,longitude 
from stg_florestas_plantadas_ibge
group by
	municipio 
	,municipio_uf 
	,estado 
	,uf 
	,regiao 
	,pais 
	,latitude 
	,longitude 

Estamos com quase todas as dimensões carregadas, vamos a última.

4.5. Carga Dimensão Tempo

Desta vez os dados não têm origem da staging area, vamos extrair de um arquivo excel, vamos usar os componentes: Microsoft Excel Input, para extrair os dados de um arquivo xlsx; Select Values, para selecionar as colunas desejadas e Dimension Lookup/Update, para carregar os dados em uma dimensão.

ETL com Pentaho

Abaixo temos uma amostra do que temos no arquivo excel, que vai gerar a dimensão de tempo.

dimensão de tempo

Finalizamos as cargas das nossas dimensões, então vamos carregar a fato.

4.6. Carga da Fato Florestas

Os dados também são de origem da staging area, vamos usar os componentes: Table Input, para extrair os dados de um banco de dados; Database Lookup, para trocar os dados da origem dos dados, pelas chaves das dimensões carregadas anteriormente;  Select Values, para selecionar as colunas desejadas e Table Output, para carregar os dados em uma base de dados.

ETL com Pentaho

Essa é a consulta SQL no componente Table Input, para a fato:

select 
	ano_data
	,municipio_uf 
	,especie_florestal
	,area_ha 
from stg_florestas_plantadas_ibge

Portanto, com todas as dimensões e fatos carregadas finalizamos a construção de ETL do projeto de BI do Dados ao Cubo. Além do que vimos por aqui, outras tarefas do processo de ETL, serão as cargas e monitoramento delas. Achou que só isso que fizemos e já estaria tudo resolvido? Não, aqui é só a criação do problema.

Brincadeiras à parte, todo esse processo que fizemos até aqui significa aproximadamente 80% do projeto de business intelligence. Então, cuide muito bem da modelagem, estruturação e transformação dos dados.

O nosso próximo passo e último da nossa série Business Intelligence – Do Problema Ao Dashboard será a cereja do bolo. A apresentação de tudo que construímos para o usuário final, então vamos caprichar. O DataViz ou visualização de dados é a beleza da coisa, é o que chama mais atenção, vamos com tudo.

Portanto, esse resumo de um projeto de BI que estamos construindo no Dados ao Cubo é para mostrar o potencial que o Business Intelligence pode gerar de resultado. Sendo assim, a ideia é sempre trazer de uma forma simplificada para facilitar a compreensão de todos. Espero que esses objetivos estejam sendo atingidos. Se está gostando do conteúdo, vamos levar para o maior número de pessoas possíveis que têm interesse na área de BI. Um grande abraço e até a próxima.

Gostou? Compartilhe!

Você pode gostar:

Comentários: