dbi Blog

Executive Summary

In this post, I want to demonstrate how to programmatically change the data source of a Power BI report. This can be useful when you develop and test the report then want to publish the report in Test, QA and Production using different data sources.

Prerequisite

Install PowerShell library ReportingServicesTools from the git

Step 1: The Power BI report

It is important to define the data source in your original Power BI Report as a parameter. The step for that can be read under Link. Find here some screenshots I took during my test.

Screenshot 3: Edit Data Source Parameters

Step 2: change the Data Source

After designing the report, you can deploy it and you will be able to change the data source with the script in listing 1. It is important to notice that, I use the report url and not the web services url.

#--Listing 1

#.pbix

## start the PowerShell in the context of a user who have the necessary permission on the report server
##
##https://learn.microsoft.com/de-de/power-bi/report-server/connect-data-source-apis
##https://learn.microsoft.com/de-de/power-bi/report-server/connect-data-source-apis-pre-oct-2020

## Install the Module
#Install-Module -Name ReportingServicesTools

## Check
#Get-Module -ListAvailable -Name ReportingServicesTools

## Import the Module
Import-Module ReportingServicesTools


$reportPortalUri = "http://nts_reportserver/reports"
$ntsContent = Get-RsRestFolderContent '/ntsfolder'  -ReportPortalUri $reportPortalUri  | Where-Object {$_.Name -eq 'nts_report'}

$ntsRsRestItemDataModelParameters = Get-RsRestItemDataModelParameters -RsItem 
 '/ntsfolder/nts_report' -ReportPortalUri $reportPortalUri  

$parameterdictionary = @{}
foreach ($parameter in $ntsRsRestItemDataModelParameters) { $parameterdictionary.Add($parameter.Name, $parameter); }

### Set the Parameter
$parameterdictionary[“ServerName”].Value = 'NTS_DATABASESERVER\INST01'
$parameterdictionary[“Databasename”].Value = 'NTS_TestDB'

### Update the report
Set-RsRestItemDataModelParameters -RsItem '/ntsfolder/nts_report' -DataModelParameters $ntsRsRestItemDataModelParameters

Conclusion

The post uses the PowerShell library ReportingServicesTools to change the data source of a Power BI Report. The data source must be previously define in the .pbix-file as parameter. Of course, this library has more features that you can use to remotely manage your Power BI Reports. Note the use of the command parameter “-RsItem” and the “reportserver url” in the script.

#beSharing

Post Views: 3,036