SPN + Azure Runbook + Webhook = Power BI Refresh (pt. 1)

I rececently read a great arcticle by Nicky Van Vroenhoven.

In his scenario Nicky has to fire Power BI dataset refresh after a specific step in SQL Server Agent’s job and this has to be done without using a real user’s credentials.

He made it creating a PowerShell script that logs-in and fires dataset refresh through an SPN.

After reading this I start thinking if I could encapsulate this in a different architecture that could be the entry point for many types of different applications.

The idea is to pack the PowerShell script into an Azure Automation Runbook and then make it available to all the applications that can simply perform a post request to an URI.

Advantages:

1. Only one centralized version of the script

2. Power BI management module is not installed on the application machine but it’s installed and executed in the cloud on a set of VMs provisioned on-demand when the execution starts

3. Runbook can be scheduled (maybe it’s not an adavantage, just an additional feature since we can already schedule a Power BI refresh)

4. Encrypted credentials support in order to make it work without knowing the SPN secret

5. Variables support to dynamically change the context of your refresh

6. Webhooks support to expose it as black box with a simple POST call

Let’s assume the following prerequisites already in place:

- SPN created and setup in AAD

- Enabled APIs use at Power BI tenant level for SPN

- Worspace V2 with SPN user as an admin

If this is already up and running the setup from now on it’s pretty easy, it could be done manually by Azure Portal but I prefer PowerShell with Az module, it’s quicker, easier to replicate and less “clicky-clicky” :-) (use PowerShell 7.x as suggested by MSFT since I found some bugs using it in PowerShell 5.1.X).

First of all setup parameters for resources creation:

$resource_group_name = '<my_rg_name>'
$automation_account_name = '<my_ac_name>'
$automation_account_location = '<my_ac_location>'
$runbook_name = '<my_rb_name>'
$tenant_id = '<my_pbi_tenant_id>'
$group_id = '<my_wks_id>' # Workspace ID
$dataset_id = '<dst_id>' # Dataset to Refresh ID
$app_id = '<my_app_id>' # Service PRincipal ID
$Password = ConvertTo-SecureString 'ThisIsMyPassword' -AsPlainText -Force # Secret from Service Principal

Then connect to the azure subscription

Connect-AzAccount

Create an Azure Automation Account (you can use an existing one if you want)

New-AzAutomationAccount -ResourceGroupName $resource_group_name -Location $automation_account_location -Name $automation_account_name

Create the Runbook that will host our PowerShell code (note the Type parameter equal to PowerShell)

New-AzAutomationRunbook -Name $runbook_name -ResourceGroupName $resource_group_name -AutomationAccountName $automation_account_name -Type PowerShell

Create 3 variables that will host values for IDs needed to refresh the dataset:

New-AzAutomationVariable -Name ‘TenantID’ -Description ‘Power BI Tenant ID’ -Value $tenant_id -AutomationAccountName $automation_account_name -ResourceGroupName $resource_group_name -Encrypted $falseNew-AzAutomationVariable -Name ‘GroupID’ -Description ‘Power BI Workspace ID’ -Value $group_id -AutomationAccountName $automation_account_name -ResourceGroupName $resource_group_name -Encrypted $falseNew-AzAutomationVariable -Name ‘DatasetID’ -Description ‘Power BI Dataset ID’ -Value $dataset_id -AutomationAccountName $automation_account_name -ResourceGroupName $resource_group_name -Encrypted $false

Store SPN secrets safely leveraging Azure Automation’s Credential feature:

$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $app_id, $passwordNew-AzAutomationCredential -name ‘SPN’ -Description ‘Service Principal’ -Value $Credential -AutomationAccountName $automation_account_name -ResourceGroupName $resource_group_name

Full code:

The environment is ready.

Just 2 manual steps to perform:

1. Import the MicrosoftPowerBIMgmt.Profile PowerShell module into the Azure Automation. This allows Azure Automation to use cmdlets contained in that module.

a. Open Azure Portal

b. Navigate to you Azure Automation Account

c. In the left blade, under “Shared Resources” click on “Modules Gallery”

d. Search for “MicrosoftPowerBIMgmt.Profile”

e. Select it and click “Import”

2. Add PowerShell script to perform Power BI refresh trough API calls

a. Open Azure Portal

b. Navigate to you Azure Automation Account

c. In the left blade, under “Process Automation” click on “Runbooks”

d. Select Runbook created during environment setup

e. Click Edit

f. Paste the following code in

g. Save and publish

This code it’s very similar to Nicky’s one, but the key difference is how it setup itself getting Power BI ids from variables we created and the most important thing is how it gets the SPN securely stored directly into the automation account. In other words, developers can leverage that SPN for as many scripts/Runbooks they want without the need to the secret.

Now you can hit that “Start” button to make the Runbook run.

As soon as you see the success on Azure Portal you can double check via PoweerShell or Power BI Portal.

At the end of the day we created an “Azure Based Black Box” that refreshes dataset in Power BI using an SPN.

In part 2 we’ll try to make it available to all the applications that can perform post request to an URI leveraging the Webhook functionality.

github repo: https://github.com/R1k91/PowerBIRefreshRunbook

--

--

--

Container of my adventures wrangling data

Recommended from Medium

Flutter at IO Extended Kuala Lumpur 2019 — Follow-up

What to choose, What not to choose!

The Good, the Bad and the Ugly

Deploy React app on AWS S3 and setting up Cloudfront

Build a Jobs Database using Indeed’s API

Are We Slowly Turning into Robots?

Introduction to EC2

Mistakes that I made to BE a beginner

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Perico Riccardo

Perico Riccardo

BI & Power BI Engineer since 2010 — Data and me in a complicated relationship — Hard Rock and Heavy Metal addicted

More from Medium

Create a Custom Connector for Consumption Logic Apps using the Azure Portal

Not Only Kibana: Using PowerBI to Visualize Elasticsearch Data

Introduction to PowerBI Custom Visual Development

Azure Pipelines catch invalid PBIX file versions