How to Automate Power BI Work? Power BI REST API

Spread the love

In my work as a data and analytics consultant, there was a case where a client had a lot of workspaces with assigned Premium capacity and wanted to know if they all needed it. If not, they wanted to unassign capacity and save resources. As you may know, Premium capacities are expensive; thus, you need to be conscious of their allocation. So, we suggested I write them some scripts using Power BI REST API and identify the workspaces that don’t use Premium features and, later, unassign such workspaces from the capacity in one go.

You may wonder, why use Power BI REST API for this task? The answer is easy because there is no automated functionality for this in Power BI Service. Without Power BI REST API, you would need to check all workspaces, in this case, a couple of hundreds, one by one and see if they use any of the premium functionalities.

Power BI REST API Usage

According to MS documentation, Power BI REST APIs provide service endpoints for embedding, administration, governance and user resources. In my words, I would say that with it, you can get a lot of information about artefacts in the Power BI Service. That it would be easier to understand, I’m pasting a few examples of what you can do with it, and later, I’ll show you how to use it.

With Power BI REST API, you can do the following:

Manage Power BI content (e.g., delete dashboard in a workspace, clone or delete a report, transfer ownership of the data sources for the specified paginated report, etc.),
Perform admin operations (e.g., assign Premium capacity to a workspace, set sensitivity labels on Power BI items, create a new datasource for a gateway etc.),
Embed Power BI content.

Now that you have a general understanding of what can be done with Power BI REST API, you must be wondering how to start using it.

For Embedding Power BI Content

Use the Power BI Embedded Azure Resource Manager REST APIs to manage Power BI Embedded capacities.

Where to Start?

First, you need to know Premium features and then search for the APIs that can give you such information. The list of features can be found in MS documentation. Moreover, you need to find a way to unassing multiple workspaces from Premium.

The APIs that could help (list can change because new APIs can be added):

If you have multiple Premium capacities, it could be useful to get a capacity name:
- Admin – Get Capacities As Admin: Returns a list of capacities for the organisation.
You’ll need all Premium workspaces and their users in your Power BI tenant. You need users and their emails to warn them that their workspaces won’t be Premium anymore. There is only an API to get all workspaces, but you can filter which ones are Premium:
- Admin – Groups GetGroupsAsAdmin: Returns a list of workspaces for the organisation.
Unfortunately, not a lot of Premium functionalities could be identified with the help of Power BI REST API, but this is what I found:
1. See if Linked Entities are used:
  - Admin – Dataflows GetUpstreamDataflowsInGroupAsAdmin: Returns a list of upstream dataflows for the specified dataflow.
  - Admin – Dataflows GetDataflowUsersAsAdmin: Returns a list of users with access to the specified dataflow.
2. If a report is refreshed more than 8 times per day:
  - Admin – Get Refreshables For Capacity: Returns a list of refreshables for the specified capacity to which the user has access. Power BI retains a seven-day refresh history for each dataset, up to a maximum of sixty refreshes.
3. If Deployment Pipelines are used:
  - Admin – Pipelines GetPipelinesAsAdmin: Returns a list of deployment pipelines for the organisation.
  - Admin – Pipelines GetPipelineUsersAsAdmin: Returns a list of users with access to a specified deployment pipeline.
4. If Paginated Reports are created. Again, the same case as with Premium workspaces, API gives you all reports, but you can filter out paginated. Moreover, you use the same API to know the workspaces in which these paginated reports are located:
  - Admin – Reports GetReportsAsAdmin: Returns a list of reports for the organisation.
When you know which Premium workspaces need to be unassigned, you can use this API:
- Admin – Capacities UnassignWorkspacesFromCapacity: Unassigns the specified workspaces from capacity.

Permissions

From APIs names, you could notice that all of them require admin permissions. So, you need to be a Power BI Service administrator or have an Azure service principal* with the correct permissions.

*To read more about Azure service principals, go here.

How to Use These APIs?

Why Do You Need PowerShell?

In order to use Power BI REST APIs for Power BI admin tasks automation, you need a convenient “place” to run the scripts. One of these “places” can be PowerShell. It is often used for such cases because it provides a convenient way to automate tasks and manage Power BI resources programmatically.

Some specific reasons to use PowerShell for Power BI REST APIs include:

Easy to learn and use for those familiar with scripting.
Provides a large library of cmdlets to interact with REST APIs.
Can be used to manage multiple Power BI tenants and resources with a single script.
Offers a secure way to manage and automate Power BI tasks with certificates and secure credentials.

Of course, you can use another programming language as Python, #C or Java.

Disclosure

I'm not a PowerShell expert and used it only a few times. So, if something didn't work for me and you know how to make it work, please, let me know in the comments below.

How to Write PowerShell Scripts for Power BI REST APIs?

First, when you open PowerShell, you need to be able to run Power BI REST API scripts. For that, you need to install the “MicrosoftPowerBIMgmt” module. This module contains a set of cmdlets for managing Power BI resources, including reports, dashboards, datasets, and more, through the Power BI REST APIs. To do that, you need to run the code below, and the module will be installed on the machine where the PowerShell script is being run. You need to run it only once.

Install-Module -Name MicrosoftPowerBIMgmt

After that, you must sign in to the tenant and use a service principal credentials or an admin account.

#comments are written when starting with "#" symbol.
#tenant ID:
$TenantId = "paste_power_bi_tenant_id"

#fill in the client id and secret value
Connect-PowerBIServiceAccount -ServicePrincipal -Credential (Get-Credential) -Tenant $TenantId

#connect with an admin account, run the code below and fill in your credentials
Connect-PowerBIServiceAccount

Now you are ready to find out which Premium workspaces are using some of the Premium features, and those that are not will be unassigned from Premium capacity.

How to Use HTTP Code in PowerShell?

If you want to use Power BI REST APIs with PowerShell, you need to be able to use HTTP codes provided in the MS documentation. To illustrate it, I will show you how to use the Admin – Groups GetGroupsAsAdmin API to identify Premium Workspaces. In this case, the HTTP code looks like this:

GET https://api.powerbi.com/v1.0/myorg/admin/groups?$top={$top}

So, PowerShell code could look like this (not going into all the details):

#define variable
$WorkspacesUrl = "https://api.powerbi.com/v1.0/myorg/admin/groups?%24top=5000"
#get the results using "GET" after -Method and use above created variable
(Invoke-PowerBIRestMethod -Method GET -URL $WorkspacesUrl | ConvertFrom-JSON).Value

Sample Respond:

{
 "value": [
   {
    "id": "d5caa808-8c91-400a-911d-06af08dbcc31",
    "isReadOnly": false,
    "isOnDedicatedCapacity": false,
    "name": "Finace",
    "description": "Sample workspace",
    "type": "Workspace",
    "state": "Active",
    "hasWorkspaceLevelSettings": false,
    "users": []
   }
 ]
}

You can notice that with this code, you will get the first 5000 workspaces, and there is no indication if they are Premium. But if we look at the response, you can see the “isOnDedicatedCapacity” part. So you can filter it:

$PremWS = (Invoke-PowerBIRestMethod -Method GET -URL "https://api.powerbi.com/v1.0/myorg/admin/groups?%24filter=(isOnDedicatedCapacity eq true and state eq 'Active')&%24top=5000" | ConvertFrom-JSON).Value

Limitations

Don't forget that some of the requests have limitations. For example, "Admin - Groups GetGroupsAsAdmin" limitations are: "Maximum 50 requests per hour, per tenant. This call will also time out after 30 seconds to prevent adverse effect on the Power BI service".

Now, in the same manner, you need to get all other possible information.

Moreover, you could want to export data to, e.g., CSV. This can be done by:

#you need to change the path where the CSV file needs to be saved
$PremWS | Export-Csv -Path C:\Users\Odeta\Downloads\PremWS.csv -Delimiter "`t" -NoTypeInformation -Force

The last helpful thing I can show you is how to make a loop in PowerShell. For example, if I want to get admins of all workspaces since I want to contact them and notify them that I’m thinking about unassigning Premium from their workspaces, I need a loop. Why? Because I can see users only for one specific workspace, using https://api.powerbi.com/v1.0/myorg/admin/groups/{groupId}/users. Here I need to indicate a workspace ID.

#storing workspace IDs to variable $PremWSIds
$PremWSIds = $PremWS | Select id
#defining users variable
$users = @()
#doing loop
@(
   $PremWSIds | ForEach-Object { 
       # In URL using "$_" I can put different worspace ID in each loop step 
       $a = (Invoke-PowerBIRestMethod -Method GET -URL "https://api.powerbi.com/v1.0/myorg/admin/groups/$_/users" | ConvertFrom-JSON).Value | Select groupUserAccessRight, emailAddress, userType 
       # and I'm adding workspace ID, $_, to the variable $a
       $a | Add-Member -MemberType NoteProperty -Name "WorkspaceId" -Value $_
       $users += $a
   }
)
#last step - saving to CSV
$users | Export-Csv -Path C:\Users\Odeta\Downloads\PremWSUsers.csv -Delimiter "`t" -NoTypeInformation -Force

I won’t go into more detail about how I did every Power BI REST API request because this post is already quite long, but if you try to do something like this and have questions, let me know.

Here I wanted to make an introduction to Power BI REST API together with PowerShell and give you some examples and use case where one could use it.

Spread the love

Marc Lelijveld

2023-03-21 at 20:21

Excellent blog! Super useful for people that might be challenged to go down the same route and explore Poweshell and Power BI REST APIs.

Charlesmaymn

2023-07-09 at 02:12

Cool, I’ve been looking for this one for a long time

Jake

2023-07-29 at 22:18

Great post thanks. If I have more than 5000 workspaces how do I retrieve all of them and exclude personal workspaces and inactive workspaces and using premium capacity?

Odeta Jankaitienė
2023-09-03 at 12:46

Thanks, Jake!
First, for getting non-personal, active workspaces and for specific capacity, you can use filters in the URL. For example, if you’re using GetGroupsAsAdmin API, your URL could look like this:

"https://api.powerbi.com/v1.0/myorg/admin/groups?%24filter=(state eq 'Active' and type eq 'Workspace' and capacityId eq 'paste_capacity_id')&%24top=5000"

Here, with “state” you filter only active workspaces, with “type” – shared workspaces and “capacityId” is self-explanatory. 🙂

As for getting all workspaces, if you have more than 5000, you can add code to the loop “ForEach-Object” and use “top=5000”, “skip=5000” in the URL. Also, you can try to use PowerShell cmdlets, e.g., Get-PowerBIWorkspace -All.