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á! ;-)

ERP BI Solutions

E esse é o mundo de hoje: quando você pensa em fazer algo, alguém já fez. Conheçam ERP BI Solutions, primo do OpenBI Solutions:

ERP BI Solutions provides business intelligence solutions for popular open source ERP systems including PostBooks and XTuple ERP. Solutions are designed using data warehousing best practices and are built on best-of-breed open source BI technology giving you cost effective, innovative business intelligence.

Assim como o OpenBI Solutions oferece soluções de BI para softwares comuns (como o atual Apache) e de treinamento (Beltrano S/A), o ERP BI Solutions oferece soluções de BI com Pentaho para ERPs Open Source. A última publicação é de janeiro de 2014 e atende aos ERPs PostBooks e XTuple. Imagino que a coisa ande devagar, pois mais difícil que criar esses projetos é mantê-los em par com os respectivos ERPs.

Reviewing Sergio Ramazzina’s New Book: Pentaho BA Cookbook

Sergio Ramazzina has published his new Packt work, Pentaho Business Analytics Cookbook. Happily, I was granted with a copy of it for reviewing. I am already reading it!

Pentaho BA Cookbook: A cookbook for fast and easy Business Analysis solutions
Pentaho BA Cookbook: A cookbook for fast and easy Business Analysis solutions

For those of you who don’t know him, Ramazzina is a seasoned BI professional with tons of experience on Pentaho – including authoring another best-selling Pentaho book, Pentaho Data Integration Kitchen.

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!