Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Tópicos Avançados
de Bases de Dados
Henrique Madeira
2004/2005
1
Henrique Madeira, DEI-FCTUC, 2001
Data Warehousing e OLAP
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
2
1
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Bibliografia (tópico de Data Warehousing)
-
The Data Warehouse Lifecycle Toolkit, Ralph Kimbal, Ed. J. Wiley & Sons, Inc,
2001.
-
"The Data Warehouse Toolkit", Ralph Kimbal, Ed. J. Wiley & Sons, Inc, 1996;
-
"Building the data warehouse", W. H. Inmon, Ed. J. Wiley & Sons, Inc, 1996;
-
"The data model resouce book", L. Silverston, W. H. Inmon e K. Graziano, Ed. J. Wiley
& Sons, Inc, 1997;
-
"Data Warehousing, Concepts, Technologies, Implementations, and Management", Harry
Singh, Ed. Prentice Hall, 1998;
-
"The Internet Data Warehouse", Rick Tanler, Ed. J. Wiley & Sons, Inc, 1997;
-
"Managing the Data Warehouse", W. H. Inmon, J. Welch, K. Glassey, Ed. J. Wiley &
Sons, Inc, 1997;
-
"Oracle data warehousing", M. Corey e M. Abbey, Osborne McGraw Hill, 1997;
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Apontamentos do docente;
• Livros sobre DW:
3
Henrique Madeira, DEI-FCTUC, 2001
Características genéricas das Data
Warehouses
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
4
2
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
O que é uma data warehouse?
• Base de dados de grande dimensão que armazena dados
para apoio à decisão estratégica.
• São construídas a partir de bases de dados operacionais e
de outros sistemas usados numa organização.
BD operacionais
e outros sistemas
Data Warehouse
Henrique Madeira, DEI-FCTUC, 2001
Utilizadores
Utilizadores
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
5
Volume de dados
• Até 20 Gbytes
Pequena dimensão; corre num bom PC
• De 20 a 100 Gbytes
Média dimensão; precisa de workstation poderosa;
Henrique Madeira, DEI-FCTUC, 2001
• De 100 Gbytes a 1 TBytes
Grande dimensão; servidores poderosos, normalmente com
processamento paralelo
• Superior a 1 TBytes
Enorme dimensão; necessita processamento maciçamente paralelo.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
6
3
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Algumas características de DW
• Dependência temporal;
• Não volatilidade;
• Orientadas para fins específicos;
Henrique Madeira, DEI-FCTUC, 2001
• Integração e consistência informação;
• Estrutura de dados optimizada para a consulta.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
7
Dependência temporal
• Os dados na DW foram recolhidos ao longo do
tempo (não são instantâneos);
Henrique Madeira, DEI-FCTUC, 2001
• É preciso adicionar aos dados o instante temporal a
que estes se reportam.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
8
4
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Não volatilidade
• Os dados numa DW são actualizados;
• A DW armazena os dados históricos (memória
histórica) das BD operacionais de onde foi gerada;
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Depois de carregados (a partir de uma BD
operacional) a única operação é fazer queries.
9
Orientadas para fins específicos
• Devem ser guardados apenas os dados relevantes
para a tomada de decisões;
Henrique Madeira, DEI-FCTUC, 2001
• Muitos dados necessários à gestão do dia-a-dia dos
sistemas operacionais não têm relevo para a DW.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
10
5
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Integração e consistência de informação
• É necessário integrar e dar consistência aos dados
provenientes das BD operacionais antes de os
armazenar na DW.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• No ambiente operacional a mesma informação pode
residir em dados com nome e aspecto diferente;
11
Optimização das consultas
• As DW têm quantidades
enormes de dados.
Os dados devem ser
armazenados de
forma a acelerar ao
máximo as consultas
Henrique Madeira, DEI-FCTUC, 2001
• Uma vez carregados, os
dados só são alvo de
consultas;
Visão multidimensional
Desnormalização parcial
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
12
6
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Henrique Madeira, DEI-FCTUC, 2001
Visão genérica do modelo dimensional
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
13
Modelo dimensional
• Modelo usual em bases de dados operacionais: E/R
• O modelo dimensional é uma alternativa
• Fácil compreensão
• Bom desempenho nas pesquisa
• Data Warehouses construídas sobre E/R complexos
falham
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
– contém a mesma informação...
– organiza-a de forma simétrica orientada para o utilizador:
14
7
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
do
Modelo multidimensional
Hi
pe
rm
er
ca
Vendas
Continente Leiria
Continente Coimbra
Leite
Henrique Madeira, DEI-FCTUC, 2001
Produto
Farinha
Açúcar
Café
Jan Fev Mar
Abr
Data
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
15
Exemplo de esquema em estrela
Cadeia de Lojas
Tempo
ID_data
Dia
Dia_da_semana
Semana_do_ano
Mês
Trimestre
Ano
Venda
ID_data
ID_produto
ID_loja
Unid_vendidas
Custo_compra
Valor_venda
Nº_Clientes
Produto
ID_produto
Nome
Tipo
Marca
Categoria
Embalagem
Descrição
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Loja
ID_loja
Nome
Localidade
Distrito
Área
Nº_Caixas
16
8
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Modelo em estrela
O modelo dimensional típico conduz a uma estrutura em estrela,
contendo uma tabela central com os factos à qual estão ligadas as
tabelas das dimensões.
Tabela dimensão 3
ID_dimensão 1
Tabela Factos
Descrição 1
Atributo
.
.
.
ID_dimensão 1
ID_dimensão 2
ID_dimensão 3
ID_dimensão 4
Facto 1
Facto 2
.
.
.
Facto n
Tabela dimensão 2
ID_dimensão 2
Descrição 2
Atributo
.
.
.
Tópicos Avançados de Bases de Dados, 2004/2005
ID_dimensão 3
Descrição 3
Atributo
.
.
.
Tabela dimensão 1
ID_dimensão 4
Descrição 4
Atributo
.
.
.
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Tabela dimensão 1
17
Tabela de factos
• Contém as medidas do negócio
• Os factos mais uteis são
• Representam relacionamentos M:1 com as
dimensões do negócio
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
– numéricos
– aditivos
18
9
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Tabelas de dimensão
• Tabelas companheiras da tabela de factos
• Cada dimensão representa parâmetros do negócio
• Chave primária determina o dado específico
• Outros atributos específicos da dimensão
• Desnormalizada e com hieraquias.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
– tempo, clientes, produtos, etc
19
OLAP - Online Analytical Processing
• Pesquisa e apresentação de texto e dados numéricos
das Data Warehouses
– Estrutura, interfaces com o utilizador e aplicações que
permitem implementar o modelo dimensional num
motor de base de dados relacional
• MOLAP (Multidimensional OLAP)
– o mesmo sobre um motor não relacional
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• ROLAP (Relational OLAP)
20
10
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Pesquisas - baixo nível
Tempo
Cadeia de Lojas
ID_data
Dia
Dia_da_semana
Semana_do_ano
Mês
Trimestre
Ano
Venda
ID_data
ID_produto
ID_loja
Unid_vendidas
Custo_compra
Valor_venda
Nº_Clientes
Loja
ID_loja
Nome
Localidade
Distrito
Área
Nº_Caixas
ID_produto
Nome
Tipo
Marca
Categoria
Embalagem
Descrição
Select avg(Valor_venda x Unid_vendidas)
from Venda V, Tempo T, Produto P
where JOIN_TABELAS
group by P.Marca, T. Mês
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Produto
21
Interfaces com o utilizador
Exploração de dados na Data Warehouse
– Ferramenta OLAP típica
Henrique Madeira, DEI-FCTUC, 2001
• acesso a motor relacional via SQL
• apresentação em tabela, gráfico, relatório, etc
• normalmente orientado para pesquisas ad-hoc
– Outras ferramentas
• Data mining
• Modelação
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
22
11
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Browsing
• Explorar uma das dimensões definindo
restrições e escolhendo as colunas pretendidas.
restrição
valores
distintos
Nome_Loja
Localidade
Distrito
Loja Zé
Super Mário
Super Bill
Loja da Maria
Loja do Manel
John's Market
Vieiras
Loja 007
Cadeia Joel
Loja dos 500
Ansião
Aveiro
Coimbra
Leiria
Penacova
Penela
Pombal
Coimbra
Leiria
Aveiro
Area_total
Nºcaixas
250
500
750
1000
1500
2
4
6
Tópicos Avançados de Bases de Dados, 2004/2005
Henrique Madeira, DEI-FCTUC, 2001
coluna
Instituto Politécnico da Guarda
23
Browsing (exemplo)
• Quais os nomes e onde se situam as lojas do distrito
de Coimbra com área igual a 750 m2 e com 4 caixas?
coluna
Localidade
Loja do Manel
John's Market
Vieiras
Loja dos 500
Coimbra
Penacova
Distrito
Coimbra
Coimbra
Area_total
750
750
Nºcaixas
4
4
Henrique Madeira, DEI-FCTUC, 2001
restrição
valores
distintos
Nome_Loja
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
24
12
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Pesquisas - Slice and Dice
Vendas por loja e
marca
Henrique Madeira, DEI-FCTUC, 2001
Vendas por tempo
e produto
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
25
Drill-Down & Roll-Up
Drill-Down
Roll-up
Categoria mais genérica
Henrique Madeira, DEI-FCTUC, 2001
Categoria intermédia
Categoria mais detalhada
Detalhe completo
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
26
13
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Tempo: Drill-Down & Roll-Up
ALL
Drill-Down
Roll-up
Ano
Trimestre
Henrique Madeira, DEI-FCTUC, 2001
Mês
Semana
Dia
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
27
Tempo: Drill-Down (exemplo)
Select avg(Valor_venda x Unid_vendidas)
from Venda V, Tempo T, Produto P
where JOIN_TABELAS
group by P.Marca, T. Dia;
• Questão: como se representa o “ALL” na pesquisa?
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Select avg(Valor_venda x Unid_vendidas)
from Venda V, Tempo T, Produto P
where JOIN_TABELAS
group by P.Marca, T. Mês;
28
14
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Henrique Madeira, DEI-FCTUC, 2001
Arquitectura geral da Data Warehouse
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
29
Elementos básicos de uma data warehouse
Data warehouse
(presentation servers)
BDs operacionais
Utilizadores
Ad hoc
queries
ROLAP/
MOLAP
Data
Staging
Area
Folhas de cálculo,
ficheiros, ...
Relatórios
Aplicações
específicas
Modelos e
outras
ferramentas
Fontes externas
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Sistemas legados
30
15
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Data Marts
• É, normalmente, um subconjunto de uma DW;
• Muitas vezes uma Data Mart é feita para responder
rápidamente a uma área de actividade.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Numa Data Mart os dados são focalizados numa
área específica (processo de negócio);
31
Arquitectura de BDs de uma organização 1
BDs operacionais
Data Warehouse
Folhas de cálculo,
ficheiros, ...
Fontes externas
Utilizadores
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Utilizadores
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Data Mart
Sistemas legados
32
16
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Arquitectura de BDs de uma organização 2
BDs operacionais
Data Mart
Sistemas legados
Folhas de cálculo,
ficheiros, ...
Utilizadores
Fontes externas
Utilizadores
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Data Warehouse
33
Sistemas fonte
• sistema de registo de transacções
• gestão de clientes, gestão de produtos, gestão de vendas, etc…
•
•
•
•
•
disponibilidade
pesquisas típicas limitadas a fichas individuais
mantêm pouca informação histórica
A obtenção de relatórios de gestão é complicada e pesada
Pouca ligação com restantes sistemas da empresa
– registos de facturação não ligados a base de produtos ou clientes
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• principais características assumidas
34
17
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Área de processamento temporário (Staging Area)
• Área e processos que actuam sobre os dados fonte
limpeza
transformação
combinação
preparação
Staging Area
Data Warehouse
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
•
•
•
•
35
Metadados
• É necessário uma estrutura (na prática outra base de
dados) para descrever os dados da DW. Deve
descrever:
– Que dados existem na DW;
– Onde estão armazenados;
– Como se relacionam com os dados de outras bases de dados;
– Qual a proveniência dos dados e quem são os seus donos.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
– Qual o seu formato;
36
18
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Processos básicos da DW
• Extracção (a partir dos sistemas fonte)
• Transformação e limpeza de dados (na staging area)
• Carregamento e indexação
Henrique Madeira, DEI-FCTUC, 2001
• Tratamento de erros
• Pesquisa (utilização normal)
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
37
Transformação
• Limpeza dos dados
• Eliminação de campos inuteis
– campos dos sistemas opeacionais que são desnecessários na DW
• Combinação de fontes de dados
• Criação de chaves primárias da DW independentes dos
sistemas operacionais
• Criação de dimensão temporal
• Construção de agregados para melhoria de velocidade em
pesquisas
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
– coincidência exacta de chaves ou “fuzzy matches”
38
19
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Limpeza dos dados
– correcção de erros (de escrita)
– correcção de inconsistências (cidade-código postal)
– eliminação de duplicados (o mesmo nome PEDRO e
Pedro)
– tratamento de faltas de dados (campos vazios)
– pôr os dados em formatos standard
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Limpeza dos dados
39
Carregamento
Staging Area
Carregamento
Transformação
• Preenche dimensões e factos temporários com dados do
período em causa
• Realiza o carregamento BULK LOAD
– carregamento ficha-a-ficha seria demasiado lento
• Indexa os dados carregados
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
DW
40
20
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Dados operacionais
Dados da Warehouse
Objectivos operacionais
Registo histórico
Acessos de leitura/escrita
Acessos só de leitura
Acesso por transacções pré-definidas
Acesso por queries ad hoc e relatórios periódicos
Acesso a poucos registos de cada vez
Muitos registos em cada acesso
Dados actualizados em tempo real
Carregamentos periódicos de mais dados
Estrutura optimizada para actualizações
Estrutura optimizada para queries complexas
Event-driven: os processos geram dados
Data-driven: os dados geram respostas
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
BD operacionais vs Data Warehouses
41
Henrique Madeira, DEI-FCTUC, 2001
Visão genérica sobre o processo de
construção de uma Data Warehouse
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
42
21
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Infraestrutura para projecto
Construir uma DW é complexo e requer
conhecimento especializado em várias áreas
• Definir equipa;
• Definir ferramentas e sistemas;
Henrique Madeira, DEI-FCTUC, 2001
• Identificar fases do projecto;
• Definir métodos de trabalho;
• Identificar responsabilidades para cada tarefa/fase.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
43
Esquema geral do projecto de uma Data Warehouse
(R. Kimball)
Definição
dos
requisitos
do negócio
Modelação
dimensional
Selecção e
instalação de
produtos
Desenho
físico
Especificação
das aplicações
de utilizador
Desenho do
Data Staing
Colocação
em
Produção
Henrique Madeira, DEI-FCTUC, 2001
Planeamento
do projecto
Desenho da
arquitectura
Desenvolvimento
das aplicações
Gestão do projecto
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
44
22
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Passos na construção de uma DW
• Identificação de objectivos (de gestão) a atingir com a DW;
• Definir infraestrutura para o projecto;
• Identificar modelo de dados das BD operacionais fonte;
• Definir regras para o mapeamento de dados;
• Extrair, integrar, purificar e consolidar os dados;
• Ferramentas de exploração, afinação de desempenho e
avaliação de eficácia.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Definir modelo de dados para a DW;
45
Objectivos a atingir com a DW
É necessário ter um entendimento profundo do processo de
negócio que a DW vai apoiar.
• Quais são os objectivos e estratégia da empresa/instituição?
• Porque é que a informação é necessária?
• Quem vai usar essa informação (dentro da empresa)?
• Como é que a informação vai ser usada?
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Qual a informação necessária para atingir esses objectivos?
46
23
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Henrique Madeira, DEI-FCTUC, 2001
Identificar modelo de dados das BD fonte
Bases de dados
operacionais
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
47
Modelo de dados das BD fonte (cont.)
• Responder à questão: quais os dados fonte para a
DW?
• Necessário usar ferramentas de reverse-engineering;
• Alguns dados da DW podem ter outras origens que
não as BD operacionais.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Muitas vezes os modelos de dados das BD
operacionais não existem ou estão desactualizados;
48
24
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Dados históricos, de referência e sínteses
• No processo de indentificar os dados a extrair para a DW é
útil olhar os dados sob grandes grupos:
– Dados históricos (factos)
Dados correspondentes a entidades que descrevem factos (vendas,
encomendas, facturas, consultas, pagamentos, etc)
Dados correspondentes a entidades de referência que permitem completar e
situar os dados dos factos históricos (clientes, fornecedores, pessoas, etc)
– Sínteses
Dados previamente calculados e que se prevê virem a ser necessários
(relatórios de vendas mensais, movimentos semanais de stock, etc)
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
– Dados de referência (dimensões)
49
Definir modelo de dados da DW
• Desenvolver/entender o modelo de negócio da DW,
identificar processos de negócio e identificar dados
disponíveis (nas BDs operacionais);
– Identificar os factos (valores numéricos);
– Escolher a granularidade dos factos (determina a
precisão com que poderá ser feita a análise);
– Definir as dimensões de interesse.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Par cada processo de negócio:
50
25
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Modelos na construção de uma DW
Que transações?
rm
aliz
açã
o si
Modelo
dimensional
ste
má
tica
Que queries?
Modelo físico
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Des
no
Modelo do
negócio (ER)
51
Definir regras para o mapeamento de dados
• Identificar os dados a extrair;
• Definir regras e processos para integrar,
compatibilizar e “limpar” os dados;
• Documentar todas os passos para permitir que os
dados históricos possam ser entendidos
posteriormente.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Identificar os dados que faltam (impossíveis de
extrair das BD operacionais);
52
26
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Extrair, integrar, purificar e racionalizar os dados
• Usar ou construir as ferramentas que concretizam
as regras para mapeamento dos dados;
Henrique Madeira, DEI-FCTUC, 2001
• Rever regras e processos de mapeamento sempre
que são detectadas inconsistências;
• Documentar todos os passos.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
53
Exploração, afinação e avaliação de eficácia
• Definição/construção de ferramentas de exploração;
• Afinação de desempenho;
Henrique Madeira, DEI-FCTUC, 2001
• Administração da data warehouse.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
54
27
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Henrique Madeira, DEI-FCTUC, 2001
O modelo multidimensional
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
55
Modelo multidimensional
• Factos armazenados num array multidimensional;
• As dimensões são usadas para indexar o array;
do
• Normalmente construídas sobre bases de dados
relacionais.
Continente Leiria
Continente Coimbra
Henrique Madeira, DEI-FCTUC, 2001
Hi
pe
rm
er
ca
Vendas
Leite
Produto
Farinha
Açúcar
Café
Jan Fev Mar
Abr
Data
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
56
28
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Exemplo de esquema em estrela
Cadeia de Lojas
Tempo
ID_data
Dia
Dia_da_semana
Semana_do_ano
Mês
Trimestre
Ano
Venda
ID_data
ID_produto
ID_loja
Unid_vendidas
Custo_compra
Valor_venda
Nº_Clientes
Produto
ID_produto
Nome
Tipo
Marca
Categoria
Embalagem
Descrição
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Loja
ID_loja
Nome
Localidade
Distrito
Área
Nº_Caixas
57
Modelo em estrela
O modelo dimensional típico conduz a uma estrutura em
estrela, contendo uma tabela central com os factos à qual
estão ligadas as tabelas das dimensões
Tabela dimensão 3
ID_dimensão 1
Tabela Factos
Descrição 1
Atributo
.
.
.
ID_dimensão 1
ID_dimensão 2
ID_dimensão 3
ID_dimensão 4
Facto 1
Facto 2
.
.
.
Facto n
Tabela dimensão 2
ID_dimensão 2
Descrição 2
Atributo
.
.
.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
ID_dimensão 3
Descrição 3
Atributo
.
.
.
Tabela dimensão 1
ID_dimensão 4
Descrição 4
Atributo
.
.
.
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Tabela dimensão 1
58
29
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Algumas características do modelo em estrela
Tabela
dimensão 11
ID_dimensão
• Constituída por atributos numéricos (factos) e
pelas chaves forasteiras que a ligam à tabelas
de dimensões;
• A tabela de factos está bastante normalizada;
• Contém normalmente uma enorme quantidade
de registo (ocupa vulgarmente mais de 95% do
espaço da DW).
Descrição 1
Atributo
.
.
.
Tabela
dimensão 22
ID_dimensão
Tabela
Tabela Factos
ID_dimensão 1
ID_dimensão 2
ID_dimensão 3
ID_dimensão 4
Facto 1
Facto 2
.
.
.
Facto n
Descrição 2
Atributo
.
.
.
dimensão 33
ID_dimensão
Descrição 3
Atributo
.
.
.
Tabela
dimensão 14
ID_dimensão
Descrição 4
Atributo
.
.
.
Tabelas de Dimensões
• Há tantas dimensões quantas vertentes sob as quais se pretende analisar os
factos;
• As tabelas de dimensões são fortemente desnormalizadas, sendo normalmente
tabelas com muitos atributos;
• Normalmente, apesar de terem muitos atributos, contêm poucos registos
(quando comparados com a tabela de factos).
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Tabela de Factos
59
Passos para definir modelos em estrela
2 - Identificar os factos;
3 - Identificar dimensões;
4 - Escolher a ganularidade
dos dados a registar.
os
ad Ds
d
B
s
s o eis (
a
t
ív c)
is
e v ispon os, et
d
r
r
d
rde ente ichei
e
p m
,f
m
Se ctiva onais
efe eraci
op
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
1 - Identificar os processos de negócio/actividade
60
30
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Exemplo – Cadeia de supermercados
• Cadeia de supermercados de uma mesma empresa
• Vamos pensar apenas nas vendas (a aquisição de produtos
aos fornecedores é global para toda a empresa)
• Vende vários milhares de produtos
• Os produtos são identificados univocamente por códigos.
– código SKU (“Stock Keeping Units”)
– códigos de barras universais SKU = UPC
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Cada supermercado tem vários departamentos (mercearia,
higiene e limpeza, etc)
61
Dados do negócio
• Onde recolher os dados?
Caixa registadora (POS - point of sales). Na prática, os dados são
recolhidos na base de dados que gere as existências, sendo as caixas
registadoras meros terminais.
• O que interessa medir?
• Qual o objectivo?
Maximização do lucro
• máximo preço de venda possível
• mais baixos custos de aquisição e administrativos
• mais clientes
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Vendas
62
31
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Factos - Cadeia de Lojas
• Identificar os factos de Vendas
– Exemplo de factos relevantes para a gestão: Número de unidades vendidas, custo
do produto quando fornecido pelo vendedor, valor total das vendas do produto,
número de clientes que comprou o produto.
– Questão: será que é possível obter dados base (no sistema operacional) para obter
estes factos?
Dimensão Loja
Factos VENDAS
ID_data
atributos
……….
ID_data
ID_produto
ID_loja
ID_promoção
Unid_vendidas
Custo_compra
Valor_venda
Nº_clientes
Dimensão Produto
ID_produto
atributos
……...
Tópicos Avançados de Bases de Dados, 2004/2005
ID_loja
atributos
……...
Dimensão Promoção
ID_promoção
atributos
……...
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Dimensão Tempo
63
Dimensões - Cadeia de Hipermercados
• Dimensões principais
– Produto x Tempo x Loja
–
–
–
–
Fornecedores?
Promoções?
Cliente?
Nome do empregado responsável naquele dia?
• É normalmente possível adicionar dimensões extra às
dimensões principais
• Todas dimensões tomam um só valor para cada combinação
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Existirão outras dimensões de interesse?
64
32
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Dimensões - Cadeia de Hipermercados
Dimensão Loja
Factos VENDAS
ID_data
atributos
……….
ID_data
ID_produto
ID_loja
ID_promoção
Unid_vendidas
Custo_compra
Valor_venda
Nº_clientes
Dimensão Produto
ID_produto
atributos
……...
Tópicos Avançados de Bases de Dados, 2004/2005
ID_loja
atributos
……...
Dimensão Promoção
ID_promoção
atributos
……...
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Dimensão Tempo
65
Granularidade
• Exemplo: registar as vendas de todos os produtos
diariamente
• Podemos ver de forma detalhada que produtos são vendidos e em
que lojas, a que preços e em que dias, …
• Granularidade:
• A granularidade determina a dimensionalidade da
DW e tem um forte impacto no seu tamanho
• A granularidade deve ser adequada às necessidades
de análise.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
produtos x loja x promoção x dia
66
33
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Granularidade: alternativas
• Porquê SKU em vez de marca ou tipo de produto?
– Valerá a pena ter tantas unidades de um determinado tamanho
para um dado produto?
• Ao nível da factura
• assim não é possivel analisar os dados de comportamento de compras
• Semanal ou mensal
– perder-se-iam efeitos interessantes a nível diário
• variações de vendas entre 2ªs e Sábados
• efeitos de promoções de dois dias
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
– tamanho da base de dados poderia tornar-se gigantesco
– identificação do cliente não existe
67
Detalhe selectivo
• Porque é que os dados devem ser expressos com
um detalhe grande num Data Warehouse?
Henrique Madeira, DEI-FCTUC, 2001
– Não por ser necessário aceder a valores específicos
– … mas as pesquisas “cortam” dimensões selectivamente
e de forma precisa
Select …
Join …
Group By produto, mês
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
68
34
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Refinar o modelo
• Detalhar as dimensões
• Rever os factos
Henrique Madeira, DEI-FCTUC, 2001
• Verificar a consistência (entre factos, entre factos e
dimensões, etc)
• Reavaliar granularidade
Tópicos Avançados de Bases de Dados, 2004/2005
- Cadeia de lojas
Produto
ID_produto
Número
Nome
Marca
Categoria
Subcategoria
Departamento
Tam_embalagem
Tipo_embalagem
Tipo_dieta
Peso
Unidade_de_peso
Quant_caixa
Caixas_p_pallete
Larg_prateleira
Altura_prateleira
Profun_prateleira
……...
69
Tempo
ID_data
Dia_do_mês
Dia_da_semana
Dia_do_ano
Semana_do_ano
Mês
Número_do_mês
Trimestre
Período_fiscal
Flag_feriado
Flag_dia_semana
Flag_últ_dia_mês
Estação_ano
Aconteci_espec
……….
ID_produto
ID_data
ID_Loja
ID_Promoção
Unid_vendidas
Custo_compra
Valor_venda
Nº_clientes
Promoção
ID_promoção
Número
Nome_promo
Tipo_red_preço
Tipo_anúncio
Tipo_cartaz
Tipo_coupons
Meio_anúncio
Meio_cartaz
Custo_promoção
Início_promoção
Fim_promoção
……...
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Loja
ID_loja
Nome
Número_loja
Endereço
Localidade
Código_postal
Distrito
Região
Telefone
Fax
Gestor_loja
Área_total
Área_mercearias
Área_congelados
Área_bazar
Nº_Caixas
Data_inauguração
Data_ult_remod.
……...
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Exemplo 1
Instituto Politécnico da Guarda
70
35
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Exemplo 1:
Dimensão tempo
• Existe sempre, pois representa a
dependência temporal inerente à DW;
Tempo
ID_data
Dia_do_mês
Dia_da_semana
Dia_do_ano
Semana_do_ano
Mês
Número_do_mês
Trimestre
Período_fiscal
Flag_feriado
Flag_dia_semana
Flag_último_dia_mês
Estação_ano
Acontecimento_espec
……….
• Deve conter a caraterização do tempo
nos atributos pelos quais se pretende
posteriormente fazer pesquisas;
• É gerada, normalmente, de uma forma
sintética (i.e., sem ser a partir de uma
BD operacional) para todo o período de
tempo considerado na DW.
Tópicos Avançados de Bases de Dados, 2004/2005
Exemplo 1:
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Deve descrever o tempo tal como ele é
visto para fins de gestão da actividade
(negócio) em causa;
71
Dimensão produto
Produto
• Deve conter a caraterização dos produtos
tal como eles são vistos pelo gestor da
cadeia de lojas;
• Contém todos os atributos pelos quais se
pretende posteriormente fazer perguntas;
• Como acontece normalmente nas tabelas
de dimensões, é uma tabela bastante
desnormalizada.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
ID_produto
Número
Nome
Marca
Categoria
Subcategoria
Departamento
Tam_embalagem
Tipo_embalagem
Tipo_dieta
Peso
Unidade_de_peso
Quantidade_caixa
Caixas_por_pallete
Largura_prateleira
Altura_prateleira
Profud_prateleira
……...
72
36
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Exemplo 1:
Dimensão loja
Loja
ID_loja
Nome
Número_loja
Endereço
Localidade
Código_postal
Distrito
Região
Telefone
Fax
Gestor_loja
Área_total
Área_mercearias
Área_congelados
Área_bazar
Nº_Caixas
Data_inauguração
Data_ultim_remod.
……...
• Contém todos os atributos pelos quais se
pretende posteriormente fazer perguntas,
incluindo atributos de natureza geográfica
(localização) e de natureza temporal (datas
de inauguração,…).
Tópicos Avançados de Bases de Dados, 2004/2005
Exemplo 1:
ID_promoção
Número
Nome_promo
Tipo_red_preço
Tipo_anúncio
Tipo_cartaz
Tipo_coupons
Meio_anúncio
Meio_cartaz
Custo_promoção
Início_promoção
Fim_promoção
……...
73
Dimensão promoções
• Contém a caraterização das promoções
efectuadas;
• Neste exemplo há apenas uma dimensão de
promoções (para todos os tipos de
promoções), mas seria possível ter em
alternativa uma dimensão para cada tipo de
promoção;
• A dimensão promoção representa, neste
exemplo, uma dimensão muito sensível e
importante, pois as promoções são um dos
aspectos em que o gestor mais facilmente
pode actuar quando pretende incrementar
as vendas numa loja ou num determinado
produto.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Promoção
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Contém a caraterização das lojas tal como
eles são vistos pelo gestão da cadeia de
lojas;
74
37
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Cálculo simplificado do espaço ocupado
Granularidade = Produtos vendidos / em cada loja / em cada dia
Tempo = 3 anos
Nº Produtos = 100.000 (apenas 20% dos produtos são vendidos diariamente)
Lojas = 100
Tamanho médio de registo = 8 atributos x 4 Bytes = 32 Bytes
Tamanho aproximado da DW = 32 x 2.190.000.000 = 70
GBytes
• Despreza-se o espaço ocupado pelas tabelas de
dimensões;
• Não considera o armazenamento dos índices
nem vistas materializadas;
Tópicos Avançados de Bases de Dados, 2004/2005
Exemplo 2:
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Nº de registos de factos = 3 x 365 x 20.000 x 100 = 2.190.000.000
75
Existências em armazéns
Caracterização da actividade de gestão de existências
• Recepção;
• Recolha do stock;
• Inspecção;
• Embalagem;
Armazém
• Entrada no stock;
• Saída
Procedimentos excepcionais:
• Detecção de falha na inspecção
Devolução ao fornecedor
• Deteriorização no manuseamento
Perda do produto
• Devolução do cliente
Reentrada no stock ;
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Autorização de venda;
76
38
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Ex. 2:
Fotografia periódica de existências
Tempo
Existências
Armazém
ID_tempo
ID_produto
ID_armazém
Quant_existente
Valor_de_custo
Último_preço_venda
• Registo periódico das existências em stock;
• A Quantidade_existente não é aditiva na dimensão tempo
• A Quantidade_saída permite saber quantos produtos saíram
no intervalo de tempo correspondente a dois registos.
Tópicos Avançados de Bases de Dados, 2004/2005
Ex. 2:
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Quant_saída
Produto
77
Registo de transações de existências
Armazém
Tempo
Existências
ID_tempo
ID_produto
ID_armazém
ID_transacção
Quant_existente
Produto
Transacção
• Contém um registo por cada possível alteração (transacção) das
existências, constituindo a forma mais detalhada de representar a
evolução do stock;
• O conjunto de possíveis transacções é reduzido.
• Necessita de atributos como Quant_existente (típicos do modelo
“fotográfico”) para dar uma visão prática do processo.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Nº_Documento
Quantidade
78
39
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Cálculo simplificado do espaço ocupado
Fotografia periódica das
Tempo = 3 anos
Nº Produtos = 500.000
Armazéns = 4
Tamanho médio de registo = 7 atributos x 4 Bytes = 28 Bytes
Nº de registos de factos = 3 x 365 x 500.000 x 4 = 2.190.000.000
Tamanho aproximado da DW = 28 x 2.190.000.000 = 61,32
existências
GBytes
Tempo = 3 anos
Registo de transacções de existências
Nº Produtos = 500.000
Armazéns = 4
Tamanho médio de registo = 7 atributos x 4 Bytes = 28 Bytes
Nº entregas (no armazém) por ano = 10
Nº de transacções por cada entrega de produto = 50
Nº de registos de factos = 3 x 500.000 x 4 x 10 x 50 = 1.095.000.000.000
Tamanho aproximado da DW = 24 x 1.095.000.000.000 =
Tópicos Avançados de Bases de Dados, 2004/2005
84 GBytes
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Ex. 2:
79
Redução espaço ocupado: abordagem simplista
Em muitas situações poderá ser aceitável ter
informação detalhada para as existências apenas
relativa ao último mês:
– Registo diário das existências do último mês;
– Média semanal das restantes semanas do ano (48 semanas);
– Média mensal dos meses dos últimos dois anos.
Nº de “fotografias” = 30 (dias) + 48 (semanas) + 24 (meses) = 102
(em vez de 3 x 365 = 1100)
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Exemplo (método da fotografia periódica das existências)
80
40
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Cruzamento de dados entre processo de negócio
diferentes
Data Mart - marketing
Data Mart - facturação
Definições e carregamentos de dimensões e factos
de forma independente ?
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Como cruzar
os dados?
81
Mais do que uma estrela
Tempo
Vendas
ID_tempo
ID_produto
ID_armazém
Unid_vendidas
Custo_compra
Valor_venda
Nº_Clientes
Quant_existente
Quant_saída
Valor_de_custo
Últim_preço_venda
Produto
Armazém
• Uma ou mais estrelas interligam-se por uma ou mais dimensões;
• As dimensões que promovem a interligação têm de ser conformes
(conter informação consistente entre si);
• Drill across: consulta à DW que cruza mais do que uma estrela.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Loja
Existências
ID_data
ID_produto
ID_Loja
82
41
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Exemplo de múltiplas estrelas
Armazenistas distribuidores
Vendas
Encomendas
Dimensão: Tempo
Dimensão: Componente
Dimensão: Cliente
Dimensão: Contrato
Henrique Madeira, DEI-FCTUC, 2001
Dimensão: Tempo
Dimensão: Componente
Dimensão: Fornecedor
Dimensão: Contrato
Existências
Dimensão: Tempo
Dimensão: Componente
Dimensão: Armazém
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
83
Exemplo de dimensões conformes
Dimensão_1
Tabela de
Factos
Dimensão_2
Dimensão_3
Dimensão_1
Dimensão10
Tabela de
Factos
Tabela de
Factos
Dimensão_6
Dimensão_7
Dimensão_8
Têm de ser
conformes
Dimensão_9
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Dimensão_4
Dimensão_5
84
42
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Factos não aditivos (ou semi-aditivos)
• Os factos não são aditivos ao longo de uma ou mais
dimensões quando a sua soma não tem significado real (mas
a média já pode ter, pelo que o facto é útil).
• Exemplos:
• Factos que representam níveis estáticos como saldos de contas ou
existências num inventário;
• A não aditividade pode resultar de peculiariedades do modelos de
estrela (por exemplo, o facto Nº_Clientes no exemplo “Cadeia de Lojas”)
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Nem sempre é fácil para o utilizador perceber que está a
fazer adições de factos não aditivos, o que pode levar a
conclusões erradas.
85
Grandes dimensões
• Em certas situações dimensões como Produtos ou
Clientes podem ter milhões de registos;
Pode ser interessante normalizar
parcialmente estas dimensões
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• É muito frequente estas dimensões terem até uma
centena de atrubutos;
86
43
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Flocos de neve
ID_produto
Número
Nome
ID_Tam_emb
ID_marca
Peso
Unidade_de_peso
Quantidade_caixa
Caixas_por_pallete
Largura_prateleira
Altura_prateleira
Profud_prateleira
……...
ID_Tam_emb
Tipo_embalag
ID_marca
ID_marca
Categoria
ID_subcat
ID_subcat
Categoria
ID_dept
ID_Dept
Departamento
• Uma dimensão pode ter múltiplas hierarquias
(flocos de neve);
• Uma hierarquia consiste numa cadeia de
típicos relacionamentos 1 para N
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Produto
87
Vantagens e desvantagens de flocos
• Vantagens
– Economiza espaço;
– Aumenta o tempo de resposta a queries;
– Torna a construção das queries mais complexa.
Por muito grande que seja uma dimensão, ela representa
sempre uma percentagem pequena da espaço ocupado
pela tabela de factos, pelo que estruturar uma dimensão
em flocos de neve raramente se justifica
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Desvantagens
88
44
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Mini-dimensões
Vendas
ID_data
ID_demograf
ID_cliente
ID_produto
ID_promoção
Unid_vendidas
Custo_compra
Valor_venda
Demográfica
ID_demograf
Faixa_etária
Nível_rendimento
Estado_civíl
Sexo
Hábitos_consumo
• As combinações possíveis relativas a
demografia (neste exemplo) ficam numa
tabela própria.
• Permite economizar espaço e ganhar
velocidade;
Tópicos Avançados de Bases de Dados, 2004/2005
ID_cliente
Nome
Apelido
Rua
Cidade
Código_postal
ID_demograf
……...
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Clientes
89
Alterações (actualizações) em dimensões
O que fazer quando é necessário actualizar um
dado registo de uma dimensão?
Três alternativas:
2) Inserir um registo novo na dimensão com os valores
actualizados;
3) Ter atributos na dimensão que permitam registar a
evolução no tempo.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
1) Escrever por cima (perde-se a história);
90
45
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
1ª Escrever por cima
Clientes
ID_cliente
Nome
Apelido
Rua
Cidade
Código_postal
Sexo
Data_nascimento
Estado_civil
Rendimento_médio
……...
ID_data
ID_cliente
ID_produto
ID_promoção
Unid_vendidas
Custo_compra
Valor_venda
Um dado
cliente alterou
o estado_civil
Actualiza-se directamente o atributo da dimensão.
• Muito simples de tratar;
• Perde-se a história (o que é inaceitável na maior parte dos casos).
Tópicos Avançados de Bases de Dados, 2004/2005
91
Novo registo na tabela da dimensão
Clientes
Vendas
ID_cliente
Nome
Apelido
Rua
Cidade
Código_postal
Sexo
Data_nascimento
Estado_civil
Rendimento_médio
……...
ID_data
ID_cliente
ID_produto
ID_promoção
Unid_vendidas
Custo_compra
Valor_venda
Um dado
cliente alterou
o estado_civil
Insere-se um novo registo na dimensão igual ao registo já
existente desse cliente mas com o novo estado civíl.
• Mantém toda a informação histórica;
• Complexo;
• Necessita de chaves com estrutura (parte da chave é usada para
identificar os registos que correspondem a alterações do cliente).
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
2ª
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Vendas
92
46
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Chaves com estrutura
Cliente
ID_Cliente
Nome
Apelido
145023-000
Ana Maria
…
Silva
Estado_civil ….
…
Solteira
………………………………………………………………………..
Ana Maria
Casada
Silva
Reserva-se três dígitos, por exemplo, para identificar sucessivas
alterações de um mesmo cliente.
Alternativamente, a chave pode ser composta por dois atributos:
um que identifica o cliente outro que identifica a alteração.
Tópicos Avançados de Bases de Dados, 2004/2005
93
Atributos para registar alterações
Clientes
Vendas
ID_cliente
Nome
Apelido
Rua
Cidade
Código_postal
Sexo
Data_nascimento
ID_data
ID_cliente
ID_produto
ID_promoção
Unid_vendidas
Custo_compra
Valor_venda
Um dado
cliente alterou
o estado_civil
Estado_civil_original
Estado_civil_actual
……...
Para cada atributo que pode variar no tempo passa-se a ter um
conjunto de atributos que regista alguns passos (de alterações).
• Simples;
• Mantém apenas parcialmente a informação histórica.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
3ª
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
145023-001
94
47
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Exemplo 3: DW de um Banco
Vender melhor os seus produtos e oferecer
serviços adicionais aos clientes que já
possuem conta(s).
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Objectivo
95
Exemplo 3: requisitos específicos:
• Pretende-se ver os dados históricos relativos aos últimos 5
anos.
– Para todos os meses anteriores ao actual é suficiente saber o saldo final do mês;
– Para o mês corrente apenas interessa a fotografia do dia anterior. Não são
necessários os outros dias;
– um saldo_primário;
– uma lista de atributos diferentes e factos numéricos também diferentes
conforme o tipo de conta;
• Cada conta pertence a um cliente;
• Os nomes dos clientes podem diferir de conta para conta;
• Além da identificação do cliente estamos interessados em
informação demográfica.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Cada tipo de conta tem
96
48
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Exemplo 3: DW de um banco: possíveis dimensões
• Dimensões:
Conta
Cliente
Agência
Produto
Estado
Tempo
Henrique Madeira, DEI-FCTUC, 2001
–
–
–
–
–
–
• Granularidade:
– Conta por mês.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
97
Exemplo 3: dimensões CONTA e CLIENTE
• Porquê dimensões diferentes CONTA e CLIENTE?
– Devido ao tamanho da dimensão conta e à sua
volatilidade;
– podemos ter num grande banco :
Henrique Madeira, DEI-FCTUC, 2001
• 10 milhões de contas e 3 milhões de clientes
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
98
49
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Exemplo 3: dimensões PRODUTO e ESTADO
• Dimensão PRODUTO
• Dimensão ESTADO
– útil para gravar o estado da conta. Conta activa ou
inactiva.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
– contêm os atributos usados para descrever todos os
produtos do banco;
– hierarquia: nome_do_produto → tipo → categoria;
99
Exemplo 3: esquema da DW de um banco
Conta
ID_conta
Titular
Seg_titular
Endereço
Localidade
Data_abertura
D_nasc_titular
Sexo_titular
Est_civil_titular
Agência
ID_conta
ID_cliente
ID_tempo
ID_agência
ID_produto
ID_estado
Saldo_primário
Nºtransacções
ID_agência
Nome
Endereço
Localidade
Tipo_agência
Estado
Tempo
ID_estado
Descrição
Motivo
Flag_conta_nova
Flag_conta_fechada
ID_tempo
Mês
Ano
Trim_fiscal
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
ID_produto
Descrição
Tipo
Categoria
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Produto
Cliente
ID_cliente
Nome_cliente
Endereço
Localidade
Rendimento
Tipo_cliente
Factos_banco
100
50
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Exemplo 3: dimensões “sujas”
Conta
Factos_banco
ID_conta
Titular
Seg_titular
Endereço
Localidade
Data_abertura
D_nasc_titular
Sexo_titular
Est_civil_titular
ID_cliente
Nome_cliente
Endereço
Localidade
Rendimento
Tipo_ cliente
Como a ênfase da actividade bancária começou por ser
centrada nas contas (e não nos clientes) não é fácil
estabelecer uma lista de clientes “limpa” a partir das
contas.
A constituição da dimensão Cliente contem seguramente duplicações e
diferentes nomes da mesma pessoa que seriam assumidos como clientes
diferentes. É uma dimensão “suja”. Na maior parte dos bancos e
companhias de seguros esta dimensão tem uma precisão de 80%.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Cliente
ID_conta
ID_cliente
ID_tempo
ID_agência
ID_produto
ID_estado
Saldo_primário
Nºtransacções
101
Exemplo 3: produtos heterogéneos
Factos_banco
Produto
ID_produto
Descrição
Tipo
Categoria
(atributos contas à ordem)
(atributos depósitos a prazo)
(atributos planos poupança)
(atributos cartões de crédito)
………………………..
Este tipo de situações leva a que as tabelas de factos e da dimensão
produtos tenham muitos valores nulos para cada registo.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
ID_conta
ID_hipoteca
ID_tempo
ID_agência
ID_produto
ID_estado
Saldo_primário
Nºtransacções
(factos contas à ordem)
(factos depósitos a prazo)
(factos planos poupança)
(factos cartões de crédito)
……………………...
102
51
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Solução para a heterogenidade numa dimensão
Produto_contas_ordem
ID_conta
ID_hipoteca
ID_tempo
ID_agência
ID_produto
ID_estado
Saldo_primário
Nºtransacções
Factos_Contas_ordem
Produto
ID_produto
Descrição
Tipo
Categoria
Factos e dimensão nucleares
referentes a todos os produtos
ID_conta
ID_hipoteca
ID_tempo
ID_agência
ID_produto_ordem
ID_estado
Saldo_primário
Nºtransacções
(factos contas à ordem)
Produto_contas_prazo
Factos_Contas_Prazo
ID_conta
ID_hipoteca
ID_tempo
ID_agência
ID_produto_prazo
ID_estado
Saldo_primário
Nºtransacções
(factos contas a prazo)
Factos e dimensões
referentes a produtos
específicos
ID_produto_ordem
Descrição
Tipo
Categoria
(atributos contas à ordem)
Tópicos Avançados de Bases de Dados, 2004/2005
ID_produto_prazo
Descrição
Tipo
Categoria
(atributos contas a prazo)
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Factos_banco
103
Tabelas de factos sem factos
Professor
Disciplina
Factos_escola
ID_disciplina
ID_aluno
ID_tempo
ID_professor
ID_sala
Aluno
A tabela de factos reduz-se ao cruzamento das chaves;
A presença de um aluno numa aula de um professor,
numa dada sala e num determinado dia fica assinalada
pelo conjunto das chaves
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Salas
Tempo
104
52
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Tabelas de factos sem factos: exemplo
Doente
Hospital
Factos_hospital
Médico
Diagnóstico
Procedimento
Tempo
E muitas outras situações onde é necessário registar apenas ocorrências
ou eventos.
Se houver necessidade de registar outros factos então a tabela de factos
já conterá atributos numéricos descrevendo esses factos.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
ID_hospital
ID_médico
ID_diagnóstico
ID_doente
ID_procedimento
ID_tempo
105
Representar eventos que não aconteceram
Loja
ID_data
Dia
Dia_da_semana
Semana_do_ano
Mês
Trimestre
Ano
…...
Factos_vendas
ID_data
ID_produto
ID_loja
ID_promo
Unid_vendidas
Custo_compra
Valor_venda
Nº_Clientes
Produto
ID_produto
Nome
Tipo
Marca
Categoria
Embalagem
Descrição
……
ID_loja
Nome
Localidade
Distrito
Área
Nº_Caixas
…...
Promoções
ID_promo
Número
Tipo_promo
Data_início
Data_fim
…...
• Quais os artigos em promoção que não de venderam?
• Como tratar o facto de as promoções poderem ser diferentes de loja para loja?
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Tempo
106
53
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Tabelas de cobertura
Indicam, para o caso do exemplo das lojas, que artigos estão em
promoção, qual a promoção, em que lojas e durante quanto tempo.
Tempo
Loja
ID_data
ID_produto
ID_loja
ID_promo
Produto
Promoções
As tabelas de coberturas registam eventos: não têm factos
A granularidade do tempo poderá, eventualmente, ser semanal
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Promoções
107
Cadeia de lojas, com cobertura de promoções
Factos_vendas
ID_data
ID_produto
ID_loja
ID_promo
Unid_vendidas
Custo_compra
Valor_venda
Nº_Clientes
ID_data
Dia
Dia_da_semana
Semana_do_ano
Mês
Trimestre
Ano
…...
Produto
Loja
ID_loja
Nome
Localidade
Distrito
Área
Nº_Caixas
…...
Promoções
Promoções
ID_produto
Nome
Tipo
Marca
Categoria
Embalagem
Descrição
……
ID_data
ID_produto
ID_loja
ID_promo
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
ID_promo
Número
Tipo_promo
Data_início
Data_fim
…...
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Tempo
108
54
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Entrevistar utilizadores
• Qual é o papel do seu grupo/ departamento/ divisão ?
• Qual foi a alteração mais significativa e recente na forma como estão
a fazer o vosso negócio ?
– O que significa “foco no cliente”?
• O que é que os vossos competidores fazem que vocês não fazem? E
querem também fazer?
• Como é que medem o sucesso no vosso grupo ? Lucro, volume
vendas, …
• O vosso grupo precisa da informação das ordens de compra,
inventários, vendas ?
• Precisam de ver os dados ao nível do dia ?
...
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
– Quantos clientes é que têm ? Como é que os agrupam ? …
109
Entrevistar os administradores da BD
• O DBA tem que levar relatórios que descrevam a BD;
• Como é que os vários sistemas de produção se relacionam uns com
os outros ?
– Que sistema alimenta o outro ?
• Por favor, descreva por escrito cada uma das tabelas mais
importantes da BD;
– Fazer o mesmo para cada um dos campos mais importantes dessas tabelas;
– Forneça o número de registos de cada uma das tabelas.
• Como é que são administradas as chaves das tabelas? Como é que
são atribuídos os números de cliente ?
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
– Onde é que os dados começam a ser produzidos ?
110
55
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Nove decisões na construção de uma DW
Identificar processos de negócio
Identificar factos de cada processo (tabela de factos)
Definir a granularidade de cada tabela de factos
Identificar as dimensões para cada tabela de factos
Definir os atributos das dimensões
Estimar tamanho e confirmar granularidade
Decidir como resolver as alterações nas dimensões
Tratar “iregularidades” das dimensões:
•
•
•
•
•
Dimensões heterogéneas
Mini-dimensões
Normalização e flocos-de-neve
Relacionamentos M para N
Etc
9. Decidir qual a duração histórica da DW
10. A periodicidade em que os dados das diferentes estrelas devem ser
carregados.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
1.
2.
3.
4.
5.
6.
7.
8.
111
Henrique Madeira, DEI-FCTUC, 2001
Aspectos básicos relativos ao
desempenho
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
112
56
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Como acelerar as respostas a queries?
• Bom projecto lógico dos esquemas em estrela
– Infraestrutura HW + SO + SGDB correctamente administrada e afinada.
– Cuidados particulares com os sistemas de discos (vários discos,
particionamento, RAID).
– Parâmetros físicos correctos para as tabelas (e todos os outros objectos).
– Particionar tabelas muito grandes
– Correcta indexação (B*Tree e Bit-map) com parametros físicos
correctamente definidos.
– Manutenção das estatísticas do SGBD para permitir optimização das
queries.
• Usar agregados (vistas materializadas)
• Usar processamento paralelo
• Redução de dados
Alguns destes assuntos já foram
aprendidos na disciplina de BD2
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Bom projecto físico (as coisas básicas)
113
Agregados
Resultados pré-calculados que são armazenados
com o objectivo de acelerar a resposta a queries
• Totais relativos a categoria de produto, por loja e por dia;
• Totais mensais por produto e por loja;
• Razão entre a margem de lucro nos dias de semana e nos fins de
semana para cada loja.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Exemplos (considerando a DW de cadeia de lojas):
114
57
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Agregados: vantagens e problemas
• Vantagens:
– Os agregados (também conhecidos por vistas materializadas)
permitem optimizar o desempenho de uma Data Warehause;
– São controlados pelo administrador, o que permite assegurar a sua
correcção;
– São partilhados e estão disponíveis para diferentes utilizadores.
– Só aceleram as respostas para as perguntas previstas (i.e.,
previamente calculadas e armazenadas);
– Obrigam a atenção constante do administrador para construir novos
agregados que respondam às perguntas mais frequentes feitas pelos
utilizadores em cada momento e eliminar agregados que se tornaram
desnecessários;
– Ocupam espaço em disco.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Problemas fundamentais:
115
Armazenamento de agregados
• Um agregado é um registo (de factos) representando uma
sumarização (um cálculo) de um conjunto de factos base;
• Um agregado está sempre associado com uma ou mais
dimensões agregadas.
Totais relativos a categoria de produto, por loja e por dia;
Factos
(cálculo de totais
por categoria)
Dimensão agregada
(substitui a dimensão produto)
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Dimensões
originais
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Exemplo:
116
58
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Exemplo de agregados: cadeia de lojas
ID_produto
Número
Nome
Marca
Categoria
Subcategoria
Departamento
Tam_embalagem
Tipo_embalagem
Tipo_dieta
Peso
Unidade_de_peso
Quant_caixa
Caixas_p_pallete
Larg_prateleira
Altura_prateleira
Profun_prateleira
……...
Tempo
ID_data
Dia_do_mês
Dia_da_semana
Dia_do_ano
Semana_do_ano
Mês
Número_do_mês
Trimestre
Período_fiscal
Flag_feriado
Flag_dia_semana
Flag_últ_dia_mês
Estação_ano
Aconteci_espec
……….
ID_data
ID_produto
ID_Loja
ID_Promoção
Unid_vendidas
Custo_compra
Valor_venda
Nº_clientes
Loja
ID_loja
Nome
Número_loja
Endereço
Localidade
Código_postal
Distrito
Região
Telefone
Fax
Gestor_loja
Área_total
Área_mercearias
Área_congelados
Área_bazar
Nº_Caixas
Data_inauguração
Data_ult_remod.
……...
Promoção
ID_promoção
Número
Nome_promo
Tipo_red_preço
Tipo_anúncio
Tipo_cartaz
Tipo_coupons
Meio_anúncio
Meio_cartaz
Custo_promoção
Início_promoção
Fim_promoção
……...
Objectivo: criar agregados por:
• Totais por categoria (dimensão produtos);
• Totais por distritos (dimensão loja);
• Totais mensais (dimensão tempo)
Quantas tabelas de factos agregados são necessárias?
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Produto
117
Agregados na cadeia de lojas: tabelas de factos
São necessárias tantas tabelas de factos agregados quantas
combinações de dimensões agregadas
1 - Totais por categoria, por loja, por dia
2 - Totais por distrito, por produto, por dia
4 - Totais por categoria, por totais de distrito, por dia
5 - Totais por categoria, por totais mensais, por loja
6 - Totais por distrito, por totais mensais, por produto
7 - Totais por categoria, por totais mensais, por totais por distrito
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
3 - Totais por mês, por produto, por loja
118
59
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Aspecto final: factos e agregados
• Estrela base: tabela de factos + 4 dimensões
• 3 dimensões agregadas
• 7 tabelas de factos agregados
–
–
–
–
–
–
–
Totais por categoria, por loja, por dia
Totais por distrito, por produto, por dia
Totais por mês, por produto, por loja
Totais por categoria, por totais de distrito, por dia
Totais por categoria, por totais mensais, por loja
Totais por distrito, por totais mensais, por produto
Totais por categoria, por totais mensais, por distrito
Tópicos Avançados de Bases de Dados, 2004/2005
Estas tabelas não
são visíveis para o
utilizador final.
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
– Categoria;
– Distritos;
– Meses.
119
Nº de tabelas de agregados: outro exemplo
• Objectivo - criar agregados por:
– Produto: totais por categoria, totais por todos os produtos
– Loja: totais por distritos, totais por divisão, totais por todas as lojas
– Tempo: totais mensais, totais anuais
Henrique Madeira, DEI-FCTUC, 2001
• Quantas dimensões agregadas?
• 7 Dimensões agregadas.
• Quantas tabelas de factos agregados?
• 35 tabelas de factos agregadas.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
120
60
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Quantos agregados são necessários?
• O administrador decide criar ou eliminar agregados, de
acordo com as queries mais frequentes;
• Na definição das dimensões agregadas pode decidir-se
evitar a criação de certos agregados
Dimensão Tempo
Dimensão Loja
Dimensão Promoções
ID_data
ID_categoria
ID_Loja
ID_Promoção
Unid_vendidas
Custo_compra
Valor_venda
Nº_clientes
Dimensão categoria
(agregada)
ID_categoria
Categoria
Departamento
Em vez de criar um agregado
separado para departamento é
usado o agregado por categorias
para acelerar a resposta
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Tabela de factos
de agregados por
categoria
121
Técnicas para armazenamento de agregados
2) Atributos de nível: os agregados são armazenados
na tabela de factos base, com a introdução de
atributos de nível nas dimensões.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
1) Armazenados em novas tabelas de factos e
dimensões agregadas (o método usado normalmente)
122
61
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Método dos atributos de nível
• Os agregados são armazenados na mesma tabela dos
factos base;
• O atributo de nível indica o nível de agregação de cada
registo na tabela da dimensão:
– Os registos originais têm Nível = Base;
– Os agregados por categoria têm Nível = Categoria;
– etc
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• As dimensões a agregar são aumentadas para conter
um atributo de nível:
123
Exemplo: método dos atributos de nível
ID_data
ID_produto
ID_Loja
ID_Promoção
Unid_vendidas
Custo_compra
Valor_venda
Nº_clientes
Registo originais:
• Nivel = ‘Base’
• Todos os atributos preenchidos com valores originais
Registos correspondentes ao agregado por Categoria
• Nivel = ‘Categoria’
Dimensão produto
aumentada
ID_produto
Nivel
Número
Nome
Marca
Categoria
Subcategoria
Departamento
Tam_embalagem
Tipo_embalagem
Tipo_dieta
Peso
Unidade_de_peso
Quant_caixa
Caixas_p_pallete
Larg_prateleira
Altura_prateleira
Profun_prateleira
……...
• Todos os atributos preenchidos com “Não Disponível”
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Factos de vendas
124
62
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Contagem dupla
ID_data
ID_produto
ID_Loja
ID_Promoção
Unid_vendidas
Custo_compra
Valor_venda
Nº_clientes
Contagem dupla:
Se uma query restringir apenas Categoria = ‘Bebida’ serão
incluído os registos base e os registos da Nível = ‘Categoria’
para o caso em que Categoria = ‘Bebida’
As queries têm de restringir sempre o
atributo Nível
Tópicos Avançados de Bases de Dados, 2004/2005
Dimensão produto
aumentada
ID_produto
Nivel
Número
Nome
Marca
Categoria
Subcategoria
Departamento
Tam_embalagem
Tipo_embalagem
Tipo_dieta
Peso
Unidade_de_peso
Quant_caixa
Caixas_p_pallete
Larg_prateleira
Altura_prateleira
Profun_prateleira
……...
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Factos de vendas
125
Comparação dos dois métodos
• O número de registo criados pelos agregados é o mesmo em
qualquer dos métodos;
•
Tabelas separadas:
• Atributos de nível
– Poder conduzir a contagens duplas;
– Para os registos correspondentes aos agregados todos os restantes atributos das
dimensões estão preenchidos com “não disponível”;
– Difícil de gerir quando há muitos níveis.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
– As tabelas correspondentes ao agregados não são visíveis para o utilizador
final. Normalmente existe uma camada de software (agregate navigator) que
optimiza a utilização dos agregados para cada query;
– Agregados em tabelas separadas podem ser facilmente criados, apagados,
carregados e indexados;
126
63
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Agregados e esparsidão
• Os agredados são muito menos esparsos do que os dados
base;
• O administrador deve gerir cuidadosamente os agregados.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• O espaço necessário para o seu armazenamento é um
problema sério;
127
Vistas materializadas (Oracle)
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Henrique Madeira, DEI-FCTUC, 2001
CREATE MATERIALIZED VIEW sales_summary
BUILD IMMEDIATE
REFRESH
COMPLETE
ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT i.ord_ord_id AS order_id,
MAX(TO_CHAR(orderdate,'MonthYYYY')) AS orderdate,
SUM(i.quantity * p.unitprice) AS total
FROM orders o, items i, parts p
WHERE o.ord_id = i.ord_ord_id
AND p.part_id = i.part_part_id
GROUP BY i.ord_ord_id;
Instituto Politécnico da Guarda
128
64
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Vistas materializadas (cont.) (Oracle)
• Refrescar as estatísticas do optimizador
BEGIN
dbms_utility.analyze_schema('SALES_APP','ESTIMATE',15);
END;
• Refrescar as vistas materializadas
dbms_mview.refresh('SALES_APP.SALES_SUMMARY', 'A');
END;
Ver artigo “Using Materialized Views to Speed Up Queries”
by Steve Bobrowski em www.oramag.com, September/October 1999.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
BEGIN
129
Henrique Madeira, DEI-FCTUC, 2001
Índices em Data Warehousing
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
130
65
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Índices em DW
• Índices B-Tree (já estudados)
Atributos de elevada cardinalidade
• Índices Bit-map
Henrique Madeira, DEI-FCTUC, 2001
Atributos de baixa cardinalidade
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
131
Estrutura de um índices B-Tree: breve revisão
Martins
Justino
Matos
Álvares
ROWID
Bento
ROWID
Justino
ROWID
Antunes
ROWID
Canelas
Ferreira
ROWID
Lemos
ROWID
Martins
ROWID
Tavares
Matos
Soares
ROWID
ROWID
Tavares
Teixeira
ROWID
ROWID
ROWID
Os índices são armazenados como B*-trees
B*-Tree é uma B+-Tree com uma política de ocupação média de blocos diferente de 50%.
Rever as seguintes estruturas de dados: árvores equlibradas AVL, B-Trees e B+-Trees
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Bento
Ventura
132
66
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Como funciona um índice B-Tree
Martins
Álvares
Antunes
ROWID
ROWID
Bento
Canelas
Ferreira
Justino
ROWID
ROWID
Matos
Justino
Lemos
Martins
ROWID
ROWID
ROWID
Tavares
Matos
Soares
ROWID
ROWID
Tavares
Teixeira
ROWID
ROWID
ROWID
SELECT *
FROM Empregados
Where nome = ‘Lemos’;
O sistema começa por usar o índice para
determinar o ROWID do registo pretendido
Depois usa o ROWID para encontrar o
registo
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Bento
133
Porque é que os índices B-Tree são insuficientes?
Produto
ID_produto
ID_data
ID_Loja
ID_Promoção
Unid_vendidas
Custo_compra
Valor_venda
Nº_clientes
Tempo
ID_data
Dia_do_mês
Dia_da_semana
Dia_do_ano
Semana_do_ano
Mês
Número_do_mês
Trimestre
Período_fiscal
Flag_feriado
Flag_dia_semana
Flag_últ_dia_mês
Estação_ano
Aconteci_espec
……….
Promoção
ID_promoção
Número
Nome_promo
Tipo_red_preço
Tipo_anúncio
Tipo_cartaz
Tipo_coupons
Meio_anúncio
Meio_cartaz
Custo_promoção
Início_promoção
Fim_promoção
……...
Loja
ID_loja
Nome
Número_loja
Endereço
Localidade
Código_postal
Distrito
Região
Telefone
Gestor_loja
Área_total
Área_mercearias
Área_congelados
Área_bazar
Nº_Caixas
Data_inauguração
Data_ult_remod.
……...
Atenção às cardinalidades!!!
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
ID_produto
Número
Nome
Marca
Categoria
Subcategoria
Departamento
Tipo_embalagem
Tipo_dieta
Peso
Unidade_de_peso
Quant_caixa
Caixas_p_pallete
Larg_prateleira
Altura_prateleira
Profun_pratelei
……...
134
67
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Índices Bit-map
Cliente
Sexo
Distrito
145023
M
Coimbra
Casado
A
145025
M
Faro
Solteiro
B
154265
F
Guarda
Casado
A
265453
M
Faro
Solteiro
C
645654
F
Beja
Solteiro
A
Índice B-tree
Estado_civil Rendimento
Possíveis colunas para índices Bit-map
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
ID_Cliente
135
Exemplos de índices Bit-map
Índices para Sexo e para Rendimento. Outros atributos da
tabela Cliente tais como Distrito e Estado_cívil também
poderiam ter índices bit-map.
1
0
1
0
0
1
0
0
1
0
0
1
1
0
0
1
0
0
0
1
0
1
1
0
0
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Sexo = ‘M’ Sexo = ‘F’ Rendimento = ‘A’ Rendimento = ‘B’ Rendimento = ‘C’
136
68
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Execução de queries usando índices Bit-map
Sexo = ‘M’
Rendimento = ‘A’
Rendimento = ‘B’
1
1
0
1
0
1
AND
1
OR
0
1
0
0
0
1
0
Henrique Madeira, DEI-FCTUC, 2001
0
SELECT COUNT(*)
FROM Cliente
WHERE Sexo = ‘M' AND Rendimento IN (‘A',‘B');
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
137
Como indexar um esquema em estrela?
Tempo
ID_data
Dia_do_mês
Dia_da_semana
Dia_do_ano
Semana_do_ano
Mês
Número_do_mês
Trimestre
Período_fiscal
Flag_feriado
Flag_dia_semana
Flag_últ_dia_mês
Estação_ano
Aconteci_espec
……….
ID_produto
ID_data
ID_Loja
ID_Promoção
Unid_vendidas
Custo_compra
Valor_venda
Nº_clientes
Promoção
ID_promoção
Número
Nome_promo
Tipo_red_preço
Tipo_anúncio
Tipo_cartaz
Tipo_coupons
Meio_anúncio
Meio_cartaz
Custo_promoção
Início_promoção
Fim_promoção
……...
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Loja
ID_loja
Nome
Número_loja
Endereço
Localidade
Código_postal
Distrito
Região
Telefone
Fax
Gestor_loja
Área_total
Área_mercearias
Área_congelados
Área_bazar
Nº_Caixas
Data_inauguração
Data_ult_remod.
……...
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Produto
ID_produto
Número
Nome
Marca
Categoria
Subcategoria
Departamento
Tam_embalagem
Tipo_embalagem
Tipo_dieta
Peso
Unidade_de_peso
Quant_caixa
Caixas_p_pallete
Larg_prateleira
Altura_prateleira
Profun_prateleira
……...
138
69
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Henrique Madeira, DEI-FCTUC, 2001
Planos de execução de queries e
métodos de acesso específicos para
Data Warehousing
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
139
Execução de queries
Query
Planos de execução de queries
Parser
Query parsed
Optimizador
Plano de
execução
Execução
Resultados
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Sequência de passos físicos necessários
para executar uma querie, incluindo
encontrar fisicamente os dados
necessários e prepará-los de modo a
poder devolver os resultados ao
utilizador.
140
70
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Optimizador de queries
– Aspectos específicos da sintaxe da querie
– Tabelas que têm de ser acedidas e suas características físicas
– Existência de estruturas auxiliares tais como índices e vistas
materializadas
– Modo seleccionado para o optimizador (baseado em regras ou custos)
– Estado das caches
– Sugestões explicitas do utilizador (hints)
– Etc, etc
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Para definir os planos o optimizador considera muitas coisas:
141
Planos de execução de queries
Duas grandes abordagens para definir planos
- Baseado em regras
Actualmente os planos baseados em estimativas de custos
são os mais usados.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
- Baseado em estimativas de custos a partir de estatísticas
142
71
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Planos baseados em regras
O optimizador de queries define o plano baseado em:
- Conjunto prédefinido de regras de precedência (regras de ouro)
- Estas regras são:
- Fixas
- Predeterminadas
- Não dependem de aspectos relativos aos dados tais como volumes das
tabelas, distribuição dos índices, etc
- As regras indicam ao optimizador que tipo de acesso a uma dada
tabela deve fazer, como deve executar um join, se deve usar um
índice ou não, etc
- O Oracle tem um conjunto primário de cerca de 20 regras.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
- Estão ordenadas (da melhor para a menos boa)
143
Planos baseados em custos
O optimizador faz o seguinte:
1. Gera vários planos alternativos.
3. Compara os custos de cada plano possível e escolhe o
que tem menor custo.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
2. Estima os custos para cada plano baseados nos
recursos necessários para executar o plano (I/O, CPU,
memória, ...).
144
72
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Planos baseados em custos (diagrama)
Query
Parser
Query parsed
Optimizador
Estimador
de custos
Gestor de
catálogo
Henrique Madeira, DEI-FCTUC, 2001
Gerador
de planos
Avaliador de planos
Plano de execução
Execução
Resultados
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
145
Ojectivos e meios dos planos baseados em custos
• Objectivos:
• Meios
• Estatísticas sobre os objectos e os dados (clusters, tabelas,
índices,..). O comando ANALIZE é o principal método para
recolher as estatísticas.
• Sugestões do utilizador (hints)
• ...
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Normalmente os custos são calculados para optimização da
execução do maior número de queries por unidade de tempo
(throughput)
• Pode-se também establecer como objectivo a minimização do
tempo de resposta (importante para DW) ou da utilização dos
recursos.
146
73
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Planos baseados em custos e DW
• Não há actualizações, pelo que a manutenção das
estatísticas é pequena.
• Queries muito complexas, pelo que a análise de
custos permite grandes optimizações.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• A actualização das estatísticas pode ser feita a
seguir aos carregamentos periódicos.
147
Métodos de acesso aos dados
• Todos os dados estão em tabelas.
• Os planos de execução de queries usam muitos métodos de acesso
aos dados que é necessário entender (alguns são novos para DW):
– Full table scan
– Por ROWID (através de índices B*-Tree ou bit-map)
– Join indexes
– Hash indexes
– Diferentes tipos de junções
– Etc.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Não é difícil encontrar os dados numa base de dados; o problema é
fazê-lo da maneira mais eficaz.
148
74
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Métodos de junções para DW
• Star join
• Star join em Oracle
Henrique Madeira, DEI-FCTUC, 2001
• Oracle star transformation
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
149
Star Join
• Tem de existir um bit-map
join index (semelhante a um
bit-map) entre a tabela de
factos e cada uma das
dimensões.
Dim 1
Dim 2
1
2
3
• A query é executada
começando pelas dimensões
e encontrando as suas
entradas nos join indexes.
ji
Conjunto temporário
de chaves para factos
factos
PKs
8
5
factos
factos
PKs
• São processados todos os
bit-map join indexes para
encontrar as linhas da tabela
de factos que são necessárias
(e só essas).
4
ji
ji
7
Dim 4
factos
PKs
6
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Dim 3
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
ji
150
75
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Star Join em Oracle (Oracle star query)
• Pode ser muito menos
eficiente do que o star join
original porque os produtos
cartesinos podem conter
muitas linhas.
Dim 1
Dim 2
1
2
linhas
de Dim 1
linhas
de Dim 2
3
producto
cartesiano
8
factos
Inclui conjunto
temporário de chaves
para factos
5
producto
cartesiano
linhas
de Dim 3
7
linhas
de Dim 4
Dim 4
4
producto
cartesiano
6
Tópicos Avançados de Bases de Dados, 2004/2005
Dim 3
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• O Oracle não tem join index
pelo que o método é realizado
fazendo o produto cartesiano
entre as linhas seleccionas de
cada dimensão.
151
Oracle star transformation
• Destina-se a resolver os casos em que no Oracle
star query os produto cartesianos dos registos
selecionados nas dimensões são muito grandes.
• Estes índices são combinados de modo a encontrar
os registos pretendidos na tabela de factos (o que é
muito semelhante ao star join original).
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Requer um índice bit-map em cada uma das
colunas de chave estrangeira na tabela de factos.
152
76
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Oracle start transformation: execução de queries
Este método obriga à reescrita das queries. Por exemplo:
f.*
factos f,
f.fk1
=
f.fk2
=
f.fk3
=
f.fk4
=
d1.atr1 =
d2.atr2 =
d3.atr3 =
d4.atr4 =
dim1 d1, dim2 d2, dim3 d3, dim4 d4
d1.pk
/* junção
*/
d2.pk
/* junção
*/
d3.pk
/* junção
*/
d4.pk
/* junção
*/
'aaa'
/* restrição */
'ccc'
/* restrição */
'eee'
/* restrição */
'ggg';
/* restrição */
é
SELECT
FROM
WHERE
AND
AND
AND
f.*
factos f
f.fk1 IN
f.fk2 IN
f.fk3 IN
f.fk4 IN
(SELECT
(SELECT
(SELECT
(SELECT
pk
pk
pk
pk
FROM
FROM
FROM
FROM
transformada em
dim1
dim2
dim3
dim4
WHERE
WHERE
WHERE
WHERE
Tópicos Avançados de Bases de Dados, 2004/2005
atr1
atr2
atr3
atr4
=
=
=
=
'aaa')
'ccc')
'eee')
'ggg');
Henrique Madeira, DEI-FCTUC, 2001
SELECT
FROM
WHERE
AND
AND
AND
AND
AND
AND
AND
Instituto Politécnico da Guarda
153
Instituto Politécnico da Guarda
154
Henrique Madeira, DEI-FCTUC, 2001
Particionamento
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
77
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Particionamento de tabelas e índices
• Decompõe as tabelas é bocados mais pequenos chamados
partições
• Muito útil para gerir tabelas (e índices) muito grandes
• O particionamento é particularmente útil quando as
partições ficam em discos diferentes (no Oracle criando vários
tablespaces tendo cada um ficheiros em discos diferentes)
• Transparência nas partições: o SGBD decide que partições
são usadas na resposta a uma query.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Uma vez definidas, os comandos SQL podem manipular as
partições em vez da tabela inteira.
155
Particionamento de tabelas e de índices (cont.)
– Tabela particionada com índices não particionados;
– Tabela particionada com índices particionados;
– Tabela particionada com parte dos índices particionados e com
outros não particionados;
– Tabela não particionada com índices particionados (todos ou
parte).
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Em quase todos os SGBD o particionamento de tabelas e
índices pode-se combinar livremente. Exemplos:
156
78
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Vantagens e desvantages do particionamento
• Vantagens:
– Acessos mais rápidos (menos dados)
– Pode-se conter o impacto de falhas (backup e recuperação
independente para cada partição)
• Desvantagens:
– Complica ainda mais a administração;
– A sua eficácia depende muito de como são decididas as partições
e de como as queries acedem aos dados.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
– Muito melhor gestão dos discos.
157
Particionamento horizontal e vertical
– Cada partição contém parte dos registos da tabela
– A estrutura é a mesma em todas as partições (mas o projecto
físico pode ser diferente).
– Conhecido vulgarmente por range partitioning mas inclui na
verdade vários métodos de particionamento.
– É de longe o tipo de particionamento mais utilizado.
• Vertical:
– Cada partição tem parte das colunas da tabela
– A estrutura é diferente de partição para partição.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Horizontal:
158
79
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Chave de particionamento (horizontal)
• Chave de particionamento: atributo ou atributos de uma
tabela particionada que permitem associar de forma não
ambígua cada registo a uma dada partição.
• A chave de particionamento é usada nas operações de
Insert, Update, Delete e Select para encontrar a partição
para cada registo.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Cada registo numa tabela particionada tem de ser associado
a uma (e só uma) partição.
159
Métodos de particionamento horizontal
• Particionamento por gama de valores (range partitioning)
• Particionamento por lista explícita (list partitioning)
• Sub-particionamento (composite partitioning)
– Range-hash partitioning
– Range-list partitioning
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Particionamento uniforme por chave (hash partitioning)
160
80
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Particionamento por gama de valores
(range partitioning)
• As partições são criadas de acordo com gamas de valores
especificadas para um dado atributo (ou conjunto de atributos).
• Pressupõe que os valores do atributo usado para particionamento
forma um conjunto ordenado.
• Os melhores resultados (em performance) quando:
– O tamanho das partições resulta razoavelmente uniforme
– As queries coincidem com a lógica do particionamento, levando a que os
acessos sejam feitos a um pequeno conjunto de partições.
• Assume que a distribuição dos dados nos atributos usados para o
particionamento é conhecida no momento em que se cria a tabela
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Muito útil quando os dados se distribuem naturalmente em gamas de
valores (e.g., meses do ano, faixas etárias, etc).
161
Exemplo de range partitioning (Oracle)
Método de
Definição das fronteiras
que definem cada partição
A tabela é criada em quantas partições?
Em que tablespaces são criadas as partições?
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
CREATE TABLE sales_range
particionamento
(salesman_id NUMBER(5),
Atributo de
salesman_name VARCHAR2(30),
particionamento
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY RANGE (sales_date)
(
PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
);
162
81
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Exemplo de range partitioning com vários atributos
(Oracle)
de
Indicação explícita do
tablespace onde fica a partição
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
CREATE TABLE sales
Múltiplos atributos
( invoice_no NUMBER,
sale_year INT NOT NULL,
particionamento
sale_month INT NOT NULL,
sale_day INT NOT NULL )
PARTITION BY RANGE (sale_year, sale_month, sale_day)
( PARTITION sales_q1 VALUES LESS THAN (1999, 04, 01)
TABLESPACE tsa,
PARTITION sales_q2 VALUES LESS THAN (1999, 07, 01)
TABLESPACE tsb,
PARTITION sales_q3 VALUES LESS THAN (1999, 10, 01)
TABLESPACE tsc,
PARTITION sales_q4 VALUES LESS THAN (2000, 01, 01)
TABLESPACE tsd );
163
Particionamento por lista explícita
(list partitioning)
• As partições são criadas de acordo com uma lista de valores (de um
dado atributo) explicitamente especificada.
• Muito útil quando os dados não formam conjuntos ordenados nem
tem relação entre si (as partições são indicadas explicitamente).
• Os bons (ou menos bons) resultados no que toca à distribuição
uniforme dos dados pelas partições e à relação entre as partições e as
queries depende da lista de valores especificada
• Assume que se conhece previamente os valores exactos dos dados do
atributos usado para o particionamento
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Só se pode usar um atributo para definir a lista (Oracle)
164
82
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Exemplo de list partitioning (Oracle)
Partição por defeito para quando
os dados não correspondem a
nenhum dos valores especificados
Tópicos Avançados de Bases de Dados, 2004/2005
Definição explícita
do valores
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Método de
particionamento
CREATE TABLE sales_list
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
Atributo de
sales_state VARCHAR2(20),
particionamento
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois')
PARTITION sales_other VALUES(DEFAULT)
);
165
Particionamento uniforme
(hash partitioning)
• As partições são definidas através de uma função de hash,
pelo que não dependem directamente dos valores dos
atributos.
– Quando não se sabe à priori como os dados se vão distribuir (por isso é
arriscado usar particionamento por gama ou lista);
– Quando se sabe como os dados se distribuem mas é difícil gerar partições
regulares;
– Quando o particionamento por gama ou lista leva a que os dados sejam
particionados de um modo não favorável face às queries mais frequentes.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Muito útil nas seguintes situações:
166
83
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Exemplo de hash partitioning (Oracle)
Número de partições
Método de
particionamento
Atributo a que é
aplicada a função
de hash
Tablespaces onde as
partições ficam armazenadas
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
CREATE TABLE sales_hash
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
week_no NUMBER(2))
PARTITION BY HASH(salesman_id)
PARTITIONS 4
STORE IN (data1, data2, data3, data4);
167
Sub-particionamento (Oracle)
• O método de particionamento base é range:
• Útil quando os objectos são mesmos muito grandes.
A utilização de hash ou list nas sub-partições segue
a mesma lógica de quando estes métodos são
usados em particionamento normal:
– hash para particionamento regular;
– list para controlar específicamente as partições.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
– Range + hash partitioning
– Range + list partitioning
168
84
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Exemplo de range-hash partitioning (Oracle)
Definição das partições primárias
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Método de
particionamento
Chave para o
CREATE TABLE sales_composite
particionamento
primário (range)
(salesman_id NUMBER(5),
primário
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
sales_date DATE)
Chave para o subPARTITION BY RANGE(sales_date)
particionamento
SUBPARTITION BY HASH(salesman_id)
(hash)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1 TABLESPACE data1,
As sub-partições e
SUBPARTITION sp2 TABLESPACE data2,
respectivos
tablespaces são
SUBPARTITION sp3 TABLESPACE data3,
indicados por um template
SUBPARTITION sp4 TABLESPACE data4)
(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY'))
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY'))
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY'))
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
PARTITION sales_may2000 VALUES LESS THAN(TO_DATE('06/01/2000','DD/MM/YYYY')));
169
CREATE TABLE bimonthly_regional_sales
(deptno NUMBER,
item_no VARCHAR2(20),
txn_date DATE,
txn_amount NUMBER,
state VARCHAR2(2))
PARTITION BY RANGE (txn_date)
SUBPARTITION BY LIST (state)
SUBPARTITION TEMPLATE(
SUBPARTITION east VALUES('NY', 'VA', 'FL') TABLESPACE ts1,
SUBPARTITION west VALUES('CA', 'OR', 'HI') TABLESPACE ts2,
SUBPARTITION central VALUES('IL', 'TX', 'MO') TABLESPACE ts3)
(
PARTITION janfeb_2000 VALUES LESS THAN (TO_DATE('1-MAR-2000','DD-MON-YYYY')),
PARTITION marapr_2000 VALUES LESS THAN (TO_DATE('1-MAY-2000','DD-MON-YYYY')),
PARTITION mayjun_2000 VALUES LESS THAN (TO_DATE('1-JUL-2000','DD-MON-YYYY'))
);
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Exemplo de range-list partitioning (Oracle)
170
85
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Particionamento de índices (Oracle)
• Local indexes: o particionamento é associado às
partições definidas para as tabelas (são estes os
mais usados em data warehousing).
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Global indexes: particionados independentemente
das tabelas (bons resultados em bases de dados
operacionais).
171
Henrique Madeira, DEI-FCTUC, 2001
Discos RAID
Redundant Arrays of Inexpensive Disk
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
172
86
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
RAID - Redundant Arrays of Inexpensive Disk
Objectivos de estudo:
– Entender os problemas e as limitações do sistema de
discos numa base de dados;
– Saber quais os benefícios que a tecnologia RAID pode
trazer;
– Saber quando é útil usar RAID e em que configuração
(nível), dependendo do tipo de base de dados.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
– Conhecer os principais conceitos da tecnologia RAID;
173
Evolução da velocidade de processadores
Tempo
In “Computer Architecture: A Quantitative Approach”, J. Hennessy and D. Patterson, Morgan Kaufmann Publishers, Inc. 1996.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Data Warehousing e OLAP
Henrique Madeira, DEI-FCTUC, 2001
Velocidade
174
87
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Evolução dos discos (custo e velocidade)
Custo MBytes
Tempo
O tempo de acesso médio baixou pouco, pois há limites mecanicos
à sua melhoria
In “Computer Architecture: A Quantitative Approach”, J. Hennessy and D. Patterson, Morgan Kaufmann Publishers, Inc. 1996.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Velocidade
175
Requisitos do armazenamento de dados
Grande
velocidade
Grande
disponibilidade
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Baixo custo
176
88
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Alguns factos acerca dos acessos a discos
• O acesso às diferentes áreas do disco não é
uniforme;
80% dos acessos são efectuados a dados que
correspondem a apenas 20% da capacidade do disco
(os “hot spots”);.
Melhorar a velocidade colocando os ficheiros
que correspondem a hot spots em vários discos
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Regras 80/20
177
Distribuição dos ficheiros por vários discos
Controlador de
Disco
Problemas
• É difícil, por vezes, identificar
os ficheiros com mais acessos;
Concentração de hot spots
• Os ficheiros com mais acessos
variam com o tempo.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Melhora-se a velocidade de
acesso distribuindo os ficheiros por diversos discos.
Bus
Bus de
de E/S
E/S
178
89
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Consequências de ter vários discos
• Melhora (potencialmente) a velocidade;
• Aumenta a capacidade a custos reduzidos;
• O tempo médio entre falhas (MTBF) reduz-se
• É necessário tolerar as falhas nos discos
Notar que os discos já são, por inerência, a parte de um
computador mais susceptível de falhas
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
mas
179
Controlador do disco
O controlador tem um papel muito importante na
disponibilidade na fiabilidade e na velocidade.
Bus
Bus de
de E/S
E/S
Controlador de
Disco
Contr.
Contr.
Disco
Disco
Contr.
Contr.
Disco
Disco
Contr.
Contr.
Disco
Disco
Ponto
único de
falha
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Bus
Bus de
de E/S
E/S
180
90
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Conceito de Striping
• Os dados são fragmentados em porções (chunks) e
distribuídos por diversos discos;
• O conjunto de discos é visto pelo utilizador como um único
disco lógico.
A B C D E F G
vel. de acesso de 1 disco * N
D
G
A
E
F
H
B
I
C
N discos
Disco 1
Disco 2
Disco 3
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Velocidade do acesso (bytes/seg) =
181
Consequências da utilização de striping
Melhora a velocidade de acesso a disco, mas…
• Uma falha num disco leva à perda de dados em todo o
conjunto.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• A probabilidade de falha num dos discos aumenta
proporcionalmente ao número de discos;
182
91
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Atributos de um RAID
• Conjunto de discos físicos vistos pelo utilizador como um
único disco lógico;
• Uma parte da capacidade dos discos é usada para
armazenar informação redundante de modo a poder
recuperar os dados mesmo quando um disco avaria
completamente.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Os dados são distribuídos pelos diferentes discos físicos de
um modo bem definido;
183
Tipos (níveis) de RAID
• A proposta original de RAID apresentava 5 alterantivas
para a utilização de arrays de discos, designadas por níveis:
RAID 1 a RAID5;
• Cada nível de RAID corresponde a um
diferente compromisso do triângulo
Grande
velocidade
Grande
disponibilidade
Os principais conceitos de RAID foram definidos num artigo muito famoso chamado “A
Case for Redundant Arrays of Inexpensive Disk (RAID)” de D. Patterson et. Al., 1987
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Foram, posteriormente, acrescentados dois níveis: RAID 0
Baixo custo
e RAID 6;
184
92
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
RAID 0 - striping sem redundância
Do ponto de vista do utilizador
corresponde a um único disco
Velocidade
Disponibilidade
A B C D E F G
I
F
Disco 1
G
J
B
L
C
Disco 2
H
M
D
Disco 3
Pode ter um número de
discos qualquer
Disco 4
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
E
A
Custo
185
Tamanho escolhido para striping
A escolha do tamanho das porções usadas para
striping (chunks) é muito importante para a
velocidade de acesso
comparando com o tamanho médio de
dados trocados em cada acesso
• Pequeno
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Grande
186
93
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Chunks grandes
A B C D E F G
I
F
G
J
B
Disco 1
L
C
Disco 2
H
M
D
Disco 3
Os outros discos
estão livres para ser
acedidos
Disco 4
Chunk grandes são bons para:
– acesso muito frequentes;
– pequena quantidade de dados trocada em cada acesso.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
E
A
A maior parte dos acesso reduzem-se a um único chunk.
187
Chunks pequenos
E
I
A
F
Disco 1
G
J
B
L
C
Disco 2
H
M
D
Disco 3
Os acessos espalham-se
pelos diversos discos em
paralelo, pelo que a
velocidade de acesso é
multiplicada pelo número
de discos. Mas só é
possível um acesso de cada
vez.
Disco 4
Chunk pequenos são bons para:
Bases de Dados com
transações longas
– acesso pouco frequentes;
– grande quantidade de dados trocada em cada acesso.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
A B C D E F G
188
94
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
RAID 1 - Discos duplicados
Primeira (e óbvia) abordagem para tolerar falhas em
discos
Velocidade
Do ponto de vista do utilizador
corresponde a um único disco
Disponibilidade
B
C
B
A
Cada disco contém uma cópia
do outro. As leituras podem ser
aceleradas lendo metade dos
dados de cada disco.
C
A
Disco 1
Disco 2
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Custo
A B C D E F G
189
RAID 1 - Discos duplicados com striping
A B C D E F G
Os dados são distribuídos por
dois pares de discos
C
A
Disco 1
D
B
Disco 2
D
B
Disco 3
Disco 4
• Usar pares de discos mais pequenos em vez de um único par de
discos de grande capacidade;
• O striping leva a que os acessos possam ser distribuídos em paralelo
pelos diversos discos;
• O tamanho do chunk deve ser afinado tendo em conta o tipo de
acessos mais frequentes.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
C
A
190
95
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
RAID 2 - Códigos de detecção/correcção
Transaposição dos mecanismos de detecção e correcção de erros
usados na memória para os discos
Códigos de detecção/correcção
(paridade, Hamming, …)
Esta configuração tem mero interesse académico porque:
– Gastar discos extra para a detecção de erros é desnecessário visto
a detecção já existir num disco normal (através de CRCs);
– Os códigos de correcção são pouco eficientes, no que toca a
espaço em disco ocupado.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Dados
191
RAID 3 - Paridade do tipo bit-interleaved
Do ponto de vista do utilizador corresponde a um
único disco com grande velocidade e fiabilidade
D+ E + F
A+ B + C
A B C D E F G
Velocidade
Disponibilidade
G
E
F
H
B
Disco 1
I
Custo
C
Disco 2
Disco 3
Disco 4
Mas o controlador é caro
• O tamanho do chunk é pequeno (1 bit ou 1 byte);
• Os acessos são simultaneos a todos os discos para que a operação de XOR possa
ser feita em tempo real e porque o chunk é pequeno.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
D
A
192
96
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Falha de um disco num RAID 3
• A falha de um disco não interrompe o funcionamento (é
sempre possível ler a informação);
• Ao substituir o disco estragado por um novo o sistema
consegue regenerar a informação que estava no disco
estragado;
Henrique Madeira, DEI-FCTUC, 2001
• Esta regeneração pode decorrer com os restantes discos em
funcionamento normal;
• A falha de um segundo disco antes de ter sido regenerado o
disco estragado é fatal. Este é o único factor que limita o
número de disco usados para cada disco com a informação
de paridade.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
193
RAID 4 - Paridade do tipo bloco- entremeado
• Identico a RAID 3 mas com o tamanho do chunk maior
(um sector, tipicamente);
A B C D E F G
A+ B + C
D
G
A
E
Disco 1
F
H
B
I
C
Disco 2
Disco 3
Disco 4
Podem decorrer várias
leituras em simultâneo, pois o
disco onde está a paridade
não é necessário para as
leituras quando não há
nenhum disco avariado. A
velocidade de cada leitura é
mais baixa do que no RAID
3. Bom para BD com muitas
transações mas cada uma de
curta duração
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Melhora a velocidade de leitura nos casos em que há
leituras muito frequentes mas com poucos dados a ser lidos
de cada vez.
194
97
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Acessos de leitura num RAID 4
Em circunstâncias normais, i.e., sem discos
avariados,
Pode haver várias leituras em simultaneo se os
acessos forem de poucos dados;
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
as leituras são feitas apenas aos sectores onde está a
informação.
195
Acessos de escrita num RAID 4
– Ler sector onde estão os dados antigos;
– Ler sector do disco de paridade onde está o resultados
do XOR correspondente a esse sector;
– É calculado um novo XOR (fazendo o XOR dos
novos dados com o XOR antigo)
– São escritos os novos dados e o novo XOR
Uma escrita envolve sempre duas leitura e duas escritas
Como só há um disco de paridade só pode haver uma escrita
de cada vez.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Escritas
196
98
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
RAID 5 - Paridade bloco-entremeado rotativa
• Possibilita mais do que uma escrita ao mesmo
tempo, pois a informação de paridade (XOR) não
está concentrada num único disco;
• Tal como RAID 4, o tamanho dos chunks é grande
(bom para acessos curtos mas muito frequentes).
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Semelhante a RAID 4, mas com a diferença que os
chunks contendo a paridade estão distribuídos pelos
discos (Rotating Parity Array);
197
RAID 5
Velocidade
Disponibilidade
Custo
Mas o controlador é
mais caro
G+ H + I
D+ E + F
A B C D E F G
D
G
A
E
B
Disco 1
I
F
H
C
Disco 2
Disco 3
Disco 4
Permite várias escritas
simultâneas porque a
informação de paridade está
espalhada por todos os
discos
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
A+ B + C
198
99
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Falhas em RAID 5
• O sistema interrompe a actividade normal;
• Todos os discos são lidos;
• O sistema volta a funcionar normalmente.
Usando controladores sofisticados é possível fazer a
recuperação mantendo o sistema a funcionar.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• O conteúdo do novo disco é reconstruído;
199
RAID 6 - Redundância dupla
• São necessários dois discos extra para guardar a paridade
• A paridade dos mesmos dados é guardada segundo dois códigos
diferentes (P e Q)
• Pode recuperar de falhas em dois discos.
Disponibilidade
C
A
F
E
Custo
D
B
Disco 1
Disco 2
P(C,D)
Disco 3
Q(C,D)
Disco 4
Q(A,B)
Henrique Madeira, DEI-FCTUC, 2001
Velocidade
A B C D E F G
P(A,B)
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
200
100
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Acessos em RAID 6
Tal como no RAID 5 o chunk é grande, pelo que
favorece acessos curtos mas muito frequentes.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Semelhantes a RAID 5 com a diferença que nas
escritas são sempre envolvidos três discos (três
leituras e três escritas).
201
Níveis de RAID
Nível
0
1
Discos
duplicados
2
Códigos de
detecção/correcção
3
Paridade do tipo bit-entremeado
4
Paridade do tipo bloco-entremeado
5
Paridade bloco-entremeado rotativa
6
Redundância dupla
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Sem redundância
202
101
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Henrique Madeira, DEI-FCTUC, 2001
Área de Estágio:
Extracção, Transformação e Transporte
(ETT)
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
203
Qual a estratégia para ETT?
BDs operacionais
Folhas de cálculo,
ficheiros, ...
Clientes
Factos
vendas
Área de
estágio
Produtos
Fontes externas
Lojas
Promoções
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Tempo
Sistemas legados
204
102
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Passos no processo de ETT
I.
Planificação
1.
2.
3.
4.
Carregamento de dimensões
1.
Fazer, testar e executar planos ETT para as dimensões estáticas e simples.
Permite testar toda a infraestrutura.
Fazer, testar e executar planos ETT para as dimensões que mudam.
Tratar todos os restantes casos (dimensões geradas, com dados manuais, etc)
2.
3.
III.
Carregamento de factos
1.
2.
IV.
Fazer, testar e executar planos ETT para tabelas de factos
Fazer e testar processo de carregamentos periódicos
Automatizar o processo ao máximo
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
II.
Definir um plano geral (do tipo end-to-end)
Definir infraestrutura para a área de estágio
Escolher as ferramentas ETT
Fazer plano detalhado analisando todos os problemas que é necessário
resolver para carregar cada tabela destino (e.g., fontes, transformações, etc)
205
Exemplo: ETT numa DW de cadeias de lojas
Loja
ID_produto
ID_data
ID_Loja
ID_Cliente
ID_Promoção
Num_produtos
Num_items
Custo_compra
Valor_venda
Tempo
ID_data
Dia_do_mês
Dia_da_semana
Dia_do_ano
Semana_do_ano
Mês
Número_do_mês
Trimestre
Período_fiscal
Flag_feriado
Flag_dia_semana
Flag_últ_dia_mês
Estação_ano
Aconteci_espec
……….
Promoção
ID_promoção
Número
Nome_promo
Tipo_red_preço
Tipo_anúncio
Tipo_cartaz
Tipo_coupons
Meio_anúncio
Meio_cartaz
Custo_promoção
Início_promoção
Fim_promoção
……...
Clientes
ID_Cliente
Número_cartão
Nome
Endereço
Localidade
Código_postal
Distrito
Região
Telefone
(outros atributos
demográficos)
……...
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
ID_loja
Nome
Número_loja
Endereço
Localidade
Código_postal
Distrito
Região
Telefone
Fax
Gestor_loja
Área_total
Área_mercearias
Área_congelados
Área_bazar
Nº_Caixas
Data_inaug
Data_ult_remod.
……...
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Produto
ID_produto
Número
Nome
Marca
Categoria
Subcategoria
Departamento
Tam_embalagem
Tipo_embalagem
Tipo_dieta
Peso
Unidade_de_peso
Quant_caixa
Caixas_p_pallete
Larg_prateleira
Altura_prateleira
Profun_prateleira
……...
206
103
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Plano geral: ideia básica
DB
existências
• Processa 50K
vendas por dia
• 40% clientes
desconhecidos
• etc
Sistemas legados
DB
corporação
• Muda lentamente
• 700K clientes
• ~0,1% mudam
diariamente em
atributos
demográficos
• Muda muito
lentamente
• Cerca de 100K
• ~0,01% muda
diariamente
Factos
vendas
Folhas de cálculo,
ficheiros, ...
• ~100 promoções ano
• Vários ficheiros
Excel
• Ficheiro em
máquinas diferentes
• Máquinas de
utilização pessoal
• Introdução
manual de dados
• Ficheiros de
texto com
características
das lojas.
• Precisam
limpeza
manual.
• Testar e conciliar
clientes em
sistemas antigos
Produtos
Clientes
Outras fontes
Gerada
sinteticamente
por ferramenta/
programa
Promoções
Lojas
Tempo
Henrique Madeira, DEI-FCTUC, 2001
Fontes BDs operacionais
Destinos
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
207
Definir infraestrutura para a área de estágio
• De uma simples conta no servidor onde vai ficar a DW a
máquinas dedicadas de grandes capacidade.
• Tipicamente, para cada dimensão e tabela de facto,
prepara-se tudo na área de estágio para depois fazer um
carregamento directo.
• A área de estágio deve ter em conta que carregamentos
parciais são frequentes para factos e dimensões muito
grandes.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• A decisão depende do volume de dados envolvidos e da
complexidade das operações a fazer nos dados antes de os
carregar na DW.
208
104
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Carregamentos iniciais
• Feitos directamente da área de estágio para as tabelas da
DW (depois dos dados preparados)
• Alguns cuidados:
• Gestão de índices:
– Drop + reindex se inserir mais do que 10% a 15% dos registos
– Manter os índices. Neste caso é preciso ter em atenção se as
estruturas físicas dos índices estão preparadas para o crescimento.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
– Desligar (ou configurar para para mínimo impacto na
performance) sistemas de logging.
– Ordenar previamente os dados a carregar pela chave primária
– Fazer, eventualmente, algumas agregações básicas durante o load.
209
Carregamentos incrementais
Definir estratégia para identificar novos dados nos
sistemas fonte:
–
–
•
Novas transacções
Actualizações a dados de transacções anteriores
Identificar:
1.
2.
3.
Registos novos a introduzir em cada dimensão
Actualizações de atributos de dimensões e como estas vão ser
tratadas
Novos factos
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
•
210
105
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Identificação de novos dados nos sistemas fonte
• Usar o sistema de logs existente no sistema fonte
– Utilização de timestamps;
– Regras usadas para a atribuição de chaves
– Atributos de certas tabelas que determinam momentos no tempo
com precisão.
– Etc.
• Se possível, construir um sistema de log específico para
sistemas fonte (e.g., usando snapshots):
– Interfere com desempenho do sistema fonte.
– Nem sempre é viável
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Usar características das aplicações dos sistemas fontes:
211
Administração da DW
• Construir, utilizar e manter as ferramentas de extracção de
dados dos sistemas operacionais;
• Garantir a qualidade dos dados (após cada extração);
• Construir e manter agregados;
• Fazer cópias de segurança periodicamente e recuperar o
estado da base de dados em caso de falha;
• Construir e manter templates para exploração de dados;
• Formar e treinar utilizadores;
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Vigiar e afinar o desempenho do sistema;
212
106
Tópicos Avançados de Bases de Dados
Instituto Politécnico da Guarda, 2004/2005
Ritmo típico de uma DW
A DW transita periodicamente (diariamente, semanalmente,
etc) entre dois estados:
(16 a 22 horas por dia, no caso de um ritmo diário)
– Carregamento (2 a 8 horas por dia, no caso de um ritmo diário)
• Carregar novos dados;
• (Re)construir índices e outras estruturas necessárias à optimização do
desempenho;
• Verificar qualidade dos dados;
• Abrir o acesso ao novos dados.
Tópicos Avançados de Bases de Dados, 2004/2005
Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
– Exploração
213
O Futuro das DW
• Optimização das estratégias de execução para as queries;
• Indexação das tabelas de dimensões para “browsing” e restrições;
• Acesso (e indexação) das chaves compostas da tabela de factos;
• Aumento do SQL de modo a suportar perguntas de negócio;
• Suporte de compressão de dados a baixo-nível;
• Ferramentas de projecto de BD dimensionais;
• Ferramentas de extracção e administração de dados;
• End user query tools.
• Integração de dados mantendo-os nos sistemas fonte
Henrique Madeira, DEI-FCTUC, 2001
• Suporte de processamento paralelo;
• Webhouses.
Tópicos Avançados de Bases de Dados, 2004/2005
Data Warehousing e OLAP
Instituto Politécnico da Guarda
214
107