Slowly Changing Dimension (SCD)

SCD - Les dimensions à évolution lente▶ Présenter

12345678

• 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, cours d'une action) n'est pas une SCD : on le stocke plutôt dans la table de faits.
• Source

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

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

• Exemples : date de naissance, date d'ouverture du magasin, code pays d'origine.
• Dans notre exemple, la colonne Magasin (le nom) pourrait être de type 0 si l'on décide qu'un magasin ne change jamais de nom.

• La vente de mars 2023, réalisée sous Pierre, est attribuée à Karim : c'est comme si Karim avait toujours dirigé le magasin.
• Pierre disparaît des rapports.
• Dans Power BI, une actualisation complète (non incrémentielle) de la table de dimension produit exactement un type 1.
• Source

• DateDébut : date à partir de laquelle la version est valable ;
• DateFin : dernier jour de validité ; la version courante reçoit une date lointaine (31/12/9999) ou reste vide ;
• EstCourant (ou Actif) : VRAI pour la version courante, pour filtrer rapidement.

• Chaque vente reste attachée au manager en poste à l'époque.
• Le total par magasin reste juste : Magasin Un = 2 500, réparti sur deux versions.
• Convention à fixer et à documenter : ici DateFin est le dernier jour inclus ; d'autres entrepôts mettent DateFin = DateDébut de la version suivante (borne exclue).
• Source

• Utile pour comparer « organisation actuelle » et « organisation précédente » après une réorganisation.
• Limite : un seul niveau d'historique ; au changement suivant, Pierre est perdu.
• Les ventes ne sont pas réparties par période : on choisit la colonne selon la question posée.
• Réservé à un petit nombre de colonnes ; souvent combiné aux types 1 et 2 dans le type 6.

• Par Manager : vue historique (Pierre 1 000, Karim 1 500, Alice 800).
• Par ManagerActuel : vue « organisation d'aujourd'hui » (Karim 2 500, Alice 800).
• Le nom « 6 » vient de 1 + 2 + 3 : on écrase (1), on versionne (2) et on garde deux points de vue en colonnes (3).

• Type 1 : natif, c'est l'actualisation complète de la dimension.
• Type 2 : Power Query ne conserve pas les données d'une actualisation à l'autre ; il ne peut donc pas détecter les changements ni fermer des versions. L'historisation doit être faite en amont :
  • entrepôt de données (SQL Server, Fabric Warehouse) alimenté par un ETL ;
  • Dataflow Gen2 ou pipeline Fabric qui écrit dans une table de destination ;
  • source qui historise déjà (ERP, table temporelle SQL).
• Power BI consomme ensuite une dimension déjà historisée.
• Source | Source (tutoriel Dataflow Gen2 type 2)

1. Importer la dimension Magasins historisée (avec MagasinSK, MagasinAK, DateDébut, DateFin, EstCourant).
2. Si la table de faits contient déjà MagasinSK : créer la relation Ventes[MagasinSK] (*) → Magasins[MagasinSK] (1), direction unique.
3. Si la table de faits ne contient que MagasinAK et une date : faire la recherche temporelle dans Power Query (ci-dessous), puis créer 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 (Date).
• Cette étape casse le query folding : sur une grosse table de faits, faire la recherche dans la source (SQL) plutôt que dans Power Query.

[Capture à insérer] Power BI Desktop, vue Modèle : les tables Ventes et Magasins reliées par MagasinSK, cardinalité plusieurs-à-un, avec les colonnes MagasinAK, DateDébut, DateFin et EstCourant visibles dans Magasins.

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

• Résultat : Magasin Un (Pierre, depuis le 01/01/2000) et Magasin Un (Karim, depuis le 17/06/2023).
• Créer une hiérarchie Magasin > Version pour explorer du membre vers ses versions.
• Compter les membres sur la clé naturelle, pas sur la clé de substitution :

Nombre de magasins = DISTINCTCOUNT ( Magasins[MagasinAK] )

• Source

• De préférence dans Power Query : référencer Magasins, filtrer EstCourant = VRAI, garder MagasinAK et Manager, puis fusionner cette requête avec Magasins sur MagasinAK et renommer la colonne en ManagerActuel.
• Sinon en DAX (colonne calculée dans Magasins) :

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

• Le rapport peut alors proposer les deux lectures : Manager (à l'époque) et Manager actuel (aujourd'hui).
• Un segment sur EstCourant permet de n'afficher que les versions en cours dans les listes.

[Capture à insérer] Rapport Power BI : une matrice avec la hiérarchie Magasin > Version en lignes et la mesure Montant en valeurs, Magasin Un développé sur ses deux versions ; à côté, un histogramme groupé du montant par Manager actuel.

• Ne jamais vider puis recharger 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.
• Choisir les colonnes historisées : seules celles qui doivent garder la perspective historique passent en type 2 ; les autres restent en type 1, sinon les versions se multiplient.
• Trop de versions pour un même membre : signe que l'attribut change trop vite et devrait aller dans la table de faits.
• Former les utilisateurs : expliquer pourquoi un même magasin apparaît plusieurs fois et quelle colonne utiliser selon la question.
• Actualisation incrémentielle : elle ne crée pas d'historique de la dimension ; elle sert à ne recharger que les partitions récentes d'une table de faits.
• Source

• Comprendre le schéma en étoile et son importance pour Power BI | Microsoft Learn
• Modélisation dimensionnelle dans Fabric Warehouse : tables de dimension | Microsoft Learn
• Implémenter une dimension à variation lente de type 2 avec Dataflow Gen2 | Microsoft Learn
• Choose between slowly changing dimension types | Microsoft Learn (formation)