Se connecter à une procédure stockée SQL avec Power Query

Publié le 30 mai 2022
par Kim Leblanc BAA
Procédure stockée - Le CFO Masqué

Saviez-vous qu’il était possible de se connecter à une procédure stockée SQL (Stored Procedure) avec Power Query ? On peut même y passer des paramètres à partir de Power Query et rendre le tout dynamique !

Ça peut être vraiment utile si une procédure stockée existe déjà, au lieu de rebâtir le tout dans Power Query. Surtout si cette dernière est relativement complexe et utilise plusieurs tables en amont.

 

Voici une procédure stockée SQL toute simple pour vous démontrer comment faire.

Procédure stockée - Create

 

Le paramètre de ma procédure stockée @Produit me permet d’identifier le produit pour lequel je veux obtenir les données. Si je passe, par exemple, le produit 722 dans la procédure, voici le résultat obtenu dans SQL Server Management Studio :

Procédure stockée - Excecute

 

Je voudrais donc amener ces données dans Power Query et y spécifier le produit à sélectionner.

 

1. Se connecter à une procédure stockée SQL

Voici en premier lieu comment se connecter à la procédure stockée.

À partir de Power BI (il serait également possible de le faire à partir d’Excel), on choisit, dans le menu Accueil (section Données), l’option SQL Server.

SQL Server

 

On pourra ensuite entrer les informations de la façon suivante :

  1. Le nom du serveur SQL
  2. Le nom de la base de données
  3. On sélectionne le mode Importation de données. On ne peut pas utiliser DirectQuery pour cette fonctionnalité.
  4. Dans la section Options avancées, inscrire l’instruction SQL qui exécutera la procédure Stockée. Dans le cas de notre exemple, on inscrit : EXECUTE [dbo].[SP_ProductOrder] @Produit = 722

Ce qui aura pour effet d’exécuter la procédure et de passer en paramètre le numéro de produit 722. On verra plus loin comment on modifie cet « input » pour le rendre dynamique.

  1. On appuie sur OK.

Procédure stockée - Base de données

 

Au message suivant, on clique sur Exécuter :

Procédure stockée - Requête

 

Puis Ok sur le message suivant :

Prise en charge du chiffrement

 

On obtient alors nos données pour le produit 722.

Procédure stockée - Produit

 

2. Création d’un paramètre

Allons maintenant un peu plus loin, en rendant le tout plus dynamique, en remplaçant la valeur 722 inscrite dans l’instruction par un paramètre.

Pour créer un paramètre dans Power BI, il faut aller dans le menu accueil, puis cliquer sur Gérer les paramètres et choisir Nouveau paramètre.

Procédure stockée - Nouveau paramêtre

 

Il faudra ensuite donner un nom au paramètre, le type de valeur et la valeur actuelle que l’on veut associer à ce paramètre.

Procédure stockée - Paramêtres

 

 

Vous avez des sources de données volumineuses à rassembler et analyser dans Excel ? Apprenez à automatiser tout le processus d’importation, de transformation et de modélisation  des données avec nos formations sur Power Query et Power Pivot dans Excel.

 

3. Intégration du paramètre dans le code M

On pourra ensuite venir remplacer une partie de notre code M pour utiliser le paramètre.

Il faut remplacer 722 par la section en jaune dans l’image ci-dessous. Il faut passer notre paramètre produit dans la fonction Text.From pour le convertir en texte puisqu’on ne peut pas concaténer du texte avec une valeur numérique.

Procédure stockée - Text From

 

Vous remarquerez que j’ai également utilisé un paramètre pour le nom du serveur (NomServeur) et un autre pour la base de données (BaseDonnees). Ainsi, si j’utilise ces informations dans d’autres requêtes, je pourrai, en cas de modification, remplacer l’information dans mes paramètres au lieu de parcourir chacune des requêtes pour en faire le changement.

 

Aller plus loin avec une liste provenant d’une autre requête

On pourrait, par la suite, identifier une liste de produits qui pourrait provenir d’une autre requête/source de données. Puis passer cette liste, à l’aide de notre paramètre, dans la procédure stockée. On obtiendrait alors l’information pour chacun de ces produits.

Il faudra, pour se faire, transformer notre requête en fonction. Le tout vous est démontré dans cet article : Une fonction pour paramétrer une procédure stockée

 


Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

 

Formation complémentaire

Afin d’explorer les principales fonctions et fonctionnalités de Power Query, qui permet d’importer, de transformer et de fusionner des données de diverses sources et de pouvoir les analyser efficacement, suivez la formation Excel – Introduction à Power Query et au langage M.

Voici quelques commentaires d’apprenants ayant suivi cette formation :

Voici quelques commentaires d’apprenants ayant suivi la formation - Excel Introduction à Power Query et au langage M
CFO-Masque_Formations-en-ligne_FBLa mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Retour en haut