Génération d’une dimension Date pour les modèles sémantiques en Direct Lake

Cet article est disponible en Anglais sur LinkedIn : Generating a Date Dimension Table for Direct Lake Model in Microsoft Fabric | LinkedIn.

L’un des éléments clés d’un modèle sémantique est la table des dates. Elle fournit une référence pour tous les calculs et analyses liés au temps. Une table de dates est une table qui contient une ligne pour chaque jour dans une plage spécifique et des colonnes supplémentaires qui décrivent les attributs de la date, tels que l’année, le mois, le trimestre, la semaine, le nom du jour, …

Une table de dates est essentielle pour l’utilisation des fonctions Time Intelligence en DAX. Ces fonctions permettent d’effectuer des calculs basés sur des périodes, comme le cumul annuel, le cumul trimestriel, le cumul mensuel, etc.

Une table de dates permet également d’utiliser des filtres de dates relatives et des Slicers dans les rapports Power BI. Ainsi les utilisateurs peuvent filtrer facilement les données par périodes dynamiques, telles que les 5 derniers jours, les 6 prochains mois, l’année précédente, … Ces fonctionnalités s’appuient sur la table des dates pour fournir le contexte et la granularité de la dimension temporelle.

Power BI vous offre la possibilité de créer plusieurs relations physiques entre deux tables. Parmi ces relations, seulement une est active, tandis que les autres sont inactives (représentées par un trait en pointillé). Les relations actives propagent par défaut les filtres, le choix des relations actives est donc très important et influencera les analyses possibles. Les dimensions de jeu de rôle (Role-playing dimensions) sont des dimensions qui sont utilisées plusieurs fois dans une table de faits. Dans l’exemple suivant, la table Vente comporte plusieurs dates comme la Clé de la date de facturation et la Clé de la date de livraison qui peuvent toutes deux être liées à la colonne Date de la table Date. Il est donc possible d’analyser les ventes en fonction de la date de la facture ou de la date de livraison. Pour plus d’information sur l’activation des relations en DAX : https://learn.microsoft.com/power-bi/guidance/relationships-active-inactive

Une dimension dates peut être créée manuellement, importée d’une source externe ou générée à l’aide des fonctions DAX ou M. Il est souvent recommandé de réaliser autant d’opérations que possible en amont, c’est pourquoi il est préférable de créer cette dimension Date en Power Query plutôt qu’en DAX. Ainsi, même si la table n’est pas très grande, sa compression sera optimisée.

Voici différents exemples pour créer cette table Date : https://learn.microsoft.com/power-bi/guidance/model-date-tables

J’utilise depuis longtemps la dimension Date générée via une fonction créée par Chris Webb :  Generating A Date Dimension Table In Power Query.

Cependant le Direct Lake ne permet pas l’usage de Power Query, ni même les Modèles Composites >>> Toutes les tables doivent être aux préalables disponibles dans OneLake au format Delta.

Une option serait de recourir à la création de tables en langage M dans Dataflow Gen 2, tandis qu’une autre approche pourrait être l’usage de Python dans un Notebook via Microsoft Fabric.

Voici donc comment implémenter cette dernière solution :

Une fois que vous avez manuellement attaché un Lakehouse à un Notebook ou en Python comme expliqué dans l’article de Sandeep Pawar : How to Attach a Default Lakehouse to a Notebook in Fabric, voici un script exemple qui illustre la création et l’enregistrement d’une dimension Date dans le Lakehouse associé :

import pandas as pd
import holidays     #https://pypi.org/project/holidays/
def DimDate(start_date, end_date):
    from pandas.tseries.offsets import MonthEnd, QuarterEnd
    df_date = pd.DataFrame({"Date": pd.date_range(start=f'{start_date}', end=f'{end_date}', freq='D')})
    def get_end_of_month(pd_date):
        if pd_date.is_month_end == True:
            return pd_date
        else:
            return pd_date + MonthEnd(1)
    def get_end_of_quarter(pd_date):
        if pd_date.is_quarter_end == True:
            return pd_date
        else:
            return pd_date + QuarterEnd(1)
    df_date["Day"] = df_date.Date.dt.day
    df_date["Week"] = df_date.Date.dt.weekday
    df_date["Month"] = df_date.Date.dt.month
    df_date["MonthName"] = df_date.Date.dt.month_name()
    df_date["Quarter"] = df_date.Date.dt.quarter
    df_date["Year"] = df_date.Date.dt.year
    df_date["FiscalYear"] = df_date['Date'].dt.to_period('A-JUN')
    df_date['EndOfMonth'] = df_date['Date'].apply(get_end_of_month)
    df_date['EOM'] = df_date['Date'].dt.is_month_end
    df_date['EndOfQuarter'] = df_date['Date'].apply(get_end_of_quarter)
    df_date['EOQ'] = df_date['Date'].dt.is_quarter_end
    return df_date

def DimDateWorkday(start_date, end_date):
    df_date = DimDate(start_date, end_date)
    df_date['Workday'] = True
    for index, row in df_date.iterrows():
        if row['Day'] in ['Saturday', 'Sunday']:
            df_date.loc[index, 'Workday'] = False
        date = row['Date'].strftime("%Y-%m-%d")
        if date in holidays.France():
            df_date.loc[index, 'Workday'] = False
    return df_date

#DimDate('2024-01-01', '2025-12-31')
#DimDateWorkday('2024-01-01', '2025-12-31')
df_DimDate = spark.createDataFrame(DimDateWorkday('2024-01-01', '2025-12-31'))
df_DimDate.write.mode("overwrite").format("delta").saveAsTable('Calandar')

J’utilise la librairie Python Holidays, permettant d’ajouter les jours ouvrés et ceux en fonction de différents pays (ici sur la France). N’hésitez pas à consulter la documentation de la librairie : https://pypi.org/project/holidays/.

Le Notebook est disponible à l’adresse suivante : DateDimensionTable.ipynb

Résultat :

Une fois exécuter, la table Calandrier sera présente dans le Lakehouse, accessible via le SQL Endpoint et aussi disponible pour vos différents modèles sémantiques en Direct Lake.

Une fois la table Calandar créée, vous pouvez la marquer comme étant la table Date du modèle, sur un modèle sémantique par non Default (Mode Direct Lake et rapports Power BI):

Il sera alors possible de créer des mesures DAX utilisant les fonctions Time Intelligence afin de calculer par exemple le total cumulé pour l’année en cours : https://learn.microsoft.com/dax/time-intelligence-functions-dax

Ici à titre d’exemple pour enrichir un précédent modèle créé dans mon article de blog : Contrôler la qualité des modèles sémantiques Power BI au fil du temps – Pulsweb – Romain Casteres.

BP Issues YTD = TOTALYTD (modelbparesults[BP Issues], calandar[Date])