View
223
Download
0
Category
Preview:
Citation preview
Data Warehousing e OLAP 1
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
1
Tópicos Avançados de Bases de Dados
Henrique Madeira
2004/2005
2Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Data Warehousing e OLAP
3Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Bibliografia (tópico de Data Warehousing)
• Apontamentos do docente;• Livros sobre DW:
- 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;
4Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Características genéricas das Data Warehouses
5Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
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 operacionaise outros sistemas Data Warehouse
Utilizadores
Utilizadores
6Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Volume de dados
• Até 20 GbytesPequena dimensão; corre num bom PC
• De 20 a 100 GbytesMédia dimensão; precisa de workstation poderosa;
• De 100 Gbytes a 1 TBytesGrande dimensão; servidores poderosos, normalmente com processamento paralelo
• Superior a 1 TBytesEnorme dimensão; necessita processamento maciçamente paralelo.
Data Warehousing e OLAP 2
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
7Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Algumas características de DW
• Dependência temporal;
• Não volatilidade;
• Orientadas para fins específicos;
• Integração e consistência informação;
• Estrutura de dados optimizada para a consulta.
8Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Dependência temporal
• Os dados na DW foram recolhidos ao longo do tempo (não são instantâneos);
• É preciso adicionar aos dados o instante temporal a que estes se reportam.
9Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
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;
• Depois de carregados (a partir de uma BD operacional) a única operação é fazer queries.
10Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Orientadas para fins específicos
• Devem ser guardados apenas os dados relevantes para a tomada de decisões;
• Muitos dados necessários à gestão do dia-a-dia dos sistemas operacionais não têm relevo para a DW.
11Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Integração e consistência de informação
• No ambiente operacional a mesma informação pode residir em dados com nome e aspecto diferente;
• É necessário integrar e dar consistência aos dados provenientes das BD operacionais antes de os armazenar na DW.
12Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Optimização das consultas
• Uma vez carregados, os dados só são alvo de consultas;
• As DW têm quantidades enormes de dados.
Visão multidimensionalDesnormalização parcial
Os dados devem ser armazenados de forma a acelerar ao máximo as consultas
Data Warehousing e OLAP 3
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
13Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Visão genérica do modelo dimensional
14Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Modelo dimensional
• Modelo usual em bases de dados operacionais: E/R
• O modelo dimensional é uma alternativa– contém a mesma informação...– organiza-a de forma simétrica orientada para o utilizador:
• Fácil compreensão• Bom desempenho nas pesquisa
• Data Warehouses construídas sobre E/R complexosfalham
15Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Modelo multidimensional
Jan Fev Mar Abr
Leite
Farinha
AçúcarCafé
Continente CoimbraContinente Leiria
Vendas
Produto
Hiperm
ercad
o
Data
16Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Exemplo de esquema em estrela
Tempo
Venda
ID_dataDiaDia_da_semanaSemana_do_anoMêsTrimestreAno
ProdutoID_produtoNomeTipoMarcaCategoriaEmbalagemDescrição
LojaID_lojaNomeLocalidadeDistritoÁreaNº_Caixas
Cadeia de Lojas
ID_dataID_produtoID_lojaUnid_vendidasCusto_compraValor_vendaNº_Clientes
17Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
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.
ID_dimensão 1ID_dimensão 2ID_dimensão 3ID_dimensão 4
Facto 1Facto 2
...Facto n
Tabela dimensão 1
Tabela FactosID_dimensão 1
Descrição 1Atributo...
Tabela dimensão 2ID_dimensão 2
Descrição 2Atributo...
Tabela dimensão 3ID_dimensão 3
Descrição 3Atributo...
Tabela dimensão 1ID_dimensão 4
Descrição 4Atributo...
18Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Tabela de factos
• Contém as medidas do negócio
• Os factos mais uteis são– numéricos– aditivos
• Representam relacionamentos M:1 com as dimensões do negócio
Data Warehousing e OLAP 4
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
19Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Tabelas de dimensão
• Tabelas companheiras da tabela de factos
• Cada dimensão representa parâmetros do negócio– tempo, clientes, produtos, etc
• Chave primária determina o dado específico
• Outros atributos específicos da dimensão
• Desnormalizada e com hieraquias. 20Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
OLAP - Online Analytical Processing
• Pesquisa e apresentação de texto e dados numéricosdas Data Warehouses
• ROLAP (Relational OLAP)– 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
21Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Pesquisas - baixo nívelTempo
VendaID_dataDiaDia_da_semanaSemana_do_anoMêsTrimestreAno
ProdutoID_produtoNomeTipoMarcaCategoriaEmbalagemDescrição
LojaID_lojaNomeLocalidadeDistritoÁreaNº_Caixas
Cadeia de Lojas
ID_dataID_produtoID_lojaUnid_vendidasCusto_compraValor_vendaNº_Clientes
Select avg(Valor_venda x Unid_vendidas)from Venda V, Tempo T, Produto Pwhere JOIN_TABELASgroup by P.Marca, T. Mês
22Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Interfaces com o utilizador
Exploração de dados na Data Warehouse – Ferramenta OLAP típica
• 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
23Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Browsing
• Explorar uma das dimensões definindo restrições e escolhendo as colunas pretendidas. coluna Nome_Loja Localidade Distrito Area_total Nºcaixas
restriçãovalores Loja Zé Ansião Coimbra 250 2 distintos Super Mário Aveiro Leiria 500 4
Super Bill Coimbra Aveiro 750 6Loja da Maria Leiria 1000Loja do Manel Penacova 1500John's Market PenelaVieiras PombalLoja 007Cadeia JoelLoja dos 500
24Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Browsing (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 Nome_Loja Localidade Distrito Area_total Nºcaixas
restrição Coimbra 750 4valores Loja do Manel Coimbra Coimbra 750 4distintos John's Market Penacova
VieirasLoja dos 500
Data Warehousing e OLAP 5
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
25Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Pesquisas - Slice and Dice
Vendas por tempo e produto Vendas por loja e
marca
26Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Drill-Down & Roll-Up
Categoria mais genérica
Categoria intermédia
Categoria mais detalhada
Detalhe completo
Drill-Down Roll-up
27Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Tempo: Drill-Down & Roll-Up
Ano
Trimestre
Mês
Semana
Drill-Down Roll-up
Dia
ALL
28Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Tempo: Drill-Down (exemplo)Select avg(Valor_venda x Unid_vendidas)from Venda V, Tempo T, Produto Pwhere JOIN_TABELASgroup by P.Marca, T. Mês;
Select avg(Valor_venda x Unid_vendidas)from Venda V, Tempo T, Produto Pwhere JOIN_TABELASgroup by P.Marca, T. Dia;
• Questão: como se representa o “ALL” na pesquisa?
29Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Arquitectura geral da Data Warehouse
30Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Elementos básicos de uma data warehouse
BDs operacionais
Sistemas legados
Folhas de cálculo, ficheiros, ...
Fontes externas
Data Staging
Area
Data warehouse(presentation servers)
Utilizadores
ROLAP/MOLAP
Ad hocqueries
Relatórios
Aplicações específicas
Modelos e outras
ferramentas
Data Warehousing e OLAP 6
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
31Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Data Marts
• É, normalmente, um subconjunto de uma DW;
• Numa Data Mart os dados são focalizados numa área específica (processo de negócio);
• Muitas vezes uma Data Mart é feita para responder rápidamente a uma área de actividade.
32Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Arquitectura de BDs de uma organização 1
BDs operacionais
Data WarehouseData MartSistemas legados
Folhas de cálculo, ficheiros, ...
Fontes externasUtilizadoresUtilizadores
33Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Arquitectura de BDs de uma organização 2
BDs operacionais
Data Warehouse
Data Mart
Sistemas legados
Folhas de cálculo, ficheiros, ...
Fontes externas
Utilizadores
Utilizadores34Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Sistemas fonte
• sistema de registo de transacções• gestão de clientes, gestão de produtos, gestão de vendas, etc…
• principais características assumidas• 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
35Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Área de processamento temporário (Staging Area)
• Área e processos que actuam sobre os dados fonte• limpeza• transformação• combinação• preparação
Staging AreaData Warehouse
36Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Metadados
• É necessário uma estrutura (na prática outra base de dados) para descrever os dados da DW. Deve descrever:– Que dados existem na DW;
– Qual o seu formato;
– 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.
Data Warehousing e OLAP 7
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
37Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
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• Tratamento de erros• Pesquisa (utilização normal)
38Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
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– coincidência exacta de chaves ou “fuzzy matches”
• 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
39Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Limpeza dos dados
• 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
40Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Carregamento
• 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
Transformação Carregamento
Staging Area
DW
41Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
BD operacionais vs Data Warehouses
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
42Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Visão genérica sobre o processo de construção de uma Data Warehouse
Data Warehousing e OLAP 8
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
43Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Infraestrutura para projecto
Construir uma DW é complexo e requer conhecimento especializado em várias áreas
• Definir equipa;
• Definir ferramentas e sistemas;
• Identificar fases do projecto;
• Definir métodos de trabalho;
• Identificar responsabilidades para cada tarefa/fase.
44Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Plan
eam
ento
do p
roje
cto Definição
dos requisitos
do negócio
Modelaçãodimensional
Desenhofísico
Desenho do Data Staing
Colocaçãoem
Produção
Gestão do projecto
Especificaçãodas aplicaçõesde utilizador
Desenvolvimentodas aplicações
Selecção e instalação de
produtos
Desenho daarquitectura
Esquema geral do projecto de uma Data Warehouse(R. Kimball)
45Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
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 modelo de dados para a DW;
• 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.
46Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
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?
• Qual a informação necessária para atingir esses objectivos?
• Porque é que a informação é necessária?
• Quem vai usar essa informação (dentro da empresa)?
• Como é que a informação vai ser usada?
47Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Identificar modelo de dados das BD fonte
Bases de dados operacionais
48Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Modelo de dados das BD fonte (cont.)
• Responder à questão: quais os dados fonte para a DW?
• Muitas vezes os modelos de dados das BD operacionais não existem ou estão desactualizados;
• Necessário usar ferramentas de reverse-engineering;
• Alguns dados da DW podem ter outras origens que não as BD operacionais.
Data Warehousing e OLAP 9
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
49Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
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 de referência (dimensões)Dados correspondentes a entidades de referência que permitem completar e situar os dados dos factos históricos (clientes, fornecedores, pessoas, etc)
– SíntesesDados previamente calculados e que se prevê virem a ser necessários (relatórios de vendas mensais, movimentos semanais de stock, etc)
50Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
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);
• Par cada processo de negócio:– 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.
51Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Modelos na construção de uma DW
Modelo do negócio (ER)
Modelo dimensional
Modelo físico
Desnormalização sistemáticaQue transações?
Que queries?
52Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Definir regras para o mapeamento de dados
• Identificar os dados a extrair;
• Identificar os dados que faltam (impossíveis de extrair das BD operacionais);
• 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.
53Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Extrair, integrar, purificar e racionalizar os dados
• Usar ou construir as ferramentas que concretizam as regras para mapeamento dos dados;
• Rever regras e processos de mapeamento sempre que são detectadas inconsistências;
• Documentar todos os passos.
54Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Exploração, afinação e avaliação de eficácia
• Definição/construção de ferramentas de exploração;
• Afinação de desempenho;
• Administração da data warehouse.
Data Warehousing e OLAP 10
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
55Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
O modelo multidimensional
56Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Modelo multidimensional
• Factos armazenados num array multidimensional;• As dimensões são usadas para indexar o array;• Normalmente construídas sobre bases de dados
relacionais.
Jan Fev Mar Abr
Leite
Farinha
AçúcarCafé
Continente CoimbraContinente Leiria
Vendas
Produto
Hiperm
ercad
o
Data
57Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Exemplo de esquema em estrela
Tempo
Venda
ID_dataDiaDia_da_semanaSemana_do_anoMêsTrimestreAno
ProdutoID_produtoNomeTipoMarcaCategoriaEmbalagemDescrição
LojaID_lojaNomeLocalidadeDistritoÁreaNº_Caixas
Cadeia de Lojas
ID_dataID_produtoID_lojaUnid_vendidasCusto_compraValor_vendaNº_Clientes
58Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
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
ID_dimensão 1ID_dimensão 2ID_dimensão 3ID_dimensão 4
Facto 1Facto 2
...Facto n
Tabela dimensão 1
Tabela FactosID_dimensão 1
Descrição 1Atributo...
Tabela dimensão 2ID_dimensão 2
Descrição 2Atributo...
Tabela dimensão 3ID_dimensão 3
Descrição 3Atributo...
Tabela dimensão 1ID_dimensão 4
Descrição 4Atributo...
59Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Algumas características do modelo em estrela
ID_dimensão 1ID_dimensão 2ID_dimensão 3ID_dimensão 4
Facto 1Facto 2...Facto n
Tabela dimensão 1
Tabela FactosID_dimensão 1
Descrição 1Atributo...
Tabela dimensão 2ID_dimensão 2
Descrição 2Atributo...
Tabela dimensão 3ID_dimensão 3
Descrição 3Atributo...
Tabela dimensão 1ID_dimensão 4
Descrição 4Atributo...
Tabela de Factos• Constituída por atributos numéricos (factos) e
pelas chaves forasteiras que a ligam à tabelasde 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).
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).60Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Passos para definir modelos em estrela
1 - Identificar os processos de negócio/actividade
2 - Identificar os factos;
3 - Identificar dimensões;
4 - Escolher a ganularidadedos dados a registar.
Sem perder de vistas os dados
efectivamente disponíveis(BDs
operacionais,
ficheiros, e
tc)
Data Warehousing e OLAP 11
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
61Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Exemplo – Cadeia de supermercados
• Cadeia de supermercados de uma mesma empresa
• Vamos pensar apenas nas vendas (a aquisição de produtosaos fornecedores é global para toda a empresa)
• Cada supermercado tem vários departamentos (mercearia, higiene e limpeza, etc)
• 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
62Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Dados do negócio
• Onde recolher os dados?Caixa registadora (POS - point of sales). Na prática, os dados sãorecolhidos na base de dados que gere as existências, sendo as caixasregistadoras meros terminais.
• O que interessa medir?Vendas
• 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
63Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
• 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 TempoDimensão Tempo
ID_dataatributos……….
Dimensão ProdutoDimensão Produto
ID_produtoatributos……...
Dimensão LojaDimensão Loja
ID_lojaatributos……...
ID_promoçãoatributos……...
Dimensão PromoçãoDimensão Promoção
ID_dataID_produtoID_lojaID_promoçãoUnid_vendidasCusto_compraValor_vendaNº_clientes
Factos VENDASFactos VENDAS
Factos - Cadeia de Lojas
64Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Dimensões - Cadeia de Hipermercados
• Dimensões principais– Produto x Tempo x Loja
• Existirão outras dimensões de interesse?– Fornecedores?– Promoções?– Cliente?– Nome do empregado responsável naquele dia?
• É normalmente possível adicionar dimensões extra àsdimensões principais
• Todas dimensões tomam um só valor para cada combinação
65Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
DimensãoDimensão TempoTempo
ID_dataatributos……….
Dimensão ProdutoDimensão Produto
ID_produtoatributos……...
Dimensão LojaDimensão Loja
ID_lojaatributos……...
ID_promoçãoatributos……...
Dimensão PromoçãoDimensão Promoção
ID_dataID_produtoID_lojaID_promoçãoUnid_vendidasCusto_compraValor_vendaNº_clientes
Factos VENDASFactos VENDAS
Dimensões - Cadeia de Hipermercados
66Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Granularidade
• 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: produtos x loja x promoção x dia
• 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.
Data Warehousing e OLAP 12
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
67Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
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– tamanho da base de dados poderia tornar-se gigantesco– identificação do cliente não existe
• 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
68Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Detalhe selectivo
• Porque é que os dados devem ser expressos com um detalhe grande num Data Warehouse?– 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
69Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Refinar o modelo
• Detalhar as dimensões
• Rever os factos
• Verificar a consistência (entre factos, entre factos e dimensões, etc)
• Reavaliar granularidade
70Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Exemplo 1 - Cadeia de lojas
TempoTempoID_dataDia_do_mêsDia_da_semanaDia_do_anoSemana_do_anoMêsNúmero_do_mêsTrimestrePeríodo_fiscalFlag_feriadoFlag_dia_semanaFlag_últ_dia_mêsEstação_anoAconteci_espec……….
ProdutoProdutoID_produtoNúmeroNomeMarcaCategoriaSubcategoriaDepartamentoTam_embalagemTipo_embalagemTipo_dietaPesoUnidade_de_pesoQuant_caixaCaixas_p_palleteLarg_prateleiraAltura_prateleiraProfun_prateleira……...
LojaLojaID_lojaNomeNúmero_lojaEndereçoLocalidadeCódigo_postalDistritoRegiãoTelefoneFaxGestor_lojaÁrea_totalÁrea_merceariasÁrea_congeladosÁrea_bazarNº_CaixasData_inauguraçãoData_ult_remod.……...
ID_produtoID_dataID_LojaID_PromoçãoUnid_vendidasCusto_compraValor_vendaNº_clientes
ID_promoçãoNúmeroNome_promoTipo_red_preçoTipo_anúncioTipo_cartazTipo_couponsMeio_anúncioMeio_cartazCusto_promoçãoInício_promoçãoFim_promoção……...
PromoçãoPromoção
71Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Exemplo 1: Dimensão tempo
TempoID_dataDia_do_mêsDia_da_semanaDia_do_anoSemana_do_anoMêsNúmero_do_mêsTrimestrePeríodo_fiscalFlag_feriadoFlag_dia_semanaFlag_último_dia_mêsEstação_anoAcontecimento_espec……….
• Existe sempre, pois representa a dependência temporal inerente à DW;
• Deve descrever o tempo tal como ele é visto para fins de gestão da actividade(negócio) em causa;
• Deve conter a caraterização do tempo nos atributos pelos quais se pretendeposteriormente fazer pesquisas;
• É gerada, normalmente, de uma forma sintética (i.e., sem ser a partir de umaBD operacional) para todo o período de tempo considerado na DW.
72Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Exemplo 1: Dimensão produto
ProdutoID_produtoNúmeroNomeMarcaCategoriaSubcategoriaDepartamentoTam_embalagemTipo_embalagemTipo_dietaPesoUnidade_de_pesoQuantidade_caixaCaixas_por_palleteLargura_prateleiraAltura_prateleiraProfud_prateleira……...
• Deve conter a caraterização dos produtostal como eles são vistos pelo gestor dacadeia de lojas;
• Contém todos os atributos pelos quais se pretende posteriormente fazer perguntas;
• Como acontece normalmente nas tabelasde dimensões, é uma tabela bastantedesnormalizada.
Data Warehousing e OLAP 13
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
73Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Exemplo 1: Dimensão loja
LojaID_lojaNomeNúmero_lojaEndereçoLocalidadeCódigo_postalDistritoRegiãoTelefoneFaxGestor_lojaÁrea_totalÁrea_merceariasÁrea_congeladosÁrea_bazarNº_CaixasData_inauguraçãoData_ultim_remod.……...
• Contém a caraterização das lojas tal comoeles são vistos pelo gestão da cadeia de lojas;
• Contém todos os atributos pelos quais se pretende posteriormente fazer perguntas, incluindo atributos de natureza geográfica(localização) e de natureza temporal (datasde inauguração,…).
74Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Exemplo 1: Dimensão promoções
ID_promoçãoNúmeroNome_promoTipo_red_preçoTipo_anúncioTipo_cartazTipo_couponsMeio_anúncioMeio_cartazCusto_promoçãoInício_promoçãoFim_promoção……...
Promoção • Contém a caraterização das promoçõesefectuadas;
• Neste exemplo há apenas uma dimensão de promoções (para todos os tipos de promoções), mas seria possível ter emalternativa uma dimensão para cada tipo de promoção;
• A dimensão promoção representa, nesteexemplo, uma dimensão muito sensível e importante, pois as promoções são um dos aspectos em que o gestor mais facilmentepode actuar quando pretende incrementaras vendas numa loja ou num determinadoproduto.
75Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Cálculo simplificado do espaço ocupado
Granularidade = Produtos vendidos / em cada loja / em cada diaTempo = 3 anosNº Produtos = 100.000 (apenas 20% dos produtos são vendidos diariamente)Lojas = 100Tamanho médio de registo = 8 atributos x 4 Bytes = 32 BytesNº de registos de factos = 3 x 365 x 20.000 x 100 = 2.190.000.000
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 índicesnem vistas materializadas;
76Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Exemplo 2: Existências em armazéns
Armazém
• Recepção;
• Inspecção;
• Entrada no stock;
• Autorização de venda;
• Recolha do stock;
• Embalagem;
• Saída
• 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 ;
Procedimentos excepcionais:
Caracterização da actividade de gestão de existências
77Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Ex. 2: Fotografia periódica de existências
ExistênciasID_tempoID_produtoID_armazémQuant_existenteValor_de_custoÚltimo_preço_venda
Tempo
Produto
Armazém
• Registo periódico das existências em stock;• A Quantidade_existente não é aditiva na dimensão tempo
Quant_saída
• A Quantidade_saída permite saber quantos produtos saíramno intervalo de tempo correspondente a dois registos.
78Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Ex. 2: Registo de transações de existências
ExistênciasID_tempoID_produtoID_armazémID_transacçãoNº_DocumentoQuantidade
Tempo
Produto
Armazém
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.
Quant_existente
• Necessita de atributos como Quant_existente (típicos do modelo“fotográfico”) para dar uma visão prática do processo.
Data Warehousing e OLAP 14
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
79Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Ex. 2: Cálculo simplificado do espaço ocupado
Tempo = 3 anosNº Produtos = 500.000Armazéns = 4Tamanho médio de registo = 7 atributos x 4 Bytes = 28 BytesNº de registos de factos = 3 x 365 x 500.000 x 4 = 2.190.000.000Tamanho aproximado da DW = 28 x 2.190.000.000 = 61,32 GBytes
Tempo = 3 anosNº Produtos = 500.000Armazéns = 4Tamanho médio de registo = 7 atributos x 4 Bytes = 28 BytesNº entregas (no armazém) por ano = 10Nº de transacções por cada entrega de produto = 50Nº de registos de factos = 3 x 500.000 x 4 x 10 x 50 = 1.095.000.000.000Tamanho aproximado da DW = 24 x 1.095.000.000.000 = 84 GBytes
Fotografia periódica das existências
Registo de transacções de existências
80Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Redução espaço ocupado: abordagem simplista
Em muitas situações poderá ser aceitável terinformação detalhada para as existências apenasrelativa ao último mês:
Exemplo (método da fotografia periódica das existências)– 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)
81Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Cruzamento de dados entre processo de negóciodiferentes
Data Mart - marketing
Data Mart - facturação
Definições e carregamentos de dimensões e factos
de forma independente ?
Como cruzaros dados?
82Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Mais do que uma estrela
ExistênciasID_tempoID_produtoID_armazémQuant_existenteQuant_saídaValor_de_custoÚltim_preço_venda
Tempo
Produto
Armazém
VendasID_dataID_produtoID_LojaUnid_vendidasCusto_compraValor_vendaNº_Clientes
Loja
• 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.
83Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Exemplo de múltiplas estrelas
Armazenistas distribuidores
EncomendasDimensão: TempoDimensão: ComponenteDimensão: FornecedorDimensão: Contrato
VendasDimensão: TempoDimensão: ComponenteDimensão: ClienteDimensão: Contrato
ExistênciasDimensão: TempoDimensão: ComponenteDimensão: Armazém
84Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Exemplo de dimensões conformes
Tabela deFactos
Dimensão_7
Dimensão_6
Dimensão_5
Dimensão_2
Tabela deFactos
Dimensão_9
Dimensão_8
Dimensão_1
Dimensão10
Tabela deFactos
Dimensão_3
Dimensão_2
Dimensão_1
Dimensão_4
Têm de ser conformes
Data Warehousing e OLAP 15
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
85Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Factos não aditivos (ou semi-aditivos)
• Os factos não são aditivos ao longo de uma ou maisdimensões quando a sua soma não tem significado real (masa média já pode ter, pelo que o facto é útil).
• 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.
• 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”)
86Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Grandes dimensões
• Em certas situações dimensões como Produtos ouClientes podem ter milhões de registos;
• É muito frequente estas dimensões terem até umacentena de atrubutos;
Pode ser interessante normalizarparcialmente estas dimensões
87Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Flocos de neve
ProdutoID_produtoNúmeroNomeID_Tam_embID_marcaPesoUnidade_de_pesoQuantidade_caixaCaixas_por_palleteLargura_prateleiraAltura_prateleiraProfud_prateleira……...
ID_Tam_embTipo_embalagID_marca
ID_marcaCategoriaID_subcat
ID_subcatCategoriaID_dept
ID_DeptDepartamento
• Uma dimensão pode ter múltiplas hierarquias(flocos de neve);
• Uma hierarquia consiste numa cadeia de típicos relacionamentos 1 para N
88Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Vantagens e desvantagens de flocos
• Vantagens– Economiza espaço;
• Desvantagens– Aumenta o tempo de resposta a queries;– Torna a construção das queries mais complexa.
Por muito grande que seja uma dimensão, ela representasempre uma percentagem pequena da espaço ocupado
pela tabela de factos, pelo que estruturar uma dimensãoem flocos de neve raramente se justifica
89Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Mini-dimensõesDemográfica
ID_demografFaixa_etáriaNível_rendimentoEstado_civílSexoHábitos_consumo
ID_dataID_demografID_clienteID_produtoID_promoçãoUnid_vendidasCusto_compraValor_venda
ID_clienteNomeApelidoRuaCidadeCódigo_postalID_demograf……...
Clientes
Vendas
• As combinações possíveis relativas a demografia (neste exemplo) ficam numatabela própria.
• Permite economizar espaço e ganharvelocidade;
90Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Alterações (actualizações) em dimensões
O que fazer quando é necessário actualizar um dado registo de uma dimensão?
Três alternativas:1) Escrever por cima (perde-se a história);
2) Inserir um registo novo na dimensão com os valoresactualizados;
3) Ter atributos na dimensão que permitam registar a evolução no tempo.
Data Warehousing e OLAP 16
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
91Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
1ª Escrever por cima
ID_clienteNomeApelidoRuaCidadeCódigo_postalSexoData_nascimentoEstado_civilRendimento_médio……...
Clientes
ID_dataID_clienteID_produtoID_promoçãoUnid_vendidasCusto_compraValor_venda
Vendas
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).
Um dado cliente alterouo estado_civil
92Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
2ª Novo registo na tabela da dimensão
ID_clienteNomeApelidoRuaCidadeCódigo_postalSexoData_nascimentoEstado_civilRendimento_médio……...
Clientes
ID_dataID_clienteID_produtoID_promoçãoUnid_vendidasCusto_compraValor_venda
Vendas
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).
Um dado cliente alterouo estado_civil
93Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Chaves com estrutura
ID_Cliente Nome Apelido … Estado_civil ….
145023-000 Ana Maria Silva … Solteira
………………………………………………………………………..
145023-001 Ana Maria Silva Casada
Reserva-se três dígitos, por exemplo, para identificar sucessivasalteraçõ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.
Cliente
94Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
3ª Atributos para registar alterações
ID_clienteNomeApelidoRuaCidadeCódigo_postalSexoData_nascimentoEstado_civil_originalEstado_civil_actual……...
Clientes
ID_dataID_clienteID_produtoID_promoçãoUnid_vendidasCusto_compraValor_venda
Vendas
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.
Um dado cliente alterouo estado_civil
95Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Exemplo 3: DW de um Banco
Objectivo
Vender melhor os seus produtos e oferecer serviços adicionais aos clientes que já
possuem conta(s).
96Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Exemplo 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;
• Cada tipo de conta tem – 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.
Data Warehousing e OLAP 17
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
97Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Exemplo 3: DW de um banco: possíveis dimensões
• Dimensões:– Conta – Cliente– Agência– Produto– Estado– Tempo
• Granularidade:– Conta por mês.
98Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
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 :
• 10 milhões de contas e 3 milhões de clientes
99Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Exemplo 3: dimensões PRODUTO e ESTADO
• Dimensão PRODUTO– contêm os atributos usados para descrever todos os
produtos do banco;– hierarquia: nome_do_produto → tipo → categoria;
• Dimensão ESTADO– útil para gravar o estado da conta. Conta activa ou
inactiva.
100Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Exemplo 3: esquema da DW de um banco
ID_clienteNome_clienteEndereçoLocalidadeRendimentoTipo_cliente
ID_contaID_clienteID_tempoID_agênciaID_produtoID_estadoSaldo_primárioNºtransacções
Cliente
ContaID_contaTitularSeg_titularEndereçoLocalidadeData_aberturaD_nasc_titularSexo_titularEst_civil_titular
AgênciaID_agênciaNomeEndereçoLocalidadeTipo_agência
TempoID_tempoMêsAnoTrim_fiscal
ProdutoID_produtoDescriçãoTipoCategoria
EstadoID_estadoDescriçãoMotivoFlag_conta_novaFlag_conta_fechada
Factos_banco
101Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Exemplo 3: dimensões “sujas”
ID_clienteNome_clienteEndereçoLocalidadeRendimentoTipo_ cliente
ID_contaID_clienteID_tempoID_agênciaID_produtoID_estadoSaldo_primárioNºtransacções
Cliente
ContaID_contaTitularSeg_titularEndereçoLocalidadeData_aberturaD_nasc_titularSexo_titularEst_civil_titular
Factos_banco
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%.
102Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Exemplo 3: produtos heterogéneos
ID_contaID_hipotecaID_tempoID_agênciaID_produtoID_estadoSaldo_primárioNºtransacções(factos contas à ordem)(factos depósitos a prazo)(factos planos poupança)(factos cartões de crédito)……………………...
ProdutoID_produtoDescriçãoTipoCategoria(atributos contas à ordem)(atributos depósitos a prazo)(atributos planos poupança)(atributos cartões de crédito)………………………..
Factos_banco
Este tipo de situações leva a que as tabelas de factos e da dimensãoprodutos tenham muitos valores nulos para cada registo.
Data Warehousing e OLAP 18
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
103Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Solução para a heterogenidade numa dimensão
ID_contaID_hipotecaID_tempoID_agênciaID_produtoID_estadoSaldo_primárioNºtransacções
ProdutoID_produtoDescriçãoTipoCategoria
Factos_banco
ID_contaID_hipotecaID_tempoID_agênciaID_produto_ordemID_estadoSaldo_primárioNºtransacções(factos contas à ordem)
Factos_Contas_ordemProduto_contas_ordemID_produto_ordemDescriçãoTipoCategoria(atributos contas à ordem)
Factos e dimensão nuclearesreferentes a todos os produtos
ID_contaID_hipotecaID_tempoID_agênciaID_produto_prazoID_estadoSaldo_primárioNºtransacções(factos contas a prazo)
Factos_Contas_PrazoProduto_contas_prazoID_produto_prazoDescriçãoTipoCategoria(atributos contas a prazo)
Factos e dimensõesreferentes a produtosespecíficos
104Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Tabelas de factos sem factos
Factos_escolaID_disciplinaID_alunoID_tempoID_professorID_sala
Disciplina
Aluno
Professor
SalasTempo
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 assinaladapelo conjunto das chaves
105Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Tabelas de factos sem factos: exemplo
Factos_hospitalID_hospitalID_médicoID_diagnósticoID_doenteID_procedimentoID_tempo
Hospital
Médico
Doente
TempoDiagnóstico
Procedimento
E muitas outras situações onde é necessário registar apenas ocorrênciasou eventos.Se houver necessidade de registar outros factos então a tabela de factosjá conterá atributos numéricos descrevendo esses factos.
106Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Representar eventos que não aconteceram
• 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?
Tempo
Factos_vendas
ID_dataDiaDia_da_semanaSemana_do_anoMêsTrimestreAno…...
Produto
ID_produtoNomeTipoMarcaCategoriaEmbalagemDescrição……
Loja
ID_lojaNomeLocalidadeDistritoÁreaNº_Caixas…...ID_data
ID_produtoID_lojaID_promoUnid_vendidasCusto_compraValor_vendaNº_Clientes
Promoções
ID_promoNúmeroTipo_promoData_inícioData_fim…...
107Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Tabelas de cobertura
Indicam, para o caso do exemplo das lojas, que artigos estão empromoção, qual a promoção, em que lojas e durante quanto tempo.
TempoPromoções
Produto
Loja
ID_dataID_produtoID_lojaID_promo
Promoções
As tabelas de coberturas registam eventos: não têm factos
A granularidade do tempo poderá, eventualmente, ser semanal
108Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Cadeia de lojas, com cobertura de promoções
Tempo Factos_vendas
ID_dataDiaDia_da_semanaSemana_do_anoMêsTrimestreAno…...
Produto
ID_produtoNomeTipoMarcaCategoriaEmbalagemDescrição……
Loja
ID_lojaNomeLocalidadeDistritoÁreaNº_Caixas…...
ID_dataID_produtoID_lojaID_promoUnid_vendidasCusto_compraValor_vendaNº_Clientes
Promoções
ID_promoNúmeroTipo_promoData_inícioData_fim…...
PromoçõesID_dataID_produtoID_lojaID_promo
Data Warehousing e OLAP 19
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
109Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
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”?
– Quantos clientes é que têm ? Como é que os agrupam ? …
• 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 ? . . .
110Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
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 ?
– Onde é que os dados começam a ser produzidos ?
• 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 ?
111Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Nove decisões na construção de uma DW1. Identificar processos de negócio2. Identificar factos de cada processo (tabela de factos)3. Definir a granularidade de cada tabela de factos4. Identificar as dimensões para cada tabela de factos5. Definir os atributos das dimensões6. Estimar tamanho e confirmar granularidade7. Decidir como resolver as alterações nas dimensões8. 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 DW10. A periodicidade em que os dados das diferentes estrelas devem ser
carregados.112Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Aspectos básicos relativos ao desempenho
113Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Como acelerar as respostas a queries?• Bom projecto lógico dos esquemas em estrela
• Bom projecto físico (as coisas básicas)– 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
114Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Agregados
Resultados pré-calculados que são armazenadoscom o objectivo de acelerar a resposta a queries
Exemplos (considerando a DW de cadeia de lojas):
• 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.
Data Warehousing e OLAP 20
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
115Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
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.
• Problemas fundamentais:– 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 pelosutilizadores em cada momento e eliminar agregados que se tornaramdesnecessários;
– Ocupam espaço em disco.
116Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Armazenamento de agregados
• Um agregado é um registo (de factos) representando umasumarização (um cálculo) de um conjunto de factos base;
• Um agregado está sempre associado com uma ou maisdimensões agregadas.
Exemplo:Totais relativos a categoria de produto, por loja e por dia;
Dimensõesoriginais
Dimensão agregada(substitui a dimensão produto)
Factos(cálculo de totais
por categoria)
117Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Exemplo de agregados: cadeia de lojas
TempoID_dataDia_do_mêsDia_da_semanaDia_do_anoSemana_do_anoMêsNúmero_do_mêsTrimestrePeríodo_fiscalFlag_feriadoFlag_dia_semanaFlag_últ_dia_mêsEstação_anoAconteci_espec……….
ProdutoID_produtoNúmeroNomeMarcaCategoriaSubcategoriaDepartamentoTam_embalagemTipo_embalagemTipo_dietaPesoUnidade_de_pesoQuant_caixaCaixas_p_palleteLarg_prateleiraAltura_prateleiraProfun_prateleira……...
LojaID_lojaNomeNúmero_lojaEndereçoLocalidadeCódigo_postalDistritoRegiãoTelefoneFaxGestor_lojaÁrea_totalÁrea_merceariasÁrea_congeladosÁrea_bazarNº_CaixasData_inauguraçãoData_ult_remod.……...
ID_dataID_produtoID_LojaID_PromoçãoUnid_vendidasCusto_compraValor_vendaNº_clientes
ID_promoçãoNúmeroNome_promoTipo_red_preçoTipo_anúncioTipo_cartazTipo_couponsMeio_anúncioMeio_cartazCusto_promoçãoInício_promoçãoFim_promoção……...
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?118Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Agregados na cadeia de lojas: tabelas de factos
São necessárias tantas tabelas de factos agregados quantascombinações de dimensões agregadas
1 - Totais por categoria, por loja, por dia
2 - Totais por distrito, por produto, por dia
3 - Totais por mês, por produto, por loja
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
119Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Aspecto final: factos e agregados
• Estrela base: tabela de factos + 4 dimensões
• 3 dimensões agregadas– Categoria;– Distritos;– Meses.
• 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
Estas tabelas nãosão visíveis para o utilizador final.
120Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Nº 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
• Quantas dimensões agregadas?• 7 Dimensões agregadas.
• Quantas tabelas de factos agregados?
• 35 tabelas de factos agregadas.
Data Warehousing e OLAP 21
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
121Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
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
Tabela de factos de agregados por
categoriaID_dataID_categoriaID_LojaID_PromoçãoUnid_vendidasCusto_compraValor_vendaNº_clientes
Dimensão LojaDimensão Promoções
ID_categoriaCategoriaDepartamento
Dimensão categoria (agregada)
Em vez de criar um agregadoseparado para departamento é usado o agregado por categoriaspara acelerar a resposta
122Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Técnicas para armazenamento de agregados
1) Armazenados em novas tabelas de factos e dimensões agregadas (o método usado normalmente)
2) Atributos de nível: os agregados são armazenadosna tabela de factos base, com a introdução de atributos de nível nas dimensões.
123Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Método dos atributos de nível
• Os agregados são armazenados na mesma tabela dos factos base;
• As dimensões a agregar são aumentadas para conterum atributo de nível:
• O atributo de nível indica o nível de agregação de cadaregisto na tabela da dimensão:– Os registos originais têm Nível = Base;– Os agregados por categoria têm Nível = Categoria;– etc
124Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Exemplo: método dos atributos de nívelDimensão produto
aumentadaID_produtoNivelNúmeroNomeMarcaCategoriaSubcategoriaDepartamentoTam_embalagemTipo_embalagemTipo_dietaPesoUnidade_de_pesoQuant_caixaCaixas_p_palleteLarg_prateleiraAltura_prateleiraProfun_prateleira……...
ID_dataID_produtoID_LojaID_PromoçãoUnid_vendidasCusto_compraValor_vendaNº_clientes
Factos de vendas
Registo originais:• Nivel = ‘Base’
• Todos os atributos preenchidos com valores originais
Registos correspondentes ao agregado por Categoria• Nivel = ‘Categoria’
• Todos os atributos preenchidos com “Não Disponível”
125Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Contagem duplaDimensão produto
aumentadaID_produtoNivelNúmeroNomeMarcaCategoriaSubcategoriaDepartamentoTam_embalagemTipo_embalagemTipo_dietaPesoUnidade_de_pesoQuant_caixaCaixas_p_palleteLarg_prateleiraAltura_prateleiraProfun_prateleira……...
ID_dataID_produtoID_LojaID_PromoçãoUnid_vendidasCusto_compraValor_vendaNº_clientes
Factos de vendas
Contagem dupla:Se uma query restringir apenas Categoria = ‘Bebida’ serãoincluí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
126Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Comparação dos dois métodos
• O número de registo criados pelos agregados é o mesmo emqualquer dos métodos;
• Tabelas separadas:– As tabelas correspondentes ao agregados não são visíveis para o utilizador
final. Normalmente existe uma camada de software (agregate navigator) queoptimiza a utilização dos agregados para cada query;
– Agregados em tabelas separadas podem ser facilmente criados, apagados, carregados e indexados;
• 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.
Data Warehousing e OLAP 22
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
127Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Agregados e esparsidão
• Os agredados são muito menos esparsos do que os dados base;
• O espaço necessário para o seu armazenamento é um problema sério;
• O administrador deve gerir cuidadosamente os agregados.
128Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Vistas materializadas (Oracle)
CREATE MATERIALIZED VIEW sales_summaryBUILD IMMEDIATEREFRESHCOMPLETEON DEMAND
ENABLE QUERY REWRITEASSELECT i.ord_ord_id AS order_id,MAX(TO_CHAR(orderdate,'MonthYYYY')) AS orderdate,SUM(i.quantity * p.unitprice) AS totalFROM orders o, items i, parts pWHERE o.ord_id = i.ord_ord_idAND p.part_id = i.part_part_idGROUP BY i.ord_ord_id;
129Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Vistas materializadas (cont.) (Oracle)
• Refrescar as estatísticas do optimizadorBEGIN
dbms_utility.analyze_schema('SALES_APP','ESTIMATE',15);END;
• Refrescar as vistas materializadasBEGIN
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.
130Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Índices em Data Warehousing
131Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Índices em DW
• Índices B-Tree (já estudados)
Atributos de elevada cardinalidade
• Índices Bit-mapAtributos de baixa cardinalidade
132Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Estrutura de um índices B-Tree: breve revisão
Martins
Bento Justino Matos Tavares
Álvares ROWID
Antunes ROWID
Bento ROWID
Canelas ROWID
Ferreira ROWID
Justino ROWID
Lemos ROWID
Martins ROWID Matos ROWID
Soares ROWID
Tavares ROWID
Teixeira ROWID
Ventura
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
Os índices são armazenados como B*-trees
Data Warehousing e OLAP 23
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
133Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Como funciona um índice B-Tree
Martins
Bento Justino Matos Tavares
Álvares ROWID
Antunes ROWID
Bento ROWID
Canelas ROWID
Ferreira ROWID
Justino ROWID
Lemos ROWID
Martins ROWID Matos ROWID
Soares ROWID
Tavares ROWID
Teixeira ROWID
SELECT *FROM EmpregadosWhere 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
134Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Porque é que os índices B-Tree são insuficientes?
TempoTempoID_data
Dia_do_mêsDia_da_semanaDia_do_anoSemana_do_anoMêsNúmero_do_mêsTrimestrePeríodo_fiscalFlag_feriadoFlag_dia_semanaFlag_últ_dia_mêsEstação_anoAconteci_espec……….
ProdutoProdutoID_produtoNúmeroNomeMarcaCategoriaSubcategoriaDepartamentoTipo_embalagemTipo_dietaPesoUnidade_de_pesoQuant_caixaCaixas_p_palleteLarg_prateleiraAltura_prateleiraProfun_pratelei……...
LojaLojaID_lojaNomeNúmero_lojaEndereçoLocalidadeCódigo_postalDistritoRegiãoTelefoneGestor_lojaÁrea_totalÁrea_merceariasÁrea_congeladosÁrea_bazarNº_CaixasData_inauguraçãoData_ult_remod.……...
ID_produtoID_dataID_LojaID_PromoçãoUnid_vendidasCusto_compraValor_vendaNº_clientes
ID_promoçãoNúmeroNome_promoTipo_red_preçoTipo_anúncioTipo_cartazTipo_couponsMeio_anúncioMeio_cartazCusto_promoçãoInício_promoçãoFim_promoção……...
PromoçãoPromoção
Atenção às cardinalidades!!!
135Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Índices Bit-map
ID_Cliente Sexo Distrito Estado_civil Rendimento145023 M Coimbra Casado A
145025 M Faro Solteiro B
154265 F Guarda Casado A
265453 M Faro Solteiro C
645654 F Beja Solteiro A
Cliente
Índice B-tree Possíveis colunas para índices Bit-map
136Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Exemplos de índices Bit-map
Sexo = ‘M’ Sexo = ‘F’ Rendimento = ‘A’ Rendimento = ‘B’ Rendimento = ‘C’ 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
Í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.
137Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Execução de queries usando índices Bit-map
Sexo = ‘M’ Rendimento = ‘A’ Rendimento = ‘B’
1 1 0
1 0 1
0 AND 1 OR 0
1 0 0
0 1 0
SELECT COUNT(*) FROM Cliente WHERE Sexo = ‘M' AND Rendimento IN (‘A',‘B');
138Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Como indexar um esquema em estrela?
TempoTempoID_dataDia_do_mêsDia_da_semanaDia_do_anoSemana_do_anoMêsNúmero_do_mêsTrimestrePeríodo_fiscalFlag_feriadoFlag_dia_semanaFlag_últ_dia_mêsEstação_anoAconteci_espec……….
ProdutoProdutoID_produtoNúmeroNomeMarcaCategoriaSubcategoriaDepartamentoTam_embalagemTipo_embalagemTipo_dietaPesoUnidade_de_pesoQuant_caixaCaixas_p_palleteLarg_prateleiraAltura_prateleiraProfun_prateleira……...
LojaLojaID_lojaNomeNúmero_lojaEndereçoLocalidadeCódigo_postalDistritoRegiãoTelefoneFaxGestor_lojaÁrea_totalÁrea_merceariasÁrea_congeladosÁrea_bazarNº_CaixasData_inauguraçãoData_ult_remod.……...
ID_produtoID_dataID_LojaID_PromoçãoUnid_vendidasCusto_compraValor_vendaNº_clientes
ID_promoçãoNúmeroNome_promoTipo_red_preçoTipo_anúncioTipo_cartazTipo_couponsMeio_anúncioMeio_cartazCusto_promoçãoInício_promoçãoFim_promoção……...
PromoçãoPromoção
Data Warehousing e OLAP 24
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
139Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Planos de execução de queries e métodos de acesso específicos para
Data Warehousing
140Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Execução de queries
Planos de execução de queriesSequê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.
Parser
Optimizador
Execução
Query
Query parsed
Plano de execução
Resultados
141Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Optimizador de queries
Para definir os planos o optimizador considera muitas coisas:– 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
142Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Planos de execução de queries
Duas grandes abordagens para definir planos- Baseado em regras
- Baseado em estimativas de custos a partir de estatísticas
Actualmente os planos baseados em estimativas de custos são os mais usados.
143Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
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- Estão ordenadas (da melhor para a menos boa)- 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.144Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Planos baseados em custos
O optimizador faz o seguinte:1. Gera vários planos alternativos.
2. Estima os custos para cada plano baseados nos recursos necessários para executar o plano (I/O, CPU, memória, ...).
3. Compara os custos de cada plano possível e escolhe o que tem menor custo.
Data Warehousing e OLAP 25
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
145Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Planos baseados em custos (diagrama)
Parser
Gerador de planos
Execução
Query
Query parsed
Plano de execução
Resultados
Estimador de custos
Gestor de catálogo
Optimizador
Avaliador de planos
146Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Ojectivos e meios dos planos baseados em custos
• Objectivos:• 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.
• 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)• ...
147Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Planos baseados em custos e DW
• Não há actualizações, pelo que a manutenção das estatísticas é pequena.
• A actualização das estatísticas pode ser feita a seguir aos carregamentos periódicos.
• Queries muito complexas, pelo que a análise de custos permite grandes optimizações.
148Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Métodos de acesso aos dados• Todos os dados estão em tabelas.
• Não é difícil encontrar os dados numa base de dados; o problema é fazê-lo da maneira mais eficaz.
• 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.
149Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Métodos de junções para DW
• Star join
• Star join em Oracle
• Oracle star transformation
150Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Star Join
factosPKs
factosPKs
factosPKs
factos
Dim 1 Dim 2
Dim 3Dim 4
ji ji
jiji
1 2
3
4
5
6
7
Conjunto temporáriode chaves para factos8
• Tem de existir um bit-mapjoin index (semelhante a um bit-map) entre a tabela de factos e cada uma das dimensões.
• A query é executada começando pelas dimensões e encontrando as suas entradas nos join indexes.
• 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).
Data Warehousing e OLAP 26
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
151Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Star Join em Oracle (Oracle star query)
I n c l u i c o n j u n t otemporário de chavespara factos
linhasde Dim 4 producto
cartesiano
linhasde Dim 3
productocartesiano
linhasde Dim 2
linhasde Dim 1
factos
Dim 1 Dim 2
Dim 3Dim 4
1 2
3
4
5
6
7
8
productocartesiano
• O Oracle não tem join indexpelo que o método é realizado fazendo o produto cartesiano entre as linhas seleccionas de cada dimensão.
• Pode ser muito menos eficiente do que o star joinoriginal porque os produtos cartesinos podem conter muitas linhas.
152Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Oracle star transformation
• Destina-se a resolver os casos em que no Oraclestar query os produto cartesianos dos registos selecionados nas dimensões são muito grandes.
• Requer um índice bit-map em cada uma das colunas de chave estrangeira na tabela de factos.
• Estes índices são combinados de modo a encontrar os registos pretendidos na tabela de factos (o que é muito semelhante ao star join original).
153Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Oracle start transformation: execução de queries
SELECT f.*FROM factos f, dim1 d1, dim2 d2, dim3 d3, dim4 d4WHERE f.fk1 = d1.pk /* junção */
AND f.fk2 = d2.pk /* junção */AND f.fk3 = d3.pk /* junção */AND f.fk4 = d4.pk /* junção */AND d1.atr1 = 'aaa' /* restrição */AND d2.atr2 = 'ccc' /* restrição */AND d3.atr3 = 'eee' /* restrição */AND d4.atr4 = 'ggg'; /* restrição */
SELECT f.*FROM factos fWHERE f.fk1 IN (SELECT pk FROM dim1 WHERE atr1 = 'aaa')AND f.fk2 IN (SELECT pk FROM dim2 WHERE atr2 = 'ccc')AND f.fk3 IN (SELECT pk FROM dim3 WHERE atr3 = 'eee')AND f.fk4 IN (SELECT pk FROM dim4 WHERE atr4 = 'ggg');
Este método obriga à reescrita das queries. Por exemplo:
é transformada em
154Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Particionamento
155Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Particionamento de tabelas e índices
• Decompõe as tabelas é bocados mais pequenos chamados partições
• Muito útil para gerir tabelas (e índices) muito grandes
• Uma vez definidas, os comandos SQL podem manipular as partições em vez da tabela inteira.
• 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.
156Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Particionamento de tabelas e de índices (cont.)
Em quase todos os SGBD o particionamento de tabelas e índices pode-se combinar livremente. Exemplos:– 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).
Data Warehousing e OLAP 27
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
157Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
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)
– Muito melhor gestão dos discos.
• 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.
158Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Particionamento horizontal e vertical
• Horizontal: – 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.
159Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Chave de particionamento (horizontal)
• Cada registo numa tabela particionada tem de ser associado a uma (e só uma) partição.
• 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.
160Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Métodos de particionamento horizontal
• Particionamento por gama de valores (range partitioning)
• Particionamento por lista explícita (list partitioning)
• Particionamento uniforme por chave (hash partitioning)
• Sub-particionamento (composite partitioning)– Range-hash partitioning– Range-list partitioning
161Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Particionamento por gama de valores(range partitioning)
• As partições são criadas de acordo com gamas de valoresespecificadas para um dado atributo (ou conjunto de atributos).
• Pressupõe que os valores do atributo usado para particionamentoforma um conjunto ordenado.
• Muito útil quando os dados se distribuem naturalmente em gamas de valores (e.g., meses do ano, faixas etárias, etc).
• 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
162Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Exemplo de range partitioning (Oracle)
CREATE TABLE sales_range(salesman_id NUMBER(5),salesman_name VARCHAR2(30),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')));
A tabela é criada em quantas partições?
Método de particionamento
Atributo de particionamento
Definição das fronteirasque definem cada partição
Em que tablespaces são criadas as partições?
Data Warehousing e OLAP 28
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
163Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Exemplo de range partitioning com vários atributos(Oracle)
CREATE TABLE sales( invoice_no NUMBER,sale_year INT NOT NULL,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 );
Múltiplos atributos de particionamento
Indicação explícita do tablespace onde fica a partição
164Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
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 nemtem relação entre si (as partições são indicadas explicitamente).
• Só se pode usar um atributo para definir a lista (Oracle)
• Os bons (ou menos bons) resultados no que toca à distribuiçãouniforme 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
165Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Exemplo de list partitioning (Oracle)
CREATE TABLE sales_list(salesman_id NUMBER(5),salesman_name VARCHAR2(30),sales_state VARCHAR2(20),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));
Método de particionamento
Definição explícitado valores
Partição por defeito para quandoos dados não correspondem a
nenhum dos valores especificados
Atributo de particionamento
166Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
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.
• Muito útil nas seguintes situações:– 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.
167Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Exemplo de hash partitioning (Oracle)
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 4STORE IN (data1, data2, data3, data4);
Método de particionamento
Tablespaces onde as partições ficam armazenadasNúmero de partições
Atributo a que é aplicada a função
de hash
168Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Sub-particionamento (Oracle)
• O método de particionamento base é range:– Range + hash partitioning– Range + list partitioning
• Ú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ãousados em particionamento normal:– hash para particionamento regular;– list para controlar específicamente as partições.
Data Warehousing e OLAP 29
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
169Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Exemplo de range-hash partitioning (Oracle)
CREATE TABLE sales_composite(salesman_id NUMBER(5),salesman_name VARCHAR2(30),sales_amount NUMBER(10),sales_date DATE)
PARTITION BY RANGE(sales_date)SUBPARTITION BY HASH(salesman_id)SUBPARTITION TEMPLATE(SUBPARTITION sp1 TABLESPACE data1,SUBPARTITION sp2 TABLESPACE data2,SUBPARTITION sp3 TABLESPACE data3,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')));
Método de particionamentoprimário (range)
As sub-partições e respectivos tablespaces sãoindicados por um template
Chave para o particionamento
primário
Chave para o sub-particionamento
(hash)
Definição das partições primárias170Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Exemplo de range-list partitioning (Oracle)
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')));
171Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Particionamento de índices (Oracle)
• Global indexes: particionados independentementedas tabelas (bons resultados em bases de dados operacionais).
• Local indexes: o particionamento é associado àspartições definidas para as tabelas (são estes osmais usados em data warehousing).
172Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Discos RAID Redundant Arrays of Inexpensive Disk
173Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
RAID - Redundant Arrays of Inexpensive Disk
Objectivos de estudo:– Entender os problemas e as limitações do sistema de
discos numa base de dados;
– Conhecer os principais conceitos da tecnologia RAID;
– 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.
174Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Evolução da velocidade de processadores
In “Computer Architecture: A Quantitative Approach”, J. Hennessy and D. Patterson, Morgan Kaufmann Publishers, Inc. 1996.
Tempo
Velocidade
Data Warehousing e OLAP 30
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
175Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Evolução dos discos (custo e velocidade)
In “Computer Architecture: A Quantitative Approach”, J. Hennessy and D. Patterson, Morgan Kaufmann Publishers, Inc. 1996.
O tempo de acesso médio baixou pouco, pois há limites mecanicosà sua melhoria
Tempo
Velocidade
Custo MBytes
176Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Requisitos do armazenamento de dados
Baixo custo
Grandevelocidade
Grandedisponibilidade
177Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Alguns factos acerca dos acessos a discos
• O acesso às diferentes áreas do disco não é uniforme;
• Regras 80/2080% 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
178Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Distribuição dos ficheiros por vários discos
Problemas• É difícil, por vezes, identificar
os ficheiros com mais acessos;
• Os ficheiros com mais acessos variam com o tempo.
Controlador de Disco
Controlador de Disco
Bus de E/SBus de E/S
Concentração de hot spots
Melhora-se a velocidade de acesso distribuindo os fichei-ros por diversos discos.
179Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
• Melhora (potencialmente) a velocidade;• Aumenta a capacidade a custos reduzidos;
mas• 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
Consequências de ter vários discos
180Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Controlador do disco
O controlador tem um papel muito importante na disponibilidade na fiabilidade e na velocidade.
Controlador de Disco
Controlador de Disco
Bus de E/SBus de E/S
Contr. DiscoContr. Disco
Bus de E/SBus de E/S
Contr. DiscoContr. Disco
Contr. DiscoContr. Disco
Ponto único de falha
Data Warehousing e OLAP 31
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
181Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
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
D G
B
E HC
F I
A B C D E F G
Disco 1 Disco 2 Disco 3
Velocidade do acesso (bytes/seg) =
vel. de acesso de 1 disco * N
N discos
182Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Consequências da utilização de striping
Melhora a velocidade de acesso a disco, mas…
• A probabilidade de falha num dos discos aumenta proporcionalmente ao número de discos;
• Uma falha num disco leva à perda de dados em todo o conjunto.
183Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Atributos de um RAID
• Conjunto de discos físicos vistos pelo utilizador como um único disco lógico;
• Os dados são distribuídos pelos diferentes discos físicos de um modo bem definido;
• 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.
184Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Tipos (níveis) de RAID
• A proposta original de RAID apresentava 5 alterantivaspara a utilização de arrays de discos, designadas por níveis: RAID 1 a RAID5;
• Foram, posteriormente, acrescentados dois níveis: RAID 0 e RAID 6;
• Cada nível de RAID corresponde a um diferente compromisso do triângulo
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
Baixo custo
Grandevelocidade
Grandedisponibilidade
185Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
C
RAID 0 - striping sem redundância
A
E I
B
F JC
G L
A B D E F G
Disco 1 Disco 2 Disco 3
D
H M
Disco 4
Velocidade
Disponibilidade
Custo
Pode ter um número de discos qualquer
Do ponto de vista do utilizador corresponde a um único disco
186Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Tamanho escolhido para striping
A escolha do tamanho das porções usadas para striping (chunks) é muito importante para a velocidade de acesso
• Grande
• Pequeno
comparando com o tamanho médio de dados trocados em cada acesso
Data Warehousing e OLAP 32
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
187Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Chunks grandes
Chunk grandes são bons para:– acesso muito frequentes;– pequena quantidade de dados trocada em cada acesso.
A
E I
B
F JC
G L
A B C D E F G
Disco 1 Disco 2 Disco 3
D
H M
Disco 4
A maior parte dos acesso re-duzem-se a um único chunk.
Os outros discos estão livres para ser acedidos
188Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Chunks pequenos
Chunk pequenos são bons para:– acesso pouco frequentes;– grande quantidade de dados trocada em cada acesso.
A
E I
B
F JC
G L
A B C D E F G
Disco 1 Disco 2 Disco 3
D
H M
Disco 4
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.
Bases de Dados com transações longas
189Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
RAID 1 - Discos duplicados
Primeira (e óbvia) abordagem para tolerar falhas em discos
A
B C
A
B C
A B C D E F G
Disco 1 Disco 2
Do ponto de vista do utilizador corresponde a um único disco
Cada disco contém uma cópia do outro. As leituras podem ser aceleradas lendo metade dos dados de cada disco.
Velocidade
Disponibilidade
Custo
190Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
A C D
RAID 1 - Discos duplicados com striping
• 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.
A
C
A
C
B
D
B E F G
Disco 1 Disco 2 Disco 3
B
D
Disco 4
Os dados são distribuídos por dois pares de discos
191Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
RAID 2 - Códigos de detecção/correcção
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.
Códigos de detecção/correcção(paridade, Hamming, …)
Transaposição dos mecanismos de detecção e correcção de erros usados na memória para os discos
Dados
192Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
C
RAID 3 - Paridade do tipo bit-interleaved
A
D G
B
E HC
F I
A B D E F G
Disco 1 Disco 2 Disco 3 Disco 4
Do ponto de vista do utilizador corresponde a um único disco com grande velocidade e fiabilidade
A + B + C
D + E + F
• 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.
Velocidade
Disponibilidade
Custo
Mas o controlador é caro
Data Warehousing e OLAP 33
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
193Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
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;
• 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.
194Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
RAID 4 - Paridade do tipo bloco- entremeado
• Identico a RAID 3 mas com o tamanho do chunk maior (um sector, tipicamente);
• Melhora a velocidade de leitura nos casos em que há leituras muito frequentes mas com poucos dados a ser lidos de cada vez.
C
A
D G
B
E HC
F I
A B D E F G
Disco 1 Disco 2 Disco 3 Disco 4
A + B + C
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
195Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Acessos de leitura num RAID 4
Em circunstâncias normais, i.e., sem discos avariados,
as leituras são feitas apenas aos sectores onde está a informação.
Pode haver várias leituras em simultaneo se os acessos forem de poucos dados;
196Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Escritas – 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
Acessos de escrita num RAID 4
Uma escrita envolve sempre duas leitura e duas escritas
Como só há um disco de paridade só pode haver uma escrita de cada vez.
197Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
RAID 5 - Paridade bloco-entremeado rotativa
• Semelhante a RAID 4, mas com a diferença que os chunks contendo a paridade estão distribuídos pelos discos (Rotating Parity Array);
• 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).
198Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1RAID 5
C
A
D G
B
E HC
FI
A B D E F G
Disco 1 Disco 2 Disco 3 Disco 4
A + B + C
D + E + FG + H + I
Permite várias escritas simultâneas porque a informação de paridade está espalhada por todos os discos
Velocidade Disponibilidade CustoMas o controlador é mais caro
Data Warehousing e OLAP 34
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
199Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Falhas em RAID 5
• O sistema interrompe a actividade normal;
• Todos os discos são lidos;
• O conteúdo do novo disco é reconstruído;
• O sistema volta a funcionar normalmente.
Usando controladores sofisticados é possível fazer a recuperação mantendo o sistema a funcionar.
200Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
RAID 6 - Redundância dupla
C
A
C
B
DE
A B D E F G
Disco 1 Disco 2 Disco 3 Disco 4
P(A,B)Q(A,B)
• 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.
P(C,D) Q(C,D)
F
Velocidade
Disponibilidade
Custo
201Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Acessos em RAID 6
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).
Tal como no RAID 5 o chunk é grande, pelo que favorece acessos curtos mas muito frequentes.
202Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Níveis de RAIDNível 0
1
2
3
4
5
6
Sem redundância
Discosduplicados
Códigos de detecção/correcção
Paridade do tipo bit-entremeado
Paridade do tipo bloco-entremeado
Paridade bloco-entremeado rotativa
Redundância dupla
203Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Área de Estágio: Extracção, Transformação e Transporte
(ETT)
204Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Qual a estratégia para ETT?
BDs operacionais
Sistemas legados
Folhas de cálculo, ficheiros, ...
Fontes externas
Área de estágio
ClientesTempo
Produtos
Promoções
Lojas
Factos vendas
Data Warehousing e OLAP 35
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
205Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Passos no processo de ETTI. Planificação
1. Definir um plano geral (do tipo end-to-end)2. Definir infraestrutura para a área de estágio3. Escolher as ferramentas ETT4. Fazer plano detalhado analisando todos os problemas que é necessário
resolver para carregar cada tabela destino (e.g., fontes, transformações, etc)
II. Carregamento de dimensões1. Fazer, testar e executar planos ETT para as dimensões estáticas e simples.
Permite testar toda a infraestrutura.2. Fazer, testar e executar planos ETT para as dimensões que mudam.3. Tratar todos os restantes casos (dimensões geradas, com dados manuais, etc)
III. Carregamento de factos1. Fazer, testar e executar planos ETT para tabelas de factos2. Fazer e testar processo de carregamentos periódicos
IV. Automatizar o processo ao máximo206Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Exemplo: ETT numa DW de cadeias de lojas
TempoTempoID_dataDia_do_mêsDia_da_semanaDia_do_anoSemana_do_anoMêsNúmero_do_mêsTrimestrePeríodo_fiscalFlag_feriadoFlag_dia_semanaFlag_últ_dia_mêsEstação_anoAconteci_espec……….
ProdutoProdutoID_produtoNúmeroNomeMarcaCategoriaSubcategoriaDepartamentoTam_embalagemTipo_embalagemTipo_dietaPesoUnidade_de_pesoQuant_caixaCaixas_p_palleteLarg_prateleiraAltura_prateleiraProfun_prateleira……...
LojaLojaID_lojaNomeNúmero_lojaEndereçoLocalidadeCódigo_postalDistritoRegiãoTelefoneFaxGestor_lojaÁrea_totalÁrea_merceariasÁrea_congeladosÁrea_bazarNº_CaixasData_inaugData_ult_remod.……...
ID_produtoID_dataID_LojaID_ClienteID_PromoçãoNum_produtosNum_itemsCusto_compraValor_venda
ID_promoçãoNúmeroNome_promoTipo_red_preçoTipo_anúncioTipo_cartazTipo_couponsMeio_anúncioMeio_cartazCusto_promoçãoInício_promoçãoFim_promoção……...
PromoçãoPromoçãoID_ClienteNúmero_cartãoNomeEndereçoLocalidadeCódigo_postalDistritoRegiãoTelefone(outros atributos demográficos)……...
ClientesClientes
207Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Plano geral: ideia básicaFontes
DestinosClientes TempoProdutos Promoções Lojas
Factos vendas
BDs operacionais Sistemas legados Folhas de cálculo, ficheiros, ... Outras fontes
DB existências
DB corporação
Gerada sinteticamente por ferramenta/
programa
• Muda muito lentamente
• Cerca de 100K• ~0,01% muda
diariamente
• Muda lentamente• 700K clientes• ~0,1% mudam
diariamente em atributos demográficos
• Testar e conciliar clientes em sistemas antigos
• ~100 promoções ano• Vários ficheiros
Excel• Ficheiro em
máquinas diferentes• Máquinas de
utilização pessoal
• Ficheiros de texto com características das lojas.
• Precisam limpeza manual.
• Processa 50K vendas por dia
• 40% clientes desconhecidos
• etc
• Introdução manual de dados
208Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
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.
• 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.
• 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.
209Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Carregamentos iniciais
• Feitos directamente da área de estágio para as tabelas da DW (depois dos dados preparados)
• Alguns cuidados:– 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.
• 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.
210Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1Carregamentos incrementais
• Definir estratégia para identificar novos dados nos sistemas fonte:
– Novas transacções– Actualizações a dados de transacções anteriores
• Identificar:1. Registos novos a introduzir em cada dimensão2. Actualizações de atributos de dimensões e como estas vão ser
tratadas3. Novos factos
Data Warehousing e OLAP 36
Tópicos Avançados de Bases de DadosInstituto Politécnico da Guarda, 2004/2005
211Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Identificação de novos dados nos sistemas fonte
• Usar o sistema de logs existente no sistema fonte
• Usar características das aplicações dos sistemas fontes:– 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
212Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
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;
• Vigiar e afinar o desempenho do sistema;
• 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;
213Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
Ritmo típico de uma DW
A DW transita periodicamente (diariamente, semanalmente, etc) entre dois estados:
– Exploração (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.
214Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Hen
rique
Mad
eira
, DEI
-FC
TUC
, 200
1
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;• Suporte de processamento paralelo;• 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• Webhouses.
Recommended