A Cost-Effective Fabric Solution Driven by Azure A...

charlyS · ‎07-14-2025

🎯Why this approach?

For small projects or cost-sensitive scenarios, we often want to limit the time when the Microsoft Fabric capacity is running. Why pay for an always-on capacity if you only need it to process data for an hour each night?

This simple but robust PowerShell script helps you:

Start your Fabric capacity on demand
Run a Data Pipeline (to ingest and transform your data)
Refresh your Power BI semantic models hosted in Power BI Pro workspaces
Stop the Fabric capacity to save money

Because your semantic model and reports are in a Pro workspace, they stay accessible to your users even when the Fabric capacity is paused — as long as your models are under 1 GB and your audience is relatively small.

✅This is a cost-effective, reliable solution for SMBs or projects with a limited audience — ideal if you don’t need Fabric for huge models or heavy concurrency.

⚙️How does it work?

This automation is designed to run entirely unattended, using an Azure Automation Account or any other scheduler that supports Managed Identity.

Using Connect-AzAccount -Identity, the runbook authenticates securely and can:

Start or suspend your Fabric capacity (with the right RBAC on the capacity)
Run the Fabric Data Pipeline (with the required permissions on the workspace)
Refresh your Power BI datasets (with workspace permissions)

📊Architecture Overview

Here’s the flow of the solution:

1️⃣Azure Automation (or another orchestrator) runs a PowerShell script
2️⃣The script resumes the Fabric capacity
3️⃣It runs your Data Pipeline to ingest and transform data
4️⃣It then refreshes one or more Power BI semantic models in your Pro workspace
5️⃣When everything is done, it suspends the capacity — saving you money
6️⃣Your end-users keep access to the reports because the models live in a Pro workspace

🛠️ Code explained

Below are the key reusable functions. Each one does one thing — this makes it easy to maintain.

✅1. Start-FabricCapacity

Calls the REST API to resume your Fabric capacity.

function Start-FabricCapacity {
    param([string]$ResourceId)
    $token = (Get-AzAccessToken -ResourceUrl "https://management.azure.com/").Token
    $headers = @{ Authorization = "Bearer $token"; "Content-Type" = "application/json" }
    $url = "https://management.azure.com$ResourceId/resume?api-version=2022-07-01-preview"
    Invoke-RestMethod -Uri $url -Method POST -Headers $headers
    Write-Host "✅ Fabric Capacity started: $ResourceId"
}

✅2. Stop-FabricCapacity

Suspends the capacity once processing is finished.

function Stop-FabricCapacity {
    param([string]$ResourceId)
    $token = (Get-AzAccessToken -ResourceUrl "https://management.azure.com/").Token
    $headers = @{ Authorization = "Bearer $token"; "Content-Type" = "application/json" }
    $url = "https://management.azure.com$ResourceId/suspend?api-version=2022-07-01-preview"
    Invoke-RestMethod -Uri $url -Method POST -Headers $headers
    Write-Host "🛑 Fabric Capacity suspended: $ResourceId"
}

✅3. Invoke-FabricPipeline
Starts the pipeline job inside your Fabric workspace.

function Invoke-FabricPipeline {
    param([string]$WorkspaceId, [string]$PipelineId)
    $token = (Get-AzAccessToken -ResourceUrl "https://api.fabric.microsoft.com").Token
    $headers = @{ Authorization = "Bearer $token"; "Content-Type" = "application/json" }
    $url = "https://api.fabric.microsoft.com/v1/workspaces/$WorkspaceId/items/$PipelineId/jobs/instances?jobType=Pipeline"
    $response = Invoke-WebRequest -Uri $url -Method POST -Headers $headers -UseBasicParsing
    if ($response.StatusCode -eq 202) {
        $location = $response.Headers['Location']
        $jobInstanceId = ($location -split "/jobs/instances/")[1]
        Write-Host "🚀 Pipeline launched (Job ID: $jobInstanceId)"
        return $jobInstanceId
    } else {
        Write-Error "❌ Pipeline launch failed"
        return $null
    }
}

✅4. Wait-FabricPipelineCompletion

Polls the pipeline status until it’s done.

function Wait-FabricPipelineCompletion {
    param([string]$WorkspaceId, [string]$PipelineId, [string]$JobInstanceId)
    $token = (Get-AzAccessToken -ResourceUrl "https://api.fabric.microsoft.com").Token
    $headers = @{ Authorization = "Bearer $token"; "Content-Type" = "application/json" }
    do {
        $url = "https://api.fabric.microsoft.com/v1/workspaces/$WorkspaceId/items/$PipelineId/jobs/instances/$JobInstanceId"
        $response = Invoke-RestMethod -Uri $url -Headers $headers -Method GET
        $status = $response.status
        Write-Host "⏳ Pipeline status: $status"
        Start-Sleep -Seconds 15
    } while ($status -notin @("Completed", "Failed", "Cancelled"))
    Write-Host "✅ Pipeline finished: $status"
}

✅5. Invoke-PowerBIRefreshDataset

Starts a dataset refresh using the Power BI REST API.

function Invoke-PowerBIRefreshDataset {
    param([string]$PowerBIWorkspaceId, [string]$DatasetId)
    $token = (Get-AzAccessToken -ResourceUrl "https://analysis.windows.net/powerbi/api").Token
    $headers = @{ Authorization = "Bearer $token"; "Content-Type" = "application/json" }
    $url = "https://api.powerbi.com/v1.0/myorg/groups/$PowerBIWorkspaceId/datasets/$DatasetId/refreshes"
    Invoke-RestMethod -Uri $url -Method POST -Headers $headers -Body "{}"
    Write-Host "🔄 Dataset refresh started: $DatasetId"
}

✅6. Wait-PowerBIRefreshCompletion

Polls the dataset refresh status until it’s done.

function Wait-PowerBIRefreshCompletion {
    param([string]$PowerBIWorkspaceId, [string]$DatasetId)
    $token = (Get-AzAccessToken -ResourceUrl "https://analysis.windows.net/powerbi/api").Token
    $headers = @{ Authorization = "Bearer $token"; "Content-Type" = "application/json" }
    do {
        $url = "https://api.powerbi.com/v1.0/myorg/groups/$PowerBIWorkspaceId/datasets/$DatasetId/refreshes?$top=1"
        $response = Invoke-RestMethod -Uri $url -Headers $headers -Method GET
        $status = $response.value[0].status
        Write-Host "⏳ Dataset status: $status"
        Start-Sleep -Seconds 15
    } while ($status -notin @("Completed", "Failed", "Cancelled"))
    Write-Host "✅ Dataset refresh finished: $status"
}

✅📦Consolidated Example

Here’s how it all ties together in one run:

<#
.SYNOPSIS
    Manages a Microsoft Fabric capacity, executes a pipeline,
    then triggers and waits for the refresh of a list of Power BI datasets provided as JSON.

.DESCRIPTION
    - Start-FabricCapacity
    - Invoke-FabricPipeline + Wait-FabricPipelineCompletion
    - For each pair in the list: Invoke-PowerBIRefreshDataset + Wait-PowerBIRefreshCompletion
    - Stop-FabricCapacity

.NOTES
    Requires Az.Accounts and permissions on the Power BI API (MSI or Service Principal).
#>

Param(
    [string]$ResourceId,
    [string]$WorkspaceId,
    [string]$PipelineId,
    [string]$DatasetRefreshListJson  # List of datasets as a JSON string
)

# ----------------------------------------
# Azure Authentication (Managed Identity or interactive login)
# ----------------------------------------
Connect-AzAccount -Identity
#$ResourceId = "/subscriptions/xxxx/resourceGroups/xxxx/providers/Microsoft.Fabric/capacities/xxxs"
$WorkspaceId = "xxxx"
$PipelineId = "xxxxx"
$DatasetRefreshListJson = '[{"PowerBIWorkspaceId":"xxxx","DatasetId":"xxxxx"}]'

# ----------------------------------------
# Convert the JSON list to an array
# ----------------------------------------
$DatasetRefreshList = $DatasetRefreshListJson | ConvertFrom-Json
Write-Host "✅ List of datasets to refresh:"
$DatasetRefreshList | Format-Table

# ----------------------------------------
# Fabric Functions
# ----------------------------------------
function Start-FabricCapacity {
    param([string]$ResourceId)
    $token = (Get-AzAccessToken -ResourceUrl "https://management.azure.com/").Token
    $headers = @{ Authorization = "Bearer $token"; "Content-Type" = "application/json" }
    $url = "https://management.azure.com$ResourceId/resume?api-version=2022-07-01-preview"
    Invoke-RestMethod -Uri $url -Method POST -Headers $headers
    Write-Host "✅ Capacity started: $ResourceId"
}

function Stop-FabricCapacity {
    param([string]$ResourceId)
    $token = (Get-AzAccessToken -ResourceUrl "https://management.azure.com/").Token
    $headers = @{ Authorization = "Bearer $token"; "Content-Type" = "application/json" }
    $url = "https://management.azure.com$ResourceId/suspend?api-version=2022-07-01-preview"
    Invoke-RestMethod -Uri $url -Method POST -Headers $headers
    Write-Host "🛑 Capacity suspended: $ResourceId"
}

function Invoke-FabricPipeline {
    param([string]$WorkspaceId, [string]$PipelineId)
    $token = (Get-AzAccessToken -ResourceUrl "https://api.fabric.microsoft.com").Token
    $headers = @{ Authorization = "Bearer $token"; "Content-Type" = "application/json" }
    $url = "https://api.fabric.microsoft.com/v1/workspaces/$WorkspaceId/items/$PipelineId/jobs/instances?jobType=Pipeline"
    $response = Invoke-WebRequest -Uri $url -Method POST -Headers $headers -UseBasicParsing
    Start-Sleep -Seconds 10
    if ($response.StatusCode -eq 202) {
        $location = $response.Headers['Location']
        $jobInstanceId = ($location -split "/jobs/instances/")[1]
        Write-Host "🚀 Pipeline started: $PipelineId (Job ID: $jobInstanceId)"
        return $jobInstanceId
    }
    else {
        Write-Error "❌ Failed to start pipeline $PipelineId"
        return $null
    }
}

function Wait-FabricPipelineCompletion {
    param([string]$WorkspaceId, [string]$PipelineId, [string]$JobInstanceId, [int]$IntervalSeconds = 15, [int]$TimeoutSeconds = 1200)
    $token = (Get-AzAccessToken -ResourceUrl "https://api.fabric.microsoft.com").Token
    $headers = @{ Authorization = "Bearer $token"; "Content-Type" = "application/json" }
    $elapsed = 0
    while ($true) {
        $url = "https://api.fabric.microsoft.com/v1/workspaces/$WorkspaceId/items/$PipelineId/jobs/instances/$JobInstanceId"
        $response = Invoke-RestMethod -Uri $url -Headers $headers -Method GET
        $status = $response.status
        Write-Host "⏳ Pipeline status: $status"
        if ($status -in @("Completed", "Failed", "Cancelled")) {
            Write-Host "✅ Pipeline finished: $status"
            break
        }
        Start-Sleep -Seconds $IntervalSeconds
        $elapsed += $IntervalSeconds
        if ($elapsed -ge $TimeoutSeconds) {
            Write-Warning "⏰ Pipeline timeout reached: $status"
            break
        }
    }
    return $status
}

# ----------------------------------------
# Power BI Functions
# ----------------------------------------
function Invoke-PowerBIRefreshDataset {
    param([string]$PowerBIWorkspaceId, [string]$DatasetId)
    $token = (Get-AzAccessToken -ResourceUrl "https://analysis.windows.net/powerbi/api").Token
    $headers = @{ Authorization = "Bearer $token"; "Content-Type" = "application/json" }
    $url = "https://api.powerbi.com/v1.0/myorg/groups/$PowerBIWorkspaceId/datasets/$DatasetId/refreshes"
    Invoke-RestMethod -Uri $url -Method POST -Headers $headers -Body "{}"
    Write-Host "🔄 Refresh triggered for DatasetId: $DatasetId"
}

function Wait-PowerBIRefreshCompletion {
    param([string]$PowerBIWorkspaceId, [string]$DatasetId, [int]$IntervalSeconds = 15, [int]$TimeoutSeconds = 900)
    $token = (Get-AzAccessToken -ResourceUrl "https://analysis.windows.net/powerbi/api").Token
    $headers = @{ Authorization = "Bearer $token"; "Content-Type" = "application/json" }
    $elapsed = 0
    while ($true) {
        $url = "https://api.powerbi.com/v1.0/myorg/groups/$PowerBIWorkspaceId/datasets/$DatasetId/refreshes?$top=1"
        $response = Invoke-RestMethod -Uri $url -Method GET -Headers $headers
        $status = $response.value[0].status
        Write-Host "⏳ Dataset status $DatasetId: $status"
        if ($status -in @("Completed", "Failed", "Cancelled")) {
            Write-Host "✅ Refresh completed: $status"
            break
        }
        Start-Sleep -Seconds $IntervalSeconds
        $elapsed += $IntervalSeconds
        if ($elapsed -ge $TimeoutSeconds) {
            Write-Warning "⏰ Timeout reached for DatasetId $DatasetId."
            break
        }
    }
    return $status
}

# ----------------------------------------
# Execution Phase
# ----------------------------------------
Start-FabricCapacity -ResourceId $ResourceId
Write-Host "⏸️ Pausing after starting capacity..."
Start-Sleep -Seconds 30

$jobId = Invoke-FabricPipeline -WorkspaceId $WorkspaceId -PipelineId $PipelineId
Start-Sleep -Seconds 10

Wait-FabricPipelineCompletion -WorkspaceId $WorkspaceId -PipelineId $PipelineId -JobInstanceId $jobId
Start-Sleep -Seconds 10

foreach ($refresh in $DatasetRefreshList) {
    $wsId = $refresh.PowerBIWorkspaceId
    $dsId = $refresh.DatasetId

    Write-Host "➡️ Triggering refresh for WorkspaceId:$wsId DatasetId:$dsId"
    Invoke-PowerBIRefreshDataset -PowerBIWorkspaceId $wsId -DatasetId $dsId
    Start-Sleep -Seconds 10

    Wait-PowerBIRefreshCompletion -PowerBIWorkspaceId $wsId -DatasetId $dsId
    Start-Sleep -Seconds 10
}

Stop-FabricCapacity -ResourceId $ResourceId
Write-Host "✅ All jobs completed successfully. Capacity stopped."

💡Wrap-Up

Using this script pattern with Azure Automation, you can automate your Fabric capacity start/stop cycles, run your ETL pipelines, and refresh your Power BI semantic models for minimal cost.
It’s simple, secure (Managed Identity!), and perfectly suited for small to mid-sized projects that don’t need Fabric licenses for everyone.

Feel free to adapt it for your own environment — and let me know how it goes! 💪