OLAP (On-Line Analytical Processing)
e Banco de Dados Multidimensionais
Jacques Robin
CIn-UFPE
O que é OLAP?
Processamento de dados,
 dedicado ao suporte a decisão,
 por meio de visualização de dados agregrados ao longo
de várias dimensões analíticas (tempo, espaço, categoria
de produto, quantidade vendida, preço...),
 hierarquizadas em várias granularidades,
 armazenados em BD especializadas,
 seguindo um modelo lógico de dados multidimensional,
 chamados de Data Warehouse, Data Mart ou BD
multidimensionais

Exemplos de consultas OLAP:




Quais foram os produtos mais vendidos no mês passado ?
A média salarial dos funcionários de informática com menos de 5 anos
de experiência é maior do que a mesma para funcionários de
telecomunicação?
Qual foi o total de vendas o mês passado por região de vinhos tintos
importados da Europa?
Por quais semanas, quais produtos e quais cidades, a variação de
venda de produtos em promoção em comparação da semana anterior
sem promoção foi  15%
Banco de dado operacional
x data warehouse x data mart

BD operacional:
• armazena valores correntes e
atômicas resultantes direitas
das últimas transações
• a fins operacionais predefinidas
ex, gerenciamento do estoque

Data Mart:
• armazena réplicas históricas,
não voláteis, agregadas ao longo
de várias dimensões analíticas
• as vezes limpadas, completadas
e normalizadas em termos de
escala e distribuição
• de dados de um único banco
operacional
• a fins analíticas abertas de
escopo departamental

Data Warehouse:
• integra e padroniza dados
• de vários:




data marts
BD operacionais
BD de legado empacotados
BD semi-estruturados
extraídos de páginas web
• em um único repositório
coerente e limpo de dados
• a fins analíticas abertas de
escopo organizacional
OLTP
Função
 Usuário humano

Automatizar operações diárias
Cliente, Atendente, DBA

Software cliente Aplicativos de inventário,

Modelo lógico
Granularidade
 Temporalidade
dos dados
 Consultas
 Direção
 Envolve
 Registros
 Usuários
 Bytes
 Prioridade


Métrica
x
contabilidade, ...
Relacional,
orientado por aplicações
Única e atômica
Apenas valor corrente
atualizada continuamente
Simples e predefinidas
Tanto ler quanto escrever
Acessos via índice e hash
10
103
MB-GB
Disponibilidade, eficiência
Numero de transações
OLAP
Auxiliar tomada de decisão
Executivo, Analista,
Eng. de Conhecimento
Aplicativos de mineração de
dados, análise matemática, ...
Multidimensional,
orientado por assuntos
Múltipla e agregada
Histórico dos valores,
completado periodicamente
Complexas e ad-hoc
Essencialmente ler
Junções, varreduras
106
[0-10]
GB-TB
Flexibilidade, interatividade
Número e tempo de cada consulta
Modelo de dados multidimensional

Cuboide:
• Espaço de dimensão N para análise de dado

Dimensão analítica:
•
•
•
•

Atributo geralmente categórico
Escolhido como eixo no espaço analítico N-dimensional
Campo de uma tabela do BD relacional fonte
ex, tempo, local, produto, fornecedor
Medida:
• Atributo geralmente numérico
• Escolhido como ponto no espaço analítico N-dimensional
• Agregação de valores de um campo de uma tabela do BD
relacional fonte, calculada por group-by de outros campos da
relação
• ex, valor total das vendas, valor média das vendas, quantidade
vendidas,
Cuboide de dados: exemplo 4D
Células
Membros
Dimensões
Location
Item
Time
Supplier
Q1
Sup1
Sup2
Q2
Sup1
Sup2
Q3
Sup1
Sup2
Q3
Sup1
Sup2
Q4
Sup1
Sup2
Chicago
HE
Comp
Tel
New York
Secu
HE
Comp
Tel
Toronto
Secu
HE
Comp
Tel
Secu
Cuboide de dados: exemplo 4D
io
at
n
itie
(c
s)
supplier = ÒSUP1Ó
supplier = ÒSUP2Ó
supplier = ÒSUP3Ó
tim e (quarters)
Chicago
New York
Toronto
Vanc ouver
c
lo
Q1 605 825 14
400
Q2
Q3
Q4
computer security
computer security
computer security
home
phone
entertainment
home
phone
entertainment
home
phone
entertainment
item (types)
item (types)
item (types)
Reticulado de Cuboides
0-D (apex) cuboid
all
time
time, item
item
location
time, supplier
time, location
time, item, location
supplier
item, supplier
item, location
time, location, supplier
time, item, supplier
time, item, location, supplier
1-D cuboids
2-D cuboids
location,
supplier
3-D cuboids
item, location,
supplier
4-D (base) cuboid
Tipologia e cálculo das medidas

Medida distributiva:
• agregada por operação distributiva sobre dados atômicos ou
medidas distributivas
• count, sum, max, min

Medida algébrica:
• agregada por operações algébricas sobre dados atômicos ou
medidas distributivas ou algébricas
• avg, standev

Medida holística:
• agregada por operações sem limite constante sobre o espaço
necessário para armazenar os sub-agregados
• median, mode, rank
• em grandes data warehouses, cálculo apenas aproximativo
Hierarquias conceituais: da
multidimensionalidade a multigranularidade

Hierarquia esquemática:
• implícita no esquema relacional do
BD operacional fonte

Hierarquia de agrupamento:
• Inexistente no esquema fonte,
gerada para reduzir numerosidade

year
Hierarquia:
• de ordem total ou parcial
• simples ou múltipla

country
Construção de hierarquias:
• Manual via GUI
• Automática via clustering
province_or_state
quarter
city
month
week
day
street
(a)
(b)
Exemplo de
hierarquia conceitual esquemática
location
all
all
country
province_or_state British Columbia
city
USA
Canada
Vancouver
Victoria
New York
Ontario
Toronto
Ottawa
New York
Buffalo
Illinois
Chicago
Exemplo de
hierarquia conceitual de agrupamento
($0 $1000]
($0 $200]
($0 $100]
($200 $400] ($400 $600] ($600 $800] ($800 $1000]
($200 $300] ($400 $500] ($600 $700] ($800 $900]
($100 $200] ($300 $400]
($500 $600] ($700 $800] ($900 $1000]
Operadores OLAP: navegação no espaço
analítico multidimensional e multigranular

Operadores de navegação ao longo das hierarquias
conceituais:
• Roll-up, abstrai detalhes, aplicando ao cuboide corrente um
operador de agregação dado ao longo de uma dimensão dada
• ex: região  pais
• Drill-down, detalha o cuboide corrente desagregando ao longo de
uma dimensão dada
• ex: região  estado
• Drill-through, detalha os valores, ao longo de uma dimensão dada,
além do nível mais baixo do cuboide, por consultas SQL
diretamente na fonte relacional
• Drill-across, detalha vários cuboides com dimensões
compartilhas, por desagregação ao longo de
Operadores OLAP: navegação no espaço
analítico multidimensional e multigranular

Operadores de navegação ao longo do reticulado de
cuboides:
• Slice, extrair sub-cuboide das células verificando um restrições
de valor ao longo de uma dimensão (ex, time = Q1)
• Dice, extrair sub-cuboide das células verificando um restrições
de valor ao longo de várias dimensões (ex, time = Q1 e item = HE)

Operadores de visualização dos resultados:
• Pivot, mudar os eixos da visualização (cross-tab ou 3D grahics)
do resultado de uma consultas (ex, time na vertical no lugar da
horizontal)
• Rank, ordena os membros de uma dimensão de acordo com a
ordem da medida corrente (ex, time retrospectivo, começando
pelo mais recentes primeiro); serve também para filtragem
es
)
t
un
on
Tor ont o 395
ti
c a Vanc ouv er
t i me
( quar t er s)
lo
ti
Q1
c
lo
605
at
Q2
c omput er
home
ent er t ai nment
i t em ( t y pes )
l
oc
(
ti
ci
es
es
)
2000
Q1 1000
Q2
Q3
Q4
c omput er s ec ur i t y
home
phone
ent er t ai nment
or
i on = ÒTor ont oÓ or ÒVanc ouver Ó)
i me = ÒQ1Ó or ÒQ2Ó) and
= Òhome ent er t ai nment Ó or Òc omput er Ó)
i t em ( t y pes )
r o l l - up
on l oc at i on
( f r om c i t i es
t o c ount r i es )
)
Chi c ago 440
New Yor k 156 0
Tor ont o 395
Vanc ouver
i
at
on
t i me ( quar t er s)
di c e f
( l oc at
and ( t
( i t em
ri
o
(c
USA
n
o
i Canada
t i me ( quar t er s)
i
(c
Q1
605
825
14
4 00
Q2
Q3
Q4
l ocat i on ( ci t i es)
sl i ce
f or t i me = ÒQ1Ó
c omput er s ec ur i t y
home
phone
ent er t ai n men t
dr i
on
(f r
t o
i t em ( t y pes )
Chi cago
New Yor k
Tor ont o
Vancouv er 605 825
14
440
home
ent er t ai nment
605
comput er
825
phone
s ec ur i t y
14
400
New Yor k Vanc ouver
Chi c ago Tor ont o
l oc at i on ( c i t i es )
t
ca
t i me ( mont hs)
pi vot
i
(c
ti
es
)
Chi c ago
New Yor k
Tor ont o
l o Vancouv er
J anuar y
Febr uar y
Mar c h
c omput er s ec ur i t y
home
phone
ent er t ai nment
i t em ( t y pes )
i t em ( t ypes)
l l - down
t i me
om quar t er s
mont hs )
n
io
150
100
150
Apr i l
May
J une
J ul y
Augus t
Sept e mber
Oc t ob er
Nov ember
De cember
c omput er s ec ur i t y
home
phone
en t er t ai nment
i t em ( t y pes )
Modelos físicos de dados para OLAP

ROLAP (OLAP Relacional):
• Armazena dados em tabelas
relacionais
• Reaproveita da tecnologia
relacional, inclusive SQL
• Apenas apresenta dados de
maneira multidimensional
• Permite acoplamento mais
estreito com fontes OLTP
(geralmente relacionais)
• Porém, necessita
remodelagem prévio de dados
em esquema especializados
(estrela, floco de neve)
• Mesmas controversias sobre
performance do que OO x OR

MOLAP (OLAP Multidimensional):
• Armazena dados em arrays de
dimensões N
• Necessita desenvolvimento de
novas técnicas de otimização
• Sem acesso a granularidade
mínima (i.e., única transações)

HOLAP (OLAP Híbrido):
•
•
•
•
•
Duplica dados
Tabelas para dados atómicos
Arrays para agregrados
Flexível e rápido de execução
Custoso em memória e
desenvolvimento
Modelos de dados ROLAP: Estrela

Uma tabela de fato com:
• uma coluna por medida agregada
• uma columa por chave de dimensão analítica

N tabelas de dimensões, uma por dimensão analítica
• uma coluna por para cada atributo descrevendo a dimensão
• geralmente um atributo por nível na hierarquia conceitual

Não normalizada:
• alguma redundância
• alguns níveis e membros aparecem em vários registros
Modelo estrela: exemplo
time
dimension table
time_ key
day
day_of_the_week
month
quarter
year
branch
dimension table
branch_key
branch_name
branch_type
sales
fact table
time_key
item_key
branch_key
location_key
dollars_sold
units_sold
item
dimension table
item_key
item_name
brand
type
supplier_type
location
dimension table
location_key
street
city
province_or_state
country
Modelos de dados ROLAP: Floco de Neve

Igual ao modelo estrela exceto pela normalização das

Vantagens
tabelas de dimensões
• Facilita evolução das dimensões
• Reduz espaço ocupado por elas

Desvantagens:
• Aumenta tempo de resposta pela necessidade de junções

Balanço:
• Espaço ganhado negligível já que espaço total do data mart é
principalmente ocupado pela tabela de fato
• Modelo estrela mais popular
Modelo floco de neve: exemplo
time
dimension table
time_key
day
day_of_week
month
quarter
year
branch
dimension table
branch_key
branch_name
branch_type
sales
fact table
time_key
item_key
branch_key
location_key
dollars_sold
units_sold
item
dimension table
item_key
item_name
brand
type
supplier_key
location
dimension table
location_key
street
city_key
supplier
dimension table
supplier_key
supplier_type
city
dimension table
city_key
city
province_or_state
country
Modelos de dados ROLAP: Constelação
Várias tabelas de fato: um por assunto analítico
 Uma tabela dimensão por dimensão analítica de algum
assunto
 As dimensões compartilhadas por vários assuntos não são
duplicadas, mas apontadas por várias tabelas de fato
 Em geral:

• data mart modelado em estrela
• data warehouse modelado em constelação
• data mart integrado em um data warehouse por:


uniformização das tabelas de dimensões dos vários data marts
ligações entre elas e as tabelas de fato
Modelo constelação: exemplo
tim e
dim ension table
tim e_key
day
day_of_week
m onth
quarter
year
branc h
dim ension table
branc h_key
branc h_name
branc h_type
sales
fac t table
tim e_key
item _key
branc h_key
loc ation_key
dollars_sold
units_sold
item
dim ension table
item _key
item _name
brand
type
supplier_type
loc ation
dim ension table
loc ation_key
street
c ity
province_or_state
c ountry
shipping
shipper
fac t table
dim ension table
item _key
shipper_key
tim e_key
shipper_nam e
shipper_key
loc ation_key
from_loc ation
shipper_type
to_loc ation
dollars_cost
units_shipped
Elementos de um modelo de dados
lógico multidimensional



BDMD: coleção de cuboides
D-dimensionais
Cuboides:
• D dimensões
(ex, tempo, produto, espaço)
• C celulas de dados
quantitativos atómicos =
valores das medidas
Dimensão:
• H hierarquias de N níveis
de granularidade
(ex, ano/mês/dias,
ano/semestre/semana)


Nível: E membros
(ex, {Jan, ..., Dez}, {1, ..., 31})
Cellset: subcubo resultado de uma
consulta OLAP selecionando:
•
•
•
•
um cubo A do DBMD
d dimensões de A como analíticas
m dimensões de A como medidas
para cada d:


uma hierarquia hd
um nivel nd com md membros
• para cada m, uma função de
agregação (sum, max, avg, var)
•  md celulas, cada uma contendo m
dados agregados
Linguagens e API de consulta de BDMD

Até 98, profusão de linguagens:
• proprietárias dos vendedores de SGBDMD
• acadêmicas

2 tentativas de padronização para interoperabilidade
entre BDMD:
• MDAPI do OLAP Council,


liderado pela Oracle e a Arbor
publicação em 98 em 3 formatos:
¤ modelo UML, interfaces Java, interfaces COM


até hoje nenhuma implementação
está rapidamente perdendo apoio
• OLE DB for OLAP


desenvolvido pela Microsoft, porém não proprietária
já apoiado de quase toda indústria dos SGBDMD
OLE DB for OLAP

Para conexão com Data Warehouse e funções
administrativas:
•

usa outros padrões “abertos e orientados a objetos” da
Microsoft: COM e OLE
Para consultar Data Mart conectado:
• usa uma linguagem com sintaxe parecida com SQL: MDX

Independente do modelo físico de dados
• ROLAP, MOLAP ou HOLAP
Amarrado a plataforma Windows
 Pode ser chamado apenas a partir de C++

Padrões da Microsoft
“abertos e orientados a objetos”

UDA (Universal Data Access):
•

COM (Component Object Model):
•
•
•

padrão de interfaces para vários tipos de serviços
como métodos de (pseudo)-objetos sem ID nem atributos
acesso a métodos via ponteiros, herança simples e simulada via includes
OLE (Object Linking and Embedding):
•
•
•

conjunto de padrões e API para acesso a vários tipos de dados
transparentemente do seu suporte de armazenamento físico
conjuntos de interfaces COM fornecendo serviços de acesso a dados de vários
tipos (planhilha, BD, HTML, etc) a partir de C/C++
OLE DB: parte de OLE para acesso a BD
OLE DB for OLAP: parte de OLE DB para acesso a BDMD
ADO (ActiveX Data Objects):
•
•
camada de UDA acima de OLE permintindo acesso “objetos” OLE a partir de
outras linguagens MS como Visual J++ e Visual Basic
ADOMD: parte de ADO para acesso a BDMD
MDX: definição e limitações

MultiDimensional eXpression:
• Linguagemde consulta para SGBDMD
• Expressões da forma SELECT FROM WHERE
• Passadas como parametros string de métodos especializados de
OLE DB for OLAP e ADOMD
• Chamável a partir de Visual C++, Visual J++, Visual Basic etc.
• Fornece built-in largo leque de operadores:



OLAP (rotate, slice, dice, drill-down, roll-up, cross-join etc)
de agregação numérica, estatística e temporal
Limitado a consultas em leitura; não permite:
• Alteração de valores no cubo (celulas)
• Materialização de visões multidimensionais (cellsets)
MDX: exemplo criação de cubo
Dimensões: {Produto, Local,
CREATE CUBE vendas (
DIMENSION Produto HIERARCHY ProdCat
Tempo, Vendas}
LEVEL [ProdCat] LEVEL ALL,
 Hierarquias:
LEVEL [Categoria], LEVEL [SubCat],
• Produto:
DIMENSION Loja HIERARCHY LojaLoc
Software:
LEVEL [LojaLoc] LEVEL ALL, LEVEL [Pais],
{EditorTexto, Planilha, EditorGrafico}
DIMENSION Tempo TYPE TIME
Hardware:
{Mouse,Teclado,Monitor,Gabinete}
LEVEL Ano TYPE YEAR
• Local:
LEVEL Semestre TYPE SEMESTER
País: {Brasil, México, USA}
MEASURE [Vendas] FUNCTION SUM)
• Tempo:

Ano: {1998,1999}
Semestre: {S1,S2}

INSERT INTO vendas
([SubCat], [Pais], [Semestre], [Vendas]),
OPTIONS DEFER_DATA
<CONSULTA SQL RECUPERANDO CAMPOS
CORRESPONDENTES NA OLTP BD FONTE>

WITH
MDX: sintaxe das consultas
• membros virtuais calculados a partir de membros do cubo
• via operadores aritméticos, estatísticos, de agregação, de
series temporais, de formatação de saída

SELECT
• conjunto de membros de dimensões, hierarquias e níveis
• especificado via expressão de operadores




OLAP (DrillDown, Filter, ...)
de manipulação de conjuntos (CrossJoin, Union, Inter, Member ...)
agregação (TopSum, TopCount, TopPercent, Order, ...)
ON eixos de visualização planares:
• row, column, page, sections, chapters
FROM cubo
 WHERE lista de membros de dimensões medidas

MDX: exemplo de visualização inicial
a um nível arbitrário
Brasil
1998
Software Editor de Texto
México
1999
1998
USA
1999
1998
1999
167
173
160
164
197
208
Planilha Eletrônica
120
122
110
130
133
136
Editoração Gráfica
134
137
126
128
157
164
245
265
230
243
280
293
Teclado
80
82
64
66
104
110
Monitor
72
73
56
58
94
98
Gabinete
66
68
52
53
78
81
Hardware Mouse
SELECT NON EMPTY (CROSSJOIN ({[LojaLoc].[Pais].MEMBERS},
{[Tempo].[Ano].MEMBERS}) ON COLUMNS
NON EMPTY {[ProdCat].[SubCat].MEMBERS} ON ROWS
FROM Vendas
WHERE ([Medidas].[Vendas]).
MDX: exemplo de drill-down
até granularidade mais baixa
Brasil
Software Editor de Texto
Planilha Eletrônica
Editoração Gráfica
Hardware Mouse
Teclado
Monitor
Gabinete
1998
S1
S2
95
72
53
67
71
63
112
133
46
34
42
30
29
37
1999
S1
S2
94
79
58
64
63
74
124 141
49
33
31
42
35
33
México
1998
S1
S2
68
92
49
61
68
58
114
116
31
33
32
24
23
29
1999
S1
S2
75
89
62
68
56
72
111
132
29
37
33
25
29
24
USA
1998
S1
S2
99
98
62
71
74
83
136
144
46
58
51
43
37
41
1999
S1
S2
102
106
65
71
86
78
156
137
57
53
53
57
45
36
SELECT NON EMPTY (CROSSJOIN ({[LojaLoc].[Pais].MEMBERS},
{[Tempo].[Semestre].MEMBERS}) ON COLUMNS
NON EMPTY {[ProdCat].[SubCat].MEMBERS} ON ROWS
FROM Vendas
WHERE ([Medidas].[Vendas]).
MDX: exemplo de roll-up
Brasil
1998
México
1999
1998
USA
1999
1998
1999
S1
S2
S1
S2
S1
S2
S1
S2
S1
S2
S1
S2
Software
219
202
215
217
185
211
193
229
235
252
253
255
Hardware
229
234
239
249
200
202
202
218
270
286
311
283
SELECT NON EMPTY (CROSSJOIN ({[LojaLoc].[Pais].MEMBERS},
{[Tempo].[Semestre].MEMBERS}) ON COLUMNS
NON EMPTY {[ProdCat].MEMBERS} ON ROWS
FROM Vendas
WHERE ([Medidas].[Vendas]).
MDX: exemplo do uso de WITH
WITH [Medidas].[Lucro]
AS ‘[Medidas].[Vendas] * ([Medidas].[PrecoUnitario] - [Medidas].[CustoUnitario])’
SELECT NON EMPTY (CROSSJOIN ({[LojaLoc].[Pais].MEMBERS},
{[Tempo].[Ano].MEMBERS}) ON COLUMNS
NON EMPTY {[ProdCat].[SubCat].MEMBERS} ON ROWS
FROM Vendas
WHERE ([Medidas].[Lucro]).