Modelagem de um Data Warehouse para mineração de dados ... Amaro de Mello... · Especialização em Desenvolvimento de Sistemas para Web Wlademir Amaro de Mello Modelagem de um

Universidade Estadual de Maringá

Centro de Tecnologia - Departamento de Informática

Especialização em Desenvolvimento de Sistemas para Web

Wlademir Amaro de Mello

Modelagem de um Data Warehouse para mineração de dados sobre

beneficiários de um plano de Saúde.

Maringá - Pr

Março de 2013







Monografia apresentada ao curso de Especialização em Desenvolvimento de Sistemas para WEB – Turma 8.

Maringá - Pr

Março de 2013







Maringá 26 de Março de 2013

Prof. Dra. Maria Madalena Dias Ass.:________________________

Prof. Dr. Renato Balancieri Ass.:________________________

Prof. Dr. Wesley Romão (Orientador) Ass.:________________________

Sumário

1 Introdução ..................................................................................................................................... 14

1.1 Objetivo Geral ............................................................................................................................ 15

1.2 Objetivos Específicos ................................................................................................................ 16

1.3 Metodologia ................................................................................................................................ 16

2 Fundamentação Teórica ............................................................................................................. 18

2.1 KDD ............................................................................................................................................. 18

2.2 Seleção de Atributos ................................................................................................................. 21

2.3 Data Warehouse ........................................................................................................................ 23 2.3.1 Arquitetura de um Data Warehouse................................................................................... 25 2.3.2 Modelagem Dimensional ..................................................................................................... 27 2.3.3 Tecnologia OLAP .................................................................................................................. 31 2.3.4 Estrutura Multidimensional .................................................................................................. 33

3 Implementação ............................................................................................................................. 36

3.1 Ferramentas Utilizadas ............................................................................................................. 36

3.2 Área de Negócio e Dados Armazenados ............................................................................... 37

3.3 Pré-processamento e Transformação dos dados ................................................................. 45

3.4 Projeto ......................................................................................................................................... 48

4 Conclusões e Trabalhos Futuros ............................................................................................. 54

5 Referências Bibliográficas ......................................................................................................... 55

Anexos .................................................................................................................................................... 57

Lista de Legendas

BI Business Intelligence CID Classificação Internacional de Doenças DW Data Warehouse JDBC Java Database Connectivety JCP Java Communyt Process MD Mineração de Dados KDD Knowledge Discovery in Databases OLAM On-Line Analytical Mining OLAP On-Line Analytical Processing OLTP On-Line Transaction Processing ROLAP Relational On-Line Analytical Processing SAD Sistemas de Apoio à Decisão SGBD Sistema Gerenciador de Banco de Dados SQL Structured Query Language

Lista de Figuras

Figura 1 - Etapas do Processo KDD (Fayyad et al., 1996) ........................................ 19

Figura 2 - Elementos do DW (Kimball et al, 2002) ...................................................... 27

Figura 3 - Modelo Estrela ................................................................................................ 30

Figura 4 - Modelo Floco de Neve ................................................................................... 30

Figura 5 - Consulta para ranking de Fatos ................................................................... 40

Figura 6 - Estrutura das tabelas GRS_GUIA_EVENTO e GRS_GUIA ................... 42

Figura 7 - Consulta para identificação de Dimensões ................................................ 44

Figura 8 - Modelo do DW gerado ................................................................................... 47

Figura 9 - Tela de Login pública ..................................................................................... 49

Figura 10 - Pentaho Analysis View ................................................................................ 51

Figura 11 - Resultado de consulta multidimensional utilizando filtros ...................... 52

Lista de Tabelas

Tabela 1 - Tabela de exemplo de Fato ......................................................................... 28

Tabela 2 - Tabela de exemplo de Dimensão ............................................................... 29

Tabela 3 - Metadados Utilizados .................................................................................... 39

Tabela 4 - Descrição das Tabelas ................................................................................ 41

Resumo

Extrair conhecimentos de bases de dados de corporações tem sido uma realidade

nos dias de hoje. Percorrer todas as fases do processo de descoberta de

conhecimento sempre que se deseja realizar um estudo sobre Mineração de

Dados acaba por ser um processo moroso e custoso. Este trabalho tem como

objetivo criar um Data Warehouse utilizando Metadados do SGBD, e em conjunto

com uma ferramenta BI (Business Intelligence) disponibilizar consultas

multidimensionais e assim atingir o objetivo que é definir um conjunto final de

dados para Mineração. O trabalho contribui evidenciando que é possível criar um

Data Warehouse com Metadados do SGBD e exportar estes dados para

mineração com uma ferramenta de BI.

Palavras-Chave: Descoberta de Conhecimento em Banco de Dados, Data

Warehouse, Metadados, Mineração de Dados.

Abstract

Extract knowledge from corporation's databases has been a reality nowadays.

Browse all stages whenever you wish to conduct a study on Data Mining turns out

to be a lengthy and costly process. This work aims to create a Data Warehouse to

DBMS using Meta Data, and in conjunction with a tool BI (Business Intelligence)

provide multidimensional queries and thus achieve the goal is to define a final set

of data to Data Mining. The work contributes showing that it is possible to create a

Data Warehouse DBMS with Meta data and export these data for data mining with

a BI tool.

Keywords: Knowledge discovery in databases, Data Warehouse , Data Mining,

Meta Data.

14

1 Introdução

Os sistemas de informação vêm abrangendo cada vez mais as regras de

negócio dos mais diversos ramos de atividade. Tal fenômeno tem colaborado

para que a utilização desses sistemas seja quase uma obrigação, pois, tanto as

entidades científicas vêm buscando a introdução, aprimoramento e descoberta de

novas tecnologias quanto o mercado de negócios busca se defender de ameaças

provenientes da concorrência do meio comercial e falhas em seus processos

administrativos. Em busca de alcançar resultados, sistemas têm gerado uma

significativa massa de dados em seus SGBDs (Sistemas Gerenciadores de Banco

de Dados). Do ponto de vista de espaço para armazenamento, a quantidade de

informações não apresenta problema porque nos dias atuais é comum encontrar

dispositivos que armazenam mais de um terabyte de dados. O desafio está em

visualizar informações além do óbvio que os sistemas já mostram. Base de dados

grandes tornam-se problemáticas para consultas e a complexidade em relacionar

os dados torna-se mais árdua devido as informações ficarem esparsas e

desbalanceadas.

O Data Warehouse (DW) é um ambiente com finalidade de extrair,

integrar, limpar e dar consistência aos dados provenientes dos sistemas

transacionais. Um DW proporciona a consolidação e dimensionamento dos

dados, organizando-os e melhorando o desempenho das consultas. Juntando

essas características com as ferramentas OLAP (On-Line Analytical Processing) é

possível viabilizar a construção de um ambiente no qual os analistas de negócio

15

possam facilmente navegar pelos dados, realizando consultas ad hoc, fazendo

novos cruzamentos entre as dimensões de análise sem sentir a dificuldade que a

vasta quantidade de informações causaria.

Uma vez que o DW organiza os dados, pré-selecionados por um

especialista de uma área de conhecimento ou "Negócio", esses dados podem ser

submetidos a técnicas de mineração de dados com o intuito de descobrir padrões

de comportamento e de novos conhecimentos sobre os dados armazenados,

sendo que esses conhecimentos dificilmente seriam encontrados diretamente

pelo sistema.

Neste trabalho é descrita uma experiência de pré-processamento de

dados com a utilização de ferramentas de BI (Business Intelligence), afim de gerar

dados para utilização de técnicas de Descoberta de Conhecimento em Banco de

Dados, também conhecido como KDD (Knowledge Discovery in Databases). O

estudo segue um formato de pesquisa bibliográfica, criação e implementação de

um DW modelando as tabelas de fatos geradores, utilização e estudo de técnicas

como OLAP. Os dados para ensaio e teste das técnicas abordadas se referem ao

nicho de negócios de Planos de Saúde Suplementar.

1.1 Objetivo Geral

Criar um Data Warehouse com dados de um plano de saúde suplementar

integrado com a ferramenta de BI Pentaho de forma a possibilitar a exportação de

dados para ensaio com algoritmos de Mineração de Dados.

16

1.2 Objetivos Específicos

Utilizar os metadados do SGBD para identificar as tabelas de fatos e

dimensões para a criação do Data Warehouse.

Acelerar o processo de mineração de dados substituindo as fases de

seleção, pré-rocessamento e transformação de dados pelo Data Warehouse

criado.

Parametrização do Data Warehose na ferramenta Pentaho para

execução de consultas multidimensionais, filtrar dados conforme necessidade de

exclusão de informação e exportação dos dados para um arquivo texto de forma a

facilitar a utilização de ferramentas de mineração de dados.

1.3 Metodologia

Este trabalho obedece o padrão exploratório, no qual é utilizado dados em

que o domínio da aplicação se refere à Administração de Planos de Saúde. A

base de dados fornecen objetos para criação de um Data Warehouse que será

desenvolvido com a exploração dos Metadados do SGBD. O data warehouse vai

ser aplicado no processo de descoberta de conhecimento proposto por Fayyad et

al. (1996) descrito com detalhes na Sessão 2.1, sua finalidade é substituir as três

17

primeiras fases que são de pré-processamento de dados. Com o apoio da

Ferramenta Pentaho e a funcionalidade OLAP, o data warehouse resultante da

análise dos Metadados serão trabalhados de forma a serem exportados para

Futuros testes de mineração de dados. Este trabalho está dividido em quatro

capítulos:

O primeiro capítulo é introdutório especificando a abrangência do

trabalho.

No segundo capítulo é esplanada a teoria sobre os conceitos utilizados no

desenvolvimento do projeto.

No terceiro capítulo são demonstradas as implementações como a

utilização de consultas em visões estáticas do banco que refletem informações

dos Metadados fornecendo dados quantitativas referente ao modelo relacional,

identificação das tabelas de fatos e suas possíveis dimensões até a definição do

modelo do data warehouse gerado. Também é demonstrado como a ferramenta

OLAP possibilita consultas e filtros para exportação dos dados.

O quarto capítulo contem as conclusões e sugestões para trabalhos

futuros.

18

2 Fundamentação Teórica

Neste capítulo é apresentado o estado da arte demonstrando sobre os

assuntos de KDD, Data Warehouse, OLAP e Mineração de Dados. É feita a

exposição dos principais conceitos e a importância de cada um no processo

decisório, mostrando sua relevância para os dias atuais.

2.1 KDD

KDD (KNOWLEDGE DISCOVERY IN DATABASE) é um processo

interativo e iterativo, que se desdobra em cinco atividades (Fayyad et al., 1996):

Seleção dos Atributos, Pré-processamento, Limpeza dos dados, Transformação

dos dados e Mineração de Dados.

KDD é interativo devido à interferência do homem colaborando nos

processos, utilizando ferramentas computacionais para análise e interpretação

dos dados.

É Iterativo por ter o nível de acoplamento muito forte entre o progresso

das etapas que o processo exige que seja percorrida. O ponto negativo por essa

iteratividade é que o resultado satisfatório nem sempre é alcançado.

Idealizarmos o fluxo proposto

sua continuidade, a não

prejudicar as seguintes

transformação de dados aconteça

técnica de MD sobre os dados gerados

Figura

A Figura 1 representa

de conhecimento em banco de dados. Segue uma breve descrição das etapas do

processo:

Seleção: Esta fas

resultado final, nela é escolhido o conjunto de dados contendo todas as possíveis

variáveis (características, atributos) e registros que farão parte da análise. A

escolha dos dados pode ficar a critério d

alguém que realmente entende do assunto em questão. A etapa ainda pode

Pré-processamento

Seleção

Dados Relevantes

Dados

dealizarmos o fluxo proposto no KDD, cada etapa dependente da anterior para

não identificação de falha em alguma das e

prejudicar as seguintes. Para ser mais claro, supondo que uma falha na

transformação de dados aconteça e não seja identificada, a aplicação

sobre os dados gerados, acabaria tornando-se um

gura 1 - Etapas do Processo KDD (Fayyad et al ., 1996)

representa um dos processos mais utilizados

em banco de dados. Segue uma breve descrição das etapas do

fase possui impacto significativo sobre a qualidade do

é escolhido o conjunto de dados contendo todas as possíveis


escolha dos dados pode ficar a critério de um especialista do domínio, ou seja,


processamento Transformação

Data Mining

Interpretação

Dados Relevantes

Dados Pré-processados

Dados Transformados

Padrões

19

cada etapa dependente da anterior para

identificação de falha em alguma das etapas pode

que uma falha na

a aplicação de alguma

se um esforço perdido.

s para descoberta

em banco de dados. Segue uma breve descrição das etapas do

e possui impacto significativo sobre a qualidade do

é escolhido o conjunto de dados contendo todas as possíveis


e um especialista do domínio, ou seja,


Interpretação

Conhecimento

20

ganhar uma complexidade maior se os dados não vierem de uma única fonte,

pois hoje as empresas têm sistemas legados1 que podem ter as informações

descentralizadas, o que acrescenta esforço em identificar os relacionamentos

entre as informações que não estejam em um mesmo banco de dados e até

mesmo um problema para captação da informação.

Pré-processamento e Limpeza: A qualidade dos dados tem uma

responsabilidade significativa em determinar a eficiência dos algoritmos de

mineração. Eliminar dados redundantes e inconsistentes, recuperar dados

incompletos e identificar dados discrepantes no conjunto.

Transformação dos Dados: Nesta etapa os dados necessitam ser

armazenados e formatados para que os algoritmos possam ser aplicados. Como

mencionado na etapa de seleção, os dados podem estar descentralizados, ou

seja, em base de dados diferentes, então é neste ponto que a dispersão dos

dados deve ser eliminada fazendo com que as informações se concentrem em um

único repositório central. Após a centralização dos dados é possível enxergar

alternativas de melhoras na transformação dos dados e criação de novos

atributos, os chamados dados derivados.

Mineração de Dados: Quando se procura por assuntos relacionados a BI

percebe-se que a Mineração de Dados é a parte do processo no qual se tem mais

destaque. De forma automática ou semiautomática é executada uma análise nos

1 Sistemas Legados segundo (Sommerville, 2007), são sistemas antigos e fundamentais para o

funcionamento da empresa

21

dados que foram tratados e centralizados com o intuito de se obter regras e

padrões.

Interpretação e Avaliação: Estatísticos, pesquisadores de IA (Inteligência

Artificial) e administradores de bancos de dados usam técnicas diferentes para

interpretar e avaliar os resultados obtidos com a mineração, acessando assim a

informação que não era visível.

2.2 Seleção de Atributos

Trata-se de uma técnica em que um subconjunto de atributos é

selecionado com a finalidade de se aplicar algoritmos de MD. O resultado deverá

fornecer informação referente à qualidade dos atributos na tarefa de extrair

conhecimento. O subconjunto dos atributos preferencialmente devem ter uma boa

precisão. O ideal é encontrar um subconjunto de atributos que tenha importância

para a descoberta de conhecimento relevante. Por inferência pode-se crer que

quanto maior o número de atributos em uma base de dados, maior o poder

discriminatório do classificador e a facilidade de extrair modelos de conhecimento

da base. Porém, quando a teoria vira prática, o mundo real apresenta provas de

que nem sempre isso é verdade. Um problema que a quantidade de atributos

pode gerar tem um jargão conhecido como Maldição da Dimensionalidade.

Analisando a seguinte hipótese: se um objeto que com um atributo contém dez

possíveis valores discretos, então tem-se no Maximo, 101 = 10 objetos distintos.

22

No entanto se alterarmos a quantidade de atributos para cinco então teremos 105

totalizando 100.000 objetos diferentes.

Pesquisas sobre seleção de atributos foram iniciadas há muito tempo nas

áreas de estatística e reconhecimento de padrões, e só posteriormente passaram

a ser tratadas na área de aprendizagem de máquina. Freitas (2002) explana dois

tipos de seleção de atributos: Filter e Wrapper. Em ambas exigem a utilização de

algoritmos.

Wrapper utiliza uma estratégia para executar uma busca ou ordenação de

forma guiada por um algoritmo de MD alvo enquanto que Filter não envolve

obrigatoriamente o uso de algoritmo de MD final. Ambas possuem vantagens e

desvantagens, o tipo Wrapper tende a ser mais preciso porém mais caro

computacionalmente, já Filter é bem mais rápido mas pode não fornecer

resultados satisfatórios.

Ambos os tipos de seleção podem exigir um grande esforço e tempo para

aplicação em qualquer tipo de dados. É justamente neste ponto que este trabalho

se propõe a melhorar disponibilizando consultas multidimensionais em

ferramentas de BI que possam dar agilidade na capitação de dado para aplicação

de algum algoritmo de MD.

23

2.3 Data Warehouse

Os Data Warehouse são tipos especiais de banco de dados cuja

popularidade tomou amplitude na década de 90. É definido por Inmon (1997)

como um depósito de dados orientado por assunto, integrado e não volátil porque

uma vez inseridos os dados não podem ser alterados, embora possam ser

excluídos. O Data Warehouse surgiu com a valorização da informação, pois

muitas empresas passaram a fazer análises de seus dados para tomar decisões

estratégicas. Segundo Gardner (1998), a implantação bem sucedida de um Data

Warehouse é considerada um dos primeiros passos para tornar mais fácil a

análise de grande quantidade de dados no apoio ao processo decisório.

O principal benefício de um projeto de Data Warehouse é gerar um

repositório que contenha dados limpos, agregados e consolidados, podendo este

ser analisado por ferramentas do tipo OLAP (On-Line Analytical Processing). Tal

ponto de vista leva a refletir: se temos um DW, logo os dados prontos para

Mineração de Dados.

Em aplicações reais, uma forma de promover a integração de tabelas é

utilizando DW. A presença de um DW como precursor à mineração de dados, e

caso não esteja disponível, muitos dos passos envolvidos na criação do mesmo

terão que ser tomados para preparar os dados para a mineração.

24

Apesar de esforços de desenvolvedores na arquitetura de software, ainda

é natural encontrar características não desejáveis nos dados, como redundância,

incompletude e ruídos. Deixando os dados confusos e com pouca viabilização

para se extrair informações com um âmbito mais decisório. O DW possibilita a

transformação de uma base de dados que utiliza OLTP (On-Line Transaction

Processing) para OLAP (On-Line Analytical Processing). OLPT se aplica em

processos de informação que executam operações do dia-a-dia da organização,

já o OLAP foca os processos de informação que possibilita a tomada de decisão.

Segundo Kimball et al. (2002), a utilização de um banco relacional é mais

complexa em suas consultas. A formalização de consultas aos dados é muito

mais rápida e intuitiva no modelo multidimensional se comparada ao modelo

relacional. A realização de consultas em banco de dados relacional exige a

navegação entre diversas tabelas, já no modelo multidimensional isto não é

necessário, o que o torna mais eficiente e com melhor desempenho.

O grande número de join, que é necessário realizar em tabelas no

modelo relacional, torna inviável a realização das consultas por usuários que

tenham apenas conhecimento do negócio. Os benefícios da modelagem

multidimensional é que ela torna os esquemas de dados mais compreensíveis

para os usuários finais, e por outro lado ela permite usar armazenamento

específico e técnicas de acesso que melhoram o desempenho de consulta se for

necessário utilizá-las.

25

Data Warehouse não tem o paradigma de modelos de dados

normalizados como visto nos SGBDS tradicionais, suas visões buscam

armazenamento histórico/temporal dos dados. Ao desnormalizar as tabelas, o

projetista do DW busca ganhar desempenho nas consultas, contudo não se deve

introduzir redundância em qualquer lugar do modelo.

2.3.1 Arquitetura de um Data Warehouse

Segundo Inmon (1997), um ambiente Data Warehouse tem a

possibilidade de conter dois tipos de dados:

• Primitivos: São dados operacionais ou atômicos, consistem em valores

referentes ao momento presente e são baseados em aplicações, podem

ser atualizados, detalhados e processados repetidas vezes;

• Derivados: São dados de apoio à decisão ou sumarizados, geralmente

valores históricos, baseados em assuntos ou negócios. São resumidos, ou

refinados, não são atualizados, representam valores de momentos já

decorridos ou instantâneos, e são processados de forma heurística.

Dados Primitivos tem como maior benefício a oferta de pesquisa em base

de dados mais ricas, proporcionando análises bem mais minuciosas e cuidadosas

sobre os dados, tal fato leva ao ganho nas verificações de histórico, tendências,

previsões e elaboração de cenários nas análises. A principal desvantagem é que

26

naturalmente o consumo de espaço será maior nos dispositivos de

armazenamento. O consumo da capacidade de processamento, para que não

haja baixo desempenho nas consultas e análises dos dados, também acabam por

agregar custos extras. Dados derivados têm como benefícios estarem resumidos

e armazenados em um formato no qual são facilmente consultados, ocupam

menos espaço e o desempenho das consultas e das análises dos dados é

melhor. A desvantagem é que a sumarização diminui a capacidade de análises e

pesquisas.

De acordo com Kimball et al. (2002), uma arquitetura geral de um DW tem

quatro elementos: Fonte de Dados, Área de data Stage, Dados de Apresentação,

Ferramentas para usuário Final. A Figura 2 demonstra a iteração entre os

elementos.

• Fonte de Dados (Operational Source Systems): Base de dados

transacionais dos sistemas satélites;

• Área de estágio de dados (Data Staging Area): Área de armazenamento de

um conjunto de processos. Neste ponto é realizado o pré-processamento

da informação: limpeza, transformação, combinação, duplicação. Porém

ainda não se encontram em fase de apresentação final;

• Dados de Apresentação (Data Presentation Area): Ponto onde os dados se

apresentam divididos por departamento ou visões necessárias para os

usuários, logo são organizados e armazenados para consulta direta pelos

usuários finais;

• Ferramentas para usuário F

de acesso aos dados, desde ferramentas para a

ferramentas de apoio

Figura

2.3.2 Modelagem Dimensional

A modelagem dimensional

representa uma dimensão

deste conceito. A visão dimensional proporciona uma visão

dados, permitindo um entendimento

uma tomada de decisão

utilizando modelagem dimensional

dados de uma forma facilitada.

Ferramentas para usuário Final (Data Access Tools): Todas as ferramentas

de acesso aos dados, desde ferramentas para análises de negócio até

ferramentas de apoio à construção do DW.

Figura 2 - Elementos do DW (Kimball et al , 2002)

Modelagem Dimensional

dimensional tem a idéia de um cubo onde cada direção

dimensão de negócio, as tecnologias OLAP fazem

deste conceito. A visão dimensional proporciona uma visão privilegiada

dados, permitindo um entendimento melhor sobre problemas que necessitam de

(Ballard et al., 1998). De acordo com Ballard

dimensional o usuário final pode navega

dados de uma forma facilitada.

27

Todas as ferramentas

lises de negócio até

tem a idéia de um cubo onde cada direção

de negócio, as tecnologias OLAP fazem utilização

privilegiada sobre os

sobre problemas que necessitam de

Ballard et al. (1998),

o usuário final pode navegar e explorar os

28

É pertinente lembrar que apesar de se tratar de modelagem dimensional

os dados que serão refletidos nessa modelagem continuam sendo relacionais. A

modelagem dimensional trabalha com três objetos para sua estruturação, Fato,

Dimensões de Negócio e Métrica. Cada um destes objetos é uma tabela do banco

de dados relacional, podendo ser tabelas construídas provenientes das etapas de

preparação de dados, como também podem ser tabelas do próprio sistema que

atendam as necessidades de cada um dos tipos de objeto relatados.

A identificação da tabela de fatos é o primeiro passo para modelagem

dimensional. Os fatos são reunidos na tabela de fatos. Segundo Kimball et al.

(1998), as tabelas de fatos normalmente contêm dados numéricos e somatórios.

Um Fato representa uma transação de negócio, ou seja, nos sistemas essas

tabelas podem ser chamadas de movimentação, têm a característica de serem

compostas por chaves estrangeiras e conterem uma informação quantitativa. A

idealização de uma tabela de fato pode ser exemplificada com uma guia de

atendimento idealizada para planos de saúde suplementar, conforme Tabela 1.

GUIA ID (PK) NUMBER BENEFICIARIO (FK) NUMBER DOENCA (FK) NUMBER EVENTO (FK) NUMBER IDADE NUMBER DATA DATE VALOR NUMBER

Tabela 1 - Tabela de exemplo de Fato

29

O objeto Dimensão é uma tabela em que suas colunas correspondem à

descrição de atributos que estão relacionados com a tabela de fatos. No exemplo

representado na Tabela 1 ficou explícito os nomes das dimensões, onde cada

chave estrangeira representa uma dimensão. Para exemplificar uma tabela de

dimensão vamos utilizar a tabela beneficiário (Tabela 2).

BENEFICIÁRIO

ID (PK) NUMBER

NOME VARCHAR

TELEFONE VARCHAR

DATA_NASCIMENTO DATE

CIDADE (FK) NUMBER

Tabela 2 - Tabela de exemplo de Dimensão

Existem três esquemas utilizados para modelagem dimensional dos dados, são

eles: Esquema Estrela (Star esquema), de onde se derivam os próximos dois

esquemas, Esquema Floco de Neve (Snowflake esquema) e Esquema

Constelação de Fatos (Facts Constallation esquema) (Kimball et al., 2002).

O Esquema Estrela (Figura 3) é uma estrutura de tabela que contém

muitos relacionamentos em sua estrutura. As ligações partem das chaves

estrangeiras contidas em sua estrutura e avançam apenas para o primeiro join

com a tabela correspondente, essa regra idealiza o formato de uma estrela, onde

o fato é o centro e as dimensões são as pontas. O esquema estrela é mais

indicado quando a quantidade de fatos é maior que a quantidade de registros em

cada dimensão individualmente. As tabelas de dimensão são desnormalizadas

para aumentar o desempenho

O esquema Floco de Neve

e normalmente é utilizado quando se tem dimensões gr

ou semiestáticas. Este modelo tem uma vantagem que é subir menos dados para

a memória, porém a consulta tem uma

individualmente. As tabelas de dimensão são desnormalizadas

para aumentar o desempenho (Han et al., 2011).

Figura 3 - Modelo Estrela

O esquema Floco de Neve (Figura 4) desdobra as tabelas de dimensões,

do quando se tem dimensões grandes que são estáticas

. Este modelo tem uma vantagem que é subir menos dados para

a memória, porém a consulta tem uma perda de desempenho.

Figura 4 - Modelo Floco de Neve

30

individualmente. As tabelas de dimensão são desnormalizadas

desdobra as tabelas de dimensões,

andes que são estáticas

. Este modelo tem uma vantagem que é subir menos dados para

31

O esquema Constelação de Fatos é a junção de duas tabelas de fato uma

ou mais tabela de dimensão em comum. Esse modelo também é chamado de

constelação por idealizarem mais de uma estrela.

2.3.3 Tecnologia OLAP

As primeiras tecnologias sobre informação que surgiram vieram do OLTP

(On-Line Transaction Processing), onde se encontram as fontes de dados

transacionais das mais diversas áreas, também chamada de fonte de dados de

sistemas legados das corporações. Segundo Inmon (1997), o tempo de resposta

entre OLTP e OLAP é bem diferente. Em uma simples explicação OLTP são os

dados que definem as movimentações do sistema, enquanto que OLAP tem a

finalidade de explorar os dados das movimentações armazenadas de forma a

analisá-las. A iteração sobre as duas é que OLTP fornece dados para análise e

OLAP executa as análises sobre os dados.

A tecnologia OLAP (On-line Analytical Process) se originou com a

evolução dos sistemas de informação, deixaram de apenas armazenar dados e

eventos do dia a dia e passaram a permitir tomadas de decisões com base em

análises. Visões OLAP parecem ser bem aceitas por usuários finais. Segundo

Inmon (2005), OLAP agrada pela característica de proporcionar uma visão fácil e

intuitiva dos dados dimensionais, por meio de análises em perspectivas

32

diferentes. OLAP é uma abstração, quando se diz OLAP na verdade está se

referindo a uma especialização do método de acesso, que são três: MOLAP

(Multidimensional OLAP), ROLAP (Relacional OLAP) e por último HOLAP (Híbrido

OLAP).

MOLAP: Utiliza SGBD Multidimensional facilitando a navegação entre os

níveis de informação em tempo real, sua eficiência tende a ser melhor. Um

agravante é que todo dado deve ser transformado antes de ser embarcado em

SGBD Multidimensional. Arquitetura de aceso aos dados multidimensionais é o

melhor, porém ainda existem poucas soluções disponíveis no mercado.

ROLAP: Método mais comumente encontrado no mercado, o qual baseia-

se em SGBDS relacionais e agrega a tecnologia já padronizada SQL.

HOLAP: é uma das tendências na arquitetura multidimensional. Os

SGBDS mais famosos como Oracle, MSSQL Server e IBM DB2 já vem

oferecendo suporte à modelagem multidimensional, o que justifica o híbrido, pois

no ambiente relacional já existente é possível mesclar o multidimensional.

O OLAP e Mineração de Dados integram todo e qualquer processo de

suporte à decisão. A maioria dos sistemas de OLAP tem o foco no provimento de

acesso aos dados multidimensionais, enquanto os sistemas de MD lidam com a

análise de influência para os dados de uma única dimensão.

33

A JCP (Java Communyt Process) tem uma API para servidores de

aplicação OLAP chamado de JOLAP, é integrada com o ambiente j2EE (Java 2

Platiform Enterprise Edition). Este modelo suporta conceitos referentes a três

áreas que são chave para as aplicações OLAP: metadados, dados e pesquisas.

Fornecedores como a IBM, ORACLE e Microsoft já desenvolvem soluções

baseadas na API. Uma nova especialização de OLAP também é proposto por

Han et al. (2011), o conceito de OLAM (On-Line Analytical Mining), cujo o foco

tem sido a integração das tecnologias de MD e OLAP e no desenvolvimento

escalável. A implementação de OLAM, apoiará os usuários a realizarem

mineração de dados interativos.

2.3.4 Estrutura Multidimensional

A estrutura multidimensional tem a característica de um cubo, no qual

uma tabela de fatos proporciona as dimensões. Os cubos são apenas visões

lógicas dos dados, chegam à este estado devido às transformações aplicadas na

informação. Segundo Thomsen (2006), o cubo de dados tem o benefício de

oferecer diferentes visões e granularidade, a consequência é a possibilidade de

navegar pelas hierarquias de dados.

A estrutura Multidimensional deve dar condições para que a tecnologia

OLAP realize algumas funções que modificam a visão da dimensão. Essas

funções são descritas por Gray et al. (1996), as operações de cubo de dados

34

foram idealizadas visando suporte de múltiplas agregações, ou seja, é uma

generalização de dimensional da operação group by.

As operações em estruturas multidimensionais são feitas utilizando

ferramentas OLAP, recebem o nome de Slice and Dice, que compreende quatro

operações: Ranging, Drilling, Rotation/Pivotting e Ranking.

A operação Ranging é responsável por, a qualquer momento, alterar o

resultado das consultas, inserindo novas posições ou removendo as que estão

em foco.

A operação Drilling consiste em escolher o que deseja analisar, o analista

ainda pode mudar o escopo do que está analisando, porém os dados podem

encontrar-se agregados em diversos níveis. O Drilling permite navegação por

entre os níveis. Existem três operações OLAP que permitem mudar o escopo dos

dados, são elas: Drill-Down, Drill-Up e Drill-Across.

• A operação Drill Down navega verticalmente na hierarquia, no sentido em

que os dados são mais atômicos (valores simples, indivisíveis e

monovalorados). Consiste em desagregar dimensões;

• A operação Drill-Across permite navegar transversalmente no eixo da

árvore hierárquica. O Drill-Across é uma operação de grande utilidade, pois

permite inserir e retirar posições do corrente cenário;

35

• O Drill Up ou Roll Up faz parte da operação Drilling e realiza a função

inversa do Drill-Down. Ela permite ao usuário uma visão mais agregada

das informações.

A operação Rotation ou Pivot além de permitir ao analista mudar as

posições das dimensões em foco, possui também a flexibilidade de alterar o eixo

de visualização dos dados, alterando linhas por colunas, com intuito de facilitar a

compreensão. Vale salientar que Rotation não adiciona nem retira posições do

cenário, mas permite ao analista alterar a visão que se tem dos dados. Um

exemplo desta operação seria alterar uma dada dimensão da horizontal para a

vertical, e desta forma o cubo tem a rotação no sentido horário.

36

3 Implementação

Neste capítulo são descritos o ambiente, ferramentas, detalhes sobre os

dados e sua área de negócio e a implementação de algumas das tecnologias

apresentadas nos capítulos anteriores de forma a preparar um DW que

disponibilize visões que esclareçam os dados possibilitando ressaltar a

visibilidade dos metadados para mineração de dados em trabalhos futuros.

3.1 Ferramentas Utilizadas

O projeto foi realizado em dois ambientes virtuais onde concentrou-se o

SGBD com os dados utilizados no ambiente não virtualizado para hospedar

ferramentas e midlewares. A máquina foi virtualizada com o Software Oracle VM

VirtualBox, tendo como imagem para ser o servidor de banco de dados a

appliance com SO Linux Oracle 5 e Banco de dados Oracle 11G devidamente

pré-configurado pela própria Oracle Corporation. Como recurso de hardware

foram disponibilizadas 3 Vcpus e 3072 MB de memória RAM. O desktop

Hospedeiro contempla a configuração de um Processador Intel Core i7 3.2 Ghz, 8

GB Memória RAM DDR 3, SO Ubuntu 12.10 e foi responsável em comportar as

demais ferramentas utilizadas no desenvolvimento e manutenção do DW. Segue

abaixo a lista dos Softwares e Ferramentas mais utilizados para o

desenvolvimento e manutenção do DW:

37

• Oracle JDK 7: JVM para ferramentas e softwares que utilizam tecnologia

java;

• Gedit: Editor de Texto para alterações dos arquivos de configuração;

• Pentaho User Console: Ferramenta para criação dos Datasources,

Dimensões dos Dados, análise multidimensional e Geração de Relatórios;

• Pentaho Administration Console: Gerenciador de usuários e Acesso do

Pentaho User Console;

• Oracle SQL Devoloper: Suite de ferramentas com suporte a manutenção e

administração do Banco de Dados, consultas SQL, e WorkFlow para

mineração de dados.

A escolha das ferramentas teve como pré-requisito ser open-source e não

gerar custo financeiro algum.

3.2 Área de Negócio e Dados Armazenados

A base de dados que a instituição disponibilizou é modelada para atender

empresas no nicho de negócio referente à Administração de Operadoras de Plano

de Saúde Suplementar (Fenaseg, 2012). A federação nacional de saúde

38

suplementar com base na Lei 9.656/1998 define Operadora de Plano de

Assistência à Saúde como sendo a pessoa jurídica, com a finalidade de garantir,

sem limite financeiro, a assistência à saúde, pela faculdade de acesso e

atendimento por profissionais ou serviços de saúde, livremente escolhidos,

visando à assistência médica, hospitalar e odontológica.

Como método de exploração inicial para o conhecimento da base de

dados e localização das tabelas candidatas a Fatos, foi adaptada uma técnica de

seleção de atributos. Han, et al. (2011) idealizam que a seleção de atributos em

um conjunto de dados reduz o tamanho do conjunto. Para viabilização deste

conceito admitiu-se que cada tabela do banco passe a se tornar um atributo

composto de duas informações: Quantidade de Registros e Quantidade de

Relacionamentos. Multiplicando as duas informações resultará em uma

correlação que definirá o valor para o atributo (Tabela). O dicionário de dados

(Metada Data) do próprio SGDB disponibiliza os números sobre quantidade de

dados e relacionamentos. Segundo Date (2004) todo SGBD deve conter um

dicionário de dados onde são armezanados os metadados que identificam e

descrevem os objetos do banco de dados. O projeto utiliza o Banco de dados

Oracle 11G. Para construir o DW com a utilização de Metadados foi executada

uma sequência de 6 passos para definir e identificar tabelas de Fatos e

Dimensão.

Passo um: A primeira etapa é descobrir quais os metadados o SGBD

disponibiliza e quais serão úteis para extrair informação quantitativa referente aos

dados. Para essa tarefa foram utilizadas visões de dicionário de dados estático.

39

Seguindo a documentação fornecida pela Oracle, foram localizados os seguintes

metadados.

Metadados

Nome Descrição

USER_TABLES Nome da tabela, Quantidade de Registros

USER_CONSTRAINTS Nome da Constraint , Tipo

USER_CONS_COLUNMS Associa a Contraint a Coluna da Tabela Tabela 3 - Metadados Utilizados

Passo Dois: Após estudar os metadados e descobrir quais deles

oferecem informações que possibilite determinar a quantidade de

relacionamentos e quantidade de registros de uma tabela, foi possível buscar as

informações referentes ao modelo de dados. Mensurar o tamanho da base de

dados é importante, pois se houver uma quantia pequena de tabelas é mais

rápido e fácil analisá-las sem a ajuda do dicionário de dados. Para essa tarefa foi

elaborada uma consulta SQL para descobrir a quantidade de tabelas do SGBD

com a view USER_TABLES.

SELECT COUNT(*) FROM user_tables;

O retorno para esta consulta foi igual a 510, significando que o esquema

considerado, denominado 'SAUDE2011', tem 510 tabelas, uma quantia razoável

para se analisar.

Passo Três: Como medida para diminuir a quantidade de tabelas a

serem examinadas e criar um atributo que possibilite mensurar a validade da

40

tabela para o DW como fato ou dimensão, elaborou-se uma segunda consulta

SQL que retorna dois campos (quantidade de registros por tabelas e quantidade

de chaves estrangeiras por tabela), esses dois campos compõem um terceiro que

foi calculado (Ranking), conforme apresentado na Figura 5.

A consulta foi executada algumas vezes para calibrar os valores

retornados de forma que não ficassem muito discrepantes. Os campos utilizados

nos filtros como parâmetro de calibragem foram qtd_row e qtd_fk. Também

acrescentou-se outros dois filtros, que foram indicados pelo gestor do negócio,

que são as tabelas que iniciam com "Z_" e "W_" as quais não devem ser

consideradas como úteis para o negócio.

Figura 5 - Consulta para ranking de Fatos

41

A consulta final para análise das tabelas obteve como filtro os seguintes

critérios: liste todas as tabelas que contenham mais de 44 registros, mais de uma

chave estrangeira, o nome das tabelas que não comecem com 'Z_' ou 'W_'.

Obteve-se 42 registros, ou seja, 42 tabelas para análise e estudo na modelagem

do DW. A amostra dos resultados se encontra no Anexo A, Tabela de Ranking.

De acordo com Barros et al. (2011), que em sua pesquisa utilizou o

mesmo esquema do banco de dados, porém com dados diferentes, pois o

intervalo de data dos dados não era o mesmo. Em seu trabalho são descritas

algumas das tabelas que foram previamente selecionadas até aqui utilizando as

consultas de metadados. Segue a Tabela 4 com a descrição das tabelas

utilizadas por Barros et al. (2011).

Tabela Descrição

GRS-BENEFICIARIO Todos os beneficiários inscritos no plano de saúde

GRS-GUIA Dados dos processos administrativos e diagnósticos

GRS-GUIA-EVENTO Eventos utilizados pelos Beneficiários

SAM-TGE Tabela geral de eventos

SAM-CID Contém todos os códigos possíveis de CID

Tabela 4 - Descrição das Tabelas (Barros, et.al., 2 011)

Passo Quatro: Observando os resultados da consulta que consta no

Anexo A, foi selecionada as duas primeiras tabelas que estão melhores

ranqueadas, GRS_GUIA_EVENTO e GRS_GUIA, para estudar a viabilidade da

Tabela de Fatos. A s

relacional.

Figura 6 – Estrutura

A tabela GRS_GUIA caracterizou

GRS_GUIA_EVENTO é uma tabela que detalha movimentos das guias,

caracterizou como um detalhe do

duas tabelas, porém, GRS

vezes. Por meio da análise dos registros da tabela GRS

os dados referem-se a uma guia com vários dados administrativos e relacion

sobre consultas e manobr

elas tem a característica de dimensão

seguir, na Figura 6, pode-se visualizar sua

Estrutura das tabelas GRS_GUIA_EVENTO e GRS_GUIA

GUIA caracterizou-se como uma tabela de movimento

EVENTO é uma tabela que detalha movimentos das guias,

detalhe do movimento, ou seja, cada registro é

duas tabelas, porém, GRS_GUIA_EVENTO pode conter GRS

da análise dos registros da tabela GRS_GUIA

se a uma guia com vários dados administrativos e relacion

sobre consultas e manobras hospitalares, qualquer tabela que se relacionem a

tem a característica de dimensão.

42

visualizar sua estrutura

tabelas GRS_GUIA_EVENTO e GRS_GUIA

tabela de movimento.

EVENTO é uma tabela que detalha movimentos das guias, que se

, ou seja, cada registro é único nas

EVENTO pode conter GRS_GUIA muitas

GUIA, verificou-se que

se a uma guia com vários dados administrativos e relacionem

qualquer tabela que se relacionem a

43

Os detalhes e comportamento nas tabelas indicam que o esquema ideal

para modelar as dimensões é o Floco de Neve, como citado na sessão 2.3.2.

Pode-se tirar proveito de mais informações devido o relacionamento acrescentar

dados, a desvantagem é que pode-se ter uma perda de desempenho devido aos

joins que deverão ser realizados para atingir as dimensões finais.

Passo Cinco: Modelando a tabela de Fatos. A possibilidade de criar uma

nova tabela com a união de GRS_GUIA_EVENTO e GRS_GUIA é inevitável. Ao

estudar o significado dos atributos de cada uma das tabelas da Figura 6

constatou-se que a ma união das duas tabelas para transformá-las em uma única

tabela de fato levaria a um prejuízo computacional. Para a análise levantou-se, a

quantidade de dados nas tabelas, a primeira tabela GRS_GUIA_EVENTO

resultou em um total de 6.624.780 linhas e a segunda GRS_GUIA 1.629.151

linhas, isso significa que haveria 6.624.780 linhas duplicadas se criada a nova

tabela, porém este é apenas uma perda de espaço para armazenamento, se a

tabela de fatos não for criada toda vez que for necessário acessar os dados

referentes à doença da guia terá a execução de um join.

A seleção dos atributos entre as duas tabelas de fatos obteve como

critério as seguintes regras: preservar a chave primária das duas tabelas,

preservar as chaves estrangeiras de tabelas que forem identificadas como

dimensão, campo texto que descreva a movimentação e campos de data. Se o

campo for repetido deve permanecer o campo da tabela com maior número de

registros.

44

Passo Seis : O próximo passo é identificar as outras dimensões. Uma

forma de automatizar é novamente utilizar uma das tabelas do dicionário de

dados do SGBD, tabelas USER_CONSTRAINT e USER_CONS_COLUMNS. A

consulta da Figura 7 retorna as tabelas que se relacionam com a tabela de Fatos

em primeiro nível.

Figura 7 - Consulta para identificação de Dimensões

Como os resultados da consulta estão refletindo apenas as associações

de primeiro nível, a cada resultado é observado se existe uma nova tabela listada.

Caso exista e seja identificado interesse em localizar as suas associações

acrescenta-se o nome da tabela na cláusula "in" da última linha. Cada vez que

forem adicionadas as tabelas entre um resultado e outro, soma-se um nível na

profundidade dos relacionamentos das tabelas. As tabelas com o resultado da

consulta sendo executada 3 vezes consta na tabela do Anexo B, a coluna

TABELA_REFERENCIA em que cada linha representa uma possível Dimensão.

45

Os passos executadas até aqui representam a primeira etapa do

processo KDD, Seleção, conforme citado na Seção 2.1, tendo como resultado a

tabela de fatos selecionada usando o valor do maior ranking de registros e

relacionamentos. Com essa tabela tem-se uma pré-definição do tipo de esquema

para modelagem multidimensional a ser utilizada, Floco de Neve. Com base no

SQL demonstrado na Figura 7 acima, foram identificadas as possíveis dimensões.

Após identificar as tabelas que estão envoltas dos fatos, foram analisadas e pré-

processadas resultando em limpeza e criação de novos atributos. Com base em

estudo e acompanhamento dos relacionamentos entre as tabelas e quantidade de

dados, foram definidas as tabelas do Anexo C para criação da consulta.

3.3 Pré-processamento e Transformação dos dados

A limpeza de dados tem como objetivo a qualidade. Os problemas de

qualidade podem ser encontrados em dados que sejam provenientes de uma

única fonte, como arquivos texto, xls e banco de dados, ou também em dados de

múltiplas fontes, dados que não estão integrados em seu armazenamento. Como

dito no Seção 1.1, o intuito deste trabalho é gerar informação a partir de um Data

Warehouse, e com a ajuda de ferramentas de BI que possibilite a exportação de

informações para MD, sendo aplicados alguns dos conceitos de limpeza.

Critérios de limpeza podem exigir que linhas sejam excluídas, mas isso pode ter

impacto negativo para um DW, as informações para DW são de nível histórico e a

ausência de um determinado dado pode não ser compreendida pela regra de

46

negócio. A falta de dados em uma ou mais colunas pode justificar a exclusão de

linhas, no entanto, considerar outra análise que estabeleça uma outra visão dos

dados pode considerar linhas excluídas como importantes. Sendo assim, a

ausência ou a presença de dados que prejudique a mineração deverá ser tratada

com os devidos filtros que ferramentas OLAP disponibilizam.

Consultas com ferramentas OLAP podem ser executadas de forma direta

nas tabelas ou à partir de visões em base de dados, porém isso pode afetar

diretamente o desempenho das consultas. O DW é uma base de dados com

informações históricas que são originadas de uma ou mais base de dados. Muitas

vezes os dados já são transformados para atender o novo modelo que é

devidamente preparado para comportar esses dados. Algoritmos utilizados na

mineração de dados requerem que os dados estejam formatados, existindo assim

a necessidade da aplicação de operações de transformação. Para atender as

duas funcionalidades é necessário adicionar descrição para dados discretos e

discretizar dados descritivos.

Os dados para o DW foram gerados a partir do Modelo relacional que

consta no Anexo C, modelo este que foi gerado a partir das consultas elaboradas

com os Metadados no modelo gerado, conforme ilustrado na Figura 8, algumas

tabelas foram unidas e outras tiveram atributos retirados da visão para o DW. A

tabela de fato foi recriada com a união dos atributos das tabelas GRS_GUIA e

GRS_GUIA_EVENTO. As tabelas que se tornaram dimensão tiveram a

quantidade de atributos reduzida significativamente. Procurou-se deixar apenas

dados descritivos e ID para essas tabelas.

47

Figura 8 – Modelo do DW gerado

Descrição das tabelas:

• Tabela FATO_GUIA: Tabela de Fato do esquema floco de neve com

informações de uma guia de plano de saúde. Essa tabela de fato é

resultante da união de campos das tabelas GRS_GUIA e

GRS_GUIA_EVENTO ;

• Tabela DIMENSAO_CID: Tabela de dimensão com relacionamento em

primeiro nível com a tabela de fato, descreve as Classificações

Internacional de Doenças, tabela relacional com a estrutura original é a

SAM_CID ;

• Tabela DIMENSAO_EVENTO: Tabela de dimensão com relacionamento

em primeiro nível com a tabela de fato, descreve os eventos utilizados pela

guia, tabela relacional com a estrutura original é a SAM_TGE;

• Tabela DIMENSAO_BENEFICIARIO: Tabela de dimensão com

relacionamento em primeiro nível com a tabela de fato, contem dados

sobre o beneficiário do plano de saúde, tabela relacional com a estrutura

original é a GRS_BENEFICIARIO;

DIMENSAO_CIDADE

DIMENSAO_BENEFICIARIO

DIMENSAO_ESTADO

FATO_GUIA

DIMENSAO_CID

DIMENSAO_EVENTO

48

• Tabela DIMENSAO_CIDADE: Tabela de dimensão com relacionamento em

primeiro nível com DIMENCAO_BENEFICIARIO. Contem o nome da

cidade para possibilidade de filtros regionais em nível de cidade, a tabela

relacional com a estrutura original é a CIDADES;

• Tabela DIMENSAO_ESTADO: Tabela de dimensão com relacionamento

em primeiro nível com DIMENSAO_BENEFICIARIO. Tabela relacional com

a estrutura original é a ESTADOS.

3.4 Projeto

O modelo de dados foi desenvolvido para facilitar a etapa de Mineração

de Dados evitando que se faça necessário percorrer os passos de preparação de

dados toda vez que se queira dados da base onde construiu-se o DW. Com o DW

devidamente preparado, uma ferramenta de BI que disponibilize a opção de

consultas OLAP se integrará com DW elaborado. A ferramenta será

disponibilizada na web afim de que possa disponibilizar uma forma

descentralizada de realizar as consultas e geração de dados.

O projeto foi elaborado em três camadas: O DW que são os dados

armazenados, servidor web hospedando a aplicação que contém ferramenta

OLAP para disponibilizar as informações de forma não centralizada, ou seja, os

clientes podem acessar de forma fácil sem a necessidade de instalar nada em

seus computadores e a última camada que é a de apresentação, realizada

através dos browsers. A partir do browser será possível acessar o sistema com

alguns datasources2 já salvos, isso possibilitar

e os utilizem em ferramentas espec

senha para acesso.

A Figura 9 mostra a tela de login do Pentaho, que foi a ferramenta de

utilizada neste trabalho.

O Pentaho BI Server

possibilitam o seu funcionamento, a

• Jpivot: Framework

• Tom Cat: Servidor WEB para publicação do sistema.

• Jetty: É um servidor HTTP e Servlet Container escrito em Java

http para servlet.

2 Datasource é uma fábrica de conexões a fonte de dados física

já salvos, isso possibilitará que usuários exportem os dados

utilizem em ferramentas específicas para MD desde que tenham

igura 9 mostra a tela de login do Pentaho, que foi a ferramenta de

Figura 9 - Tela de Login pública

O Pentaho BI Server é formado a partir de algumas ferramentas que

sibilitam o seu funcionamento, as principais são:

k web para consultas olap.

Servidor WEB para publicação do sistema.

um servidor HTTP e Servlet Container escrito em Java

Datasource é uma fábrica de conexões a fonte de dados física (Oracle, 2012).

49

que usuários exportem os dados

desde que tenham login e

igura 9 mostra a tela de login do Pentaho, que foi a ferramenta de BI

ir de algumas ferramentas que

um servidor HTTP e Servlet Container escrito em Java, Servidor

50

• Spring Security: Para controle de autenticação forte e altamente

personalizável e quadro de controle de acesso. É o padrão de fato para

proteger aplicativos.

• Hibernate: Mapeamento objeto-relacional escrito na linguagem Java,

responsável em mapear as bases de dados com o DW.

• Quartz: Serviço de agendamento de tarefas que podem ser integrados ou

utilizadas praticamente em qualquer versão Java EE ou aplicativo Java SE.

• HyperSQL: Servidor de banco de dados (SGBD), de código aberto, escrito

totalmente na linguagem Java. Não é possível compará-lo, em termos de

robustez e segurança com outros servidores SGBD, como Oracle ou

Microsoft SQL Server.

O Pentaho Analysis View tem as operações OLAP do tipo Slice and Dice

e estão dispostas para serem utilizadas pela ferramenta Pentaho cuja finalidade é

facilitar a execução e visualização multidimensional dos dados contidos no Data

Warehouse. Outras operações: Adicionar filtros nos dados, alterar a dimensão

das colunas e linhas, ordenar tabela com base em colunas também são

contempladas na ferramenta.

A Figura 10 abaixo apresenta as opções Slice and Dice para consultas

multidimensionais, que estão dividas em três grupos:

Filters: Todas os objetos possíveis para gerar a consulta tanto em linha com em

coluna.

Columns: Objetos arrastados para esta opção serão agrupadores de colunas.

Rows: Objetos arrastados aqui serão

simples em tabelas de banco de dados.

A Figura 11 demonstra dados pré

entre um e oitenta anos, Estado Santa Catarina e metadados são a soma do valor

das ocorrências da CID, idade média dos

Objetos arrastados para esta opção serão agrupadores de colunas.

s arrastados aqui serão exibidos linha a linha com uma consulta

simples em tabelas de banco de dados.

Figura 10 - Pentaho Analysis View

demonstra dados pré-filtrados em que a faixa de idade est


da CID, idade média dos Beneficiários e Quantidade de

51

Objetos arrastados para esta opção serão agrupadores de colunas.

exibidos linha a linha com uma consulta

faixa de idade está


e Quantidade de

ocorrências da CID na categoria do se

Para exportação de dados com o intuito de aplicar técnicas de mineração de

dados essa visão não é a ideal

longas. Quando o DW é modelado

associados um número que o identifica

Figura 11 - Resultado de consulta multidimensional utilizando f iltros

O cenário hipotético

Figura 11, e a mineração d

para um plano de saúde suplementar

Santa Catarina tem utilizado o

que os outros estados apesar de conter mais

maior lucro.

Padrões que IDADE_EXC

investigada) podem localizar:

da CID na categoria do sexo. Os dados descritivos são sexo e CID.

ara exportação de dados com o intuito de aplicar técnicas de mineração de

dados essa visão não é a ideal, pois a coluna CID Nome cont

o DW é modelado garante-se que para essas descrições

mero que o identifica Chave Primária.

Resultado de consulta multidimensional utilizando f iltros

hipotético, no qual os dados selecionados e

igura 11, e a mineração de dados têm a possibilidade de encontrar padrões

para um plano de saúde suplementar, pode ser o seguinte: mulheres da região de

Santa Catarina tem utilizado o plano de saúde suplementar com

apesar de conter mais beneficiárias, tal fato tem gerado

IDADE_EXC (idade média dos beneficiários) e a CID

podem localizar:

52

ados descritivos são sexo e CID.

ara exportação de dados com o intuito de aplicar técnicas de mineração de

pois a coluna CID Nome contém descrições

se que para essas descrições fiquem

Resultado de consulta multidimensional utilizando f iltros

estão conforme à

m a possibilidade de encontrar padrões úteis

ulheres da região de

suplementar com menor frequência

, tal fato tem gerado

) e a CID (doença

53

• Doença "X" ocorre com uma idade média menor que a Doença y em Santa

Catariana?

• Doença "X" ocorre com uma idade média maior que a Doença y em Santa

Catariana?

• Doenças com média de idade maior que "X" sempre tem um valor menor?

• Doenças com média de idade menor que "X" sempre tem um valor menor?

Após definida a visão que contenha variáveis possibilitando a resposta do

problema alvo da mineração, o próximo passo é exportar a informação para um

arquivo txt, isso deixa a possibilidade de que seja escolhido um software de

mineração de dados, como por exemplo o WEKA, e sejam utilizados os dados do

Data Warehouse gerado com metadados para mineração de dados.

54

4 Conclusões e Trabalhos Futuros

Com base na implementação realizada na base de dados disponível, foi

possível a construção de um Data Warehouse utilizando consultas que, com o

auxilio das informações dos metadados do SGBD, não se exige o conhecimento

pleno da regra de negócio a qual os dados se referem. Tal fato contribui para a

aceleração das consultas multidimensionais.

A utilização da ferramenta Pentaho, depois de ter o data warehouse

configurado para que se beneficie dos dados, foi capaz de oferecer filtros para

gerar visões da informação e exportar o arquivo com os dados para um csv,

formato este que a maioria das ferramentas de MD utilizam.

Como trabalho futuro sugere-se:

Realizar uma comparação entre os resultados de uma mineração de

dados pré-processados com técnicas automatizadas contra os gerados através do

Pentaho.

55

5 Referências Bibliográficas

Ballard, Chuck, et al. Data Modeling Techniques for Data Warehousing. San Jose : IBM

Corporation, 1998.

Barros, Everton, et al. Pré-Processamento para mineração de dados sobre benificiários

de planos de sáude sunplementar. Journal Health Informatics. 2011.

Date, C, J. Introdução a sistemas de Banco de Dados. São Paulo : Campus, 2004.

Fayyad, Usama, Piatetsky-Shapiro, Gregory e Smyth, Padharaic. 1996. From Data Minig to

Knowledge Discovery in Databases. AI MAGAZINE. 1996, Vol. 17, 3.

Fenaseg. 2012. O que é Saúde suplementar. O que é Saúde suplementar.

Disponível em: <http://www.fenase.org.br>

Acessado em 17/11/2012.

Gardner, S. 1998. ACM Digital Library. Building the Data Warehouse.

Disponível em: <http://www.acm.org/sigmod/record/index.html>

Acesso em: 07/08/2012.

Gray, Jim, et al. Data Cube: A realtion Aggregation Operator Generalizing Group-By,

Cross-tab, Sub-Totals. San Jose : Kluwe Academic Publishers, 1996.

Han, Jiawei, Kamber et al. Data Mining Concepts and Techniques. Boston : Elsevier Inc,

2011.

Inmon, W,H. Contruindo um Data Wharehouse. s.l. : Whiley Publishing, Inc, 1997.

Kimball, Ralph e Ross, Margy. The Data Warehouse Toolkit. New York : John Wiley &

Sons, Inc., 2002.

Kimball, Ralph, et al. The Data Warehouse Lifecycle Toolkit. New york : Jhon Wilet &

Sons, Inc, 1998.

Oracle. Static Data Dictionary Views. docs.oracle.com.

Disponível em: <docs.oracle.com/cd/B19306_01/server.102/b14237.pdf>

Acesso em: 12/12/2012

Oracle. Interface DataSource. docs.oracle.com.

Disponível em: <docs.oracle.com/javase/6/docs/api/javax/sql/DataSource.html>.

Acesso em: 12/12/2012

56

Sommerville, Ian. Engenharia de Software. São Paulo : Addilson Wesley, 2007.

Thomsen, E. 2006. Sistemas de Informação Multidimencionais . São Paulo : Campus,

2006.

57

Anexos

58

Anexo A - Tabela de Ranking

Tabela Qtd. Linhas Qtd. Fks (Linha * Fk)

GRS_GUIA_EVENTO 6624780 3 19874340

GRS_GUIA 1629151 7 11404057

GRS_BENEF_PROCESSADO 1874552 3 5623656

GEP_ACAOBENEFICIARIO 699029 7 4893203

GRS_IMPORTACAO_BENEFICIARIO 1600807 2 3201614

GRS_BENEF_ESTRATIFICADO 698990 3 2096970

GRS_BENEFICIARIO 186005 11 2046055

GRS_MOTIVO_ESTRATIFICACAO 381505 5 1907525

CRM_TAREFA 170702 10 1707020

LOGRADOUROS 556648 2 1113296

GRS_BENEFICIARIO_SITUACAO 86081 4 344324

SAM_TGE 62782 5 313910

GEP_PROTOCOLOBENEFICIARIO 21063 7 147441

QT_QUESTAO_BENEF 34828 3 104484

SAM_TGE_EPHARMA 8529 4 34116

SAM_TGE_EPHARMA_XML 8159 4 32636

GRS_DIAGNOSTICO_CID 12273 2 24546

CRM_TAREFA_HISTORICO 5581 4 22324

GRS_ROT_QUALIFICACAO_BENEF 7766 2 15532

SAM_TGE_AMB92 3749 4 14996

GRS_BENEFICIARIO_HOMONIMO 5495 2 10990

GRS_REPROC_INQ_PROTOC 3459 2 6918

GEP_ACAO 204 6 1224

MUNICIPIOS 373 3 1119

R_DETALHECAMPOS 260 4 1040

GRS_usuárioAVISO 476 2 952

QT_QUESTIONARIO_GRUPO_QUESTAO 234 3 702

GEP_ACAOPADRAO 100 6 600

GRS_AGENDA 99 6 594

GEP_LISTARESPOSTA 229 2 458

GRS_usuário_AGENDA 208 2 416

GRS_GRUPOEVENTOS_EVENTO 163 2 326

GRS_ROT_INCLUIR_PROTOC 77 4 308

GEP_LISTARESPOSTAPADRAO 125 2 250

GRS_ROTESTRATIFICA_REGRA 123 2 246

GEP_PROTOCOLO 104 2 208

GRS_REGRA_REGRA 101 2 202

GEP_RESPOSTAQUESTOES 47 4 188

GRS_ROT_IMPORTACAO 81 2 162

R_DETALHES 81 2 162

59

Tabela Qtd. Linhas Qtd. Fks (Linha * Fk)

GRS_TIPOACAO_TIPOusuário 65 2 130

GRS_PROGRAMA_REGRA 53 2 106

AUT_ACCOUNTPERMISSIONS 46 2 92

Anexo B - Tabelas para dimensão.

Tabelas para dimensão.

60

Anexo C - Estrutura dos dados selecionados

Estrutura dos dados selecionados

61

Documents

Modelagem de um Data Warehouse para mineração de dados ... Amaro de Mello... · Especialização em Desenvolvimento de Sistemas para Web Wlademir Amaro de Mello Modelagem de um