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