BLOG DOS
INCOMPARÁVEIS

Categorias

Publicações

Como Construir um Data Warehouse (DW) com Pentaho

Seja bem-vindo ao Blog.

O objetivo hoje é mostrar como criar um DW com o Pentaho.

Pentaho é uma ferramenta open source que não tem custo de licenciamento, podendo ser utilizado para ETL, para projetos Big Data e automação de processos.

As principais vantagens de utilizar o Pentaho para criação de um DW, é a velocidade do desenvolvimento e manutenção.

Cenário

Vamos trabalhar com as seguintes tabelas:

1- Tabela de Clientes

2- Tabela de Produtos

3- Tabela de Pedidos Detalhada

4- Tabela de funcionários

5- Tabela de Pedidos

Figura 01 – Tabelas do Banco de Dados

Com base nessas tabelas, serão criadas as tabelas dimensão e fato:

dCliente

dFuncionarios

dProduto

dCalendario

Que programa é esse que deixa a modelagem bonitinha:
É o Power Architect, ele te ajuda a entender os dados antes de começar a manipulá-los, deixando mais claro o relacionamento entre as tabelas. Ele permite exportar o modelo em pdf, com isso a modelagem fica salva para uso futuro.

É possível também exportar em html deixando a visualização muito mais detalhada.

Figura 02 – Modelagem Power Architect

No exemplo aqui retratado, será utilizado o mySQL como banco de dados, mas você pode conectar em outro de sua preferência.

Dimensão Cliente

Vamos começar com o step Table input que é utilizado quando queremos importar uma tabela armazenada em um banco de dados.

Dê 02 cliques no Table input, e execute as seguintes ações:

1- Selecione o step Table input e arraste para sua área de trabalho

2- Clique em New

3- Nomeie sua conexão

4- Escolha onde você quer conectar (local da sua base de dados), nesse caso mySQL

5- Insira os dados da sua conexão

6- Clique em teste para certificar que conectou

7- Em caso de sucesso deve abrir essa janela, em seguida você pode clicar em OK.

Figura 03 – Conectando no banco

Em seguida:

  1. Clique em Get SQL select statement
  2. A janela aberta mostra as tabelas existentes no banco que conectamos, clique em customers
  3. Clique em OK.
Figura 04 – Importando a tabela Customers

Na janela aberta:

  1. Nomeie o step como Customers
  2. Veja que temos um script SQL que faz select do banco de dados
  3. Clicando em Preview você irá visualizar uma prévia dos seus dados
Figura 05 – Preview dos dados

Agora:

  1. Clique em ▶ para executar
  2. Em seguida clique em Run
  3. Salve sua transformação como dimCustomers
Figura 06 – Executando uma transformação

O ticket verdinho ao lado do step é um indicativo de que os dados foram carregados, para visualizar como ficou a carga, clique em preview

Figura 07 – Visualizar resultado

Clicando em Step Metrics, ao lado do Preview Data, é possível ver a quantidade de dados:

  • Lidos
  • Escritos
  • Entrada
  • Saída
  • Atualizados
  • Rejeitados
  • Erros
  • Velocidade da consulta por linha

Note que há dados sem padrão, por exemplo, no endereço algumas linhas estão com o número após a descrição da rua, e em outras linhas esse número está antes da descrição. Selecione o step String operations, use o campo search para facilitar encontrar o step.

Figura 08 – String operations

Relacione os steps:

Clique em Customers, segure o Shift, em arraste o mouse na direção do novo step String operations.

Dica: Sempre que inserir um novo step ele deve ser relacionado com o anterior.

Figura 09 – Relacionando steps

Para tratar os campos de texto, siga os passos abaixo:

  1. Dê 02 cliques em String Operations,
  2. Depois clique em get fields
  3. Clicando em cima da linha address, dê um CRTL+C  e CRTL+V para duplicar essa linha
  4. Nomeie uma das linhas do endereço como rua e outra como número
  5. Na coluna Trim type escolha a opção both para tirar os espaços de ambos os lados, e na coluna Lower/Upper escolha opção Upper para deixar todas as letras em maiúsculo
  6. Nos campos de address, na linha da rua remova os dígitos, selecionando remove, e na linha dos números mantenha apenas números selecionando only.
  7. Agora é só clicar em OK.

Dica: Ao duplicar o endereço e renomear como rua e número, são criadas 02 novas colunas, e a coluna address é mantida, os demais campos como não tem preenchimento no Out stream field, o Pentaho faz o tratamento na coluna já existente, ou seja, não cria novas colunas.

Lembrete: Sempre que terminar de configurar um step cliquem no botão ▶ Run para executar, e depois no Preview data para visualizar o resultado da execução, Ok ?.

Veja agora no preview que na coluna rua, temos ainda textos fora do padrão, por exemplo, ora é ponto, ora é virgula etc.

  1. Selecione o step Replace in string
  2. Digite rua em 4 linhas conforme abaixo
  3. Insira os caracteres que vamos substituir, um para cada linha do campo rua. Os caracteres são, ponto, vírgula, –, e ª
  4. Insira os caracteres que irá substituir o que foi informado no passo 3, nesse caso vamos deixar em branco (substituir por nada), em seguida pode clicar em Ok.
Figura 10 – Replace in string

Pulo do Gato: Alguns step, ao fazer o relacionamento, pede que seja escolhido entre duas opções main output of step ou error handling of step. Nesses casos escolha a primeira opção.

Figura 11 – Main output of step

Concatenar o endereço:

  1. Selecione o step Concat fields
  2. Configure o step informando: nome do step, nome do campo, tamanho e separador
  3. Selecione os campos rua e numero, colocando a rua com tipo string e numero como number, e em Trim Type selecione a opção both (os dois)
Figura12 – Concatenando o endereço

Para tratar os campos nulos, proceda da seguinte forma:

  1. Selecione o step If field value is null
  2. Nomeie o step
  3. Marque a opção Select value type
  4. Tipo String > será substituído por -1

Tipo Integer > será substituído por NÃO INFORMADO

Figura13 – Tratando os nulos

Perceba que os valores nulos que continham na tabela Customer, não existem mais. Legal né.

Agora selecione apenas as colunas que serão utilizadas:

  1. Selecione o step Select values
  2. Clique em Get fields to select
  3. Mantenha apenas os campos (Customer_id, company_name, contact_name, contact_title, Endereco, city, region, postal_code, country, phone, fax). Se atente para o campo endereço que está renomeado para address.

Pulo do Gato: para mudar a ordem dos campos, selecione a linha clicando em cima do número correspondente a ela, segure o CTRL e use as setas para cima ou para baixo para a linha selecionada.

Figura14 – Selecionando colunas

Crie o seu DW no banco de dados para armazenar as tabelas que foram tratadas no Pentaho.

Figura15 – Criando o DW no banco

Selecione o step Dimension lookup/update

  1. Nomeie o step: DimCustomer
  2. Nomeie a tabela: dim_customer
  3. Clique em Wizard
  4. Dê um nome para a conexão com o DW que acabamos de criar, aqui chamamos de DW_north mesmo
  5. Escolha o banco:  mySQL
  6. Clique em NEXT
Figura16 – Conectando no DW

Coloque o nome da host: localhost

Coloque o nome do DW: DW_north

Figura17 – Dados da Conexão

Coloque seu usuário e senha de acesso ao banco

Teste a conexão, clique em OK e em seguida finalizar.

Figura18 – Testando a conexão

Em Field in stream selecione o campo Customer_id, copie e cole dimension field

Em Technical key field selecione sk_customers

Em Version field escreva version

Em Date range start field selecione a opção date_from, marque a opção Use na alternative start date e selecione a opção System_date, em Table date range end selecione a opção date_to

Clique em fields

Clique em Get fields

Clique em SQL

Figura19 – Configurando a dim_customer

No script SQL delete as últimas 02 linhas de código (CREATE INDEX), em seguida confira os tamanhos dos campos de VARCHAR, certifique-se de estar conforme abaixo:

Customer_id VARCHAR (5)

Company_name VARCHAR (36)

Contact_name VARCHAR (23)

Conatct_title VARCHAR (30)

address VARCHAR (50)

city_id VARCHAR (15)

region VARCHAR (13)

postal_code VARCHAR (9)

country VARCHAR (11)

phone VARCHAR (17)

fax VARCHAR (17)

Figura20 – Script dim_customer

Execute e confira no banco se carregou.

Se der algum erro, apague o hop (seta entre select values  e DimCustomer) e crie o hop novamente e clique em OK.

Assim deve ficar suas transformações.

Figura 21- Transformações dim_customer

Clique em salvar.

Dimensão Produto

Vá em em Arquivo > Salvar Como > DimProducts > OK

  1. Note que a transformação mudou de nome
  2. Agora delete os steps da área indicada
Figura 22 – Salvando a dimproducts

De 02 cliques no step Customer

  1. Renomeie o step para Products
  2. Clique em Get SQL select statement
  3. Selecione a tabela de produto > OK > Sim
Figura 23 – Importando a tabela produto

Na janela aberta, clique em Preview para ver uma prévia dos dados, confira os dados e pode clicar em OK. Em seguida execute o step.

Selecione o step Select values, clique em Get fields to select e mantenha apenas as colunas de product_id, product_name e quantity_per_unit.

Selecione o step If field value is null:

  1. Marque a opção select fields
  2. Clique em obter campos
  3. Configure conforme mostrado
Figura 26 – Tratando nulos através de parâmetros

Selecione o String operations, clique em get fields e execute as configurações conforme abaixo:

  1. Trim type: both (ambos)
  2. Lower/Upper: Upper

Agora selecione o step Table output.

Esse step faz carga full, ou seja, os dados serão apagados da tabela e carregados novamente.

  1. Nomeie o step: dimProducts
  2. Selecione o destino da carga: DW_north
  3. Nomeie a tabela a ser carregada: dim_products
  4. Marque a opção truncate table
  5. Selecione a aba database fields
  6. Clique em get fields
  7. Clique em SQL
  8. Inclua a Primary Key conforme destacado
  9. Informe qual é o número da primeira chave, no caso 1
  10. Clique em Executar > OK > Fecha > OK.
Figura 28 – Passos de 1 a 7

Figura 29 – Passos de 8 a 10

Assim deve ficar as transformações da dimProducts.

Figura 30 – Transformações dimProducts

Clique em salvar.

Dimensão Funcionário

Com a transformação dim_products aberta:

Vá em em Arquivo > Salvar Como > DimEmployee > OK

Apague todos os steps, menos o primeiro.

Importe a tabela dimEmployee do banco de dados

Figura 31 – Importante a dimEmployee

Delete a coluna de notas, pois não vamos utilizar

Insira o step Concat fields: em name selecione as colunas last_name e first_name, em type selecione o tipo string, e em Length de um espaço (que é o separador).

Figura 32 – Concatenando primeiro e último nome

Insira o step string operations

Delete as colunas first name e last name

Duplique a coluna adress e configure conforme mostrado

Figura 33 – Tratando campos de texto

Adicione o step replace in string, selecione o campo rua em 3 linhas, e Search coloque um caractere em cada linha (ponto, vírgula e traço) clique em OK.

Figura 34 – Substituindo caracteres

Adicione um concat fields para concatenar o em endereço.

Figura35 – Concatenando endereço

Clicando em uma área em branco com o botão direito, selecione a opção propriedades, para criar uma variável.

  1. Clique em parâmetros

Parâmetro: ds; Valor padrão: NAO INFORMADO
Parâmetro: cd; Valor padrão: -1

 Agora adicione o step if field value is null

Clique na coluna Replace by value  e dê o comando CTRL + Espaço para abrir as opções, comece a digitar cd (nome da variável que criamos) e ela será facilmente encontrada.

Selecione a variável ao encontrá-la e dê enter.

Figura 36 – Criando variáveis

A vantagem de utilizar parâmetro para tratar os campos nulos é que você altera todas as colunas da tabela de 01 única vez.

Insira o step Select values

Clique em Get fields to select

Mantenha apenas os campos mostrados abaixo.

Figura 37 – Selecionando campos

Nesse mesmo step, na aba Meta-data, vamos tratar os campos de data.

Selecione os campos, birth_date e hire_date, em type coloque Date e em Format selecione dd/MM/yyyy.

Figura 38 – Tratando os campos de data

Insira o Table output e carregue os dados para o DW_north.

No comando SQL configure seu script para que ele fique exatamente igual demonstrado abaixo.

Note que que incluímos a sk_employee e mudamos o número de caracteres de alguns campos do tipo varchar.

Figura 39 – Script dim_employee

Staging da Tabela Fato

Salve a transformação dimCustomer

Em seguida:

Vá em salvar como > nomeie como stg_fato

Importe a tabela Orders

Mantenha apenas as colunas que iremos utilizar: order_id, Customer_id, emplyee_id, order_date, required_Date, shipped_Date, ship_via, e freight.

Figura 40 – Input tabela orders

Copie e cole este step e importe a tabela Order Details.

Inclua um step sort rows e relacione com o step orders

Excute as configurações conforme abaixo

Repita o processo para o step Orders Details

Figura 41 – Ordenando a tabela

Agora insira o step Merge join e relacione com os 02 steps Sort rows

Figura 42 – Step Merge

Configure conforme mostrado abaixo:

  1. Selecione os steps que irá mesclar (Sort Order e Sort Order Details) e o tipo de join (INNER)
  2. Ao clicar em Get keys fields, irá trazer todos os campos de ambas as tabelas, delete os campos desnecessários, mantendo apenas o order_id.
Figura 43 – Mesclando tabelas

Execute os steps.

Note que o resultado mostra o order_id das 02 tabelas, vamos excluir 01.

Figura 44 – Order_id repetido

Insira o step select values e na aba remove, selecione o campo order_id_1

Aproveite esse step para ajustar os campos de data: Na aba Meta-data, selecione os campos order_date, required_date, shipped_date, em seguida coloque o tipo como date e o formato como dd/MM/yyyy.

Com o table output carregue essa tabela para o DW com o nome stg_fato_pedidos.

Nessa carga, não é necessário nenhum ajuste no scrip de SQL, apenas Execute e Feche.

Salve sua transformação stg_fato_pedidos.

Tabela Fato Oficial

Com a transformação stg_fato_pedidos salva, vá em salvar como e nomeie como fato_orders.

Delete todos os steps.

Insira o step Table Input > conecte no DW e importe a tabela stg_fato_pedidos

Figura 46 – Input stg_fato_pedidos

Em uma tabela fato, deve conter apenas os ids e os valores, OK?!

Insira um novo table input, e importe a tabela dimCustomer do DW.

Figura 47 – dimCustomer DW

No final do script, insira o comando order by 1

Figura 48 – Order by

O objetivo agora é fazer um group by para considerar somente a última versão da tabela cliente, insira o step group by e relacione com a dimCustomer:

  1. Clique em Get Fields
  2. Apague a linha version na janela indicada
  3. Escolha o campo de agregação e o tipo (version e last value)
  4. Execute os step.
Figura 49 – Group by

Agora volte no step table input e ajuste o script SQL, mantendo apenas as colunas sk_customers, version, e Customer_id.

Figura 50 – Ajuste dim_customer

Abra o step group by, clique em get fields, clear and add all.

Delete a linha do version destacada

Figura 51 – Ajuste no group by

O resultado dessa consulta deve ficar conforme mostrado na figura 52.

Figura 52 – Resultado group by

Agora apague o group by, e em seguida no step do table input, edite o script para ficar conforme mostrado abaixo:

Figura 53 – Ajuste input dimCustomers

Insira o step stream lookup e relacione com os steps table input dimCustomer e stg fato.

  1. Selecione o step de input: Input dimCustomer
  2. Selecione os ids que que relacionam: Customer_id
  3. Selecione o campo sk_custormers, em New Name coloque também sk_customers e o tipo deve ser inteiro.
Figura 54 – Stream lookup

Figura 55 – Steps relacionados

No table input da tabela dimCustomers

Ajuste o script SQL conforme mostrado abaixo:

Figura 56 – Script ajustado dimCustomer

Insira o step database lookup:

  1. Nomeie o step
  2. Conecte no DW
  3. Escolha a tabela dim_employee
  4. Selecione os ids correspondentes entre si: employee_id
  5. Selecione o campo de retorno: sk_employee_id, e o tipo deve ser inteiro
Figura 57 – Database lookup

Repita o processo do database lookup só que agora com a dim_products.

Insira o step calculator

Esse step nos dá mostra uma gama de cálculos prontos que podemos utilizar. Isso facilita o trabalho de aplicações DAX, uma vez que muitos cálculos podemos trazer pronto do banco.

Configure conforme abaixo:

Novo Campo: ValorTotal

Cálculo: A*B

Campo A: unit_price

Campo B: quantity

Tipo do Valor: Number

Figura 58 – Cálculo Total

Com o select, mantenha apenas os campos necessários, conforme mostrado abaixo.

Figura 59 – Select tabela fato

Use o table output para carregar a tabela fato final para o seu DW.

  1. Conecte no DW
  2. Nomeie como fato_pedidos
  3. Marque a opção truncate e specify database fields
  4. Clique em database fields
  5. Clique em get fields

No script SQL, ajuste para criar um id da fato.

Figura 60 – Script tabela fato

E se der algum pau? O Pentaho irá te mostrar exatamente qual step deu erro, e com isso você ganha muita velocidade.

Agora você pode conectar o Power BI no mySQL e criar seus relatórios.

Por hoje é isso, abraços.

Léo.

Leia também

Assista agora o Minicurso gratuito de Power BI:

Publicações recentes:
Assuntos:
Xperiun