Home > Tutorials > Tutorial 15: Cost Management & Capacity Optimization

💰 Tutorial 15: Cost Management & Capacity Optimization¶

Last Updated: 2026-04-15 | Version: 2.0 Status: ✅ Final | Maintainer: Documentation Team

📑 Table of Contents¶

• 📋 Overview
• 🎯 Learning Objectives
• 🏗️ Capacity Architecture
• ✅ Prerequisites
• 📊 Step 1: Understand Fabric Capacity SKUs and Pricing
• 📈 Step 2: Configure Capacity Metrics App
• ⏸️ Step 3: Implement Capacity Pause/Resume Automation
• 🔔 Step 4: Set Up Cost Monitoring and Budget Alerts
• 💳 Step 5: Implement Chargeback and Cost Allocation
• ⚡ Step 6: Optimization Strategies
• 🔄 Step 7: Burst Capacity and Smoothing
• 💵 Step 8: Reserved Capacity vs Pay-As-You-Go
• 🏢 Step 9: Multi-Workspace Capacity Sharing
• ✅ Validation Checklist
• 🔧 Troubleshooting
• 📌 Best Practices
• 📝 Summary

Source: Microsoft Fabric Licenses

Progress Tracker¶

Tutorial	Name	Status	Duration	Difficulty
00	Environment Setup		45-60 min	Beginner
01	Bronze Layer		60-90 min	Beginner
02	Silver Layer		60-90 min	Intermediate
03	Gold Layer		90-120 min	Intermediate
04	Real-Time Analytics		90-120 min	Advanced
05	Direct Lake & Power BI		60-90 min	Intermediate
06	Data Pipelines		60-90 min	Intermediate
07	Governance & Purview		60-90 min	Intermediate
08	Database Mirroring		60-90 min	Intermediate
09	Advanced AI/ML		90-120 min	Advanced
10	Teradata Migration		120-180 min	Advanced
11	SAS Connectivity		60-90 min	Intermediate
12	CI/CD & DevOps		90-120 min	Advanced
13	Migration Planning		60-90 min	Intermediate
14	Security & Compliance		90-120 min	Advanced
15	Cost Management & Capacity Optimization		2 hours	Intermediate
16	Performance Tuning		90-120 min	Advanced
17	Monitoring & Alerting		60-90 min	Advanced
18	Data Sharing		60-90 min	Intermediate
19	Copilot & AI		120-180 min	Advanced

Click any tutorial name to navigate directly to it

📋 Overview¶

This tutorial provides comprehensive guidance on managing costs and optimizing capacity utilization in Microsoft Fabric. You will learn how to right-size your Fabric capacity, implement automation for cost savings, set up monitoring dashboards, and establish chargeback models for your organization.

Effective cost management is critical for any data platform. Microsoft Fabric's consumption-based pricing model offers flexibility but requires careful monitoring and optimization to maximize value while controlling spend.

flowchart TB
    subgraph Strategy["Cost Management Strategy"]
        direction TB
        PLAN[Capacity Planning] --> MONITOR[Cost Monitoring]
        MONITOR --> OPTIMIZE[Optimization]
        OPTIMIZE --> GOVERN[Cost Governance]
        GOVERN --> PLAN
    end

    subgraph Tools["Fabric Cost Tools"]
        direction TB
        METRICS[Capacity Metrics App]
        AZURE[Azure Cost Management]
        ALERTS[Budget Alerts]
        AUTO[Pause/Resume Automation]
    end

    Strategy --> Tools

    style PLAN fill:#4a90d9,stroke:#2c5282,color:#fff
    style MONITOR fill:#48bb78,stroke:#276749,color:#fff
    style OPTIMIZE fill:#ed8936,stroke:#c05621,color:#fff
    style GOVERN fill:#9f7aea,stroke:#6b46c1,color:#fff

🎯 Learning Objectives¶

By the end of this tutorial, you will be able to:

• Understand Fabric capacity SKUs and Capacity Units (CUs)
• Configure and use the Capacity Metrics app for monitoring
• Implement capacity pause/resume automation using PowerShell and Logic Apps
• Set up cost monitoring dashboards and budget alerts
• Design chargeback and cost allocation models
• Apply optimization strategies for compute, storage, and queries
• Compare reserved capacity vs. pay-as-you-go pricing
• Estimate costs for POC and production deployments
• Configure multi-workspace capacity sharing effectively

🏗️ Capacity Architecture¶

Understanding how Fabric capacity works is fundamental to cost optimization.

flowchart TB
    subgraph Azure["Azure Subscription"]
        subgraph Capacity["Fabric Capacity (F64)"]
            CU[64 Capacity Units]

            subgraph Workloads["Workload Distribution"]
                SPARK[Spark Jobs<br/>Notebooks]
                DW[Data Warehouse<br/>Queries]
                RT[Real-Time<br/>Analytics]
                DF[Data Factory<br/>Pipelines]
                PBI[Power BI<br/>Refresh/Query]
            end

            CU --> Workloads
        end

        subgraph Workspaces["Workspaces"]
            WS1[casino-prod]
            WS2[casino-dev]
            WS3[casino-analytics]
        end

        Capacity --> Workspaces
    end

    subgraph Billing["Billing Model"]
        PAYG[Pay-as-you-go<br/>Hourly billing]
        RI[Reserved Capacity<br/>1-year commitment]
    end

    Azure --> Billing

Capacity Units Explained¶

Capacity Units (CUs) are the fundamental compute resource in Microsoft Fabric. All workloads consume CUs, which are pooled across a capacity.

✅ Prerequisites¶

Before starting this tutorial, ensure you have:

• Completed Tutorial 00: Environment Setup
• Fabric Admin or Capacity Admin role
• Access to Azure portal for capacity management
• PowerShell 7.0+ with Az module installed
• Azure subscription Owner or Contributor role (for automation setup)
• Understanding of basic Fabric workload types

Note: Some sections require elevated permissions. Work with your Fabric administrator if you don't have direct access.

📊 Step 1: Understand Fabric Capacity SKUs and Pricing¶

1.1 Fabric Capacity SKU Reference¶

Microsoft Fabric offers capacity SKUs ranging from F2 to F2048. Each SKU provides a specific number of Capacity Units.

Note: Prices are approximate and vary by region. Check Azure Pricing Calculator for current rates. Monthly estimates assume 730 hours (24/7 operation).

1.2 CU Consumption by Workload Type¶

Different Fabric workloads consume CUs at different rates:

pie showData
    title CU Consumption Distribution (Typical Casino Workload)
    "Spark/Notebooks" : 35
    "Data Warehouse" : 25
    "Real-Time Analytics" : 15
    "Data Factory" : 10
    "Power BI" : 10
    "OneLake Storage" : 5

1.3 POC vs Production Cost Estimation¶

Casino POC Cost Estimate (3-Day Workshop)¶

Casino Production Cost Estimate (Monthly)¶

Tip: Reserved capacity offers up to 30% savings for committed workloads. Consider for production deployments with predictable usage.

📈 Step 2: Configure Capacity Metrics App¶

The Microsoft Fabric Capacity Metrics app provides detailed insights into capacity utilization.

2.1 Install the Capacity Metrics App¶

1. Navigate to Microsoft AppSource
2. Search for "Microsoft Fabric Capacity Metrics"
3. Click Get it now
4. Select your workspace for installation
5. Configure the connection to your Fabric capacity

2.2 Key Metrics to Monitor¶

flowchart LR
    subgraph Metrics["Key Capacity Metrics"]
        direction TB
        CU_PCT[CU Utilization %]
        THROTTLE[Throttling Events]
        BURST[Burst Usage]
        TREND[Usage Trends]
    end

    subgraph Actions["Optimization Actions"]
        direction TB
        SCALE[Scale Up/Down]
        SCHEDULE[Reschedule Jobs]
        OPTIMIZE[Optimize Queries]
        PAUSE[Pause Capacity]
    end

    CU_PCT -->|">80% sustained"| SCALE
    THROTTLE -->|"frequent"| SCALE
    BURST -->|"excessive"| SCHEDULE
    TREND -->|"predictable lows"| PAUSE

2.3 Understanding the Metrics Dashboard¶

2.4 Create Custom Monitoring Views¶

# Fabric Notebook: Capacity Metrics Analysis
# ==========================================
# Connect to Capacity Metrics data for custom analysis

from pyspark.sql.functions import col, avg, max as spark_max, sum as spark_sum
from pyspark.sql.functions import window, date_trunc
from datetime import datetime, timedelta

# Read capacity metrics (if exported to Lakehouse)
df_metrics = spark.table("admin.capacity_metrics")

# Daily CU utilization summary
df_daily = df_metrics \
    .withColumn("date", date_trunc("day", col("timestamp"))) \
    .groupBy("date") \
    .agg(
        avg("cu_utilization_pct").alias("avg_utilization"),
        spark_max("cu_utilization_pct").alias("peak_utilization"),
        spark_sum("throttling_events").alias("total_throttling")
    ) \
    .orderBy("date")

display(df_daily)

# Identify peak usage hours
df_hourly = df_metrics \
    .withColumn("hour", date_trunc("hour", col("timestamp"))) \
    .groupBy("hour") \
    .agg(avg("cu_utilization_pct").alias("avg_cu_pct")) \
    .orderBy(col("avg_cu_pct").desc())

print("Peak Usage Hours:")
df_hourly.show(10)

⏸️ Step 3: Implement Capacity Pause/Resume Automation¶

One of the most effective cost-saving strategies is automatically pausing capacity during non-business hours.

3.1 Cost Savings from Pause/Resume¶

gantt
    title Capacity Usage Schedule (Casino POC)
    dateFormat HH:mm
    axisFormat %H:%M

    section Weekday
    Active (Business Hours)    :active, 08:00, 12h
    Paused (Night)            :done, 20:00, 12h

    section Weekend
    Paused (Saturday)         :done, 00:00, 24h
    Paused (Sunday)           :done, 00:00, 24h

3.2 PowerShell Automation Script¶

Create a PowerShell script for capacity management:

# ============================================================
# Fabric Capacity Pause/Resume Automation
# ============================================================
# File: Manage-FabricCapacity.ps1
# Purpose: Automate capacity pause/resume for cost savings
# ============================================================

param(
    [Parameter(Mandatory=$true)]
    [ValidateSet("Pause", "Resume", "Status")]
    [string]$Action,

    [Parameter(Mandatory=$true)]
    [string]$SubscriptionId,

    [Parameter(Mandatory=$true)]
    [string]$ResourceGroupName,

    [Parameter(Mandatory=$true)]
    [string]$CapacityName
)

# Import Azure module
Import-Module Az.Accounts
Import-Module Az.Resources

# Connect to Azure (use managed identity in automation)
if (-not (Get-AzContext)) {
    Connect-AzAccount -Identity
}

# Set subscription context
Set-AzContext -SubscriptionId $SubscriptionId

# Get capacity resource
$resourceId = "/subscriptions/$SubscriptionId/resourceGroups/$ResourceGroupName/providers/Microsoft.Fabric/capacities/$CapacityName"

function Get-CapacityStatus {
    $capacity = Get-AzResource -ResourceId $resourceId
    return $capacity.Properties.state
}

function Invoke-CapacityAction {
    param([string]$ActionType)

    $apiVersion = "2023-11-01"
    $actionUri = "$resourceId/$($ActionType.ToLower())?api-version=$apiVersion"

    try {
        Invoke-AzRestMethod -Path $actionUri -Method POST
        Write-Host "Successfully initiated $ActionType on capacity: $CapacityName"
    }
    catch {
        Write-Error "Failed to $ActionType capacity: $_"
        exit 1
    }
}

# Execute requested action
switch ($Action) {
    "Status" {
        $status = Get-CapacityStatus
        Write-Host "Capacity '$CapacityName' is currently: $status"
    }
    "Pause" {
        $status = Get-CapacityStatus
        if ($status -eq "Active") {
            Invoke-CapacityAction -ActionType "suspend"
            Write-Host "Capacity pause initiated. May take 1-2 minutes to complete."
        }
        else {
            Write-Host "Capacity is already paused or in transition. Current state: $status"
        }
    }
    "Resume" {
        $status = Get-CapacityStatus
        if ($status -eq "Paused") {
            Invoke-CapacityAction -ActionType "resume"
            Write-Host "Capacity resume initiated. May take 2-3 minutes to complete."
        }
        else {
            Write-Host "Capacity is already active or in transition. Current state: $status"
        }
    }
}

# Output final status
Start-Sleep -Seconds 10
$finalStatus = Get-CapacityStatus
Write-Host "Final capacity state: $finalStatus"

3.3 Azure Automation Runbook Setup¶

1. Create Azure Automation Account:

# Create Automation Account for scheduled capacity management
$automationAccountName = "aa-fabric-capacity-mgmt"
$resourceGroup = "rg-fabric-casino-poc"
$location = "eastus2"

New-AzAutomationAccount `
    -ResourceGroupName $resourceGroup `
    -Name $automationAccountName `
    -Location $location `
    -AssignSystemIdentity

# Grant Contributor role to the managed identity
$automationAccount = Get-AzAutomationAccount -ResourceGroupName $resourceGroup -Name $automationAccountName
$principalId = $automationAccount.Identity.PrincipalId

New-AzRoleAssignment `
    -ObjectId $principalId `
    -RoleDefinitionName "Contributor" `
    -Scope "/subscriptions/$subscriptionId/resourceGroups/$resourceGroup"

1. Create Runbook:

# Import the runbook
$runbookName = "Manage-FabricCapacity"
$runbookPath = "./Manage-FabricCapacity.ps1"

Import-AzAutomationRunbook `
    -ResourceGroupName $resourceGroup `
    -AutomationAccountName $automationAccountName `
    -Name $runbookName `
    -Type PowerShell `
    -Path $runbookPath `
    -Published

1. Create Schedules:

# Schedule: Pause at 8 PM weekdays
New-AzAutomationSchedule `
    -ResourceGroupName $resourceGroup `
    -AutomationAccountName $automationAccountName `
    -Name "Pause-Weekday-Evening" `
    -StartTime (Get-Date "20:00").AddDays(1) `
    -WeekInterval 1 `
    -DaysOfWeek Monday,Tuesday,Wednesday,Thursday,Friday `
    -TimeZone "Eastern Standard Time"

# Schedule: Resume at 7 AM weekdays
New-AzAutomationSchedule `
    -ResourceGroupName $resourceGroup `
    -AutomationAccountName $automationAccountName `
    -Name "Resume-Weekday-Morning" `
    -StartTime (Get-Date "07:00").AddDays(1) `
    -WeekInterval 1 `
    -DaysOfWeek Monday,Tuesday,Wednesday,Thursday,Friday `
    -TimeZone "Eastern Standard Time"

# Link schedules to runbook
$params = @{
    "Action" = "Pause"
    "SubscriptionId" = $subscriptionId
    "ResourceGroupName" = $resourceGroup
    "CapacityName" = "fabric-casino-poc"
}

Register-AzAutomationScheduledRunbook `
    -ResourceGroupName $resourceGroup `
    -AutomationAccountName $automationAccountName `
    -RunbookName $runbookName `
    -ScheduleName "Pause-Weekday-Evening" `
    -Parameters $params

3.4 Logic Apps Alternative¶

For a no-code solution, use Azure Logic Apps:

flowchart LR
    subgraph Trigger["Trigger"]
        SCHED[Recurrence<br/>Schedule]
    end

    subgraph Condition["Condition"]
        CHECK{Is Business<br/>Hours?}
    end

    subgraph Actions["Actions"]
        RESUME[Resume<br/>Capacity]
        PAUSE[Pause<br/>Capacity]
    end

    SCHED --> CHECK
    CHECK -->|Yes| RESUME
    CHECK -->|No| PAUSE

Logic App ARM Template:

{
    "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {
        "logicAppName": {
            "type": "string",
            "defaultValue": "la-fabric-capacity-scheduler"
        },
        "capacityResourceId": {
            "type": "string"
        }
    },
    "resources": [
        {
            "type": "Microsoft.Logic/workflows",
            "apiVersion": "2019-05-01",
            "name": "[parameters('logicAppName')]",
            "location": "[resourceGroup().location]",
            "identity": {
                "type": "SystemAssigned"
            },
            "properties": {
                "definition": {
                    "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
                    "triggers": {
                        "Recurrence": {
                            "type": "Recurrence",
                            "recurrence": {
                                "frequency": "Day",
                                "interval": 1,
                                "schedule": {
                                    "hours": ["7", "20"],
                                    "minutes": [0]
                                },
                                "timeZone": "Eastern Standard Time"
                            }
                        }
                    },
                    "actions": {
                        "Get_Current_Hour": {
                            "type": "Compose",
                            "inputs": "@int(formatDateTime(utcNow(), 'HH'))"
                        },
                        "Check_Business_Hours": {
                            "type": "If",
                            "expression": {
                                "and": [
                                    {
                                        "greaterOrEquals": ["@outputs('Get_Current_Hour')", 7]
                                    },
                                    {
                                        "less": ["@outputs('Get_Current_Hour')", 20]
                                    }
                                ]
                            },
                            "actions": {
                                "Resume_Capacity": {
                                    "type": "Http",
                                    "inputs": {
                                        "method": "POST",
                                        "uri": "[concat('https://management.azure.com', parameters('capacityResourceId'), '/resume?api-version=2023-11-01')]",
                                        "authentication": {
                                            "type": "ManagedServiceIdentity"
                                        }
                                    }
                                }
                            },
                            "else": {
                                "actions": {
                                    "Pause_Capacity": {
                                        "type": "Http",
                                        "inputs": {
                                            "method": "POST",
                                            "uri": "[concat('https://management.azure.com', parameters('capacityResourceId'), '/suspend?api-version=2023-11-01')]",
                                            "authentication": {
                                                "type": "ManagedServiceIdentity"
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    ]
}

🔔 Step 4: Set Up Cost Monitoring and Budget Alerts¶

4.1 Azure Cost Management Integration¶

Configure Azure Cost Management to track Fabric spending:

flowchart TB
    subgraph Sources["Cost Data Sources"]
        FABRIC[Fabric Capacity]
        STORAGE[OneLake Storage]
        EVENT[Eventstream]
    end

    subgraph ACM["Azure Cost Management"]
        ANALYZE[Cost Analysis]
        BUDGET[Budgets]
        EXPORT[Cost Exports]
    end

    subgraph Alerts["Alert Actions"]
        EMAIL[Email Notifications]
        WEBHOOK[Webhook Triggers]
        AUTO[Automation Actions]
    end

    Sources --> ACM
    BUDGET --> Alerts

    style FABRIC fill:#4a90d9,stroke:#2c5282,color:#fff
    style BUDGET fill:#ed8936,stroke:#c05621,color:#fff

4.2 Create Cost Budgets¶

# Create monthly budget for Fabric resources
$budgetName = "budget-fabric-casino-poc"
$resourceGroup = "rg-fabric-casino-poc"
$monthlyLimit = 10000  # $10,000 monthly limit

# Define budget
$budget = @{
    "properties" = @{
        "category" = "Cost"
        "amount" = $monthlyLimit
        "timeGrain" = "Monthly"
        "timePeriod" = @{
            "startDate" = (Get-Date -Day 1).ToString("yyyy-MM-dd")
            "endDate" = (Get-Date).AddYears(1).ToString("yyyy-MM-dd")
        }
        "filter" = @{
            "dimensions" = @{
                "name" = "ResourceGroup"
                "operator" = "In"
                "values" = @($resourceGroup)
            }
        }
        "notifications" = @{
            "Actual_GreaterThan_50_Percent" = @{
                "enabled" = $true
                "operator" = "GreaterThan"
                "threshold" = 50
                "contactEmails" = @("finance@casino.com", "dataplatform@casino.com")
                "thresholdType" = "Actual"
            }
            "Actual_GreaterThan_80_Percent" = @{
                "enabled" = $true
                "operator" = "GreaterThan"
                "threshold" = 80
                "contactEmails" = @("finance@casino.com", "dataplatform@casino.com", "cto@casino.com")
                "thresholdType" = "Actual"
            }
            "Forecasted_GreaterThan_100_Percent" = @{
                "enabled" = $true
                "operator" = "GreaterThan"
                "threshold" = 100
                "contactEmails" = @("finance@casino.com", "cto@casino.com")
                "thresholdType" = "Forecasted"
            }
        }
    }
}

# Create budget via REST API
$subscriptionId = (Get-AzContext).Subscription.Id
$uri = "https://management.azure.com/subscriptions/$subscriptionId/resourceGroups/$resourceGroup/providers/Microsoft.Consumption/budgets/$($budgetName)?api-version=2023-05-01"

Invoke-AzRestMethod -Uri $uri -Method PUT -Payload ($budget | ConvertTo-Json -Depth 10)

4.3 Budget Alert Thresholds¶

4.4 Cost Analysis Queries¶

Use Azure Cost Management to analyze spending patterns:

// KQL Query: Fabric cost by service meter
CostManagementData
| where ResourceGroup == "rg-fabric-casino-poc"
| where ServiceName contains "Fabric"
| summarize TotalCost = sum(Cost) by MeterName, bin(UsageDate, 1d)
| order by UsageDate desc, TotalCost desc

// Top cost drivers
CostManagementData
| where TimeGenerated > ago(30d)
| where ResourceGroup == "rg-fabric-casino-poc"
| summarize
    TotalCost = sum(Cost),
    AvgDailyCost = avg(Cost)
    by MeterName
| order by TotalCost desc
| take 10

💳 Step 5: Implement Chargeback and Cost Allocation¶

5.1 Chargeback Model Design¶

flowchart TB
    subgraph Capacity["Fabric Capacity Cost: $8,400/month"]
        TOTAL[Total Monthly Cost]
    end

    subgraph Allocation["Allocation Methods"]
        CU[By CU Usage %]
        USER[By User Activity]
        WS[By Workspace]
        PROJ[By Project Tag]
    end

    subgraph Departments["Department Chargeback"]
        GAMING[Gaming Analytics<br/>40% = $3,360]
        COMP[Compliance<br/>25% = $2,100]
        MARKETING[Marketing<br/>20% = $1,680]
        FINANCE[Finance<br/>15% = $1,260]
    end

    TOTAL --> Allocation
    Allocation --> Departments

5.2 Tag-Based Cost Allocation¶

Apply Azure tags for cost tracking:

# Apply cost allocation tags to Fabric capacity
$tags = @{
    "CostCenter" = "CC-1001-DataPlatform"
    "Environment" = "Production"
    "Project" = "CasinoAnalytics"
    "Owner" = "DataPlatformTeam"
    "Department" = "IT"
}

$resourceId = "/subscriptions/$subscriptionId/resourceGroups/$resourceGroup/providers/Microsoft.Fabric/capacities/fabric-casino-poc"

Update-AzTag -ResourceId $resourceId -Tag $tags -Operation Merge

5.3 Workspace-Based Allocation Report¶

# Fabric Notebook: Generate Chargeback Report
# ============================================

from pyspark.sql.functions import col, sum as spark_sum, round as spark_round
from datetime import datetime
import pandas as pd

# Configuration
MONTHLY_CAPACITY_COST = 8400  # F64 monthly cost

# Workspace CU usage data (from Capacity Metrics)
# In production, read from exported metrics
workspace_usage = [
    ("casino-gaming-prod", "Gaming Analytics", 2800),
    ("casino-compliance", "Compliance", 1750),
    ("casino-marketing", "Marketing BI", 1400),
    ("casino-finance", "Finance Reporting", 1050),
    ("casino-dev", "Development", 700),
    ("casino-sandbox", "Sandbox/POC", 300),
]

df_usage = spark.createDataFrame(
    workspace_usage,
    ["workspace_name", "department", "cu_hours_used"]
)

# Calculate allocation
total_cu_hours = df_usage.agg(spark_sum("cu_hours_used")).collect()[0][0]

df_chargeback = df_usage \
    .withColumn("usage_pct", spark_round(col("cu_hours_used") / total_cu_hours * 100, 2)) \
    .withColumn("allocated_cost", spark_round(col("cu_hours_used") / total_cu_hours * MONTHLY_CAPACITY_COST, 2))

# Display chargeback report
print("=" * 70)
print(f"FABRIC CAPACITY CHARGEBACK REPORT - {datetime.now().strftime('%B %Y')}")
print("=" * 70)
print(f"Total Capacity Cost: ${MONTHLY_CAPACITY_COST:,.2f}")
print(f"Total CU Hours Used: {total_cu_hours:,}")
print("-" * 70)

df_chargeback.orderBy(col("allocated_cost").desc()).show()

# Summary by department
df_dept = df_chargeback \
    .groupBy("department") \
    .agg(
        spark_sum("cu_hours_used").alias("total_cu_hours"),
        spark_sum("allocated_cost").alias("total_cost")
    ) \
    .orderBy(col("total_cost").desc())

print("\nDEPARTMENT SUMMARY:")
df_dept.show()

# Export to CSV for finance
df_chargeback.toPandas().to_csv(
    f"Files/reports/chargeback_{datetime.now().strftime('%Y%m')}.csv",
    index=False
)

5.4 Chargeback Report Template¶

⚡ Step 6: Optimization Strategies¶

6.1 Compute Optimization¶

flowchart TB
    subgraph Compute["Compute Optimization"]
        direction TB
        RIGHT[Right-Size Capacity]
        SCHEDULE[Schedule Workloads]
        PARALLEL[Parallelize Jobs]
        CACHE[Use Caching]
    end

    subgraph Impact["Cost Impact"]
        direction TB
        I1["-30% with right-sizing"]
        I2["-50% with scheduling"]
        I3["-20% with parallelization"]
        I4["-15% with caching"]
    end

    RIGHT --> I1
    SCHEDULE --> I2
    PARALLEL --> I3
    CACHE --> I4

Right-Sizing Decision Matrix¶

6.2 Query Optimization for CU Reduction¶

# Fabric Notebook: Query Optimization Examples
# =============================================

# BAD: Full table scan
df_bad = spark.sql("""
    SELECT * FROM bronze.slot_transactions
    WHERE YEAR(event_timestamp) = 2024
""")

# GOOD: Partition pruning with proper predicate
df_good = spark.sql("""
    SELECT machine_id, event_type, coin_in, coin_out
    FROM bronze.slot_transactions
    WHERE event_date >= '2024-01-01' AND event_date < '2025-01-01'
""")

# BAD: Expensive window function on large dataset
df_bad2 = spark.sql("""
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_timestamp DESC) as rn
    FROM bronze.player_sessions
""")

# GOOD: Pre-filter before window function
df_good2 = spark.sql("""
    WITH recent_sessions AS (
        SELECT * FROM bronze.player_sessions
        WHERE event_date >= CURRENT_DATE - 30
    )
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_timestamp DESC) as rn
    FROM recent_sessions
""")

# BAD: Repeated expensive subqueries
# GOOD: Use temporary views or cache
df_base = spark.table("gold.player_metrics").cache()
df_summary = df_base.groupBy("tier").agg(...)
df_detail = df_base.filter(...)
df_base.unpersist()  # Release cache when done

6.3 Storage Optimization¶

# Fabric Notebook: Delta Lake Storage Optimization
# ================================================

from delta.tables import DeltaTable

# 1. OPTIMIZE: Compact small files (reduces read I/O and CU usage)
spark.sql("""
    OPTIMIZE bronze.slot_transactions
    WHERE event_date >= current_date() - 7
""")

# 2. VACUUM: Remove old files (reduces storage costs)
spark.sql("""
    VACUUM bronze.slot_transactions
    RETAIN 168 HOURS  -- 7 days retention
""")

# 3. Z-ORDER: Co-locate related data (improves query performance)
spark.sql("""
    OPTIMIZE bronze.slot_transactions
    ZORDER BY (machine_id, event_date)
""")

# 4. Check table statistics
df_stats = spark.sql("""
    DESCRIBE DETAIL bronze.slot_transactions
""")
display(df_stats.select(
    "numFiles", "sizeInBytes", "numPartitions"
))

# 5. Analyze column statistics for better query planning
spark.sql("""
    ANALYZE TABLE gold.player_summary
    COMPUTE STATISTICS FOR ALL COLUMNS
""")

6.4 Workload Scheduling¶

Schedule heavy workloads during off-peak hours:

# Data Factory Pipeline: Off-Peak Processing Schedule
# ====================================================

# Configuration in pipeline JSON
pipeline_config = {
    "name": "pl_nightly_etl_processing",
    "properties": {
        "activities": [
            {
                "name": "Bronze to Silver Transformation",
                "type": "SparkJob",
                "description": "Heavy transformation - scheduled for 2 AM"
            },
            {
                "name": "Gold Aggregations",
                "type": "SparkJob",
                "description": "Aggregate calculations - after Silver completes"
            },
            {
                "name": "OPTIMIZE Delta Tables",
                "type": "SparkJob",
                "description": "Storage optimization - end of batch window"
            }
        ]
    },
    "trigger": {
        "type": "ScheduleTrigger",
        "recurrence": {
            "frequency": "Day",
            "interval": 1,
            "startTime": "2024-01-01T02:00:00Z",  # 2 AM UTC
            "timeZone": "Eastern Standard Time"
        }
    }
}

🔄 Step 7: Burst Capacity and Smoothing¶

7.1 Understanding Burst Behavior¶

flowchart TB
    subgraph Normal["Normal Operation"]
        BASE[Baseline: 64 CUs]
    end

    subgraph Burst["Burst Scenario"]
        DEMAND[Sudden Demand: 128 CUs]
        BURST_POOL[Burst Pool: +64 CUs]
        SMOOTH[Smoothing Window: 24 hrs]
    end

    subgraph Result["Outcome"]
        OK[Workload Completes]
        DELAY[Subsequent Throttling]
    end

    BASE --> DEMAND
    DEMAND --> BURST_POOL
    BURST_POOL --> SMOOTH
    SMOOTH --> OK
    SMOOTH --> DELAY

7.2 Smoothing Behavior¶

7.3 Burst Monitoring Query¶

// KQL: Monitor burst usage patterns
CapacityMetrics
| where TimeGenerated > ago(7d)
| summarize
    AvgCU = avg(CUUtilization),
    MaxCU = max(CUUtilization),
    BurstEvents = countif(CUUtilization > 100)
    by bin(TimeGenerated, 1h)
| where MaxCU > 100
| order by TimeGenerated desc

💵 Step 8: Reserved Capacity vs Pay-As-You-Go¶

8.1 Cost Comparison¶

xychart-beta
    title "F64 Monthly Cost: Reserved vs PAYG"
    x-axis ["Month 1", "Month 2", "Month 3", "Month 6", "Month 12"]
    y-axis "Cumulative Cost ($)" 0 --> 120000
    bar [8400, 16800, 25200, 50400, 100800]
    line [5880, 11760, 17640, 35280, 70560]

8.2 Break-Even Analysis¶

8.3 Decision Framework¶

flowchart TB
    START[Evaluate Capacity Needs] --> Q1{Usage >70%<br/>of month?}
    Q1 -->|Yes| Q2{Predictable for<br/>12 months?}
    Q1 -->|No| PAYG[Use PAYG<br/>+ Pause/Resume]
    Q2 -->|Yes| RESERVED[Purchase Reserved<br/>Save 30%]
    Q2 -->|No| HYBRID[Hybrid Approach]

    HYBRID --> H1[Reserved: Base load]
    HYBRID --> H2[PAYG: Burst capacity]

    style RESERVED fill:#48bb78,stroke:#276749,color:#fff
    style PAYG fill:#4a90d9,stroke:#2c5282,color:#fff
    style HYBRID fill:#ed8936,stroke:#c05621,color:#fff

🏢 Step 9: Multi-Workspace Capacity Sharing¶

9.1 Capacity Sharing Architecture¶

flowchart TB
    subgraph Capacity["F64 Fabric Capacity"]
        CU[64 Capacity Units<br/>Shared Pool]
    end

    subgraph Workspaces["Assigned Workspaces"]
        WS1[casino-prod<br/>Priority: High]
        WS2[casino-analytics<br/>Priority: Medium]
        WS3[casino-dev<br/>Priority: Low]
        WS4[casino-sandbox<br/>Priority: Low]
    end

    CU --> WS1
    CU --> WS2
    CU --> WS3
    CU --> WS4

    subgraph Behavior["Resource Allocation"]
        B1[High priority gets<br/>CUs first during contention]
        B2[Low priority may be<br/>throttled during peaks]
    end

9.2 Best Practices for Sharing¶

9.3 Workspace Assignment Script¶

# Assign workspace to Fabric capacity
$workspaceName = "casino-prod"
$capacityId = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

# Get workspace ID
$workspace = Get-PowerBIWorkspace -Name $workspaceName
$workspaceId = $workspace.Id

# Assign to capacity using Fabric Admin API
$body = @{
    "capacityId" = $capacityId
} | ConvertTo-Json

Invoke-PowerBIRestMethod `
    -Url "admin/groups/$workspaceId/AssignToCapacity" `
    -Method Post `
    -Body $body

✅ Validation Checklist¶

Before considering cost optimization complete, verify:

• Capacity Metrics App Installed - Dashboard accessible and showing data
• Pause/Resume Automation Deployed - Schedules verified and tested
• Budget Alerts Configured - At least 50%, 80%, and 100% thresholds set
• Chargeback Model Defined - Allocation method documented and agreed
• Query Optimization Applied - Top 10 queries reviewed for efficiency
• Storage Optimization Scheduled - OPTIMIZE and VACUUM in nightly jobs
• Workload Scheduling Implemented - Heavy ETL moved to off-peak
• Reserved Capacity Evaluated - Decision documented with ROI analysis

# Check if app is installed
Get-PowerBIApp -Name "Microsoft Fabric Capacity Metrics"

# Test capacity pause (during non-business hours)
.\Manage-FabricCapacity.ps1 -Action Status -SubscriptionId $subId -ResourceGroupName $rg -CapacityName $cap

# Verify automation schedules
Get-AzAutomationSchedule -ResourceGroupName $rg -AutomationAccountName $aaName

# List budgets
Get-AzConsumptionBudget -ResourceGroupName $rg

# Review query execution times
df_queries = spark.sql("""
    SELECT query_text, execution_time_ms, rows_scanned
    FROM system.query_history
    WHERE execution_time_ms > 60000  -- > 1 minute
    ORDER BY execution_time_ms DESC
    LIMIT 10
""")
display(df_queries)

🔧 Troubleshooting¶

Common Issues¶

Diagnostic Queries¶

# Fabric Notebook: Cost Optimization Diagnostics
# ==============================================

# 1. Check for runaway queries
print("Long-running queries in last 24 hours:")
spark.sql("""
    SELECT
        query_id,
        user_name,
        start_time,
        duration_seconds,
        rows_scanned,
        bytes_scanned
    FROM system.query_log
    WHERE start_time > current_timestamp() - INTERVAL 24 HOURS
    AND duration_seconds > 300
    ORDER BY duration_seconds DESC
    LIMIT 20
""").show(truncate=False)

# 2. Storage efficiency check
print("\nTables with many small files:")
for table in spark.catalog.listTables("bronze"):
    try:
        detail = spark.sql(f"DESCRIBE DETAIL bronze.{table.name}").collect()[0]
        if detail.numFiles > 1000:
            print(f"  {table.name}: {detail.numFiles} files, {detail.sizeInBytes/1e9:.2f} GB")
    except:
        pass

# 3. Unused tables (candidates for archival)
print("\nTables not accessed in 30+ days:")
spark.sql("""
    SELECT
        table_name,
        last_access_time,
        size_bytes / 1e9 as size_gb
    FROM system.table_access_history
    WHERE last_access_time < current_timestamp() - INTERVAL 30 DAYS
    ORDER BY size_bytes DESC
    LIMIT 20
""").show()

📌 Best Practices¶

Cost Governance¶

1. Establish Ownership - Assign cost owners to each workspace/project
2. Set Budgets Early - Configure alerts before heavy usage begins
3. Review Monthly - Schedule monthly cost review meetings
4. Document Decisions - Record reserved capacity and scaling choices
5. Train Users - Educate analysts on query efficiency

Optimization Priorities¶

Anti-Patterns to Avoid¶

• Over-provisioning - Starting with F256 when F64 would suffice
• Always-On Dev - Leaving development capacity running 24/7
• Ignoring Metrics - Not reviewing capacity utilization regularly
• Manual Operations - Pausing/resuming manually instead of automation
• Single-Tenant Thinking - Not considering multi-workspace sharing

📝 Summary¶

Congratulations! You have completed the Cost Management & Capacity Optimization tutorial. You have learned to:

• Understand Fabric capacity SKUs and Capacity Unit consumption
• Configure and use the Capacity Metrics app for monitoring
• Implement capacity pause/resume automation for significant savings
• Set up cost monitoring dashboards and budget alerts
• Design chargeback and cost allocation models for your organization
• Apply optimization strategies for compute, storage, and queries
• Evaluate reserved capacity vs. pay-as-you-go options
• Estimate costs for POC and production casino analytics deployments

🚀 Next Steps¶

Continue to Tutorial 16: Performance Tuning to learn advanced techniques for optimizing query performance, Spark job efficiency, and real-time analytics throughput.

📚 Additional Resources¶

• Microsoft Fabric Pricing
• Azure Pricing Calculator
• Capacity Metrics App Documentation
• Fabric Capacity Administration
• Azure Cost Management Best Practices
• Fabric Reserved Capacity
• Pause and Resume Fabric Capacity

🧭 Navigation¶

⬆️ Back to Top | 📚 Tutorials | 🏠 Home