BANCO DE DADOS
DISTRIBUÍDOS e DATAWAREHOUSING
Asterio K. Tanaka
http://www.uniriotec.br/~tanaka/tin0036
[email protected]
Modelagem Dimensional – Conceitos Avançados
Asterio K. Tanaka
Modelagem Multidimensional:
Conceitos Avançados
• Conformidade e Combinações de Dimensões
• Dimensões Tradicionais
– Quando, Onde, O que
•
•
•
•
•
•
•
Dinâmica das Dimensões
Dimensões Degeneradas e Dimensões Lixo
Campos Chaves de Dimensões e Fatos
Tabelas Fato sem Dados ou Métricas
Tabelas Fato com Classificação ou SubTipo
Relacionamentos M:N entre Fatos e Dimensões
Agregados
Asterio K. Tanaka
Page 1
Esquema Estrela
Uma
Umatabela
tabelade
defatos
fatoscercada
cercadade
detabelas
tabelasde
dedimensões
dimensões
Dimensão
Dimensão
Fato
Dimensão
Dimensão
Asterio K. Tanaka
Tipos de Dimensões mais
comuns
quando
onde
Fato
quem
o quê
Asterio K. Tanaka
Page 2
Esquema Estrela - Lógico
Asterio K. Tanaka
Conformidade e Combinações de Dimensões
• Dimensões devem ter sempre o mesmo significado, para
que os diversos esquemas dimensionais de diferentes DM
possam ser cruzados, produzindo informações
compatíveis.
• Regra básica: definir as dimensões no maior grau de
granularidade (mais detalhada) possível ?
compartilhamento de dimensões em diferentes DM.
• Considerações especiais:
– Diferentes hierarquias na mesma dimensão. Ex:
CATEGORIA? PRODUTO e FABRICANTE? PRODUTO
– Hierarquia que leva a roll-up indevido. Ex:
ANO? TRIMESTRE? Mês? SEMANA? DIA
• Combinações de dimensões (em uma única tabela)
– Quando existe forte coesão entre duas dimensões, ou seja, acontecem
quase sempre juntas para determinados subconjuntos de valores
combinados. Ex: somente certos PRODUTOS são vendidos em certas
LOJAS ? poderia sugerir uma combinação de dimensões, desde que
observado o aspecto de proliferação (produto cartesiano)
Asterio K. Tanaka
Page 3
Dimensões Clássicas
• Dimensão Tempo (Data) – quase sempre
presente, pois DW é histórico.
– Data completa – Dia da semana – Dia do mês – Dia do ano –
Semana do mês – Semana do ano – Mês do ano – Trimestre –
Período Fiscal – Tag de fim de semana – Tag de último dia útil do
mês – Tag de feriado – Tag de temporada – etc. etc.
• Dimensão Espaço (Local, Loja, etc.)
– Nome – Número – Endereço – Bairro – Município – Estado – CEP
– Gerente – Distrito – Região – Data de abertura – Área útil –
Coordenadas geográficas – etc. etc.
• Dimensão Produto
– Descrição – Número de estoque – Unidade – Marca – Categoria –
Departamento – Tipo de embalagem – Tamanho de embalagem –
Peso – Unidade de peso – (Características típicas do produto … ) –
tipicamente pode ter mais de 50 atributos descritivos.
Asterio K. Tanaka
Dinâmica das Dimensões
• Atualização das dimensões
– Exemplos: Endereço de Cliente, Descrição de Produto.
• Várias alternativas
– Atualizar por cima do valor antigo
» É simples mas não preserva histórico.
– Adicionar uma nova linha com o novo valor do atributo atualizado, mantendo os
demais.
» A nova linha particiona o histórico na tabela fato.
» É a técnica adequada para dimensões que mudam lentamente (slowly
changing dimensions).
– Adicionar uma nova coluna, preservando o valor anterior e inserindo o novo valor
na nova coluna.
» Permite a manutenção de apenas duas visões simultâneas do histórico, e dá
margem a muitos valores nulos quando as mudanças são lentas.
– Soluções híbridas, com múltiplas versões (linhas) combinadas ou não com coluna
de valor anterior.
» Mais flexíveis e completas, porém mais complexas.
• Dimensões com grande volume e alta volatilidade
– Particionamento da dimensão em tabelas diferentes, separando-se os dados
estáticos dos dados voláteis.
» Dimensões são relacionadas entre si e ambas relacionadas com a tabela fato
Asterio K. Tanaka
Page 4
Dimensões Especiais
• Dimensões degeneradas
– Relacionadas com objetos do tipo evento, como Ordem de Compra, Nota
Fiscal ou Pedido de Serviço, normalmente compostas de Itens (Item de
OC, Linha de NF, Item de PS).
– Quando a granularidade da tabela fato é Item, o número do documento
maior (OC, NF, PS) estará na tabela fato apenas para permitir o
agrupamento dos itens por documento.
– O documento maior é uma dimensão degenerada.
• Dimensões lixo, descartável (junk)
– Relacionadas com tabelas tipo código-descrição com baixa cardinalidade:
Sexo, Estado Civil, Tags diversos, Textos descritivos, etc. São campos
tipo miscelânea que não trazem muita correlação com os outros campos
da tabela fato, mas são usados como filtro, daí serem dimensões.
– Podem ser usadas de forma combinada.
» Exemplo: três tags binários ? 23 = 8 combinações
– Pode ser usado como artifício para economia de textos (por exemplo,
observações, comentários), pois o que vai para a tabela fato é a chave
numérica (surrogate)
Asterio K. Tanaka
Campos Chaves de Dimensões e Fatos
• Regra básica: uso de surrogates ou chaves artificiais.
– Ajudam a manter a estabilidade, através da neutralidade.
– Evitam manutenção custosa de tabelas, especialmente das tabelas fatos.
– Chaves naturais podem ter problemas de unicidade, ausência, tamanhos
exagerados.
– Chaves artificiais podem ser especificadas como inteiros de 4 bytes,
alcançando até 232, isto é, mais de 2 bilhões de ocorrências, o que é mais
do que necessário para qualquer tabela dimensão.
– Chaves artificiais ficam transparentes (invisíveis) para os usuários,
servindo apenas como ligação entre dimensões e fatos.
– Campos naturais poderão ser indexados, tornando as consultas
amistosas.
– Se produzidas automaticamente, deve-se ter cuidado no processo de
preparação (ETL), especialmente nos reprocessamentos.
– A desvantagem das chaves artificiais é que a tabela fato não pode ser
consultada diretamente, pois os campos de filtro estarão armazenados
nas dimensões.
Asterio K. Tanaka
Page 5
Tabelas Fatos Especiais
• Tabela Fatos sem Dados ou Métrica
– Não muito comum. A tabela fato sem dados serve apenas para relacionar
as dimensões. Exemplo:
» Fato: Frequencia
» Dimensões: Aluno – Disciplina – Dia
• Tabelas Fatos com Classificação ou Subtipos
– Tipos de tabelas fato, uma para cada linha de produto, cada uma com
métricas diferentes.
– Modelagem como nas hierarquias de classe/subclasse.
– Exemplo:
» Ambiente bancário com vários tipos de contas e produtos
» Fato: Produtos especializados (multifatos)
» Dimensões: Agência – Cliente – Data (comuns a todos os produtos)
Asterio K. Tanaka
Relacionamentos M:N entre Fatos e Dimensões
• Exemplo: Desfiles de Escolas de Samba
Quesito
Escola
Nota
Desfile
Enredo
Jurado
Chave-Escola
Chave-Enredo
Chave-Ano
Chave-Corpo-Jurado
Número-Participantes
Número-Alas
Classificação
Valor-Gasto
Etc...
Corpo Jurado
Chave-Corpo-Jurado + Quesito + Jurado + Nota
Dimensões do tipo Corpo Jurado, que substituem
relacionamentos M:N entre Fatos e Dimensões,
são conhecidas como Dimensões Ponte.
Ano
Asterio K. Tanaka
Page 6
Agregados (1)
• Materializar (armazenar) ou não?
– Vide síndrome da explosão do volume de dados
• Critérios para definição de agregados
– Passam pela análise dos principais tipos de informação necessárias
e pela dificuldade de se obtê-las diretamente das tabelas granulares.
– Exemplo:
TDLoja (chave-loja, nome-loja, endereco-loja, cidade, estado, regiao)
TDProduto (chave-produto, descricao, marca, categoria, tipo-embalagem, departamento)
TDDia (chave-dia, data-completa, dia, mês, ano, período-fiscal, estação)
TFVendas (chave-loja, chave-produto, chave-dia, valor-vendido-real, custo-real, lucro, qtdvendida)
Hierarquias de dimensões
REGIÃO ? LOJA
CATEGORIA ? PRODUTO
ANO ? MÊS ? DIA
Asterio K. Tanaka
Agregados (2)
• Combinações possíveis
– Ternárias: LOJA X PRODUTO X DIA
? 2 x 2 x 3 = 12 combinações
– Binárias:
» LOJA X PRODUTO + LOJA X DIA + PRODUTO X DIA
? 2 x 2 + 2 x 3 + 2 x 3 = 16 combinações
– Unárias:
» LOJA + PRODUTO + DIA
? 2 + 2 + 3 = 7 combinações
– Total = 35 combinações
• Quais deveriam ser materializadas?
• Qual a distribuição de valores agregados por dimensão?
– Ex: LOJA
SELECT nome-loja, COUNT(*)
FROM TFVendas, TDLoja
WHERE TFVendas.chave-loja = TDLoja.chave-loja
GROUP BY nome-loja
Asterio K. Tanaka
Page 7
Problemas
Calcular
Calcularos
osagregados
agregadosno
nomomento
momento
da
darecuperação
recuperaçãoou
ouarmazená-los?
armazená-los?
Armazenamento
Tempo de
Resposta
X
BD3
BD4
BD2
BD1
BD3
BD4
BD2
BD1
Asterio K. Tanaka
A Síndrome da Explosão no
Volume de Dados
Número de Agregações
70000
65536
60000
50000
40000
30000
20000
16384
10000
0
16
2
3
4
4096
1024
256
64
5
6
7
8
Número de Dimensões
(4 níveis em cada dimensão)
Asterio K. Tanaka
Page 8
Agregados (3)
• Cuidados na definição dos agregados
– Valores aditivos
» Nem todas as métricas armazenadas nas tabelas granulares
são aditivas em todas as dimensões (fatos semi-aditivos ou
não aditivos). Isto significa que os atributos das tabelas fatos
de agregados poderão ser diferentes das tabelas fatos
granulares.
– Precisão
» Deve-se definir criteriosamente a precisão dos valores aditivos
de agregados, que deverão ser maiores do que os usados nos
respectivos valores das tabelas granulares (para evitar
overflow na adição)
» Fatos e dimensões agregados devem estar em tabelas
fisicamente diferentes das tabelas granulares, mesmo que o
número de tabelas cresça muito. Ferramentas de análise
(OLAP, por exemplo) possuem mecanismo de navegação de
agregados que escondem a complexidade da estrutura.
Asterio K. Tanaka
Agregados (4)
• Exemplos
– Agregação por loja, para todos os produtos, todos os dias.
– Agregação por loja, por mês, para todos os produtos.
– Agregação por regiao de venda, por mês, por categoria.
Asterio K. Tanaka
Page 9
Agregados (4)
• Exemplos
– Agregação por loja, para todos os produtos, todos os dias.
INSERT INTO AG-LOJA AS
SELECT nome-loja, sum(valor-vendido-real), sum(custo-real)
FROM TDLoja, TFVendas
WHERE TDLoja.chave-loja=TFVendas.chave-loja
GROUP BY nome-loja
– Agregação por loja, por mês, para todos os produtos.
INSERT INTO AG-LOJA-MÊS AS
SELECT nome-loja, mês, sum(valor-vendido-real), sum(custo-real)
FROM TDLoja, TFVendas, TDDia
WHERE TDLoja.chave-loja=TFVendas.chave-loja AND
TFVendas.chave-dia=TDDia .chave-dia
GROUP BY nome-loja, mês
Asterio K. Tanaka
Agregados (5)
• Exemplos
– Agregação por regiao de venda, por mês, por categoria.
INSERT INTO AG-REG-CAT-MES AS
SELECT regiao, mês, categoria, sum(valor-vendido-real), sum(custo-real)
FROM TDLoja, TFVendas.TDProduto, TDDia
WHERE TDLoja.chave-loja=TFVendas.chave-loja AND
TFVendas.chave-dia=TDDia .chave-dia AND
TFVendas.chave-produto=TDProduto.chave-produto
GROUP BY regiao, mês, categoria
• Cuidados operacionais
– Modelos separados (agregados e granulares) para evitar contenções
mútuas no momento de carga ou atualização.
– Carga total versus Atualização incremental: Tempo de processamento
versus Complexidade de programas
– Carga/atualização pode requerer processamento paralelo, para otimização
• Utilização de agregados
– Navegador de agregados: camada de interface entre a ferramenta OLAP e
o servidor de DW. O navegador realiza transparentemente a conversão de
comandos SQL granulares nos equivalentes que trabalham informações
agregadas.
Asterio K. Tanaka
Page 10
Arquitetura Genérica de um
Data Warehouse
Meta Dados
FERRAMENTAS
DE CONSULTA
FONTES DE DADOS
OLAP
BDs Operacionais
Extração
Transformação
Carga
Atualização
Análise
Data Mining
Data Warehouse
Relatórios
Fontes Externas
Data Marts
OLAP
Chaudhri&Dayal, SIGMOD RECORD 1997
Asterio K. Tanaka
Page 11