Automate Power BI Report Server with PowerShell

I develop in both the Power BI Service (Service) and on a Power BI Report Server (PBIRS). Most people would probably be happy to forego having to know PBIRS and would just like to use the Service, but there are times when PBIRS is really nice to use I prefer it. Not all the time, but sometimes. No, really.

I am not the admin at the tenant and I don’t really have the ability to do too much other than what any other admin can do on a workspace. I cannot generally query APIs directly or have a service account. I have made use of using the XMLA endpoint and have written script to back up things published in the Service.

On the PBIRS side, once I realized that PBIRS is a superset of Reporting Services and the Reporting Services has its own PowerShell library, things got way more interesting on the PBIRS side of the house for me.

If you don’t have admin rights on your computer, you can install the PowerShell library this way:

Install-Module -Name ReportingServicesTools -Scope CurrentUser

This does not install it for all users, just you. Usually, you won’t need admin rights to do this. Just make sure that whatever library you are installing is safe to use and if there are any questions include your Information Security Officer (or similar person in that position).

Some other libraries that have been helpful have been dbatools (interacting with databases), burnttoast (for push notifications), and ImportExcel (for Excel interaction).

Once you are set up, you can use these in tandem with each other to do things like:

• Check permissions
• Grant/Revoke permissions
• Upload a .pbix or .xlsx file to a folder
• Check refreshes on a report
• and much, much more!!!

Here is an example of one I wrote to dump a table to an Excel file on a shared drive and then upload it to a specific folder in PBIRS:

#Import modules
Import-Module dbatools
Import-Module ImportExcel

+#Connect to SQL Server
$SQlServer = 'YourServer'
$ExcelPath = 'C:\YourFilePath.xlsx'

#Export the data
$data = Invoke-DbaQuery -SqlInstance $SqlServer -Query "SELECT * FROM [Database].[dbo].[Table]"
$data Export-Excel -Path $ExcelPath


#Import modules
Import-Module dbatools
Import-Module ImportExcel

#Connect to SQL Server
$SQlServer = 'YourServer'
$ExcelPath = 'C:\YourFilePath.xlsx'

#Export the data
$data = Invoke-DbaQuery -SqlInstance $SqlServer -Query "SELECT * FROM [Database].[dbo].[Table]"
$data Export-Excel -Path $ExcelPath

#Set PBIRS URL
$ReportPortalUri = 'https://yourportal.com/reports'

Write-Host "Uploading excel file..."

#Set up API call parameters
$uploadItemPath = $ExcelPath
$catalogItemsUri = $ReportPortalUri + "/api/v2.0/CatalogItems"
$bytes = [System.IO.File]::ReadAllBytes($uploadItemPath)
$payload = @{
      "@odata.type" = "#Model.ExcelWorbook";
      "Content" = [System.Convert]::ToBase64String($bytes);
      "Content-Type" = "ExcelWorkbook";
      "Name" = "Test_Upload.xlsx";
      "Path" = '/Folder/Path/On/PBIRS'
} | ConvertTo-Json

#Make API call to upload
Invoke-WebRequest -Uri $catalogItemsUri -Method Post -Body $payload -ContentType "application/json" -UseDefaultCredentials | Out-Null

Now, this can be extended to make it a little more dynamic in terms of the file naming, etc. However, it definitely demonstrates how you can mix and match different PowerShell libraries to extend the utility of PBIRS.

Of course, someone reading this may say, “Why don’t you just make a paginated report?” I could have, for sure. However, I definitely have been pressed for an Excel file for one particular use case and honestly scheduling this with a PowerShell is not really difficult at all. Plus, I can move on to other projects and continue to expand my knowledge on automating recurring asks.