Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Join us at the 2025 Microsoft Fabric Community Conference. March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for $400 discount. Register now

GilbertQ
Super User
‎04-09-2018 10:37 PM

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

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
‎04-11-2018 09:41 PM

nice

0
Super User
‎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. 

0
Super User
‎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

0
‎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/

0
Super User
‎04-29-2018 04:11 PM

Hi @awaisafridi55

 

It will refresh once the Gateway has been connected

0
Anonymous
‎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.

0
Super User
‎05-02-2018 02:20 PM

Hi @Anonymous 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

0
Helper III
‎06-14-2018 09:17 AM

@GilbertQ,

Can this be done with a DataSource/Gateay via DirectQuery option in the Power BI services.

Scenario I have a single gateway with multiple DataSources both DataSources are set to use DirectQuery.  Report built on one datasource refresh successfully. The other reports fails with Mashup error. Any ideals on how I can specific or indicate which DataSource the report should use. 

 

Thank you,

Conarl_On_BI

0
Super User
‎06-14-2018 05:52 PM

Hi @Conarl_on_BI

 

I would possibly need more details, but I think you possibly would have to make the changes in the Power BI Desktop file. Ensure that it is using the new data source, so that when it is uploaded it connects to that particular dataset.

0
Anonymous
‎11-12-2018 09:58 PM

Hi @GilbertQ,

 

I have set of reports in my service, I imported it from a Single data source. But i think i didnt set any parmeters while importing the datasets for those reports in my service, is it still possible ? for me to Change the parameter n power bi service itself and i can Change the dat source Server 's new ip by replacing the existing one.

Please do the needful ,Fortunately my link search leaded to your post again. i Have been Constantly asking help from you, now also .

 

Thanks ,

Valliappan. 

0
Super User
‎11-12-2018 11:09 PM

Hi there You will first need to create and configure the parameter in Power BI Desktop.

Then upload this to the Power BI Service, after which you will get the option to update the parameter in the Power BI Service.

0
Post Patron
‎11-13-2018 09:00 AM

@GilbertQ Hi I tried the same way how you did with SQL Server as source but still not working.

 

 

Please help me??

0
Super User
‎11-13-2018 01:34 PM

Hi there

 

Can you please explain what you did and where it is going wrong?

 

Make sure that your Parameter is set to data type of Text.

 

If it is set to Any it will not show as a Parameter in the Power BI Service.

0
Post Patron
‎11-13-2018 01:38 PM

@GilbertQ  Yes My parameter is of Type Text i did it same as you did but still it is not working.

 

I

0
‎01-28-2019 12:15 PM

Can we implement this in direct query?

0