Slowly Changing Dimension

En bref

Objectifs : distinguer clé de substitution et clé naturelle ; choisir un type de dimension à évolution lente (0, 1, 2, 3 ou 6) ; relier les faits à la bonne version d'un membre ; proposer une vue historique et une vue actuelle.
Prérequis : pages Schéma en étoile et Relations ; fusion de requêtes dans Power Query.
Niveau : avancé.

Pourquoi ?

Une dimension à évolution lente (Slowly Changing Dimension, SCD) est une dimension dont certains attributs changent de temps en temps : le manager d'un magasin, la région d'un commercial, l'adresse d'un client. La question à se poser est simple : quand un attribut change, les ventes passées doivent-elles suivre la nouvelle valeur ou garder l'ancienne ? Le « type » de SCD est la réponse à cette question.

Une dimension peut mélanger plusieurs types (type 1 pour l'adresse e-mail, type 2 pour la région). Un attribut qui change souvent (prix d'un produit) n'est pas une SCD : on le stocke plutôt dans la table de faits. Source

Notions clés

Les deux clés d'une dimension

Clé	Autres noms	Rôle
Clé de substitution (Surrogate Key, SK)	Clé technique, auto-incrément, colonne IDENTITY	Identifie une ligne de la dimension ; c'est elle qui relie la dimension aux faits
Clé naturelle (Alternate Key, AK)	Clé métier, clé dans la source	Identifie le membre dans le système source (code magasin, matricule)

Sans historique, les deux clés désignent la même chose. Avec historique (type 2), un membre a plusieurs lignes, donc plusieurs SK pour une même AK : la clé de substitution devient obligatoire. Source

L'exemple fil rouge

Trois magasins, chacun avec un manager : M01 Magasin Un (Pierre), M02 Magasin Deux (Alice), M03 Magasin Trois (Jean). Trois ventes :

Date	MagasinAK	Montant
10/03/2023	M01	1 000
05/09/2023	M01	1 500
12/09/2023	M02	800

Le changement : à partir du 17/06/2023, Karim remplace Pierre à la tête du Magasin Un. Question métier : quel est le chiffre d'affaires par manager ?

Les types de SCD

Type	Principe	Historique	Usage typique
0	Conserver : la valeur ne change jamais	Sans objet	Date de naissance, date d'ouverture
1	Écraser l'ancienne valeur	Non	Corrections, e-mail, libellés
2	Fermer la version courante et ajouter une ligne	Complet	Région d'un commercial, manager d'un magasin
3	Garder la valeur actuelle et la précédente dans deux colonnes	Limité	Réorganisation ponctuelle
6	Hybride (1 + 2 + 3) : type 2 plus une colonne « valeur actuelle »	Complet + vue actuelle	Analyser « à l'époque » et « aujourd'hui »

Les types 1 et 2 sont de loin les plus courants ; le type 3 est rare dans un modèle sémantique. Source

Type 1 | Écraser

MagasinSK	MagasinAK	Magasin	Manager
1	M01	Magasin Un	Karim
2	M02	Magasin Deux	Alice
3	M03	Magasin Trois	Jean

Résultat : Karim 2 500, Alice 800. La vente de mars, réalisée sous Pierre, est attribuée à Karim, et Pierre disparaît des rapports. Dans Power BI, une actualisation complète de la dimension produit exactement un type 1. Source

Type 2 | Historiser

On ajoute une ligne à chaque changement, avec trois colonnes de suivi : DateDébut, DateFin (31/12/9999 pour la version courante) et EstCourant (VRAI pour la version courante).

MagasinSK	MagasinAK	Magasin	Manager	DateDébut	DateFin	EstCourant
1	M01	Magasin Un	Pierre	01/01/2000	16/06/2023	FAUX
2	M02	Magasin Deux	Alice	01/01/2000	31/12/9999	VRAI
3	M03	Magasin Trois	Jean	01/01/2000	31/12/9999	VRAI
4	M01	Magasin Un	Karim	17/06/2023	31/12/9999	VRAI

La table de faits pointe vers la version valable à la date de la vente : MagasinSK 1 pour la vente de mars, 4 pour celle de septembre, 2 pour Alice. Résultat : Pierre 1 000, Karim 1 500, Alice 800, et le total du Magasin Un reste 2 500. Fixez et documentez la convention de DateFin : ici le dernier jour inclus ; d'autres entrepôts utilisent la date de début de la version suivante.

Type 3 | Valeur précédente

Deux colonnes, ManagerActuel (Karim) et ManagerPrécédent (Pierre), sans nouvelle ligne. On choisit la colonne selon la question posée. Limite : un seul niveau d'historique ; au changement suivant, Pierre est perdu.

Type 6 | Hybride

On part du type 2 et on ajoute une colonne ManagerActuel, écrasée sur toutes les versions du membre (Karim sur les lignes 1 et 4). Par Manager, on obtient la vue historique ; par ManagerActuel, la vue « organisation d'aujourd'hui » (Karim 2 500, Alice 800).

Pas à pas

Power Query ne conserve pas les données d'une actualisation à l'autre : il ne peut ni détecter les changements ni fermer des versions. L'historisation de type 2 se fait en amont (entrepôt alimenté par un ETL, Dataflow Gen2 ou pipeline Fabric qui écrit dans une table de destination, source qui historise déjà). Power BI consomme ensuite la dimension historisée. Source

Étape 1 | Relier les faits à la bonne version

1. Importez la dimension Magasins historisée (MagasinSK, MagasinAK, DateDébut, DateFin, EstCourant).

2. Si la table de faits contient déjà MagasinSK, créez la relation Ventes[MagasinSK] (*) vers Magasins[MagasinSK] (1), à sens unique.

3. Si elle ne contient que MagasinAK et une date, faites la recherche temporelle dans la requête Ventes (éditeur avancé), puis créez la même relation :

let
    Source = VentesBrutes,
    Fusion = Table.NestedJoin(Source, {"MagasinAK"}, Magasins, {"MagasinAK"}, "Versions", JoinKind.LeftOuter),
    AjoutSK = Table.AddColumn(Fusion, "MagasinSK", each
        let
            Version = Table.SelectRows([Versions], (v) => v[DateDébut] <= [Date] and [Date] <= v[DateFin])
        in
            if Table.IsEmpty(Version) then -1 else Version{0}[MagasinSK],
        Int64.Type),
    Résultat = Table.RemoveColumns(AjoutSK, {"Versions"})
in
    Résultat

La valeur -1 renvoie vers un membre « Inconnu » à ajouter dans la dimension, pour ne perdre aucune vente. Les colonnes Date, DateDébut et DateFin doivent avoir le même type. Cette étape casse le query folding : sur une grosse table de faits, faites la recherche dans la source SQL.

[Capture : vue Modèle, tables Ventes et Magasins reliées par MagasinSK en plusieurs-à-un, colonnes MagasinAK, DateDébut, DateFin et EstCourant visibles dans Magasins]

Étape 2 | Rendre les versions lisibles

Un utilisateur qui voit deux fois « Magasin Un » dans un segment ne comprend pas pourquoi. Ajoutez une colonne Version dans la requête Magasins :

= Table.AddColumn(Précédent, "Version", each
    [Magasin] & " (" & [Manager] & ", depuis le " & Date.ToText([DateDébut], [Format = "dd/MM/yyyy"]) & ")",
    type text)

Créez ensuite une hiérarchie Magasin > Version, et comptez les membres sur la clé naturelle :

Nombre de magasins = DISTINCTCOUNT ( Magasins[MagasinAK] )

Étape 3 | Ajouter la vue actuelle

Si la dimension ne fournit pas de colonne ManagerActuel, calculez-la de préférence dans Power Query : référencez Magasins, filtrez EstCourant = VRAI, gardez MagasinAK et Manager, puis fusionnez avec Magasins sur MagasinAK. À défaut, en colonne calculée DAX :

Manager actuel =
CALCULATE (
    MAX ( Magasins[Manager] ),
    ALLEXCEPT ( Magasins, Magasins[MagasinAK] ),
    Magasins[EstCourant] = TRUE ()
)

[Capture : matrice avec la hiérarchie Magasin > Version en lignes et le Montant en valeurs, Magasin Un développé sur ses deux versions ; à côté, un histogramme du montant par Manager actuel]

Pièges et bonnes pratiques

Ne videz jamais puis rechargez une dimension de type 2 générée par l'entrepôt : les clés de substitution changeraient et les versions passées seraient perdues.
N'historisez que les colonnes qui doivent garder la perspective historique ; les autres restent en type 1.
Trop de versions pour un même membre signale un attribut qui devrait aller dans la table de faits.
Expliquez aux utilisateurs pourquoi un magasin apparaît plusieurs fois et quelle colonne utiliser.
L'actualisation incrémentielle ne crée pas d'historique de dimension : elle ne recharge que les partitions récentes d'une table de faits. Source

À retenir

Le type de SCD répond à la question : les faits passés suivent-ils la nouvelle valeur ?
Type 1 : on écrase ; type 2 : on ajoute une ligne ; type 6 : historique et vue actuelle.
Power BI consomme une dimension historisée en amont, il ne l'historise pas.
Les faits se relient à la clé de substitution ; les membres se comptent sur la clé naturelle.

Pour aller plus loin

Comprendre le schéma en étoile (dimensions à variation lente). Source (Microsoft Learn)
Modélisation dimensionnelle dans Fabric Warehouse : tables de dimension. Source (Microsoft Learn)
Implémenter une dimension à variation lente de type 2 avec Dataflow Gen2. Source (Microsoft Learn)