BANCO DE DADOS
DISTRIBUÍDOS e DATAWAREHOUSING
Asterio K. Tanaka
http://www.uniriotec.br/~tanaka/tin0036 tanaka@uniriotec.br
Introdução a Data Warehouse e
Modelagem Dimensional
• Visão Geral de BI
– ERM, EAI, CRM, PRM, ERP, ...
• OLTP x OLAP
• Definições e Arquitetura de DW
• Projeto de DW
• Modelagem dimensional
– Esquema Estrela
» Fatos
» Dimensões
» Hierarquias e Agregados
– Esquema Snowflake
Sistemas na Pirâmide Organizacional
EAI
Enteprise Application Integration
CRM PRM
B2C B2B
ERP Outros sistemas transacionais Business Intelligence Clientes Parceiros/Fornecedores Alta Gerência BSC CI KMS
(BI-Business Intelligence, C. Barbieri, Axcel Books, 2001)
Suporte à Decisão
Produção e distribuição de
INFORMAÇÃO ÚTIL
para
gerentes, executivos e analistas do conhecimento.
INFORMAÇÃO ÚTIL!
Mas de que forma?
Com:
•
Agilidade
•
Flexibilidade
Ambiente de Sistemas de Aplicação
Operacionais
• Dão suporte às funções
associadas à
execução
do negócio da
empresa:
– ERP, SCM
– B2C, B2B
– Sistemas transacionais
legados
– etc.
Informacionais
• Dão suporte às
funções associadas à
concepção
do
negócio da empresa
– DSS, EIS
– DW, CRM, PRM
– Ferramentas OLAP
– Ferramentas de Data
Mining
– etc.
Business Inteligence
• Inteligência Aplicada aos Negócios
– BI é um conjunto de tecnologias que permitem o cruzamento
de informações e suportam a análise dos indicadores de
desempenho de um negócio.
• Data Warehouse é um componente essencial
de BI (é o banco de dados da BI)
• Ferramentas OLAP e de Data Mining são
usadas como ferramentas de apoio à decisão,
para análise e extração de dados de Data
Características Dados operacionais
Dados informacionais
Conteúdo Valores correntes, detalhados Valores históricos, sumariados
Organização dos dados
Por aplicação, sistema de informação
Por assunto, negócio
Natureza dos dados Dinâmica, sujeita a atualizações freqüentes
Estática, atualização apenas com “refreshment”
Estrutura de dados Relacional, própria para processamento transacional
Dimensional, própria para processamento analítico
Uso Estruturado, repetitivo Desestruturado, consultas sob diferentes perspectivas
Desempenho Otimizado para tempo de resposta (máximo 2 a 3 segundos)
Análises complexas, com tempos de respostas maiores
Suporte à Decisão
“Tradicional”
Ambiente Operacional
Ambiente Operacional
Analista
Analista
do
do
negócio
negócio
• Dados não estão adequados para suporte à decisão
• Sistemas de suporte à decisão desenvolvidos ad-hoc
• Analista do Negócio vira “Programador”
Porque um ambiente de Data Warehouse?
9 Integrar dados de múltiplas
fontes
9 Facilitar o processo de análise
sem impacto para o ambiente de
dados operacionais
9 Obter informação de qualidade
9 Atender diferentes tipos de
usuários finais
9 Flexibilidade e agilidade para
atender novas análises
BDs Operacionais
DW
Aplicação
SD
Ferramenta
OLAP
Aplicação
EIS
Data Warehouse - Definições
• Processos, ferramentas e recursos para gerenciar e
disponibilizar informações de negócios precisas e
inteligíveis para que indivíduos possam tomar
decisões efetivas.
• Um ambiente para adequadamente organizar,
gerenciar e disponibilizar informações oriundas de
fontes diversas, fornecendo um visão única de parte
ou de todo o negócio com o objetivo de dar suporte a
operações analíticas.
Definição de Data Warehouse
“A Data Warehouse is a
subject-oriented,
integrated,
time-variant,
non-volatile
collection of data in support
of management’s decision-making process.”
(W. Imnon)
Um banco de dados destinado a sistemas de suporte à
decisão, cujos dados são armazenados em estruturas lógicas
dimensionais, possibilitando o seu processamento analítico
por ferramentas OLAP e de mineração de dados.
Arquitetura Genérica de um
Data Warehouse
BDs Operacionais Fontes Externas FONTES DE DADOS Meta Dados Data Warehouse Data Marts FERRAMENTAS DE CONSULTA Análise Data Mining Relatórios OLAP OLAP Extração Transformação Carga AtualizaçãoEstratégias de projeto de DW
• O Data Warehouse Corporativo
• Data Warehouses Departamentais
• Data Warehouses Funcionais
–
Marketing, Financeiro, Admnistrativo, etc.
• Data Warehouses para projetos especiais
• ...
O Mito
Para serem úteis, os Data Warehouses devem
possuir grande abrangência, com dados de
variadas fontes abrangendo os diversos
aspectos da Empresa.
• Pré-requisito : empresa totalmente
informatizada e integrada.
• Implicação : projetos extremamente complexos
e com alta probabilidade de insucesso.
A Realidade
• Projetos evolutivos
• Enfoque inicial nos aspectos mais críticos
• Aproveitamento da estrutura operacional
disponível
• Retorno mais rápido
• Acúmulo de experiência : menor risco e
menor custo
Abordagem corrente :
• Estratégia
– Desenvolver incrementalmente
– Visão Integrada
– Dividir para conquistar
– Errar pequeno
• Implementação
– Planejamento Top-Down
– Desenvolvimento Bottom-Up, um Data Mart de cada vez,
resultados devem ser atingidos em pequenos ciclos (ex.: a
cada 3 meses)
– Cada Data Mart deve ser encarado de forma evolutiva
• Desafio
Data Mart - Conceito
“Um subconjunto lógico do Data Warehouse, geralmente
visto como um data warehouse setorial.” (Kimball)
Uma perspectiva top-down considera que um DW completo,
centralizado deva ser desenvolvido antes que partes dele,
sumariadas, possam ser derivadas na forma de Data Marts.
Uma perspectiva bottom-up, considera que um DW possa ser
composto a partir de Data Marts previamente desenvolvidos.
Fatores Críticos de Sucesso em
Projetos de DW/DM
• Foco bem definido
• Patrocinador forte
• Existência dos dados necessários
• Envolvimento dos usuários
• Qualificação da equipe de projeto
• Arquitetura tecnológica bem definida
• Marketing interno e acompanhamento
• Gerência e manutenção de metadados
Modelagem para Data Warehouse
• Requisitos distintos das aplicações do
ambiente transacional:
–flexibilidade quanto às análises a suportar
–medidas a analisar precisam ser vistas sob
diferentes perspectivas
• Enfoque diferente da modelagem no ambiente
operacional
• Abordagem utilizada:
Visão multidimensional
• 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
A visão lógica é multidimensional, embora a
estrutura física tenha a mesma visão tabular
Estrutura Relacional
Volume de vendas (do revendedor GLEASON)
MODEL COLOR SALES VOLUME
MINI VAN BLUE 6
MINI VAN RED 5
MINI VAN WHITE 4
SPORTS COUPE BLUE 3
SPORTS COUPE RED 5
SPORTS COUPE WHITE 5
SEDAN BLUE 4
SEDAN RED 3
Visão matricial ou multidimensional
COLOR
M
O
D
E
L
Mini Van Sedan Coupe Red White Blue6
5
4
3
5
5
4
3
2
Volume de Vendas (do revendedor Gleason)
9
Um array multidimensional tem um número fixo de dimensões e
os valores são armazenados nas células
Acrescentando mais uma coluna...
MODEL COLOR DEALERSHIP VOLUME
MINI VAN BLUE CLYDE 6
MINI VAN BLUE GLEASON 6
MINI VAN BLUE CARR 2
MINI VAN RED CLYDE 3
MINI VAN RED GLEASON 5
MINI VAN RED CARR 5
MINI VAN WHITE CLYDE 2
MINI VAN WHITE GLEASON 4
MINI VAN WHITE CARR 3
SPORTS COUPE BLUE CLYDE 2
SPORTS COUPE BLUE GLEASON 3
SPORTS COUPE BLUE CARR 2
SPORTS COUPE RED CLYDE 7
SPORTS COUPE RED GLEASON 5
SPORTS COUPE RED CARR 2
SPORTS COUPE WHITE CLYDE 4
SPORTS COUPE WHITE GLEASON 5
SPORTS COUPE WHITE CARR 1
SEDAN BLUE CLYDE 6
SEDAN BLUE GLEASON 4
SEDAN BLUE CARR 2
SEDAN RED CLYDE 1
SEDAN RED GLEASON 3
SEDAN RED CARR 4
SEDAN WHITE CLYDE 2
SEDAN WHITE GLEASON 2
SEDAN WHITE CARR 3
Volume de Vendas
de todos os
revendedores
Visão multidimensional
Volume de Vendas
DEALERSHIP
Mini Van Coupe SedanBlue Red White
M
O
D
E
L
Clyde Gleason CarrCOLOR
•
O cubo é, de fato, apenas uma metáfora visual.
•
É uma representação intuitiva do fato porque todas as
dimensões coexistem para todo ponto no cubo e são
independentes umas das outras.
Adicionando Dimensões - Hipercubos
M O D E L Mini Van Coupe SedanBlue Red White Clyde Gleason Carr COLOR
Volume de Vendas
Coupe SedanBlue Red White Clyde Gleason Carr COLOR DEALERSHIP Mini Van Coupe Sedan
Blue Red White Clyde
Gleason Carr
COLOR
JANUARY FEBRUARY MARCH
Níveis nas dimensões ou Hierarquias
1996 abril maio 7 14 21 29 15 30 Brasil SUL NE NO SE RS PE SC AC AM 18 46 34 14 234 150 Dimensão: tempo Dimensão: área 34 23 45 62 56 23 92 73 23 13 87 21…..
…..
•
Hierarquias são a base das agregações
Produto
Alfa1
Agregados
ES
XPTO
XPTA
XPTN
M
ar
ço
Ab
ri
l
M
ai
o
RJ
SP
Vendas
Categoria
Trimestre
Região
...
...
Produto
Estado
Problemas
Tempo de
Resposta
BD1
BD2
BD3
BD4
Calcular os agregados no momento
da recuperação ou armazená-los?
Calcular os agregados no momento
da recuperação ou armazená-los?
A Síndrome da Explosão no
Volume de Dados
16
64
256
1024
4096
16384
65536
0
10000
20000
30000
40000
50000
60000
70000
2
3
4
5
6
7
8
Número de Dimensões
Número de Agregações
Agregados
• As hierarquias permitem que o usuário possa ter
acesso a dados com maior ou menor detalhe
• Os valores apresentados quando o analista
consulta dados em níveis hierárquicos mais altos
são valores agregados
Exemplo
Qual a margem de
contribuição de cada
área de vendas?
Hierarquias e Agregados
Geografia
Estado
Tempo
Produto
Consultas
Produto
Marca
Categoria
Mês
Trimestre
Ano
Região
País
Vendas por
Marca,
Trimestre
e Região
Vendas por
Produto,
Ano e
Região
Ferramentas OLAP
• OLAP: On Line Analytical Processing
– Conjunto de técnicas para tratar informações contidas em DW. – Visão Multidimensional dos Dados
• Termo proposto por E.F. Codd, em 1993
– Providing OLAP to User-Analysts: An IT Mandate.
• “Doze Regras de Codd” para ferramentas OLAP:
– Visão conceitual multidimensional – Transparência
– Acessibilidade
– Desempenho de Informações consistentes – Arquitetura Cliente Servidor
– Dimensionalidade genérica
– Manipulação de dados dinâmicos – Suporte a multiusuários
– Operações ilimitadas em dimensões cruzadas – Manipulação intuitiva de dados
– Flexibilidade nas consultas
“Doze Regras de Codd” para ferramentas OLAP
• Visão conceitual multidimensional
– Os dados são modelados em diversas dimensões podendo
haver cruzamento de todos os tipos de informações
• Transparência
– OLAP deve atender a todas as solicitações do analista, não
importando de onde os dados virão. Todas as implicações
devem ser transparentes para os usuários finais.
• Acessibilidade
– As ferramentas OLAP devem permitir conexão com todas as
bases de dados legadas. A distribuição de informações deve
ser mapeada para permitir o acesso a qualquer base.
• Desempenho de Informações consistentes
– As ferramentas OLAP devem possuir conhecimento sobre
todas as informações armazenadas que possa disponibilizar,
sem complexidade para o usuário final, qualquer tipo de
“Doze Regras de Codd” para ferramentas OLAP
• Arquitetura Cliente Servidor
– OLAP deve ser construída em arquitetura C/S para que
possa atender a qualquer usuário em qualquer ambiente
operacional
• Dimensionalidade genérica
– Deve ser capaz de tratar informações em qualquer
quantidade de dimensões
• Manipulação de dados dinâmicos
– Devido ao grande volume de informações armazenadas nas
diversas dimensões de um modelo multidimensional, é
comum a esparsidade dos dados, e então essas células
nulas devem ser tratadas para evitar custos com memória.
• Suporte a multiusuários
– Nas grandes organizações, é comum vários analistas
trabalharem com a mesma massa de dados.
“Doze Regras de Codd” para ferramentas OLAP
• Operações ilimitadas em dimensões cruzadas
– As ferramentas OLAP devem ser capazes de navegar nas
diversas dimensões existentes.
• Manipulação intuitiva de dados
– O usuários devem ser capazes de manipular os dados
livremente, sem necessitar de qualquer tipo de ajuda.
• Flexibilidade nas consultas
– O usuário deve ter a flexibilidade para efetuar qualquer tipo
de consulta.
• Níveis de dimensão e agregação ilimitados
– Devido às várias dimensões existentes, deve haver vários
níveis de agregação dos dados.
Operações OLAP
• Ferramentas OLAP fornecem suporte para funções
de análise de dados, típicas de aplicações avançadas
de planilhas eletrônicas.
• Operações dimensionais de ferramentas OLAP:
– Slice and Dice (Ponto, Plano, Cubo)
– Rotation (Rotação ou Pivotamento)
– Drilling
» Drill Down
» Drill Across e Drill Through
» Drill Up (Roll Up)
Operadores Dimensionais
• Ponto - Valor pontual
– Interseção de valores (Fato) com relação aos eixos (Dimensões)
• Plano – Slicing
– Duas dimensões variando com outras fixas.
• Cubo – Dicing
– Todas as dimensões variando
• Rotação – Pivotamento
Operadores Drilling
Drill-up ou Roll-up
Drill Across
Drill Through
Tipos de ferramentas OLAP
• OLAP Multidimensional (MOLAP)
– Utilizam estrutura de dados multidimensional e permitem a navegação pelos níveis de detalhamento em tempo real.
– O BD e o SGBD são multidimensionais
– Estrutura de dados é um array com um número fixo de dimensões. O
(hiper)cubo é uma metáfora visual, onde as dimensões coexistem para todo ponto e são independentes entre si.
• OLAP RELACIONAL (ROLAP)
– Decorrência do uso consagrado de SGBDs relacionais nos BDs operacionais (transacionais), com as vantagens da tecnologia aberta e padronizada
(SQL).
– Utiliza os metadados no apoio à descrição do modelo de dados e na construção de consultas. Através de uma camada semântica acima do esquema relacional, os dados são apresentados ao usuário com visão multidimensional.
• OLAP HÍBRIDO (HOLAP)
– Tendência dos modernos SGBDs relacionais de adicionar uma arquitetura multidimensional para prover facilidades a ambientes de suporte a decisão. – Proporciona o desempenho e flexibilidade de um BD multidimensional e