Les SCD (dimensions à variation lente ou tout simplement dimension de type 2 dans un espace Kimballien) sont un grand classique en BI.
Du fait que Power BI soit sur un modèle de Annule-Remplace, il est difficile d’implémenter ce type de dimension. D’autant plus que la gestion des Surrogate Keys n’est pas native non plus.

Essayons de résoudre quand même un cas de SCD en Power BI.

J’ai des utilisateurs qui ont un statut qui peut changer. Ces utilisateurs se trouvent dans une table Users qui ne me donne que le dernier statut (ie. celui en cours).
Les utilisateurs déclarent des activités, qui seront les faits que je calcule (partons sur un simple Count).

Évidemment, je souhaite connaître les activités par statut et je rappelle que celui-ci peut changer. Je dois donc connaître le statut de l’utilisateur au moment où il a déclaré son activité. Il va me falloir historiser l’information, c’est le principe même de la type II.

Pour casser le suspens tout de suite et éviter toute méprise, ce n’est pas Power BI qui va historiser. Comme je le disais, Power BI fait toujours du Annule-et-Remplace des tables du modèle.

La clé, c’est votre backend

La clé, c’est votre backend. C’est lui qui doit vous renvoyer les données avec les informations d’historique, d’une façon ou d’une autre.
Dans mon cas, j’ai choisi comme solution d’opérer un changement de granularité. En effet, intrinsèquement, le statut n’est pas porté par l’utilisateur mais par une autre entité (ie. une autre table).
Pour vous rendre cela plus concret, disons que cette table contient les abonnements des utilisateurs. À un instant T, un utilisateur n’a qu’un abonnement et cet abonnement possède un statut.
(Mais ne focalisez pas trop sur ces entités, j’ai anonymisé mon vrai use case)

On obtiendrait donc un flocon si je parle en terme Kimballiens.

Flocon que je peux star-ifier (mettre sous forme d’un schéma en étoile – star-schema) en aplatissant la jointure. Les modèles tabulaires s’en sortent mieux avec des tables dénormalisées (Tabulaire == tables)

Le problème maintenant est : comment je relie ma table de faits (activités) avec cette nouvelle dimension qui a pour clé l’identifiant de l’abonnement. En effet, je connais l’utilisateur mais pas son abonnement dans mes faits.

On va devoir le déduire de la date de l’activité. Cela suppose que la requête qui construit la dimension doit ramener également des informations temporelles de l’historique. On utilise très souvent deux champs dates : début et fin (avec la valeur nulle pour la ligne « en cours ») comme vous pouvez le voir sur les captures.
Note: Si vous n’avez pas ces informations, il va falloir déterminer une autre règle pour déduire l’abonnement à date (un et un seul). Ca se fait en creusant la problématique avec le métier et en fouillant dans les données.

Je pourrais simplifier et vous dire de faire la jointure en T-SQL (ou dans le langage de votre backend) mais dans mon cas, le problème est que je n’ai pas la main pour le faire (imaginez que les activités et les abonnements sont dans des sources de données différentes).
Je dois donc changer la granularité de mes faits avec Power BI.

Revenons au T-SQ, j’écrirai ma jointure de cette façon :

Select abo_id
From Activités
Inner Join Abonnements
on Activités.user_id = Abonnements.user_id
and Activités.Date >= Abonnements.date_debut
and (Activités.Date < Abonnements.date_fin
or Abonnements.date_fin is null)

Soit, traduisons en langage M. Il y a bien des fonctions de jointure dans Power Query mais aucune ne permet de se baser sur une inégalité ou un OR. On peut voir ci-dessous qu’il y a les INNER, LEFT, RIGHT, FULL OUTER mais rien permettant de coder notre inégalité.
Il en est de même par code avec la fonction Table.Join ou Table.NestedJoin.

Comme pour la syntaxe SQL, on va décomposer ce qu’on veut faire. La condition (l’inégalité) portée par la jointure peut très bien être descendue dans la clause Where.
On rajoute donc la fonction Table.SelectRows pour filtrer la jointure et exclure les abonnements qui ne collent pas avec les dates

...
Jointure = Table.NestedJoin(Activités_Table,{"user_id"},Abonnements,{"user_id"},"InfosAbo",JoinKind.LeftOuter),
Developper_InfosAbo = Table.ExpandTableColumn(Jointure, "InfosAbo", {"abo_id", "date_debut", "date_fin"}, {"abo_id", "date_debut", "date_fin"}),
Clause_WHERE = Table.SelectRows(Developper_InfosAbo, each ([date] >= [date_debut] and ([date] <= [date_fin] or [date_fin] = null)))
...

Nous avons donc réussi à injecter abo_id dans la table de faits et descendre sa granularité.

Je n’ai plus qu’à faire la jointure sur ma dimension à variation lente pour analyser en fonction du statut de l’abonnement au moment de l’activité.

Attention : soyez très vigilant avec ce type de dimension. La volumétrie est forcément plus conséquente. Si vous avez 10 000 utilisateurs qui changent tous les jours de statut, vous allez exploser votre volumétrie. C’est pour cela qu’on parle de « variation lente ». Si ce n’est pas le cas, il faut revoir votre modélisation et avoir une dimension Statut directement rattachée à votre table de faits.

Happy End !