Lorsque vous effectuez une requête sur une source de données dans Power Query (ou dans Power BI Desktop), il est possible que la source change d’endroit dans le futur. Par exemple, si la source est un fichier Excel, celui-ci peut facilement être déplacé de dossier, pour des raisons de réorganisation, ou, si la source est une base de données, il est possible que vous pointiez d’abord vers un serveur en « dev » et que vous souhaitiez ensuite faire pointer vos requêtes vers un serveur en « prod ». Dans tous les cas, il existe plusieurs raisons et plusieurs façons d’ajouter de la flexibilité à vos requêtes dans Power Query, afin de les faire facilement pointer vers une nouvelle source, au besoin. Cet article vise à présenter 3 de ces approches.
Approche no.1
La première approche que j’aimerais vous présenter est expliquée en détails dans un article de Chris Webb, Avoiding Duplication Of Database Connection Information In Power BI. En voici un résumé.
D’abord, dans l’exemple ci-bas, on remarque que lorsqu’on se connecte à un serveur SQL, on peut choisir plusieurs tables (en cochant les cases qui apparaissent à gauche de l’écran, ci-bas).
En cochant ces 5 cases, on se trouve à créer 5 requêtes distinctes, qu’on peut apercevoir dans la section ci-dessous, à gauche.
Quand on ouvre la requête DimDate (via le Advanced Editor), on s’aperçoit qu’il y a d’abord une connexion qui se crée sur le serveur SQL Sophie et, plus précisément, sur la base de données AdventureWorksDW2014 et, finalement, sur la table DimDate.
Le problème, c’est qu’en ouvrant les autres requêtes, on s’aperçoit que la connexion au serveur, et à la base de données plus particulièrement, n’est pas partagée. Elle est plutôt reproduite. Donc, dans notre petit exemple, nous avons 5 connexions à 5 tables différentes, ce qui siginifie que nous dupliquons 5 fois la connexion à la base de données.
Pour contourner ce problème, Chris Webb propose d’abord de créer une requête vierge (from blank) qui ne comprend que le nom du serveur, et de nommer cette requête SQLServerInstanceName.
Il recommande ensuite de faire la même chose avec la base de données.
Finalement, il recommande de modifier chacune des 5 requêtes afin qu’elles s’alimentent désormais à partir de la connexion SQLServerInstanceName et SQLServerDatabaseName, tel qu’illustré ci-dessous.
Si la source devait changer, il faudrait simplement changer les paramètres des requêtes SQLServerInstanceName et SQLServerDatabaseName.
Approche no.2
Une deuxième approche, inspirée de la première, serait de créer une requête qui effectue réellement la connexion à la source de données, et de nommer cette requête SQLServerDatabaseConnection.
Ensuite, pour chacune des 5 autres requêtes, il faudrait faire une référence à cette première requête (faire un clic droit de souris sur la requête SQLServerDatabaseConnection et cliquer Reference ou, tout simplement, partir d’une requête vide et inscrire =SQLServerDatabaseConnexion dans la barre de formule).
Ce faisant, si nous souhaitons procéder avec la balance de la requête pour extirper la dimension Customer, nous n’avons qu’à effectuer un filtre pour sélectionner DimCustomer dans la liste des noms de tables, tel qu’illustré ci-dessous.
Ensuite, nous pouvons utiliser le menu d’expansion pour choisir seulement les colonnes qui nous intéressent pour notre modèle, le cas échéant.
Finalement, notre requête DimCustomerV2 est plus légère que la première (puisque nous n’avons retenu que les colonnes nécessaires à notre analyse). De plus, elle se réfère à la source SQLServerDatabaseConnection, ce qui nous permet de ne pas dédoubler les informations de connexion dans les autres requêtes et ce qui permet également de changer la source aisément, au besoin.
Approche no.3
Une troisième approche pourrait être utilisée dans Power Query pour Excel (mais pas dans Power Bi Desktop). Il s’agirait d’insérer les paramètres de connexion dans Excel et de les utiliser pour passer des paramètres dans Power Query.
Les étapes sont les suivantes:
- Créer une table de paramètres dans Excel
- Modifier les requêtes afin qu’elles fassent référence au tableau de paramètres
Il s’agit d’abord de créer une table avec les informations de connexion, la mettre sous forme de tableau et lui donner un nom, Parameters, dans l’exemple ci-bas.
Ensuite, dans chacune de vos 5 requêtes, vous devez ajouter les 3 lignes suivantes de code:
- Parameters = Indique que les paramètres se trouvent dans le tableau Parameters de votre fichier Excel.
- DBServer = Indique que l’information pour DBServer se trouve dans le tableau dans la colonne de valeur correspondant au libellé DB server
- DB = Indique que l’information pour DB se trouve dans le tableau dans la colonne de valeur correspondant au libellé DB
Finalement, il s’agit de modifier les lignes existantes de code pour que Sophie réfère à DBServer et AdventureWorksDW2014 à DB, tel qu’illustré ci-dessous.
De cette façon, nous évitons de dupliquer les informations de connexion et celles-ci sont plus faciles à modifier, au besoin. Vous aurez également compris que cette technique de tableau de paramètres peut être utilisée à différentes sauces. Nous en présentons justement une autre version dans l’article suivant: Passer des paramètres dans Power Query via Excel.
NOTRE OFFRE DE FORMATIONS
Le CFO masqué vous offre 13 formations, réparties dans 4 catégories: Tableaux de bord, Modélisation financière, Finance corporative et Analyse et modélisation de données. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.
Consulter la liste des formations offertes
Pour info: 514-605-7112 ou [email protected]
Jeu de briques
Snake
Pacman
Bubble