CIn/UFPE Projeto Conceitual de BD - Prof. Robson Fidalgo 1

(1)

(2)

Projeto Conceitual de BD

Data Warehouse e OLAP

Principais conceitos

Por:

Robson do Nascimento Fidalgo

(3)

Sistemas de Suporte à Decisão

• Sistemas de Suporte a Decisão (SSD)

Permitem armazenar e analisar grandes volumes de dados

para extrair informações que auxiliam a compreensão do

comportamento dos dados

• _{Armazenar DW}

• _{Analisar Relatórios, OLAP, Mineração e SIG}

São sistemas computacionais que integram dados oriundos

de diversas fontes, devem ser de uso amigável e com

grande poder analítico

(4)

Suporte à Decisão X Operacional

• Diferem de sistemas de suporte operacional, pois:

Operacional → orientado por transação

• Ex: o produto vendido, o artigo publicado, o imposto predial pago

Decisão → orientado por assunto

• Ex: Vendas, Publicações de Artigos, Gerenciamento Urbano

• Dividir os dados em dois ambientes ortogonais

Operacional (normalmente já existe)

Estratégico (construir um DW)

(5)

(6)

Data Warehouse

• Data Warehouse (DW)

Segundo Inomn, o DW é uma base de dados para facilitar

a execução de consultas de apoio à decisão, onde suas

principais características são :

• Orientada ao assunto: pois o objetivo é tomar decisões sobre o

"assunto” - tema dos dados.

• Perfeitamente integrada: pois deve consolidar dados de

diferentes origens

• Variante no tempo: pois deve-se manter um histórico dos dados,

permitindo comparações ao longo do tempo

• Não volátil: pois as informações já presentes no banco são

(7)

Data Warehouse & Data Mart

• Data Warehouse (DW)

BD otimizado para processamento de consulta complexas (Modelo

Estrela ou Flocos de Neve)

Objetivo: fornecer uma "imagem global da realidade do negócio".

• Data Mart (DM)

O que é ?

• É um

DW departamental

(específico a um assunto/negócio)

• Subconjunto do DW que satisfaz os requisitos de uma atividade de negócio

Benefício ?

• Tempo/dificuldade de implantação minimizado

Restrição ?

(8)

DW & Data Warehousing

• Data Warehouse (DW) = Dados armazenados.

• Data Warehousing =

Processo de construção do DW.

Data Warehousing é uma estratégia para armazenar e

manipular dados estratégicos, separadamente dos

operacionais, de forma a subsidiar a tomada de decisões

Data Warehousing

→

DW

Estrela

ou

F. Neve

→

BD

Op.

_BD

Op.

BD

Op.

BD

Op.

→

DM DM

DM

OLAP

Data Mining

Relatórios

Metadados produzidos em todas as etapas

E

T

L

Extract

Transform

Load

→

_Operational

OD

S

Data

Store

(9)

ETL e ODS

• Ferramentas de ETL

Responsáveis pela conversão dos dados do ambiente

operacional para o de suporte à decisão

Realizam Acesso, Extração, Transformação, Validação e Carga

dos dados

• Operational Datastore (ODS)

Repositório de dados intermediário

Benefícios

• Otimiza a criação do DW

• Possibilita a realização de consultas relacionais sobre dados

históricos

(10)

Arquiteturas de Data Warehousing

• Data Warehousing (Top-Down).

Visão do DW corporativo

• Grande abrangência

• Complexo/Custoso/Demorado

• Alta probabilidade de insucesso

Só pode ser avaliado quando terminado.

DW

→

DM

Data Warehousing Top

Data Warehousing Top--Down

Down

→

BD

Op.

_BD

Op.

BD

Op.

BD

Op.

→

OLAP

Data Mining

Relatórios

Metadados produzidos em todas as etapas

E

T

L

Extract

Transform

Load

→

_Operational

OD

S

Data

Store

(11)

Arquiteturas de Data Warehousing

• Data Warehousing (Botton-Up).

Visão do DW Departamental

• Foco específico nos aspectos mais críticos

• -Complexo/-Custoso/-Demorado

• Alta probabilidade de ilhas de dados

Pode acontecer dos DM não se integrarem.

Data Warehousing Botton

Data Warehousing Botton--Up

Up

→

BD

Op.

_BD

Op.

BD

Op.

BD

Op.

→

OLAP

Data Mining

Relatórios

E

T

L

Extract

Transform

Load

→

_Operational

OD

S

Data

Store

→

DM

DW

(12)

Arquiteturas de Data Warehousing

• Data Warehousing (Abordagem corrente).

Visão do DW Incremental

• Planejar Top-Down

• Desenvolver Botton-Up

• ODS Histórico

DW

Data Warehousing Botton

Data Warehousing Botton--Up

Up

→

BD

Op.

_BD

Op.

BD

Op.

BD

Op.

→

OLAP

Data Mining

Relatórios

Metadados produzidos em todas as etapas

E

T

L

Extract

Transform

Load

→

_Operational

OD

S

Data

Store

→

DM

(13)

• Modelagem = processo de abstração da realidade

• Modelo ER

Usado para identificar relacionamentos entre tipos de entidades

Visa remover a redundância de dados

Processamento de Transações On-Line (OLTP)

Modelagem de Dados

• Modelo Dimensional

Apresenta dados em uma estrutura intuitiva permitindo alta

performance de acesso

Independe da representação física dos dados

Organiza dados em tabelas de

fatos

e

dimensões

Processamento Analítico On-Line (OLAP)

(14)

Modelagem Dimensional

Tabelas de

Dimensão

Geográfica

Tabela Fatos

Medidas

Dimensão

Componentes de Modelagem Dimensional

Geográfica

Produto

Tempo Und.

$

Produto

Tempo

Tabela Fatos

Fatos

(15)

Tabelas de fatos e de dimensões

• As tabelas de Dimensões

Tabelas periféricas com pouco volume de dados

Seus atributos armazenam as descrições do negócio.

• Ex: Categoria (Bebida), Tipo (refrigerante), Ano (2000)

Atributos tendem a ser organizados em hierarquias

• EX: Produto (Categoria → Marca → Descrição)

• Loja (Tipo → Endereço → Nome_Loja)

• Tempo (Ano → Mês → Dia_Do_Mês)

• O menor nível hierárquico define o grão do DW

(16)

Fatos e dimensões

• A tabela de Fatos

Tabela central com grande volume de dados

Armazena as medições numéricas do negocio

(fatos)

e as FK para as dimensões

(fatos)

e as FK para as dimensões

As melhores medidas são numéricas e aditivas:

• EX: Vendas

(R$)

, Vl_unitário

(R$)

, Despesas

(R$)

, QtdVendida

• Temperatura não é um bom fato!

(17)

Fatos e dimensões

• A tabela de Fatos

Quando um dado numérico é medida ou atributo ?

• Medida ⇒ varia continuamente a cada amostragem

Ex: Quantidade vendida de um produto

• Atributo ⇒ praticamente constante

Ex: Peso de um produto

Cada fato é a interseção entre as dimensões

relacionadas

(18)

Modelos Dimensionais do DW

Tabela Fato

Tabela Dimensão

Dim_Tempo

Dim_Funcionario

Chave_Funcionario Codigo_Funcionario . . .

Dim_Produto

• O esquema estrela (Star)

Tabela Fato

Dim_Tempo

Chave_Tempo Data Data . . .

Fato_Vendas

Chave_Tempo Chave_Empregado Chave_Produto Chave_Cliente Chave_Entregador

.

Data_Requerida

.

Dim_Produto

Chave_Produto Codigo_Produto

.

Codigo_Produto

.

Dim_Cliente

Chave_Cliente Codigo_Cliente . . .

Dim_Entregador

Chave_Entreegador Codigo_Entregador . . .

(19)

Modelos Dimensionais do DW

Fato_Vendas Chave_Funcionario Chave_Entregador Chave_Tempo Chave_Funcionario Chave_Produto Chave_Cliente Chave_Entregador RequiredDate . . .

Dim_Produto

Chave_Produto Nome Tamanho CodigoMarca Tabela-Dimensão Principal

• O esquema Flocos de Neve (Snowflake)

Tabelas-Dimensão Secundárias . . .

MarcaProduto

CodigoMarca CodigoCategoria

Categoria

CodigoCategoria Nome

Normalizar as dimensões

Kimball

aconselha,

devido

a

complexidade desta estrutura, não

transformar o esquemas estrela em

esquemas floco de neve (o ganho

(20)

Escolhendo um schema

Star

Snowflake

Clareza

+ fácil

+ difícil

Número de tabelas

<

>

Número de tabelas

<

>

Complexidade de consultas

+ simples

+ complexo

(21)

• O cubo multidimensional

É uma abordagem multidimensional (metáfora) para

visualização e organização dos dados

Várias dimensões podem ser usadas simultaneamente

Os dados são manipulados mais rapidamente e

facilmente (agregação em níveis de hierarquia)

Abstração do DW

Lo

ja

Tempo

P

r

o

d

u

t

o

categoria

marca

descrição

tipo

endereço

nome_loja

ano

mes

(22)

Dimensão Tempo

Chave_Tempo

Mes

Trimestre

Ano

^

Tempo

^

1996

Trim1/96

Janeiro

Fevereiro

Cubo Multidimensional

Agregação em Níveis de Hierarquias

Produto

∨

1997

Trim2/96

Trim3/96

Trim4/96

Fevereiro

Março

(23)

Como representar as dimensões no cubo?

Produto

Loja Vendas

AAAA

F01

50 AAAA

F02

60 AAAA

100 BBBB

F01

40 F03

AAAA

Cubo Multidimensional

BBBB

40 BBBB

70 BBBB

80 CCCC

90 CCCC

120 CCCC

140 DDDD

20 DDDD

10 DDDD

30 F01

F01

F02

F03

AAAA

BBBB

CCCC

DDDD

F01 F02 F03

(24)

Como representar as dimensões no cubo?

Produto

Loja Vendas

AAAA

F01

50 AAAA

F02

60 AAAA

100 BBBB

F01

40 F03

Produto Loja Tempo Vendas

AAAA F01 1999 50 AAAA F02 1999 60 AAAA F03 1999 100 BBBB F01 1999 40 BBBB F02 1999 70 BBBB F03 1999 80 CCCC F01 1999 90 CCCC F02 1999 120 CCCC F03 1999 140

1999

2000

AAAA

Cubo Multidimensional

BBBB

40 BBBB

70 BBBB

80 CCCC

90 CCCC

120 CCCC

140 DDDD

20 DDDD

10 DDDD

30 F01

F01

F02

F03

CCCC F03 1999 140 DDDD F01 1999 20 DDDD F02 1999 10 DDDD F03 1999 30 AAAA F01 2000 50 AAAA F02 2000 60 AAAA F03 2000 100 BBBB F01 2000 40 BBBB F02 2000 70 BBBB F03 2000 80 CCCC F01 2000 90 CCCC F02 2000 120 CCCC F03 2000 140 DDDD F01 2000 20 DDDD F02 2000 10

AAAA

BBBB

CCCC

DDDD

F01 F02 F03

(25)

1999

2000

AAAA

1999

2000

AAAA

Qual o total de vendas do produto AAAA?

Exemplo de Consultas

Cubo Multidimensional

AAAA

BBBB

CCCC

DDDD

AAAA

BBBB

CCCC

DDDD

(26)

1999

2000

AAAA

1999

2000

AAAA

Qual o total de vendas da loja F03?

Exemplo de Consultas

Cubo Multidimensional

AAAA

BBBB

CCCC

DDDD

F01

F02

F03

AAAA

BBBB

CCCC

DDDD

F01

F02

F03

(27)

Qual o total de vendas do ano 1999?

1999

2000

AAAA

1999

2000

AAAA

Exemplo de Consultas

Cubo Multidimensional

AAAA

BBBB

CCCC

DDDD

AAAA

BBBB

CCCC

DDDD

(28)

Qual o total de vendas do produto BBBB no ano de

1999 e na loja F2?

1999

2000

AAAA

1999

2000

AAAA

Exemplo de Consultas

Cubo Multidimensional

AAAA

BBBB

CCCC

DDDD

F01

F02

F03

AAAA

BBBB

CCCC

DDDD

F01

F02

F03

(29)

(30)

• O que é OLAP (On-Line Analytical Processing)?

Conjunto de tecnologias projetadas para analisar dados

típicos de suporte à decisão que estão no DW

Fornece dados em alto nível (totais,medias,min..)

OLAP

Acessa vários registros

Tem alta performance e consultas fáceis e interativas

Lida com dados históricos (dimensão temporal)

(31)

• O que é OLAP ? (Cont.)

Exemplos de consultas típicas de OLAP:

• Quais os produtos mais bem vendidos no mês passado?

• Quais os 10 piores vendedores dos departamentos da filial X?

OLAP

• Quais os 10 piores vendedores dos departamentos da filial X?

• Qual a média salarial dos funcionários de informática na região sul

nos últimos 5 anos?

(32)

OLTP

• O que é OLTP (On-Line Transaction Processing)?

Conjunto de tecnologias projetadas para analisar e

acessar dados típicos de suporte operacional

• Exemplos de consultas típicas de OLTP:

Qual o produto mais vendido?

Qual o pior vendedor?

Qual o total de vendas?

(33)

OLAP X OLTP

Características

Ope racional

Estratégico

Objetivo

Op. diárias do negócio

Análisar o negócio

Visão dos dados

Relacional

Dimensional

Op. com os dados

Incl., Alt, Excl e Cons.

Carga e Consulta

Atualização

Contínua (tempo real)

Periódica (Bach)

N

o

de usuários

Milhares

Dezenas

OLAP

Características

OLTP

N

o

de usuários

Milhares

Dezenas

Tipo de usuário

Operaciona l

Gerencial

Interação c/ usuário

Pré-definida

Pré-definida e ad-hoc

Granularidade dos dados Detalhados

Detalhados e Resumos

Redundância dos dados Não ocorre

Ocorre

Volume dos dados

Megabytes-Gigabytes

Gigabytes-Terabytes

Histórico dos dados

Até a última atualização 5 a 10 anos

(34)

Arquiteturas OLAP

• Relacional OLAP = ROLAP

• Multidimensional OLAP = MOLAP

• Híbrido OLAP = HOLAP

• ROLAP

Utiliza BD relacional para fazer análise dos fatos

Utiliza SQL para Manipula os dados de forma

multidimensional

Lida com fatos atômicos assim como sumarizados.

(35)

Arquiteturas OLAP

• ROLAP

Data Warehouse ou Data Mart

Dados ROLAP Dados ROLAP

Dados ROLAP

Agregações ROLAP Agregações

AgregaçõesROLAPROLAP Agregações são armazenadas_{em tabelas relacionais}

Dados-base são mantido no SGBD-fonte

Data Warehouse ou Data Mart

Dados ROLAP Dados ROLAP

Dados ROLAP

AgregaçõesROLAPROLAP Dados ROLAP

Dados ROLAP

Dados ROLAPDados ROLAP_{Dados ROLAP}

Dados ROLAP

Agregações_{Agregações ROLAP}ROLAPROLAP Agregações

AgregaçõesROLAPROLAP Agregações são armazenadas_{em tabelas relacionais}

Dados-base são mantido no SGBD-fonte

SGBD

em tabelas relacionais Estrutura totalmente relacional.

SGBD

em tabelas relacionais Estrutura totalmente relacional.

(36)

• MOLAP

Utiliza MDDB proprietários (com matrizes

n-dimensionais) para manipular fatos agregados

Principal premissa

• Armazenar de forma multidimensional para visualizar de forma

Arquiteturas OLAP

• Armazenar de forma multidimensional para visualizar de forma

multidimensional

Desvantagens

• Não manipula fatos atômicos

• Não trabalha com muitas dimensões

(37)

MOLAP

Data Warehouse ou

Data Mart

Data Warehouse ou

Data Mart

Cubo

Armazena cópia da tabela fato e dimensões Armazena agregações

Data Warehouse ou

Data Mart

Data Warehouse ou

Data Mart

Data Warehouse ou

Data Mart

Data Warehouse ou

Data Mart

Cubo

Armazena cópia da tabela fato e dimensões

Armazena agregações

Cubo

Armazena cópia da tabela fato e dimensões Armazena agregações

Arquiteturas OLAP

SGBD

Dados MOLAP Agregações MOLAP Armazena agregações

SGBD

Dados MOLAP Agregações MOLAP Armazena agregações Dados MOLAP Agregações MOLAP Armazena agregações

(38)

• HOLAP

Mistura de MOLAP com ROLAP

Suporta manipulação de fatos atômicos e agregados

Utiliza MDDB para analisar os fatos agregados

Arquiteturas OLAP

Utiliza SQL para manipular fatos atômicos

(39)

HOLAP

Data Warehouse ou Data Mart

Cubo

_Cubo

Dados-base mantidos no BD fonte

Agregações calculadas e armazenadas no cubo

Data Warehouse ou Data Mart

Cubo

_Cubo

Dados-base mantidos no BD fonte Agregações calculadas e armazenadas no cubo

Arquiteturas OLAP

Dados ROLAP Dados ROLAP Dados ROLAP SGBD SGBD _Agregações MOLAP Dados ROLAP Dados ROLAP

Dados ROLAPDados ROLAP_{Dados ROLAP}

Dados ROLAP

SGBD

SGBDSGBD

SGBD _Agregações

(40)

Resumo sobre as Arquiteturas

Armazenamento Armazenamento Dados de base Agregações MOLAP MOLAP Cubo Cubo HOLAP HOLAP Tabela Relacional Cubo ROLAP ROLAP Tabela Relacional Tabela Relacional

Arquiteturas OLAP

Relacional Perspectiva do Cliente

Perspectiva do Cliente MOLAPMOLAP HOLAPHOLAP ROLAPROLAP

Performance de consulta Consumo em disco Manutenção do cubo Imediato Alto Alto Mais rápido Médio Médio Rápido Baixo Baixo