Contenidos
Cette semaine, je travaillais pour un client - ils ont eu un problème de performances avec un rapport Power BI. Les données du classeur n'étaient pas trop volumineuses, environ 400 000 lignes, mais la taille du fichier était de 110 mégaoctets et les performances du modèle étaient relativement lentes compte tenu du nombre d'enregistrements. Quand j'ai regardé le rapport, j'ai remarqué que le rapport utilisait des GUID entre les clés primaires et étrangères dans plusieurs tables. En règle générale, il n'est pas recommandé d'utiliser un GUID pour joindre des tables, car les GUID ne se compressent pas correctement et ont un effet négatif sur l'efficacité des relations physiques 1 à plusieurs.
Qu'est-ce qu'un GUID?
GUID est un acronyme pour Global Unique IDentifier. En bref, un GUID est un nombre hexadécimal qui équivaut à un nombre décimal avec 39 chiffres, quelque chose comme ça 1 000 000 000 000 000 000 000 000 000 000 000 000 (un duodécillion). En d'autres termes, un GUID est un ID très unique qu'il est peu probable (basé sur la probabilité) de se reproduire même s'il a été généré de manière aléatoire, ce qui est le cas. En savoir plus ici:
https://betterexplained.com/articles/the-quick-guide-to-guids/
Quel est le problème avec l'utilisation des GUID dans les relations?
Une relation physique 1 à plusieurs dans Power BI est un élément essentiel de la structure de base de données sous-jacente. Le moteur Power BI (Vertipaq) matérialise et stocke ces relations dans la base de données, puis les utilise pour propager rapidement des filtres d'une table à une autre (via la relation 1-à-plusieurs). Il est très courant (même souhaitable) que la logique de rapport soit chargée dans le cache rapide L1 ou L2 de la puce de votre PC afin que vous puissiez faire votre travail très rapidement. Si la relation ne rentre pas dans le cache, l'ensemble du processus sera plus lent. Les éléments qui peuvent augmenter la taille de la relation sont le nombre de valeurs uniques dans les colonnes utilisées dans la relation, mais également le type de données utilisé dans la relation. Qu'il suffise de dire qu'un GUID est beaucoup moins efficace qu'une valeur entière en tant que colonne clé dans Power BI.
Entrez un mot de passe de remplacement
Une façon de résoudre ce problème consiste à remplacer le GUID par une clé de substitution. Une clé de substitution, comme son nom l'indique, est une nouvelle «colonne clé» qui est un «substitut» (ou remplacement) de la colonne clé d'origine. Dans un monde parfait où vous avez un serveur SQL dans le back-end et vous avez un service informatique qui peut faire le travail pour vous, je vous suggère de demander à votre service informatique de créer la clé de substitution pour vous et de la rendre disponible en un clin d'œil. vous n'avez donc pas besoin d'utiliser le GUID. Mais le monde dans lequel nous vivons n'est pas parfait, c'est pourquoi cet article vous montrera comment remplacer le GUID par une clé de substitution à l'aide de Power Query.
Étapes à suivre
En supposant que vous ne pouvez pas effectuer le travail au niveau de la source de données, il s'agit essentiellement d'un problème que Power Query doit résoudre. Les étapes pour terminer ce processus sont les suivantes
- créer une connexion à la table de dimension brute
- créer une branche dans la requête d'alimentation
- ajouter une nouvelle colonne d'identifiant entier (clé de substitution)
- Rejoindre la table de clé de substitution avec la table de dimension d'origine et remplacer le GUID à l'aide de la jointure
- répétez l'étape pour remplacer le GUID dans la table de faits
Ce processus suppose que votre table de dimension contient une liste complète des clés qui existent dans votre table de faits. Si ce n'est pas le cas, vous avez quand même un problème. Il est possible de faire varier ce modèle pour inclure les GUID qui proviennent également de la table de faits, mais cela ne résout pas vraiment le problème de racine (vous pourriez avoir des ID dans la table de faits manquants dans la table de dimension). non fourni / suggéré dans le cadre de la solution. De plus, cela ralentira certainement le temps de mise à jour sans résoudre le problème racine.
Connectez-vous aux données
Comme vous pouvez le voir dans l'image ci-dessous, j'ai deux requêtes (RawCustomer, RawSales indiquées comme # 1 ci-dessous) qui se connectent directement à mes exemples de données (j'ai modifié Adventure Works pour que le numéro de client utilise un GUID # 2 ci-dessous). Notez que mes deux requêtes de données brutes sont de simples connexions. C'est la technique que Ken enseigne dans le Formation à la Power Query Academyet je pense que c'est une excellente pratique. À partir de là, j'ai créé deux requêtes de test (illustrées comme # 3 ci-dessous) qui sont de simples références aux requêtes de données brutes
Donc à ce stade, ça ressemble à ça
Créer une branche dans Power Query sur la table de dimension
- La prochaine étape que j'ai prise a été de cliquer avec le bouton droit sur la requête de disponibilité du client et de sélectionner «référence» pour créer une nouvelle succursale. J'ai appelé cette nouvelle requête CustomerGUIDs. Dans cette requête, je viens d'enregistrer la colonne GUID et de supprimer tout le reste. Par mesure de sécurité, je supprime les doublons au cas où il y aurait un doublon dans la colonne GUID (peu probable cependant).
- Ensuite, j'ai ajouté une colonne d'index commençant à 1 et l'ai nommée CustomerID.
La nouvelle colonne CustomerID est la clé de substitution. La beauté de cette approche est que les clés de substitution augmenteront au fil du temps si la liste GUID d'origine augmente (ce qui est très probable). En outre, s'il y a des suppressions de la table client, la clé de substitution se reconstruira simplement avec le nouvel ensemble de données.
Rejoindre la table de clés de substitution pour former une nouvelle table de dimension
Vous pouvez voir la version avant (n ° 1) et après (n ° 2) du tableau des clients ci-dessous.
Répétez le processus pour remplacer le GUID dans la table de faits
- J'ai créé une nouvelle requête à partir de la requête SalesStaging (clic droit, «référence») et l'ai fusionnée avec la table CustomerGUIDs.
- Puis j'ai extrait la clé de substitution
- Ensuite, j'ai supprimé le GUID d'origine de la table des ventes finales.
Ma vue finale des dépendances de requête ressemble à ceci.
Je me suis assuré que toutes les tables étaient configurées pour ne pas se charger en dehors des tables Clients et Ventes.
Résultats
La démo que je vous ai montrée ici est avec un très petit ensemble de données (19 000 lignes). Dans mon cas, l'approche de la clé de substitution a réduit la taille du fichier de plus de 30%, et il n'y avait qu'une seule table de clé de substitution dans mon fichier. Pour mon client qui avait environ 400000 lignes de données, 3 colonnes de clé GUID et un nombre beaucoup plus grand de GUID uniques, la réduction de la taille du fichier était beaucoup plus importante, de 110 Mo à seulement 11 Mo (réduction de 90% avec la clé de substitution). Probablement le plus important, il y a eu des améliorations de performances notables après le changement.
Y a-t-il des impacts négatifs?
Temps de mise à jour
À ce stade, vous vous demandez peut-être "cela ne ralentira-t-il pas le temps de mise à jour?" Si vous pensez cela, vous avez tout à fait raison, cela ralentira probablement les performances de la mise à jour. Cependant, il est préférable d'avoir un temps de mise à jour plus lent et des performances d'exécution plus rapides que l'inverse.
Que faire si j'ai besoin de mes GUID pour l'audit?
Un deuxième problème est que vous aurez peut-être besoin des GUID pour pouvoir retracer les données de votre rapport jusqu'à la transaction sur le système source. S'il s'agit d'un besoin courant avec vos données, je vous suggère de toujours créer et d'utiliser la clé de substitution, mais également de charger le GUID dans la table de dimension en tant que colonne supplémentaire. De cette façon, le GUID est disponible mais pas utilisé dans la relation. Mieux encore, supprimez le GUID de la table de dimension, puis ramenez-le ultérieurement uniquement si vous rencontrez un problème qui vous oblige à tracer la source.
Conclusion et où en savoir plus
Voici un copie de classeurs J'ai utilisé dans cet article au cas où vous seriez intéressé à regarder de plus près.
Si vous voulez apprendre à être génial avec Power Query, je vous recommande de jeter un œil à la Académie Power Query formation en ligne sur Skillwave.training. Ken, Miguel et moi avons uni nos forces pour créer le cours de formation Power Query le meilleur et le plus complet disponible.
