For SME customers, Power BI licensing costs can be a significant barrier when distributing reports to stakeholders who don’t have licenses or the dedicated Premium / Fabric capacity. I previously worked with a customer who needed a highly cost-effective solution for sharing Power BI reports. We developed an automated system that efficiently distributes Power BI reports as static PDFs while minimizing licensing expenses.
In this post, I want to share the solution as a pattern, as it might be useful for others to solve a similar problem.
NOTE: This is not an end-to-end how-to guide, but more of a pattern that can be implemented in different scenarios (although I included some examples of my implementation to give you an idea).
Before we get into the details, let’s take a look at the available options, as there is no one-size-fits-all solution, and there might be existing solutions that fit your needs and requirements.
Options for distributing Power BI reports
While there are several options to distribute Power BI reports, they all come with either licensing costs or other drawbacks, depending on the scenario:
- Standard sharing - Probably the most straightforward out-of-the-box solution. You have great flexibility in terms of sharing options when it comes to utilizing role-based security (Entra ID security groups, roles, etc.).
- Publish to web - Works great just with Pro or Premium per user license assigned to the publisher if you want to make the report publicly available (not recommended for any internal or sensitive data). There are also quite a few limitations.
- Publish to a third-party app - A bit of a niche scenario, but definitely worth mentioning. It’s feasible to embed reports using Power BI Embedded capacity in almost any web application that we build.
- Export of static reports - The obvious drawback in this case is the loss of interactivity of the report. When it comes to formats, we have a few options (depending on whether we are working with standard PBI reports or paginated reports). If we want to automate the export, the PBI Rest API is the way to go. The catch is that the workspace with the report needs to be assigned a Power BI Premium / Fabric capacity, otherwise, you end up with an error.
NOTE: I didn’t include the method of sharing PBIX files as I consider it a bad practice for a number of reasons when it comes to sharing data with stakeholders.
The choice of the option depends on the requirements and available licenses in the organization. In our scenario, we decided to go with the fourth option (exporting static reports), despite not having any dedicated Premium / Fabric capacity available and no chance to get one in the short term.
The enabler
Power BI Premium / Fabric capacities are not the only options. The enabler in our case is Power BI Embedded (yes, the same SKU that is used for embedding reports into other applications). This SKU is primarily designed for embedding Power BI reports into other applications, but its feature parity with regular Premium capacity allows us to utilize the REST API for exporting reports to files.
With the lowest tier of the PBI Embedded SKU, we managed to cover around 2000 export tasks for a cost of around $8 for the capacity. These 2000 tasks were generated in various batch sizes over a period of an entire year. Just for comparison, the P1 SKU costs around $5000 per month.
The pattern
Now that we’ve established the cost enabler, let’s take a look at the pattern itself.
The main idea is to utilize the PBI Embedded capacity only for the necessary time to run the export tasks and then switch it off, as we don’t need it for anything else. Let’s break it down into a few steps:
Step 0: Prerequisites
These are the necessary components that we need to have in place to implement the solution:
- A PBI workspace with the published report
- Provisioned PBI Embedded capacity in Azure (switched off, so there are no unnecessary costs)
- Some kind of an orchestration tool that would allow sending the HTTP requests to Azure and the PBI service (Power Automate, Azure Automation, Azure Functions, etc.). This pretty much depends on your scenario and existing tech stack.
Step 1: Switch on the PBI Embedded capacity
The PBI Embedded is an Azure resource, which can be switched on and off on demand. In our case, we’ve used an Azure Automation PowerShell runbook, so we could utilize its 500 minutes of job time included in the free tier and it’s very simple to implement.
param(
[Parameter(Mandatory = $true)]
[string]$WorkspaceId,
[Parameter(Mandatory = $true)]
[string]$ResourceGroupName,
[Parameter(Mandatory = $true)]
[string]$AutomationAccountName,
[Parameter(Mandatory = $true)]
[string]$SubscriptionName
)
# Getting Automation account credentials
$Credential = Get-AutomationPSCredential -Name $AutomationAccountName
Update-AzConfig -EnableLoginByWam $false
# Starting PowerBI Embedded capacity
Connect-AzAccount -Credential $Credential
Select-AzSubscription -SubscriptionName $SubscriptionName
Resume-AzPowerBIEmbeddedCapacity -Name $PBIEmbeddedName -ResourceGroupName $ResourceGroupName -PassThru
NOTE: This is a simplified implementation that utilizes authentication without MFA or service principal. I highly recommend using a service principal such as app registration or managed identity if supported. It’s also very likely that Microsoft will enforce MFA for the Az module in the near future (early 2025).
Step 2: Apply the PBI Embedded capacity to the workspace
Once the capacity is started, we need to apply it to the workspace. Again, we’ve used an Azure Automation runbook with the MicrosoftPowerBIMgmt module to achieve that (it’s likely that this part can be easily combined with the previous step as we want to apply the capacity right after it’s started).
param(
[Parameter(Mandatory = $true)]
[string]$WorkspaceId,
[Parameter(Mandatory = $true)]
[string]$AutomationAccountName,
[Parameter(Mandatory = $true)]
[string]$PBIEmbeddedCapacityId
)
$Credential = Get-AutomationPSCredential -Name $AutomationAccountName
Connect-PowerBIServiceAccount -Credential $Credential
$body = "{'capacityId': $PBIEmbeddedCapacityId}"
Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/groups/${WorkspaceId}/AssignToCapacity" -Method POST -Body $body
Step 3: Export the report to a file
Now that the workspace is assigned to the capacity, we are able to export the report. The tech-agnostic way to do that is to use the PBI Rest API. In my scenario, we were planning to distribute the reports using a combination of email attachments and SharePoint Online, so we’ve decided to simplify and do the export job directly in Power Automate as the distribution would be done also via PA. Microsoft provides some helpful examples.
The export options are very flexible, so you can pass various filters, RLS, specify which page to export, etc.
Step 4: Switch off the PBI Embedded capacity
The export job is done, so we can switch off the PBI Embedded capacity using the Suspend-AzPowerBIEmbeddedCapacity cmdlet.
param(
[Parameter(Mandatory = $true)]
[string]$WorkspaceId,
[Parameter(Mandatory = $true)]
[string]$ResourceGroupName,
[Parameter(Mandatory = $true)]
[string]$AutomationAccountName,
[Parameter(Mandatory = $true)]
[string]$SubscriptionName
)
# Getting Automation account credentials
$Credential = Get-AutomationPSCredential -Name $AutomationAccountName
Update-AzConfig -EnableLoginByWam $false
# Suspending PowerBI Embedded capacity
Connect-AzAccount -Credential $Credential
Select-AzSubscription -SubscriptionName $SubscriptionName
Suspend-AzPowerBIEmbeddedCapacity -Name $PBIEmbeddedName -ResourceGroupName $ResourceGroupName -PassThru
Step 5: Remove the PBI Embedded capacity from the workspace
The GUID 00000000-0000-0000-0000-000000000000 changes the workspace type back to the Pro license mode, as the workspace is normally used by other users with Pro licenses.
param(
[Parameter(Mandatory = $true)]
[string]$WorkspaceId,
[Parameter(Mandatory = $true)]
[string]$AutomationAccountName
)
$Credential = Get-AutomationPSCredential -Name $AutomationAccountName
Connect-PowerBIServiceAccount -Credential $Credential
$body = "{'capacityId': '00000000-0000-0000-0000-000000000000'}"
Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/groups/${WorkspaceId}/AssignToCapacity" -Method POST -Body $body
Step 6: Share the report with stakeholders
The last step is to share the report with stakeholders/users. In our scenario, we’ve already exported the report in Power Automate, so we just need to attach it to an email and send it out (as demonstrated in the example by Microsoft) or upload it to a SharePoint Library in the same workflow.
Tips
Dedicated report(s) for export
I’ve already mentioned that the export of the report is lossy, as it doesn’t include any interactivity of the original report. I recommend creating a dedicated report just for the export purposes, which would be formatted for the best possible output.
Paginated reports
These are great for this scenario, especially when it comes to exporting table visuals, as they can automatically span to multiple pages to fit the data.
Merging multiple pages into a single PDF
In our scenario, we were exporting pages from multiple reports (combination of paginated and standard reports) and then merging them into a single PDF file. You have a few options, but if your volume is not too high (e.g., less than 500 reports per month), you can, for example, utilize the free tier of the Adobe PDF Services (also existing as a Power Automate connector).
Focus on the PBI Embedded capacity management
What I mean by that is that you should have proper error handling in place, as you don’t want failed export tasks (which will occasionally happen) to leave the capacity running. Additionally, depending on your scenario, you might want to implement logic to prevent accidentally switching off the capacity during overlapping export batches/jobs.
When to use this pattern
To summarize, this pattern is not a silver bullet and should be used in scenarios where:
- No dedicated Power BI Premium / Fabric capacity is available
- No other use cases would benefit from Premium / Fabric capacity
- Interactive features of Power BI reports can be omitted
- Reports need to be distributed to stakeholders who don’t have user accounts (either internal or guest) in your M365 tenant or don’t have Power BI Pro licenses
- There is a predictable cadence of report distribution