Cet article fait suite à l’article Power Pivot: Améliorer la performance des modèles de données. Les problèmes de performance que vous éprouvez avec les add-ins Power BI dans Excel peuvent être rattachés à Power Pivot, mais ils peuvent également être causés par Power Query. Cet article vise donc à mettre en lumière quelques pistes de solution pour améliorer la performance de vos requêtes dans Power Query. Mais avant toute chose, sachez que les dernières versions de Power Query offrent une performance de loin supérieure aux versions précédentes. Assurez-vous donc d’utiliser la plus récente version de Power Query.
Spécifications et limites de Power Query
Avant toute chose, il serait sans doute judicieux que vous consultiez l’article suivant pour connaître les spécifications et les limites de Power Query: Power Query Specifications and Limits.
Optimiser l’importation de données dans Power Query
Il faut comprendre que lorsque vous vous connectez à une source de données à partir de Power Query et que vous effectuez ensuite des transformations sur cette source de données, Excel importe d’abord l’ensemble des données de la source de données et effectue ensuite les transformations et ce, même si vous n’avez besoin que de quelques colonnes ou lignes spécifiques. Si vous utilisez Power Query pour supprimer des lignes et/ou des colonnes, vous n’allégez pas l’action de départ, qui est d’importer l’ensemble des données de la source de données.
Encore une fois, si vous pouviez vous connecter à une source de données qui ne comprend que les données dont vous avez besoin, une vue par exemple, ce serait votre meilleure option.
Sinon, selon la source de vos données, vous pourrez utiliser quelques techniques pour éviter d’importer l’ensemble des données.
Si votre source de données est un serveur SQL, vous pourrez alors rédiger une requête SQL en utilisant l’espace prévu à cet effet dans Power Query.
Évidemment, vous pouvez également utiliser le code M et le modifier en conséquence. Toutefois, il y a de fortes chances pour que vos requêtes sur un serveur SQL profitent du Query Folding (expliqué plus bas dans cet article). Dans ce cas, vous ne gagnerez pas nécessairement en performance lorsque vous modifierez le code M.
Voici un exemple de modification de code M.
D’abord, voici une requête régulière où on a créé une connexion àla table FactInternetSales de la BD AdvendureWorksDW2014 sur le serveur Sophie et où on a ensuite filtré la table sur 3 clés de produits manuellement. Donc ici, on a d’abord importé l’ensemble des données de la table et ensuite apposé un filtre.
Ensuite, voici une requête où on a modifié le code pour aller directement chercher les lignes de la table qui correspondent aux 3 clés de produits, sans avoir eu à importer d’abord l’ensemble des données de la table.
Si votre source de données est un cube tabulaire ou multidimensionnel sur SSAS, vous pourrez alors choisir vos colonnes (mais pas vos lignes).
Si votre source de données est un lien OData feed, le OData REST API permet d’effectuer des filtres avant l’importation des données, à partir du lien URL. L’article When Using Power Query to Retrieve Dynamics CRM vous présente un exemple concret. Au final, vous devez rédiger un URL qui ressemble à ceci:
https://<tenant>.crm.dynamics.com/XRMServices/2011/OrganizationData.svc/OpportunitySet()?$select=c5_Date,column2,column3,column4,column5,column5,column6,column7,column8,column9,column10,&$filter=(c5_Date gt DateTime’2014-01-01T00:00:00′)&$orderby=c5_Date desc
Vous pouvez également télécharger le OData query designer pour vous aider à rédiger ce type d’URL.
Ne pas dupliquer les connexions
En vous référant à l’article Power Query : Faire pointer vos requêtes vers une nouvelle source de données, vous verrez qu’il existe plusieurs façons de centraliser les informations de connexion. Ceci est pratique puisque si la source change d’endroit, vous n’aurez à modifier les informations de connexion qu’à un seul emplacement dans votre fichier. Mais c’est aussi intéressant parce que ça évite de dupliquer les connexions et donc, de ralentir inutilement le déroulement de vos requêtes.
Optimiser la performance des requêtes
Il existe quelques façons d’optimiser la performance de vos requêtes dans Power Query. En voici quelques-unes:
1.Tirer profit du Query Folding
Le Query Folding est un phénomène très peu documenté, mais voici ce que nous en savons jusqu’à maintenant.
- Query Folding veut dire que le serveur source exécute lui-même des transformations demandées par PowerQuery, ce qui devrait en améliorer grandement la performance.
- Sources qui permettent le Query Folding :
- Sources sur des serveurs de bases de données (SQL Server, Oracle, …);
- AzureMarketPlace (basé sur SQL Server);
- OData sources;
- ActiveDirectory (je ne sais pas ce que cela implique…);
- Exchange (je ne sais pas c’est quoi…);
- Liste de fichiers ou des répertoires (est-ce vraiment utile?…)
- AUCUNE LISTE OFFICIELLE N’EXISTE!
- Le Query Folding se produit selon les circonstances, les sources et les transformations impliquées. L’usager n’a pas vraiment de contrôle…
- Transformations supportées par Query Folding :
- Filtrage de lignes et de colonnes
- Opérations de type JOIN
- Opérations de type AGGREGATE ou GROUP BY
- Transformations de type Pivot ou Unpivot
- Calculs numériques
- Changement de types des colonnes
- Transformation simples comme UPPER
- AUCUNE LISTE OFFICIELLE n’existe!
- Ce qui empêche le Query Folding :
- Le Query Folding s’arrête, le cas échéant, dès qu’une condition adverse est rencontrée, comme :
- Utilisation de commandes ou de filtres qui n’ont pas d’équivalents dans le système source (par exemple, Remove last 6 rows, filter on ThisMonth, …)
- Fonction créée par l’usager – peut être incompatible avec Query Folding
- Utilisation de la fonction BUFFER de PowerQuery (cela force le chargement de la source dans PowerQuery, donc pas de possibilité de Query folding)
- Certains paramètres de Privacy Level sont incompatibles avec Query folding
- Filtre afin d’enlever les lignes avec erreurs
- Utilisation de ses propres requêtes SQL personnalisées
- Le Query Folding s’arrête, le cas échéant, dès qu’une condition adverse est rencontrée, comme :
Donc, on peut, dans PowerQuery, mettre les transformations qui ont le plus de chances d’utiliser le Query folding en premier, afin de maximiser le gain de performance de Query folding. Le Query folding s’arrête dès qu’une transformation ne peut être interprétée par Query folding, on va donc garder ces transformations ne supportant pas Query Folding pour la fin…
Il est toutefois inutile de faire ces entourloupes si la source ne supporte pas Query Folding (flat files, CSV, texte, Excel, Access, …).
Pour monitorer vos requêtes et comprendre si elles bénéficient ou non du Query Folding, vous pouvez utiliser l’outil SQL Server Profiler.
2.Utiliser les options Fast Data Load et Fast Combine
Vous pouvez modifier les paramètres des options définies par défaut dans Power Query, en vous rendant dans la section Query Options.
D’abord, sous Data Load, vous pouvez utiliser les options de téléchargement par défaut ou préciser ce que vous préférez. Vous avez notamment l’option de cocher la case Fast Data Load. On explique que cette option permet de charger les requêtes plus rapidement, mais que, ce faisant, il se pourrait qu’Excel soit hors d’usage pour toute la période de téléchargement/rafraîchissement.
Une autre option disponible est le Fast Combine. Lorsque vous cochez cette case, vous améliorez la performance de vos requêtes, mais celles-ci ignorent alors le niveau de confidentialité que vous avez attribué à vos sources de données. Donc, si vous travaillez avec des données sensibles, cette option n’est pas intéressante pour vous. Autrement, faites-en le test!
3.Utiliser la fonction Buffer
Il existe certaines fonctions dans le langage M, comme List.Buffer, Table.Buffer et Binary.Buffer qui permettent d’améliorer la performance d’une requête, en enregistrant en mémoire les résultats d’une opération (et donc, en évitant de la répéter à chaque mise à jour). Chris Webb en fait une bonne démonstration dans son article Improving Power Query Calculation Performance With List.Buffer(). Toutefois, cet article indique qu’en utilisant la requête ci-dessous, on échappe au Query Folding, qui serait sans doute plus performant. Il faut se rappeler, par contre, que le Query Folding ne s’effectue pas sur toutes les sources de données. Donc, si votre source de données ne supporte pas le Query Folding, la fonction Buffer pourrait vous être utile.
NOTRE OFFRE DE FORMATIONS
Le CFO masqué vous offre 13 formations, réparties dans 4 catégories: Tableaux de bord, Modélisation financière, Finance corporative et Analyse et modélisation de données. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.
Consulter la liste des formations offertes
Pour info: 514-605-7112 ou [email protected]
Jeu de briques
Snake
Pacman
Bubble