Data Warehousing. Histórico. Uma Hierarquia de Necessidades de Informação. Transacional vs. Data Warehousing. OLAP Décadas de 60 e 70

(1)

1

Data Warehousing

Ana Carolina Salgado Fábio Ávila Carlos Eduardo Pires

2

Uma Hierarquia de Necessidades de

Informação

Data S o fi s ti c a ç ã o d o U s u á ri o Dados Relatório Operacional Consulta ad hoc OLAP MD

Que outras coisas podiam ser importantes?

O que é provável acontecer amanhã?

Por que isto aconteceu hoje?

O que aconteceu hoje?

3

Sistemas Analíticos – uma solução DW

OLAP – On Line Analytical Processing

Apoio a suporte estratégico de decisão

Provê diferentes níveis de análise

Permite aos usuários que naveguem nos diferentes

níveis de dados

Permitem busca no Sistema para achar novos

relacionamentos

Exemplos:

Aplicações baseadas em planilha Aplicações de previsão de vendas

4

Transacional vs. Data Warehousing

Transacional

Transacional Data WarehousingData WarehousingData Warehousing

Frequência de atualização

Frequência

de atualização Tempo realTempo real PeriodicamentePeriodicamente

Estruturado para

Estruturado para Integridade de dados_{Integridade de dados} Facilidade de consultaFacilidade de consulta

Otimizado para

Otimizado para Processamento de transações Processamento

de transações Perfomance de consultasPerfomance de consultas

5

Histórico

6

OLAP – Décadas de 60 e 70

1962 - APL – “A Programming Language” – Ken

Iverson

Primeira linguagem com variáveis multi-dimensionais e

operadores de processamento.

WOL – Write-Only Language

Usado em muitas aplicações comerciais nos anos 70 e 80

com funções similares aos Sistemas OLAP de hoje.

1970 – Express

Mais orientado a aplicações e largamente utilizado Produto multi-dimensional com origens acadêmicas Comprado pela Oracle e acoplado ao seu SGBD

(2)

7

OLAP – Década de 80

1981 - System W da Comshare Primeiro a ter uma abordagem de hipercubo

Bem mais orientado ao desenvolvimento do usuário final e Sistemas Financeiros

Trouxe muitos conceitos hoje altamente adotados Metaphor

Também trouxe muitos conceitos adotados hoje

Computação client/server, processamento multidimensional em dados relacionais, processamento de grupos de trabalho, desenvolvimento OO. Problemas em hardware não-proprietário e GUI sem padronização. Comprado pela IBM em 1994

Final da década – integração com planilhas

8

OLAP – Década de 80

Microsoft Excel

Recurso de PivotTable – ferramenta OLAP mais

utilizada, não pelos recursos, mas pelo número de

usuários de Excel

Add-ins - oportunidade para aplicações de terceiros

para OLAP no Excel

Aplicações frontend proprietárias pouco

populares

Solução: integração com planilhas

9

Conceitos e Terminologia de

Data Warehousing

O que é Banco de Dados Multidimensional?

O que é Data Warehouse (DW)?

O que é Data Mart?

O que é OLAP?

10

Banco de Dados Multidimensional

B a n c o d e D a d o s M u lt iD i m e n s i o n a l B a n c o d e D a d o s M u lt iD i m e n s io n a l B a n c o d e D a d o s M u lt iD i m e n s io n a l P e n s e (c o n c e i tu a lm e n te ) e m u m B a nc o d e D a d os M u l tid im e n s i o n a l (B D M D ) c o m o u m A R R A Y g ig a n t e s c o B D M D (i ,j ,l ,m , n , o ,p ,r, s ,t, . .. , ) T a m a n h o M á x im o d o B D M D = im a x * jm a x * kma x * lm a x * ... P e ns e (c o n c e i tu a lm e n te ) e m um P e ns e (c o n c e i tu a lm e n te ) e m um B a n c o d e D a d o s B a n c o d e D a d o s M u l tid im e n s i o n a l (B D M D ) c o m o M u l tid im e n s i o n a l (B D M D ) c o m o u m A R R A Y g ig a n t e s c o u m A R R A Y g ig a n t e s c o B D M D (i ,j ,l ,m , n , o ,p ,r, s ,t, . .. , ) B D M D (i ,j ,l ,m , n , o ,p ,r, s ,t, . .. , ) T a m a n h o M á x im o d o B D M D = i T a m a n h o M á x im o d o B D M D = im a x m a x ** j jm a x m a x * k* km a x m a x * l* lm a x m a x * ...* ...

Banco de Dados Multidimensional

Leste Oeste Sul

Produto1 50 80 100 Produto2 40 70 80 Produto3 90 120 140 Produto4 20 10 30

BIDIMENSIONAL

Data Warehouse

“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

"a copy of transaction data

specifically structured for query and

analysis"

(3)

13

Data Warehouse

“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 sumarizados”.

-

Definição de DW da Oracle

14

Propriedades de um DW

Integrado Variante no Tempo Não-volátil Orientado a Assunto Data Warehouse 15

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 Financeiras dos Clientes 16

Integrado

• Os dados de um determinado assunto são

definidos e armazenados apenas uma vez

Data Warehouse Aplicações OLTP Cliente Poupança Contas Empréstimos 17

Variante no Tempo

• Os dados são armazenados como uma série de

fotografias, cada uma representando um período no tempo

Data Warehouse

18

Não-Volátil

• Os dados “não são atualizados” e nem

removidos

Warehouse Leitura Carga Operacional Inserção, Atualização, Remoção e/ou Leitura

(4)

19

Alterando os Dados em um DW

Bancos de Dados Operacionais Banco de Data Warehouse

Primeira Carga Atualizar Atualizar Atualizar Eliminar ou Arquivar 20

Características dos dados

Característica

Caracter

Caracteríísticastica DescriDescriçDescriçãoçãoão

Consolidado

Consolidado Centralizado em toda a empresaCentralizado em toda a empresa Consistente

Consistente Dentro do data warehouseDentro do data warehouse Orientado ao assunto

Orientado ao assunto Organizado à perspectiva do usuárioOrganizado à perspectiva do usuário Histórico

Histórico Snapshots no tempoSnapshots no tempo

Somente-leitura

Somente-leitura Não pode atualizarNão pode atualizar Resumido

Resumido Ao nível apropriado de detalheAo nível apropriado de detalhe

21

Comparativo

Análise Processos Atividades Operacional, Interna, Externa Operacional, Interna Fontes de Dados

Grande para Muito Grande Pequeno para grande Tamanho Assunto, tempo Aplicação Organização dos Dados Snapshots no tempo 30 – 60 dias

Natureza dos dados

Leitura DML

Operações

Segundos para horas Milisegundos para segundos Tempo de Resposta Data Warehouse OLTP Propriedade 22

O que é um Data Mart

O que é

Um subconjunto de um Data Warehouse

Específico a um assunto particular ou atividade de negócios Pode ser uma solução tática

Por que construir Data Marts

Consultas mais rápidas e menor número de usuários Tempo de implantação minimizado

Data Marts Integrados

Garantem dados consistentes Requerem planejamento prévio

Movendo dados de um Data Warehouse

para Data Marts

Vantagens

Campos compartilhados Fonte comum Processamento distribuído

Mart Mart SuporteSuporte

a a clientesclientes Mart Mart VendasVendas Data

Data

Warehouse

Warehouse Mart Mart FinanFinanççasas

Fonte 1FonteFonte11

Fonte 2FonteFonte22

Fonte 3FonteFonte33

Desvantagens Tempo mais longo de

desenvolvimento

Movendos dados de Data Marts para um

Data Warehouse

Data Data Warehouse Warehouse Mart Mart VendasVendas

Mart Mart FinanFinanççasas

Mart Mart SuporteSuporte

a a clientesclientes

Fonte 1FonteFonte11

Fonte 2FonteFonte22

Fonte 3FonteFonte33

Vantagens

Mais simples e rápido Dados específicos de

cada departamento

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

(5)

25

Comparativo

Meses Meses a anos Tempo de Implementação Poucas Várias Fonte de Dados Um único assunto Vários Assuntos Departamento Empresa Escopo Data Mart Data Warehouse Propriedade 26

Processamento OLTP

Sistemas OLTP

Muitas transações e curtas

Alto grau de concorrência

Atividades tendem a acontecer numa taxa

relativamente constante

Dados detalhados

Atualizações dos dados freqüentes

Bancos de dados refletem o estado atual dos dados

27

Processamento OLTP

Sistemas OLTP

Consultas geralmente ocorrem sobre um

pequeno conjunto de dados e são conhecidas

antecipadamente

Foco é armazenamento, confiabilidade,

desempenho e disponibilidade dos dados

28

Processamento OLAP

Sistemas OLAP

Projetados para

análise gerencial

Priorizam facilitar consultas e navegação na

estrutura dimensional

Dados são resumidos, históricos e baseados

em assuntos

29

Processamento OLAP

Sistemas OLAP

Consultas OLAP geralmente "ad-hoc" e acessam

grandes conjuntos de dados

Atualizações só em em "batch" e por causa disso

não há controle de concorrência

Dados não normalizados já que o foco do OLAP

está na recuperação e visualização e não no

armazenamento

30

(6)

31

Data Warehousing (DWing)

32

O processo de Data Warehousing

Data Marts Data Data Warehouse Warehouse Sistemas fonte OLTP Clientes Obter

Obterdadosdados PopularPopular PopularPopular ConsultarConsultar Transforma

Transformaççõesões Data WarehouseData Warehouse Data MartsData Marts DadosDados 1 1 1 2 2 2 3 3 3 444 555 33

Um Sistema de Data Warehousing

Dados no Ambiente OLAP Dados no Ambiente OLAP Data Marts Data Marts Data Warehouse Data Warehouse Dados de Sistemas Operacionais Dados de Sistemas Operacionais Compras Produção Contábil Dados da Empresa OLTP OLTP OLTP Vendas 34 Região Nordeste Total Cidade Olinda Recife Fortaleza Crato Maceió

Relatório mensal de vendas do nordeste – Maio/1999 Un. Vendidas 2.500 2.750 5.250 3.200 1.725 4.925 1.900 1.900 12.075 Vendas $ $12.850 $14.135 $26.985 $16.800 $ 9.143 $25.943 $ 9.595 $ 9.595 $62.523 Estado PE PE PE Totais CE CE CE Totais AL AL Totais E X E M P L O

Modelando um Data Warehouse

Comparando ambientes de modelagem

Operacional: OLTP

Operacional: 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 JOIN de

tabelas em consultas

(7)

37

Componentes de Modelagem do DW

Geográfica

Geográfica ProdutoProduto TempoTempoUnd.Und. $$ Tabelas de Dimensão Tabelas de Dimensão Geográfica Geogr Geográáficafica

Produto Produto Produto Tempo Tempo Tempo Tabela-Fato Medidas Medidas Fatos Fatos Fatos Dimensão Dimensão Dimensão 38

Esquema Estrela (Star)

Tabela Fato Dimension Table Dim_Tempo Dim_Tempo Dim_Tempo Chave_Tempo Chave_Tempo Data . . . Data . . . Fato_Vendas Fato_Vendas Chave_Tempo Chave_Funcionario Chave_Produto Chave_Cliente Chave_Entregador Chave_Tempo Chave_Funcionario Chave_Produto Chave_Cliente Chave_Entregador Qtd-Prod Valor-Total …. . . Qtd-Prod Valor-Total …. . . Dim_Funcionario Dim_Funcionario Dim_Funcionario Chave_Funcionario Chave_Funcionario Codigo_Funcionario . . . Codigo_Funcionario . . . Dim_Produto Dim_Produto Dim_Produto Chave_Produto Chave_Produto Codigo_Produto . . . Codigo_Produto . . . Dim_Cliente Dim_Cliente Dim_Cliente Chave_Cliente Chave_Cliente Codigo_Cliente . . . Codigo_Cliente . . . Dim_Entregador Dim_Entregador Dim_Entregador Chave_Entreegador Chave_Entreegador Codigo_Entregador . . . Codigo_Entregador . . . 39

Esquema Floco de Neve (Snowflake)

Tabelas-Dimensão Secundárias Tabelas-Dimensão Secundárias Fato_Vendas Fato_Vendas Chave_Tempo Chave_Funcionario Chave_Produto Chave_Cliente Chave_Entregador Chave_Tempo Chave_Funcionario Chave_Produto Chave_Cliente Chave_Entregador RequiredDate . . . RequiredDate . . . MarcaProduto MarcaProduto MarcaProduto CodigoMarca CodigoMarca CodigoCategoria CodigoCategoria Categoria Categoria Categoria CodigoCategoria CodigoCategoria Nome Nome Dim_Produto Dim_Produto Dim_Produto Chave_Produto Chave_Produto Nome Nome Tamanho Tamanho CodigoMarca CodigoMarca Tabela-Dimensão Principal 40

Escolhendo um schema

Star Star

Star SnowflakeSnowflakeSnowflake Clareza

Clareza + fácil+ fácil + difícil+ difícil

Número de tabelas

Número de tabelas << >>

Complexidade de consultas

Complexidade de consultas + simples+ simples + complexo+ complexo

Performance de consulta

Performance de consulta + rápido+ rápido + lento+ lento

41

Escolhendo granuralidade

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 à granuralidade estabelecida

Considerações de design

Usar medidas aditivas e numéricas

42

Definindo características de dimensões

Aplicando características a 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 faltando

Minimizar o número de registros que mudam ao longo do

(8)

43

Identificando Hierarquias de Dimensão

Hierarquia Consolidada Hierarquia Consolidada Local da Loja Continente Continente Continente País Pa Paííss Região Região Região Cidade Cidade Cidade Loja Loja Loja Hierarquia em Separado Hierarquia em Separado Lozal da Loja Lozal LozaldadaLojaLoja

Continente Continente Continente Continente Continente País País País Pa Paííss Região Região Região Região Região Cidade Cidade Cidade Cidade Cidade Loja Loja Loja Loja Loja 01 01 44

Definido dimensões

Dimensão tempo

Quebrar tempo em atributos individuais

Representar tempo como dias úteis, finais de

semana, feriados, ou períodos fiscais

Limitado a granuralidade da tabela fato

Dimensão geográfica

Dimensão de produto

Dimensão de cliente

45

Considerações de Performance

Tabelas fato muito grandes

Design do Star Schema

Tabelas fato – longas e estreitas Tabelas de dimensão – curtas e largas

Incluindo dados pré-calculados

Aumenta a performance das consultas mas aumenta o

tamanho de uma tabela fato.

Movendo colunas de uma tabela fato para outra tabela

Reduz o tamanho da tabela fato mas pode afetar

performance de consultas 46 Q4 Tempo Q1 Q2 Q3 Product Grapes Apples Melons Cherries Pears Location Atlanta Denver Detroit Sales Sales Product Grapes Apples Melons Cherries Pears Produto Uvas Maçãs Melões Cerejas Pêras Local Atlanta Denver Detroit Sales Sales Time Produto Local Dimensão Dimensão Q4 Q1 Q2 Q3 Uvas Maçãs Melões Cerejas Pêras Atlanta Denver Detroit Membro Membro Célula Célula Tempo

Tempo InícioInício Trimestre 1

Trimestre 1 1 deJulho1 deJulho Fim

Fim

39 de Setembro

Trimestre 2

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

Trimestre 3

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

Trimestre 4

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

Propriedades

Dia 1

Dia

Dia11 DiaDiaDia 222 ...

Jan.

Jan. Fev..FevFev.... ... 1998

1998

1998 199919991999 ...

Níveis

Características das tabelas

Tabelas fato

Uma tabela fato por cubo

Chaves e medidas

Medidas - colunas aditivas

Determinar nível de detalhe

Tabelas dimensão

(9)

49

Mapeamento Dados Operacionais →

Dados Multidimensionais

Fatos

Atributos quantitativos sobre o desempenho do negócio em um determinado ramo

Exemplo: sobre o fato

vendas, a quantidade vendida, o preço da venda, a margem de lucro, etc

Dimensões

Atributos qualitativos sobre os ramos do negócio envolvidos na medida de desempenho de determinado fato Exemplo: sobre a dimensão produto, a descrição, o código, o preço, etc 50

Dimensões Compartilhadas

Fatos

Dimensão 1 Dimensão 2 Dimensão 3 Dimensão 4

Fatos

Dimensão 5 Dimensão 6 51

Fatos x Fatos

Fatos Fatos

NÃO!!!!

Fatos

Dimensão 1 Dimensão 2

Fatos

Dimensão 5 Dimensão 6 52

Armazenamento do cubo

Explosão de dados

Regra 80-20

20% de todas as possíveis agregações provê 80%

de ganho de performance.

Formas de armazenamento

MOLAP – Multidimensional OLAP

ROLAP – Relational OLAP

HOLAP – Hybrid OLAP

53

Estrutura MOLAP

Data Warehouse ou Data Mart Data Warehouse ou Data Mart SGBD SGBD Cubo Cubo Dados MOLAP Agregações MOLAP

Armazena cópia da tabela fato e dimensões

Armazena agregações

54

Estrutura ROLAP

Data Warehouse ou Data Mart Data Warehouse ou Data Mart

Dados ROLAP Dados ROLAP Dados ROLAP

Agregações ROLAP Agrega AgregaççõesõesROLAPROLAP

SGBD SGBD

Agregações são armazenadas em tabelas relacionais Dados-base são mantidos no SGBD-fonte

Estrutura totalmente relacional.

(10)

55

Estrutura HOLAP

Data Warehouse ou Data Mart Data Warehouse ou Data Mart

Dados ROLAP Dados ROLAP Dados ROLAP SGBD SGBD Cubo Cubo Agregações MOLAP Dados-base mantidos no BD fonte Agregações calculadas e armazenadas no cubo 56 Perspectiva do Cliente Perspectiva

Perspectivado do ClienteCliente MOLAPMOLAPMOLAP HOLAPHOLAPHOLAP ROLAPROLAPROLAP Armazenamento Armazenamento Armazenamento Dados de base Dados de base Agregações Agregações Performance de consulta Performance de consulta Consumo em disco Consumo em disco Manutenção do cubo Manutenção do cubo MOLAP MOLAP MOLAP Cubo Cubo Cubo Cubo Imediato Imediato Alto Alto Alto Alto HOLAP HOLAP HOLAP Tabela Relacional Tabela Relacional Cubo Cubo Mais rápido Mais rápido Médio Médio Médio Médio ROLAP ROLAP ROLAP Tabela Relacional Tabela Relacional Tabela Relacional Tabela Relacional Rápido Rápido Baixo Baixo Baixo Baixo 57

Front-End de um DW

Ferramentas OLAP

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 Drill Across Slicing/Dicing 58

Relatório OLAP

Produto

Região

Vendas no

Mês

Comparação

com o Mês

Pasta Colgate

Sul

110 **12%

Pasta Colgate

Sudeste

179 -3%

Pasta Colgate

Nordeste

55 5%

Total

344 **6%

Relatório OLAP

P ro d u to R e g iã o T a m a n h o V e n d a s n o M ê s C o m p . C o m o M ê s A n te rio r C o lg a te S u l A 3 4 **1 0 % C o lg a te S u l B 3 6 **1 3 % C o lg a te S u l C 4 0 **1 1 % C o lg a te T o ta l 1 1 0 **1 2 % C o lg a te S u d e s te A 6 3 -2 .8 % C o lg a te S u d e s te B 6 0 -3 .1 % C o lg a te S u d e s te C 5 6 -2 .9 % C o lg a te T o ta l 1 7 9 -3 % C o lg a te N o rd e s te A 1 9 5 % C o lg a te N o rd e s te B 1 7 4 % C o lg a te N o rd e s te C 1 9 6 % C o lg a te T o ta l 5 5 5 % T o ta l 3 4 4 6 %

Relatório OLAP

P r o d u t o R e g i ã o E q u i p e d e V e n d a s V e n d a s n o M ê s C o m p . C o m o M ê s A n t e r i o r C o l g a t e S u l P . A l e g r e 5 2 * * 2 1 % C o l g a t e S u l C u r i t i b a 2 8 5 % C o l g a t e S u l F l o r i a n ó p . 3 0 6 % C o l g a t e 1 1 0 * * 1 2 % C o l g a t e S u d e s t e S ã o P a u l o 9 3 4 % C o l g a t e S u d e s t e R i o 7 5 5 % C o l g a t e S u d e s t e B e l o H o r i z . 1 1 - 1 5 % C o l g a t e 1 7 9 - 3 % C o l g a t e N o r d e s t e S a l v a d o r 2 1 5 % C o l g a t e N o r d e s t e F o r t a l e z a 1 8 4 % C o l g a t e N o r d e s t e R e c i f e 1 6 6 % C o l g a t e 5 5 5 % T o t a l 3 4 4 6 %

(11)

61

Tamanho de um DW

Um Cubo Quadridimensional

VENDAS 62

Tamanho de um DW

É medido pelo tamanho da Tabela de Fatos

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

Dimensão Loja: 300 lojas

Dimensão Produto: 30.000 produtos

63

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 produtos vendidos ao dia em cada

loja):

730 x 300 x 3000 x 1 = 657 milhões de

registros

64

Tamanho de um DW

Número de campos chaves = 4; Número de

campos fatos = 4; Total de campos = 8 (de 4

bytes, cada um)

Tamanho da tabela de fatos: 657.000.000 x 8 x

4 = 21GB

65

SQL para OLAP (Oracle)

SELECT p.produto#, d.trimestre, SUM(v.faturamento) FROM vendas v, data d, Produto p

WHERE v.produto# = p.produto#

AND v.data = d.data#

AND d.trimestre IN (‘2’, ‘3’)

AND d.ano = 1999

AND p.produto# IN (‘P1’, ‘P2’)

GROUP BY {CUBE,ROLLUP}(p.produto#, t.trimestre)

66

SQL para OLAP (Oracle)

MERGE INTO newemp n1 USING external_table e1 ON ( n1.empno = e1.empno ) WHEN MATCHED THEN UPDATE SET n1.ename = e1.ename, n1.job = e1.job

WHEN NOT MATCHED THEN INSERT (empno, ename, job) /