Forum Discussion
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 😉)
| database | intl_customer |
| schema | US |
| table | customer |
| column | city |
| column | state |
| column | creditline |
Is there a way to get this information?
SpartaBI
lbendlin
dm-p
6 Replies
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 columnsWhat'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.
