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: Data Warehouse:
• armazena valores correntes e • integra e padroniza dados
atômicas resultantes direitas
das últimas transações • de vários:
data marts
• a fins operacionais predefinidas
ex, gerenciamento do estoque BD operacionais
Data Mart: BD de legado empacotados
BD semi-estruturados
• armazena réplicas históricas,
extraídos de páginas web
não voláteis, agregadas ao longo
de várias dimensões analíticas • em um único repositório
• as vezes limpadas, completadas coerente e limpo de dados
e normalizadas em termos de • a fins analíticas abertas de
escala e distribuição escopo organizacional
• de dados de um único banco
operacional
• a fins analíticas abertas de
escopo departamental
OLTP x OLAP
Função Automatizar operações diárias Auxiliar tomada de decisão
Usuário humano Cliente, Atendente, DBA Executivo, Analista,
Eng. de Conhecimento
Software cliente Aplicativos de inventário, Aplicativos de mineração de
contabilidade, ... dados, análise matemática, ...
Modelo lógico Relacional, Multidimensional,
orientado por aplicações orientado por assuntos
Granularidade Única e atômica Múltipla e agregada
Temporalidade Apenas valor corrente Histórico dos valores,
dos dados atualizada continuamente completado periodicamente
Consultas Simples e predefinidas Complexas e ad-hoc
Direção Tanto ler quanto escrever Essencialmente ler
Envolve Acessos via índice e hash Junções, varreduras
Registros 10 106
Usuários 103 [0-10]
Bytes MB-GB GB-TB
Prioridade Disponibilidade, eficiência Flexibilidade, interatividade
Métrica Numero de transações 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 Chicago New York Toronto
Item HE Comp Tel Secu HE Comp Tel Secu HE Comp Tel Secu
Time Supplier
Q1 Sup1
Sup2
Q2 Sup1
Sup2
Q3 Sup1
Sup2
Q3 Sup1
Sup2
Q4 Sup1
Sup2
Cuboide de dados: exemplo 4D
s)
itie
(c
n supplier = ÒSUP1Ó supplier = ÒSUP2Ó supplier = ÒSUP3Ó
io
c at Chic ago
lo New Y ork
tim e (quarters)
Toronto
Vanc ouver
Q1 605 825 14 400
Q2
Q3
Q4
c omputer sec urity c omputer sec urity c omputer sec urity
home phone home phone home phone
entertainment entertainment entertainment
item (types) item (types) item (types)
Reticulado de Cuboides
all 0-D (apex) cuboid
item location 1-D cuboids
time supplier
time, supplier item, supplier
time, item location,2-D cuboids
time, location item, location supplier
time, location, supplier 3-D cuboids
time, item, location
time, item, supplier item, location,
supplier
time, 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 country
year
Hierarquia:
• de ordem total ou parcial province_or_state quarter
• simples ou múltipla
Construção de hierarquias: city month week
• Manual via GUI
• Automática via clustering street
day
(a) (b)
Exemplo de
hierarquia conceitual esquemática
location
all all
country Canada USA
province_or_state British Columbia Ontario New York Illinois
city Vancouver Victoria Toronto Ottawa New York Buffalo Chicago
Exemplo de
hierarquia conceitual de agrupamento
($0 $1000]
($0 $200] ($200 $400] ($400 $600] ($600 $800] ($800 $1000]
($0 $100] ($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 es
i ti t ri
(c o un
on Tor ont o 395 (c
t i anc ouv er o n Canada
USA
( quar t er s)
2000
ca V
t i me ( quar t er s)
i
lo Q1 605 c at
t i me
lo 1
Q 1000
Q2 Q2
c omput er Q3
hom e
ent er t ai nment Q4
i t em ( t y pes )
c om put er s ec ur i t y
hom e phone
di c e f or ent er t ai nment
( l oc at i on = ÒTor ont oÓ or Ò Vanc ouver Ó) i t em ( t y pes )
and ( t e Q
i m = Ò 1Ó or Ò Q2Ó) and
( i t em = Ò e
hom ent er t ai nm c
ent Ó or Ò om put er Ó)
r ol l - up
on l oc at i on
(f r om c i t i es
) t o c ount r i es )
es
ti
( ci
Chi c ago 440
i on
e or
N w Y k 156 0
at
oc Tor ont o 395
t i me ( quar t er s)
l Vanc ouver
Q1 605 825 14 400
Q2
Q3
Q4
sl i ce
c omput er s ec ur i t y
e Q
f or t i m = Ò 1Ó
hom e phone
ent er t ai n ment dr i l l - down
l ocat i on ( ci t i es)
i t em ( t y pes ) on t i m e
(f r om quar t er s
Chi cago t o m ont hs )
or
New Y k
Tor ont o )
es
Vancouv er 605 825 14 440 i ti
(c
Ch i c ago
n
c om put er s ec ur i t y
t ioN or
ew Y k
Tor ont o
hom e phone ca
ent er t ai nm ent l o Vancouv er
i t em ( t y pes ) J anuar y 150
Febr uar y 100
M ch
ar 150
t i me ( mont hs)
pi vot Apr i l
M ay
J une
J ul y
hom e
i t em ( t ypes)
ent er t ai nment 605 Augus t
Sept em ber
comput er 825
c
O t ober
phone 14 Nov em ber
De cember
s ec ur i t y 400
p
c om ut er s ec ur i t y
N or
ew Y k Vanc ouver hom e ph on e
Chi c ago Tor ont o ent er t ai nm ent
l oc at i on ( c i t i es ) i t em ( t y pes )
Modelos físicos de dados para OLAP
ROLAP (OLAP Relacional): MOLAP (OLAP Multidimensional):
• Armazena dados em tabelas
relacionais • Armazena dados em arrays de
dimensões N
• Reaproveita da tecnologia
relacional, inclusive SQL • Necessita desenvolvimento de
• Apenas apresenta dados de novas técnicas de otimização
maneira multidimensional • Sem acesso a granularidade
• Permite acoplamento mais mínima (i.e., única transações)
estreito com fontes OLTP
(geralmente relacionais) HOLAP (OLAP Híbrido):
• Porém, necessita • Duplica dados
remodelagem prévio de dados • Tabelas para dados atómicos
em esquema especializados
(estrela, floco de neve) • Arrays para agregrados
• Mesmas controversias sobre • Flexível e rápido de execução
performance do que OO x OR • 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 sales item
dimension table fact table dimension table
time_ key time_key item_key
day item_key item_name
day_of_the_week branch_key brand
month location_key type
quarter dollars_sold supplier_type
year units_sold
branch location
dimension table dimension table
branch_key location_key
branch_name street
branch_type city
province_or_state
country
Modelos de dados ROLAP: Floco de Neve
Igual ao modelo estrela exceto pela normalização das
tabelas de dimensões
Vantagens
• 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 sales item supplier
dimension table fac t table dimension table dimension table
time_key time_key item_key supplier_key
day item_key item_name supplier_type
day_of_week branc h_key brand
month loc ation_key type
quarter dollars_sold supplier_key
year units_sold
branc h loc ation
dimension table dimension table
branc h_key loc ation_key c ity
branc h_name street dimension table
branc h_type c ity_key c ity_key
c ity
provinc e_or_state
c ountry
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 sales item shipping shipper
dim ension table fac t table dim ension table fac t table dim ension table
tim e_key tim e_key item _key item _key shipper_key
day item _key item _name tim e_key shipper_nam e
day_of_week branc h_key brand shipper_key loc ation_key
m onth loc ation_key type from_loc ation shipper_type
quarter dollars_sold supplier_type to_loc ation
year units_sold dollars_cost
units_shipped
branc h loc ation
dim ension table dim ension table
branc h_key loc ation_key
branc h_name street
branc h_type c ity
province_or_state
c ountry
Elementos de um modelo de dados
lógico multidimensional
BDMD: coleção de cuboides Nível: E membros
D-dimensionais (ex, {Jan, ..., Dez}, {1, ..., 31})
Cuboides: Cellset: subcubo resultado de uma
• D dimensões consulta OLAP selecionando:
(ex, tempo, produto, espaço) • um cubo A do DBMD
• C celulas de dados • d dimensões de A como analíticas
quantitativos atómicos = • m dimensões de A como medidas
valores das medidas • para cada d:
Dimensão: uma hierarquia hd
um nivel nd com md membros
• H hierarquias de N níveis
• para cada m, uma função de
de granularidade
agregação (sum, max, avg, var)
(ex, ano/mês/dias, • md celulas, cada uma contendo m
ano/semestre/semana) 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):
• conjunto de padrões e API para acesso a vários tipos de dados
transparentemente do seu suporte de armazenamento físico
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):
• 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 (
Tempo, Vendas} DIMENSION Produto HIERARCHY ProdCat
LEVEL [ProdCat] LEVEL ALL,
Hierarquias:
LEVEL [Categoria], LEVEL [SubCat],
• Produto:
Software:
DIMENSION Loja HIERARCHY LojaLoc
{EditorTexto, Planilha, EditorGrafico} LEVEL [LojaLoc] LEVEL ALL, LEVEL [Pais],
Hardware: DIMENSION Tempo TYPE TIME
{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}
INSERT INTO vendas
Semestre: {S1,S2}
([SubCat], [Pais], [Semestre], [Vendas]),
OPTIONS DEFER_DATA
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 México USA
1998 1999 1998 1999 1998 1999
Software Editor de Texto 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
Hardware Mouse 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
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 México USA
1998 1999 1998 1999 1998 1999
S1 S2 S1 S2 S1 S2 S1 S2 S1 S2 S1 S2
Software Editor de Texto 95 72 94 79 68 92 75 89 99 98 102 106
Planilha Eletrônica 53 67 58 64 49 61 62 68 62 71 65 71
Editoração Gráfica 71 63 63 74 68 58 56 72 74 83 86 78
Hardware Mouse 112 133 124 141 114 116 111 132 136 144 156 137
Teclado 46 34 49 33 31 33 29 37 46 58 57 53
Monitor 42 30 31 42 32 24 33 25 51 43 53 57
Gabinete 29 37 35 33 23 29 29 24 37 41 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 México USA
1998 1999 1998 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]).