schema types data warehouse modeling star snowflake schema
Este tutorial explica vários tipos de esquema de data warehouse. Aprenda o que é Star Schema & Snowflake Schema e a diferença entre Star Schema e Snowflake Schema:
Nisso Tutoriais de data warehouse para iniciantes , analisamos em profundidade Modelo de dados dimensionais em data warehouse em nosso tutorial anterior.
Neste tutorial, aprenderemos tudo sobre esquemas de data warehouse usados para estruturar data marts (ou) tabelas de data warehouse.
Vamos começar!!
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 as áreas de Data warehouse / ETL.
- Graduados universitários / novatos em busca de empregos em data warehouse.
O que você aprenderá:
- Esquema de Data Warehouse
- Conclusão
Esquema de Data Warehouse
Em um data warehouse, um esquema é usado para definir a maneira de organizar o sistema com todas as entidades do banco de dados (tabelas de fatos, tabelas de dimensão) e sua associação lógica.
Aqui estão os diferentes tipos de esquemas em DW:
- Star Schedule
- Esquema do Floco de Neve
- Diagrama de Galáxia
- Esquema Star Cluster
# 1) Calendário de estrelas
Este é o esquema mais simples e eficaz em um data warehouse. Uma tabela de fatos no centro, cercada por tabelas de múltiplas dimensões, lembra uma estrela no modelo Star Schema.
A tabela de fatos mantém relações um para muitos com todas as tabelas de dimensão. Cada linha em uma tabela de fatos está associada às suas linhas da tabela de dimensão com uma referência de chave estrangeira.
Devido ao motivo acima, a navegação entre as tabelas neste modelo é fácil para consultar dados agregados. Um usuário final pode entender facilmente essa estrutura. Portanto, todas as ferramentas de Business Intelligence (BI) oferecem grande suporte ao modelo de esquema Star.
Ao projetar esquemas em estrela, as tabelas de dimensão são desnormalizadas propositalmente. Eles são amplos com muitos atributos para armazenar os dados contextuais para uma melhor análise e relatório.
Benefícios do esquema em estrela
- As consultas usam junções muito simples ao recuperar os dados e, portanto, o desempenho da consulta é aumentado.
- É simples recuperar dados para relatórios, a qualquer momento e em qualquer período.
Desvantagens do esquema em estrela
- Se houver muitas mudanças nos requisitos, o esquema em estrela existente não é recomendado para modificar e reutilizar a longo prazo.
- A redundância de dados é maior porque as tabelas não são divididas hierarquicamente.
Um exemplo de um esquema em estrela é fornecido abaixo.
Consultando um esquema em estrela
Um usuário final pode solicitar um relatório usando ferramentas de Business Intelligence. Todas essas solicitações serão processadas criando uma cadeia de “consultas SELECT” internamente. O desempenho dessas consultas terá um impacto no tempo de execução do relatório.
A partir do exemplo de esquema Star acima, se um usuário empresarial deseja saber quantos romances e DVDs foram vendidos no estado de Kerala em janeiro de 2018, você pode aplicar a consulta como segue nas tabelas de esquema Star:
SELECT pdim.Name Product_Name, Sum (sfact.sales_units) Quanity_Sold FROM Product pdim, Sales sfact, Store sdim, Date ddim WHERE sfact.product_id = pdim.product_id AND sfact.store_id = sdim.store_id AND sfact.date_id = ddim.date_id AND sdim.state = 'Kerala' AND ddim.month = 1 AND ddim.year = 2018 AND pdim.Name in (‘Novels’, ‘DVDs’) GROUP BY pdim.Name Resultados:
| Nome do Produto | Quantidade vendida | |
|---|---|---|
| 7 | Qualquer pessoa pode entender e projetar o esquema facilmente. | É difícil entender e projetar o esquema. |
| Romances | 12.702 | |
| DVDs | 32.919 |
Espero que você tenha entendido como é fácil consultar um esquema em estrela.
# 2) Esquema do Floco de Neve
O esquema Star atua como uma entrada para projetar um esquema SnowFlake. A descamação da neve é um processo que normaliza completamente todas as tabelas de dimensão de um esquema em estrela.
A organização de uma tabela de fatos no centro cercada por várias hierarquias de tabelas de dimensão se parece com um SnowFlake no modelo de esquema SnowFlake. Cada linha da tabela de fatos está associada às suas linhas da tabela de dimensão com uma referência de chave estrangeira.
Ao projetar esquemas SnowFlake, as tabelas de dimensão são normalizadas propositalmente. Chaves estrangeiras serão adicionadas a cada nível das tabelas de dimensão para vincular a seu atributo pai. A complexidade do esquema SnowFlake é diretamente proporcional aos níveis de hierarquia das tabelas de dimensão.
Benefícios do Esquema SnowFlake:
- A redundância de dados é completamente removida com a criação de novas tabelas de dimensão.
- Quando comparado com o esquema em estrela, menos espaço de armazenamento é usado pelas tabelas de dimensão Snow Flaking.
- É fácil atualizar (ou) manter as tabelas de flocos de neve.
Desvantagens do esquema SnowFlake:
- Devido às tabelas de dimensão normalizadas, o sistema ETL deve carregar o número de tabelas.
- Você pode precisar de junções complexas para realizar uma consulta devido ao número de tabelas adicionadas. Conseqüentemente, o desempenho da consulta será prejudicado.
Um exemplo de Esquema de Floco de Neve é fornecido abaixo.
As tabelas de dimensões no Diagrama do Floco de Neve acima são normalizadas conforme explicado abaixo:
- A dimensão da data é normalizada nas tabelas Trimestral, Mensal e Semanal, deixando os IDs de chave estrangeira na tabela Data.
- A dimensão da loja é normalizada para incluir a tabela de Estado.
- A dimensão do produto é normalizada em Marca.
- Na dimensão Cliente, os atributos conectados à cidade são movidos para a nova tabela Cidade, deixando um id de chave estrangeira na tabela Cliente.
Da mesma forma, uma única dimensão pode manter vários níveis de hierarquia.
Diferentes níveis de hierarquias do diagrama acima podem ser referidos da seguinte forma:
- Os ids trimestrais, os ids mensais e os ids semanais são as novas chaves substitutas criadas para as hierarquias de dimensão de data e que foram adicionadas como chaves estrangeiras na tabela de dimensão de data.
- ID de estado é a nova chave substituta criada para a hierarquia da dimensão Store e foi adicionada como chave estrangeira na tabela de dimensão Store.
- O ID da marca é a nova chave substituta criada para a hierarquia da dimensão Produto e foi adicionada como chave estrangeira na tabela de dimensão Produto.
- O ID da cidade é a nova chave substituta criada para a hierarquia da dimensão Cliente e foi adicionada como chave estrangeira na tabela de dimensão Cliente.
Consultando um esquema em floco de neve
Podemos gerar o mesmo tipo de relatório para os usuários finais que as estruturas de esquema em estrela com esquemas SnowFlake também. Mas as consultas são um pouco complicadas aqui.
A partir do exemplo de esquema SnowFlake acima, vamos gerar a mesma consulta que projetamos durante o exemplo de consulta de esquema Star.
Ou seja, se um usuário empresarial quiser saber quantos romances e DVDs foram vendidos no estado de Kerala em janeiro de 2018, você pode aplicar a consulta a seguir nas tabelas de esquema do SnowFlake.
SELECT pdim.Name Product_Name, Sum (sfact.sales_units) Quanity_Sold FROM Sales sfact INNER JOIN Product pdim ON sfact.product_id = pdim.product_id INNER JOIN Store sdim ON sfact.store_id = sdim.store_id INNER JOIN State stdim ON sdim.state_id = stdim.state_id INNER JOIN Date ddim ON sfact.date_id = ddim.date_id INNER JOIN Month mdim ON ddim.month_id = mdim.month_id WHERE stdim.state = 'Kerala' AND mdim.month = 1 AND ddim.year = 2018 AND pdim.Name in (‘Novels’, ‘DVDs’) GROUP BY pdim.Name Resultados:
| Nome do Produto | Quantidade vendida |
|---|---|
| Romances | 12.702 |
| DVDs | 32.919 |
Pontos a serem lembrados ao consultar tabelas de esquema em estrela (ou) floco de neve
Qualquer consulta pode ser projetada com a estrutura abaixo:
Cláusula SELECT:
- Os atributos especificados na cláusula select são mostrados nos resultados da consulta.
- A instrução Select também usa grupos para encontrar os valores agregados e, portanto, devemos usar grupo por cláusula na condição where.
Cláusula FROM:
- Todas as tabelas de fatos e tabelas de dimensão essenciais devem ser escolhidas de acordo com o contexto.
Cláusula WHERE:
- Os atributos de dimensão apropriados são mencionados na cláusula where unindo-se aos atributos da tabela de fatos. As chaves substitutas das tabelas de dimensão são unidas às respectivas chaves estrangeiras das tabelas de fatos para corrigir o intervalo de dados a ser consultado. Consulte o exemplo de consulta de esquema em estrela escrito acima para entender isso. Você também pode filtrar dados na própria cláusula from se, no caso, estiver usando junções internas / externas lá, conforme escrito no exemplo de esquema SnowFlake.
- Os atributos de dimensão também são mencionados como restrições aos dados na cláusula where.
- Ao filtrar os dados com todas as etapas acima, os dados apropriados são retornados para os relatórios.
De acordo com as necessidades de negócios, você pode adicionar (ou) remover os fatos, dimensões, atributos e restrições de um esquema em estrela (ou) consulta de esquema SnowFlake seguindo a estrutura acima. Você também pode adicionar subconsultas (ou) mesclar resultados de consultas diferentes para gerar dados para quaisquer relatórios complexos.
# 3) Diagrama de galáxia
Um esquema de galáxia também é conhecido como Esquema de Constelação de Fato. Nesse esquema, várias tabelas de fatos compartilham as mesmas tabelas de dimensão. A disposição das tabelas de fatos e tabelas de dimensão parece uma coleção de estrelas no modelo de esquema Galaxy.
As dimensões compartilhadas neste modelo são conhecidas como dimensões conformadas.
Esse tipo de esquema é usado para requisitos sofisticados e para tabelas de fatos agregadas que são mais complexas para serem suportadas pelo esquema Star (ou) SnowFlake. Este esquema é difícil de manter devido à sua complexidade.
Um exemplo de Galaxy Schema é fornecido abaixo.
# 4) Esquema de cluster em estrela
Um esquema SnowFlake com muitas tabelas de dimensão pode precisar de junções mais complexas durante a consulta. Um esquema em estrela com menos tabelas de dimensão pode ter mais redundância. Conseqüentemente, um esquema de agrupamento em estrela entrou em cena combinando os recursos dos dois esquemas acima.
software de backup gratuito para windows 8.1
O esquema em estrela é a base para projetar um esquema de cluster em estrela e poucas tabelas de dimensões essenciais do esquema em estrela são flocos de neve e isso, por sua vez, forma uma estrutura de esquema mais estável.
Um exemplo de um Esquema Star Cluster é dado abaixo.
Qual é o melhor esquema do floco de neve ou esquema em estrela?
A plataforma de data warehouse e as ferramentas de BI usadas em seu sistema DW desempenharão um papel vital na decisão do esquema adequado a ser projetado. Star e SnowFlake são os esquemas usados com mais frequência no DW.
O esquema Star é preferido se as ferramentas de BI permitirem que os usuários de negócios interajam facilmente com as estruturas da tabela com consultas simples. O esquema SnowFlake é preferido se as ferramentas de BI são mais complicadas para os usuários de negócios interagirem diretamente com as estruturas da tabela devido a mais junções e consultas complexas.
Você pode prosseguir com o esquema SnowFlake se quiser economizar algum espaço de armazenamento ou se o seu sistema DW tiver ferramentas otimizadas para projetar esse esquema.
Esquema em estrela versus esquema em floco de neve
A seguir estão as principais diferenças entre o esquema Star e o esquema SnowFlake.
| S.Não | Star Schedule | Esquema de flocos de neve |
|---|---|---|
| 1 | A redundância de dados é mais. | A redundância de dados é menor. |
| dois | O espaço de armazenamento para tabelas de dimensão é mais. | O espaço de armazenamento para tabelas de dimensão é comparativamente menor. |
| 3 | Contém tabelas de dimensão desnormalizadas. | Contém tabelas de dimensões normalizadas. |
| 4 | Uma única tabela de fatos é cercada por várias tabelas de dimensão. | A tabela de fatos única é cercada por várias hierarquias de tabelas de dimensão. |
| 5 | As consultas usam junções diretas entre fatos e dimensões para buscar os dados. | As consultas usam junções complexas entre fatos e dimensões para buscar os dados. |
| 6 | O tempo de execução da consulta é menor. | O tempo de execução da consulta é maior. |
| 8 | Usa abordagem de cima para baixo. | Usa uma abordagem ascendente. |
Conclusão
Esperamos que você tenha uma boa compreensão dos diferentes tipos de Esquemas de Data Warehouse, junto com seus benefícios e desvantagens deste tutorial.
Também aprendemos como Star Schema e SnowFlake Schema podem ser consultados e qual esquema deve ser escolhido entre os dois, juntamente com suas diferenças.
Fique ligado em nosso próximo tutorial para saber mais sobre Data Mart em ETL !!
=> Assista à série de treinamento sobre armazenamento de dados simples aqui.
Leitura recomendada
- Tipos de dados Python
- Tipos de dados C ++
- Tutorial de teste de data warehouse com exemplos | Guia de teste ETL
- As 10 principais ferramentas de data warehouse e tecnologias de teste populares
- Modelo de dados dimensionais em data warehouse - Tutorial com exemplos
- ETL Testing Tutorial de teste de data warehouse (um guia completo)
- O que é o processo ETL (Extract, Transform, Load) no Data Warehouse?
- Mineração de dados: processo, técnicas e questões importantes na análise de dados