Modèle de données : Problématique et résolution pas à pas

Raphaël a posé une question sur notre forum. Le titre de sa question était : Fonction RELATED ne fonctionne pas. On reçoit souvent ce type de question sur le forum. La plupart du temps, le problème ne vient pas de la mesure elle-même mais plutôt du modèle de données sous-jacent. Suite à cette question (et à plusieurs autres reçues par le passé), j’ai décidé de rédiger l’article ci-dessous, qui passe à travers toutes les étapes que vous devriez suivre lorsque vient le temps de travailler une problématique en lien avec un modèle de données et/ou une mesure en DAX, que ce soit dans Excel via Power Pivot ou dans Power BI. Cet article vise donc à expliquer comment créer un modèle de données de royautés efficace dans Power Pivot ou Power BI.

Mise en contexte : modèle de données de royautés

Raphaël cherche à calculer les royautés à verser selon les ventes de CDs réalisées. Les taux de royautés dépendent du CD lui-même (ci-dessous nommé Référence), de l’artiste et du type de ventes (physique vs digital).

Voici le modèle de données proposé par Raphaël.

Voici maintenant le contenu de chacune de ses tables.

Table de ventes, nommée Sales dans son modèle de données:

Table de références:

Table de plateformes:

Table de royautés, nommée Artist-Royalties dans son modèle de données:

Cheminement de ma réflexion concernant ce modèle de données de royautés

Mon premier réflexe a été de noter qu’il manquait des tables dans ce modèle de données, dont la table de dates, essentielle à tout modèle, surtout si l’on veut éventuellement calculer les royautés YTD et/ou comparer les royautés de l’année vs celles de l’année précédente. J’ai aussi noté que nous étions en présence de 2 tables de faits, soit la table de Ventes et la table de Royautés. Finalement, j’ai noté que la table de Royautés ne devait pas posséder deux colonnes distinctes pour ses royautés physiques vs digitales (à ce sujet, je vous recommande ma série de 3 articles intitulée : Power BI: 15 erreurs courantes chez les débutants). Je suis donc partie avec cette réflexion et j’ai apporté les modifications suivantes aux tables du modèle de données de Raphaël.

Vous avez des sources de données volumineuses à rassembler et analyser dans Excel ? Apprenez à automatiser tout le processus d’importation, de transformation et de modélisation  des données avec nos formations sur Power Query et Power Pivot dans Excel.

Table de ventes proposée

Dans la table de ventes que je propose et que j’ai nommée FaitsVentes, pour bien la distinguer des tables de dimensions, je n’ai apporté que deux changements. D’abord, j’ai changé les données de la colonne Type, qui étaient en texte, par un nombre. J’ai aussi ajouté une colonne qui propose les concaténations des colonnes de références et de types. Ces modifications ont été faites par le biais du langage M et des autres fonctionnalités de l’éditeur de requêtes Power Query.

Ce faisant, j’ai proposé une nouvelle table que j’ai nommée DimTypes et qui comprend les valeurs suivantes:

Table de royautés proposée

Dans la table de royautés que je propose et que j’ai nommée FaitsRoyautés, j’ai dépivoté les colonnes de type, que j’ai aussi converties en nombres et j’ai également ajouté une colonne de type RefTyp. Encore une fois, tout ça a été effectué dans l’éditeur de requête Power Query.

Table DimRefTypes proposée

J’ai créé une table de dimensions DimRefTypes, à partir de la table de ventes (combinaisons uniques de références et de types) et j’ai aussi ajouté une colonne TauxRoyauté qui fait la somme des taux de royautés de la table FaitsRoyautés, pour chacune des lignes.

Je n’ai rien modifié aux tables de dimensions de plateformes et de références mais je les ai renommées DimPlateformes et DimRéférences.

Table de dates proposée

J’ai ajouté une table de dates dynamique, que j’ai créée avec Power Query et qui est liée à la table de ventes.

Modèle de données de royautés proposé

J’ai d’abord créé le modèle de données suivants:

Pour montrer à Raphaël que la fonction RELATED fonctionnait maintenant, j’ai d’abord ajouté une colonne calculée dans la table de ventes.

Comme il n’est ni nécessaire ni recommandé de créer des colonnes calculées inutilement, j’ai remplacé le tout par la mesure itératives suivante:

Ça m’a permis de créer les tables suivantes:

Mais lorsque j’ai voulu présenter le tout par artiste, j’ai obtenu ceci:

De toute évidence, j’avais oublié une réalité qui devenait maintenant évidente… Nous sommes en présence d’une relation plusieurs à plusieurs. En effet, un artiste peut avoir participé à plusieurs albums et un album peut avoir été enregistré par plusieurs artistes.

J’ai donc corrigé subtilement mon modèle de données pour créer une relation plusieurs à plusieurs entre mes deux tables de faits.

Ceci m’a permis d’analyser les ventes et les royautés par artiste.

De même, j’ai pu détailler le résultat par date.

En fait, il est maintenant possible de le détailler par n’importe quelle dimension du modèle, comme le type de ventes, par exemple.

À noter que d’autres approches de modélisation auraient pu être utilisées. L’important est de vous poser les bonnes questions et d’avoir les bons réflexes, lorsque vient le temps de construire un modèle de données et des mesures en DAX.

Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

Formations chaudement recommandées

Si vous souhaitez acquérir une base solide en modélisation de données, je vous suggère fortement notre formation Excel – Introduction à Power Pivot et aux modèles de données, dont le module de modélisation de données vient tout juste d’être mis à jour. Je vous recommande ensuite la formation Introduction au langage DAX, mais seulement après avoir fait des tentatives de votre côté. C’est seulement à ce moment-là que les éléments de cette formation sauront vous ouvrir la voie.

Voici quelques commentaires d’apprenants ayant suivi la formation Excel – Introduction à Power Pivot et aux modèles de données :

La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.