Transcription
UNIVERSIDADE DO SUL DE SANTA CATARINAMICHEL ANGELO DA SILVA DARABASCONSTRUINDO SOLUÇÕES DE BUSINESS INTELLIGENCE COM PENTAHO BISUITE COMMUNITY EDITION (CE)Palhoça2012
MICHEL ANGELO DA SILVA DARABASCONSTRUINDO SOLUÇÕES DE BUSINESS INTELLIGENCE COM PENTAHO BISUITE COMMUNITY EDITION (CE)Este Trabalho de Conclusão de Curso foi julgadoadequado à obtenção do título de Bacharel emSistemas de Informação e aprovado em sua formafinal pelo Curso de Graduação em Sistemas deInformação da Universidade do Sul de SantaCatarina.Orientador: Prof. Aran Morales, Dr.Palhoça2012
MICHEL ANGELO DA SILVA DARABASCONSTRUINDO SOLUÇÕES DE BUSINESS INTELLIGENCE COM PENTAHO BISUITE COMMUNITY EDITION (CE)Este Trabalho de Conclusão de Curso foi julgadoadequado à obtenção do título de Bacharel emSistemas de Informação e aprovado em sua formafinal pelo Curso de Graduação em Sistemas deInformação da Universidade do Sul de SantaCatarina.Palhoça, 21 de novembro de 2012.
Dedico este trabalho a minha esposa e a minhafamília, principalmente aos mais próximos, queme ajudaram a alcançar mais um objetivo.
AGRADECIMENTOSA Deus por tudo que me proporciona na vida.A minha esposa, Jacqueline F. R. Darabas, pelo seu apoio, paciência e incentivono decorrer deste projeto.A minha mãe, Terezinha da Silva, pelo seu suporte e apoio.Ao amigo, João A. Bertotti, por seu apoio, incentivo e aconselhamento.Ao professor e orientador Aran Morales, por ter dado a ideia e orientação paraeste trabalho e, também, pelo incentivo na trajetória de conclusão do mesmo.Aos professores do curso de Sistemas de Informação, que foram tão importantesna minha vida acadêmica e no desenvolvimento desta monografia.A todos que de alguma forma contribuíram para este trabalho, com dicas,sugestões e opiniões.
RESUMOOs recursos de business intelligence (BI) são muito utilizados na indústria. Porém a utilizaçãode ferramentas open source ainda é um tanto limitada. Essas ferramentas têm como vantagemo fato de não terem custo de licenciamento de software e de possuírem código fonte aberto.Este trabalho mostra a criação de uma solução de BI, através da construção de um repositóriode dados do tipo data warehouse, utilizando as ferramentas open source da suite Pentahopara, no fim, se ter um uma interface gráfica ou front end para o usuário final. Para a criaçãoda solução, foram escolhidos dados públicos do Portal da Transparência e do IBGE, ambossubordinados ao governo federal, com o objetivo de cruzar informações sobre a transferênciade recursos federais para os estados e municípios com a estimativa da população dos mesmos,entre os anos de 2005 e 2011, subdivididos por projeto em que o recurso foi aplicado. Estasolução de BI, que utiliza ferramentas da suite Pentaho, começa com a criação do datawarehouse, seguindo pela criação de um repositório de metadados com a ferramentaMetadata Editor. Na sequência, mostra o processo ETL criado com ferramenta Kettle, e aconclusão da carga dos dados. A ferramenta utilizada para criação de relatórios é a ReportDesigner e, para a criação de gráficos, a Design Studio. Por fim, são criados cubos de dadosOLAP com a ferramenta Schema Workbench, e tanto os cubos como os relatórios e gráficossão publicados no BI Server. Este último possui o motor para o fornecimento de uma série defunções essenciais para suite Pentaho e também contém um servidor web com uma ferramentachamada de Console do Usuário, sendo este um front end para interagir com o usuário final.Palavras-chave: Business Intelligence. Data Warehouse. ETL. OLAP. Pentaho. Kettle.Mondrian. Metadata Editor. Report Designer. Schema Workbench. Design Studio.
LISTA DE ILUSTRAÇÕESFigura 1 – Uma arquitetura de alto nível do BI . 18Figura 2 – Ilustração data mart . 20Figura 3 – Importância da definição da granularidade no projeto de data warehouse . 21Figura 4 – Elementos participantes da ocorrência de uma compra . 23Figura 5 – Modelo multidimensional . 24Figura 6 – Modelo estrela . 25Figura 7 – Modelo floco de neve . 26Figura 8 – O processo de ETL . 27Figura 9 – Pilha BI Pentaho . 29Figura 10 – Console do Usuário . 31Figura 11 – Interface de Pentaho Metadata Editor . 32Figura 12 – Pentaho Data Integration, ferramentas e componentes . 35Figura 13 – Arquitetura do processo de geração de relatórios . 36Figura 14 – Data warehouse com Mondrian . 38Figura 15 – Visão geral dos componentes Pentaho OLAP . 39Figura 16 – Etapas Metodológicas . 44Figura 17 – Modelo no formato estrela para o banco de dados multidimensional . 50Figura 18 – Administration Console – Conexão com o banco de dados . 52Figura 19 – Console do Usuário – Solução transferência de recursos . 53Figura 20 – Console do Usuário - Relatório aberto na parte central . 54Figura 21 – Publisher config . 54Figura 22 – Modelo de metadados Transferência de Recursos . 55Figura 23 – Modelo lógico de tabelas de negócio . 57Figura 24 – Propriedades da tabela de negócio Fato População . 57Figura 25 – Configuração de um relacionamento com PME . 58Figura 26 – Visão de negócios com PME . 59Figura 27 – Publicação de PME para o BI Server . 60Figura 28 – Exemplo de interface do PDI Spoon – Transformação Dimensão Tempo . 61Figura 29 – Job principal do processo ETL . 62Figura 30 – Job Carrega Dimensão Geográfica . 62
Figura 31 – Transformação Carrega Dimensão Geográfica 1 . 63Figura 32 – Transformação Carrega Dimensão Geográfica 2 . 64Figura 33 – Transformação Código Portal da Transparência 1 . 65Figura 34 – Transformação – Configuração Modifield Java Script Value . 67Figura 35 – Transformação Dimensão Tempo . 68Figura 36 – Job Carrega Fato População. 69Figura 37 – Transformação Carrega Fato População 2005 . 69Figura 38 – Carga Fato População 2005, Merge Join . 70Figura 39 – População dos municípios em PDF para o ano de 2008 . 71Figura 40 – Transformação Carga Fato População 2008 . 72Figura 41 – Job Carrega Dimensão Projeto. 73Figura 42 – Transformação Dimensão Projeto 2011 . 73Figura 43 – Configuração do Componente Insert / Update . 74Figura 44 – Job Carrega Fato Recurso Transferido . 75Figura 45 - Transformação Carrega Fato Recurso Transferido 2011 . 76Figura 46 – Transformação Fato Recurso Transferido por Habitante . 77Figura 47 – Métricas da transformação da dimensão tempo . 78Figura 48 – Relatório Transferência de Recursos por Função e Estado . 80Figura 49 – PRD – Metadados como fonte dos dados . 81Figura 50 – PRD – Query Editor . 82Figura 51 – PRD Wizard, Definição do Layout do Relatório . 83Figura 52 – Interface de relatório de Pentaho Report Designer. 84Figura 53 – Configuração de parâmetros . 85Figura 54 – Publicação no BI Server . 86Figura 55 – Relatório Transferência de Recursos aos Municípios por Estado e Ano . 87Figura 56 – Gráfico Bolha Transferência de Recursos por Localidade . 88Figura 57 – Interface de Pentaho Design Studio . 90Figura 58 – Design Studio código fonte XML . 91Figura 59 – Gráfico Linha Transferência de Recursos por Localidade e Ano . 92Figura 60 – Configuração do gráfico com XML . 93Figura 61 – Gráfico Linha Transferência de Recursos por Regiões do Brasil e Ano . 94Figura 62 – Interface de Pentaho Schema Workbench . 95
Figura 63 – Cubo Transferência de Recursos com JPivot no Console do Usuário . 96Figura 64 – Árvore do cubo Transferência de Recursos . 97Figura 65 – Atributos da medida valor transferido. 99Figura 66 – Cubo Transferência de Recursos por Habitante – JPivot. 99Figura 67 – Publicação no BI Server por Schema Workbench . 101
LISTA DE SIGLASBI - Business IntelligenceBPM - Business Performance ManagementCDF - Community Dashboard FrameworkCE - Community EditionCFO - Chief Financial OfficerCGU - Controladoria Geral da UniãoDB - Data BaseDM - Data MartDML - Data Manipulation LanguageDS - Data SourceDW - Data WarehouseETL - Extract, Transform and LoadIBGE - Instituto Brasileiro de Geografia e EstatísticaIDE - Integrated Development EnvironmentJDBC - Java Database ConnectivityMDX - Multidimensional ExpressionsOLAP - On-Line Analytical ProcessingOLTP - Online Transaction ProcessingPDI - Pentaho Data IntegrationPDS - Pentaho Design StudioPL/pgSQL - Procedural Language/Postgre SQLPME - Pentaho Metadata EditorPRD - Pentaho Report DesignerPSW - Pentaho Schema WorkbenchRDBMS - Relational Database Management SystemROLAP - Relacional OLAPSQL - Structured Query LanguageTCU - Tribunal de Contas da UniãoXML - Extensible Markup LanguageXML/A - XML for Analysis
SUMÁRIO1 INTRODUÇÃO. 141.1 PROBLEMÁTICA . 141.2 OBJETIVOS . 151.2.1 Objetivo Geral . 151.2.2 Objetivos Específicos. 151.3 JUSTIFICATIVA . 151.4 ESTRUTURA DA MONOGRAFIA . 162 REFERENCIAL BIBLIOGRÁFICO . 172.1 DEFINIÇÕES E CONCEITOS DE BI . 172.2 ARQUITETURAS DE BI. 172.2.1 Data Warehouse (DW) . 182.2.1.1 Data Mart (DM) . 192.2.1.2 Granularidade . 202.2.1.2 Metadados. 212.2.2 Modelagem Multidimensional. 222.2.2.1 Modelo Estrela. 242.2.2.2 Modelo Floco de Neve . 252.2.3 Extract, transform and load (ETL) . 262.2.4 On-Line Analytical Processing (OLAP) . 272.3 SUITE PENTAHO. 282.3.1 Arquitetura Pentaho . 282.3.2 BI Server . 302.3.3 Pentaho Metadata Editor (PME) . 312.3.4 Pentaho Data Integration (Kettle) . 332.3.5 Pentaho Reporting. 352.3.5.1 Pentaho Report Designer . 362.3.6 Pentaho Design Studio . 372.3.7 Pentaho Analysis Services (Mondrian) . 372.3.7.1 Pentaho Schema Workbench . 402.4 CONSIDERAÇÕES FINAIS . 40
3 MÉTODO . 423.1 CARACTERIZAÇÃO DO TIPO DE PESQUISA . 423.2 ETAPAS METODOLÓGICAS . 433.3 DELIMITAÇÕES . 434 SOLUÇÃO DE BI TRANSFERÊNCIA DE RECURSOS . 454.1 ESCOLHA DA FONTE DOS DADOS . 454.1.1 Portal da Transparência . 454.1.1.1 Dados do Portal da Transparência . 464.1.2 Portal do IBGE . 464.1.2.1 Pesquisa Demográfica . 474.1.2.2 Dados do Portal da IBGE . 474.2 MODELAGEM MULTIDIMENSIONAL . 484.2.1 Tabelas de Fatos . 484.2.2 Tabelas de Dimensões . 484.2.3 Modelo . 494.3 PLATAFORMA DE BI PENTAHO (SERVER) . 504.3.1 Administration Console . 514.3.2 Console do Usuário . 524.3.3 Publicações Externas. 544.4 REPOSITÓRIO DE METADADOS (PME) . 554.4.1 Conexão . 564.4.2 Modelo de Negócios . 564.4.2.1 Tabelas de Negócios . 564.4.2.2 Relacionamentos. 574.4.2.3 Visão de Negócios . 584.4.3 Publicação no BI Server . 594.5 PROCESSO ETL (KETTLE) . 604.5.1 Job Carrega Dimensão Geográfica . 624.5.1.1 Transformação Dimensão Geográfica 1 . 634.5.1.2 Transformação Dimensão Geográfica 2 . 644.5.1.3 Transformação Dimensão Geográfica 3 – Nomes dos Estados. 64
4.5.1.4 Transformação Dimensão Geográfica – Código Portal da Transparência . 654.5.1.5 Transformação Dimensão Geográfica – Registro de Estados . 674.5.2 Transformação Dimensão Tempo. 674.5.3 Job Carrega Fato População . 684.5.3.1 Transformação - Carga Fato População 2005, 2006, 2007 e 2011 . 694.5.3.2 Transformação - Carga Fato População 2008 e 2009 . 704.5.3.3 Transformação - Carga Fato População 2010 . 724.5.4 Job Carrega Dimensão Projeto . 724.5.4.1 Transformação Dimensão Projetos de 2005 a 2011 . 734.5.5 Job Carrega Fato Recurso Transferido . 744.5.5.1 Transformações - Carga Fato Recurso Transferido de 2005 a 2011 . 754.5.6 Transformação Fato População Recurso Transferido por Habitante . 764.5.7 Execução do Job Principal do Processo ETL . 774.6 PENTAHO REPORTING . 784.6.1 Pentaho Report Designer . 784.6.1.1 Relatório Transferência de Recursos por Função e Estado . 794.6.1.2 Relatório Transferência de Recursos aos Municípios por Estado e Ano . 864.7 PENTAHO DESIGN STUDIO. 874.7.1 Gráfico Bolha Transferência de Recursos por Localidade . 884.7.2 Gráfico Linha Transferência de Recursos por Localidade e Ano . 914.8 PENTAHO MONDRIAN COM SCHEMA WORKBENCH . 944.8.1 Cubo de Dados Transferência de Recursos . 954.8.1.1 Elementos do Cubo de Dados. 964.8.2 Cubo de Dados Transferência de Recursos por Habitante . 994.8.3 Publicação no BI Server . 1005 CONCLUSÕES E TRABALHOS FUTUROS . 102REFERÊNCIAS . 104APÊNDICES . 108APÊNDICE A – Script SQL para Criação do Data Warehouse. 109APÊNDICE B – Script PL/pgSQL Auxiliar para Execução dos Gráficos . 111ANEXO . 118ANEXO A – Exemplo de Origem dos Dados . 119
141INTRODUÇÃOSegundo Scheps (2008), Business Intelligence (BI) é um conjunto de ferramentascom o propósito principal de entregar as informações adequadas para os corretos tomadoresde decisões em momentos oportunos. Contudo, isso só funciona se os últimos forem nãosomente capazes de usar as ferramentas de BI, mas também devem ser capazes de formular asquestões certas. Com soluções de BI, empresas podem descobrir informações valiosas dentrode uma massa de dados complexa.As soluções de BI tratadas, nesta proposta, são as da Pentaho BI SuiteCommunity Edition (CE), que representam um conjunto de ferramentas Pentahomantidas pela comunidade, open source1 e com processos de extração de dados eorganização dos mesmos para que se tornem informações. Isto através de ferramentas deanálise e de apresentação de relatórios e gráficos.1.1PROBLEMÁTICAA utilização de soluções de BI vem crescendo gradativamente ao longo dos anos,cada vez mais organizações procuram por esse tipo de tecnologia para terem parâmetrosprecisos na tomada de decisão.Os sistemas transacionais, Online Transaction Processing (OLTP2) geram umgrande número de dados, tornando-se difícil, com o tempo, a obtenção de informaçõeshistóricas precisas, sendo assim, necessária uma solução de BI para utilizar as informaçõestemporais de forma compacta e objetiva, tornando-se possível a obtenção de informaçõesvaliosas para a tomada de decisão.Soluções de BI podem exigir adequação às necessidades específicas de umaorganização, em muitos casos, sendo necessário realizar customizações no código fonte, ouaté mesmo, de novas implementações de software e, ainda, essa organização pode necessitardistribuir esta solução customizada para suas filiais. As soluções de BI proprietárias têmcondições mais rígidas de licenciamento e distribuição, neste caso, uma solução open sourcepode ser inevitável, pois o custo de se desenvolver uma solução de BI do zero pode serinviável. Em contra partida, as soluções open source não possuem custo com licenciamento e1Open source é uma modalidade de licenciamento de software no qual não há custos com licenças. É baseadoem padrões abertos e o código fonte está disponível para qualquer um. (BOUMAN; DANGEN, 2009).2Processamento de Transações online (OLTP) são ambientes de software que lidam com os negócios rotineirosno andamento de uma empresa, sendo eficientes no processamento de transações, porém ineficientes na geraçãode consultas e relatórios. (TURBAN et al., 2008).
15também possuem código fonte aberto, podendo ser modificadas e depois distribuídas àvontade.Neste sentido, a pesquisa deste trabalho procura responder a seguinte pergunta:Como construir soluções de BI com ferramentas open source para auxílio aoprocesso de tomada de decisão?1.2OBJETIVOSOs objetivos podem ser divididos em objetivo geral e objetivos específicos.1.2.1 Objetivo GeralConstruir uma solução de BI, para auxílio ao processo decisório, utilizandoferramentas open source e disponibilizadas pela Pentaho em sua suite3 de aplicativos.1.2.2 Objetivos EspecíficosConstruir um repositório de dados tipo data warehouse4 de uma base de dadospública específica.Realizar o processo de extração, transformação e carga de dados com umaferramenta gráfica e open source.Construir soluções de análises de dados com ferramentas open source edisponibiliza-las através de uma ferramenta de front end5.1.3JUSTIFICATIVAComo objetivos principais do BI estão relacionados, o acesso interativo aos dados,a manipulação dos mesmos e a análise adequada dos dados por parte dos gerentes e analistas3Suite são um conjunto de programas de computador, com um design uniforme e com a capacidade decompartilhar dados. (OXFORD, 2012).4Data warehouse é grande base de dados capaz de reunir as informações de interesse de uma empresa,provenientes de fontes de dados diversas. (MACHADO, 2004).5Front end são programas que fornecem uma interface amigável com o usuário, permitindo que os mesmosinterajam com o software. (BOUMAN; DANGEN, 2009).
16de negócios. O processo do BI baseia-se em transformar os dados em informações, asmesmas em decisões, para, no final, tomar as ações adequadas. (TURBAN et al., 2008).O uso de ferramentas de business intelligence é muito popular na indústria.Entretanto, o uso de ferramentas open source é ainda um tanto limitada se comparada comoutros tipos de software. As ferramentas dominantes são as de código fechado e comercial.(THOMSEN; PEDERSEN, 2009).Para apresentar este trabalho, os softwares escolhidos foram os da suite deferramentas Pentaho, por ser um ferramental de BI bem completo, bastante difundido,multiplataforma, baseado em padrões abertos e open source.Segundo Weber (2003), ferramentas open source permitem o acesso ao códigofonte das mesmas sem limites, não possuem custos com licenciamento e ainda podem ter seucódigo fonte customizado, conforme a necessidade, para que depois se possam distribuir asaplicações modificadas.1.4ESTRUTURA DA MONOGRAFIAO capítulo 1 apresentou a problemática e justificativas sobre a construção desoluções de BI com Pentaho BI Suite Community Edition (CE).No capítulo 2 é apresentado o referencial teórico que dará embasamento científicopara o desenvolvimento deste trabalho, nessa seção, serão apresentados conceitos sobre BI,incluindo ELT, data warehouse, OLAP e sobre a suite de Ferramentas Pentaho.O capítulo 3 apresenta o método proposto juntamente com a metodologia adotadae delimitação do trabalho.No capítulo 4, é apresentada a solução do projeto.No quinto e último capítulo, serão apresentadas as conclusões e trabalhos futuros.
172REFERENCIAL BIBLIOGRÁFICONeste capítulo, são apresentados os conceitos essenciais para a construção de umrepositório de dados do tipo data warehouse, assim como conceitos de business intelligence.Também, são apresentadas as ferramentas open source da suite Pentaho que serãoutilizadas para apresentar a solução de BI aqui proposta.2.1DEFINIÇÕES E CONCEITOS DE BIBusiness Intelligence (BI) não é um simples produto, aplicação, programa,usuário, área ou sistema, mas, sim, uma arquitetura abrangente de sistemas integrados emétodos que oferecem informações para tomada de decisão e aprendizado. As pressõescompetitivas fazem com que as organizações tenham que continuamente se adaptarem emelhorarem para obterem sucesso em ambientes de negócio que estão em constante mudança.As informações podem ser requeridas em todos os níveis da organização para contínuatomada de decisão. (WOODSIDE, 2010).O benefício principal do BI para uma organização é a capacidade do mesmofornecer informações precisas de acordo com a necessidade, incluindo uma visão dodesempenho da empresa em tempo real e, também, de suas partes. (TURBAN et al., 2008).De acordo co
PDI - Pentaho Data Integration PDS - Pentaho Design Studio PL/pgSQL - Procedural Language/Postgre SQL PME - Pentaho Metadata Editor PRD - Pentaho Report Designer PSW - Pentaho Schema Workbench RDBMS - Relational Database Management System ROLAP - Relacional OLAP SQL - Structured Query Language TCU - Tribunal de Contas da União
