Populando fatos e dimensões no PDI (Kettle)

Dando continuidade ao uso de componentes que podem nos ajudar na etapa de estagiamento, que é uma das etapas na construção de um DW, vamos mostrar os componentes que poderiam ser úteis para popularmos as tabelas dimensão e a tabela fato.

Em uma tabela dim_cliente ou dim_produto, por exemplo, não existe mistério.   As tabelas dimensão são responsáveis por guardar as descrições, ou seja, nada de métricas por aqui.    O importante, é entender que além das descrições, guardaremos o código dos clientes e dos produtos na base relacional (base utilizada pelo sistema, pelas tabelas Cliente e Produtos), e além disso, teremos um NOVO código para cada um desses valores.

Aconselho iniciarmos o ID da tabela dim_cliente, por exemplo, com um número bem acima de 1.   Dessa forma, evitaremos facilmente coincidir os códigos da tabela relacional e da dimensão.

Poderíamos modelar nossa tabela dim_cliente com as colunas:

• ID_CLIENTE (codigo do cliente para esta tabela, preferencialmente começando por um número alto)
• NM_CLIENTE (campo com a descrição, ou seja, o nome do cliente)
• CD_CLIENTE_RELACIONAL (codigo do cliente na base relacional)

Mas para que usaremos esta coluna (cd_cliente_relacional) no nosso DW?   Como o foco deste post não é explicar os conceitos de modelagem de um DW, até porque o material existente sobre o tema é extenso, basta-nos dizer que, ao popularmos nossa tabela fato com os eventos, como as vendas de um produto para os clientes de nossa loja, por exemplo, extrairemos os dados da base relacional e verificaremos quais os novos codigos de cada cliente fazendo a comparação pelo código da base relacional, que também está gravado na dimensão (do contrário teríamos que fazer esta comparação pelo nome, o que não é aconselhável).

A tabela Fato, que pode ser uma fato_vendas, por exemplo, teria as colunas:

• ID
• ID_CLIENTE (cliente que comprou)
• ID_TEMPO (código indicando a data da compra)
• QUANTIDADE (quantidade de produtos que o cliente comprou neste período)

E como poderíamos ter um código para o tempo?  Podemos modelar nossa dimensão tempo da seguinte forma:

• ID_TEMPO (id da tabela)
• DATA (data completa, ex: 01/01/2011)
• ANO (inteiro indicando o número da data)
• MES (idem)
• DIA (idem)

Poderíamos ter as colunas hora, minuto e até segundo, se a necessidade de agruparmos os dados na tabela fato fosse diferente, mas em nosso exemplo, utilizaremos o dia como menor valor.

O  figura abaixo ilustra um ETL que popula a tabela dim_tempo instantaneamente, sendo necessário apenas indicar uma data inicial, o critério para soma (dias, horas, minutos, etc) e o número a somar (100 dias, 100 horas, 100 minutos, etc).   Apenas fornecendo estas informações, teremos nossa tabela dim_tempo populada em segundos, considerando até milhares de anos.

Para fazer o download do arquivo KTR, para manipulá-lo em sua própria máquina, clique aqui.

O componente Gera Linhas é um Generate Rows, utilizado apenas para definir quantas linhas vamos colocar em nossa tabela dim_tempo.    Neste caso, colocaremos 1000 linhas, o que significa que criaremos uma linha para cada dia, começando do dia 01/01/2011.

O componente Adiciona Dias é um Add Sequence, utilizado para gerar uma sequencia numérica e adicioná-la ao fluxo, ou seja, cada linha receberá um número, começando de 1 e incrementado em 1, conforme definido dentro do componente.

O componente Get Data Inicial recebe uma data inicial, o número que será somado à data e como ela será incrementada (em dias, anos, meses, etc).   Em nosso exemplo está definido que será em dias.

O componente Cria ID é um outro Add Sequence, que desta vez gerará um ID para nossa dim_tempo.

O Componente JS Quebra Tempo é um componente para scripts em javascript.    Neste caso estamos utilizando-o para "particionar" a data, separando o dia, mês e ano que serão inseridos de forma numérica em nossa dim_tempo.

Por último temos um Table Output renomeado para Popula Dim_Tempo, que receberá as linhas provenientes do fluxo para gravar na tabela dim_tempo.

Para entender melhor o funcionamento de cada componente, aconselho a fazer o download do mesmo através do link acima.

Chegamos finalmente ao objetivo principal de nosso post.    

Considerando que temos em nossa base relacional uma tabela que relacione cada venda efetuada pelo sistema, cadastrando o código do cliente e a data da venda, por exemplo, faremos agora, de forma simples, a carga destes dados e seu devido agrupamento para dentro de nossa tabela fato.

Como a tabela fato registra apenas métricas e códigos, indicando a ligação com as tabelas de dimensão, utilizaremos o componente Database Lookup diversas vezes para procurar o código das tabelas de dimensão que correspondem ao valor de cliente e tempo da base relacional, além do componente Group By, responsável por agrupar a quantidade de vendas por dia, por exemplo, feitas para cada cliente registrado em nosso DW, na tabela dim_cliente.

Veja na figura abaixo como ficou nosso processo de carga da tabela fato_vendas:

Para fazer o download deste ETL, clique aqui.

O exemplo utilizado aqui é extremamente simples e não reflete a realidade, visto que nunca iríamos modelar um DW e criar uma tabela de fato vendas (por exemplo) sem levar em conta os produtos vendidos, as regiões, lojas, fornecedores, etc.

Nosso objetivo, que esperamos ter sido alcançado, é dar uma visão inicial de como utilizar os componentes que nos ajudarão a popular os fatos e as dimensões, abordando principalmente a dimensão tempo, fruto de diversas dúvidas e discussões.

No próximo post voltaremos a explicar de forma detalhada sobre mais alguns componentes do PDI.

Utilizando o Database Lookup

Para definirmos de forma simples o comportamento de um componente de lookup, poderíamos dizer que estes funcionam como uma cláusula SELECT com WHERE.     Basicamente tentamos recuperar um ou mais valores baseados na comparação entre colunas e seus valores.
Ex:  Select NOME, ENDEREÇO From tabela Where ID = (valor numérico)

O componente Database Lookup, como o próprio nome sugere, faz uma busca de valores em uma tabela de um banco de dados.     Para usar este componente, assim como o Table Input, também será necessário ter ao menos uma conexão configurada.

O comportamento do Stream Lookup é parecido, porém a busca de valores é feita dentro do próprio fluxo. de dados.  Esta afirmação sugere que o componente receba duas fontes de dados diferentes.    No Database Lookup isso não é necessário, pois uma das fontes de dados é a própria tabela.

O Database Lookup é extremamente útil em diversas ocasiões.   Entre elas, podemos destacar principalmente a fase de estagiamento de um DW.
Utilizamos como exemplo o preenchimento de uma tabela FatoVendas, onde precisaremos:

• Buscar o código dos clientes na base relacional
• Compará-lo com a tabela de Dimensão de Clientes
• Buscar o seu novo código correspondente na Dimensão e, então, 
• Preencher a linha da tabela fato com o código correspondente, ou seja, o cliente que participou de uma determinada venda.  

Para isso, será necessário:

1. Fazer um lookup na tabela Dim_Clientes, passando o código do cliente que veio da base relacional e comparando-o com um campo cd_cliente_relacional da tabela Dim_Cliente, que possui a referencia do codigo do cliente na base relacional.
2. Teremos, então, como retorno, o campo ID_CLIENTE, do tipo Integer, que é o ID daquele cliente dentro do DW, conforme mostra a figura abaixo.

Importante lembrar que um valor (cliente, por exemplo), NUNCA deve ter o mesmo código na base relacional e na dimensão de um DW.    Esta prática não é aconselhada.

Os números da figura acima correspondem:

1. Escolher a conexão que será utilizada
2. Escolher a tabela onde serão procurados os valores
3. Puxar todos os valores da tabela e deixá-los em cache.   Ideal nao habilitar quando há muitos valores na tabela, pois carregará muitos dados na memória
4. Campo da tabela a ser comparado
5. Campo do fluxo comparado ao campo da tabela
6. Campo a ser retornado caso a comparação anterior seja verdadeira.   É possível definir o tipo do campo e um valor "Default", caso os valores comparados não existam.

Além do comparador de igualdade (=), temos também <>, <, >, <=, >=, ISNULL, BETWEEN, LIKE e IS NOT NULL).
Como a busca de dados pelo componente Database Lookup nao utiliza o NOLOCK, existe o risco de deadlock quando a busca é pesada, longa e há várias outras operações acontecendo na mesma tabela ao mesmo tempo (Inserts, updates, etc).
Para isso, recomendo um Select feito em um Table Input, utilizando o NOLOCK, e aí então, utilizar o Stream Lookup para buscar os valores desejados.    Dessa forma, o Kettle coloca as linhas em memória, não sendo necessário utilizar e acessar o banco diversas vezes durante o fluxo de dados.
Este exemplo está ilustrado na figura a seguir:

O comportamento do componente Stream Lookup, assim como suas opções e formas de uso serão discutidos no próximo post.

Configurando Conexões - Parte 2

Sempre que trabalhamos em um ambiente de desenvolvimento, costumamos trabalhar com um determinado número de bancos de dados diferentes, ou até mesmo SGBDs diferentes, que normalmente referenciam a alguma(s) aplicação(ões).

Uma outra alternativa que temos, além do uso de variáveis na configuração das conexões, para desenvolvermos um processo de ETL de forma mais rápida e dinâmica, evitando replicar diversas vezes as mesmas conexões a cada transformation ou job criado, é o compartilhamento (share) de conexões.

Para habilitar esta opção, basta clicarmos com o botão direito na conexão desejada, e em seguida selecionar a opção Share, conforme mostra a imagem a seguir:

Toda vez que uma conexão é compartilhada, ela estará indicada em negrito, conforme mostra a figura abaixo:

 

Tendo feito isto, qualquer transformation ou job criado a partir deste momento, terá como padrão todas as conexões compartilhadas anteriormente.

Além disso, caso seja necessário excluir ou alterar todas as conexões compartilhadas em sua máquina, basta acessar o arquivo shared.xml, que encontra-se na pasta .kettle.    Essa pasta é criada dentro da pasta Users/nome_usuario/

Lembrando apenas que o fato de excluir o arquivo shared.xml, elimina o compartilhamento, porém a conexão criada se mantém.

Em outros posts explicaremos outras configurações avançadas sobre as conexões.