1 Data Warehouse Data Warehouse & Data Mart Suporte à Decisão X Operacional Banco de Dados Avançado Sistemas de Suporte à Decisão

(1)

Mestrado / Doutorado

Banco de Dados Fernando Fonseca

&

Robson Fidalgo

Sistemas de Suporte à Decisão

Sistemas de Suporte a Decisão (SSD)

Permitem armazenare 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

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)

Banco de Dados Avançado

Data Ware hous e

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 raramente modificadas (sobrescrita).

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 ?

Requerem planejamento prévio, para evitar ilhas de dados.

(2)

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

Data WarehousingWarehousing

→

→→

→ ^DW

Estrela ou F. Neve

→

→→

→

BD Op.BD

Op.BD Op.BD

Op.

→

DM DM DM

DM DM

OLAP Data Mining

Relatórios

Metadados produzidos em todas as etapas ETL

Extract Transform

Load

→

→→

→ Operational^ODS Data Store

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

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 DM DM

Data

Data Warehousing TopWarehousing Top--DownDown

→

→ →→→→

BD Op.BD Op.BD Op.BD

Op.

→

→→

→

Relatórios

Load

→

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

Data Warehousing BottonWarehousing Botton--UpUp

→

→→

→ →→→→

Op.

→

→→

→

Relatórios

Load

→

→→

DM DM DM

DW

Arquiteturas de Data Warehousing

Data Warehousing (Abordagem corrente).

Visão do DW Incremental Planejar Top-Down Desenvolver Botton-Up ODS Histórico

DW Data

Data Warehousing BottonWarehousing Botton--UpUp

→

→ →→→→

Op.

→

→→

→

Relatórios

ETL

Load

→

→→

DM DM DM

Arquiteturas de Data Warehousing

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) 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 fatose dimensões Processamento Analítico On-Line (OLAP)

Modelagem de Dados

(3)

Modelagem Dimensional

Geográfica

Geográfica ProdutoProduto TempoTempoUnd.Und. $$

Tabelas de Dimensão Tabelas de Dimensão

Geográfica Geográfica Geográfica

Produto Produto Produto

Tempo Tempo Tempo

Tabela Fatos

Medidas Medidas

Fatos Fatos Fatos Dimensão

Dimensão Dimensão

Componentes de Modelagem Dimensional

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 É normalmente desnormalizada (esquema estrela)

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

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!

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

Tabelas de fatos são esparsas

Modelos Dimensionais do DW

Tabela Fato Tabela Dimensão

Dim_Tempo Dim_Tempo Dim_Tempo Chave_Tempo Chave_Tempo Data. .. Data. ..

Fato_Vendas Fato_Vendas Chave_Tempo Chave_Empregado Chave_Produto Chave_Cliente Chave_Entregador Chave_Tempo Chave_Empregado Chave_Produto Chave_Cliente Chave_Entregador Data_Requerida .. . Data_Requerida .. .

Dim_Funcionario Dim_

Dim_FuncionarioFuncionario Chave_Funcionario Chave_Funcionario Codigo_Funcionario .. . Codigo_Funcionario .. .

Dim_Produto Dim_

Dim_ProdutoProduto Chave_Produto Chave_Produto Codigo_Produto .. . Codigo_Produto .. .

Dim_Cliente Dim_Dim_ClienteCliente Chave_Cliente Chave_Cliente Codigo_Cliente .. . Codigo_Cliente .. . Dim_Entregador

Dim_Dim_EntregadorEntregador Chave_Entreegador Chave_Entreegador Codigo_Entregador .. . Codigo_Entregador .. .

O esquema estrela (Star)

Modelos Dimensionais do DW

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_

Dim_ProdutoProduto Chave_Produto Chave_Produto NomeNome Tamanho Tamanho CodigoMarca CodigoMarca Tabela-Dimensão Principal

O esquema Flocos de Neve (Snowflake)

Normalizar as dimensões Kimball aconselha, devido a complexidade desta estrutura, não transformar o esquemas estrela em esquemas floco de neve (o ganho de espaço de armazenamento seria pouco relevante).

(4)

Escolhendo um schema

StarStar

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

Loja Tempo

Pr od ut o categoria

marca descrição

tipo endereço nome_loja ano

mes dia_do_mes flag_feriado

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

Mestrado / Doutorado Dim ensão Tem po

Chave_Tem po Mes Trim estre Ano

^ ^Tempo

Produto

∨

∨∨

∨

^ ^

∨

∨∨

∨

1996

1997 Trim1/96

Trim 2/96 Trim 3/96 Trim 4/96

Janeiro Fevereiro M arço

Cubo Multidimensional

Agregação em Níveis de Hierarquias

Como representar as dimensões no cubo?

Produto Loja Vendas AAAA F01 50 AAAA F02 60

AAAA 100

BBBB 40

BBBB 70

BBBB 80

CCCC 90

CCCC 120

CCCC 140

DDDD 20

DDDD 10

DDDD 30

F01

F01 F02

F02

F02 F03

F03

AAAA BBBB CCCC DDDD

F01 F02 F03

Cubo Multidimensional

Como representar as dimensões no cubo?

Produto Loja Vendas AAAA F01 50 AAAA F02 60

AAAA 100

BBBB 40

BBBB 70

BBBB 80

CCCC 90

CCCC 120

CCCC 140

DDDD 20

DDDD 10

DDDD 30

F01

F01 F02

F02

F02 F03

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 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

1999 2000 AAAA BBBB CCCC DDDD

F01 F02 F03

Cubo Multidimensional

1999 2000 AAAA BBBB CCCC DDDD

F01 F02 F03 1999 2000

AAAA BBBB CCCC DDDD

F01 F02 F03

Qual o total de vendas do produto AAAA?Exemplo de Consultas

Cubo Multidimensional

(5)

1999 2000 AAAA BBBB CCCC DDDD

F01 F02 F03 1999 2000

AAAA BBBB CCCC DDDD

F01 F02 F03

Qual o total de vendas da loja F03?

Exemplo de Consultas

Cubo Multidimensional

Qual o total de vendas do ano 1999?

1999 2000 AAAA BBBB CCCC DDDD

F01 F02 F03 1999 2000

AAAA BBBB CCCC DDDD

F01 F02 F03

Cubo Multidimensional

Qual o total de vendas do produto BBBB no ano de 1999 e na loja F2?

1999 2000 AAAA BBBB CCCC DDDD

F01 F02 F03 1999 2000

AAAA BBBB CCCC DDDD

F01 F02 F03

Cubo Multidimensional

Banco de Dados Avançado

OLA P

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..) Acessa vários registros

Tem alta performance e consultas fáceis e interativas Lida com dados históricos (dimensão temporal) Oferece visões multidimensionais (perspectivas)

OLAP

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?

Qual a média salarial dos funcionários de informática na região sul nos últimos 5 anos?

OLAP

(6)

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?

Qual a média salarial dos funcionários?

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 Relaciona l Dimensiona l 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

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

Acesso a registro Dezenas Milhares

OLAP Características OLTP

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.

Trabalha em ambientes dinâmicos e muitas dimensões

Arquiteturas OLAP

ROLAP

Data Warehouse ou Data Mart Data Warehouse ou Data Mart

Dados ROLAP Dados ROLAP Dados ROLAP

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

SGBD

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

Estrutura totalmente relacional.

Agregações ROLAP Agregações AgregaçõesROLAPROLAP Dados ROLAP Dados ROLAP Dados ROLAPDados ROLAP Dados ROLAP Dados ROLAP

Agregações ROLAP Agregações AgregaçõesAgregações ROLAPROLAPROLAP Agregações AgregaçõesROLAPROLAP SGBD

SGBDSGBD SGBD

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

Estrutura totalmente relacional.

MOLAP

Utiliza MDDB proprietários (com matrizes n- dimensionais) para manipular fatos agregados Principal premissa

Armazenar de forma multidimensional para visualizar de forma multidimensional Desvantagens

Não manipula fatos atômicos Não trabalha com muitas dimensões Não gerencia um grande volume de fatos

Arquiteturas OLAP

MOLAP

Data Warehouse ou Data Mart Data Warehouse ou

Data Mart

SGBD SGBD

CuboCubo

Dados MOLAP Agregações

MOLAP Armazena cópia da tabela fato e dimensões

Armazena agregações Data Warehouse ou

Data Mart Data Warehouse ou

Data Mart

SGBD SGBD Data Warehouse ou

Data Mart Data Warehouse ou

Data Mart

SGBD SGBD SGBD SGBD

CuboCubo

Armazena agregações CuboCubo

Armazena agregações

Arquiteturas OLAP

(7)

HOLAP

Mistura de MOLAP com ROLAP

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

Utiliza MDDB para analisar os fatos agregados Utiliza SQL para manipular fatos atômicos É mais complexo para administrar e implementar

Arquiteturas OLAP

HOLAP

SGBDSGBD

CuboCubo

Agregações MOLAP Dados-base mantidos

no BD fonte

Agregações calculadas e armazenadas no cubo Data Warehouse ou Data Mart

Data Warehouse ou Data Mart

Dados ROLAP Dados ROLAP Dados ROLAPDados ROLAP Dados ROLAP Dados ROLAPDados ROLAP Dados ROLAP Dados ROLAP

SGBDSGBD SGBDSGBD

CuboCubo

Agregações MOLAP Dados-base mantidos

no BD fonte

Agregações calculadas e armazenadas no cubo

Arquiteturas OLAP

Resumo sobre as Arquiteturas

Armazenamento Armazenamento Armazenamento

Dados de base Dados de base

Agregações Agregações

MOLAP MOLAP MOLAP

CuboCubo

HOLAP HOLAP HOLAP

Tabela Relacional

CuboCubo

ROLAP ROLAP ROLAP

Tabela Relacional

Perspectiva do Cliente Perspectiva

Perspectivado do ClienteCliente MOLAPMOLAPMOLAP HOLAPHOLAPHOLAP ROLAPROLAPROLAP

Performance de consulta Performance de consulta

Consumo em disco Consumo em disco

Manutenção do cubo Manutenção do cubo

Imediato Imediato

AltoAlto

Mais rápido Mais rápido

Médio Médio

Rápido Rápido

Baixo Baixo

Arquiteturas OLAP

Conclusão sobre as Arquiteturas ROLAP é mais flexível e mais geral MOLAP é para soluções específicas ou departamentais

HOLAP é supostamente a tendência

Arquiteturas OLAP

Desempenho x Armazenamento

BD1

Tempo de Resposta Calcular os agregados no momento

da recuperação ou armazená-los?

Quanto maior o número de agregados, melhor o desempenho e mais dados para serem

armazenados e gerenciados!

BD1

BD2

Desempenho x Armazenamento

(8)

BD1

BD2

BD2 BD3

BD3

Desempenho x Armazenamento

BD1

BD2

BD3

BD4

Desempenho x Armazenamento

Principais Operações OLAP

Drill Down e Rolll Up (ou Drill Up)

Técnica que possibilita caminhar pela estrutura multidimensional (hierarquias), permitindo ver diferentes níveis de detalhes

Drill Down Drill Down Drill Drill//Roll UpRoll Up

50.000 Clientes 50.000 Clientes 500 Cidades 500 Cidades 50 Estados 50 Estados 5 Regiões 5 Regiões 1 Total 1 Total

Drill Down e Roll Up (ou Drill Up) Drill Down

Drill Down

Drill Up Drill Up Loja Vendas

F01 200

F02 260

F03 350

Loja Tipo Vendas

F01 Luxo 50

Popular 40 Padrão 90 Franquia 20

F02 Luxo 60

Popular 70 Padão 120 Franquia 10

F03 Luxo 100

Popular 80 Padrão 140 Franquia 30

200

260 350 Principais Operações OLAP

Rotação

Técnica que gira o cubo, permitindo diferentes visões dos dados

4

2

3 4 x 2 x 3

3

2

4 3 x 2 x 4

4 3

2 4 x 3 x 2

Principais Operações OLAP

Slice and Dice

Técnica que fatia o cubo, permitindo restringir a análise aos dados, sem inversão de eixos

“Semelhante” a cláusula WHERE de SQL 4

2

3 4 x 2 x 3

4

2

2 4 x 2x 2

2

3 2x 2x 3

Principais Operações OLAP

(9)

DW e OLAP

Grupo de DW da UFRJ

(http://genesis.nce.ufrj.br/dataware/)

Data Warehouse Brasil (http://www.dwbrasil.com.br/) Artigos de Kimball

(http://www.ralphkimball.com/html/articles.html) Data Warehousing Institute

(http://www.dw-institute.com/) OLAP Report

(http://www.olapreport.com/)

1 Data Warehouse Data Warehouse & Data Mart Suporte à Decisão X Operacional Banco de Dados Avançado Sistemas de Suporte à Decisão

Sistemas de Suporte à Decisão

Suporte à Decisão X Operacional

Banco de Dados Avançado

Data Ware hous e

Data Warehouse

Data Warehouse & Data Mart

DW & Data Warehousing

ETL e ODS

Arquiteturas de Data Warehousing

Arquiteturas de Data Warehousing

Arquiteturas de Data Warehousing

Modelagem de Dados

Modelagem Dimensional

Tabelas de fatos e de dimensões

Fatos e dimensões

Fatos e dimensões

Modelos Dimensionais do DW

Modelos Dimensionais do DW

Escolhendo um schema

Abstração do DW

Cubo Multidimensional

Cubo Multidimensional

Cubo Multidimensional

1999 2000 AAAA BBBB CCCC DDDD

F01 F02 F03 1999 2000

AAAA BBBB CCCC DDDD

F01 F02 F03

Cubo Multidimensional

1999 2000 AAAA BBBB CCCC DDDD

F01 F02 F03 1999 2000

AAAA BBBB CCCC DDDD

F01 F02 F03

Cubo Multidimensional

1999 2000 AAAA BBBB CCCC DDDD

F01 F02 F03 1999 2000

AAAA BBBB CCCC DDDD

F01 F02 F03

Cubo Multidimensional

1999 2000 AAAA BBBB CCCC DDDD

F01 F02 F03 1999 2000

AAAA BBBB CCCC DDDD

F01 F02 F03

Cubo Multidimensional

Banco de Dados Avançado

OLA P

OLAP

OLAP

OLTP

OLAP X OLTP

Arquiteturas OLAP

Arquiteturas OLAP

Arquiteturas OLAP

Arquiteturas OLAP

Arquiteturas OLAP

Arquiteturas OLAP

Arquiteturas OLAP

Arquiteturas OLAP

Desempenho x Armazenamento

Desempenho x Armazenamento

Desempenho x Armazenamento

Desempenho x Armazenamento

Principais Operações OLAP

200

260

350 Principais Operações OLAP

Principais Operações OLAP

Principais Operações OLAP

Referências