Auto Scaling of Power BI embedded using ARM templates

Question

We are designing SaaS Azure-based application and for analytics visualization, we have decided to use Power BI Embedded.We can't predict the service load precisely so we would like to scale up or downsize of Power BI Embedded instances on demand.&nbsp;Now the question is how to do this correctly using ARM templates?When I go into Azure Portal Power BI Embedded configuration blade and switch "Automation script" section in the script there is a property called "scale": null. But looks like this property just ignored.What we figured out when we try to run deploy into existing resource(Power BI embedded) with different than current SKU name than actually, Power BI Embedded will start to scale up/down which is what we are trying to achieve. But the drawback is that the scripts end with an error, which is the undesirable outcome, as we can't distinguish the real error situation and if our scaling attempt was successful.New-AzureRmResourceGroupDeployment : 18:34:47 - Resource Microsoft.PowerBIDedicated/capacities 'ndaxxxx' failed with message '{
  "error": {
    "code": "ResourceDeploymentFailure",
    "message": "The response for resource had empty or invalid content."
  }
}'
At line:12 char:2
+     New-AzureRmResourceGroupDeployment -TemplateParameterFile $powerB ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [New-AzureRmResourceGroupDeployment], Exception
    + FullyQualifiedErrorId : Microsoft.Azure.Commands.ResourceManager.Cmdlets.Implementation.NewAzureResourceGroupDeploymentCmdlet
 
New-AzureRmResourceGroupDeployment : 18:34:47 - Template output evaluation skipped: at least one resource deployment operation failed. Please list deployment operations for details. Please se
e https://aka.ms/arm-debug for usage details.
At line:12 char:2
+     New-AzureRmResourceGroupDeployment -TemplateParameterFile $powerB ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [New-AzureRmResourceGroupDeployment], Exception
    + FullyQualifiedErrorId : Microsoft.Azure.Commands.ResourceManager.Cmdlets.Implementation.NewAzureResourceGroupDeploymentCmdlet
 
New-AzureRmResourceGroupDeployment : 18:34:47 - Template output evaluation skipped: at least one resource deployment operation failed. Please list deployment operations for details. Please se
e https://aka.ms/arm-debug for usage details.
At line:12 char:2
+     New-AzureRmResourceGroupDeployment -TemplateParameterFile $powerB ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [New-AzureRmResourceGroupDeployment], Exception
    + FullyQualifiedErrorId : Microsoft.Azure.Commands.ResourceManager.Cmdlets.Implementation.NewAzureResourceGroupDeploymentCmdlet
 
 =========================================================================================================
 DeploymentName          : Deploy_PowerBI
ResourceGroupName       : PowerBITest
ProvisioningState       : Failed
Timestamp               : 22.02.2018 16:34:25
Mode                    : Incremental
TemplateLink            : 
Parameters              : 
                          Name             Type                       Value     
                          ===============  =========================  ==========
                          env_name         String                     Dev       
                          powerbiinstanceName  String                     ndaxxxx
                          
Outputs                 : 
DeploymentDebugLogLevel :

Deployment PowerBI instance to Azure is finished

==============================================================================================================================
 #Deployment script

$resourceGroupName = "PowerBITest"

#PowerBI Deployment

$powerBIParameterFilePath =  'C:\Temp\azure_templates\powerbi_parameters.json'
    $powerBITemplateFilePath =  'C:\Temp\azure_templates\powerbi_template.json'

$newDeploymentName = "Deploy_PowerBI"
	Write-Host "Deploying Resources from Template - $powerBITemplateFilePath"
	New-AzureRmResourceGroupDeployment -TemplateParameterFile $powerBIParameterFilePath -TemplateFile $powerBITemplateFilePath -ResourceGroupName $resourceGroupName -Name $newDeploymentName
	Write-Host "Deployment PowerBI instance to Azure is finished"
 ##########&nbsp;

anonymous · Answer

donquijote&nbsp;Here you go.&nbsp; &nbsp;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.&nbsp;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!"

anonymous · Answer

I understand that we may face unavailability during scaling, but Power BI Embedded should support API as first class citizen.
It should be scalable via ARM template otherwise I do not understand why such service even exist in Azure.

anonymous · Answer

I have a PowerShell runbook scheduled in Azure that does what you are trying to do, but based on a schedule created by me in a JSON config object in the runbook.&nbsp; &nbsp;I haven't figured out yet how to check/test the current memory usage in our capacity yet though.&nbsp; &nbsp;I would love to make my runbook more of a true "auto-scale", but the scheduling mechanism works beautifully.

v-ljerr-msft · Answer

Hi&nbsp;Anonymous,
&nbsp;
A scaling operation can take about a minute. During this time, the capacity will not be available. Embedded content may fail to load. So this could be a limitation currently.&nbsp;:smileyhappy:
&nbsp;
Reference:
https://docs.microsoft.com/en-us/azure/power-bi-embedded/scale-capacity
&nbsp;
Regards

anonymous · Answer

Taking into account that in your response you even didn't tried to answer to my question and provided a guide for scaling PBI instances via portaI I assume that manipulating Power BI Embedded instances via ARM is a "grey" area which don't receive support from Power BI team at least at this moment.
Is it true?

Forum Discussion

Auto Scaling of Power BI embedded using ARM templates

8 Replies

Related content

Scale embedded sku level automatically - without lag?

PBI Template

Warehouse Scaling

Create Template App for AppSource with embedded data

PBI Embedded