Auto Scaling of Power BI embedded using ARM templates

donquijote Here you go.   FYI you might wonder about all the wacky crap around formatting the JSON timestamps to dates, but that's required in order to get the timezone set correctly for whereever your PBI capcity is hosted, as the Powershell scripts will be executing in the time zone of your host, and the Powershell SETTIMEZONE functions don't work in Azure.

param(     
    [string] $resourceGroupName = "io-reporting",
    [string] $instanceName = "ioreportingbi",
    #[string] $azureProfilePath  = "c:\~temp\AzureRMContext.txt",
    [string] $azureProfilePath  = "",
    [string] $azureRunAsConnectionName = "AzureRunAsConnection",
    [string] $configStr = "
    [
        {
            Name: ""Weekday Heavy Load Hours""
            ,WeekDays:[1,2,3,4,5]	
            ,StartTime: ""06:45:00""
            ,StopTime: ""23:45:00""
            ,Sku: ""A4""
        }
        ,
        {
            Name: ""Early AM Hours""
            ,WeekDays:[0,1,2,3,4,5,6]	
            ,StartTime: ""00:00:00""
            ,StopTime: ""04:44:00""
            ,Sku: ""A1""
        }
        ,
        {
            Name: ""Model Refresh""
            ,WeekDays:[0,1,2,3,4,5,6]	
            ,StartTime: ""04:45:00""
            ,StopTime: ""06:45:00""
            ,Sku: ""A3""
        }
        ,
        {
            Name: ""Weekend Operational Hours""
            ,WeekDays:[6,0]	
            ,StartTime: ""06:45:00""
            ,StopTime: ""18:00:00""
            ,Sku: ""A3""
        }    
    ]
"
)

$VerbosePreference = "Continue"
$ErrorActionPreference = "Stop"

Import-Module "AzureRM.PowerBIEmbedded" 

Write-Verbose "Logging in to Azure..."

# Load the profile from local file
if (-not [string]::IsNullOrEmpty($azureProfilePath))
{    
    Import-AzureRmContext -Path $azureProfilePath | Out-Null
}
# Load the profile from Azure Automation RunAS connection
elseif (-not [string]::IsNullOrEmpty($azureRunAsConnectionName))
{
    $runAsConnectionProfile = Get-AutomationConnection -Name $azureRunAsConnectionName      

    Add-AzureRmAccount -ServicePrincipal -TenantId $runAsConnectionProfile.TenantId `
        -ApplicationId $runAsConnectionProfile.ApplicationId -CertificateThumbprint $runAsConnectionProfile.CertificateThumbprint | Out-Null
}
# Interactive Login
else
{
    Add-AzureRmAccount | Out-Null
}

$fmt = "MM/dd/yyyy HH:mm:ss"   # format string
$culture = [Globalization.CultureInfo]::InvariantCulture

$startTime = Get-Date
Write-Verbose "Current Local Time: $($startTime)"
$startTime = [System.TimeZoneInfo]::ConvertTimeBySystemTimeZoneId($startTime, [System.TimeZoneInfo]::Local.Id, 'Eastern Standard Time')
Write-Verbose "Current Time EST: $($startTime)"
$scheduleTimeMidnight = ($startTime).Date
Write-Verbose "Schedule Time Base (Midnight): $($scheduleTimeMidnight)"
$currentDayOfWeek = [Int]($scheduleTimeMidnight).DayOfWeek
Write-Verbose "DOW: $($currentDayOfWeek)"

$stateConfig = $configStr | ConvertFrom-Json #| Select-Object Sku, WeekDays, Name, StartTime, EndTime #, @{Name="StartTime"; Expression={[DateTime]::ParseExact($_.StartTime, $fmt, $culture)}}, @{Name="StopTime"; Expression={[DateTime]::ParseExact($_.StopTime, $fmt, $culture)}}

Write-Verbose " "
Write-Verbose " "
Write-Verbose " "
Write-Verbose "Writing Config Objects..."
foreach($x in $stateConfig)
{
    Write-Verbose "Name: $($x.Name)"
    Write-Verbose "Weekdays: $($x.WeekDays -join ',')"
    $x.StartTime = ($scheduleTimeMidnight).AddHours([int]$x.StartTime.Split("{:}")[0]).AddMinutes([int]$x.StartTime.Split("{:}")[1]).AddSeconds([int]$x.StartTime.Split("{:}")[2])
    Write-Verbose "Start Time: $($x.StartTime)"
    $x.StopTime = ($scheduleTimeMidnight).AddHours([int]$x.StopTime.Split("{:}")[0]).AddMinutes([int]$x.StopTime.Split("{:}")[1]).AddSeconds([int]$x.StopTime.Split("{:}")[2])
    Write-Verbose "End Time: $($x.StopTime)"
    Write-Verbose " "
}
Write-Verbose " "
Write-Verbose " "

Write-Verbose "Getting current status..."
# Get the server status
$pbiService = Get-AzureRmPowerBIEmbeddedCapacity -ResourceGroupName $resourceGroupName

switch ($pbiService.State) {
    "Scaling" { Write-Verbose "Service scaling operation in progress...  Aborting." end  }
    "Succeeded" {Write-Verbose "Current Status: Running"}
    Default {Write-Verbose "Current Status: $($pbiService.State)"}
}

Write-Verbose "Current Capacity: $($pbiService.Sku)"
Write-Verbose " "
Write-Verbose " "

# Find a match in the config
$dayObjects = $stateConfig | Where-Object {$_.WeekDays -contains $currentDayOfWeek } 

# If no matching day then exit
if($dayObjects -ne $null){

    # Can't treat several objects for same time-frame, if there's more than one, pick first
    $matchingObject = $dayObjects | Where-Object { ($startTime -ge $_.StartTime) -and ($startTime -lt $_.StopTime) } | Select-Object -First 1

    if($matchingObject -ne $null)
    {
        Write-Verbose "Current Config Object"
        Write-Verbose $matchingObject.Name
        Write-Verbose "Weekdays: $($matchingObject.WeekDays -join ',')"
        Write-Verbose "SKU: $($matchingObject.Sku)"
        Write-Verbose "Start Time: $($matchingObject.StartTime)"
        Write-Verbose "End Time: $($matchingObject.StopTime)"

        # if Paused resume
        if($pbiService.State -eq "Paused")
        {
            Write-Verbose "The service is Paused.  Resuming the Instance"
            $pbiService = Resume-AzureRmPowerBIEmbeddedCapacity -Name $instanceName -ResourceGroupName $resourceGroupName -PassThru -Verbose
        }      
    
        # Change the SKU if needed
        if($pbiService.Sku -ne $matchingObject.Sku)
        {
            Write-Verbose "Updating Capacity Tier from $($pbiService.Sku) to $($matchingObject.Sku)"
            #Update-AzureRmPowerBIEmbeddedCapacity -Name $instanceName -sku $matchingObject.Sku
        }
    }
    else {
        Write-Verbose "No Interval Found.  Checking current capacity tier."
        if($pbiService.Sku -ne "A2")
        {
            Write-Verbose "No Interval Found.  Scaling to A2"
            Write-Verbose "Updating Capacity Tier from $($pbiService.Sku) to A2"
            #Update-AzureRmPowerBIEmbeddedCapacity -Name $instanceName -sku $matchingObject.Sku
        }
    }
}
else
{
    Write-Verbose "No Interval Found.  Checking current capacity tier."
    if($pbiService.Sku -ne "A2")
    {
        Write-Verbose "No Interval Found.  Scaling to A2"
        Write-Verbose "Updating Capacity Tier from $($pbiService.Sku) to A2"
        #Update-AzureRmPowerBIEmbeddedCapacity -Name $instanceName -sku $matchingObject.Sku
    }      
}

Write-Verbose "Done!"