Admin - Datasets GetDatasetToDataflowsLinksInGroupAsAdmin - Avoiding Hitting API Call Limit

TravisKale — Tue, 09 Feb 2021 23:24:33 GMT

I am using the Admin - Datasets GetDatasetToDataflowsLinksInGroupAsAdmin API to get the dataflows used by datasets. The problem I am running into is the only way to get this data is by workspace. I have over 3K workspaces, the API only allows 200 calls per hour for this endpoint. Is the API really this short sighted to not allow a broader scope to get this data? With this limitation it would take over 15 hours total time to get all the data I need. Not only will it take more time, it will require more complicated code to handle the stopping and resuming after the call limit has been exhausted. I would think this endpoint should be setup more like Admin - Datasets GetDatasourcesAsAdmin where there is no limit to calls per hour but rather the API processes each request for 0.5 seconds, in the mean time other requests will be queued.

I am assuming I am out of luck until Microsoft realizes the shortsightedness of this limitation but just in case.... anyone know of a way around this issue?

Re: Admin - Datasets GetDatasetToDataflowsLinksInGroupAsAdmin - Avoiding Hitting API Call Limit

TravisKale — Wed, 10 Feb 2021 14:06:44 GMT

I have figured out a hack around this issue. I call it a hack because it will only work if you happen to have less than 200 workspaces with datasets that use a dataflow as a datasource, if you have more than 200 workspaces in this criteria there is still an issue with the API call limit.

The powershell code below demonstrates the workaround. You need to check the connection details of a datasource. If the datasourceType is equal to "Extension" and the connectionDetails contains "path": "PowerBI" then the dataset uses a dataflow as a source, if so I add the workspace the dataset lives into an array. I then loop through that array looking for DatasetToDataflowLinks instead of all workspaces in my environment. This script takes quite a while to run from the numerous calls to the API since there is no way to eager load all DatasetDatasources in one call either just like the DStoDFLinks. I don't check the full connection details, if you want to, the JSON looks like this for datasources that are dataflows: { "path": "PowerBI", "kind": "PowerBI" }

$datasetDatasources = @() $WorkspaceIdsWithDatasetsThatUseDataflows = @() $workspaceDatasets = @() Login-PowerBI $workSpaces = Get-PowerBIWorkspace -Scope Organization -Include Datasets -All # -Filter "isOnDedicatedCapacity eq true" $workSpaces | Add-Member -MemberType AliasProperty -Name WorkspaceId -Value id ForEach($workSpace in $workSpaces) { ForEach($dataSet in $workspace.datasets) { $dataSet | Add-Member -NotePropertyName WorkspaceId -NotePropertyValue $workSpace.id $dataSet | Add-Member -MemberType AliasProperty -Name DatasetId -Value id $workspaceDatasets+=$dataSet } } ForEach($dataSet in $workspaceDatasets) { $datasourceApiUrl = "https://api.powerbi.com/v1.0/myorg/admin/datasets/" + $dataset.id + "/datasources" $apiDatasourceResult = Invoke-PowerBIRestMethod -Url $datasourceApiUrl -Method Get | ConvertFrom-Json $datasources = $apiDatasourceResult.value $datasources | Add-Member -NotePropertyName DatasetId -NotePropertyValue $dataset.Id $datasources | Add-Member -NotePropertyName WorkspaceId -NotePropertyValue $dataset.WorkspaceId $datasources | Add-Member -NotePropertyName ConnectionDetailsJson -NotePropertyValue "" ForEach($dataItem in $datasources) { $dataItem.ConnectionDetailsJson = ConvertTo-Json $dataItem.connectionDetails If ($dataItem.datasourceType -eq "Extension" -and $dataItem.ConnectionDetailsJson -like '*"path": "PowerBI"*' -and $WorkspaceIdsWithDatasetsThatUseDataflows -notcontains $dataset.WorkspaceId) { $WorkspaceIdsWithDatasetsThatUseDataflows += $dataset.WorkspaceId } } $datasetDatasources += $datasources } if($WorkspaceIdsWithDatasetsThatUseDataflows.Count > 200) { throw "Too many Workspaces with Datasets Using Dataflows to Fit in to this hack around the API call limit of 200" } ForEach($ws in $WorkspaceIdsWithDatasetsThatUseDataflows) { $ApiUrl = "https://api.powerbi.com/v1.0/myorg/admin/groups/" + $ws + "/datasets/upstreamDataflows" $apiDfLinksResult = Invoke-PowerBIRestMethod -Url $ApiUrl -Method Get $apiDfLinksObjectResult = $apiDfLinksResult | ConvertFrom-Json $upStreamFlows = $apiDfLinksObjectResult.value ForEach($up in $upStreamFlows) { $up | Add-Member -NotePropertyName DatasetWorkspaceId -NotePropertyValue $ws $up | Add-Member -MemberType AliasProperty -Name DatasetId -Value datasetObjectId $up | Add-Member -MemberType AliasProperty -Name DataflowId -Value dataflowObjectId $up | Add-Member -MemberType AliasProperty -Name DataflowWorkspaceId -Value workspaceObjectId $workspaceDatasetToDataflowLinks += $up } } $workspaceDatasetToDataflowLinks | Export-CSV -NoTypeInformation -Force -Path .\DatasetToDataflowLinks.csv $workSpaces | Export-CSV -NoTypeInformation -Force -Path .\Workspaces.csv $workspaceDatasets | Export-CSV -NoTypeInformation -Force -Path .\Datasets.csv $datasetDatasources | Export-CSV -NoTypeInformation -Force -Path .\DatasetDatasources.csv

Re: Admin - Datasets GetDatasetToDataflowsLinksInGroupAsAdmin - Avoiding Hitting API Call Limit

v-lionel-msft — Fri, 12 Feb 2021 02:48:14 GMT

Hi @TravisKale ,

Thank you for your sharing and you could submit your idea .

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

topic Re: Admin - Datasets GetDatasetToDataflowsLinksInGroupAsAdmin - Avoiding Hitting API Call Limit in Developer

Admin - Datasets GetDatasetToDataflowsLinksInGroupAsAdmin - Avoiding Hitting API Call Limit

Re: Admin - Datasets GetDatasetToDataflowsLinksInGroupAsAdmin - Avoiding Hitting API Call Limit

Re: Admin - Datasets GetDatasetToDataflowsLinksInGroupAsAdmin - Avoiding Hitting API Call Limit