How to find all reports\Dashboard that use a specified Power BI Data Source

[NMAN47]

We have a need to move our data sources from one gateway cluster to another gateway cluster due to an OS version compliancy. This is so that I can notify the owners of the reports to make the necessary changes ahead of the implementation date. In SSRS, you have an option to view all the dependant items for a specific data source but I am not finding an option to see all the dependant items (reports\dashboard) in Power BI. Is there such an option?

Thanks, Nimesh.

[v-yiruan-msft]

Hi @NMAN47 ,

As suggested by @lbendlin , you can view the data source information (include gateway) by the lineage view in every workspace. For more details, you can review the contents in the following links.

Looking at the Lineage View in Power BI

Introducing Power BI lineage view

Best Regards

Rena

[NMAN47]

Hi Rena, thank you for responding.

I am aware of the Lineage view but you would have to go through each workspace + Dashboard\reports in order to see the gateway and the data source being used. More like a bottom up approach (Workspace - Dashboard\Report - Data Set - Data source\Gateway). On top of that you can only see those workspaces where you have been given access.

What I am looking for is a Top down approach (Gatewaycluster\Data Source - Data Set - Dashboard\Report). As an admin who is maintaining the On-Prem Enterprise gateway host, I want to be able to see all reports and dashboards using data sources under a gateway cluster. And I am fine if I have to go through one data source at a time. This makes it easier to notify the users of the impacted reports and dashboards. So, is this option even available?

Hope that made sense. Thank you again.

Nimesh.

[admachiano]

I believe this can be done using the Power BI REST APIs. The APIs for the gateways require the user to have admin permissions on the gateway(s) in question but if you've created an Azure AD App Registration that has the service principal role, you should be able to use it to send the API calls.

Use Gateways - Get Gateways - REST API (Power BI Power BI REST APIs) | Microsoft Docs to get the gateway(s) which have the datasources you're concerned about.

Use Admin - Datasets GetDatasetsAsAdmin - REST API (Power BI Power BI REST APIs) | Microsoft Docs to get all the datasets in your tenant.

Use the results in the next call which is to get the datasources for each dataset. Filter the results to only match the gatewayId(s) that you identified when you got the gateway names in the first API call.

Admin - Datasets GetDatasourcesAsAdmin - REST API (Power BI Power BI REST APIs) | Microsoft Docs

If you need the datasource details, use Gateways - Get Datasources - REST API (Power BI Power BI REST APIs) | Microsoft Docs with the gatewayId(s) for the gateway(s) you're concerned with. If not, you can skip this.

Use Admin - Reports GetReportsAsAdmin - REST API (Power BI Power BI REST APIs) | Microsoft Docs with the dataset (not datasource) IDs to get the reports associated with those datasets. The users and their access grant (permission level, including owner) should be returned in the response but if not, run Admin - Reports GetReportsAsAdmin - REST API (Power BI Power BI REST APIs) | Microsoft Docs and use the reportIds to get the users.

You should be able to do this in Power Automate to get to the final list of users who need to be notified and then send the email as the final step in the flow.

This is just off the top of my head, there may be a better way to do this, I'm just spitballing.

[lbendlin]

No, and there can't be such a thing.  The presence of a gateway connection is a necessary but not a sufficient condition for that connection to be consumed.

You need to use the gateway logs if you want that kind of inventory. There's a template app somewhere but you need to adjust your data collection process if you have a gateway cluster.

https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-performance

This will not capture anyone who only does manual refreshes on their Power BI Desktop.

[lbendlin · Accepted solution]

Yes, the Lineage view in the Power BI service does exactly that.