Deploying To a Power Bi Report Server with PowerShell

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

manual deploy

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

manage

and I can alter the User Name and Password or the type of connection by clicking on DataSources

testconn.PNG

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

The function below is available via the PowerShell Gallery also and you can get it with

The source code is on Github

and the code to call it looks like this

code1.PNG

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

numbe3r1.PNG

and uses the username and password specified

code2.PNG

If I wanted to use a Domain reporting user instead I can do

and it changes
code4 reporting
If we want to use a SQL Authenticated user then
sql auth.PNG
Excellent, it all works form the command line. You can pass in a credential object as well as username and password. The reason I enabled username and password? So that I can use TFS or VSTS and store my password as a secret variable.
Now I simply create a repository which has my pbix files and a PowerShell script and build a quick release process to deploy them whenever there is a change πŸ™‚
The deploy script looks like
Although the function does not need to be embedded in the script and can be deployed in a module, I have included it in here to make it easier for people to use quickly. I
Then create a PowerShell step in VSTS or TFS and call the script with the parameters as shown below and PowerBi files auto deploy to Power Bi Report Server
vsts.PNG
and I have my process complete πŸ™‚
Happy Automating πŸ™‚

9 thoughts on “Deploying To a Power Bi Report Server with PowerShell

  1. Pingback: Deploying To Power BI Report Server Using Powershell – Curated SQL

  2. 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.

  3. 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?

Please feel free to comment on this post. All comments are moderated first before appearing on the site

This site uses Akismet to reduce spam. Learn how your comment data is processed.