Dans Power BI, la gestion des données liées aux dates est incontournable. Sur des rapports simples qui utilise qu’une seule requête (table), la gestion des calendriers peut être optionnelle. Cependant, que ce soit pour des représentations plus complexes, ou tout simplement pour utiliser les bonnes pratiques, voyons ensemble comment utiliser les tables de calendrier dans Power BI. Si vous souhaitez utiliser ce type de table dans votre base de données, vous pouvez consulter l’article sur « Créer une table Calendar dans Azure SQL« .
Table des matières
Intérêt d’une table calendrier dans Power BI
La théorie
Dans une base de données, on a un grand nombre de données liées à une date. Si on prend l’exemple d’un e-commerce, on va avoir par exemple la date de notre connexion, la date de création du panier, la date de notre commande, de la réception de la commande … Bref, autant de données liées au temps, mais surtout à une « colonne » temps différente à chaque fois !
Le problème ici, c’est que les champs « date de commande » et « date de livraison » ne sont pas liés entre eux. Donc si je filtre par date de livraison, cela n’aura aucun impact sur les graphiques des commandes. Et inversement.
Pourquoi lier les dates entre elles ne sont pas une bonne pratique
Quand on voit la problématique présentée ci-dessus, on se dit qu’il suffirait de lier les colonnes « date » des deux tables entre elles pour régler le problème. Et c’est effectivement une solution qui fonctionnerait ! Cependant, cette solution est difficilement maintenable dans le temps. En effet, elle a un défaut majeur : cela implique d’être dépendant d’un champ et/ou d’une table ! Or, cela peut bouger dans le temps, et on veut éviter qu’un simple renommage mette à mal notre structure de donnée.
La bonne solution : la table calendrier
Comme on l’a vu sur le schéma précédent, on a souvent un nombre important de tables à lier.
Plutôt que de sélectionner l’une de ces tables comme la table « principale », on préfère utiliser une table date dédié qui aura pour but de répertorier l’ensemble des informations date possible. Cela comporte plusieurs avantages :
- Schéma plus clair : vu que la table « calendrier » est la table de référence, on ne se pose pas la question de « avec quelle table on fait la liaison avec le champ date? »
- Moins d’erreurs : pour les mêmes raisons que précédemment, si un filtre date ne fonctionne pas avec les autres éléments du rapport, c’est probablement que la liaison avec la table calendrier ne c’est pas faite ! La recherche de bug est simplifiée
- Plus informations : Vu que la table calendrier est dédié à cet usage, on peut avoir plusieurs colonnes qui peuvent être intéressantes en fonction des usages. Par exemple, en stockant en face de la date le nom de jour en toutes lettres, ou du mois, etc.
- Indépendance des dates et des données : Le problème souvent lorsqu’on souhaite afficher les données d’une date sur un graphique de temps, c’est qu’il peut arriver d’avoir des dates sans données. Si on affiche les données « brutes », on peut alors avoir des trous sur notre graphique. Avec une date calendrier qui possède l’ensemble des données disponibles, chaque jour est représenté
Cas 1 : Plusieurs graphiques temporels sur une même page
Nous l’avons vu dans l’article sur la revue globale de Power BI, il est très simple de créer un graphique temporel. Un « Line Chart », la sélection de la colonne date et de la colonne de donnée, et c’est en place !
Si on reprend la représentation au-dessus, un des problèmes vient du fait que nous ayons deux graphiques qui utilisent tous deux une métrique « temps » liée à leurs requêtes. Or, si on veut filtrer par la date, nous allons devoir choisir l’une ou l’autre des métriques.
Si, comme dans l’exemple ci-dessous, je choisis simplement l’une des dates pour mon filtre en haut de page, alors seul l’un de mes deux graphiques sera filtré.
Cas 2 : L’utilisation de filtres pour l’ensemble du rapport
Pour extrapoler le problème du cas 1, on peut penser à avoir des filtres sur une page qui seront chargés de filtrer l’ensemble des pages du rapport. Or, il y a peu de chance que l’ensemble des pages de vos rapports utilisent le même champ date.
Comment créer et utiliser une table calendrier dans Power BI ?
Création de la table calendrier dans Power BI
Pour créer une table calendrier, vous pouvez utiliser la documentation Microsoft ou suivre le tutoriel ci-dessous.
Accédez à la partie « Data » de votre application Power Bi Desktop, puis cliquez sur « New Table ».
De là, vous aurez un Input avec « Table = ». Table représente le nom de la table qui va contenir vos dates, que vous pourrez appeler « Calendar » ou « Date » par exemple. Utilisez la fonction « Calendar » pour générer automatiquement toutes les dates possibles entre la date de début et la date de fin désirée. Prévoyez un peu large pour que votre rapport ne devienne pas obsolète. L’exemple ci-dessous génère toutes les dates entre 2020 et 2050.
Calendar = CALENDAR("2020-01-01", "2050-01-01")
Création des liens entre la table calendrier et les autres données
Maintenant que la table calendrier a été établi, il faut créer les liaisons entre les dates générées et vos tables de données. Pour cela, voici les étapes présentes sur la capture d’écran ci-dessous :
- Cliquez sur « Manage relationships »
- Puis cliquez sur « New »
- Sélectionnez les deux tables à lier entre elles (votre calendrier, et la table de donnée)
- Sélectionnez ensuite les colonnes dates à lier sur les deux tables
- Vérifiez que la cardinalité qui a été automatiquement trouvée et la bonne. Cela devrait être une one-to-many ou many-to-one selon l’ordre dans laquelle vous avez mis vos tables
- Une fois que tout est vérifié, cliquez sur « Ok »
- Recommencez pour chacune des liaisons à créer
Une fois les relations établies, votre table calendrier est en place et prête à l’emploi !
Lorsque vous devez renseigner un champ date pour un filtre, ou une représentation graphique, utilisez directement votre table calendrier pour que l’ensemble de vos champs dates soient corrélés.
Vérification du fonctionnement attendu sur les rapports
Les filtres liés sont désormais parfaitement fonctionnels. Le filtre par date placé précédemment met bien à jour les graphiques « Datas X » et « Datas Y » placés précédemment.
Réflexion
Avoir une table de calendrier dans ses données est une bonne pratique, pas seulement dans Power BI. Si vous possédez une base de données principale, il peut être intéressant d’envisager la création d’une telle table. Elle pourrait alors servir aussi dans certaines jointures et optimisations.