Sumárioronaldo/ine5342/6-dw.pdf– quantos liquidificadores Walita foram vendidos ... – qual a marca de forno micro-ondas mais vendida para clientes das classes A e B?

1

Sumário

1 Introdução2 BD Orientado a Objetos3 BD Objeto-Relacional4 Noções Básicas de Data Warehouse5 XML e BD XML

Motivação• Sistemas de Apoio à Decisão

– Objetivo• análise de dados históricos da organização para

auxílio na tomada de decisões estratégicas– exemplo: análise de volume trimestral de vendas como

auxílio para determinar quais produtos adquirir e em que quantidade nestes períodos do ano

– Usuários• Alto escalão de organizações (gerentes, analistas

de negócio, ...)

2

Motivação• Questão

– BDs não são adequados a operações analíticas• análise de informação exige flexibilidade de correlação e

sumarização de dados• BDs não necessariamente são históricos

• Solução: Data Warehouse (DW)– Sistema que armazena dados históricos para

tomada de decisão

– Interrelaciona diversos dados corporativos em um único repositório

• apresenta visões centralizadas e dinâmicas de dados que podem vir de várias fontes de dados da organização

Povoamento de um DW – “ETC”

3

Tecnologia BD x Tecnologia DW

periódicacontínuaatualização dados

variávelfixa (detalhada)granularidade dos dados

muito altoaltovolume de dados

históricos/não-voláteisinstantâneos/voláteisdados

informativooperacionaluso

verificada na execução dos processos ETC

verificada na execução de operações

integridade

OLAPOLTPprocessamento

predefinida e ad hocpredefinidatipo interação

nível gerencialfuncionáriostipo usuário

médio/pequenoaltonúmero usuários

DWBDCaracterística

OLTP

• On-Line Transaction Processing– processamento baseado em transações

• Características– processamento orientado à aplicação

• execução de transações diárias da organização

– operações realizadas pelas transações• I, E, A ou C de dados

– ênfase na eficiência• processamento rápido e seguro

– operações não alteram a estrutura dos dados

4

OLTP – Exemplos de Operações

• Domínio Exemplo: Setor de Vendas de uma Loja– incluir um novo produto– alterar o preço de um produto– consultar o preço dos fornos micro-ondas– remover os liquidificadores fornecidos pela

Walita

OLAP

• On-Line Analitical Processing– processamento baseado em análises

• Características– processamento orientado à tomada de

decisões– operações realizadas

• C de dados

– ênfase na flexibilidade analítica• possibilidade de definir diversas visões de dados

– exemplos: produto X clientes de uma faixa de renda; produto X trimestres do ano, ...

– operações alteram a estrutura dos dados

5

OLAP – Exemplos de Operações

• Domínio Exemplo: Setor de Vendas de uma Loja– quantos liquidificadores Walita foram vendidos

no período de maio a setembro?– qual a marca de forno micro-ondas mais

vendida para clientes das classes A e B?– qual a média de tempo que clientes da classe

A levam para trocar de forno micro-ondas nas cidades de Florianópolis e Lages?

DW e Tecnologia OLAP

• DW– “armazém” de dados históricos da organização

• OLAP– tecnologia para análise ad-hoc destes dados

• OLAP é a atividade básica do DW“não adianta termos um DW bem definido senão tivermos ferramentas que permitamconsultas analíticas para fins de tomada dedecisão”

6

DW – Ferramentas de Análise

DW

FerramentasOLAP

Usuários

FerramentasData Mining

FerramentasDiversas

DMn

DM1

. . .

Ambiente do DW


DW

FerramentasOLAP

Usuários


FerramentasDiversas

DMn

DM1

. . .

Ambiente do DW

• classe mais comum de ferramenta analítica• manipulação de esquemas multidimensionais• operações de reorganização de dados• exemplos: Oracle, SQL Server, Informix,

DSS MicroStrategy, SAS

7


DW

FerramentasOLAP

Usuários


FerramentasDiversas

DMn

DM1

. . .

Ambiente do DW

• ferramentas para descoberta de tendênciasou padrões implícitos nos dados

• base: técnicas de aprendizado de máquina (redes neurais, algoritmos genéticos, ...) e análise estatística


DW

FerramentasOLAP

Usuários


FerramentasDiversas

DMn

DM1

. . .

Ambiente do DW

• ferramentas mais simples• atuam diretamente sobre o esquema

predefinido do DW• exemplos: geradores de relatórios, browsers

8

Modelos Multidimensionais

• Modelos manipulados pelas ferramentas OLAP

• Modelos para representação de assuntospassíveis de análise– foco na definição de visões

• Conceitos– fatos– dimensões– medidas (variáveis ou valores)

Fato

• Representação de um assunto• Um assunto pode ser

– dado operacional, transação do negócio ou evento

• Um fato é composto por dimensões e medidas

• Exemplos (domínio Loja):– vendas (transação do negócio)– promoções (evento)– produtos e estoque (dado operacional)

9

Dimensão

• Representação dos contextos relevantes para a análise de um fato

• Exemplo:– Fato: Vendas– Dimensões: Clientes, Produtos, Tempo,

Locais, ...

• Uma dimensão pode– conter membros– ser organizada em hierarquias

Dimensão - Membro

• Um membro dá mais detalhes sobre uma dimensão

• Exemplos:– dimensão Tempo

• dia, semana, horário

– dimensão Locais• bairro, cidade, estado

10

Dimensão - Hierarquia

• Uma hierarquia define os níveis de granularidade de cada dimensão (ordenação de membros)– mais abaixo na hierarquia → mais detalhado

(granularidade mais fina)

• Exemplos para a dimensão Tempo– hierarquia 1

• ano-trimestre-mês-dia

– hierarquia 2• semana-dia-hora

Medida• Representação de atributos (variáveis)

relevantes para a análise de um fato– valores numéricos em geral

– indicadores de desempenho para a análise

• Uma medida é determinada pela associação de dimensões– produto X tempo.ano;

local.estado X cliente.classe X tempo.semana.dia

• Exemplo:– Fato: Vendas

– Medidas: quantidade vendida, valor da venda, ...

11

Visualização

• Uma visão multidimensional é usualmente representada por um cubo– adequado à visualização de até 3 dimensões

• análise tridimensional é bastante utilizada

– hipercubo: cubo com mais de 3 dimensões

73

12

59

dimensão x

dimensão y

dimensão z

Cubo do fato A

medidas

Visualização - Exemplo

• Cubo do Fato Vendas

12152521

20384046

41322311

41382122

Caxias Sul

P. AlegreRS

Lages

FpolisSC

CidadeEstado

TipoMicro-OndasLiquidificador

MarcaSharpConsulFaetWalita

Ano2000

20012002

Volume de vendas (milhares de R$)

Dimensão Local

Dimensão Produto

Dimensão Tempo

12

Esquemas Relacionais para DW

• Duas abordagens– esquema estrela (STAR)– esquemas floco de neve (SNOWFLAKE)

Esquema Estrela

id_tempoid_produtoid_localid_clienteqtde_vendidavalor_vendidopercentual_lucro

Tabela de Fatos: Vendas

id_tempoanotrimestremêsdia

Tabela de Dimensão: Tempo

id_localregiãoestadocidadefilial

Tabela de Dimensão: Local

id_produtotipomarcamodelopreço

Tabela de Dimensão: Produto

id_clientenomeclasseendereço

Tabela de Dimensão: Cliente

13

Esquema Estrela• Tabelas de Dimensões (pontas da estrela)

– tabelas geralmente derivadas das tabelas de dados operacionais (nem sempre é verdade para o caso da dimensão Tempo)

– dados mais estáveis a cada carga do DW– mantém dados sobre todos os membros– hierarquias implícitas

• Tabela de Fatos (centro da estrela)– tabela para fins de análise– atributos: chave primária composta + medidas

• tabela de relacionamento: várias associações 1:N

– dados mais variáveis a cada carga do DW

Esquema Floco de Neve


Tabela de Fatos: Vendas

id_tempoid_diaid_semana

Dimensão: Tempo

id_localnome_filialid_cidade

Dimensão: Local

id_produto. . .

Dimensão: Produto

id_clientenome. . .

Dimensão: Clienteid_cidade

nome_cidadeid_estado

Dimensão: Cidade

id_estadonome_estadoid_região

Dimensão: Estado

. . .

id_dianúmero_diaid_mês

Dimensão: Dia

id_semanaid_trimestre

Dimensão: Semana

id_mêsnome_mêsid_ano

Dimensão: Mês

. . .

. . .

14

Esquema Floco de Neve

• Variante do modelo estrela– tabelas de dimensões estão normalizadas (3FN)

• representação explícita das hierarquias

• Vantagem– evita redundância (mais economia de espaço)

• Desvantagem– maior número de tabelas (maior número de junções)

• Modelo estrela é considerado mais apropriado– desperdício de espaço é menos crítico que tempo

para recuperação de dados em um DW

Esquema Constelação• União de esquemas estrela e/ou floco de neve

– esquema estrela: modela em geral um Data Mart– esquema constelação: modela o DW


Tabela de Fatos: Vendas id_produto

nomemarcamodelopreço

Tabela de Dimensão: Produto

id_clientenomeclasseendereço

Tabela de Dimensão: Cliente

id_tempoid_produtoid_fornecedorqtde_compradavalor_compradoprazo_entrega

Tabela de Fatos: Compras

id_fornecedornomeendereço

Tabela de Dimensão: Fornecedor

15

Ferramentas OLAP

• Funcionalidades principais– definição de hipercubos

– aplicação de operações analíticas• navegação, consulta e reorganização de hipercubos

– visualização de hipercubos

– definição de fórmulas aplicadas a dimensões• cálculos predefinidos

Operações OLAP• Principais operações

– Pivot• rotação do hipercubo

– Drill-Down e Roll-Up• modificação da granularidade do hipercubo

– navegação nas hierarquias de dimensões

– Slice-and-Dice• redução do número (projeção) de dimensões do

hipercubo– mantém a mesma perspectiva de visualização

• Operações podem ser combinadas para se obter a visualização desejada

16

Pivot• Rotação do cubo

384046

322311

382122

dimensão Cdimensão B

dimensão A

421

Pivot (B,C)

214

322311

382122

dimensão Bdimensão C

dimensão A

4638

40

Pivot - Exemplo

Micro-ondasLiquidificador

2002Quantidade Vendida

(milhares)

15277SUL

Pivot (local, produto)

152Micro-ondas

77Liquidificador

SUL


(milhares)

17

Drill-Down• Aumento de detalhe (granularidade + fina)

384046

322311

382122

dimensão C

dimensão B

dimensão A

421

Drill-Down (A.x,B.y)

dimensão B.y

dimensão A.x

421

6978811

3201114423

444735

101421021

894565

1849374(x = x1)

(x = x2)

(x = x3)

(y = y1) (y = y2) (y = y3)

Drill-Down - Exemplo



(milhares)

15277SUL

Drill-Down (local.região.estado,produto.tipo.marca)

25

20

25

Consul

13

10

16

Faet


129SC

PR

RS 4014

SharpWalita


(milhares)

3015

SUL

18

Roll-Up• Diminuição de detalhe (granularidade mais grossa)

Roll-Up (A,B)

dimensão B.y

dimensão A.x

421

6978811

3201114423

444735

101421021

894565

1849374(x = x1)

(x = x2)

(x = x3)

(y = y1) (y = y2) (y = y3)

384046

322311

382122

dimensão C

dimensão B

dimensão A

421

dimensão C

Roll-Up - Exemplo

Roll-Up (produto.tipo)

25

20

25

Consul

13

10

16

Faet


129SC

PR

RS 4014

SharpWalita


(milhares)

3015

SUL


3219SC

PR

RS 6530


(milhares)

5528

SUL

19

Slice-and-Dice• Projeção de dimensões (“fatia e corta”)

Slice-and-Dice (A.x = x2,B.y = y1 ∨ B.y = y3)

dimensão A.x4435

101421(x = x2)

421

dimensão C

dimensão B.y

(y = y1) (y = y3)

dimensão B.y

dimensão A.x

421

6978811

3201114423

444735

101421021

894565

1849374(x = x1)

(x = x2)

(x = x3)

(y = y1) (y = y2) (y = y3)dimensão C

Slice-and-Dice - Exemplo

Slice-and-Dice (local.região.estado = ‘SC’, produto.tipo.marca = ‘walita’ ∨produto.tipo.marca = ‘sharp’)

25

20

25

Consul

13

10

16

Faet


129SC

PR

RS 4014

SharpWalita


(milhares)

3015

SUL


129SC

SharpWalita


(milhares)

SUL

20

Exercício• Considere um esquema estrela para um fato Compras

composta pelas seguintes hierarquias de dimensões:• Produto: Tipo←Marca←Modelo• Fornecedor: Categoria←Nome• Tempo: Ano←Mês←Dia• Local: Região←Estado←Cidade←Filial

e tendo como medidas a quantidade e o valor comprado

• Supondo um hipercubo quadrimensional (organizado em uma visão 2D: eixo x: Fornecedor←Local; eixo y: Tempo←Produto) no nível mais detalhado de granularidade, defina uma seqüência de operações OLAP para se obter:

a) Qual o total anual fornecido para a região Sul a partir de 2001?b) A partir do resultado obtido no item a, mostre o volume mensal de

fornecimento de liquidificadores Walita e Micro-ondas Sharp e Consul(associar Fornecedor←Produto no mesmo eixo da tabela para fins de visualização) pelos fornecedores de nome F1 e F2

Definição de Medidas

• Dois tipos de medidas podem ser representados em esquemas multidimensionais– medidas primitivas (ou aditivas)

• podem ser simples ou compostas• medidas compostas são hierarquizadas

– medidas derivadas (ou não-aditivas)• são simples• são obtidas através de cálculos aplicados sobre

medidas primitivas e/ou derivadas

21

Medidas Primitivas• Valores mantidos no DW

– armazenados diretamente ou obtidos via operações aritméticas simples

– exemplos

• O armazenamento ou não de todos os componentes de uma medida composta éuma decisão de projeto do DW

Quantidade Vendida

Lucrode Venda

Lucro Bruto (+)

Receita (+)Despesa (-)

Impostos (-)

Medidas Derivadas• Medidas relativas a outras medidas do DW

– são indicadores analíticos relevantes• definem um nível de intensidade de uma

característica

– exemplos• médias, percentuais, ...

Média de quantidade vendida (QV) de liquidificadores em SC nos primeiros 4 meses do ano

MQV = (QV(SC/Liq/Jan) + ... + QV(SC/Liq/Abr) ) / 4

Percentual de participação dos liquidificadores Walita nas médias de vendas em SC nos meses de 2002

PPWalita = (MQV(SC/Liq[Walita]/2002) / MQV(SC/Liq/20 02)) * 100

22

Categorias de Ferramentas OLAP

• ROLAP (Relational OLAP)– ferramentas que acessam BDs relacionais– manipulação de esquemas multidimensionais na

forma de tabelas relacionais

• MOLAP (Multidimensional OLAP)– ferramentas que acessam BDs multidimensionais– manipulação direta de cubos ou hipercubos

• HOLAP (Hybrid OLAP)– ferramentas que permitem acesso tanto a BDs

relacionais como BDs multidimensionais

ROLAP x MOLAP x HOLAP

custo para definição de hipercubosnão há hipercubos

HOLAP

• manipulação de tabelas e hipercubos• possibilidade de combinação de dados relacionais e multidimensionais

durante uma análise

multidimensões já estão predefinidas (acesso + rápido)

custo para montagem de multidimensões a cada análise

indicado para escopos de análise bem definidos

indicado para escopos de análise que não são bem definidos

manipulação via uso direto de operações OLAP em hipercubos

manipulação via SQL estendida para dar suporte a operações OLAP

estrutura multidimensional(EDs especiais para hipercubos -

otimizadas para acesso)

estrutura relacional(tabelas que definem esquemas

estrela ou floco de neve)

MOLAPROLAP

23

Exemplos de Ferramentas OLAP

• ROLAP– Oracle Discover– Informix Metacube

• MOLAP– Hyperion Essbase

• HOLAP– Oracle Express– SQL Server 7.0

BD Multidimensional • Definição e manipulação de hipercubos

– define conjuntos de células com valores para certas medidas em cruzamentos de dimensões

– oferece suporte a realização de operações OLAP, aritméticas, financeiras e estatísticas sobre um esquema multidimensional

– oferece uma interface com o usuário para a visualização de fatos e hierarquias de dimensões e medidas

• Integração com planilhas eletrônicas e possibilidade de exportação de dados em vários formatos (exemplo: XML)

24

BDs Multidimensionais• Para um esquema multidimensional podem

ser definidos vários hipercubos– relacionamento um-para-muitos– exemplo de esquema multidimensional

• fato: Vendas• dimensões: Produto, Cliente, Tempo e Local• medidas: quantidade vendida, valor vendido

– exemplos de hipercubos• Hipercubo 1:

– quantidade vendida por estado/cidade/filial para um certo tipo de produto em períodos trimestrais/mensais/diários

• Hipercubo 2:– valor vendido por classe/sexo de cliente para um certo

tipo/marca/modelo de produto em períodos anuais/mensais

BDs Multidimensionais

• Um BD multidimensional é considerado um subconjunto do DW– aplicado às necessidades analíticas de um DM

DW

BDM1 BDMn. . .

BDETC

p

x

y z

t

w

r

p

w t

y

xt

r

tx

Documents

Sumárioronaldo/ine5342/6-dw.pdf– quantos liquidificadores Walita foram vendidos ... – qual a marca de forno micro-ondas mais vendida para clientes das classes A e B?