Tópicos Avançados de Bases de Dados
André Valente Rodrigues  110370165
Carlos Filipe Ribeiro Ferreira  060316048
 Data Warehousing
 Data Warehouse
 BDOs vs DWs:
 BDOs
 DWs
 OLAP
 Cubos OLAP
 Exemplo de queries ad-hoc em excel
 Modelação dimensional
 Esquema em estrela
 Esquemas "Floco de Neve" e Constelações de Fatos




Arquitetura Data Warehousing
Exemplo Prático
Utilidades e Ferramentas Back End
Servidores de uma DW
 OLAP em BDOs
 Desafios
 Tipos de Servidores
 Metadata e Gestão de uma Warehouse
2/34
 Data warehousing: coleção de tecnologias de suporte à decisão,
com o objetivo de permitir ao trabalhador (executivo, gestor,
analista, etc.) tomar melhores e mais rápidas decisões.
 O mercado de data warehousing explodiu tanto em número de
produtos e serviços oferecidos como na adoção destas
tecnologias pela indústria.
 As tecnologias foram adotadas em imensas indústrias:
- fabrico;
- venda a retalho;
- serviços financeiros;
- transportes;
- telecomunicações;
- saúde.
3/34
 Data warehouse: é uma coleção de dados não volátil, que varia no
tempo, integrada e orientada para o utilizador que é usada
primeiramente na tomada de decisão organizacional.
 Tipicamente mantida separadamente das bases de dados
operacionais das organizações.
 Uma Data Warehouse (DW) suporta on-line analytical
processing (OLAP).
 Uma Base de Dados Operacional (BDO) suporta on-line
transaction processing (OLTP).
 Ambos têm requisitos de funcionalidade e de performance
diferentes.
4/34
 Aplicações OLTP tipicamente automatizam tarefas como
uma nova entrada na base de dados ou uma transação
bancária que são tarefas diárias de uma organização.
 Estas transações requerem dados atualizados e detalhados.
 As bases de dados operacionais tendem a ter entre centenas
de megabytes e gigabytes de tamanho.
 São desenhadas para refletir as semânticas operacionais de
aplicações conhecidas, em particular, para minimizar os
conflitos de concorrência.
5/34
 Desenhadas para suporte à decisão.
 Dados históricos, sumarizados e consolidados são mais
importantes que entradas individuais detalhadas.
 Como tendem a conter dados consolidados de uma ou mais bases
de dados tendem a ser ordens de magnitude maiores que estas.
Warehouses de dados empresariais são projetadas para ser de
centenas de gigabytes a terabytes em tamanho.
 As queries são intensivas por isso o throughput das queries e os
tempos de resposta são mais importantes do que o throughput
das transações.
6/34
 Para facilitar a visualização, os dados são tipicamente modelados
multidimensionalmente.
 Muitas vezes estas dimensões/atributos são hierárquicas:
- tempo da venda pode ser organizado como uma hierarquia de dia-mêstrimestre-ano;
- um produto pode ser organizado como uma hierarquia de produto-categoriaindústria.
 É esta a tecnologia que oferece a interface para os utilizadores responsáveis
pelas tomadas de decisão.
 Operações típicas OLAP incluem:
- rollup;
- drill-down;
- slide_and_dice;
- pivot.
7/34
 Apresentação ao utilizador da informação numa
visão cúbica, que se trata do output dos servidores
OLAP, em que as dimensões e métricas da DW são
directamente mapeadas para este.
Figura 1 – Data Cube.
8/34
Figura 2 – Exemplo de queries ad-hoc em excel.
9/34
 Tabela de fatos
 Medidas numéricas de performance;
 Granularidade da Tabela de Factos:
 Consiste no nível de detalhe da DW em relação às transacções
operacionais tendo por medida unidades de tempo.
 Evitar os “Zeros” (“o que não aconteceu”);
 Muitas Linhas, Poucas Colunas;
 90% do espaço;
 2 ou mais Chaves Estrangeiras (FK);
 Chave Primária (composta).
Figura 3 – Tabela de fatos de
vendas diárias.
10/34
 Tabelas de Dimensões
 Descrição Textual do Negócio;
 Contêm Atributos;
 Poucas Linhas(relativamente); Muitas Colunas;
 Chave Primária (PK) – Integridade Referencial com factos;
 Respondem aos “por”:
 Vendas em €“por mês”, “por” Cliente e “por” “Artigo”.
 Hierarquia de Dimensões (sem normalização):
 Ex: Produtos->Marcas->Categorias.
Figura 4 – Tabela de
Dimensões de Produtos.
11/34
 Diagramas ER e técnicas de normalização são populares em ambientes OLTP.
 Diagramas ER são inapropriados para suporte à decisão onde a eficiências das
queries e no carregamento dos dados são importantes.
 Data Warehouses usam o esquema em estrela para representar modelos de
dados multidimensionais.
Figura 5 – Esquema em estrela.
12/34
 A hierarquia dimensional é explicitamente representada normalizando
as tabelas de dimensões.
Figura 6 – Esquema “Floco de Neve”.
13/34
 Desvantagem: A estrutura desnormalizada das tabelas de
dimensões em esquemas estrela pode ser mais apropriado
para navegar nas dimensões.
 Constelações de fatos: Estruturas mais complexas em que
múltiplas tabelas de fatos partilham tabelas dimensionais.
14/34

Operational Source Systems(OSS):




Transacções do Negócio;
Performance e Disponibilidade;
Heterogéneos e não integrados.
Data Staging Area:



Storage + processos ETL ;
Extracção de dados a partir dos OSS;
Transformação (“Limpeza “ de dados);

Regra geral, BDs “não Normalizadas”;
Figura 7 – Arquitetura Data Warehousing.
Carregamento (Loading) dos vários Data Marts.  ETL – Extract Transform Load:
 Data PresentationArea:
 Consiste no processo de transformação e
passagem (load) dos dados para a DW;
 Conjunto de Data Marts;
 O ETL package, deve ser capaz de
 Estruturas (Modelos) Dimensionais:
analisar o que há de novo na BD
 Baixa complexidade;
operacional, de modo a no momento do
 Dados Atómicos.
varrimento (refresh) actualizar a DW
 Factos e Dimensões Partilhados (“Conformed” );
com sucesso, carregar a DW inteira não é
 Cubos OLAP.
aplicável isto só se deve fazer na primeira
vez, estes updates à DW são sempre
 Data Access Tools:
feitos nas horas em que o sistema está
 Ad-hoc queries – acesso apenas a especialistas.
menos sobrecarregado.
15/34

 Esta DW pequena,
teve origem na BD
OLTP (Online
Transaction
Processing)
proveniente da
demonstração do
ERP da Primavera.
Figura 8 – Exemplo prático de uma pequena DW.
16/34
 Neste caso o que se esta a medir na tabela de factos são os dados relativos às
vendas. As vendas são então o somatório das quantidades vendidas e totais
ilíquidos agrupados por artigo, entidade data e vendedor, esta agregação é fácil
de entender a olhar pela ilustração a baixo. Neste caso TSQL.
Figura 9 – Screenshot dos valores retirados das BD e da DW.
17/34
 Uma DW tem que ter sempre a dimensão tempo, pois é deste que ela depende
para organizar a sua granularidade.
 A DW não pode nunca conter valores nulos, regra geral o que se usa é a
substituição de nulos.
 Slowly changin dimensions, consiste em updates na base da dados operacional.
ex: “Produto = garrafa”, “Material = plástico” passar a ser Material = vidro:
 Os tratamentos mais comuns são:
 De tipo 1 (não é mantido histórico);
 De tipo 2 (todo o histórico é mantido);
 De tipo 3 (é apenas mantido um histórico recente).
 Mapeamento Surrogate Key, Natural Key:
 Consiste num sistema de pk-fk interno à DW, para ligar os factos às dimensões.
 Os dados da DW nunca são removidos.
18/34
 Ferramentas de migração dos dados.
 Ferramentas de data scrubbing.
 Ferramentas de audição dos dados.
19/34
 Load
 Batch Load:
 processamento adicional para carregar os dados para uma
data warehouse (toda a computação necessária para criar as
tabelas derivadas que são guardadas na data warehouse);
 monitorizar o estado, suspender, resumir e reiniciar um load
(carga) para a base de dados.
 Refresh:
 Quando fazer refresh e como fazê-lo;
 A política de refresh é definida pelo administrador
dependendo nas necessidades e tráfego dos utilizadores e
pode ser diferente para diferentes fontes.
20/34
 Dado que as BDOs estão desenhadas para suportar os workloads
de OLTP, executar queries OLAP complexas resultaria em
performance inaceitável.
 Suporte à decisão requere dados que podem não estar numa
BDO:
- Dados históricos;
- Dados consolidados de várias fontes heterogéneas.
 BDOs não providencionam os modelos multidimensionais de
dados e a organização dos dados especial e os métodos de
implementação e de acesso que OLAP requere.
21/34
 Desafios:
 Escolher que índices gerar e que vistas materializar.
 Usar efetivamente os índices e as vistas para responder a
queries.
 Otimização das queries complexas.
 Melhorar a eficiência de scans.
 Paralelismo tem que ser explorado para reduzir os tempos de
resposta a queries.
22/34
 As operações de reunião e de interseção de índices podem
ser usadas para reduzir significativamente o acesso às bases
de dados.
 Os servidores de Warehouses conseguem utilizar índices
bit map que suportam operações sobre índices eficientes.
 A natureza dos esquemas em estrela torna a utilização de
índices join especialmente atrativa para suporte à decisão.
 Índices para suportar pesquisa de texto também são muito
úteis.
23/34
 Materializar resumos de dados pode ajudar a
acelerar muitas queries comuns (por ex: num ambiente de
investimento a grande maioria das queries pode ser baseada na
performance do último trimestre ou no ano fiscal actual).
 Uma estratégia simples mas muito útil para usar
uma view é a utilização de selection ou rollup (ex:
query que pede o total de venda das calças de determinada marca do
ano. aplica-se a seleção para a marca de calças e de seguida faz-se
rollup do trimestre para o ano).
24/34
 Tradução de queries SQL com nested subqueries em queries
SQL de um único bloco.
 Redução de número de invocações de inner subqueries
utilizando técnicas semi-join.
 Os maiores vendedores de DBMSs oferecem
particionamento de dados e tecnologia de processamento
de queries paralela.
25/34
 Servidores SQL especializados: Providencionam uma linguagem
de queries e de suporte ao processamento de queries SQL
avançada sobre esquemas em estrema e floco de neve em
ambientes read-only.
 Servidores ROLAP: Servidores intermediários que se localizam
entre um servidor back end relacional (onde os dados da
warehouse estão guardados) e as ferramentas frontend to cliente.
 Servidores MOLAP: Suportam a visão multidimensional de
dados através de um motor de armazenamento
multidimensional.
26/34
 Data Warehouses são implementadas em servidores
relacionais OLAP (ROLAP).
 Estes servidores assumem que:
- os dados estão guardados em BDs relacionais:
 - suportam extensões para MySQL;
 - possuem métodos especiais de acesso e de implementação
que implementem eficientemente o modelo de dados e as
operações multidimensionais.
27/34
 Eles estendem os servidores relacionais tradicionais com middleware
especializado para eficientemente suportar queries OLAP
multidimensionais.
 Identificam as vistas que devem ser materializadas.
 Refraseiam as queries dos utilizadores para as vistas materializadas.
 Geram multi-statement SQL (é uma técnica que permite executar
várias declarações SQL como sendo apenas uma) para o servidor back
end relacional.
 Providencionam serviços adicionais tais como scheduling de queries e
atribuição de recursos.
 Desvantagens: Diferenças intrínsecas entre o estilo de querying de
OLAP e de SQL pode causar bottleneck de performance para os
servidores OLAP.
28/34
 Suportam diretamente a visão multidimensional dos dados através de
um motor multidimensional de armazenamento.
Vantagem: Propriedades de indexação excelentes.
Desvantagem: Providenciam utilização do armazenamento
fraca especialmente quando o conjunto de dados é esparso.
 Alguns adotam uma representação de armazenamento de 2º nível para
se adaptarem a conjuntos de dados esparsos e usarem compressão
extensivamente:
- Um conjunto de dados que seja identificado como sendo
possivelmente esparso é representando num array;
- A estrutura de indexação tradicional é usada para indexar para
estes arrays.
29/34
 Família Estendida de Funções Agregadas: Suporte para
rank e percentile.
 Relatórios de Características: Avaliação de agregações de
dados numa janela temporal com pontos de interrupção e
totais correntes.
 Group-By Múltiplo: Suportam Rollup e Cube.
 Comparações: SQL possui deficiências na execução de
comparações que são comuns no mundo dos negócios.
30/34
 Diferentes tipos de metadata que tem de ser gerida:
- Administrativa - toda a informação necessária para a criação
e utilização de uma warehouse;
- Negocial - termos e definições de negócios, propriedade dos
dados e políticas tarifárias;
- Operacional - a linhagem dos dados migrados e
transformados, o estado dos dados na warehouse e
informação de monitorização.
 Pode ser usado um repositório de metadata que é
utilizado para guardar e gerir toda a metadata
associada à warehouse (ex: “Platinum Repository” e “Prism
Directory Manager”).
31/34
Ferramentas de desenvolvimento - utilizadas para desenhar e editar esquemas,
vistas, scripts, regras, queries e relatórios.
Ferramentas de planeamento e análise - utilizadas em cenários "e-se" para
entender o impato de alterações de esquema ou refresh rates e para fazer
planeamento de capacidade.
Ferramentas de gestão de Warehouses - Usadas para monitorizar uma warehouse,
reportar estatísticas e fazer sugestões para o administrador. Exemplos: “HP
Intelligent Warehouse Advisor”, “IBM Data Hub”, “Prism Warehouse Manager”.
Ferramentas de gestão de rede e de sistema - Usadas para medir o tráfego entre
clientes e servidores, entre servidores de warehouses e BDOs, entre outros.
Exemplos: “HP OpenView”, “IBM NetView“ e “Tivoli”.
Ferramentas de gestão de fluxo - Gestão do processo extração-scrubtransformação-carregamento-refresh.
32/34
Perguntas???
33/34
Obrigado pela atenção!
34/34