Data Warehousing

1. Data Warehousing

2. B.D. Modelo Conceitual Modelo Lógico Mundo Real Modelo Físico Processo de Modelagem • Levantamento de Requisitos • Execução da Modelagem • Modelagem Conceitual (Modelo Entidade-Relacionamento) • Modelagem Lógica (Modelo Relacional) • Modelagem Física

3. Modelo Entidade-Relacionamento • É um modelo de dados de alto nível. • Possui dois elementos principais: Entidades e Relacionamentos. matr cod M N nome Disciplina Aluno Cursa nome cr semestre nota N 1 1 N Monitor Orientado 1 matr 1 Ministra Professor nome título Esquema Conceitual MER

4. Entidade • (A010, João, 9,5) • (A020, Maria, 8,1) • (A030, José, 4,7) • (A040, Ana, 9,8) • (D01, BD, 2001.2) • (D02, BD, 2002.1) • (D03, Cálculo,2002.1) matr cod M N nome Disciplina Aluno Cursa nome cr semestre nota N 1 1 N Monitor Orientado 1 matr 1 Ministra Professor nome título Chave: atributo (ou conjunto de atributos) que identifica uma entidade, ou seja, não há valores repetidos deste atributo na entidade. O atributo chave aparece sublinhado. • (P010, Alexandre, D.Sc.) • (P020, Lygia, D.Sc.) • (P030, Branca, M.Sc.)

5. Relacionamento • (P010, A040) • (P010, A030) • (P020, A010) • (A010, D01, 9,0) • (A010, D02, ---) • (A020, D03, 4,3) • (A030, D03, 5,8) matr cod M N nome Disciplina Aluno Cursa nome cr semestre nota N 1 1 N Monitor Orientado • (A020, D01) • (A030, D02) • (A040, D03) 1 matr 1 Ministra Professor nome título • (P010, D01) • (P010, D02) • (P020, D03)

6. Relacionamento Ternário • O grau do relacionamento representa o número • de entidades participantes N N Peça Fornecedor Fornece N Um determinado fornecedor pode fornecer uma determinada peça para ser utilizada em vários projetos. Grau 3 (ternário) Projeto

7. Modelo Relacional • É um modelo de dados de implementação. • Neste modelo, os dados são organizados em tabelas (relações).

8. Domínio • Conjunto de valores que um determinado atributo pode assumir. D2 D3 D4 D1 • Adams • Jones • Roma • S1 • S4 • S7 • 20 • 5 • Madri • 25 • Joe • Clark • S2 • S6 • Rio • Atenas • 10 • 30 • Anne • Blake • Rio • 40 • S8 • S5 • S3 • 15 • Londres • Paris • 35 • Smith

9. Relação (Tabela) • Uma relação (Tabela), definida sobre os domínios D1, D2, ...Dn, é um conjunto de tuplas. Cada tupla é uma lista de valores <v1,v2,...,vn>, tal que cada valor vi é um elemento do domínio Di, ou é o valor nulo. atributos (colunas) tuplas (linhas)

10. Grau e Cardinalidade • O grau de uma relação R é o número de atributos de R. • A cardinalidade de uma relação R é o número de tuplas de R. Grau 4 Cardinalidade 5

11. Chave • Uma chave é um atributo (ou conjunto de atributos) que identifica univocamente uma relação. Ou seja, um atributo chave não apresenta valores duplicados. As chaves são representadas sublinhadas.

12. Chave Primária e Chave Secundária • As chaves de uma relação, quando há mais de uma, são chamadas chaves candidatas. Uma delas, a principal, é eleita a chave primária e as outras, se houver, são chamadas chaves secundárias. 1 2 Chaves cadidatas de P: (P#), (Nome,Cor) Chave primária de P: (P#) Chave secundária de P: (Nome,Cor)

13. Chave Estrangeira • Uma chave estrangeira de uma relação R é um atributo (ou conjunto de atributos) que referencia a chave primária de uma (outra) relação S. • Desta forma, para qualquer tupla de R, o valor da chave estrangeira deve ser igual ao valor da chave primária de alguma tupla da relação S referenciada, ou deve ser o valor nulo. (Integridade Referencial) Chaves estrangeiras de SP: (S#) referenciando a relação S e (P#) referenciando a relação P.

14. Mapeamento Lógico-Relacional Entrada: Esquema Conceitual (MER) M N Fornecedor (S) Peça (P) Fornecimento (SP) Saída: Esquema Lógico (Relacional) Diagrama Relacional

15. Entidade end Aluno CR nome matr CPF Disciplina semestre cod nome Professor título matr nome

16. Relacionamento N:1 N 1 Professor Disciplina Ministra

17. Relacionamento N:M M N Disciplina Aluno Cursa nota

18. S# Id# S# P# Nome Id# Nome Nome P# Duração Cor Status Peso Quantidade Cidade Relacionamento N:M (Ternário) N N Peça Fornecedor Fornece N quantidade Projeto Fornece Projeto Fornecedor Peça

19. Modelagem de Dados para Data Warehouse • Algumas características importantes do Modelo Entidade e Relacionamento (MER) Tradicional: • Foco em aplicações transacionais; • Foco no armazenamento “não-histórico”, momentâneo, da informação; • Acesso individual e pontual aos dados; • Tende a um grande número de tabelas; • Podem ocorrer muitas tabelas semelhantes e diversas tabelas pouco povoadas; • Eficiente apenas para consultas simples e diretas.

20. Modelagem de Dados para Data Warehouse • Algumas necessidades importantes em um ambiente de Data Warehouse: • Foco em aplicações gerenciais; • Análise histórica das informações; • Visão ampla das informações (sumarizações, cruzamentos...); • Visualizar os dados sobre diferentes perspectivas e flexibilidade no suporte a análises -> consultas complexas. • Conclusão: para implementar um Data Warehouse, necessita-se de um novo modelo, diferente do MER tradicional.

21. Modelagem Multidimensional A idéia fundamental da modelagem multidimensional é representar os tipos de dados de negócio por uma estrutura conhecida como cubo de dados, onde suas células contêm valores de medidas, enquanto os lados do mesmo definem as dimensões dos dados. Obs: Normalmente também refere-se como cubo de dados ou simplesmente cubo à modelagem multidimensional com mais de três dimensões, no entanto, o termo técnico para tal estrutura é hipercubo.

22. Modelagem Multidimensional • Facilita o entendimento e a visualização das informações utilizadas pelo suporte à decisão • Estrutura mais intuitiva para o processamento analítico • Dados agrupados por assunto (vendas)

23. Visão Relacional X Visão Multidimensional Visão Relacional Volume de vendas de uma loja de instrumentos musicais por instrumento e por estado InstrumentoEstadoVolume Vendido Violão MG 140 Violão RJ 100 Violão SP 150 Guitarra MG 140 Guitarra RJ 120 Guitarra SP 80 Bateria MG 30 Bateria RJ 20 Bateria SP 50

24. Visão Relacional X Visão Multidimensional Visão Multidimensional Volume de vendas de uma loja de instrumentos musicais por instrumento e por estado Volume de Vendas 140 100 150 Violão Instrumento 140 120 80 Guitarra 30 20 50 Bateria MG RJ SP Estado

25. Visão Relacional X Visão Multidimensional Visão Relacional Volume de vendas por instrumento, por estado e por ano Instrumento Estado Ano Volume Vendido Violão MG 1997 30 Violão MG 1998 60 Violão MG 1999 50 Violão RJ 1997 10 Violão RJ 1998 30 Violão RJ 1999 60 Violão SP 1997 50 Violão SP 1998 50 Violão SP 1999 50 Guitarra MG 1997 40 Guitarra MG 1998 30 Guitarra MG 1999 70 Guitarra RJ 1997 40 Guitarra RJ 1998 50 Guitarra RJ 1999 30 Guitarra SP 1997 60 ...

26. Visão Relacional X Visão Multidimensional Visão Multidimensional Volume de vendas por instrumento, por estado e por ano Volume de Vendas Violão Instrumento Guitarra 1999 Bateria 1998 1997 Ano MG RJ SP Estado

27. Modelagem Multidimensional Hipercubo Volume de Vendas Violão Violão Guitarra Guitarra 1999 1999 Bateria Bateria 1998 1998 1997 1997 MG RJ SP MG RJ SP Importado Nacional

28. Modelagem Multidimensional • Elementos básicos: • Fatos: é tudo aquilo que pode ser representado por um valor aditivo, ou seja, por meio de valores numéricos. Este conjunto de valores numéricos é denominado de métricas ou medidas. • Ex.: Vendas. • Dimensões: determinam o contexto no qual o negócio será analisado. • Ex.: As dimensões do fato venda de produtos poderiam ser o Tempo, a Localização, os Clientes e os Vendedores • Medidas (variáveis): atributos numéricos que representam um fato • Ex.: Valor em reais das vendas, o número de unidades vendidas, a quantidade em estoque, o custo de venda etc.

29. Modelagem Multidimensional • Star Schema: • Forma de dispor as tabelas do banco para simular um banco de dados multidimensional; • Presença de dados redundantes para se obter um melhor desempenho de consultas; • Composto por uma tabela dominante, chamada tabela de fatos, rodeada de tabelas auxiliares, chamadas tabelas de dimensão; • A tabela de fatos conecta-se às demais por múltiplas junções e as tabelas de dimensões se conectam com apenas uma junção à tabela de fatos.

30. MG Estados Banana RJ SP Goiaba Produtos Laranja Maçã 50 Vendas (em toneladas) Fato (assunto) Dimensões Ano 96 97 98 99 Modelagem Multidimensional

31. Modelagem Multidimensional • Star Schema:

32. Modelagem Multidimensional • Star Schema: • As tabelas de dimensões são desnormalizadas para aumentar o desempenho da consulta; • A tabela de fatos armazena grande quantidade de dados históricos em função do tempo, obtidos a partir da intersecção de todas as dimensões da estrela; • A dimensão tempo é sempre integrante da chave primária da tabela de fatos; • Os indicadores de desempenho (medidas) do negócio são armazenados na tabela de fatos.

33. Modelagem Multidimensional • Star Schema – Exemplo de tabela de dimensão (Produto):

34. Modelagem Multidimensional • Star Schema – Exemplo de tabela de fatos (Vendas):

35. Mês Trimestre Janeiro 1o Trimestre Fevereiro Ano 2o Trimestre 1996 3o Trimestre Dimensão Tempo 4o Trimestre 1997 Modelagem Multidimensional Membros da uma Dimensão: são os elementos das dimensões Ex.: Todas as ocorrências de cidades, estados e regiões formam a dimensão geográfica, todas as ocorrências de ano, trimestre e mês formam a dimensão tempo. Hierarquia de uma Dimensão: a hierarquia existente entre os membros de uma dimensão Ex.:

36. Visão Multidimensional Dimensão Localização Hierarquia da Dimensão Medidas Campinas SP 41 Americana 11 21 38 22 43 35 41 Niterói 41 45 56 33 Rio 39 Campos 42 41 43 Dimensão Tempo Modelo do Produto 1101 1102 2001 2002 Hierarquia da Dimensão Membros da Dimensão Celular Pager Produto Modelagem Multidimensional

37. Modelagem Multidimensional • Dimensões: • Representam entidades que evoluem no tempo, como pessoas que mudam de endereço, mudanças na descrição e/ou formulação de um produto etc.; • Não se deve criar dependências entre as demais dimensões e a dimensão tempo, pois seria um passo em direção a uma estrutura altamente relacionada, o que traria perda de desempenho em consultas; • Pode-se tratar estas modificações de três formas diferentes, de acordo com o tipo da dimensão. • Obs: recomenda-se fortemente o uso de chaves artificiais.

38. Modelagem Multidimensional • Dimensão Tipo 1: • O histórico não é relevante – as substituições de valores podem ser feitas diretamente no registro em questão. • Ex: Cris - tinha estado civil solteira até 25/06/2010. • Casou-se em 25/06/2010. Como refletir esta “evolução” no DW ? • Nenhuma chave é afetada no banco de dados • Muito fácil de implementar mas os dados históricos ficam inconsistentes • Duas questões básicas devem ser feitas antes de decidir por esta solução: • Qual a importância desse valor para as análises do usuário final? • Qual a importância de se rastrear o histórico ? • Ex: Cris teria seu atributo estado civil atualizado para casada

39. Modelagem Multidimensional • Dimensão Tipo 1:

40. Modelagem Multidimensional • Dimensão Tipo 2: • O histórico é relevante -> Inserção de um novo registro na mesma entidade dimensional, refletindo a “mudança de estado”; • Ex: Existirão dois registros de Cris na dimensão cliente. O primeiro referente ao seu estado civil até 25/06/2010 - solteira e o outro ao estado civil casada. • Na tabela de fatos vendas, o primeiro registro de Cris está vinculado as Vendas anteriores a 25/06/2010, e o segundo estará vinculado as vendas posteriores a essa data

41. Modelagem Multidimensional • Dimensão Tipo 2:

42. Modelagem Multidimensional • Dimensão Tipo 3 • São necessários campos para armazenar: • status original do atributo dimensional • status atual do atributo dimensional • data efetiva da ultima alteração do campo (status atual) • Apenas dois status podem ser rastreados: o atual e o original; • É possível fazer análises comparando com os resultados utilizando os • status original e atual

43. Modelagem Multidimensional • Dimensão Tipo 3 • O atributo estado civil, seria renomeado para estado civil original e seriam incluídos os atributos estado civil atual e data efetiva do estado civil. • Sempre que acontecer uma mudança no estado civil de Cris, substituiremos o valor do campo estado civil atual e mudaremos a data efetiva. O campo estado civil original nunca é modificado

44. Modelagem Multidimensional • Dimensão Tipo 3:

45. Modelagem Multidimensional • Dimensão Tipo 3:

46. Gerar todas combinações possíveis Modelagem Multidimensional • Dimensões pequenas para auxílio de desempenho: • “Qual o volume de vendas de livros técnicos para pós-graduados?”

47. Modelagem Multidimensional • Dimensões descaracterizadas: É chave no BD operacional, mas não é chave de dimensão. Representa o próprio conceito.

48. Modelagem Multidimensional • Dimensão Tempo: • Procura-se armazenar descrições que não podem ser extraídas utilizando funções nativas dos SGBDs.

49. Modelagem Multidimensional Esquema SnowFlake (Floco de Neve) Dimensão Região Dimensão Tempo Dimensão Estado Dimensão Cliente Dimensão Cidade Fato Vendas Dimensão Categoria Dimensão Produto Dimensão Vendedor

50. Modelagem Multidimensional • Granularidade: • É o nível de detalhe das tabelas. Quanto maior o nível de detalhe, menor o grão. • Agregação: • É o processo de resumir, sumarizar dados granulares ou detalhados e de armazenar fisicamente as agregações na tabela de fatos ao longo de uma hierarquia. • Gera redundância, mas agiliza processos de consultas.