Une question de temps (1) : ajouter la table du temps

Ajouter la table du temps

(Remarque : 13 novembre 2019, en complément de cet article, mise à jour du script de création de la table, avec la question de la colonne semaine, que vous trouverez ICI)

La question du temps dans Power BI possède à mon sens 4 couches :

• La base indispensable, autrement dit la table du temps (ou les tables du temps) dans le modèle
• Le fonctionnement clé de l’analyse, autour des fonctions CALCULATE, FILTER et DATEADD
• La découverte des nombreuses fonctions de Time Intelligence présentes en DAX
• L’exposition de cas particuliers (je pense à des difficultés de granularité – de niveau de détail – entre la table du temps et la table des faits*, au travail sur les heures, les minutes, les secondes, etc.)
(* la table des faits est celle où sont enregistrées les transactions, par exemple le montant de la ligne de facture, le résultat d’un échantillonnage, la quantité commandée, etc. C’est la table centrale du modèle, à laquelle sont liées les tables de dimension – la liste des produits, des clients, ou encore la table du temps – du moins dans un modèle simple)

Dans cette première partie du dossier, je vous propose de regarder de près la table du temps, sa construction, les différentes colonnes qui la composent et la mise en place de la relation (ou des relations) avec la table des faits.

Faut-il toujours ajouter une table du temps ?

A première vue, dans un modèle de données simple (par exemple, l’import d’une feuille Excel comprenant une date), Power BI propose des outils automatiques qui semblent fonctionner correctement.

Ces outils reposent sur l’option Date/heure automatique (activée par défaut) que vous trouverez dans les options du fichier actif (mais aussi dans les options globales). Cette option crée automatiquement une table du temps, cachée et non-modifiable, pour chaque date du modèle. Et génère pour le champ de type Date une hiérarchie automatique (Année, Trimestre, Mois, Jour).

Le danger ici est de voir se créer un nombre important de tables (qui vont donc augmenter la taille du modèle), sans pouvoir les personnaliser (ajout de la semaine par exemple).

D’autre part, le champ Date que vous utiliserez dans votre rapport sera bien celui de la table que vous avez importée, avec ses « trous » : si aucune transaction n’a eu lieu à une date donnée, mettons le 10 janvier, cette date n’apparaît pas dans le tableau résultant, ce qui, en soit, est une perte d’information.

Les possibilités de filtrer à l’aide d’un slicer seront difficiles à mettre en place : puisque chaque date a sa propre table du temps, un slicer ne peut filtrer la mesure (mettons le HT) que pour une date (la date de commande et pas le date de règlement – qui nécessitera un deuxième slicer).

Enfin les fonctions de Time Intelligence auront un comportement parfois curieux et pourront générer des résultats incomplets :

Il est donc recommandé de de désactiver l’option Date/heure automatique et de créer sa propre table du temps.

Comment créer la table du temps ?

Si la table du temps n’existe pas déjà dans la source de données – auquel cas il faut utiliser celle-là -, un peu de DAX permet de la créer assez simplement.

Je vous propose un script (fichier .txt), que vous pouvez télécharger librement, coller dans la barre de formule Créer une table, et adapter à votre situation (les instructions pour son utilisation sont comprises en commentaire dans le script – il importe notamment de fixer les dates de début (toujours un 1er janvier) et de fin (toujours un 31 décembre), en fonction de la période utile à l’analyse – et plus précisément des années).

Le résultat génère la table suivante :

Si vous avez besoin de créer plusieurs tables du temps, je vous recommande de copier-coller le code autant de fois que nécessaire (ou utiliser la commande Dax Table du temps 2 = Table du temps). Et penser à renommer les colonnes : il faut toujours éviter d’avoir deux objets portant le même nom dans le modèle !

Faut-il mettre une table du temps, ou plusieurs ?

Enfin se pose la question de savoir s’il faut une table et plusieurs relations (une sur chaque date significative, et la relation active sur la date la plus utilisée, les autres relations étant donc par définition inactives), ou plusieurs tables (une pour chaque date significative).

Il n’y a pas de bonne réponse ici : tout dépend du type d’analyse que vous souhaitez faire. La règle à appliquer est la suivante :

• Si vous souhaitez comparer plusieurs mesures sur une date (un axe du temps commun), alors une table du temps. C’est l’illustration 1 ci-dessous, avec le modèle correspondant.
  Par exemple, comparer le montant des achats et le montant des ventes pour un mois donné.
  Dans ce cas de figure, il s’agit donc de créer autant de mesures que nécessaire, et d’activer à la demande les relations inactives à l’aide de la fonction USERELATIONSHIP.

• Si vous souhaitez comparer une mesure sur plusieurs dates (par ex., date de réalisation, date de règlement), alors plusieurs tables du temps. C’est l’illustration 2 et son modèle.
  Par exemple, créer une matrice pour répartir le CA en fonction de la date de commande et de la date de règlement.