DATA WAREHOUSE

Profa Marilde Terezinha Prado Santos

Universidade Federal de So CarlosDepartamento de ComputaoCurso de Ps-Graduao Lato-Sensu emComputao

0

Apostila originalmente organizada por:

Profa. Marina Teresa Pires Vieira Prof. Joaquim Cezar Felipe

1

1. INTRODUO.............................................................................................................................................3

2. CONCEITOS BSICOS...............................................................................................................................6

2.1. Data Warehouse.................................................................................................................................62.2. Data Warehousing..............................................................................................................................62.3. Aplicaes sobre o DW para tomada de deciso...............................................................................82.4. Caractersticas de Data Warehouses.................................................................................................92.5. Arquitetura do Data Warehousing...................................................................................................112.6. Data Mart (DM)...............................................................................................................................12

3. MODELAGEM DE DADOS PARA DW..................................................................................................14

3.1. Modelo de dados multi-dimensional................................................................................................143.2. Consultas OLAP...............................................................................................................................183.2.1. Agregao......................................................................................................................................193.2.2. Roll-up...........................................................................................................................................203.2.2. Drill-down.....................................................................................................................................203.2.4. Pivot...............................................................................................................................................213.2.5. Comparando com consultas SQL..................................................................................................22

4. PROJETO DO DATA WAREHOUSE.....................................................................................................24

4.1. Esquema Estrela...............................................................................................................................244.2. Esquema Snowflack..........................................................................................................................264.3. Constelao de Fatos.......................................................................................................................284.3. Vises Materializadas......................................................................................................................284.4. Construindo um Data Warehouse....................................................................................................304.5. Resumindo as Funcionalidades Tpicas de Data Warehouses.........................................................334.6. Consideraes Finais.......................................................................................................................344.6.1. Data warehousing e Vises...........................................................................................................344.6.2. Dificuldades na Implementao de Data Warehouses..................................................................35

5. SERVIDORES OLAP.................................................................................................................................37

5.1. Servidores ROLAP............................................................................................................................375.2. Servidores MOLAP...........................................................................................................................385.3. Servidores HOLAP...........................................................................................................................385.4. On-Line Analytical Mining (OLAM)................................................................................................39

APNDICE A..................................................................................................................................................41

ESTUDO DE CASO 1 Criao de um Data Mart...............................................................................41

APNDICE B...................................................................................................................................................47

ESTUDO DE CASO 2 Cubo Multidimensional e OLAP......................................................................47

Bibliografia.......................................................................................................................................................55

2

1. Introduo

Os recursos tecnolgicos de hardware e software, oferecidos nos ltimos

anos, para o armazenamento, manuteno e compartilhamento de dados tm

permitido aos usurios de banco de dados (empresas, pesquisadores e rgos

governamentais) manter quantidades de informaes cada vez maiores em suas

bases de dados. Esse grande volume de dados excede a capacidade humana de

anlise e compreenso, mesmo utilizando mtodos como planilhas eletrnicas e

ambientes de consulta ad hoc. Isso motivou o grande investimento em pesquisa,

que vem ocorrendo, relacionada s tecnologias envolvidas em data warehousing.

Data Warehousing e On-Line Analytical Processing (OLAP) surgiram como

elementos essenciais de apoio deciso, atraindo ateno cada vez maior da

indstria de bancos de dados. Data Warehousing uma coleo de tecnologiasde apoio deciso, voltadas a capacitar o usurio a realizar tomadas de deciso

mais rpidas e precisas. O produto da aplicao dessas tecnologias o Datawarehouse, uma base de dados temporais, no volteis e integrados, tipicamentemantida separadamente da base de dados operacionais da empresa. Para

facilitar anlises e visualizaes complexas, os dados em um warehouse so

modelados de forma multi-dimensional, onde cada elemento de importncia

relevante corresponde a uma dimenso (por exemplo, produto, tempo e regio

num warehouse de vendas). Essas dimenses podem ser organizadas em

hierarquias (por exemplo, produto - tipo - categoria) a fim de permitir diferentes

nveis de agregao durante a anlise.

OLAP (on-line analytical processing) um conjunto de operaes quepodem ser executadas sobre o Data Warehouse, a fim de viabilizar a extrao

eficaz de informaes do mesmo. Os requisitos funcionais e de desempenho

dessas operaes so totalmente diferentes do processamento de transaes on-

line (OLTP on-line transaction processing) das aplicaes tradicionalmentesuportadas pelas bases de dados operacionais.

3

Aplicaes OLTP tipicamente automatizam tarefas de processamento dedados tais como transaes bancrias e de processamento de pedido que so

operaes rotineiras de uma organizao. Essas tarefas so estruturadas e

repetitivas e consistem de transaes curtas, atmicas e isoladas. As transaes

requerem dados detalhados, atualizados e realizam operaes de leitura e escrita

envolvendo alguns (poucos) registros tipicamente acessados atravs de suas

chaves primrias. O tamanho das bases de dados operacionais geralmente varia

de centenas de megabytes a gigabytes. Aspectos crticos a considerar so a

consistncia e recuperao da base de dados e a mtrica de desempenho chave

maximizar o throughput das transaes. Consequentemente, o banco de dados

projetado para refletir a semntica operacional de aplicaes conhecidas e, em

particular, para minimizar conflitos de concorrncia.

Data warehouses, em contraste, so totalmente distintos de bancos de

dados tradicionais em sua estrutura (so estruturados para suporte deciso),

funcionamento, desempenho e propsito. Dados histricos, sumarizados e

consolidados so mais importantes do que registros individuais detalhados.

Alm dos recursos para tomada de deciso oferecidos pela tecnologia

OLAP, existem tambm tcnicas e ferramentas "inteligentes", com o objetivo de

fazer emergir, automaticamente, padres e regras de relacionamento entre os

dados, na busca do conhecimento intrnseco aos mesmos. Essas tcnicas e

ferramentas so objeto de estudo de uma linha de pesquisa que aborda o

chamado processo de extrao de conhecimento de Bases de Dados (Knowledge

Discovery in Databases - KDD). Uma etapa desse processo de KDD a de

aplicao de mtodos de Data Mining, que consiste na aplicao de algoritmos

especficos sobre uma base de dados, com o objetivo de produzir uma srie

particular de padres e regras que relacionem os dados da base de dados.

Resumindo, para a obteno de informaes contidas em um warehouse

tem-se um conjunto de ferramentas de anlise e explorao dos dados, utilizando

consultas em SQL , consultas OLAP e tcnicas de data mining. Consultas SQL

so construdas baseadas na lgebra relacional, com algumas extenses; OLAP

fornece idiomas de consulta de nvel mais alto com base no modelo de dados

4

multi-dimensional e data mining fornece operaes de anlise mais abstratas.

Nos prximos captulos so tratados os principais conceitos, questes e

tecnologia envolvidos com Data Warehouse, procurando focalizar os vrios

aspectos envolvidos nessa abordagem.

5

2. Conceitos Bsicos

2.1. Data Warehouse

Data warehouses tm sido desenvolvidos nas organizaes para resolver

necessidades particulares, com enfoques variados, no possuindo, portanto, uma

definio nica para o termo. Data warehouses so projetados para suportar

extrao, processamento e apresentao eficientes de informao para

propsitos analticos e tomada de deciso.

O significado de data warehouse tem sido difundido em uma variedade de

maneiras na literatura. Inmon [1] caracterizou data warehouse (DW) como uma

coleo de dados orientada a assuntos, integrada, no voltil e varivel no

tempo, que usada para apoio a decises gerenciais. Em comparao a bancos

de dados tradicionais, data warehouses geralmente contm quantidade muito

grande de dados vindos de diversas fontes que podem incluir bancos de dados de

diferentes modelos de dados e algumas vezes arquivos adquiridos de sistemas e

plataformas independentes.

2.2. Data Warehousing

Data Warehousing um conceito cada vez mais poderoso de aplicao de

tecnologia de informao para resolver problemas empresariais. Compe-se de

um conjunto de tecnologias de software e hardware voltadas a viabilizar e

otimizar a anlise de dados em larga escala, gerando informaes gerenciais

valiosas. Algumas das principais tecnologias utilizadas so:

Gerenciadores de bases de dados distribudas, que suportam

processamento paralelo.

Produtos de converso de dados operacionais.

6

Tecnologia cliente/servidor que permite acesso a dados distribudos em

mltiplas plataformas.

Integrao de ferramentas de anlise e relatrio em ambiente de

escritrio (desktop).

Algumas aplicaes usuais de Data Warehousing:

Anlises de vendas e marketing.

Movimentao de inventrio e acompanhamento de produo em

manufaturas.

Otimizao de rotas e anlise de riscos em transportes.

Anlise de lucros e riscos em empreendimentos bancrios.

Anlise de reclamaes ou deteco de fraudes em seguradoras.

Dados operacionais: so os dados originais utilizados nas transaes normaisdos sistemas, ou seja, aqueles dados tipicamente armazenados, recuperados e

atualizados pelo sistema. So majoritariamente armazenados em bases de dados

relacionais, podendo, porm, ser armazenados em bases hierrquicas ou at

mesmo em arquivos simples. Algumas das caractersticas dos dados operacionais

incluem:

Freqentemente atualizados atravs de transaes online.

Dados no histricos (geralmente no mais que trs a seis meses

anteriores).

Otimizados para processamento transacional.

Tipicamente normalizados em bases relacionais, para otimizar

atualizaes, manuteno e integridade.

Dados informativos: so dados tipicamente armazenados com o objetivo detornar a anlise mais eficaz. Essa anlise pode ocorrer na forma de ambientes de

apoio deciso, sistemas de informaes executivas ou anlises estatsticas

7

sofisticadas. Os dados informativos so criados a partir de selees e

sumarizaes criteriosas executadas sobre os dados operacionais.

O Data Warehouse uma base de dados composta por dados informativos.

A criao do Data Warehouse a partir dos sistemas de dados operacionais a

principal etapa do processo de data warehousing como um todo. A construo da

base de dados informativos feita utilizando-se ferramentas de transformao e

propagao. Essas ferramentas no apenas movem os dados de mltiplas fontes,

mas freqentemente manipulam os dados para um formato mais apropriado para

o warehouse. Essas alteraes podem ser:

Criao de novos campos com resultados de clculos.

Criao de novos campos com sumarizaes de dados.

Desnormalizao de dados com propsitos de performance.

Compatibilizao de campos oriundos de diferentes bases.

2.3. Aplicaes sobre o DW para tomada de deciso

Uma tendncia com relao abordagem de data warehousing o

desenvolvimento de poderosas ferramentas de anlise. H trs amplas classes de

ferramentas de anlise emergentes [4]:

a) SGBDs projetados para suportar consultas complexas eficientemente.

Tais sistemas podem ser considerados como SGBDs relacionais otimizados para

aplicaes de suporte deciso.

b) sistemas que suportam uma classe de consultas que envolvem

tipicamente o operadores group-by e de agregao. Aplicaes dominadas por

tais consultas so as chamadas OLAP (On-Line Analytical Processing). Esses

sistemas suportam um estilo de consulta em que os dados so melhor pensados

como um array multi-dimensional. Aplicaes OLAP permitem a analistas,

gerentes e executivos realizar insights pelos dados normalmente armazenados

em data warehouses atravs de acessos rpidos, consistentes e interativos,

com uma ampla variedade de possveis vises sobre as informaes, para refletir

8

a real dimenso do negcio. OLAP implementado num ambiente cliente/servidor

multiusurio e deve oferecer respostas rpidas a consultas, independentemente

do tamanho ou da complexidade da base de dados. OLAP ajuda o usurio a

sintetizar informaes atravs de visualizaes comparativas personalizadas,

assim como atravs de anlises de dados histricos em diferentes modelos de

cenrios.

c) ferramentas para anlise exploratria de dados ou data mining, em queo usurio procura por padres de interesse. Por exemplo, um comerciante de

venda por catlogo pode querer analisar os registros dos clientes para identificar

clientes em potencial para uma nova promoo; essa identificao pode depender

do nvel de salrio, padres de compra, reas de interesse demonstradas, etc.

Em situaes como essa, muito difcil formular uma consulta que captura a

essncia de um padro de interesse e a quantidade de dados muito grande

para permitir anlise manual ou mesmo anlise estatstica tradicional.

2.4. Caractersticas de Data Warehouses

Um Data Warehouse apresenta as seguintes aspectos que os distinguem de

bancos de dados transacionais:

Possui um modelo de dados apropriado o modelo de dados multi-

dimensional se adequa bem para OLAP e tecnologia de suporte deciso;

um armazm de dados integrados vindos de mltiplas fontes, processados

para armazenamento em um modelo multi-dimensional;

Tipicamente suportam sries de tempo e anlise de tendncias, os quais

requerem dados mais histricos do que aqueles geralmente mantidos em

bancos de dados transacionais;

9

So no volteis, comparados com os bancos de dados transacionais isto ,

a informao no data warehouse muda muito menos freqentemente e pode

ser considerado como no sendo de tempo-real com atualizao peridica.

Atualizaes no warehouse so manipuladas pelo componente de aquisio

do warehouse, que fornece todo o pr-processamento requerido.

Algumas caractersticas de data warehouses so:

Viso conceitual multi-dimensional;

Dimensionalidade genrica;

Nveis de dimenses e agregaes ilimitadas;

Operaes cross-dimensional irrestritas;

Manipulao de matriz esparsa dinmica;

Arquitetura cliente-servidor;

Suporte a multi-usurio;

Manipulao de dados intuitiva;

Flexibilidade na elaborao de relatrios.

Geralmente data warehouses envolvem grandes volumes de dados (na ordem de

terabytes), o que leva a diferentes abordagens:

Data warehouses de todo o empreendimento so projetos muitograndes requerendo investimento massivo de tempo e recursos.

Data warehouses virtuais fornecem vises de bancos de dadosoperacionais que so materializadas para acesso eficiente.

Data marts so geralmente idealizados para um subconjunto daorganizao, tal como um departamento, e so mais especficos.

10

2.5. Arquitetura do Data Warehousing

Um data warehousing pode ser descrito como uma coleo de tecnologias

de suporte deciso, objetivando capacitar o trabalhador do conhecimento

(executivo, gerente, analista) a fazer decises melhores e mais rpidas. A Figura

1 extrada de [2], apresenta uma arquitetura tpica de data warehousing,

mostrando todo o processo envolvido. Essa arquitetura inclui ferramentas para:

extrao de dados de mltiplas bases de dados operacionais e de fontesexternas (por exemplo perfil dos clientes fornecida por consultores externos)usando gateways ou interfaces externas padro suportadas pelo SGBD de

suporte; limpeza dos dados para minimizar erros e preencher informaesausentes, quando possvel; transformao para reconciliar erros semnticos;integrao desses dados; carga dos dados no data warehouse, que consiste namaterializao das vises e no seu armazenamento no warehouse; e para

periodicamente executar o refresh do warehouse, para que esse reflita asatualizaes sofridas pela base operacional. Alm do warehouse principal, pode

haver diversos data marts (subconjuntos especficos focados em assuntosselecionados) departamentais. Os dados contidos no warehouse e nos data marts

so armazenados e gerenciados por um ou mais servidores de warehouse, queoferecem vises multidimensionais dos dados para uma variedade de ferramentas

front-end: ferramentas de consulta, geradores de relatrios, ferramentas deanlises especficas e ferramentas de data mining. Finalmente, h umrepositrio para armazenamento e gerenciamento de dados de log e ferramentas

para monitoramento e administrao do sistema total.

11

Figura 1. Arquitetura de Data Warehousing

As informaes sobre os dados do warehouse so armazenadas no

catlogo do sistema e freqentemente armazenado e gerenciado em um banco

de dados separado chamado repositrio de metadados. O tamanho e

complexidade do catlogo em parte devido ao tamanho e complexidade do

warehouse em si e em parte porque uma grande poro de informao

administrativa deve ser mantida. Por exemplo, precisamos ser informados sobre a

fonte de cada tabela do warehouse e quando ela sofreu o ltimo refresh, alm da

descrio de seus campos.

2.6. Data Mart (DM)

um data warehouse de pequena capacidade usado para atender a uma

unidade especfica de negcios. Data Marts no so diferenciados dos DWs com

base no tamanho, mas no uso e gerenciamento. Entretanto, DMs so menores e

menos complexos do que DWs e portanto so tipicamente mais fceis de

construir e manter.

Em mdia, um DM pode ser construdo num perodo de 3 a 6 meses,

enquanto um DW leva de 2 a 3 anos para ser concludo.

Os motivos que levam ao desenvolvimento de um Data Mart podem ser:

Fontes externas

Dados operacionais

Data Marts

Data Warehouse

Servidores OLAP

Relatrios

Data Mining

Repositriode Metadados

Monitoramento e Administrao

Ferramentas

ExtraoTransformaoCargaRefresh

Anlise

12

servir como projeto piloto, atender necessidades imediatas de uma unidade,

atender a restries de custo, tempo, etc., entre outros.

13

3. Modelagem de Dados para DW

3.1. Modelo de dados multi-dimensional

A fim de facilitar anlises e visualizaes complexas, os dados em um

warehouse so tipicamente modelados num formato multi-dimensional. Por

exemplo, num data warehouse de vendas, a poca da venda, o local geogrfico, o

cliente e o produto podem ser algumas das dimenses de interesse.

Freqentemente essas dimenses so hierrquicas: a poca da venda pode ser

organizada numa hierarquia dia ms trimestre ano, e produto pode ser

organizado numa hierarquia produto tipo categoria.

A maioria das pessoas, intuitivamente, pensa no modelo como um cubo de

dados (matrizes multidimensionais), como mostrado na Figura 2, onde cada

aresta representa uma das dimenses com seus diferentes valores distribudos ao

longo da mesma. Os pontos internos ao cubo representam os valores de medida

do negcio no nosso exemplo, os valores de vendas.

Figura 2. Cubo de dados de vendas

A figura 3 mostra uma matriz bi-dimensional, apresentando os produtos

como linhas, com rendimento de venda para cada local compreendendo as

colunas. Essa matriz pode estar representando o rendimento das vendas por

Produto

Tempo

Local

14

local e por produto para um particular perodo de tempo.

LOC1 LOC2 LOC3 . . .P1P2P3P4...

Figura 3. Matriz bi-dimensional

A adio de uma dimenso tempo, produz uma matriz tri-dimensional que

pode ser representada usando o cubo de dados. A figura 4 mostra um cubo que

organiza dados de venda de produto por data e regies de venda. Cada clula

representa a venda de um produto especfico, em um perodo de tempo

especfico (ano, trimestre, ms,...) em um local especfico. Adicionando outras

dimenses, teremos um hipercubo.

Os dados podem ser consultados diretamente em qualquer combinao de

LOCAL

PRODUTO

Local (codLocal)

. . .

.

Reg3Reg2Reg1

P3

Tempo (codTempo)

t4

t3 t2

t1

P1

P2

...

Produto(codProd)

Figura 4. Cubo de dados de vendas

15

dimenses, permitindo que consultas complexas no banco de dados original

sejam realizadas de forma mais direta e com maior desempenho. Existem

ferramentas que permitem a visualizao dos dados de acordo com a escolha de

dimenses do usurio.

Num ambiente relacional os dados da matriz multi-dimensional podem ser

representados como uma relao, como ilustrado na figura 5. Nessa relao as

tuplas esto agrupadas de acordo com as fatias (slices) verticais, paralelas com

relao ao eixo do tempo. Essa relao que associa as dimenses medida de

interesse chamada tabela fato. Note na figura 5 que somente constam databela as composies produto-local-tempo que possuem valor de vendas. Cada

dimenso pode ter um conjunto de atributos associados. Por exemplo, a

dimenso Local identificada pelo atributo codlocal, que foi usado para identificar

um local na tabela Vendas. A dimenso Local pode ter os atributos adicionais

Cidade, Estado e Pas. A dimenso Produto pode conter os atributos codProd,

Descrio, Marca, Categoria, Estilo, Preo. A Categoria de um produto indica sua

natureza geral; por exemplo, um produto camisa pode pertencer categoria

roupa. O Estilo pode ser social, esporte, passeio, etc. A dimenso Tempo pode ter

os atributos Data, Semana, Ms, Trimestre, Ano, alm do identificador

codTempo.

codProd codLocal codTempo vendasP1 loc1 t1 1000P1 loc2 t1 880P1 loc3 t1 1025P2 loc1 t1 775P2 loc2 t1 1002P3 loc1 t1 888P3 loc4 t1 989P4 loc2 t1 1550P4 loc3 t1 900P1 loc1 t2 1030P1 loc2 t2 920P2 loc1 t2 1010P2 loc2 t2 700

fatia (slice) doperodo de tempo t1

fatia (slice) doperodo de tempo t2

16

P2 loc4 t2 1100P3 loc1 t2 980P3 loc4 t2 980P4 loc2 t2 1550P4 loc3 t2 950

...Figura 5. Relao Vendas (tabela fato)

As dimenses podem ser representadas como relaes:

Local (codLocal, Cidade, Estado, Pas)

Produto (codProd, Descrio, Marca, Categoria, Estilo, Preo)

Tempo( codTempo, Data, Semana, Ms, Trimestre, Ano)

Para cada dimenso, o conjunto de valores associados podem ser

estruturados em uma hierarquia. Por exemplo, cidades pertencem a estados e

estados pertencem a pases. Datas pertencem a semanas e a meses; semanas e

meses esto contidos em trimestres e trimestres esto contidos em anos. A figura

6 mostra as hierarquias para Produto, Local e Tempo do exemplo aqui

considerado.

PRODUTO

categoria

estilo

cdigo produto

TEMPO

ano

trimestre

semana ms

data

LOCAL

pas

estado

cidade

Figura 6. Possveis hierarquias para Produto, Tempo e Local

17

3.2. Consultas OLAP

Sobre o cubo de dados (data warehouse) podem ser aplicadas as

operaes tpicas de OLAP, para viabilizar a extrao eficaz de informaes do

mesmo.

As operaes suportadas pelo modelo multi-dimensional so fortemente

influenciadas por ferramentas existentes para usurio final tal como aquelas que

trabalham com planilhas eletrnicas. A meta oferecer ao usurios finais, que

no so especialistas em SQL, uma interface intuitiva e poderosa para tarefas

comuns de anlise de negcio. Cada operao sobre o conjunto de dados multi-

dimensional retorna ou uma apresentao diferente ou uma sumarizao desse

conjunto de dados. O conjunto de dados est sempre disponvel para o usurio

manipular, independente do nvel de detalhe em que ele est sendo visto.

3.2.1. Agregao

Uma operao muito comum agregar uma medida sobre uma ou mais

dimenses. Exemplos de consultas tpicas so:

Encontrar o total de vendas.

Encontrar o total de vendas para cada cidade.

Encontrar o total de vendas para cada estado.

Encontrar os cinco produtos mais vendidos.

As trs primeiras consultas podem ser expressas como consultas SQL sobre

18

as tabelas fato e dimenso, mas a ltima consulta no pode ser expressa em

SQL (embora se possa conseguir um resultado satisfatrio ordenando o total

de vendas atravs de ORDER BY).

Um outro uso de agregao sumarizar em diferentes nveis de hierarquia.

Isso conseguido atravs das operaes OLAP roll-up e drill-down queoferecem visualizaes hierrquicas dos dados.

3.2.2. Roll-up

A operao roll-up realiza um aumento no nvel de agregao dos dados,agrupando em unidades maiores ao longo da dimenso. Por exemplo, a figura 7

mostra os dados de venda usando a operao roll-up que, a partir de informaes

de venda de produtos individuais por local (por cidade), faz um agrupamento de

categorias de produtos, apresentando o total de vendas por categoria de produto

(por exemplo: categoria Roupa abrange os produtos P1 a P10, a categoria

Calado abrange os produtos P11 a P15, etc.).

So Carlos Ribeiro Preto ...Roupa 10.000 15.000 ...Calado 7.000 10.000 ...Bijuteria 30.000 20.000... ... ... ...

3.2.2. Drill-down

A operao drill-down oferece a capacidade oposta, fornecendo umaviso mais detalhada. Por exemplo, dado o total de vendas por estado e por

categoria de produto, podemos solicitar uma apresentao mais detalhada

LOCAL

CATEGORIASDE

PRODUTOS

Figura 7. A operao roll-up (venda por categoria/cidade)

19

desagregando vendas de cada estado por cidade e tambm quebrando categoria

de produtos por estilos, conforme figura 8.

S.Carlos Rib.Preto Lins Assis P. Alegre CaxiasSul

ABC

DAB

DABC...

Estilos Roupa

Estilos Calado

Estilos Bijouteria

So Paulo

...Rio Grande do Sul

20

Figura 8. A operao drill-down

3.2.4. Pivot

A mudana de uma hierarquia dimensional (orientao) para uma outra

facilmente obtida em um cubo de dados utilizando a tcnica chamada pivoting(ou rotao). A operao pivot realiza uma re-orientao do ngulo de viso dosdados. Nessa tcnica, os eixos podem ser mostrados em orientaes diferentes.

Por exemplo, pode-se fazer uma rotao no cubo de dados da figura 4 para

mostrar rendimentos de vendas regionais como linhas, os totais de rendimentos

dirios como colunas e os produtos da companhia na terceira dimenso (figura 9).

Isso equivale a ter uma tabela de venda regional para cada produto

separadamente, onde cada tabela mostra, para o produto especfico, totais de

vendas para cada local e cada perodo considerado.

Reg3

Reg2

Reg1Tempo ...

.

t3t2t1

Produto P4

P3P2

P1

...

Local

...

Figura 9. Rotao (Pivot) do cubo de dados de vendas

21

3.2.5. Comparando com consultas SQL

Algumas consultas OLAP no podem ser (ou no podem ser facilmente)

expressas em SQL. Por exemplo, das quatro consultas a seguir, as duas

primeiras podem ser expressas como consultas SQL sobre as tabelas fato e

dimenso. A terceira consulta pode ser expressa tambm, mas mais

complicada em SQL. A ltima consulta no pode ser expressa em SQL, se n

para ser um parmetro da consulta. Observe que todas as consultas a seguir

envolvem o tempo. Na verdade a dimenso Tempo muito importante em OLAP.

Consultas:

Encontre o total de vendas por ms.

Encontre o total de vendas por ms para cada cidade.

Encontre a variao de porcentagem no total de vendas mensalmente para

cada produto.

Encontre a mdia de movimento de vendas de n dias. (Para cada dia, deve ser

calculada a mdia de vendas diria sobre os n dias precedentes).

Um grande nmero de consultas OLAP, entretanto, pode ser expresso em

SQL. Tipicamente elas envolvem agrupamento e agregao, e uma nica

operao OLAP conduz a vrias consultas relacionadas. Por exemplo, a mesma

informao apresentada na tabela da figura 7 pode ser obtida atravs da seguinte

consulta:

SELECT SUM (V.vendas)

FROM Vendas V, Produto P, Local L

WHERE V.codProd=P.codProd AND V.codLocal=L.codLocal

GROUP BY P.Categoria, L.Cidade

A operao realizada na tabela da figura 7 a Roll-up, que agrupa os

dados em unidades maiores. Cada operao roll-up corresponde a uma consulta

22

SQL com GROUP BY. Em geral, dada uma medida com k dimenses associadas,

podemos fazer um roll up sobre qualquer subconjunto dessas k dimenses e

ento tem-se 2k possveis consultas SQL.

Uma extenso proposta para o SQL chamada CUBE equivalente a uma

coleo de comandos GROUP BY, com um comando GROUP BY para cada

subconjunto das k dimenses. Por exemplo, considere a consulta:

CUBE codProd, codLocal, codTempo BY SUM Vendas

Essa consulta far um roll up sobre a tabela Vendas sobre todos os oito

subconjuntos do conjunto { codProd, codLocal, codTempo}. Ela equivalente a

oito consultas da forma:

SELECT SUM (V.vendas)

FROM Vendas V

GROUP BY lista-do-agrupamento

As consultas diferem somente na lista-do-agrupamento, que algum

subconjunto do conjunto { codProd, codLocal, codTempo}.

23

4. Projeto do Data Warehouse

4.1. Esquema Estrela

Diagramas entidade-relacionamento e tcnicas de normalizao so

popularmente usadas para projetos de bases de dados operacionais. Nos

sistemas de apoio deciso, porm, devem prevalecer mtodos de projeto que

tenham como alvo a eficincia nas consultas [19].

A maioria dos data warehouses utilizam um esquema estrela para representar

o modelo multi-dimensional de dados [12]. A base dos dados consiste de uma tabela

dimenso para cada dimenso, alm de uma tabela fato, a qual contm um

relacionamento com cada uma das tabelas dimenso e um valor para a dimenso do

negcio relativo ao conjunto de dimenses referenciadas. A Figura 10 mostra o

esquema estrela para o exemplo de vendas de produto adotado no captulo 3.

VENDAS(tabela fato)

REGIO

PRODUTOTEMPO

codProdcodTempocodRegiovendasvalor-vendas

codTempoDataSemanaMs TrimestreAno

CodProdutoCategoria DescrCategoriaEstiloPreo

CodRegCidadeEstadoPas

(tabelas dimenso)(tabela dimenso)

Figura 10. Esquema estrela do exemplo dado (fig.4)

24

Uma outra maneira de representar esquema estrela fornecido na figura 11.

O volume maior dos dados est tipicamente na tabela fato, que no tem

redundncia. Usualmente ela est na Forma Normal de Boyce Codd (FNBC).

Para minimizar o tamanho da tabela fato, os identificadores das dimenses (ex.

CodLocal, CodTempo) so gerados pelo sistema.

As tabelas dimenso usualmente so no normalizadas. A razo para isso

que um banco de dados usado para OLAP esttico; assim, anomalias de

atualizao, insero e eliminao no so importantes. Alm disso, o espao de

armazenamento ganho atravs da normalizao das tabelas dimenso

desprezvel, frente ao tempo de processamento gasto para combinar a tabela fato

com as tabelas dimenso, caso estas fossem quebradas em tabelas menores

normalizadas (que podem conduzir a junes adicionais).

PRODUTO

CodRegio Cidade Estado Pas

CodTempo Data Semana Ms Trimestre Ano

TEMPO

REGIO

CodProd CodLocal CodTempo vendas valor-vendas

VENDA

Figura 11. Outra representao de esquema estrela

CodProd Categoria DescrCategoria Estilo Preo

25

4.2. Esquema Snowflack

Esquemas estrela no fornecem explicitamente suporte para hierarquias de

atributos. Um modelo refinado do esquema estrela o chamado esquema

snowflack, no qual as tabelas dimenso so organizadas em uma hierarquia

atravs de sua normalizao, como mostrado na Figura 12. Isso traz vantagens

para a manuteno das tabelas-dimenses. Entretanto, a estrutura no

normalizada das tabelas dimenso nos esquemas estrela podem ser mais

apropriados para a manipulao das dimenses.

As figuras 13 e 14 apresentam um outro exemplo de esquemas estrela e

snowflack.

CATEG-PROD

VENDA(tabela fato)

TEMPO

PRODUTO

REGIOCodProdCodRegioCodTempovendasvalor-vendas

CodRegioCidadeEstado

CodProdutoCategoriaEstiloPreo

CodTempoData SemanaMsAno

(tabelas dimenso)(tabelas dimenso)

EstadoPas

ESTADO

CategoriaDescrioCateg

Ms Trimestre

Figura 12. Esquema Snowflack

26

RENDIMENTOVENDAS(tabela fato)

VENDEDOR

DATACIDADE

PRODUTO

PEDIDO

CLIENTE

NroPedidoCodVendedorCodClienteCodProdutoChaveDataCidadeQuantidadeValorTotal...

NroPedidoDataPedido...

CodClienteNomeClienteEndereoClienteCidadeCliente...

CodVendedorNomeVendedorCidadeVendedorCota...

CodProdutoNomeProdutoDescrProdutoCategoriaDescrCategEstiloProdutoPreoUnitario...

ChaveDataDataMsAno...

CidadeEstadoPas...

Figura 13. Outro exemplo de esquema estrela

MS

ESTADO

VENDAS(tabela-fato)

VENDEDOR

DATACIDADE

PRODUTO

PEDIDO

CLIENTE

NroPrdidoCodVendedorCodClienteCodProdutoChaveDataCidadeQuantidadeValorTotal...

NroPedidoDataPedido...

CodClienteNomeClienteEndereoClienteCidadeCliente...

CodVendedorNomeVendedorCidadeVendedorCota...CodProduto

NomeProdutoDescrProdutoCategoriaPreoUnitario...

ChaveDataDataMs...

CidadeEstado...

CategoriaDescrCateg

EstadoPas

MsAno

Figura 14. Esquema Snowflack

27

4.3. Constelao de Fatos

Constelao de fatos so exemplos de estruturas mais complexas nas

quais mltiplas tabelas fato compartilham tabelas dimenso. A figura 15 mostra

uma constelao de fatos com duas tabelas fato, Resultado de Vendas e Previso

de Vendas, que compartilham a tabela dimenso Produto. Constelaes de fatos

limitam as possveis consultas para o warehouse.

Outro exemplo seria uma constelao de fatos representando despesas projetadas e

despesas reais, contendo duas estrelas que compartilham vrias dimenses.

4.3. Vises Materializadas

A fim de otimizar a anlise multi-dimensional, tornando-a viavelmente

utilizvel pelo usurio, uma das solues adotadas atualmente a pr-

computao de agregaes em alguns subconjuntos de dimenses e suas

hierarquias correspondentes. Outra soluo comumente usada a materializao

de consultas muito freqentes. Essas tabelas especiais so chamadas de vises

materializadas.

As vises materializadas geralmente consistem de junes da tabela fato com

um subconjunto de tabelas dimenso, com a sumarizao de uma ou mais medidas

de valor do negcio, agrupadas por um conjunto de atributos das tabelas dimenso.

RESULTADO.VENDAS(tabela fato)

PRODUTO

CodProdutoCodRegioCodTempoVendas

CodProdutoCategoriaDescrCategoriaEstiloPreo

(tabela dimenso)

PREVISOVENDAS(tabela fato)

CodProdutoCodRegioCodTempoFuturo ProjeoVendas

Figura 15. Uma constelao de fatos

28

A seleo das vises a materializar deve levar em conta a freqncia de

utilizao, caractersticas de carga de trabalho, custo de atualizaes

incrementais e exigncias de armazenamento.

Como um exemplo, suponha que num ambiente de vendas, como o do exemplo

aqui considerado, uma grande maioria das consultas baseada na performance de vendas

no estado de So Paulo, do trimestre mais recente. Ter uma tabela que contenha dados

sumrios sobre esses parmetros pode acelerar significativamente o processamento das

consultas. A figura 16 ilustra esse exemplo.

Vendas - Estado So Paulo

Categoria

Data01/07/99 02/07/99 ... 25/11/99

Roupa 2000 3000 ... 1000Calado 500 650 ... 220Bijuteria 300 550 ... 430

Vendas - Estado So PauloCategoria Trimestre 3

Roupa 21200Calado 5670Bijuteria 6980

Figura 16. Agregao

Consultas ad hoc realizadas pelos usurios so respondidas usando as

tabelas originais juntamente com sumrios pr-computados.

4.4. Construindo um Data Warehouse

H muitos desafios na criao e manuteno de um data warehouse.

necessrio projetar um bom esquema de banco de dados para manter uma

coleo integrada de dados copiados de diversas fontes. Por exemplo, o data

agregao

29

warehouse de uma companhia pode incluir os bancos de dados do departamento

de Estoque e de Pessoal, junto com os bancos de dados de Vendas mantidos por

escritrios em diferentes pases (ou filiais). Uma vez que os dados so criados e

mantidos por diferentes grupos, existem algumas questes que devem ser

avaliadas, tais como erros semnticos, diferenas na forma como as tabelas

foram normalizadas ou estruturadas, nomes diferentes para o mesmo atributo.,

entre outros.

Para construir um data warehouse, necessrio ter uma viso antecipada

do uso do warehouse. No h uma maneira para antecipar todas as possveis

consultas ou anlises durante a fase de projeto. Entretanto, o projeto deve

especificamente suportar consultas ad-hoc, isto , acesso aos dados com

qualquer combinao significativa de valores para os atributos na tabela

dimenso ou tabela fato.

A aquisio de dados para o warehouse envolve os seguintes passos:

extrao de dados: Os dados podem ser extrados de mltiplas fontesheterogneas. Durante essa fase pode ser necessrio selecionar dados sobre

itens especficos ou categorias de itens, ou de armazns em uma regio

especfica do pas;

formatao: Os dados devem ser formatados para ficar consistentes com owarehouse. Nomes, significados e domnios de dados vindos de fontes no

relacionadas devem ser conciliados. Por exemplo: companhias subsidirias de

uma grande corporao podem ter diferentes calendrios fiscais com os

trimestres terminando em diferentes datas, tornando difcil agregar dados

financiais por trimestre; outro exemplo a existncia de diferentes unidades

monetrias. Essas inconsistncias de formato devem ser resolvidas.

Limpeza dos dados: Os dados devem passar por uma limpeza paraminimizar erros, preencher informao ausente, quando possvel, e deixar os

elementos de dados dentro de formatos e significados padronizados e

consistentes. Limpeza dos dados um processo complexo que tem sido

identificado como o componente que demanda maior trabalho na construo

30

do data warehouse. Para dados de entrada, a limpeza deve ocorrer antes que

os dados sejam armazenados no warehouse. A limpeza de dados que requer o

reconhecimento e correo automticos de dados errneos e incompletos

uma tarefa difcil. Alguns aspectos, tais como checagem de domnio, so

facilmente codificados em rotinas de limpeza de dados, mas reconhecimento

automtico de alguns problemas de dados pode ser mais desafiador.

Por exemplo, o processo de limpeza pode corrigir CEPs invlidos ou

eliminar registros com prefixos de telefone incorretos.

A tabela da figura 17 a seguir apresenta alguns exemplos de dados que

necessitam de um tratamento:

nome de cidade errado ("Centro"), e no padronizado (So Paulo e

S.Paulo)

nome de bairro errado ("XXX") e no padronizado ("Centro" -

transformar para letras maisculas).

as datas devem seguir um mesmo formato.

valores nulos devem ter um tratamento adequado (data com valor

"000000")

eliminar dados inteis para o data warehouse: a ltima linha da tabela no

representa informao til.

CIDADE BAIRRO DATA

SO CARLOS CENTRO 10/09/99

CENTRO Centro 08/03/1999

RIO DE JANEIRO XXX 13-05-99

SO PAULO PINHEIROS 15101998

31

S. PAULO SANTANA 000000

RIBEIRO PRETO CENTRO 10/06/1998

XXXX 000000

Figura 17 - Erros durante a fase de limpeza

Os dados devem ser ajustados no modelo de dados do data warehouse. Os

dados vindos de vrias fontes devem ser instalados no modelo de dados do

warehouse. Os dados podem ter que ser convertidos de bancos de dados

relacionais, orientados a objetos ou legados (redes ou hierrquicos) para um

modelo multi-dimensional.

Os dados devem ser carregados no warehouse. Essa tarefa significativa

devido ao grande volume de dados do warehouse. So requeridas

ferramentas de monitorao para a carga, bem como mtodos para

recuperao a partir de cargas incompletas ou incorretas. Questes sobre a

atualizao dos dados tambm so levadas em conta. As seguintes questes

devem ser consideradas:

At que ponto os dados podem ser atualizados?

O warehouse pode se tornar off-line e por quanto tempo?

Quais so as interdependncias de dados?

Qual a disponibilidade de armazenamento?

Quais so os requisitos de distribuio (tais como para replicao

e particionamento)?

Qual o tempo de carga (incluindo limpeza, formatao, cpia,

transmisso, reconstruo de ndice, ...)?

32

Uma tarefa de projeto muito importante avaliar quais tabelas sumrio devem

ser materializadas para alcanar o melhor uso de memria disponvel e

responder consultas ad hoc comumente realizadas com tempo de resposta

interativo.

Quando o warehouse usa um mecanismo de reabastecimento (refreshing) de

dados incremental, pode ser necessrio purgar periodicamente os dados; por

exemplo, um warehouse que mantm dados sobre os ltimos 3 anos pode

purgar seus dados a cada ano.

4.5. Resumindo as Funcionalidades Tpicas de Data Warehouses

O objetivo dos data warehouses facilitar consultas complexas e que

envolvem muitos dados. Eles devem fornecer um suporte de consulta maior e

mais eficiente do que os oferecidos nos bancos de dados transacionais. Os

suportes do componente de acesso do data warehouse incluem:

- funcionalidades melhoradas de ferramentas que trabalham com

planilhas eletrnicas: isto , suportes conhecidos oferecidos para

planilhas eletrnicas bem como suporte de programas de

aplicao OLAP.

- Processamento de consulta eficiente

- consultas estruturadas

- consultas ad hoc

- data mining

- vises materializadas

As funcionalidades pr-programadas que so oferecidas pelos data

warehouses so:

- Roll-up: os dados so sumarizados com crescente generalizao (por

ex., de semanalmente para trimestralmente para anualmente).

- Drill-down: crescentes nveis de detalhe so revelados (operao

33

oposta de roll-up).

- Pivot (rotao): realizada tabulao cruzada.

- Slice and dice: so realizadas operaes de projeo sobre as

dimenses.

- Ordenao: os dados so ordenados atravs de um atributo.

- Seleo: os dados so disponveis por valor ou agrupados em

categorias de valores.

- atributos derivados (computados): valores derivados atravs de

operaes sobre dados armazenados.

4.6. Consideraes Finais

4.6.1. Data warehousing e Vises

Data warehouses tm sido considerados por algumas pessoas como sendo

uma extenso de funes e vises do banco de dados. Entretanto vises

fornecem somente um subconjunto das capacidades de data warehouses. Vises

e data warehouses so parecidos nos seguintes aspectos:

- ambos tm dados extrados de bancos de dados;- so orientados ao assunto .

Data warehouses so diferentes de vises nos seguintes aspectos:

- Data warehouses existem como armazenamento persistente ao invs

de ser materializado sob demanda;

- Data warehouses no so usualmente relacionais, mas sim

multidimensionais. Vises de um banco de dados relacional so

relacionais.

- Data warehouses podem ser indexados para otimizar performance.

Vises no podem ser indexadas independente dos bancos de dados

utilizados.

34

- Data warehouses caracteristicamente fornecem suporte especfico de

funcionalidade; vises no podem fornecer.

- Data warehouses fornecem grande quantidade de dados integrados e

freqentemente temporais, geralmente mais do que est contido em um

banco de dados, enquanto que vises so um extrato de um banco de

dados.

4.6.2. Dificuldades na Implementao de Data Warehouses

A construo de um warehouse de um amplo empreendimento em uma

grande organizao pode levar anos, considerando desde a fase inicial de

concepo at a implementao. Devido dificuldade e quantidade de tempo

requerido para uma tal tarefa, o desenvolvimento e emprego difundido de data

marts pode fornecer uma alternativa atrativa, especialmente para aquelas

organizaes com necessidades urgentes de OLAP, sistemas de suporte

deciso e/ou suporte de data mining.

Alguns aspectos a serem considerados so:

A administrao de um data warehouse uma tarefa intensiva, proporcional

ao tamanho e complexidade do warehouse.

O controle de qualidade dos dados e a consistncia so aspectos importantes

a considerar. Cada vez que um banco de dados fonte muda, o administrador

do data warehouse deve considerar as possveis interaes com outros

elementos do warehouse.

Projees de uso devem ser estimadas antes da construo do data

warehouse e devem ser revisadas continuamente para atender requisitos

correntes.

Administrao de data warehouse ir requerer habilidades mais amplas do

que so necessrias para administrao de banco de dados tradicional.

35

5. Servidores OLAP

Servidores que utilizam bancos de dados relacionais tradicionais no foram

concebidos para propiciar o uso inteligente de ndices e de outros recursos

necessrios para suportar vises multidimensionais de dados. Em adio aos

servidores relacionais tradicionais, h trs opes principais para a

implementao do servidor OLAP: servidores ROLAP (Relational OLAP), que

armazenam os dados em tabelas, servidores MOLAP (Multi-dimensional OLAP),

que armazenam os dados em arrays, e servidores HOLAP (Hibrid OLAP), que so

hbridos dos dois anteriores.

5.1. Servidores ROLAP

Nos servidores OLAP Relacionais (ROLAP) os dados so armazenados em

tabelas de bancos de dados relacionais ou relacionais estendidos. Eles utilizam

SGBDs relacionais para gerenciar os dados e agregaes do esquema estrela do

warehouse. Tambm suportam extenses a SQL e acesso e mtodos de

implementao especiais, tais como o OLAP midleware para a implementao

eficiente do modelo multi-dimensional e suas operaes. Dessa forma, sua

estrutura de dados implementada por tabelas relacionais, e uma clula do

espao multi-dimensional representada por uma tupla. Essa tupla carrega

alguns atributos que identificam a posio da clula no espao multi-dimensional,

alm de outros atributos que contm os valores de dados correspondentes quela

clula.

Por utilizarem bancos de dados relacionais para implementar o modelo

multi-dimensional, os servidores OLAP precisam reescrever as consultas dos

usurios para compatibiliz-las com as vises materializadas e gerar mltiplas

consultas SQL para o servidor. A principal vantagem dos servidores ROLAP est

no armazenamento de grandes conjuntos de dados, devido ao fato de se poder

36

armazenar dados esparsos de forma mais compacta em tabelas do que em

arrays.

5.2. Servidores MOLAP

Outra opo amplamente adotada so os servidores OLAP

Multidimensionais (MOLAP). Servidores MOLAP implementam as vises

multidimensionais diretamente, armazenando dados em algumas estruturas

especiais (por exemplo, arrays esparsos) e executando as operaes OLAP

diretamente sobre essas estruturas. O cubo de dados implementado atravs do

mapeamento de suas dimenses para os ndices do array, de forma que o

contedo do array formado pelos valores contidos em cada clula do cubo.

Servidores MOLAP possuem excelentes propriedades de indexao devido

ao fato de ser mais simples localizar uma clula num array do que numa tabela.

Por outro lado, quando os dados so esparsos, os servidores MOLAP perdem

performance, sendo que nesses casos algumas tcnicas de matrizes de

compresso devem ser exploradas. Para pequenos e mdios conjuntos de dados,

eles so mais eficientes em armazenamento e recuperao dos dados.

5.3. Servidores HOLAP

Alguns servidores OLAP adotam uma forma de armazenamento em dois

nveis, a fim de manipular conjuntos de dados densos e esparsos. O conjunto de

dados de alguns subcubos dimensionais que so identificados como densos so

armazenados no formato de arrays. O restante dos subcubos, que so esparsos,

so armazenados em tabelas empregando tecnologias de compresso. Isso

resulta em um mtodo de armazenamento hbrido chamado de OLAP Hbrido

(HOLAP).

37

5.4. On-Line Analytical Mining (OLAM)

Considerando o alto poder do mtodo OLAP para se realizar anlises

multidimensionais e vislumbrando as vantagens geradas pela possibilidade de

utilizar essa anlise na preparao dos dados na aplicao de Data Mining, J.

Han e sua equipe desenvolveram um mecanismo batizado de OLAM (On-Line

Analytical Mining), que propicia a aplicao de KDD num ambiente multi-

dimensional em grandes bases de dados e data warehouses .

As principais vantagens geradas por essa abordagem so:

A maioria das ferramentas de Data Mining necessitam trabalhar com dados

integrados, consistentes e limpos, que exigem um rduo trabalho de limpeza,

transformao e integrao dos dados nas fases que precedem a aplicao de

DM no processo KDD. Num data warehouse, a maior parte dessa preparao

normalmente j foi realizada, servindo esse warehouse para a aplicao tanto

de OLAP quanto de DM.

DM efetivo necessita de anlises exploratrias de dados. O usurio

frequentemente deseja navegar de forma flexvel ao longo da base de dados,

selecionar diferentes partes de dados relevantes e analisar os dados em

diferentes granularidades.

A integrao de OLAP com mltiplas funes de DM permite ao usurio

investigar diferentes tarefas de data mining de forma fcil e flexvel.

38

39

Apndice A

ESTUDO DE CASO 1 Criao de um Data Mart

Este estudo de caso descreve a criao de um data mart realizada em uma

empresa que comercializa pedras e concreto, situada no estado de Maryland,

EUA [8].

O objetivo foi auxiliar na anlise de vendas e marketing para os seus

principais clientes, que so empresas de construo e pavimentao, alm de,

eventualmente, avaliar a eficincia da rede de distribuio.

Os dados de entrada originam-se de sistemas IBM ES/9000 e AS/400.

A ferramenta utilizada para a execuo do projeto foi o Oracle Data Mart

Suite para Windows NT.

Objetivou-se fornecer aos usurios do sistema acesso ad hoc grfico s

informaes gerenciais, sem envolver grande volume de processamento a cada

acesso realizado.

A principal rea de interesse identificada foi a anlise dos lucros.

O primeiro passo foi a identificao acurada de todas as informaes que o

usurio realmente necessitava em suas consultas, definindo-se, assim, o grau de

detalhamento que o sistema deveria contemplar. O estudo dos relatrios j

existentes foi um ponto de partida.

O passo seguinte foi a compreenso do mecanismo de clculo dos lucros

da empresa, j que o antigo sistema de contas no fornecia explicitamente esses

valores, mas sim um grande nmero de campos que poderiam levar a esses

valores. O projeto das tabelas foi tal que o lucro pudesse ser pr-calculado e

armazenado na tabela fato, evitando, assim, clculos no momento da consulta.

40

Outra caracterstica a ser definida foi o perodo de tempo em que o usurio

gostaria de ter suas informaes armazenadas, j que a dimenso tempo

crucial para o esquema estrela.

Iniciou-se, ento, o projeto do banco de dados, descrito a seguir.

O esquema estrela construdo objetivando-se simplicidade e velocidade

de recuperao. Cada tabela dimenso possui uma chave primria, usualmente o

ID. A tabela fato contm uma chave composta por todas as chaves das

dimenses.

Ao invs de utilizar diretamente os identificadores das tabelas dimenso,

foram criadas chaves sintticas tanto para as chaves primrias das tabelas

dimenso, quanto para as chaves estrangeiras da tabela fato. Esse procedimento

torna mais eficientes as consultas, alm de manter uma homogeneidade na base

de dados, garantindo tambm a unicidade dos identificadores e uma maior

facilidade na manuteno do data mart.

A tabela fato da empresa foi construda a partir de tickets e faturas de

produtos (pedras) que foram comprados e transportados para um certo local.

O grau de granularidade (nvel de detalhe informacional) que o cliente

deseja obter nos relatrios tem um impacto sobre o projeto da tabela fato.

Importante: obter dos clientes o que eles necessitam ver e no o que elesquerem ver.

As tabelas dimenso criadas so mostradas a seguir.

Tabela dimenso Fbrica:

Tabela esttica com detalhes sobre todas as fbricas da empresa.

FBRICA

CdigoDescrioId-linha-produto

DIM-FBRICA

Chave-FbricaCod-FbricaDescr-FbricaRegio-Fbrica 41

Tabela dimenso Tempo:A tabela Tempo foi necessria, pois os relatrios necessitam avaliar os dados pordia, feriados, perodos fiscais, etc. Se os agrupamentos fossem somentereferentes a anos/meses a tabela Tempo poderia ser dispensada.

Tabela dimenso Item:

DIM-TEMPO

Chave-tempoId-dataDia-do-msNumero-msDescr-msSemana-do-anoDescr-diaNum-anoDia-do-anoNmero-trimestreData-venda

DIM-ITEM

Chave-itemTipo-itemCd-linha-item Descr-linha-itemClasse-itemSub-classe-itemFlag-itemDescrio-item

ITEM

CdigoTipoDescrioId-linha-itemClasse

LINHA-ITEMCdigoDescrio

42

Tabela dimenso Cliente:

Tabela dimenso Representante de Vendas :

Tabela dimenso Transportadora:

CLIENTE

CdigoNomeEndereo1Endereo 2CidadeEstadoCEPRepresent-vendaTipo

DIM-CLIENTE

Chave-clienteCod-clienteNome-clienteCod-Repres-vendaNome-repres-vendaTipo-cliente

REPRES-VENDA

CdigoNome

DIM-REPRES-VENDA

Chave-repr-vendaCodigo-repr-vendanome-repr-venda

TRANSPORTADORA

CdigoNome

DIM-TRANSPORTADORA

Chave-TransportadoraCdigoNome

43

Tabela fato Ticket -Fatura:

Tabela dimenso Regio de Entrega:

TICKET-FATURA

Nmero-faturaNmero-ticketTipo-ticketCod-FbricaCod-clienteCdigoData-envioCod-produtoQtde-enviadaPreo-produtoCusto-fixado-produto-por-unidadeCusto-variavel-produto-por-unidadeCd-transportadoraCusto-transporte-por-unidadeRegio-entregaLocal-entrega

DIM-REGIO-ENTREGA

Chave-entregaEstado-entregaPas-entregaLocal-entregaRegio-entrega

44

O negcio da empresa era rastrear e avaliar as faturas e tickets para cada

carga transportada por seus caminhes. Havia vrios tickets por fatura. Cada

ticket tinha medidas e preos tais como datas da transportadora, quantidades de

itens, custos totais, etc. Devido ao relacionamento entre tickets e faturas, decidiu-

se fundir as duas tabelas em uma tabela fato principal. Para cada fatura pode

haver vrios tickets.

Para a tabela fato, a soluo adotada foi a criao de uma nica tabela

contendo as informaes sobre os tickets e os pedidos que os contm.

Tabelas sumrio:

A fim de reduzir o tempo de processamento de recuperao de dados que

so freqentemente executadas, foram criadas tabelas sumrio para a dimenso

tempo, devido sua alta freqncia de consultas. As tabelas criadas produzem

freqentemente relatrios por ms, trimestre e ano.

45

Apndice B

ESTUDO DE CASO 2 Cubo Multidimensional e OLAP

Este estudo de caso descreve a implementao do cubo multidimensionalpara consultas OLAP realizada em uma usina de acar e lcool, situada noestado de So Paulo.

O objetivo foi auxiliar nas anlises setoriais, gerar regras de negcio,analisar riscos e produtividade e permitir a comparao e a combinao deinformaes. Algumas perguntas das quais desejava-se conhecer as respostas:

- com quais clientes praticou-se o melhor preo?- quais clientes geram maior lucratividade?- quais os mercados mais lucrativos?- qual o perfil dos clientes?- em que regies concentram-se as vendas?

Inicialmente, foram comparadas as duas alternativas: OLTP e OLAP, a fimde verificar as vantagens da soluo OLAP, justificando, assim a implementaodo projeto. Algumas das caractersticas levantadas foram:

OLTP OLAP- utilizao da base de dados operacionais;- demora na execuo de consultas e relatrios;- pouca flexibilidade;- muitos relatrios utilizados apenas uma vez;- necessidade de pessoal de informtica para desenvolvimento de templates de relatrios;- grande volume de informaes a serem processadas.

- utilizao de data marts- consultas e relatrios obtidos instantaneamente- viso multidimensional das informaes;- transparncia da origem dos dados (Ingres, texto, excel, web);- arquitetura cliente/servidor, permitindo utilizao remota;- gerao de regras de negcio.

Para o desenvolvimento do projeto, foi contratada uma empresa deconsultoria com experincia na rea.

A ferramenta utilizada para a extrao/visualizao dos dados foi a O3.

A criao do cubo foi realizada utilizando-se o aplicativo O3 Designer,seguindo-se os seguintes passos:

46

Definio das dimenses e medidas do cubo e das origens de dados:

Definio das queries:

47

Codificao das queries:

Definio dos campos das queries:

48

Definio das hierarquias dimensionais:

Definio das medidas:

49

A seguir so ilustrados alguns exemplos de consultas com visualizaogrfica.

Volume de vendas anual do produto acar para os diferentes ramos deatividade:

Drill-down no ramo Doces, enfocando o ano de 98 (vendas mensais):

50

51

Volume de vendas anual do produto acar para os diferentes destinos:

Enfoque no ano de 98 (vendas mensais):

52

Receita lquida obtida no ano de 98, com o produto acar, para o clienteVonpar:

53

Bibliografia

[1] Inmon, W.H. Building the Data Warehouse. John Wiley, 1992.

[2] Chaudhuri, S., Dayal, U. An Overview of Data Warehousing and OLAPTechnology, ACM SIGMOD Record, vol.26, 65-74, 1997.

[3] Elmasri, R., Navathe, S. Fundamentals of Databases, 3 edio, 2000.

[4] Ramakrishnan, R. Database Management Systems. McGraw-Hill, 1998.

[5] Felipe, J.C. O Processo de Extrao de Conhecimento de Bases deDados Aplicado a Bancos de Dados Multimdia Orientados a Objetos.Monografia de Exame de Qualificao de Mestrado. Programa de Ps-

Graduao em Cincia da Computao, Departamento de Computao -

UFSCar, So Carlos, Fevereiro 1999.

[6] Becker, K., Pereira, W. Tutorial de Data Warehouse, XIV SimpsioBrasileiro de Banco de Dados, outubro, 1999, Florianpolis, SC, Brasil.

[7] Oracle, Oracle Data Mart Suite.

http://www.twinsoft.de/english/produkte/dmsuite_E.htm

[8] Oracle, Data Marte Suite Design - A Case Study.

http://www.avanco.com/dmdesignstudy.htm.

[9] IBM, Data Warehousing Concepts.

http://as400.rochester.ibm.com/db2/dataware.htm , 1998.

[10] Tam, Y. J., Datacube: Its Implementation and Application in OLAPMining, Thesis submitted for the degree of Master of Science in theDepartment of Computer Science of Simon Fraser University, Canada,

september 1998.

[11] Harinarayan, V., Rajaraman, A., Ullman, J. D., Implementing Data CubesEfficiently, Proc. ACM SIGMOD Int. Conference on Management of Data,June 1996.

54

[12] Roussopoulos, N., Materialized Views and Data Warehouses, ACMSIGMOD Record, Vol. 27, No. 1, Maro 1998.

55

1. Introduo2. Conceitos Bsicos2.1. Data Warehouse2.2. Data Warehousing2.3. Aplicaes sobre o DW para tomada de deciso2.4. Caractersticas de Data Warehouses2.5. Arquitetura do Data Warehousing2.6. Data Mart (DM)

3. Modelagem de Dados para DW3.1. Modelo de dados multi-dimensional3.2. Consultas OLAP3.2.1. Agregao3.2.2. Roll-up3.2.2. Drill-down3.2.4. Pivot3.2.5. Comparando com consultas SQL

4. Projeto do Data Warehouse4.1. Esquema Estrela4.2. Esquema Snowflack4.3. Constelao de Fatos4.3. Vises Materializadas4.4. Construindo um Data Warehouse4.5. Resumindo as Funcionalidades Tpicas de Data Warehouses4.6. Consideraes Finais4.6.1. Data warehousing e Vises4.6.2. Dificuldades na Implementao de Data Warehouses

5. Servidores OLAP5.1. Servidores ROLAP5.2. Servidores MOLAP5.3. Servidores HOLAP5.4. On-Line Analytical Mining (OLAM)

Apndice AESTUDO DE CASO 1 Criao de um Data Mart

Apndice BESTUDO DE CASO 2 Cubo Multidimensional e OLAP

Bibliografia