Dimension des entrepôts de données à évolution lente

Les dimensions, dans la gestion et l’entreposage de données, contiennent des données relativement statiques relatives à des entités telles que les zones géographiques, les clients ou les produits. Les données capturées par les dimensions à évolution lente (DEL) évoluent lentement et de manière imprévisible, et non selon un calendrier régulier.

Certains scénarios peuvent engendrer des problèmes d’intégrité référentielle.

Par exemple, une base de données peut contenir une table de faits stockant les enregistrements de ventes. Cette table de faits est liée aux dimensions par des clés étrangères. L’une de ces dimensions peut contenir des données sur les commerciaux de l’entreprise : par exemple, les agences régionales dans lesquelles ils travaillent. Or, les commerciaux sont parfois mutés d’une agence régionale à une autre. Pour les besoins de l’historique des ventes, il peut être nécessaire de conserver une trace du fait qu’un commercial donné était affecté à une agence régionale donnée à une date antérieure, alors que ce commercial est désormais affecté à une autre agence régionale.

La gestion de ces problèmes implique des méthodologies de gestion des SCD (Systèmes de Définition de la Dimension) désignées par les types 0 à 6. Les SCD de type 6 sont parfois appelés SCD hybrides.

Type 0 : Conservation de l’original

La méthode de type 0 est passive. Elle gère les modifications dimensionnelles sans effectuer d’action. Les valeurs restent inchangées depuis l’insertion initiale de l’enregistrement de dimension. Dans certains cas, l’historique est conservé avec le type 0. Les types d’ordre supérieur sont utilisés pour garantir la conservation de l’historique, tandis que le type 0 offre le contrôle le plus faible, voire aucun. Cette méthode est rarement utilisée.

Type 1 : Écrasement

Cette méthodologie écrase les anciennes données avec les nouvelles et ne conserve donc pas l’historique. Exemple de table fournisseur :

Clé_Fournisseur	Code_Fournisseur	Nom_Fournisseur	État_Fournisseur
123	ABC	Acme Supply Co	CA

Dans cet exemple, Code_Fournisseur est la clé naturelle et Clé_Fournisseur est une clé de substitution. Techniquement, la clé de substitution n’est pas nécessaire, car la ligne est unique grâce à la clé naturelle (Code_Fournisseur). Toutefois, pour optimiser les performances des jointures, utilisez des clés entières plutôt que des clés de caractères (sauf si le nombre d’octets de la clé de caractères est inférieur à celui de la clé entière).

Si le fournisseur transfère son siège social dans l’Illinois, l’enregistrement sera écrasé :

Clé_Fournisseur	Code_Fournisseur	Nom_Fournisseur	État_Fournisseur
123	ABC	Acme Supply Co	IL

L’inconvénient de la méthode de type 1 est l’absence d’historique dans l’entrepôt de données. Son avantage réside cependant dans sa facilité de maintenance.

Si une table agrégée résumant les informations par État a été calculée, elle devra être recalculée lors de toute modification de l’état du fournisseur.

Type 2 : Ajout d’une nouvelle ligne

Cette méthode assure le suivi des données historiques en créant plusieurs enregistrements pour une clé naturelle donnée dans les tables dimensionnelles, avec des clés de substitution distinctes et/ou des numéros de version différents. L’historique est conservé de manière illimitée pour chaque insertion.

Par exemple, si le fournisseur déménage dans l’Illinois, les numéros de version seront incrémentés séquentiellement :

Clé_Fournisseur	Code_Fournisseur	Nom_Fournisseur	État_Fournisseur	Version.
123	ABC	Acme Supply Co	CA	0
124	ABC	Acme Supply Co	IL	1

Une autre méthode consiste à ajouter des colonnes « date d’effet ».

Clé_Fournisseur	Code_Fournisseur	Nom_Fournisseur	État_Fournisseur	Date_Début	Date_Fin
123	ABC	Acme Supply Co	CA	01-Jan-2000	21-Dec-2004
124	ABC	Acme Supply Co	IL	22-Dec-2004	NULL

La valeur nulle de la colonne Date_Fin (ligne 2) indique la version actuelle du tuple. Dans certains cas, une date de substitution standardisée (par exemple, le 31/12/9999) peut être utilisée comme date de fin, afin que le champ puisse être inclus dans un index et que la substitution de valeurs nulles ne soit pas nécessaire lors des requêtes.

Les transactions qui font référence à une clé de substitution particulière (Clé_Fournisseur) sont alors liées de manière permanente aux périodes définies par cette ligne de la table de dimension à évolution lente. Une table agrégée récapitulant les faits par état continue de refléter l’état historique, c’est-à-dire l’état dans lequel se trouvait le fournisseur au moment de la transaction ; aucune mise à jour n’est nécessaire. Pour référencer l’entité via la clé naturelle, il est nécessaire de supprimer la contrainte d’unicité, ce qui rend l’intégrité référentielle par le SGBD impossible.

Si des modifications rétroactives sont apportées au contenu de la dimension, ou si de nouveaux attributs sont ajoutés à la dimension (par exemple, une colonne Sales_Rep) avec des dates d’effet différentes de celles déjà définies, les transactions existantes devront être mises à jour pour refléter la nouvelle situation. Cette opération de base de données peut s’avérer coûteuse ; par conséquent, les SCD de type 2 ne sont pas recommandés si le modèle dimensionnel est susceptible d’évoluer.

Type 3 : Ajout d’un nouvel attribut

Cette méthode suit les modifications à l’aide de colonnes distinctes et conserve un historique limité. Le type 3 ne conserve qu’un historique limité, car il est restreint au nombre de colonnes désignées pour le stockage des données historiques. La structure de table d’origine des types 1 et 2 est identique, mais le type 3 ajoute des colonnes supplémentaires. Dans l’exemple suivant, une colonne supplémentaire a été ajoutée au tableau pour enregistrer l’état initial du fournisseur ; seul l’historique précédent est conservé.

Clé_Fournisseur	Code_Fournisseur	Nom_Fournisseur	État_Fournisseur_Original	Date_d’effet	État_Fournisseur_Actuel
123	ABC	Acme Supply Co	CA	22-Dec-2004	IL

Cet enregistrement contient une colonne pour l’état initial et l’état actuel ; il est impossible de suivre les changements si le fournisseur déménage une seconde fois.

Une variante consiste à créer le champ État_Fournisseur_Précédent au lieu d’État_Fournisseur_Original, ce qui permettrait de suivre uniquement la modification historique la plus récente.

Type 4 : Ajout d’une table d’historique

La méthode de type 4 est généralement appelée « utilisation de tables d’historique ». Une table conserve les données actuelles, tandis qu’une autre table enregistre tout ou partie des modifications. Les deux clés de substitution sont référencées dans la table de faits pour améliorer les performances des requêtes.

Dans l’exemple ci-dessus, le nom de la table d’origine est Fournisseur et celui de la table d’historique est Fournisseur_Historique. Fournisseur

Fournisseur
Clé_fournisseur	Code_fournisseur	Nom_fournisseur	État_fournisseur
124	ABC	Acme & Johnson Supply Co	IL

Historique_fournisseur
Clé_fournisseur	Code_fournisseur	Nom_fournisseur	État_fournisseur	Date_de_création
123	ABC	Acme Supply Co	CA	14-June-2003
124	ABC	Acme & Johnson Supply Co	IL	22-Dec-2004

Cette méthode est similaire au fonctionnement des tables d’audit de bases de données et des techniques de capture des modifications de données.

Type 6 : Hybride

La méthode de type 6 combine les approches des types 1, 2 et 3 (1 + 2 + 3 = 6). L’origine du terme pourrait être attribuée à Ralph Kimball, lors d’une conversation avec Stephen Pace de Kalido. Dans son ouvrage *The Data Warehouse Toolkit*, Ralph Kimball nomme cette méthode « Modifications imprévisibles avec superposition de version unique ». La table Fournisseur contient initialement un enregistrement pour notre fournisseur d’exemple :

Clé_Fournisseur

Clé_Ligne

Code_Fournisseur

Nom_Fournisseur

État_Actuel

État_Historique

État_Début

Date_Fin

Indicateur_Actuel

123

ABC

Acme Supply Co

01-Jan-

2000

31-Dec-

2009

Les valeurs de l’État Actuel et de l’État Historique sont identiques. L’attribut optionnel Indicateur Actuel indique qu’il s’agit de l’enregistrement actuel ou le plus récent pour ce fournisseur. Lorsque la société Acme Supply déménage dans l’Illinois, nous ajoutons un nouvel enregistrement, comme dans le traitement de type 2. Cependant, une clé de ligne est incluse afin de garantir une clé unique pour chaque ligne :

Clé_Fournisseur

Clé_Ligne

Code_Fournisseur

Nom_Fournisseur

État_Actuel

État_Historique

Date_Début

Date_Fin

Indicateur_Actuel

123

ABC

Acme Supply Co

01-Jan-

2000

21-Dec-

2004

123

ABC

Acme Supply Co

22-Dec-

2004

31-Dec-

2009

Nous remplaçons les informations de l’indicateur actuel dans le premier enregistrement (Clé_Ligne = 1) par les nouvelles informations, comme dans le traitement de type 1. Nous créons un nouvel enregistrement pour suivre les modifications, comme dans le traitement de type 2. Enfin, nous stockons l’historique dans une seconde colonne État (État_Historique), ce qui intègre le traitement de type 3. Par exemple, si le fournisseur déménageait à nouveau, nous ajouterions un enregistrement à la dimension Fournisseur et écraserions le contenu de la colonne État_Actuel :

Clé_Fournisseur	Clé_Ligne	Code_Fournisseur	Nom_Fournisseur	État_Actuel	État_Historique	Date_Début	Date_Fin	Indicateur_Actuel
123	1	ABC	Acme Supply Co	NY	CA	01-Jan- 2000	21-Dec- 2004	N
123	2	ABC	Acme Supply Co	NY	IL	22-Dec- 2004	03-Feb- 2008	N
123	3	ABC	Acme Supply Co	NY	NY	04-Feb- 2008	31-Dec- 2009	Y

Notez que, pour l’enregistrement actuel (Indicateur_Actuel = « Y »), les valeurs de l’État_Actuel et de l’État_Historique sont toujours identiques.

Implémentation des faits de type 2 / type 6

Clé de substitution de type 2 avec attribut de type 3

Dans de nombreuses implémentations SCD de type 2 et de type 6, la clé de substitution issue de la dimension est insérée dans la table de faits à la place de la clé naturelle lors du chargement des données de faits dans le référentiel de données. La clé de substitution est sélectionnée pour un enregistrement de fait donné en fonction de sa date d’effet et des valeurs Date_Début et Date_Fin de la table de dimension. Cela permet de joindre facilement les données de faits aux données de dimension appropriées pour la date d’effet correspondante.

Voici la table Fournisseur telle que nous l’avons créée ci-dessus à l’aide de la méthodologie hybride de type 6 :

Clé_Fournisseur	Code_Fournisseur	Nom_Fournisseur	État_Actuel	État_Historique	Date_Début	Date_Fin	Indicateur_Actuel
123	ABC	Acme Supply Co	NY	CA	01-Jan- 2000	21-Dec- 2004	N
124	ABC	Acme Supply Co	NY	IL	22-Dec- 2004	03-Feb- 2008	N
125	ABC	Acme Supply Co	NY	NY	04-Feb- 2008	31-Dec- 9999	Y

Une fois que la table Livraison contient la clé Fournisseur correcte, elle peut être facilement jointe à la table Fournisseur à l’aide de cette clé. La requête SQL suivante récupère, pour chaque enregistrement de fait, l’état actuel du fournisseur et l’état dans lequel il se trouvait au moment de la livraison :

SELECT
delivery.delivery_cost,
supplier.supplier_name,
supplier.historical_state,
supplier.current_state
FROM delivery
INNER JOIN supplier
ON delivery.supplier_key = supplier.supplier_key

Implémentation de type 6 pure

L’utilisation d’une clé de substitution de type 2 pour chaque période peut poser problème si la dimension est susceptible d’évoluer.

Une implémentation de type 6 pure n’utilise pas cette méthode, mais une clé de substitution pour chaque élément de données de base (par exemple, chaque fournisseur unique possède une seule clé de substitution).

Cela évite que toute modification des données de base n’ait un impact sur les informations système.

Elle offre également davantage d’options lors de l’interrogation des transactions.

Voici la table Fournisseur utilisant la méthodologie de type 6 pure :

Clé_Fournisseur	Code_Fournisseur	Nom_Fournisseur	État_Fournisseur	Date_Début	Date_Fin
456	ABC	Acme Supply Co	CA	0i-Jan-2000	2l-Dec-2004
456	ABC	Acme Supply Co	IL	22-Dec-2004	03-Feb-2008
456	ABC	Acme Supply Co	NY	04-Feb-2008	3i-Dec-9999

L’exemple suivant montre comment la requête doit être étendue pour garantir la récupération d’un seul enregistrement de fournisseur pour chaque transaction. SELECT

SELECT
supplier.supplier_code,
supplier.supplier_state
FROM supplier
INNER JOIN delivery
ON supplier.supplier_key = delivery.supplier_key
AND delivery.delivery_date BETWEEN supplier.start_date AND supplier.end_date

Un enregistrement de fait avec une date d’effet (Delivery_Date) du 9 août 2001 sera lié au Supplier_Code ABC, avec un Supplier_State « CA ». Un enregistrement de fait avec une date d’effet du 11 octobre 2007 sera également lié au même Supplier_Code ABC, mais avec un Supplier_State « IL ».

Bien que plus complexe, cette approche présente plusieurs avantages, notamment :

L’intégrité référentielle par le SGBD est désormais possible, mais il est impossible d’utiliser Supplier_Code comme clé étrangère dans la table Product et, en utilisant Supplier_Key comme clé étrangère, chaque produit est lié à une période spécifique.
Si la donnée de fait comporte plusieurs dates (par exemple, date de commande, date de livraison, date de paiement de la facture), vous pouvez choisir celle à utiliser pour une requête.
Vous pouvez effectuer des requêtes « à l’heure actuelle », « à la date de la transaction » ou « à un instant précis » en modifiant la logique de filtrage des dates.
Il n’est pas nécessaire de retraiter la table de fait en cas de modification de la table de dimension (par exemple, l’ajout rétroactif de champs supplémentaires modifiant les périodes, ou la correction facile d’erreurs de dates dans la table de dimension).
Vous pouvez introduire des dates bi-temporelles dans la table de dimension.
Vous pouvez joindre la donnée de fait à plusieurs versions de la table de dimension pour permettre la génération de rapports sur les mêmes informations avec différentes dates d’effet, dans une même requête.

L’exemple suivant illustre l’utilisation d’une date spécifique telle que « 2012-01-01 00:00:00 » (qui pourrait correspondre à la date et l’heure actuelles). SELECT

SELECT
delivery.delivery_cost,
supplier.supplier_name,
supplier.supplier_state
FROM delivery
INNER JOIN supplier
ON delivery.supplier_code = supplier.supplier_code
WHERE supplier.current_flag = ‘Y’

Clé de substitution et clé naturelle

Une autre implémentation consiste à placer à la fois la clé de substitution et la clé naturelle dans la table de faits. Cela permet à l’utilisateur de sélectionner les enregistrements de dimension appropriés en fonction :

de la date d’effet principale de l’enregistrement de faits (ci-dessus),
des informations les plus récentes ou actuelles,
de toute autre date associée à l’enregistrement de faits.

Cette méthode permet des liens plus flexibles avec la dimension, même si l’approche de type 2 a été utilisée au lieu de celle de type 6.

Voici la table Fournisseur telle que nous aurions pu la créer avec la méthodologie de type 2 :

Clé_fournisseur	Code_fournisseur	Nom_fournisseur	État_fournisseur	Date_début	Date_fin	Indicateur_actuel
123	ABC	Acme Supply Co	CA	01-Jan- 2000	21-Dec- 2004	N
124	ABC	Acme Supply Co	IL	22-Dec- 2004	03-Feb- 2008	N
125	ABC	Acme Supply Co	NY	04-Feb- 2008	31-Dec- 9999	Y

La requête SQL suivante récupère les valeurs les plus récentes de Supplier_Name et Supplier_State pour chaque enregistrement :

SELECT
delivery.delivery_cost,
supplier.supplier_name,
supplier.supplier_state
FROM delivery
INNER JOIN supplier
ON delivery.supplier_code = supplier.supplier_code
WHERE supplier.current_flag = ‘Y’

Si plusieurs dates sont associées à l’enregistrement de fait, il est possible de joindre ce fait à la dimension en utilisant une autre date que la date d’effet principale. Par exemple, la table Delivery peut avoir comme date d’effet principale Delivery_Date, mais également une Order_Date associée à chaque enregistrement.

La requête SQL suivante récupère les valeurs Supplier_Name et Supplier_State correctes pour chaque enregistrement de fait en fonction de l’Order_Date :

SELECT
delivery.delivery_cost,
supplier.supplier_name,
supplier.supplier_state
FROM delivery
INNER JOIN supplier
ON delivery.supplier_code = supplier.supplier_code
AND delivery.order_date BETWEEN supplier.start_date AND supplier.end_date

Quelques mises en garde :

L’intégrité référentielle par le SGBD n’est pas possible, car il n’existe pas d’identifiant unique pour établir la relation. • Si une relation est établie avec un substitut pour résoudre le problème ci-dessus, on obtient une entité liée à une période spécifique.
Si la requête de jointure est mal formulée, elle peut renvoyer des lignes dupliquées et/ou des résultats incorrects.
La comparaison des dates peut être peu performante.
Certains outils de Business Intelligence ne gèrent pas correctement la génération de jointures complexes.
Les processus ETL nécessaires à la création de la table de dimension doivent être conçus avec soin afin d’éviter tout chevauchement des périodes pour chaque élément de données de référence distinct.
La plupart des problèmes ci-dessus peuvent être résolus à l’aide du diagramme mixte d’un modèle SCD ci-dessous.

Types combinés

Différents types de SCD peuvent être appliqués à différentes colonnes d’une table. Par exemple, on peut appliquer le type 1 à la colonne Supplier_Name et le type 2 à la colonne Supplier_State de la même table.

Modèle SCD

Source: Drew Bentley, Business Intelligence and Analytics. © 2017 Library Press, License CC BY-SA 4.0. Traduction et adaptation: Nicolae Sfetcu. © 2025 MultiMedia Publishing, L’informatique décisionnelle et l’analyse exploratoire des données dans les entreprises, Collection Sciences de l’information