Exemples de Query Folding

Cet article fournit des exemples de scénarios pour chacun des trois résultats possibles pour le pliage des requêtes. Il inclut également quelques suggestions sur la façon de tirer le meilleur parti du mécanisme de pliage des requêtes et l’effet qu’il peut avoir dans vos requêtes.

Scénario

Imaginez un scénario où, à l’aide de la base de données Wide World Importers pour la base de données SQL Azure Synapse Analytics, vous êtes chargé de créer une requête dans Power Query qui se connecte à la fact_Sale table et récupère les 10 dernières ventes avec uniquement les champs suivants :

• Clé de vente
• Clé client
• Clé de date de facture
• Descriptif
• Quantité

Cet article présente trois façons d’obtenir la même sortie avec différents niveaux de pliage des requêtes :

• Pas de repliement de requête
• Pliage de requêtes partielles
• Pliage des requêtes complètes

Aucun exemple de pliage de requête

Après vous être connecté à votre base de données et en accédant à la fact_Sale table, vous sélectionnez la transformation Conserver les lignes inférieures trouvées dans le groupe Réduire les lignes de l’onglet Accueil .

Une fois cette transformation sélectionnée, une nouvelle boîte de dialogue s’affiche. Dans cette nouvelle boîte de dialogue, vous pouvez entrer le nombre de lignes que vous souhaitez conserver. Dans ce cas, entrez la valeur 10, puis sélectionnez OK.

Ensuite, sélectionnez la transformation Choisir les colonnes trouvées dans le groupe Gérer les colonnes de l’onglet Accueil . Vous pouvez ensuite sélectionner les colonnes que vous souhaitez conserver de votre table et supprimer le reste.

Enfin, dans la boîte de dialogue Choisir des colonnes, sélectionnez le Sale Key, Customer Key, Invoice Date KeyDescriptionet Quantity les colonnes, puis sélectionnez OK.

L’exemple de code suivant est le script M complet de la requête que vous avez créée :

let
    Source = Sql.Database(ServerName, DatabaseName),
    Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
    #"Kept bottom rows" = Table.LastN(Navigation, 10),
    #"Choose columns" = Table.SelectColumns(
        #"Kept bottom rows",
        {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"}
    )
in
    #"Choose columns""

Aucun pliage de requête : Comprendre l’évaluation de la requête

Sous Étapes appliquées dans l’éditeur Power Query, notez que les indicateurs de pliage de requête pour les lignes inférieures conservées et Choisir des colonnes sont marqués comme des étapes évaluées en dehors de la source de données ou, en d’autres termes, par le moteur Power Query.

Vous pouvez cliquer avec le bouton droit sur la dernière étape de votre requête, celle nommée Choisir des colonnes, puis sélectionner l’option qui lit le plan de requête d’affichage. L’objectif du plan de requête est de vous fournir une vue détaillée de la façon dont votre requête est exécutée. Pour en savoir plus sur cette fonctionnalité, accédez au plan de requête.

Chaque zone de l’image précédente est appelée nœud. Un nœud représente la répartition des opérations pour répondre à cette requête. Les nœuds qui représentent des sources de données, telles que SQL Server dans l’exemple précédent et le Value.NativeQuery nœud, représentent la partie de la requête qui est déchargée vers la source de données. Le reste des nœuds, dans ce cas Table.LastN et Table.SelectColumns mis en surbrillance dans le rectangle de l’image précédente, est évalué par le moteur Power Query. Ces deux nœuds représentent les deux transformations que vous avez ajoutées, Conserver les lignes du bas et Choisir des colonnes. Le reste des nœuds représentent les opérations qui se produisent au niveau de votre source de données.

Pour afficher la requête exacte envoyée à votre source de données, sélectionnez Afficher les détails dans le Value.NativeQuery nœud.

Cette demande de source de données est dans la langue native de votre source de données. Dans ce cas, ce langage est SQL et cette instruction représente une demande pour toutes les lignes et champs de la fact_Sale table.

La consultation de cette demande de source de données peut vous aider à mieux comprendre l’histoire que le plan de requête tente de transmettre :

• Sql.Database: ce nœud représente l’accès à la source de données. Se connecte à la base de données et envoie des demandes de métadonnées pour comprendre ses fonctionnalités.
• Value.NativeQuery: représente la requête générée par Power Query pour répondre à la requête. Power Query envoie les demandes de données dans une instruction SQL native à la source de données. Dans ce cas, cela représente tous les enregistrements et tous les champs (colonnes) de la table fact_Sale. Pour ce scénario, ce cas n’est pas souhaitable, car la table contient des millions de lignes et l’intérêt n’est que dans les 10 derniers.
• Table.LastN: une fois que Power Query reçoit tous les enregistrements de la fact_Sale table, il utilise le moteur Power Query pour filtrer la table et conserver uniquement les 10 dernières lignes.
• Table.SelectColumns: Power Query utilise la sortie du Table.LastN nœud et applique une nouvelle transformation appelée Table.SelectColumns, qui sélectionne les colonnes spécifiques que vous souhaitez conserver d’une table.

Pour son évaluation, cette requête devait télécharger toutes les lignes et tous les champs de la fact_Sale table. Cette requête a pris en moyenne 6 minutes et 1 seconde pour être traitée dans une instance standard de dataflows Power BI (qui compte pour l’évaluation et le chargement de données dans des dataflows).

Exemple de repli de requête partielle

Après vous être connecté à la base de données et en accédant à la fact_Sale table, vous commencez par sélectionner les colonnes que vous souhaitez conserver de votre table. Sélectionnez la transformation Choisir des colonnes trouvées dans le groupe Gérer les colonnes sous l’onglet Accueil . Cette transformation vous permet de sélectionner explicitement les colonnes que vous souhaitez conserver de votre table et de supprimer le reste.

Dans la boîte de dialogue Choisir des colonnes, sélectionnez les Sale Keycolonnes, puis Customer KeyInvoice Date KeyDescriptionQuantitysélectionnez OK.

Vous créez maintenant une logique qui trie le tableau pour placer les ventes les plus récentes au bas du tableau. Sélectionnez la Sale Key colonne, qui est la clé primaire et la séquence incrémentielle ou l’index de la table. Triez la table en utilisant uniquement ce champ dans l’ordre croissant à partir du menu contextuel de la colonne.

Ensuite, sélectionnez le menu contextuel du tableau et choisissez la transformation Conserver les lignes inférieures .

Dans Conserver les lignes inférieures, entrez la valeur 10, puis sélectionnez OK.

L’exemple de code suivant est le script M complet de la requête que vous avez créée :

let
    Source = Sql.Database(ServerName, DatabaseName),
    Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
    #"Choose columns" = Table.SelectColumns(
        Navigation, 
        {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"}
    ),
    #"Sorted rows" = Table.Sort(#"Choose columns", {{"Sale Key", Order.Ascending}}),
    #"Kept bottom rows" = Table.LastN(#"Sorted rows", 10)
in
    #"Kept bottom rows"

Exemple de repli de requête partielle : Présentation de l’évaluation de la requête

En vérifiant le volet Étapes appliquées, vous remarquez que les indicateurs de pliage de requête montrent que la dernière transformation que vous avez ajoutée, Kept bottom rowsest marquée comme une étape évaluée en dehors de la source de données ou, en d’autres termes, par le moteur Power Query.

Vous pouvez cliquer avec le bouton droit sur la dernière étape de votre requête, celle nommée Kept bottom rows, puis sélectionner l’option plan de requête pour mieux comprendre comment votre requête peut être évaluée.

Chaque zone de l’image précédente est appelée nœud. Un nœud représente chaque processus qui doit se produire (de gauche à droite) afin que votre requête soit évaluée. Certains de ces nœuds peuvent être évalués à votre source de données, tandis que d’autres, comme le nœud pour Table.LastN, représenté par l’étape Des lignes inférieures conservées, sont évalués à l’aide du moteur Power Query.

Pour afficher la requête exacte envoyée à votre source de données, sélectionnez Afficher les détails dans le Value.NativeQuery nœud.

Cette requête se trouve dans la langue native de votre source de données. Dans ce cas, ce langage est SQL et cette instruction représente une demande pour toutes les lignes, avec uniquement les champs demandés de la fact_Sale table ordonnée par le Sale Key champ.

La consultation de cette demande de source de données peut vous aider à mieux comprendre l’histoire que le plan de requête complet tente de transmettre. L’ordre des nœuds est un processus séquentiel qui commence par demander les données de votre source de données :

• Sql.Database: se connecte à la base de données et envoie des demandes de métadonnées pour comprendre ses fonctionnalités.
• Value.NativeQuery: représente la requête générée par Power Query pour répondre à la requête. Power Query envoie les demandes de données dans une instruction SQL native à la source de données. Dans ce cas, qui représente tous les enregistrements, avec uniquement les champs demandés de la table de la base de données fact_Sale triés par ordre croissant par le champ Sales Key.
• Table.LastN: une fois que Power Query reçoit tous les enregistrements de la fact_Sale table, il utilise le moteur Power Query pour filtrer la table et conserver uniquement les 10 dernières lignes.

Pour son évaluation, cette requête devait télécharger toutes les lignes et uniquement les champs requis de la fact_Sale table. Il a fallu en moyenne 3 minutes et 4 secondes pour le traitement dans une instance standard des "dataflows" Power BI (qui inclut l'évaluation et le chargement de données dans des "dataflows").

Exemple de fusion de requête complète

Après vous être connecté à la base de données et accédez à la fact_Sale table, commencez par sélectionner les colonnes que vous souhaitez conserver de votre table. Sélectionnez la transformation Choisir des colonnes trouvées dans le groupe Gérer les colonnes sous l’onglet Accueil . Cette transformation vous permet de sélectionner explicitement les colonnes que vous souhaitez conserver de votre table et de supprimer le reste.

Dans Choisir des colonnes, sélectionnez les colonnes Sale Key, Customer Key, Invoice Date Key, Description et Quantity, puis cliquez sur OK.

Vous créez maintenant une logique qui trie le tableau pour avoir les dernières ventes en haut du tableau. Sélectionnez la Sale Key colonne, qui est la clé primaire et la séquence incrémentielle ou l’index de la table. Triez la table en utilisant uniquement ce champ dans l'ordre décroissant via le menu contextuel de la colonne.

Ensuite, sélectionnez le menu contextuel du tableau et choisissez la transformation Conserver les lignes supérieures .

Dans Conserver les lignes supérieures, entrez la valeur 10, puis sélectionnez OK.

L’exemple de code suivant est le script M complet de la requête que vous avez créée :

let
    Source = Sql.Database(ServerName, DatabaseName),
    Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
    #"Choose columns" = Table.SelectColumns(
        Navigation, 
        {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"}
    ),
    #"Sorted rows" = Table.Sort(#"Choose columns", {{"Sale Key", Order.Descending}}),
    #"Kept top rows" = Table.FirstN(#"Sorted rows", 10)
in
    #"Kept top rows"

Exemple de pliage de requête complet : Présentation de l’évaluation de la requête

Lors de la vérification du volet des étapes appliquées, notez que les indicateurs de pliage de requête montrent que les transformations que vous avez ajoutées, Colonnes choisies, Lignes triées et Lignes du haut conservées, sont marquées en tant qu’étapes évaluées de la source de données.

Vous pouvez cliquer avec le bouton droit sur la dernière étape de votre requête, celle nommée Lignes conservées, puis sélectionner l’option intitulée plan de requête.

Cette requête se trouve dans la langue native de votre source de données. Dans ce cas, ce langage est SQL et cette instruction représente une demande pour toutes les lignes et champs de la fact_Sale table.

La consultation de cette requête de source de données peut vous aider à mieux comprendre l’histoire que le plan de requête complet tente de transmettre :

• Sql.Database: se connecte à la base de données et envoie des demandes de métadonnées pour comprendre ses fonctionnalités.
• Value.NativeQuery: représente la requête générée par Power Query pour répondre à la requête. Power Query envoie les demandes de données dans une instruction SQL native à la source de données. Dans ce cas, cela représente une demande pour seulement les 10 premiers enregistrements de la fact_Sale table, avec uniquement les champs obligatoires après avoir été triés dans l’ordre décroissant à l’aide du Sale Key champ.

Pour son évaluation, cette requête télécharge uniquement 10 lignes, avec uniquement les champs que vous avez demandés à partir de la fact_Sale table. Cette requête a pris en moyenne 31 secondes pour être traitée dans une instance standard de dataflows Power BI (qui compte pour l’évaluation et le chargement de données dans des dataflows).

Comparaison entre les performances

Pour mieux comprendre l’effet que le pliage des requêtes a dans ces requêtes, vous pouvez actualiser vos requêtes, enregistrer le temps nécessaire pour actualiser entièrement chaque requête et les comparer. Par souci de simplicité, cet article fournit les minutages d’actualisation moyens capturés à l’aide du mécanicien d’actualisation des flux de données Power BI lors de la connexion à un environnement Azure Synapse Analytics dédié avec DW2000c comme niveau de service.

L’heure d’actualisation de chaque requête était la suivante :

C’est souvent le cas qu’une requête qui revient entièrement à la source de données dépasse les requêtes similaires qui ne se plient pas complètement à la source de données. Il pourrait y avoir de nombreuses raisons pour lesquelles c’est le cas. Ces raisons vont de la complexité des transformations effectuées par votre requête, aux optimisations de requête implémentées à votre source de données, telles que les index et l’informatique dédiée et les ressources réseau. Toutefois, il existe deux processus clés spécifiques que le pliage de requête tente d’utiliser pour minimiser l'impact que ces deux processus ont sur Power Query.

• Données en transit
• Transformations exécutées par le moteur Power Query

Les sections suivantes expliquent l’effet que ces deux processus ont dans les requêtes mentionnées précédemment.

Données en transit

Lorsqu’une requête est exécutée, elle tente d’extraire les données de la source de données en tant que première étape. Les données extraites de la source de données sont définies par le mécanisme de pliage des requêtes. Ce mécanisme identifie les étapes de la requête qui peuvent être déchargées vers la source de données.

Le tableau suivant répertorie le nombre de lignes demandées à partir de la fact_Sale table de la base de données. La table inclut également une brève description de l’instruction SQL envoyée pour demander ces données à la source de données.

Lorsque vous demandez des données à partir d’une source de données, la source de données doit calculer les résultats de la demande, puis envoyer les données au demandeur. Bien que les ressources informatiques aient déjà été mentionnées, les ressources réseau de déplacement des données de la source de données vers Power Query, puis Power Query peuvent recevoir efficacement les données et les préparer pour les transformations qui se produisent localement peuvent prendre un certain temps en fonction de la taille des données.

Pour les exemples présentés, Power Query a dû demander plus de 3,6 millions de lignes à partir de la source de données pour les exemples sans pliage de requête et de pliage de requête partiel. Pour l'exemple complet de repliement de requête, seulement 10 lignes ont été demandées. Pour les champs demandés, l’exemple de pliage de requête a demandé tous les champs disponibles depuis la table. Les exemples de pliage de requêtes partielles et de pliage de requêtes complètes n’ont envoyé qu’une demande pour exactement les champs dont ils ont besoin.

Transformations exécutées par le moteur Power Query

Cet article explique comment utiliser le plan de requête pour mieux comprendre comment votre requête peut être évaluée. Dans le plan de requête, vous pouvez voir les nœuds exacts des opérations de transformation effectuées par le moteur Power Query.

Le tableau suivant présente les nœuds des plans de requête des requêtes précédentes qui auraient été évaluées par le moteur Power Query.

Pour les exemples présentés dans cet article, l’exemple de pliage de requête complet ne nécessite aucune transformation à l’intérieur du moteur Power Query, car la table de sortie requise provient directement de la source de données. En revanche, les deux autres requêtes nécessitaient un calcul sur le moteur Power Query. En raison de la quantité de données qui doivent être traitées par ces deux requêtes, le processus de ces exemples prend plus de temps que l’exemple complet de pliage des requêtes.

Les transformations peuvent être regroupées en catégories suivantes :

Considérations et suggestions

• Suivez les meilleures pratiques lors de la création d’une requête, comme indiqué dans Les meilleures pratiques dans Power Query.
• Utilisez les indicateurs de repli de requête pour vérifier les étapes qui empêchent votre requête de se replier. Réorganisez-les si nécessaire pour augmenter le pliage.
• Utilisez le plan de requête pour déterminer les transformations qui se produisent au niveau du moteur Power Query pour une étape particulière. Envisagez de modifier votre requête existante en réorganisant vos étapes. Vérifiez ensuite le plan de requête de la dernière étape de votre requête et vérifiez si le plan de requête semble mieux que celui précédent. Par exemple, le nouveau plan de requête a moins de nœuds que le précédent, et la plupart des nœuds sont des nœuds « Streaming » et non « analyse complète ». Pour les sources de données qui prennent en charge le pliage, les nœuds du plan de requête autres que Value.NativeQuery les nœuds d’accès aux sources de données représentent des transformations qui n’ont pas été pliés.
• Si elle est disponible, vous pouvez utiliser l’option Afficher la requête native (ou afficher la requête de source de données) pour vous assurer que votre requête peut être repliée vers la source de données. Si cette option est désactivée pour votre étape et que vous utilisez une source qui l’active normalement, vous avez créé une étape qui arrête le pliage des requêtes. Si vous utilisez une source qui ne prend pas en charge cette option, vous pouvez vous appuyer sur les indicateurs de repli de requête et le plan de requête.
• Utilisez les outils de diagnostic de requête pour mieux comprendre les demandes envoyées à votre source de données lorsque les fonctionnalités de pliage des requêtes sont disponibles pour le connecteur.
• Lorsque vous combinez des sources de données à partir de l’utilisation de plusieurs connecteurs, Power Query tente d’envoyer le plus de travail possible aux deux sources de données tout en respectant les niveaux de confidentialité définis pour chaque source de données.
• Lisez l’article sur les niveaux de confidentialité pour protéger vos requêtes contre une erreur de pare-feu de confidentialité des données.
• Utilisez d’autres outils pour vérifier le pliage des requêtes du point de vue de la requête reçue par la source de données. En fonction de l’exemple de cet article, vous pouvez utiliser Microsoft SQL Server Profiler pour vérifier les demandes envoyées par Power Query et reçues par Microsoft SQL Server.
• Si vous ajoutez une nouvelle étape à une requête entièrement pliée et que la nouvelle étape se plie également, Power Query peut envoyer une nouvelle requête à la source de données au lieu d’utiliser une version mise en cache du résultat précédent. Dans la pratique, ce processus peut entraîner des opérations apparemment simples sur une petite quantité de données qui prennent plus de temps à s’actualiser dans la préversion que prévu. Cette actualisation plus longue est due à la nouvelle requête Power Query qui interroge la source de données plutôt que de travailler sur une copie locale des données.