Admin - Datasets GetDatasourcesAsAdmin request limit

[mrraut]

I have been using the 'Admin - Datasets GetDatasourcesAsAdmin' REST api (https://learn.microsoft.com/en-us/rest/api/power-bi/admin/datasets-get-datasources-as-admin) in our scripts for a while now and it has been working as expected, however, I have been getting error with Status code : 429. You have exceeded the amount of requests allowed in the current time frame and further requests will fail. Retry in xxxx seconds.

The error occurs after 30 odd requests.

Has anything changed recently on this api?

[samsonfrb · Accepted solution]

Hi all,
I got a reply from Microsoft about the issue:

We have confirmation about a new throttling limit on this specific API call to 50 calls per hour.

Product Group informs they are aware this new limit is affecting users and their actual implementations, for this reason they share this workaround in the meantime a solution is applied about the new throttling limit:

“While we work on refining the throttling limits for the GetDatasourcesAsAdmin API, we understand the importance of providing viable alternatives for our users. As a temporary workaround, we recommend leveraging our scanner APIs to access all data source information for your tenant. The scanner APIs offer a robust solution for retrieving datasets and related data source information.

For more information, please refer “-

https://learn.microsoft.com/en-us/rest/api/power-bi/admin/workspace-info-get-scan-result

I hope they align the final throttling limit with GetXXXUsersAsAdmin APIs (200 calls per hour) so that with have consistency with our scripts. Or that they implement an API that return all datasources of all semantic models of the tenant in a single call.

Using scanner API is more involved (asynchronous calls, complex Json result) requiring a longer implementation.

Best regards,

Frederick

[mrraut]

I am running the get dataset api only once. The api I am running multiple times is this (below) and no limitaions have been documented yet.
https://learn.microsoft.com/en-us/rest/api/power-bi/admin/datasets-get-datasources-as-admin

[mrraut]

That is getting the workspace information. The filtered result is <200 workspaces. There is no issue with that api and does not time out. Neither any other api in the same session. The issue is with the

datasouces = requests.get('https://api.powerbi.com/v1.0/myorg/admin/datasets/'+datasets_info['id']+'/datasources', headers=headers)

part of the code. This times out after 30 odd requests. We have about 1250 datasets and the api was successfuly able to fetch the datasource information for all those datasets till last month

[aj1973]

Hi @mrraut 

Nothing has changed in my knowledge and it shouldn't happen. maybe the process when you run the script is taking too long and causing the problem

Can you show me the Script please?

[mrraut]

Sure, here is the python script I have been running to get the information needed. This was working correctly till a few weeks ago, but I have been seeing the 429 error since yesterday. I run this every 2-3 months as needed.

######## Loading the required libraries

# import adal
import requests
# import os
import pandas as pd
from azure.identity import ClientSecretCredential
from time import sleep


tenant = 'xxx'
client = 'xxx'
client_secret = 'xxx' 
api = 'https://analysis.windows.net/powerbi/api/.default'

# Generates the access token for the Service Principal
auth = ClientSecretCredential(authority = 'https://login.microsoftonline.com/',
                                                        tenant_id = tenant,
                                                        client_id = client,
                                                        client_secret = client_secret)
access_token = auth.get_token(api)
access_token = access_token.token

headers = {'Authorization': f'Bearer {access_token}'}

workspacetable_id = []
workspacetable_name = []

workspace = requests.get('https://api.powerbi.com/v1.0/myorg/admin/groups?%24top=5000&%24filter=state%20eq%20%27Active%27%20and%20type%20eq%20%27Workspace%27&%24expand=users', headers=headers)

if workspace.status_code == 200:
    for workspace_info in workspace.json()['value']:
        
        workspacetable_id.append(workspace_info['id'])
        workspacetable_name.append(workspace_info['name'])


Workspace_Info = pd.DataFrame({
 	"Workspace ID":workspacetable_id,
 	"Workspace Name":workspacetable_name
})



dataset_id = []
dataset_name = []
dataset_url = []
dataset_owner = []
dataset_workspaceid = []
dataset_connection_details = []
dataset_datasource_id = []
dataset_gateway_id = []


datasets = requests.get('https://api.powerbi.com/v1.0/myorg/admin/datasets', headers=headers)
for datasets_info in datasets.json()['value']:
    datasouces = requests.get('https://api.powerbi.com/v1.0/myorg/admin/datasets/'+datasets_info['id']+'/datasources', headers=headers)
    
    #Deleted dataset ids are sometimes retained by PowerBI service, when you try to get datascource information with the above api, it errors out. The below if statement is to mitigate that situation
    if datasouces.status_code != 200:
        continue
    
    for datasources_info in datasouces.json()['value']:
        
        try:
            dataset_id.append(datasets_info['id'])
        except:
            dataset_id.append("")
        try:
            dataset_name.append(datasets_info['name'])
        except:
            dataset_name.append("")
        try:
            dataset_url.append(datasets_info['webUrl'])
        except:
            dataset_url.append("")
        try:
            dataset_owner.append(datasets_info['configuredBy'])
        except:
            dataset_owner.append("")
        try:
            dataset_workspaceid.append(datasets_info['workspaceId'])
        except:
            dataset_workspaceid.append("")
        try:
            dataset_connection_details.append(datasources_info['connectionDetails'])
        except:
            dataset_connection_details.append("")
        try:
            dataset_datasource_id.append(datasources_info['datasourceId'])
        except:
            dataset_datasource_id.append("")                
        try:
            dataset_gateway_id.append(datasources_info['gatewayId'])
        except:
            dataset_gateway_id.append("")                


Dataset_Info = pd.DataFrame({
 	"Dataset ID":dataset_id,
    "Dataset Name": dataset_name,
    "Dataset URL": dataset_url,
    "Dataset Owner": dataset_owner,
    "Workspace ID": dataset_workspaceid,
 	"Connection Details":dataset_connection_details, 
 	"Datasource ID":dataset_datasource_id,
 	"Gateway ID":dataset_gateway_id
})


Dataset_Info_Complete = pd.merge(Dataset_Info, Workspace_Info, on='Workspace ID', how='left')

Dataset = Dataset_Info_Complete[['Workspace Name','Dataset Name','Dataset URL','Dataset Owner','Connection Details','Dataset ID','Workspace ID','Datasource ID','Gateway ID']]

Dataset.to_excel('E:/Dataset Info.xlsx', sheet_name='PBI Reports',index=False)

[aj1973]

Ok, I have met this problem before but using Powershell scripts. Deleted Datasets in service can take a while to completly disappear and that could cause the problem. I saw in your script the condition when Dataset is deleted.... but I am not sure that the process in running the script takes all in effect or not.

Anyway here is my script if it can help you. Didn't use your condition and it works just fine

Get-PowerBIDataset -Scope Organization |
Foreach {
$dsID = $_.Id;
$dsName = $_.Name;
Get-PowerBIDatasource -DatasetId $dsID -Scope Organization |
Foreach {
[PSCustoMObject]@{

'WSID' = $wsId;
'WSName' = $wsName;
'DSID' = $dsID;
'DSName' = $dsName;
'SrcId' = $_.DatasourceId;
'SrcName' = $_.Name;
'DatasourceType' = $_.DatasourceType;
'ConnectionString' = $_.ConnectionString;
'ConnectionDetails.server' = $_.ConnectionDetails.server;
'ConnectionDetails.database' = $_.ConnectionDetails.database;
'ConnectionDetails.Url' = $_.ConnectionDetails.Url;
'GatewayId' = $_.GatewayId

}
}
}| ConvertTo-Json | Out-File -FilePath "D:\Logs\Inventaire\Datasources\PowerBI_Datasources_$(Get-Date -Format yyyyMMdd).json" -Encoding utf8 -Force

By the way, I use App Registration to connect so I don't go through the access token limitations.

[samsonfrb]

Hey Amine, I hope you are doing fine.

Get-PowerBIDatasource has the same problem, except that it takes a long time before throwing an exception:
+ CategoryInfo : WriteError: (Microsoft.Power...werBIDatasource:GetPowerBIDatasource) [Get-PowerBIDatasource], TaskCanceledException
+ FullyQualifiedErrorId : Une tâche a été annulée.,Microsoft.PowerBI.Commands.Data.GetPowerBIDatasource

Frederick

[aj1973]

Hello Frederick, mon ancien collègue non! Oui je vais bien et toi! je dois admettre que j'ai appris beaucoup de chose de toi 😉.

Alors: Is this an info you wanted to share with me regarding the API or an issue you want to understand?

[samsonfrb]

Hi Amine,

Doing great, thanks. It was not a question but an information.

Both 'Get-PowerBIDatasource' CmdLet and 'Admin - Datasets GetDatasourcesAsAdmin' REST API fail after 50 calls in the same hour. Before January 28 2024, both worked fine. 

Frederick

[karo]

Hi, I am experience similar issues since 31st of January 2024. Have you find any solution?

[mrraut]

I ended up adding a 60 min sleep time in the loop. Took me 2 days to go through all the datasets in our tenant.

if datasouces.status_code == 429:
                print(len(set(dataset_id)), "datasets out off",total_datasets,"scanned. Encountered 429 error. Sleeping for 3600 secs")
                sleep(3601)
                auth = ClientSecretCredential(authority = 'https://login.microsoftonline.com/',
                                                        tenant_id = tenant,
                                                        client_id = client,
                                                        client_secret = client_secret)
                access_token = auth.get_token(api)
                access_token = access_token.token
                headers = {'Authorization': f'Bearer {access_token}'}
                datasouces = requests.get('https://api.powerbi.com/v1.0/myorg/admin/datasets/'+datasets_info['id']+'/datasources', headers=headers)

[samsonfrb]

Good workaround,

Problem is that people keep adding/modifying/deleting semantic models.

We need a solution where we have a clear picture every 24h.

For reports/apps/dashboards/semantic models permissions, the limit is 200 calls/hour which is OK to crawl all permissions every 24h.
But this (around 50) limit per hour is not acceptable.
The Microsoft support team reached out to the product team. I gave this forum as additional information so that they know that it affects others organizations too.
I'll update this forum when I get news.

Frederick

[aj1973]

Please let us know when you get the reply from MSFT on the matter.

Thanks Fred

[BiLukJak]

Seems like there is implemented limitation on number of calls for that api. All my jobs were running perfectly fine until 28/01. After that it works only if reduce calls below 50 but when I try to rerun on different scope than it is getting stuck. This is the same for two different tenants in two different regions. Microsoft, fix it or update documentation.