Just a quick post to share some code that I used to solve a problem I had recently.
I needed to automate the deployment of some Power Bi reports to a Power Bi Report Server PBRS using TFS. I had some modified historical validation dbachecks pbix files that I wanted to automate the deployment of and enable the client to be able to quickly and simply deploy the reports as needed.
The manual way
It is always a good idea to understand how to do a task manually before automating it. To deploy to PBRS you need to use the Power Bi Desktop optimised for Power Bi Report Server. There are instructions here. Then it is easy to deploy to the PBRS by clicking file and save as and choosing Power Bi Report Server
If I then want to set the datasource to use a different set of credentials I navigate to the folder that holds the report in PBRS and click the hamburger menu and Manage
and I can alter the User Name and Password or the type of connection by clicking on DataSources
and change it to use the reporting user for example.
Automation
But I dont want to have to do this each time and there will be multiple pbix files, so I wanted to automate the solution. The end result was a VSTS or TFS release process so that I could simply drop the pbix into a git repository, commit my changes, sync them and have the system deploy them automatically.
As with all good ideas, I started with a google and found this post by Bill Anton which gave me a good start ( I could not get the connection string change to work in my test environment but this was not required so I didnt really examine why)
I wrote a function that I can use via TFS or VSTS by embedding it in a PowerShell script. The function requires the ReportingServicesTools module which you can get by
Install-Module -Name ReportingServicesTools
The function below is available via the PowerShell Gallery also and you can get it with
Install-Script -Name PublishPBIXFile
The source code is on Github
and the code to call it looks like this
$folderName = 'TestFolder'
$ReportServerURI = 'http://localhost/Reports'
$folderLocation = '/'
$pbixfile = 'C:\Temp\test.pbix'
$description = "Descriptions"
$publishPBIXFileSplat = @{
ReportServerURI = $ReportServerURI
folderLocation = $folderLocation
description = $description
pbixfile = $pbixfile
folderName = $folderName
AuthenticationType = 'Windows'
ConnectionUserName = $UserName1
Secret = $Password1
Verbose = $true
}
Publish-PBIXFile @publishPBIXFileSplat
which uploads the report to a folder which it will create if it does not exist. It will then upload pbix file, overwriting the existing one if it already exists
and uses the username and password specified
If I wanted to use a Domain reporting user instead I can do
$UserName1 = 'TheBeard\ReportingUser'
$publishPBIXFileSplat = @{
ReportServerURI = $ReportServerURI
folderLocation = $folderLocation
description = $description
pbixfile = $pbixfile
folderName = $folderName
AuthenticationType = 'Windows'
ConnectionUserName = $UserName1
Secret = $Password1
Verbose = $true
}
Publish-PBIXFile @publishPBIXFileSplat
$UserName1 = 'TheReportingUserOfBeard'
$publishPBIXFileSplat = @{
ReportServerURI = $ReportServerURI
folderLocation = $folderLocation
description = $description
pbixfile = $pbixfile
folderName = $folderName
AuthenticationType = 'SQL'
# credential = $cred
ConnectionUserName = $UserName1
Secret = $Password1
}
Publish-PBIXFile @publishPBIXFileSplat
[CmdletBinding()]
Param (
$PBIXFolder,
$ConnectionStringPassword
)
$VerbosePreference = 'continue'
$ReportServerURI = 'http://TheBeardsAmazingReports/Reports'
Write-Output "Starting Deployment"
function Publish-PBIXFile {
[CmdletBinding(DefaultParameterSetName = 'ByUserName', SupportsShouldProcess)]
Param(
[Parameter(Mandatory = $true)]
[string]$FolderName,
[Parameter(Mandatory = $true)]
[string]$ReportServerURI,
[Parameter(Mandatory = $true)]
[string]$FolderLocation,
[Parameter(Mandatory = $true)]
[string]$PBIXFile,
[Parameter()]
[string]$Description = "Description of Your report Should go here",
[Parameter()]
[ValidateSet('Windows', 'SQL')]
[string]$AuthenticationType,
[Parameter(ParameterSetName = 'ByUserName')]
[string]$ConnectionUserName,
[Parameter(ParameterSetName = 'ByUserName')]
[string]$Secret,
[Parameter(Mandatory = $true, ParameterSetName = 'ByCred')]
[pscredential]$Credential
)
$FolderPath = $FolderLocation + $FolderName
$PBIXName = $PBIXFile.Split('\')[-1].Replace('.pbix', '')
try {
Write-Verbose"Creating a session to the Report Server $ReportServerURI"
# establish session w/ Report Server
$session = New-RsRestSession-ReportPortalUri $ReportServerURI
Write-Verbose"Created a session to the Report Server $ReportServerURI"
}
catch {
Write-Warning"Failed to create a session to the report server $reportserveruri"
Return
}
# create folder (optional)
try {
if ($PSCmdlet.ShouldProcess("$ReportServerURI", "Creating a folder called $FolderName at $FolderLocation")) {
$Null = New-RsRestFolder-WebSession $session-RsFolder $FolderLocation-FolderName $FolderName-ErrorAction Stop
}
}
catch [System.Exception] {
If ($_.Exception.InnerException.Message -eq 'The remote server returned an error: (409) Conflict.') {
Write-Warning"The folder already exists - moving on"
}
}
catch {
Write-Warning"Failed to create a folder called $FolderName at $FolderLocation report server $ReportServerURI but not because it already exists"
Return
}
try {
if ($PSCmdlet.ShouldProcess("$ReportServerURI", "Uploading the pbix from $PBIXFile to the report server ")) {
# upload copy of PBIX to new folder
Write-RsRestCatalogItem-WebSession $session-Path $PBIXFile-RsFolder $folderPath-Description $Description-Overwrite
}
}
catch {
Write-Warning"Failed to upload the file $PBIXFile to report server $ReportServerURI"
Return
}
try {
Write-Verbose"Getting the datasources from the pbix file for updating"
# get data source object
$datasources = Get-RsRestItemDataSource-WebSession $session-RsItem "$FolderPath/$PBIXName"
Write-Verbose"Got the datasources for updating"
}
catch {
Write-Warning"Failed to get the datasources"
Return
}
try {
Write-Verbose"Updating Datasource"
foreach ($dataSourcein$datasources) {
if ($AuthenticationType -eq 'SQL') {
$dataSource.DataModelDataSource.AuthType = 'UsernamePassword'
}
else {
$dataSource.DataModelDataSource.AuthType = 'Windows'
}
if ($Credential -or $UserName) {
if ($Credential) {
$UserName = $Credential.UserName
$Password = $Credential.GetNetworkCredential().Password
}
else {
$UserName = $ConnectionUserName
$Password = $Secret
}
$dataSource.CredentialRetrieval = 'Store'
$dataSource.DataModelDataSource.Username = $UserName
$dataSource.DataModelDataSource.Secret = $Password
}
if ($PSCmdlet.ShouldProcess("$ReportServerURI", "Updating the data source for the report $PBIXName")) {
# update data source object on server
Set-RsRestItemDataSource-WebSession $session-RsItem "$folderPath/$PBIXName"-RsItemType PowerBIReport -DataSources $datasource
}
}
}
catch {
Write-Warning"Failed to set the datasource"
Return
}
Write-Verbose"Completed Successfully"
}
foreach ($File in (Get-ChildItem $PBIXFolder\*.pbix)) {
Write-Output"Processing $($File.FullName)"
## to enable further filtering later
if ($File.FullName -like '*') {
$folderName = 'ThePlaceForReports'
$folderLocation = '/'
$UserName = 'TheBeard\ReportingUser'
$Password = $ConnectionStringPassword
$pbixfile = $File.FullName
}
if ($File.FullName -like '*dbachecks*') {
$description = "This is the morning daily checks file that....... more info"
}
if ($File.FullName -like '*TheOtherReport*') {
$description = "This is hte other report, it reports others"
}
$publishPBIXFileSplat = @{
ReportServerURI = $ReportServerURI
folderLocation = $folderLocation
description = $description
AuthenticationType = 'Windows'
pbixfile = $pbixfile
folderName = $folderName
ConnectionUserName = $UserName
Secret = $Password
Verbose = $true
}
$Results = Publish-PBIXFile@publishPBIXFileSplat
Write-Output$Results
}
Pingback: Deploying To Power BI Report Server Using Powershell – Curated SQL
Okay seems figure out that Install-Script is not available for the version i am on (Ver 5.0).
Not sure but for some reason my PS doesn’t recognized the install-script command. May be console issue or something i am missing. I did managed to have the function inline for now to get this moving and it all works as expected. We have the folder connection used in the Report, so have to figure out if that is possible. But not too worried as Business have gone wild with their First report by integrating data from like 10 different excel file and data only gets refresh every month. So for now will upload as a static file and then think about integrating that in to SSAS Tabular and automate the refresh process.
Need a tweak on FolderPath, as if its not deployed at the root folder it fails to upload the report as it expect the backlash
Also i baked in $Environment parameter instead of $ReportServerURI, so user only have to pass DEV, QA or PROD to deploy the report and URL is assign based on the parameter.
I still haven’t integrated with TFS PS Project, but next in my list.
——————————————————————————————————————
Folder Path Tweak (I am not PS expert, so not sure if this is coded in the best possible way!)
if($folderLocation.length -gt 1){
$FolderPath = $FolderLocation + ‘/’ + $FolderName
}
else {
$FolderPath = $FolderLocation + $FolderName
}
$PBIXName = $PBIXFile.Split(‘\’)[-1].Replace(‘.pbix’, ”)
——————————————————————————————————————
Environment Variable
$Environment =’DEV’
Switch ( $Environment )
{
‘DEV’ {$ReportServerURI = ‘http://pbi-dev/reports’}
‘TEST’ {$ReportServerURI = ‘http://pbi-test/reports’}
‘PROD’ {$ReportServerURI = ‘http://pbi-prod/reports’}
}
——————————————————————————————————————
Console Error Log for Install-Script
PS C:\Users\thakks> Install-Module -Name ReportingServicesTools
PS C:\Users\thakks> Install-Script -Name PublishPBIXFile
Install-Script : The term ‘Install-Script’ is not recognized as the name of a cmdlet, function, script file, or
operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try
again.
At line:1 char:1
+ Install-Script -Name PublishPBIXFile
+ ~~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (Install-Script:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException
Thank you for the feedback, I think you could use $folderlocation = ‘/‘ and $foldername = folder to get round that. It is designed so it can be used to deploy to multiple folders
It need the backslash at the end to upload the report. So if i need to upload the report at the following location /Enterprise Solution/TestFolder.
I pass $folderlocation=/Enterprise Solution and $FolderName =TestFolder. It doesn’t upload as $Folderpath is invalid in that case. May be i am missing something.
Ah, I see. You can pass ‘TestFolder/‘ to foldername but I will code a check and replace the script tomorrow. Thank you
It wont create the new folder if not present, if we pass TestFolder/
Very clear and detailed post, thanks!
I’d like to ask you a question about a related issue: I am trying to migrate a tree of 150+ Excel reports from Sharepoint (I can easily download all the files and structure to a local filesystem, if it’s easier) to PBIRS. I tried several scripts and/or commands, but I can’t figure out how to get the uploaded files listed under “Excel Workbooks” (as I get if I upload a file manually) instead of a generic “Resources”.
Do you know of any “PublishExcelFiles” script?
Hmm, I am afraid that I do not 🙁
Hi,
I’m using this one:
$dataSources = Get-RsRestItemDataSource -ReportPortalUri http://localhost/Reports -RsItem $RsItem
$dataSources[0].DataModelDataSource.AuthType = ‘Windows’
$dataSources[0].DataModelDataSource.Username = ‘Domain/user’
$dataSources[0].DataModelDataSource.Secret = $Finalpassword
Set-RsRestItemDataSource -ReportPortalUri http://localhost/Reports -RsItem $RsItem -RsItemType PowerBIReport -DataSources $datasources -verbose
to set up data source.
$Finalpassword = Get-content C:\reports\password.txt – here store password
When I run this script i get this error:
VERBOSE: Performing the operation “Update data sources” on target “/Dev_Test_folder/test1”.
VERBOSE: Updating data sources for /Dev_Test_folder/test1…
Failed to update data sources for ‘/Dev_Test_folder/test1’: The remote server returned an error: (400) Bad Request.
At C:\Program Files\WindowsPowerShell\Modules\ReportingServicesTools\0.0.4.8\Functions\CatalogItems\Rest\Set-RsRestItemDataSource.ps1:249 char:13
+ throw (New-Object System.Exception(“Failed to update data …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OperationStopped: (:) [], Exception
+ FullyQualifiedErrorId : Failed to update data sources for ‘/Dev_Test_folder/test1’: The remote server returned an error: (400) Bad Request.
when I replace $Finalpassword with ‘test’ then it works fine.
$dataSources[0].DataModelDataSource.Secret = ‘test’
How to pass parameter as secret?
I would not store the passwords in a text file. Take a look at https://www.jaapbrasser.com/quickly-and-securely-storing-your-credentials-powershell/
In your example script you have an error. You made the call to the function as ‘Publish-PBIXFile’ when it should be ‘PublishPBIXFile’, without the hyphen.
Hi Adly, Thank you for your comment.
The function name is Publish-PBIXFile but the name of the script file in the PowerShell gallery is PublishPBIXFile
Hi there, thank you for sharing this! I was wondering if you have an experience trying to runt his powershell script via any sort of CI, especially runner in Gitlab? I seemed to have hassles trying to get it work. Im super new to powershell as well.
thank you for your advice 🙂
Ped
Yes, I use this via TFS to deploy to PowerBi Report Server
hey there, I’m getting from my output WARNING: Failed to set the datasource on my pbix files, is there anyway I can dive deeper into finding out why? the file uploads but the comments only updates when I run it again and the username and password won’t update or be retained when uploaded to on premise server.
We are running an on premise version of Power Bi and on premise version of VSTS 2018. Have you had any luck being able to change the connection string through a swim lane variable? If you have could you share how you are doing it?
Hi thank you so much for sharing! Do you have any idea why it is not possible to change the connection string? I am trying to just change the database name, but to no avail… I saw in your github code that you commented that section out… Do you have any idea how I could workaround it? Or if Microsoft plans doing something about it?
Hi, Thanks a lot for sharing. Looks it will solve my issue. But I couldn’t get it properly. I need to know how / which environment should I run this code to call the code on GitHub. I was using this code wtih my values and saved in power shell file to run from ps. but it does nothing. Further can I upload on remote server rather than local (which is the requirement)? Please Help me getting this run to test. Thanks
HI Sonia, You need to load the function into the session using hte code on github and then you can call it using the
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
$folderName = ‘TestFolder’
$ReportServerURI = ‘http://localhost/Reports’
$folderLocation = ‘/’
$pbixfile = ‘C:\Temp\test.pbix’
$description = “Descriptions”
$publishPBIXFileSplat = @{
ReportServerURI = $ReportServerURI
folderLocation = $folderLocation
description = $description
pbixfile = $pbixfile
folderName = $folderName
AuthenticationType = ‘Windows’
ConnectionUserName = $UserName1
Secret = $Password1
Verbose = $true
}
Publish-PBIXFile @sqldbawithabeard
It will work remotely as well as locally
Hi Rob,
This looks exactly what I’ve been looking, thank you for sharing it. Is it possible to change the connection string of the pbix file as well this way?
Thanks again.
Hi. I’m using this script to publish my report to report server. The report gets published but the credentials are empty and I am required to enter credentials on the report server portal under security to make the report work. Is there any setting i need to do? I’m assigning username and password to variables in the script and using them in the $publishPBIXFileSplat function. Can you help?
Thanks for sharing this Andy. Do you know how we can get this done if one has over 30 PBX reports to deploy in one go?
hi, when i run my code, it says:
Failed to create a session to the report server
i think this is because of authentication and i use windows authentication. how can i pass my windows credentials? a hard coding would also be ok as of now.
does it support report server which are of https type?