Martin's Blog

This blog post is part of my “Automating Power BI deployments” series, and you can find a list of the other posts here.

Retrieve the workspace

Before we can deploy a report to a workspace, we will need the Id of the workspace or a workspace object itself. We’ve done that in the previous blog post, but I’ll post it again here with a minor difference:

#Retrieve the workspace
$WorkspaceObject = (Get-PowerBIWorkspace -Scope Organization -Name $WorkspaceName)

Remember how we forced the script in the previous post to ignore the potential error when retrieving the workspace, because it may not have existed yet? We wouldn’t want to do that here because the workspace has to exist in order for us to deploy a report.

Deploy the report

Deploying the report is seemingly straight-forward, but there are some risks we need to consider:

• What should we do if the report already exists?
• If the dataset exists, what should we do if there are other reports that use this (shared) dataset?

The last item is a bit of an edge-case that we’ll have to dive deeper into, but let’s look at the basic cmdlet first.

#Deploy pbix file
New-PowerBIReport -Workspace $WorkspaceObject -Path $PbixFilePath -Name $ReportName -ConflictAction CreateOrOverwrite

The code snippet above assumes that we are connected to the service, have parameters or variables with assigned values for the file path and report name ($PbixFilePath & $ReportName), and have the workspace object stored in a variable as well.

A pretty cool feature here is that we can call the deployed report and dataset anything we want, irrespective of the actual name of the .pbix file.

The -ConflictAction parameter

We need to pay close attention to this parameter as it will dictate the behavior of the deployment in case of a conflict. The official documentation gives us the list of possible values we can use, but you have to dig a few levels deeper into the API documentation in order to find the descriptions. Let’s take a closer look at what our options are:

• CreateOrOverwrite
  • This is most likely the option you will use 99.9% of the time, and it simply means that the report (or dataset) with the same name will be replaced if it already exists. If it doesn’t exist, it will be created as you would expect.
• Overwrite
  • If you use this option, the cmdlet will fail if the report (or dataset) doesn’t exist. This option could be useful if you wanted to iterate over a list of workspaces, replacing the report/dataset only if it exists.
• Abort
  • Execution will be aborted (i.e. it will fail) if the report already exists. You’ll use this option if you only want to create new reports and not overwrite existing ones, and could be useful if you know that older versions of the same report are already deployed and you don’t want to replace them.
• GenerateUniqueName
  • This option only applies to dataflows and not relevant to our conversation here.
• Ignore
  • Have you ever wondered how great it would be to have two reports/datasets with identical names but different id’s in the same workspace? Yeah, neither have I but that is what could happen if you use this option. I am sure that somebody out there has a use-case for this option, but you should be very careful before using it as you could end up with uncontrolled chaos. Just don’t do it…

The (not so) edge-case

While working on this part of an automation for a customer, we encountered an interesting edge-case which I think will become more prevalent as we use shared datasets and users create copies of existing reports.

According to the documentation, the New-PowerBIReport cmdlet will fail if there is more than one dataset with the same name as the one you’re trying to deploy. What it doesn’t tell you is that it will also throw an error if there are more than one report associated with the existing dataset you’re attempting to overwrite.

I chose my words carefully here to say “throw an error” because even though an error is thrown by the cmdlet itself, the deployment itself actually succeeds! Yep, I’m unfortunately not kidding and have logged a bug in the powerbi-powershell repo as a result. I’m hoping that this will be fixed and that we’ll get a few more options in the cmdlet to account for this scenario in future.

To circumvent this “feature” we’ll have to incorporate some fancy error-handling footwork, trapping and ignoring this specific error only. It’s not completely bullet-proof, but given what we have available right now this is how I like to do it:

try { 

    #Deploy pbix file
    New-PowerBIReport -Workspace $WorkspaceObject -Path $PbixFilePath -Name $ReportName -ConflictAction CreateOrOverwrite | Out-Null 
} 

catch { 

    #This "error" means that there were more than one report associated with the dataset, so we're going to ignore it and continue 
    if ($_.Exception.Message -ccontains "Sequence contains more than one element") { 

        Write-Output "More than one report was associated with this dataset. We're ignoring the ""error"" and continuing..." `n

        #It's a non-terminating error, but we'll clear it to be safe 
            $error.Clear()
            
    } 

    #A real error occurred, so throw it 
    else { 
        throw      
    } 
} 

#Retrieve the newly deployed report 
$PbiReportObject = (Get-PowerBIReport -Workspace $WorkspaceObject -Name $ReportName) 

#Check to see if it's been deployed successfully 
if ($PbiReportObject) {

    Write-Output "Report ""$ReportName"" successfully deployed..." `n 
}

else {

    #throw an error if it hasn't been deployed successfully (i.e. the variable is empty) 
    throw "Hmmm...something went wrong. The report was not deployed :-/" 
}

I’m only looking for (or catching) a specific error message and ignoring it, and even though the error is non-terminating (i.e. it won’t cause the entire script to stop executing) I still think it’s a good practice to reset or clear the error afterwards.

You may also think that the second part of the script that attempts to retrieve the newly deployed report is unnecessary, but I think it’s useful to at least make sure that the report exists after the attempted deployment.

It would have been super useful if the Get-PowerBIReport or Get-PowerBIDataset cmdlets returned the “last modified date” so that we can verify deployments in a more elegant way. Hopefully we will get something to that effect soon to make these things a bit easier…please vote for my submitted idea if you agree.

Want to download the PowerShell scripts to perform these actions? Get it from my GitHub repo.