Data Warehouse e Modelagem Dimensional - Conceitos bsicos

Disciplina: Sistemas de Apoio Deciso Prof. Wamberg

Objetivos de um Data WarehousePermitir que as informaes da empresa sejam facilmente acessadas (dados de fcil compreenso) Deve apresentar as informaes da empresa de modo consistente (dados confiveis significam alta qualidade) Deve ser adaptvel e flexvel a mudanas (mudanas no devem invalidar os dados e as aplicaes existentes)

Objetivos de um Data WarehouseDever ser um repositrio seguro que protege as informaes (acesso controlado informaes confidenciais) Deve funcionar como a base para uma melhor tomada de decises (dados apropriados para a tomada de deciso) Um DW de sucesso deve ser aceito pela comunidade de negcios (a simplicidade leva a uma maior aceitao)

Componentes de um data warehouseSistemas operacionais de origem Data staging area rea de apresentao de dados Ferramentas de acesso dados

Componentes de um Data WarehouseSistemas operacionais de origem

Data Staging AreaServios: Filtrar, combinar e padronizar dimenses Armazenamento de dados: tabelas relacionais e arquivos simples Processamento: Classificao e processamento seqencial

rea de Apresentao de DadosData Mart 1 Dados dimensionais atmico e de resumo baseados em um nico de processo de negcioBarramento do DW: fatos e decises em conformidad e

Ferramentas de acesso a dadosFerramentas de consultas especficas Geradores de relatrios Aplicaes de anlise Modelagem: previso, explorao de dados

Extrair

Carregar

Consultar

Extrair

Extrair

Carregar

Data Mart 2 (projetado da mesma Consultar forma...)

Componentes de um data warehouseSistemas operacionais de origemCapturam as transaes da empresa So considerados externos ao DW So aplicaes que executam independentemente

Componentes de um data warehouseData staging areaAbrange tudo entre os sistemas operacionais e a rea de apresentao de dados Local onde os dados operacionais brutos so transformados em um formato prprio para serem consultados e utilizados pelo usurio

Componentes de um data warehouse Data staging areaA extrao a primeira etapa do processo de obteno de dados no ambiente de data warehouse A extrao envolve a leitura e compreenso dos dados de origem Cpia dos dados de origem para a staging area Depois da extrao ocorrem muitas transformaes nos dados, como filtragem (ex: correo de erros de digitao, soluo de conflitos de domnio), combinao de dados e cancelamento de dados duplicados

Componentes de um data warehouseData staging areaA data staging area dominada pelas atividades simples de classificao e processamento seqencial No necessrio usar um conjunto de tabelas normalizadas para armazenar os dados da data staging area (solues mais simples podem ser adotadas) A ltima etapa do processo de ETL carregar os dados

Componentes de um data warehouseApresentao dos dados o local em que os dados ficam organizados, armazenados e tornam-se disponveis para serem consultados diretamente pelos usurios rea que a comunidade de negcio v e acessa atravs das ferramentas de acesso a dados

Componentes de um data warehouseApresentao dos dadosPode ser vista como uma srie de data marts integrados Um data mart uma parte do todo que compe a rea de apresentao Um data mart representa os dados de um nico processo de negcio (ex: vendas, compras, estoque) Os dados so armazenados de acordo com um modelo dimensional

Componentes de um data warehouseApresentao dos dadosModelagem dimensional facilita a compreenso dos dados Dados no normalizados Os dados dos data marts da rea de apresentao devem ser atmicos para permitir que consultas inesperadas sejam respondidas

Componentes de um data warehouseFerramentas de acesso aos dadosRecursos com que usurios de negcio podem contar para melhorar a tomada de decises analticas

Modelagem Dimensional TerminologiaTabela de fatos a principal tabela de um modelo dimensional em que as medies numricas de desempenho da empresa esto armazenadas Os dados de medio resultantes de um processo de negcio devem ser armazenados em um nico data mart Os dados de medio no devem ser duplicados

Modelagem Dimensional Tabela de Fatos A palavra fato representa uma medio de negcio A lista de dimenses define a granularidade da tabela de fatos e informa o escopo da medio Ex: quantidade de produtos vendidos em uma determinada loja em um determinado dia A medio feita na interseo de todas as dimenses (dia, produto e loja)

Modelagem Dimensional Tabela de Fatos

Tabela de fatos de vendas dirias

Chave da data (FK) Chave do produto (FK) Quantidade vendida Total de vendas em dlarOs fatos mais teis so numricos e aditivos!

Modelagem Dimensional Tabela de Fatos A capacidade de adio fundamental porque as aplicaes de DW raramente recuperam uma nica linha da tabela de fatos (milhares de linhas podem ser recuperadas de uma nica vez) Os fatos geralmente contm valores contnuos Medidas textuais geralmente dizem respeito s dimenses Somente no caso em que um texto for exclusivo para qualquer linha da tabela de fatos ele dever ficar na tabela de fatos

Modelagem Dimensional Tabela de FatosAs tabelas de fatos tm um grande nmero de linhas As tabelas de fatos so simples com relao ao nmero de atributos A tabela no deve ser preenchida com com zeros para representar que nada aconteceu

Modelagem Dimensional Tabela de Fatos Toda tabela de fatos possui duas ou mais chaves estrangeiras Ex: a chave do produto na tabela de fatos corresponde a chave do produto na tabela de dimenso produto A chave primria da tabela de fatos formada por um subconjunto das chaves estrangeiras Toda tabela que expressa uma relao muitospara-muitos deve ser uma tabela de fatos

Modelagem Dimensional Tabelas de Dimenso Esto sempre acompanhando uma tabea de fatos Contm descritores textuais Possuem muitos atributos (ex: 50 a 100 atributos) Os atributos descrevem as linhas na tabela de dimenso So simples com relao ao nmero de linhas e complexas com relao ao nmero de colunas

Modelagem Dimensional Tabelas de DimensoCada dimenso definida por sua chave primria (PK) A PK funciona como base da integridade referencial com qualquer tabela de fatos a que esteja associada Os atributos dimensionais funcionam como fonte para restries de consultas, agrupamentos e rtulos de relatrios (ex: vendas por marca, vendas por semana)

Modelagem Dimensional Tabelas de DimensoOs atributos dimensionais so fundamentais para fazer com que o DW possa ser usado e compreendido A potncia do DW diretamente proporcional qualidade e complexidade dos atributos das dimenses

Modelagem Dimensional Tabelas de DimensoAs tabelas de dimenso so pontos de entrada para as tabelas de fatos As dimenses implementam a interface de usurio para o DW Os melhores atributos so os textuais e discretos Os valores dos atributos devem ser palavras reais e no abreviaes

Modelagem Dimensional Tabelas de DimensoTabela de dimenso de um produto

Chave do produto (FK) Descrio do produto Descrio da marca Descrio da categoria Descrio do departamento Descrio do tipo de embalagem Tamanho do pacote Peso

Modelagem Dimensional Tabelas de DimensoQuando um campo de dados numrico um atributo de fatos ou de dimenses?Se o campo uma medida que adota vrios valores e participa dos clculos ento um fato Se o campo uma descrio com valor discreto que mais ou menos constante e participa de restries ento um atributo dimensional

Nem sempre esta distino clara!

Modelagem Dimensional Tabelas de DimensoOs cdigos operacionais (abreviaes) devem existir juntamente com as descries apropriadas Os significados dos cdigos devem ser extrados e apresentados como atributos de dimenses separados, que podem ser agrupados ou reportados

Modelagem Dimensional Tabelas de Dimenso Representam relaes hierrquicas na empresa (ex: produtos so divididos por marcas e depois por categorias) Para cada linha da dimenso produto inclumos a descrio da marca e da categoria associada a cada produto As informaes descritivas hierrquicas so armazenadas de forma redundante, visando facilidade de utilizao e o bom desempenho das consultas No possuem normalizao O espao trocado pela simplicidade e acessibilidade

Modelagem Dimensional Esquema Estrela A tabela de fatos formada por medidas numricas associada a um conjunto de tabelas de dimenso com atributos descritivos O esquema dimensional bastante simplesFacilita o entendimento Vantagens para o desempenho (menos junes) So flexveis para acomodar mudanas

Modelagem Dimensional Esquema EstrelaOs dados atmicos que no foram agregados so os mais expressivos Os dados atmicos so a base para o projeto da tabela de fatos para suportar consultas inesperadas

Esquema EstrelaDimenso LojaDimenso Data

Chave da data (PK) Atributos da data ...

Chave da loja (PK) Atributos da loja ...

Fatos de vendas dirias

Chave da data (FK) Chave do produto (FK) Chave da loja (FK) Quantidade vendida Total de vendas em dlarDimenso Produto

Chave do produto (PK) Atributos do produto ...

Esquema EstrelaDimenso Data

Dimenso Loja

Chave da data (PK) Data Dia da semana ...Dimenso Produto

Chave da loja (PK) Nmero da loja Nome da loja Estado da loja ...Fatos de vendas dirias

Chave do produto (PK)Descrio do produto Descrio da marca

Chave da data (FK) Chave do produto (FK)Chave da loja (FK)

...Estado Cear Pernambuco Piau Marca Sony Panasonic Philips

Quantidade vendida Total de vendas em dlarQuant. Vendida 10000 8000 5000 Total de Vendas 640.000 520.000 370.000

Modelagem Dimensional Esquema Estrela Um diagrama ER normalizado geralmente transforma-se em um conjunto de modelos dimensionaisO diagrama ER deve ser separado a partir dos processos de negcio (ex: vendas, clientes, compras, estoque) Cada processo de negcio deve ser modelado separadamente As relaes muitos-para-muitos so designadas como tabelas de fatos As tabelas restantes devem ser denormalizadas e transformadas em tabelas de dimenses

Mitos que cercam a modelagem dimensional Mito 1: Os data marts e modelos dimensionais so usados apenas para dados de resumoDados atmicos devem ser armazenados para possibilitar a resposta consultas imprevistas

Mito 2: Os data marts e os modelos dimensionais so solues departamentais e no corporativasOs data marts devem ser organizados de acordo com processos de negcio e no com base nos limites departamentais Deve ser evitada a duplicao de medidas bsicas

Mitos que cercam a modelagem dimensional Mito 3: Os data marts e modelos dimensionais no so escalonveisAs tabelas de fatos modernas contm bilhes de linhas Os modelos dimensionais so escalonveis

Mito 4: Os data marts e modelos dimensionais so apropriados apenas com existe um padro de utilizao previsvelAs estruturas dimensionais so extremamente flexveis e adaptveis a mudanas Tabelas de fatos devem ser criadas no nvel mais granular possvel (garante maior flexibilidade e mxima extensibilidade)

Mitos que cercam a modelagem dimensionalMito 5: Os data marts e modelos dimensionais no podem ser integrados e, portanto, levam a solues isoladasOs modelos dimensionais podem ser integrados

Data Warehouse e Modelagem Dimensional Vendas a VarejoCaptulo 2 The Data Warehouse ToolkitDisciplina: Sistemas de Apoio Deciso Profa. Dra. Bernadette Farias Lscio

Processo de Criao Dimensional em 4 etapasEtapa 1: Selecione o processo de negcio a ser modeladoUm processo uma atividade de negcio executada na empresa As medies de desempenho analisadas no DW resultam de processos de medio de negcios Ex: compra de material, pedidos, remessas, faturamento, estoque

Processo de Criao Dimensional em 4 etapas Um processo de negcio no se refere a um departamento especfico Ex: um nico modelo dimensional criado para tratar dados de pedidos, ao invs de criar modelos separados para os departamentos de vendas e marketing

Esta abordagem permite fornecer informaes consistentes para toda a empresa Modelos separados para cada departamento podem levar a duplicao de informaes Uma nica publicao reduz o esforo com as etapas de extrao, transformao e carga, e garante maior consistncia dos dados

Processo de Criao Dimensional em 4 etapasEtapa 2: Declare o gro (nvel de detalhes) do processo de negcio Especifique exatamente o que uma linha da tabela de fatos representa O gro exprime o nvel de detalhes associados s medidas da tabela de fatosComo se descreve uma linha especfica da tabela de fatos?

Processo de Criao Dimensional em 4 etapasExemplos de declaraes de gro:Um instantneo mensal de cada conta bancria Um instantneo dirio dos nveis de estoque de cada produto em um warehouse

Processo de Criao Dimensional em 4 etapasEtapa 3: Escolha as dimenses que se aplicam a cada linha da tabela de fatosAs dimenses representam as descries possveis que utilizam valores nicos no contexto de cada medida Quando no h dvidas a respeito do gro as dimenses podem ser identificadas facilmente Para cada dimenso so descritos os atributos que a alimentaroEx: data, produto, cliente

Processo de Criao Dimensional em 4 etapasEtapa 4: Identifique os fatos numricos que preenchero cada linha da tabela de fatosO que estamos medindo? Os usurios das reas de negcios esto profundamente interessados em analisar essas medidas de desempenho do seu processo de negcio Todas as medidas devem ser verdadeiras para o gro definido na Etapa 2

Processo de Criao Dimensional em 4 etapasRequisitos de Negcio

Modelo dimensional 1. Processo de negcio 2. Gro 3. Dimenses 4. Fatos Realidade dos DadosAs informaes do usurio so fundamentais!

Estudo de Caso sobre Varejo Descrio: Grande cadeia de produtos alimentcios 100 supermercados espalhados por cinco estados Cada loja possui departamentos completos (mercearia, congelados, padaria, etc.) Cada loja possui cerca de 60.000 produtos individuais (SKU stock keeping unit unidades em estoque) Cerca de 55.000 dos produtos so fornecidos por fabricantes externos e possuem cdigo barra impresso no pacote (UPC Universal Product Codes Cdigo Universal de Produto) Os UPCs esto no mesmo nvel de detalhes (gro) das SKus individuais (cada variao de pacote de um produto possui um UPC diferente e uma SKU diferente) Os 5.000 produtos restantes so originados internamente; esses produtos tambm possuem um cdigo barra que so nmeros de SKU

Estudo de Caso sobre VarejoDescrio (cont.):Os dados so coletados nas caixas registradoras quando os clientes compram os produtos Os cdigos de barra so varridos diretamente no caixa) O ponto de entrega das mercadorias tambm um ponto de coleta de dados

Estudo de Caso sobre VarejoOs gerentes esto preocupados com a logstica dos pedidos, com o estoque e com os produtos de venda, ao mesmo tempo que com o mximo de lucro As decises de gerncia mais significativas esto relacionadas a preos e promoes

Estudo de Caso sobre Varejo Etapa 1: Selecione o processo de negcioA partir de um acordo entre os requisitos de negcio com os dados disponveis Os gerentes desejam entender melhora as compras dos clientes de acordo com os dos capturados nos caixas O processo a ser modelado Vendas a Varejo no Ponto de Venda Esses dados permitiro analisar quais produtos esto vendendo, em quais lojas, em que dias e em que condies promocionais

Estudo de Caso sobre Varejo Etapa 2: Declare o Gro Que nvel de detalhes de dados deve ficar disponvel no modelo dimensional? Quanto mais detalhada e atmica a medida dos fatos, mais informaes podem ser obtidas Gros de nvel mais alto podem limitar as dimenses (menos detalhes ou menos dimenses) No estudo de caso, os dados mais granulares so um item de linha individual em uma transao POS (Point-of-sale ponto de venda) Permite uma viso detalhada das vendas das lojas Exemplo: qual a diferena entre as vendas na segunda-feira e no domingo? Quantos clientes tiraram proveito de uma promoo de 50% em um determinado tipo de cereal?

Estudo de Caso sobre VarejoEtapa 3: Escolha as dimensesAs dimenses escolhidas imediatamente so: data, produto e loja Dimenses descritivas adicionais: promoo A tabela de fatos tambm conta com outra dimenso adicional: nmero da transao POS

Estudo de Caso sobre VarejoDimenso Data

Dimenso LojaFatos da transao de vendas a varejo no POS

Chave da data (PK) Atributos da data ...

Chave da data (FK) Chave do produto (FK) Chave da loja (FK)Chave da promoo (FK)

Chave da loja (PK) Atributos da loja ...

Nmero da transao POS Fatos ...Dimenso Produto Dimenso Promoo

Chave do produto (PK) Atributos do produto ...

Chave da promoo (PK) Atributos da promoo ...

Estudo de Caso sobre VarejoEtapa 4: Identifique os fatosOs fatos devem ser verdadeiros para o gro (o item de linha individual na transao POS) Fatos coletados pelo sistemas POS incluem:Quantidade de vendas, o preo unitrio para a venda e o total de vendas. O custo do produto tambm pode ser obtido

Estudo de Caso sobre VarejoDimenso Data

Dimenso LojaFatos da transao de vendas a varejo no POS

Chave da data (PK) Atributos da data ...

Chave da data (FK) Chave do produto (FK) Chave da loja (FK)Chave da promoo (FK)

Chave da loja (PK) Atributos da loja ...

Nmero da transao POS Quantidade de Vendas Total de vendas em reais Valor de custo em reais Valor do lucro bruto em reaisDimenso Produto Dimenso Promoo

Chave do produto (PK) Atributos do produto ...

Chave da promoo (PK) Atributos da promoo ...

Atributos da Tabela de Dimenso Dimenso Data Provavelmente constar em todos data marts Cada coluna na tabela de dimenso Data definida pelo dia especfico que a linha representa A coluna dia da semana contm o nome do dia, como segunda-feira Nmero do dia na coluna de ms civil til para comparar o mesmo dia todos os meses (1... 31) Nmero de ms no ano (1...12) Perodos fiscais so diferentes de perodos civis Indicador de feriado (feriado e no-feriado) Os atributos da tabela de dimenso funcionam como rtulos dos relatrios, logo os dados devem ter um significado claro

Atributos da Tabela de DimensoDimenso data Chave da data (PK) Data Descrio completa da data Dia da semana Nmero do dia no ms civil Nmero do dia no ano no ms civil Nmero do dia no ms fiscal Nmero do dia no ano no ms fiscal ..... Ano fiscal Indicador de feriado Indicador de dia da semana Evento principal ... e muito mais!

Atributos da Tabela de DimensoDimenso ProdutoDescreve todos os itens de estoque do supermercado Os itens de estoque podem ser agrupados por marca, as marcas podem ser agrupadas em categorias, e as categorias em departamentos

Atributos da Tabela de DimensoPara cada item de estoque, todos os nveis da hierarquia de mercadorias so bem definidos Alguns atributos so exclusivos para cada produto, outros atributos podem se repetir Os atributos podem se repetirNo existe problema de espao!

Uma tabela de dimenso Produto teria 50 ou mais atributos descritivos

Atributos da Tabela de Dimenso

Dimenso produtoChave da produto (PK) Descrio do produto Nmero da SKU (chave natural) Descrio da marca Descrio da categoria Descrio do tipo de pacote Tamanho do pacote ... Peso Tipo de armazenamento Tipo de vida de prateleira ... e muito mais!

Atributos da Tabela de DimensoDescer na hierarquia em um data mart nada mais que adicionar cabealhos de linhas das tabelas de dimenses Subir na hierarquia remover cabealhos de linha Podemos subir ou descer conforme os atributos de mais de uma hierarquia explcita e com atributos que no fazem parte de nenhuma hierarquia

Atributos da Tabela de Dimenso Dimenso LojaDescreve todas as lojas na cadeia de produtos alimentcios Cada loja pode ser considerada um local possvel agrupar as lojas atravs de um atributo geogrfico, como CEP,municpio e estado As lojas tambm podem ser agrupadas por regies possvel haver mais de uma hierarquia em uma mesma tabela de dimenso

Atributos da Tabela de DimensoDimenso loja Chave da loja (PK) Nome da loja Nmero da loja (chave natural) Endereo da loja Cidade da loja Municpio da loja Estado da loja ... CEP da loja Gerente da loja Regio da loja ... Tipo de planta baixa Tipo de servio financeiro ... e muito mais!

Devem ser descritores textuais

Atributos da Tabela de Dimenso Dimenso PromooDescreve as condies de promoo sob as quais um produto foi vendido As condies de promoo incluem redues de preo temporrias, cartazes de fundo de corredor, anncios de jornal e cupons Descrevem fatores que podem causar uma mudana nas vendas dos produtos Os gerentes das sedes das lojas esto interessados em determinar se uma promoo eficaz ou no

Atributos da Tabela de DimensoDimenso PromooAs promoes so julgadas pelos seguintes fatores:Os produtos em promoo tiveram ganho nas vendas durante o perodo promocional Os produtos em promoo apresentaram queda nas vendas imediatamente antes ou depois da promoo Os produtos em promoo apresentaram ganho, mas produtos prximos na prateleira apresentaram queda ...

Atributos da Tabela de DimensoDimenso promoo Chave da promoo (PK) Nome da promoo Tipo de reduo de preo Tipo de meio da promoo Tipo de anncio Tipo de cartaz Tipo de cupom ... Fornecedor do cartaz Custo da promoo Data de incio da promoo Data de trmino da promoo ... e muito mais!

Atributos da Tabela de DimensoQuando uma dimenso no for aplicvel a uma medio deve-se incluir uma linha da tabela de dimenso correspondenteex: Nenhuma promoo em vigor!

Atributos da Tabela de Dimenso Que produtos estavam em promoo e no foram vendidos? Tabela de fatos de cobertura da promooHaveria uma linha na tabela de fatos para cada em promoo em cada loja a cada dia independentemente do produto ter sido vendido ou no chamada de tabela de fatos sem fatos porque no possui mtrica de medio A tabela apenas captura a reao entre as chaves envolvidas

Atributos da Tabela de DimensoQue produtos estavam em promoo e no foram vendidos?Primeiro preciso determinar os produtos que estavam em promoo no dia solicitado (tabela de cobertura de promoo) Depois preciso identificar os produtos que foram vendidos para Ento feita a diferena entre estas duas listas de produtos

Dimenso de DegeneraoO nmero da transao POS serve para agrupar todos os produtos adquiridos em uma nica transao A dimenso transao POS est vazia!Todos os itens descritivos que poderiam estar associados ao nmero da transao POS j foram distribudos nas dimenses chamada de dimenso de degenerao

Dimenso de DegeneraoSo comuns quando o gro de uma tabela de fatos representa um nico item de uma transao A dimenso de degenerao representa o identificador exclusivo do paiEx: nmeros de pedido e nmeros de fatura

Extenso do esquema de varejoComo adicionar informaes sobre clientes freqentes? O que fazer com os clientes que no tm identificao? O que fazer com as informaes do histrico de vendas sabendo-se que os clientes no tinham identificao? Que outras dimenses podem ser adicionadas?Quanto menor o gro maior a facilidade para se acrescentar novas dimenses!

Mudanas inesperadas no DWNovos atributos de dimensoNovos descritores textuais podem ser acrescentados como novas colunas As aplicaes existentes sero ignoradas pelos novos atributos

Mudanas inesperadas no DWNovas dimensesUma nova dimenso pode ser adicionada a uma tabela de fatos existente incluindo um novo campo de chave externa e preenchendo-o corretamente com os valores da chave primria da nova dimenso

Mudanas inesperadas no DWNovos fatos medidosOs novos fatos medidos esto no mesmo gro que os fatos existentes A tabela de fatos alterada para acrescentar a nova coluna e os valores so preenchidos na tabela Se os novos fatos tm um gro diferentes e no podem ser adaptados ao gro atual ento uma nova tabela de fatos dever ser criada

Mudanas inesperadas no DWDimenso tornando-se mais granular Deve ser criada uma nova dimenso Os atributos antigos podem ser copiados para a nova dimenso A tabela de fatos tambm precisa ser mais granularPode ser necessrio reconstruir a tabela de fatos

Mudanas inesperadas no DWAdio de uma fonte de dados completamente novaEnvolvendo dimenses existentes assim como novas dimenses inesperadas Uma nova fonte de dados possui sua prpria granularidade e dimensionalidade, ento pode ser necessrio criar uma nova tabela de fatos No devemos forar a incluso de medies que deixaro a tabela de fatos inconsistente!

Resistncia tentao da comodidadeA modelagem dimensional tem como foco a transmisso de valor do negcio atravs de facilidade de uso e de desempenho, e no nas eficincias de processamento das transaes

Resistncia tentao da comodidade A normalizao da tabela de dimenso chamada de snowflaking Os atributos redundantes so removidos da tabela de dimenso desnormalizada e so includos em tabelas de dimenso secundrias normalizadas Apesar de ser uma extenso possvel ao modelo dimensional deve-se evitar o uso do snowflakingFacilidade de uso e desempenho

Resistncia tentao da comodidadePor que evitar o snowflaking?A grande quantidade de tabelas torna a apresentao mais complexa Muitas tabelas e junes se traduzem em queda no desempenho da consulta A economia de espao em disco insignificante Reduz a capacidade dos usurios de navegar em uma dimensoUtilize snowflaking apenas quando for necessrio!