Data Warehousing Disciplina: Banco de Dados II Professor: Carlos Eduardo Pires [email protected]

Data WarehousingData Warehousing

Disciplina: Banco de Dados II

Professor: Carlos Eduardo Pires

[email protected]

UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 2

Bibliografia

KIMBALL, R., ROSS, M., THORNTHWAITE, W., MUNDY, J., BECKER, B. The Data Warehouse Lifecycle Toolkit. Wiley, 2nd Edition, 2008.

SILBERSCHATZ, A., KORTH, H., SUDARSHAN, S. Sistema de Banco de Dados. Campus, 5ª Edição, 2006.

INMON, W. H. Building the Data Warehouse. Wiley, 4th Edition, 2005.


Agenda

Introdução (Integração de Dados) Data Warehousing: Conceitos e

Terminologias Processo de Data Warehousing Modelagem Multidimensional Ferramentas OLAP SQL para Data Warehousing Otimização de DW

Abordagens para Integração de Dados


Integração de Dados

Objetivo de um Sistema de Integração de Dados (SID) Fornecer uma interface uniforme para acesso a

múltiplas fontes de dados Permite ao usuário especificar o que ele

deseja e o sistema determina como e onde a informação será conseguida

Estudada no campo da Inteligência Artificial e de Banco de Dados



Problemas da Integração de Dados Fontes são compostas por dados muito

específicos (granularidade) Dados são armazenados em diferentes

esquemas e modelos Dados podem ser não estruturados, semi-

estruturados ou estruturados Fontes de dados têm diferentes linguagens de

consulta



Autonomia das Fontes de Dados Continuam a suportar aplicações locais

Alterações podem ocorrer tanto nos dados quanto nos esquemas

Um SID necessita lidar com as constantes mudanças nas fontes que estão sendo integradas



Principais Abordagens Abordagem Virtual (Mediadores)

Consulta às fontes em tempo de execução Abordagem Materializada

Consulta um repositório com dados materializados Vantagens e desvantagens

Dados atuais X Tempo de resposta


Arquiteturas Clássicas

Arquitetura de Mediadores Abordagem virtual Características

Domínio específico Mapeamentos Apenas consultas Fontes de dados de

diferentes tipos


Arquiteturas Clássicas

Arquitetura de Data Warehouse Abordagem materializada Estratégias de manutenção

Rematerialização da visão integrada

Manutenção incremental

A Arquitetura de Data Warehouse será abordada nesta disciplina

A Arquitetura de Data Warehouse será abordada nesta disciplina

Data Warehouse


Data Warehouse – Definições

“Coleção de dados orientada a assunto, integrada, não-volátil e variante no tempo, utilizada para tomada de decisões”

W. H. Inmon

“Repositório estruturado e corporativo de dados orientados a assunto, variantes no tempo e históricos, usados para recuperação de informações e suporte à decisão. O DW armazena dados atômicos e sumariados”

Oracle Corporation


Data Warehouse – Definições

“Uma cópia de dados transacionais estruturada especificamente para consulta e análise”

R. Kimball


Propriedades de um Data Warehouse

Integrado

Variante noTempoNão-volátil

Orientado aAssunto

DataWarehouse


Orientado a Assunto

Os dados são divididos e armazenados por áreas de negócio

Aplicações OLTP

Aposentadoria

Investimento

Seguro

Empréstimo

Poupança

Data Warehouse

Informações Financeirasdos Clientes


Integrado

Data WarehouseAplicações OLTP

Cliente

Poupança

Contas

Empréstimos

Os dados de um determinado assunto são definidos e armazenados apenas uma vez


Variante no Tempo

Os dados são armazenados como uma série de fotografias, cada uma representando um

período no tempo

Data Warehouse


Não-Volátil

Dados são materializados no DW

Data Warehouse

Leitura

Carga

Operacional

Inserção, Atualização, Remoção e/ou Leitura


Alterando os Dados em um Data Warehouse

Bancos de Dados Operacionais Banco de Data Warehouse

Primeira Carga

Atualizar

Atualizar

AtualizarEliminar ou

Arquivar


Data Mart

Data Warehouse departamental Espelho parcial de um Data Warehouse Oferece melhoria no desempenho

Armazena menos dados Desenvolvimento

Construído e “povoado” mais rapidamente Satisfação imediata do Cliente


Data Mart


Data Mart

DW Corporativo

Data Mart Vendas & Marketing Data Mart Garantia & Suporte

Clientes

Clientes Clientes

1998_Vendas1999_Vendas2000_Vendas1998_Garantia1998_Suporte...

1998_Vendas1999_Vendas2000_Vendas...

1998_Garantia...1998_Suporte...


Data Warehouse x Data Mart

MesesMeses a anosTempo de Implementação

PoucasVáriasFonte de Dados

Um único assuntoVáriosAssuntos

DepartamentoEmpresaEscopo

Data MartData WarehousePropriedade


Movendo Dados de um Data Warehouse para Data Marts

Vantagens Campos compartilhados Fonte comum Processamento distribuído

Mart SuporteMart Suportea clientesa clientes

Mart VendasMart Vendas

DataDataWarehouseWarehouse Mart FinançasMart Finanças

Fonte 1Fonte 1Fonte 1Fonte 1



Desvantagens Tempo mais longo de

desenvolvimento


Movendo Dados de Data Marts para um Data Warehouse

DataDataWarehouseWarehouse

Mart VendasMart Vendas

Mart FinançasMart Finanças

Mart SuporteMart Suportea clientesa clientes




Vantagens Mais simples e rápido Dados específicos de

cada departamento

Desvantagens Duplicação de dados Data Marts incompatíveis

Processo de Data Warehousing


Componentes do Processo de Data Warehousing

Fontes de Dados

Área dePreparação

Área deArmazenamento

Ferramentasde Acesso

ODS

Operacional

Externa

Legado

Repositório de Metadados

Data Marts

Data Warehouse


Um Sistema de Data Warehousing

Dados noAmbiente

OLAP

Dados noAmbiente

OLAP

Data MartsData MartsDataWarehouse

DataWarehouse

Dados deSistemas

Operacionais

Dados deSistemas

Operacionais

Compras

Produção

ContábilDados

da EmpresaOLTP

OLTP

OLTPVendas


Principais Profissionais Envolvidos em um Projeto de Data Warehousing

Analista de Negócios Administrador de Dados Administrador de Banco de Dados Projetista de DW Desenvolvedor de DW Desenvolvedor de Relatórios OLAP

Pessoal da própria empresa!


Coleção de Ferramentas

Obtenção de dados Limpeza, integração, ... Consulta, relatório, análise Mineração de dados Monitoração e administração do DW Monitoração do ETL


Processo de Extração, Transformação e Carga

“Extrai dados necessários das fontes a serem integradas, transforma-os e carrega-os no DW”

Extraction, Transformation and Load (ETL) Limpeza de Dados

Corrige e pré-processa os dados

Operacional Data WarehouseÁrea de Preparação


Transformação de Dados


Limpeza de Dados

Código Candidato Profissão Idade

1 José Roberto Engenheiro 44

1 Maria Silva Médico 67

2 Pedro Alcântara Advogado 1001

3 P. Alcântara Bancário 43

4 Marta Borges Comerciante 22

null Priscila Souza Professor 18

5 Adolfo Farias Docente 27

...

5 erros


Ferramentas de ETL: Oracle Warehouse Builder

Desenho Arquitetural


Diagrama de Fluxo de Dados

Mapeamento da Movimentação dos Dados


Fontes de Dados

Transformações

Destinos

Refinando a Movimentação dos Dados


Modelando um Data Warehouse


Comparando Ambientes de Modelagem

Operacional: OLTPOperacional: OLTP Analítico: Data WarehouseAnalítico: Data Warehouse

Entidades normalizadas Segue terceira forma normal

ou maior Produz um design complexo

de BD Armazena dados no nível

transacional mais baixo Aumenta o nível de JOIN de

tabelas em consultas Estrutura tipicamente

estática

Entidades desnormalizadas Produz um único design de

BD mais facilmente compreensível pelos usuários

Armazena dados Nível de transação Nível de sumário

Diminui o número de JOINs de tabelas em consultas

Estrutura dinâmica


Análise de Negócio

“Conjunto de atividades e técnicas utilizadas para servir como ligação entre partes interessadas no intuito de compreender a estrutura, políticas e operações de uma organização e para recomendar soluções que permitam que a organização alcance suas metas” BABoK (Business Analysis Body of Knowledgement)

Como analisar? Ponha-se no lugar de um Gerente de Vendas

O que ele gostaria de analisar?


Modelagem Multidimensional

Dados operacionais visualizados sob diversos ângulos

Os ângulos são chamados dimensões do negócio Produto Loja Tempo Região

Uma das dimensões é “sempre” o Tempo



Outros Exemplos de Dimensões



Atributos de Dimensão São atributos qualitativos que caracterizam os

ramos do negócio envolvidos na medida de desempenho de determinado fato

Exemplo Dimensão “Produto”

Descrição, embalagem, preço, etc.



Granularidade Define o nível de detalhe das dimensões Influencia o tamanho das dimensões Exemplo

Dimensão “Loja” País Maior Granularidade Região Estado Cidade Menor Granularidade



Hierarquias entre os atributos das dimensões Úteis para a geração de relatórios

Dimensão “Região” Fornecedor Cidade Estado Região

Dimensão “Tempo” Dia Semana Mês Trimestre Ano



Associados a cada dimensão existem fatos: Vendas Compras Sinistro

Fatos São valores quantitativos referentes ao desempenho de

um grupo de dimensões Exemplo

Fato “Vendas” (Loja, Produto e Tempo) Quantidade, lucro, valor, etc.


Processamento OLAP

OLAP (On-line Analytical Processing) Aplicado sobre estruturas multidimensionais O termos “On-line” significa que

os resumos solicitados são obtidos “rapidamente”

Difere substancialmente daquele utilizado por aplicações do nível operacional (OLTP)


Processamento OLAP x OLTP

AnáliseProcessosAtividades

Operacional, Interna, Externa

Operacional, InternaFontes de Dados

Grande para Muito Grande

Pequeno para grandeTamanho

Assunto, tempoAplicaçãoOrganização dos Dados

Snapshots no tempo30 – 60 diasNatureza dos dados

LeituraDMLOperações

Segundos para horasMilisegundos para segundos

Tempo de Resposta

OLAPOLTPPropriedade



Principais vertentes para Modelagem Multidimensional Multidimensional OLAP (MOLAP) Relational OLAP (ROLAP) Hybrid OLAP (HOLAP)

Outras variações Web-based OLAP (WOLAP) Desktop OLAP (DOLAP) Real-Time OLAP (RTOLAP)


MOLAP (Multidimensional OLAP) Baseia-se nos Bancos de Dados Multidimensionais Estrutura utilizada: Cubo Multidimensional Cada aresta representa uma dimensão do negócio As células do cubo são preenchidas com valores

quantitativos (medidas ou fatos)Data Warehouse ou

Data Mart

Data Warehouse ouData Mart

SGBDSGBD

CuboCubo

Dados MOLAP

AgregaçõesMOLAP

Armazena cópia da tabela fato e dimensões

Armazena agregações


MOLAP (Multidimensional OLAP)T

EM

PO

CLI

EN

TE

PRODUTO

Cal

ça

Têni

s

Cas

aco

Mei

a1999

2000

2001

2002 Cliente 1

Cliente 2

VendasVendas

Qual o total de vendasde ‘Casacos' do 'Cliente 1'em '2001'?

Mais informações: http://download.oracle.com/docs/cd/E10530_01/doc/epm.931/html_esb_dbag/frameset.htm?dinconc.htm


ROLAP (Relational OLAP) Tabelas relacionais simulam o cubo multidimensional O esquema proposto concentra numa tabela as medidas ou

fatos do negócio (tabela de fatos) Os componentes das dimensões são armazenados em outras

tabelas (tabelas de dimensão)

Data Warehouse ou Data MartData Warehouse ou Data Mart

Dados ROLAPDados ROLAPDados ROLAPDados ROLAP

Agregações ROLAPAgregações ROLAPAgregações ROLAPAgregações ROLAP

SGBDSGBD

Agregações são armazenadasem tabelas relacionais

Dados-base são mantidos no SGBD-fonte

Estrutura totalmente relacional


HOLAP (Hybrid OLAP)

Data Warehouse ou Data MartData Warehouse ou Data Mart

Dados ROLAPDados ROLAPDados ROLAPDados ROLAP

SGBDSGBD

CuboCubo

AgregaçõesMOLAP

Dados-base mantidosno BD fonte

Agregações calculadas earmazenadas no cubo


MOLAP vs. ROLAP vs. HOLAP

Perspectiva do ClientePerspectiva do ClientePerspectiva do ClientePerspectiva do Cliente MOLAPMOLAPMOLAPMOLAP HOLAPHOLAPHOLAPHOLAP ROLAPROLAPROLAPROLAP

ArmazenamentoArmazenamentoArmazenamentoArmazenamento

Dados de baseDados de base

AgregaçõesAgregações

Desempenho de consultaDesempenho de consulta

Consumo em discoConsumo em disco

Manutenção do cuboManutenção do cubo

MOLAPMOLAPMOLAPMOLAP

CuboCubo

CuboCubo

ImediatoImediato

AltoAlto

AltoAlto

HOLAPHOLAPHOLAPHOLAP

TabelaRelacional

TabelaRelacional

CuboCubo

Mais rápidoMais rápido

MédioMédio

MédioMédio

ROLAPROLAPROLAPROLAP

TabelaRelacional

TabelaRelacional

TabelaRelacional

TabelaRelacional

RápidoRápido

BaixoBaixo

BaixoBaixo


Modelo Estrela (“Star Schema”)Dimensão ProdutoProduto_idDescrição,...

Dimensão TempoTempo_idDiaMêsAno,...

Fatos “Vendas”Produto_idLoja_idTempo_idCliente_idValor_vendasTotal_vendas,...

Dimensão ClienteCliente_idNome,...

Dimensão LojaLoja_idCidade,...

Tabela de Fatos Dimensões

Desnormalizadas


Modelo Estrela (“Star Schema”)

GeográficaGeográfica ProdutoProduto TempoTempo Und.Und. $$

Tabelas deDimensão

Tabelas deDimensão

GeográficaGeográficaGeográficaGeográfica

ProdutoProdutoProdutoProduto

TempoTempoTempoTempo

Tabela-FatoMedidasMedidas

FatosFatosFatosFatos

DimensãoDimensãoDimensãoDimensão



Tabela FatoDimension TableDim_TempoDim_TempoDim_TempoDim_Tempo

Chave_TempoChave_Tempo

Data...

Data...

Fato_VendasFato_VendasChave_TempoChave_FuncionarioChave_ProdutoChave_ClienteChave_Entregador

Chave_TempoChave_FuncionarioChave_ProdutoChave_ClienteChave_Entregador

Qtd-ProdValor-Total…...

Qtd-ProdValor-Total…...

Dim_FuncionarioDim_FuncionarioDim_FuncionarioDim_FuncionarioChave_FuncionarioChave_Funcionario

Codigo_Funcionario...

Codigo_Funcionario...

Dim_ProdutoDim_ProdutoDim_ProdutoDim_ProdutoChave_ProdutoChave_ProdutoCodigo_Produto...

Codigo_Produto...

Dim_ClienteDim_ClienteDim_ClienteDim_ClienteChave_ClienteChave_Cliente

Codigo_Cliente...

Codigo_Cliente...

Dim_EntregadorDim_EntregadorDim_EntregadorDim_EntregadorChave_EntreegadorChave_Entreegador

Codigo_Entregador...

Codigo_Entregador...



saleorderId

datecustIdprodIdstoreId

qtyamt

customercustIdname

addresscity

productprodIdnameprice

storestoreId

city



customer custId name address city53 joe 10 main sfo81 fred 12 main sfo

111 sally 80 willow la

product prodId name pricep1 bolt 10p2 nut 5

store storeId cityc1 nycc2 sfoc3 la

sale oderId date custId prodId storeId qty amto100 1/7/97 53 p1 c1 1 12o102 2/7/97 53 p2 c1 2 11105 3/8/97 111 p1 c3 5 50


Exercício de Sala

A partir do Modelo Conceitual do Projeto da Disciplina, tente montar um esquema estrela

Mostrar Fato, Dimensões e Relacionamentos, além dos atributos


Modelo Flocos de Neve (“Snowflakes”)

Tabelas-Dimensão SecundáriasTabelas-Dimensão Secundárias

Fato_VendasFato_Vendas



RequiredDate...

RequiredDate...

MarcaProdutoMarcaProdutoMarcaProdutoMarcaProdutoCodigoMarcaCodigoMarca

CodigoCategoriaCodigoCategoria

CategoriaCategoriaCategoriaCategoriaCodigoCategoriaCodigoCategoria

NomeNome

Dim_ProdutoDim_ProdutoDim_ProdutoDim_ProdutoChave_ProdutoChave_Produto

NomeNome

TamanhoTamanho

CodigoMarcaCodigoMarca

Tabela-Dimensão Principal



LOJA

CLIENTE

PRODUTO

VendasCusto

TEMPO

CIDADE

ESTADO

SUBCATEGORIA

CATEGORIA

Dimensões Normalizadas



store storeId cityId tId mgrs5 sfo t1 joes7 sfo t2 freds9 la t1 nancy

city cityId pop regIdsfo 1M northla 5M south

region regId namenorth cold regionsouth warm region

sType tId size locationt1 small downtownt2 large suburbs

storesType

city region

snowflake schema constellations

Hierarquia entre Dimensões


Escolhendo uma Modelagem Multidimensional

Star SchemaStar SchemaStar SchemaStar Schema SnowflakeSnowflakeSnowflakeSnowflake

ClarezaClareza + fácil+ fácil + difícil+ difícil

Número de tabelasNúmero de tabelas << >>

Complexidade de consultasComplexidade de consultas + simples+ simples + complexo+ complexo

Desempenho de consultaDesempenho de consulta + rápido+ rápido + lento+ lento


Exercício de Sala

Montar um esquema snowflakes referente ao fato Vendas

Responda a seguinte consulta no esquema snowflakes: Quantidade de vendas de carros da marca W,

feitas ao cliente X, no ano de Z


Escolhendo a Granularidade

Determinar requisitos dos dados Escolher o nível mais baixo de detalhe

Requer espaço em disco Envolve maior tempo de processamento Provê capacidade detalhada de análise de dados

Adaptar medidas à granularidade estabelecida

Considerações de projeto Usar medidas aditivas e numéricas


Definindo Características de Dimensões Aplicar características às tabelas de dimensão

Definir PRIMARY KEY Incluir colunas altamente correlacionadas e descritivas

Projetando para Usabilidade e Extensibilidade Minimizar ou evitar uso de códigos e abreviações Criar colunas úteis para níveis de agregação Evitar valores nulos ou em branco Tratar os atributos que mudam ao longo do tempo


Identificando Hierarquias de Dimensões

Hierarquia ConsolidadaHierarquia Consolidada

Local da Loja

ContinenteContinenteContinenteContinentePaísPaísPaísPaís

RegiãoRegiãoRegiãoRegiãoCidadeCidadeCidadeCidade

LojaLojaLojaLoja

Hierarquia em SeparadoHierarquia em Separado

Local da LojaLocal da LojaLocal da LojaLocal da Loja

ContinenteContinente

ContinenteContinenteContinenteContinente

PaísPaís

PaísPaísPaísPaís

RegiãoRegião

RegiãoRegiãoRegiãoRegião

CidadeCidade

CidadeCidadeCidadeCidade

LojaLoja

LojaLojaLojaLoja

0101


Dicas Importantes

Levar a PK original da tabela fonte para a dimensão Carga incremental

Durante a carga, carregar as dimensões primeiramente

Dimensões são bem menores que os fatos Vale a pena desnormalizar


Tempo

ProdutoLocal

DimensãoDimensão

Q4Q1 Q2 Q3

Uvas

Maçãs

Melões

Cerejas

Pêras

Atlanta

Denver

DetroitMembroMembro

CélulaCélula

TempoTempo InícioInício

Trimestre 1Trimestre 1 1 deJulho1 deJulho

FimFim

30 de Setembro30 de Setembro

Trimestre 2Trimestre 2 1 de Outubro1 de Outubro 31 de Dezembro31 de Dezembro

Trimestre 3Trimestre 3 1 de Janeiro1 de Janeiro 31 de Março31 de Março

Trimestre 4Trimestre 4 1 de Abril1 de Abril 30 de Junho30 de JunhoPropriedadesPropriedades

Dia 1Dia 1Dia 1Dia 1 Dia 2Dia 2Dia 2Dia 2 ............

Jan.Jan.Jan.Jan. Fev..Fev..Fev..Fev.. ............

1998199819981998 1999199919991999 ............

NíveisNíveis

Terminologia


Q4

Tempo

Q1 Q2 Q3

ProdutoUvas

Maçãs

Melões

Cerejas

Peras

LocalAtlanta

Denver

Detroit

SalesSales

ProdutoUvas

Maçãs

Melões

Cerejas

Peras

ProdutoUvas

Maçãs

Melões

Cerejas

Pêras

LocalAtlanta

Denver

Detroit

SalesSales

Operações no Cubo


Dimensões Compartilhadas

Fatos 1

Dimensão 1

Dimensão 2

Dimensão 3

Dimensão 4

Fatos 2

Dimensão 5

Dimensão 6


Fatos & Fatos?

Fatos 2

Dimensão 1

Dimensão 2

Fatos 1

Dimensão 5

Dimensão 6

UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 74Data Warehousing - Prof. Carlos Eduardo Santos Pires 74

Tamanho de um DW

Fato: Vendas


Tamanho de um DW

É medido pelo tamanho da Tabela de Fatos Em geral, o tamanho das dimensões é

desprezado Dimensão Tempo: 2 anos x 365 dias = 730

dias (linhas) Dimensão Loja: 300 lojas Dimensão Produto: 30.000 produtos


Tamanho de um DW

Dimensão Promoção: cada item vendido está associado a uma única promoção

Número de registros da tabela de fatos (média de 3.000 itens (produtos) vendidos ao dia em cada loja):

730 (dias) x 300 (lojas) x 3000 (produtos/dia) x 1 (promoção)= 657 milhões de registros


Tamanho de um DW

Número de campos chaves = 4 Número de campos fatos (medidas) = 4 Total de campos = 8 (de 4 bytes, cada um) Tamanho da Tabela de Fatos

657.000.000 x 8 x 4 = 21GB

Ferramentas OLAP


Ferramentas OLAP

DW

BDFerramentas deApresentação

Ferramentas OLAP

Ferramentas de Mineração de Dados

Ferramentas deApresentação

CAMADA DE “FRONT-END”

Executivo


Ferramentas OLAP

Admitem análise interativa de informações resumidas

Fornecem ao usuário a visualização dos dados sob diferentes ângulos Comportam as necessidades da atividade de análise

Características de um Relatório OLAP Séries temporais Comparações Ajuda à identificação de anomalias ou exceções

Operações: Drill Up, Drill Down, Slicing/Dicing


Exemplo de Relatório OLAP

Produto Região Vendas noMês

Comparaçãocom o Mês

AnteriorPasta Colgate Sul 110 **12%Pasta Colgate Sudeste 179 -3%Pasta Colgate Nordeste 55 5%

Total 344 **6%


Exemplo de Relatório OLAP

Produto Região Tamanho Vendas noMês

Comp. Como Mês

AnteriorColgate Sul A 34 **10%Colgate Sul B 36 **13%Colgate Sul C 40 **11%Colgate Total 110 **12%Colgate Sudeste A 63 -2.8%Colgate Sudeste B 60 -3.1%Colgate Sudeste C 56 -2.9%Colgate Total 179 -3%Colgate Nordeste A 19 5%Colgate Nordeste B 17 4%Colgate Nordeste C 19 6%Colgate Total 55 5%

Total 344 6%

UFCG/CEEI/DSC - Banco de Dados II - Prof. Carlos Eduardo Pires 83Data Warehousing - Prof. Carlos Eduardo Santos Pires

Exemplo de Relatório OLAPProduto Região Equipe de

VendasVendas no

MêsComp.Com o

MêsAnterior

Colgate Sul P. Alegre 52 **21%Colgate Sul Curitiba 28 5%Colgate Sul Florianóp. 30 6%Colgate 110 **12%Colgate Sudeste São Paulo 93 4%Colgate Sudeste Rio 75 5%Colgate Sudeste Belo Horiz. 11 -15%Colgate 179 -3%Colgate Nordeste Salvador 21 5%Colgate Nordeste Fortaleza 18 4%Colgate Nordeste Recife 16 6%Colgate 55 5%

Total 344 6%


Ferramentas OLAP: Oracle Discoverer

TABULAÇÃO CRUZADA


Tabela Dinâmica no Excel

É uma tabela interativa que resume uma grande quantidade de dados rapidamente, ou os combina de tabelas diferentes

É possível girar suas linhas e colunas para ver resumos diferentes dos dados de origem, filtrar os dados exibindo páginas diferentes ou exibir os detalhes das áreas de interesse


Tabela Dinâmica no Excel Exemplo: AULAPED.xls

(http://www.virtual.epm.br/material/tis/curr-med/tab_dinamica/tabdin.html)

Selecione a área compreendida entre as células A1 e H1612 Menu Dados "Relatório da Tabela Dinâmica"

Onde estão os dados para análise? BD ou Lista do Excel Intervalos de dados a serem usados? $A$1:$H$1612

Layout

SQL para OLAP


Extensões SQL para Ferramentas OLAP

Comando Merge A partir de uma tabela origem, insere ou atualiza linhas em

uma tabela destino Operador ROLLUP

Produz um conjunto de resultados que contém as linhas agrupadas e os valores de subtotais

Operador CUBE Produz um conjunto de resultados que contém as linhas de

ROLLUP e as linhas de tabulação cruzada Operador Grouping Sets

Extensão da cláusula GROUP BY Possibilita explicitar os agrupamentos desejados


Merge

Sintaxe: MERGE <hint> INTO <table_name>

USING <table, view or query>ON (<condition>)WHEN MATCHED THEN <update_clause>WHEN NOT MATCHED THEN <insert_clause>[LOG ERRORS <log_errors_clause> <reject limit <integer | unlimited>];


Merge – Exemplo 01

ALUNO ALUNO2

INSERT XOR UPDATE

Origem Destino



CREATE TABLE aluno ( codigo NUMBER PRIMARY KEY, nome VARCHAR2(10), curso VARCHAR2(10), telefone VARCHAR2(20));

INSERT INTO aluno VALUES (1,'JOSE','CC','88847654'); INSERT INTO aluno VALUES (2,'MARIA','EE','89887112'); INSERT INTO aluno VALUES (3,'PAULO','EM','99817638');

CREATE TABLE aluno2 ( id NUMBER PRIMARY KEY, nome VARCHAR(10), curso VARCHAR2(10), telefone VARCHAR2(20), codigo NUMBER);


Merge – Exemplo 01CREATE SEQUENCE seq_aluno;

CREATE OR REPLACE PROCEDURE atualiza ISBEGIN

MERGE INTO aluno2 a2USING aluno aON (a.codigo = a2.codigo)WHEN MATCHED THEN UPDATE SET

a2.curso = a.curso,a2.telefone = a.telefone

WHEN NOT MATCHED THEN INSERT (a2.id, a2.nome, a2.curso, a2.telefone, a2.codigo)

VALUES (seq_aluno.nextval, a.nome, a.curso, a.telefone, a.codigo);COMMIT;

END;

EXECUTE atualiza;

SELECT * FROM aluno2;



INSERT INTO aluno VALUES (4,'ZILDA','CC','81318630');

COMMIT;

EXECUTE atualiza

SELECT * FROM aluno2;

UPDATE aluno SET curso = 'CC' WHERE codigo = 2;


Merge – Exemplo 02 CREATE TABLE employee ( employee_id NUMBER(5), first_name VARCHAR2(20), last_name VARCHAR2(20), dept_no NUMBER(2), salary NUMBER(10));

INSERT INTO employee VALUES (1,'Dan', 'Morgan',10,100000); INSERT INTO employee VALUES (2,'Helen','Lofstr',20,100000); INSERT INTO employee VALUES (3,'Akiko','Toyota',20,50000); INSERT INTO employee VALUES (4,'Jackie','Stough',20,40000); INSERT INTO employee VALUES (5,'Richard','Foote',20,70000); INSERT INTO employee VALUES (6,'Joe', 'Johnson',20,30000); INSERT INTO employee VALUES (7,'Clark', 'Urling',20,90000);



CREATE TABLE bonuses ( employee_id NUMBER, bonus NUMBER DEFAULT 100);

INSERT INTO bonuses (employee_id) VALUES (1); INSERT INTO bonuses (employee_id) VALUES (2); INSERT INTO bonuses (employee_id) VALUES (4); INSERT INTO bonuses (employee_id) VALUES (6); INSERT INTO bonuses (employee_id) VALUES (7); COMMIT;

SELECT * FROM employee; SELECT * FROM bonuses;



MERGE INTO bonuses b USING ( SELECT employee_id, salary, dept_no FROM employee WHERE dept_no = 20) e ON (b.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET b.bonus = e.salary * 0.1 WHEN NOT MATCHED THEN INSERT (b.employee_id, b.bonus) VALUES (e.employee_id, e.salary * 0.05);

subconjunto de employee


Cubo 2D

sale prodId storeId amtp1 c1 12p2 c1 11p1 c3 50p2 c2 8

c1 c2 c3p1 12 50p2 11 8

Fact table: Multi-dimensional cube:

dimensions = 2

GROUP BY storeId, prodId


Cubo 3D

sale prodId storeId date amtp1 c1 1 12p2 c1 1 11p1 c3 1 50p2 c2 1 8p1 c1 2 44p1 c2 2 4

day 2c1 c2 c3

p1 44 4p2 c1 c2 c3

p1 12 50p2 11 8

day 1

dimensions = 3

Multi-dimensional cube:Fact table:

GROUP BY storeId, prodId, date


Agregação


• Soma de quantidade (amt) em um dia (dia 1)• Em SQL: SELECT SUM(amt)

FROM saleWHERE date = 1;

81


Agregação


• Soma quantidade (amt) por dia• Em SQL: SELECT date, SUM(amt)

FROM saleGROUP BY date;

ans date sum1 812 48


Agregação


• Soma quantidade (amt) por dia e produto• Em SQL: SELECT date, prodId, SUM(amt)

FROM saleGROUP BY date, prodId

sale prodId date amtp1 1 62p2 1 19p1 2 48

drill-down

rollup

Desconsidera storeId


Agregação

Operadores SUM, COUNT, MAX, MIN, AVG

Cláusula “HAVING” Eliminar determinados grupos

Uso da hierarquia entre dimensões Média de vendas por região (região está dentro

de loja (“store”)) Maior venda por mês (mês está dentro de ano)


Agregação no Cubo

day 2c1 c2 c3

p1 44 4p2 c1 c2 c3

p1 12 50p2 11 8

day 1

c1 c2 c3p1 56 4 50p2 11 8

c1 c2 c3sum 67 12 50

sump1 110p2 19

129

. . .

drill-down

rollup

GROUP BY storeId, prodId, date

GROUP BY storeId, prodId

GROUP BY storeId

GROUP BY prodId


Operadores do Cubo

day 2c1 c2 c3

p1 44 4p2 c1 c2 c3

p1 12 50p2 11 8

day 1

c1 c2 c3p1 56 4 50p2 11 8

c1 c2 c3sum 67 12 50

sump1 110p2 19

129

. . .

sale(c1,*,*)

sale(*,*,*)sale(c2,p2,*)


Operadores do Cubo

c1 c2 c3 *p1 56 4 50 110p2 11 8 19* 67 12 50 129day 2 c1 c2 c3 *

p1 44 4 48p2* 44 4 48

c1 c2 c3 *p1 12 50 62p2 11 8 19* 23 8 50 81

day 1

*

sale(*,p2,*)


Extensões SQL para Ferramentas OLAP Cláusula GROUP BY tradicional

SELECT department_id, job_id, AVG(salary)

FROM employees

GROUP BY department_id, job_id


Operador ROLLUP

SELECT department_id, job_id, SUM(salary)FROM employeesWHERE department_id < 60GROUP BY ROLLUP(department_id, job_id);

12

3

Total de Combinações: n + 1


Operador CUBE

SELECT department_id, job_id, SUM(salary)FROM employeesWHERE department_id < 60GROUP BY CUBE (department_id, job_id) ;

12

3

4

Total de Combinações: 2n


Grouping Sets

SELECT department_id, job_id, manager_id,avg(salary)FROM employeesGROUP BY GROUPING SETS ((department_id,job_id), (job_id,manager_id),());

…

1

2

34000


Grouping Sets

SELECT department_id, job_id, manager_id,avg(salary) FROM employees WHERE salary > 12000 GROUP BY GROUPING SETS ((department_id,job_id), (job_id,manager_id),())

DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY) ------------- ---------- ---------- ----------- 20 MK_MAN 13000 80 SA_MAN 13750 90 AD_VP 17000 90 AD_PRES 24000 AD_VP 100 17000 MK_MAN 100 13000 SA_MAN 100 13750 AD_PRES 24000 16416,6667

Otimizações


Visão (Virtual)

Crie uma visão, EMPVU80, que contenha detalhes dos funcionários do departamento 80

CREATE VIEW empvu80 AS SELECT employee_id, last_name, salary FROM employees WHERE department_id = 80;View created.View created.


Visões (Virtual)

USER_VIEWSUSER_VIEWS EMPVU80EMPVU80SELECT employee_id, last_name, salaryFROM employeesWHERE department_id=80;

Aplicação

SELECT *FROM empvu80;

EMPLOYEES

Servidor de Servidor de Banco de DadosBanco de Dados


Visões Materializadas

Armazenam fisicamente os resultados de uma consulta

Podem ser atualizadas periodicamente Ajudam a aumentar a velocidade de resposta das

consultas que envolvem funções de agregação ou junções entre tabelas

Usadas em soluções de Data Warehousing e Replicação de Dados

No SGBD Oracle, eram chamadas de snapshots



CREATE MATERIALIZED VIEW relatorio_clienteREFRESH COMPLETEENABLE QUERY REWRITE ASSELECT c.nome_cliente, SUM(f.valor) AS totalFROM fatos_vendas f, dim_cliente cWHERE f.cliente_id = c.cliente_idGROUP BY c.nome_cliente;


Visões Materializadas – Exemplo 1

INSERT INTO products VALUES (1,'NESCAU',null); INSERT INTO products VALUES (2,'AGUA MINERAL',null); INSERT INTO products VALUES (3,'DESODORANTE',null); INSERT INTO sales VALUES (101,1,20,450); INSERT INTO sales VALUES (100,1,10,225); INSERT INTO sales VALUES (100,2,5,30); INSERT INTO sales VALUES (100,3,30,160); INSERT INTO sales VALUES (102,2,2,10);

COMMIT;

CREATE TABLE products(id_product NUMBER PRIMARY KEY,nm_product VARCHAR2(40),ds_product VARCHAR2(200));

CREATE TABLE sales(id_sales NUMBER,id_product NUMBER,amount NUMBER,value NUMBER, PRIMARY KEY (id_sales, id_product));



CREATE MATERIALIZED VIEW product_sales_mv BUILD IMMEDIATE REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS SELECT p.nm_product, SUM(s.value) FROM products p, sales s WHERE p.id_product = s.id_product GROUP BY nm_product;

SELECT * FROM product_sales_mv;

NM_PRODUCT SUM(S.VALUE) ---------------------------- -------------------- AGUA MINERAL 40 DESODORANTE 160 NESCAU 675


Visões Materializadas – Exemplo 1 INSERT INTO sales VALUES (103,2,3,14);


NM_PRODUCT SUM(S.VALUE) ------------------------------ -------------------- AGUA MINERAL 40 DESODORANTE 160 NESCAU 675

COMMIT;


NM_PRODUCT SUM(S.VALUE) ------------------------------- --------------------- AGUA MINERAL 54 DESODORANTE 160 NESCAU 675

Mesmo resultado anterior

Resultado atualizado após o COMMIT

Provoca a rematerialização da visão



CONN hr/hr

@d:\oracle\ora92\rdbms\admin\utlxplan.sql SET autotrace traceonly

CREATE MATERIALIZED VIEW dept_emp REFRESH COMPLETE ENABLE QUERY REWRITE AS SELECT d.department_name, SUM(e.salary) AS total FROM departments d, employees e WHERE d.department_id = e.department_id GROUP BY d.department_name;



ALTER SESSION set query_rewrite_enabled = true;

SELECT d.department_name, SUM(e.salary) AS total FROM departments d, employees e WHERE d.department_id = e.department_id GROUP BY d.department_name;

11 linhas selecionadas.

Plano de Execução ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2

Card=327 Bytes=9810) 1 0 TABLE ACCESS (FULL) OF 'DEPT_EMP' (Cost=2

Card=327 Bytes=9810)



Vantagens Útil para sumarização, cálculos pré-computados e

replicação de dados Acesso mais rápido para consultas envolvendo

junções complexas Transparente para usuários finais

Podem ser adicionadas ou eliminadas sem invalidar código SQL

Desvantagens Custos de desempenho (rematerialização) Custos de armazenamento


Particionamento de Tabelas

Tabelas Particionadas Decomposição de grandes tabelas em

pedaços menores chamados partições Cada partição possui seu próprio nome e

pode, opcionalmente, ter características de armazenamento próprias

Benefícios Gerenciamento, o desempenho de

consultas e a disponibilidade dos dados


Particionamento de Tabelas Do ponto de vista da aplicação, uma tabela

particionada é idêntica a uma tabela não-particionada

Não são necessárias modificações ao acessar uma tabela particionada utilizando comandos DML

Chave de particionamento Conjunto de colunas que determina em qual

partição estará cada linha



Tipos de Particionamento Faixa Lista Hash



CREATE TABLE employee_r (empno NUMBER(10) PRIMARY KEY,name VARCHAR2(40),deptno NUMBER(2))PARTITION BY RANGE (deptno)(PARTITION P1 VALUES LESS THAN (11) TABLESPACE p1_ts,PARTITION P2 VALUES LESS THAN (21) TABLESPACE p2_ts,PARTITION P3 VALUES LESS THAN (31) TABLESPACE p3_ts,PARTITION P4 VALUES LESS THAN (MAXVALUE) TABLESPACE p4_ts);

Particionamento por Faixa de Valores


Exemplo CREATE TABLE exemplo ( id NUMBER, name VARCHAR2(30)) PARTITION BY RANGE (id) (PARTITION P1 VALUES LESS THAN (250000)

TABLESPACE users, PARTITION P2 VALUES LESS THAN (500000)

TABLESPACE users, PARTITION P3 VALUES LESS THAN (750000)

TABLESPACE users, PARTITION P4 VALUES LESS THAN (MAXVALUE)

TABLESPACE users);


Exemplo

CREATE OR REPLACE PROCEDURE carrega IS BEGIN FOR i in 1..100000 LOOP INSERT INTO exemplo VALUES (i,'AAAA'||i); END LOOP; COMMIT; END; /

EXEC carrega

SELECT * FROM exemplo partition(p1) WHERE id = 25000;


Exemplo

ANALYZE TABLE exemplo COMPUTE STATISTICS;

SELECT table_name, partition_name, high_value, num_rows

FROM user_tab_partitions WHERE table_name = 'EXEMPLO';

TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS -------------------- --------------------------- -------------------- ---------- EXEMPLO P1 250000 499998 EXEMPLO P2 500000 500000 EXEMPLO P3 750000 500000 EXEMPLO P4 MAXVALUE 500002



CREATE TABLE cliente (codigo NUMBER(5) NOT NULL,nome VARCHAR2(100),estado CHAR(2))TABLESPACE usersPARTITION BY LIST (estado)(PARTITION p_regional_sudeste VALUES ('SP', 'RJ', 'MG', 'ES')TABLESPACE users,PARTITION p_regional_nordeste VALUES ('BA', 'PE', 'PB', 'RN')

TABLESPACE users);

Particionamento por Lista de Valores


Exemplo BEGIN FOR i in 1..1000 LOOP IF MOD(i,2) = 0 THEN INSERT INTO cliente VALUES (i,'AAAAAA','SP'); ELSE INSERT INTO cliente VALUES (i,'BBBBBB','PB'); END IF; END LOOP; COMMIT; END;

SELECT table_name, partition_name, high_value, num_rows FROM user_tab_partitions WHERE table_name = 'CLIENTE'

TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS --------------- ------------------------- ------------------------- ---------- CLIENTE P_REGIONAL_SUDESTE 'SP', 'RJ', 'MG', 'ES' 500 CLIENTE P_REGIONAL_NORDESTE 'BA', 'PE', 'PB', 'RN' 500

INSERT INTO cliente VALUES (1001, 'CCCCCC', 'RS'); ERRO na linha 1: ORA-14400: chave de partição inserida não está mapeada para partição alguma



CREATE TABLE pessoa (codigo NUMBER(7) NOT NULL,nome VARCHAR2(150) NOT NULL,nascimento DATE,email VARCHAR2(30))PARTITION BY HASH (codigo)PARTITIONS 2STORE IN (users, example);

Particionamento por Hash

Usa um algoritmo de dados para misturar os dados entre as partições, balanceando o volume de dados


Exemplo BEGIN FOR i IN 1..10000 LOOP INSERT INTO pessoa VALUES (i, 'NONO'||i, SYSDATE+i, i||'@empresa.com'); END LOOP; COMMIT; END; /

ANALYZE TABLE pessoa COMPUTE STATISTICS;

SELECT table_name, partition_name, high_value, num_rows FROM user_tab_partitions WHERE table_name = 'PESSOA';

TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS ---------- -------------- ------------ ---------- PESSOA SYS_P21 4992 PESSOA SYS_P22 5008



Vantagens Acelera o desempenho

Baixa o tempo de consulta Aumenta a disponibilidade

Acesso 24x7 a informações críticas Melhora a capacidade de gerenciamento

Gerencia porções menores de dados

Desvantagens Consultas devem usar os mesmos critérios do

particionamento Partições podem ter mais dados do que outras


Indexação

Índices de Bitmap

2023

1819

202122

232526

id name age1 joe 202 fred 203 sally 214 nancy 205 tom 206 pat 257 dave 218 jeff 26

. .

.

ageindex

bitmaps

datarecords

110110000

0010001011


Indexação

Índices de Bitmap Considere a seguinte consulta:

Recupere as pessoas com idade = 20 e nome = “fred”

Mapa de bits para idade = 20: 1101100000 Mapa de bits para nome = “fred”: 0100000001 A resposta é a interseção: 010000000000


Indexação

Criando um Índice de Bitmap

Deve ser usado Em tabelas com milhões de linhas e as colunas

têm poucos valores distintos Quando tem pouca atualização de dados

CREATE BITMAP INDEX emp_est_civil_idx

ON empregado(estado_civil)

TABLESPACE indx;

CREATE BITMAP INDEX emp_est_civil_idx

ON empregado(estado_civil)

TABLESPACE indx;


Índices de Bitmap – Exemplo

CREATE TABLE paciente ( codigo NUMBER PRIMARY KEY, nome VARCHAR2(40), estcivil VARCHAR2(10), datanasc DATE, sexo CHAR(1));

BEGIN FOR i in 1..10000 LOOP IF MOD(i,2) = 0 THEN INSERT INTO paciente VALUES (i,'AAAAAA'||i, 'CASADO',SYSDATE-i,'F'); ELSE INSERT INTO paciente VALUES (i,'OOOOO'||i, 'SOLTEIRO',SYSDATE-

i,'M'); END IF; END LOOP; COMMIT; END;



SELECT sexo, COUNT(*) FROM paciente GROUP BY sexo;

SEXO COUNT(*) -------- ---------- F 5000 M 5000

CREATE BITMAP INDEX paciente_sexo_idx ON paciente (sexo); CREATE BITMAP INDEX paciente_estcivil_idx ON paciente (estcivil);



SELECT /*+ index(paciente paciente_sexo_idx, paciente paciente_estcivil_idx) */ COUNT(*)

FROM paciente WHERE sexo = 'F' AND estcivil = 'CASADO';

Plano de Execução ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=10) 1 0 SORT (AGGREGATE) 2 1 BITMAP CONVERSION (COUNT) 3 2 BITMAP AND 4 3 BITMAP INDEX (SINGLE VALUE) OF 'PACIENTE_ESTCIVIL_IDX' 5 3 BITMAP INDEX (SINGLE VALUE) OF 'PACIENTE_SEXO_IDX'

Documents

Data Warehousing Disciplina: Banco de Dados II Professor: Carlos Eduardo Pires [email protected]