Base de Dados para Treinamento

Para escrever o Pentaho na Prática eu construí, do zero, uma base de dados – a Beltrano S/A. Ela está disponível na forma de um backup Postgres, com licença que permite até mesmo o uso comercial. Ou seja, se você quiser montar um curso, ou um produto, embutindo a Beltrano S/A, pode.


A única coisa que eu peço em troca é ser informado no que ela está sendo usada. Só. E mesmo assim, se não quiser não precisa me falar nada. Liberdade total. ;-)


A julgar pelo feedback de quem pediu para usar, eu diria que, como material de ensino, a base é interessante. Por outro lado, como tecnologia ela é bem pouco prática, na minha opinião.

Começa que, para usar, é preciso instalar o Postgres. Depois, precisa criar um banco, baixar o backup, restaurá-lo etc. Daí, se você alterar o conteúdo e quiser resetar o banco para o estado inicial, é preciso dropar a base e recriá-la. E se quiser customizar, precisa extrair umbackup no final e repetir o passo-a-passo de configuração em todas as máquinas de alunos.

É um trabalho chato mesmo para quem sabe mexer no Postgres. Para quem tem pouca familiaridade, é um porre.

Nem todo mundo é fluente em Postgres, e nem sempre dá para instalá-lo nas máquinas. Ou até dá, mas pode rolar conflito de porta, restrições de administração, blá blá blá… Eu ministrei umas vinte turmas de [BI com Pentaho][bicp4lin_bitly], e em todas elas sempre houve ao menos uma pessoa/máquina com problema sério na preparação do banco.

Bom, Bonito e Barato

Existe um servidor de banco de dados chamado HSQLDB que é 100% Java e que não precisa ser instalado. Basta baixar o arquivo do programa, rodá-lo com Java e voilà, tudo em riba!

Como se não bastasse ser Software Livre e 100% Java (ou seja, 100% portável – roda em qualquer plataforma, mesmo!), ainda tem uma interface gráfica a lá pgAdmin III, que pode ser usada para explorar os bancos e executar comandos SQL.

Mas tem uma coisa mais legal ainda: os bancos são construídos, por default, em memória. Quando o servidor é baixado, ele grava tudo que estava em memória em arquivos texto planos, no diretório que especificarmos. Isso, mais o fato de tomar muitas de suas configurações de servidor da linha de comando, nos dá muitas vantagens:

  • Qualquer modificação que é feita nos dados pode ser mantida e transferida para outras máquinas, simplificando o processo de customização para cada uso;
  • Se quiser voltar ao estado original, basta apagar o banco atual e descompactar o banco inicial mais uma vez. Estou falando do Beltrano S/A, mas pode ser feito para qualquer outro banco que você criar: basta guardar uma cópia à parte do seu banco;
  • Ele não mantém arquivos escondidos. Apagou, desinstalou;
  • A porta é configurada na chamada que sobe o servidor. Deu conflito? Baixe o banco, mude a porta e suba novamente;
  • Como podemos rodar um programa Java quantas vezes quisermos, podemos montar um conjunto de vários servidores, cada qual com sua porta e seu banco;
  • Um mesmo servidor, aliás, pode manter vários bancos;
  • É um programa pequeno: o pacote inteiro não chega a 5MB;
  • Mesmo tendo uma “pegada” muito pequena, ele não deixa nada a dever a bancos maiores, em termos de funcionalidades, e aguenta um bom volume de dados (basicamente limitado pela RAM da máquina;)
  • Você pode embuti-lo em outros programas, afinal é um projeto livre, em Java;
  • Você pode carregar data marts nele, e dar a capacidade de processamento de dados in memory a qualquer ferramentas de análises de dados que se conecte a bancos de dados via JDBC, como o Pentaho. Evidentemente não é a mesma coisa que um engine otimizado, como o que acompanha o QlikView, mas é mais rápido que disco.

E o HSQLDB ainda oferece uma boa gama de comandos e recursos, como índices, integridade relacional, muitos tipos de dados, várias funções nativas etc. etc. etc. Não fica a dever para outros bancos, mesmo.

Quem me conhece deve estar esperando a pegadinha, que sempre vem depois que eu desenho uma cena linda, toda rósea e luminosa. ;-) Bom, desta vez não tem: é tudo róseo e lindo, mesmo! No restante do post você verá um pouco sobre o HSQLDB (como baixar e subir um banco) e como instalar o mesmo conteúdo do banco Beltrano S/A oferecido em PostgreSQL.

Introdução ao HSQLDB

Como a promessa deste post é oferecer uma base de treinamento pronta e fácil de usar, eu vou cortar direto para a perseguição1 e ensinar o bê-a-bá do agá-esse-kê-ele-debê: de onde fazer o download, como subir o servidor, criar novos bancos, acessá-lo e depois baixá-lo.

O site do HSQLDB é o http://hsqldb.org, onde você pode estudar toda documentação e ler os guias de uso. Como é um projeto voltado principalmente para programadores (é um banco embutível, afinal de contas), ele abusa da linguagem técnica, tanto SQL quanto Java, e pode ser um pouco árido para o usuário menos proficiente nesses assuntos.

O pacote do servidor HSQLDB pode ser baixado diretamente do SourceForge, neste link, e o pacote do Beltrano S/A pré-configurado pode ser baixado daqui. O restante desta seção usa o conteúdo desse pacote, não do servidor original – muito técnico, lembram-se?

O zip do Beltrano OLTP/HSQLDB traz os arquivos do servidor e mais dois scripts, cada um dos quais em duas versões: Linux (.sh) e Windows (.bat.) Um dos scripts sobe o servidor e outro baixa-o. Os diretórios são ./data, onde fica o conteúdo do banco propriamente dito, e ./lib, onde está o servidor.

Subindo e Baixando o HSQLDB

Basta rodar o script start-hsqldb.sh/bat que o HSQLDB subirá com o banco desejado – Beltrano S/A neste caso. Para rodar esse script, abra um terminal (Windows: prompt do DOS) e mude para o diretório recém-criado. Daí comande ./start-hsqldb.sh (Linux) ou start-hsqldb.bat (Windows.) Em poucos segundos o banco estará no ar.

Eis as mensagens do HSQLDB subindo em um Ubuntu:

ubuntu:beltrano_oltp_hsqldb$ ./beltrano_oltp_hsqldb
classpath is :./lib/hsqldb.jar
[Server@5cd73256]: [Thread[main,5,main]]: checkRunning(false) entered
[Server@5cd73256]: [Thread[main,5,main]]: checkRunning(false) exited
[Server@5cd73256]: Startup sequence initiated from main() method
[Server@5cd73256]: Could not load properties from file
[Server@5cd73256]: Using cli/default properties only
[Server@5cd73256]: Initiating startup sequence...
[Server@5cd73256]: Server socket opened successfully in 4 ms.
[Server@5cd73256]: Database [index=0, id=0, db=file:./data/beltrano10k, alias=beltrano_oltp] opened successfully in 1430 ms.
[Server@5cd73256]: Startup sequence completed in 1435 ms.
[Server@5cd73256]: 2016-11-24 10:37:47.379 HSQLDB server 2.3.4 is online on port 9001
[Server@5cd73256]: To close normally, connect and execute SHUTDOWN SQL
[Server@5cd73256]: From command line, use [Ctrl]+[C] to abort abruptly

Eis o conteúdo da versão Bash do script:

#!/bin/sh
### =================== ###
##  HSQLDB Start Script  ##
### =================== ###

DIR_REL=`dirname $0`
cd $DIR_REL
DIR=`pwd`
cd -

#---------------------------------#
# dynamically build the classpath #
#---------------------------------#
THE_CLASSPATH=
for i in `ls $DIR_REL/lib/hsqldb*.jar`
do
THE_CLASSPATH=${THE_CLASSPATH}:${i}
done
echo "classpath is $THE_CLASSPATH"

java -cp $THE_CLASSPATH org.hsqldb.Server
-database.0 $DIR_REL/data/beltrano
-dbname.0 beltrano_oltp -port 9001

O último comando do script acima deve estar em uma única linha.

Uma vez no ar, você não pode simplesmente apertar CTRL+C para interrompê-lo. Isso pode corromper os arquivos, sem contar que não vai salvar o que estiver em memória. Para encerrar um banco é preciso rodar o script stop-hsqldb.sh/.bat. Note que talvez seja preciso abrir outro terminal. O script de encerramento é o seguinte:

#!/bin/sh
### =================== ###
##  HSQLDB Stop Script  ##
### =================== ###

DIR_REL=`dirname $0`
cd $DIR_REL

java -jar $DIR_REL/lib/sqltool.jar
--inlineRc=url=jdbc:hsqldb:hsql://localhost:9001/nome_do_banco,user=SA,password=""
--sql="SHUTDOWN;"

De novo: linhas maiores foram separadas em várias para facilitar a leitura, mas sempre devem formar uma única linha, sem quebras.

A diferença deste script para o de inicialização é que você não precisa informar o(s) nome(s) do(s) banco(s): ao receber o comando para se desligar, o HSQLDB encerra todos que estiverem abertos. Apenas não se esqueça de conferir as portas: o comando de shutdown precisa ser enviado para a porta certa, caso contrário, óbvio, nada acontecerá.

Acessando o Servidor

O HSQLDB é um servidor de banco de dados como outro qualquer. Ele pode ser acessado por qualquer aplicação que use o padrão JDBC. O driver JDBC dele, aliás, é o próprio servidor. No pacote do Beltrano S/A o servidor/driver é o arquivo hsqldb.jar, que fica dentro da pasta ./lib. Basta passar esse arquivo para o programa que vai se conectar ao servidor HSQLDB e usar a string de conexão abaixo:

jdbc:hsqldb:hsql://:/; ~~~

`<PARAMETROS>` é qualquer configuração que precise ser passada para o servidor. Lembre-se de remover o **;** entre a URL e os parâmetros caso não haja nenhum.

Digamos que você queira usar o PDI (Kettle) para acessar esse servidor. Uma conexão com o HSQLDB é totalmente padrão: basta selecionar Hypersonic (seu antigo nome) na lista e entrar os dados de conexão:

<a href="https://geekbi.files.wordpress.com/2016/11/161123_basededadosparatreinamento_004.png"><img class="size-full wp-image-1550" src="https://geekbi.files.wordpress.com/2016/11/161123_basededadosparatreinamento_004.png" alt="Parâmetros de conexão Pentaho." width="608" height="500" /></a> Parâmetros de conexão Pentaho.

Porém, é importante setar um parâmetro para evitar problemas com &quot;conjuntos vazios&quot; (conexões que se abre e fecham sem gravar nada, acho.) Na janela de conexões do *Spoon*, acesse a seção *Options* e entre o parâmetro `allow_empty_batch = true`:

<a href="https://geekbi.files.wordpress.com/2016/11/161123_basededadosparatreinamento_003.png"><img class="size-large wp-image-1551" src="https://geekbi.files.wordpress.com/2016/11/161123_basededadosparatreinamento_003.png?w=720" alt="Onde adicionar o parâmetro para evitar problemas com INSERTs vazios." width="720" height="278" /></a> Onde adicionar o parâmetro para evitar problemas com INSERTs vazios.

A string de conexão ficaria:

jdbc:hsqldb:hsql://localhost:9001/beltrano_oltp;allow_empty_batch=true

<br />Aproveitando, se você precisa construir uma conexão [JNDI][JNDI_bitly], o formato é esse:

BELTRANO_OLTP/type=javax.sql.DataSource
BELTRANO_OLTP/driver=org.hsqldb.jdbcDriver
BELTRANO_OLTP/url=jdbc:hsqldb:hsql://localhost:9001/beltrano_oltp
BELTRANO_OLTP/user=sa
BELTRANO_OLTP/password=

<br />Lembre-se de ajustar os parâmetros para o seu caso!

### Criar Novos Bancos ###

Pronto, agora você possui um servidor de banco de dados portátil, versátil, apto a um monte de usos educacionais e profissionais. Por exemplo, você pode criar um Data Mart para servir relatórios.

> ---
>
> Francamente, eu não consegui entender a documentação. Não sei dizer se dá para criar um banco subindo um servidor "vazio" e fazendo um `CREATE DB` ou coisa do gênero. Logo, eu vou contar aqui o que eu sei que funciona, mas não é necessariamente a única forma de fazê-lo - eu só não sei de outras. ;-)
>
> ---

Observe a linha de comando Java do script de inicialização:

java -cp $THE_CLASSPATH org.hsqldb.Server
-database.0 $DIR_REL/data/beltrano
-dbname.0 beltrano_oltp -port 9001

<br />É ali que definimos que bancos o servidor vai oferecer, a partir de que diretório, em que porta etc. Logo, para criar um novo banco basta adicionar, a essa linha, um par de parâmetros:

* Diretório do banco e nome dos arquivos: `$DIR_REL/data/NOME`
* Número e nome do banco de dados: `-dbname.X NOME`

Você pode adicionar um novo banco ou remover o antigo antes de criar o novo, apagando os arquivos `$DIR_REL/data/NOME`.

Para subir mais de um banco no mesmo servidor, inclua um novo conjunto de parâmetros. Por exemplo, para termos dois bancos, chamados *nome_do_banco* e *banco_de_dados*, a linha de comando deve ser:

java -cp $THE_CLASSPATH org.hsqldb.Server
-database.0 $DIR_REL/data/nomequalquer1 -dbname.0 nome_do_banco
-database.1 $DIR_REL/data/nomequalquer2 -dbname.1 banco_de_dados
-port 9001

<br />> *Atenção*: o comando acima deve compor uma única linha.

E, claro que você já deve ter sacado, para alterar a porta na qual o banco vai responder simplesmente mude o número que vem depois do parâmetro `-port`.

### Interface Gráfica ###

Em 17/1/2013 eu postei um artigo mostrando como abrir uma interface gráfica ("mais ou menos") para um servidor HSQLDB: [Interface para o HSQLDB][inthsqldb_bitly]. Vamos relembrar os pontos importantes:

* Abra um terminal (DOS prompt no Windows)
* Mude para o diretório do *HSQLDB*;
* Comande:
java -cp hsqldb.jar org.hsqldb.util.DatabaseManagerSwing --noexit

* Preencha os campos da janela Connect com os seguintes dados:

Setting name: Beltrano OLTP
URL: jdbc:hsqldb:hsql://localhost:9001/beltrano_oltp
User: sa
Senha:
~~~

A figura abaixo mostra um exemplo. Note que ela se conecta a outro banco, e por isso os parâmetros são diferentes – ela foi puxada do outro post.

Exemplo de como criar uma nova conexão.
Exemplo de como criar uma nova conexão.

Quando você conseguir se conectar, poderá explorar o banco à vontade:

Conectado ao servidor.
Conectado ao servidor.

HSQLDB & Pentaho

Se você costuma usar o Pentaho, especialmente o servidor, vale a pena notar que esse é o banco que o Pentaho BA Server usa em sua instalação pré-configurada. Se você tiver curiosidade, tudo que foi descrito aqui pode ser verificado no diretório ./biserver-ce/data, se você souber a quê estou me referindo.

Conclusão

Eu já havia visto MySQL portátil, que é uma boa solução também. O único inconveniente era o fato de precisar de binários diferentes para cada arquitetura (Linux, Windows, Intel, ARM etc.) O HSQLDB supera essa limitação e é, de fato, um servidor de bancos de dados relacionais de arquitetura “universal”. Claro que não possui a mesma robustez de um servidor como Oracle ou PostgreSQL, mas resolve muito bem uma série de necessidades.

Não sei porque eu demorei tanto a perceber isso… ;-)

Não deixe de entrar em contato por meios dos comentários se tiver alguma dúvida, ou encontrar algum bug.

Até a próxima! :-)


  1. Cut to the chase é uma coisa que dizem em Inglês quando não estão a fim de ouvir a história inteira. Aqui usamos mais “vá direto ao assunto”. E porque eu usei a expressão estrangeira? Oras, para poder dizer que GeekBI é cultura, claro! Kkkk…. 

Analisando os Logs do PDI – Parte 2

No primeiro post da série Logs do PDI vimos como configurar as transformações e jobs do PDI para capturar os logs de processamento e como usá-los para monitorar as execuções. Neste segundo post veremos como usar esses dados para identificar gargalos.

Pias & Transformações

Imagine uma pia, na qual uma torneira despeja água. Se entrar mais água do que a pia consegue escoar, a água vai começar a se acumular. Se nada for feito, o nível de água na pia vai subir, e subir, até transbordar. Há duas formas de se evitar que a água transborde: reduzir a entrada ou aumentar a saída. Em termos mais genéricos, para evitar que a água transborde devemos condicionar a entrada à restrição. A “restrição” é justamente a válvula de escoamento da pia.

A velocidade de uma transformação, medida em linhas por segundo, l/s, está condicionada à restrição existente na transformação: a velocidade de uma transformação será igual à velocidade do seu passo mais lento. Esse passo mais lento é o gargalo.


Se uma transformação possuir passos de ordenação de linhas, agrupamentos etc., que represam linhas, a velocidade média da transformação perde essa relação direta. Ainda vai haver uma relação entre a velocidade da transformação e a velocidade do seu passo mais lento, apenas não será mais 1 para 1. Mas, para manter o didatismo do post, vamos assumir uma transformação perfeitamente esférica e sem atrito, na qual essa condição vale. ;-)


Para aumentar a velocidade de uma transformação, portanto, devemos aumentar a velocidade do fluxo (em l/s) no gargalo. Quando a velocidade no gargalo tiver subido o bastante, algum outro passo vai ser o responsável por restringir a velocidade da transformação, se transformando no novo gargalo. Removemos esse novo gargalo, aumentando a velocidade da transformação, e outro aparecerá, reiniciando o processo. Eventualmente, chegaremos num ponto de rendimento máximo da transformação, no qual uma nova mudança em qualquer passo não vai resultar em aumento de velocidade da transformação.

Simples, não?

Engarrafamento de Linhas

E como descobrimos gargalos em uma transformação?

Usando o Spoon para detectar gargalos: notou um pontilhado em volta de algum passo? É um gargalo!
Usando o Spoon para detectar gargalos: notou um pontilhado em volta de algum passo? É um gargalo!

Se você estiver rodando-a no Spoon, a interface gráfica do PDI, é muito fácil. O PDI detecta o gargalo, definido por ele como “o passo que recebe mais linhas do que é capaz de processar”, e faz com que o Spoon apresente um pontilhado animado em volta de cada passo identificado como gargalo. Esse monitoramento ocorre em tempo real, e causa efeitos curiosos: em certas situações o gargalo parece “pular” entre dois ou mais passos, e em outras vários passos são marcados como gargalos, simultaneamente. A dinâmica que causa isso está fora do escopo deste post, mas não é difícil de entender se você parar para pensar um pouco.

Para que seu Spoon mostre esse passo na sua transformação você precisa habilitar o checkbox Show bottleneck transformation steps, conforme visto na figura abaixo. Essa janela aparece quando selecionamos o item Options, no menu Tools.

Onde fica o checkbox que habilita a exibição do gargalo.
Onde fica o checkbox que habilita a exibição do gargalo.

Essa opção é muito boa durante o processo de desenvolvimento, mas o que fazer para transformações que rodamos em produção? É um ambiente diferente – memória diferente, disco diferente, carga (tarefas simultâneas) diferentes! Se bobear até o sistema operacional é diferente! Não dá para rodar uma por uma no Spoon e ficar olhando, esperando encontrar um passo-gargalo.

Você adivinhou: consultamos o log de performance.

Entendendo o Gargalo

Conforme o próprio PDI coloca, um gargalo (ou restrição) é “um passo que recebe mais linhas do que é capaz de processar”. Para entender isso você precisa saber um pouco sobre a estrutura interna de um passo.

Um passo possui, grosso modo, três partes:

  1. Buffer de entrada: onde são gravadas as linhas que chegam do passo anterior, e onde elas ficam esperando ser processadas;
  2. Motor do passo: retira uma linha do buffer de entrada, processa e grava o resultado no buffer de saída, repetindo esse processo enquanto o passo anterior estiver mandando linhas;
  3. Buffer de entrada: onde são gravadas as linhas processadas pelo passo, e onde elas aguardam até o engine do PDI movê-las para o passo seguinte.
Todo passo possui dois buffers: entrada e saída.
Todo passo possui dois buffers: entrada e saída.

Assim, se o motor do passo, o processamento em si, não for rápido o bastante, ele começa a ficar para trás: as linhas começam a se acumular no buffer de entrada. Se, além disso, se o passo seguinte for no mínimo tão rápido quanto o passo atual, as linhas processadas não “esquentam cadeira” no buffer de saída, e vão sendo levadas adiante. Por exemplo, na transformação simples mostrada abaixo, com três passos, o passo do meio é um gargalo:

O passo do meio é um gargalo: buffer de saída com menos linhas que o de entrada.
O passo do meio é um gargalo: buffer de saída com menos linhas que o de entrada.

Note que os buffers do passo do meio, representados pelas caixas de entrada e saída, aparecem vazio na saída e cheio na entrada. Essa é a condição de gargalo, ou de restrição. Se pudermos aumentar (elevar) a vazão dessa restrição, a velocidade da transformação, como um todo, aumentará.

Como vemos isso nos logs? Bom, quando rodamos uma transformação no Spoon, este monta um quadro de métricas dos passos, e atualiza-o conforme a transformação vai sendo executada, conforme as linhas vão sendo processadas.

Passos e seus parâmetros durante execução.
Passos e seus parâmetros durante execução.

A coluna input/output, última à direita na imagem anterior, exibe a relação entre a quantidade de linhas presentes no buffer de entrada, aguardando processamento, e quantidade de linhas no buffer de saída, aguardando transporte até o passo seguinte. Sempre que um passo possui mais linhas em sua entradas que em sua saída, ele está “segurando” a transformação. Para encontrarmos os gargalos de uma transformação, então, basta analisarmos os passos dela, em busca de buffers de entrada cheios e buffers de saída vazios (ou com bem menos linhas.)

Analisando Vazão em Transformações

O log que captura as métricas de cada passo, segundo a segundo, chama-se Performance. Veja como ativá-lo no primeiro post da série. Essa tabela possui as seguintes colunas:

  • id_batch
  • seq_nr
  • logdate
  • transname
  • stepname
  • step_copy
  • lines_read
  • lines_written
  • lines_updated
  • lines_input
  • lines_output
  • lines_rejected
  • errors
  • input_buffer_rows
  • output_buffer_rows

Reparou nas duas últimas? São as que nos interessam.


Não vou montar uma solução pronta, mas mostrar como usar essa tabela para desenhar algo que atenda à sua necessidade – e depois vou mostrar um exemplo do relatório que eu uso para “debugar” transformações em produção.


Precisamos de uma consulta que traga os buffers, por cada passo, por transformação. Além disso, como cada transformação pode ter sido executada várias vezes, precisamos filtrar também por lote (id_batch.) Essa consulta ficaria assim:

SELECT
  id_batch,
  transname,
  stepname,
  input_buffer_rows,
  output_buffer_rows
FROM transformation_performance
WHERE
  id_batch = X AND
  transname = 'x'

Filtrando para lote 2080 e transformação Carga da Lista de Clientes PF, temos:

id_batch transname stepname input_buffer_rows output_buffer_rows
2080 Carga da Lista de Clientes PF Gera strings aleatórias 224 276
2080 Carga da Lista de Clientes PF Cria CEP, Telefone e Fax 0 0
2080 Carga da Lista de Clientes PF Cria CEP, Telefone e Fax 0 0
2080 Carga da Lista de Clientes PF Lê nomes 0 0
2080 Carga da Lista de Clientes PF Cria as partes de CEP, Telefone e Fax 0 0
2080 Carga da Lista de Clientes PF Muda rnd_telefone/fax para string 0 0
2080 Carga da Lista de Clientes PF Seleciona endereço 2 0
2080 Carga da Lista de Clientes PF Lê sobrenomes 0 61
2080 Carga da Lista de Clientes PF Acerta tamanho das strings 0 0
2080 Carga da Lista de Clientes PF Seleciona sobrenome 61 0
2080 Carga da Lista de Clientes PF Lê lista de enderecos 0 0
2080 Carga da Lista de Clientes PF Seleciona nome 0 0
2080 Carga da Lista de Clientes PF Cria ID do Cliente 1560 225
2080 Carga da Lista de Clientes PF Insere cidade_id 1 3
2080 Carga da Lista de Clientes PF Limpa fluxo 0 0
2080 Carga da Lista de Clientes PF Cria índice do endereço 276 125
2080 Carga da Lista de Clientes PF Gerar linhas 0 2444
2080 Carga da Lista de Clientes PF Cria índices de nome 147 110
2080 Carga da Lista de Clientes PF String operations 3 1
2080 Carga da Lista de Clientes PF Absoluto de rnd_cpf 110 3
2080 Carga da Lista de Clientes PF Grava tabela 0 0
2080 Carga da Lista de Clientes PF Gera strings aleatórias 1778 5755
2080 Carga da Lista de Clientes PF Cria CEP, Telefone e Fax 0 0

E aí, consegue dizer se algum destes passos é um gargalo? Nem eu. Vamos melhorar nossa consulta: vamos agrupar por passo, remover o nome da transformação e lote da execução, já que ambos são previamente conhecidos (estão no filtro.) Fica assim:

SELECT
  stepname,
  sum(input_buffer_rows) as buffer_entrada,
  sum(output_buffer_rows) as buffer_saida
FROM transformation_performance
WHERE
  id_batch = 2080 AND
  transname = 'Carga da Lista de Clientes PF'
GROUP BY
  stepname
ORDER BY
  stepname

Resultado:

stepname buffer_entrada buffer_saida
Absoluto de rnd_cpf 4289 2770
Acerta tamanho das strings 10117 10100
Cria as partes de CEP, Telefone e Fax 100 10098
Cria CEP, Telefone e Fax 10101 20099
Cria ID do Cliente 21021 11892
Cria índice do endereço 15660 3458
Cria índices de nome 3483 4289
Gerar linhas 0 21902
Gera strings aleatórias 11889 15658
Grava tabela 20281 0
Insere cidade_id 202 259
Lê lista de enderecos 0 0
Lê nomes 0 0
Lê sobrenomes 0 61
Limpa fluxo 20099 20281
Muda rnd_telefone/fax para string 125 100
Seleciona endereço 258 714
Seleciona nome 714 100
Seleciona sobrenome 161 125
String operations 2770 202

Já dá para dizer se alguém é gargalo? Não! Veja, buffer de entrada e buffer de saída são métricas não-aditivas: somá-las ao longo do tempo não dá nenhuma informação sobre o processo. Nem somando-as ao longo dos passos, ou das transformações, nada! No máximo, como métricas não-aditivas, talvez uma média ao longo do tempo possa dar alguma idéia do que está acontecendo.

Mesmo assim, a média não ajudaria. Lembra-se que o monitoramento de gargalo do Spoon ocorre em tempo real? E que os gargalos podem pular de um passo para outro? Só conseguimos identificar um gargalo olhando o comportamento dele ao longo do tempo, momento a momento.

Incluíndo o tempo na consulta, temos:

SELECT
  stepname,
  logdate,
  sum(input_buffer_rows) as buffer_entrada,
  sum(output_buffer_rows) as buffer_saida
FROM transformation_performance
WHERE
  id_batch = 2080 AND
  transname = 'Carga da Lista de Clientes PF'
GROUP BY
  stepname,
  logdate
ORDER BY
  stepname

Vai nos dar a seguinte saída:

stepname logdate buffer_entrada buffer_saida
Absoluto de rnd_cpf 2016-02-21 16:34:41 110 3
Absoluto de rnd_cpf 2016-02-21 16:34:41 110 3
Absoluto de rnd_cpf 2016-02-21 16:34:42 87 100
Absoluto de rnd_cpf 2016-02-21 16:34:43 4092 2667
Absoluto de rnd_cpf 2016-02-21 16:34:44 0 0
Absoluto de rnd_cpf 2016-02-21 16:34:45 0 0
Absoluto de rnd_cpf 2016-02-21 16:34:46 0 0
Absoluto de rnd_cpf 2016-02-21 16:34:46 0 0
Acerta tamanho das strings 2016-02-21 16:34:41 0 0
Acerta tamanho das strings 2016-02-21 16:34:42 0 0
Acerta tamanho das strings 2016-02-21 16:34:43 120 100
Acerta tamanho das strings 2016-02-21 16:34:44 9997 10000
Acerta tamanho das strings 2016-02-21 16:34:45 0 0
Acerta tamanho das strings 2016-02-21 16:34:46 0 0
Acerta tamanho das strings 2016-02-21 16:34:46 0 0
Cria as partes de CEP, Telefone e Fax 2016-02-21 16:34:41 0 0
Cria as partes de CEP, Telefone e Fax 2016-02-21 16:34:42 0 0
Cria as partes de CEP, Telefone e Fax 2016-02-21 16:34:43 100 101
Cria as partes de CEP, Telefone e Fax 2016-02-21 16:34:44 0 9997
Cria as partes de CEP, Telefone e Fax 2016-02-21 16:34:45 0 0
Cria as partes de CEP, Telefone e Fax 2016-02-21 16:34:46 0 0
Cria as partes de CEP, Telefone e Fax 2016-02-21 16:34:46 0 0
Cria CEP, Telefone e Fax 2016-02-21 16:34:41 0 0
Cria CEP, Telefone e Fax 2016-02-21 16:34:42 0 0
Cria CEP, Telefone e Fax 2016-02-21 16:34:43 101 100
Cria CEP, Telefone e Fax 2016-02-21 16:34:44 10000 10000
Cria CEP, Telefone e Fax 2016-02-21 16:34:45 0 9999
Cria CEP, Telefone e Fax 2016-02-21 16:34:46 0 0

Agora podemos dizer, se em algum instante, um passo se comportou como um gargalo. Claro que examinar os dados assim é uma bobagem – podemos montar um gráfico com isso.

Relatório de Análise de Fluxos

Juntando essa consulta com os relatórios do primeiro post, montamos um relatório de análise de fluxo de transformações:

Relatório de análise de fluxo em transformações.
Relatório de análise de fluxo em transformações.

Esse relatório traça a velocidade daquele passo em linhas por segundo no gráfico de cima, e o volume de linhas presentes nos buffers, no gráfico de baixo, tudo instante a instante. O que vemos é que esse passo possui uma vazão boa, até, de 20.000 l/s, e que ele nunca esteve em condição de gargalo. Muito pelo contrário: no instante 5 ele teve o seu buffer de saída completamente cheio, sugerindo que o passo seguinte poderia ser um gargalo.

Eis o trecho da transformação que estamos analisando:

Trecho final da transformação de carga de clientes PF da Beltrano S/A.
Trecho final da transformação de carga de clientes PF da Beltrano S/A.

Os passos seguintes são um Select Values e um Table Output. Eis os buffers do Select Values:

Buffers do Select Values.
Buffers do Select Values.

E os do Table Output:

Buffers do Table Output.
Buffers do Table Output.

A-há! Veja ali, do momento 3 ao momento 6, como o buffer de entrada (a linha vermelha) sobe até atingir 10.000 linhas, enquanto o buffer de saída permanece em zero! Esse passo é um gargalo! Ele ficou com o buffer de entrada cheio, que fez com que as linhas “transbordassem” para o passo anterior, o Select Values. Tanto esse Select Values não é um gargalo que os dois buffers sobem e descem juntos. Mais ainda: foi justamente entre os momentos 3 e 6 (comuns a todos os passos, aliás, pois o log de performance captura todo mundo junto) que o buffer de saída do passo Cria CEP, Telefone e Fax saturou, isto é, atingiu seu limite de 10.000 linhas, e começou a se refletir no buffer de entrada, mesmo que por apenas um segundo (cada momento equivale a um segundo nesta escala.)

Conclusão

A partir da consulta mostrada podemos criar um relatório que exibe o estado dos buffers de cada passo momento a momento. Usando a transformação como guia, podemos analisar cada um dos passos em busca do sintoma de restrição de fluxo.

Essa não é a melhor análise, já que nos obriga a olhar um por um. Como você faria essa análise automaticamente? Como deixar para o Report Designer detectar os gargalos de uma transformação?

E apenas para fechar o problema encontrado neste exemplo: um gargalo em um passo que grava dados no banco significa que a rede e o banco é que são os gargalos. Dependendo da influência da rede e do banco na força da restrição, podemos levar o processamento para mais perto (reduzindo o custo da rede) ou melhorar o banco com, por exemplo, discos mais rápidos.

Na próxima semana terminaremos a série com um relatório de genealogia de um processo de ETL.

Até lá!


Novo Lançamento!

Sexta-feira, 11 de março de 2016, será o lançamento do meu novo livro, Autopublicação na Prática. Não deixe de passar por aqui, porque – como sempre – ele será lançado por R$ 0,00!

Analisando os Logs do PDI – Parte 1

O Pentaho Data Integration, antigamente conhecido por Kettle, é a ferramenta de integração de dados da Suite Pentaho. É, de longe, a ferramenta de maior sucesso deles e tanto é assim que fornecedores tradicionais desse mercado “sentiram o calor” da concorrência.

Neste post eu não vou falar sobre o PDI em si, mas sobre uma de seus recursos: o log.

Toco Cru

Em inglês, log significa “tora de madeira”.

Those are the logs.
Those are the logs.

Ou diário, como em captain’s log, o diário do capitão, ou lab log, que é aquele caderninho preto em que cientistas anotam o que aconteceu nos experimentos. :-) Quando dizemos que um programa gera logs, estamos querendo dizer que ele registra, em algum lugar, informações sobre o que está acontecendo. Podem ser erros, avisos ou simplesmente uma narração das ações do programa. Em geral, cada entrada no registro possui uma etiqueta de tempo, ou timestamp, para que seja possível saber quando tal evento ocorreu, e colocá-los em sequência, para estudo.

Transformações e jobs do PDI geram uma profusão de dados durante seu processamento, e a maioria dos usuários do PDI vê parte desses dados na saída do processamento:

geekbi@dw:/opt/pentaho/5.1/data-integration$ ./pan.sh -file=/home/Beltrano/00_Beltrano_1.0/ETL/t_d_date.ktr
2016/02/21 10:50:04 - Pan - Start of run.
2016/02/21 10:50:05 - Dimensão Data - Dispatching started for transformation [Dimensão Data]
2016/02/21 10:50:05 - d_date.0 - Connected to database [beltrano_dw] (commit=10000)
2016/02/21 10:50:05 - Days of week.0 - Finished processing (I=0, O=0, R=0, W=7, U=0, E=0)
2016/02/21 10:50:05 - Months.0 - Finished processing (I=0, O=0, R=0, W=12, U=0, E=0)
2016/02/21 10:50:05 - 10000 dias: 25 anos.0 - LineNr : 5000
2016/02/21 10:50:05 - Days_since.0 - linenr 5000
2016/02/21 10:50:05 - 10000 dias: 25 anos.0 - LineNr : 10000
2016/02/21 10:50:05 - 10000 dias: 25 anos.0 - Finished processing (I=0, O=0, R=0, W=10000, U=0, E=0)
2016/02/21 10:50:05 - Days_since.0 - linenr 10000
2016/02/21 10:50:05 - Days_since.0 - Finished processing (I=0, O=0, R=10000, W=10000, U=0, E=0)
2016/02/21 10:50:05 - Calc Date.0 - Linenr 5000
2016/02/21 10:50:05 - DayOfWeekDesc.0 - linenr 5000
2016/02/21 10:50:05 - MonthDesc.0 - linenr 5000
2016/02/21 10:50:05 - Select values.0 - linenr 5000
2016/02/21 10:50:05 - d_date.0 - linenr 5000
2016/02/21 10:50:06 - Calc Date.0 - Linenr 10000
2016/02/21 10:50:06 - Calc Date.0 - Finished processing (I=0, O=0, R=10000, W=10000, U=0, E=0)
2016/02/21 10:50:06 - Quarter.0 - Finished processing (I=0, O=0, R=10000, W=10000, U=0, E=0)
2016/02/21 10:50:06 - DayOfWeekDesc.0 - linenr 10000
2016/02/21 10:50:06 - DayOfWeekDesc.0 - Finished processing (I=0, O=0, R=10007, W=10000, U=0, E=0)
2016/02/21 10:50:06 - MonthDesc.0 - linenr 10000
2016/02/21 10:50:06 - MonthDesc.0 - Finished processing (I=0, O=0, R=10012, W=10000, U=0, E=0)
2016/02/21 10:50:06 - Select values.0 - linenr 10000
2016/02/21 10:50:06 - Select values.0 - Finished processing (I=0, O=0, R=10000, W=10000, U=0, E=0)
2016/02/21 10:50:06 - d_date.0 - linenr 10000
2016/02/21 10:50:06 - d_date.0 - Finished processing (I=0, O=10000, R=10000, W=10000, U=0, E=0)
2016/02/21 10:50:06 - Pan - Finished!
2016/02/21 10:50:06 - Pan - Start=2016/02/21 10:50:04.785, Stop=2016/02/21 10:50:06.757
2016/02/21 10:50:06 - Pan - Processing ended after 1 seconds.
2016/02/21 10:50:06 - Dimensão Data -
2016/02/21 10:50:06 - Dimensão Data - Step Days_since.0 ended successfully, processed 10000 lines. ( 10000 lines/s)
2016/02/21 10:50:06 - Dimensão Data - Step Calc Date.0 ended successfully, processed 10000 lines. ( 10000 lines/s)
2016/02/21 10:50:06 - Dimensão Data - Step DayOfWeekDesc.0 ended successfully, processed 10007 lines. ( 10007 lines/s)
2016/02/21 10:50:06 - Dimensão Data - Step MonthDesc.0 ended successfully, processed 10012 lines. ( 10012 lines/s)
2016/02/21 10:50:06 - Dimensão Data - Step Select values.0 ended successfully, processed 10000 lines. ( 10000 lines/s)
2016/02/21 10:50:06 - Dimensão Data - Step d_date.0 ended successfully, processed 10000 lines. ( 10000 lines/s)
2016/02/21 10:50:06 - Dimensão Data - Step Quarter.0 ended successfully, processed 10000 lines. ( 10000 lines/s)
2016/02/21 10:50:06 - Dimensão Data - Step Days of week.0 ended successfully, processed 7 lines. ( 7 lines/s)
2016/02/21 10:50:06 - Dimensão Data - Step Months.0 ended successfully, processed 12 lines. ( 12 lines/s)
2016/02/21 10:50:06 - Dimensão Data - Step 10000 dias: 25 anos.0 ended successfully, processed 10000 lines. ( 10000 lines/s)

Essa é só a narração dos eventos, mas muitos outros dados são capturado durante o processamento: quantidade de linhas lidas e gravadas em bancos e arquivos, quantidade de linhas processadas em cada passo, conteúdo de variáveis carregadas, conexões feitas, erros, aviso – o PDI captura tudo, tudo, até a vazão da pia da cozinha. Esses dados podem ser descarregados diretamente num banco, o que habilita o gestor do projeto a monitorar a carga minuciosamente. Por exemplo, podemos criar indicadores de performance e acompanhá-los diariamente, ou buscar informações sobre o que aconteceu durante uma corrida qualquer do ETL.

Está série é composta de três posts. Nesta primeira parte vamos mostrar como configurar o PDI para injetar os dados capturados em um banco de dados e como construir um relatório bem simples, com indicadores básicos sobre o processamento.

Na Parte 2 veremos como descobrir gargalos, que são pontos de lentidão no fluxo de dados. Por fim, na parte 3, veremos um relatório que dá a genealogia (quem chamou quem, a que horas e quanto tempo levou) de um job, uma informação crucial para o monitoramento da “saúde” do processo.

Ativando a Captura de Log

O PDI descarrega seu log apenas em bancos de dados. Por isso você precisa ter um banco de destino para gravar seus logs. Normalmente eu mantenho um esquema de log nos mesmos bancos dos DWs, pois isso simplifica tanto o desenvolvimento quanto a análise.

Uma vez que você tenha um banco/esquema, precisamos definir quais tabelas receberão os logs. De novo, eu adoto um padrão: trans_LOG para log das transformações, e job_LOG para jobs. Neste caso, LOG é o nome do log capturado na tabela.

Na versão 5.4 o PDI captura os seguintes logs (com o nome da tabela que eu uso entre parênteses):

  • Transformações:
    1. Transformation (transformation): registra uma linha por transformação, por corrida. O campo LOG_FIELD contém o log de texto do processamento;
    2. Step (transformation_steps): registra um sumário do processamento de cada passo, em cada transformação, por corrida, em uma linha por passo. Ligado à anterior pela coluna ID_BATCH;
    3. Performance (transformation_performance): mede a vazão de linhas através de cada passo, segundo a segundo, em cada corrida de uma transformação, uma linha por passo, por segundo. Ligado à tabela de log de transformações pela coluna ID_BATCH;
    4. Logging channels (transformation_lc): registra os nomes e propriedades dos “canais” de dados em uma transformação ou job;
    5. Metrics (não coleto esta): http://forums.pentaho.com/showthread.php?137582-Output-steps-metrics-How-to-use
  • Jobs:
    1. Jobs (jobs): equivalente à da transformação, registra os dados de cada execução de um job em uma linha. Assim como na transformação, o mesmo campo LOG_FIELD contém toda saída de texto do processamento;
    2. Job Entry (job_entry):
    3. Job Logginc Channels (job_lc): registra os nomes e propriedades dos “canais” de dados em uma transformação ou job. Possui o mesmo layout que a logging channels das transformações. Pelo que entendi, a “boa prática” é serem a mesma.

Eu simplifiquei um pouco essas descrições, ou a coisa viraria um livro. Por exemplo, cada log possui parâmetros de tempo de vida e latência de atualização. Consultem o Pentaho Infocenter, a Wiki, o Kettle Solutions ou o Pentaho na Prática para mais informações. Nosso foco hoje é aprender a capturar e usar.


Todos esses parâmetros são entrados na aba de log das janelas de propriedades das transformações…

Aba de logs de uma transformação.
Aba de logs de uma transformação.

 

… e jobs:

Aba de logs de um job.
Aba de logs de um job.

Para chamar essa janela clique com o botão direito sobre qualquer parte em branco de um job ou transformação e, no menu que aparecer, escolha Transformation settings (ou Job settings.)

Esses parâmetros podem ser preenchidos de três maneiras:

  1. Manualmente, entrando os nomes de tabelas e valores dos parâmetros um por um, em toda transformação e job que você criar. Essa alternativa é boa para testes e estudos, enquanto você não está em projeto, por que é mais rápida e prática. Só que, se estiver em um projeto e definir todos os valores manualmente, você vai ter um trampo cabuloso para mudar qualquer coisa, pois vai ter que editar todos os artefatos, um por um, do processo inteiro;
  2. Por meio de variáveis definidas pelo desenvolvedor: defina uma variável para cada parâmetro no arquivo kettle.properties e insira-as nos lugares adequados usando a sintaxe ${VARIÁVEL}. O início é tedioso, como na opção anterior, mas é mais flexível pois basta mudar a variável no kettle.properties para propagar seu valor por todo processo. É a melhor opção quando temos vários projetos, pois podemos criar lotes de variáveis para cada um;
  3. Definindo valores das variáveis-default do PDI: esse método é de longe o mais prático para quem lida com um só projeto, pois você não precisa alterar nada em nenhuma transformação ou job. Cada campo das definições de log possui uma variável default. Definindo o valor destas no kettle.properties, o PDI configura a captura do log automaticamente. Por outro lado, se você trabalhar em mais de um projeto, vai precisar trocar essas variáveis sempre que mudar de projeto.

Alguns truques facilitam a vida do desenvolvedor: ao invés de configurar manualmente o log em todos os artefatos, crie modelos de transformação e job já configurados. Daí, quando precisar criar uma nova transformação/job, use o modelo: duplique o arquivo ou abra e salve com outro nome e comece dali. Meus modelos não apenas possuem os logs pré-configurados, como ainda deixo notas e padrões de documentação pré-preenchidos.


As variáveis são definidas no arquivo <diretório do usuário>/.kettle/kettle.properties. O Spoon oferece uma interface para fazer essa edição sem precisar sair da interface gráfica: clique no menu Edit e escolha a opção Edit the kettle.properties file. Aparecerá uma janela listando todas as variáveis default, mesmo que estejam vazias, além de todas as variáveis definidas pelo desenvolvedor:

Janela de edição do conteúdo do arquivo kettle.properties (variáveis.)
Janela de edição do conteúdo do arquivo kettle.properties (variáveis.)

Lembre-se de fechar o Spoon antes de editar o arquivo diretamente. Caso contrário, não apenas ele não vai ver os novos valores e variáveis, como ainda corre-se o risco de elas serem apagadas.

Um último detalhe: quase todos os logs são capturados por default. Quase: o de performance de passo, que é o que mais gera linhas no banco, está desligado por default. Para ativá-lo marque o checkbox Enable step performance monitoring? na aba Monitoring (a última na versão 5.4) das propriedades da transformação:

Ligue o checkbox para capturar o rendimento de cada passo da transformação.
Ligue o checkbox para capturar o rendimento de cada passo da transformação.

Criando as Tabelas

Como tudo no Spoon, sempre que algo escreve em uma tabela, um botão que permite criar ou ajustar essa tabela automaticamente. Para criar qualquer tabela de log basta preencher seus dados de conexão e nome, e clicar no botão SQL no fundo da janela de propriedades:

Criando as tabelas de log de transformação com ajuda do botão SQL.
Criando as tabelas de log de transformação com ajuda do botão SQL.

Se você preencher todos os parâmetros antes e deixar o botão de SQL para o fim, o Spoon vai te dar um monte de janelas, uma para cada tabela. O mesmo vale os logs de jobs.

Analisando Logs

Para os exemplos vamos usar o log gerado pela repetição do processo de carga do Beltrano S/A, de mil pedidos. Esse backup está disponível aqui, como um pacote RAR. Consulte as instruções de restauração da base Beltrano, lembrando apenas de substituir o nome do arquivo para o do arquivo da base de logs.

Vamos ver dois relatórios tão simples quanto úteis:

  • Logs de jobs e transformações
  • Histórico de performance

Logs de Jobs e Transformações

Quem cuida de processos de ETL sabe que a principal pergunta que se faz é “Rodou? Deu certo? O que aconteceu?”

Ok, são três perguntas, mas no fundo todas elas querem saber a mesma coisa: se o processo funcionou ou não, e o que aconteceu, se tiver dado erro.

A forma mais simples de respondê-las seria olhar o log de texto, o relato de tudo que se passou durante o processamento. É o primeiro lugar que devemos olhar porque é ali que estão as mensagens de erros. Eu chamo esse tipo de relatório simplesmente de “Relatório de Log”: Log de Jobs e Log de Transformações. Ele mostra um cabeçalho com parâmetros do objeto sendo examinado, como datas, status e duração, e logo abaixo o conteúdo de texto do processamento. A consulta que eu uso é assim:

Transformação

SELECT
     'Duracao em Segundos' as categoria,
     id_batch,
     transname,
     status,
     lines_read,
     lines_written,
     lines_updated,
     lines_input,
     lines_output,
     lines_rejected,
     replaydate,
     logdate,
     extract(hour from (logdate - replaydate))*60*60 +
          extract(minute from (logdate - replaydate))*60 +
          extract(second from (logdate - replaydate)) as duracao,
     log_field
FROM
     transformation

Job

SELECT
     'Duracao em Segundos' as categoria,
     id_job,
     jobname,
     status,
     lines_read,
     lines_written,
     lines_updated,
     lines_input,
     lines_output,
     lines_rejected,
     replaydate,
     logdate,
     extract(hour from (logdate - replaydate))*60*60 +
          extract(minute from (logdate - replaydate))*60 +
          extract(second from (logdate - replaydate)) as duracao,
     log_field
FROM
     job

Note a diferença entre os nomes dos campos dos dois artefatos (job e transformação), mas como mesmo assim são parecidos.

Essas consultas retornam todos os logs, de todas as transformações e jobs, o que é uma coisa inútil – imagine ter que varrer centenas, milhares de páginas atrás de um job ou outro! Por isso eu filtro essa consulta com dois prompts: um para nome do artefato e outro para seu batch, ou lote. As consultas para nomes são assim:

Lista de nomes de transformações:

SELECT DISTINCT transname
FROM transformation
ORDER BY transname ASC

Lista de nomes de jobs:

SELECT DISTINCT jobname
FROM job
ORDER BY jobname ASC

Quando o prompt de nome é preenchido, o prompt de lote fica disponível. As consultas para lotes são:

Lista de lotes de transformações:

SELECT id_batch,replaydate
FROM transformation
WHERE transname=${nome_transformacao}
ORDER BY id_batch DESC

Lista de lotes de jobs:

SELECT id_job,replaydate
FROM job
WHERE jobname=${nome_job}
ORDER BY id_job DESC

Com os dois prompts preenchidos (que estão marcados como obrigatórios, a propósito), o relatório roda:

Relatório de log de transformação.
Relatório de log de transformação.

Histórico de Performance

O mesmo resultado é obtido no relatório de jobs e seria redundante mostrá-lo tal como está. Vamos avançar um pouco, e mostrar o relatório de jobs com um pouco mais de informações: um histórico de duração.

Depois de saber “se deu certo”, o aspecto mais importante do ETL é sua repetibilidade. Afinal, se montamos um processo acreditando que, todo dia, ele vai rodar em menos de uma hora, precisamos saber se isso está acontecendo ou não.

Poderíamos usar o relatório anterior e, manualmente, examinar os tempos de um mesmo job ou transformação, em várias corridas ao longo de um período – uma semana, por exemplo. Trabalhoso. Bom, nós somos o pessoal da Inteligência de Negócios ou não? Como seria um vexame ter tanto trabalho, vamos ser mais práticos: vamos colocar um gráfico das várias execuções.

Pegamos o mesmo relatório anterior e acrescentamos uma nova consulta: a lista da duração das últimas X execuções em ordem crescente:

SELECT * FROM
(SELECT
     'Duracao em Segundos' as categoria,
     id_job,
     to_char(replaydate, 'DD/MM/YY HH24:MI:SS') as replaydate,
     extract(hour from (logdate - replaydate))*60*60 +
     extract(minute from (logdate - replaydate))*60 +
     extract(second from (logdate - replaydate)) as duracao
FROM job
WHERE jobname=${nome_job}
ORDER BY id_job DESC
LIMIT 30) AS X
ORDER BY id_job ASC

O SELECT interno obtém uma lista das últimas 30 execuções do job nome_job (o nome do parâmetro que contém a seleção do usuário). O SELECT externo inverte a ordem para que o gráfico seja plotado corretamente. O resultado dessa consulta para, por exemplo, o job Popula Tabelas Dependentes, é:

categoria id_job replaydate Duracao
Duracao em Segundos 2 23/02/16 20:34:24 1.064
Duracao em Segundos 5 23/02/16 20:51:37 0.671
Duracao em Segundos 7 23/02/16 20:54:54 0.655
Duracao em Segundos 11 23/02/16 21:01:08 0.652
Duracao em Segundos 14 23/02/16 21:07:54 0.742

Como um relatório pode ter apenas uma consulta ativa, e o relatório tal como está já usa uma, precisamos adicionar um sub-relatório para poder usar essa nova query. Neste sub-relatório, que herda essa consulta, adicionamos apenas o elemento de chart. Eu usei um gráfico de linhas, mas poderíamos usar qualquer outro tipo – barras, bullets, área etc.

Sub-relatório com consulta e parâmetro herdados do relatório principal.
Sub-relatório com consulta e parâmetro herdados do relatório principal.

Rodando o relatório o PRD vai exibir o prompt, já visto na figura acima. Já ao selecionar um job o gráfico será preenchido. Após selecionarmos um dos lotes – uma das corridas ou execuções do job – aparece o log logo (hehe) abaixo do plot.

Resultado: log do job selecionado, com visão simultânea das últimas execuções.
Resultado: log do job selecionado, com visão simultânea das últimas execuções.

Você pode baixar esses relatórios neste link.


Todos os materiais foram criados com a suite 5.4 do Pentaho. Se você tentar usar, e não conseguir, deixe um comentário que eu tento te ajudar.


Conclusão

Com um pouco mais de trabalho – configurar a captura de log e criar os relatórios – o gestor de ETL pode conseguir uma riqueza de informações sobre seu processo. Entre outros benefícios, o gerente de ETL passa a ser capaz de dizer, em poucos minutos, com poucos cliques, se o processo rodou hoje, se demorou mais, se deu algum erro e vários outros detalhes (incluindo volume processado e tempo de cada processamento.)

Outra função importante servida pelos logs é apoio na melhoria do processo. No próximo post veremos como usar os dados de performance das transformações para analisar o perfil de processamento e identificar gargalos.

Até lá! ;-)

Relatórios com Metamodelos – Complemento

Semana passada eu mostrei como montar um relatatório a partir de um metamodelo. Hoje eu vou encerrar o assunto com mais algumas dicas.

Concepts

Um dos pilares de um bom relatório é a qualidade da apresentação, e nesta qualidade inclui-se a consistência, ou seja, dados do mesmo tipo possuem a mesma aparência, ou no mínimo possuem algum padrão.

Um relatório ordinário, construído a partir de um SQL, tem seus campos formatados livremente. Se por um lado isso é bom, já que dá espaço irrestrito para o autor do relatório passar sua mensagem, por outra é ruim, porque obriga esse mesmo autor a uma formatação tediosa, repetitiva. Fora o aborrecimento de repetir sempre a mesma coisa, ainda podemos acabar esquecendo ou mudando algum detalhe ao longo do tempo.

Um concept, ou conceito, é um padrão de formatação definido dentro do metamodelo e obedecido integralmente pelo PRD. Acesse esta página para obter mais informações sobre concepts.

Vamos fazer um exercício: criaremos um conceito de texto destacado e aplicaremos ao relatório do post anterior. Primeiro, abrimos o PME e carregamos o metamodelo do Beltrano OLTP. Em seguida acessamos o editor de conceitos (concept editor):

Acessando editor de conceitos no PME.
Acessando editor de conceitos no PME.

Nele clicamos sobre o conceito Base e depois clicamos no sinal de + no canto superior direito dessa seção. Uma janela se abrirá, pedindo o nome do conceito – eu eu chamei de Destaque. Como criamos o novo conceito a partir do Base, Destaque herdou as propriedades deste. Caso contrário, se tívéssemos criado um conceito na raiz, ele teria nascido vazio e precisaria receber todos os atributos prentendidos.

Com o conceito Destaque selecionado, clique no elo ao lado do atributo Color of Text. Isso quebra a herança e nos permite redefinir aquele atributo, mantendo o restante:

Novo conceito: destaque (fonte em cor vermelha).
Novo conceito: destaque (fonte em cor vermelha).

Dê ok e, de volta à interface principal do PME, localize um campo na camada de apresentação – Neste exemplo eu selecionei Autor. Clique com o botão da direita sobre ele e selecione Set Parent Concept… para escolher o conceito desejado. Selecione Destaque na janela que se abrirá e Ok para aplicar.

Atribuindo conceito a campo Autor.
Atribuindo conceito a campo Autor.

Feito! Quando o campo Autor for usado no relatório, ele aparecerá em cor vermelha, em destaque:

Conceito entra em ação automaticamente.
Conceito entra em ação automaticamente.

Notou a festa do caqui que está o layout do relatório, na parte de cima da figura? Mas como o PRD vai respeitar o definido no metamodelo, o relatório vai sair arrumadinho, apenas com o nome do autor em vermelho!

Outro exemplo: digamos que precisamos formatar a coluna da direita como um valor monetário, usando a máscara R$ #.###,00. Alteramos ou criamos um conceito no metamodelo, exportamos e reaplicamos o metamodelo à consulta previamente criada:

Altere um conceito para alterar o relatório.
Altere um conceito para alterar o relatório.

Reaplicar o metamodelo a um relatório aberto no PRD é meio chato. Como o PRD não embute o metamodelo no relatório, bastaria purgar o cache ou fechar o relatório e recarregá-lo para forçar o PRD a reler o XMI. Por puro e simples hábito, eu apelo para força bruta: simplesmente apago e recrio a fonte e as consultas (apelando para um copy-paste básico, já que ninguém é de ferro…)

Para relatórios publicados no BA Server não é preciso nada: basta republicar o modelo a partir do PME, ou recarregá-lo no quadro Manage Data Sources e já está valendo. A série 5 do BA Server veio com purga e recarga automática de metamodelos na republicação.


Só essa simplicidade e praticidade, na minha opinião, já é o bastante para fazer valer a pena usar um MQL ao invés de SQL.

Prompts

Um recurso crucial para relatórios são os “prompts”, ou filtros, que permitem ao usuário escolher uma determinada fatia dos dados para apresentação. O PRD oferece esse recurso sob o nome de parameters. Um parameter – ou prompt – é uma variável preenchida pelo usuário em tempo de execução, que entra na consulta como algum tipo de restrição. Por exemplo, a consulta abaixo retorna todos os clientes de um determinado “tipo”, filtrando a consulta pelo conteúdo do prompt tipo_selecionado:

    SELECT cliente, estado, cidade
    WHERE tipo = ${tipo_selecionado}

Não é um mecanismo complicado, mas não é trivial o bastante para eu mostrá-lo aqui completamente. Assistam este vídeo que verão como fazer um prompt.

Quando usamos uma consulta MQL, de metadados, aplicar a restrinção é um pouco mais simples. Os passos são esses:

  1. Construa um relatório com MQL, como o que fizemos no post passado;
  2. Crie uma consulta (MQL, SQL, tanto faz) que retorne a lista de opções que o cliente pode selecionar;
  3. Crie o parâmetro no PRD usando essa consulta como fonte;
  4. Volte para a consulta principal e, usando a interface de criação de consultas, adicione o filtro e insira o parâmetro.

No nosso caso vamos filtrar o relatório de vendas do Alexandre por UF.

Primeiro, construímos uma consulta em MQL que traz a lista de estados:

Lista de UFs criada no construtor de consultas MQL.
Lista de UFs criada no construtor de consultas MQL.

O MQL dessa consulta é:

    <mql>
      <domain_type>relational</domain_type>
      <domain_id>BeltranoOLTP</domain_id>
      <model_id>BV_MODEL_1</model_id>
      <model_name>Beltrano OLTP</model_name>
      <options>
        <disable_distinct>false</disable_distinct>
        <limit>-1</limit>
      </options>
      <selections>
        <selection>
          <view>BC_CLIENTES_PF</view>
          <column>BC_ESTADOS_UF_2</column>
          <aggregation>none</aggregation>
        </selection>
      </selections>
      <constraints/>
      <orders>
        <order>
          <direction>asc</direction>
          <view_id>BC_CLIENTES_PF</view_id>
          <column_id>BC_ESTADOS_UF_2</column_id>
        </order>
      </orders>
    </mql>
    </pre>

Construímos um paramater alimentado por essa consulta, chamado UF:

Novo parâmetro UF, alimentado pela consulta Query 2 do metamodelo.
Novo parâmetro UF, alimentado pela consulta Query 2 do metamodelo.

Depois inserimos esse parâmetro, UF, na consulta que popula o relatório: colocamos a coluna que vai ser filtrada na seção constraints e aplicamos uma igualdade para o parâmetro.

Inserindo o parâmetro na consulta principal com o editor de MQL.
Inserindo o parâmetro na consulta principal com o editor de MQL.

O truque é envolver o parâmetro em chaves, { e }. Com isso o construtor de consultas reconhece que se trata de um parâmetro e não de um valor ordinário, e ajusta a consulta automaticamente. A título de comparação, observe o filtro para pegar apenas as vendas do Alexandre.


Ao adicionar um nome cercado por { e } nos filtros, o construtor de consulta realiza duas coisas:

  1. Define um parâmetro no início da consulta
    <mql>
      <domain_type>relational</domain_type>
      <domain_id>BeltranoOLTP</domain_id>
      <model_id>BV_MODEL_1</model_id>
      <model_name>Beltrano OLTP</model_name>
      <options>
        <disable_distinct>false</disable_distinct>
        <limit>-1</limit>
      </options>
      <parameters>
        <parameter defaultValue="XX" name="UF" type="STRING"/>
      </parameters>
      <selections>
        <selection>
          <view>BC_PEDIDOS</view>
    <...restante da consulta...>        
    
  2. E usa esse parâmetro na seção conditions:
    <...começo da consulta...>
      <constraints>
        <constraint>
          <operator>AND</operator>
          <condition>CONTAINS([BC_PEDIDOS.BC_VENDEDOR_NOME_COMPLETO];"Alexandre")</condition>
        </constraint>
        <constraint>
          <operator>OR</operator>
          <condition>[BC_CLIENTES_PJ.BC_ESTADOS_UF_3] = [param:UF]</condition>
        </constraint>
      </constraints>
    

Ao rodar o relatório o PRD cria e apresenta um controle do tipo drop-down, populado com a lista dos estados. Sempre que um estado é selecionado, o relatório é automaticamente filtrado:

Filtro aplicado em consulta MQL.
Filtro aplicado em consulta MQL.

Um bom uso dessas possibilidades é criar relatórios que se adequam a cada usuário: basta usar o parâmetro pré-definido env::username para filtrar a consulta pelo nome do usuário na plataforma desde que esse usuário possua um nome igual registrado no banco de dados.

Por exemplo, se eu registrar no Pentaho BA Server os vendedores com o mesmo nome que possuem no Beltrano OLTP, eu posso filtrar os dados montando uma constraint do tipo Vendedor CONTAINS {env::username}.

Experimente!

8.3 Segurança

Por fim, mas não menos importante, um metamodelo pode restringir o conteúdo de uma consulta em função das permissões de acesso – controle de segurança – dos dados.

Como sempre, a idéia é simples: definimos um filtro de dados no metamodelo que usa o nome do usuário e, eventualmente, seu papel, para montar um controle de exibição baseado no controle de acesso.

Como fazer:

  1. No Pentaho BA Server, usando o papel de administrador, registre todos seus usuários. Eu fiz isso com os usuários da Beltrano;
  2. Com Pentaho Metadata Editor edite o metamodelo e registre um filtro no nível do modelo de negócio, seção Data Constraints: qualquer consulta contra esse modelo de negócio vai embutir aquele filtro automaticamente. Salve e publique o metamodelo no servidor;

    Registrando filtro no PME.
    Registrando filtro no PME.
  3. No PRD basta remover qualquer filtro local. Por exemplo, eu removi o filtro de usuário “Alexandre”, deixando o relatório trazer dados de todos os vendedores, indistintamente. Salvei e publiquei o metamodelo.

Feito! :-)

Para testar, acessei a plataforma com duas contas: Fábio e Alexandre. Como o relatório consome metadados, e os metadados são completamente filtrados por meio do atributo Data Constraints no Business Model, os dados apresentados já estão filtrados.

Resultado do filtro por controle de segurança.
Resultado do filtro por controle de segurança.

8.4 Conclusão

“Nossa, Fábio! Que maravilha!”, dirão vocês, “e não tem nenhuma desvantagem?”

Sim, tem: justamente adicionar uma outra camada para manutenção. Se usamos apenas SQL, qualquer alteração no banco entra em efeito imediatamente para o relatório. Se usarmos um metamodelo vamos precisar atualizar a camada de metadados antes de poder sequer testar as mudanças no PRD. Fazer uma pequena alteração na formatação pode forçar o analista a quebrar o vínculo do campo com a metaconsulta, perdendo a vantagem do modelo centralizado, ou então obrigar a uma atualização do metamodelo, cascateando a publicação de uma nova versão.

Há uma segunda desvantagem, mas é mais sutil e, talvez, pior: aparentemente não há como fazer UNIONs! Isso não é um problema para modelos bem comportados, mas modelos como o do Beltrano – que nem é tão exótico assim – se quebram quando tentamos fazer certas combinações numa só consulta. Por exemplo, no modelo transacional, não é possível escrever um único SQL que traga todos os clientes PF e PJ. É preciso duas consultas, coladas a posteriori com UNION, para então chegar ao conjunto completo. E eu não achei como fazer isso com MQL.

Ou seja, as vantagens de uso de um metamodelo vêm com um preço: um processo de desenvolvimento com mais alguns passos. Se isso vai compensar no final é uma questão respondida projeto a projeto.

Até a próxima! :-)

Instalando A Base Beltrano S/A

Em breve publicarei um post sobre o uso de metamodelos como fontes de dados no PRD. O post de hoje é para ajudá-lo a se preparar, e não traz nada de novo: como instalar Postgres e as bases da Beltrano S/A.

Esse post é uma atualização das instruções de instalação da base de treinamento Beltrano S/A. Você pode seguir este link para conhecer a Beltrano e como ela foi pensada. O texto a seguir apareceu pela primeira no Capítulo 3 do livro Pentaho na Prática.

Instalando e Configurando um Postgres

Para usar as bases OLTP e DW da Beltrano você precisa ter um Postgres instalado e funcionando, bem como conhecer o usuário root e sua senha. Se você já tem um, clique aqui e pule para etapa seguinte. Caso contrário, siga os passos descritos nesta seção para instalar um Postgres em sua máquina.

Se você usa Windows, continue lendo. Se usa Linux, pule para a próxima seção.

Instalar PostgreSQL no Windows XP

As instruções de instalação no Windows estão disponíveis em um e-Book gratuito.

Capa do e-Book Instalando Postrgres 9.0 no Windows.
Capa do e-Book Instalando Postrgres 9.0 no Windows.

Você pode usar o Calibre ou qualquer outro programa leitor de e-Pubs para lê-lo.


Eu tinha uma licença do WindowsXP e resolvi aproveitá-la para fazer esse tutorial. Pelo mesmo motivo (falta de licença) não existe tutorial para Windows mais novos. Entretanto, a uniformidade entre as versões do Windows deve ser o bastante para que você possa seguir praticamente 100% do tutorial em qualquer versão mais recente do Windows.


Instalar PostgreSQL no Linux

Linux é um sistema operacional com n sabores – distribuições – e é virtualmente impossível cobrir todas. Por isso eu não fiz um livro mostrando como instalar Postgres em Linux, mas fiz para Windows. Vamos ver um passo-a-passo para Debian/Ubuntu. Se não for o suficiente, acesse a página de instalação do PostgreSQL para outros Linuxes.

Esse procedimento funciona em qualquer derivado Debian (como o Ubuntu). Existem opções gráficas, mas a linha de comando é universal e relativamente simples.

  1. Abra um terminal de comandos;
  2. Digite,seguido de \[ENTER\]:
    sudo apt-get install postgresql
    
  3. Se perguntado, responda Y ou S. Assim que a instalação acabar você terá um banco instalado, configurado e em operação, mas vazio;Em seguida, altere a senha do usuário-padrão, postgres, para algo fácil de lembrar (e potencialmente inseguro), como postgres. Faça assim:
  4. Entre na conta de usuário Postgres do sistema operacional (aquele que executa o programa do servidor:)
    sudo su – postgres
    

    Esse usuário do sistema operacional (postgres) tem permissão para acessar o servidor PostgreSQL diretamente. Ele é criado pelo processo de instalação do banco e recebe uma senha aleatória, que nunca é mostrada ao usuário final. Como não sabemos nem a senha do usuário nem a senha do root do banco, precisamos fazer login na conta de sistema operacional postgres com sudo. Você até pode modificar a senha desse usuário, mas não há razão para isso.

  5. Agora entre no programa de interface de linha de comando do PostgreSQL, o PSQL, digitando o comando:
    psql
    
  6. Deve aparecer um prompt, esperando comandos, parecido com isso:
    postgres=#
    

    Pronto, você está no prompt do PSQL. Agora altere a senha do usuário do banco (que é diferente do usuário do sistema operacional no qual o banco é executado):

    ALTER ROLE postgres with password 'postgres';
    

    ROLE é o usuário, e entre as aspas simples está a nova senha – postgres. Se deu tudo certo, deve aparece uma mensagem como:

    ALTER ROLE
    postgres=#
    
  7. Saia do PSQL, digitando \q seguido por \[ENTER\];
  8. Faça logout da conta postgres, digitando exit seguido por \[ENTER\].

Agora você tem um servidor PostgreSQL instalado e pronto para ser usado. Sempre que precisar acessá-lo para algo, comande:

    psql postgres -U postgres

Se ele pedir senha, ela será postgres.

Fazer o Postgres Aceitar Qualquer Conexão


Atenção! O procedimento abaixo vai abrir seu PostgreSQL para acesso por qualquer um na mesma rede em que você está! Lembre-se disso ao criar bancos e gravar dados nele. Essa configuração é útil para desenvolvimento, testes, treinamento, mas é perigosa para ambientes de produção ou no caso de dados sensíveis.


  1. Volte à conta do postgres. Em um terminal, entre novamente:
    sudo su – postgres
    
  2. Abra o arquivo de configuração pg_hba.conf para edição com seu editor de textos favorito. Nosso exemplo usa o nano:
    nano etc/postgresql/X.Y/main/pg_hba.conf
    
  3. Vá até o final do arquivo e insira a seguinte linha:
    host all all 0.0.0.0/0 md5
    

    Isso vai fazer com que o servidor aceite conexões de qualquer IP, desde que forneça usuário (postgres) e senha (postgres) para qualquer banco;

  4. Salve o arquivo (nano: CTRL+O) e depois saia do editor (nano: CTRL+X);
  5. Agora edite o arquivo de configuração do servidor postgres.conf:
    nano etc/postgresql/X.Y/main/postgresql.conf
    
  6. Role o arquivo até encontrar a linha com listen_addresses;
  7. Altere localhost para *. Isso vai fazer com que o servidor passe a ouvir conexões de qualquer IP;
  8. Salve o arquivo (nano: CTRL+O) e depois saia do editor (nano: CTRL+X);
  9. Re-inicie o servidor:
    /etc/init.d/postgresql restart
    
  10. Finalmente saia da conta de usuário postgres:
    exit
    

A partir de agora qualquer cliente PostgreSQL pode se conectar ao seu servidor.


Dica: se você for adotar o PostgreSQL como banco para sua empresa, contrate suporte especializado e treine pelo menos um DBA. Você faria isso com Oracle e MS SQL Server, porque vai deixar de fazer com PostgreSQL (ou MySQL)?


Instalando as Bases

Primeiro, instale as bases:

  1. Baixe-as destes links (clique nos nomes dos bancos:)
    1. Beltrano (OLTP) (arquivo beltrano_oltp_10k_pedidos.backup.zip;)
    2. Beltrano DW (arquivo beltrano_dw.backup.zip;)
    3. Beltrano Log (arquivo beltrano_log.backup.zip;)
  2. Descompacte-as em um diretório conhecido e sem espaços no path. Por exemplo, C:\ no Windows e /opt/beltrano no Linux.
  3. Se você usa Linux, não se esqueça de mudar as permissões dos arquivos (já descompactados) com um chmod 777 *.backup.Usando o PSQL (interface de linha de comando do Postgres), conecte-se ao servidor, crie um banco vazio para cada base e restaure-as neles:
  4. Rode o PSQL:
    1. Windows: rode o programa SQL Shell, que fica em Menu Iniciar -> Programas -> Posgtres <Versão> -> SQL Shell (psql);
    2. Linux: abra um terminal e comande psql -U postgres. Isso vai conectar você ao servidor localhost, porta 5432, banco postgres, com usuário postgres;
  5. Crie o novo banco de dados, comandando:
    1. Beltrano OLTP: CREATE DATABASE beltrano; (inclua o ponto-e-vírgula!);
    2. Beltrano DW: CREATE DATABASE beltrano_dw; (não se esqueça do ponto-e-vírgula!);
    3. Beltrano Log: CREATE DATABASE beltrano_log; (já sabe, não? Ponto-e-vírgula incluso!);
  6. Conecte-se a cada uma das bases recém-criadas e restaure-as. Ou seja, ainda no psql, comande (pressionando \[ENTER\] ao final de cada linha:)
    \c beltrano
    \i /opt/beltrano_oltp_10k_pedidos.backup
    \c beltrano_dw
    \i /opt/beltrano_dw.backup
    \c beltrano_log
    \i /opt/beltrano_log.backup
    

    Cada um destes comandos terá uma resposta. Os comandos \i efetivamente populam o banco, processando cada linha do script, e por isso resulta em uma lista maior de respostas – uma para cada comando do arquivo de backup.


Esse script é para Linux!


Windows: entre os mesmos comandos acima, mas use o caminho c:\, com barra contrária, ao referenciar o arquivo. Por exemplo:

  • \i c:\beltrano_oltp_10K_pedidos.backup
  • \i c:\beltrano_dw.backup
  • \i c:\beltrano_log.backup

Voilà! Bases instaladas e prontas para os exercícios. Você pode comprovar usando o pgAdmin para examinar os bancos e verificar o conteúdo das tabelas.

Bases restauradas (pgAdmin III do Windows.)
Bases restauradas (pgAdmin III do Windows.)

Todos os Caminhos Levam a um DW

No final de 2014 o artigo Monetizing Big Data: A Q&A with Wells Fargo’s Data Chief expunha a opinião de que DWs não são uma condição necessária para projetos de BI de sucesso. Concordo com ele em certos pontos, mas não de maneira geral. Eu não cheguei realmente a detectar uma tendência que defenda isso, mas vejo essa mesma opinião em fóruns e na propaganda de algumas ferramentas de visualização de dados.

O Prof. Goodnight, CEO do SAS, tem um excelente artigo no qual ele retruca a declaração acima. Leia ambos, são muito bons.

Assim como o Prof. Goodnight, eu também não acredito que DWs tenham chegado ao fim do seu ciclo de vida. Vou contar uma história, e no final eu volto para amarrar as pontas. Divirtam-se!

No Início Era o Excel

Para escrever meu livro Pentaho na Prática eu criei uma base de dados de treinamento chamada Beltrano S/A. Essa base reflete o sistema transacional da empresa fictícia chamada (adivinhe!!) Beltrano S/A, que é uma empresa de treinamentos.

Agora imagine que o Sr. Beltrano da Silva decidiu investir no crescimento da empresa, e se prepara para abrir filiais. Ele usa alguns sistemas transacionais para cuidar dos negócio da empresa, e começa a fazer perguntas. Cada pergunta que ele faz percorre um caminho dentro da empresa – ora pelo Financeiro, ora por Vendas, ora por Operações – mas sempre acaba caindo no mesmo lugar: a Informática, que é a responsável por manter os sistemas funcionando.

Porque lá, se eles não têm nada a ver com o negócio em si, mas apenas com as bases e os dados? É porque os “outros” empregados da Beltrano não sabem mexer nessas bases, ou simplesmente nem sabem que elas existem.

O Sr. Gerente da Informática recebe cada um desses pedidos e devolve arquivos com linhas e linhas de dados. Por exemplo, o Sr. Beltrano pediu ao Sr. Gerente de Vendas a lista dos 10 cursos mais rentáveis por região de São Paulo, e por Bairro. O Sr. de Vendas pediu ajuda ao Sr. da Informática, que fez uma consulta na base e trouxe a resposta para o ano passado, mês a mês, em um arquivo CSV. O Sr. de Vendas montou “um Excel” com esses dados, imprimiu e entregou na mão do Sr. Beltrano.

Invariavelmente e, para supremo desgosto do Sr. de Vendas, imediatamente, o Sr. Beltrano olha os papéis na mão do subordinado de relance e responde: “Jóia, e quebrando por X?” X pode ser qualquer coisa – cidade, bairro, curso, instrutor, estado etc. etc. etc.

Água Mole em Pedra Dura

Você conhece o final dessa história: de tanto vai e volta alguém decide que precisam achar uma forma melhor de fazer isso. Descobrem que o assunto responde pelo nome de “BI” e vão ao mercado buscar o que precisam.

Vem uma consultoria e diz que precisam de um projeto super-duper de DW. Caro demais.

Vem outra e diz que precisam de Data Mining, que depende de construir um DW antes. Caro demais ao quadrado!!

Outra empresa oferece as ferramentas de BI, poderosas, complexas e caras, mas nada de serviço. Caro demais e trabalhoso demais.

Finalmente uma das empresas mostra um produto interessante: caro, mas acessível, permite manusear facilmente os dados de qualquer base. “Precisa de DW, como o projeto de Data Mining?” Nããão, diz o fornecedor, é self-service (!), basta conectá-lo à base de dados e mandar ver! É fácil e simples!

Fechado! Na semana seguinte vem o CD. O Sr. da Informática instala a cópia no computador do Sr. Beltrano e configura para ler as bases.

O Paraíso na Terra…

Nos primeiros dias o Sr. Beltrano até se diverte, mas ele tem mais o que fazer e repassa o serviço para um dos membros da TI, o Sr. Analista. Esse novo usuário conhece as bases e sabe como extrair o que o dono precisa.

Funcionou! Um relatório, um gráfico, um painel, outro gráfico… Ficando mais complicado… Pegando mais dados…

Pá! O pessoal de vendas começa a reclamar que o sistema está lento. O pessoal do Financeiro começa a reclamar que o sistema está lento. A menina da portaria reclama que o sistema está lento! Sem dinheiro para mais computadores, o Sr. da Informática tem a idéia salvadora: gravar uma cópia dos bancos na máquina do Sr. Analista.

… Não Existe

De novo, funcionou no começo. O problema passou a ser outro: os dados ficavam defasados, porque a base inteira era muito grande para copiar, e não dava para sincronizar minuto a minuto.

Tudo bem, disse o Sr. Beltrano, não precisa minuto a minuto. Uma vez por dia basta.

Beleza: durante a noite, um processo automático copiava a base, que estava sem atividade àquela hora, do servidor para o computador do Sr. Analista.

Funcionou? Sim, claro.

O Que Foi Agora?!

O Sr. Beltrano não deixava o Sr. Analista em paz, e chegou o dia em que pediu um relatório comparando aqueles números com os do mês passado.

Fácil! A base da Beltrano já tem histórico: ela guarda todos os dados e pedidos e coisas que aconteceram na vida da Beltrano. Claro que, no começo, a base era diferente e por isso os dados de um ano atrás estavam em tabelas antigas, que não era mais atualizadas. Tudo: o pedido era só para um mês.

Funcionou, no começo.

A base guardava tudo que ela processava, mas a complexidade do modelo de dados, aliado à complexidade das perguntas de negócio fazia com que os cruzamentos – as consultas SQL escritas pelo Sr. Analista – crescessem em complexidade. A certa altura o Sr. Analista levava até dois dias escrevendo e testando e debugando as consultas.

O Sr. Beltrano disse ao Sr. Analista, suavemente, que precisa de um serviço mais rápido De qualquer *%$%@!$ de jeito!

Você Está de Brincadeira!

A idéia salvadora veio de Vendas: e se o Sr. Analista deixasse os dados pré-arranjados? Ele tem poder total sobre a cópia do banco dele, mesmo.

Boa idéia, pensou o Sr. Analista, e montou consultas que pré-combinavam os dados em novas tabelas, mais simples, sem relacionamentos com nenhuma outra e fáceis de se consultar.

Funcionou? Sim, claro, no começo… KKKKK

O sistema antigo estava chegando a seu limite e por isso o Sr. Beltrano comprou um ERP. Resultado? Ele ficou sem relatórios do novo sistema por quase um mês, até o Sr. Analista aprender o suficiente para repetir as mesmas consultas no novo sistema, mas ainda assim não era a mesma coisa. As tabelas eram diferentes, os dados guardados eram diferentes, organizados de maneira diferente.

O fato é que todo o trabalho do Sr. Analista foi congelado. O Sr. Analista voltou para a TI e contrataram um novo profissional, de BI, para ajudar o Sr. Beltrano. Mais um ou dois meses, e o Sr. Beltrano, agora com uma nova versão daquela ferramenta – lembram-se? – estava de volta ao jogo.

QUEREM ME ENLOUQUECER?!!

(Risos)

Bom, lembram do Sr. Analista? Que voltou para TI? Então, souberam disso. Fora da asa do Sr. Beltrano, o Sr. Analista virou presa fácil da empresa inteira: pediam relatórios para ele o tempo todo! Financeiro, Vendas, Operações, a moça da recepção, todo mundo conseguia pedir alguma coisa para ele, e o Sr. da Informática deixava.

E isso funcionava? Não, é óbvio!!! (kkkk)

O backlog do Sr. Analista crescia, as reclamações sobre o Sr. da Informática cresciam (ele era acusado de não deixar o Sr. Analista atender quem precisava de ajuda) e as reuniões de diretoria estavam virando um pesadelo de conciliação!

Vejam, o Sr. BI, auxiliar direto do Sr. Beltrano, montava gráficos e relatórios e tudo mais – e o Sr. Analista fazia exatamente a mesma coisa, mas para o restante da empresa inteira. Cada qual em cima de suas bases, com seus próprios critérios, a seu tempo.

Os números viviam a se explicar. Era quase um Inquérito Policial-Matemático.

Highlander na Parada

Os cérebros da empresa concluíram que a raiz do problema era o fato de cada um usar uma base diferente e critérios diferentes.

Reorganizaram a empresa: montaram o setor de BI, que cuidaria de uma base de dados única. O Sr. BI cuidaria dos relatórios e análises, e o Sr. Analista manteria e extenderia a base.

Ah, é! A esta altura, a base de dados corporativa era uma mixórdia de tabelas, scripts, comandos SQL, componentes de replicação blá blá blá. Os tempos de consulta estavam ficando maiores, e tabelas de apoio eram criadas quando necessário.

Os Portões do Inferno se Abrem

Enquanto na teoria o Departamento de TI rumava para o manso recanto da base corporativa e dos empregados dedicados, felizes e satisfeitos, na prática a tensão crescia: o backlog de demandas do Sr. BI (que agora acumulava o do Sr. Beltrano e o do restante da empresa) transbordava. As reclamações de prevaricação, justas e nem tão justas, abundavam. Volta e meia voava um dedo na cara de alguém. Horas extras foram jogadas na equação, mas resolveu pouco, porque o time de BI estava no limite da exaustão.

A solução é óbvia! Contrataram um ajudante para cada um, descartaram o antigo software do Sr. Beltrano, compraram um novo, com portal web e recursos de Business Analytics Self-Service Turbo Pro 2.0 etc. blá blá blá

Aqueles Que Desconhecem a História…

No final, o cenário na Beltrano era apocalíptico:

  • O ritmo de produção de análises e relatórios era lento…
  • … por que todas as demandas da empresa passavam por dois empregados;
  • Na tentativa de aliviar esse gargalo, dando autonomia aos usuários, mais software foi comprado…
  • … e a demanda em cima do departamento de BI aumentou: além de atender os usuários do backlog, tinham novos produtos para cuidar.
  • O processo de ingestão de dados na base corporativa não era automatizado…
  • … o que ajudava a aumentar a carga em cima do departamento de BI.
  • Os usuários continuavam tão insatisfeitos quanto antes…
  • … e a sensação era de que “o BI” só atrapalhava.

… Estão Condenados a Repeti-la

Aos poucos alguns usuários mais espertos começaram a descobrir como acessar os dados da empresa, e conseguiam exportá-los para um Excel. Ali eles podiam trabalhar em paz, longe da zoeira que era “o BI”, e resolver suas necessidades.

Estava tudo indo bem, mas aos poucos o trabalho para manter aquelas poucas planilhas foi aumentando, as poucas planilhas viraram muitas planilhas, foram ficando mais complexas… O diretor de Vendas, que era mais desesperado, usou a verba do departamento para comprar um daqueles produtos self-service, se ligando direto nos dados.

E a roda seguia girando…

Data Warehouse São Obsoletos?

É claro que essa história é só uma invenção da minha cabeça. Mas, guardadas as devidas proporções, eu vi isso acontecer na empresa em que trabalho, e em outras empresas, através de relatos de amigos e conhecidos. Gente se queimando com chefe, chefe se queimando com diretor, e assim sucessivamente, no meio de uma bagunça de ferramentas e conceitos, com cada solução criando novos problemas mas nem sempre resolvendo os antigos.

Olhando só o que aconteceu com os dados da Beltrano S/A, o que vemos na história?

  1. No começo é consulta direta (e usa-se Excel para análise dos dados);
  2. Os dados passam a ser replicados integralmente (explorados com uma ferramenta mais adequada;)
  3. A cópia em tempo real é pesada demais e um compromisso é atingido: a replicação passa a ser diária;
  4. A cópia não basta: a complexidade dos dados força à sua recombinação a priori, antes da exploração – os dados começam a ser transformados durante a carga;
  5. A demanda sobe, e o peso dos dados é aliviado com estruturas de apoio, como tabelas pré-agregadas e/ou normalizadas.

Neste ponto a ferramenta de consulta precisa estar à disposição da comunidade de não-técnicos que consomem aqueles dados. Isso causa um efeito em cascata de demanda, sofisticação crescente, dados de outras fontes começam a ser requeridos e por aí vamos.

Conclusão

Quem é do ramo sabe o que aconteceu nessa historinha: a Beltrano re-inventou a roda da bicicleta, um raio de cada vez. Eles partiram da idéia simplória de que os dados podem ser consumidos em seu estado bruto, para chegar na necessidade de centralização e organização dos dados. Caso contrário as contradições e falhas dos dados vão aparecer nos números construídos por cada usuário. No limite não haverão dois totais de venda do mês iguais na empresa inteira.

Um Armazém de Dados não é um projeto estéril, um capricho do povo de TI. É um “ser” que reflete a empresa e seu conhecimento, que muda e cresce continuamente. Um DW constrói e guarda a memória que a empresa, na figura de seus empregados, consulta para estabelecer relações, para responder perguntas, para tirar as dúvidas no momento de escolher a direção do próximo passo.

Por isso eu não acredito que DWs sejam obsoletos. Cada nova moda tecnológica traz promessas valiosas, mas somos nós, seres humanos, que realizamos essas promessas usando essas tecnologias. Por si só, elas são estéreis. Não existe mágica, só trabalho duro e recompensas ao final do dia.

A Opinião dos Fornecedores

Alguns fornecedores perceberam a armadilha em que se colocariam se defendessem a não-adoção de DWs. Por exemplo, este post do Tibco Spotfire de 2011 explica que, apesar de custar dinheiro, muito dinheiro, um DW é importante e dá boas explicações do porquê. Já este outro artigo, de uma empresa alemã de consultoria, oferece alguns comentários interessantes sobre DW, sua importância e a realidade típica de projetos de DW. Leia ambos, valem a pena.

Até a próxima!

Como um Data Vault Evolui

Ontem eu estava mexendo no meu projeto de DV de estimação (um aconselhamento que presto a um amigo) e descobri uma coisa: o satélite dos dados dos empregados tinha centenas de registros por empregado. Eu fui olhar de perto e descobri que não eram centenas, mas sim um por dia desde o início da captura dos dados. Para um DV, isso significa que a cada refresh (que é diário neste caso) o ETL está versionando o satélite de empregados. Como isso só aconteceria se todo dia houvesse alguma diferença para a última versão, eu decidi analisar os registros, comparando as versões de satélites para um empregado qualquer.

Eu vou mostrar algumas imagens para ilustrar o que eu fiz, mas saibam que todos os nomes foram mudados para coisas mais óbvias, e campos particulares (que possuíam detalhes da arquitetura do sistema) foram removidos. São ilustrações, não são o caso real, ok? Exceto por esse detalhe, todo o restante é a narração fiel do que eu fiz.

O satélite Empregado, mostrado na figura abaixo junto ao hub Empregado, tem cerca de 13 campos do sistema de origem, mais 4 de controle do DV.

Satélite empregado original, com todos os campos em uma única tabela.
Satélite empregado original, com todos os campos em uma única tabela.

Todos esses campos estão em uma única tabela no sistema de origem, de modo que a carga desse pedaço do modelo é feito por duas transformações: uma de hub e uma de satélite.

Eu descobri, depois de analisar o conteúdo do satélite, que três campos eram atualizados sempre que o empregado chegava para trabalhar e depois ia embora:

  • emp_ultimo_acesso
  • emp_ultimo_ip
  • emp_ultima_sessao

Na verdade, sempre que o empregado fazia login (e depois logoff) em qualquer computador na empresa, esses detalhes são atualizados. Resultado: todo dia eles estavam diferentes do dia anterior, a menos que o empregado tivesse faltado, ou o dia anterior fosse um feriado/fim-de-semana. Portanto, todo dia um novo satélite era carregado, e isso estava correto. O ETL estava fazendo o que havia sido programado para fazer.

Satélites Separados

Temos aqui uma situação clássica para Data Vault: o sistema de origem tem taxas de atualização diferente entre os atributos. Alguns são atualizados uma vez na vida e outra na morte, e outros são alterados todos os dias, quando não várias vezes por dia. Se capturarmos todos os registros na mesma tabela estaremos desperdiçando tempo e espaço em disco, tratando e gravando coisas duplicadas, desnecessariamente.

Neste caso optamos por quebrar um satélite em dois ou mais, em função de sua taxa de atualização: um satélite conterá apenas os três campos que mudam todos os dias, e o outro satélite ficará com os campos mais estáveis. Cada um terá sua própria transformação para carga. No final, nosso DV vai ficar assim:

Satélite Empregado, agora quebrado em duas tabelas em função da taxa de atualização.
Satélite Empregado, agora quebrado em duas tabelas em função da taxa de atualização.

Aplicando a Mudança

Antes havia um satélite (=uma tabela), com todos os campos, carregada por uma transformação. Depois teremos duas tabelas, cada qual com seu conjunto de campos, e cada qual carregada por sua transformação. Eis o passo-a-passo que eu passei para meu amigo aplicar em produção:

  1. Renomear o satélite original para s_empregado_antigo;
  2. Criar as duas novas tabelas, s_empregado e s_empregado_1;
  3. Popular cada uma com o histórico atual;
  4. Apagar o satélite original;
  5. Subir as duas novas transformações.

As novas tabelas foram criadas e populadas de uma só vez com um SELECT INTO (o DV está em Postgres, o que facilita tudo.) As transformações foram criadas semi-automaticamente (basta entrar os nomes das tabelas, campos e da transformação e um script parametrizado gera tudo sozinho.)

Pronto! Quinze minutos depois de diagnosticar o problema eu tinha desenhado a solução. Meu amigo avisou que aplicou e deu tudo certo e agora vai propagar a solução para outros satélites (ele tem vários desses com status instantâneo.)

(Bom, vá lá, eu demorei um pouco mais que quinze minutos porque deu um trabalhinho até eu acertar o SELECT – ele precisava manter o histórico, o que significa dois SELECT DISTINCTs, com ORDER BY etc. Mas agora eu já sei o macete, e da próxima vez vai ser só 15min mesmo!)

Outras Quebras

Outras opções de quebras para satélites são por sistema de origem, que dá a vantagem de integrar os dados já no DV, e por particionamento. Neste último podemos deslocar satélites estáveis para outras mídias (outros tablespaces), e continuar a carregar os novos em uma tabela menor.

O Que Kimball Faria?

Meu grande amigo Gurgel que me perdoe, mas eu não vou nem considerar essa situação na 3NF. Agora, como ficaria essa situação em um Modelo Dimensional?

Na verdade, ficaria muito bem, obrigado! Veja, Modelagem Dimensional é uma técnica resiliente e robusta, com muita flexibilidade. Teríamos praticamente a mesma abordagem: quebrar uma tabela (de dimensão) em duas, criar dois novos processos de cargas (duas novas transformações), e recriar a fato, agora com duas chaves no lugar de uma só.

Factível, viável e simples, sim. Interessante? Não tenho certeza: hoje esses dados não possuem nenhum interesse para o negócio do meu amigo. São dados que ele descartaria sem pensar duas vezes – e foi essa a primeira sugestão dele. Saber qual é a hora do último logoff de cada empregado não tem o menor impacto no rendimento da empresa, nem a menor relação com a produtividade desse empregado. É um dado pura e simplesmente inútil, e só entrou no satélite porque simplificamos o levantamento de requisitos ao mínimo essencial – o também clássico carrega tudo!.

O Que Linstedt Faria?

Tá bom, saber quando o empregado fez o último logoff do dia é inútil. Mas e saber o histórico completo de todos os logins e logoffs, interessa?

Veja, estamos falando de capturar todas as mudanças, até mesmo em tempo real se for necessário. Para conseguir isso, sem gastar muito, basta rodar apenas a transformação que carrega esse satélite (com um ajuste para a variável de tempo – detalhes que não vêm ao caso agora…) a cada hora ou minuto. Com algumas centenas de empregados esse processo seria muito leve.

No caso mais extremo, dá para agendar uma PROC no banco que faça isso, e descartar o PDI completamente, reduzindo ainda mais o impacto da captura de histórico.

Com esses dados, novas perguntas são possíveis:

  • Será que um empregado que entra e sai do sistema várias vezes produz menos?
  • Preciso me preocupar com isso?

Um modelo dimensional permite capturar isso se você quiser. Basta montar uma estrela só para isso, já que esse vai ser o processo de negócio em análise.

Agora, e aqui vem uma das coisas legais do DV, um Data Vault te permite capturar isso já integrado com os outros satélites, o hub e tudo o mais. Um bom desenhista dimensional sabe que a técnica do Kimball também te dá essa possibilidade. Mas quanto mais você extende seu DW Dimensional com esse tipo de recurso, mais chega perto de um modelo Data Vault! A quantidade de dimensões e inter-relações (o Bus Matriz) começa a crescer, e a gestão vai ficando cada vez mais difícil. Apesar de flexível, a Modelagem Dimensional não é pensada para acumular dados, mas para análisá-los e por isso, cedo ou tarde, se seu DW muda com frequẽncia, um DV vai se tornar uma alternativa interessante.

E então você vai testar, só para ver como seria. E, então, já será tarde demais. Você será fisgado, como eu fui.

Até a próxima! Fui! :-)

Gravar Fato com Dimension Lookup/Update

Inteligência e criatividade já foram definidas como misturar coisas com um propósito e atingir outro. Eu tive o melhor exemplo disso há pouco, durante uma aula.

Explicando a gravação de uma fato, e peculiariedades de cada tipo de fato (snapshot, acumulating etc.), ela me perguntou:

Ah, então eu posso gravar a fato com a Table Output, e se quiser atualizar uso um Dimension Lookup/Update?”

De cara eu não entendi a pergunta – “Comparar table output com dimension lookup/update? Mas não tem nada a ver…” – foi quando eu parei para pensar e vi que era não só uma pergunta lógica, como óbvia.

Sim, claro, o D L/U (Dimensio Lookup/Update) é um passo dedicado a gravar dimensões, mas a lógica dele se presta a qualquer gravação/atualização que envolva uma chave composta e “coisas” (métricas!) que variem (ou não.)

Eu resolvi fazer um teste. Essa é a transformação que grava a fato pedido da Beltrano S/A, tirada do meu livro Pentaho na Prática:

Transformação que carrega a fato, usando passo "normal" Insert/Update.
Transformação que carrega a fato, usando passo “normal” Insert/Update.

Eu então troquei o Update no final por um D L/U – e não mexi em mais nada:

Transformação que carrega a fato, usando passo D L/U.
Transformação que carrega a fato, usando passo D L/U.

E essa ficou sendo a configuração do passo (eu tratei o tipo de pagamento como uma degeneração, mas sem querer coloquei como métrica):

Configuração do passo D L/U para gravar uma fato.
Configuração do passo D L/U para gravar uma fato.

Resultado? Bom, depois que eu criei a fato com o botão de SQL do D L/U e carreguei, ela ficou assim:

Fato gravada pelo passo D L/U. Repare que todos campos de controle da dimensão foram chamados de fut_ext_x (futuras extensões), e que agora ela possui uma chave primária.
Fato gravada pelo passo D L/U. Repare que todos campos de controle da dimensão foram chamados de fut_exp_x (futuras extensões), e que agora ela possui uma chave primária.

Ou seja, é a fato, mas com os campos de controle da dimensão. Eu nomeie todos eles como futuras expansões (fut_exp). A chave delegada também está lá, mas agora na função de uma chave primária! Compare com a fato “normal”:

A tabela fato gravada com um passo normal. (As chaves zeradas são um bug da minha transformação, que está bagunçada (sabe como é, próxima versão do livro...)
A tabela fato gravada com um passo normal. (As chaves zeradas são um bug da minha transformação, que está bagunçada (sabe como é, próxima versão do livro…)

Os zeros nas chaves decorrem do fato de a minha transformação estar em mudança – por algum motivo eu não recuperei as chaves (pau do banco?) – mas normalmente dá certo. Por favor, releve como ruído no material do livro, que está sendo revisado.

Conclusão? Dá certo! (Claro que dá, mas… como é que eu nunca pensei nisso antes???) Será que é alguma vantagem usar o D L/U para isso? Será que simplifica alguma lógica? A ver!

Genial! :-)

Beltrano S/A: Modelos de Dados

Dando sequência à abertura do meu projeto de banco de dados para treinamento, Beltrano S/A na Open BI Solutions, acabei de incluir os três modelos de dados da Beltrano S/A:

  • beltrano_mer.architect: tem a base relacional, ou OLTP, que corresponde ao arquivo beltrano_10k_orders_oltp.zip;
  • beltrano_md_logico.architect: é o desenho lógico da base de dados dimensional (DW) e não corresponde a nenhuma base física;
  • beltrano_md_fisico.architect: é o desenho da base de dados dimensional (DW), com todos os detalhes da implementação no banco (é o layout físico do banco). Corresponde ao arquivo beltrano_10k_orders_dw.zip.

Acesse o projeto no SourceForge para baixar esses arquivos: http://bit.ly/GNGzoS. Imagens destes modelos aparecem no post Base de Treinamento Beltrano S/A.

Lembre-se que você pode também pode ter acesso ao repositório do projeto: consulte a seção Code do projeto.

Base de Treinamento Beltrano S/A

Meu projeto de soluções livres de BI com Pentaho, Open BI Solutions, acabou de receber mais um item: uma base de dados para treinamentos, a Beltrano S/A.

Baixar & Instalar

O pacote é composto por dois dumps PostgreSQL: uma base OLTP e uma base DW. Eles são dumps textuais, scripts SQL, porque assim são compatíveis com outras versões do Postgres e podem ser migrados para outros bancos mais facilmente – se alguém quiser montar a Beltrano em um MySQL, por exemplo. Baixe-os, descompacte-os (anote o diretório no qual deixou-os) e reserve.

Para restaurá-los você precisa ter um Postgres (9.x preferencialmente) instalado. Crie dois bancos vazios, com o nome que você quiser – eu sugiro beltrano_oltp e beltrano_dw. Como são scripts e não dumps binários, você precisa restaurá-los com o comando \i do psql. Siga as instruções neste post do site companheiro e se tiver dúvida poste um comentário aqui.

Essa era a última seção deste post, depois da história, depois da explicação, depois de tudo, que eu movi para cima em respeito ao seu tempo. Eu não me senti confortável em te obrigar a ler tuuuudo só para chegar ao motivo que o trouxe aqui em primeiro lugar.

Beltrano S/A

A Beltrano é uma empresa fictícia, que desenvolve e vende treinamentos.

Ela tem um catálogo de cursos, que são vendidos em turmas. Assim, quando um cliente compra algo, compra vagas em uma turma (e não vagas em um curso) que é de um determinado curso. Se um curso não tem uma turma aberta, então ele (o curso) não pode ser vendido.

Cada vaga (ou lote de vagas) é comprada por um cliente que pode ser PF ou PJ, e é vendida por um empregado da Beltrano. Cada vaga é comercializada por um preço do catálogo, e pode ter ou não um desconto. Cada curso tem um autor, registrado na tabela de empregados.

Modelo Transacional

Eu idealizei a Beltrano como uma empresa que, como tantas outras, cresceu usando uma aplicação doméstica (i.e. construída em casa) para atender suas necessidades. Estes casos, normalmente, contam com algum sistema que registra as vendas em tabelas mais ou menos bagunçadas, um aspecto que eu tentei embutir no banco. A figura a seguir é o diagrama de dados do “Beltrano ERP” (BERP, para os íntimos), desenhado no Power*Architect:

Modelo E-R da aplicação transacional.
Diagrama da aplicação transacional (OLTP).

As tabelas são mais ou menos auto-explicativas. Os clientes são registrados em uma estrutura um pouco mais complicada, com duas tabelas: cliente_pf e cliente_pj. Eles compartilham a mesma tabela de cidades, que por sua vez se liga a uma tabela de estados. Veja que o estado não é gravado na “ficha” do cliente (a respectiva tabela), apenas a cidade o é. Quem recupera o estado, para mostrar ao operador, é a aplicação. As tabelas de clientes têm ainda algumas diferenças entre si, como por exemplo as colunas cargo e contato, que só existem na tabela cliente_pj.

Modelo Dimensional

Eu apliquei a técnica de modelagem dimensional do Kimball e cheguei a duas estrelas: Pedidos e Vendas. Elas são o embrião do DW da Beltrano, o “BAD” (Beltrano Armazém de Dados, hehe).

Diagrama do Modelo Dimensional.
Diagrama do Modelo Dimensional.

Observe como a Dimensão Data cumpre mais de um papel na fato Pedidos. Depois repare que a fato Vendas é uma estrela semelhante à Pedidos, mas com um grão mais grosso:

  • Grão Pedidos: Custo da Vaga vs. Quantidade de Vagas vs. Desconto vs. Pedido vs. Cliente vs. Turma vs. Curso vs. Data Pedido vs. Data Turma vs. Tipo de Pagamento vs. Empregado
  • Grão Vendas: Total Gasto vs. Pedido vs. Cliente vs. Curso vs. Data Pedido vs. Tipo de Pagamento vs. Empregado

No fundo elas são redundantes, já que todas as perguntas que a fato Vendas responde também podem ser respondidas pela fato Pedidos. Lembre-se que esse banco é para treinamento e por isso há um pouco de forçação de barra.

Se você baixar qualquer um desses diagramas, vai ver que algumas tabelas e campos possuem comentários.

Tamanho & Carga de Dados

Minha meta é construir uma base de exemplo com um milhão de pedidos (me sinto o Dr. Evil…) Isso vai tomar um tempo danado processando, por isso eu comecei com um volume menor, beeem menor. Minha primeira versão tinha 100 pedidos, a segunda 1.000 e a atual tem 10.000 pedidos. Isso se traduz em mais ou menos 50.000 linhas na tabela pedidos_detalhes, e na mesma quantidade de fatos da tabela f_pedidos. Meu próximo passo será – adivinhem! – chegar em 100.000 pedidos.

Meu projeto para gerar um milhão de pedidos envolve montar um cluster de máquinas (eu tenho quatro computadores em casa) para processar tudo. A maior dificuldade, porém, não é máquina, mas sim providenciar um volume de nomes e endereços aleatórios tal que eu tenha um número razoável de clientes fazendo esses pedidos, ao longo de um tempo interessante. Cinquenta mil clientes PF e uns 5.000 PJs, ao longo de 10 anos é um número razoável, porque dá uma média de 1,8 pedidos por cliente, por ano. Isso dá quase dois cursos por ano, o que é algo bem verossímel.

Eu também quero balancear os pedidos. Por exemplo, quero que empresas sejam clientes dez vezes mais assíduos que PFs, e que as PFs não façam mais que 3 pedidos de uma vaga por ano. Coisas assim dificultam o projeto de carga, que é feito em PDI (figura abaixo.)

Job PDI que carrega base Beltrano OLTP.
Job PDI que carrega base Beltrano OLTP.

Isso vai acabar dando um outro post, sobre clusters com PDI, e depois, quem sabe, um sobre Hadoop.

História

Eu desenvolvi o primeiro material de Pentaho para um ciclo de oficinas em um CONISLI, em 2008. Foi uma correria, mas eu tirei lições importantes dessa experiência. Uma delas foi entender que as oficinas, para ser efetivas, teriam que ter uma mensagem clara. O assunto é muito extenso, e pode ser bem profundo também, e por isso eu precisava estabelecer um objetivo claro, com um perímetro bem delineado. No final eu concluí que eu queria que o aluno:

  • Ganhasse uma visão de ponta-a-ponta, que ele entendesse que poderia popular um DW, criar um relatório e explorar cubos OLAP com o Pentaho;
  • Aprendesse o básico: como instalar e rodar cada programa, como criar, salvar e rodar cada artefato;
  • Aprendesse as operações mais importantes de cada ferramenta: dimensionamento para ETL, grupos e gráficos nos relatórios, criar um cubo OLAP completo, fazer um metamodelo com qualquer base.

Acho essa que foi a tarefa mais difícil no desenvolvimento daquelas oficinas.

Eu não teria tempo para ensinar as coisas mais simples, em todos seus detalhes, e por isso eu apostei na idéia de que, ao focar em operações importantes, ainda que complexas, e levá-los passo-a-passo através das dificuldades, eles acabariam preenchendo as lacunas sozinhos. Deu certo, e no final eu aprendi quais pontos precisavam de mais explicação, e quais poderiam ser ignorados.

Para os exercícios práticos eu precisava de uma base de dados, que fosse simples mas completa e fácil de entender. Um amigo rascunhou um modelo de dados para mim, preencheu todas as tabelas com algumas linhas (tinha só 90 clientes e nem 1000 pedidos!) e me deu de presente. Bingo! A base era pequena, simples e completa – tão completa que nem tinha erros nos dados.

BI com Pentaho pela 4Linux

Pouco tempo depois a 4Linux me procurou, para me contratar para elaborar um treinamento para eles. A coisa ficou bem séria: não bastava mais tirar umas dúvidas, ou dar uns exemplos. Se eu assinasse o contrato, eu precisaria criar algo que garantisse o aprendizado dos clientes da 4Linux, além de uma apostila beeem caprichada. Eu fui em frente e assinei com eles. Quatro meses depois marcaram a primeira turma, e o material estava ainda pela metade. Eu tinha os slides, os dois DVDs com máquinas virtuais, exemplos e artefatos, mas a apostila ainda não estava pronta. Eu escrevia freneticamente à noite, mas não foi o bastante e assim eu comecei a primeira turma com o material ainda em produção. Faltava pouco, mas eu precisei trabalhar quase around the clock, indo dormir à uma, duas da manhã, para levantar às 6H00min e continuar. Como a turma leva duas semanas, no final-de-semana eu dei um sprint final e completei a apostila, de modo que no início da segunda semana os alunos receberam a primeira versão dela.

Depois que o curso acabou eu dormi as 4h mais tranquilas da minha vida (meu primeiro filho tinha uns seis meses, de modo que nunca dormia muito, mesmo – hehe.)

Pentaho na Prática

Quando o livro Pentaho na Prática se firmou na minha cabeça, eu vi que aquela base não seria o suficiente para minhas pretensões, e decidi que era hora de tirar o pó de um antigo projeto – a base de treinamento. E foi o que eu fiz: antes de começar a escrever o livro eu repensei toda aquela base e fiz meu dever de casa:

  • Defini meu caso de negócio: escolhi um ramo e um nome para empresa (mentira: o nome veio no fim, quase na hora de lançar o livro);
  • Criei um diagrama em Power*Architect, com os objetivos de simplicidade e um leve amadorismo em mente;
  • Desenhei as transformações que carregavam cada tabela (empregados, clientes, pedidos, produtos etc.);
  • Amarrei tudo em um job, parametrizado pelo número de pedidos;
  • Consegui uma lista de cidades e estados do Brasil;
  • E finalmente montei listas de dados de apoio, como nomes de pessoas, endereços etc.

Apesar de não ser uma parametrização muito firme, ela era o bastante para trabalhar. Primeiro montei uma base com 100 – cem cliente, cem pedidos etc. Vi que o conceito funcionava e ampliei minhas listas para 1000 pedidos. Nisso precisei criar nomes de cursos (não dava para gerar aleatoriamente, como eu fazia com os nomes de clientes e endereços), nomes de instrutores, montei um quadro de empregados para a empresa etc. etc. etc. (Nossa, estou abusando do et coetera… mas tinha detalhe pra chuchu; daria até para escrever um outro livro, pequeno, sobre a história desse banco – teve até mapa mental!!!)

Finalmente o processo começou a operar, mais ou menos bem. Demorava bastante para carregar porque eu iterei uma transformação pelo número de pedidos, e isso é um overhead e tanto – repetir mil vezes a mesma transformação, que até era pequena, consumia coisa de duas horas.

Beleza, quase lá! Mil pedidos (com 1000 clientes PF e 1000 PJ) era pouco. O legal seria ter 100.000 pedidos, pelo menos, ou um milhão – aí sim, seria do balacobaco! Fiz alguns ajustes: mudei o número de clientes PF para 5000 e mantive os PJs em 1000, pois eu queria que a base refletisse a idéia de que há mais pessoas que empresas. Incrementei as listas de nomes e endereços para poder gerar 6000 nomes e endereços únicos (ou a chave primária das tabelas de PF e PJ seria violada), sem precisar me preocupar em checar repetições (o que tornaria as transformações mais complicadas.)

Cometi só um erro: criei poucos cursos. Deveria ter criado uns 20, 30, mas eu esqueci desse detalhe e rodei a carga de 10.000 pedidos com uns 10 cursos. Eu poderia ter adicionado mais e reiniciado o processo, mas neste ponto o livro estava parado, esperando a base, que tinha levado dois fins-de-semana para completar. No primeiro deu pau ai pela metade e eu descobri alguns bugs, depois de vários pequenos detalhes que eu ainda ajustei, coisas que eu precisei mudar e por aí foi. No segundo final-de-semana eu botei meu micro para rodar na sexta-feira à noite, planejando desligá-lo na manhã do sábado. Veio o sábado, foi-se o sábado, começou o domingo… Bom, encurtando: deu pau algumas vezes e eu precisei restartar na mão, adaptando as transformações para começar do pedido seguinte ao último antes da interrupção anterior e teria sido uma perda muito grande de tempo reiniciar só para colocar mais cursos.

Finalmente, a base ficou pronta e pudemos acabar o livro. Ufa! Depois de todo esse trabalho, teria sido um desperdício jogar a Beltrano fora, ou mantê-la apenas para nós, ainda mais quando nós mesmos chegamos onde chegamos graças ao compartilhamento de idéias e softwares.

Resolvemos abrir, e o resto é história.

Até a próxima!