Auto scale the Power BI Embedded capacity using Job Scheduler in Azure
Power BI Embedded is a Microsoft Azure service that is useful for the ISVs and developers to embed visuals, reports and even dashboard into the application. As Power BI Embedded is a PaaS analytics solution which provide Azure based capacity, Power BI embedded charge customers on an hourly basis there are n annual commitment for the Power BI Embedded service.
As Power BI Embedded charges on hourly basis and there is no direct Auto Scaling feature available on Azure but, we do have API provided by using which we can scale the capacity. In this blog we are going to see how scale the Power BI Embedded capacity using PowerShell script.
Before going to start we’ll first quick list the set up the prerequisites:
- You will need an Azure account, if you are implementing the PowerShell script for your organisation then you must have co-administrator role assign kindly keep in mind that if you have contributor role assign then you’ll not be able to make Automation account.(we’ll see about the Automation account in the later part of this blog.)
- Power BI Embedded subscription.
- Automation Account.
I’m assuming you already have Azure account along with the subscription for the Power BI Embedded.
Steps:-
- Create Automation Account:- Automation account is use to manage the Azure resource across all the subscription for the given tenant. To create Automation click on the create resource in your Azure portal as shown below and search for Automation account.
Or you can type in search box Automation Account.
2. Click on create Automation Account and make sure to fill the following details. If you have multiple subscription then make sure to select proper subscription from drop-down. Make sure create Azure Run As account is selected to Yes (if you are co-administrator or administrator then it will by default selected to Yes). Once we create Azure automation account it will show under automation account.
3. Open the Automation account and go to the Connections and add below connection and types as shown below (Click on Add a connection and type the name and type as shown below)
4. For the AzureClassicRunAsConnection set the CertificateAssetName to AzureRunAsCertificate.
5. Add the Power BI Embedded subscription to your resource group.
6. Once we have Automation account ready go to the Runbooks under Process Automation in Automation Account. Runbook is useful for the routine procedures and operations. We can also use Azure Function app instead of Runbook.
7. Click on the Create a runbook and use fill following details.
8. Once we open runbook make sure to import the Module AzureRM.PowerBIEmbedded which can be installed by going to Module under Shared Resources then click on Browse gallery and search for the AzureRM.PowerBIEmbedded module.9. Use the below PowerShell script which can also be found on the Power BI discussion site.
$resourceGroupName = “<your resource group>”
$instanceName = “<Power BI embedded instance name>”
$azureProfilePath = “”
$azureRunAsConnectionName = “AzureRunAsConnection” #”PowerBIAutoscale”
$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]:Smiley TonguearseExact($_.StartTime, $fmt, $culture)}}, @{Name=”StopTime”; Expression={[DateTime]:Smiley TonguearseExact($_.StopTime, $fmt, $culture)}}
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 “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)”
# 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!”
10. Above script not includes Capacity pause, we can add that in the script.
11. Once we done with the script click on Save and the Publish the script.
12. Create the Schedule under the Shared Resources and click on the Add a schedule.
13. Once we create schedule go to the runbook and under Resources click on the Schedules and Add a schedule and set the recurring frequency as per the requirement.
14. Link the schedule to your runbook.
This way we can schedule autoscale for the Power BI Embedded capacity using PowerShell and Automation Account.