Forum Discussion

Plantje's avatar
Plantje
Helper I
8 years ago

I can now deploy the reports. But, since I have been searching this long to get that working I forgot that I read here that it is impossible to change the data source afterwards :( Why is that read only?

elitekrishnan's avatar
elitekrishnan
Frequent Visitor
8 years ago

 

Hi,

As Jon-Heide mention, at the moment PowerBI is not supporting it.

 

For this issue, I done some workaround as below.

  • In ReportServer database, "DataModelDataSource" table has connection string details of each datasource in PowerBI Report (with ItemId refeence) but this is in varbinary/encrypted format.
  • For Example if you are moving the report from Dev. environment to Prod. Environment
  • Create dummy report with Prod env as datasource details and upload that report in development environment, then you can get connection string (in varbinary) from DataModelDataSource table, keep this string as your new datasource connection string for prod environment.
  • After publishing the reports in prod environment, for this particular itemid and datasource id, repalce the new connection string.
  • You can get the Item ID and Datasource ID "Get-RsRestItemDataSource" method.
  • You need to maintain the Prod. env encrypted connection string (combination of server name and database name) and need to pass to PowerShell script while publishing the report.
  • Using belowquery, you can update the new connection string (Prod. env) for each report. 
$Query = "Declare @varCharConnectionString varchar(max); 
		SELECT @varCharConnectionString = '" + $EncryptedConnectionString + "'; 
		Update DataModelDataSource set ConnectionString = convert(varbinary(max),@varCharConnectionString,1) 
		Where ItemId = '" + $PowerBIReportItem.Id + "' and DataSourceID ='" + $PowerBIReportDataSource.Id + "'"
Invoke-Sqlcmd -Query $Query -ServerInstance $ReportServerName -Database $ReportServerDatabaseName -Username $ReportServerDatabaseUserName -Password $ReportServerDatabasePassword

 

I hope this will help you..

 

Thanks,

Navaneethakrishnan Thangaraj

 

 

 

  • Plantje's avatar
    Plantje
    Helper I
    8 years ago

    Cool! Thanks! That is most likely something I can use in the coming sprints when creating a deployment tool

    • Plantje's avatar
      Plantje
      Helper I
      8 years ago

      I have found another work around.

      If you edit the C:\Windows\System32\drivers\etc\hosts\file file (correct, no extension)

      And add something like this:

      [IP of your SQL Server DB] SQLServer

       

      Then you can do that on all your application servers and in the report you can use "SQLServer" as server name. Then you can deploy the reports without editting them.

      • elitekrishnan's avatar
        elitekrishnan
        Frequent Visitor
        8 years ago

        Thanks, 

        In my case, need to change both database and server name in all environments.

         

  • craiha's avatar
    craiha
    Regular Visitor
    7 years ago

    I'm curios if anyou really managed to get this working.

     

    We have implemented the connectionstring update in the ReportServer database and all looks well in the Power BI Report Server. I.e. on the DataSources page of the report. However when you create a Scheduled refresh it doesn't use the new value from the database, instead it uses the value in the pbix file :(