Efficient Cost Management with Copilot for PowerBI: A Complete Guide

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.

⚠️ 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.

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.

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.

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"
    }
}

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

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!