Plan de requête pour Power Query

Le plan de requête pour Power Query est une fonctionnalité qui offre une meilleure vue de l’évaluation de votre requête. Il est utile d'aider à déterminer pourquoi une requête particulière pourrait ne pas se replier à une étape particulière.

Dans un exemple pratique, cet article illustre le cas d’usage principal et les avantages potentiels de l’utilisation de la fonctionnalité de plan de requête pour passer en revue vos étapes de requête. Les exemples utilisés dans cet article ont été créés à l’aide de l’exemple de base de données AdventureWorksLT pour Azure SQL Server, que vous pouvez télécharger à partir de exemples de bases de données AdventureWorks.

Remarque

La fonctionnalité de plan de requête pour Power Query est disponible uniquement dans Power Query Online.

Cet article est divisé en une série d’étapes recommandées pour interpréter le plan de requête. Ces étapes sont les suivantes :

1. Réviser les indicateurs de pliage de requête.
2. Sélectionner l’étape de requête pour réviser son plan de requête.
3. Implémentez les modifications à votre requête.

Procédez comme suit pour créer la requête dans votre propre environnement Power Query Online.

1. Dans Power Query - Choisir une source de données, sélectionnez Requête vide.

2. 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"
   

3. Modifiez servername et database avec les noms appropriés pour votre propre environnement.

4. (Facultatif) Si vous essayez de vous connecter à un serveur et une base de données pour un environnement local, veillez à configurer une passerelle pour cet environnement.

5. Cliquez sur Suivant.

6. Dans l’Éditeur Power Query, sélectionnez Configurer la connexion et fournissez les informations d’identification à votre source de données.

Remarque

Pour plus d’informations sur la connexion à un serveur SQL Server, accédez à base de données SQL Server.

Après avoir suivi ces étapes, votre requête ressemble à celle de l’image suivante.

Cette requête se connecte à la table SalesOrderHeader et sélectionne quelques colonnes dans les cinq dernières commandes avec une valeur TotalDue supérieure à 1 000.

Remarque

Cet article utilise un exemple simplifié pour présenter cette fonctionnalité, mais les concepts décrits dans cet article s’appliquent à toutes les requêtes. Nous vous recommandons d'avoir une bonne connaissance du pliage de requêtes avant de lire le plan de requête. Pour en savoir plus sur le repliage des requêtes, consultez concepts de base du repliage des requêtes.

1. Examiner les indicateurs de pliage des requêtes

Remarque

Avant de lire cette section, nous vous recommandons de consulter l'article sur les Indicateurs de pliage de requêtes.

La première étape de ce processus consiste à passer en revue votre requête et à prêter une attention particulière aux indicateurs de pliage de requête. L’objectif est de passer en revue les étapes marquées comme non pliées. Vous pouvez ensuite voir si des modifications apportées à la requête globale pourraient faire en sorte que ces transformations se plient complètement.

Pour cet exemple, la seule étape qui ne peut pas être pliée est celle des rangées inférieures conservées, qui est facile à identifier grâce à l'indicateur d'étape non pliée. Cette étape est également la dernière étape de la requête.

L’objectif est maintenant de revoir cette étape et de comprendre ce qui est replié vers la source de données et ce qui ne peut pas être replié.

2. Sélectionnez l’étape de requête pour passer en revue son plan de requête

Vous avez identifié l'étape Conserver les lignes inférieures comme une étape intéressante car elle ne se replie pas sur 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 qui contient un diagramme pour le plan de requête de l’étape sélectionnée.

Power Query essaie d'optimiser votre requête en tirant parti de l'évaluation paresseuse et du pliage de requêtes, comme mentionné dans Principes de base du pliage de requêtes. 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 toutes 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 de la sortie de votre requête, qui est représentée à l’extrême gauche du diagramme. Dans ce cas, c'est le nœud Table.LastN qui représente l'étape Conserver les lignes inférieures.

En bas de la boîte de dialogue, il existe une barre avec des icônes qui vous aident à effectuer un zoom avant ou hors de l’affichage du plan de requête et d’autres boutons pour vous aider à gérer l’affichage. Pour l’image précédente, l’option Ajuster pour afficher à partir de cette barre a été utilisée pour mieux apprécier les nœuds.

Remarque

Le plan de requête représente le plan optimisé. Lorsque le moteur évalue une requête, il tente d'intégrer tous les opérateurs à une source de données. Dans certains cas, il peut même effectuer une réorganisation interne des étapes pour optimiser le pliage. Avec ce processus à l'esprit, les nœuds/opérateurs laissés dans ce plan de requête optimisé contiennent généralement la requête de source de données « pliée ». Tous les opérateurs qui n’ont pas pu être pliés sont évalués localement.

Identifier les nœuds pliés parmi d’autres nœuds.

Vous pouvez identifier les nœuds de ce diagramme sous la forme de deux groupes :

• nœuds pliés: ce nœud peut être Value.NativeQuery ou « source de données », comme Sql.Database. Ces nœuds peuvent également être identifiés avec l'étiquette remote sous leur nom de fonction.
• nœuds non pliés: d’autres opérateurs de table, tels que Table.SelectRows, Table.SelectColumnset d’autres fonctions qui n’ont pas pu être pliées. Ces nœuds peuvent également être identifiés avec les étiquettes Full scan et Streaming.

L’image suivante montre les nœuds pliés à l’intérieur du rectangle rouge. Le reste des nœuds n’a pas pu être replié vers la source de données. Vous devez passer en revue le reste des nœuds, car l’objectif est de tenter de ramener ces nœuds à 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 affichent la requête native (en SQL) envoyée à la source de données.

La requête présentée ici peut ne pas être exactement la même requête envoyée à la source de données, mais il s’agit d’une bonne approximation. Pour ce cas, il vous indique exactement quelles colonnes sont interrogées à partir de la table SalesOrderHeader. Ensuite, comment il filtre cette table à l’aide du champ TotalDue pour obtenir uniquement les lignes où la valeur de ce champ est supérieure à 1 000. 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é.

Remarque

Les opérateurs peuvent ne pas correspondre exactement aux fonctions utilisées dans le script de la requête.

Examiner les nœuds non pliés et envisager des actions pour plier votre transformation

Vous avez maintenant déterminé quels nœuds n’ont pas pu être pliés et peuvent être évalués localement. Ce cas n’a 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 afin que l’étape puisse être pliée. Certaines des modifications que vous pouvez implémenter peuvent aller de la réorganisation de vos étapes à l’application d’une logique alternative à votre requête plus explicite à la source de données. Cela ne signifie pas que toutes les requêtes et toutes les opérations peuvent être pliées en appliquant certaines modifications. Mais il est recommandé de procéder par essais et erreurs pour déterminer 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. Comme il n’existe aucune clause BOTTOM dans SQL, la transformation Table.LastN dans PowerQuery ne peut pas être traduite en SQL. Vous pouvez supprimer l’étape Table.LastN et la remplacer par :

• Une étape décroissante de tri par la colonne SalesOrderID dans la table, car cette colonne détermine l’ordre qui passe en premier et qui a été entré en dernier.
• Sélectionnez les cinq premières lignes puisque le tableau a été trié, cette transformation accomplit la même chose que s'il s'agissait de conserver les lignes inférieures (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 vérifier si cette alternative permet à ce nœud de se replier complètement vers la source de données.

3. Implémenter les modifications apportées à votre requête

Implémentez l’alternative décrite dans la section précédente :

1. Fermez la boîte de dialogue du plan de requête et revenez à l’éditeur Power Query.

2. Supprimez l’étape Lignes du base conservées.

3. Triez la colonne SalesOrderID dans l’ordre décroissant.

   

4. Sélectionnez l’icône de tableau dans le coin supérieur gauche de la vue d’aperçu des données et sélectionnez l’option qui lit Conserver les lignes supérieures. Dans la boîte de dialogue, passez le nombre cinq en tant qu’argument et 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é.

Maintenant, il est temps de passer en revue le plan de requête de la dernière étape, qui est maintenant Conserver les lignes supérieures. Il n’y a maintenant 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 quelle alternative appliquer, l’objectif principal est d’apprendre à utiliser le plan de requête pour examiner le fusionnement des requêtes. Cet article fournit également une visibilité de ce qui est envoyé à votre source de données et des transformations effectuées localement.

Vous pouvez ajuster votre code pour voir l’impact qu’il a dans votre requête. En utilisant les indicateurs de repliement de requête, vous avez également une meilleure idée des étapes qui empêchent votre requête de se replier.