Skip to main content
Efficient Cost Management with Copilot for PowerBI: A Complete Guide
9:13

Welcome to this guide on how to use Copilot for PowerBI, a tool that can transform your data analysis and visualization experience. However, using it non-stop can be costly. This guide is here to help you manage these costs effectively. We’ll walk you through how to use Microsoft’s tools, Azure Automation, and PowerShell, to control when and how much you use Copilot for PowerBI. This way, you only pay for what you use, making it more affordable for everyone, including individuals and small to medium-sized businesses.

Table of contents:

💪Leveraging Azure Automation and PowerShell for Cost Management

⚠️ Warning: Potential Cost Overages with F64 SKU

📋 Prerequisites

🔧 Step 1: Setting Up Azure Automation Account

📝 Step 2: Creating the Azure Automation Runbook

🔑 Step 3: Grant Contributor access to your Fabric Capacity for your Azure Automation account Managed Identity

🔗 Step 4: Creating a Webhook for the Runbook

💻 Step 5: Local PowerShell Script

🧪 Step 6: Testing

Leveraging Azure Automation and PowerShell for Cost Management

Copilot for PowerBI offers a transformative experience for data analysis and visualization, providing users with advanced AI capabilities to enhance their Power BI usage. However, it's important to note that while Copilot can significantly boost productivity and insights, running high-capacity SKUs like the Fabric F64 capacity 24/7 can lead to substantial costs which makes it difficult for individuals and small/medium businesses to adopt this technology.

Leveraging the power of Azure Automation and PowerShell, you can efficiently manage your PowerBI Fabric F64 capacity to use with Copilot for Power BI without breaking the bank. This guide will walk you through the process of using an Azure Automation Runbook in conjunction with a local PowerShell script to start your Fabric F64 capacity for a set duration and then automatically stop it, ensuring you only pay for what you use.

Ideally this will open the door for individuals and small/medium businesses to be able to use Copilot for Power BI.

 
To learn more about managing Power BI usage metrics, visit this blog: Power BI Usage Metrics Across All Workspaces: Step by Step
 

 

banner of cloud automation vectors

⚠️ Warning: Potential Cost Overages with F64 SKU

⚠️ Caution: The F64 SKU of PowerBI Fabric is a high-capacity offering that can incur significant costs, potentially amounting to thousands of dollars per month. While the automation scripts provided in this blog aim to help manage and minimize these costs, they are not infallible.

⚠️ Use at Your Own Risk: The approach outlined in this blog, including the Azure Automation Runbook and local PowerShell script, requires careful implementation and constant monitoring to avoid unintended charges. It is crucial to ensure that the scripts are functioning correctly and that the Fabric F64 capacity is not running longer than necessary.

Recommendations:

  1. Regularly review the Azure Automation Runbook's execution history to confirm that it starts and stops the capacity as expected.
  2. Monitor your Azure billing closely to detect any anomalies or unexpected charges.
  3. Consider implementing additional safeguards, such as budget alerts in Azure, to receive notifications when your spending approaches a predefined threshold.

⚠️ Disclaimer: The author of this blog and the code provided herein are not responsible for any financial impact related to the use of these scripts. Extreme caution is advised when automating the management of your Fabric F64 capacity. Always test thoroughly in a non-production environment before implementing in a live setting.

Prerequisites

Before we dive into the setup, ensure you have the following:

An Azure subscription.
PowerBI Fabric F64 capacity to manage.
Basic knowledge of PowerShell scripting.

Step 1: Set Up an Azure Automation Account

First, you'll need to create an Azure Automation account if you don't already have one. Here's how:

1.1 Sign in to the Azure portal.
1.2 Navigate to Create a resource > Management Tools > Automation.
1.3 Fill in the necessary details like the name, subscription, resource group, and location.
1.4 Click Create to provision your new Automation account.

Step 2: Create the Azure Automation Runbook

With your Automation account ready, it's time to create a Runbook:

2.1. In your Automation account, go to Runbooks and click Create a runbook.
2.2. Give your Runbook a name, select PowerShell as the Runbook type, and click Create.

Here, you'll input the PowerShell code that will start and stop your Fabric F64 capacity. Note the need to fill in the subscription, resourceGroup and capacity variables in the code below.

Param
(
[Parameter(Mandatory= $false)]
[object]$WebhookData
)
<#
.DESCRIPTION
A Runbook for Starting and Stopping a Fabric Capicity using the Managed Identity

.NOTES
ORIGINAL AUTHOR: Richard Mintz
LASTEDIT: Nov. 15, 2023
UPDATE AUTHOR: Scott Sugar
UPDATED: May. 1, 2024
#>

$subscription = ""
$resourceGroup = ""
$capacity = ""
$apiVersion = "2022-07-01-preview"

$minutesToRun = ($WebhookData.RequestBody | ConvertFrom-Json).minutesToRun

"Received $($minutesToRun) from the WebhookData Request Body"

"Please enable Contributor RBAC permissions on the Fabric Capacity to the system identity of this automation account. Otherwise, the runbook will not be successful"

try
{
"Logging in to Azure..."
Connect-AzAccount -Identity
}
catch {
Write-Error -Message $_.Exception
throw $_.Exception
}

"Get the current status of the Capacity"
$response = Invoke-AzRestMethod -Method "GET" -Path "/subscriptions/${subscription}/resourceGroups/${resourceGroup}/providers/Microsoft.Fabric/capacities/${capacity}?api-version=${apiVersion}"
$state = ($response.Content | ConvertFrom-Json).properties.state
"Current status is: $($state)"
if ($state -eq "Active")
{
Write-Output("${capacity} is already in an Active State")
}
elseif ($state -eq "Paused")
{
"Capacity Paused - Resuming"
$response = Invoke-AzRestMethod -Method "POST" -Path "/subscriptions/${subscription}/resourceGroups/${resourceGroup}/providers/Microsoft.Fabric/capacities/${capacity}/resume?api-version=${apiVersion}"
if($response.StatusCode -eq "202")
{
"Resume Command Run"
} else {
"Resume Command Failed: $($response.StatusCode)"
$response.Content | ConvertFrom-Json
}
}

#Let the capacity run for desired # of minutes
$secondsToRun = $minutesToRun * 60
"Allowing capacity to run for $($minutesToRun) minutes"
Start-Sleep -Seconds $secondsToRun

"Get the current status of the Capacity"
$response = Invoke-AzRestMethod -Method "GET" -Path "/subscriptions/${subscription}/resourceGroups/${resourceGroup}/providers/Microsoft.Fabric/capacities/${capacity}?api-version=${apiVersion}"
$state = ($response.Content | ConvertFrom-Json).properties.state
"Current status is: $($state)"

#Continue attempting to pause the capacity until it is seen in a paused state - wait 30 seconds between attempts.
$x = 1
while($state -ne "Paused")
{
"Capacity Active - Pause Attempt $($x)"
$response = Invoke-AzRestMethod -Method "POST" -Path "/subscriptions/${subscription}/resourceGroups/${resourceGroup}/providers/Microsoft.Fabric/capacities/${capacity}/suspend?api-version=${apiVersion}"
if($response.StatusCode -eq "202")
{
"Pause Command Run"
} else {
"Pause Command Failed: $($response.StatusCode)"
$response.Content | ConvertFrom-Json
}
"Waiting 30 seconds"
Start-Sleep -Seconds 30
"Get the current status of the Capacity"
$response = Invoke-AzRestMethod -Method "GET" -Path "/subscriptions/${subscription}/resourceGroups/${resourceGroup}/providers/Microsoft.Fabric/capacities/${capacity}?api-version=${apiVersion}"
$state = ($response.Content | ConvertFrom-Json).properties.state
"Current status is: $($state)"
$x++
if($state -eq "Paused")
{
"Capacity is paused - Exiting"
}
}

AI Solutions Power BI Ready banner - top 5 things to do to be power bi copilot ready

Step 3: Grant Contributor access to your Fabric Capacity for your Azure Automation account Managed Identity

3.1. Navigate to your Fabric Capacity resource.
3.2. Go to Access Control (IAM).
3.3. Click on Add a role assignment.
3.4. In the Role field, select Contributor.
3.5. In the Assign access to field, select Managed Identity.
3.6. Select the appropriate managed identity associated with your Azure Automation account.
3.7. Click Save to apply the changes.

Step 4: Create a Webhook for the Runbook

To trigger the Runbook remotely, you'll need a webhook:

4.1. In your Runbook, go to Webhooks and click Add Webhook.
4.2. Choose Create new webhook, give it a name, and set the expiration date.
4.3. Copy the webhook URL (you won't be able to retrieve it after you leave this blade).
4.4. Click OK and then Create.

Step 5: Run Local PowerShell Script

On your local machine, you'll have a PowerShell script that calls the webhook to start the Runbook. Note the need to fill in the WebhookUri variable in the code below. This Uri will come from Step 4.3 above.

param (
[Parameter()]
[Int32]
$minutesToRun = 5
)

$WebhookUri = ""

$Body = @(@{ minutesToRun=$($minutesToRun)})
$Body = $Body | ConvertTo-Json

invoke-restmethod -Method POST -Uri $WebhookUri -Body $Body

Step 6: Testing

After setting up your Runbook and local script, test them to ensure they work as expected. You can run the local PowerShell script using the following command (assuming you save the above local powershell script code as Run-PowerBICopilotForXMinutes.ps1).

.\Run-PowerBICopilotForXMinutes.ps1 -minutesToRun 5

Conclusion:

By following the steps in this guide, you can create a cost-effective solution for managing your PowerBI Fabric F64 capacity. This ensures that you’re only paying for the resources you need when you need them. Now, individuals and small/medium businesses can also harness the power of Copilot for Power BI without worrying about the costs. Remember, while this guide aims to help you minimize costs, it’s crucial to implement the steps carefully and monitor your usage regularly to avoid any unexpected charges. Happy data analyzing with PowerBI and Copilot! Let’s make the switch to Microsoft 365 today for efficient operations and enhanced productivity. Get started now!

Do you want to transform your data into actionable insights with Power BI?

ProServeIT helps organizations harness the power of Power BI to turn data into valuable business intelligence.

Get started with Power BI today and empower your team to make data-driven decisions!

 

Scott Sugar
By Scott Sugar
May 24, 2024
From the 1980's, when his father used to hand down old computer equipment, to now, Scott Sugar has always had a fascination with technology. The ability to communicate with people, regardless of distance or location, is, in Scott's opinion, one of the best things about tech. With over 20 years of experience in the IT industry, and 17 years at ProServeIT, Scott's areas of expertise include data & analytics, and IT operations, monitoring, and alerting. Scott heads up ProServeIT's Ho Chi Minh City, Vietnam office. He has spent the majority of his adult life in Asia, and speaks 3 languages. 

Comments