Olap 1p

��

Stanley Robson de M. Oliveira

��

��

� O que é uma ferramenta OLAP?

� Tipos de ferramentas OLAP.

� Requisitos de uma ferramenta OLAP.

� Operações básicas de OLAP.

� Consultas OLAP.

� Arquitetura de uma ferramenta OLAP.

� MOLAP versus ROLAP.

��

��

� Na análise multidimensional, os dados são representados por meio de dimensões (tabelas).

� Combinando essas dimensões, o usuário tem uma visão dos dados de um Data Warehouse.

� A análise multidimensional possibilita operações típicas, tais como:� Os dez maiores (Ex.: produtos mais vendidos);� Comparações de valores entre períodos;� Percentual de variação;� Médias, somas ou valores cumulativos.

��

��

� O termo OLAP refere-se a um conjunto de tecnologias para acesso e análise ad-hoc de dados.

� Aplicações OLAP são conhecidas como queries complexas.

� O objetivo final de uma ferramenta OLAP é dar suporte a decisões gerenciais de forma amigável e flexível, em tempo hábil.

� Em termos de SQL, OLAP envolve “group by”e operadores de agregação.

!��

"��

� ROLAP (OLAP Relacional):� Acessam bancos de dados relacionais.

� MOLAP (OLAP Multidimensional):� Acessam bancos de dados multidimensionais por

meio de cubos e hipercubos.

� HOLAP (OLAP Híbrida):

� Permitem tanto acesso aos bancos de dados relacionais como aos multidimensionais.

� DOLAP (OLAP Desktop):� Voltadas para computadores pessoais.

#��

� Rapidez de Cálculo e Acesso: � A rapidez na execução de consultas é um fator crítico quando

levamos em consideração o volume de dados e as consultas envolvidas.

� Capacidade de Análise Avançada:� Uma ferramenta OLAP deve ser capaz de prover operações mais

sofisticadas.

� Exemplo: média ponderada, normalização, "ranking" (ordenação), valores acumulados no tempo, etc.

$�%��

&��

� Flexibilidade:

� Flexibilidade de visualização é o poder de escolher a forma através da qual a informação será apresentada (Ex.: tabelas, matrizes, gráficos, etc).

� Flexibilidade de definição ao usuário significa lhe dar permissão de mudar o conteúdo de descritores, formatar a apresentação de células, definir fórmulas, etc.

� Flexibilidade de análise é a possibilidade de definição das operações que serão executadas em uma consulta por parte do usuário.

� Flexibilidade de interface é o que usualmente chamamos de interface intuitiva e amigável.

$�%��

'��

� Suporte a múltiplos usuários:

� O desafio das ferramentas OLAP é que os múltiplos acessos aos dados não afetem a performance das consultas de forma significativa.

$�%��

(��

)*��+��

1. O que é uma ferramenta OLAP?

2. Explique a diferença entre:

a) ROLAPb) MOLAPc) HOLAPd) DOLAP

3. Quais são os requisitos básicos para uma ferramenta OLAP?

��


,,��

� Exemplo de dados de vendas representados por umCUBO de dados.

-��.��

Campinas

Americana

Niterói

Campos

SP

RJ

Estado Cidade

Dimensão LocalizaçãoHierarquia de dimensão

Membros dedimensão

1101 1102 2001 2002

Celular Pager

Medidas

Dimensão tempo

Modelo de produto

Produto

Hierarquiada dimensão

,��

� A medida (volume de vendas) é determinada pela combinação de três dimensões: localização, produto etempo.

� As dimensões localização e produto possuem dois níveis de hierarquia.

� A dimensão tempo teria os números dos anos de venda, tais como 2003, 2004, 2005 e 2006.

� Cada subcubo possui o valor da medida de quantidade de venda.

� Exemplo: em um período específico, na cidade de Campinas, estado de SP, foram vendidos 11.000 telefones do modelo 1101.

-��.��$�� /�

,��

��/�

� Imagine você analisando um cubo.� Você fatia os dados e as dimensões que quiser analisar,

observar ou estudar.

,��

-��-�0��$��1�

� São operações para movimentar a visão dos dados ao longo dos níveis hierárquicos de uma dimensão.

� Drill down ocorre quando o usuário aumenta o nível de detalhe da informação, diminuindo o nível de granularidade.

� Roll up ocorre quando o usuário aumenta o nível de granularidade, diminuindo o nível de detalhe da informação.

� Os caminhos de navegação são determinados pelas hierarquias de dimensão.

,!��

$��1�� Exemplo de uma operação de Roll Up utilizando a

dimensão Tempo.

RSSC

7890

6767

2288

5699

Trim. 1 Trim. 2 Trim. 3 Trim. 42004

RegiãoSul

Volume de Produção(em milhares)

RSSC

3028

2630

2232

Janeiro Fevereiro Março2004

RegiãoSul


Roll Up

Dimensão Tempo

,#��

-��-�0�� Exemplo de uma operação de Drill Down utilizando a

dimensão localização geográfica.

Drill Down

Dimensão localização geográfica

Membro RS

RSSC

3345

1234

820

1223

1001 1002 2001 2002Telefone Celular

RegiãoSul


Pagers

CanoasPorto Alegre

1320

48

26

57


RS


Pagers

,&��

2��3��-��-�0��$��1�� Drill Down: aumentar o nível de detalhe.� Roll Up: diminuir o nível de detalhe.

Cidade

Cidade

Cidade

Cidade

Cidade

Estado

Drill Down Roll Up

,'��

-�� Ocorre quando o usuário pula um nível intermediário dentro de

uma mesma dimensão.

� O usuário executa um Drill Across quando ele passar de anodireto para trimestre ou mês.

Drill Across

Dimensão localização geográfica

Membro RS (de ano para mês)

RSSC

3345

1234

820

1223


RegiãoSul


Pagers

RSSC

25

43

13

42

Janeiro 01 Janeiro 02 Janeiro 01 Janeiro 02Telefone Celular

RegiãoSul


Pagers

,(��

-��"4��4

� Ocorre quando um usuário passa de uma informação contida em uma dimensão para uma outra.

� Exemplo: um usuário está na dimensão tempoe, no próximo passo, começa a analisar a informação por região.

�5��

2��-��

� São operações para realizar navegação por meio dos dados na visualização de um cubo.

� Slice and Dice é o mesmo que filtrar.

�,��

2��-��6

� Utilizando as operações Slice and Dice, conseguimos ver a informação de ângulos que anteriormente inexistiam sem a implementação de um DW e a utilização de uma ferramenta OLAP.

� Slice é operação que corta o cubo, mas mantém a mesma perspectiva de visualização dos dados.

��

2��-��6� Tabela 1 apresenta as vendas de celulares e pagers.

� Tabela 2 representa uma fatia dos dados (operação que visualiza somente a produção de um tipo de produto – celulares).

RSSC

3028

2630

2232

Janeiro Fevereiro MarçoCelulares e Pagers

RegiãoSul


Tabela 1

Tabela 2

RSSC

2219

1827

1825

Janeiro Fevereiro MarçoCelulares

RegiãoSul


��

2��-��6� Dice é a mudança de perspectiva da visão.

� É a extração de um subcubo ou a interseção de vários slices.

� É como se girássemos o cubo em nossas mãos.

��

2��-��6� Estávamos visualizando e analisando no sentido estado, cidade,

ano, modelo de produto e produto (Tabela 1).

� Dice é a mudança de perspectiva para modelo de produto,produto, ano, estado e cidade (Tabela 2).

CanoasPorto Alegre

1320

48

26

57


RS

Volume de Produção(em milhares) Pagers

2006

Tabela 1

Tabela 2 10011002

134

208

Canoas Porto AlegreRS

Telefone Celular


2006

20012002

25

67

Pagers

�!��

�7��

� É o ângulo pelo qual os dados são vistos.

� Na prática, corresponde à modificação das dimensões em um gráfico ou troca de linhas por colunas em uma tabela.

� É uma característica de planilhas eletrônicas, a sua capacidade de realizar slice.

� Pivot representa agregações em dimensões selecionadas.

� Exemplo: “Pivoting” em Local e Tempo resulta na seguinte tabulação cruzada:

Dimensão localDimensão

Tempo

��


�&��

��89��2��

� Algumas consultas OLAP não podem ser facilmente formuladas em SQL.

� Exemplos:� Consultas que ranqueiam resultados;� Consultas que envolvem operações baseadas no

tempo.

� Um grande número de consultas podem ser formulada usando SQL.

� As consultas formuladas em SQL, envolvem:� “grouping” e “aggregation”

�'��

��89��2��:::

SELECT SUM(S.sales)FROM Sales S, Times T, Locations LWHERE S.timeid=T.timeid AND S.timeid=L.timeidGROUP BY T.year, L.state

SELECT SUM(S.sales)FROM Sales S, Times TWHERE S.timeid=T.timeidGROUP BY T.year

SELECT SUM(S.sales)FROM Sales S, Location LWHERE S.timeid=L.timeidGROUP BY L.state

Dimensão localDimensão

Tempo

Valores agregados por ano Valores agregados por estado

A tabulação cruzada da operação Pivot

�(��

��/��;��/�<

� Em geral, se há K dimensões, temos 2k possíveis consultas SQL GROUP BY que podem ser geradas através de pivoting em um subconjunto de dimensões.

� CUBE pid, locid, timeid BY SUM Sales

� Equivalente a rolling up Sales em todos oitos subconjuntos do conjunto {pid, locid, timeid}; cada roll up corresponde a uma consulta SQL da forma:

SELECT SUM(S.sales)FROM Sales SGROUP BY grouping-list

As consultas diferem somente na seleção das dimensões:grouping-list

�5��

��/��;��/�<�:::

� O relacionamento das 8 consultas possíveis no CUBOpode ser representado por um reticulado:

{pid, locid, timeid}

{pid, locid} {pid, timeid} {locid, timeid}

{pid} {locid} {timeid}

{ }

Reticulado das consultas GROUP BY em um CUBO

�,��

)*��=�>��)��.��

� Elementos participantes de uma venda:� Quando foi realizada a venda?

� Onde foi realizada a venda?

� Quem realizou a venda?

� O que foi vendido?

Vendas

Onde?

Quem?

Quando?

O quê?

��

Modelo físico (nível de implementação).

Cod_TempoCod_LocalCod_ProdutoCod_Vendedor

Qtde_ProdutoTotal_Venda

VendasCod_Tempo

AnoTrimestreMesData

Cod_Local

RegiaoEstadoCidade

Tempo

Local

Cod_Vendedor

Nome_Vendedor

Vendedor

Cod_Produto

Tipo_ProdutoNome_Produto

Produto

)*��=�>��)��.��

��

)*��=�"�/��.��;,��<

32.100,00C723

9.150,00B622

76.400,00A521

14.700,00D424

8.900,00C323

5.400,00B222

2.700,00A121

6.500,00A1214

13.100,00D1113

12.800,00C1012

11.450,00B911

4.300,00A814

35.000,00D713

3.450,00C612

25.000,00B511

7.500,00D414

550,00C313

8.975,00B212

15.350,00A111

Total_VendaCod_ProdutoCod_PeriodoCod_VendedorCod_Local

��

��1��2��

1. Qual o total de vendas durante todo o período?SELECT SUM(total_venda) FROM vendas;

2. Qual o total de vendas do vendedor 1 no período?SELECT SUM(total_venda) FROM vendas WHERE cod_vendedor =1;

3. Qual o total de vendas do vendedor 2, no último trimestre?SELECT SUM(total_venda) FROM vendas WHERE cod_vendedor = 2 AND (cod_periodo = 10 OR cod_periodo = 11 OR cod_periodo = 12);

�!��

��1��2��:::

4. Qual o total de vendas do vendedor 1 no último trimestre, no local 3?SELECT SUM(total_venda) FROM vendas WHERE cod_vendedor = 1 AND cod_local = 3 AND (cod_periodo = 10 OR cod_periodo = 11 OR cod_periodo = 12);

5. Qual o total de vendas do vendedor 2 no período, por produto?SELECT cod_produto, SUM(total_venda) FROM vendas WHERE cod_vendedor = 2 GROUP BY cod_produto;

6. Qual o total de vendas do vendedor 1 no período, por produto, por local?SELECT cod_local, cod_produto, SUM(total_venda) FROM vendas WHERE cod_vendedor = 1 GROUP BY cod_local, cod_produto;

�#��

)*��1��2��:::

7. Qual o total de vendas do produto A para o vendedor 1, no último semestre, por local?SELECT cod_local, SUM(total_venda) FROM vendasWHERE cod_vendedor = 1 AND cod_produto = 'A' AND (cod_periodo > 6 AND cod_periodo <= 12) GROUP BY cod_local;

8. Qual o total de vendas dos produtos A e D para o vendedor 2, no período, por local?SELECT cod_local, SUM(total_venda) FROM vendasWHERE cod_vendedor = 2 AND (cod_produto = 'A' OR cod_produto = 'D') GROUP BY cod_local;

��

Stanley Robson de Medeiros Oliveira

�'��

��?��

� Existem várias maneiras de se classificar uma ferramenta OLAP quanto à sua arquitetura.

� Em geral, a classificação de ferramentas considera o tipo de dados acessado e o processamento desses dados.

� Quanto aos dados acessados, podemos classificar as ferramentas como:� MOLAP ou OLAP Multidimensional;

� ROLAP ou OLAP Relacional;

� HOLAP ou OLAP híbrido.

�(��

��>��

� Ferramentas MOLAP acessam bancos de dados multidimensionais.

� Ao invés de acessarem tabelas relacionais, MOLAPextraem informações de estruturas de dados pré–calculados (Cubos).

� Desvantagens do uso de ferramentas MOLAP:� Escalabilidade limitada, já que o tamanho do cubo fica

muito grande e sua carga muito demorada à medida em que adicionamos dimensões ou dados mais detalhados.

�5��

� Vantagens do uso de ferramentas MOLAP:� O tempo de resposta é bem mais rápido do que o das

ferramentas que acessam tabelas relacionais.

� As ferramentas MOLAP se aplicam melhor no caso onde o usuário: � já tem definidas as dimensões e os conceitos com os

quais ele quer trabalhar, ou seja, tem o escopo de sua análise de dados já definida.

��>��:::

�,��

� As ferramentas ROLAP (OLAP Relacionais) não utilizam os cubos pré–calculados.

� A característica principal das ferramentas ROLAP é o poder de fazer qualquer consulta.

� ROLAP não está limitada ao conteúdo de um "cubo" e à capacidade de navegar nos dados em níveis de detalhe mais baixos.

� Ferramentas ROLAP atendem melhor usuários que não têm um escopo de análise bem definido.

��$��

��

� Os usuários típicos de ROLAP:� Usuários da área de Marketing;� Estão sempre buscando novos relacionamentos

entre as dimensões existentes.

� A desvantagem mais citada das ferramentas ROLAPé o seu tempo de resposta.

� Isso pode ser amenizado com a construção de tabelas agregadas:� Positivo: diminuem o tempo de resposta;� Negativo: ocupam um grande espaço em disco e

exigem um certo esforço de manutenção.

��$��:::

��

>��7��$��

� O desenvolvimento MOLAP é mais fácil, pois a ferramenta realiza as agregações sozinha e não requer tuning específico do BD multidimensional.

� Já o desenvolvimento ROLAP requer:

� Um projeto lógico específico (star ou snowflakeschema);

� Tuning do banco de dados relacional;

� Criação e manutenção das tabelas sumarizadas.

��

>��7��$��:::

� Os argumentos usados pelos fabricantes dessas ferramentas são:

� Oferecer a escolha entre um banco de dados relacional e um banco de dados multidimensional;

� Carregar resultados de consultas relacionais em um banco de dados multidimensional, ou estrutura que simule a multidimensionalidade dos dados;

� Usar um banco de dados multidimensional p/ fazer um cache dos dados com maior nível de agregação;

� Usar um banco relacional para fazer um acesso dinâmico aos dados detalhados.

�!��

� Outra classificação das ferramentas OLAP diz respeito às funcionalidades.

� Existem três funções básicas inerentes a quase todos os sistemas:

� Interface com o usuário;

� Gerenciamento do processamento ou camada lógica do sistema;

� Gerenciamento (acesso e armazenamento) dos dados.

��?��89��%��@��

�#��

� A estrutura clássica de um ambiente cliente-servidor coloca a interface exclusivamente no cliente.

� O gerenciamento dos dados fica no servidor para garantir o compartilhamento dos dados.

� O processamento é distribuído entre o cliente e o servidor.

��?��89��%��@��

�&��

� Ferramentas OLAP multidimensionais possuem uma arquitetura de duas camadas.

� O banco de dados multidimensional realiza as tarefas de armazenamento de dados, acesso e recuperação de registros, etc.

� Ao cliente, cabem todas as funcionalidades de interface.

��?��89��%��@��

�'��

� As ferramentas OLAP relacionais geralmente têm uma arquitetura de três camadas: banco de dados relacional, mecanismo OLAP e interface (front-end).

� Através da interface, atuando como cliente, acessamos o mecanismo OLAP, responsável por acessar o banco de dados e multidimensionalizar esses dados para consulta.

� O processamento fica dividido entre o mecanismo OLAP e o cliente.

��?��89��%��@��

�(��

)*��+��1. Qual é a diferença básica entre ferramentas MOLAP e

ROLAP?

2. Cite uma vantagem e uma desvantagem do uso de ferramentas MOLAP.

3. Quais seriam as melhores aplicações para ferramentas MOLAP?

4. Cite uma vantagem e uma desvantagem do uso de ferramentas ROLAP.

5. Quais seriam as melhores aplicações para ferramentas ROLAP?

6. Quantas camadas tem as ferramentas MOLAP e ROLAP, respectivamente? Quais são essas camadas?

!5��

)*��

� Para exemplos de ferramentas OLAP e ferramentas de Data Warehouse, favor consultar:

� Capítulo 16 do livro “Tecnologia e Projeto de Data Warehouse: uma visão multidimensional”

Autor: Felipe Nery Rodrigues Machado.Editora Érica, 2004.

Documents

Olap 1p