Lire en anglais

Partager via


Plan de requête pour Power Query (préversion)

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.

Processus suggéré pour utiliser la fonctionnalité Plan de requête dans Power Query en examinant les indicateurs de pliage de la requête, examiner le plan de requête pour une étape sélectionnée et finalement implémenter toute modification dérivée de l’examen du plan de requête.

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 :

  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émenter les modifications de votre requête.

Suivez les étapes suivantes 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. Remplacez servername et database par les noms appropriés pour votre propre environnement.

  4. (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.

  5. Cliquez sur Suivant.

  6. 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.

Échantillon de requête avec les indicateurs de pliage de requête activés. 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.

1. Réviser les indicateurs de pliage de requête

Notes

Avant de lire cette section, nous vous recommandons de lire l’article sur les indicateurs de 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.

Indicateurs de pliage de requête pour l’échantillon de requête à l’intérieur du volet Étapes appliquées.

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.

Boîte de dialogue de plan de requête montrant l’affichage d’un diagramme pour le plan de requête avec des nœuds connectés par lignes. 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.

Boîte de dialogue Plan de requête avec les nœuds agrandis pour améliorer l’affichage.

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.

Contrôles d’affichage de plan de requête au bas de la boîte de dialogue avec l’option Ajuster à la vue sélectionnée. 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.

Affichage des détails du nœud Value.NativeQuery dans le plan de requête. 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 :

  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.

    Tri de la colonne SalesOrderID dans l’ordre décroissant à l’aide du menu de filtre automatique.

  4. 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.

    Dans le menu contextuel de la table, sélectionner la transformation Conserver les lignes du haut pour ne conserver que les cinq premières lignes.

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é.

Tous les indicateurs de pliage de requête sont verts et montrent que le pliage est possible. Le tableau final fournit les mêmes lignes mais dans un ordre différent. 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.

Nouveau plan de requête après apport des modifications à la requête, qui ne présente maintenant que les nœuds pliés, avec Value.NativeQuery montrant l’instruction SQL complète, qui évalue la requête.

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.