cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chrishiggins29
Regular Visitor

REST API call to get datasource used in Power BI Report

Hi!

I'm new to Power BI and have a question regarding REST API:

I was able to connect to a postgreSQL database server via ODBC driver and created a report with the help of Power BI Desktop. Then I published the report. Now I would like to find out via an API call, what database, schema, table (even columns?) were used in this report.

So something like

GET https://api.powerbi.com/v1.0/myorg/reports/879445d6-3a9e-4a74-b5ae-7c0ddabf0f11/pages/Sheet1/metadata

 which I would expect to return eg. (obviously in JSON 😉)

databaseintl_customer
schemaUS
tablecustomer
columncity
columnstate
columncreditline

 

Is there a way to get this information?

@SpartaBI
@lbendlin 
@dm-p 

6 REPLIES 6
lbendlin
Super User
Super User

You are mixing up a couple of concepts here.  Databases and schemas are used in datasets, not in reports.

 

Anyway, here is the official API documentation Reports - REST API (Power BI Power BI REST APIs) | Microsoft Docs

Feel free to request additions for features that you think are useful. If this is important to you please consider raising it at https://ideas.powerbi.com

 

Failiarize yourself with XMLA endpoint tools like Tabular editor, DAX Studio, Bravo, Power BI Helper, ALM Toolkit etc. They go more into the details of metadata.

 

The "what is used"  question is very multifaceted.  You need to be more specific on what you want to achieve. Reduce cost? Improve Performance? Improve maintenance?  Depending on your focus the question "what is used" may or may not be relevant at all.

@lbendlinThank you so much for your answer!

Sorry, if I wasn't precise enough. Still a greenhorn, but let me clarify:

[see attached screenshot] in a published Power BI report on "Page 1" I use the customer table, and 4 columns (ie. city, creditlimit, zip code and state) which I would like to request through some API call. I was able to retrieve everything else I was interested in, like project, dataset and report (which are grouped in a project) but have hard times to leverage the (meta)data used in a report.

The very reason I would like to access this information is for lineage purpose only. Key takeaway should be that I'd like to understand what tables/columns/etc are being used most in my reports. Granted, right now I'm testing with one report but in the future I need to know what the most "valuable" assets are across all reports.


Above: Published Power BI report showing used page, table, and columnsAbove: Published Power BI report showing used page, table, and columns

What's a "project" ?  Did you mean a workspace?

 

Your screenshot shows the field selection for a visual, not for a page. 

 

For Lineage research you may want to use Tabular Editor and Power BI Helper.

 

Be aware that there are functions like "Analyze in Excel" which may be impacted by your decisions.

 

I want to pivot your attention back to the "cost"  aspect.  What is the cost of a certain column in your data model? What is the storage cost? What is the performance cost? What is the cost (in terms of user satisfaction) of not having the column available in the data model?  etc.

Thanks again, @lbendlin !

My bad - "project" term is from previous work with Tableau API 😄. You're absolutely correct, I meant workspace.

 

My guess is, there're no API calls to investigate further into reports, pages or visuals then? That's a bummer. Please pardon my ignorance but isn't the purpose of a BI tool to be also capable of reporting data assets and their impact and frequency of use?

 

In order to evaluate costs - or better - importance of my data assets it would make sense to be able to determine what assets have been used the most in reports/visuals. This would benefit data analysts to make a better decision of maintaining these assets in particular. Needless to say this needs to be exposed as an API endpoint of some sort.

 

Seems I would have to go the ideation route for a feature that should already be available.

"but isn't the purpose of a BI tool to be also capable of reporting data assets and their impact and frequency of use?"

 

The primary purpose of a BI tool (by a wide margin)  is to support the business decision making process - provide users with the data and UX that helps them draw information out of the data, get business insights, and make decisions.  What you describe is a distant second or third purpose, more a focus of making life easier for the developers and IT support teams. Don't optimize things that don't need optimizing.

IMHO this is a missing feature, so I created a feature request:

https://ideas.powerbi.com/ideas/idea/?ideaid=f77ffb4d-e9d6-ec11-bea1-281878bd96c0

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors