Dans cet article, nous allons voir comment power Query peut interagir avec une procédure stockée en lui passant dynamiquement des paramètres stockés dans une feuille Excel.

Passage de paramètre avec des valeurs distinctes:

-Créer une table DimCustomer pour l’exemple:
CREATE TABLE [dwh].[DimCustomer](
[SID_CUSTOMER] [smallint] NOT NULL,
[CUSTOMER_LABEL] [varchar](50) NOT NULL,
[CUSTOMER_BACK_COLOR] [varchar](10) NULL,
[CUSTOMER_ORDER] [tinyint] NULL,
CONSTRAINT [PK_DimCustomer] PRIMARY KEY CLUSTERED
(
[SID_CUSTOMER] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

–Insérer qlqs enregistrements dans cette table

insert Into [dwh].[DimCustomer] ([SID_CUSTOMER],[CUSTOMER_LABEL], [CUSTOMER_BACK_COLOR],[CUSTOMER_ORDER]) values (1,’NYC’,’SeaGreen’,1)
insert Into [dwh].[DimCustomer] ([SID_CUSTOMER],[CUSTOMER_LABEL], [CUSTOMER_BACK_COLOR],[CUSTOMER_ORDER]) values (20,’LILLE’,’#828282′,20)

-Créer une procédure stockée (SP) de test [dbo].[PowerQueryPassezParameter]

CREATE PROCEDURE [dbo].[PowerQueryPassezParameter] (
@ReportParameter_DimCustomerID AS varchar(500),
@ReportParameter_DimSale_Date_1 AS INT,
@ReportParameter_DimSale_Date_2 AS INT
)
AS BEGIN

select Top 10 * from [dwh].[DimCustomer]
where [SID_CUSTOMER] = @ReportParameter_DimCustomerID
And DimSale_Date_1 = @ReportParameter_DimSale_Date_1
And DimSale_Date_2 = @ReportParameter_DimSale_Date_2
End

– exécuter et valider le bon déroulement de la SP

Exec [dbo].[PowerQueryPassezParameter]
@ReportParameter_DimCustomerID =1,
@ReportParameter_DimSale_Date_1=20150801, –dates au format INT (ici 2015-08-01)
@ReportParameter_DimSale_Date_2=20151130 –dates au format INT (ici 2015-11-30)

-Lancer Excel et créer une feuille de calcul par laquelle le passage de paramètres sera effectif
Point d’attention sur le fait de bien nommer le tableau de paramétrage, dans notre cas il s’appelle « Parameter »

– configurer Power query pour qu’il fasse appel à cette SP, pour cela
allez dans PowerQuery From Database From SQL Server Database et spécifiez les paramètres de connexion à la BD puis l’appel de la SP

Faudra également spécifier les « Credentials » pour se connecter à la BD (Compte SQL /compte AD)
-une fois dans l’environnement PowerQuery  Edit Settings ViewAdvenced Editor
Cette fois dans le langage M que nous atterrissons, il n’est pas compliqué de comprendre ce langage mais des exercices sont néanmoins indispensable pour l’apprivoiser. La requête de base ressemble à cela

Pour modifier cette mécanique, nous commençons par récupérer la liste des paramètres de la feuille Excel pour les stocker dans des variables du langage M
Pour cela
let
Parameter = Excel.CurrentWorkbook(){[Name= »Parameter »]}[Content],
Parameter_Customer=Parameter[Value]{0},
ReportParameter_SETTLEMENT_DATE_1=Parameter[Value]{1},
ReportParameter_SETTLEMENT_DATE_2=Parameter[Value]{2},

Puis nous allons modifier l’appel de la procédure stockée à ces paramètres en convertissons la chaine en text  » & Number.ToText(Parameter_Customer) & »

Faudra faire le même exercice pour les deux autres paramètres SaleDate1 et SaleDate2
Pour finir la chaine de connexion ressemble à cela

Source = Sql.Database(«  », «  », [Query= »Exec [dbo].[PowerQueryPassezParameter] #(lf)@ReportParameter_DimCustomerID = » & Number.ToText(Parameter_Customer) & » ,#(lf)@ReportParameter_DimSale_Date_1=20150801,#(lf)@ReportParameter_DimSale_Date_2=20151130″])
in
Source

Le résultat final correspond à une saisie libre dans la feuille « Parameter » , rafraichir la requête Query1 qui sera pris en compte dynamiquement par la SP