dimensional data model data warehouse tutorial with examples
Este tutorial explica os benefícios e mitos do modelo de dados dimensional no data warehouse. Além disso, aprenda sobre tabelas dimensionais e tabelas de fatos com exemplos:
Teste de Data Warehouse foi explicado em nosso tutorial anterior, neste Série de treinamento de data warehouse para todos .
Dados enormes são organizados no Data Warehouse (DW) com técnicas de Modelagem de Dados Dimensionais. Essas técnicas de modelagem de dados dimensionais tornam o trabalho dos usuários finais muito fácil de consultar sobre os dados de negócios. Este tutorial explica tudo sobre os modelos de dados dimensionais em DW.
Público-alvo
- Desenvolvedores e testadores de data warehouse / ETL.
- Profissionais de banco de dados com conhecimento básico de conceitos de banco de dados.
- Administradores de banco de dados / especialistas em big data que desejam entender os conceitos de data warehouse / ETL.
- Graduados universitários / novatos em busca de empregos em data warehouse.
O que você aprenderá:
Modelos de Dados Dimensionais
Modelos de dados dimensionais são as estruturas de dados que estão disponíveis para os usuários finais no fluxo ETL, para consultar e analisar os dados. O processo ETL termina com o carregamento de dados nos Modelos de Dados Dimensionais de destino. Cada modelo de dados dimensional é construído com uma tabela de fatos cercada por várias tabelas de dimensão.
Etapas a serem seguidas ao projetar um modelo de dados dimensionais:
Benefícios da modelagem de dados dimensionais
Listados abaixo estão os vários benefícios da modelagem de dados dimensionais.
- Eles são protegidos para usar os ambientes DW em constante mudança.
- Dados enormes podem ser facilmente construídos com a ajuda de modelos de dados dimensionais.
- Os dados dos modelos de dados dimensionais são fáceis de entender e analisar.
- Eles são rapidamente acessíveis pelos usuários finais para consultas com alto desempenho.
- Modelos de dados dimensionais nos permitem detalhar (ou) acumular os dados hierarquicamente.
Modelagem ER Vs Modelagem de Dados Dimensionais
- A modelagem ER é adequada para sistemas operacionais, enquanto a modelagem dimensional é adequada para o data warehouse.
- A modelagem ER mantém dados transacionais atuais detalhados, enquanto a modelagem dimensional mantém o resumo dos dados transacionais atuais e históricos.
- A modelagem ER normalizou os dados, enquanto a modelagem dimensional desnormalizou os dados.
- A modelagem ER usa mais junções durante a recuperação da consulta, enquanto a modelagem dimensional usa um número menor de junções, portanto, o desempenho da consulta é mais rápido na modelagem dimensional.
Mitos da modelagem de dados dimensionais
Abaixo estão alguns dos mitos da modelagem de dados dimensionais existentes.
- Modelos de dados dimensionais são usados apenas para representar o resumo dos dados.
- Eles são específicos do departamento em uma organização.
- Eles não suportam escalabilidade.
- Eles são projetados para servir ao propósito de relatórios e consultas do usuário final.
- Não podemos integrar os modelos de dados dimensionais.
Tabelas Dimensionais
As tabelas de dimensão desempenham um papel fundamental no sistema DW, armazenando todos os valores de métrica analisados. Esses valores são armazenados em atributos dimensionais facilmente selecionáveis (colunas) na tabela. A qualidade de um sistema DW depende principalmente da profundidade dos atributos de dimensão.
Portanto, devemos tentar fornecer muitos atributos junto com seus respectivos valores nas tabelas de dimensão.
Vamos explorar a estrutura das tabelas de dimensão !!
# 1) Chave da tabela de dimensões: Cada tabela de dimensão terá qualquer um de seus atributos de dimensão como uma chave primária para identificar exclusivamente cada linha. Portanto, os valores numéricos distintos desse atributo podem atuar como chaves primárias.
Se os valores do atributo não forem exclusivos em nenhum caso, você pode considerar os números do sistema gerados sequencialmente como as chaves primárias. Eles também são chamados de chaves substitutas.
Os modelos de dados dimensionais devem ter a restrição de integridade referencial para cada chave entre dimensões e fatos. Assim, as tabelas de fatos terão uma referência de chave estrangeira para cada chave primária / surrogate na tabela de dimensão para manter a integridade referencial.
Se falhar, os respectivos dados da tabela de fatos não podem ser recuperados para essa chave de dimensão.
# 2) A mesa é ampla: Podemos dizer que as tabelas de dimensão são largas, pois podemos adicionar qualquer número de atributos a uma tabela de dimensão em qualquer ponto do ciclo DW. O arquiteto DW solicitará que a equipe ETL adicione respectivos novos atributos ao esquema.
Em cenários de tempo real, você pode ver tabelas de dimensão com 50 (ou) mais atributos.
# 3) Atributos textuais: Os atributos dimensionais podem ser de qualquer tipo, preferencialmente texto (ou) numérico. Os atributos textuais terão palavras comerciais reais em vez de códigos. As tabelas dimensionais não são destinadas a cálculos, portanto, os valores numéricos raramente são usados para atributos dimensionais.
# 4) Os atributos podem não estar diretamente relacionados: Todos os atributos em uma tabela de dimensão podem não estar relacionados entre si.
# 5) Não normalizado: Normalizar uma tabela de dimensão traz mais tabelas intermediárias para a imagem, o que não é eficiente. Portanto, as tabelas de dimensão não são normalizadas.
Os atributos dimensionais podem atuar como a fonte de restrições em consultas e também podem ser exibidos como rótulos nos relatórios. As consultas serão executadas com eficiência se você selecionar diretamente um atributo da tabela de dimensão e se referir diretamente à respectiva tabela de fatos, sem tocar em nenhuma outra tabela intermediária.
# 6) Perfuração para baixo e enrolamento: Os atributos de dimensão têm a capacidade de detalhar (ou) acumular os dados sempre que necessário.
# 7) Hierarquias múltiplas: Uma tabela de dimensão única com várias hierarquias é muito comum. Uma tabela de dimensão terá uma hierarquia simples se houver apenas um caminho do nível inferior ao superior. Da mesma forma, ele terá várias hierarquias se houver vários caminhos presentes para alcançar do nível inferior ao topo.
# 8) Poucos registros: As tabelas de dimensão terão menos número de registros (em centenas) do que as tabelas de fatos (em milhões). Embora sejam menores do que os fatos, eles fornecem todas as entradas para as tabelas de fatos.
Aqui está um exemplo de uma tabela de dimensão do cliente:
Ao compreender os conceitos acima, você pode decidir se um campo de dados pode atuar como um atributo de dimensão (ou não) ao extrair os dados da própria fonte.
O plano de carga básico para uma dimensão
As dimensões podem ser criadas de duas maneiras, ou seja, extraindo os dados de dimensão de sistemas de origem externa (ou) O sistema ETL pode construir as dimensões a partir do teste sem envolver quaisquer fontes externas. No entanto, um sistema ETL sem qualquer processamento externo é mais adequado para criar tabelas de dimensão.
Abaixo estão as etapas envolvidas neste processo:
- Limpeza de dados: Os dados são limpos, validados e as regras de negócios são aplicadas antes de carregar na tabela de dimensão para manter a consistência.
- Dados em conformidade: Os dados de outras partes do data warehouse devem ser agregados adequadamente como um único valor, com relação a cada campo da tabela de dimensão.
- Compartilhe os mesmos domínios: Assim que os dados são confirmados, eles são armazenados novamente em tabelas de teste.
- Entrega de dados: Finalmente, todos os valores de atributos dimensionais são carregados com chaves primárias / substitutas atribuídas.
Tipos de dimensões
Os vários tipos de dimensões estão listados abaixo para sua referência.
como obter um email falso
Vamos começar!!
# 1) Dimensões pequenas
Pequenas dimensões no data warehouse agem como tabelas de pesquisa com menos número de linhas e colunas. Os dados em pequenas dimensões podem ser facilmente carregados de planilhas. Se necessário, pequenas dimensões podem ser combinadas como uma super dimensão.
# 2) Dimensão Conformada
Uma dimensão conformada é uma dimensão que pode ser referida da mesma forma com todas as tabelas de fatos relacionadas.
A dimensão de data é o melhor exemplo de uma dimensão conformada, pois os atributos da dimensão de data, como ano, mês, semana, dias, etc. comunicam os mesmos dados da mesma maneira em qualquer número de fatos.
Um exemplo de dimensão conformada.
# 3) Dimensão do lixo
Poucos atributos em uma tabela de fatos, como sinalizadores e indicadores, podem ser movidos para uma tabela de dimensão de lixo separada. Esses atributos também não pertencem a nenhuma outra tabela de dimensão existente. Em geral, os valores desses atributos são simplesmente “sim / não” (ou) “verdadeiro / falso”.
A criação de uma nova dimensão para cada atributo de sinalizador individual torna-o complexo ao criar mais número de chaves estrangeiras para a tabela de fatos. Ao mesmo tempo, manter todos esses sinalizadores e informações de indicadores em tabelas de fatos também aumenta a quantidade de dados armazenados em fatos, o que degrada o desempenho.
Portanto, a melhor solução para isso é criar uma única dimensão de lixo, pois uma dimensão de lixo é capaz de conter qualquer número de indicadores “sim / não” ou “verdadeiro / falso”. No entanto, as dimensões de lixo armazenam valores descritivos para esses indicadores (sim / não (ou) verdadeiro / falso), como ativo e pendente, etc.
Com base na complexidade de uma tabela de fatos e seus indicadores, uma tabela de fatos pode ter uma ou mais dimensões de lixo.
Um exemplo de dimensão do lixo.
# 4) Dimensão de RPG
Uma única dimensão que pode ser referida para vários fins em uma tabela de fatos é conhecida como dimensão Role-playing.
O melhor exemplo de uma dimensão de desempenho de papel é novamente uma tabela de dimensão de Data, pois o mesmo atributo de data em uma dimensão pode ser usado para diferentes fins em um fato, como data do pedido, data de entrega, data da transação, data de cancelamento, etc.
Se necessário, você pode criar quatro visualizações diferentes na tabela de dimensão de data em relação a quatro atributos de data diferentes de uma tabela de fatos.
Um exemplo de uma dimensão Role-Playing.
# 5) Dimensões degeneradas
Pode haver poucos atributos que não podem ser dimensões (métricas) nem fatos (medidas), mas eles precisam de análise. Todos esses atributos podem ser movidos para dimensões degeneradas.
Por exemplo, você pode considerar o número do pedido, o número da fatura etc. como atributos de dimensão degenerados.
Um exemplo de dimensão degenerada.
# 6) Dimensões que mudam lentamente
Uma dimensão que muda lentamente é um tipo em que os dados podem mudar lentamente a qualquer momento, em vez de em intervalos regulares periódicos. Os dados modificados nas tabelas de dimensão podem ser tratados de maneiras diferentes, conforme explicado abaixo.
Você pode selecionar o tipo de SCD para responder a uma mudança individualmente para cada atributo em uma tabela dimensional.
(i) SCD Tipo 1
- No tipo 1, quando há uma mudança nos valores dos atributos dimensionais, os valores existentes são substituídos pelos valores recém-modificados, o que nada mais é do que uma atualização.
- Os dados antigos não são mantidos para referência histórica.
- Os relatórios anteriores não podem ser regenerados devido à inexistência de dados antigos.
- De fácil manutenção.
- O impacto nas tabelas de fatos é maior.
Exemplo de SCD Tipo 1:
(Ii) SCD Tipo 2
- No tipo 2, quando houver alteração nos valores dos atributos dimensionais, uma nova linha será inserida com os valores modificados sem alterar os dados da linha anterior.
- Se houver qualquer referência de chave estrangeira para o registro antigo em qualquer uma das tabelas de fatos, a chave substituta antiga será atualizada em todos os lugares com uma nova chave substituta automaticamente.
- O impacto nas mudanças da tabela de fatos é muito menor com a etapa acima.
- Os dados antigos não são considerados em nenhum lugar após as alterações.
- No tipo 2, podemos rastrear todas as mudanças que estão acontecendo nos atributos dimensionais.
- Não há limite para o armazenamento de dados históricos.
- No tipo 2, adicionar alguns atributos a cada linha, como data de alteração, data-hora efetiva, data-hora de término, o motivo da alteração e o sinalizador atual é opcional. Mas isso é significativo se a empresa deseja saber o número de alterações feitas durante um determinado período.
Exemplo de SCD Tipo 2:
(Iii) SCD Tipo 3
- No tipo 3 quando há uma alteração nos valores dos atributos dimensionais, novos valores são atualizados mas os valores antigos ainda permanecem válidos como segunda opção.
- Em vez de adicionar uma nova linha para cada mudança, uma nova coluna será adicionada se não existir anteriormente.
- Os valores antigos são colocados nos atributos adicionados acima e os dados do atributo primário são substituídos pelo valor alterado como no tipo 1.
- Existe um limite para o armazenamento de dados históricos.
- O impacto nas tabelas de fatos é maior.
Exemplo de SCD Tipo 3:
(iv) SCD Tipo 4
- No tipo 4, os dados atuais são armazenados em uma tabela.
- Todos os dados históricos são mantidos em outra tabela.
Exemplo de SCD Tipo 4:
(v) SCD Tipo 6
- Uma tabela dimensional também pode ter uma combinação de todos os três tipos de SCD 1, 2 e 3, que é conhecida como dimensão de mudança lenta Tipo 6 (ou) Híbrida.
Tabelas de fatos
As tabelas de fatos armazenam um conjunto de valores medidos quantitativamente que são usados para cálculos. Os valores da tabela de fatos são exibidos nos relatórios de negócios. Em contraste com o tipo de dados textuais das tabelas de dimensão, o tipo de dados das tabelas de fatos é significativamente Numérico.
As tabelas de fatos são profundas, enquanto as tabelas de dimensão são largas, pois as tabelas de fatos terão um número maior de linhas e um número menor de colunas. Uma chave primária definida na tabela de fatos é principalmente para identificar cada linha separadamente. A chave primária também é chamada de chave composta na tabela de fatos.
Se a chave composta estiver faltando em uma tabela de fatos e se quaisquer dois registros tiverem os mesmos dados, é muito difícil diferenciar entre os dados e fazer referência aos dados nas tabelas de dimensão.
Conseqüentemente, se uma chave exclusiva adequada existir como a chave composta, é recomendável gerar um número de sequência para cada registro da tabela de fatos. Outra alternativa é formar uma chave primária concatenada. Isso será gerado pela concatenação de todas as chaves primárias referidas das tabelas de dimensão por linha.
Uma única tabela de fatos pode ser cercada por várias tabelas de dimensão. Com a ajuda das chaves estrangeiras existentes nas tabelas de fatos, o respectivo contexto (dados detalhados) dos valores medidos pode ser consultado nas tabelas de dimensão. Com a ajuda de consultas, os usuários realizarão drill down e roll up de maneira eficiente.
O nível mais baixo de dados que pode ser armazenado em uma tabela de fatos é conhecido como Granularidade. O número de tabelas de dimensão associadas a uma tabela de fatos é inversamente proporcional à granularidade dos dados da tabela de fatos. ou seja, o menor valor de medição precisa de mais tabelas de dimensão para ser referido.
Em um modelo dimensional, as tabelas de fatos mantêm relação de muitos para muitos com as tabelas de dimensão.
Um exemplo de tabela de fatos de vendas:
Carregar tabelas de planos para fatos
Você pode carregar os dados de uma tabela de fatos de forma eficiente, considerando as seguintes dicas:
Nº 1) Descartar e restaurar índices
Os índices, na verdade, as tabelas são bons impulsionadores do desempenho ao consultar os dados, mas eles demolem o desempenho ao carregar os dados. Portanto, antes de carregar qualquer dado enorme em tabelas de fatos, elimine principalmente todos os índices dessa tabela, carregue os dados e restaure os índices.
# 2) Separe as inserções das atualizações
Não mescle os registros de inserção e atualização ao carregar em uma tabela de fatos. Se o número de atualizações for menor, processe inserções e atualizações separadamente. Se o número de atualizações for maior, é aconselhável truncar e recarregar a tabela de fatos para obter resultados rápidos.
# 3) Particionamento
Faça o particionamento fisicamente em uma tabela de fatos em mini-tabelas para melhor desempenho de consulta nos dados da tabela de fatos em massa. Exceto para os DBAs e a equipe ETL, ninguém terá conhecimento das partições dos fatos.
Como um exemplo , você pode particionar uma tabela mensal, trimestral, anual, etc. Durante a consulta, apenas os dados particionados são considerados, em vez de varrer a tabela inteira.
# 4) Carregar em paralelo
Agora temos uma ideia sobre partições em tabelas de fatos. Partições em fatos também são benéficas ao carregar dados enormes em fatos. Para fazer isso, primeiro divida os dados logicamente em arquivos de dados diferentes e execute as tarefas ETL para carregar todas essas partes lógicas dos dados em paralelo.
melhor downloader de vídeo gratuito do youtube para windows 10
# 5) Utilitário de carregamento em massa
Ao contrário de outros sistemas RDBMS, o sistema ETL não precisa manter logs de rollback explicitamente para falhas no meio da transação. Aqui, “cargas em massa” acontecem em fatos em vez de “inserções de SQL” para carregar dados enormes. Se, no caso de uma única carga falhar, todos os dados podem ser facilmente recarregados (ou) podem continuar de onde pararam com a carga em massa.
# 6) Exclusão de um registro de fato
A exclusão de um registro da tabela de fatos ocorre apenas se a empresa quiser explicitamente. Se houver algum dado da tabela de fatos que não existe mais nos sistemas de origem, esses respectivos dados podem ser excluídos fisicamente (ou) logicamente.
- Exclusão física: Os registros indesejados são removidos da tabela de fatos permanentemente.
- Exclusão lógica: Uma nova coluna será adicionada à tabela de fatos, como 'excluído' do tipo Bit (ou) Booleano. Isso atua como um sinalizador para representar os registros excluídos. Você deve garantir que não está selecionando os registros excluídos ao consultar os dados da tabela de fatos.
# 7) Sequência para atualizações e exclusões em uma tabela de fatos
Quando houver algum dado a ser atualizado, as tabelas de dimensão devem ser atualizadas primeiro, seguido pela atualização das chaves substitutas na tabela de consulta, se necessário, e depois disso a respectiva tabela de fatos é atualizada. A exclusão ocorre ao contrário, pois a exclusão de todos os dados indesejados das tabelas de fatos facilita a exclusão dos dados indesejados vinculados das tabelas de dimensão.
Devemos seguir a sequência acima em ambos os casos, porque as tabelas de dimensão e tabelas de fatos mantêm a integridade referencial o tempo todo.
Tipos de fatos
Com base no comportamento dos dados das tabelas de fatos, eles são categorizados como tabelas de fatos de transações, tabelas de fatos de captura instantânea e tabelas de fatos de captura instantânea acumuladas. Todos esses três tipos seguem recursos diferentes com estratégias de carregamento de dados diferentes.
# 1) Tabelas de fatos de transações
Como o nome indica, as tabelas de fatos da transação armazenam dados no nível da transação para cada evento que ocorre. Esse tipo de dado é fácil de analisar no próprio nível da tabela de fatos. Mas para uma análise mais aprofundada, você também pode consultar as dimensões associadas.
Por exemplo, cada venda (ou) compra ocorrida em um site de marketing deve ser carregada em uma tabela de fatos de transação.
Um exemplo de uma Tabela de fatos de transação é mostrado abaixo.
# 2) Tabelas de fatos instantâneos periódicos
Como o nome indica, os dados na tabela de fatos instantâneos periódicos são armazenados na forma de instantâneos (fotos) em intervalos periódicos, como para cada dia, semana, mês, trimestre, etc., dependendo das necessidades do negócio.
Portanto, é claro que se trata de uma agregação de dados o tempo todo. Portanto, os fatos do instantâneo são mais complexos em comparação com as tabelas de fatos da transação. Por exemplo, quaisquer dados de relatórios de receita de desempenho podem ser armazenados em tabelas de fatos instantâneos para fácil referência.
Um exemplo de Tabela Periódica de Fatos Instantâneos é mostrado abaixo.
# 3) Acumulação de tabelas de fatos instantâneos
O acúmulo de tabelas de fatos de instantâneos permite armazenar dados em tabelas durante toda a vida útil de um produto. Isso atua como uma combinação dos dois tipos acima, onde os dados podem ser inseridos por qualquer evento a qualquer momento como um instantâneo.
Nesse tipo, colunas de data adicionais e dados para cada linha são atualizados com cada marco desse produto.
Um exemplo de tabela de fatos instantâneos acumulativos.
Além dos três tipos acima, aqui estão alguns outros tipos de tabelas de fatos:
# 4) Tabelas de fatos factuais: Um fato é uma coleção de medidas, enquanto que menos fato captura apenas eventos (ou) condições que não contêm nenhuma medida. Uma tabela de fatos sem fatos é usada principalmente para rastrear um sistema. Os dados nessas tabelas podem ser analisados e usados para relatórios.
Por exemplo, você pode procurar detalhes de um funcionário que tirou licença e o tipo de licença em um ano, etc. Incluindo todos esses detalhes de fatos não claros em um fato, a tabela definitivamente aumentará o tamanho dos fatos.
Um exemplo de Tabela de Fatos Sem Fato é mostrado abaixo.
# 5) Tabelas de fatos conformados: Um fato conformado é um fato que pode ser referido da mesma forma com cada data mart ao qual está relacionado.
Especificações de uma tabela de fatos
A seguir estão as especificações de uma Tabela de Fatos.
- Nome do fato: Esta é uma string que descreve brevemente a funcionalidade da tabela de fatos.
- Processo de negócio: As conversas sobre o negócio precisam ser cumpridas por essa tabela de fatos.
- Questões: Menciona uma lista de perguntas de negócios que serão respondidas por essa tabela de fatos.
- Grão: Indica o nível mais baixo de detalhe associado aos dados da tabela de fatos.
- Dimensões: Liste todas as tabelas de dimensão associadas a essa tabela de fatos.
- Medidas: Os valores calculados armazenados na tabela de fatos.
- Freqüência de carga Representa os intervalos de tempo para carregar dados na tabela de fatos.
- Linhas iniciais: Consulte os dados iniciais preenchidos na tabela de fatos pela primeira vez.
Exemplo de modelagem de dados dimensionais
Você pode ter uma ideia de como as tabelas de dimensão e tabelas de fatos podem ser projetadas para um sistema, observando o diagrama de modelagem de dados dimensionais abaixo para vendas e pedidos.
Conclusão
Agora, você deve ter adquirido um excelente conhecimento sobre as técnicas de modelagem de dados dimensionais, seus benefícios, mitos, tabelas dimensionais, tabelas de fatos, juntamente com seus tipos e processos.
Confira nosso próximo tutorial para saber mais sobre Esquemas de Data Warehouse !!
=> Visite aqui para aprender a armazenar dados do zero.
Leitura recomendada
- Tutorial de teste de data warehouse com exemplos | Guia de teste ETL
- Exemplos de mineração de dados: aplicações mais comuns de mineração de dados 2021
- Tutorial Python DateTime com exemplos
- Fundamentos de armazenamento de dados: um guia definitivo com exemplos
- Tutorial de teste de volume: exemplos e ferramentas de teste de volume
- As 10 principais ferramentas de data warehouse e tecnologias de teste populares
- Mineração de dados: processo, técnicas e questões importantes na análise de dados
- Como realizar testes orientados a dados no SoapUI Pro - Tutorial # 14 do SoapUI