Modelagem Dimensional do Data Warehouse Outubro2017 Cea Moraes

Modelagem Dimensional do Data Warehouse Outubro/2017 Ceça Moraes cecafac@gmail. com

Algum Material da Professora Valéria Cesário Times, do Cin-UFPE 2

Conteúdo • • • Objetivos da modelagem dimensional Fatos Dimensões Esquemas Modelagem dimensional 3

Objetivos da Modelagem Dimensional • Modelar informações em níveis apropriados de detalhes (resumido ou detalhado) • Otimizar o processamento de consultas complexas (Modelo Estrela ou Flocos de Neve) 4

Relacional x Dimensional • Modelo Relacional – Usado para identificar relacionamentos entre tabelas – Não redundância de dados – Processamento de Transações On-Line (OLTP) • Modelo Dimensional – Apresenta dados objetivando performance de acesso – Organiza dados em tabelas de fatos e dimensões – Processamento Analítico On-Line (OLAP) 5

Componentes Modelo Dimensional FK FK FK 6

Tabela de Fatos • Tabela central • Armazena as medidas numéricas do negócio e chaves das dimensões (ID das dimensões) – Na tabela de fatos as chaves das dimensões são FK e juntas formam a PK do fato • Idealmente medidas são numéricas e aditivas – Vendas(R$), lucro(R$), despesas(R$), quantidades 7

Tabela de Fatos • Principal tabela do modelo dimensional – medidas numéricas sobre o desempenho da atividade de negócio • Maioria dos fatos são numéricos e aditivos (podem ser somados) – Valor de vendas • Existem fatos não aditivos que não podem ser adicionados – Temperatura, preço, médias • Uma linha da tabela de fato corresponde ao valor de uma medida dentro de algumas dimensões 8

Tabela de Fatos • Todas as medidas da tabela de fatos devem ter a mesma granularidade • Na teoria, uma medida de fato pode ser textual – Uma medida textual é uma descrição de algo, i. e. temperatura – É obtida de uma lista discreta de valores – Por exemplo: 20 – 40 C = quente 9

Tabela de Fatos • Quando um dado numérico é Medida ou Dimensão? • Medida ⇒ varia continuamente (valores) – Quantidade vendida de um produto – Número de acertos no jogo • Dimensão ⇒ praticamente constante (descrições) – Produto (nome, descrição) – Jogo (nome, níveis) 10

Tabela de Dimensões • Tabelas periféricas com menor volume de dados • Armazenam as descrições do negócio – São usadas como filtros, agrupamentos e rótulos – Podem ser compartilhadas • São normalmente desnormalizadas (esquema estrela) • Atributos das dimensões ⇒ podem ser organizados em hierarquias – Produto (Categoria → Marca → Descrição) – Loja (Tipo → Endereço → Nome_Loja) – Tempo (Ano → Mês → Dia_Do_Mês) 11

Dimensões Compartilhadas 12

Modelo Dimensional 13

Tabela de Dimensões • Possuem relacionamentos (1: N) com a tabela de fato • Informação descritiva hierárquica pode ser armazenada redundantemente • Por exemplo, para cada linha da dimensão produto, as colunas marca e categoria são repetidas • Desnormalização favorece o desempenho de consultas e facilita o uso do sistema – Dimensões Desnormalizadas ⇒ modelo estrela – Dimensões Normalizadas ⇒ modelo snowflake 14

Dimensões Comuns 15

Tabelas de Dimensões • Dimensões funcionam como “filtros” 16

Tabelas Fato x Dimensão • Fatos – Atributos quantitativos sobre o desempenho do negócio – Fato vendas: a quantidade vendida, o valor da venda, a margem de lucro, média de vendas • Dimensões – Atributos qualitativos sobre os ramos do negócio envolvidos na medida de um determinado fato – Dimensão produto: a descrição, a marca, o tipo 17

Identificadores Artificiais • Cada dimensão deve possuir um número inteiro como identificador (a PK daquele objeto na dimensão) – Não é igual a PK do objeto na base de dados de origem – Identificadores de ambientes operacionais mudam com o tempo • Códigos de produtos obsoletos • Números de contas inativas • Funcionários demitidos e readmitidos • Evitar identificadores compostos – No DW, geralmente usamos a PK da dimensão como AUTOINCREMENT 18

Identificadores Naturais • Na tabela dimensão colocar (SEMPRE!!!!!) um atributo com o valor do seu identificador na base de dados de origem • Este é atributo da dimensão é conhecido com identificador natural 19

Esquema Estrela PK autoincrement PK base operacional 20

Esquema Floco de Neve • Dimensões são normalizadas • Ganho de espaço de armazenamento é pouco relevante – Estrutura complexa – Custo de junções 21

Modelos Dimensionais • Economia de espaço de disco obtida pelo Snowflake é geralmente menos de 1% do espaço de disco total para manter o BD multidimensional! 22

Dicas – Modelagem Dimensional • Fazer análise criteriosa dos dados do ambiente operacional (das fontes do DW) – Levantamento de requisitos • A partir das fontes de dados, vários modelos dimensionais podem ser gerados (Data Marts) – Relacionamentos N: M na fonte de dados, com propriedades numéricas e aditivas geralmente são mapeados em tabelas fato 23

Hierarquias nas Dimensões Esquema Flocos de Neve Esquema Estrela 24

Modelando. . . 25

Modelagem 26

Entidades da atividade de negócio Vendas de Imóvel • Escolher a granularidade – Vendas de Imóveis Individuais • Escolher as dimensões – Tabelas restantes ligadas à venda + dimensão temporal 27

Identificando e Padronizando Dimensões • Dimensões definem o contexto das consultas sobre os fatos (filtros) • Um conjunto incompleto de dimensões limita o universo de análises que podem ser feitas • Dois fatos podem compartilhar dimensões 28

Fatos Venda e Divulgação Proprietario 29

Fato Aluguel Proprietario 30

Medidas Não Aditivas • Dependendo do contexto da aplicação e das necessidades do usuário • A soma das medidas pode fazer menos sentido do que a aplicação de uma outra função de agregação – Média – Mínimo ou Máximo – Desvio Padrão • Exemplos – Temperatura – Preços Unitários – Taxas 31

Medidas Não Aditivas • Temperatura – Não faz sentido dizer que a temperatura de 2008 foi 200 graus – Faz sentido obter a temperatura média do ano • Preço Unitário 32

Medidas Não Aditivas • Temperatura – Não faz sentido dizer que a temperatura de 2008 foi 200 graus – Faz sentido obter a temperatura média do ano • Preço Unitário 33

Dimensão Temporal • DW sempre têm uma dimensão temporal • Existem vários atributos de datas – Períodos fiscais – Estações do ano – Feriados – Finais de semana – Eventos especiais • Carnaval • Páscoa, São João, Natal 34

Tabela Dimensão Temporal 35

Estendendo um esquema. . . 36

Estendendo o esquema. . . • A dimensão clientes é nova, assim as vendas que estão atualmente no DW não estão associadas a clientes, certo? • Criar a tabela da dimensão cliente • Adicionar uma nova chave estrangeira de cliente na tabela de fato vendas • Nem toda venda estará associada a um cliente cadastrado – vendas pré-existentes, antes da dimensão 37

Estendendo o esquema. . . • Inserir um registro de cliente com nome ou descrição “cliente não existente” na nova dimensão – O valor da PK deste na tabela dimensão é 0 (zero) – Vendas sem clientes terão o valor 0 na FK de cliente na tabela Fato • Dimensões e fatos existentes permanecem inalterados • Por conta de situações desse tipo, TODA dimensão tem que ter um registro do tipo “objeto não 38 existente” com PK = 0 (zero)

Esquema Estendido 39

Muitas Dimensões 40

Muitas Dimensões • Um grande número de dimensões no esquema é indica que: – Várias dimensões podem não ser independentes – Combinar em uma única dimensão • Consiste em um erro de modelagem representar elementos de uma hierarquia como dimensões separadas • Dimensões correlacionadas devem ser combinadas 41

Muitas Dimensões 42

Mudanças nas Dimensões • Mudanças ocorrem no mundo real – Linhas de produto são reestruturadas, causando mudanças em hierarquias de produto • Não se pode perder informações históricas de dimensões anteriores • Existem três abordagens mais simples para manutenção de valores de dimensões: – Sobrescrita do valor – Adição de uma tupla – Adição de uma coluna 43

Sobrescrita de Valor • Não há mudanças nas chaves • É a abordagem mais simples – rapidez e facilidade de implementação • Não mantém o histórico dos valores anteriores 44

Adição de Tupla • Uma nova tupla na tabela de dimensão é inserida • Tabela de fatos não é modificada • Histórico do produto é mantido – Fatos ocorridos antes da mudança estão associados ao valor do atributo anterior – Aumenta o tamanho da tabela de dimensões • Colocar período de validade dos departamentos 45

Adição de Coluna • Uma nova coluna na tabela de dimensão é inserida para refletir a mudança no valor do atributo • Fatos antigos e novos podem ser sumarizados em termos dos antigos e novos valores de atributo • Valores antigos e novos do atributo podem ser considerados 46

Arquitetura do DW (DW Bus) • Conjunto de fatos que compartilham um conjunto de dimensões padronizadas 47

Matriz DW Bus • Usada para criar, documentar e divulgar a arquitetura do DW • Colunas são dimensões compartilhadas e linhas são fatos • Possibilita a visualização de quais dimensões merecem atenção especial por participarem de vários fatos 48

Bibliografia • Data Warehousing, Data Mining & OLAP, Alex Berson, Stephen J. Smith. Mc. Graw-Hill • The Data Warehouse Toolkit. Ralph Kimball, Margy Ross. John Wiley & Sons, Inc. • Data Warehouse Brasil (http: //www. dwbrasil. com. br/) • Artigos de Kimball (http: //www. ralphkimball. com/html/articles. html) • Data Warehousing Institute (http: //www. dwinstitute. com/) • OLAP Report - (http: //www. olapreport. com/) 49