dimensional data model data warehouse tutorial with examples
Ce didacticiel explique les avantages et les mythes du modèle de données dimensionnelles dans l'entrepôt de données. Découvrez également les tableaux de dimensions et les tableaux de faits avec des exemples:
Test d'entrepôt de données a été expliqué dans notre tutoriel précédent, dans ce Série de formation sur l'entrepôt de données pour tous .
D'énormes données sont organisées dans l'entrepôt de données (DW) avec des techniques de modélisation des données dimensionnelles. Ces techniques de modélisation dimensionnelle des données permettent aux utilisateurs finaux de se renseigner très facilement sur les données commerciales. Ce didacticiel explique tout sur les modèles de données dimensionnelles dans DW.
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 du Big Data qui souhaitent comprendre les concepts d'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:
Modèles de données dimensionnelles
Les modèles de données dimensionnelles sont les structures de données disponibles pour les utilisateurs finaux dans le flux ETL, pour interroger et analyser les données. Le processus ETL se termine par le chargement de données dans les modèles de données dimensionnelles cibles. Chaque modèle de données dimensionnel est construit avec une table de faits entourée de plusieurs tables de dimension.
Étapes à suivre lors de la conception d'un modèle de données dimensionnelles:
Avantages de la modélisation des données dimensionnelles
Vous trouverez ci-dessous les divers avantages de la modélisation des données dimensionnelles.
- Ils sont sécurisés pour utiliser les environnements DW en constante évolution.
- Des données énormes peuvent être facilement construites à l'aide de modèles de données dimensionnelles.
- Les données des modèles de données dimensionnelles sont faciles à comprendre et à analyser.
- Ils sont rapidement accessibles par les utilisateurs finaux pour des requêtes de haute performance.
- Les modèles de données dimensionnelles nous permettent d'explorer (ou) de remonter les données de manière hiérarchique.
Modélisation ER vs modélisation de données dimensionnelles
- La modélisation ER convient aux systèmes opérationnels tandis que la modélisation dimensionnelle convient à l'entrepôt de données.
- La modélisation ER maintient des données transactionnelles actuelles détaillées tandis que la modélisation dimensionnelle maintient le résumé des données transactionnelles actuelles et historiques.
- La modélisation ER a des données normalisées tandis que la modélisation dimensionnelle a des données dénormalisées.
- La modélisation ER utilise plus de jointures lors de la récupération des requêtes, tandis que la modélisation dimensionnelle utilise un nombre moindre de jointures, les performances des requêtes sont donc plus rapides dans la modélisation dimensionnelle.
Mythes de la modélisation des données dimensionnelles
Voici quelques-uns des mythes existants sur la modélisation des données dimensionnelles.
- Les modèles de données dimensionnelles ne sont utilisés que pour représenter le résumé des données.
- Ils sont spécifiques au service dans une organisation.
- Ils ne prennent pas en charge l'évolutivité.
- Ils sont conçus pour répondre aux besoins des rapports et des requêtes des utilisateurs finaux.
- Nous ne pouvons pas intégrer les modèles de données dimensionnelles.
Tables de dimension
Les tables de dimension jouent un rôle clé dans le système DW en stockant toutes les valeurs métriques analysées. Ces valeurs sont stockées sous des attributs dimensionnels (colonnes) facilement sélectionnables dans la table. La qualité d'un système DW dépend principalement de la profondeur des attributs de cote.
Par conséquent, nous devrions essayer de fournir de nombreux attributs avec leurs valeurs respectives dans les tables de dimension.
Explorons la structure des tables de dimension !!
# 1) Clé du tableau des dimensions: Chaque table de dimension aura l'un de ses attributs de dimension comme clé primaire pour identifier de manière unique chaque ligne. Par conséquent, les valeurs numériques distinctes de cet attribut peuvent agir comme des clés primaires.
Si les valeurs d'attribut ne sont en aucun cas uniques, vous pouvez considérer les numéros système générés séquentiellement comme clés primaires. Celles-ci sont également appelées clés de substitution.
Les modèles de données dimensionnelles doivent avoir la contrainte d'intégrité référentielle pour chaque clé entre les dimensions et les faits. Ainsi, les tables de faits auront une référence de clé étrangère pour chaque clé primaire / de substitution dans la table de dimension afin de maintenir l'intégrité référentielle.
En cas d'échec, les données de la table de faits respective ne peuvent pas être extraites pour cette clé de dimension.
# 2) La table est large: Nous pouvons dire que les tables de dimension sont larges car nous pouvons ajouter n'importe quel nombre d'attributs à une table de dimension à tout moment du cycle DW. L'architecte DW demandera à l'équipe ETL d'ajouter de nouveaux attributs respectifs au schéma.
Dans les scénarios en temps réel, vous pouvez voir les tables de dimension avec 50 (ou) attributs supplémentaires.
# 3) Attributs textuels: Les attributs dimensionnels peuvent être de n'importe quel type, de préférence du texte (ou) numérique. Les attributs textuels auront de vrais mots commerciaux plutôt que des codes. Les tables de dimensions ne sont pas destinées aux calculs, c'est pourquoi les valeurs numériques sont rarement utilisées pour les attributs dimensionnels.
# 4) Les attributs peuvent ne pas être directement liés: Tous les attributs d'une table de dimension peuvent ne pas être liés les uns aux autres.
# 5) Non normalisé: La normalisation d'une table de dimension apporte plus de tables intermédiaires dans l'image, ce qui n'est pas efficace. Ainsi, les tables de dimension ne sont pas normalisées.
Les attributs dimensionnels peuvent servir de source pour les contraintes dans les requêtes et peuvent également être affichés sous forme d'étiquettes dans les rapports. Les requêtes fonctionneront efficacement si vous sélectionnez directement un attribut dans la table de dimension et faites directement référence à la table de faits respective sans toucher à aucune autre table intermédiaire.
# 6) Forage vers le bas et enroulement: Les attributs de dimension ont la capacité d'explorer (ou) de remonter les données chaque fois que nécessaire.
# 7) Hiérarchies multiples: Une table à dimension unique ayant plusieurs hiérarchies est très courante. Une table de dimension aura une hiérarchie simple si un seul chemin existe du niveau inférieur vers le haut. De même, il aura plusieurs hiérarchies s'il y a plusieurs chemins à atteindre du niveau inférieur vers le haut.
# 8) Quelques records: Les tables de dimension auront moins d'enregistrements (en centaines) que les tables de faits (en millions). Bien qu'ils soient plus petits que les faits, ils fournissent toutes les entrées aux tables de faits.
Voici un exemple de table de dimension client:
En comprenant les concepts ci-dessus, vous pouvez décider si un champ de données peut agir comme un attribut de dimension (ou) pas lors de l'extraction des données de la source elle-même.
Le plan de charge de base pour une dimension
Les dimensions peuvent être créées de deux manières, c'est-à-dire en extrayant les données de dimension à partir de systèmes sources externes (ou) Le système ETL peut créer les dimensions à partir de la mise en scène sans impliquer aucune source externe. Cependant, un système ETL sans traitement externe est plus adapté pour créer des tables de dimension.
Voici les étapes impliquées dans ce processus:
comment makefile c ++
- Nettoyage des données: Les données sont nettoyées, validées et les règles métier sont appliquées avant le chargement dans la table de dimension pour maintenir la cohérence.
- Conformité des données: Les données provenant d'autres parties de l'entrepôt de données doivent être correctement agrégées en une seule valeur, par rapport à chaque champ de la table de dimension.
- Partagez les mêmes domaines: Une fois les données confirmées, elles sont à nouveau stockées dans des tables intermédiaires.
- Livraison des données: Enfin, toutes les valeurs d'attribut dimensionnel sont chargées avec des clés primaires / de substitution attribuées.
Types de dimensions
Les différents types de dimensions sont énumérés ci-dessous pour votre référence.
Commençons!!
# 1) Petites dimensions
Les petites dimensions de l'entrepôt de données agissent comme des tables de recherche avec moins de lignes et de colonnes. Les données en petites dimensions peuvent être facilement chargées à partir de feuilles de calcul. Si nécessaire, de petites dimensions peuvent être combinées en une super dimension.
# 2) Dimension conforme
Une dimension conforme est une dimension qui peut être référencée de la même manière avec chaque table de faits à laquelle elle est liée.
La dimension de date est le meilleur exemple d'une dimension conforme car les attributs de la dimension de date tels que l'année, le mois, la semaine, les jours, etc. communiquent les mêmes données de la même manière sur un nombre illimité de faits.
Un exemple de dimension conforme.
# 3) Dimension indésirable
Peu d'attributs dans une table de faits tels que les indicateurs et les indicateurs peuvent être déplacés vers une table de dimension indésirable distincte. Ces attributs n'appartiennent également à aucune autre table de dimension existante. En général, les valeurs de ces attributs sont simplement un «oui / non» (ou) «vrai / faux».
La création d'une nouvelle dimension pour chaque attribut d'indicateur individuel le rend complexe en créant un plus grand nombre de clés étrangères dans la table de faits. Dans le même temps, conserver tous ces indicateurs et informations d'indicateur dans des tableaux de faits augmente également la quantité de données stockées dans les faits, ce qui dégrade les performances.
Par conséquent, la meilleure solution pour cela est de créer une seule dimension indésirable car une dimension indésirable est capable de contenir n'importe quel nombre d'indicateurs «oui / non» ou «vrai / faux». Cependant, les dimensions indésirables stockent des valeurs descriptives pour ces indicateurs (oui / non (ou) vrai / faux) telles que actif et en attente, etc.
En fonction de la complexité d'une table de faits et de ses indicateurs, une table de faits peut avoir une ou plusieurs dimensions indésirables.
Un exemple de Junk Dimension.
# 4) Dimension du jeu de rôle
Une dimension unique qui peut être référencée à des fins multiples dans une table de faits est appelée dimension de jeu de rôle.
Le meilleur exemple de dimension de jeu de rôle est à nouveau une table de dimension Date car le même attribut de date dans une dimension peut être utilisé à des fins différentes dans un fait tel que la date de commande, la date de livraison, la date de transaction, la date d'annulation, etc.
Si nécessaire, vous pouvez créer quatre vues différentes sur la table de dimension de date par rapport à quatre attributs de date différents d'une table de faits.
Un exemple de dimension de jeu de rôle.
# 5) Dimensions dégénérées
Il peut y avoir peu d'attributs qui ne peuvent être ni des dimensions (métriques) ni des faits (mesures), mais ils nécessitent une analyse. Tous ces attributs peuvent être déplacés dans des dimensions dégénérées.
Par exemple, vous pouvez considérer le numéro de commande, le numéro de facture, etc. comme des attributs de dimension dégénérés.
Un exemple de dimension dégénérée.
# 6) Dimensions changeantes lentement
Une dimension qui change lentement est un type où les données peuvent changer lentement à tout moment plutôt qu'à intervalles réguliers périodiques. Les données modifiées dans les tables de dimension peuvent être traitées de différentes manières, comme expliqué ci-dessous.
Vous pouvez sélectionner le type SCD pour répondre individuellement à une modification pour chaque attribut d'une table dimensionnelle.
(i) SCD de type 1
- Dans le type 1, en cas de modification des valeurs des attributs dimensionnels, les valeurs existantes sont écrasées par les valeurs nouvellement modifiées, ce qui n'est rien d'autre qu'une mise à jour.
- Les anciennes données ne sont pas conservées pour référence historique.
- Les anciens rapports ne peuvent pas être régénérés en raison de l’absence d’anciennes données.
- Facile à maintenir.
- L'impact sur les tables de faits est plus important.
Exemple de SCD de type 1:
(Ii) SCD de type 2
- Dans le type 2, en cas de modification des valeurs des attributs dimensionnels, une nouvelle ligne sera insérée avec les valeurs modifiées sans changer les anciennes données de ligne.
- S'il existe une référence de clé étrangère à l'ancien enregistrement dans l'une des tables de faits, l'ancienne clé de substitution est automatiquement mise à jour partout avec une nouvelle clé de substitution.
- L'impact sur les changements de table de faits est très moindre avec l'étape ci-dessus.
- Les anciennes données ne sont prises en compte nulle part après les modifications.
- Dans le type 2, nous pouvons suivre toutes les modifications apportées aux attributs dimensionnels.
- Il n'y a pas de limite au stockage des données historiques.
- Dans le type 2, l'ajout de quelques attributs à chaque ligne tels que la date modifiée, la date-heure effective, la date-heure de fin, la raison du changement et l'indicateur actuel est facultatif. Mais cela est important si l'entreprise souhaite connaître le nombre de modifications apportées au cours d'une certaine période.
Exemple de SCD de type 2:
(Iii) SCD de type 3
- Dans le type 3, en cas de modification des valeurs des attributs dimensionnels, les nouvelles valeurs sont mises à jour mais les anciennes valeurs restent valables comme deuxième option.
- Au lieu d'ajouter une nouvelle ligne pour chaque modification, une nouvelle colonne sera ajoutée si elle n'existe pas auparavant.
- Les anciennes valeurs sont placées dans les attributs ajoutés ci-dessus et les données de l'attribut principal sont écrasées par la valeur modifiée comme dans le type 1.
- Le stockage des données historiques est limité.
- L'impact sur les tables de faits est plus important.
Exemple de SCD de type 3:
(iv) SCD de type 4
- Dans le type 4, les données actuelles sont stockées dans une table.
- Toutes les données historiques sont conservées dans une autre table.
Exemple de SCD de type 4:
(v) SCD de type 6
- Une table dimensionnelle peut également avoir une combinaison des trois types SCD 1, 2 et 3 qui est connue sous le nom de dimension à changement lent de type 6 (ou) hybride.
Tables de faits
Les tables de faits stockent un ensemble de valeurs mesurées quantitativement qui sont utilisées pour les calculs. Les valeurs de la table de faits sont affichées dans les rapports d'activité. Contrairement au type de données textuelles des tables de dimension, le type de données des tables de faits est significativement numérique.
Les tables de faits sont profondes alors que les tables de dimension sont larges car les tables de faits auront un plus grand nombre de lignes et un nombre moindre de colonnes. Une clé primaire définie dans la table de faits consiste principalement à identifier chaque ligne séparément. La clé primaire est également appelée table de faits clé composite.
Si la clé composite est manquante dans une table de faits et si deux enregistrements ont les mêmes données, il est très difficile de faire la différence entre les données et de référencer les données dans les tables de dimension.
Par conséquent, si une clé unique appropriée existe en tant que clé composite, il est bon de générer un numéro de séquence pour chaque enregistrement de table de faits. Une autre alternative consiste à former une clé primaire concaténée. Cela sera généré en concaténant toutes les clés primaires référencées des tables de dimension par ligne.
Une seule table de faits peut être entourée de plusieurs tables de dimension. A l'aide des clés étrangères qui existent dans les tables de faits, le contexte respectif (données détaillées) des valeurs mesurées peut être référencé dans les tables de dimension. À l'aide de requêtes, les utilisateurs effectueront une analyse descendante et une remontée efficace.
Le niveau de données le plus bas qui peut être stocké dans une table de faits est appelé Granularité. Le nombre de tables de dimension associées à une table de faits est inversement proportionnel à la granularité de ces données de table de faits. c'est-à-dire que la plus petite valeur de mesure nécessite plus de tables de dimensions pour être référencées.
Dans un modèle dimensionnel, les tables de faits conservent une relation plusieurs-à-plusieurs avec les tables de dimension.
Un exemple de tableau de faits de vente:
Plan de charge pour les tableaux de faits
Vous pouvez charger efficacement les données d'une table de faits en tenant compte des pointeurs suivants:
# 1) Supprimer et restaurer les index
Les index en fait, les tables sont de bons boosters de performances lors de l'interrogation des données, mais ils démolissent les performances lors du chargement des données. Par conséquent, avant de charger des données volumineuses dans les tables de faits, supprimez principalement tous les index de cette table, chargez les données et restaurez les index.
# 2) Séparez les insertions des mises à jour
Ne fusionnez pas les enregistrements d'insertion et de mise à jour lors du chargement dans une table de faits. Si le nombre de mises à jour est inférieur, traitez les insertions et les mises à jour séparément. Si le nombre de mises à jour est supérieur, il est conseillé de tronquer et de recharger la table de faits pour des résultats rapides.
# 3) Partitionnement
Effectuez le partitionnement physique sur une table de faits en mini-tables pour de meilleures performances de requête sur les données de la table de faits en bloc. À l'exception des DBA et de l'équipe ETL, personne ne sera au courant des partitions sur les faits.
En tant que Exemple , vous pouvez partitionner une table par mois, par trimestre, par année, etc. Lors de l'interrogation, seules les données partitionnées sont prises en compte au lieu d'analyser la table entière.
# 4) Charger en parallèle
code C ++ de liste double chaînée
Nous avons maintenant une idée des partitions sur les tables de faits. Les partitions sur des faits sont également bénéfiques lors du chargement de données volumineuses dans des faits. Pour ce faire, commencez par diviser les données de manière logique dans différents fichiers de données et exécutez les travaux ETL pour charger toutes ces parties logiques de données en parallèle.
# 5) Utilitaire de chargement en bloc
Contrairement aux autres systèmes SGBDR, le système ETL n'a pas besoin de maintenir explicitement les journaux de restauration pour les échecs de mi-transaction. Ici, les 'chargements en masse' se produisent dans les faits au lieu des 'insertions SQL' pour charger d'énormes données. Si un seul chargement échoue, alors les données entières peuvent être facilement rechargées (ou) elles peuvent être continuées là où elles ont été laissées avec le chargement en bloc.
# 6) Suppression d'un enregistrement de faits
La suppression d'un enregistrement de table de faits se produit uniquement si l'entreprise le souhaite explicitement. S'il existe des données de table de faits qui n'existent plus dans les systèmes source, ces données respectives peuvent être supprimées physiquement (ou) logiquement.
- Suppression physique: Les enregistrements indésirables sont supprimés définitivement de la table de faits.
- Suppression logique: Une nouvelle colonne sera ajoutée à la table de faits telle que «supprimé» de type booléen Bit (ou). Cela agit comme un indicateur pour représenter les enregistrements supprimés. Vous devez vous assurer que vous ne sélectionnez pas les enregistrements supprimés lors de l'interrogation des données de la table de faits.
# 7) Séquence pour les mises à jour et les suppressions dans une table de faits
Lorsqu'il y a des données à mettre à jour, les tables de dimension doivent d'abord être mises à jour, suivies de la mise à jour des clés de substitution dans la table de recherche si nécessaire et après cela, la table de faits respective est mise à jour. La suppression se produit en sens inverse, car la suppression de toutes les données indésirables des tables de faits facilite la suppression des données indésirables liées des tables de dimension.
Nous devons suivre la séquence ci-dessus dans les deux cas, car les tables de dimension et les tables de faits conservent l'intégrité référentielle en permanence.
Types de faits
En fonction du comportement des données des tables de faits, elles sont classées en tant que tables de faits de transaction, tables de faits instantanés et tables de faits instantanés accumulés. Ces trois types suivent des fonctionnalités différentes avec différentes stratégies de chargement de données.
# 1) Tables de faits sur les transactions
Comme son nom l'indique, les tables de faits de transaction stockent des données au niveau de la transaction pour chaque événement qui se produit. Ce type de données est facile à analyser au niveau de la table de faits. Mais pour une analyse plus approfondie, vous pouvez également vous référer aux dimensions associées.
Par exemple, chaque vente (ou) achat effectué sur un site Web de marketing doit être chargé dans une table de faits sur les transactions.
Un exemple de tableau de faits de transaction est présenté ci-dessous.
# 2) Tableaux de faits instantanés périodiques
Comme son nom l'indique, les données de la table de faits des instantanés périodiques sont stockées sous forme d'instantanés (images) à des intervalles périodiques tels que pour chaque jour, semaine, mois, trimestre, etc. en fonction des besoins de l'entreprise.
Il est donc clair qu'il s'agit d'une agrégation de données en permanence. Par conséquent, les faits instantanés sont plus complexes que les tables de faits de transaction. Par exemple, toutes les données des rapports sur les revenus de performance peuvent être stockées dans des tableaux de faits instantanés pour une référence facile.
Un exemple de tableau de faits instantané périodique est présenté ci-dessous.
# 3) Accumuler les tableaux de faits instantanés
L'accumulation de tables de faits instantanés vous permet de stocker des données dans des tables pendant toute la durée de vie d'un produit. Cela agit comme une combinaison des deux types ci-dessus où les données peuvent être insérées par n'importe quel événement à tout moment comme un instantané.
Dans ce type, des colonnes de date et des données supplémentaires pour chaque ligne sont mises à jour avec chaque jalon de ce produit.
Un exemple de tableau de faits instantanés cumulatifs.
En plus des trois types ci-dessus, voici quelques autres types de tables de faits:
# 4) Tableaux de faits sans faits: Un fait est un ensemble de mesures alors que fact less ne capture que des événements (ou) des conditions qui ne contiennent aucune mesure. Une table de faits sans faits est principalement utilisée pour suivre un système. Les données de ces tableaux peuvent être analysées et utilisées pour les rapports.
Par exemple, vous pouvez rechercher des détails sur un employé qui a pris un congé et le type de congé dans un an, etc. En incluant tous ces détails de faits non clairs dans un fait, le tableau augmentera certainement la taille des faits.
Un exemple de tableau de faits sans faits est présenté ci-dessous.
# 5) Tableaux de faits conformes: Un fait conforme est un fait qui peut être référencé de la même manière avec chaque data mart auquel il est lié.
Spécifications d'un tableau de faits
Vous trouverez ci-dessous les spécifications d'un tableau de faits.
- Nom du fait: Il s'agit d'une chaîne qui décrit brièvement les fonctionnalités de la table de faits.
- Processus d'affaires: Les discussions sur l'entreprise doivent être satisfaites par cette table de faits.
- Des questions: Mentionne une liste de questions commerciales auxquelles répondra ce tableau de faits.
- Grain: Indique le niveau de détail le plus bas associé à ces données de table de faits.
- Dimensions: Répertoriez toutes les tables de dimension associées à cette table de faits.
- Les mesures: Les valeurs calculées stockées dans la table de faits.
- Fréquence de charge Représente les intervalles de temps pour charger les données dans la table de faits.
- Lignes initiales: Reportez-vous aux données initiales renseignées dans la table de faits pour la première fois.
Exemple de modélisation de données dimensionnelles
Vous pouvez avoir une idée de la façon dont les tables de dimension et les tables de faits peuvent être conçues pour un système en consultant le diagramme de modélisation des données dimensionnelles ci-dessous pour les ventes et les commandes.
Conclusion
À présent, vous devriez avoir acquis une excellente connaissance des techniques de modélisation de données dimensionnelles, de leurs avantages, mythes, tables de dimensions, tables de faits, ainsi que de leurs types et processus.
Consultez notre prochain tutoriel pour en savoir plus sur les schémas d'entrepôt de données !!
=> Visitez ici pour apprendre l'entreposage de données à partir de zéro.
lecture recommandée
- Tutoriel de test de l'entrepôt de données avec des exemples | Guide de test ETL
- Exemples d'exploration de données: applications les plus courantes de l'exploration de données 2021
- Tutoriel Python DateTime avec des exemples
- Principes de base de l'entreposage de données: un guide ultime avec des exemples
- Tutoriel de test de volume: exemples et outils de test de volume
- Top 10 des outils d'entrepôt de données et des technologies de test les plus populaires
- Exploration de données: processus, techniques et problèmes majeurs dans l'analyse des données
- Comment effectuer des tests basés sur les données dans SoapUI Pro - Tutoriel SoapUI # 14