Best Practices to prevent GetGroupsAsAdmin API timeout

[marinafuster]

Overview

This blog post presents a set of best practices aimed at assisting large-scale tenants in effectively using GetGroupsAsAdmin API. Recognizing the most common challenges related to timeouts and performance, in this post, we recommend a range of enhancements and viable alternatives to proactively mitigate such issues. Administrators using this API are strongly encouraged to review the optimization tips in this post. 

Using Expand

Expanding the content of the workspace using the 'expand' option is a common cause of timeouts, particularly when combined with filters. The information about workspaces can be expanded to include details about users, datasets, dataflows, dashboards, reports and workbooks.

If timeouts occur when using 'expand', consider utilizing other existing APIs to acquire the required information. For instance, admins can use GetGroupUsersAsAdmin or PostWorkspaceInfo (with the 'getArtifactUsers' option) instead of expanding for users.

Similarly, admins can use artifact specific APIs to query artifacts of specific types instead of using ‘expand’ to get artifacts in a workspace. Examples of artifact specific APIs are GetReportsAsAdmin, GetDashboardsAsAdmin, GetDataflowsAsAdmin and GetDatasetsAsAdmin, which now include the 'workspaceId' property to relate the artifact to a specific workspace returned by GetGroupsAsAdmin.

However, workbooks don’t have an artifact specific API to query from workspaces.  So, it’s OK to use “expand” option to get all workbooks in a workspace.

Example:

https://api.powerbi.com/v1.0/myorg/admin/groups?$top=100&$expand=workbooks

Limiting the number of workspaces

The GetGroupsAsAdmin API provides a wealth of information and flexibility. It allows users to get information about workspaces and various other PowerBI objects within the workspaces. The API also allows users to filter the results based on properties such as PowerBI objects, state of the workspace, etc.

Querying workspaces and artifacts in large tenants with thousands of workspaces and each having hundreds or thousands of artifacts can lead to lengthy query time resulting in timeouts. If such timeouts occur, try limiting the number of workspaces using the 'top' parameter.

Though the official documentation for GetGroupsAsAdmin specifies the max value allowed for 'top' parameter as 5000, users should critically think about the number of workspaces to be retrieved for a given use-case.

Example:

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

Using Workspace Filters

Filter usage is another important factor contributing to time-outs. Recently, we've optimized the following filters: 'type', 'state', 'name', and 'isOnDedicatedCapacity’. By utilizing these filters as proposed in the examples below, users can reduce the load on Power BI infrastructure, minimizing the likelihood of timeouts. Users can also combine these filters to further narrow down the query results.

Syntax:

$filter=specificFilter eq 'Value'

Examples:

Retrieve first 100 workspaces of type “Workspace” using 'top' and 'type' filters: 

https://api.powerbi.com/v1.0/myorg/admin/groups?$top=100&$filter=type eq ‘Workspace’ 

Retrieve first 100 workspaces attached to premium capacity using 'top' and 'isOnDedicatedCapacity' filters: 

https://api.powerbi.com/v1.0/myorg/admin/groups?$top=100&$filter=isOnDedicatedCapacity eq true 

Retrieve workspace by name using ‘name’ filter: 

https://api.powerbi.com/v1.0/myorg/admin/groups?$top=1&$filter=name eq ‘Sales Workspace’ 

Retrieve first 100 active app workspaces attached to a dedicated capacity using the combination of filters:

https://api.powerbi.com/v1.0/myorg/admin/groups?$top=100&$filter=state eq ‘Active’ and type eq ‘Workspace’ and isOnDedicatedCapacity eq true

End-to-End Example

Assuming there is a requirement to get all workspaces within a tenant that have no datasets, the direct API call to GetGroupsAsAdmin might take the form shown below, utilizing the 'expand' and 'filter' options. 

https://api.powerbi.com/v1.0/myorg/admin/groups?$top=100&$expand=datasets&$filter=(not datasets/any())

However, it's important to be mindful that with larger tenants, the above call could potentially result in timeouts due to the scale of the operation. As an alternative approach, the following example demonstrates an effective strategy that employs GetDatasetsAsAdmin and GetGroupsAsAdmin APIs without resorting to any 'expand' or 'filter' operations.

This method's core concept involves acquiring two distinct lists: one listing workspaces ids with datasets, using GetDatasetsAsAdmin API, and the other cataloging all workspaces through GetGroupsAsAdmin API. Subsequently, by excluding the content of the first list from the comprehensive list of all workspaces in a tenant, this approach sidesteps the utilization of expand and filter operations.

Connect-PowerBIServiceAccount

$workspacesWithoutDatasets = @()

Write-Output "Fetching workspace ids with datasets..."

# TODO: remember to include the throttling limits logic for each API (groups, users, reports, datasets, ...)

$workspacesWithDatasets = (Invoke-PowerBIRestMethod -Url 'admin/datasets' -Method GET | ConvertFrom-Json).value | Where-Object { $_.workspaceId -ne $null } | ForEach-Object { $_.workspaceId }

Write-Output "Finished getting workspace ids with datasets."

Write-Output "Fetching all workspaces"

$totalWorkspaces = @()

$currentWorkspaces = @()

$top = 100 # TODO: adjust number based on your needs

$skip = 0

$iterations = 0

do

{

Write-Output "Retrieving $top workspaces."

$url = "admin/groups?`$top={0}&`$skip={1}" -f ($top), ($skip + $top * $iterations)

# TODO: remember to include the throttling limits logic for each API (groups, users, reports, datasets, ...)

$currentWorkspaces = (Invoke-PowerBIRestMethod -Url $url -Method GET | ConvertFrom-Json).value

$totalWorkspaces += $currentWorkspaces

} while($currentWorkspaces.Count -ge $top)

$totalWorkspacesCount = $totalWorkspaces.Count

Write-Output "Total workspaces count is $totalWorkspacesCount"

foreach($workspace in $totalWorkspaces)

{

if ($workspacesWithDatasets -notcontains $workspace.id) {

$workspacesWithoutDatasets += $workspace

}

}

$workspacesWithoutDatasetsCount = $workspacesWithoutDatasets.Count

Write-Output "There are $workspacesWithoutDatasetsCount workspaces without datasets."

Final Thoughts

Timeouts in this resource-intensive Admin API are influenced by various factors such as workspace count, artifact types in 'expand', and filter complexity. We recommend testing the API to determine its limitations within your tenant's unique characteristics. For example, expanding all artifacts might trigger timeouts, but expanding them individually may not. Experimentation can reveal effective combinations, like moving a filter from GetGroupsAsAdmin to a corresponding artifact specific API.

Our goal is to ease challenges with GetGroupsAsAdmin. Your feedback helps us gauge the impact of these efforts.