Unable to change Power BI connection string using API

Question

I'm trying to change Power BI connection string using their API (Microsoft.IdentityModel.Clients.ActiveDirectory). Using this API, I'm able to publish .pbix file to my PBI account. But Getting Bad Request error while trying to update dataset connection string. Here is my code.

var client = new HttpClient();
client.DefaultRequestHeaders.Add("Accept", "application/json");
client.DefaultRequestHeaders.Add("Authorization", "Bearer " + accessToken);

var restUrlImportPbix = POWER_BI_SERVICE_ROOT_URL + $"datasets/{dataset.id}/Default.SetAllConnections";

var postData = new { connectionString = _powerBISettings.DataConnectionString };
var response = client.PostAsync(restUrlImportPbix, new StringContent(JsonConvert.SerializeObject(postData), Encoding.UTF8, "application/json")).Result;

Also I found in a blog that SetAllConnections only works on direct query connections. Anybody help please.

Answer 1

In addition to trying to redirect the datasource by changing the connection string, you can achieve the same by allowing the report itself to switch it's data source. To do this, use connection specific parameters in the report. To do this, open Power Query Editor by clicking Edit Queries and in Manage Parameters define two new text parameters, lets name them ServerName and DatabaseName:

Set their current values to point to one of your data sources, e.g. SQLSERVER2016 and 'AdventureWorks2016. Then right click your query in the report and openAdvanced Editor`. Find the server name and database name in the M code:

and replace them with the parameters defined above, so the M code will look like this:

Now you can close and apply changes and your report should work as before. But now when you want to change the data source, do it using Edit Parameters:

and change the server and/or database name to point to the other data source, that you want to use for your report:

After changing parameter values, Power BI Desktop will ask you to apply the changes and reload the data from the new data source. To change the parameter values (i.e. the data source) of a report published in Power BI Service, go to dataset's settings and enter new server and/or database name:

If the server is on-premise, check the Gateway connection too, to make sure that it is configured properly to use the right gateway. You may also want to check the available gateways in Manage gateways:

After changing the data source, refresh your dataset to get the data from the new data source. With Power BI Pro account you can do this 8 times per 24 hours, while if the dataset is in a dedicated capacity, this limit is raised to 48 times per 24 hours.

To do this programatically, use Update Parameters / Update Parameters In Group and Refresh Dataset / Refresh Dataset In Group REST API calls, or if I modify your code, something like this:

var client = new HttpClient();
client.DefaultRequestHeaders.Add("Accept", "application/json");
client.DefaultRequestHeaders.Add("Authorization", "Bearer " + accessToken);

var restUrlUpdateParameters = POWER_BI_SERVICE_ROOT_URL + $"datasets/{dataset.id}/Default.UpdateParameters";
var postData = new { updateDetails = new[] { new { name = "ServerName", newValue = "NEWSERVER" }, new { name = "DatabaseName", newValue = "Another_AdventureWorks2016" } } };
var responseUpdate = client.PostAsync(restUrlUpdateParameters, new StringContent(JsonConvert.SerializeObject(postData), Encoding.UTF8, "application/json")).Result;

var restUrlRefreshDataset = POWER_BI_SERVICE_ROOT_URL + $"datasets/{dataset.id}/refreshes";
var responseRefresh = client.PostAsync(restUrlRefreshDataset, null).Result;

This is a easy way to make your reports "switchable", e.g. for switching one report from DEV or QA to PROD environment, or as part of your disaster recovery plan, to automate switching all reports in some workgroup to another DR server. In general I recommend to define such connection specific parameters in all reports.