IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Voir le flux RSS

Pierre Fauconnier

[Actualité] Filtrer une source MS SQL avec Power Query (L'histoire des trois petits cochons revisitée)

Noter ce billet
par , 24/09/2021 à 09h43 (3619 Affichages)
Salut

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:

Nom : 2021-09-24_093244.png
Affichages : 1210
Taille : 13,6 Ko

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)

Nom : 2021-09-23_211839.png
Affichages : 1148
Taille : 129,7 Ko

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.

Nom : 2021-09-23_214309.png
Affichages : 1131
Taille : 15,0 Ko

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

Nom : 2021-09-23_214557.png
Affichages : 1132
Taille : 46,8 Ko

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 - Visualiser dans une fenêtre à part
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:
  1. il faut avoir le droit de requêter par commande textuelle dans la db;
  2. il faut connaître le langage sql;
  3. Il faut échapper certains caractères tels que ', _ ou % (liste non exhaustive);
  4. 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 - Visualiser dans une fenêtre à part
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:

Nom : 2021-09-24_090419.png
Affichages : 1124
Taille : 8,4 Ko

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() & "'"]).

Nom : 2021-09-24_091511.png
Affichages : 1115
Taille : 7,8 Ko

Avantages:
  1. 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;
  2. L'accès aux données est sécurisé;
  3. La procédure stockée mutualise la récupération des données;
  4. 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.


.

Envoyer le billet « Filtrer une source MS SQL avec Power Query (L'histoire des trois petits cochons revisitée) » dans le blog Viadeo Envoyer le billet « Filtrer une source MS SQL avec Power Query (L'histoire des trois petits cochons revisitée) » dans le blog Twitter Envoyer le billet « Filtrer une source MS SQL avec Power Query (L'histoire des trois petits cochons revisitée) » dans le blog Google Envoyer le billet « Filtrer une source MS SQL avec Power Query (L'histoire des trois petits cochons revisitée) » dans le blog Facebook Envoyer le billet « Filtrer une source MS SQL avec Power Query (L'histoire des trois petits cochons revisitée) » dans le blog Digg Envoyer le billet « Filtrer une source MS SQL avec Power Query (L'histoire des trois petits cochons revisitée) » dans le blog Delicious Envoyer le billet « Filtrer une source MS SQL avec Power Query (L'histoire des trois petits cochons revisitée) » dans le blog MySpace Envoyer le billet « Filtrer une source MS SQL avec Power Query (L'histoire des trois petits cochons revisitée) » dans le blog Yahoo

Mis à jour 26/09/2021 à 04h28 par Malick (Centrage des images)

Catégories
Excel , MS Office , Power Query, Power Pivot, Power View

Commentaires