Business Intelligence Tutorial para Programadores

Por que eu devo ler este artigo: O tema � �til a todos que t�m a necessidade de implantar uma soluç�o de Business Intelligence e n�o t�m como dispender grandes quantias de dinheiro em licenças e servidores. A soluç�o apresentada � gratuita e escal�vel, entretanto os conceitos e as t�cnicas apresentadas podem ser aplicados em quaisquer ferramentas de BI.

Trabalhando com Business Intelligence

Business Intelligence se refere ao conjunto de conceitos, m�todos e recursos tecnol�gicos que habilitam a obtenç�o e distribuiç�o de informaç�es geradas a partir de dados operacionais e hist�ricos, visando proporcionar subs�dios a tomada de decis�es gerenciais.

Uma caracter�stica fundamental de um processo de BI � que nele os dados s�o copiados da base de dados transacional e de outras fontes, para a base de dados anal�tica, o que permite que as informaç�es sejam extra�das desta �ltima sem que a performance do sistema transacional seja prejudicada e da forma mais eficiente, eficaz e visualmente agrad�vel poss�vel, sempre focada na tomada de decis�o.

Os dados podem, na sua origem, estar estruturados ou n�o, ou seja, podem vir de um sistema de informaç�o com dados organizados e corretamente normalizados em tabelas e colunas, e podem ainda serem extra�dos de sites da internet como texto puro, �udio, v�deo ou outros formatos. Apesar disso, em ambas as situaç�es eles devem ser tratados e gravados em um formato que facilite a extraç�o de informaç�es e apoie as decis�es dos gestores.

Neste artigo ser�o apresentadas as t�cnicas b�sicas envolvidas na criaç�o de um data warehouse, na modelagem da base multidimensional, no processo de ETL, na modelagem e publicaç�o da parte l�gica do cubo (metadados) e, por fim, na visualizaç�o dos dados em uma ferramenta OLAP.

Ao longo da d�cada de 90 vivemos a descoberta da internet, ocasi�o em que a rede mundial de computadores revelou-se um �timo mercado, surgindo a partir da� o e-commerce, os portais de not�cias, de m�sicas e, o que vem revolucionado o mundo, as redes sociais.

Concomitante � corrida pela visibilidade na internet, com o custo do hardware cada vez menor, as empresas investiram em seus sistemas de informaç�o, que se tornaram imprescind�veis. Desde panificadoras at� a montagem robotizada de ve�culos automotivos, todos necessitam de um software especializado para gerir as etapas de seus processos.

Entretanto, seja pela dificuldade enfrentada pelas empresas em criar software sob medida para suas necessidades, seja pela complexidade envolvida em manter uma equipe interna de TIC (Tecnologia da Informaç�o e Comunicaç�o), seja pelos custos elevados da terceirizaç�o desse desenvolvimento, a maioria adquire v�rios produtos de terceiros para gerir diversas �reas da empresa como o RH, Financeiro, Gest�o de Projetos, Gest�o de Clientes, Ensino � Dist�ncia, entre outros, o que acarreta em dados redundantes, descentralizados e em Sistemas Gerenciadores de Bancos de Dados Relacionais (SGBDRs) distintos, ao inv�s de serem armazenados em uma �nica base de dados, corporativa, normalizada e �ntegra.

Diante da situaç�o criada pela descentralizaç�o e heterogeneidade dos dados, o grande desafio do momento � integr�-los, interpret�-los e transform�-los, de alguma forma, em informaç�o relevante ao seu neg�cio, possibilitando, com a devida an�lise, a criaç�o de conhecimento.

O conhecimento pode, muitas vezes, ser o diferencial de uma empresa, pois possibilita ressaltar os seus pontos fortes e mitigar os riscos envolvidos nos pontos fracos. Tendo um retrato fiel da realidade, uma empresa consegue, em muitos casos, com base nos dados hist�ricos, fazer uma previs�o bastante assertiva do futuro e utiliz�-la como embasamento para suas decis�es.

Com base nisso, nesta primeira parte da s�rie ser�o considerados os conceitos mais relevantes da Business Intelligence (BI), seguidos da apresentaç�o da su�te Pentaho, capaz de contemplar no case proposto, todos os requisitos e regras de neg�cio elencados por uma empresa fict�cia, com diversas filiais. Analisando a base origem e levando em conta os requisitos e regras de neg�cio, ser� descrito o racioc�nio necess�rio para a modelagem da base destino, em formato estrela. Por fim, dando in�cio ao tutorial propriamente dito, tem-se o passo a passo para a instalaç�o e configuraç�o do Pentaho Data Integration.

BI n�o � um bicho de sete cabeças

O termo Business Intelligence provoca arrepios em muita gente. Atualmente, afirma-se que as grandes vendedoras de soluç�es propriet�rias e seus especialistas pintam um �bicho de sete cabeças� para justificar as altas cifras envolvidas. Esta � uma atividade altamente especializada e exige, em suas diversas etapas, profissionais treinados com uma gama muito grande de conhecimentos. Entretanto, o processo de BI propriamente dito � bastante simples.

Em 1992, o Gartner Group definiu Business Intelligence como o �conjunto de conceitos, m�todos e recursos tecnol�gicos que habilitam a obtenç�o e distribuiç�o de informaç�es geradas a partir de dados operacionais e hist�ricos, visando proporcionar subs�dios a tomada de decis�es gerenciais�. O termo pode ser traduzido como intelig�ncia empresarial ou intelig�ncia de neg�cios.

Gartner Group

O Gartner � uma empresa de consultoria fundada em 1979, por Gideon Gartner, com sede nos Estados Unidos, em Stamford, Connecticut. Atualmente conta com 5.300 associados, incluindo 1.280 consultores e analistas. Refer�ncia por ser formadora de opini�o, trabalha em pesquisas de mercado e vende seus relat�rios com incr�vel valor agregado para empresas privadas e para o governo de 85 pa�ses.

Base de dados OLTP � Transacional

A maioria dos sistemas de informaç�o se enquadra na categoria dos Online Transaction Processing(OLTP), tamb�m chamada de processamento de transaç�es em tempo real. Um sistema deste tipo exige uma base de dados modelada para otimizar a inclus�o e alteraç�o de dados, obedecendo a padr�es r�gidos de normalizaç�o, evitando redund�ncias, permitindo a integridade referencial e outras consist�ncias.

Base de dados OLAP � Anal�tica

As soluç�es para an�lise de grandes volumes de dados, sob diversas perspectivas, exigem uma base de dados multidimensional, chamada de Online Analytical Processing (OLAP), modelada para otimizar a extraç�o de informaç�es e normalmente armazenada em servidores diferentes dos utilizados pela aplicaç�o OLTP.

A Figura 1 ilustra as etapas de um ciclo de BI, que � repetido com certa periodicidade. Como pode ser observado, os dados seguem da sua origem at� o seu destino e fornecer�o insumos para as sa�das aos usu�rios finais. A primeira etapa deste ciclo consiste na captaç�o dos dados, oriundos de diversas fontes e em distintos formatos. Na maioria dos casos, no entanto, os dados s�o lidos da base transacional, OLTP.

Figura 1. Ciclo peri�dico de uma aplicaç�o de Business Intelligence.

A segunda etapa � a Extract, Transform and Load, que consiste em ler os dados, fazer as adequaç�es para torn�-los de f�cil interpretaç�o e pr�-calcular os totalizadores desejados, gravando-os no Data Warehouse.

Extract, Transform and Load � ETL

O processo de ETL � dividido em tr�s etapas:

Extraction(extraç�o): Consiste na leitura dos dados a partir de uma ou mais fontes, como, na obtenç�o de todos os registros adicionados ou alterados depois do �ltimo processo de ETL executado, na leitura de p�ginas da web, de web services, xml, xls, csv, json, al�m de formatos Big Data, como Hadoop, Cassandra, MongoDB e HBase.
Transformation (transformaç�o): Consiste na alteraç�o da forma ou do conte�do do dado, para que se encaixe a estrutura da base destino. A transformaç�o ocorre, por exemplo, ao se fazer um lookup pelo nome da cidade para armazenar o seu c�digo, ao ajustar o nome do bairro a partir do seu CEP, ao invalidar um registro por ter CPF ou e-mail inv�lidos, ao fazer decodificaç�es, renomeaç�es, c�lculos, agregaç�es, etc.
Load (carga): Consiste na gravaç�o dos dados em seu destino, independente do seu formato, que pode ser em um arquivo, em uma tabela da base transacional, em uma dimens�o ou tabela fato de um cubo ou em formato Big Data.

A ETL � uma das etapas do processo de Business Intelligenceque visa a criaç�o de um grande armaz�m para os dados (Data Warehouse). Para que o processo de Business Intelligence se tornasse vi�vel, foi necess�rio o desenvolvimento de ferramentas especialistas, capazes de executar todas as tarefas exigidas pela atividade. Devido � grande facilidade proporcionada, a adoç�o destas ferramentas para outros processos, como a migraç�o e a sincronizaç�o de dados entre sistemas, passou a ser apenas uma quest�o de tempo. Hoje em dia essas ferramentas s�o utilizadas para atividades como garimpagem de dados, leitura e an�lise de conte�do das redes sociais e bolsas de valores, envio de e-mail marketing, etc.

A terceira e �ltima etapa do ciclo de BI � a da sa�da dos dados, que pode acontecer em diversos formatos. Os relat�rios, o formato mais usual, t�m layout pr�-definido, aceitam uma gama de filtros e geram documentos em pdf, xls, etc. Outra forma de sa�da de dados s�o os dashboards, que t�m a finalidade principal de permitir o acompanhamento de indicadores em tela, de forma gr�fica e interativa. J� as an�lises s�o feitas por meio de uma ferramenta de navegaç�o OLAP, pelo pr�prio usu�rio final, que pode manipular os cubos criados, al�m de salvar e compartilhar suas consultas anal�ticas. Outras formas usuais de sa�da s�o os E-mails e Posts, que podem ser disparados para sinalizar a ocorr�ncia de um evento ou para alertar que determinado indicador atingiu um n�vel cr�tico.

Normalmente o processo de ETL se repete uma vez ao dia, de madrugada, refletindo todas as alteraç�es do dia anterior, mas a periodicidade deve ser ajustada de acordo com a necessidade, em cada situaç�o.

Pentaho Business Analytics

Com essa enorme demanda, muitas soluç�es de BI foram criadas. Inicialmente o foco das empresas desenvolvedoras era apenas em grandes mercados e, por isso, cobravam valores estratosf�ricos pelos softwares, consultorias e claro, pelo hardware, que ainda hoje, n�o raro, vem embutido na maioria das propostas.

O advento do software livre possibilitou que �timas alternativas �s soluç�es propriet�rias fossem criadas. Em 2004, na Fl�rida, Estados Unidos, formou-se uma equipe de executivos de grande experi�ncia em BI que analisou diversas soluç�es Open Source do mercado, selecionando as mais interessantes em cada especialidade. A equipe, que foi acrescida dos principais l�deres dos projetos escolhidos, fez algumas pequenas adaptaç�es para que as suas ferramentas fossem todas compat�veis entre si, criando assim a su�te de aplicativos Pentaho Business Analytics. Esta su�te segue as pol�ticas de desenvolvimento, distribuiç�o e suporte dos softwares open source, que s�o flex�veis, independem de sistema operacional e de fornecedores, o que garante alta confiabilidade, segurança e escalabilidade. Tamb�m por isso, o c�digo fonte, baseado em padr�es do mercado (J2EE e AJAX), � aberto e liberado para distribuiç�o e modificaç�o sem qualquer custo de licenciamento.

A vers�o 4.8 do Pentaho, lançada em novembro de 2012, conta com uma das principais novidades dos �ltimos anos, o Pentaho Marketplace, que � um reposit�rio que permite a instalaç�o, atualizaç�o e remoç�o de plugins, pela interface web.

Outro ponto positivo do Pentaho � que muitas pessoas est�o envolvidas nas melhorias acrescentadas �s ferramentas da su�te. Uma delas merece especial destaque, o portugu�s Pedro Alves. Ele � o respons�vel pela criaç�o das ferramentas C, batizadas de C*Tools, todas dispon�veis no Pentaho Marketplace, que englobam diversas ferramentas extremamente �teis, como o Community Dashboard Framework (CDF), Community Dashboard Editor (CDE), Community Data Access (CDA), Community Cluster Cache (CCC), entre outras.

Cabe destacar tamb�m o plugin Saiku Analytics, que ser� demonstrado nesta s�rie e que traz um front end em jQuery que permite a criaç�o de an�lises OLAP com grande facilidade, por meio de recursos de drag and drop.

� importante ressaltar que o Pentaho, apesar de ser um software livre, conta tamb�m com uma vers�o comercial, com o nome de Pentaho Enterprise Edition. Basicamente o software � o mesmo, mas acrescenta alguns recursos que permitem maior facilidade na sua configuraç�o, gest�o e an�lise de dados, al�m do suporte t�cnico oferecido. Esta vers�o tem um modelo semelhante aos outros distribuidores de BI, que levam em conta o n�mero de servidores, processadores e n�cleos, mas com valores irrelevantes quando comparados. A vers�o livre, chamada de Pentaho Community Edition � suficiente para se iniciar um projeto e, caso haja a necessidade, a vers�o paga pode ser contratada a qualquer momento, sem a necessidade de ajustes nas soluç�es j� criadas.

A parte servidora da su�te � formada por dois serviços web. O primeiro � o BI-Server, executado no servidor Tomcat. Al�m de se encarregar de executar todas as ETLs, possui uma interface web para disponibilizar ao usu�rio final as soluç�es criadas, chamada de Pentaho User Console (PUC). O segundo serviço � o da interface de administraç�o, executado no servidor light de aplicaç�es Jetty, e chamado de Pentaho Administration Console (PAC). � neste serviço que os usu�rios, seus grupos, conex�es JNDI e agendamentos de ETLs s�o mantidos e os caches dos diversos componentes podem ser limpos.

Com os conceitos apresentados, t�m-se subs�dios para iniciar o desenvolvimento de uma aplicaç�o anal�tica, utilizando software livre. A primeira etapa de qualquer projeto de TIC � o levantamento de requisitos e de regras de neg�cio, que � fundamental para delimitar o que se espera como resultado deste trabalho e como ele ser� validado. Com projetos de Business Intelligence n�o � diferente. Para exemplificar as etapas do processo de desenvolvimento de BI, a seguir apresentam-se os requisitos e regras de neg�cio do case proposto para esta s�rie de artigos.

Requisitos e regras de neg�cio do case proposto

Para apresentar os conceitos relevantes para esta s�rie de artigos, bem como para exemplificar o uso de algumas das ferramentas da su�te Pentaho Business Analytics, suponha que o diretor da Magazine Setorial, uma grande empresa fict�cia de e-commerce, contrata os serviços de Business Intelligence de uma empresa especializada e explica, nos itens a seguir, as caracter�sticas e necessidades do projeto:

A empresa tem a necessidade de acompanhar a evoluç�o de certos indicadores de venda, sem degradar o desempenho do sistema transacional, que gerencia as vendas efetuadas pelo site e por todas as filiais do Brasil;
A visualizaç�o dos dados deve ser poss�vel pela internet, por meio de computadores, tablets ou celulares;
A soluç�o deve ser criada sem custo algum com software;
A soluç�o deve rodar em servidores com qualquer Sistema Operacional;
A soluç�o deve ser funcional, mesmo em hardware com configuraç�es �discretas�;
A soluç�o deve permitir computar as vendas efetuadas at� o dia anterior, medindo quantidade, valor de compra, valor de venda e valor do lucro totalizados por ano, trimestre, m�s ou dia da venda;
A soluç�o deve permitir totalizar os valores, em qualquer ordem ou combinaç�o, pelos seguintes atributos: filial, vendedor, cliente, nota fiscal, produto e categoria do produto;
Um vendedor, ao executar uma venda, estar� sempre atrelado a uma filial, mas pode mudar a filial em que trabalha a qualquer momento.

Normalmente os requisitos de um case real s�o repassados ao analista de BI de forma sucinta, tal como foram, propositadamente, descritos neste case. A an�lise dos requisitos � fundamental para que as perguntas corretas sejam formuladas e respondidas, ainda que mentalmente, para que ent�o a soluç�o seja modelada.

Na Figura 2 � apresentado o Modelo de Entidade-Relacionamento (MER ou ER) da base que armazena os dados do sistema transacional, em MySQL. Esta estrutura armazena dados referentes �s filiais e seus vendedores, al�m dos dados dos clientes e suas notas fiscais, com os produtos de cada compra. Percebe-se, al�m disso, que os produtos s�o sempre de uma categoria (g�nero ou tipo) de produto, pois a tabela de produtos tem uma chave estrangeira apontando para a de categoria de produtos, sem aceitar nulos.

Figura 2. Modelo de ER da base transacional � OLTP.

Base de dados multidimensional � OLAP

Ap�s a an�lise da base origem do processo de ETL e o confronto com os requisitos elencados, percebe-se que a criaç�o de um cubo para totalizar os dados com toda a flexibilidade solicitada ser� a melhor alternativa, uma vez que esta soluç�o contempla todos os requisitos e o usu�rio ter� todos os resultados em uma �nica tela, com uma ferramenta OLAP.

Para armazenar os dados do cubo, uma base multidimensional � necess�ria. Muito embora existam mitos sobre a dificuldade na modelagem de dados multidimensional, o conceito � simples. Independente das diferenças doutrin�rias de Ralph Kimball e William Inmon, dois dos precursores do BI, entende-se que a maioria das necessidades apresentadas pelos usu�rios s�o contempladas por meio do esquema estrela (star schema), defendido por Kimball e que consiste em uma �tabela fato� e suas v�rias �dimens�es�. A outra vertente, �til em muitos casos e defendida por Inmon, prega o modelo floco de neve (snow flake), onde modelagens mais complexas (com maior normalizaç�o) s�o utilizadas para armazenar os dados.

Este case adota o esquema estrela que, tal como salientado, � utilizado na grande maioria dos casos e atende a todos os requisitos elencados aqui. Para tanto, abordar-se-� a seguir alguns conceitos importantes.

Um data warehouse pode ter um ou mais data marts, que seriam um conjunto de tabelas que armazenam os cubos multidimensionais de um mesmo assunto, como por exemplo, �vendas�. Cada dimens�o permite analisar os fatos por uma determinada vis�o, seja por �filial�, �categoria de produto�, �data da venda�, etc.

As dimens�es podem ser basicamente de dois tipos, Simples e SCD. No primeiro tipo, o processo de ETL n�o versiona os dados na base anal�tica. Isso significa que, por exemplo, caso o telefone de um cliente fosse alterado na base transacional, o telefone antigo seria sobrescrito na dimens�o de clientes no processo de ETL. Este comportamento para o caso do telefone � perfeito, mas para analisar informaç�es temporais, n�o.

Imagine que para o seu neg�cio, a UF em que o cliente mora � de suma import�ncia e um determinado cliente se mudou para outro estado. Todo o hist�rico dele, quando ele morava no primeiro endereço, � extremamente relevante e por isso n�o deve ser perdido, ou seja, quando analisados os dados das vendas efetuadas na �poca em que ele morava no primeiro endereço, estes devem ser computados nas estat�sticas da primeira UF, e os dados das vendas efetuadas depois da mudança devem entrar nas estat�sticas da nova UF.

Para resolver esse problema existem as dimens�es do tipo Slowly Changing Dimension (SCD), que guardam o hist�rico dos dados caso tenham seu valor alterado. O hist�rico pode ser criado de algumas formas, mas a principal � versionando o registro. Isto quer dizer que cada registro tem datas de in�cio e fim da sua vig�ncia, e uma coluna para indicar o n�mero da vers�o. Deste modo, apenas um registro ser� vigente em um determinado momento. No exemplo do telefone alterado, o registro teria sua data de fim de vig�ncia alterada do valor default, que � �01/01/2199�, para a data e hora atuais e seria inclu�do um novo registro vigente com o n�mero da vers�o acrescido em um. Muitos autores dizem que esta caracter�stica � um divisor de �guas entre as �verdadeiras� ferramentas de BI e as �falsas�. Isto porque, muitas soluç�es propriet�rias de relativo sucesso no mercado n�o oferecem esta funcionalidade, limitando-se apenas a oferecer os dados atuais, desprezando toda a riqueza que o hist�rico tem a oferecer.

Dando continuidade aos importantes conceitos, a chave prim�ria que as dimens�es recebem � chamada de Surrogate Key ou �chave substituta�, da� o prefixo �SK_� em seus nomes. Outra nomenclatura utilizada � Technical Key, com o prefixo �TK_�.

Como boa pr�tica, o primeiro registro de toda dimens�o deve ter em seus atributos valores nulos. Isso porque, este registro ser� utilizado nos casos em que a ETL da tabela fato n�o encontra, nas dimens�es, o registro procurado. Dessa forma, a tabela fato sempre apontar� para um registro da dimens�o, mesmo que com valores nulos, garantindo a integridade relacional e deixando evidenciado que aquela informaç�o n�o foi encontrada. Isto permite que o processo de ETL, em um caso de n�o conformidade dos dados, siga sua execuç�o normalmente, sem disparar erro algum.

Outra boa pr�tica � utilizar colunas de auditoria nas dimens�es, como a data de inserç�o e a data de alteraç�o do registo. Mas evite criar estas colunas com valores default no banco de dados, pois assim, todo o controle fica com o Pentaho e o comportamento das tabelas � sempre o mesmo, independente do SGBDR envolvido.

A partir destes conceitos, t�m-se subs�dios para iniciar efetivamente a modelagem da base de dados anal�tica. Como primeira tarefa, devem-se verificar os dados da base origem e definir qual � o fato que ser� analisado a partir do cubo criado. Neste case s�o as vendas de produtos. Isto j� define a origem dos dados da tabela fato e, pela padronizaç�o sugerida neste artigo, o seu nome. Como os dados vir�o da tabela �notafiscal_produto�, que cont�m uma linha por produto vendido, a tabela Destino ser� chamada de �ft_notafiscal_produto�, que teve seu nome formado pelo prefixo �ft_� e pelo nome da tabela origem.

Analisando os requisitos, percebe-se que o de n�mero 7 orienta a definiç�o de algumas dimens�es, sendo, a princ�pio, uma para cada conceito tratado (filial, vendedor, cliente, nota fiscal, produto e categoria do produto), enquanto o requisito de n�mero 8 diz que filial e vendedor podem ser armazenadas em uma mesma dimens�o, explicando que um vendedor sempre estar� ligado a uma filial, e ter� o nome �dim_vendedor�, obtido concatenando o prefixo �dim_� com o nome da tabela origem. Como um vendedor pode mudar de filial, esta dimens�o deve guardar o hist�rico dos seus dados.

Com o objetivo de facilitar a venda, bem como a gest�o dos produtos, estes s�o agrupados conforme o g�nero, de modo que um produto como o �Ipad� est� atrelado � categoria �Eletr�nicos�, simulando a experi�ncia de estar em uma loja com diversos corredores. Neste contexto, um produto raramente muda de categoria e, em ocorrendo tal hip�tese, pode-se simplesmente sobrescrever, na dimens�o do produto, o valor antigo da categoria, mantendo o novo valor. Assim, a dimens�o �dim_produto� deve ter tamb�m as informaç�es da categoria do produto, al�m das informaç�es referentes ao produto, e n�o precisa das colunas para o versionamento (n�mero de vers�o e datas de vig�ncia do registro).

Na pr�tica, o que diferencia o tipo da dimens�o � a presença ou aus�ncia dessas colunas, pois nenhuma alteraç�o na nomenclatura das tabelas � recomendada para indicar se a dimens�o � uma SCD ou Simples. Isto porque, a qualquer momento pode-se optar por versionar ou deixar de versionar os registros de uma tabela, e a nomenclatura pode se tornar um obst�culo consider�vel, pois com o nome da tabela alterado, os metadados do cubo deveriam tamb�m refletir esta alteraç�o e ser republicados.

Ainda analisando os produtos, uma caracter�stica que merece especial atenç�o � a de que os valores de compra e venda tamb�m v�o para a tabela fato. Isto porque um servidor OLAP tem a incumb�ncia de traduzir as consultas feitas por meio de queries multidimensionais (MDX) em simples queries SQL para obter os valores das suas diversas Medidas. Estes c�lculos s�o sempre feitos levando em conta os dados da tabela fato de cada cubo, por meio da utilizaç�o de agregadores como soma, m�dia, quantidade total, valor m�ximo e valor m�nimo, etc. Entretanto, nada impede que os valores tamb�m sejam armazenados e versionados na �dim_produto�. Apesar disso, estes valores seriam apenas ilustrativos e n�o seriam utilizados nos c�lculos.

Para que sejam poss�veis an�lises levando em conta o cliente, a dimens�o �dim_cliente� � fundamental e ter� os atributos para versionamento, pois neste caso as informaç�es s�o todas relevantes ao neg�cio. Outra dimens�o necess�ria e presente em praticamente todas as aplicaç�es de BI � a dimens�o tempo, aqui chamada de �dim_data�.

Neste case ser�o computados dados com as granularidades de Ano, Trimestre, M�s e Dia, de acordo com os requisitos, mas poder�amos ter uma dimens�o para as horas e minutos, por exemplo, o que permitiria an�lises sobre o hor�rio em que cada compra foi efetuada. A dimens�o �dim_data� ter� um registro para cada dia e ter� atributos para representar tamb�m o ano, trimestre e m�s referentes ao dia em quest�o, sem a necessidade dos atributos de versionamento. Por exemplo, o registro do dia �01/01/2008� tem o atributo dsAno com o valor �2008�, o atributo dsMes com valor �Janeiro� e nrTrimestre com valor igual a �1�.

O processo com a finalidade de popular esta dimens�o com seus dados deve gerar registros suficientes que abranjam as datas em que os fatos analisados no case ocorreram ou ocorrer�o. Por exemplo, desde 01/01/2000 at� 01/01/2020. Armazenamos dias suficientes para que a aplicaç�o n�o exija a geraç�o destes registros em um curto espaço de tempo. Datas mais avançadas tamb�m ser�o �teis para an�lises preditivas e por este motivo deve-se gerar linhas suficientes para se contemplar esta an�lise.

O modelo de dados multidimensional apresentado na Figura 3 segue o modelo estrela, defendido por Kimball, e ser� a base Destino do processo de ETL, criando assim o Data mart de Vendas. Na hip�tese deste case ser proposto a diversos profissionais da �rea de Business Intelligence, as bases de dados por eles modeladas n�o seriam muito diferentes desta figura.

Figura 3. Modelo de ER da base multidimensional, esquema estrela.

Com relaç�o � performance do banco de dados, para este case foi selecionado o SGBDR MySQL com a engine MyIsam, que � otimizada para a leitura de dados. As Foreign Keys representadas na Figura 3 n�o ser�o criadas efetivamente, pois a engine MyIsam n�o conta com este recurso. Em outros SGBDRs, no entanto, a utilizaç�o das chaves estrangerias de integridade referencial � recomendada.

MySQL Engines Vs Bancos colunares

No que tange � performance na leitura de dados, alguns autores defendem que a engine InnoDB j� atingiu maturidade suficiente para competir com a engine MyIsam, enquanto outros autores, visando melhor performance nesta leitura, defendem a utilizaç�o do MySQL com a engine MariaDb, ou ent�o a utilizaç�o de bancos de dados colunares como Cassandra ou MongoDB. Entretanto, este assunto exige um novo estudo e foge do escopo deste artigo.

Saiba mais: Curso de MongoDB

Cumpre registrar que est�o dispon�veis para download, no site da SQL Magazine, as bases de dados Origem e Destino, os arquivos do PDI capazes de executar toda a ETL e o arquivo com os metadados do cubo de vendas.

Assim, para dar in�cio ao processo de ETL descrito a seguir, baixe os arquivos fornecidos e execute os dois arquivos SQL em uma ferramenta cliente do MySQL. O arquivo DumpMagazineSetorial.sql cria a base origem, j� com seus dados, e o arquivo CriaBaseDestino_OLAP.sql cria a base destino, pronta para receber os dados migrados pelo processo de ETL.

Pentaho Data Integration (Kettle)

O Pentaho Data Integration (PDI), uma das ferramentas da su�te open source Pentaho Business Analytics, � comumente chamado pelo nome do projeto que lhe deu origem, o Kettle. Ele � composto por quatro componentes, sendo o mais importante o Spoon, uma interface gr�fica que ser� utilizada neste case para a criaç�o do processo de ETL, enquanto os componentes Pan, Kitchen e Carte se destinam � execuç�o, via linha de comando ou requisiç�es HTTP, dos processos criados no Spoon. Como possibilitam chamadas remotas aos processos, estes componentes permitem a criaç�o de clusters para a execuç�o das ETLs e tamb�m a f�cil utilizaç�o de processos Pentaho por softwares de terceiros.

Com a adoç�o do PDI, passa a ser indiferente para a aplicaç�o qual � o SGBDR Origem e o Destino. Eles s�o apenas datasources, que fornecer�o os dados que passar�o por processos de validaç�o, higienizaç�o, formataç�o, normalizaç�o, sincronizaç�o, etc.

O PDI tem conectividade com praticamente todos os bancos de dados do mercado e pode ainda acessar web services ou fazer chamadas HTTP, al�m de ler e gerar arquivos xml, json, csv, excel, hadoop fs, etc.

Download, Instalaç�o e Configuraç�o do PDI

Para a concretizaç�o do case proposto neste artigo, foram utilizados os softwares Pentaho Data Integration, Pentaho Schema Workbench e Pentaho Business Intelligence Server. Todos s�o executados em m�quinas virtuais Java, da� a necessidade da pr�via instalaç�o do Java Runtime Environment (JRE) e da posterior instalaç�o dos drivers JDBC em cada uma das ferramentas Pentaho. Os caminhos para download dos softwares referidos est�o destacados na seç�o Links.

Para iniciar as atividades, faça o download da vers�o mais recente do PDI e descompacte o arquivo, disponibilizado em formato .zip. Feito isto, instale e execute o aplicativo realizando as etapas apresentadas a seguir:

Para o caso do OS X, execute dentre os arquivos descompactados anteriormente, o Data Integration 64-bit ou o Data Integration 32-bit;
Em SOs Linux, instale o JavaRuntime Environment (JRE) com o Path apontando para a pasta $JAVA_HOME/bin, digite no terminal ou inclua no etc/profile a configuraç�o �export PATH=$PATH:/usr/java/jdk1.6.0_07/bin� e ent�o execute, dentre os arquivos descompactados anteriormente, o .\spoon.sh;
Caso o SO seja o Windows, instale o JavaRuntime Environment (JRE) e configure as vari�veis do sistema �JAVA_HOME� e �Path�. Para isto, acesse o Painel de Controle, Sistema e Segurança e, por fim, Sistema. Ao abrir a janela, selecione Configuraç�es avançadas do sistema e na aba Avançado, clique em Vari�veis de Ambiente. Nas Vari�veis do sistema, clique em Novo e, na janela Nova Vari�vel de Sistema, em Nome da vari�vel, digite �JAVA_HOME�. Em Valor da vari�vel, preencha com o caminho para a pasta do JRE da sua instalaç�o do Java, por exemplo: �C:\Program Files\Java\ jre7�. Para finalizar a configuraç�o no Windows, selecione Path, ainda em Vari�veis do sistema, e clique em Editar..., completando o valor de Path com um ponto e v�rgula e com o caminho para a pasta jre7\bin. Al�m disso, acrescente �;C:\Program Files\Java\jre7\bin� e execute, dentre os arquivos descompactados anteriormente, o Spoon.bat.

Drivers JDBC

Com o PDI instalado e em execuç�o, a primeira tarefa � conferir se ele, em sua configuraç�o inicial, j� possui conectividade com os SGBDRs envolvidos. No PDI, a conex�o aos bancos de dados � feita via JDBC, e cada banco de dados deve ter um driver JDBC correspondente para ele. Dessa forma, basta que o .jar do driver JDBC seja salvo em data-integration\libext\JDBC e o PDI seja reiniciado.

Para este case, em que � utilizado o MySQL, foi utilizado o driver mysql-connector-java-5.1.17.jar. O caminho para download deste driver se encontra na seç�o Links, ao final do artigo.

Saiba mais: Curso Completo de MySQL

Organizaç�o das ETLs

O PDI permite armazenar todas as ETLs em banco de dados, entretanto para isso seria necess�ria a criaç�o do reposit�rio do Pentaho. Neste case, utilizaremos a opç�o de salvar as ETLs em arquivos, e para a organizaç�o destas, foi criada uma pasta chamada SQLMagazine-DW, onde estes arquivos ser�o salvos.

Ao longo desta s�rie, ser� criado um arquivo .ktr para cada transformaç�o. Cada qual ser� composta por uma s�rie de componentes chamados de steps, ligados entre si por meio de hops, que s�o as flechas indicativas da direç�o do fluxo dos dados.

Uma transformaç�o pode ser executada individualmente ou fazer parte de um Job, que permite a execuç�o de v�rias transformaç�es ou at� mesmo de outros Jobs em sequ�ncia, armazenados em arquivos .kjb.

Tanto os arquivos .ktr quanto os .kjb s�o gravados no formato XML, sem criptografia.

Criando a Conex�o Origem

Para obter acesso aos dados da base origem, h� a necessidade de criar uma conex�o com o banco de dados. Esta configuraç�o � feita uma �nica vez e a conex�o ser� compartilhada entre todas as transformaç�es. Isto gera uma flexibilidade muito grande, permitindo que as ETLs sejam criadas acessando uma base de desenvolvimento e, depois de efetuados os testes, com uma �nica modificaç�o nesta configuraç�o, todas as transformaç�es passem a apontar para a base de dados de produç�o, por exemplo. Com o objetivo de demonstrar esta flexibilidade, ser� criada uma transformaç�o e uma conex�o para a base Origem, em MySQL, conforme os passos a seguir:

Inicie o PDI, clique no �cone New file para criar um novo arquivo, selecione Transformation e clique no �cone Salvar;
Selecione a pasta SQLMagazine-DW e salve a transformaç�o com o nome �1-Testa_Conexoes�;
Na interface do PDI, ao lado esquerdo, tem-se a barra de componentes (steps) organizados por categorias. Arraste para a �rea de programaç�o do PDI o step Table input, que fica na categoria Input, cuja finalidade � buscar dados via SQL. Com um duplo clique no step criado, abra a tela de ediç�o;

Como achar um objeto na barra de ferramentas

O PDI possui duas abas no frame esquerdo. Na primeira, chamada View, est�o todos os objetos utilizados, enquanto na segunda, chamada Design, est�o todos os componentes dispon�veis para utilizaç�o. Ambas contam com, na parte superior, a conveni�ncia de um campo de busca pelo nome do componente.

Na tela exibida a partir da realizaç�o do passo anterior, clique em New... e preencha os campos da nova conex�o com os dados da base de Origem, em MySQL, conforme a Figura 4;

Figura 4. Conex�o para a base Origem, em MySQL.

Na sequ�ncia, clique em Test para se certificar que a conex�o est� correta e ent�o em OK;
Para criar automaticamente a query do select, necess�ria para a recuperaç�o dos dados referentes aos vendedores, clique em Get SQL select statement e, no componente Table input, selecione com um duplo clique a tabela vendedor, como indica a Figura 5;

Figura 5. Tabela �vendedor� selecionada no Database Explorer.

Superado o passo anterior, o usu�rio dever� responder � seguinte quest�o: Do you want to include the field-names in the SQL? Clique em Yes para que o PDI gere a query com os nomes das colunas, ao inv�s do �perigoso� SELECT *;
Na continuidade, clique em Preview e em OK para visualizar as primeiras 1000 linhas;
Em seguida, clique em Close para fechar o Preview;
Na etapa Table input, em Step name, escreva um nome que n�o deixe d�vidas da sua funcionalidade, como: �L� vendedor�;
Por fim, clique em OK para fechar o step e salve o arquivo.

Criando a Conex�o Destino

Com a conex�o para a base Origem configurada, criar-se-� a conex�o para a base destino conforme as especificaç�es a seguir:

Arraste para a �rea de programaç�o do PDI o step Table output, que fica na categoria Output;
Crie uma seta de ligaç�o (hop) entre os dois steps. Para isto, clique no primeiro step, mantenha a tecla Shift pressionada e arraste a seta at� o step Table output, tal como se visualiza na Figura 6;

Figura 6. Steps ligados por um hop, que especifica a direç�o do fluxo.

Com um duplo clique em Table output, que tem a finalidade de persistir os dados, informe em Step name o nome �teste�;
Na tela exibida a partir da realizaç�o do passo anterior, clique em New... e preencha os campos com os dados de conex�o da base destino, conforme a Figura 7;

Figura 7. Conex�o para a base Destino, em MySQL.

Na sequ�ncia, clique em Test para se certificar que a conex�o est� correta e ent�o em OK;
Voltando � tela de ediç�o do step Table output, em Target table, escreva �teste�;
Ainda nesta tela, clique em SQL e visualize a DDL para criaç�o da tabela chamada teste no banco destino, com todos os campos do resultset;
Feito isso, clique em Execute e receba a mensagem de retorno do banco de dados, informando que a tabela �teste� foi criada com sucesso;
Em seguida, clique em OK e feche o step;
Para testar efetivamente as conex�es, execute a transformaç�o que est� sendo desenvolvida, �1-Testa_Conexoes�, acessando o menu Action > Run;
Superadas estas etapas, a tela apresentada na Figura 8 ser� visualizada, onde ser� poss�vel, mas n�o necess�rio neste case, configurar alguns par�metros para a sua execuç�o;

Figura 8. Tela de par�metros para execuç�o da transformaç�o.

Em seguida, clique em Launch e acompanhe a execuç�o, como exibido na Figura 9;

Figura 9. Resultado obtido com a execuç�o do processo para testar as conex�es com os bancos Origem e Destino.

Com isso, a primeira ETL foi efetivada. Ela busca dados da tabela �vendedor� do banco Origem e os grava na tabela �teste� do banco Destino. Para verificar a execuç�o desta transformaç�o, observe os resultados obtidos diretamente no banco de dados. A correta execuç�o desta transformaç�o comprova a efic�cia do driver JDBC de conex�o com o MySQL, dos encodes, das permiss�es do banco de dados e da rede;

Para que as conex�es criadas possam ser utilizadas por todas as transformaç�es, no PDI, clique na aba View e depois em Database Connections. Em cada uma das duas conex�es criadas, clique com o bot�o direito e depois em Share, como exp�e a Figura 10.

Figura 10. Conex�o sendo compartilhada.

Com as conex�es criadas, torna-se transparente ao usu�rio do PDI quais s�o os bancos de dados manipulados, pois para esta ferramenta, os dados podem ter origens e formatos diversos. Al�m da facilidade de acesso aos dados, a ferramenta conta com uma longa lista de steps, cada qual com uma atribuiç�o bem espec�fica. Alguns destes steps executam as mesmas tarefas que os comandos SQL, mas nem por isso o conhecimento desta linguagem deixa de ser relevante, pois seu uso repercute, muitas vezes, em ganho de produtividade.

Conclus�o

Neste artigo foram abordados os principais conceitos de Business Intelligence, e para exemplific�-los, foi proposto o case de uma loja de departamentos fict�cia, formada por diversas filiais e com a necessidade de analisar os dados de suas vendas. Em seguida, foi apresentada a su�te Pentaho, capaz de contemplar os requisitos e regras de neg�cio elencados. De volta ao exemplo, a base de dados Origem foi analisada, de forma que o racioc�nio necess�rio para a modelagem da base Destino, em formato estrela, pudesse ser descrito. Por fim, as etapas para a configuraç�o do Pentaho Data Integration foram realizadas.

Os resultados desse trabalho dar�o subs�dios para que, na segunda e �ltima parte deste artigo, o processo de ETL seja criado, juntamente com os metadados do cubo, sua publicaç�o no servidor web e a an�lise dos dados em uma ferramenta OLAP.

Continue lendo: Business Intelligence com Pentaho

Saiu na DevMedia!

D� o pr�ximo passo ap�s o HTML/CSS!:
Nesta s�rie falamos sobre o que vem depois do HTML/CSS. Saiba o que � requisiç�o, resposta e se prepare para os seus primeiros passos na programaç�o back-end.

Saiba mais sobre Business Intelligence ;)

Business Intelligence 2.0:
Veja neste artigo os principais conceitos sobre Business Intelligence 2.0. Veja tamb�m um breve comparativo entre o BI tradicional e o BI 2.0
Porque tomar decis�es utilizando Business Intelligence:
Veja neste artigo a import�ncia do Business Intelligence para as organizaç�es, o porqu� e como essa tecnologia auxilia no processo de tomada de decis�o.
Aplicando Business Intelligence em uma base de dados de Pizzaria:
Este artigo apresenta na pr�tica a aplicaç�o de ferramentas de Business Intelligence em uma base de dados de Pizzaria.
Business Intelligence: Conhecendo algumas ferramentas Open Source:
Veja neste artigo o que � Business Intelligence (BI) e conheça algumas ferramentas open source dispon�veis para BI e quais as funcionalidades e caracter�sticas que podemos esperar dessas ferramentas.
Business Intelligence 2.0 e as m�dias sociais:
Veja neste artigo as novas pr�ticas do Business Intelligence, que s�o as aplicaç�es de BI 2.0 que contam com informaç�es em tempo real e integraç�o com as m�dias sociais para auxiliar no processo de tomada de decis�o das organizaç�es.

Business Intelligence Tutorial

Neste artigo serão apresentadas as técnicas básicas envolvidas na criação de um data warehouse, na modelagem da base multidimensional, no processo de ETL, na modelagem e publicação da parte lógica do cubo (metadados).