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!

Anúncios

7 comentários sobre “Base de Treinamento Beltrano S/A

    1. Muito obrigado, Marco! Eu concordo contigo: numa implementação real eu criaria uma dimensão (provavelmente uma junk) para ela, eventualmente coagulando outros atributos orfãos. No caso da Beltrano S/A eu procurei criar um modelo que trouxesse exemplos das possibilidades mais importantes da Modelagem Dimensional. Apesar de ter o número do pedido como uma dimensão degenerada, eu queria um outro exemplo, que oferecesse uma agregação maior que um pedido.

  1. Fábio, no seu modelo dimensional, sexo e estado do cliente estão na mesma dimensão (d_Cliente). Utilizando o saiku para montar o cubo, como vc faria para extrair quantidade de pedidos por estado e sexo do cliente, por exemplo?
    Meu modelo é parecido e a maioria das ferramentas que já testei, não permitem usar duas hierarquias de uma mesma dimensão em um mesmo cubo.

    Abraço,
    Camila

    1. Obrigado pela pergunta, Camila. De fato, a maioria das ferramentas limita o uso de duas hierarquias de uma mesma dimensão em um mesmo cubo. Curiosamente, e contra os meus instintos, o Pentaho Analyzer (o cliente OLAP da versão EE – pago) faz isso. Ele deixa ver duas hierarquias da mesma dimensão em eixos distintos da análise.

      Eu suspeito que isso tenha mais a ver com o Mondrian que com a ferramenta – o Analyzer não roda consultas MDX “puras”, como faz o jPivot e, suspeito, o Saiku. Porém, o Mondrian 4 (que pelo visto está atrasado, pois era esperado para Jan/2014) deve alterar isso, já que ele alivia a forma de declarar hierarquias.

      Agora, respondendo sua pergunta, eu provavelmente espetaria a dimensão Cliente duas vezes (dois Dimension Usages) com a mesma chave. Isso me permitiria cruzar duas hierarquias diferentes. Eu vou testar e posto aqui o resultado.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s