schema types data warehouse modeling star snowflake schema
Ce didacticiel explique les différents types de schéma de l'entrepôt de données. Apprenez ce qu'est le schéma en étoile et le schéma en flocon de neige et la différence entre le schéma en étoile et le schéma en flocon de neige:
Dans ce Tutoriels Date Warehouse pour les débutants , nous avons examiné en profondeur Modèle de données dimensionnelles dans l'entrepôt de données dans notre tutoriel précédent.
Dans ce didacticiel, nous allons tout apprendre sur les schémas d'entrepôt de données qui sont utilisés pour structurer les data marts (ou) les tables d'entrepôt de données.
site Web qui vous permet de télécharger des vidéos youtube
Commençons!!
Public cible
- Développeurs et testeurs d'entrepôt de données / ETL.
- Professionnels des bases de données ayant une connaissance de base des concepts de bases de données.
- Administrateurs de bases de données / experts en big data qui souhaitent comprendre les domaines de l'entrepôt de données / ETL.
- Diplômés du Collège / Freshers qui recherchent des emplois dans l'entrepôt de données.
Ce que vous apprendrez:
Schéma de l'entrepôt de données
Dans un entrepôt de données, un schéma est utilisé pour définir la manière d'organiser le système avec toutes les entités de la base de données (tables de faits, tables de dimension) et leur association logique.
Voici les différents types de schémas dans DW:
- Horaire des étoiles
- Schéma SnowFlake
- Diagramme de la galaxie
- Schéma de cluster en étoile
# 1) Horaire des étoiles
Il s'agit du schéma le plus simple et le plus efficace dans un entrepôt de données. Une table de faits au centre entourée de tables de dimensions multiples ressemble à une étoile dans le modèle Star Schema.
La table de faits maintient des relations un-à-plusieurs avec toutes les tables de dimension. Chaque ligne d'une table de faits est associée à ses lignes de table de dimension avec une référence de clé étrangère.
Pour la raison ci-dessus, la navigation parmi les tables de ce modèle est facile pour interroger des données agrégées. Un utilisateur final peut facilement comprendre cette structure. Par conséquent, tous les outils de Business Intelligence (BI) prennent en charge le modèle de schéma Star.
Lors de la conception de schémas en étoile, les tables de dimension sont volontairement dénormalisées. Ils sont larges avec de nombreux attributs pour stocker les données contextuelles pour une meilleure analyse et un meilleur reporting.
Avantages du schéma en étoile
- Les requêtes utilisent des jointures très simples lors de la récupération des données et augmentent ainsi les performances des requêtes.
- Il est simple de récupérer des données à des fins de rapport, à tout moment et pour toute période.
Inconvénients du schéma en étoile
- S'il y a de nombreux changements dans les exigences, il n'est pas recommandé de modifier et de réutiliser le schéma en étoile existant à long terme.
- La redondance des données est davantage due au fait que les tables ne sont pas divisées hiérarchiquement.
Un exemple de schéma en étoile est donné ci-dessous.
Interroger un schéma en étoile
Un utilisateur final peut demander un rapport à l'aide des outils de Business Intelligence. Toutes ces demandes seront traitées en créant une chaîne de «requêtes SELECT» en interne. Les performances de ces requêtes auront un impact sur le temps d'exécution du rapport.
À partir de l'exemple de schéma Star ci-dessus, si un utilisateur professionnel souhaite savoir combien de romans et de DVD ont été vendus dans l'état du Kerala en janvier 2018, vous pouvez appliquer la requête comme suit sur les tables de schéma 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
Résultats:
Nom_produit | Quantité vendue | |
---|---|---|
sept | Tout le monde peut facilement comprendre et concevoir le schéma. | Il est difficile de comprendre et de concevoir le schéma. |
Des romans | 12 702 | |
DVD | 32 919 |
J'espère que vous avez compris à quel point il est facile d'interroger un schéma en étoile.
# 2) Schéma SnowFlake
Le schéma en étoile agit comme une entrée pour concevoir un schéma SnowFlake. L'écaillage de la neige est un processus qui normalise complètement toutes les tables de dimension à partir d'un schéma en étoile.
La disposition d'une table de faits au centre entourée de plusieurs hiérarchies de tables de dimension ressemble à un SnowFlake dans le modèle de schéma SnowFlake. Chaque ligne de table de faits est associée à ses lignes de table de dimension avec une référence de clé étrangère.
Lors de la conception des schémas SnowFlake, les tables de dimension sont volontairement normalisées. Des clés étrangères seront ajoutées à chaque niveau des tables de dimension pour se lier à son attribut parent. La complexité du schéma SnowFlake est directement proportionnelle aux niveaux de hiérarchie des tables de dimension.
Avantages du schéma SnowFlake:
- La redondance des données est complètement supprimée en créant de nouvelles tables de dimension.
- Par rapport au schéma en étoile, moins d'espace de stockage est utilisé par les tables de dimension Snow Flaking.
- Il est facile de mettre à jour (ou) de maintenir les tables de floconnage de neige.
Inconvénients du schéma SnowFlake:
- En raison des tables de dimensions normalisées, le système ETL doit charger le nombre de tables.
- Vous pouvez avoir besoin de jointures complexes pour effectuer une requête en raison du nombre de tables ajoutées. Par conséquent, les performances des requêtes seront dégradées.
Un exemple de schéma SnowFlake est donné ci-dessous.
Les tableaux de dimensions du diagramme SnowFlake ci-dessus sont normalisés comme expliqué ci-dessous:
- La dimension de date est normalisée en tables trimestrielles, mensuelles et hebdomadaires en laissant les identifiants de clé étrangère dans la table de dates.
- La dimension de magasin est normalisée pour comprendre la table pour l'état.
- La dimension du produit est normalisée en Marque.
- Dans la dimension Client, les attributs connectés à la ville sont déplacés dans la nouvelle table Ville en laissant un identifiant de clé étrangère dans la table Client.
De la même manière, une seule dimension peut maintenir plusieurs niveaux de hiérarchie.
Différents niveaux de hiérarchies du diagramme ci-dessus peuvent être désignés comme suit:
- Les identifiants trimestriels, mensuels et hebdomadaires sont les nouvelles clés de substitution créées pour les hiérarchies de dimension Date et qui ont été ajoutées en tant que clés étrangères dans la table de dimension Date.
- L'ID d'état est la nouvelle clé de substitution créée pour la hiérarchie de dimension Store et elle a été ajoutée en tant que clé étrangère dans la table de dimension Store.
- L'identifiant de marque est la nouvelle clé de substitution créée pour la hiérarchie de dimension Produit et a été ajoutée en tant que clé étrangère dans la table de dimension Produit.
- L'ID de ville est la nouvelle clé de substitution créée pour la hiérarchie de dimension Client et elle a été ajoutée comme clé étrangère dans la table de dimension Client.
Interroger un schéma de flocon de neige
Nous pouvons également générer le même type de rapports pour les utilisateurs finaux que celui des structures de schéma en étoile avec les schémas SnowFlake. Mais les requêtes sont un peu compliquées ici.
À partir de l'exemple de schéma SnowFlake ci-dessus, nous allons générer la même requête que celle que nous avons conçue lors de l'exemple de requête de schéma Star.
Autrement dit, si un utilisateur professionnel souhaite savoir combien de romans et de DVD ont été vendus dans l'État du Kerala en janvier 2018, vous pouvez appliquer la requête comme suit sur les tables de schéma 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
Résultats:
Nom_produit | Quantité vendue |
---|---|
Des romans | 12 702 |
DVD | 32 919 |
Points à retenir lors de l'interrogation des tables de schéma Star (ou) SnowFlake
Toute requête peut être conçue avec la structure ci-dessous:
Clause SELECT:
- Les attributs spécifiés dans la clause select sont affichés dans les résultats de la requête.
- L'instruction Select utilise également des groupes pour trouver les valeurs agrégées et, par conséquent, nous devons utiliser la clause group by dans la condition where.
Clause FROM:
- Tous les tableaux de faits et de dimensions essentiels doivent être choisis en fonction du contexte.
Clause WHERE:
- Les attributs de dimension appropriés sont mentionnés dans la clause where en les joignant aux attributs de la table de faits. Les clés de substitution des tables de dimension sont jointes aux clés étrangères respectives des tables de faits pour fixer la plage de données à interroger. Veuillez vous référer à l'exemple de requête de schéma en étoile écrit ci-dessus pour comprendre cela. Vous pouvez également filtrer les données dans la clause from elle-même si vous y utilisez des jointures internes / externes, comme indiqué dans l'exemple de schéma SnowFlake.
- Les attributs de dimension sont également mentionnés comme des contraintes sur les données dans la clause where.
- En filtrant les données avec toutes les étapes ci-dessus, les données appropriées sont renvoyées pour les rapports.
Selon les besoins de l'entreprise, vous pouvez ajouter (ou) supprimer les faits, dimensions, attributs et contraintes à un schéma en étoile (ou) à une requête de schéma SnowFlake en suivant la structure ci-dessus. Vous pouvez également ajouter des sous-requêtes (ou) fusionner différents résultats de requête pour générer des données pour tout rapport complexe.
# 3) Diagramme de la galaxie
Un schéma de galaxie est également connu sous le nom de schéma de constellation des faits. Dans ce schéma, plusieurs tables de faits partagent les mêmes tables de dimension. La disposition des tables de faits et des tables de dimension ressemble à une collection d'étoiles dans le modèle de schéma Galaxy.
Les dimensions partagées dans ce modèle sont appelées dimensions conformes.
Ce type de schéma est utilisé pour des exigences sophistiquées et pour des tables de faits agrégées plus complexes à prendre en charge par le schéma Star (ou) SnowFlake. Ce schéma est difficile à maintenir en raison de sa complexité.
Un exemple de schéma Galaxy est donné ci-dessous.
# 4) Schéma de cluster en étoile
Un schéma SnowFlake avec de nombreuses tables de dimension peut nécessiter des jointures plus complexes lors de l'interrogation. Un schéma en étoile avec moins de tables de dimension peut avoir plus de redondance. Par conséquent, un schéma d'amas d'étoiles est entré dans l'image en combinant les caractéristiques des deux schémas ci-dessus.
Le schéma en étoile est la base pour concevoir un schéma de cluster en étoile et quelques tables de dimension essentielles du schéma en étoile sont flocées de neige, ce qui forme une structure de schéma plus stable.
Un exemple de schéma Star Cluster est donné ci-dessous.
Quel est le meilleur schéma de flocon de neige ou schéma en étoile?
La plate-forme d'entrepôt de données et les outils BI utilisés dans votre système DW joueront un rôle essentiel dans le choix du schéma approprié à concevoir. Star et SnowFlake sont les schémas les plus fréquemment utilisés dans DW.
Le schéma en étoile est préférable si les outils de BI permettent aux utilisateurs professionnels d'interagir facilement avec les structures de table avec des requêtes simples. Le schéma SnowFlake est préférable si les outils de BI sont plus compliqués pour que les utilisateurs professionnels interagissent directement avec les structures de table en raison de davantage de jointures et de requêtes complexes.
Vous pouvez continuer avec le schéma SnowFlake si vous souhaitez économiser de l'espace de stockage ou si votre système DW dispose d'outils optimisés pour concevoir ce schéma.
Schéma en étoile contre schéma en flocon de neige
Vous trouverez ci-dessous les principales différences entre le schéma Star et le schéma SnowFlake.
S. Non | Horaire des étoiles | Schéma de flocon de neige |
---|---|---|
1 | La redondance des données, c'est plus. | La redondance des données est moindre. |
deux | L'espace de stockage pour les tables de dimension est plus. | L'espace de stockage pour les tables de dimension est comparativement moindre. |
3 | Contient des tables de dimensions dénormalisées. | Contient des tables de dimensions normalisées. |
4 | La table de faits unique est entourée de tables de dimensions multiples. | La table de faits unique est entourée de plusieurs hiérarchies de tables de dimension. |
5 | Les requêtes utilisent des jointures directes entre le fait et les dimensions pour récupérer les données. | Les requêtes utilisent des jointures complexes entre le fait et les dimensions pour récupérer les données. |
6 | Le temps d'exécution des requêtes est inférieur. | Le temps d'exécution des requêtes est plus long. |
8 | Utilise une approche descendante. | Utilise une approche ascendante. |
Conclusion
Nous espérons que vous avez bien compris les différents types de schémas d'entrepôt de données, ainsi que leurs avantages et inconvénients grâce à ce didacticiel.
Nous avons également appris comment Star Schema et SnowFlake Schema peuvent être interrogés, et quel schéma choisir entre les deux ainsi que leurs différences.
Restez à l'écoute de notre prochain tutoriel pour en savoir plus sur Data Mart dans ETL !!
=> Regardez la série de formation sur l'entreposage de données simples ici.
lecture recommandée
- Types de données Python
- Types de données C ++
- Tutoriel de test de l'entrepôt de données avec des exemples | Guide de test ETL
- Top 10 des outils d'entrepôt de données et des technologies de test les plus populaires
- Modèle de données dimensionnelles dans l'entrepôt de données - Tutoriel avec des exemples
- Tutoriel de test de test de l'entrepôt de données ETL (un guide complet)
- Qu'est-ce que le processus ETL (extraction, transformation, chargement) dans l'entrepôt de données?
- Exploration de données: processus, techniques et problèmes majeurs dans l'analyse des données