escola superior aberta do brasil – esab curso de engenharia de
Propaganda
ESCOLA SUPERIOR ABERTA DO BRASIL – ESAB CURSO DE ENGENHARIA DE SISTEMAS RACHEL TEREZA MENEGAZZO IMPLEMENTANDO UMA SOLUÇÃO OLAP UTILIZANDO SOFTWARE LIVRE CURITIBA – PR 2009 RACHEL TEREZA MENEGAZZO IMPLEMENANDO UMA SOLUÇÃO OLAP UTILIZANDO SOFTWARE LIVRE Monografia apresentada à ESAB – Escola Superior Aberta do Brasil sob orientação da Professora Beatriz Christo Gobbi. CURITIBA – PR 2009 RACHEL TEREZA MENEGAZZO IMPLEMENTANDO UMA SOLUÇÃO OLAP UTILIZANDO SOFTWARE LIVRE Aprovada em 06 de Abril de 2009. _________________________________ _________________________________ _________________________________ CURITIBA – PR 2009 Á Deus pela vida. A meus pais pelo exemplo e sabedoria. Aos meus filhos Guilherme, Helena e Marcelo que sempre me apoiaram em todas as minhas iniciativas. “Só quem sonha o azul do vôo sabe o seu poder de pássaro. ” (Thiago de Mello) RESUMO As técnicas de construção de data warehouse (DW) e uso ferramentas on-line analytical processing (OLAP) para permitir o armazenamento e consulta a informações estratégicas como apoio ao processo de tomada de decisão têm despertado o interesse de organizações desde os anos 90. De encontro a essa tendência, este estudo tem como objetivo conhecer e apresentar os conceitos necessários para implementação de uma solução OLAP com uso de ferramentas Open Source. Como etapas para alcançar este objetivo, inicialmente foi feita uma abordagem dos conceitos envolvidos e relacionados a OLAP e na sequência os softwares escolhidos para montagem do ambiente operacional foram apresentados conforme utilização específicas através de um estudo de caso. Os autores consultados destacam que OLAP é uma tecnologia recente que tem recebido desenvolvimentos significativos nos últimos 10 anos e que apresenta uma grande diversidade nas possibilidades de aplicação, ficando o sucesso do empreendimento a cargo das decisões a serem tomadas quanto a componentes, arquitetura e projeto. A pesquisa mostra também que existem opções de uso de software livre, possibilitando uma escolha por parte das empresas e órgãos do governo em adotar outras soluções tecnológicas além dos softwares proprietários, usufluindo desta forma de tecnologias de ponta a preços acessíveis para alavancar seu desenvolvimento. Conclui-se colocando que o trabalho contribui para o conhecimento da tecnologia de data warehouse e ferramentas OLAP e sua aplicação no processo de suporte a decisão, possibilitando ao usuário chegar a resultados significativos através de consultas complexas feitas de modo relativamente simples utilizando ferramentas Open Source. LISTA DE FIGURAS Figura 1 - Integração dos dados................................................................................18 Figura 2 - Elementos básicos do data warehouse.....................................................20 Figura 3 - Implementação top-down. .........................................................................25 Figura 4 - Implementação botton up..........................................................................25 Figura 5 - Modelo estrela...........................................................................................30 Figura 6 - Modelo Estrela – visão relacional..............................................................30 Figura 7 - Modelo Floco de Neve ou Snowflake........................................................31 Figura 8 – Cubo.........................................................................................................32 Figura 9 - Hierarquias................................................................................................34 Figura 10 - Exemplo de arquivo XML definindo um schema mondrian .....................41 Figura 11 - Consulta MDX .........................................................................................42 Figura 12 - Resultado da consulta MDX da figura 10. ...............................................43 Figura 13 - Extração de dados ..................................................................................47 Figura 14 - Transformação de dados ........................................................................48 Figura 15 - Script de transformação no Kettle ...........................................................48 Figura 16 - Tipo de alta..............................................................................................51 Figura 17 - Composição da dimensão tempo............................................................51 Figura 18 – Transformação para gerar a dimensão tempo........................................52 Figura 19 - Cubo emergência1.no Schema Workbench............................................53 Figura 20 - Tela do Discoverer – Cubo, medida, dimensões e montagem da consulta ..................................................................................................................................54 Figura 21 - Navigator - Seleção de filtros. .................................................................55 Figura 22 - Table viewer – Atendimento do mês de março/2008 por sexo e de 0 a 6 anos. .........................................................................................................................55 Figura 23 - Resultado de consulta em formato PDF .................................................55 Figura 24 - Gráfico da consulta visualizada na figura 22...........................................56 Figura 25 - Operação drill through.............................................................................57 Figura 26 - Operação slice and dice –perspectiva.A .................................................58 Figura 27 - Operação slice and dice – perspectiva.B ................................................58 Figura 28 - Operação slice and dice – perspectiva.C................................................58 LISTA DE QUADROS Quadro 1 - Faixa etária de sete anos. .......................................................................49 Quadro 2 -Faixa etária de dez anos. .........................................................................49 Quadro 3 - Tempo de permanência. ..........................................................................49 Quadro 4 - Descrição dos capítulos do CID-10. ........................................................50 Quadro 5 - Motivo e tipo de alta ................................................................................51 LISTA DE SIGLAS OLAP On-Line Analytical Processing OLTP On-Line Transaction Processing HOSPUB Sistema de Gestão Hospitalar DM Data Mart DW Data Warehouse ETC Extração, Transformação e Carga de Dados HOLAP Hybrid On-Line Analytical Processing MOLAP Multidimensional On-Line Analytical Processing MS Microsoft ROLAP Relational On-Line Analytical Processing SGBD Sistema de Gerenciamento de Banco de Dados SGBDR Sistema de Gerenciamento de Banco de Dados Relacional SQL Structured Query Language (Linguagem de Consulta Estruturada) XML eXtensible Markup Language MDX Multidimensional Expression (Expressão Multidimensional) API Application Programming Interface (Interface de Programação de Aplicativos) JDBC Java Data Base Conector. CID10 Classificação Estatística Internacional de Doenças e Problemas Relacionados à Saúde – Décima revisão. SUMÁRIO 1 INTRODUÇÃO ................................................................................12 2 DATA WAREHOUSE.......................................................................16 2.1 CARACTERÍSTICAS ...............................................................16 2.1.1 Orientação por Assunto.....................................................................17 2.1.2 Variação no tempo .............................................................................17 2.1.3 Não volatilidade..................................................................................18 2.1.4 Integração ...........................................................................................18 2.2 COMPONENTES E ARQUITETURAS DE IMPLEMENTAÇÃO.19 2.2.1 2.2.1.1 Sistemas operacionais de origem ................................................20 2.2.1.2 Data staging area.........................................................................21 2.2.1.3 Área de apresentação de dados ..................................................22 2.2.1.4 Ferramentas de acesso a dados..................................................22 2.2.1.5 Papeis..........................................................................................23 2.2.2 3 Arquiteturas ........................................................................................23 2.3 GRANULARIDADE DE DADOS...............................................25 2.4 METADADOS...........................................................................26 MODELAGEM PARA DATA WAREHOUSE ...................................28 3.1 4 Componentes .....................................................................................19 MODELAGEM MULTIDIMENSIONAL OU DIMENSIONAL......28 3.1.1 Modelo Estrela ou Star.......................................................................29 3.1.2 Modelo floco de neve ou snowflake .................................................31 3.1.3 Cubos ..................................................................................................31 3.1.4 Fatos....................................................................................................32 3.1.5 Dimensões ..........................................................................................33 3.1.6 Medidas...............................................................................................33 3.1.7 Hierarquias .........................................................................................34 ON-LINE ANALYTICAL PROCESSING .........................................35 4.1 ARQUITETURAS .....................................................................36 4.2 OPERAÇÕES BÁSICAS OLAP ...............................................36 4.2.1 Slice-and-Dice.....................................................................................37 4.2.2 Drill Down e Roll Up ...........................................................................37 4.2.3 Drill Across .........................................................................................37 4.2.4 Drill Through.......................................................................................37 4.3 5 6 TECNOLOGIAS .......................................................................38 4.3.1 Kettle ...................................................................................................38 4.3.2 Mondrian .............................................................................................38 4.3.2.1 Camada de apresentação............................................................39 4.3.2.2 Camada Dimensional...................................................................39 4.3.2.3 Camada Estrela ...........................................................................39 4.3.2.4 Camada de armazenamento .......................................................40 4.3.3 Mondrian Schema Workbench ..........................................................41 4.3.4 Mdx (Multidimensional Expressions) ...............................................41 4.3.5 Jrubik ..................................................................................................43 4.3.6 MySql...................................................................................................44 ESTUDO DE CASO ........................................................................45 5.1 NECESSIDADE .......................................................................45 5.2 IDENTIFICANDO A FONTE DOS DADOS:..............................46 5.3 EXTRAÇÃO, TRANSFORMAÇÃO E CARGA - ETC ...............47 5.4 IMPLEMENTANDO OS CUBOS OLAP. ...................................52 5.5 CONSULTAS............................................................................53 CONCLUSÃO .................................................................................59 12 1 INTRODUÇÃO Data Warehouse Modelagem Multidimensional On-Line Analytical Processing Trabalhar a informação, do nível operacional ao estratégico, é um aspecto relevante para alcançar o sucesso em todas as áreas de atuação. A necessidade de respostas confiáveis, em tempo hábil, que retratem a realidade ou apontem caminhos futuros tem movimentado profissionais de tecnologia da informação na busca de novas soluções tecnológicas. Na área da saúde, especificamente falando do Sistema Único de Saúde, onde são utilizados uma variedade de sistemas de informação atendendo aos níveis Federal, Estadual e Municipal, encontramos sistemas que em sua maioria utilizam diferentes bancos de dados, transacionais, atendendo basicamente o nível operacional e administrativo. A decisão de escrever este trabalho foi a partir da constatação da grande dificuldade quem encontrei como profissional da área de tecnologia da informação, diante da necessidade de extrair dados gerenciais de bancos de dados transacionais. É comum nesta área de atuação, Sistema Único de Saúde, encontrar em um único ambiente operacional integrados, vários sistemas informatizados, quase sempre não atendendo basicamente as necessidades operacionais diárias e imediatas de informação. A integração dos sistemas fica difícil e trabalhosa, visto a diversidade de soluções implementadas ao longo do tempo, sendo muitas vezes inviabilizada por barreiras tecnológicas e a forma como se encontram pulverizados os sistemas nas esferas municipal, estadual e federal. Então, um simples relatório gerencial, para ser realizado em tempo hábil mobiliza um exército de pessoas, utilizando recursos rudimentares e deixando no ar a incerteza dos resultados. Por isso, o processo de extração, tratamento e armazenamento de dados, bem como o processo de modelagem multidimensional para permitir a maximização de consultas com o uso de ferramentas OLAP despertou grande curiosidade e interesse. 13 Pode-se dizer que a situação encontrada no Sistema Único de Saúde é bastante similar a descrita por Machado (2008,p.26), como justificativa para a aplicação de tecnologia de Data Warehouse, a seguir: Várias plataformas de hardware e software. Constantes alterações nos sistemas transacionais corporativos. Dificuldade acentuada na recuperação de dados históricos em períodos superiores ao ano atual de operações. Existência de sistemas “pacotes” de fornecedores diferentes. Falta de padronização e integração dos dados existentes nos diversos sistemas. Carência de documentação e segurança no armazenamento dos dados. Dificuldade de aplicação de sistemas EIS ou DSS devido a dependências múltiplas de sistemas corporativos. Tomando como base o modelo de montagem de Data Warehouse apresentado, a empresa pode optar por construí-lo em uma base global ou em bases teoricamente locais de acordo com as áreas de negócios. Isso implica na utilização de arquiteturas específicas para a construção de um Data Warehouse, as quais têm evoluído desde o princípio dos conceitos de Data Warehouse até o momento atual sempre em busca do sucesso de sua utilização. Frente a este cenário, o presente trabalho tem como objetivo geral conhecer conceitos e um conjunto de ferramentas para implementação de uma solução OLAP (On-Line Analytical Processing), utilizando software livre. Como etapas para alcançar este objetivo, inicialmente é feita abordagem dos conceitos envolvidos e relacionados a OLAP e na sequência os softwares escolhidos para montagem do ambiente operacional são apresentados conforme utilização específicas de cada um e demonstrados os exemplos de um estudo de caso. Como escopo do trabalho os processos de um Data Warehouse e ferramentas OLAP. Como processos a extração dos dados dos sistemas legados, a organização e integração desses dados para compor o data warehouse e o acesso aos dados para consultas de forma rápida e extremamente flexível, auxiliando como ferramenta de apoio a tomada de decisão. Para exemplificar a utilização das ferramentas é apresentado um estudo de caso sobre um hospital público, limitando o escopo de aplicação e demonstração de uso das ferramentas ao setor de atendimento de emergência hospitalar. Por questões de segurança das informações é utilizado uma base de dados onde os dados pessoais 14 dos pacientes, como nomes e endereços, são omitidos. O hospital em questão é o Hospital do Trabalhador, referência em atendimentos ao trauma na região de Curitiba, estado do Paraná, que nos últimos cinco anos vem se informatizando e fazendo uso de um sistema de gestão hospitalar, HOSPUB, desenvolvido e disponibilizado pelo Ministério da Saúde. Para possibilitar consultas que atendam a níveis estratégicos da administração e prover a direção do hospital de respostas hoje não disponíveis nos sistemas tradicionais em tempo real, também conhecidos como On-Line Transaction Processing (OLTP), a construção de um ambiente de data warehouse tornou-se uma opção viável técnica e financeiramente, considerando a opção do uso de software Open Source. Os softwares utilizados para compor o ambiente com o propósito de demonstrar a utilização da técnica Olap são independentes de plataforma e são: Para o processo de extração, transformação e carga dos dados é utilizado ferramentas do Projeto Kettle, onde Kettle é um acrônimo para "Extraction , Transformation, Transportation and a Loading Data Enviroment". Como banco de dados é utilizado o MySql e como ferramentas para modelagem e apresentação dos dados são utilizados o Mondrian Workbench e Jrubik, respectivamente. A escolha desse conjunto de ferramentas é unicamente para possibilitar a construção de um ambiente teste para o propósito da pesquisa e possível de implementação dado o escasso período de tempo disponível para a sua conclusão. Entretanto, vale ressaltar que outras opções de softwares podem ser compostas, como por exemplo a utilização combinada do servidor Mondrian com o jPivot. Este trabalho é realizado através de uma combinação do Método de pesquisa exploratória bibliográfica e estudo de caso, o qual representa mais do que uma exemplificação da aplicação dos conceitos na utilização de algumas ferramentas. Representa também as necessidades de informação que dão sentido a construção de um data warehouse como ambiente de apoio ao planejamento estratégico e 15 operacional de uma empresa ou organização. 16 2 DATA WAREHOUSE Segundo Inmon (1997, p. 33, p.37), Data Warehouse é uma coleção de dados baseada em assuntos, integrados, variáveis com o tempo e não voláteis, para dar suporte ao processo de tomada de decisão. Data Warehouse também pode ser entendido como um conjunto de tabelas (banco de dados) contendo dados extraídos dos sistemas de operação ou transacionais da empresa, podendo ter adicionado a este contexto informações provenientes de documentos, planilhas eletrônicas e outras fontes objetivando fornecer e apresentar um repositório único, consolidado, como representação do negócio da empresa para processamento de consultas que apoiam o processo de tomada de decisão. Machado (2008,pag.27) contextualizando o Data Warehouse afirma que: [...], o Data Warehouse proporciona uma sólida e concisa integração dos dados da empresa, para a realização de análises gerenciais estratégicas de seus principais processos de negócio. Ele se preocupa em integrar e consolidar as informações de fontes internas, na maioria das vezes heterogêneas, e fontes externas, sumarizando, filtrando e limpando esses dados, preparando-os para análise e suporte à decisão. Como subconjuntos lógicos de um data warehouse, tem-se os data marts, contextualizado na Wikipedia (2008) como: O Data Warehouse é normalmente acessado através de Data Marts, que são pontos específicos de acesso a subconjuntos do Data Warehouse. Os Data Marts são construídos para responder prováveis perguntas de um tipo específico de usuário. Por exemplo: um Data Mart financeiro poderia armazenar informações consolidadas dia-a-dia para um usuário gerencial e em periodicidades maiores (semana, mês, ano) para um usuário no nível diretoria. 2.1 CARACTERÍSTICAS Em Machado (2008,pag.27) pode-se encontrar as características que diferenciam um Data Warehouse de sistemas convencionais: Extração de dados de fontes heterogêneas (existentes ou externas); 17 Transformação e integração dos dados antes de sua carga final; Normalmente requer máquina e suporte próprio; Visualização dos dados em diferentes níveis. Os dados do Data Warehouse podem ou não ser extraídos para um nível mais específico, os Data Marts, e a partir deste para um banco de dados individual; Utilização de ferramentas voltadas para acesso com diferentes níveis de apresentação; Dados somente são inseridos, não existindo atualização, ou melhor, updates. Outras características de um data warehouse podem ser citadas como orientação por assunto, variação no tempo, não volatilidade e integração. 2.1.1 Orientação por Assunto Um data warehouse armazena os dados obedecendo uma orientação por assunto. Isto indica sua derivação dos principais processos de negócio da organização. No modelo de dados corporativo é onde pode-se encontrar as representações dos principais processos de negócios de uma empresa. E por esse motivo Inmon (2000) afirma que o modelo corporativo de dados é um lugar muito bom para iniciar o processo de construção do Data Warehouse. Para Machado (2008, p.29) enquanto o projetista de sistemas transacionais tem o foco em projeto de banco de dados, projeto dos processos transacionais e suas atividades e controles operacionais, o projetista de data warehouse tem o foco em modelagem dos dados e projeto de banco de dados. No data warehouse “[...] somente interessam dados que sejam importantes para a tomada de decisões, que sejam relativos à análise e desempenho de processos ou atividades críticas [...]”. (MACHADO,2008, p.29). 2.1.2 Variação no tempo Os dados de um Data Warehouse representam resultados operacionais de um 18 momento de tempo, o momento em que foram capturados. É um conjunto estático de registros referentes a um período de tempo, onde a data é elemento essencial e componente chave, que norteia a organização do armazenamento e da pesquisa. Esse elemento é obrigatoriamente trabalhado na composição dos dados de um data warehouse. 2.1.3 Não volatilidade Basicamente um Data Warehouse tem duas operações de dados: carga (inicial + incremental) e consulta aos dados. Desse modo difere de um sistema transacional, pois não tem operações como atualizações registro a registro ou bloqueio por concorrência no acesso. O acesso a seus dados é apenas como leitura. 2.1.4 Integração A Integração proporciona a unicidade de informações. É no processo de extração, filtragem e agregação que os dados provenientes de diferentes fontes são integrados, proporcionando conformidade ao Data Warehouse considerando também os aspectos de padronização e granularidade dos dados. (fig. 1). Figura 1 - Integração dos dados. 19 2.2 COMPONENTES E ARQUITETURAS DE IMPLEMENTAÇÃO. 2.2.1 Componentes A arquitetura de um data warehouse apresenta um conjunto de componentes , onde cada um assume uma função específica no ambiente como mecanismos de armazenamento e comunicação de dados, ferramentas de extração, carga, consulta e apresentação de dados. Conforme a figura 2, um modelo padrão de ambiente de DW considera quatro componentes separados e distintos: sistemas operacionais de origem, data staging area, área de apresentação de dados e ferramentas de acesso a dados. 20 Figura 2 - Elementos básicos do data warehouse Fonte: Adaptado de Kimball e Ross (2002). Como descrito pelos autores Kimball e Ross (2002, p.7), “Cada componente do data warehouse atende a uma função específica. E precisamos aprender a importância estratégica de cada um deles e como controlá-los de modo eficiente para obtermos êxito com o data warehouse.”. 2.2.1.1 Sistemas operacionais de origem São os sistema onde são gravadas as transações dos negócios da empresa e a origem dos dados que povoarão o data warehouse. Os sistemas de origem estão fora do data warehouse e geralmente as pessoas responsáveis pelo data warehouse tem pouco ou nenhum controle sobre o conteúdo ou formato dos dados nos sistemas de origem. Mesmo assim é interessante considerar e aproveitar o estudo e discussão realizada durante o processo de modelagem do data warehouse sobre a origem dos dados como possibilidade de reengenharia dos sistemas transacionais visando facilitar o desenvolvimento do data warehouse. 21 Segundo Inmon (2000) , o modelo corporativo de dados é um lugar muito bom para iniciar o processo de construção do Data Warehouse, devido a facilitar a identificação das tabelas de fatos. Portanto, caso o modelo de dados corporativo não exista deve ser construído considerando principalmente os tópicos: Em particular, o modelo corporativo de dados deve ter identificado e estruturado - pelo menos - o seguinte: ● ● ● os principais assuntos da empresa; as relações entre os assuntos; a criação de um ERD (diagrama entidade relacionamento); para cada área temática: ● as chaves (s) do sujeito, os atributos do sujeito, os subtipos do sujeito, os conectores de um tema para a próxima, o agrupamento dos atributos. ● ● ● ● 2.2.1.2 Data staging area Este componente compreende uma área de armazenamento de dados e os processos de extração, transformação e carga (ETC). Essa área de armazenamento de dados representa um repositório intermediário para armazenamento e processamento dos dados extraídos dos sistemas de origem dos dados, transacionais, e outras fontes, onde os dados poderão receber o tratamento necessário antes de ser realizada a carga para o data warehouse. A partir do modelo de dados corporativo, Inmon(2000) estabelece alguns passos para iniciar a transformação dos dados, sendo : ● ● ● ● a remoção de dados de natureza puramente operacional; a adição de um elemento de tempo para as principais estruturas do Data Warehouse; adição apropriada de dados derivados; a transformação de dados de relacionamentos em dados de artefatos; 22 ● ● ● ● Kimball (2002,p.8) acomodação de diferentes níveis de granularidade; fusão de dados de diferentes tabelas; criação de matrizes de dados; separação de atributos de dados de acordo com características de estabilidade. ressalta que “Um requisito arquitetural chave para um data staging area é estar fora do limite dos usuários e não fornecer serviços de consulta.” Comparativamente, Kimball descreve um data staging área como uma cozinha de um restaurante, onde os alimentos crus vão ser preparados e transformados em um prato palatável para o cliente, assim como os dados também o serão para os usuários. 2.2.1.3 Área de apresentação de dados Área de apresentação de dados é a área onde os dados estão armazenados, sendo o próprio repositório de dados do data warehouse e data marts, de forma organizada e disponíveis para softwares consultas de usuários através de geradores de relatórios e ferramentas analíticas de dados. O data warehouse é a espinha dorsal do ambiente, constituindo uma enorme base de dados históricos sobre assuntos de negócios institucionais de períodos que são geralmente acima de três anos, permitido desta forma análises onde possam ser identificadas indicadores e tendências. Os data marts representam um subconjunto lógico de dados do DW. Seus dados são direcionados a uma área específica de negócios da empresa e tem como principal vantagem permitir uma rápida implementação, como maior envolvimento do usuário final e menor tempo de retorno de resultados. 2.2.1.4 Ferramentas de acesso a dados. 23 Ferramentas de acesso a dados são todas as ferramentas que podem ser oferecidas aos usuários do data warehouse ou data mart para uso na realização de consultas a essas bases. Variam de simples ferramentas para realização de consultas ad hoc até sofisticadas ferramentas de modelagem e aplicações para realização de data mining. 2.2.1.5 Papeis No ambiente de data warehouse um dos aspectos importantes a se observar é o conjunto de usuários envolvidos no processo de construção e manutenção do DW e que podem ser agrupados conforme a função específica de cada um, conforme a seguir: Analistas responsáveis pela carga dos dados – conhecem o mapeamento entre os sistemas de origem dos dados e o DW além das transformações necessárias para filtragem e integração dos dados; usuários finais – são os usuários que conhecem os termos de negócios da organização e irão explorar os dados do DW para resolução de problemas apresentados. São os especialistas, gerentes e administradores; analistas responsáveis pelo desenvolvimento e manutenção do data warehouse e data marts – profissional responsável pelas arquiteturas de armazenamento e dados e metadados; administradores de dados - tem o papel de integrador dos ambientes transacional e dimensional garantindo a integridade e qualidade dos dados nos processos de manutenção de extração, transformação e carga do DW. 2.2.2 Arquiteturas Machado (2008,p.50) apresenta três tipos de arquitetura para data warehouse: arquitetura global, arquitetura de data marts independentes e arquitetura de data marts integrados. 24 Na arquitetura global, o data warehouse trata a empresa como um todo, com foco em visões corporativas. Consequentemente tem-se menor redundância, maior consistência e integração dos dados. Sua implementação é bastante demorada e de elevado custo. Na arquitetura de data marts independentes, as bases são construídas sem foco corporativo, atendendo as necessidades específicas e departamentais de seus usuários. Cada departamento ou área da empresa constroi e gerencia o seu data mart, o que pode resultar em maior redundância e menor integração dos dados. Este tipo de implementação não tem grande impacto financeiro e é rápidamente implementado. A arquitetura de data marts integrados é uma combinação das duas anteriormente citadas como coloca Machado (2008,p.51): A arquitetura de Data Marts integrados é basicamente uma distribuição de implementação. Apesar de os Data Marts serem implementados separadamente por grupos de trabalho ou departamentos, eles são integrados ou interconectados, provendo uma visão corporativa maior dos dados e informações. De fato o alto nível de integração é similar ao da arquitetura global. Por outro lado, os usuários de um departamento podem acessar e utilizar os dados de um Data Mart de outro departamento. No processo de implementação de uma arquitetura de data warehouse, pode-se adotar uma das seguintes abordagens: top down, botton up ou intermediária. Na implementação top down a empresa é forçada a definir regras de negócio de forma corporativa antes de iniciar o projeto de data warehouse, o que torna o processo de implementação longo e difícil porque as decisões são tomadas em conjunto por todos os departamentos envolvidos. Em contrapartida os data marts resultantes utilizarão a arquitetura e os dados do data warehouse manutenção (fig. 3). facilitando a 25 Origem 1 Data Mart 1 Origem 2 Data Mart 2 Data Warehouse Data Mart 3 Origem 3 Figura 3 - Implementação top-down. Na implementação botton up a contrução de um data warehouse é incremental a partir de data marts independentes. Segundo Machado (2008,p.54) “ Um dos grandes problemas dessa implementação é a falta de um gerenciador que garanta padrões únicos de metadados, mesmo com a independência dos Data Marts.” Caso não seja adotada forma de controlar esse problema, podem ocorrer falhas no processo de padronização, ocasionando redundâncias e inconsistências entre os data marts. (fig. 4). Origem 1 Data Mart 1 Origem 2 Data Mart 2 Origem 3 Data Mart 3 Data Warehouse Figura 4 - Implementação botton up Na implementação intermediária ocorre uma combinação entre as duas abordagens anteriormente citadas. Inicialmente faz-se uma macro modelagem de dados do data warehouse e no momento seguinte a escolha das partes mais importantes para iniciar a implementação dos data marts. Dessa forma tem-se a garantia da consistência e integração dos dados. 2.3 GRANULARIDADE DE DADOS Para Machado (2006, p. 59), granularidade de dados refere-se “[...] ao nível de 26 sumarização dos elementos e de detalhes disponíveis nos dados, considerado o mais importante aspecto do projeto de um Data Warehouse.”. A granularidade dos dados é considerada importante porque afeta diretamente o volume de dados no Data Warehouse e também porque determina o nível de detalhe que uma consulta poderá atingir. Outro aspecto importante referente à granularidade é a performance que poderá variar devido ao volume de dados. Quanto mais detalhes, mais baixo o nível de granularidade. Quanto menos detalhes maior o nível de granularidade. Um aspecto importante destacado por Machado (2008, p.60) é que “Quando a granularidade de um Data Warehouse ou de um Data Mart é estabelecida, o projeto tem seu desenvolvimento com foco e irá fluir com mais tranquilidade. Enquanto não for visualizado o nível de granularidade, torna-se muito difícil a modelagem do Data Mart.” Outro aspecto é a utilização da mesma granularidade definida para um determinado assunto de negócio em todos os DM ou DW onde se faça referência a este assunto como forma de garantir a integração dos dados e permitir análises de negócios comparativas. Desta forma tem-se dois fatores a serem considerados na definição da granularidade: tempo e forma de agrupamento das informações. 2.4 METADADOS Os metadados são dados sobre os dados de um banco de dados e estão organizados em dicionário digital de dados, para facilitar a documentação, estruturação e manutenção dessas informações. Na definição da enciclopédia Wikipédia (2008) metadados “[...] , ou Metainformação, são dados sobre outros dados. Um item de um metadado pode dizer do que se trata aquele dado, geralmente uma informação inteligível por um computador.” 27 Em Machado (2008,p.299) metadados é definido como um repositório do DW onde “[...] dados de mais alto nível descrevem os dados de níveis inferiores que compõem a estrutura do Data Warehouse.” Os metadados, organizados em um dicionário de informações, descrevem informações não somente dos dados armazenados no DW como formato, tamanho, tipo, nome e alias como o dado é conhecido, mas também guardam informações sobre fontes geradoras e o destino dos dados; as regras de transformação aplicadas no processo de extração, limpeza e agrupamento de dados dos sistemas origem; os níveis de acesso dos usuário; as informações referentes a atualização dos dados, possibilitando ao usuário conhecer a atualidade dos dados e periodicidade de atualização dos dados. 28 3 MODELAGEM PARA DATA WAREHOUSE Kimball (1997), afirma que modelagem multidimensional é uma técnica de modelagem de dados viável para suportar consultas de usuários a um data warehouse, e que embora a modelagem entidade relacionamento seja muito útil na captura de transações e administração de dados na fase de construção de um data warehouse, deve-se evitar repassar este modelo ao usuário final. Modelagem entidade e relacionamento é uma técnica de modelagem lógica que busca remover redundâncias de dados. Como conseqüência, no processo de implementação temos consistência e agilidade nos processamentos transacionais. Entretanto, na busca do modelo relacional perfeito, cria-se bases de dados onde as consultas para serem realizadas necessitam de uma quantidades de junções entre tabelas que muitas vezes são inviáveis de serem realizadas devido ao grande número de tabelas originadas no processo de normalização. Kimball (1997) afirma que essa situação é muito mais do que um incômodo, e aponta três motivos porque não se pode usar diretamente a modelagem entidade relacionamento em Data Warehouse: 1) Usuários finais podem não entender ou relembrar sobre modelo entidade relacionamento e não conseguir navegar completamente por um modelo entidade e relacionamento. 2)Não existe uma interface gráfica para usuário finais que disponibilize o modelo entidade relacionamento como um todo para uso; 3)O uso da técnica de modelagem entidade relacionamento coloca por terra as principais atrações de um data warehouse, a saber recuperação de dados de forma intuitiva e de alta-performance. 3.1 MODELAGEM MULTIDIMENSIONAL OU DIMENSIONAL Em Machado (2008), modelagem multidimensional é definida como sendo : [...] uma técnica de concepção e visualização de um modelo de dados de um conjunto de medidas que descrevem aspectos comuns de negócios. É utilizada especialmente para sumarizar e reestruturar dados e apresentá-los 29 em visões que suportem a análise dos valores desses dados. Definindo o modelo multidimensional, Kimball (1997) descreve-o como: [...] uma técnica de modelagem de dados que busca apresentar os dados em determinado padrão, apresentando uma estrutura intuitiva que permite acesso de alta-performance. É naturalmente dimensional e segue a disciplina que usa o modelo relacional com algumas importantes restrições. Todo modelo dimensional é composto de uma tabela com chave múltipla , chamada de tabela de fatos, e um conjunto de pequenas tabelas chamadas de tabelas de dimensões. Cada tabela de dimensão tem uma chave primária simples que corresponde exatamente a um dos componentes da chave múltipla na tabela de fatos. A tabela de fatos, por ter uma chave múltipla, tem uma ou mais chaves estrangeiras, sempre expressas em relacionamentos muitos-para-muitos. A tabela de fatos contém também uma ou mais medidas numéricas, ou fatos, que ocorrem da combinação de chaves que definem cada registro. A modelagem multidimensional é aplicada tanto para bancos relacionais quanto para bancos multidimensionais. O projeto lógico é o mesmo. A implementação é diferenciada. Existem duas formas de modelagem multidimensional, o modelo estrela ou star e o modelo floco de neve ou snowflake. 3.1.1 Modelo Estrela ou Star Machado (2008, pag.93) define o modelo estrela como: O modelo estrela é a estrutura básica de um modelo de dados multidimensional. Sua composição típica possui uma grande entidade central denominada fato (fact table) e um conjunto de entidades menores denominadas dimensões (dimension tables), arranjadas ao redor dessa entidade central, formando uma estrela, [...] No centro da estrela fica o fato Atendimentos de Emergência e ao seu redor, nas pontas da estrela as dimensões faixa etária, médico, Cid10, motivo de atendimento e 30 tempo(fig. 5) Figura 5 - Modelo estrela. Numa visão relacional do modelo estrela, a tabela de fatos ao centro e as tabelas das dimensões nas extremidades. (fig. 6) Figura 6 - Modelo Estrela – visão relacional Neste modelo, os relacionamentos entre a entidade fato e as dimensões são expressas em relacionamentos de um para muitos no sentido da entidade dimensão 31 para a entidade fato. As dimensões com várias hierarquias são apresentadas em uma única tabela desnormalizada. 3.1.2 Modelo floco de neve ou snowflake O modelo floco de neve diferencia-se do modelo estrela por apresentar em uma ou mais dimensões, hierarquias decompostas, normalizadas. (fig.7) Figura 7 - Modelo Floco de Neve ou Snowflake 3.1.3 Cubos Popularmente usa-se o cubo ou hipercubo (fig. 8) para uma representação visual das possíveis dimensões de um fato em um modelo multidimensional. A intenção é demonstrar que um fato pode ser visualizado sob diversas óticas e níveis de hierarquias. 32 Figura 8 – Cubo Fonte: http://www.bi4all.pt Machado (2008,p.83) afirma que “A razão de utilizar um cubo para expressar essa nova realidade de sistemas é transmitir a idéia de múltiplas dimensões. Cubos podem ter 2, 3, 4 ou qualquer outro número de dimensões.” 3.1.4 Fatos Um fato é um conceito de interesse primário para o processo de tomada de decisões e corresponde aos eventos que ocorrem de forma dinâmica no negócio da empresa. É a tabela primária do modelo dimensional e nela são armazenados os valores do negócio que se deseja analisar (MICROSOFT, 2007). Um fato é representado basicamente por valores numéricos e implementado em tabelas denominadas tabelas de fato (fact tables). Medidas ou métricas são os atributos numéricos que representam um fato. Machado(2008) afirma que um fato satisfaz a três características básicas: 1)varia ao longo do tempo; 2)possui valores numéricos de avaliação e; 3)seu histórico pode ser mantido e cresce com o passar do tempo. Exemplificando, “Qual o índice de pacientes atendidos na emergência, em 33 determinado hospital, com indicação para internação no ano de 2008?” é um fato. Primeiramente, varia ao longo do tempo; segundo, possui valores numéricos de avaliação e; terceiro, mantem um histórico que pode ser mantido e cresce com o passar do tempo. 3.1.5 Dimensões Dimensões são as formas de apresentação, visualização ou participação dos dados determinando o contexto de um assunto de negócios. Generalizando o exemplo acima sobre pacientes atendidos na emergência de um hospital, o fato atendimento de emergência pode ter como dimensões tempo, faixa etária, motivos de atendimento, pacientes, médicos, procedimentos. No processo para se identificar as dimensões de apresentação de um fato, Machado(2008,pag. 111 ) afirma que : Todo e qualquer fato possui sempre quatro pontos de referência, que podemos denominar de quatro pontos cardeais de um fato. Os elementos que participam de um fato, seja qual for ele, no mínimo são estes quatro: Onde aconteceu o fato. Quando aconteceu o fato. Quem executou o fato. O que é objeto do fato. Quando uma dimensão possui um número enexpressível de ocorrências que não justifique sua implementação como uma entidades, ela pode ser implentada como atributo de um fato, recebendo o nome de dimensão mascarada. 3.1.6 Medidas Medidas são fundamentais na criação de fatos. Através delas podemos analisar o 34 desempenho de alguma atividade ou negócio. As medidas se classificam em 2 tipos: valores aditivos e valores não aditivos. Machado(2008,pag.134) apresenta a seguinte definição: Valores aditivos: são aqueles referentes ao fato sobre os quais podem ser aplicadas as operações de soma, subtração e média. Os valores, como, por exemplo, “número de crimes” e “número por tipo de transplante” representam valores aditivos. Valores não aditivos: referentes aos fatos que não podem ser manipulados livremente, como valores percentuais ou relativos. Na realidade representam os indicadores de desempenho do fato. 3.1.7 Hierarquias As dimensões são estruturas de dados que possuem hierarquias implícitas que são fundamentais para a construção de um modelo multidimensional. Como exemplo, considerando a dimensão localização (fig. 9), que estabelece critérios para agregação e sumarização dos valores do fato objeto de análise em espaços geográficos como região, estado e cidade. Figura 9 - Hierarquias 35 4 ON-LINE ANALYTICAL PROCESSING OLAP (On-Line Analytical Processing) é definido como um conjunto de ferramentas projetadas para dar suporte a análise e consultas ad hoc. Seus usuários, analistas e executivos, conseguem de forma rápida e intuitiva realizar comparações, pesquisas e análises históricas em grandes bases de dados. Segundo Inmon, Consultas Ad-hoc são consultas com acesso casual único e tratamento dos dados segundo parâmetros nunca antes utilizados, geralmente executado de forma iterativa e heurística. Em outras palavras, a possibilidade do próprio usuário gerar consultas de acordo com suas necessidades de cruzar as informações de uma forma não vista e com métodos que o levem a descoberta daquilo que procura. (INMON, apud Geocities, 2009). Em Wikipedia(2009), OLAP é definida como: “ [...] a capacidade para manipular e analisar um largo volume de dados sob múltiplas perspectivas . As aplicações OLAP são utilizadas pelos gestores em qualquer nível da organização para lhes permitir análises comparativas que facilitem a sua tomada de decisões diária.” A característica principal dos sistemas OLAP é definida como: “A característica principal dos sistemas OLAP é permitir uma visão conceitual multidimensional dos dados de uma empresa. É natural, fácil e intuitiva, permitindo a visão em diferentes perspectivas dos negócios da empresa e desta maneira tornando o analista um explorador da informação (MINERAÇÃO DE DADOS-UEM,2009). “ Outras importantes características dos sistemas OLAP são: • • • • Análise de tendências. A tecnologia OLAP é mais do que uma forma de visualizar a história dos dados. Deve, também, ajudar os usuários a tomar decisões sobre o futuro, permitindo a construção de cenários ( "e se ...") a partir de suposições e fórmulas aplicadas, pelos analistas, aos dados históricos disponíveis ; Busca automática (reach-through) de dados mais detalhados que não estão disponíveis no servidor OLAP. Detalhes não são normalmente importantes na tarefa de análise mas quando necessários, o servidor OLAP deve ser capaz de buscá-los; Dimensionalidade genérica; Operação trans-dimensional. Possibilidade de fazer cálculos e manipulação de dados através diferentes dimensões; 36 • • Possibilidade de ver os dados de diferentes pontos de vista (slice and dice), mediante a rotação (pivoting) do cubo e a navegação (drill-up/drill-down) entre os níveis de agregação; Conjunto de funções de análise e cálculos não triviais com os dados. (MINERAÇÃO DE DADOS-UEM,2009) 4.1 ARQUITETURAS Sao categorizadas de acordo com a forma de armazenamento dos dados a serem consultados. As arquiteturas existentes são: Multidimensional On-Line Analytical Processing (MOLAP), Relational On-Line Analytical Processing (ROLAP), Desktop On-Line Analytical Processing (DOLAP) e Hybrid On-Line Analytical Processing (HOLAP). No modo MOLAP (Multidimensional OLAP) os dados no servidor OLAP são armazenados em estruturas otimizadas para acesso multidimensional. Geralmente essas estruturas são formadas por arrays multidimensionais. No modo ROLAP (Relational OLAP) os dados no servidor OLAP são armazenados em bancos de dados relacionais. No modo HOLAP (Hybrid OLAP) – os dados no servidor MOLAP são armazenados em formato multidimensional, mas se existirem mais do que algumas dimensões, esses dados ficaram esparsos, e o servidor não terá um bom desempenho. Um Servidor HOLAP resolve este problema deixando os dados mais granulares armazenados em um banco de dados relacional e os dados agregados no formato multidimensional. 4.2 OPERAÇÕES BÁSICAS OLAP As operações básicas com as ferramentas OLAP são: 37 4.2.1 Slice-and-Dice Segundo Machado (2008, pag 90), “Significa definir um pedaço da base, uma “mordida”, como universo para uma consulta. Slice and dice é o mesmo que filtrar.” Esta técnica permite-nos analisar as informações sobre os mais diferentes prismas, onde consegue-se ver a informação sobre ângulos antes inimagináveis sem a construção de um DW e a utilização de uma ferramenta OLAP. 4.2.2 Drill Down e Roll Up Consiste em fazer uma exploração em diferentes níveis de detalhe ou hierarquias das informações de uma dimensão. Com o Drill Down pode-se aumentar o nível de detalhe da informação. Com o Roll Up o processo é inverso, sendo o nível de detalhamento diminuído e a granularidade aumentada. 4.2.3 Drill Across Consiste em passar um nível hierárquico intermediário de uma mesma dimensão. Como exemplo, considerando uma dimensão tempo composta de ano, semestre, trimestre, mês e dia, o usuário executa um drill across quando passa direto para semestre. 4.2.4 Drill Through Consiste em passar de uma informação contida em uma dimensão para uma outra dimensão. É importante observar que essas operações podem ser utilizadas de forma 38 combinadas, onde busca-se descobrir comportamentos sob diferentes perspectivas de análise dos dados. 4.3 TECNOLOGIAS As principais softwares abordadas neste trabalho compõem um conjunto de ferramentas open source disponíveis para ambiente de business intelligence (BI) no mercado mundial. Entre elas temos as ferramentas do Pentaho com o projeto Kettle, que abrange a área de extração, transformação e carga de dados para data warehouse; o projeto Mondrian como servidor OLAP, incluindo o Mondrian Schema Workbench para definição e mapeamento do modelo lógico multidimensional. Para formulação e visualização das consultas optou-se pelo Jrubik , também open source, com desenvolvimento baseado no Jpivot1, pela facilidade e rapidez de configuração 4.3.1 Kettle Kettle é um conjunto de ferramentas Open Source, para extração, transformação e carga de dados em um data warehouse. O nome Kettle é uma acrônimo para “Kettle Extraction, Transformation,.T.ransportation and Loading Enviroment” . O acesso às funcionalidades desse ambiente é através de uma interface gráfica chamada Spoon. Nesta interface gráfica as transformações são construídas e programadas para ação sobre os dados que podem ser de várias origens e para vários destinos bem como de diversos formatos. 4.3.2 Mondrian Mondrian é um servidor OLAP Open Source, escrito em Java, que executa 1 JPIVOT. A JSP based OLAP. Disponível em: < http://jpivot.sourceforge.net>. 39 consultas escritas na linguagem MDX, fazendo a leitura dos dados a partir um banco de dados relacional e apresentando o resultado em formato multidimensional utilizando API Java, sendo classificado como uma ferrametna ROLAP. A arquitetura Mondrian é estruturada em quatro camadas: a camada de apresentação, camada dimensional, camada estrela e camada de armazenamento, conforme descrito em Mondrian (2008). 4.3.2.1 Camada de apresentação A camada de apresentação determina o que o usuário vê em seu monitor e como pode interagir para obter novas informações. Existem várias maneiras dos dados multidimensionais serem apresentados para os usuários, incluindo tabelas interativas, gráficos de linhas, barras ou fatias. No entanto, todas essas formas de apresentação têm em comum os conceitos multidimensionais de dimensões, medidas e células, sobre os quais esta camada recebe as requisições dos usuários e devolve seus resultados; 4.3.2.2 Camada Dimensional Executa particionamento, validação e execução das consultas MDX submetidas pela camada de apresentação. As consultas são tratadas em diferentes fases. Os eixos das dimensões são computados primeiramente, e então os valores das células de cada eixo; 4.3.2.3 Camada Estrela Esta camada é responsável por manter uma estrutura de cache com um conjunto de medidas associadas às respectivas dimensões. Quando os dados solicitados não 40 estão em cache ou não podem ser dela derivados, a requisição é repassada à camada de armazenamento; 4.3.2.4 Camada de armazenamento Consiste no sistema de gerenciamento de banco de dados relacional e é responsável por prover os dados agregados e seus membros das respectivas dimensões. Todos estes componentes podem estar presentes na mesma máquina, ou serem distribuídos por outras máquinas. As camadas 2 e 3 (Dimensional e Estrela) compõem o servidor do Mondrian e devem estar presentes na mesma máquina. A camada de armazenamento pode estar situada em outra máquina e ser acessada remotamente através de uma conexão jdbc (Java Data Base Conector). Utilizando o Mondrian são três os tipos de dados a serem armazenados: das tabelas de fatos, dimensões e agregações. Agregações são resumos de dados pré-calculados que melhoram o tempo de resposta pelo simples motivo de ter as respostas prontas antes de receber as perguntas (MICROSOFT, 2007). A estratégia de agregação do Mondrian é manter os dados da tabela de fatos armazenados no SGBDR e ter os dados agregados no cache através da submissão de consultas group by2. (FELBER, 2005). O cache mantém os dados pré-calculados em memória disponibilizando-os para as próximas consultas, agizilizando o tempo de resposta. 2 Cláusula de grupo da linguagem de consulta SQL. 41 4.3.3 Mondrian Schema Workbench Mondrian Schema workbench é a ferramenta onde o modelo lógico multidimensional, contendo os cubos, dimensões, membros e hierarquias é construído e representado em linguagem XML. Neste schema é feito o mapeamento entre a definição lógica e o modelo físico, o banco de dados relacional. O exemplo de arquivo XML da figura 10 demonstra parte de um schema contendo um cubo denominado “emergencia1”, e as dimensões “motivo de atendimento”, “idade”, “sexo”, “descrição_capitulo_cid10“ . Figura 10 - Exemplo de arquivo XML definindo um schema mondrian 4.3.4 Mdx (Multidimensional Expressions) MDX é um padrão para expressões multidimensionais para consultas a banco de dados, semelhante a linguagem SQL. A linguagem MDX foi originalmente criada pela Microsoft para utilização com o produto SQL Server OLAP Services como parte da especificação OLE DB/OLAP API 3. 3 O Microsoft OLE DB para OLAP é um conjunto de objetos e interfaces que estendem a habilidade do OLE DB para prover acesso a armazenagem de dados multidimensional. 42 Pinho (2008,p.14) apresenta a seguinte definição para MDX: “A linguagem MDX provê uma sintaxe rica, poderosa e ao mesmo tempo simples para consultar e manipular dados armazenados nos servidores OLAP de forma bastante flexível. Ela possui uma vasta quantidade de operadores analíticos e utiliza expressões compostas de identificadores, valores e funções que são avaliados pelo servidor OLAP para obter os objetos (por exemplo, membros) ou escalares (por exemplo, um número). MDX representa para os servidores OLAP o que a linguagem SQL representa para os SGBD Relacionais. No entanto ela não representa uma extensão da linguagem SQL e pode-se diferenciar as duas em vários pontos. Como exemplo, nos SGBD relacionais tabelas são utilizadas como fontes de dados, enquanto MDX utiliza-se do conceito de cubos.” A figura 11 apresenta uma consulta MDX que retorna a quantidade atendimentos de emergência por ano e sexo. Figura 11 - Consulta MDX O resultado pode ser visualizado na figura 12, onde a cláusula select .seleciona as ocorrências de atendimentos de emergência utilizando as dimensões ano e sexo nas linhas e dispondo as medidas nas colunas. A cláusula from indica que a fonte dos dados é o cubo “Emergência1”. 43 Figura 12 - Resultado da consulta MDX da figura 10. 4.3.5 Jrubik Jrubik é um cliente OLAP Open Source desenvolvido em Java/Swing4 para conectar a servidores OLAP. Alguns de seus componentes são o olap navigator, MDX query editor, table viewer, chart viewer, map viewer, menus e bookmarks e statistical data . O table é seu principal componente, pois permite ao usuário visualizar e trabalhar os dados em forma tabular. Permite também a navegação por dimensões, hierarquias, membros, realizando operações típicas de ferramentas olap, permitindo a impressão e exportação dos dados para os formatos PDF, XML, HTML e MSExcel. O Chart é o visualizador de gráficos e pode ser configurado para diferentes formatos como pizza, barra, 3D, linha. O Navigator tree possibilita ao usuário editar os componentes a serem exibidos, estabelecendo filtros aos membros das dimensões, alterando a o escopo da consulta, através de interface gráfica que reflete automaticamente as alterações selecionadas na MDX correspondente a consulta. As consultas podem ser gravadas no componente Bookmark para uso futuro. 4 Swing é um Framework (conjunto de classes que constitui um design abstrato para soluções de uma família de problemas) para a criação de aplicações gráficas em Java 44 4.3.6 MySql O MySQL é um sistema de gerenciamento de banco de dados (SGBD), que utiliza a linguagem SQL(Structured Query Language - Linguagem de Consulta Estruturada) como interface. É atualmente um dos bancos de dados mais populares, com mais de 10 milhões de instalações pelo mundo (WIKIPEDIA, 2009). 45 5 ESTUDO DE CASO O Hospital do Trabalhador é um hospital público, com atendimento exclusivo aos pacientes do Sistema Único de Saúde – SUS e referência no atendimento ao trauma na região metropolitana de Curitiba. Visando demonstrar como construir uma solução OLAP para uso como ferramenta de apoio à tomada de decisão, o estudo de caso está estruturado em tópicos retratando os processos e softwares utilizados, ficando o escopo de aplicação restrito a área de Emergência hospitalar. No primeiro tópico é apresentado a necessidade geradora do objeto de estudo, no segundo tópico é apresentado o processo de extração, transformação e carga dos dados no ambiente escolhido neste trabalho para DW. No terceiro tópico é apresentado o processo de modelagem e consulta para DW e no quarto tópico a conclusão dos benefícios da implementação. 5.1 NECESSIDADE A demanda de informações produzidas na Emergência de um hospital de grande porte abrange desde resolução de situações operacionais até atendimento a órgãos internos e externos como imprensa, universidades, governos, etc., que vêem nesta fonte de dados a oportunidade de embasar estudos científicos e obter informações claras e precisas para planejamentos estratégicos e operacionais. No Hospital do Trabalhador, na área de Emergência hospitalar são feitos atendimentos de casos clínicos e cirúrgicos prioritariamente em traumatologia e ortopedia. Como objeto de estudo de caso será abordado a necessidade apresentada pela direção do hospital em extrair informações relacionadas aos dados coletados durante o atendimento de emergência e armazenados pelos sistemas transacionais. 46 Como exemplos de consultas temos dados quantitativos da evolução dos atendimentos na emergência considerando aspectos combinados como : atendimentos de ano, mês, sexo e faixa etária; atendimentos por sexo e motivo de atendimento. Ao resultado dessas consultas também pode ser desejável a aplicação de filtros para isolar um aspecto em particular para detalhar uma investigação, como por exemplo, a evolução dos quantitativos de um motivo de atendimento específico em um período específico. Como característica principal das consultas desejadas, observa-se que são tipicamente consultas ad-hoc e, para que seja possível ao profissional de tecnologia da informação atender aos seus usuários, optou-se por desenhar as principais dimensões relacionadas ao assunto em questão – atendimento de emergência – que é o nosso fato. 5.2 IDENTIFICANDO A FONTE DOS DADOS: Os dados foram extraídos do banco da dados Openbase, ambiente operacional Linux, que atende ao sistema de produção utilizado pelo hospital desde 2005. As tabelas identificadas como principais fatos e dimensões foram extraídas e transferidas para o banco de dados MySql. Conforme schema do banco do Hospub, a principal tabela extraída e trabalhada foi a tabela de atendimentos da emergência, compondo o histórico dos atendimentos de emergência, que no modelo multidimensional representa a tabela de fatos. Na sequência, temos as tabelas auxiliares de unidades da federação, cadastro de motivos de atendimento na amergência, cadastro de setores da emergênia , nacionalidade, código internacional de doenças – cid10 e profissionais de saúde. Para um momento futuro, outras fontes de dados podem ser adicionadas como o dados do sistema de prescrição médica e faturamento do seguro dpvat5. 5 DPVAT – Seguro obrigatório de danos pessoais causados por veículos automotores de via terrestre. 47 5.3 EXTRAÇÃO, TRANSFORMAÇÃO E CARGA - ETC Para extração dos dados utilizou-se o Kettle/Spoon, ferramenta open source para extração, transformação e carga. Para acesso à base de dados original optou-se por uma configuração de conecção jdbc com o banco de dados Openbase, e gravação no banco de dados MySql. Duas transformações foram desenhadas para extrações de dados dos sistemas originais: a extração da tabela de atendimentos (fig. 13) e a extração das tabelas auxiliares (unidades da federação, cadastro de motivos de atendimento na amergência, cadastro de setores da emergênia , nacionalidade, código internacional de doenças – cid10 e profissionais de saúde). Figura 13 - Extração de dados Na sequência, os dados são trabalhados para implementar as transformações necessárias resultando na construção das dimensões do modelo para atendimento às consultas dos usuários (fig. 14). As dimensões trabalhadas são tempo (ano, trimestre, mês, dia), idade (faixa etária), tempo de atendimento, sexo, diagnóstico cid10 (Classificação Estatística Internacional de Doenças e Problemas Relacionados à Saúde), motivo de atendimento e tipo de alta. Para delimitar a faixa de registros a serem trabalhados são necessários alguns filtros para exclusão de registros em abertos e fora da faixa fixada para carga inicial dos dados, que compreende de 2004 a outubro de 2008. 48 Figura 14 - Transformação de dados Fonte: TOMIO (2008) As transformações são programadas utilizando-se java script (fig. 15), e agrupadas conforme sua natureza e campos relacionados. Figura 15 - Script de transformação no Kettle Fonte: TOMIO (2008) A transformação para o cálculo da faixa etária cria duas categorias de faixas etárias, a de sete e a de dez anos, criando os campos faixa_etaria_7_anos e faixa 49 etária_10_anos respectivamente. A faixa etária de sete em sete anos é conforme quadro 1: 0 a 6 anos 07 a 13 anos 14 a 20 anos 21 a 27 anos 28 a 34 anos 35 a 41 anos 42 a 48 anos 49 a 55 anos 56 a 62 anos 63 a 69 anos 70 a 76 anos 77 a 83 anos 84 a 90 anos 91 a 97 anos 98 a 104 anos acima de 105 anos Quadro 1 - Faixa etária de sete anos. E a faixa etária de dez em dez anos, conforme quadro 2: 00 a 9 anos 10 a 19 anos 20 a 29 anos 30 a 39 anos 40 a 49 anos 50 a 59 anos 60 a 69 anos 70 a 79 anos 80 a 89 anos 90 a 99 anos acima de 10o anos Quadro 2 -Faixa etária de dez anos. A transformação para o cálculo do tempo de permanência do paciente na emergência é baseado nas datas de entrada e saída do paciente, utilizando-se para isso uma categorização do tempo de permanência do paciente, conforme quadro 3. Menos que 1 hora 1 hora até 1:59 2 horas até 2:59 3 horas até 3:59 4 horas até 5:59 5 horas até 5:59 6 horas até 6:59 7 horas até 7:59 8 horas até 8:59 9 horas até 9:59 10 horas até 10:59 11 horas até 11:59 Acima de 11:59 horas Quadro 3 - Tempo de permanência. A transformação para os campos referentes ao Cid-10 permite a visualização dos 50 atendimentos por capítulos do CID-10 de forma descritiva facilitando o entendimento pelo usuário, conforme quadro 4. Quadro 4 - Descrição dos capítulos do CID-10. A transformação para os campos motivo de saída e tipo de alta tem a função de unificá-los tornando-os um único campo descritivo e de fácil entendimento para o usuário. (fig. 16 ) e (quadro 5). O campo motivo de saída do paciente indica qual o motivo da saída sendo: alta médica, óbito, remoção, internação ou encaminhamento para ambulatório. O campo tipo de alta caracteriza a alta médica em :por decisão médica, a pedido do paciente, a revelia, a desistência, extraviado ou cancelado. 51 Figura 16 - Tipo de alta Quadro 5 - Motivo e tipo de alta A transformação para tratar os campos que compõe a dimensão tempo, possibilita o desmembramento ou consolidação das consultas por ano, trimestre, mês e dia em sua visualização. (fig. 17 e fig. 18). Figura 17 - Composição da dimensão tempo 52 Figura 18 – Transformação para gerar a dimensão tempo 5.4 IMPLEMENTANDO OS CUBOS OLAP. Após a criação da tabela de fatos e suas dimensões no banco de dados relacional parte-se para a criação dos cubos olap utilizando a ferramenta Mondrian Schema Workbench (fig.19). Nesta etapa, utiliza-se o Mondrian Schema Workbench para desenhar o modelo lógico (cubos, hierarquias, níveis e membros) e fazer o seu mapeamento para o modelo físico, (banco relacional – modelo estrela) . Este modelo lógico é um arquivo xml e utilizado nas queries MDX . Os principais componentes lógicos descritos do arquivo XML visualizados na figura 19 são: o Nome do schema : “bdint”; o Nome do cubo: “Emergencia1”; o Nome da tabela fato ="emergencia-consolidado" 53 o Nome da medida: Measure="N54NUMBOLET" o Nome das dimensões vinculadas aos seus respectivos campos: ="Motivo de Atendimento"; "idade", "Sexo", "Descricao_Capitulo_CID", "Tempo de Atendimento - Categorizado", "Codigo_Capitulo_CID", "Ano ", "Mes", "Nome do dia", "Trimestre ", "Dia do mes ", "Tipo de Alta", "Setor", "Nome Profissional (medico)", "Bairro", Figura 19 - Cubo emergência1.no Schema Workbench Fonte: TOMIO (2008). 5.5 CONSULTAS Utiliza-se a ferramenta Jrubik para visualizar o modelo lógico desenvolvido (cubos, hierarquias, níveis e membros) e disponível para compor as consultas. (fig. 20). Realizando consultas considerar os seguintes exemplos: Exemplo 1: Qual o número de atendimentos de emergência realizados no mês de março de 2008, por sexo e idade até 6 anos? 54 Para realizar a consulta o usuário seleciona as dimensões ano, mês, sexo e idade para compor as linhas e measures (atendimentos) para compor as colunas. Esta operação é simples e realizada utilizando-se o recurso drag and drop (arrastar e soltar) sobre as abas “ON rows” e “ON columns”. (fig. 20) Figura 20 - Tela do Discoverer – Cubo, medida, dimensões e montagem da consulta Para estabelecer o filtro (mês de março e ano 2008 e idade de 0 a 6 anos) ou um slice , utiliza-se a janela Navigator para ativar/desativar os itens selecionados, correspondentes a cada dimensão selecionada para as linhas. (fig. 21). 55 Figura 21 - Navigator - Seleção de filtros. O resultado da consulta pode ser visualizado na tela table viewer conforme figura 22. . Figura 22 - Table viewer – Atendimento do mês de março/2008 por sexo e de 0 a 6 anos. Ou pode ser exportado para um arquivo formato PDF, conforme figura 23. Figura 23 - Resultado de consulta em formato PDF A ferramenta também possibilita a visualização dos resultados de forma gráfica, permitindo a configuração de cores, legendas e tipos de gráficos, como o exemplo 56 de gráfico de formato pizza,. ( figura 24). Figura 24 - Gráfico da consulta visualizada na figura 22 Aos resultados de uma consulta pode-se ainda aplicar outras funcionalidades do software para uma investigação mais detalhada das ocorrências encontradas. Exemplo 2: Qual o número de atendimentos de emergência por ano, motivo de atendimento e idade (faixa etária) ? Para realizar a consulta o usuário seleciona as dimensões ano, motivo de atendimento idade (faixa etária) para compor as linhas e measures (atendimentos) para compor as colunas. Esta operação é simples e realizada utilizando o recurso drag and drop (arrastar e soltar) sobre as abas “ON rows” e “ON columns”, conforme mecanismo já demonstrado na figura 20. Ao resultado pode-se realizar um drill down ou um roll up para navegar do nível mais alto até o nível mais detalhado, membro a membro, conforme determinado pelas hierarquias das dimensões. Na figura 25, observa-se um drill down no membro 57 motivo de atendimento para o ano de 2008. E sobre esse resultado aplica-se um drill through para possibilitar chegar a visualização dos dados primários dos registros selecionados. (Aqui não serão mostrados os dados primários por se tratarem de dados pessoais de pacientes. Entretanto a operação é possível realizando um duplo “clic” sobre o número de ocorrência que se deseja visualizar). Figura 25 - Operação drill through Exemplo 3 : Qual o numero de atendimentos de emergência por para o ano de 2008, por trimestre e sexo. Para uma consulta também pode-se aplicar a operação “slice and dice”, que além de selecionar uma fatia dos dados, realiza uma mudança de perspectiva na visualização dos mesmos (fig. 26 e fig. 27 e fig. 28). O filtro é estabelecido pelo “navigator” e a inversão de posições de linhas e colunas é feita utilizando-se para isso a funcionalidade “swap axis” ou realizando a operação de drag end drop (arrastar e soltar) das linhas ou colunas nas posições desejadas, na “table viewer”. 58 Figura 26 - Operação slice and dice –perspectiva.A Figura 27 - Operação slice and dice – perspectiva.B Figura 28 - Operação slice and dice – perspectiva.C 59 6 CONCLUSÃO Este trabalho contribuiu para o conhecimento da tecnologia de data warehouse e ferramentas olap e sua aplicação no processo de suporte a decisão. Através da pesquisa realizada constata-se que a tecnologia de data warehouse apresenta um melhor resultado quando aplicada a grandes volumes de dados históricos para as empresas que precisam recuperá-los de forma ágil e segura, auxiliando os profissionais no processo de tomada de decisões estratégicas. O estudo de caso desenvolvido no Hospital do Trabalhador foi revelador porque permitiu realizar a prática da teoria pesquisada, evidenciando os pontos importantes de projeto que não podem ser esquecidos, ao mesmo tempo que abre uma perspectiva futura para atender a necessidade local de padronização e integração das informações resultantes dos processos de negócios do hospital, que se encontram dispersos em vários sistemas de bancos de dados e fontes informais. A metodologia utilizada no desenvolvimento do DW foi a modelagem Multidimensional proposta por Ralph Kimball e Bill Inmon, adaptadas aos recursos e ferramentas disponíveis e os resultados obtidos até o momento superam as espectativas iniciais, tendo despertado grande interesse das instituições envolvidas no processo de informatização no Hospital do Trabalhador em prover incentivo ao uso desta tecnologia para melhoria e extensão deste projeto a outros similares no Sistema Único de Saúde. Como conclusão deste trabalho coloca-se a grande viabilidade do uso de ferramentas OLAP como apoio ao processo de tomada de decisão, visto que permite ao usuário chegar a resultados significativos através de consultas complexas feitas de modo relativamente simples utilizando ferramentas Open Source. 60 REFERÊNCIAS BIBLIOGRÁFICAS: MACHADO, Nery Rodrigues. Tecnologia e Projeto de Data Warehouse. 4ª.ed.São Paulo:Érica,2008. MARTINS et al, Mário Pereira. Analysis: uma proposta de ferramenta OLAP-WEB para a análise de informações ambientais do Vale do Rio dos Sinos. UFRGS. (2007) Disponível em <<http://ccet.ucs.br/erbd2007/artigos/26041.pdf >> Acesso em 28 de dez de 2008. INMON, W.H.. Como construir o Data Warehouse. 2. ed. Rio de Janeiro: Editora Campus, 1997. 388 p. ______. Creating The Data Warehouse Data Model From The Corporate Data Model. 2000. Disponível em: <http://www.inmoncif.com/registration/whitepapers/ ttdwdmod-1.pdf>. Acesso em: 15 dez. 2008. KIMBALL, Ralph. Data Warehouse Toolkit – Técnicas para construção de Data Warehouses Dimensionais. São Paulo: Makron Books, 1998. 388 p. KIMBALL, Ralph; MERZ, Richard. Data Webhouse – Construindo o data warehouse para a Web. Rio de Janeiro: Editora Campus, 2000. 367 p.75 SOUZA, Eliane Martins de. Ferramentas de Back End. Disponível em: <http://www.datawarehouse.inf.br/Artigos/backend.pdf>. Acesso em: 11 jan. 2009. WIKIPÉDIA. Wikipédia, a enciclopédia livre. Disponível em: <http://pt.wikipedia.org/wiki/Armaz%C3%A9m_de_dados>. Acesso em: 11 jan. 2009. ______. Wikipédia, a enciclopédia livre. Disponível em: <http://pt.wikipedia.org/wiki/Metadados>. Acesso em: 20 jul. 2007. KIMBALL, Ralph. Fact Tables and Dimension Tables. Disponível em <http://www.intelligententerprise.com/030101/602warehouse1_1.jhtml> Acesso em 11 jan 2009. ______. DBMS A DIMENSIONAL MODELING MANIFESTO. 1997. Disponível em <<ttp://www.uniriotec.br/~tanaka/SAIN/DBMS%20-%20August%201997%20%20A%20Dimensional%20Modeling%20Manifesto.htm>>. Acesso em dez 2008. KIMBALL, Ralph; ROSS, Margy. The Data Warehouse Toolkit – Guia completo para modelagem dimensional. Rio de Janeiro: Editora Campus, 2002. 494 p. PENTAHO, Open Source Business Intelligence. How to Design a Mondrian Schema. Disponível em <http://mondrian.pentaho.org/documentation/schema.php> . 61 Acesso em 05 jan 2009. MINERAÇAO DE DADOS-UEM, Grupo de Sistemas Inteligentes. OLAP. Disponível em <http://www.din.uem.br/ia/a_multid/mineracao/OLAP.html>. Acesso em 17 jan 2009. GEOCITIES. OLAP (On-Line Analitic Processing ). Disponível em <http://br.geocities.com/danielstrider/olap.html>. Acesso em 17 jan 2009. ________. DW (Data Warehouse) . Disponível em <<http://br.geocities.com/ danielstrider/dw.html>>. Acesso em 10 jan 2009. MICROSOFT. Módulo I - O que é Business Intelligence. 2007. 15 p. MICROSOFT. Módulo 2 - Definindo Soluções OLAP. 2007. 16 p. MICROSOFT. Módulo 3 - Desenhando uma solução OLAP. 2007. 22 p. MICROSOFT. Módulo 4 - Contruindo uma solução OLAP. 2007. 15 p. MICROSOFT. Módulo 5 - Implementando Cubos OLAP. 2007. 22 p. COLOMBARI, Jacidio; MARINHO, Sandro M. N.. Desenvolvimento de data warehouse para o domínio de telecomunicações. 2007. UNOPAR. Disponível em <http://pessoal.sercomtel.com.br/sandroeshirlei/DATA_ WAREHOUSE.pdf >>Acesso em dez 2008. MONDRIAN. Mondrian 2.2.2 Technical Guide. Developing OLAP solutions with Mondrian. March 2007. Disponível em <http://mondrian.sourceforge.net/>. Acesso em nov 2008. PINHO, Fábio Rocha de. Estendendo o Servidor OLAP Mondrian com UDF Envolvendo Operadores Espaciais. 2008. Universidade Federal de Pernambuco. Disponível em << http://www.cin.ufpe.br/~tg/2008-1/frp.pdf >> . Acesso em dez 2008. TOMIO, Rivaldo Luiz. Data warehouse e Hospub. In: I Encontro de Gestores do Hospub. Curitiba. 2008. FELBER, Edimilson J. W.. Proposta de uma ferramenta OLAP em um data mart comercial: uma aplicação prática na indústria calçadista. 2005. Centro Universitário FEEVALE. Disponível em << http://ead.feevale.br/tc/files/450.pdf >> Acesso em fev. 2009)
