André Valente Rodrigues e Carlos Filipe Ribeiro Ferreira
Download
Report
Transcript André Valente Rodrigues e Carlos Filipe Ribeiro Ferreira
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