Contact: 514-605-7112 / info@lecfomasque.com

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

Publié le : 31 janvier 2019

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.

 

Mise en contexte

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.

Modèle données initial

 

Voici maintenant le contenu de chacune de ses tables.

 

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

Table Ventes Initiale

 

Table de références:

Table Références Initiales

 

Table de plateformes:

Table Plateformes Initiale

 

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

Table Royautés Initiale

 

Cheminement de ma réflexion

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.

 

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.

Table FaitsVentes

 

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

TableDimTypes

 

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.

TableFaitsRoyautés

 

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.

TableRefTypes

 

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 DimPlateformes

 

Table 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.

Table DimDates

 

Modèle de données proposé

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

Modèle de données proposé

 

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

Colonne calculée related

 

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:

Mesure Royautés

 

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

Calcul Royautés v1

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

Royautés par artiste v1

 

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.

Modèle de données plusieurs à plusieurs

 

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

Royautés par artiste v2

 

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

Calcul de royautés v2

 

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.

Royautés par type et artiste

 

À 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.

 

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.

 


NOTRE OFFRE DE FORMATIONS


Microsoft Most Valuable Professional

Le CFO masqué vous offre un vaste choix de formations Excel et Power BI, réparties dans 8 catégories: Excel – Tableaux de bord, Excel – Modélisation financière, Excel – VBA, Excel – Power Tools, Excel – Ninja, Power BI, Finance corporative et Compléments avancés. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et ils sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

 

Découvrez quelles formations vous conviennent

Pour info: 514-605-7112 ou info@lecfomasque.com

 

Sophie Marchand

Sophie Marchand, M.Sc., CPA, CGA, MVP, est détentrice d’une M.Sc. en finance corporative et d’un titre comptable CPA, CGA. Elle est également MVP Office Apps and Services (Excel) et MVP Data Platform (Power BI). Le titre de MVP est un titre honorifique remis par Microsoft et signifie « Most Valuable Professional ». Il est attribué à des experts de solutions Microsoft qui se distinguent non seulement par leurs compétences mais également par leur implication dans la communauté. Seuls 4 professionnels possèdent cette double-qualification dans le monde. Mme Marchand cumule de nombreuses années d’expérience dans le milieu des affaires et se spécialise en modélisation financière et en intelligence d’affaires avec Excel et Power BI. Après avoir longtemps offert ses services comme consultante, elle se consacre désormais à la formation, sous toutes ses formes (formations en classe, formations en ligne, formations en entreprise, webinaires, animation de groupe d'usagers, rédaction d'articles de blogue, animation de forum en ligne et plus encore). Vous pouvez d'ailleurs la retrouver à la tête du groupe d'usagers Montreal Modern Excel and Power BI.

  • réponse Raphael ,

    Bonjour Sophie,

    Merci pour ces excellentes explications et pour la rapidité avec laquelle vous avez partagé cela!
    Je trouve particulièrement intéressant le fait que vous ayez considéré la table Royauté comme une table de faits alors que je l’imaginais comme une table de dimension… ce qui permet ensuite la relation de plusieurs à plusieurs avec la table des ventes.

    Merci encore.
    Raphaël

    • réponse stephanie_lamber ,

      Très bel exemple pour montrer l’importance des modèles de données. C’est un art qu’il faut maîtriser pour éviter soucis et erreurs par la suite.

      Laisser un commentaire