Desenho Lógico de DW

(1)

SAD Tagus 2006/07 H. Galhardas

Desenho Lógico de DW

Arquitectura

multi-

nível

Data Warehouse Extract Transform Load Refresh OLAP Engine Analysis Query Reports Data mining Monitor & Integrator Metadata

Data Sources Front-End Tools

Serve Data Marts Operational DBs other sources Data Storage OLAP Server

(2)

Ciclo de vida de um

projecto de suporte à

decisão

Ciclo de vida de um

projecto de suporte à

decisão

(3)

Como

desenhar

a DW?

Data Warehouse

Data Marts

Processo

de

desenho

de DW



 EscolherEscolher o o processoprocesso de de negócionegócio a a modelizarmodelizar (ex: (ex:

encomendas

encomendas, , recebimentosrecebimentos, etc), etc)



 EscolherEscolher o o grãogrão ( (nívelnível de dados de dados atómicoatómico) ) dada

tabela

tabela de de factosfactos

 Volume mais pequeno de info que se poderá consultar



 IdentificarIdentificar e e tornartornarconformesconformes as as dimensõesdimensões queque

se

se aplicamaplicam a a cadacadaregistoregisto dada tabelatabela de de factosfactos

 Vértices de análise por onde será possível navegar



 EscolherEscolherosos factosfactosououmedidasmedidas quequevãovão popular popular

cada

(4)

Processo

de

desenho

de DW



 EnriquecerEnriquecer as as tabelastabelas de de dimensõesdimensões com com

todos

todos osos atributosatributosqueque podempodem ajudarajudar a a explicar

explicar a a existênciaexistência de de determinadosdeterminadosfactosfactos 

 MonitorizarMonitorizar as as dimensõesdimensões lentamente lentamente

modificadas

modificadas aoao longolongo do tempo do tempo ( (slowlyslowly changing dimensions

changing dimensions))



 Definição dos níveis de Definição dos níveis de agregaçãoagregação



 AnáliseAnálise do tempo de do tempo de vidavida e do e do históricohistórico dos dos

dados a

dados a carregarcarregar 

 NecessidadesNecessidades de de disponibilidadesdisponibilidades dos dados dos dados

Exemplo de motivação

SI de uma cadeia de lojas de material informático

Lojas (

Lojas (lojaidlojaid, nome, morada, codpostal, localidade, NIF), nome, morada, codpostal, localidade, NIF) TiposCliente (

TiposCliente (tipoclienteidtipoclienteid, dsc), dsc) TabelasPrecos(

TabelasPrecos(tabelaprecoidtabelaprecoid, dsc), dsc) Clientes (

Clientes (clienteidclienteid, nome, nomecomercial, morada,, nome, nomecomercial, morada, codpostal, localidade, ...,

codpostal, localidade, ..., tipoclienteidtipoclienteid,, tabelaprecoid

tabelaprecoid)) Vendas (

Vendas (vendaidvendaid, datavenda, , datavenda, lojaidlojaid, , clienteidclienteid,, vendadinheiro, desconto)

vendadinheiro, desconto) FamiliasArtigos (

FamiliasArtigos (familiaidfamiliaid, dsc, , dsc, superfamiliaidsuperfamiliaid)) Artigos (

Artigos (artigoidartigoid, referencia, codbarras, nome,, referencia, codbarras, nome, familiaid

familiaid, iva, unidade), iva, unidade) LinhasVendas (

LinhasVendas (vendaid, artigoidvendaid, artigoid, qt, precounidade), qt, precounidade)

...

(5)

1. Escolher o processo de

negócio

Análise das vendas realizadas a clientes => define-se como prioridade a análise das

transacções comerciais do ponto de vista da facturação Faz parte do data mart de suporte à área comercial, onde também se está interessado nas compras feitas a

Fornecedores, por exemplo

2. Escolher o grão

Artigos vendidos, por cliente, em cada loja, ao

longo de cada dia

De modo a responder a questões do tipo:



 Quanto se vendeu em caixas de DVD hoje?Quanto se vendeu em caixas de DVD hoje?



 Qual a loja com maior volume de vendas emQual a loja com maior volume de vendas em

Fevereiro?



 Quais os clientes que compraram um maiorQuais os clientes que compraram um maior

número de artigos de informática na loja de Lx?

(6)

Tabela de factos

Vendas (

Vendas (artigoid, lojaid, clienteid,artigoid, lojaid, clienteid, tempoid

tempoid, qt, valor...), qt, valor...)

Vs

Vendas (

Vendas (artigoid, venda id, lojaid,artigoid, venda id, lojaid, clienteid, tempoid

clienteid, tempoid, qt, valor...), qt, valor...)

Grão mais fino implica maior número de registos

3. Identificar e tornar

conformes as dimensões



 A dimensão Tempo deve ser sempre incluida naA dimensão Tempo deve ser sempre incluida na

DW, dada a característica predominante de análise

de dados históricos



 Tem um número muito grande de registos, mesmoTem um número muito grande de registos, mesmo

que não existam factos para alguns deles.

DimTempo

DimTempo ( (tempoidtempoid, data, , data, anoano,, trimestre

trimestre, , diadia, , semanasemana, , diasemanadiasemana,, feriado

(7)

Dimensões conformes



 As dimensões são os pontos de entrada numAs dimensões são os pontos de entrada num

data mart. Determinam:

 Os critérios de navegação

 Os cabeçalhos dos relatórios

 Vocabulário da organização para os utilizadores



 Dimensões conformesDimensões conformes: significam a mesma: significam a mesma

coisa e guardam a mesma informação

independentemente da tabela de factos a que

estão ligadas.

Hieraquias nas dimensões



Uma dimensão comporta normalmente (pelo

menos) uma hieraquia de análise através da

qual se podem realizar:

 Drill-down – operações de consulta detalhada  Roll-up – consultas de resumos de informação

DimTempo

DimTempo ( (tempoidtempoid, data, , data, anoano, , trimestretrimestre,, dia

dia, , semanasemana, , diasemanadiasemana, , feriadoferiado,, períodofiscal

(8)

Esq. floco de neve vs estrela

DimArtigos (

DimArtigos (artigoidartigoid, nome, referência,, nome, referência,

subfamiliaid

subfamiliaid, marcaid, custoactual,, marcaid, custoactual,

iva, ultimoprecocompra, ...)

DimSubFamilias (

DimSubFamilias (subfamiliaidsubfamiliaid, familiaid,, familiaid, subfamilia)

subfamilia)

DimFamilias(

DimFamilias(familiaidfamiliaid, familia), familia)

Vs

DimArtigos (

DimArtigos (artigoidartigoid, nome, referência,, nome, referência, subfamiliaid, subfamilia, familiaid,

subfamiliaid, subfamilia, familiaid,

familia, marcaid, marca, custoactual,

iva, ultimoprecocompra, ...)

Mini-dimensões



 Estrutura de dados que contém atributos não chave e queEstrutura de dados que contém atributos não chave e que não se encontram envolvidos em hierarquias mas que podem

não se encontram envolvidos em hierarquias mas que podem

ser usados como critérios de pesquisa sobre a dimensão.



 Diminuem o número de atributos das dimensões maisDiminuem o número de atributos das dimensões mais complexas em termos de atributos e volumosas em número

complexas em termos de atributos e volumosas em número

de registos

FactVendas (tempoid, clienteid, artigoid, miniclienteid, qt, precovenda, custostock, ...)

DimClientes (clienteid, nome, apelido, morada, codpostal, localidade, concelho, distrito) DimMiniClientes (miniclienteid, agregadofamiliar,

(9)

4. Escolher os factos



O

grão

da tabela de factos determina que

factos usar num data mart



Todos os factos têm que ser especificados ao

mesmo nível determinado pelo grão



Os factos devem ser o mais

aditivos

possíveis



Podem ser adicionados

factos suplementares

desde que sejam consistentes com o grão

.

Categorização dos factos

ou medidas



 AditivaAditiva: podem ser somadas através de todas: podem ser somadas através de todas

as dimensões; são medidas de actividade

 E.g.: unidades_vendidas, dolares_vendidos



 Semi-aditivaSemi-aditiva: só podem somadas ao longo de: só podem somadas ao longo de

algumas dimensões; são fotografias no tempo

 E.g.: saldo_conta, quantidade de um inventário não

podem ser somadas ao longo do tempo



 Não aditivaNão aditiva: não podem ser somadas de todo: não podem ser somadas de todo

(10)



 F : A x B F : A x B _®_® C C é aditivaé aditiva sobre A sse: sobre A sse:  F (a₁+ a₂,b) = F (a1, b) + F (a2, b)

 F é aditiva se é aditiva sobre todos os seus

argumentos

Exemplo :

Exemplo : conta(contaID, clienteID, data, saldo)conta(contaID, clienteID, data, saldo) saldo : contaID x clienteID x data

saldo : contaID x clienteID x data ®® saldo saldo saldo (x, y, [t

saldo (x, y, [t₀₀, t, t₂₂]) = saldo (x, y, [t]) = saldo (x, y, [t₀₀, t, t₁₁]) ]) + ₊saldo (x, y, [tsaldo (x, y, [t1 1 ,,

t

t₂₂])]) saldo (x

saldo (x₁₁ÈÈ xx₂₂, y, t, y, t) = saldo (x) = saldo (x₁₁, y, t) , y, t) È _Èsaldo (xsaldo (x₂₂, y, t, y, t₂₂))

Medidas aditivas

? ?

Factos

factless

(1)



Factos que não fazem parte de uma tabela

de factos normal, pois não medem nada.



Descrevem

eventos

ou

coverage

Ex

:

Sistema de monitorização dos alunos que detecta a assiduidade de cada aluno às aulas

Tabela de factos: AssiduidadeAlunos com atributo assiduidade (0/1)

(11)

6. Monitorizar as dimensões

lentamente modificadas ao

longo do tempo



 As fontes de dados operacionais que alimentam asAs fontes de dados operacionais que alimentam as

dimensões podem mudar, o que significa que os

atributos descritivos das dimensões podem mudar

Ex

Ex::

Clientes de hipermercado podem mudar a morada ou o

seu estado civil.

Importante para aferir a receptividade dos clientes à

oferta de uma gama de produtos ou medir o

impacto de uma promoção

Soluções



Actualizar os atributos com os seus valores maisActualizar os atributos com os seus valores mais

recentes

recentes, sobrepondo estes às instâncias anteriores, sobrepondo estes às instâncias anteriores 

 Todos os registos da dimensão sãoTodos os registos da dimensão são etiquetadosetiquetados

temporalmente indicando a data de inserção ou da

última modificação

última modificação. O mesmo cliente, por exemplo,. O mesmo cliente, por exemplo, pode ser representado por mais do que um registo

pode ser representado por mais do que um registo



 Criar um campo Criar um campo ““antigoantigo”” em cada registo para em cada registo para

guardar o valor antigo ou criar

guardar o valor antigo ou criar uma tabelauma tabela complementar de histórico da dimensão

complementar de histórico da dimensão, onde são, onde são

guardadas infos sobre todas as alterações sofridas

pelos registos ao longo do tempo

(12)

Exemplo dimensão clientes

com solução do tipo 2

Codcliente

Codcliente nome estcivil agfamiliar dataregisto nome estcivil agfamiliar dataregisto

12319990930 Jorge Silva solt 1 30/09/1999

12320000505 Jorge Silva casad 2 05/05/2000

12320021111 Jorge Silva casad 3 11/11/2002

14320030728 Alda Gomes casad 2 28/07/2003



 Volume de dados na DW aumentaVolume de dados na DW aumenta



 Filtragem sobre as dimensões passa a ser enquadrada naFiltragem sobre as dimensões passa a ser enquadrada na evolução histórica de um histórico

evolução histórica de um histórico

7. Definição dos níveis de

agregação



 Quais os equipamentos que estiveram afectados, no últimoQuais os equipamentos que estiveram afectados, no último mês, menos de 5% do tempo a um projecto?

mês, menos de 5% do tempo a um projecto?



 Quais os clientes com maior volume de vendas no últimoQuais os clientes com maior volume de vendas no último mês?

mês?



 Quanto é que se vendeu do artigo X no dia de hoje?Quanto é que se vendeu do artigo X no dia de hoje? Valores agregados pré-calculadas

Valores agregados pré-calculadas –– mesmos valores que estão mesmos valores que estão na tabela de factos inicial mas envolvendo outro grão.

na tabela de factos inicial mas envolvendo outro grão.

Guardam-se em tabelas de factos distintas.

Vantagem

Vantagem: ganho no tempo de resposta das interrogações: ganho no tempo de resposta das interrogações OLAP

OLAP

Desvantagens

Desvantagens: torna mais lento o refrescamento (actualização): torna mais lento o refrescamento (actualização) da DW e aumenta o volume de dados armazenado

(13)

Exemplo de agregação

pré-calculada

calculada

AggVendasFamiliasMes AggVendasFamiliasMes (

(tempoid,familiaidtempoid,familiaid, qt, total), qt, total) DimTempo (

DimTempo (tempoidtempoid, mes, ano), mes, ano) DimAggFamilias (

DimAggFamilias (familiaidfamiliaid, familia), familia)



 Não se deve sumariar os factos para todas asNão se deve sumariar os factos para todas as

possiveis questões, mas só para as mais frequentes



 A escolha das agregações pré-calculadas podeA escolha das agregações pré-calculadas pode

evoluir ao longo do tempo, mas deve ser

transparente para o utilizador final

Outro exemplo

Customer Summary Cust_id Total Sales Highest Sales Value

Average Sales Store Table Store_id District_id Customer Table Cust_id Cust_name Time Table Week_id Period_id Year_id Product Table Product_id Product_desc Sales Fact Table

Unit Sell Price Dollar Sales

Unit Sales Dollar Cost

Summary

Summary for for Product Product,,

Store, and Time for all

Customers

(14)

Bibliografia



 (Livro) Sistemas de Apoio à Decisão, Bruno Cortes,(Livro) Sistemas de Apoio à Decisão, Bruno Cortes,

FCA, 2005



 (Livro) (Livro) Data Mining: Concepts and TechniquesData Mining: Concepts and Techniques, J., J.

Han & M. Kamber, Morgan Kaufmann, 2001 (parte

da Secção 2.3)



 (Livro) The Data Warehouse Lifecycle Toolkit, R.(Livro) The Data Warehouse Lifecycle Toolkit, R.

Kimball, Wiley 1998 (Cap. 5, 6 e 7)



 (Artigo) Letting the Users Sleep Part 1 and 2, R.(Artigo) Letting the Users Sleep Part 1 and 2, R.

Kimball,

Kimball, DBMSDBMS–– Dec. 1996 and Jan. 1997, Dec. 1996 and Jan. 1997, http://www.dbmsmag.com/9612d05.html

http://www.dbmsmag.com/9612d05.html

http://www.dbmsmag.com/9701d05.html