Aula22_DawareHouseOLAP
Download
Report
Transcript Aula22_DawareHouseOLAP
SCC0141 - Bancos de Dados
e Suas Aplicações
Data Warehouse e OLAP
Prof. Jose Fernando Rodrigues Junior
1
Introdução
No início, uma única fonte de dados
No início, uma única fonte de dados
Exemplo: primeira loja do grupo Casas Bahia.
Mais tarde
Exemplo: primeira cidade ocupada
pelo grupo Casas Bahia.
E assim sucessivamente
Exemplo: primeiros estados ocupados
pelo grupo Casas Bahia.
Finalmente
Exemplo: Brasil (primeiro país?)
ocupado pelo grupo Casas Bahia.
Até que…
O que está acontecendo nas minhas
lojas?
-
-
O que é vendido mais?
Quando?
Por qual loja?
Qual a melhor loja?
Quanto estou faturando?
Qual seria uma boa oferta?
As vendas cresceram ou subiram?
Quais regiões vendem mais?
...
Até que…
O que está acontecendo nas minhas
lojas?
Soluções:
- O que é vendido mais?
1) Ir- até
cada uma das lojas e perguntar;
Quando?
-
-
Por qual loja?
Pouco viável.
Qual a melhor loja?
Quanto estou faturando?
Qual seria uma boa oferta?
As vendas cresceram ou subiram?
Quais regiões vendem mais?
...
Até que…
O que está acontecendo nas minhas
lojas?
Soluções:
- O que é vendido mais?
2) Usar
os dados das lojas para analisar o que está
- Quando?
acontecendo no meu negócio.
- Por qual loja?
- Qual a melhor loja?
Boa idéia!!!
- Quanto estou faturando?
- Qual seria uma boa oferta?
- As vendas cresceram ou subiram?
- Quais regiões vendem mais?
- ...
Até que…
O que está acontecendo nas minhas
lojas?
Soluções:
- O que é vendido mais?
2) Usar
os dados das lojas para analisar o que está
- Quando?
acontecendo no meu negócio.
- Por qual loja?
- Qual a melhor loja?
Boa idéia!!!
- Quanto estou faturando?
- Qual seria uma boa oferta?
Mas...
- As vendas cresceram
ou subiram?
- Quais regiões vendem mais?
- ...
Impecilhos ao uso dos dados…
-
Diversos sistemas de bancos de dados em uso
nas lojas; depende do gerente que o
implementou:
PostgreSQL,
Oracle,
DB2,
SQLServer, ...
-
Cada loja, um controle transacional diferente,
de acordo com suas necessidades regionais;
-
Esquemas diferentes, tipos de dados
diferentes, distribuição geográfica, falta de
interconexão.
Impecilhos ao uso dos dados…
-
Diversos sistemas de bancos de dados em uso
nas lojas; depende do gerente que o
implementou:
PostgreSQL,
Oracle,
DB2,
SQLServer, ...
Total falta de conformidade!!!
-
-
Cada loja, um controle transacional diferente,
E muitos,
muitos dados!!! regionais;
de acordo com suas
necessidades
O que fazer?
Esquemas diferentes, tipos de dados
diferentes, distribuição geográfica, falta de
interconexão.
O que é necessário?
-
1) Recolher (extrair) os dados
não
importando qual o tipo do sistema de dados;
-
2) Padronizar (transformar) os dados, para
terem um significado comum mesmo que,
originalmente,
codificados
de
maneira
diferente; resolução de dados ausentes e
espúrios;
-
3) Unir (carregar) os resultados das duas
operações em um único sistema capaz para
responder às minhas perguntas.
O que é necessário?
-
1) Recolher (extrair) os dados
não
importando qual o tipo do sistema de dados;
Complicado.
-
2) Padronizar (transformar) os dados, para
Como fazer comum
então?
terem um significado
mesmo que,
originalmente,
codificados
de
maneira
diferente; resolução de dados ausentes e
espúrios;
-
3) Unir (carregar) os resultados das duas
operações em um único sistema capaz para
responder às minhas perguntas.
Mas há uma solução
Mas há uma solução
Loja 1
Oracle
Modelagem
x
Loja 2
DB2
Modelagem
y
Loja 3
SQLServer
Modelagem
z
...
Loja n-2
Loja n-1
Loja n
Oracle
Modelagem
u
DB2
Modelagem
v
SQLServer
Modelagem
w
Extrair, transformar, carregar dados
Data warehouse
O
ETL – Extract Transform Load
processo descrito de se extrair
(Extract), transformar (Transform) e
carregar (Load) os dados a partir das
diversas fontes de dados é denominado
ETL
ETL
– uma das camadas principais da
arquitetura de um data warehouse
Consolidação de dados
O
ETL – Extract Transform Load
processo descrito de se extrair
(Extract), transformar (Transform) e
carregar (Load) os dados a partir das
diversas fontes de dados é denominado
ETL
ETL
– uma das camadas principais da
arquitetura de um data
warehouse
Extrair,
transformar, carregar dados
Consolidação de dados
ETL – Extract Transform Load
Extração/transformação (Extract/Transform)
de dados
extração de múltiplas fontes
consolidação e integração de dados de múltiplas
fontes
limpeza e validação
conversão dos dados para o modelo do DW
ETL – Extract Transform Load
Carregamento (Load) de dados
armazenamento de acordo com o modelo do DW
criação e manutenção de estruturas de dados
criação e manutenção de caminhos de acesso
tratamento de dados que variam no tempo
suporte a atualização
refresh
purging (eliminação)
Conceitos
O que é exatamente?
“Data Warehouse é uma coleção de dados
orientados por assunto, integrada, nãovolátil, variante no tempo, que dá apoio às
decisões de administração” (W.H. Inmon,
1992).
Orientados
a
transações:
vendas,
operações bancárias, acessos à informação.
Introdução
Aplicações
empresas de telefonia
redes de varejo
instituições financeiras
instituições governamentais
instituições de ensino e pesquisa
….
24
Sistemas OLTP
Extrair, transformar, carregar dados
Sistemas OLTP
Data warehouses são,
comumente, alimentados
por
sistemas
OLTP
independentes.
Extrair, transformar, carregar dados
Sistemas OLTP
Data warehouses são,
comumente, alimentados
por
sistemas
OLTP
independentes.
Extrair, transformar, carregar dados
Sistemas OLTP (Online Transaction Processing):
gerenciamento de transações;
toda vez que você vai ao mercado, ao banco ou
faz uma compra online, você está usando um
sistema OLTP
Objetivos
Consolidação dos dados de uma empresa
Desempenho na consulta aos dados
Separação entre suporte à decisão e bancos
de dados operacionais
Suporte à ferramentas: mineração de
dados, visualização e On-line Analytical
Processing (OLAP)
Objetivos
Os termos Datawarehouse, OLTP e OLAP
não se refererem a software apenas
São termos que englobam software e
serviços (muitos serviços – consultoria)
São
termos
cunhados
para
a
comunidade empresarial não possuindo
uma correspondência simples em Ciência da
Computação
Relação OLTP e OLAP
Arquitetura de um data warehouse
Arquitetura de um data warehouse
Arquitetura
organização
De
1.
2.
3.
4.
definida
pelo
contexto
da
maneira geral, tem as seguintes camadas:
Operacional (OLTPs): fornecem dados
De acesso aos dados: ETL
Acesso à informação: ferramentas de
acesso a dados, geração de relatórios, e
análise (OLAP) Business Intelligence
Metadados: detalhamento do conteúdo
do data warehouse dicionário de
dados
Arquitetura de um data warehouse
Arquitetura
organização
De
1.
2.
3.
4.
definida
pelo
contexto
da
maneira geral, tem as seguintes camadas:
Operacional (OLTPs): fornecem dados
De acesso aos dados: ETL
Acesso à informação: ferramentas de
acesso a dados, geração de relatórios, e
análise (OLAP) Business Intelligence
Metadados: detalhamento do conteúdo
do data warehouse dicionário de
dados
Dicionário de dados
Descrição
dos dados do DW:
origem
regras
de transformação
nomes e aliases
formato dos dados
histórico de atualizações
acesso e segurança
responsabilidades sobre os dados
Sem
o dicionário, não há sistema
Data warehouse vs Banco de dados
operacional
Data warehouse vs Banco de dados
operacional
Data warehouse
Banco de dados
operacional
Orientado a objetivos específicos
Orientado a transações
Grande (centenas de GBs até TBs)
Dados históricos
Pequeno/Médio (MBs até alguns GBs)
– distribuído se necessário
Dados correntes
De-normalizado (poucas tabelas com
muitas colunas)
Atualizações em Batch
Normalizado (muitas tabelas com
poucas colunas)
Atualizações contínuas
Otimizado para acesso
Otimizado para escrita/atualização
Juntos data warehouse e bancos de
dados provém uma solução completa
Bancos de dados
Inserção/Atualização
Data Warehouse
Acesso aos dados
Visão Geral
Extrair, transformar, carregar dados
1. Camada Operacional (OLTPs)
2. Camada de acesso aos dados (ETL)
3. Camada de acesso à informação: MD, relatórios, OLAP
Business Intelligence
4. Dicionário de dados
Projeto de Data warehouses
Datamarts
Datamart: ambientes de análise de dados
de menor magnitude, com fins mais
específicos, limitados a subcomunidades de
uma organização
Exemplo, uma loja das casas Bahia
Loja i
Modelagem
x
Datamarts
Datamart: ambientes de análise de dados
de menor magnitude, com fins mais
específicos, limitados a subcomunidades de
uma organização
Exemplo, uma loja das casas Bahia
Modelagem
x
Acréscimo de
ferramentas de
análise
Datamarts
Datamart: ambientes de análise de dados
de menor magnitude, com fins mais
específicos, limitados a subcomunidades de
uma organização
Exemplo, uma loja das casas Bahia
Loja i
Modelagem
x
Datamart i
Bottom-up
Projeto Bottom-up: exemplo, rede das casas
Bahia
....
Datamart 1
Datamart 2
Datamart 3
Datamart n
Bottom-up
Projeto Bottom-up: exemplo, rede das casas
Bahia
....
Datamart 1
Datamart 2
Datamart 3
Datamart n
Bottom-up
Projeto Bottom-up: exemplo, rede das casas
Bahia
....
Datamart 1
Datamart 2
Datamart 3
Datamart n
Data warehouse : conjunto de pequenos sistemas de tratamento e
análise de dados.
Bottom-up
Projeto Bottom-up: exemplo, rede das casas
Bahia
....
Datamart 1
Datamart 2
Datamart 3
Datamart n
Data warehouse : conjunto de pequenos sistemas de tratamento e
análise de dados.
Grande esforço de consolidação modelo de dados não universal.
Top-down
Projeto Top-down
Data warehouse : projeto bem definido.
Top-down
Projeto Top-down
Datamart 1
Data warehouse : projeto bem definido.
Top-down
Projeto Top-down
Datamart 1
Datamart 2
Data warehouse : projeto bem definido.
Top-down
Projeto Top-down
Datamart 1
Datamart 2
Datamart 3
Data warehouse : projeto bem definido.
Top-down
Projeto Top-down
....
Datamart 1
Datamart 2
Datamart 3
Data warehouse : projeto bem definido.
Datamart n
Top-down
Projeto Top-down
....
Datamart 1
Datamart 2
Datamart 3
Datamart n
Data warehouse : projeto bem definido.
Esforço reduzido de consolidação modelo de dados universal e bem definido.
Projeto Top-down vs Bottom-up
Bottom-up
Top-down
Consistência via
manutenção constante
Alta consistência
Expansão custosa
Expansão com novos DMs
facilitada
Custo diluído ao longo do
tempo
Inicialmente custosa
Natural em organizações
Rara em start-ups
Baixa latência
Alta latência
Conceitos vistos
Data warehouse
OLTP
ETL
Dicionário de dados
DW vs BD
Datamart
Projeto Bottom-up vs Top-down
OLAP – Online Analytical Processing
OLAP
Definição:
uma categoria de tecnologia
de software que visa à compreensão de
dados
Provê:
acesso
interativo
DW
rápido, consistente e
e OLAP, em muitos casos, conceitos
indissociáveis
OLAP - FASMI
FASMI:
Fast
Analysis
Multidimensional Information
Fast:
of
agilidade
em
responder
consultas
Analysis: versatilidade analítica
Shared: dados/analistas múltiplos
Multidimensional:
orientado
a
dimensões de dados
Information: propósito fim
Cubo de dados OLAP
Cubo de dados
240
101 110
150 204 190
90
83
90
35
87
19
27
35
45
Cubo de dados
Estrutura básica da prática de OLAP
Observam-se
As dimensões dos dados
As medidas sobre os dados
O cubo é orientado a planos (faces)
Apesar da complexidade dos sistemas OLAP, seus
objetivos analíticos são básicos: contagem, média,
máximo, mínimo, soma, ...
Agregação, rápida e flexível, sobre
imensos volumes de dados
Cubo de dados - slicing
Pode
ser interessante ver o cubo a partir
de diferentes perspectivas (planos)
Operações
sobre o cubo: slicing, dicing
e rotating (pivoting)
Cubo de dados - slicing
• A operação de slicing
equivale a fatiar o cubo,
definindo um novo plano
de apreciação dos dados
• A operação geométrica é
apenas uma analogia, o
slicing
dispara
o
processamento
OLAP
para calcular o novo plano
Cubo de dados - slicing
• A operação de slicing
equivale a fatiar o cubo,
definindo um novo plano
de apreciação dos dados
• A operação geométrica é
apenas uma analogia, o
slicing
dispara
o
processamento
OLAP
para calcular o novo plano
Dados calculados e acessíveis ao analista.
Cubo de dados - slicing
• A operação de slicing
equivale a fatiar o cubo,
definindo um novo plano
de apreciação dos dados
• A operação geométrica é
apenas uma analogia, o
slicing
dispara
o
processamento
OLAP
para calcular o novo plano
Exemplo de dado calculado: quantas TVs de plasma foram vendidas em
2002 na região SE?
Cubo de dados - slicing
• A operação de slicing
equivale a fatiar o cubo,
definindo um novo plano
de apreciação dos dados
• A operação geométrica é
apenas uma analogia, o
slicing
dispara
o
processamento
OLAP
para calcular o novo plano
Exemplo de dado calculado: quantas TVs de plasma foram vendidas em
2002 na região SE?
Cubo de dados - slicing
• A operação de dicing é
semelhante ao slicing,
mas usa dois, ou mais,
planos de corte
Dados calculados e acessíveis ao analista.
Dicing conseguido com 5 planos de corte.
Cubo de dados - slicing
• A operação de dicing é
semelhante ao slicing,
mas usa dois, ou mais,
planos de corte
Dados calculados e acessíveis ao analista.
Dicing conseguido com 5 planos de corte.
Exemplo de dado calculado: quantas Celulares foram vendidas em 2001
na região CO?
Cubo de dados - slicing
• A operação de dicing é
semelhante ao slicing,
mas usa dois, ou mais,
planos de corte
Dados calculados e acessíveis ao analista.
Dicing conseguido com 5 planos de corte.
Exemplo de dado calculado: quantas Celulares foram vendidas em 2001
na região CO?
Cubo de dados - rotating
• A operação de rotating
muda a perspectica do
cubo todo.
Novo plano: Produto x Tempo
Dados calculados e acessíveis ao analista.
Cubo de dados - rotating
• A operação de rotating
a perspectica do
cubomuda
apenas
cubo todo.
Obviamente refere-se a
para fins didáticos – aplicações de
DW/OLAP geralmente envolvem
mais do que 3 dimensões, definindo
hipercubos.
Novo plano: Produto x Tempo
Dados calculados e acessíveis ao analista.
Modelo de dados dimensional
OLAP e DW
OK,
mas e o DW, onde entra nisso tudo?
O modelo de dados do DW é orientado a servir a
análise baseada em Dimensões de dados
Modelo de dados DW
Data Warehouse
Banco de dados operacional
FATOS
Esquema
estrela
Esquema complexo
Modelo de dados DW
Data Warehouse
Banco de dados operacional
FATOS
Esquema
estrela
Esquema complexo
Modelo de dados DW
Data Warehouse
Banco de dados operacional
FATOS
Esquema floco
de neve
(snow flake)
Esquema complexo
(normalizado)
Modelo de dados dimensional
Exemplo
Estrela:
Produto
p_chave
Tipo
Fabricante
Modelo
Fatos
t_chave
Tempo
t_chave
Ano
Trimestre
Mes
p_chave
Local
l_chave
l_chave
Nro_unidades
Regiao
Valor
Cidade
Assist_tecnica
Loja
Modelo de dados dimensional
Produto
Exemplo:
Exemplo:
p_chave considerando Ano, Tipo de Produto e
Quantas vendas e qual valor de vendas ocorreram
Tipo
Região?
Fabricante
SELECT Tempo.Ano, Produto.Tipo, Local.Regiao,
Sum(Nro_unidades), Sum(valor)
Modelo
FROM Fatos, Tempo, Produto, Local
WHERE Fatos.t_chave = Tempo.t_chave
AND
Fatos
Fatos.p_chave = Produto.p_chave AND
t_chave
Fatos.l_chave = Local.l_chave
Local
p_chave
GROUP
BY
Tempo.Ano,
Produto.Tipo,
Local.Regiao
Tempo
t_chave
Ano
Trimestre
Mes
l_chave
l_chave
Nro_unidades
Regiao
Valor
Cidade
Assist_tecnica
Loja
Modelagem de Dados para DW
Exemplo Snowflake
(Elmasri e Navathe, 2005)
79
Modelo de dados dimensional
Cubo
de dados: análise dimensional das
medidas (dados)
DW:
modelo de dados dimensional
Dimensões: dão contexto aos fatos
Fatos: números transacionais
Modelo de dados dimensional
Observe
que as dimensões dos dados
possuem uma hierarquia categórica
Por
exemplo:
Tempo(Ano, Trimestre, Mês)
Modelo de dados dimensional
Hierarquia
das dimensões: apreciação dos dados em
diferentes granularidades. Exemplo:
Itens_vendidos(ano) > Itens_vendidos(Trimestre)
Itens_vendidos(Trimestre) > Itens_vendidos(Mês)
Duas
outras operações muito importantes
Drill down
Roll up
Drill down / Roll up
Drill-down
e roll-up: navegação ao longo
dos níveis hierárquicos das dimensões
Exemplo
Drill-down
Itens_vendidos(Região)
Itens_vendidos(Cidade)
Roll-up
Drill down / Roll up
Drill-down sobre as três
dimensões
simultaneamente.
Drill down / Roll up
Exemplo:
Quantas vendas e qual valor de vendas ocorreram considerando Ano, Tipo de Produto e
Região?
Drill-down sobre as três
Drill Down em todas as dimensões
dimensões
Quantas vendas e qual valor de vendas ocorreram considerando
Trimestre,
simultaneamente.
Fabricante e Cidade?
SELECT Tempo.Ano, Tempo.Trimestre, Produto.Tipo, Produto.Fabricante, Local.Regiao
Local.Cidade, Sum(Nro_unidades), Sum(valor)
FROM Fatos, Tempo, Produto, Local
WHERE Fatos.t_chave = Tempo.t_chave AND
Fatos.p_chave = Produto.p_chave AND
Fatos.l_chave = Local.l_chave
GROUP BY Tempo.Ano, Tempo.Trimestre, Produto.Tipo, Produto.Fabricante, Loca.Regiao,
Local.Cidade
Drill down / Roll up
Exemplo:
Quantas vendas e qual valor de vendas ocorreram considerando Ano, Tipo de Produto e
Região?
Drill-down sobre as três
Drill Down em todas as dimensões
dimensões
Quantas vendas e qual valor de vendas ocorreram considerando
Trimestre,
simultaneamente.
Fabricante e Cidade e considerando um slicing de ano entre 2001 e 2002?
SELECT Tempo.Trimestre, Produto.Fabricante, Local.Cidade, Sum(Nro_unidades), Sum(valor)
FROM Fatos, Tempo, Produto, Local
WHERE Fatos.t_chave = Tempo.t_chave AND
Fatos.p_chave = Produto.p_chave AND
Fatos.l_chave = Local.l_chave AND
Tempo.Ano between 2001 AND 2002
GROUP BY Tempo.Trimestre, Produto.Fabricante, Local.Cidade
OLAP - Agregação
OLAP - Agregação
Mais do que cubinhos, o
OLAP/DW é uma tecnologia
sofisticada que visa responder
às diferentes possibilidades e
níveis de agregação de maneira
rápida e precisa.
Síntese
http://etl-tools.info
Relational OLAP (ROLAP)
Recursos OLAP sobre SGBDs disparam todas as agregações
necessárias para se obter os resultados requisitados
O OLAP não relacional, também é conhecido como
Multidimensional OLAP - MOLAP
SQL:1999
ROLLUP e CUBE
SELECT ….
FROM ….
WHERE …
GROUP BY CUBE (trim, região)
SELECT ….
FROM ….
WHERE …
GROUP BY ROLLUP (trim, região)
ex: selecionar
total de vendas por trim. por região
total de vendas por trim.
total de vendas por região
total geral de vendas
ex: selecionar
total de vendas por trim. por região
total de vendas por trim.
total geral de vendas
90
Relational OLAP (ROLAP)
Sobre ROLLUP e CUBE
ROLLUP e CUBE aplicados sobre k atributos
... GROUP BY CUBE (....)
é equivamente a agrupamentos sobre cada um
dos 2k subconjuntos de atributos
ex: k=2 ⇒ ... GROUP BY CUBE (a,b)
agrupamentos sobre: (a,b), (a), (b), (null)
agrupamento em (null) é o total geral de vendas
91
Relational OLAP (ROLAP)
(cont...)
(cont...)
... GROUP BY CUBE (....)
é equivamente a agrupamentos sobre cada um
dos 2k subconjuntos de atributos
ex: k=3 ⇒ ... GROUP BY CUBE (a,b,c)
agrupamentos sobre: (a,b,c), (a,b), (a,c), (b,c) (a),
(b),(c), (null)
92
Relational OLAP (ROLAP)
Dept
Funcao
COUNT(*)
SUM(Salario)
------------------------------------------------
Exemplo GROUP BY
CUBE
SELECT Dept, Funcao,
10
Secretario
1
100
10
Gerente
1
500
10
Presidente
1
900
3
1500
10
20
Analista
2
350
FROM Empregados
20
Secretario
2
240
GROUP BY CUBE(Dept, Funcao);
20
Gerente
1
800
5
1390
Secretario
3
340
Gerente
2
1300
Presidente
1
500
Analista
2
350
8
2490
COUNT(*), SUM(Salario)
20
Relational OLAP (ROLAP)
(cont...)
(cont...)
... GROUP BY ROLLUP (....)
é equivamente a agrupamentos em k+1 subconjuntos:
k=2 ⇒ ... GROUP BY ROLLUP (a,b)
agrupamentos sobre: (a,b), (a), (null)
agrupamento em (null) é o total geral de vendas
k=3 ⇒ ... GROUP BY ROLLUP (a,b,c)
agrupamentos sobre: (a,b,c), (a,b), (a), (null)
94
Ferramentas
Ferramentas de suporte à criação e manutenção
de DW
Ferramentas OLAP
Ferramentas de BI
…
95
Referências
ELMASRI, R; NAVATHE, S.B. Sistemas de Banco
de Dados, Addison Wesley, 4a edição, 2005.
Ramakrishnan
R.;
Gehrke,
J.
Database
Management Systems, Mc Graw Hill, 2000.
96