I
NTEGRAÇÃO
E
P
ROCESSAMENTO
A
NALÍTICO
DE
I
NFORMAÇÃO
Modelação Dimensional – 4
António Manuel Silva Ferreira
U
NIVERSIDADEDE
L
ISBOA– F
ACULDADEDE
C
IÊNCIASD
EPARTAMENTODE
I
NFORMÁTICASumário
• Modelação dimensional
– Hierarquias e tabelas de ponte – Dimensões mulV-‐valor
– Tipos de tabelas de factos
• Recomendações de modelação
– Regras a seguir– Erros a evitar
• Carregamento do data warehouse
– Data staging area
– Sistema ETL
Hierarquias de Atributos
• Relações hierárquicas de Vpo
muitos
-‐para-‐
um
são habituais
– Ex. muitos meses compõem um ano, dias compõem um mês• Hierarquias desempenham papéis importantes
– Navegação no cubo de dados
• Permitem operações de drill-‐down e roll-‐up
• Ex. encomendas por ano, mês, dia, … e país, cidade, freguesia, …
– Pré-‐cálculo de valores agregados para cada nível hierárquico
• Agregações armazenadas em disco e usadas automaVcamente nos relatórios • Ex. totais e médias por ano, mês, e dia
• Desafios
– Modelação adequada ao presente e flexível para mudanças futuras – Limpeza e manutenção dos dados de hierarquias
Tipos de Hierarquias de Atributos
• Profundidade fixa
– Todos os níveis da hierarquia têm sempre valores
– De uso generalizado e fáceis de entender – Exemplos
• Anos têm sempre meses, e meses têm sempre dias
• ConVnentes têm sempre países e países têm sempre cidades
• Profundidade variável
– Alguns níveis podem não estar preenchidos
– Para relações hierárquicas mais complexas – Exemplos
• Árvore genealógica de uma família
Modelação de Hierarquias Fixas
• Todos os níveis da hierarquia numa tabela desnormalizada
– Decisores disHnguem hierarquias em atributos da dimensão
• Atributos de uma mesma hierarquia devem estar seguidos
– Permite várias hierarquias em simultâneo na mesma tabela
– Ex. Produto(…, Categoria MarkeVng, Subcategoria MarkeVng, …
Categoria Finanças, Subcategoria Finanças, …)
• Cada nível da hierarquia numa tabela separada (snowflaking)
– Antes: Produto (…, Categoria Produto, Subcategoria Produto) – Depois: Produto (…, pSubcategoria)Subcategoria (ID, Subcategoria Produto, pCategoria) Categoria (ID, Categoria Produto)
– Bifurcações, apenas úteis em dimensões “monstras”
Modelação de Hierarquias Variáveis
• Possível com chave estrangeira para a própria tabela de dimensão
– Ex. muitos empregados têm o mesmo chefe• Problemas
– Níveis hierárquicos diferentes misturados na tabela de factos
• Chave estrangeira para empregado pode referir o presidente, diretores, …
• Cálculo de valores agregados por nível hierárquico inviável
– Interrogações muito complexas e/ou com comandos SQL proprietários
1
2 3
4 5
ID Nome Empregado OEmpregadoChefe
1 Presidente —
2 Diretor A 1
3 Diretor B 1
4 Empregado C 2
Tabelas de Ponte – 1
• Permitem
navegar
em hierarquias de profundidade variável
– Ex. saber quem é chefe de quem na dimensão empregado– Ex. somar salários dos empregados chefiados por um diretor
• Guardam
caminhos
1. Entre cada nível e o próprio nível (distância zero) 2. Entre cada nível e todos os níveis descendentes
• Atributos específicos
– Nível ascendente (1) – Nível descendente (2) – Distância entre níveis (1) – Ascendente no topo? (S) – Descendente na base? (N)
Empregado 1 Empregado 2 Empregado 4 Empregado 5 Empregado 3
• Exemplo: hierarquia de clientes (estado, ministérios, repartições, …)
• Dimensão cliente tem 7 linhas
– Ponte tem muitas mais linhas, uma para cada caminho entre níveis
– Linhasponte = ∑(nível de 1 a profundidade) nósnível × nível = 1×1 + 3×2 + 3×3 = 16
Tabelas de Ponte – 2
Ascendente Descendente Distância Topo? Base?
Cliente 1 Cliente 1 0 Sim Não
Cliente 1 Cliente 2 1 Sim Não
Cliente 1 Cliente 3 2 Sim Sim
… … … … …
Cliente 2 Cliente 2 0 Não Não
Cliente 2 Cliente 3 1 Não Sim
… … … … …
Cliente 3 Cliente 3 0 Não Sim
… … … … … Cliente 1 Cliente 2 Cliente 4 Cliente 5 Cliente 3 Cliente 6 Cliente 7
Tabelas de Ponte – 3
• Tabela de ponte colocada entre tabelas de factos e de dimensão
– Factos e dimensão não precisam de ser alterados
– Uso da tabela de ponte é opcional
• ConVnuaria a ser possível associar factos à dimensão • Mas deixaria de ser possível navegar na hierarquia
• Exemplo
– Honorários cobrados aos clientes descendentes de um outro cliente
Tabela de Factos
…
Cliente (FK)
Honorários
Tabela de Ponte
Ascendente (FK) Descendente (FK) Distância Topo? Base? Dimensão Cliente ID Cliente (PK) Nome …
1. Fixar ID do cliente “pai” e
Dimensões MulH-‐Valor
• Dimensões podem ter vários valores para um mesmo facto
– Aparentemente grão deveria ser mais fino, até um só valor por facto – Mas existem situações legíVmas para dimensões mulV-‐valor
– Exemplo: cobrança de diagnósVcos médicos
• No mesmo ato médico ficam registados vários diagnósVcos
• Alguns levados pelo próprio paciente, pelo que não podem ser cobrados • Peso indica contribuição (%) desse diagnósVco para o total cobrado
• Total de pesos dos diagnósVcos de um grupo deve ser 100%
Tabela de Factos
…
Paciente (FK)
Grupo Diags. (FK)
QuanVa cobrada
Tabela de Ponte
Grupo Diags. (FK) DiagnósVco (FK) Peso DiagnósHco ID DiagnósVco Nome Tipo … Grupo DiagnósHcos ID Grupo Nome Grupo
Tipos de Tabelas de Factos
• Transações
– Registam eventos que ocorreram em determinados momentos – Ex. venda de produto a um cliente, numa loja, numa data
• Instantâneos periódicos (periodic snapshots)
– Guardam desempenho acumulado em períodos fixos e regulares
– Dados novos são inseridos e algumas medidas podem valer zero – Ex. saldo, comissões, e juros em janeiro, fevereiro, março, …
• Instantâneos cumulaVvos (accumula:ng snapshots)
– Acompanham um processo recorrente, mas de duração variável
– Etapas previsíveis, cumpridas em datas imprevisíveis
– Dados novos através de inserções e atualizações de linhas
Exemplos de Tipos de Tabelas de Factos
Transações Data (FK) Produto (FK) Loja (FK) Cliente (FK) Empregado (FK) Promoção (FK) ID Transação (DD) QuanVdade Preço Instantâneos Periódicos Mês (FK) Conta (FK) Agência (FK) Cliente (FK) Saldo Comissões Cobradas Juros Creditados Número Transações Instantâneos CumulaHvos Data Encomenda (FK) Data Envio (FK) Data Entrega (FK) Data Pagamento (FK) Data Devolução (FK) Loja (FK) Cliente (FK) Produto (FK) Estado Encomenda (FK) QuanVdade PreçoVenda de produtos
Saldos bancários mensais
Comparação de Tipos de Tabelas de Factos
!
Transações Instantâneos Periódicos Instantâneos CumulaHvos Período
representado Ponto no tempo Intervalos fixos e regulares Intervalo indeterminado
Grão Uma linha por transação Uma linha por período Uma linha por ciclo de vida Carregamento
de factos Inserções Inserções Inserções e atualizações
Atualização
de factos Inexistente Inexistente Quando houver aVvidade
Dimensão
data Data da transação Data do fim do período Várias datas do ciclo de vida
Factos AVvidade de
Regras da Modelação Dimensional – 1
1. Carregar dados atómicos nas tabelas de dimensões
– Possibilitam todo o Vpo de agregações, mesmo as mais imprevisíveis
2. Estruturar modelos de dados em torno dos processos de negócio
– Cada processo de negócio representa eventos mensuráveis
3. Referir dimensão data em cada tabela de factos
– Factos (agregados ou não) vão acontecendo ao longo do tempo
4. Usar mesmo grão para todas as linhas da tabela de factos
– Mistura de factos com diferentes grãos causa confusão
5. Resolver relações muitos-‐para-‐muitos em tabelas de factos
– Suportadas naturalmente pelas múlVplas chaves estrangeirasRegras da Modelação Dimensional – 2
6. Resolver relações muitos-‐para-‐um em tabelas de dimensões
– Manter atributos dependentes entre si na mesma tabela7. Guardar valores descriHvos em tabelas de dimensões
– De preferência os usados em filtros e cabeçalhos de relatórios
8. Usar chaves subsHtutas em tabelas de dimensões
– Maior flexibilidade, rapidez, e poupança de espaço em disco
9. Criar dimensões conformadas, válidas em toda a organização
– Permitem combinação de dados de várias tabelas de factos10. Compreender equilíbrio entre requisitos e realidade
– Para apoiar efeVvamente a tomada de decisãoErros a Evitar na Modelação Dimensional – 1
12. Deixar atributos de texto na tabela de factos
– Tabela de factos guarda medidas numéricas e chaves estrangeiras
– Atributos de texto devem ser colocados em tabelas de dimensões
11. Evitar atributos descriVvos nas tabelas de dimensões
– Espaço poupado não compensa perda de inteligibilidade– Cada código numérico deve ter tradução textual
10. Distribuir hierarquias por várias tabelas de dimensões
– Decisores reconhecem níveis hierárquicos numa mesma tabela
– Mesma tabela pode inclusivamente conter várias hierarquias
9. Lidar tardiamente com dimensões de mudança lenta
– Evitar que carregamento de dados escreva por cima dos existentes
Erros a Evitar na Modelação Dimensional – 2
8. Usar chaves naturais em tabelas de dimensões
– Cria dependência com sistema operacional e pode impedir histórico
– Dimensões devem ser idenHficadas por chaves subsHtutas
7. Adicionar dimensões antes de escolher o grão dos factos
– Factos registados no nível de detalhe do negócio– Dimensões com grão diferente dos factos causam confusão e erros
6. Criar modelo dimensional baseado num relatório específico
– Relatório é apenas uma vista parVcular sobre os dados– Modelo dimensional assenta nos processos de medição do negócio
5. Misturar linhas com vários grãos na mesma tabela de factos
– Factos com subtotais e outros cálculos auxiliares causam errosErros a Evitar na Modelação Dimensional – 3
4. Deixar dados atómicos apenas na data staging area
– Decisores não vão procurar dados na data staging area
– Dimensões e factos devem ser registados com o máximo de detalhe
3. Evitar agregados pré-‐calculados para melhorar desempenho
– Mais hardware reduz tempo de resposta das interrogações– Mas agregados pré-‐calculados têm melhor relação custo/bene_cio
2. Falhar conformação de factos
– Mesma medida deve ter representação coerente em vários processos
– Permite combinação de mesmas medidas de várias tabelas de factos
1. Falhar conformação de dimensões
– Erro mais grave: impede expansão do data warehouse!
Matérias Cobertas – 1
• Sistemas de apoio à decisão e data warehouses
– Dados operacionais e de apoio à decisão– Sistemas OLTP e OLAP
– Estrutura básica de um data warehouse – Bases de dados federadas
• Modelação dimensional
– Obtenção de dimensões– Metáfora do cubo de dados
– Tabelas de factos e de dimensões – Grão da tabela de factos
Matérias Cobertas – 2
• Interrogações OLAP em SQL
– Abordagem clássica– Extensões OLAP no SQL-‐99
• Modelação dimensional
– Chaves primárias e dimensões degeneradas – Dimensão data e hierarquias fixas
– Chaves subsVtutas – Snowflaking
• Mais sobre modelação de factos
– Medidas adiVvas e semi-‐adiVvas – Tabelas de factos sem factosMatérias Cobertas – 3
• Dimensões de mudança lenta
– Técnicas para registar mudanças em dimensões – Tipos 1, 2, 3, e híbridas
• Role-‐playing de dimensões
• Dimensões conformadas
– Bus matrix
– Relatórios transdepartamentais
• Gestão de dados mestre
• Dimensões muito grandes
– Bifurcações (outriggers) – Mini-‐dimensõesMatérias Cobertas – 4
• Tabelas de ponte
– Hierarquias variáveis – Dimensões mulV-‐valor
• Tipos de tabelas de factos
– Transações– Instantâneos periódicos – Instantâneos cumulaVvos
• Regras da modelação dimensional
• Erros a evitar
Carregamento do Data Warehouse
• Data staging area
– Situada entre os sistemas operacionais e a data presenta:on area
– Área de trabalho para pré-‐processar dados em bruto
– Operações „picas: limpar, fundir duplicados, combinar, transformar, …
• Data presenta:on area
– Guarda dados limpos, com apresentação simples e inteligível
– Inclui índices e agregados pré-‐calculados para reduzir tempos resposta
Extração periódica de dados Limpeza e transformação Carregamento de dados
Sistema ETL
• Sistema extrac:on-‐transforma:on-‐load (ETL)
– Consome cerca de 70% do esforço de construção do data warehouse – Kimball idenVfica 34 subsistemas disVntos
• Extração (extrac:on)
– Analisar domínio e regras de integridade das colunas
– Detetar alterações nos dados, aplicar filtros, ordenar dados
• Transformação (transforma:on)
– Limpar dados, usar dicionários para correções automáVcas – Tratar exceções, fundir duplicados, conformar valores
• Carregamento (load)
– Manter chaves subsVtutas, lidar com dimensões de mudança lenta – Preencher hierarquias, pré-‐calcular valores agregados
Manutenção de Chaves SubsHtutas – 1
• Tabelas de correspondência (cross-‐reference tables)
– Fazem a correspondência entre chaves naturais e chaves subsHtutas
– Cada dimensão tem a sua tabela de correspondência – Guardadas de forma persistente na data staging area
• Deteção de alterações
– Novos registos nos sistemas operacionais geram novas chaves substitutas
– Alterações detetadas através da comparação de valores de CRC
• Cyclic redundancy code (CRC): uma síntese numérica • Muito usada na deteção de erros em dados
• Coluna extra para CRC em cada linha de dados
– Comparação coluna a coluna pouco eficiente
• Só aplicada depois de detetada a alteração através do CRC
Manutenção de Chaves SubsHtutas – 2
• Dimensões de mudança lenta de Hpo 1
– Tabela de correspondências não guarda histórico
– Chave subsVtuta refere sempre a versão mais atual da chave natural
• Dimensões de mudança lenta de Hpo 2
– Guardadas todas as chaves subsVtutas para cada chave natural – Necessários campos extra para período e indicação de validade
Chave
SubsHtuta NaturalChave Descrição ValidadeInício ValidadeFim Em Vigor CRC
123 MG2 Magalhães 01.01.2010 29.03.2010 3A03C9DD 456 MG2 Magalhães 30.03.2010 x 2077CCED
Manutenção de Chaves SubsHtutas – 3
Dados mudaram? Tipo de mudança lenta? Chave natural na TC?Não faz nada Insere linha na TC Insere linha na TD Atualiza linha na TC Atualiza linha na TD
Atualiza e insere linha na TC
Atualiza e insere linha na TD
Legenda: TC = Tabela de Correspondência; TD = Tabela de Dimensão
Sim Sim Tipo 2
Tipo 1 Não
Bibliografia
• Ralph Kimball e Margy Ross, The Data Warehouse Toolkit: The
Complete Guide to Dimensional Modeling, Wiley, 2002
– Capítulos 5, 6, 13, 15, 16
• Ralph Kimball e Margy Ross, The Kimball Group Reader:
Relentlessly Prac:cal Tools for Data Warehousing and Business
Intelligence, Wiley, 2010
Extra – IntegraHon Services no SQL Server
Fluxo de Controlo
Fluxos de Dados
