Optimiser Power Query lors du développement des colonnes de table

La simplicité et la facilité d’utilisation qui permettent aux utilisateurs de Power BI de collecter rapidement des données et de générer des rapports intéressants et puissants pour prendre des décisions commerciales intelligentes permettent également aux utilisateurs de générer facilement des requêtes peu performantes. Cela se produit souvent lorsqu'il y a deux tables qui sont liées de la même manière qu’une clé étrangère relie des tables SQL ou des listes SharePoint. (Pour information, ce problème n’est pas spécifique à SQL ou SharePoint. Il se produit dans de nombreux scénarios d’extraction de données back-end, en particulier quand le schéma est fluide et personnalisable.) Il n’y a rien de mal non plus à stocker des données dans des tables distinctes qui partagent une clé commune. En fait, c'est un principe fondamental de la conception et de la normalisation des bases de données. Mais cela implique une meilleure façon de développer les relations.

Prenons l’exemple suivant de liste de clients SharePoint.

Et la liste d’adresses suivante à laquelle elle fait référence.

Quand vous vous connectez pour la première fois à la liste, l’adresse s’affiche sous forme d’enregistrement.

Ces données de premier niveau sont rassemblées via un seul appel HTTP à l’API SharePoint (en ignorant l’appel des métadonnées), comme vous pouvez le voir dans n’importe quel débogueur web.

Quand vous développez l’enregistrement, vous voyez les champs joints à partir de la table secondaire.

Quand vous développez des lignes associées d’une table à une autre, le comportement par défaut de Power BI consiste à générer un appel à Table.ExpandTableColumn. Vous pouvez le voir dans le champ de formule généré. Malheureusement, cette méthode génère un appel individuel à la deuxième table pour chaque ligne de la première table.

Cela entraîne une augmentation du nombre d’appels HTTP d’une unité pour chaque ligne de la liste principale. Cela peut sembler peu dans l’exemple ci-dessus de cinq ou six lignes, mais dans les systèmes de production où les listes SharePoint atteignent des centaines de milliers de lignes, cela peut entraîner une dégradation significative de l’expérience utilisateur.

Quand les requêtes atteignent ce goulot d’étranglement, la meilleure atténuation consiste à éviter le comportement d’appel ligne par ligne à l’aide d’une jointure de table classique. Cela permet de garantir qu’un seul appel est effectué pour récupérer la deuxième table, et que le reste du développement peut s’effectuer en mémoire à l’aide de la clé commune entre les deux tables. La différence de performance peut être énorme dans certains cas.

Tout d’abord, commencez par la table d’origine, notez la colonne à développer, puis vérifiez que vous disposez de l’ID de l’élément pour pouvoir le faire correspondre. En règle générale, le nom de la clé étrangère est similaire au nom d’affichage de la colonne auquel Id est ajouté. Dans cet exemple, il s’agit de LocationId.

Chargez ensuite la table secondaire en veillant à inclure l'ID, qui représente la clé étrangère. Cliquez avec le bouton droit sur le panneau Requêtes pour créer une requête.

Enfin, joignez les deux tables à l’aide des noms de colonnes respectifs correspondants. Vous pouvez généralement trouver ce champ en développant d’abord la colonne, puis en recherchant les colonnes correspondantes dans l’aperçu.

Dans cet exemple, vous pouvez voir que LocationId dans la liste principale correspond à Id dans la liste secondaire. L’IU renomme ceci en Location.Id pour rendre le nom de colonne unique. Utilisons à présent ces informations pour fusionner les tables.

Quand vous cliquez avec le bouton droit dans le panneau Requêtes, et que vous sélectionnez Nouvelle requête>Combiner>Fusionner les requêtes comme nouvelles, une IU conviviale s’affiche pour vous aider à combiner ces deux requêtes.

Sélectionnez chaque table dans la liste déroulante pour voir un aperçu de la requête.

Une fois que vous avez sélectionné les deux tables, sélectionnez la colonne qui joint les tables de manière logique (dans cet exemple, il s’agit de LocationId dans la table principale et de Id dans la table secondaire). La boîte de dialogue vous indique le nombre de lignes qui correspondent à l’aide de cette clé étrangère. Vous souhaiterez probablement utiliser le genre de jointure par défaut (externe gauche) pour ce genre de données.

Sélectionnez OK, et vous verrez une nouvelle requête représentant le résultat de la jointure. Désormais, le développement de l’enregistrement n’implique pas d’appels supplémentaires au back-end.

L’actualisation de ces données entraîne uniquement deux appels à SharePoint : un pour la liste principale, et un autre pour la liste secondaire. La jointure est effectuée en mémoire, ce qui réduit considérablement le nombre d’appels à SharePoint.

Cette approche peut être utilisée pour deux tables Power Query ayant une clé étrangère correspondante.

Remarque

Les listes d’utilisateurs et la taxonomie SharePoint sont également accessibles sous forme de tables, et peuvent être jointes exactement de la manière décrite ci-dessus, à condition que l’utilisateur dispose des privilèges adéquats pour accéder à ces listes.