Apprendre à utiliser une procédure stockée MS SQL avec Power QuerySalut
- Pierre, lorsque je ramène mes infos de ventes de sql, Power Query me ramène énormément de lignes qui ne servent à rien...
- Vends moins... Tu auras moins de lignes à ramener de ta DB;
- Hmmmm. Oui, je vais y penser... A par cela, t'as pas un truc vite fait?
- Crée une procédure stockée paramétrée dans ton SQL, tu feras travailler ton serveur plutôt que ton réseau
-... Heu... Mais encore?
Dans cet ancien billet, je montrais comment filtrer une requête Power Query sur base d'un critère exprimé côté Excel. Dans cet autre billet, j'expliquais comment Power Query "comprenait" une cellule nommée Excel. Ces techniques illustrent deux solutions pour filtrer une requête Power Query: la cellule nommée et le tableau structuré.
Power Query propose de rechercher des données sur un serveur SQL et propose alors de choisir les tables qui seront récupérées dans Power Query:
Dans le cadre d'une requête Power Query ayant comme source une table ou une vue SQL, cette méthode serait celle de la maison de paille.
La maison de paille
Pour rappel, cette méthode consiste à monter dans la solution Power Query la cellule nommée ou le tableau structuré, puis à fusionner les requêtes en jointure interne (voir mon billet sur les jointures)
Pourquoi est-ce une maison de paille? Parce que c'est Power Query qui filtre. Dit autrement, cela signifie que Power Query ramène TOUTES les lignes pour ne garder que les bonnes...
Si vous avez 10.000 lignes à garder d'une table ou vue de 1.000.000 lignes, vous ramenez 990.000 lignes pour RIEN!!La maison de bois
Une solution pour ne ramener que les lignes souhaitées en déléguant le travail de filtrage au moteur MS-SQL consiste à saisir la commande de la requête plutôt que d'aller chercher les données dans les tables ou les vues.
On obtient alors une requête en une étape. On pourrait d'ailleurs passer directement à la saisie de l'étape dans une requête vide (sur base d'un squelette stocké dans son OneNote, par exemple)...
Pour rendre le filtre dynamique, c'est-à-dire récupérer le service choisi dans Excel, nous allons devoir utiliser une fonction qui récupère le service pour recréer la chaine de commande sql (voir mon billet sur la création d'une fonction Power Query(*)). Voici le script de cette fonction, que j'appelle xlService:
| Code : | Sélectionner tout |
1 2 3 4 5 6 7 | let
Source = () as text => let
Source = Table.FirstValue(Excel.CurrentWorkbook(){[Name="Service"]}[Content])
in
Source
in
Source |
Notre commande SQL devient alors: = Sql.Database("MonServeur", "dvp-tests", [Query="select ContactPK, Firstname, Lastname, service from contact where service = '" & xlService() & "'"]). On remarque que notre chaine de commande est recomposée par concaténation de la même manière que celle utilisée en Excel.
Pourquoi une maison de bois? Certes, on ne ramène que les lignes souhaitées, mais:
- il faut avoir le droit de requêter par commande textuelle dans la db;
- il faut connaître le langage sql;
- Il faut échapper certains caractères tels que ', _ ou % (liste non exhaustive);
- il faut connaître la structure des données (tables, vues, ...).
Quatre raisons qui font que cette solution n'est pas la plus robuste.
La maison de briques
Recréer la commande dans Power Query pose donc certains problèmes, dont celui non négligeable résultant du fait que le DBA (DataBase Administrator) de votre entreprise ne vous a peut-être pas donné les droits "d'attaquer" la base par des commandes textuelles.
La meilleure technique consisterait selon moi à créer une procédure stockée côté SQL, et à l'appeler par Power Query. Cette solution délègue ainsi la création de la commande textuelle à SQL, et vous utilisez dans Power Query la procédure stockée, éventuellement paramétrée, comme un alias pointant vers la commande sql(**). De notre point de vue d'utilisateur Excel, on peut voir la procédure stockée (stored procedure, en anglais) comme étant une fonction à laquelle on passe des arguments et qui nous renvoie une table de données.
Voici une procédure stockée qui permet de récupérer les contacts en fonction d'un service:
| Code sql : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | USE [DVP-Tests] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Pierre Fauconnier -- Create date: 2021-09-24 -- Description: Récupère les contacts du service passé en paramètre -- ============================================= ALTER PROCEDURE [dbo].[GetContactsForService] @Service varchar(50) AS BEGIN SET NOCOUNT ON; select ContactPK, Firstname, Lastname, Service from contact with(nolock) where Service = @Service END |
Si l'on exécute cette procédure stockée dans le SQL Management Studio, on précise le service choisi dans la fenêtre de dialogue et on obtient le résultat suivant, qui reprend la commande utilisée et le jeu d'enregistrements (Recordset) produit:
Dans la fenêtre supérieure, on remarque le texte de la commande générée lors de l'exécution de la procédure stockée, que l'on peut simplifier grandement et l'écrire comme ceci: EXEC [dbo].[GetContactsForService] @Service = N'it' selon la configuration dans laquelle on se trouve dans le SQL Management Studio.
Vous l'aurez compris, il suffit de passer cette chaine de commande dans notre Power Query pour utiliser la procédure stockée directement. En gardant l'idée de la fonction xlService() pour récupérer le nom du service choisie dans Excel, notre requête Power Query devient: = Sql.Database("ServeurSQL", "dvp-tests", [Query="EXEC [dbo].[GetContactsForService] @Service = N'" & xlService() & "'"]).
Avantages:
- Les techniques SQL sont déportées du côté du moteur SQL, vous déchargeant de la construction de la chaine de commande in extenso;
- L'accès aux données est sécurisé;
- La procédure stockée mutualise la récupération des données;
- Une modification de la procédure stockée n'affecte pas le fonctionnement de vos requêtes Power Query.
Pour l'avantage n°4, on pourrait imaginer que l'on ajoute une clause Order By côté SQL (dans la procédure stockée) pour éviter de devoir réaliser le tri dans Power Query qui récupérerait alors le jeu d'enregistrements trié: Order By Lastname, FirstName par exemple. Avantage: Cette modification "au plus près de la source" est mutualisée et disponible pour tout client de la procédure stockée.
Conclusions
Comme on le voit, Power Query et SQL Server font évidemment bon ménage et les procédures stockées, liées à des fonctions de récupération des arguments d'Excel dans Power Query donnent encore plus de souplesse et de puissance à vos requêtes, tout en vous déchargeant au maximum d'un travail qui, en fait, n'est pas forcément le vôtre.
Bon travail avec Excel, Power Query et SQL Server.
Si vous mettez ces techniques en place, n'hésitez pas à mettre votre feedback en commentaires 
(*) J'explique dans ce billet comment gérer plusieurs paramètres grâce à un tableau structuré
(**) Cette façon de procéder permet en plus de ne pas "mélanger les genres" (vous êtes utilisateur d'Excel, par DBA), de sécuriser et de limiter l'accès aux données et vous permet de vous concentrer sur votre business, à savoir l'analyse de données avec Excel et Power Query.
.