Modelagem Dimensional para Datawarehouses

Introdução à Modelagem
Dimensional para
Datawarehouses
Fernanda Baião
[email protected]
PPGI – UNIRIO
Abril 2007
1
Tópicos
„
„
„
Inteligência do Negócio
Contexto das Organizações
A tecnologia de Data Warehouse
„
„
„
Motivação e Conceitos
Objetivos e Arquitetura
Processo de Desenvolvimento do Data Warehouse
„
Projeto do Warehouse
„
Modelagem Multidimensional
2
1
Ambiente de aplicações
Adaptado de:
Carlos Barbieri
BI: Business Intelligence - Modelagem e Tecnologia
3
Inteligência do Negócio (BI)
„
“Utilização de várias fontes de
informação para se definir estratégias
de competitividade nos negócios da
empresa”
„
Problema da “inundação de informações”
„
„
„
„
“Montanhas de dados”
Busca e recuperação de informação são difíceis
Apresentação da informação
Tomadas de decisão são prejudicadas
4
2
Inteligência do Negócio
„
Desafio:
„
Definir regras e técnicas para a
apresentação adequada deste volume de
dados visando transformá-los em depósitos
estruturados de informação, independente
da sua origem
5
Contexto das Organizações
„
“2 mundos de informação diferentes”
„
„
„
Operacional (execução) x Gerencial (Planejamento
e Controle)
Objetivos distintos
Requisitos de ambientes de BD distintos
Informação
Informação
6
3
Ambientes de BD Operacionais
„
Dão suporte às funções associadas à execução do
negócio da empresa:
„
„
sistemas administrativos, de controle de estoque,
assinaturas de clientes, etc.
Tipo de processamento: OLTP
„
„
„
„
„
„
On-line transactional processing
transações pontuais (1 registro por vez)
velocidade e automação de funções “repetitivas”
atualizações e consultas em grande número
trabalha com alto nível de detalhe
situação corrente
7
Ambientes de BD Gerenciais
„
Dão suporte às atividades de tomada de decisões
gerenciais na organização
„
„
Sistemas de suporte à decisão, ferramentas de análise
Tipo de processamento: OLAP
„
„
„
„
„
„
„
„
On-line analytical processing
“Pequeno” número de consultas “variáveis”
centenas, milhares, ... de registros por consulta
Diversas fontes de dados
Diferentes perspectivas
Operações de agregação e cruzamentos
Atualização quase inexistente, apenas novas inserções
Dados históricos são relevantes
8
4
O que é um Data Warehouse
Data Warehouse
“Uma coleção de dados...
Orientados ao assunto
Integrados
Não voláteis
Variantes no tempo
... Para fornecer suporte ao processo de tomada de
decisões na organização” [Inmon, 92]
9
O que é um Data Mart
Data Mart
“Um subconjunto lógico do Data Warehouse,
geralmente visto como um data warehouse
setorial.” [Kimball]
10
5
Objetivos de um Data
Warehouse
„
„
„
„
„
Integrar dados de múltiplas fontes
Facilitar o processo de análise sem impacto para o
ambiente de dados operacionais
Obter informação de qualidade
Atender diferentes tipos de usuários finais
Ser flexível e ágil para atender novas análises
Sistema de
Apoio à Decisão
Fontes de Dados
Operacionais
DW
Ferramenta
OLAP
Sistema de
Mineração de Dados11
Arquitetura de um Ambiente
de Data Warehouse
„
„
[Han e Kamber, 2001]
Componentes
„
„
Dados Operacionais
3 camadas
„
„
„
Camada Interna: Servidor DW
Camada Intermediária: Servidor OLAP
Camada Externa: Ferramentas de acesso aos
dados
12
6
Arquitetura de um Ambiente
de Data Warehouse
Análise
Relatórios
Ferramentas
Mineração de Dados
Servidor
OLAP
DW
Extração
Limpeza
Transformações
Carga
Atualização
Metadados
Servidor
DW
Data Marts
Dados
BDs operacionais
Fontes externas
13
Processo de Desenvolvimento de DWs
„
Etapas:
„
„
„
„
„
Planejamento
Levantamento de Requisitos e Análise do
Problema
Projeto do Data Warehouse
Integração de Dados e Testes
Implantação
14
7
Projeto do Data Warehouse
„
Requisitos
„
„
„
flexibilidade e agilidade para suportar
análises não previstas
Permitir análise das medidas
disponibilizadas sob diferentes perspectivas
Abordagem utilizada:
MODELAGEM DIMENSIONAL
15
Visão multidimensional
„
Forma pela qual analistas de negócio,
gerentes e executivos analisam informações
Qual o total de
vendas do
Produto X na Região
Sudeste no mês de
janeiro ?
Como foi a distribuição
das vendas do Produto
X em todas as cidades
no ano passado?
Em quanto
aumentaram as
vendas do Produto X
durante a promoção
em abril de 2003 ?
16
8
Visão multidimensional
„
Foco no cruzamento das informações
„
„
„
„
Facilita o entendimento e visualização de
problemas típicos de suporte à decisão
Mais intuitiva para o processamento analítico
Utilizada pelas ferramentas OLAP
Qual a diferença da visão multidimensional
para a visão “tabular” do modelo relacional?
17
Visão Relacional
„
Volume de vendas para a concessionária XCar
MODEL
CITY
SALES VOLUME
MINI VAN
NEW YORK
MINI VAN
LOS ANGELES
5
MINI VAN
MADISON
4
SPORTS COUPE
NEW YORK
3
SPORTS COUPE
LOS ANGELES
5
SPORTS COUPE
MADISON
5
SEDAN
NEW YORK
4
SEDAN
LOS ANGELES
3
SEDAN
MADISON
2
6
18
9
Visão multidimensional
„
Volume de vendas para a concessionária Xcar
M
O
D
E
L
Mini Van
6
5
4
Coupe
3
5
5
Sedan
4
3
2
NY
LA
Madison
CITY
„
„
Um vetor multidimensional tem um número fixo de
dimensões e os valores são armazenados nas células
Cada dimensão consiste de um número de elementos
Acrescentando mais uma
coluna...
„
Volume de
vendas por
fornecedor
MODEL
CITY
DEALERSHIP
MINI VAN
MINI VAN
MINI VAN
MINI VAN
MINI VAN
MINI VAN
MINI VAN
MINI VAN
MINI VAN
SPORTS COUPE
SPORTS COUPE
SPORTS COUPE
SPORTS COUPE
SPORTS COUPE
SPORTS COUPE
SPORTS COUPE
SPORTS COUPE
SPORTS COUPE
SEDAN
SEDAN
SEDAN
SEDAN
SEDAN
SEDAN
SEDAN
SEDAN
SEDAN
NEW YORK
NEW YORK
NEW YORK
LOS ANGELES
LOS ANGELES
LOS ANGELES
MADISON
MADISON
MADISON
NEW YORK
NEW YORK
NEW YORK
LOS ANGELES
LOS ANGELES
LOS ANGELES
MADISON
MADISON
MADISON
NEW YORK
NEW YORK
NEW YORK
LOS ANGELES
LOS ANGELES
LOS ANGELES
MADISON
MADISON
MADISON
CLYDE
GLEASON
CARR
CLYDE
GLEASON
CARR
CLYDE
GLEASON
CARR
CLYDE
GLEASON
CARR
CLYDE
GLEASON
CARR
CLYDE
GLEASON
CARR
CLYDE
GLEASON
CARR
CLYDE
GLEASON
CARR
CLYDE
GLEASON
CARR
19
VOLUME
6
6
2
3
5
5
2
4
3
2
3
2
7
5
2
4
5
1
6
4
2
1
3
4
2
2
3 20
10
Visão multidimensional
„
Volume de vendas para a concessionária Xcar
Mini Van
M
O
D
E
L
Coupe
Sedan
6
1
NY
LA
Carr
Gleason
Clyde
2
DEALERSHIP
Madison
CITY
Dados podem ser imaginados como em um “cubo”
„
metáfora visual
representação intuitiva: dimensões coexistem para todo
ponto no cubo e são independentes umas das outras 21
„
„
Adicionando Dimensões Hipercubos
„
M
O
D
E
L
Volume de vendas para a concessionária
Xcar, ao longo do tempo
Mini Van
Mini Van
Coupe
Coupe
Sedan
6
1
NY
LA
2
Carr
Gleason
Clyde
Madison
CITY
JANUARY
Sedan
Mini Van
Coupe
5
10
NY
LA
1
Carr
Gleason
Clyde
Madison
CITY
FEBRUARY
Sedan
6
25
NY
LA
0
Carr
Gleason
Clyde
DEALERSHIP
Madison
CITY
MARCH
22
11
Modelagem Multidimensional
„
„
Utilização dos conceitos do modelo
multidimensional a fim de representar, de
forma clara, eficiente e flexível, a visão
multidimensional dos dados
Conceitos
„
„
Fatos
Dimensões
„
Hierarquias e Agregações
23
Fatos
„
Medidas numéricas do negócio
„
Sales Fact
date_key
product_key
store_key
dollar_sold
unit_sold
dollar_cost
„
Volume de vendas (número de itens, total em reais),
quantidade de itens em estoque, volume de
transações de cartão de crédito
Representados em uma Tabela de Fatos
„
„
„
Valores das medidas (numéricas e aditivas) e
Referências para as dimensões (granularidade)
Não armazena informação redundante! (textos,
valores zerados)
Quantidade de registros é normalmente muito grande
em um DW
25
12
Tabela de Fatos
„
Em um SGBD relacional
„
„
Sales Fact
date_key
product_key
store_key
dollar_sold
unit_sold
dollar_cost
„
„
Relação (Tabela)
Chaves estrangeiras para as tabelas de
dimensão
Chave primária é subconjunto das chaves
estrangeiras
Em um SGBD multidimensional
„
Cubo (vetor n-dimensional)
26
Dimensões
„
Product Dimension
product_key
description
brand
category
department
package type
package size
fat content
diet type
weight
weight unit of measure
storage type
...
Pontos de vista ou perspectivas do negócio
sobre os quais uma organização deseja
guardar registros
„
„
Loja, Produto, Fornecedor, Tempo
Representadas em Tabelas de Dimensão
„
Descrição completa da dimensão
„
„
Atributos textuais e de domínio discreto
preferencialmente
Essenciais para tornar o DW usável e legível
„
“Tipo de armazenamento = V” ou “Tipo de
armazenamento = Vácuo” ?
27
13
Tabela de Dimensão
Product Dimension
product_key
description
brand
category
department
package type
package size
fat content
diet type
weight
weight unit of measure
storage type
...
„
„
Chave simples
Fonte principal das cláusulas das
consultas, agrupamentos e títulos
de relatórios
Volume de vendas “por produto”
„
„
„
„
Usualmente não dependente do
tempo
Desnormalizada
Hierarquias implícitas
28
Níveis nas dimensões ou
Hierarquias
„
Hierarquias são a base das agregações
Volume de vendas
Date Dimension
date_key
date
month
year
...
Ano
Mês
Dia
Área
geográfica
NE
PE
Tempo:
abril
2003
maio
País
Brasil
SUL
NO
SE RS
SC AC AM
23
62
7
34
14
21
23
92
73
23 234
13
87
21
14
45
29
15
30
…..
56
150
Região
Estado
Area Dimension
area_key
state
region
country
...
29
14
Hierarquias e Agregados
Produto
Tempo
Geografia
Consultas
Ano
Marca
País
Categoria
Trimestre
Região
Produto
Mês
Estado
Vendas por
Produto,
Ano e
Região
30
Esquemas para o modelo
Multidimensional
„
Fatos e dimensões podem ser dispostos
segundo diferentes configurações
„
„
„
Esquema Estrela
Esquema Flocos de Neve
Esquema Constelação de Fatos
31
15
Esquema Estrela
„
1 tabela de fatos
„
„
Sem redundância
n tabelas de dimensões
„
1 para cada dimensão
O quê
onde
quando
quem
32
Esquema Estrela
Time Dimension
time_key
day
month
year
holiday_flag
Sales Fact
time_key
product_key
store_key
dollar_sold
unit_sold
dollar_cost
Product Dimension
product_key
description
brand
category
Store Dimension
store_key
store_name
address
city
state
33
16
Modelagem Multidimensional:
Esquema Estrela
„
„
Dominante no projeto de DW [Kimball e Ross,
2002]
Características:
„
„
„
„
„
„
Distingue bem as dimensões dos fatos medidos
Simplifica a visualização dimensional
Simetria
Eficiente para a realização de consultas
Acomodam mudanças mais facilmente
Hierarquias são representadas pelos atributos
da dimensão
34
Exercício
„
Suponha o exemplo da concessionária Xcar já
apresentado, onde um gerente geral de
marketing deseja analisar o volume de
vendas dos modelos de carro de cada
fornecedor em cada cidade de cada estado
dos EUA, onde a concessionária possua filiais.
Especifique um esquema estrela para esta
concessionária. Dê alguns exemplos de
consultas e análises que poderiam ser úteis
para o gerente.
35
17
Exercício
„
M
O
D
E
L
Concessionária XCar
Mini Van
Mini Van
Coupe
Coupe
Carr
Gleason
Clyde
Sedan
NY
LA
Madison
Mini Van
Coupe
Carr
Gleason
Clyde
Sedan
NY
LA
Madison
Carr
Gleason
Clyde
Sedan
NY
LA
DEALERSHIP
Madison
CITY
CITY
CITY
JANUARY
FEBRUARY
MARCH
36
Esquema Floco de Neve
„
Variante do esquema estrela
„
1 tabela de fatos
„
K tabelas de dimensões
„
„
„
1 dimensão pode ter várias tabelas
Tabelas de dimensão são normalizadas
„
„
„
sem redundância
Evita redundância
Requer mais junções para as consultas
Hierarquias representadas pelos
relacionamentos entre as dimensões
39
18
Esquema Floco de Neve
40
Esquema Floco de Neve
Time Dimension
time_key
day_of_week
month
quarter
year
holiday_flag
Sales Fact
time_key
product_key
store_key
dollar_sold
unit_sold
dollar_cost
Product Dimension
product_key
description
category_key
Store Dimension
store_key
store_name
address
city_key
Category Dimension
category_key
description
brand_key
Brand Dimension
brand_key
description
City Dimension
city_key
city_name
state
41
19
Esquema Constelação de
Fatos
„
Múltiplas tabela de fatos com dimensões
compartilhadas
„
Maior complexidade
42
Esquema Constelação de
Fatos
Time Dimension
time_key
day_of_week
month
quarter
year
holiday_flag
Shipping Fact
Sales Fact
time_key
product_key
location_key
dollar_sold
unit_sold
dollar_cost
Product Dimension
product_key
description
brand
category
Location Dimension
loc_key
loc_name
address
city
state
time_key
product_key
from_location_key
to_location_key
shipper_key
dollar_cost
units_shipped
Shipper Dimension
shipper_key
shipper_name
location_key
43
20
A Modelagem Multidimensional no
Projeto de DWs
„
Etapas:
„
Escolha do processo de negócio
„
„
„
„
Departamental ou corporativo
Pedidos, seguros, inventário, vendas, entregas
Os de maior impacto para o usuário primeiro
Escolha da granularidade do Processo de Negócio
„
„
„
Nível mais detalhado das medidas da tabela de fatos
Transações individuais ou posições periódicas
Cada item de uma nota fiscal de venda, um cartão de
embarque em um vôo, posição diária de um produto em
estoque, posição mensal de uma conta bancária
44
A Modelagem Multidimensional no
Projeto de DWs
„
Etapas:
„
Escolha das dimensões para cada tabela de fatos
„
„
„
Como descrever os dados do negócio?
Data (tempo), produto, cliente, tipo de transação
Escolha das medidas para a tabela de fatos
„
„
„
„
Que medidas devem ser acompanhadas?
Granularidade definida pelas dimensões relacionadas
Quantidade solicitada, Custo em reais
Percentuais não devem ser armazenados, e sim seus
numeradores e denominadores
45
21
Questões críticas para
modelagem dimensional
„
Foco nos requisitos e objetivos do negócio
„
„
„
Envolvimento do patrocinador e usuários gerenciais é essencial
para o sucesso
Adote uma abordagem incremental e iterativa para o
desenvolvimento do DW
„
„
„
„
„
Não tente fazer tudo de uma vez
Desempenho das consultas do usuário e facilidade de uso são
os fatores mais críticos
„
„
Não na tecnologia e nos dados
Otimização de consultas OLAP
Apresente os dados de forma simples, e com a semântica clara
Nível de detalhe deve chegar até os dados atômicos
Esteja preparado para mudanças no negócio e nos dados
Dê especial atenção à aceitação dos usuários
46
Conclusão
„
Modelagem dimensional é a abordagem
utilizada para o projeto de DWs
„
Visão dimensional dos dados
„
„
„
Esquemas
„
„
intuitiva, flexível e eficiente para consultas
Fatos, dimensões e hierarquias
Estrela, flocos de neve e constelação de fatos
Fases do projeto
„
Escolha do: Processo de negócio, granularidade,
dimensões e medidas de fatos (nesta ordem)
47
22
Exemplo de tabela Tempo
date
key
1
2
3
4
5
6
7
8
9
full date
1/1/96
1/2/96
1/3/96
1/4/96
1/5/96
1/6/96
1/7/96
1/8/96
1/9/96
day
day
day
abbre
day
day of num in num
v
week month overall name
1
1
1 Monday Mon
2
2
2 Tuesday Tue
3
3
3 WednesdaWed
4
4
4 Thursday Thu
5
5
5 Friday
Fri
6
6
6 Saturday Sat
7
7
7 Sunday Sun
1
8
8 Monday Mon
2
9
9 Tuesday Tue
week week
weekday num in num
year overall
flag
y
1
1
y
1
1
y
1
1
y
1
1
y
1
1
n
1
1
n
1
1
y
2
2
y
2
2
week
month
begin
week
num
month
date
begin
name
key month overall
date
1/1/96
1
1
1 January
1/1/96
1
1
1 January
1/1/96
1
1
1 January
1/1/96
1
1
1 January
1/1/96
1
1
1 January
1/1/96
1
1
1 January
1/1/96
1
1
1 January
1/8/96
8
1
1 January
1/8/96
8
1
1 January
month
abbrev
Jan
Jan
Jan
Jan
Jan
Jan
Jan
Jan
Jan
48
Exercício
„
Suponha uma Companhia Aérea GOAL que
deseja construir um DW para o seu negócio.
Seu objetivo principal é aumentar seu lucro
através do aumento da taxa de ocupação de
seus vôos, para isso lançando promoções e
descontos aos clientes. Também é desejável
otimizar a escala da tripulação, e o período
de manutenção das aeronaves. Projete um
esquema para o DW da companhia GOAL,
descrevendo o resultado de cada uma das
etapas da modelagem dimensional
apresentadas.
49
23