Forum Discussion

Jon-Heide's avatar
Jon-Heide
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Unfortunately this is not currently supported. Changes to data source connection strings must be made the PBI report and then uploaded. They can't be changed after saving to the server. 

  • elitekrishnan's avatar
    elitekrishnan
    Frequent Visitor
    8 years ago

    Hi Jon,

     

    Thanks for your reply. 

     

    In this case, for a single report, we need to keep duplicate files with each environment connection string. This will be hard to manage in development and deployment.

    We are having more than 100 Power BI Reports and these needs to deploy in multiple environments, it will be more work to change connection property and upload in each nvironment.

     

    Is there any alternate way to deploy the Power BI reports to multiple environment with different connection string/ data source?

    or any option avaialble to update the connection string property in PBIX file through coding?

     

     

    Thanks,

    Navaneethakrishnan Thangaraj

    • Plantje's avatar
      Plantje
      Icon for Helper I rankHelper 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