Unanswered

Power BI API

(0) Share

Report

Posted on by srduval

1,796 Moderator

Is it possible via the API to drill into a paginated report and/or a PBIX report datasets to get to the source SQL object?

Most of our reports are sourced by stored procedures in an azure SQL database, so I am trying to be able to build a glossary of sorts to be able to be able to have the report name, and workspace listed along side the SQL objects that are used within them.

so report MyTasks.rdl is in the DailyWork Workspace and uses the the dbo.usertasks()

I want to be able to get that data with the API so I can populate a sharepoint list with the data.

Categories:

General topics

All responses (5)

Answers (0)

Sort by

lbendlin 8,726 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Possible via the Scanner API. I would not recommend trying to do this via Power Automate though. Use more robust tools.

Metadata scanning - Power BI | Microsoft Learn
srduval 1,796 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

thanks I'll check it out.
srduval 1,796 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Just tried running some tests through the API for a single workspace to analyze the output, and it appears to stop and the SQL database level.

So I get to

SQL Server: Server1

database: Database1

It doesn't tell me (at least not that i've found yet) that the source object of Table1 is exec server1.database1.dbo.Mystoredprocedure()

All the API parameters are set to true:

POST https://api.powerbi.com/v1.0/myorg/admin/workspaces/getInfo?lineage=true&datasetSchema=true&datasourceDetails=true&lineage=true&datasetExpressions=true

and all the metadata API settings are set to allow in the power bi admin center.
lbendlin 8,726 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Look at the "Tables" tab in the Power BI Tenant Scanner Report. It includes the data source expressions.

There is another way to do this via DMV queries (partitions and expressions) but that needs to be done separately by dataset. Not sure if that scales for you.
srduval 1,796 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

I must have had a parameter keyed in wrong, I rewrote the call from scratch and I was able to get the SQL query. Trying to set it up in power automate for testing purposes for now, since I already have the power BI API all configured in that environment. When I test the API in the manage connections, for the custom API, the start workspace scan works just fine. When I try to call it from a flow it fails giving me a "forbidden" message.

any ideas why it works in one place but not another?