La fonctionnalité Plan de requête pour Power Query permet de mieux visualiser l’évaluation de votre requête. Elle est utile pour déterminer la raison pour laquelle le pliage d’une requête particulière pourrait échouer à une étape particulière.
Au travers d’un exemple pratique, cet article présente le principal cas d’usage de la fonctionnalité Plan de requête, ainsi que les avantages potentiels liés à son utilisation pour examiner les étapes de votre requête. Les exemples utilisés dans cet article ont été créés à l’aide de l’échantillon de base de données AdventureWorksLT pour Azure SQL Server, que vous pouvez télécharger à partir des échantillons de bases de données AdventureWorks.
Notes
La fonctionnalité Plan de requête pour Power Query n’est disponible que dans Power Query Online.
Cet article a été divisé en une série d’étapes recommandées pour interpréter le plan de requête. Ces étapes sont les suivantes :
Suivez les étapes suivantes pour créer la requête dans votre propre environnement Power Query Online.
Dans Power Query - Choisir une source de données, sélectionnez Requête vide.
Remplacez le script de la requête vide par la requête suivante.
let
Source = Sql.Database("servername", "database"),
Navigation = Source{[Schema = "Sales", Item = "SalesOrderHeader"]}[Data],
#"Removed other columns" = Table.SelectColumns(Navigation, {"SalesOrderID", "OrderDate", "SalesOrderNumber", "PurchaseOrderNumber", "AccountNumber", "CustomerID", "TotalDue"}),
#"Filtered rows" = Table.SelectRows(#"Removed other columns", each [TotalDue] > 1000),
#"Kept bottom rows" = Table.LastN(#"Filtered rows", 5)
in
#"Kept bottom rows"
Remplacez servername et database par les noms appropriés pour votre propre environnement.
(Facultatif) Si vous tentez de vous connecter à un serveur et à une base de données pour un environnement local, veillez à configurer une passerelle pour cet environnement.
Cliquez sur Suivant.
Dans l’éditeur Power Query, sélectionnez Configurer la connexion et fournissez les informations d’identification pour l’accès à votre source de données.
Notes
Pour plus d’informations sur la connexion à un serveur SQL Server, consultez Base de données SQL Server.
À l’issue de ces étapes, votre requête ressemblera à celle présentée dans l’image suivante.
Cette requête se connecte à la table SalesOrderHeader et sélectionne quelques colonnes dans les cinq dernières commandes dont la valeur TotalDue est supérieure à 1000.
Notes
Cet article utilise un exemple simplifié pour présenter cette fonctionnalité, mais les concepts qu’il décrit s’appliquent à toutes les requêtes. Avant de lire le plan de requête, il est recommandé d’avoir une bonne connaissance du pliage de requête. Pour en savoir plus sur le pliage de requête, consultez les principes de base du pliage de requête.
Votre première étape dans ce processus consiste à réviser votre requête en étant particulièrement attentif aux indicateurs de pliage de requête. L’objectif est d’examiner les étapes marquées comme non pliées. Ensuite, vous pouvez voir si l’apport de modifications à la requête globale pourrait aboutir à un pliage complet de ces transformations.
Pour cet exemple, la seule étape qui ne peut pas être pliée est Lignes du bas conservées, qui est facile à identifier par le biais de l’indicateur d’étape Non pliée. Cette étape est également la dernière étape de la requête.
L’objectif est maintenant de réviser cette étape pour comprendre ce qui est replié vers la source de données et ce qui ne peut pas être plié.
2. Sélectionner l’étape de requête pour réviser son plan de requête
Vous avez identifié l’étape Lignes du bas conservées comme étape intéressante, car elle ne se replie pas vers la source de données. Cliquez avec le bouton droit sur l’étape et sélectionnez l’option Afficher le plan de requête. Cette action affiche une nouvelle boîte de dialogue contenant un diagramme pour le plan de requête de l’étape sélectionnée.
Power Query tente d’optimiser votre requête en tirant parti de l’évaluation différée et du pliage de requête, comme décrit dans les principes de base de Query Folding. Ce plan de requête représente la traduction optimisée de votre requête M dans la requête native envoyée à la source de données. Il inclut également les transformations effectuées par le moteur Power Query. L’ordre dans lequel les nœuds apparaissent suit l’ordre de votre requête à partir de la dernière étape ou du résultat, qui est représenté à l’extrême gauche du diagramme et qui, dans ce cas, est le nœud Table.LastN représentant l’étape Lignes du bas conservées.
Au bas de la boîte de dialogue figure une barre contenant des icônes permettant d’effectuer un zoom avant ou arrière sur la vue du plan de requête, ainsi que d’autres boutons pour gérer l’affichage. Pour l’image précédente, l’option Ajuster à la vue de cette barre a été utilisée pour mieux apprécier les nœuds.
Notes
Le plan de requête représente le plan optimisé. Quand le moteur évalue une requête, il tente de plier tous les opérateurs dans une source de données. Dans certains cas, il peut même effectuer une réorganisation interne des étapes pour optimiser le pliage. Dans cette optique, les nœuds/opérateurs restants dans ce plan de requête optimisé contiennent généralement la requête de source de données « pliée », ainsi que tous les opérateurs qui n’ont pas pu être pliés et qui sont évalués localement.
Distinguer les nœuds pliés des autres nœuds
Vous pouvez identifier les nœuds figurant dans ce diagramme comme constituant deux groupes :
Nœuds pliés : ces nœuds peuvent être Value.NativeQuery ou des nœuds « source de données » tels que Sql.Database. Ils peuvent également être identifiés avec l’étiquette distant sous leur nom de fonction.
Nœuds non pliés : autres opérateurs de table, tels que Table.SelectRows, Table.SelectColumns, et d’autres fonctions qui n’ont pas pu être pliées. Ils peuvent également être identifiés avec les étiquettes Analyse complète et Diffusion en continu.
L’image suivante montre les nœuds pliés à l’intérieur du rectangle rouge. Les nœuds restants n’ont pas pu être repliés vers la source de données. Vous devez examiner les nœuds restants, car l’objectif est de tenter de les replier vers la source de données.
Vous pouvez sélectionner Afficher les détails au bas de certains nœuds pour afficher des informations étendues. Par exemple, les détails du nœud Value.NativeQuery montrent la requête native (en SQL) qui sera envoyée à la source de données.
La requête affichée ici pourrait différer légèrement de la même requête envoyée à la source de données, mais il s’agit d’une bonne approximation. Dans ce cas, elle vous indique exactement quelles colonnes de la table SalesOrderHeader seront interrogées, puis comment elle filtrera cette table à l’aide du champ TotalDue pour obtenir uniquement les lignes dans lesquelles la valeur de ce champ est supérieure à 1000. Le nœud en regard de celui-ci, Table.LastN, est calculé localement par le moteur Power Query, car il ne peut pas être plié.
Notes
Il se peut que les opérateurs ne correspondent pas exactement aux fonctions utilisées dans le script de la requête.
Examinez les nœuds non pliés et envisagez des actions pour faire en sorte que votre transformation soit pliée
Vous avez maintenant déterminé quels nœuds n’ont pas pu être pliés et seront évalués localement. Ce cas n’inclut que le nœud Table.LastN mais, dans d’autres scénarios, il pourrait en avoir beaucoup plus.
L’objectif est d’appliquer des modifications à votre requête de façon à ce que l’étape puisse être pliée. Des modifications que vous pourriez implémenter pourraient aller de la réorganisation de vos étapes à l’application d’une autre logique à votre requête, plus explicite par rapport à la source de données. Cela ne signifie pas que l’ensemble des requêtes et opérations sont pliables en appliquant certaines modifications. Mais il est recommandé de déterminer par le biais d’essais et d’erreurs si votre requête peut être repliée.
Étant donné que la source de données est une base de données SQL Server, si l’objectif est de récupérer les cinq dernières commandes de la table, une bonne alternative serait de tirer parti des clauses TOP et ORDER BY dans SQL. Étant donné qu’il n’existe pas de clause BOTTOM dans SQL, la transformation Table.LastN dans Power Query ne peut pas être traduite en SQL. Vous pouvez supprimer l’étape Table.LastN et la remplacer comme suit :
Une étape de tri décroissant sur la colonne SalesOrderID dans la table, car celle-ci détermine la commande qui vient en premier et celle qui a été entrée en dernier.
La sélection des cinq premières lignes car la table a été triée, et cette transformation fait la même chose que s’il s’agissait d’une ligne du bas conservée (Table.LastN).
Cette alternative équivaut à la requête d’origine. Bien que cette alternative semble bonne en théorie, vous devez apporter les modifications pour voir si elle aura pour effet que ce nœud se replie entièrement vers la source de données.
3. Implémenter les modifications de votre requête
Implémentez l’alternative décrite dans la section précédente :
Fermez la boîte de dialogue du plan de requête et revenez à l’éditeur Power Query.
Supprimez l’étape Lignes du base conservées.
Triez la colonne SalesOrderID dans l’ordre décroissant.
Sélectionnez l’icône de table dans l’angle supérieur gauche de la vue d’aperçu des données, et sélectionnez l’option Conserver les lignes du haut. Dans la boîte de dialogue, entrez le chiffre cinq comme argument, puis appuyez sur OK.
Après avoir implémenté les modifications, vérifiez à nouveau les indicateurs de pliage de requête pour voir s’il y a un indicateur plié.
Il est maintenant temps de réviser le plan de requête de la dernière étape, Conserver les lignes du haut. Il n’y a désormais que des nœuds pliés. Sélectionnez Afficher les détails sous Value.NativeQuery pour vérifier quelle requête est envoyée à la base de données.
Bien que cet article suggère l’alternative à appliquer, votre objectif principal est d’apprendre à utiliser le plan de requête pour étudier le pliage de requête. Cet article montre également ce qui est envoyé à votre source de données et les transformations qui seront effectuées localement.
Vous pouvez ajuster votre code pour voir l’impact sur votre requête. En utilisant les indicateurs de pliage de requête, vous acquerrez également une meilleure compréhension de ce qui empêche le pliage de votre requête.
Démontrez des méthodes et les meilleures pratiques qui s’alignent sur les exigences métier et techniques pour la modélisation, la visualisation et l’analyse des données avec Microsoft Power BI.