Modéliser les données et créer les mesures

Améliorer son modèle de données

Il est obligatoire de créer un modèle sémantique de données pour créer tout rapport : ce sera le jeu de données attaché automatiquement à votre datamart, avec ses tables, ses mesures et ses relations. Il sera mis à jour sans intervention du concepteur de rapport (cf. chapitre Maintenir un datamart, sections Actualiser un datamart en global et Actualiser les données en incrémentiel).

Les problèmes potentiels que les créateurs de rapports risquent de rencontrer lorsqu’ils voudront réaliser des visuels rapidement et sans anomalie sont :

Vous avez vu au chapitre Initialiser votre datamart, que plusieurs types de structures de modèles existent (en étoile, en flocon ou en synapse), et qu’il est souvent plus efficace d’opter pour un modèle de données en étoile pour éviter ces problèmes.

Si vos données sont en flocons ou en synapse, comme cela arrive le plus souvent, voici les étapes concrètes pour vous diriger vers un modèle en étoile.

Marquer la table de dates

Une table de dates est une table dédiée qui contient un unique enregistrement pour chaque jour et couvre une période donnée entre deux dates. Cette table peut inclure tout attribut utile à l’utilisateur : l’année, le mois, la semaine, le numéro de semaine européen ou le fait que le jour soit férié ou pas.

Il faut toujours marquer la table Dimension temps comme table de dates pour éviter à Power BI Service de créer des tables de temps cachées, et obtenir que les visuels fonctionnent sur les dates.

Pour marquer une table de dimension temps comme table de dates, suivez ces instructions :

Pour faire au plus vite, lorsqu’il y a beaucoup de tables :

Un message précise :...

Créer les relations

Les relations dans un modèle de données propagent des filtres pour exécuter les calculs relatifs à des critères et des faits constatés.

Dénormaliser les tables

Pour bien réussir les analyses de dimension à dimension, par exemple compter le nombre de catégories par auteur ou l’inverse, vous pouvez intégrer les catégories directement dans la table des livres : nous appelons cela « dénormaliser » les tables.

En clair, nous ajoutons les catégories dans la table Livres et nous masquons la table des Catégories et des images de catégories du modèle : il n’y a plus qu’une dimension du côté livre, ce qui fait sens et simplifie le modèle.

Les segments, que ce soit sur les noms des catégories ou sur le nom des livres, fonctionnent parfaitement : même si la colonne NomCatégorie de la table Livres contient plusieurs catégories identiques, et la colonne Auteur contient des noms qui se répètent, le segment ciblé sur la colonne Catégorie ou Auteur de la nouvelle table Livres ne retient que les noms sans faire aucun doublon.

Avec cette démarche, vous quittez les normes des bases de données qui cherchent à limiter l’espace disque, pour aller vers un principe de BI avec Schéma en étoile strict (sans dimension ni sous-dimension en cascade).

L’avantage est triple : il y a moins de tables à gérer, les analyses interdimension sont facilitées, les formules...

Spécificités du DAX de datamart

La différence la plus importante avec le DAX de Power BI Desktop est l’incapacité structurelle de créer une table de datamart en DAX, ce qui explique notre section sur la dimension temps dans Power Query Online.

La fonction GENERATESERIES, par exemple, ne peut pas exposer une liste de 1 à n qui aurait permis à l’utilisateur de faire ultérieurement un choix sur cette liste. Vous préparerez cette liste TopN dans Power Query Online, comme indiqué au chapitre Power Query Online, section Ajouter une série de 1 à 20 pour un choix du Top N.

Cette différence n’empêche absolument pas DAX d’utiliser des fonctions de table à l’intérieur d’une formule, comme CROSSFILTER ou SUMMARIZE, sous forme d’une variable ou d’une sous-partie de fonction. Simplement, la formule ne doit pas retourner au final une table dans le datamart, ce ne peut être qu’une variable intermédiaire.

Les autres différences sont :

DAX et les relations

Plusieurs fonctions DAX présentent un intérêt pour les relations de modèle. Chaque fonction est décrite brièvement dans la liste ci-dessous.

RELATED : récupère la valeur du côté « un » d’une relation. Elle est utile lors de l’implication de calculs à partir de différentes tables évaluées dans le contexte de ligne.

Voici un exemple de code (réf. DAX 4.2) qui utilise correctement cette fonction, pour calculer la somme des ventes en volume hors bande dessinée.

Qté Vendues Hors BD = SUMX (FILTER ('Détail des commandes',  
RELATED (Livres[Code catégorie]) <>"BD"), 'Détail des  
commandes'[Quantité]) 

RELATEDTABLE : récupère une table de lignes du côté « plusieurs » d’une relation, si elle existe. Ceci sert à faire par exemple des comptages dans la table de fait dans un contexte donné, comme un comptage de commandes de clients par vendeur et par année, par exemple. Voici le code DAX (réf. 4.3) qui effectue ce comptage avec succès :

NbreCommandesClients = COUNTROWS ( RELATEDTABLE ( 'Commandes' )) 

Cette mesure va compter les lignes de commandes (côté plusieurs), en face de chaque vendeur, sachant que le vendeur est relié aux commandes.

USERELATIONSHIP : permet à un calcul de réactiver temporairement une relation inactive. En voici un exemple (code réf. DAX 4.32) :

NbreTicketsOuverts = CALCULATE( 
[NbreTickets], 
USERELATIONSHIP (Tickets_Fr[OuvertPar],EMPLOYEE_role_FR[Employé(e)]) 

CROSSFILTER : modifie la direction de la relation qui existe dans le modèle. La fonction peut inverser le sens de la relation, ou bien l’ignorer, ou encore passer d’une relation monodirectionnelle à une relation bidirectionnelle. Ici, par exemple, nous avons réussi à établir une relation...

Documentation DAX

Voici quelques livres et sites sur le sujet et nos commentaires :

Ergonomie DAX Online

Écrire dans l’éditeur DAX Online du datamart permet de bénéficier de l’aide à la découverte des fonctions, des noms de tables, variables et mesures. Cette écriture offre moins de possibilités que DAX Studio, mais c’est de loin la solution la plus simple. Il n’y a pas besoin d’inclure une évaluation ou de définition au début du code comme dans DAX Studio.

Ergonomie DAX Studio

Dax Studio est un logiciel open source gratuit sous licence permissive Microsoft Reciprocal License (Ms-RL). Ce client riche de taille raisonnable (52 Mo) est sous Windows. Il est agréable et pratique. Il offre ces avantages :

L’outil est disponible en téléchargement gratuit à l’adresse : https://daxstudio.org

L’outil de formatage de DAX Studio s’appelle Format Query.

Son formatage est riche : il sait formater la requête DAX avec des lignes courtes ou longues, détecter les erreurs de parenthèse ou de virgule et enfin enregistrer le fichier au format DAX. C’est l’outil qui a été utilisé pour formater et sauvegarder le code DAX de ce livre.

Voici une formule DAX non formatée :

Nb vendeurs = CALCULATE(DISTINCTCOUNT(Vendeurs[Numéro vendeur]),  
CROSSFILTER(Commandes[Numéro vendeur],Vendeurs[Numéro vendeur],Both)) 

Voici cette formule DAX formatée type long ou court. C’est beaucoup plus lisible  (code réf. DAX 4.30) :

Nb vendeurs = 
CALCULATE ( 
    DISTINCTCOUNT ( Vendeurs[Numéro vendeur] ), 
    CROSSFILTER ( Commandes[Numéro vendeur], Vendeurs[Numéro vendeur], BOTH ) 
) 

DAX Studio peut se connecter à votre datamart et fournir un retour sur vos formules DAX. Il offre également une aide à la syntaxe (branchée sur le guide DAX en ligne), un test d’exécution de formule DAX sans devoir passer par un rapport, une aide à la construction de requêtes simples et enfin des mesures de performance. Dans cet esprit de test, DAX studio peut aussi ajouter ou supprimer des commentaires pour tester certaines modifications sans détruire l’original, ou bien passer en majuscules...

Ergonomie ChatGPT

ChatGPT est co-financé par Microsoft. Et, bonne nouvelle, ChatGPT supporte les langages DAX et M. C’est une énorme avancée, en particulier pour les non experts en DAX. Cela va modifier totalement le rôle du Data Analyste, qui sera d’optimiser et de tester les formules plutôt que de les inventer depuis une feuille blanche. Si votre entreprise bloque ce site, pour éviter de fuiter des données, vous pourrez utiliser un compte personnel Microsoft ou Google.

ChatGPT propose un code DAX complété d’explications de son code et notamment des arguments à utiliser. À l’heure où sont écrites ces lignes, l’accès dispose d’un plan totalement gratuit, qui est disponible quand la demande est basse. L’accès gratuit offre une réponse rapide sur les créneaux horaires européens. Une version ChatGPT Plus est à 20 USD mensuels pour une meilleure disponibilité quand la demande est forte. Cette version n’est pas indispensable, mais elle offre encore plus de rapidité et un accès prioritaire aux nouveautés.

On peut poser des questions en français, c’est tout aussi efficace qu’en anglais.

L’adresse internet à utiliser pour Power BI est :

Nom du site : ChatGPT | OpenAI

Adresse internet : https://chat.openai.com

L’interface est très simple et en anglais. Une fois authentifié avec un compte Microsoft ou Google, vous communiquez en bas dans Send a message. Il est bon de fournir le nom des champs, pour que le code soit rapide à adapter.

Voici pour votre information le contexte : vous êtes sur le datamart 5.Editions, le calendrier s’appelle Dim_Temps, la date s’appelle Date, et une formule fait déjà...

DAX au secours du modèle de données figé

Nous avons fait un modèle en étoile, avec des relations d’un à plusieurs entre les tables de dimension et celle de fait, et en prenant une relation à sens unique dimension vers table de fait.

Ce modèle et son sens unique sont pratiques pour les rapports sur les faits comme les ventes par pays et par vendeur. Il permet, comme nous l’avons vu précédemment, l’intégrité référentielle entre les dimensions et la table de fait. En clair, tout est carré, aucun fait ne se trouve orphelin. Cette intégrité référentielle évite les vides dans les segments ou les lignes incomplètes ou vides.

Quand nous recherchons le chiffre d’affaires par catégorie de livre par année, Power BI Service s’intéresse à chaque catégorie, critère qui descend dans la table Livres, et à chaque Année, ce qui descend dans la table Fait_Commandes le long du sens de filtrage (sur la flèche du diagramme ci-dessus) pour la filtrer correctement. La ligne Total (du bas) fait la somme des lignes sélectionnées.

CA € total = SUMX((Fait_Commandes , [Quantité]*[PV]) 

La référence de ce code est DAX 4.11.

Cependant, quand on va analyser une dimension par rapport à une autre - par exemple le nombre d’ouvrages...

Les fonctions DAX de rang

Comme nous l’avons vu au chapitre Power Query Online sur la fonction M de rang, DAX est l’outil complet pour calculer le classement d’un ensemble de valeurs dans un modèle de données riche.

La syntaxe de la fonction DAX est la suivante :

RANKX ( <Table>, <Expression> [, <Valeur>] [, <Ordre ascendant>] [, <Densité de doublons>] )

RANKX retourne une valeur scalaire : un numéro d’ordre d’une expression (un classement des vendeurs en fonction des ventes par exemple).

Les fonctions DAX de moyenne mobile

La moyenne mobile en DAX nécessite une réflexion préalable. Plusieurs formules sont possibles, mais apportent des résultats différents. Il faut donc les faire examiner auprès des consommateurs. Le choix de la formule va dépendre de la saisonnalité et de la distribution des valeurs (faits). Si on a des faits (ventes, achats, affectations…) très occasionnels, c’est-à-dire seulement à certains jours, les autres jours seront à zéro fait, a-t-on vraiment besoin d’une moyenne mobile fine ? Un lissage par semestre n’est-il pas suffisant ? S’il n’y a pas ou peu de faits certains jours, comme les week-ends et les jours fériés, ne doit-on pas annihiler ces jours dans la moyenne ? A-t-on des ventes sur la période antérieure ? Si ce n’est pas le cas, comment veut-on calculer la moyenne sur le tout début de l’année : la faire disparaître ? Ou réduire la moyenne sur deux mois au lieu de trois s’il n’y a pas de données précédentes ? Si on a des valeurs très régulières, comme un cours de bourse, ne doit-on pas plutôt raisonner en moyenne sur quelques jours (sept par exemple) ?

Voici une première formule DAX testée sur un datamart avec des valeurs quotidiennes, comme un cours de bourse, ou une consommation d’énergie.

Prenons le cours d’un indice boursier : par exemple l’EURO STOXX 50, qu’on peut télécharger sur marketwatch.com par exemple.

Si vous avez une licence Microsoft Fabric, vous aurez un accès OneLake très aisé depuis votre explorateur Windows. Le mode opératoire pour récupérer la donnée est expliqué au chapitre Power Query Online, section Importer un fichier depuis Microsoft Fabric OneLake.

À défaut, pour récupérer les sources Excel d’une certaine taille, il est recommandé de les importer dans une base de données. Le processus est expliqué au chapitre Power Query Online. L’expérience montre que la reprise depuis un répertoire OneDrive ou depuis SharePoint fonctionne, mais peut parfois générer des soucis d’authentification....

Fonction DAX conditionnelle

DAX a un avantage sur Power Query et son langage M : il dispose de fonctions conditionnelles multicritères, ce qui évite de passer par une série de IF qui s’emboîtent et qui rendent la formule rapidement illisible et difficile à déverminer.

Mais DAX a un inconvénient dans le cadre du datamart : le DAX du datamart ne peut pas créer de colonne calculée, mais uniquement des mesures, ce qui fait que l’on doit adapter la formule. Une mesure IF ou SWITCH de DAX demande une expression qui permet de déterminer une valeur unique ou une mesure, elle ne peut donc pas se raccrocher à toute une colonne qui, par définition, contient de multiples lignes.

La syntaxe de IF pour DAX est la suivante :

IF(<testlogique>, <ValeurSiVrai>[, <ValeurSiFaux>])

Celle-ci met en avant quelques principes du DAX de datamart.

Ainsi, c’est la formule DAX suivante qui fonctionne parfaitement dans une mesure du datamart (code réf. DAX 4.20) :

test76 = IF(SELECTEDVALUE(Vendeurs[Departement])="76",  
"Seine-Maritime","inconnu") 

La formule suivante indique « Seine-Maritime » lorsque le département du vendeur est 76 et « inconnu » dans le cas contraire.

SELECTEDVALUE(nomdecolonne) retourne la valeur de la colonne nomdecolonne lorsqu’il n’y a qu’une seule valeur dans cette colonne.

En revanche, la formule suivante trop simpliste ne peut pas fonctionner :

NomduDeparttHS = IF(Vendeurs[Departement]="76","Seine maritime","inconnu") 

Elle soulève l’erreur suivante de la part du datamart, qui apparaît en jaune sous la formule DAX dans l’éditeur :

A single value for column Department in table 'Vendeurs' 
cannot be determined. 

Ce message du logiciel signifie en français :

« Il est impossible de déterminer une valeur unique pour la colonne "Departement" de la table "Vendeurs". Cela peut se produire quand une formule de mesure fait référence à une colonne qui contient de nombreuses valeurs, sans spécifier d’agrégation telle que min, max, count ou sum pour obtenir un résultat unique. »

Ceci arrive parce que la condition de la formule...