Change SSAS server name in data source of a pbix report programmatically

[reggdsouza]

Hi All, 

Using this solution, I was able to upload a pbix report on a reporting server without issues. I then tried updating the data source information by doing the following:

var dsInfo = rs.GetItemDataSources(report.Output + "/" + report.Name); //gets the data source of the report
var dsReference = (DataSourceDefinition) dsInfo[0].Item; //references the data source
dsReference.ConnectString = newConnectionString; //updates the connection string of the reference
dsReference.CredentialRetrieval = CredentialRetrievalEnum.Store; //sets credential retrieval value to store
dsReference.WindowsCredentials = true; //sets windowsCredential property to true
dsReference.UserName = username; //sets the user name through the report will be accessed
dsReference.Password = password; //sets the password through which the report will be accessed
DataSource df = new DataSource(); //creates a new datasource
df.Item = dsReference; //assigns the reference datasource to the new datasource
df.Name = dsInfo[0].Name; //assigns the reference datasource name to the new datasource name
rs.SetItemDataSources(report.Output + "/" + report.Name, new DataSource[] {df}); //replaces the datasource for the report
Console.WriteLine($"Report {report.Name} datasource updated...");

The above code works well when we access the report through the repoorting portal. However, when I open the report using the "Edit in Power BI Desktop", it throws an error saying the server name is incorrect. On clicking edit, it shows me a screen where I can update the SSAS server name. Once the server name is updated, the report works as expected.

What should I do to update the server name value in the pbix file as well? It gets updated with the above code on the web portal(Report Works), but the server name remains unchanged on using Power BI Desktop.

[v-yulgu-msft]

Hi @reggdsouza,

Since PBIX file is not a straight forward xml file like SSRS rdl report, it is not possible to update the data source in pbix file programmatically.

Best regards,
Yuliana Gu

[reggdsouza]

Thanks for the reply @v-yulgu-msft. Appreciate it.

Do you have any recommendation for uploading reports from and to different environments.   E.g. dev to test to prod?  We have few pbix reports which is updated every week in the dev environment and need to be uploaded to the production environment. Production environment have different custom reports too that do not exist in test. So we cannot replace the whole reporting database.

We would like to find a way to automate this process of moving reports from one environment to another. Any suggestions highly appreciated. Thanks!!

[reggdsouza]

Any ideas or suggestions on this one yet?

[xpherion]

Please vote for this idea.

[v-yulgu-msft]

Hi @reggdsouza,

Per my knowledge, it is not available to set/update data source in pbix file programmatically.

I will consult with someone else more familiar with this, once I get any response, I will give you an update. 

Regards,
Yuliana Gu

[reggdsouza]

Any insights appreciated!!

[reggdsouza]

Hi All, 

Using this solution, I was able to upload a pbix report on a reporting server without issues. I then tried updating the data source information by doing the following:

var dsInfo = rs.GetItemDataSources(report.Output + "/" + report.Name); //gets the data source of the report
var dsReference = (DataSourceDefinition) dsInfo[0].Item; //references the data source
dsReference.ConnectString = newConnectionString; //updates the connection string of the reference
dsReference.CredentialRetrieval = CredentialRetrievalEnum.Store; //sets credential retrieval value to store
dsReference.WindowsCredentials = true; //sets windowsCredential property to true
dsReference.UserName = username; //sets the user name through the report will be accessed
dsReference.Password = password; //sets the password through which the report will be accessed
DataSource df = new DataSource(); //creates a new datasource
df.Item = dsReference; //assigns the reference datasource to the new datasource
df.Name = dsInfo[0].Name; //assigns the reference datasource name to the new datasource name
rs.SetItemDataSources(report.Output + "/" + report.Name, new DataSource[] {df}); //replaces the datasource for the report
Console.WriteLine($"Report {report.Name} datasource updated...");

The above code works well when we access the report through the repoorting portal. However, when I open the report using the "Edit in Power BI Desktop", it throws an error saying the server name is incorrect. On clicking edit, it shows me a screen where I can update the SSAS server name. Once the server name is updated, the report works as expected.

What should I do to update the server name value in the pbix file as well? It gets updated with the above code on the web portal(Report Works), but the server name remains unchanged on using Power BI Desktop.