View
1
Download
0
Category
Preview:
Citation preview
1
Bancos de Dados IV
OLAP e Cubos de Dados
Rogério Costa
rogcosta@inf.puc-rio.br
OLAP
Online Analytical Processing (OLAP)
Análise interativa de dados, permitindo que
dados sejam sumarizados e vistos de diferentes
formas de maneira on-line
Dados e Dimensões
Dados que podem ser modelados como
atributos de dimensões e de medida são
chamados de dados multidimensionais.
Dados e Dimensões
Atributos de medida
Medem algum valor
Podem sofrer agregações
Exemplo: quantidade em uma tabela de vendas
Atributos de dimensão
Definem as dimensões segundo as quais medir os
atributos (ou realizar agregações)
Exemplo: nome_produto, cor e tamanho em uma relação
de vendas
“Cross Tab”
Exemplo de cross-tabulation (cross-tab) –
também conhecida como pivot-table
“Cross Tab”
No exemplo:
Valores para uma dimensão de atributos formam
o cabeçalho das colunas
Valores de outra dimensão formam o cabeçalho
das linhas
Outra dimensão está listada acima
Valores das células são agregados
correspondentes aos atributos que definem as
células
Representação Relacional
Pivot-tables também podem
ser exibidas em formato
relacional
No exemplo ‘todos’ significa
agregado (SQL:2003 utiliza null
apesar da possível confusão com
valores realmente nulos)
Cubo de Dados
Um cubo de dados
multidimensional é uma
generalização de uma
tabela pivot
Podem existir n
dimensões
Online Analytical Processing
Pivoteamento (ou rotação) -> realização da
rotação em torno de um ou mais eixos do
cubo de dados de forma que as categorias
dos eixos do plano sejam alteradas, segundo
orientação do usuário
Exemplo: no plano tínhamos Produto x Região,
agora queremos ter Período x Região
Online Analytical Processing
Slicing: criar uma cross-tab para valores
fixos (as vezes chamada dicing quando
valores de várias dimensões estão fixos)
Hierarquias em dimensões
Podemos visualizar
os atributos de
dimensões em
diferentes níveis de
detalhes, criando
hierarquias.
Trimestre
Mês
Data
Data-hora
Ano
Dia da Semana
Hora do dia
Região
Estado
Cidade
Bairro
País
‘Cross-Tab’ x Hierarquia
Online Analytical Processing
Roll-up (agregação) -> agrupamento por
unidades maiores em torno de uma
dimensão.
Exemplo: cubo de Produto x Estado x Trimestre,
com roll-up na segunda dimensão se
transformando em cubo de Produto x Região x
Trimestre
Online Analytical Processing
Drill-down (desmembramento) -> quebra de
informações em dados de menor
granularidade.
Exemplo: cubo de Produto x Região x Trimestre,
com drill-down na terceira dimensão se
transformando em cubo de Produto x Região x
Mês
OLAP - Implementações
MOLAP - multidimensional OLAP
Primeiros sistemas OLAP utilizavam arrays
multidimensionais em memória para armazenar
os cubos de dados
Arrays podiam ser armazenados em disco
Ex.: Arbor Essbase OLAP Server, SAS system
OLAP- Implementações
Primeiros sistemas OLAP pré-calculavam todas as possíveis agregações para poder responder as requisições on-line
Espaço e tempo podem ser muito altos (2^n combinações)
É suficiente pré-computar alguns agregados e calcular outros a partir dos primeiros (para a maioria dos operadores). Ex.: (região, estado) pode ser calculado a partir de (região, estado, município)
OLAP - Implementações
ROLAP - Relational OLAP
Implementação que utiliza bancos de dados
relacionais
Ex.: Red Brick, Sybase
HOLAP - Hybrid OLAP
Sistemas híbridos que utilizam bases relacionais
mas armazenam sumarizações em memória/disco
Ex. Pilot Decision Suporte System
OLAP - Implementações Representando dados multidimensionais com tabelas relacionais
(esquema estrela)
Extensões SQL para OLAP
Novas cláusulas introduzidas a partir da
SQL:1999
Permitem a ‘geração de pivot-tables’ a partir
de comandos SQL
Extensões SQL para OLAP
Dados
Exemplo
Extensões SQL para OLAP
Qual a quantidade total vendida em cada
combinação de <produto, mês, estado>?
Resposta com GROUP BY tradicional
SELECT ESTADO, MONTH(DATA) AS MES,
PRODUTO, SUM(QUANTIDADE) AS TOTAL
FROM VENDA
GROUP BY ESTADO, MONTH(DATA), PRODUTO
ORDER BY ESTADO, MONTH(DATA), PRODUTO
Extensões SQL para OLAP
ESTADO MES PRODUTO TOTAL
------ --- ------------------ -----
MG 1 COMPUTADOR PESSOAL 8
MG 2 COMPUTADOR PESSOAL 12
MG 3 SCANNER 20
RJ 1 COMPUTADOR PESSOAL 25
RJ 2 COMPUTADOR PESSOAL 10
SP 1 IMPRESSORA 24
SP 1 SCANNER 25
SP 2 SCANNER 25
Extensões SQL para OLAP
Quais os totais de vendas para:
diversas combinações de <produto, mês, estado>
para cada par <mês, estado>
para cada estado isoladamente?
De um grupo no nível de detalhe, temos que
fazer agregações… (roll-up)
Extensões SQL para OLAP
Operador ROLLUP – gera união em cada
subconjunto da lista de atributos.
Pode ser utilizado para construir diferentes níveis
da hierarquia
SELECT ESTADO, MONTH(DATA) AS MES,
PRODUTO, SUM(QUANTIDADE) AS TOTAL
FROM VENDA
GROUP BY ROLLUP (ESTADO, MONTH(DATA),
PRODUTO)
ORDER BY ESTADO, MONTH(DATA), PRODUTO
Extensões SQL para OLAP
Extensões SQL para OLAP
Operador GROUPING SETS –agrupamento
realizado isoladamente por cada uma das
expressões contidas no GROUP BY
Extensões SQL para OLAP
Utilização de GROUPING SETS em uma cláusula GROUP BY com n expressões é idêntica à união de n comandos com a cláusula GROUP BY utilizada isoladamente, cada um dos n comandos com somente uma das n colunas originais no GROUP BY
SELECT ESTADO, MONTH(DATA) AS MES, PRODUTO, SUM(QUANTIDADE) AS TOTAL FROM VENDA GROUP BY grouping sets(ESTADO, MONTH(DATA), PRODUTO) ORDER BY ESTADO, MONTH(DATA), PRODUTO
Extensões SQL para OLAP
Extensões SQL para OLAP
Operador CUBE – agrupamentos por cada
combinação possível das expressões contidas
na cláusula GROUP BY.
Apresenta, também, uma linha com informação
condensada de toda a tabela
Extensões SQL para OLAP
SELECT ESTADO, MONTH(DATA) AS MES,
PRODUTO, SUM(QUANTIDADE) AS TOTAL
FROM VENDA
GROUP BY cube(ESTADO, MONTH(DATA),
PRODUTO)
ORDER BY ESTADO, MONTH(DATA),
PRODUTO
Extensões SQL para OLAP
Extensões SQL para OLAP
Rank – descobrir a posição de um item em
um grupo
Duas formas: analítica e agregada
Extensões SQL para OLAP
Rank – forma agregada – busca a posição da
linha que possui o valor passado como
parâmetro em um conjunto ordenado
segundo um critério especificado
Extensões SQL para OLAP
Qual a posição da venda de R$5000,00 no
conjunto das vendas realizadas?
SELECT RANK(5000) WITHIN GROUP
(ORDER BY valor DESC) “Posicao"
FROM totais_vendas;
Resultado
Posicao
----------
4
Extensões SQL para OLAP
Rank – forma analítica – apresenta a posição
de cada linha com relação a ordem
especificada
O rank não precisa ser um número seqüencial
=> dois itens com o mesmo rank…
Extensões SQL para OLAP
Rank – forma analítica
SELECT departamento, ultimo_nome,
salario, comissao,
RANK() OVER (ORDER BY salario DESC,
comissao) "Rank"
FROM funcionarios
WHERE departamento = 80;
Extensões SQL para OLAP
Extensões SQL para OLAP
Merge
MERGE INTO TABELA_DESTINO [AS APELIDO_DESTINO] USING TABELA_ORIGEM [AS APELIDO_ORIGEM] ON ( EXPRESSÃO_BOOLEANA )
WHEN MATCHED THEN OPERAÇÃO_VERDADEIRO
WHEN NOT MATCHED THEN OPERAÇÃO_FALSO
Extensões SQL para OLAP
Merge MERGE INTO CLIENTE CLI
USING AUTOR AU
ON (AU.CPF = CLI.CPF)
WHEN MATCHED THEN
UPDATE SET CLI.NOME = AU.NOME,
CLI.ENDERECO = AU.ENDERECO,
CLI.DATA_NASCIMENTO =
AU.DATA_NASCIMENTO
WHEN NOT MATCHED THEN
INSERT (CODIGO, NOME, CPF,
ENDERECO, DATA_NASCIMENTO)
VALUES (AU.MATRICULA, AU.NOME, AU.CPF,
AU.ENDERECO, AU.DATA_NASCIMENTO)
OLAP – Onde Usar?
Onde aplicar as consultas para a montagem
dos relatórios?
As bases de dados dos sistemas da empresa são,
muitas vezes, descentralizadas
Diferentes SGBDs, locais, plataformas, ...
As bases de dados dos sistemas da empresa
possuem os dados para sua operação. Não
possuem dados históricos...
OLAP – Onde Usar?
Precisa-se de um repositório de informações:
De múltiplas fontes de dados
Localizado em um único site
Com dados históricos
Com esquema único
…
Data-warehouse!
OLTP x OLAP
42
Sistemas
OLTP
Base de dados
operacional
(normalizada)
Sistemas
OLAP
Base de dados
de suporte à
tomada de decisão
(data warehouse)
Recommended