Projeto SGCF/RISQ

Workshop 1

Modelagem Multi-Dimensional:
Conceitos e Técnicas

© 2006 Kybernetics. Diretos Reservados.

© 2006 Pentaho Corporation. All Rights Reserved.
Sumário

Soluções Analíticas, OLAP e Data Warehouse

Técnicas de Modelagem de Data Warehouse
Exemplos de Modelagem
Exercícios de Modelagem aplicados ao projeto
Objetivos de Aprendizagem
• Conhecer os conceitos, terminologias e técnicas básicas de modelagem
multi-dimensional e de projeto lógico de data warehouse
• Desenvolver a especificação inicial do modelo lógico de data warehouse
para o projeto SGCF/RISQ
Problemática
“já apuramos nossos resultados pela contabilidade” - sistema de mensuração
de performance puramente financeiro
“informações críticas demoram a chegar...”
“como eu não fiquei sabendo disto antes?” – falta de monitoramento dos
processos chave
“cada setor apresenta um número diferente na reunião de resultados” – falta
de visão integrada
“os setores desconhecem ou confundem as metas e resultados de outros
setores” – falta de visão sistêmica
leva-se dias para elaborar um demonstrativo amplo de resultados
muitas pessoas elaborando suas “planilhas de resultados” – administração da
informação ineficiente (e muitas vezes ineficaz)
Causas

falta de integração de informações

falta de visão sistêmica
pouca qualidade, confiabilidade e disponibilidade da informação
Comparando Tipos de Soluções
Tipo de Aplicação Foco Manipulação de Dados Interatividade Exemplos
Solução Analítica Mensuração do Examina dados em Altamente Análise de Vendas,
(OLAP) Desempenho do agregados no interativa Financeira
Negócio tempo Painéis de Indicadores
(dashboards)
Solução Transacional Executar processo de Modifica o estado Altamente ERP
(OLTP on-line Negócio corrente dos dados (no interativa CRM
transaction grão de detalhe)
processing)

Relatórios Suporte a decisões e Mostra dados atuais Estático Relatórios de exceção

Operacionais exceções em processos (estado corrente) de Lista de tarefa/produção
de negócio modo detalhado diárias
Romaneios, Inventários,
etc

Mineração de Dados Identificar fatores de Utiliza dados atômicos Embutida Segmentação de Clientes
(Data Mining) predição de para reconhecimento (pouca Melhor oferta para clientes
desempenho e de padrões e execução interatividade) Detecção de Fraudes
algoritmos (modelos) dos modelos Análise (alta, Scoring de risco
para predição especializada)
O que é OLAP?

Enxergar os dados de forma

“dimensional”
e.x. Vendas por região, por canal, por
período de tempo

Navegar e explorar
Análise Ad Hoc
“Drill-down” (descer) de ano para mês,
dia
“Pivot” (modificar eixos)
Selecionar membros específicos para
análise

Interatividade com alto desempenho

Tecnologia otimizada para resposta
interativa
Características de Soluções Analíticas

Foco na Informação: Projetada para investigação e exploração dos

dados, não transacional
Interativa: Aceita e responde a consultas “ad-hoc” de usuários
Agregação Dinâmica: Resumo de dados detalhados em tempo-real
Drilling: Habilidade de movimentar níveis de granularidade de dados
Slice and Dice: Habilidade de combinar e re-combinar várias dimensões
para revelar diferentes facetas da informação
Pivot: Habilidade de oferecer comparações, revelar padrões e
relacionamentos e analizar tendências
Desempenho: Acesso a dados e manipulações devem responder em
“tempo-real”
 Arquitetura Típica de uma Solução OLAP
Ambiente Analítico
Dados Fonte
Base de Dados Analítica (DW)
ETL
Database1

SQL MDX
Servidor Cliente
Estruturas OLAP OLAP
Arquivos Dimensionais
Database2
de Usuários
Extração
Repositório
Arquivos de ETL
Texto

• Dados fonte podem vir de data warehouses pré-existentes ou

diretamente de fontes externas ou OLTP
• Dados fonte podem ser extraídos diretamente ou em lotes (batch-mode)
com processamento intermediário (staged)
Componentes da Arquitetura
Componente Papel na Arquitetura
Dados Fonte Ingredientes para toda a análise
ETL “Extrair” dados das fontes
“Transformar” os dados através de limpeza, formatação e integração
“Carregar” os dados em um BD analítico otimizado
Banco de dados SGBD que armazena as estruturas dimensionais de dados (star
analítico (data schemas)
warehouse) Otimizado para acesso OLAP
Servidor OLAP Processa consultas MDX, returnando resultados (result sets) multi-
dimensionais.
Dispara consultas SQL ao SGBD analítico
Caching e agregação otimizados para desempenho
Gerenciamento de estados e sessões de usuário
Cliente OLAP Ferramenta de usuário final, com funções gráficas, pivot, drill, …
Geração dinâmica de consultas MDX com base nos pedidos (interação)
do usuário
Geralmente possui uma metáfora de “planilha dinâmica” na interface
gráfica
Servidores RDBMS vs OLAP em detalhe

RDBMS proporciona Servidor OLAP proporciona

Armazenagem de Dados Visão dimensional de dados
Execução de consultas SQL Interpretação de MDX
Ordenação, correlação e agregação Geração de SQL
em larga escala Caching
Ponto de integração de todas as Cálculos de alto-nível
ferramentas de BI Reconhecimento de agregações
(aggregate awareness)
Objetivos de um Datawarehouse

Tornar a informação de fácil acesso

Apresentar as informações de modo consistente
Deve ser adaptativo e flexível à mudança
Deve proteger as informações
Base para um processo decisório mais eficaz
Elementos Básicos de um Data Warehouse

Área de Preparação Área de Apresentação Ferramentas

Sistemas dos Dados (staging area) dos Dados de Acesso
Operacionais
Fonte
Serviços: limpeza, Data Mart 1 Consultas e navegação
combinação e padronização DIMENSIONAL ad-hoc
das dimensões Dados atômicos e sumarizados
compartilhadas Baseados em um processo de
negócio Relatórios
SEM SERVIÇOS DE
CONSULTA
Aplicações Analíticas
Armazenamento de dados: Dimensões e
Fatos
arquivos “flat” e tabelas
compartilhados
relacionais Modelagem:
Predição/previsão
Processamento: ordenado e Scoring
sequencial Data Mart 2
Mineração de Dados
...

Extração Carga Acesso

Elementos Básicos de um Data Warehouse
Sistemas Operacionais Fonte

Área de Preparação dos Dados (staging area)

Apresentação dos Dados

Ferramentas de Acesso

Meta-dados

Armazenagem Operacional dos Dados (ODS)

Sistemas Operacionais Fonte

Armazenam as transações relacionadas aos processos de negócio;

Estão fora de controle do data warehouse;
São constituídos por sistemas corporativos cuja ênfase encontra-se em
armazenar transações, dificultando uma navegação amigável por
usuários de negócios.
Área de Preparação dos Dados
Composta por uma área de armazenamento e um conjunto de processos
de ETL.
Tudo que estiver entre os sistemas operacionais e a área de
apresentação dos dados.
Não deve oferecer serviços de consulta.
Podem ser usadas estruturas normalizadas.
Apresentação dos Dados
É o local onde os dados são organizados, armazenados e disponibilizados
para consulta.
É composta por uma série de data marts integrados, representando
processos de negócios.
Os dados devem ser dimensionais, atômicos e aderentes a arquitetura de
barramento (bus matrix).
Ferramentas de Acesso

Front-end dos sistemas de BI

Navegadores multi-dimensionais (Jpivot)
Geradores de Relatórios
Painéis de Controle (Dashboards)
...
Metadados

Todas as informações relativas ao ambiente do data warehouse que não

estão diretamente armazenadas nos sistemas operacionais.
Exemplos:
Definição de tipos de dados;
Nomes de tabelas e campos;
Parâmetros de bancos e outras fontes;
Regras de transformação;
Regras de formatação

Sempre utilize nomes ou definições amigáveis

Armazenagem Operacional dos Dados
(ODS)
Áreas de armazenagem utilizadas para a execução de relatórios
operacionais.
Frequentemente atualizadas e integradas aos dados operacionais.
Pode existir de maneira independente do DW (ou relacionada a este)
Não pode se sobrepor ao DW.
Vocabulário (1)
Tabela Fato Vendas Diárias
Tabela Fato
Chave Data (FK)
Uma linha corresponde a uma Chave Produto (FK)
medida Chave Loja (FK)
Todas as medidas de uma tabela Quantidade Venda
fato devem ter a mesma Valor Venda
granularidade
As medidas em uma tabela fato
devem ser numéricas e aditivas
(de preferência)
Expressam as múltiplas relações
entre dimensões em modelos
multi-dimensionais
Vocabulário (2)
Tabela Dimensão Produto
Tabelas Dimensionais
Chave Produto (PK)
São os pontos de entrada para Descrição Produto
uma tabela fato Número SKU
Atributos robustos permitem Marca
capacidades de navegação Categoria
analítica Peso
Unidade de Medida do Peso
Dimensões caracterizam a forma Tipo de Armazenagem
como o usuário poderá navegar e ...e muitas outras
consultar a base de dados multi-
dimensional
Desenho Multi-Dimensional

Geografia
Empregado
Produto

Fato Vendas

Cliente Tempo

Modelos dimensionais são chamados de Star-Schemas

Tabela Fato Tabela Dimensão

Uma tabela Fato contém itens que você deseja Dimensões são os modos pelo qual você olha os
mensurar. Por exemplo: dados. Por exemplo:
Lucro Por cliente
Quantidade de vendas Por data
Preço Médio Por produto

Medidas (Metricas) são valores que medem o Dimensões fornecem os parâmetros de

desempenho dos processo de negócios navegação e geração de relatórios
Mitos sobre a Modelagem Multi-Dimensional

▪ Modelos multi-dimensionais e data-marts servem somente para

sumarizar dados
▪ Modelos multi-dimensionais e data-marts pertencem a soluções
departamentais, e não corporativas
▪ Modelos multi-dimensionais e data-marts não são escalonáveis
▪ Modelos multi-dimensionais e data-marts são somente apropriados
quando existe um padrão previsível de uso
▪ Modelos multi-dimensionais e data-marts não podem ser integrados
▪ Modelos multi-dimensionais e data-marts só podem ser implementados
com softwares de alto custo
Erros a Serem Evitados

▪ Se enamorar com a tecnologia e com os dados, perdendo o foco nos

objetivos e necessidades de negócio
▪ O projeto não possui um líder e patrocinador
▪ Espírito megalomaníaco – desenvolver um projeto galático ao invés de
buscar implementações iterativas, interativas e mais fáceis de
administrar
▪ Alocar energia para construir uma estrutura de dados normalizada,
esquecendo de como os dados serão apresentados
▪ Dar maior atenção a questões de desempenho e facilidade de
desenvolvimento do que aspectos ligados a apresentação e facilidade de
uso
Erros a Serem Evitados

1. Desenhar uma arquitetura de dados que não previlegie a integração e

conformidade (compartilhamento) das dimensões
2. Carregar somente dados sumarizados no data warehouse

3. Presumir que os processos de negócios, seus requisitos e análises, e os

dados que os suportam sejam estáticos
4. Esquecer que o sucesso de um data warehouse está diretamente ligado
à aceitação dos usuários (gestores do negócio)
Processo de Desenho das Dimensões

▪ Selecione o processo de negócio a ser modelado

▪ Defina a granularidade do processo de negócio
Exemplos: uma linha individual de cada item de um pedido, um cartão
individual de embarque (companhia aérea), uma fotografia (snapshot) diária
dos níveis de inventário para cada produto em um armazém, uma fotografia
mensal de cada conta bancária
A granularidade especifica exatamente o que uma linha da tabela fato
representa

▪ Escolha as dimensões que se aplicam a cada linha da tabela fato

▪ Identifique os fatos numéricos que iram popular cada linha da tabela
fato
Processo de Desenho das Dimensões

Requisitos de
Negócio

▪ Processo de negócio
▪ Granularidade
▪ Dimensões
▪ Fatos

Realidade dos
Dados
Exemplo de Modelagem: Varejo

Imagine que trabalhamos no centro administrativo de uma grande loja de

varejo
O negócio tem 100 lojas distribuídas por mais de 5 estados da federação
Cada uma das lojas possui diferentes departamentos, que incluem
Padaria, Alimentos Congelados, Derivados de Leite, Bebidas, Flores,
Produtos de Limpeza e Higiene
Cada loja tem aproximadamente 60.000 produtos nas prateleiras
Os produtos individualizados são chamados de SKU (stock keeping units)
Cerca de 55.000 dos SKUs vem de fornecedores externos e possuem
códigos de barras nas embalagens
Estes códigos de barras são chamados UPC (universal product code)
Exemplo de Modelagem: Varejo

A granularidade dos SKUs e UPCs são as mesmas

Os restantes 5.000 SKUs vêm de departamentos tais como açougue,
peixaria e padaria
Estes produtos não possuem UPCs, mas somente SKUs
Os dados são coletados em diferentes lugares das lojas
Os dados mais úteis são coletados nas caixas registradoras, através de
leitura de códigos de barras pelo sistema POS (ponto de venda)
A loja aplica frequentemente promoções sobre produtos
Passo 1: Selecionar o Processo de Negócio

O mais básico modelo dimensional deve ser aquele com maior impacto
Deve responder às questões mais relevantes para o negócio e que
possam ser facilmente acessíveis para extração de dados
No nosso caso: o principal objetivo é entender o comportamento de
compra dos clientes
O processo que iremos modelar é a venda por POS (ponto de venda)
Analisar que produtos estão sendo vendidos em que lojas, em que dias, sobre
quais condições promocionais
Passo 2: Declarar a Granularidade

Que nível de detalhe deve ser incluído no modelo dimensional?

Dica: desenvolver o modelo dimensional para a informação mais atômica
capturada por um processo de negócio
O dado atômico é a mais detalhada informação coletada, não podem ser
sub-divididos
Em nosso exemplo, o dado de granularidade mais detalhada é o item de
uma transação do POS (ou seja, um item do cupom fiscal)
Passo 3: Escolha as Dimensões

A partir da granularidade escolhida, as seguintes dimensões ficam

logicamente definidas:
Data da Venda
Produto
Loja
Promoção

Se alguma dimensão que for considerada violar a granularidade da tabela

fato, exigindo que mais linhas sejam geradas, será necessário rever o
projeto para acomodar esta necessidade
Passo 3: Escolha as Dimensões

Dimensão Promoção
Dimensão Produto
Chave Promoção (PK)
Chave Produto (PK)
Atributos da Promoção..
Atributos do Produto..
Fato Transação de
Venda
Chave Data (FK) Dimensão Data
Dimensão Loja Chave Produto (FK)
Chave Loja (FK) Chave Data (PK)
Chave Loja (PK)
Chave Promoção (FK) Atributos da Data..
Atributos da
Loja.. Número da transação
Fatos...
Passo 4: Identificar os Fatos

Dimensão Promoção
Dimensão Produto Fato Transação de
Venda Chave Promoção (PK)
Chave Produto (PK)
Atributos da Promoção..
Atributos do Produto.. Chave Data (FK)
Chave Produto (FK)
Chave Loja (FK)
Chave Promoção (FK) Dimensão Data
Dimensão Loja Número da transação
Quantidade Venda Chave Data (PK)
Chave Loja (PK)
Valor Venda Atributos da Data..
Atributos da
Loja.. Custo da Venda
Lucro Bruto Venda
Passo 4: Identificar os Fatos

Existem dois tipos de medidas:

Aditivas – podem ser somadas, e os valores permanecem coerentes/válidos
para qualquer subconjunto de análise. Ex: Quantidade Vendas, pode ser
somado por loja e por período.
Não aditivas – representadas geralmente em termos de percentagens e
relações. Tanto o numerador quanto o denominador devem ser armazenados
na tabela fato. Para qualquer subconjunto de análise, o valor é calculado em
termos da relação de somas, e não de soma das relações. Exemplos:
Margem de Lucro, calculada pela relação entre as somas de Lucro Bruto e as somas
de Valor Venda.
Saldo de estoque, o qual não pode ser somado no tempo.

É indicado realizar uma estimativa do número de linhas da tabela fato, de

modo a determinar requisitos de projeto.
Discussão de Exemplos de Tabelas de
Dimensão: Dimensão Data
Dimensão Data Data warehouses sempre
Chave Data (PK)
necessitam de uma tabela de
Data
Descrição da Data Dimensão Data explícita
Dia da Semana
Semana do ano
Semana do mês
Número do Mês Adicionar todas as informações que
Descrição do Mês serão necessárias para os
Trimestre
Ano diferentes tipos de análises
Indicador Fim de Semana
Indicador Feriado
...
Exemplos de Tabelas de Dimensão: Dimensão
Produto

Código Produto Descrição do Produto Marca do Produto Departamento

10052300 Caldo de Galinha Knorr Mercearia

78552002 Iogurte Natural Batavo Derivados do leite

39980026 Barra de Chocolate Garoto Doces

Branco 250g
58890004 Suco de Laranja 1L Petry Bebidas

87700125 Pão Francês 50g Própria Padaria

Exemplos de Tabelas de Dimensão: Dimensão
Produto
Dimensão Produto
A dimensão armazena todos os
Chave Produto (PK)
Código Produto valores de cada item (ex.
Descrição do Produto
Marca
hierarquia de marca) de modo
Departamento normalizado
Peso
Tipo de Estocagem
...
Visões de Data Marts

Cada atributo de uma dimensão é uma fonte para restringir e construir

consultas sobre a tabela fato
“Drilling-down” é o processo de incluir cabeçalhos de linhas na análise
“Drilling-up” é o processo de excluir cabeçalhos de linhas na análise
Visões de Data Marts
Departamento Valor Venda Quantidade Venda

Mercearia ... ..

Derivados do leite .. ...

Drilling down Drilling up

Departamento Marca do Produto Valor Venda Quantidade Venda

Mercearia Knorr ... ..

Derivados do leite Batavo .. ...

Drilling down Drilling up

Departamento Conteúdo de Valor Venda Quantidade Venda

Gordura

Mercearia Normal ... ..

Derivados do leite Light .. ...

Dimensões Degeneradas

Uma dimensão que pode ser representada por um simples atributo

Exceto quando o tipo de dados for muito largo, estas dimensões são
armazenadas como uma coluna na tabela fato
Ex.
Número da fatura
Número do pedido
Número do contrato
Hierarquias e Níveis

Hierarquias e Níveis
Hierarquias podem existir em uma dimensão para servir de “drill” pré-definido
Uma hierarquia é composta de um ou mais níveis
Uma dimensão pode ter uma ou mais hierarquias

Propriedades
Cada nível da hierarquia da dimensão deve possuir um atributo primário que
“define” este nível
Atributos adicionais (propriedades) podem existir como descritores de cada
nível
Modelo Dimensional vs. 3NF (desnormalizado)
Dimensional 3NF

Melhor desempenho de consulta Desempenho transacional

Agregação Dinâmica Armazenagem e recuperação de
Análise de séries históricas detalhes

Dados não voláteis (transformação Tipo Compactação de armazenagem

2) histórica
Dados voláteis
Modelos Dimensionais Estrela vs. Floco de Neve
(Snowflake)
Estrela (Star) Floco de Neve (Snowflake)

Todos os níveis dimensionais contidos Os níveis dimensionais são

em uma tabela normalizados em tabelas separadas
Introduz redundância de dados
Elimina a redundância de dados
Consulta e indexação simplificada
Reutilização de dimensões de mais
Geralmente o método preferido
alto nível em agregados é
simplificada
Exemplo de Snowflake

Day Prodid Units Dollars Payment Custid

Mfr Mfrid Brand Prodid

Mfrid
name
Mfr
Product
Sales

Year Qtr Month Day

State City Custid

Time

Customer

Dimensão Fabricante é “snow-flaked”

Dimensões Compartilhadas (Conformed)
Dimensões compatilhadas são aquelas compartilhadas entre vários esquemas estrela
Permite projeto de bases de dados analíticos escalonáveis
Permite a agregação e análise através de tópicos relacionados em diferentes data marts

Geografia
Empregado
Cliente
Fato Vendas

Produto
Time

Fato Inventário

Armazém
Chaves Primárias

Recomendamos fortemente o uso de chaves primárias técnicas dentro do

modelo dimensional ao invés de códigos operacionais.
Chaves técnicas são conhecidas como surrogate keys ou technical keys.
Em geral, números inteiros sequenciais são escolhidos.
Possibilitam:
Melhor controle;
Melhor desempenho nas consultas.
Registrar condições para os quais não existem códigos operacionais,
evitando valores nulos na tabela fato.
Servem de suporte para armazenagem de históricos.
Isto não impede o armazenamento de códigos estruturados operacionias.
Ex. [Link]-1234/00-0
Exercício I

Descreva os quatro passos de construção de um modelo dimensional

para o processo de venda de financiamentos do Banco Pecúnia,
identificando dimensões, medidas, etc.
Tabelas Fato

Existem diferentes maneiras de representar os fatos em relação ao

tempo
Fatos transação (“Transaction fact”) – cada transação individual é
armazenada em um registro na tabela fato.
Fotografia periódica (“Periodic Snapshot”) – cada período é sumarizado
em um registro na tabela fato.
Fotografia acumulada (“Accumulating Snapshot”) – todos os períodos são
sumarizados em um registro na tabela fato.
Tabelas Fato e Análises no Tempo

t0 t1 t2 t3 t4

t0 t1 t2 t3 t4
Projeto Integrado

Fato Transação Dimensão Promoção

Dimensão Produto
...

Fotografia Dimensão Data

Dimensão Loja Periódica
...

Fato Transação
...
Arquitetura Integrada

Uma forma racional de decompor o problema em subproblemas menores

e mais fáceis de projetar;
Integração dos diferentes processos com as várias dimensões criadas;
Compartilhamento das mesmas dimensões por várias tabelas fato.
Matriz de Integração

DIMENSÕES COMUNS

to
en
a

m
at

ci
/D

le

na
o

o
po

be

ut

ui
od
m

ta

aq
su
PROCESSOS DE NEGÓCIO

Te

Es

Pr

In

M
Produção X X X X X
Estoque X X X
Embarque X X X
Custo Contábil X X
Matriz de Integração

As linhas correspondem aos data-marts

As colunas correspondem a dimensões comuns
A criação da matriz é um dos principais fatores de sucesso da
implementação do data warehouse
Ferramenta de gestão do projeto, bem como de comunicação e projeto
técnico
Exercício II

Desenhe a matriz de integração para o conteúdo do exercício anterior.

Como Implementar Mudanças em Dimensões
Se o valor de um atributo é modificado no contexto operacional, qual
será o efeito em nosso modelo dimensional?
Várias abordagens de implementação:
Tipo 1: Sobrescrever o valor;
Tipo 2: Adicionar uma linha;
Tipo 3: Adicionar uma coluna.
Tipo 1: Sobrescrever o Valor
Nova informação sobrescreve a informação desatualizada.
A informação desatualizada não é salva, ela é perdida!!!
Recomendada para aplicações que não necessitem de históricos sobre
este atributo.
Tipo 2: Adicionar uma Linha
Uma nova linha na dimensão é criada quando um atributo for modificado.
A informação desatualizada não é perdida, permanece como um
elemento do modelo.
Os registros possuem versionamento.
Pode ser utilizada em aplicações nos quais o histórico é importante.
Mudanças podem ser rastreadas.
Tipo 3: Adicionando uma Coluna
A nova informação é salva junto a informação desatualizada, em colunas
adjacentes.
Colunas adicionais são criadas para mostrar quando a nova inofrmação
passa a ter validade.
Permite análises considerando ambos os estados e realizar análises tipo
“o que-se” de valores dimensionais.
Exercício III

Para os exercícios anteriores, descreva as melhores maneiras de

introduzir mudanças nas dimensões projetadas.
Referências Bibliográficas

Kimball & Ross, The Data Warehouse Toolkit, 2nd Edition, Wiley: New
York, 2002.
Material da Pentaho Corporation sobre o projeto de datawarehouse,
2006.
Projeto Final

Realizar o projeto de uma base de dados multi-dimensional para os

seguintes indicadores de desempenho:
IP1, IP2, IP3;
HR6, HR9, HR12;
Taxa de efetividade da cobrança;
Taxa de coleta da cobrança.
 Fim do Workshop

© 2006 Kybernetics. Diretos Reservados.

© 2006 Pentaho Corporation. All Rights Reserved.