Using the Power BI Service Parameters to change connection strings (To possibly change between Dev,

by Super User on ‎04-09-2018 10:37 PM

As I am sure a lot of people noticed if you now go into the settings of your dataset there is an additional option which says Parameters.

 

What I wanted to test out was to see if I could change the connection strings using the Parameters in the Power BI Service. The advantages of this working in my opinion is the following:

  • I would be able to change the underlying data source connection without having to redeploy my Power BI desktop file.
    • This would work particularly well if I am deploying reports from Dev (Development environment) to Test (Testing environment) to Prod (Production environment)
  • If I was looking for disaster recovery options, this too could work. I would need to ensure that I have an On-Premise Gateway configured for my Disaster recovery servers, and once again I could change the connection strings.

One thing to take note of, is that this currently only works on Imported data sources and not DirectQuery or LiveConnection

 

Data Preparation

What I did in order to test the changing of the connection strings is I created 2 Sample Azure SQL databases. Not only was this very quick to complete, it was also extremely cheap with it costing $6.99 AUD per month for me test my solution. As part of the installation I installed the Adventure Works databases

Then in order to ensure that I was looking at the different servers, I updated the Products table so that for the Colour Black it was changed to "Black-Svr1_DW" and "Black_Svr2_DW"

 

Creating the Power BI Report

I created my Power BI Report, where I first connected to the Azure SQL Database and imported the SalesLT.Products table from Server1_DW

NOTE: As you can see above my colour has the name of "Black-Svr1_DW" I then created a second table, which runs the TSQL command @@ServerName, this is to ensure that not only could I get back the relevant server name, but that I am indeed not masking the results for this blog post (Honesty is the BEST policy )

Whilst in the Power Query Editor I then created 2 Parameters, one for the Server Name and another for the Database Name

  • I then put in the default values, in order to connect to the correct Azure SQL Database.
  • Next, I went to my initial Query and clicked on the Source Gear Icon
  • Next in the SQL Server Database Window I clicked on the drop down next to server and selected Parameter
    • I selected Server Name
  • And likewise, I did the same for Database and selected Database Name
  • So that once complete I had both parameters selected.
  • I then clicked Ok, and my data loaded.
    • I did the above for my Server Details query also.
  • I then imported my data into my Power BI Desktop file.
  • My final step was to load the data and create a report, in which I could see the Server Details from the @@Servername output, as well as the SalesLT.Product table details as shown below.
  • I then uploaded my PBIX file to the Power BI Service

Modifying the Parameters in the Power BI Service

I now wanted to modify the parameters in the Power BI Service and see if they would work as expected.

  • Now that I had my report uploaded to the Power BI Service, I went into Settings and then clicked on Datasets.
  • I then clicked on the arrow next to Parameters and modified the parameters so that it would use Server2_DW
    • And then clicked Apply
  • I was then prompted to update my credentials to the new database
    • I clicked on Edit credentials and put in valid credentials for Server2_DW
  • I got confirmation that my credentials had updated successfully.

Testing the updated parameters

The final step was to refresh the data and see if it now was getting data from Server2_DW

  • I went to the dataset, clicked on the breadcrumbs and selected "Refresh Now"
  • Because I am using Azure SQL Database, the refresh happens really quickly.
  • I was very excited to see that it now is indeed getting the data from Server2_DW

Conclusion

As I have demonstrated there now is the flexibility within the Power BI Service to leverage the parameters in order to quickly and easily change data source connections to underlying datasets. If there are any questions or comments please leave them in the section below.

Comments
by lloydedu01 New Member
on ‎04-11-2018 09:41 PM

nice

by danextian New Contributor
‎04-18-2018 06:31 AM - edited ‎04-18-2018 06:31 AM

I noticed this yesterday but haven't tried since the on-premise data source has not been configured be updated via scheduled refresh. 

by Super User
on ‎04-18-2018 04:01 PM

Hi @danextian

 

When you do configure the on-premise data source it should then refresh based on the parameters

by awaisafridi55 Frequent Visitor
on ‎04-29-2018 03:58 PM

Hi When you do configure the on-premise data source it should then refresh based on the parameters 

http://www.powerbitraining.com.au/power-bi-basic/

by Super User
on ‎04-29-2018 04:11 PM

Hi @awaisafridi55

 

It will refresh once the Gateway has been connected

by davidbrown100 Frequent Visitor
on ‎05-02-2018 04:32 AM

This is great functionality, however not all data sources have the parameters option.

For example we have connections into Redshift and therefore have no means of changing the connection string.

by Super User
on ‎05-02-2018 02:20 PM

Hi @davidbrown100 this can be possibility done by creating yyour parameter and then putting that into the advanced query editor.

 

I'm not exactly sure what is available via the advanced query editor when using RedShift