Power Query – Optimisation avec les modèles et les Dataflows

Par Simon-Pierre Morin, ing. – Avril 2024
(niveau débutant)

Pour faire suite aux articles d’optimisation précédents, voyons quelques autres outils à dispositions. Cette fois, nous traiterons de l’amélioration de la performance du chargement des rapports. Notez que l’amélioration des performances est indépendante de toutes les optimisations traitées dans les articles précédents et qu’elle peut se faire à tout moment.

Tout d’abord, il faut savoir qu’en tant que moteur d’acquisition de données, Power Query permet plusieurs types de manipulations. Toutefois, effectuer ces manipulations à ce stade n’est pas toujours la solution optimale.

Parmis les opérations non-optimales, nous avons, par exemple :

Certaines opérations en lien avec la manipulation et la transformation des données.
Certaines acquisitions de données brutes.
La décentralisation des données.
La redondance d’effort d’opération.
Les incohérences de données causées par des maintenances asynchrones.

Certaines de ces opérations sont si coûteuses qu’elles peuvent ralentir la génération des rapports. Il est parfois plus optimal de les exécuter dans le modèle plutôt qu’à la source. De plus, centraliser certaines opérations, surtout si celles-ci sont réutilisées dans plusieurs rapports, apporte un gain de performance, de maintenance et d’uniformité sur tous les rapports touchés par ces transformations. Il devient donc intéressant de partager le fardeau de transformation de données.

C’est donc dans cette optique que je vous présente les manipulations de modèles et les dataflows.

Traitement dans le modèle

Le premier constat est qu’il n’est pas nécessaire de tout faire en Power Query. Il est possible de donner une partie du travail au modèle avec le langage DAX (Data Analysis Expressions). Dans Excel, il est également possible d’utiliser les formules et les tableaux. Il serait également possible d’utiliser du code (VBA ou NET), mais je ne m’y attarderai pas dans cet article.

Excel

Dans Excel, pour un gain de performance sur vos manipulations, travaillez toujours avec des tables et non avec les cellules. Les tables permettent une meilleure gestion des références. Par défaut, lorsque vous recevez vos données Power Query ou toute autre source de données, Excel vous les organisera en table automatiquement. Vous pouvez également convertir vos cellules en tableau.

Ce qui vous donnera accès à vos titres de colonnes dans vos formules qui s’appliqueront automatiquement à l’ensemble de la colonne.

Il est également suggéré de toujours nommer ses tables avec un nom significatif.

De cette manière, vous pourrez référer directement à votre table et ses colonnes dans vos formules, et ce même si les dimensions de celle-ci changent.

De plus, vous pourriez vouloir activer Power Pivot dans Excel pour faciliter les manipulations du modèle et profiter des avantages du DAX. Celui-ci est déjà fourni dans Excel, mais est désactivé par défaut. Pour l’activer:

Aller dans le menu Fichier > Options (File > Options).
Dans la fenêtre de dialogue, aller dans la section Compléments (Add-ins).
Choisir les Compléments COM (COM Add-ins)
Cocher «Microsoft Power Pivot for Excel».

Cette opération est optionnelle.

DAX en général

Les opérations sur le modèle ou les tables sont plus performantes pour les opérations directement liées aux enregistrements ou leurs agrégats. Celles-ci se calculent que lorsque cela est nécessaire. Le reste du temps, le modèle utilisera le jeu de données conservé en mémoire. Quelques exemples d’opérations qu’il est préférable de laisser au modèle sont :

copie intégrale d’une table,
table résultante d’un filtre d’une autre table,
informations calculées sur la base d’une autre colonne,
mesures basées sur un ou plusieurs filtres,
calcul d’agrégat,
gestion des tables à hiérarchie récursive (hierarchy path).

Toute ces opérations nécessitent peu de ressources comparativement à l’équivalent fait en Power Query. Il faut garder à l’esprit que DAX ne calculera que les données requises par le filtre à l’affichage, contrairement à Power Query qui calcule toujours la totalité des enregistrements pour chacune des acquisitions.

À titre d’exemple, pour une copie de table faite en Power Query, lors de l’acquisition des données, même s’il ne s’agit à l’interne que d’une référence, l’ensemble des données sera chargé en double dans le modèle. Cette même copie en DAX ne chargera les données qu’une seule fois à l’acquisition et utilisera les informations en mémoire pour la copie. Il en va de même pour des sous-tables qui filtres une table de données brutes ou qui en fait un agrégat.

Dans une situation réelle, en termes de gain de performance, j’ai pu atteindre jusqu’à cinquante fois plus rapidement un résultat en DAX comparativement à sa version purement en Power Query. Selon les opérations à effectuer, la question à se poser est : « Est-ce que l’opération doit être faite à la source pour altérer l’ensemble des données, ou s’agit-il d’une information requise pour un affichage? » Dans le premier cas, Power Query est le candidat idéal, dans le second, il peut être préférable de passer le flambeau au modèle pour des questions de performances.

Flux de données (Dataflow)

Pour les utilisateurs des licences Pro de Power BI Service, il existe un autre atout disponible. L’utilisation des flux de données. Ceux-ci permettent de créer des jeux de données disponibles pour l’ensemble de l’espace de travail. Les différents avantages des flux de données sont les suivants :

Configuré avec Power Query (même langage)
Transformation centralisée des données
Recouvrement en cas d’erreur d’actualisation
Connexion rapide et performante dans les rapports
Le fardeau de connexion est passé dans le flux de données et n’affecte pas la performance des rapports
Une seule connexion requise pour accéder au jeu de données du flux.
Utilisable comme source de données dans Power BI, Excel et autres.

L’avantage principal est donc qu’il est possible de centraliser des jeux de données dans un flux de données unique et de s’y connecter comme s’il s’agissait d’une base de données conventionnelle. Du point de vue du rapport, la source du flux de données nous donne accès aux tables exposées. De plus, le résultat des tables n’est pas recalculé à chaque actualisation. Ceci offre un gain de performance accru pour les requêtes complexes nécessitant plusieurs transformations.

Comme il s’agit d’une source centralisée, la modification du flux de données sera répercutée dans tous les rapports l’utilisant comme source de données. Lorsque plusieurs rapports utilisent ces mêmes données, ceci permet également d’uniformiser certaines transformations entre les rapports. En contrepartie, si vous « brisez » votre flux de données, tous vos rapports utilisant cette source seront impactés.

Comment les créer?

Tout d’abord, l’éditeur de Data Flow utilise lui aussi Power Query. Dans la majorité des situations, il suffit de copier le code M dans l’éditeur du flux de données. Il y a cependant deux points importants à prendre en considération:

Certaines fonctions ne sont pas disponibles dans les flux de données.
Il n’est pas possible d’avoir un autre type que Table de primitives en sortie.

Parmi les fonctions non supportées, il y a les fonctions tierces et certaines fonctions propriétaires (comme la suite de fonction VSTS permettant de se connecter à Azure DevOps). Il est tout de même possible de transférer le fardeau du rapport en déplaçant la partie supportée dans un flux de données.

Pour ce qui est du type de données en sortie, tout ce qui retourne une valeur primitive unique (variable, constante, paramètre), un ensemble (liste, enregistrement) ou une fonction sera converti en table si l’option d’exportation de la requête est activée. Il faut donc bien s’assurer que les objets et tout ce qui ne doit pas être converti en table ne soient pas exportés/exposés afin que le flux de données demeure fonctionnel.

De plus, seuls les types primitifs sont autorisés comme valeurs exportées dans la table. Il n’est donc pas possible d’avoir une table contenant des ensembles ou des fonctions. Ceci implique malheureusement qu’il n’est pas possible d’utiliser le flux de données pour se faire une bibliothèque de fonctions centralisées et partageables.

Lors de l’étape de validation du flux de données, le moteur s’assurera que chaque sortie est une table, que chaque colonne ait un type primitif défini et que les colonnes n’aient pas d’erreur (les erreurs seront remplacées par la valeur null).

Pour plus d’explication sur la configuration, voir la documentation de Microsoft.

Quand doit-on les utiliser?

Il y a plusieurs cas d’utilisation. Si vous avez besoin de réutiliser des requêtes, d’héberger vos transformations dans le nuage et de simplifier la modélisation de données, vous voudrez utiliser Dataflow. Si vous travaillez principalement sur un rapport spécifique, une requête Power Query directe dans le rapport suffit.

Les Dataflows sont un outil puissant pour la préparation des données et la modélisation. Voici quelques points à considérer pour décider quand utiliser un dataflow plutôt qu’une requête Power Query directement dans le rapport :

Réutilisation et partage : Les dataflows sont conçus pour être réutilisable par un grand nombre d’utilisateurs. Si vous avez des requêtes qui font partie de plusieurs modèles de données, les dataflows permettent de les partager et de les réutiliser plus facilement.
Hébergement dans le nuage : Ce sont des requêtes effectuées et hébergées sur le service Power BI. Cela permet de centraliser la préparation des données et d’éviter la duplication d’efforts.
Modélisation et performance : Si vous devez analyser de grandes quantités de données et les présenter dans des rapports et tableaux de bord avec des indicateurs de performance pertinents, les dataflows peuvent être plus efficaces. Ils permettent de préparer les données unifiées pour la modélisation et d’optimiser les performances.
Entités et relations : Les dataflows permettent de mapper des entités, ce qui facilite l’accessibilité à des sources de données plus complexes et la mise en relation de différentes sources de données.

Les avantages combinés permettent également de partager le fardeau de chargement. Comme les rapports sont dissociés des transformations de données, le chargement des rapports est très rapide. L’actualisation des données se fait en arrière-plan et un mécanisme « sans échec » évite le bris d’un rapport si une source n’est pas accessible, donnant simplement la dernière version dont l’actualisation a été un succès.

Toujours Curieux?

Les sujets de DAX, Excel, Power Query et Power BI sont couverts par plusieurs spécialistes du domaine. Pour ceux qui aimeraient avoir des exemples supplémentaires, des idées concrètes, ou vous êtes simplement désireux d’en apprendre davantage, je vous suggère de regarder les exemples de Chandeep, Bas Dohmen et de l’équipe de Guy in a Cube (sources vidéo en anglais).

Vous désirez approfondir un nouveau sujet, visitez notre section Blogue pour y trouver plusieurs articles. Cette bibliothèque est en pleine croissance, donc revenez-y régulièrement ou abonnez-vous à notre fil LinkedIn pour être tenu au courant du nouveau contenu.