1 - DC - UFSCar

Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em
Computação
DATA WAREHOUSE
Profa Marilde Terezinha Prado Santos
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
Apostila originalmente organizada por:
Profa. Marina Teresa Pires Vieira
Prof. Joaquim Cezar Felipe
1
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
1. INTRODUÇÃO ........................................................................................................................................... 3
2. CONCEITOS BÁSICOS ............................................................................................................................. 6
2.1. Data Warehouse................................................................................................................................ 6
2.2. Data Warehousing ............................................................................................................................ 6
2.3. Aplicações sobre o DW para tomada de decisão .............................................................................. 8
2.4. Características de Data Warehouses ................................................................................................ 9
2.5. Arquitetura do Data Warehousing .................................................................................................. 11
2.6. Data Mart (DM) .............................................................................................................................. 12
3. MODELAGEM DE DADOS PARA DW ................................................................................................ 14
3.1. Modelo de dados multi-dimensional ............................................................................................... 14
3.2. Consultas OLAP.............................................................................................................................. 17
3.2.1. Agregação .................................................................................................................................... 18
3.2.2. Roll-up ......................................................................................................................................... 19
3.2.2. Drill-down .................................................................................................................................... 19
3.2.4. Pivot ............................................................................................................................................. 20
3.2.5. Comparando com consultas SQL ................................................................................................. 21
4. PROJETO DO DATA WAREHOUSE .................................................................................................... 24
4.1. Esquema Estrela ............................................................................................................................. 24
4.2. Esquema Snowflack ........................................................................................................................ 26
4.3. Constelação de Fatos ...................................................................................................................... 28
4.3. Visões Materializadas ..................................................................................................................... 28
4.4. Construindo um Data Warehouse ................................................................................................... 30
4.5. Resumindo as Funcionalidades Típicas de Data Warehouses ........................................................ 33
4.6. Considerações Finais...................................................................................................................... 34
4.6.1. Data warehousing e Visões .......................................................................................................... 34
4.6.2. Dificuldades na Implementação de Data Warehouses................................................................. 35
5. SERVIDORES OLAP ............................................................................................................................... 37
5.1. Servidores ROLAP .......................................................................................................................... 37
5.2. Servidores MOLAP ......................................................................................................................... 38
5.3. Servidores HOLAP.......................................................................................................................... 38
5.4. On-Line Analytical Mining (OLAM) ............................................................................................... 39
APÊNDICE A ................................................................................................................................................ 41
ESTUDO DE CASO 1 – Criação de um Data Mart ............................................................................. 41
APÊNDICE B ................................................................................................................................................ 47
ESTUDO DE CASO 2 – Cubo Multidimensional e OLAP ..................................................................... 47
BIBLIOGRAFIA ........................................................................................................................................... 55
2
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
1. Introdução
Os recursos tecnológicos de hardware e software, oferecidos nos últimos
anos, para o armazenamento, manutenção e compartilhamento de dados têm
permitido aos usuários de banco de dados (empresas, pesquisadores e órgãos
governamentais) manter quantidades de informações cada vez maiores em suas
bases de dados. Esse grande volume de dados excede a capacidade humana de
análise e compreensão, mesmo utilizando métodos como planilhas eletrônicas 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 à decisão, atraindo atenção cada vez maior da
indústria de bancos de dados. Data Warehousing é uma coleção de tecnologias
de apoio à decisão, voltadas a capacitar o usuário a realizar tomadas de decisão
mais rápidas e precisas. O “produto” da aplicação dessas tecnologias é o Data
warehouse, uma base de dados temporais, não voláteis e integrados, tipicamente
mantida separadamente da base de dados operacionais da empresa. Para
facilitar análises e visualizações complexas, os dados em um warehouse são
modelados de forma multi-dimensional, onde cada elemento de importância
relevante corresponde a uma dimensão (por exemplo, produto, tempo e região
num warehouse de vendas). Essas dimensões podem ser organizadas em
hierarquias (por exemplo, produto - tipo - categoria) a fim de permitir diferentes
níveis de agregação durante a análise.
OLAP (on-line analytical processing)
é um conjunto de operações que
podem ser executadas sobre o Data Warehouse, a fim de viabilizar a extração
eficaz de informações do mesmo. Os requisitos funcionais e de desempenho
dessas operações são totalmente diferentes do processamento de transações online (OLTP – on-line transaction processing)
das aplicações tradicionalmente
suportadas pelas bases de dados operacionais.
3
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
Aplicações OLTP tipicamente automatizam tarefas de processamento de
dados tais como transações bancárias e de processamento de pedido que são
operações rotineiras de uma organização. Essas tarefas
são estruturadas e
repetitivas e consistem de transações curtas, atômicas e isoladas. As transações
requerem dados detalhados, atualizados e realizam operações de leitura e escrita
envolvendo alguns (poucos) registros tipicamente acessados através de suas
chaves primárias. O tamanho das bases de dados operacionais geralmente varia
de centenas de megabytes a gigabytes. Aspectos críticos a considerar são a
consistência e recuperação da base de dados e a métrica de desempenho chave
é maximizar o throughput das transações. Consequentemente, o banco de dados
é projetado para refletir a semântica operacional de aplicações conhecidas e, em
particular, para minimizar conflitos de concorrência.
Data warehouses, em contraste, são totalmente distintos de bancos de
dados tradicionais em sua estrutura (são estruturados para suporte à decisão),
funcionamento, desempenho e propósito. Dados históricos, sumarizados e
consolidados são mais importantes do que registros individuais detalhados.
Além dos recursos para tomada de decisão oferecidos pela tecnologia
OLAP, existem também técnicas e ferramentas "inteligentes", com o objetivo de
fazer emergir, automaticamente, padrões e regras de relacionamento entre os
dados, na busca do conhecimento intrínseco aos mesmos. Essas técnicas e
ferramentas são objeto de estudo de uma linha de pesquisa que aborda o
chamado processo de extração de conhecimento de Bases de Dados (Knowledge
Discovery in Databases - KDD). Uma etapa desse processo de KDD é a de
aplicação de métodos de Data Mining, que consiste na aplicação de algoritmos
específicos sobre uma base de dados, com o objetivo de produzir uma série
particular de padrões e regras que relacionem os dados da base de dados.
Resumindo, para a obtenção de informações contidas em um warehouse
tem-se um conjunto de ferramentas de análise e exploração dos dados, utilizando
consultas em SQL , consultas OLAP e técnicas de data mining. Consultas SQL
são construídas baseadas na álgebra relacional, com algumas extensões; OLAP
fornece idiomas de consulta de nível mais alto com base no modelo de dados
4
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
multi-dimensional e data mining fornece operações de análise mais abstratas.
Nos próximos capítulos são tratados os principais conceitos, questões e
tecnologia envolvidos com Data Warehouse, procurando focalizar os vários
aspectos envolvidos nessa abordagem.
5
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
2. Conceitos Básicos
2.1. Data Warehouse
Data warehouses têm sido desenvolvidos nas organizações para resolver
necessidades particulares, com enfoques variados, não possuindo, portanto, uma
definição única para o termo. Data warehouses são projetados para suportar
extração, processamento e apresentação eficientes de informação para
propósitos analíticos e tomada de decisão.
O significado de data warehouse tem sido difundido em uma variedade de
maneiras na literatura. Inmon [1] caracterizou data warehouse (DW) como uma
coleção de dados
orientada a assuntos, integrada, não volátil e variável no
tempo, que é usada para apoio a decisões gerenciais. Em comparação a bancos
de dados tradicionais, data warehouses geralmente contém 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 aplicação de
tecnologia de informação para resolver problemas empresariais. Compõe-se de
um conjunto de
tecnologias de software e hardware voltadas a viabilizar e
otimizar a análise de dados em larga escala, gerando informações gerenciais
valiosas. Algumas das principais tecnologias utilizadas são:
 Gerenciadores
de
bases
de
dados
distribuídas,
que
suportam
processamento paralelo.
 Produtos de conversão de dados operacionais.
 Tecnologia cliente/servidor que permite acesso a dados distribuídos em
6
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
múltiplas plataformas.
 Integração de ferramentas de análise e relatório em ambiente de
escritório (desktop).
Algumas aplicações usuais de Data Warehousing:
 Análises de vendas e marketing.
 Movimentação de inventário e acompanhamento de produção em
manufaturas.
 Otimização de rotas e análise de riscos em transportes.
 Análise de lucros e riscos em empreendimentos bancários.
 Análise de reclamações ou detecção de fraudes em seguradoras.
Dados operacionais: são os dados originais utilizados nas transações normais
dos sistemas, ou seja, aqueles dados tipicamente armazenados, recuperados e
atualizados pelo sistema. São majoritariamente armazenados em bases de dados
relacionais, podendo, porém, ser armazenados em bases hierárquicas ou até
mesmo em arquivos simples. Algumas das características dos dados operacionais
incluem:
 Freqüentemente atualizados através de transações online.
 Dados não históricos (geralmente não mais que três a seis meses
anteriores).
 Otimizados para processamento transacional.
 Tipicamente
normalizados
em
bases
relacionais,
para
otimizar
atualizações, manutenção e integridade.
Dados informativos: são dados tipicamente armazenados com o objetivo de
tornar a análise mais eficaz. Essa análise pode ocorrer na forma de ambientes de
apoio à decisão, sistemas de informações executivas ou análises estatísticas
sofisticadas. Os dados informativos são criados a partir de seleções e
7
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
sumarizações criteriosas executadas sobre os dados operacionais.
O Data Warehouse é uma base de dados composta por dados informativos.
A criação do Data Warehouse a partir dos sistemas de dados operacionais é a
principal etapa do processo de data warehousing como um todo. A construção da
base de dados informativos é feita utilizando-se ferramentas de transformação e
propagação. Essas ferramentas não apenas movem os dados de múltiplas fontes,
mas freqüentemente manipulam os dados para um formato mais apropriado para
o warehouse. Essas alterações podem ser:
 Criação de novos campos com resultados de cálculos.
 Criação de novos campos com sumarizações de dados.
 Desnormalização de dados com propósitos de performance.
 Compatibilização de campos oriundos de diferentes bases.
2.3. Aplicações sobre o DW para tomada de decisão
Uma tendência com relação à abordagem de data warehousing é o
desenvolvimento de poderosas ferramentas de análise. Há três amplas classes de
ferramentas de análise emergentes [4]:
a) SGBDs projetados para suportar consultas complexas eficientemente.
Tais sistemas podem ser considerados como SGBDs relacionais otimizados para
aplicações de suporte à decisão.
b) sistemas que suportam uma classe de consultas que envolvem
tipicamente o operadores group-by e de agregação. Aplicações dominadas por
tais consultas são as chamadas OLAP (On-Line Analytical Processing). Esses
sistemas suportam um estilo de consulta em que os dados são melhor pensados
como um array multi-dimensional. Aplicações OLAP permitem a analistas,
gerentes e executivos realizar insights pelos dados – normalmente armazenados
em data warehouses – através de acessos rápidos, consistentes e interativos,
com uma ampla variedade de possíveis visões sobre as informações, para refletir
a real dimensão do negócio. OLAP é implementado num ambiente cliente/servidor
8
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
multiusuário e deve oferecer respostas rápidas a consultas, independentemente
do tamanho ou da complexidade da base de dados. OLAP ajuda o usuário a
sintetizar informações através de visualizações comparativas personalizadas,
assim como através de análises de dados históricos em diferentes modelos de
cenários.
c) ferramentas para análise exploratória de dados ou data mining, em que
o usuário procura por padrões de interesse. Por exemplo, um comerciante de
venda por catálogo pode querer analisar os registros dos clientes para identificar
clientes em potencial para uma nova promoção; essa identificação pode depender
do nível de salário, padrões de compra, áreas de interesse demonstradas, etc.
Em situações como essa, é muito difícil formular uma consulta que captura a
essência de um padrão de interesse e a quantidade de dados é muito grande
para permitir análise manual ou mesmo análise estatística tradicional.
2.4. Características 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 multidimensional se adequa bem para OLAP e tecnologia de suporte à decisão;

É um armazém de dados integrados vindos de múltiplas fontes, processados
para armazenamento em um modelo multi-dimensional;

Tipicamente suportam séries de tempo e análise de tendências, os quais
requerem dados mais históricos do que aqueles geralmente mantidos em
bancos de dados transacionais;

São não voláteis, comparados com os bancos de dados transacionais – isto
9
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
é, a informação no data warehouse muda muito menos freqüentemente e
pode ser considerado como não sendo de tempo-real com atualização
periódica.

Atualizações no warehouse são manipuladas pelo componente de aquisição
do warehouse, que fornece todo o pré-processamento requerido.
Algumas características de data warehouses são:

Visão conceitual multi-dimensional;

Dimensionalidade genérica;

Níveis de dimensões e agregações ilimitadas;

Operações cross-dimensional irrestritas;

Manipulação de matriz esparsa dinâmica;

Arquitetura cliente-servidor;

Suporte a multi-usuário;

Manipulação de dados intuitiva;

Flexibilidade na elaboração de relatórios.
Geralmente data warehouses envolvem grandes volumes de dados (na ordem de
terabytes), o que leva a diferentes abordagens:

Data warehouses de todo o empreendimento são projetos muito
grandes requerendo investimento massivo de tempo e recursos.

Data warehouses virtuais fornecem visões de bancos de dados
operacionais que são materializadas para acesso eficiente.

Data marts são geralmente
idealizados para um subconjunto da
organização, tal como um departamento, e são mais específicos.
10
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
2.5. Arquitetura do Data Warehousing
Um data warehousing pode ser descrito como uma coleção de tecnologias
de suporte à decisão, objetivando capacitar o trabalhador do conhecimento
(executivo, gerente, analista) a fazer decisões melhores e mais rápidas. A Figura
1 extraída de [2], apresenta uma arquitetura típica de data
warehousing,
mostrando todo o processo envolvido. Essa arquitetura inclui ferramentas para:
extração de dados de múltiplas bases de dados operacionais e de fontes
externas (por exemplo perfil dos clientes fornecida por consultores externos)
usando gateways ou interfaces externas padrão suportadas pelo SGBD de
suporte; limpeza dos dados para minimizar erros e preencher informações
ausentes, quando possível; transformação para reconciliar erros semânticos;
integração desses dados; carga dos dados no data warehouse, que consiste na
materialização das visões e no seu armazenamento no warehouse; e para
periodicamente executar o refresh do warehouse, para que esse reflita as
atualizações sofridas pela base operacional. Além do warehouse principal, pode
haver diversos data marts (subconjuntos específicos focados em assuntos
selecionados) departamentais. Os dados contidos no warehouse e nos data marts
são armazenados e gerenciados por um ou mais servidores de warehouse, que
oferecem visões multidimensionais dos dados para uma variedade de ferramentas
front-end: ferramentas de consulta, geradores de relatórios, ferramentas de
análises específicas e ferramentas de data mining. Finalmente, há um
repositório para armazenamento e gerenciamento de dados de log e ferramentas
para monitoramento e administração do sistema total.
11
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
Monitoramento e Administração
Repositório
de Metadados
Servidores
OLAP
Análise
Data Warehouse
Fontes externas
Extração
Transformação
Carga
Refresh
Relatórios
Data Mining
Dados operacionais
Data Marts
Figura 1. Arquitetura de Data Warehousing
Ferramentas
As informações sobre os dados do warehouse são armazenadas no
catálogo do sistema e é freqüentemente armazenado e gerenciado em um banco
de dados separado chamado repositório de metadados. O tamanho e
complexidade do catálogo é em parte devido ao tamanho e complexidade do
warehouse em si e em parte porque uma grande porção de informação
administrativa deve ser mantida. Por exemplo, precisamos ser informados sobre a
fonte de cada tabela do warehouse e quando ela sofreu o último refresh, além da
descrição de seus campos.
2.6. Data Mart (DM)
É um data warehouse de pequena capacidade usado para atender a uma
unidade específica de negócios. Data Marts não são diferenciados dos DWs com
base no tamanho, mas no uso e gerenciamento. Entretanto, DMs são menores e
menos complexos do que DWs e portanto são tipicamente mais fáceis de
construir e manter.
Em média, um DM pode ser construído num período de 3 a 6 meses,
enquanto um DW leva de 2 a 3 anos para ser concluído.
Os motivos que levam ao desenvolvimento de um Data Mart podem ser:
12
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
servir como projeto piloto, atender necessidades imediatas de uma unidade,
atender a restrições de custo, tempo, etc., entre outros.
13
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
3. Modelagem de Dados para DW
3.1. Modelo de dados multi-dimensional
A fim de facilitar análises e visualizações complexas, os dados em um
warehouse são tipicamente modelados num formato multi-dimensional. Por
exemplo, num data warehouse de vendas, a época da venda, o local geográfico, o
cliente e o produto podem ser algumas das dimensões de interesse.
Freqüentemente essas dimensões são hierárquicas: a época da venda pode ser
organizada numa hierarquia dia – mês – 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 dimensões com seus diferentes valores distribuídos ao
longo da mesma. Os pontos internos ao cubo representam os valores de medida
do negócio – no nosso exemplo, os valores de vendas.
Local
Tempo
Produto
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
local e por produto para um particular período de tempo.
14
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
LOCAL
LOC1
LOC2
LOC3
...
P1
P2
P3
P4
...
PRODUTO
Figura 3. Matriz bi-dimensional
A adição de uma dimensão 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 regiões de venda. Cada célula
representa a venda de um produto específico, em um período de tempo
específico (ano, trimestre, mês,...) em um local específico. Adicionando outras
dimensões, teremos um hipercubo.
Tempo
(codTempo)
t4
t3
t2
t1
Reg1
Reg2
P1
Produto
(codProd)
P2
Reg3
Local
(codLocal)
...
.
P3
...
Figura 4. Cubo de dados de vendas
Os dados podem ser consultados diretamente em qualquer combinação de
dimensões, permitindo que consultas complexas no banco de dados original
sejam realizadas de forma mais direta e com maior desempenho. Existem
15
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
ferramentas que permitem a visualização dos dados de acordo com a escolha de
dimensões do usuário.
Num ambiente relacional os dados da matriz multi-dimensional podem ser
representados como uma relação, como ilustrado na figura 5. Nessa relação as
tuplas estão agrupadas de acordo com as fatias (slices) verticais, paralelas com
relação ao eixo do tempo. Essa relação que associa as dimensões à medida de
interesse é chamada tabela fato. Note na figura 5 que somente constam da
tabela as composições produto-local-tempo que possuem valor de vendas. Cada
dimensão pode ter um conjunto de atributos associados. Por exemplo, a
dimensão Local é identificada pelo atributo codlocal, que foi usado para identificar
um local na tabela Vendas. A dimensão Local pode ter os atributos adicionais
Cidade, Estado e País. A dimensão Produto pode conter os atributos codProd,
Descrição, Marca, Categoria, Estilo, Preço. 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 dimensão Tempo pode ter
os atributos Data, Semana, Mês, Trimestre, Ano, além do identificador
codTempo.
codProd
P1
P1
P1
P2
P2
P3
P3
P4
P4
P1
P1
P2
P2
P2
P3
P3
codLocal
loc1
loc2
loc3
loc1
loc2
loc1
loc4
loc2
loc3
loc1
loc2
loc1
loc2
loc4
loc1
loc4
codTempo
t1
t1
t1
t1
t1
t1
t1
t1
t1
t2
t2
t2
t2
t2
t2
t2
vendas
1000
880
1025
775
1002
888
989
1550
900
1030
920
1010
700
1100
980
980
fatia (slice) do
período de tempo t1
fatia (slice) do
período de tempo t2
16
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
P4
P4
...
loc2
loc3
t2
t2
1550
950
Figura 5. Relação Vendas (tabela fato)
As dimensões podem ser representadas como relações:
Local (codLocal, Cidade, Estado, País)
Produto (codProd, Descrição, Marca, Categoria, Estilo, Preço)
Tempo( codTempo, Data, Semana, Mês, Trimestre, Ano)
Para cada dimensão, o conjunto de valores associados podem ser
estruturados em uma hierarquia. Por exemplo, cidades pertencem a estados e
estados pertencem a países. Datas pertencem a semanas e a meses; semanas e
meses estão contidos em trimestres e trimestres estão contidos em anos. A figura
6 mostra as hierarquias para Produto, Local e Tempo do exemplo aqui
considerado.
TEMPO
PRODUTO
categoria
ano
estilo
código
produto
LOCAL
país
trimestre
semana
estado
mês
cidade
data
Figura 6. Possíveis hierarquias para Produto, Tempo e Local
3.2. Consultas OLAP
Sobre o cubo de dados (data warehouse) podem ser aplicadas as
17
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
operações típicas de OLAP, para viabilizar a extração eficaz de informações do
mesmo.
As operações suportadas pelo modelo multi-dimensional são fortemente
influenciadas por ferramentas existentes para usuário final tal como aquelas que
trabalham com planilhas eletrônicas. A meta é oferecer ao usuários finais, que
não são especialistas em SQL, uma interface intuitiva e poderosa para tarefas
comuns de análise de negócio. Cada operação sobre o conjunto de dados multidimensional retorna ou uma apresentação diferente ou uma sumarização desse
conjunto de dados. O conjunto de dados está sempre disponível para o usuário
manipular, independente do nível de detalhe em que ele está sendo visto.
3.2.1. Agregação
Uma operação muito comum é agregar uma medida sobre uma ou mais
dimensões. Exemplos de consultas típicas são:

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 três primeiras consultas podem ser expressas como consultas SQL sobre
as tabelas fato e dimensão, mas a última consulta não pode ser expressa em
SQL (embora se possa conseguir um resultado satisfatório ordenando o total
de vendas através de ORDER BY).
Um outro uso de agregação é sumarizar em diferentes níveis de hierarquia.
Isso é conseguido através das operações OLAP roll-up e drill-down que
oferecem visualizações hierárquicas dos dados.
18
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
3.2.2. Roll-up
A operação roll-up realiza um aumento no nível de agregação dos dados,
agrupando em unidades maiores ao longo da dimensão. Por exemplo, a figura 7
mostra os dados de venda usando a operação roll-up que, a partir de informações
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
Calçado abrange os produtos P11 a P15, etc.).
LOCAL
CATEGORIAS
DE
PRODUTOS
Roupa
Calçado
Bijuteria
...
São Carlos
10.000
7.000
30.000
...
Ribeirão Preto
15.000
10.000
20.000
...
...
...
...
...
Figura 7. A operação roll-up (venda por categoria/cidade)
3.2.2. Drill-down
A operação drill-down oferece a capacidade oposta, fornecendo uma
visão mais detalhada. Por exemplo, dado o total de vendas por estado e por
categoria de produto, podemos solicitar uma apresentação mais detalhada
desagregando vendas de cada estado por cidade e também quebrando categoria
de produtos por estilos, conforme figura 8.
19
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
São Paulo
Rio Grande do Sul
...
S.Carlos Rib.Preto
Estilos
Roupa
Estilos
Calçado
Estilos
Bijouteria
Lins
Assis
P. Alegre
Caxias
Sul
A
B
C
D
A
B
D
A
B
C
...
Figura 8. A operação drill-down
3.2.4. Pivot
20
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
A mudança de uma hierarquia dimensional (orientação) para uma outra é
facilmente obtida em um cubo de dados utilizando a técnica chamada pivoting
(ou rotação). A operação pivot realiza uma re-orientação do ângulo de visão dos
dados. Nessa técnica, os eixos podem ser mostrados em orientações diferentes.
Por exemplo, pode-se fazer uma rotação no cubo de dados da figura 4 para
mostrar rendimentos de vendas regionais como linhas, os totais de rendimentos
diários como colunas e os produtos da companhia na terceira dimensão (figura 9).
Isso equivale a ter uma tabela de venda regional para cada produto
separadamente, onde cada tabela mostra, para o produto específico, totais de
vendas para cada local e cada período considerado.
...
P4
Produto
P3
P2
P1
t1
t2
Reg1
t3
...
Tempo
Local
Reg2
.
Reg3
...
Figura 9. Rotação (Pivot) do cubo de dados de vendas
3.2.5. Comparando com consultas SQL
Algumas consultas OLAP não podem ser (ou não 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
21
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
dimensão. A terceira consulta pode ser expressa também, mas é
mais
complicada em SQL. A última consulta não pode ser expressa em SQL, se n é
para ser um parâmetro da consulta. Observe que todas as consultas a seguir
envolvem o tempo. Na verdade a dimensão Tempo é muito importante em OLAP.
Consultas:

Encontre o total de vendas por mês.

Encontre o total de vendas por mês para cada cidade.

Encontre a variação de porcentagem no total de vendas mensalmente para
cada produto.

Encontre a média de movimento de vendas de n dias. (Para cada dia, deve ser
calculada a média de vendas diária sobre os n dias precedentes).
Um grande número de consultas OLAP, entretanto, pode ser expresso em
SQL. Tipicamente elas envolvem agrupamento e agregação, e uma única
operação OLAP conduz a várias consultas relacionadas. Por exemplo, a mesma
informação apresentada na tabela da figura 7 pode ser obtida através 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 operação realizada na tabela da figura 7 é a Roll-up, que agrupa os
dados em unidades maiores. Cada operação roll-up corresponde a uma consulta
SQL com GROUP BY. Em geral, dada uma medida com k dimensões associadas,
podemos fazer um roll up sobre qualquer subconjunto dessas k dimensões e
então tem-se 2k possíveis consultas SQL.
Uma extensão proposta para o SQL chamada CUBE é equivalente a uma
22
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
coleção de comandos GROUP BY, com um comando GROUP BY para cada
subconjunto das k dimensões. 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
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
4. Projeto do Data Warehouse
4.1. Esquema Estrela
Diagramas entidade-relacionamento e técnicas de normalização são
popularmente usadas para projetos de bases de dados operacionais. Nos
sistemas de apoio à decisão, porém, devem prevalecer métodos de projeto que
tenham como alvo a eficiência 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
dimensão para cada dimensão, além de uma tabela fato, a qual contém um
relacionamento com cada uma das tabelas dimensão e um valor para a dimensão do
negócio relativo ao conjunto de dimensões referenciadas. A Figura 10 mostra o
esquema estrela para o exemplo de vendas de produto adotado no capítulo 3.
(tabela dimensão)
VENDAS
(tabela fato)
PRODUTO
codProd
codTempo
codRegião
CodProduto
Categoria
DescrCategoria
Estilo
Preço
(tabelas dimensão)
TEMPO
vendas
valor-vendas
codTempo
Data
Semana
Mês
Trimestre
Ano
REGIÃO
Figura 10. Esquema estrela do exemplo dado (fig.4)
CodReg
Cidade
Estado
País
24
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
Uma outra maneira de representar esquema estrela é fornecido na figura 11.
TEMPO
CodTempo Data Semana Mês Trimestre Ano
REGIÃO
CodRegião Cidade Estado País
VENDA
CodProd CodLocal
CodTempo
vendas
valor-vendas
PRODUTO
CodProd Categoria DescrCategoria
Estilo Preço
Figura 11. Outra representação de esquema estrela
O volume maior dos dados está tipicamente na tabela fato, que não tem
redundância. Usualmente ela está na Forma Normal de Boyce Codd (FNBC).
Para minimizar o tamanho da tabela fato, os identificadores das dimensões (ex.
CodLocal, CodTempo) são gerados pelo sistema.
As tabelas dimensão usualmente são não normalizadas. A razão para isso
é que um banco de dados usado para OLAP é estático; assim, anomalias de
atualização, inserção e eliminação não são importantes. Além disso, o espaço de
armazenamento ganho através da normalização das tabelas dimensão é
desprezível, frente ao tempo de processamento gasto para combinar a tabela fato
com as tabelas dimensão, caso estas fossem quebradas em tabelas menores
normalizadas (que podem conduzir a junções adicionais).
25
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
4.2. Esquema Snowflack
Esquemas estrela não fornecem explicitamente suporte para hierarquias de
atributos. Um modelo refinado do esquema estrela é o chamado esquema
snowflack, no qual as tabelas dimensão são organizadas em uma hierarquia
através de sua normalização, como mostrado na Figura 12. Isso traz vantagens
para a manutenção das tabelas-dimensões. Entretanto, a estrutura não
normalizada das tabelas dimensão nos esquemas estrela podem ser mais
apropriados para a manipulação das dimensões.
(tabelas dimensão)
PRODUTO
VENDA
(tabela fato)
CodProd
CodRegião
CodTempo
vendas
valor-vendas
CodProduto
Categoria
Estilo
Preço
(tabelas dimensão)
REGIÃO
CodRegião
Cidade
Estado
ESTADO
Estado
País
TEMPO
CATEG-PROD
Categoria
DescriçãoCateg
CodTempo
Data
Semana
Mês
Ano
Mês
Trimestre
Figura 12. Esquema Snowflack
As figuras 13 e 14 apresentam um outro exemplo de esquemas estrela e
snowflack.
26
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
PEDIDO
NroPedido
DataPedido
...
PRODUTO
CodProduto
NomeProduto
DescrProduto
Categoria
DescrCateg
EstiloProduto
PreçoUnitario
...
RENDIMENTO
VENDAS
(tabela fato)
VENDEDOR
CodVendedor
NomeVendedor
CidadeVendedor
Cota
...
NroPedido
CodVendedor
CodCliente
CodProduto
ChaveData
Cidade
Quantidade
ValorTotal
...
CLIENTE
CodCliente
NomeCliente
EndereçoCliente
CidadeCliente
...
DATA
CIDADE
ChaveData
Cidade
Data
Estado
Mês
País
Ano
PEDIDO
...
... VENDEDOR
NroPedido
VENDAS
CodVendedor
Figura 13. Outro exemplo
de esquema estrela
DataPedido
(tabela-fato)
NomeVendedor
...
CidadeVendedor
NroPrdido
Cota
PRODUTO
CodVendedor
...
CodProduto
CodCliente
Categoria
DescrCateg
ESTADO
Estado
País
NomeProduto
DescrProduto
Categoria
PreçoUnitario
...
CodProduto
ChaveData
Cidade
Quantidade
ValorTotal
...
CLIENTE
CodCliente
NomeCliente
EndereçoCliente
CidadeCliente
...
DATA
CIDADE
Cidade
Estado
...
Figura 14. Esquema Snowflack
MÊS
Mês
Ano
ChaveData
Data
Mês
...
27
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
4.3. Constelação de Fatos
Constelação de fatos são exemplos de estruturas mais complexas nas
quais múltiplas tabelas fato compartilham tabelas dimensão. A figura 15 mostra
uma constelação de fatos com duas tabelas fato, Resultado de Vendas e Previsão
de Vendas, que compartilham a tabela dimensão Produto. Constelações de fatos
limitam as possíveis consultas para o warehouse.
RESULTADO.
VENDAS
(tabela fato)
PRODUTO
(tabela dimensão)
CodProduto
Categoria
DescrCategoria
Estilo
Preço
CodProduto
CodRegião
CodTempo
Vendas
PREVISÃO
VENDAS
(tabela fato)
CodProduto
CodRegião
CodTempoFuturo
ProjeçãoVendas
Figura 15. Uma constelação de fatos
Outro exemplo seria uma constelação de fatos representando despesas projetadas e
despesas reais, contendo duas estrelas que compartilham várias dimensões.
4.3. Visões Materializadas
A fim de otimizar a análise multi-dimensional, tornando-a viavelmente
utilizável pelo usuário, uma das soluções adotadas atualmente é a précomputação de agregações em alguns subconjuntos de dimensões e suas
hierarquias correspondentes. Outra solução comumente usada é a materialização
de consultas muito freqüentes. Essas tabelas especiais são chamadas de visões
materializadas.
As visões materializadas geralmente consistem de junções da tabela fato com
um subconjunto de tabelas dimensão, com a sumarização de uma ou mais medidas
de valor do negócio, agrupadas por um conjunto de atributos das tabelas dimensão.
28
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
A seleção das visões a materializar deve levar em conta a freqüência de
utilização,
características
de
carga
de
trabalho,
custo
de
atualizações
incrementais e exigências 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 São Paulo, do trimestre mais recente. Ter
uma tabela que contenha dados sumários sobre esses parâmetros pode acelerar
significativamente o processamento das consultas. A figura 16 ilustra esse
exemplo.
Vendas - Estado São Paulo
Data
Categoria
01/07/99
Roupa
02/07/99
...
25/11/99
2000
3000
...
1000
Calçado
500
650
...
220
Bijuteria
300
550
...
430
agregação
Vendas - Estado São Paulo
Categoria
Trimestre 3
Roupa
21200
Calçado
5670
Bijuteria
6980
Figura 16. Agregação
Consultas ad hoc realizadas pelos usuários são respondidas usando as
tabelas originais juntamente com sumários pré-computados.
29
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
4.4. Construindo um Data Warehouse
Há muitos desafios na criação e manutenção de um data warehouse. É
necessário projetar um bom esquema de banco de dados para manter uma
coleção integrada de dados copiados de diversas fontes. Por exemplo, o data
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
escritórios em diferentes países (ou filiais). Uma vez que os dados são criados e
mantidos por diferentes grupos, existem algumas questões que devem ser
avaliadas, tais como erros semânticos, diferenças na forma como as tabelas
foram normalizadas ou estruturadas, nomes diferentes para o mesmo atributo.,
entre outros.
Para construir um data warehouse, é necessário ter uma visão antecipada
do uso do warehouse. Não há uma maneira para antecipar todas as possíveis
consultas ou análises durante a fase de projeto. Entretanto, o projeto deve
especificamente suportar consultas ad-hoc, isto é, acesso aos dados com
qualquer combinação significativa de valores para os atributos na tabela
dimensão ou tabela fato.
A aquisição de dados para o warehouse envolve os seguintes passos:

extração de dados: Os dados podem ser extraídos de múltiplas fontes
heterogêneas. Durante essa fase pode ser necessário selecionar dados sobre
itens específicos ou categorias
de itens, ou de armazéns em uma região
específica do país;

formatação: Os dados devem ser formatados para ficar consistentes com o
warehouse. Nomes, significados e domínios de dados vindos de fontes não
relacionadas devem ser conciliados. Por exemplo: companhias subsidiárias de
uma grande corporação podem ter diferentes calendários fiscais com os
trimestres terminando em diferentes datas, tornando difícil agregar dados
financiais por trimestre; outro exemplo é a existência de diferentes unidades
monetárias. Essas inconsistências de formato devem ser resolvidas.

Limpeza dos dados: Os dados devem passar por uma limpeza
para
30
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
minimizar erros, preencher informação ausente, quando possível, 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 construção
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 correção automáticos de dados errôneos e incompletos é
uma tarefa difícil. Alguns aspectos, tais como checagem de domínio, são
facilmente codificados em rotinas de limpeza de dados, mas reconhecimento
automático de alguns problemas de dados pode ser mais desafiador.
Por exemplo, o processo de limpeza pode corrigir CEPs inválidos 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 não padronizado (São Paulo e
S.Paulo)

nome de bairro errado ("XXX") e não padronizado ("Centro" transformar para letras maiúsculas).

as datas devem seguir um mesmo formato.

valores nulos devem ter um tratamento adequado (data com valor
"000000")

eliminar dados inúteis para o data warehouse: a última linha da tabela
não representa informação útil.
CIDADE
BAIRRO
DATA
SÃO CARLOS
CENTRO
10/09/99
31
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
CENTRO
Centro
08/03/1999
RIO DE JANEIRO
XXX
13-05-99
SÃO PAULO
PINHEIROS
15101998
S. PAULO
SANTANA
000000
RIBEIRÃO 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 várias 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 hierárquicos) para um
modelo multi-dimensional.

Os dados devem ser carregados no warehouse. Essa tarefa é significativa
devido ao grande volume de dados do warehouse. São requeridas
ferramentas de monitoração para a carga, bem como métodos para
recuperação a partir de cargas incompletas ou incorretas. Questões sobre a
atualização dos dados também são levadas em conta. As seguintes questões
devem ser consideradas:

Até que ponto os dados podem ser atualizados?

O warehouse pode se tornar off-line e por quanto tempo?

Quais são as interdependências de dados?

Qual é a disponibilidade de armazenamento?

Quais são os requisitos de distribuição (tais como para replicação
e particionamento)?
32
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação

Qual é o tempo de carga (incluindo limpeza, formatação, cópia,
transmissão, reconstrução de índice, ...)?

Uma tarefa de projeto muito importante é avaliar quais tabelas sumário devem
ser materializadas para alcançar o melhor uso de memória disponível 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 necessário purgar periodicamente os dados; por
exemplo, um warehouse que mantém dados sobre os últimos 3 anos pode
purgar seus dados a cada ano.
4.5. Resumindo as Funcionalidades Típicas 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 eletrônicas: isto é, suportes conhecidos oferecidos para
planilhas eletrônicas bem como suporte de programas de
aplicação OLAP.
-
Processamento de consulta eficiente
-
consultas estruturadas
-
consultas ad hoc
-
data mining
-
visões materializadas
As funcionalidades pré-programadas que são oferecidas pelos data
warehouses são:
-
Roll-up: os dados são sumarizados com crescente generalização (por
33
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
ex., de semanalmente para trimestralmente para anualmente).
-
Drill-down: crescentes níveis de detalhe são revelados (operação
oposta de roll-up).
-
Pivot (rotação): é realizada tabulação cruzada.
-
Slice and dice: são realizadas operações de projeção sobre as
dimensões.
-
Ordenação: os dados são ordenados através de um atributo.
-
Seleção: os dados são disponíveis por valor ou
agrupados em
categorias de valores.
-
atributos derivados (computados): valores derivados através de
operações sobre dados armazenados.
4.6. Considerações Finais
4.6.1. Data warehousing e Visões
Data warehouses têm sido considerados por algumas pessoas como sendo
uma extensão de funções e visões do banco de dados. Entretanto visões
fornecem somente um subconjunto das capacidades de data warehouses. Visões
e data warehouses são parecidos nos seguintes aspectos:
-
ambos têm dados extraídos de bancos de dados;
são orientados ao assunto .
Data warehouses são diferentes de visões nos seguintes aspectos:
-
Data warehouses existem como armazenamento persistente ao invés
de ser materializado sob demanda;
-
Data
warehouses
não
são
usualmente
relacionais,
mas
sim
multidimensionais. Visões de um banco de dados relacional são
relacionais.
-
Data warehouses podem ser indexados para otimizar performance.
34
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
Visões não podem ser indexadas independente dos bancos de dados
utilizados.
-
Data warehouses caracteristicamente fornecem suporte específico de
funcionalidade; visões não podem fornecer.
-
Data warehouses fornecem grande quantidade de dados integrados e
freqüentemente temporais, geralmente mais do que está contido em um
banco de dados, enquanto que visões são um extrato de um banco de
dados.
4.6.2. Dificuldades na Implementação de Data Warehouses
A construção de um warehouse de um amplo empreendimento em uma
grande organização pode levar anos, considerando desde a fase inicial de
concepção até a implementação. 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
organizações com necessidades urgentes de OLAP, sistemas de suporte à
decisão e/ou suporte de data mining.
Alguns aspectos a serem considerados são:

A administração de um data warehouse é uma tarefa intensiva, proporcional
ao tamanho e complexidade do warehouse.

O controle de qualidade dos dados e a consistência são aspectos importantes
a considerar. Cada vez que um banco de dados fonte muda, o administrador
do data warehouse deve considerar as possíveis interações com outros
elementos do warehouse.

Projeções de uso devem ser estimadas antes da construção do data
warehouse e devem ser revisadas continuamente para atender requisitos
correntes.
35
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação

Administração de data warehouse irá requerer habilidades mais amplas do
que são necessárias para administração de banco de dados tradicional.
36
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
5. Servidores OLAP
Servidores que utilizam bancos de dados relacionais tradicionais não foram
concebidos para propiciar o uso inteligente de índices e de outros recursos
necessários para suportar visões multidimensionais de dados. Em adição aos
servidores
relacionais
tradicionais,
há
três
opções
principais
para
a
implementação 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 são
híbridos dos dois anteriores.
5.1. Servidores ROLAP
Nos servidores OLAP Relacionais (ROLAP) os dados são armazenados em
tabelas de bancos de dados relacionais ou relacionais estendidos. Eles utilizam
SGBDs relacionais para gerenciar os dados e agregações do esquema estrela do
warehouse. Também suportam extensões a SQL e acesso e métodos de
implementação especiais, tais como o OLAP midleware para a implementação
eficiente do modelo multi-dimensional e suas operações. Dessa forma, sua
estrutura de dados é implementada por tabelas relacionais, e uma célula do
espaço multi-dimensional é representada por uma tupla. Essa tupla
carrega
alguns atributos que identificam a posição da célula no espaço multi-dimensional,
além de outros atributos que contém os valores de dados correspondentes àquela
célula.
Por utilizarem bancos de dados relacionais para implementar o modelo
multi-dimensional, os servidores OLAP precisam reescrever as consultas dos
usuários para compatibilizá-las com as visões materializadas e gerar múltiplas
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
37
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
armazenar dados esparsos de forma mais compacta em tabelas do que em
arrays.
5.2. Servidores MOLAP
Outra
opção
Multidimensionais
amplamente
adotada
(MOLAP). Servidores
são
MOLAP
os
servidores
implementam
as
OLAP
visões
multidimensionais diretamente, armazenando dados em algumas estruturas
especiais (por exemplo, arrays esparsos) e executando as operações OLAP
diretamente sobre essas estruturas. O cubo de dados é implementado através do
mapeamento de suas dimensões para os índices do array, de forma que o
conteúdo do array é formado pelos valores contidos em cada célula do cubo.
Servidores MOLAP possuem excelentes propriedades de indexação devido
ao fato de ser mais simples localizar uma célula num array do que numa tabela.
Por outro lado, quando os dados são esparsos, os servidores MOLAP perdem
performance, sendo que nesses casos algumas técnicas de matrizes de
compressão devem ser exploradas. Para pequenos e médios conjuntos de dados,
eles são mais eficientes em armazenamento e recuperação dos dados.
5.3. Servidores HOLAP
Alguns servidores OLAP adotam uma forma de armazenamento em dois
níveis, a fim de manipular conjuntos de dados densos e esparsos. O conjunto de
dados de alguns subcubos dimensionais que são identificados como densos são
armazenados no formato de arrays. O restante dos subcubos, que são esparsos,
são armazenados em tabelas empregando tecnologias de compressão. Isso
resulta em um método de armazenamento híbrido chamado de OLAP Híbrido
(HOLAP).
38
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
5.4. On-Line Analytical Mining (OLAM)
Considerando o alto poder do método OLAP para se realizar análises
multidimensionais e vislumbrando as vantagens geradas pela possibilidade de
utilizar essa análise na preparação dos dados na aplicação de Data Mining, J.
Han e sua equipe desenvolveram um mecanismo batizado de OLAM (On-Line
Analytical Mining), que propicia a aplicação de KDD num ambiente multidimensional em grandes bases de dados e data warehouses .
As principais vantagens geradas por essa abordagem são:
 A maioria das ferramentas de Data Mining necessitam trabalhar com dados
integrados, consistentes e limpos, que exigem um árduo trabalho de limpeza,
transformação e integração dos dados nas fases que precedem a aplicação de
DM no processo KDD. Num data warehouse, a maior parte dessa preparação
normalmente já foi realizada, servindo esse warehouse para a aplicação tanto
de OLAP quanto de DM.
 DM efetivo necessita de análises exploratórias de dados. O usuário
frequentemente deseja navegar de forma flexível ao longo da base de dados,
selecionar diferentes partes de dados relevantes e analisar os dados em
diferentes granularidades.
 A integração de OLAP com múltiplas funções de DM permite ao usuário
investigar diferentes tarefas de data mining de forma fácil e flexível.
39
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
40
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
Apêndice A
ESTUDO DE CASO 1 – Criação de um Data Mart
Este estudo de caso descreve a criação 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 análise de vendas e marketing para os seus
principais clientes, que são empresas de construção e pavimentação, além de,
eventualmente, avaliar a eficiência da rede de distribuição.
Os dados de entrada originam-se de sistemas IBM ES/9000 e AS/400.
A ferramenta utilizada para a execução do projeto foi o Oracle Data Mart
Suite para Windows NT.
Objetivou-se fornecer aos usuários do sistema acesso ad hoc gráfico às
informações gerenciais, sem envolver grande volume de processamento a cada
acesso realizado.
A principal área de interesse identificada foi a análise dos lucros.
O primeiro passo foi a identificação acurada de todas as informações que o
usuário realmente necessitava em suas consultas, definindo-se, assim, o grau de
detalhamento que o sistema deveria contemplar. O estudo dos relatórios já
existentes foi um ponto de partida.
O passo seguinte foi a compreensão do mecanismo de cálculo dos lucros
da empresa, já que o antigo sistema de contas não fornecia explicitamente esses
valores, mas sim um grande número 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, cálculos no momento da consulta.
41
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
Outra característica a ser definida foi o período de tempo em que o usuário
gostaria de ter suas informações armazenadas, já que a dimensão tempo é
crucial para o esquema estrela.
Iniciou-se, então, o projeto do banco de dados, descrito a seguir.
O esquema estrela é construído objetivando-se simplicidade e velocidade
de recuperação. Cada tabela dimensão possui uma chave primária, usualmente o
ID. A tabela fato contém uma chave composta por todas as chaves das
dimensões.
Ao invés de utilizar diretamente os identificadores das tabelas dimensão,
foram criadas chaves sintéticas tanto para as chaves primárias das tabelas
dimensão, quanto para as chaves estrangeiras da tabela fato. Esse procedimento
torna mais eficientes as consultas, além de manter uma homogeneidade na base
de dados, garantindo também a unicidade dos identificadores e uma maior
facilidade na manutenção do data mart.
A tabela fato da empresa foi construída a partir de tickets e faturas de
produtos (pedras) que foram comprados e transportados para um certo local.
O grau de granularidade (nível de detalhe informacional) que o cliente
deseja obter nos relatórios tem um impacto sobre o projeto da tabela fato.
Importante: obter dos clientes o que eles necessitam ver e não o que eles
querem ver.
As tabelas dimensão criadas são mostradas a seguir.
Tabela dimensão Fábrica:
Tabela estática com detalhes sobre todas as fábricas da empresa.
FÁBRICA
Código
Descrição
Id-linha-produto
DIM-FÁBRICA
Chave-Fábrica
Cod-Fábrica
Descr-Fábrica
Região-Fábrica
42
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
Tabela dimensão Tempo:
A tabela Tempo foi necessária, pois os relatórios necessitam avaliar os dados por
dia, feriados, períodos fiscais, etc. Se os agrupamentos fossem somente
referentes a anos/meses a tabela Tempo poderia ser dispensada.
DIM-TEMPO
Chave-tempo
Id-data
Dia-do-mês
Numero-mês
Descr-mês
Semana-do-ano
Descr-dia
Num-ano
Dia-do-ano
Número-trimestre
Data-venda
Tabela dimensão Item:
ITEM
Código
Tipo
Descrição
Id-linha-item
Classe
LINHA-ITEM
Código
Descrição
DIM-ITEM
Chave-item
Tipo-item
Cód-linha-item
Descr-linha-item
Classe-item
Sub-classe-item
Flag-item
Descrição-item
43
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
Tabela dimensão Cliente:
CLIENTE
Código
Nome
Endereço1
Endereço 2
Cidade
Estado
CEP
Represent-venda
Tipo
DIM-CLIENTE
Chave-cliente
Cod-cliente
Nome-cliente
Cod-Repres-venda
Nome-repres-venda
Tipo-cliente
Tabela dimensão Representante de Vendas :
REPRES-VENDA
DIM-REPRES-VENDA
Código
Nome
Chave-repr-venda
Codigo-repr-venda
nome-repr-venda
Tabela dimensão Transportadora:
TRANSPORTADORA
DIM-TRANSPORTADORA
Código
Nome
Chave-Transportadora
Código
Nome
44
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
Tabela fato Ticket -Fatura:
TICKET-FATURA
Número-fatura
Número-ticket
Tipo-ticket
Cod-Fábrica
Cod-cliente
Código
Data-envio
Cod-produto
Qtde-enviada
Preço-produto
Custo-fixado-produto-por-unidade
Custo-variavel-produto-por-unidade
Cód-transportadora
Custo-transporte-por-unidade
Região-entrega
Local-entrega
Tabela dimensão Região de Entrega:
DIM-REGIÃO-ENTREGA
Chave-entrega
Estado-entrega
País-entrega
Local-entrega
Região-entrega
45
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
O negócio da empresa era rastrear e avaliar as faturas e tickets para cada
carga transportada por seus caminhões. Havia vários tickets por fatura. Cada
ticket tinha medidas e preços tais como datas da transportadora, quantidades de
itens, custos totais, etc. Devido ao relacionamento entre tickets e faturas, decidiuse fundir as duas tabelas em uma tabela fato principal. Para cada fatura pode
haver vários tickets.
Para a tabela fato, a solução adotada foi a criação de uma única tabela
contendo as informações sobre os tickets e os pedidos que os contém.
Tabelas sumário:
A fim de reduzir o tempo de processamento de recuperação de dados que
são freqüentemente executadas, foram criadas tabelas sumário para a dimensão
tempo, devido à sua alta freqüência de consultas. As tabelas criadas produzem
freqüentemente relatórios por mês, trimestre e ano.
46
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
Apêndice B
ESTUDO DE CASO 2 – Cubo Multidimensional e OLAP
Este estudo de caso descreve a implementação do cubo multidimensional
para consultas OLAP realizada em uma usina de açúcar e álcool, situada no
estado de São Paulo.
O objetivo foi auxiliar nas análises setoriais, gerar regras de negócio,
analisar riscos e produtividade e permitir a comparação e a combinação de
informações. Algumas perguntas das quais desejava-se conhecer as respostas:
- com quais clientes praticou-se o melhor preço?
- quais clientes geram maior lucratividade?
- quais os mercados mais lucrativos?
- qual é o perfil dos clientes?
- em que regiões concentram-se as vendas?
Inicialmente, foram comparadas as duas alternativas: OLTP e OLAP, a fim
de verificar as vantagens da solução OLAP, justificando, assim a implementação
do projeto. Algumas das características levantadas foram:
OLTP
- utilização da base de dados operacionais;
- demora na execução de consultas e relatórios;
- pouca flexibilidade;
- muitos relatórios utilizados apenas uma vez;
- necessidade de pessoal de informática para
desenvolvimento de templates de relatórios;
- grande volume de informações a serem
processadas.
OLAP
- utilização de data marts
- consultas e relatórios obtidos instantaneamente
- visão multidimensional das informações;
- transparência da origem dos dados (Ingres,
texto, excel, web);
- arquitetura cliente/servidor, permitindo
utilização remota;
- geração de regras de negócio.
Para o desenvolvimento do projeto, foi contratada uma empresa de
consultoria com experiência na área.
A ferramenta utilizada para a extração/visualização dos dados foi a O3.
A criação do cubo foi realizada utilizando-se o aplicativo O3 Designer,
seguindo-se os seguintes passos:
47
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação

Definição das dimensões e medidas do cubo e das origens de dados:

Definição das queries:
48
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação

Codificação das queries:

Definição dos campos das queries:
49
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação

Definição das hierarquias dimensionais:

Definição das medidas:
50
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
A seguir são ilustrados alguns exemplos de consultas com visualização
gráfica.

Volume de vendas anual do produto açúcar para os diferentes ramos de
atividade:

Drill-down no ramo Doces, enfocando o ano de 98 (vendas mensais):
51
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
52
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação

Volume de vendas anual do produto açúcar para os diferentes destinos:

Enfoque no ano de 98 (vendas mensais):
53
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação

Receita líquida obtida no ano de 98, com o produto açúcar, para o cliente
Vonpar:
54
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
Bibliografia
[1] Inmon, W.H. Building the Data Warehouse. John Wiley, 1992.
[2] Chaudhuri, S., Dayal, U. An Overview of Data Warehousing and OLAP
Technology, ACM SIGMOD Record, vol.26, 65-74, 1997.
[3] Elmasri, R., Navathe, S. Fundamentals of Databases, 3ª edição, 2000.
[4] Ramakrishnan, R. Database Management Systems. McGraw-Hill, 1998.
[5] Felipe, J.C. O Processo de Extração de Conhecimento de Bases de
Dados Aplicado a Bancos de Dados Multimídia Orientados a Objetos.
Monografia de Exame de Qualificação de Mestrado. Programa de PósGraduação em Ciência da Computação, Departamento de Computação UFSCar, São Carlos, Fevereiro 1999.
[6] Becker, K., Pereira, W. Tutorial de Data Warehouse, XIV Simpósio
Brasileiro de Banco de Dados, outubro, 1999, Florianópolis, 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 OLAP
Mining, Thesis submitted for the degree of Master of Science in the
Department of Computer Science of Simon Fraser University, Canada,
september 1998.
[11] Harinarayan, V., Rajaraman, A., Ullman, J. D., Implementing Data Cubes
Efficiently, Proc. ACM SIGMOD Int. Conference on Management of Data,
June 1996.
55
Universidade Federal de São Carlos
Departamento de Computação
Curso de Pós-Graduação “Lato-Sensu” em Computação
[12] Roussopoulos, N., Materialized Views and Data Warehouses, ACM
SIGMOD Record, Vol. 27, No. 1, Março 1998.
56