PS-400
GENERATING MANAGEMENT PANELS FROM DATA MARTS: AN EXPERIENCE REPORT
Fabio Zanardi (West State University of Parana/Datacoper Software, Paraná, Brazil) – fabio.zanardi@datacoper.com.br
Marcio Seiji Oyamada (West State University of Parana, Paraná, Brazil) – marcio.oyamada@unioeste.br
Clodis Boscarioli (West State University of Parana, Paraná, Brazil) – clodis.boscarioli@unioeste.br
Data stored are the basic elements which alone have no value for management analysis, and the operational databases are not created for the extraction of such information. Data Warehouse or Data Marts is the solution usually used for this purpose. In order to demonstrate the main advantages of using a Data Mart to build management panels, this paper presents a case study of a real business problem to generate a graphical agribusiness management panel.
Keywords: Business Intelligence, Decision Support Systems, Panel Management Solution
GERAÇÃO DE PAINÉIS GERENCIAIS A PARTIR DE DATA MARTS: UM RELATO DE EXPERIÊNCIA
Dados são os elementos básicos armazenados que por si só não tem valor para análise gerencial, e as bases de dados operacionais não são criadas para a extração desse tipo de informação. A utilização de Data Warehouse ou mesmo Data Marts é uma solução muito utilizada para esse fim. Com o intuito de demonstrar as principais vantagens da utilização de um Data Mart na construção de painéis gerenciais, esse trabalho apresenta um estudo de caso que traz a resolução de um problema real de uma empresa ao gerar gráficos de um painel gerencial de agronegócios.
Palavras-chave: Inteligência de Negócio, Sistemas de Suporte a Decisão, Painéis de Gestão.
INTRODUÇÃO
Com o passar do tempo, as organizações vão crescendo e, junto com elas, suas bases de dados, que se tornam uma fonte útil para a geração de informação sobre seus negócios e, consequentemente, de apoio à gestão estratégica da organização.
Normalmente, estes dados são manipulados e armazenados com o intuito de dar suporte às atividades críticas da organização, como compras, vendas, pagamentos, estoque, entre outros, por meio de sistemas de processamento de transações on-line (OLTP).
Ao extrair informações de bases de dados alimentadas diretamente por sistemas OLTP para fins de tomada de decisão, algumas dificuldades são encontradas, como a de manter dados históricos que variam com o tempo, a exemplo do estado civil dos clientes. Além disso, há problemas que podem ocorrer com relação ao desempenho, uma vez que essas bases de dados não são otimizadas para recuperação em grande massa de dados.
Neste contexto, muito se fala sobre Business Intelligence (BI) – que pode ser traduzido como Inteligência de Negócio ou Inteligência Empresarial –, e sobre os desafios na geração de informações para a gestão de negócios. Segundo TURBAN et al. (2009), os principais objetivos do BI são permitir o acesso interativo aos dados, proporcionar a manipulação desses dados e fornecer aos gerentes e analistas de negócios a capacidade de realizar a análise adequada.
O objetivo deste artigo é apresentar a modelagem dimensional e criação de um painel gerencial para o VISTRA, um sistema de BI, bem como uma aplicação no agronegócio, e está assim organizado:
A Seção Fundamentação Teórica introduz os conceitos principais de embasamento do trabalho. A Seção O sistema VISTRA BI apresenta a ferramenta na qual o desenvolvimento desse trabalho está inserido. Na Seção Metodologia traz a sistematização metodológica de execução da pesquisa. O Estudo de Caso realizado em uma empresa do ramo de agronegócios é apresentado a seguir. A Seção Resultados discute os principais resultados da pesquisa, e a Seção Conclusões por seu turno, traz algumas considerações finais e perspectivas da pesquisa.
FUNDAMENTAÇÃO TEÓRICA
As organizações estão sendo forçadas a captar, compreender e explorar seus dados para dar suporte à tomada de decisões, a fim de melhorar suas operações de negócios. Combinando um conjunto de ferramentas com suas próprias bases de dados operacionais, as organizações podem buscar informações relevantes para a tomada de decisões mais rápida.
Os sistemas de informação que utilizam estruturas especiais de dados voltados à análise exploratória e consultas analíticas são chamados de ferramentas de BI - Business Intelligence, e são responsáveis por coletar, extrair e analisar informações que serão utilizadas para auxiliar nos processos de gestão e tomada de decisão (KIMBALL e ROSS, 2002). As principais tecnologias envolvidas em um projeto de BI são Data Warehouse (DW) e Data Mart (DM), On-line Analytical Processing (OLAP) e Data Mining.
Data Warehouse, segundo (KIMBALL, 1998), é um banco de dados orientado por assunto, integrado, não volátil e histórico, criado para suportar o processo de tomada de decisão. OLAP é a capacidade para manipular e analisar um grande volume de dados em múltiplas perspectivas. Para (WITTEN; FRANK, 2005), Data Mining é o processo responsável pela extração automática de novas informações que estão implícitas em uma base de dados. O foco desse estudo é OLAP e visualização de dados a partir de gráficos e navegação informacional.
Contudo, segundo (TURBAN et al., 2009), para que esses dados brutos possam se tornar informação e posteriormente, conhecimento, primeiramente é necessário que haja um processo de transformação para estarem prontos para que as ferramentas de BI responsáveis pela visualização dessas informações possam lê-los e interpretá-los corretamente. Esse processo denomina-se ETL (Extract, Transform and Load – Extração, Transformação e Carga).
O processo do BI se baseia, portanto, na transformação de dados em informações, depois em decisões e finalmente, em ações. Ferramentas ETL tem por função a extração de dados de diversos sistemas e a transformação desses dados conforme regras de negócios para posterior carga em um Data Warehouse que, segundo (KIMBALL, 1998), é um banco de dados orientado por assunto, integrado, não volátil e histórico, criado para suportar o processo de tomada de decisão.
ETL envolve a extração de dados de fontes externas, a transformação dos mesmos para atender às necessidades de negócios, e finalmente, a carga em um
DW ou DM. Segundo FARIA (2006
Data Warehouse é então um sistema de banco de dados que recupera e consolida os dados periodicamente a partir dos sistemas de origem em um conjunto de dados dimensional ou normalizado (RAINARDI, 2007). Também são conhecidos por serem repositórios de dados que após devidamente preparados, podem armazenar dados atuais e históricos, de forma incremental. Sua arquitetura é focada nos assuntos mais importantes de uma organização.
), a fase de ETL é uma das mais críticas do ciclo de vida de um DW, pois a conexão do mesmo com os bancos de dados fonte implica em quais dados serão trazidos, quais os principais refinamentos que serão feitos nesses dados e como essa informação será disponibilizada.
A estrutura de um DW normalmente é constituída de vários DM, que podem ser considerados subconjuntos de dados de um DW. Geralmente, os DM são criados para a separação de dados referentes a um nicho da organização (FARIA, 2006). Também podem ser criados para a separação de diferentes níveis de sumarização com dados agregados, focalizando uma ou mais áreas específicas, como vendas por ano ou vendas por mês.
Segundo RAINARDI (2007), um
DM
Um Data Mart se diferencia de um DW em seu uso e gerenciamento. DM são menores e menos complexos que os DW e, portanto, são tipicamente mais fáceis de construir e manter. Segundo Prado (2006), entre os motivos que levam primeiro ao desenvolvimento de um
é um grupo de tabelas de fatos relacionados e suas tabelas de dimensão correspondente, contendo as medições de eventos empresariais categorizados por suas dimensões.
DM estão: servir como projeto piloto, atender necessidades imediatas de uma unidade, curto tempo para o projeto, etc.
O SISTEMA VISTRA BI
Os dados brutos nas bases de dados não tem valor para a análise gerencial, mas a colocação desses dados em um contexto permite que o mesmo passe a gerar a informação. Conhecendo essa necessidade das organizações, a VISTRA Tecnologia, empresa do grupo Datacoper Software1, criou o VISTRA BI2
O sistema VISTRA BI não tem integrado a ele uma solução de ETL efetiva. Atualmente, os dados brutos do ERP são disponibilizados em uma base de dados Firebird, de onde o sistema de BI recupera a maioria de seus dados.
, que contempla uma série de recursos para criação de análises e processos inteligentes de negócios, fazendo com que seu uso, aplicado a um determinado processo de decisão, gere vantagem competitiva. A Datacoper também desenvolve sistemas ERP focados em agronegócio e um dos interesses da organização é fornecer aos clientes de ERP a sua solução de BI.
A solução até então adotada supre grande parte das necessidades de extração de informação, mas para alguns clientes, a demora na recuperação dessas informações torna-se mais crítica a cada dia devido ao acelerado aumento do espaço em disco ocupado pela base de dados, que em alguns casos, ultrapassa centenas de Gigabytes (GB).
Quando se trata da gestão de uma organização, relatórios que listam movimentações não são suficientes. Em determinadas situações um gráfico pode dar informação mais útil que uma lista de milhares de registros. As decisões que podem mudar drasticamente o rumo de uma organização são dirigidas por dados agregados, ou seja, um gráfico que mostra o total em vendas por mês em determinado ano geralmente é mais significativo na visão estratégica do que uma listagem de todas as vendas ocorridas nesse período. Portanto, o importante nem sempre é visualizar todas as informações separadas, mas sim, o que um grupo de dados agregados pode mostrar.
Os clientes da Datacoper que utilizam VISTRA BI estão acostumados a ter acesso a informações em várias formas de apresentação, seja por listagem, análise multidimensional (cubo OLAP), gráficos, indicadores de desempenho, planilhas, etc. O usuário pode ter todas essas informações advindas de várias bases de dados, recuperadas por meio de consultas SQL (Structured Query Language).
Após a inclusão do suporte a painéis gerenciais no VISTRA BI, onde todas essas formas de apresentação podem estar juntas em uma única tela, os gestores constataram a possibilidade de agregar e relacionar informação de forma facilitada, com maior controle, podendo fazer análises comparativas mais elaboradas.
Nesse sentido, devido à demanda desses clientes, painéis de gestão de desempenho começaram a ser desenvolvidos. Em cada um desses painéis, diferentes tipos de informações devem ser mostrados. Essas informações devem ser exibidas em gráficos, medidores, listagens, planilhas, análises multidimensionais, possibilitando o uso de filtros, entre outros, o que levou à busca de alternativas para otimização do tempo de junção das informações. A proposição do uso de DM se justifica pelo tempo elevado necessário para extrair
1 Site oficial da empresa: http://www.datacoper.com.br/ 2
das bases de dados operacionais as informações que alimentarão o painel gerencial.
Ao carregar dados da base operacional, normalmente registros das principais tabelas do sistema são necessários. Isso significa que em alguns casos, precisam ser feitas junções de várias tabelas e milhões de registros precisam ser carregados para gerar a informação desejada. O problema é que com tantas informações sendo buscadas de diferentes tabelas do banco de dados, com a maioria delas agregadas, o carregamento desses dados em alguns casos ficou praticamente inviável em se tratando de tempo gasto para execução.
Visando um estudo de caso em que a necessidade de um DM para viabilizar a utilização de painéis gerenciais seja real, para esse trabalho um desses painéis foi escolhido.
METODOLOGIA
Dado o problema central da pesquisa, um dos objetivos específicos desse trabalho é, então, a diminuição do tempo de resposta às consultas SQL necessárias para a execução de um painel de gestão específico, requerido por um cliente. Foi então implementado a partir de um processo de ETL, um DM que armazena os dados necessários em tabelas de conteúdo agregado, diminuindo assim a quantidade de registros e tabelas a serem utilizados pelas consultas SQL, tornando-as mais rápidas e, consequentemente, viabilizando a utilização do painel.
A seguinte metodologia foi adotada para tal implementação:
- Análise do banco de dados operacional de origem para a carga do DM; - Modelagem do Data Mart;
- Definição da ferramenta de ETL a ser utilizada; - Definição e execução do Processo de ETL;
- Escolha de um cliente para realização de um estudo de caso; - Testes de desempenho e avaliação dos resultados.
Por ser o mesmo Sistema Gerenciador de Banco de Dados (SGBD) da base de dados operacional, o Firebird 2.5 (2011) foi escolhido para ser o SGBD da base de dados do DM, por ser gratuito e por suportar o processo de carga e busca de dados.
Para cada tabela criada no DM um processo de transformação foi desenvolvido na ferramenta Pentaho Data Integration, também conhecido como Kettle3, por ser open source e multiplataforma (CASTERS, BOUMAN, DONGEN, 2010). Esses processos envolvem a busca de dados das tabelas do banco de dados de origem via comandos SQL, a manipulação desses dados para se adequarem às respectivas tabelas do banco de dados de destino (DM) e o carregamento dessas tabelas.
3 Mais informações sobre a ferramenta Kettle e sobre a plataforma Pentaho podem ser encontradas na URL:
Estudo de Caso
Para o desenvolvimento deste estudo de caso foi utilizado a base de dados de um cliente do sistema VISTRA BI do ramo do agronegócio, com aproximadamente 18 Gigabytes.
Segundo (CORREA, 2010), “compor análises sobre o agronegócio é sempre um desafio, dado o número de fontes de dados heterogêneas e de diferentes granularidades de informação. Mas, se os conteúdos estiverem agrupados por assunto e tratados para gerar informação consolidada, é possível agilizar a obtenção de variáveis para apoiarem as pesquisas e análises para o agronegócio”. Essa é a mesma motivação para o estudo em questão, para validar o painel gerencial desenvolvido, em uma aplicação real.
Neste estudo de caso, as informações que serão disponibilizadas no painel gerencial (representação gráfica) são:
• Valor total por ano do faturamento com insumos; • Quantidade a fixar pelos quatro principais produtos; • Saldo a fixar (a vencer e vencido);
• Movimentação agrícola por ano dos quatro principais produtos; • Faturamento por grupo de estoque.
Esses gráficos requerem a carga de muitos dados, pois além dos dados serem de tabelas com grandes números de registros, estes são de um período de 10 anos. Após a análise do banco de dados operacional de origem para a alimentação do DM, verificou-se que nove tabelas seriam necessárias, conforme Tabela 1.
Nome da tabela Descrição
AGR007 Saldo de mercadorias a afixar por parceiro
CADEMP Empresas e unidades
EST001 Pedidos de compra/venda de mercadorias
EST002 Itens de estoque
EST003 Movimentação de estoque de mercadorias
EST009 Itens do pedido de compra/venda de mercadoria
EST024 Contas de estoque
FIN007 Títulos (pagar/receber)
GER_PARAMETROS Parâmetros gerais do sistema
Tabela 1: Tabelas necessárias do banco de dados origem
Apesar de uma característica marcante de um DM ou DW ser guardar dados históricos, o DM em questão não tem essa necessidade. Também não possuirá a dimensão tempo, pois não possui campos de data em nenhuma das tabelas fato. O modelo de dados obtido para a alimentação do painel de gráficos é demonstrado na Figura 1:
Figura 1: Modelagem do Data Mart gerado
Para cada assunto tratado no painel foi criada uma tabela correspondente: - o gráfico de Faturamento de Insumos é alimentado pela tabela FATO_FAT_INSUMOS;
- o gráfico de Quantidade a Fixar pela tabela FATO_QTD_AFIXAR;
- o gráfico de Saldo a Fixar pela tabela
FATO_SALDO_AVENCER_VENCIDO;
- os quatro gráficos de movimentação agrícola pela tabela
FATO_QTD_ENTREGUE; e,
- o gráfico de faturamento por grupo de estoque da tabela FATO_FAT_GRUPO_ESTOQUE.
- para a lista das filiais que serão utilizadas como filtro dos gráficos, foi criada a tabela DIM_EMPRESA.
Foram necessárias seis transformações para a alimentação do DM e todas foram suportadas pela ferramenta Kettle. As conexões aos bancos de dados origem/destino foram feitas via ODBC (Open Data Base Connectivity). As transformações de Kettle selecionam os registros da base de dados origem através de consultas SQL, processam esses dados e finalmente alimentam as tabelas do DM.
Para cada tabela criada no DM um processo de transformação foi desenvolvido no Kettle. Esses processos envolveram a busca de dados das tabelas do banco de dados de origem via comandos SQL, a manipulação desses dados para se adequarem às respectivas tabelas do banco destino (DM) e a carga dessas tabelas.
Uma vez que a granularidade de tempo das tabelas fato do DM serem de
ano e mês (FATO_FAT_GRUPO_ESTOQUE, FATO_QTD_ENTREGUE,
FATO_QTD_AFIXAR e FATO_FAT_INSUMOS), decidiu-se que busca na base de dados origem tivesse parâmetros de ano/mês inicial e ano/mês final. Assim, o processo de carregamento completo se dá somente uma vez. Após a primeira carga, que tem como parâmetros o período de todas as movimentações da
organização, as cargas seguintes podem ser feitas somente sobre o mês corrente, diminuindo a quantidade de registros e o tempo de busca, transformação e carga. Esse processo pode ser realizado uma vez por dia no período da noite, quando a base de dados não está sendo utilizada ou com utilização menor.
RESULTADOS E DISCUSSÃO
Nesta seção serão apresentados os resultados obtidos com a utilização do DM para construção do painel gerencial. Os testes foram realizados em um PC com processador Intel(R) Core (TM)2 Quad CPU Q8400 2.66GHz, 4 GB de memória RAM, HD de 500 GB, Sistema Operacional Windows 7 Ultimate 64 bits.
A Tabela 2 demonstra as tabelas envolvidas (origem e destino) em cada transformação realizada para o carregamento do DM com seus respectivos tempos de execução. Os tempos são apresentados em duas colunas: uma que mostra o tempo de carga completo que busca as informações de um período de 10 anos, abrangendo todos os períodos de movimentação de dados da organização, e uma coluna de tempo de carga de somente um mês (último mês de movimentação).
Pode-se perceber que o tempo para a carga mensal cai pela metade (em torno dos 15 minutos) em relação à carga completa. O tempo só não é menor ainda pelo fato da tabela que leva mais tempo para ser carregada (FATO_SALDO_AVENCER_VENCIDO) não tem granularidade de ano/mês, precisando assim buscar a movimentação completa em todas as cargas.
Tabelas Origem Tabela Destino
Tempo (carga completa) Tempo (carga mensal)
CADEMP DIM_EMPRESA 1 seg. 1 seg.
GER_PARAMETROS , EST001, EST002, EST003, EST009
FATO_FAT_INSUMOS 4 min. 10 seg.
GER_PARAMETROS
, AGR007 FATO_QTD_AFIXAR 1 min. 6 seg.
GER_PARAMETROS
, EST003 FATO_QTD_ENTREGUE 3 min. 10 seg.
GER_PARAMETROS , EST001, EST002, EST003, EST009, EST024
FATO_FAT_GRUPO_ESTOQUE 11 min. 1 min.
GER_PARAMETROS , EST003, FIN007, DIM_EMPRESA, FATO_QTD_AFIXAR
FATO_SALDO_AVENCER_VENCIDO 14 min. 14 min.
Total: 33:01 min. 15:27 min.
Tabela 2: Tabelas envolvidas na carga do Data Mart
Como é possível ver na Tabela 2, para alimentar a tabela FATO_SALDO_AVENCER_VENCIDO, além das tabelas da base de dados operacional, também foram necessárias duas tabelas da base do DM. Utilizando
essas tabelas foi possível ter um ganho de tempo de execução, pois os dados já estavam processados e agregados.
A Figura 2 apresenta um painel gerencial alimentado pelo DM desenvolvido no trabalho. Todos os gráficos podem ser filtrados em tempo de execução por filial. Sendo assim, ao visualizar o painel, o usuário pode ver os dados de todas as filiais juntas ou individualmente. Nas barras onde o valor está omitido, é possível visualizá-lo ao colocar o mouse sobre o item desejado.
Os gráficos permitem o recurso de drill down, sendo possível ver o detalhamento no nível de registro das tabelas do DM, simplesmente ao dar duplo clique sobre a coluna ou barra do gráfico, como pode ser visto na Figura 3.
O gráfico “A Fixar em Sacas de 60 Kg” em especial possui dois níveis de drill down. A tela demonstrada na Figura 4 mostra um novo gráfico detalhando os dados pelos quatro principais tipos produtos conforme filial determinada no filtro do painel e ano selecionado por meio de duplo clique.
Figura 3: Exemplo de detalhamento no nível de registro
Figura 4: Exemplo de detalhamento gráfico por tipo de produto
Antes, cada vez que executados a partir de base de dados operacional, normalmente quando os dados para a geração dos gráficos eram consultados levavam cerca de 30 minutos para trazer o resultado. Suponha vários gerentes e diretores desejando essa informação ao mesmo tempo. Esse fato poderia sobrecarregar o servidor e levar ainda mais tempo para terminar o processo, suscitando a insatisfação dos usuários pela demora na geração da informação.
Com esses dados carregados no DM já processados, agregados e praticamente sem nenhum aninhamento (junção) de tabelas a ser feito, o seu carregamento é praticamente instantâneo, cerca de 3 segundos, o que mostra um considerável ganho de tempo ao executar o painel gerencial haja vista que a execução anterior demorava cerca de 30 minutos.
É importante frisar que os tempos de execução, tanto do carregamento do DM quanto da execução do painel estão atrelados ao tipo de equipamento utilizado. À medida que forem utilizados computadores com maior capacidade de processamento, esses tempos tendem a ter algum grau de diminuição.
CONCLUSÕES
Este trabalho apresentou o uso de DM para permitir a viabilização do uso de um painel gerencial específico. Foi mostrado que mesmo com a necessidade de um processo de ETL, após a sua carga, o DM consegue reduzir drasticamente o tempo de construção do painel gerencial.
Pelo fato dos dados estarem em uma granularidade máxima de mês, o número de registros dificilmente será expressivo o suficiente para interferir drasticamente na variação do tempo de execução das consultas SQL, independente do tamanho da base origem a ser utilizada. O tempo que terá maior variância será o de carregamento do DM. Porém, como esse é feito apenas uma vez durante o dia, não terá tanto impacto quanto teria se não existisse o DM e o carregamento da base operacional tivesse que ser feito toda vez que se carregasse o painel gerencial.
Os resultados obtidos no estudo de caso mostraram que a utilização do DM foi de grande valia e pretende-se com esse estudo e implementação, utilizar-se dos conhecimentos e vantagens adquiridos para promover a utilização de outros DM para suprir outras demandas de clientes.
Como trabalho futuro pretende-se criar outras tabelas fato com nível de granularidade maior, podendo guardar agrupamentos por cliente, ou mesmo por data, adicionando assim, outras tabelas dimensão como, cliente e tempo. Assim, seria possível criar outros painéis gerenciais para novas perspectivas de gerenciamento, e até mesmo como detalhamento (drill down) do painel já existente, para uma análise mais aprofundada das informações.
REFERÊNCIAS BIBLIOGRÁFICAS
CASTERS, M.; BOUMAN R.; DONGEN J. Pentaho Kettle Solutions: Building Open Source ETL Solutions with Pentaho Data Integration. 2010.
CORREA, F. E. Representação de comercialização agropecuária através de
modelo de Data Warehouse. Dissertação (Mestrado). Escola Politécnica.
Universidade de São Paulo. Departamento de Engenharia de Computação e Sistemas Digitais. Ed. rev. São Paulo, 2010. 71 p.
FARIA, J. M. B. Artefatos da semiótica organizacional na elicitação de
requisitos para soluções de data warehouse. Campinas, Universidade
Estadual de Campinas, Instituto de Computação, 2006, 135 p. Trabalho Final (Mestrado Profissional).
FIREBIRD – Open source database. Disponível em http://www.firebirdsql.org/. Acesso em Nov/ 2011.
KIMBALL, R. Data Warehouse Toolkit. Makron Books, 1998.
Campus, 2002.
PRADO, M. V. Data Warehouse para Apoio a Gestão da Operação em
Empresas do Transporte Rodoviário Interestadual de Passageiros.
Brasília, Departamento de Engenharia Civil e Ambiental, Universidade de Brasília, 2006, 111 p. Dissertação de Mestrado, Publicação T. DM- 009A/2006. PRIMAK, F. V. Decisões com BI (Business Intelligence). Ciência Moderna.
2008.
RAINARDI, V.
TURBAN, E.; SHARDA, R; ARONSON, J. E.; KING, D. Business Intelligence: Um enfoque gerencial para a inteligência do negócio. Bookman Companhia Ed, 2009.
Building a Data Warehouse: With Examples in SQL Server.
Apress´, 2007.
WITTEN, I. H.; FRANK, E. Data Mining: Practical Machine Learning Tools and
