Integração de Dados e DW
Banco de Dados Multidimensionais (Data Warehouse e OLAP)
Por:
Robson do Nascimento Fidalgo
rdnf@cin.ufpe.br
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
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.
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 Warehousing Warehousing
→
→
→
→ DW
Estrela ou F. Neve
→
→
→
→
BD Op. BD
Op. BD Op. BD
Op.
→ →
→ →
DM DM DM
DM DM
DM DM
OLAP
Data Mining Relatórios
Metadados produzidos em todas as etapas ETL
Extract
TransformLoad
→
→ →
→
OperationalODS
Data StoreETL 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 Top Warehousing Top - - Down Down
→ →
→ → → → → →
BD Op. BD
Op. BD Op. BD
Op.
→ → →
→
OLAP
Data Mining Relatórios
Metadados produzidos em todas as etapas ETL
Extract
TransformLoad
→ →
→ →
OperationalODS
Data StoreArquiteturas 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
Data Warehousing Botton Warehousing Botton - - Up Up
→ →
→ → → → → →
BD Op. BD
Op. BD Op. BD
Op.
→ → →
→
OLAP
Data Mining Relatórios
ETL
Extract
TransformLoad
→ →
→ →
OperationalODS
Data Store→
→ →
→
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 Botton Warehousing Botton- -Up Up
→
→
→
→ → → → →
BD Op. BD
Op. BD Op. BD
Op.
→
→ →
→
OLAP
Data Mining Relatórios
Metadados produzidos em todas as etapas ETL
Extract
TransformLoad
→
→
→
→
OperationalODS
Data Store→ → →
→
DM
DM
DM
• 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 fatos e dimensões Processamento Analítico On-Line (OLAP)
Modelagem de Dados
Modelagem Dimensional
Geográfica
Geográfica ProdutoProduto TempoTempo Und.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
FatosFatos Fatos DimensãoDimensã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_FuncionarioDim_Funcionario
Chave_Funcionario Chave_Funcionario
Codigo_Funcionario
.. .
Codigo_Funcionario
.. .
Dim_Produto Dim_ProdutoDim_Produto Chave_Produto Chave_Produto
Codigo_Produto ..
.
Codigo_Produto ..
.
Dim_Cliente Dim_ClienteDim_Cliente
Chave_Cliente Chave_Cliente
Codigo_Cliente
.. .
Codigo_Cliente
.. . Dim_Entregador
Dim_EntregadorDim_Entregador
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 NomeNome
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
Escolhendo um schema
Star Star
Star Snowflake Snowflake Snowflake 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
Lo ja Tempo
P r o d u t o
categoria marca descrição
tipo
endereço nome_loja ano
mes
• 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
Dimensão Tempo
Chave_Tempo Mes
Trimestre Ano
^ Tempo
Produto
∨ ∨ ∨
∨
^ ^
∨ ∨
∨ ∨
∨
∨
∨
∨
∨ ∨
∨ ∨
∨
∨
∨
∨
1996
1997
Trim1/96
Trim2/96 Trim3/96 Trim4/96
Janeiro Fevereiro
Març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
F01 F02
F02
F02 F03
F03
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
F01 F02
F02
F02 F03
F03
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 DDDD F02 2000 10
1999 2000 AAAA BBBB CCCC DDDD
F01 F02 F03
Cubo Multidimensional
1999 2000 AAAA
BBBB CCCC DDDD
1999 2000 AAAA
BBBB CCCC DDDD
Qual o total de vendas do produto AAAA?
Exemplo de Consultas
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 da loja F03?
Exemplo de Consultas
Cubo Multidimensional
Qual o total de vendas do ano 1999?
1999
2000 AAAA
BBBB CCCC DDDD
1999
2000 AAAA
BBBB CCCC DDDD
Exemplo de Consultas
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 Exemplo de Consultas
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
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 Relacional Dimensiona l
Op. com os dados Incl., Alt, Exc l e Cons. Carga e Consulta Atualização Contínua (tempo real) Periódica (Bach)
N
ode usuários Milhares Dezenas
Tipo de usuário Operacional 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
SGBDSGBD
Agregações são armazenadas em tabelas relacionais
Dados-base são mantido no SGBD-fonte
Estrutura totalmente relacional.
Data Warehouse ou Data Mart
Data Warehouse ou Data Mart
Dados ROLAP Dados ROLAP Dados ROLAP
Agregações ROLAP Agregações
AgregaçõesROLAPROLAP Dados ROLAP
Dados ROLAP Dados ROLAPDados ROLAPDados ROLAP Dados ROLAP
Agregações ROLAP Agregações
AgregaçõesAgregaçõesAgregações ROLAPROLAPROLAP AgregaçõesROLAPROLAP
SGBDSGBDSGBD 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
SGBDSGBD
Cubo
CuboDados 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
SGBDSGBD
Data Warehouse ou Data Mart
Data Warehouse ou Data Mart
SGBDSGBDSGBD SGBD
Cubo
CuboDados MOLAP Agregações
MOLAP
Armazena cópia da tabela fato e dimensões
Armazena agregações
Cubo
CuboDados MOLAP Agregações
MOLAP
Armazena cópia da tabela fato e dimensões
Armazena agregações
Arquiteturas OLAP
• 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
Data Warehouse ou Data Mart Data Warehouse ou Data Mart
Dados ROLAP Dados ROLAP Dados ROLAP
SGBDSGBD
Cubo Cubo
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 ROLAPDados ROLAP Dados ROLAPDados ROLAPDados ROLAP Dados ROLAP
SGBDSGBDSGBD SGBD
Cubo Cubo
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
CuboCubo
HOLAP HOLAP HOLAP
Tabela Relacional
Tabela Relacional
CuboCubo
ROLAP ROLAP ROLAP
Tabela Relacional
Tabela Relacional
Tabela Relacional
Tabela Relacional
Perspectiva do Cliente Perspectiva
Perspectiva do 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
AltoAlto
Mais rápido Mais rápido
Médio Médio
Médio Médio
Rápido Rápido
Baixo Baixo
Baixo Baixo
