Modelação Dimensional 2

(1)

I

NTEGRAÇÃO E

P

ROCESSAMENTO

A

NALÍTICO DE

I

NFORMAÇÃO

Modelação Dimensional – 2

António Manuel Silva Ferreira

UNIVERSIDADE DE LISBOA – FACULDADE DE CIÊNCIAS

(2)

Sumário

• Modelação dimensional

– Exemplo do retalhista

– Chaves candidatas e dimensões degeneradas – Dimensão data e hierarquias fixas

– Chaves substitutas – Snowflaking

• Análise dimensional

– Legibilidade e desempenho

• Mais sobre modelação de factos

– Medidas, aditividade, e semi-aditividade – Tabelas de factos sem factos

(3)

Breve Revisão de Conceitos

• Esquema em estrela

– Tabelas de dimensões em redor da tabela de factos

• Tabela de factos

– Guarda informação referente a um processo de negócio

– Colunas com medidas e com chaves estrangeiras para as dimensões

• Tabela de dimensão

– Guarda atributos de uma mesma dimensão, com o grão apropriado

• Medida

– Valor numérico que pode ser usado para avaliar um negócio

• Dimensão

– Entidade independente que pode participar na análise de medidas – Categorização usada para esmiuçar uma medida agregada

(4)

• Empresa de vendas a retalho com

registo de vendas em cada loja

• 100 lojas em 5 países

• 60.000 produtos únicos

• Alguns têm UPCs (universal product

code), mas outros não (padaria,

charcutaria, peixe, carne, ...)

• Objectivo

– Entender o impacto dos preços

e das promoções no total das vendas

(5)

Perguntas a Responder

• Qual é o lift derivado de uma promoção?

– Lift: ganho em vendas num produto porque está a ser promovido – Obriga a conhecer o valor base (consulta ao histórico)

• Detectar deslocações no tempo

– Clientes armazenam e deixam de comprar o produto por muito tempo

• Detectar canibalismo

– Clientes compram o produto promovido em vez de outros

• Detecção de vendas cruzadas

– Mais vendas de morangos implicam mais vendas de chantilly

• Detecção do crescimento do mercado

– Todos os produtos de uma categoria em promoção subiram nas vendas, durante e depois da promoção

• Detectar o lucro derivado de uma promoção

– Considerando todos os aspectos: custos da promoção, lift, deslocações no tempo, canibalismo, e vendas cruzadas

(6)

Construção do Data Warehouse para Vendas a Retalho

1. Identificação dos processos a modelar

– Vital para sucesso do data warehouse

– Focar nas vendas a retalho, incluindo promoções

2. Determinação do nível de detalhe da tabela de factos

– Grão ao nível do item individual vendido numa transacção

3. Escolha e modelação das dimensões

–

Data, produto, loja, promoção

–

IDTransacção

4. Identificação das medidas numéricas na tabela de factos

–

Quantidade, valor de venda, valor de compra

(7)

Chave Candidata da Tabela de Factos

• Chave candidata da tabela de factos é condicionada pelo grão

– Regra geral: mais dimensões, mais atributos na chave candidata – Cada valor da chave candidata identifica univocamente um facto

• Exemplos

– HyperVending

• Chave candidata: data, sucursal, produto, cliente

– Item no talão da caixa do supermercado

• Chave candidata: ID da transacção e código do produto

• Chave candidata pode não incluir algumas dimensões

– Desde que estas possam ser referenciadas indirectamente

(8)

Dimensões Degeneradas

• Algumas dimensões servem apenas para agrupar factos

– Sem outros atributos próprios

– Sem tabela de dimensão associada

• Exemplo: ID de transacção em talão de supermercado

– Chave candidata é ID da transacção e código de produto

– ID da transacção é um identificador único que serve apenas para agrupar produtos comprados na mesma transacção

– Dimensão pode ser descartada se não fizer falta à análise – Pode ser substituída por outras dimensões

(9)

Dimensão Data

• Presente em quase todos os data warehouses

– Quase todos os data marts são séries temporais

• Nível de detalhe/grão

– Típico: dia (em que dia aconteceu o facto?)

– Noutros casos: hora, minuto, segundo, ou mais fino ainda

• Atributos típicos

– Feriado ou não, dia da semana, fim de semana

– Estação: “de volta às aulas”, Natal, saldos de inverno, “férias”, … – Calendário fiscal: trimestre, ano fiscal, …

(10)

Dimensões Hierárquicas – 1

• Permitem a análise de medidas em vários níveis de detalhe

• Dimensão data é hierárquica

– Muitos dos seus atributos têm relações de hierarquia – Ano > semestre > trimestre > mês > semana > dia – Por vezes há mais de uma hierarquia na data

• Exemplo: calendário fiscal sobreposto ao calendário civil

• Outras dimensões também têm expressão hierárquica

– Geografia: continente > país > distrito > cidade – Posição na empresa (organigramas)

(11)

Dimensões Hierárquicas – 2

• Hierarquias podem ou não ser equilibradas

– Todos os anos têm meses e todos os meses têm dias, …

– Todos os continentes têm países e todos os países têm cidades, … – Nem todos os departamentos têm o mesmo tipo de funcionários

• Cada nível hierárquico deve ser um atributo da dimensão

– Na construção do data warehouse é geralmente possível estabelecer as relações hierárquicas entre os atributos

• Chave primária de dimensão hierárquica

– Deve identificar univocamente valores no nível de detalhe mais fino – Tabela de factos guarda factos o mais detalhados possível

(12)

Dimensões Hierárquicas – 3

• Exemplo: número de fotos tiradas em viagens

– factViagem(data, local, fotos)

– dimData(id, ano, semestre, trimestre, mês, semana, dia, data) – dimLocal(id, continente, país, distrito, cidade, coordenadas)

• Dimensões

– Data: dias em que foram tiradas fotos

• Possível extrair ainda mais detalhes através do atributo data

– Local: cidades onde foram tiradas fotos

• Possível extrair pormenores da localização através das coordenadas

• Análises ao número de fotos tiradas

– Por dia, ou contagem agregada por semana, mês, trimestre, … – Por cidade, ou contagem por distrito, país, continente

(13)

Chaves Substitutas em Tabelas de Dimensões – 1

• Chave primária de tabela de dimensão deve ser substituta

– Identifica univocamente cada linha da dimensão – Sem qualquer significado para o utilizador

– Sem ligação com os identificadores dos sistemas operacionais

• Assumpções subjacentes podem ser invalidadas

• Chave substituta (surrogate key) típica

– Número inteiro, positivo, e sequencial

– Primeira linha tem identificador 1, depois 2, 3, …

• Chave substituta serve apenas para permitir a junção da

tabela de dimensão com a tabela de factos

(14)

Chaves Substitutas em Tabelas de Dimensões – 2

• Vantagens

– Chaves mais pequenas e de pesquisa eficiente

– Protegem o data warehouse das alterações nos sistemas operacionais

• Exemplo: reciclagem bianual de códigos de produto

• Mesmo ID no sistema operacional, mas ID diferente no data warehouse

– Simplificam a integração de dados de várias fontes

• Mesmos identificadores usados de forma inconsistente

– Permitem registar eventos que não existem no sistema operacional

• Exemplo: identificadores de dados desconhecidos (datas, vendedores, …)

• Desvantagem

– Carregamento de dados mais complexo

• Necessário fazer correspondência entre códigos dos sistemas operacionais e valores das chaves substitutas

(15)

Snowflaking – 1

• Tabelas de dimensão são não normalizadas

– Informação redundante proveniente de hierarquias

• Normalização conduz ao snowflaking

– Menor redundância

– Menor ocupação de espaço em disco – Escritas mais eficientes

(16)

Snowflaking – 2

• Tabelas de dimensões não devem ser normalizadas

– Aumenta número de tabelas

– Torna o design lógico mais complexo – Interrogações mais difíceis de escrever

– Resposta mais demorada às interrogações (mais junções de tabelas)

• Acessos a tabelas de dimensões são tipicamente leituras e não escritas

– Espaço ocupado pelas dimensões é insignificante quando comparado com o das tabelas de factos

• Excepções

– Dimensões MUITO grandes e complexas

– Exemplo: dimensão cliente inclui morada, família, rendimentos, …

(17)

Análise Dimensional – 1

• Dois conceitos devem ser considerados como uma dimensão

ou como duas?

• Exemplo:

– Promoções: anúncios, descontos, cupões, e fim de stock – Opção A: 4 dimensões, uma para cada tipo de promoção – Opção B: 1 dimensão para todas as promoções

• Factores importantes

– Como é que os utilizadores pensam nos dados?

• Frequente promoções serem combinadas (ex. anúncios + cupões)

• Cada linha da tabela de dimensão representaria uma combinação possível

(18)

Análise Dimensional – 2

• Interrogações OLAP limitadas pelos acessos ao disco

– Mais espaço ocupado no disco  geralmente pior desempenho – Modelação dimensional afecta directamente o espaço ocupado

• Exemplo

– 100 milhões de linhas de factos

– 3 medidas de factos de 4 bytes cada uma (12 bytes no total)

– 100 atributos de dimensões, cada um ocupando 20 bytes em média

• 3 opções de modelação

A. Uma dimensão com todos os atributos B. Cada atributo numa tabela de dimensão

C. 5 dimensões: data, produto, loja, promoção, IDTransacção

(19)

Análise Dimensional – 3

• Opção A: uma só tabela de dimensão

– Tabela de dimensão ilegível

• Tabela de factos muito estreita

– 16 bytes/linha = (4+4+4)_medidas + 4_fkDimensão

– Espaço em disco = 100 milhões linhas × 16 bytes/linha ≈ 1,5 GBytes

• Tabela de dimensões muito larga

– 2000 bytes /linha = 20 bytes/atributo × 100 atributos de dimensão – Tabela dimensões tem mesmo número de linhas que tabela de factos – Espaço em disco = 100 milhões linhas × 2000 bytes/linha ≈ 186 GBytes

(20)

Análise Dimensional – 4

• Opção B: uma tabela por atributo de dimensão

– Designs tipo centopeia (com demasiadas dimensões)

• Tabela de factos larga

– Assumindo chaves estrangeiras com 2 bytes – 212 bytes/linha = 12_medidas + (100 × 2)

– Espaço em disco = 100 milhões linhas × 212 bytes/linha ≈ 20 GBytes

• Tabelas de dimensões muito finas e com poucas linhas

– Cada linha da tabela de dimensão ocupa 22 bytes = 2_chave + 20_atributo – 2 bytes da chave primária permitem até 65536 = 216 _{valores diferentes} – Espaço máximo/tabela = 65536 linhas × 22 bytes/linha ≈ 1,4 MBytes – Espaço em disco = 100 tabelas × 1,4 MBytes/tabela ≈ 138 MBytes

(21)

Análise Dimensional – 5

• Opção C: 5 dimensões “naturais”

– Data, produto, loja, promoção, IDTransacção

• Tabela de factos fina

– 2 bytes para cada chave estrangeira que referencia data e loja – 4 bytes cada referência para produto, promoção, e IDTransacção – 28 bytes/linha = 12_medidas + (2 × 2) + (4 × 3)

– Espaço em disco = 100 milhões linhas × 28 bytes/linha ≈ 2,6 GBytes

• Tabelas de dimensões largas e com poucas linhas

– Espaço ocupado muito inferior a 1 GByte para todas as dimensões

(22)

Medidas e Aditividade

• Tabelas de factos geralmente contêm medidas aditivas

– Permitem a soma de potencialmente milhões de valores – Soma é das operações mais úteis

• Exemplos

– Quantidades, vendas, erros, durações

• Medidas não aditivas

– Temperaturas

– Médias, mínimos, máximos

• Devem ser apenas guardados dados agregáveis

– Em vez de preço unitário, guardar quantidade vendida e valor total – Regra geral, guardar o numerador e denominador em vez do resultado

Mês Vendas

Abril 1 455,02 Maio 1 845,32 Junho 2 012,23

(23)

Medidas Semi-Aditivas

• Tabelas de facto de tipo snapshot são semi-aditivas

– Saldos diários de conta bancária

– Níveis diários de inventário num armazém

• Nestes casos, medidas só são aditivas em algumas dimensões

– Não faz sentido adicionar saldos/níveis de inventário ao longo do tempo

– Mas é legítimo somar níveis de inventário na dimensão produto

• Soma da quantidade de cada produto é total de produtos no armazém

• Operações agregadoras semi-aditivas

(24)

Tabelas de Factos sem Factos

• Servem para resolver problema do registo de eventos

mesmo sem outra informação relevante

• Exemplo clássico

– Registo de presenças em aulas

– Dimensões: data, aluno, professor, disciplina, sala – Nenhuma medida registada