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.