Power BI Rest API - $PbiRestApi + "admin/datasets/" + $DatasetId + "/datasources"

[Anonymous]

I've been using Power BI Rest API to extract datasource information given a dataset id. It was working fine for the last 7 months. I had many attempts this week but it is erroring out after 300 iterations, we have ~ 1600 ids.

$GetDatasetDatasources = $PbiRestApi + "admin/datasets/" + $DatasetId + "/datasources"

[Madalina2801]

Hi,

I also found this post Solved: Re: Admin - Datasets GetDatasourcesAsAdmin request... - Microsoft Fabric Community. It seems the API limit was changed "Instead of queuing, the API now supports up to 300 requests/hour. " Admin API updates and upcoming definition changes  | Microsoft Fabric Blog | Microsoft Fabric

300 requests/hour is a very low limit, we have thousands of datasets, the API should work as it was before queuing the requests, otherwise it will take a very long time to extract the data...

[v-csrikanth]

Hi @Anonymous 
As highlighted by @Deku , the proposed approach appears to effectively address your requirements. Could you please confirm if your issue has been resolved.
If you are still facing any challenges, kindly provide further details, and we will be happy to assist you.

If the above information helps you, please give us a Kudos and marked the response Accept as a solution which resolve your issue.

Best Regards,
Community Support Team _ C Srikanth.

[Anonymous · Accepted solution]

Hi All,

The Scanner API didn't meet my needs, so I resolved the issue by running the admin API at 1-hour intervals to bypass the limits. The code below, broken down into six separate runs, worked for me. I hope this helps anyone facing a similar issue.

# Base API for Power BI REST API
$PbiRestApi = "https://api.powerbi.com/v1.0/myorg/"

# Define the run number
$Runnumber = 1

# Change the api based on run number
if ($Runnumber -eq 1)
{
$ApiCall = "/admin/datasets?%24top=300"
}
elseif ($Runnumber -eq 2)
{
$ApiCall = "/admin/datasets?%24top=300&%24skip=300"
}
elseif ($Runnumber -eq 3)
{
$ApiCall = "/admin/datasets?%24top=300&%24skip=600"
}
elseif ($Runnumber -eq 4)
{
$ApiCall = "/admin/datasets?%24top=300&%24skip=900"
}
elseif ($Runnumber -eq 5)
{
$ApiCall = "/admin/datasets?%24top=300&%24skip=1200"
}
else
{

$ApiCall = "/admin/datasets?%24top=300&%24skip=1500"
}

#=================================================================
# General tasks
#=================================================================
# Sign in to the Power BI Service using OAuth
Write-Host -ForegroundColor White "Sign in to connect to the Power BI Service";
Connect-PowerBIServiceAccount

#=================================================================## Dataset tasks
#=================================================================
# List all datasets
Write-Host "Collecting dataset metadata..."
$GetDatasetsApiCall = $PbiRestApi + $ApiCall
$AllDatasets = Invoke-PowerBIRestMethod -Method GET -Url $GetDatasetsApiCall | ConvertFrom-Json
$ListAllDatasets = $AllDatasets.value

#=================================================================
# Function to get dataset datasources results
#=================================================================

# Create empty json array
$DatasetResults = @()

# Get datasources for each dataset in defined workspace
$counter = 0
foreach($Dataset in $ListAllDatasets) {
$counter++
$DatasetHistories = GetDatasetDataSources -DatasetId $Dataset.id
foreach($DatasetHistory in $DatasetHistories) {
Add-Member -InputObject $DatasetHistory -NotePropertyName 'DatasetId' -NotePropertyValue $Dataset.id
$DatasetResults += $DatasetHistory
}
Write-Host "Incremented counter: $counter"
}

Function GetDatasetDataSources {
[cmdletbinding()]
param (
[parameter(Mandatory = $true)][string]$DatasetId
)
Write-Host "Collecting dataset datasources..." $DatasetId
$GetDatasetDatasources = $PbiRestApi + "admin/datasets/" + $DatasetId + "/datasources"
$DatasetDatasources = Invoke-PowerBIRestMethod -Method GET -Url $GetDatasetDatasources | ConvertFrom-Json
return $DatasetDatasources.value
}

[Deku]

Scanner API would take 2 minutes to run. Here is a powershell example

[Deku]

Forum post saying new limits that are not documented against the api

[Anonymous]

That post is from last year but I have been successfully running the API to iterate ~1600 datasets in 45 mins until this week,  where it is failing after 300 iterations. 

[Anonymous]

[Deku]

Would be helpful to know what error message you are getting 

[Anonymous]

[Deku]

I find this method to very slow. I would suggest using the scanner api.

You need to set datasourceDetails to true for data sources.

This is much quicker and only a handful of requests.

[Anonymous]

I would have to rewrite/test the powershell script to do that, have been using the script for more than 6 months without any issue, although it does take 45 mins to complete, did anything change with the api, throttle limits, etc.?