1
Tópicos Avançados de Bases de Dados
Henrique Madeira 2004/2005
Henrique Madeira, DEI-FCTUC, 2001
Data Warehousing e OLAP
3
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Bibliografia
(tópico de Data Warehousing)•Apontamentos do docente;
•Livros sobre DW:
- The Data Warehouse Lifecycle Toolkit, Ralph Kimbal, Ed. J. Wiley & Sons, Inc, 2001.
- "The Data Warehouse Toolkit", Ralph Kimbal, Ed. J. Wiley & Sons, Inc, 1996;
- "Building the data warehouse", W. H. Inmon, Ed. J. Wiley & Sons, Inc, 1996;
- "The data model resouce book", L. Silverston, W. H. Inmon e K. Graziano, Ed. J. Wiley
& Sons, Inc, 1997;
- "Data Warehousing, Concepts, Technologies, Implementations, and Management", Harry Singh, Ed. Prentice Hall, 1998;
- "The Internet Data Warehouse", Rick Tanler, Ed. J. Wiley & Sons, Inc, 1997;
- "Managing the Data Warehouse", W. H. Inmon, J. Welch, K. Glassey, Ed. J. Wiley &
Sons, Inc, 1997;
- "Oracle data warehousing", M. Corey e M. Abbey, Osborne McGraw Hill, 1997;
Henrique Madeira, DEI-FCTUC, 2001
Características genéricas das Data
Warehouses
5
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
O que é uma data warehouse?
• Base de dados de grande dimensão que armazena dados para apoio à decisão estratégica.
• São construídas a partir de bases de dados operacionais e de outros sistemas usados numa organização.
BD operacionais
e outros sistemas Data Warehouse
Utilizadores Utilizadores
Henrique Madeira, DEI-FCTUC, 2001
Volume de dados
• Até 20 Gbytes
Pequena dimensão; corre num bom PC
• De 20 a 100 Gbytes
Média dimensão; precisa de workstationpoderosa
;
• De 100 Gbytes a 1 TBytes
Grande dimensão; servidores poderosos, normalmente com processamento paralelo
• Superior a 1 TBytes
Enorme dimensão; necessita processamento maciçamente paralelo.
7
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Algumas características de DW
• Dependência temporal;
• Não volatilidade;
• Orientadas para fins específicos;
• Integração e consistência informação;
• Estrutura de dados optimizada para a consulta.
Henrique Madeira, DEI-FCTUC, 2001
Dependência temporal
• Os dados na DW foram recolhidos ao longo do tempo (não são instantâneos);
• É preciso adicionar aos dados o instante temporal a
que estes se reportam.
9
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Não volatilidade
• Os dados numa DW são actualizados;
• A DW armazena os dados históricos (memória histórica) das BD operacionais de onde foi gerada;
• Depois de carregados (a partir de uma BD operacional) a única operação é fazer queries.
Henrique Madeira, DEI-FCTUC, 2001
Orientadas para fins específicos
• Devem ser guardados apenas os dados relevantes para a tomada de decisões;
• Muitos dados necessários à gestão do dia-a-dia dos
sistemas operacionais não têm relevo para a DW.
11
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Integração e consistência de informação
• No ambiente operacional a mesma informação pode residir em dados com nome e aspecto diferente;
• É necessário integrar e dar consistência aos dados provenientes das BD operacionais antes de os armazenar na DW.
Henrique Madeira, DEI-FCTUC, 2001
Optimização das consultas
• Uma vez carregados, os dados só são alvo de consultas;
• As DW têm quantidades enormes de dados.
Visão multidimensional Desnormalização parcial
Os dados devem ser
armazenados de
forma a acelerar ao
máximo as consultas
13
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Visão genérica do modelo dimensional
Henrique Madeira, DEI-FCTUC, 2001
Modelo dimensional
• Modelo usual em bases de dados operacionais: E/R
• O modelo dimensional é uma alternativa – contém a mesma informação...
– organiza-a de forma simétrica orientada para o utilizador:
• Fácil compreensão
• Bom desempenho nas pesquisa
• Data Warehouses construídas sobre E/R complexos
falham
15
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Modelo multidimensional
Jan Fev Mar Abr Leite
Farinha Açúcar Café Continente Coimbra
Continente Leiria
Vendas
Produto Hipermercado
Data
Henrique Madeira, DEI-FCTUC, 2001
Exemplo de esquema em estrela
Tempo
Venda ID_data
Dia
Dia_da_semana Semana_do_ano Mês
Trimestre Ano
Produto ID_produto Nome Tipo Marca Categoria Embalagem Descrição
Loja ID_loja Nome Localidade Distrito Área Nº_Caixas
Cadeia de Lojas
ID_data ID_produto ID_loja Unid_vendidas Custo_compra Valor_venda Nº_Clientes
17
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Modelo em estrela
O modelo dimensional típico conduz a uma estrutura em estrela, contendo uma tabela central com os factos à qual estão ligadas as tabelas das dimensões.
ID_dimensão 1 ID_dimensão 2 ID_dimensão 3 ID_dimensão 4
Facto 1 Facto 2
.. Facto n. Tabela dimensão 1
Tabela Factos ID_dimensão 1
Descrição 1 Atributo
.. .
Tabela dimensão 2 ID_dimensão 2
Descrição 2 Atributo
.. .
Tabela dimensão 3 ID_dimensão 3
Descrição 3 Atributo
.. .
Tabela dimensão 1 ID_dimensão 4
Descrição 4 Atributo
.. .
Henrique Madeira, DEI-FCTUC, 2001
Tabela de factos
• Contém as medidas do negócio
• Os factos mais uteis são – numéricos
– aditivos
• Representam relacionamentos M:1 com as
dimensões do negócio
19
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Tabelas de dimensão
• Tabelas companheiras da tabela de factos
• Cada dimensão representa parâmetros do negócio – tempo, clientes, produtos, etc
• Chave primária determina o dado específico
• Outros atributos específicos da dimensão
• Desnormalizada e com hieraquias.
Henrique Madeira, DEI-FCTUC, 2001
OLAP - Online Analytical Processing
• Pesquisa e apresentação de texto e dados numéricos das Data Warehouses
• ROLAP (Relational OLAP)
– Estrutura, interfaces com o utilizador e aplicações que permitem implementar o modelo dimensional num motor de base de dados relacional
• MOLAP (Multidimensional OLAP)
– o mesmo sobre um motor não relacional
21
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Pesquisas - baixo nível
Tempo
Venda ID_data
Dia
Dia_da_semana Semana_do_ano Mês
Trimestre Ano
Produto ID_produto Nome Tipo Marca Categoria Embalagem Descrição
Loja ID_loja Nome Localidade Distrito Área Nº_Caixas
Cadeia de Lojas
ID_data ID_produto ID_loja Unid_vendidas Custo_compra Valor_venda Nº_Clientes
Select avg(Valor_venda x Unid_vendidas) from Venda V, Tempo T, Produto P where JOIN_TABELAS
group by P.Marca, T. Mês
Henrique Madeira, DEI-FCTUC, 2001
Interfaces com o utilizador
Exploração de dados na Data Warehouse – Ferramenta OLAP típica
• acesso a motor relacional via SQL
• apresentação em tabela, gráfico, relatório, etc
• normalmente orientado para pesquisas ad-hoc
– Outras ferramentas
• Data mining
• Modelação
23
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Browsing
• Explorar uma das dimensões definindo
restrições e escolhendo as colunas pretendidas.
coluna Nome_Loja Localidade Distrito Area_total Nºcaixas
restrição
valores Loja Zé Ansião Coimbra 250 2
distintos Super Mário Aveiro Leiria 500 4
Super Bill Coimbra Aveiro 750 6
Loja da Maria Leiria 1000
Loja do Manel Penacova 1500
John's Market Penela
Vieiras Pombal
Loja 007 Cadeia Joel Loja dos 500
Henrique Madeira, DEI-FCTUC, 2001
Browsing (exemplo)
• Quais os nomes e onde se situam as lojas do distrito de Coimbra com área igual a 750 m
2e com 4 caixas?
coluna Nome_Loja Localidade Distrito Area_total Nºcaixas
restrição Coimbra 750 4
valores Loja do Manel Coimbra Coimbra 750 4
distintos John's Market Penacova Vieiras
Loja dos 500
25
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Pesquisas - Slice and Dice
Vendas por tempo
e produto Vendas por loja e marca
Henrique Madeira, DEI-FCTUC, 2001
Drill-Down & Roll-Up
Categoria mais genérica
Categoria intermédia
Categoria mais detalhada
Detalhe completo
Drill-Down Roll-up
27
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Tempo: Drill-Down & Roll-Up
Ano Trimestre
Mês
Semana
Drill-Down Roll-up
Dia
ALL
Henrique Madeira, DEI-FCTUC, 2001
Tempo: Drill-Down (exemplo)
Select avg(Valor_venda x Unid_vendidas) from Venda V, Tempo T, Produto P where JOIN_TABELAS
group by P.Marca, T. Mês;
Select avg(Valor_venda x Unid_vendidas) from Venda V, Tempo T, Produto P where JOIN_TABELAS
group by P.Marca, T. Dia;
• Questão: como se representa o “ALL” na pesquisa?
29
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Arquitectura geral da Data Warehouse
Henrique Madeira, DEI-FCTUC, 2001
Elementos básicos de uma data warehouse
BDs operacionais
Sistemas legados
Folhas de cálculo, ficheiros, ...
Fontes externas
Data Staging
Area
Data warehouse (presentation servers)
Utilizadores
ROLAP/
MOLAP
Ad hoc queries Relatórios Aplicações específicas
Modelos e outras ferramentas
31
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Data Marts
• É, normalmente, um subconjunto de uma DW;
• Numa Data Mart os dados são focalizados numa área específica (processo de negócio);
• Muitas vezes uma Data Mart é feita para responder rápidamente a uma área de actividade.
Henrique Madeira, DEI-FCTUC, 2001
Arquitectura de BDs de uma organização 1
BDs operacionais
Data Warehouse
Data Mart Sistemas legados
Folhas de cálculo, ficheiros, ...
Fontes externas
Utilizadores Utilizadores
33
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Arquitectura de BDs de uma organização 2
BDs operacionais
Data Warehouse
Data Mart Sistemas legados
Folhas de cálculo, ficheiros, ...
Fontes externas
Utilizadores
Utilizadores
Henrique Madeira, DEI-FCTUC, 2001
Sistemas fonte
• sistema de registo de transacções
• gestão de clientes, gestão de produtos, gestão de vendas, etc…
• principais características assumidas
• disponibilidade
• pesquisas típicas limitadas a fichas individuais
• mantêm pouca informação histórica
• A obtenção de relatórios de gestão é complicada e pesada
• Pouca ligação com restantes sistemas da empresa
– registos de facturação não ligados a base de produtos ou clientes
35
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Área de processamento temporário (Staging Area)
• Área e processos que actuam sobre os dados fonte
• limpeza
• transformação
• combinação
• preparação
Staging Area
Data Warehouse
Henrique Madeira, DEI-FCTUC, 2001
Metadados
• É necessário uma estrutura (na prática outra base de dados) para descrever os dados da DW. Deve
descrever :
– Que dados existem na DW;
– Qual o seu formato;
– Onde estão armazenados;
– Como se relacionam com os dados de outras bases de dados;
– Qual a proveniência dos dados e quem são os seus donos.
37
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Processos básicos da DW
• Extracção
(a partir dos sistemas fonte)• Transformação e limpeza de dados
(na staging area)• Carregamento e indexação
• Tratamento de erros
• Pesquisa
(utilização normal)Henrique Madeira, DEI-FCTUC, 2001
Transformação
• Limpeza dos dados
• Eliminação de campos inuteis
– campos dos sistemas opeacionais que são desnecessários na DW
• Combinação de fontes de dados
– coincidência exacta de chaves ou “fuzzy matches”
• Criação de chaves primárias da DW independentes dos sistemas operacionais
• Criação de dimensão temporal
• Construção de agregados para melhoria de velocidade em
pesquisas
39
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Limpeza dos dados
• Limpeza dos dados
– correcção de erros (de escrita)
– correcção de inconsistências (cidade-código postal) – eliminação de duplicados (o mesmo nome PEDRO e
Pedro)
– tratamento de faltas de dados (campos vazios) – pôr os dados em formatos standard
Henrique Madeira, DEI-FCTUC, 2001
Carregamento
• Preenche dimensões e factos temporários com dados do período em causa
• Realiza o carregamento BULK LOAD
– carregamento ficha-a-ficha seria demasiado lento
• Indexa os dados carregados
Transformação Carregamento
Staging Area
DW
41
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
BD operacionais vs Data Warehouses
Dados operacionais Dados da Warehouse
Objectivos operacionais Registo histórico
Acessos de leitura/escrita Acessos só de leitura
Acesso por transacções pré-definidas Acesso por queries ad hoc e relatórios periódicos Acesso a poucos registos de cada vez Muitos registos em cada acesso
Dados actualizados em tempo real Carregamentos periódicos de mais dados Estrutura optimizada para actualizações Estrutura optimizada para queries complexas Event-driven: os processos geram dados Data-driven: os dados geram respostas
Henrique Madeira, DEI-FCTUC, 2001
Visão genérica sobre o processo de
construção de uma Data Warehouse
43
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Infraestrutura para projecto
Construir uma DW é complexo e requer
conhecimento especializado em várias áreas
• Definir equipa;
• Definir ferramentas e sistemas;
• Identificar fases do projecto;
• Definir métodos de trabalho;
• Identificar responsabilidades para cada tarefa/fase.
Henrique Madeira, DEI-FCTUC, 2001
Planeamento do projecto Definição dos requisitos do negócio
Modelação dimensional
Desenho físico
Desenho do Data Staing
Colocação em Produção
Gestão do projecto Especificação
das aplicações de utilizador
Desenvolvimento das aplicações
Selecção e instalação de
produtos Desenho da
arquitectura
Esquema geral do projecto de uma Data Warehouse
(R. Kimball)
45
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Passos na construção de uma DW
• Identificação de objectivos (de gestão) a atingir com a DW;
• Definir infraestrutura para o projecto;
• Identificar modelo de dados das BD operacionais fonte;
• Definir modelo de dados para a DW;
• Definir regras para o mapeamento de dados;
• Extrair, integrar, purificar e consolidar os dados;
• Ferramentas de exploração, afinação de desempenho e avaliação de eficácia.
Henrique Madeira, DEI-FCTUC, 2001
Objectivos a atingir com a DW
É necessário ter um entendimento profundo do processo de negócio que a DW vai apoiar.
• Quais são os objectivos e estratégia da empresa/instituição?
• Qual a informação necessária para atingir esses objectivos?
• Porque é que a informação é necessária?
• Quem vai usar essa informação (dentro da empresa)?
• Como é que a informação vai ser usada?
47
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Identificar modelo de dados das BD fonte
Bases de dados operacionais
Henrique Madeira, DEI-FCTUC, 2001
Modelo de dados das BD fonte (cont.)
• Responder à questão: quais os dados fonte para a DW?
• Muitas vezes os modelos de dados das BD
operacionais não existem ou estão desactualizados;
• Necessário usar ferramentas de reverse-engineering;
• Alguns dados da DW podem ter outras origens que
não as BD operacionais.
49
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Dados históricos, de referência e sínteses
• No processo de indentificar os dados a extrair para a DW é útil olhar os dados sob grandes grupos:
– Dados históricos (factos)
Dados correspondentes a entidades que descrevem factos (vendas, encomendas, facturas, consultas, pagamentos, etc)
– Dados de referência (dimensões)
Dados correspondentes a entidades de referência que permitem completar e situar os dados dos factos históricos (clientes, fornecedores, pessoas, etc)
– Sínteses
Dados previamente calculados e que se prevê virem a ser necessários (relatórios de vendas mensais, movimentos semanais de stock, etc)
Henrique Madeira, DEI-FCTUC, 2001
Definir modelo de dados da DW
• Desenvolver/entender o modelo de negócio da DW, identificar processos de negócio e identificar dados disponíveis (nas BDs operacionais);
• Par cada processo de negócio:
– Identificar os factos (valores numéricos);
– Escolher a granularidade dos factos (determina a precisão com que poderá ser feita a análise);
– Definir as dimensões de interesse.
51
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Modelos na construção de uma DW
Modelo do negócio (ER)
Modelo dimensional
Modelo físico Desnorm
alização siste
mática
Que transações?
Que queries?
Henrique Madeira, DEI-FCTUC, 2001
Definir regras para o mapeamento de dados
• Identificar os dados a extrair;
• Identificar os dados que faltam (impossíveis de extrair das BD operacionais);
• Definir regras e processos para integrar, compatibilizar e “limpar” os dados;
• Documentar todas os passos para permitir que os dados históricos possam ser entendidos
posteriormente.
53
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Extrair, integrar, purificar e racionalizar os dados
• Usar ou construir as ferramentas que concretizam as regras para mapeamento dos dados;
• Rever regras e processos de mapeamento sempre que são detectadas inconsistências;
• Documentar todos os passos.
Henrique Madeira, DEI-FCTUC, 2001
Exploração, afinação e avaliação de eficácia
• Definição/construção de ferramentas de exploração;
• Afinação de desempenho;
• Administração da data warehouse.
55
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
O modelo multidimensional
Henrique Madeira, DEI-FCTUC, 2001
Modelo multidimensional
• Factos armazenados num array multidimensional;
• As dimensões são usadas para indexar o array;
• Normalmente construídas sobre bases de dados relacionais.
Jan Fev Mar Abr Leite
Farinha Açúcar Café Continente Coimbra
Continente Leiria
Vendas
Produto Hipermercado
Data
57
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Exemplo de esquema em estrela
Tempo
Venda ID_data
Dia
Dia_da_semana Semana_do_ano Mês
Trimestre Ano
Produto ID_produto Nome Tipo Marca Categoria Embalagem Descrição
Loja ID_loja Nome Localidade Distrito Área Nº_Caixas
Cadeia de Lojas
ID_data ID_produto ID_loja Unid_vendidas Custo_compra Valor_venda Nº_Clientes
Henrique Madeira, DEI-FCTUC, 2001
Modelo em estrela
O modelo dimensional típico conduz a uma estrutura em estrela, contendo uma tabela central com os factos à qual estão ligadas as tabelas das dimensões
ID_dimensão 1 ID_dimensão 2 ID_dimensão 3 ID_dimensão 4
Facto 1 Facto 2
.. Facto n. Tabela dimensão 1
Tabela Factos ID_dimensão 1
Descrição 1 Atributo
.. .
Tabela dimensão 2 ID_dimensão 2
Descrição 2 Atributo
.. .
Tabela dimensão 3 ID_dimensão 3
Descrição 3 Atributo
.. .
Tabela dimensão 1 ID_dimensão 4
Descrição 4 Atributo
.. .
59
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Algumas características do modelo em estrela
ID_dimensão 1 ID_dimensão 2 ID_dimensão 3 ID_dimensão 4 Facto 1 Facto 2 .. Facto n. Tabela
dimensão 1
Tabela Factos ID_dimensão 1
Descrição 1 Atributo
.. .
Tabela dimensão 2 ID_dimensão 2 Descrição 2
Atributo .. .
Tabela dimensão 3 ID_dimensão 3 Descrição 3
Atributo .. .
Tabela dimensão 1 ID_dimensão 4 Descrição 4
Atributo .. .
Tabela de Factos
• Constituída por atributos numéricos (factos) e pelas chaves forasteiras que a ligam à tabelas de dimensões;
• A tabela de factos está bastante normalizada;
• Contém normalmente uma enorme quantidade de registo (ocupa vulgarmente mais de 95% do espaço da DW).
Tabelas de Dimensões
• Há tantas dimensões quantas vertentes sob as quais se pretende analisar os factos;
• As tabelas de dimensões são fortemente desnormalizadas, sendo normalmente tabelas com muitos atributos;
• Normalmente, apesar de terem muitos atributos, contêm poucos registos (quando comparados com a tabela de factos).
Henrique Madeira, DEI-FCTUC, 2001
Passos para definir modelos em estrela
1 - Identificar os processos de negócio/actividade 2 - Identificar os factos;
3 - Identificar dimensões;
4 - Escolher a ganularidade dos dados a registar.
Semperder de vistas osdados efectivamentedisponíveis(BDs
operacionais, ficheiros, etc)
61
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Exemplo – Cadeia de supermercados
• Cadeia de supermercados de uma mesma empresa
• Vamos pensar apenas nas vendas (a aquisição de produtos aos fornecedores é global para toda a empresa)
• Cada supermercado tem vários departamentos (mercearia, higiene e limpeza, etc)
• Vende vários milhares de produtos
• Os produtos são identificados univocamente por códigos.
– código SKU (“Stock Keeping Units”) – códigos de barras universais SKU = UPC
Henrique Madeira, DEI-FCTUC, 2001
Dados do negócio
• Onde recolher os dados?
Caixa registadora (POS - point of sales). Na prática, os dados são recolhidos na base de dados que gere as existências, sendo as caixas registadoras meros terminais.
• O que interessa medir?
Vendas
• Qual o objectivo?
Maximização do lucro
• máximo preço de venda possível
• mais baixos custos de aquisição e administrativos
• mais clientes
63
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
• Identificar os factos de Vendas
– Exemplo de factos relevantes para a gestão: Número de unidades vendidas, custo do produto quando fornecido pelo vendedor, valor total das vendas do produto, número de clientes que comprou o produto.
– Questão: será que é possível obter dados base (no sistema operacional) para obter estes factos?
Dimensão Tempo Dimensão Tempo
ID_data atributos
……….
Dimensão Produto Dimensão Produto
ID_produto atributos
……...
Dimensão Loja Dimensão Loja
ID_loja atributos
……...
ID_promoção atributos
……...
Dimensão Promoção Dimensão Promoção ID_data
ID_produto ID_loja ID_promoção Unid_vendidas Custo_compra Valor_venda Nº_clientes Factos VENDAS Factos VENDAS
Factos - Cadeia de Lojas
Henrique Madeira, DEI-FCTUC, 2001
Dimensões - Cadeia de Hipermercados
• Dimensões principais – Produto x Tempo x Loja
• Existirão outras dimensões de interesse?
– Fornecedores?
– Promoções?
– Cliente?
– Nome do empregado responsável naquele dia?
• É normalmente possível adicionar dimensões extra às dimensões principais
• Todas dimensões tomam um só valor para cada combinação
65
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Dimensão DimensãoTempoTempo
ID_data atributos
……….
Dimensão Produto Dimensão Produto
ID_produto atributos
……...
Dimensão Loja Dimensão Loja
ID_loja atributos
……...
ID_promoção atributos
……...
Dimensão Promoção Dimensão Promoção ID_data
ID_produto ID_loja ID_promoção Unid_vendidas Custo_compra Valor_venda Nº_clientes Factos VENDAS Factos VENDAS
Dimensões - Cadeia de Hipermercados
Henrique Madeira, DEI-FCTUC, 2001
Granularidade
• Exemplo: registar as vendas de todos os produtos diariamente
• Podemos ver de forma detalhada que produtos são vendidos e em que lojas, a que preços e em que dias, …
• Granularidade:
produtosx lojax promoçãox dia
• A granularidade determina a dimensionalidade da DW e tem um forte impacto no seu tamanho
• A granularidade deve ser adequada às necessidades
de análise.
67
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Granularidade: alternativas
• Porquê SKU em vez de marca ou tipo de produto?
– Valerá a pena ter tantas unidades de um determinado tamanho para um dado produto?
• Ao nível da factura
– tamanho da base de dados poderia tornar-se gigantesco – identificação do cliente não existe
• assim não é possivel analisar os dados de comportamento de compras
• Semanal ou mensal
– perder-se-iam efeitos interessantes a nível diário
• variações de vendas entre 2ªs e Sábados
• efeitos de promoções de dois dias
Henrique Madeira, DEI-FCTUC, 2001
Detalhe selectivo
• Porque é que os dados devem ser expressos com um detalhe grande num Data Warehouse?
– Não por ser necessário aceder a valores específicos – … mas as pesquisas “cortam” dimensões selectivamente
e de forma precisa
Select … Join …
Group By produto, mês
69
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Refinar o modelo
• Detalhar as dimensões
• Rever os factos
• Verificar a consistência (entre factos, entre factos e dimensões, etc)
• Reavaliar granularidade
Henrique Madeira, DEI-FCTUC, 2001
Exemplo 1 - Cadeia de lojas
Tempo Tempo
ID_data Dia_do_mês Dia_da_semana Dia_do_ano Semana_do_ano Mês
Número_do_mês Trimestre Período_fiscal Flag_feriado Flag_dia_semana Flag_últ_dia_mês Estação_ano Aconteci_espec
……….
Produto Produto
ID_produto Número Nome Marca Categoria Subcategoria Departamento Tam_embalagem Tipo_embalagem Tipo_dieta Peso
Unidade_de_peso Quant_caixa Caixas_p_pallete Larg_prateleira Altura_prateleira Profun_prateleira
……...
Loja Loja
ID_loja Nome Número_loja Endereço Localidade Código_postal Distrito Região Telefone Fax Gestor_loja Área_total Área_mercearias Área_congelados Área_bazar Nº_Caixas Data_inauguração Data_ult_remod.
……...
ID_produto ID_data ID_Loja ID_Promoção Unid_vendidas Custo_compra Valor_venda Nº_clientes
ID_promoção Número Nome_promo Tipo_red_preço Tipo_anúncio Tipo_cartaz Tipo_coupons Meio_anúncio Meio_cartaz Custo_promoção Início_promoção Fim_promoção
……...
Promoção Promoção
71
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Exemplo 1: Dimensão tempo
Tempo
ID_data Dia_do_mês Dia_da_semana Dia_do_ano Semana_do_ano Mês
Número_do_mês Trimestre Período_fiscal Flag_feriado Flag_dia_semana Flag_último_dia_mês Estação_ano Acontecimento_espec
……….
• Existe sempre, pois representa a dependência temporal inerente à DW;
• Deve descrever o tempo tal como ele é visto para fins de gestão da actividade (negócio) em causa;
• Deve conter a caraterização do tempo nos atributos pelos quais se pretende posteriormente fazer pesquisas;
• É gerada, normalmente, de uma forma sintética (i.e., sem ser a partir de uma BD operacional) para todo o período de tempo considerado na DW.
Henrique Madeira, DEI-FCTUC, 2001
Exemplo 1: Dimensão produto
Produto
ID_produto Número Nome Marca Categoria Subcategoria Departamento Tam_embalagem Tipo_embalagem Tipo_dieta Peso
Unidade_de_peso Quantidade_caixa Caixas_por_pallete Largura_prateleira Altura_prateleira Profud_prateleira
……...
• Deve conter a caraterização dos produtos tal como eles são vistos pelo gestor da cadeia de lojas;
• Contém todos os atributos pelos quais se pretende posteriormente fazer perguntas;
• Como acontece normalmente nas tabelas de dimensões, é uma tabela bastante desnormalizada.
73
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Exemplo 1: Dimensão loja
Loja
ID_loja Nome Número_loja Endereço Localidade Código_postal Distrito Região Telefone Fax Gestor_loja Área_total Área_mercearias Área_congelados Área_bazar Nº_Caixas Data_inauguração Data_ultim_remod.
……...
• Contém a caraterização das lojas tal como eles são vistos pelo gestão da cadeia de lojas;
• Contém todos os atributos pelos quais se pretende posteriormente fazer perguntas, incluindo atributos de natureza geográfica (localização) e de natureza temporal (datas de inauguração,…).
Henrique Madeira, DEI-FCTUC, 2001
Exemplo 1: Dimensão promoções
ID_promoção Número Nome_promo Tipo_red_preço Tipo_anúncio Tipo_cartaz Tipo_coupons Meio_anúncio Meio_cartaz Custo_promoção Início_promoção Fim_promoção
……...
Promoção • Contém a caraterização das promoções efectuadas;
• Neste exemplo há apenas uma dimensão de promoções (para todos os tipos de promoções), mas seria possível ter em alternativa uma dimensão para cada tipo de promoção;
• A dimensão promoção representa, neste exemplo, uma dimensão muito sensível e importante, pois as promoções são um dos aspectos em que o gestor mais facilmente pode actuar quando pretende incrementar as vendas numa loja ou num determinado produto.
75
Tópicos Avançados de Bases de Dados, 2004/2005 Instituto Politécnico da Guarda
Henrique Madeira, DEI-FCTUC, 2001
Cálculo simplificado do espaço ocupado
Granularidade = Produtos vendidos / em cada loja / em cada dia Tempo = 3 anos
Nº Produtos = 100.000 (apenas 20% dos produtos são vendidos diariamente) Lojas = 100
Tamanho médio de registo = 8 atributos x 4 Bytes = 32 Bytes Nº de registos de factos = 3 x 365 x 20.000 x 100 = 2.190.000.000 Tamanho aproximado da DW = 32 x 2.190.000.000 =
70 GBytes
•Despreza-se o espaço ocupado pelas tabelas de dimensões;
•Não considera o armazenamento dos índices nem vistas materializadas;
Henrique Madeira, DEI-FCTUC, 2001
Exemplo 2: Existências em armazéns
Armazém
• Recepção;
• Inspecção;
• Entrada no stock;
• Autorização de venda;
• Recolha do stock;
• Embalagem;
• Saída
• Detecção de falha na inspecção Devolução ao fornecedor
• Deteriorização no manuseamento Perda do produto
• Devolução do cliente Reentrada no stock ;
Procedimentos excepcionais:
Caracterização da actividade de gestão de existências