Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Yashwanth_G
New Member

how to find out sql server tables used in the reports of power bi report server

I want to review BI reports to get database, schema , table names and queries which was used to create the report.
The reports are in power BI Report Server

 

is there a way to check these details manually ? (without using power query tool)

Thanks in advance 

1 ACCEPTED SOLUTION
Shahfaisal
Solution Sage
Solution Sage

The best way is to download the report and look at the code in Power BI Desktop. If you don't have that option, you can try the below:

1. If you are uding Direct Query: Setup a trace in SQL Server to see what tables are being queried when users are interacting with the report.

2. If you are using Import Mode: Setup a trace in SQL Server to see what tables are being queried when the data refresh is taking place.

Again, best is to download the file and look at the code behind it.

View solution in original post

4 REPLIES 4
Askrr
New Member

I've recently ran into this same issue (400+ reports and 300+ tables in my workspace) and couldn't find a simple solution to find which reports are utilizing which tables. I wrote a simple Python script to help with this. I posted it on my GitHub and hope that someone else that searches these forums finds it useful. I have directions for it in the README. 

https://github.com/Askrr/PBI_tableLookUp/tree/main

jquintero
New Member

I have a similar problem but I need a solution that allows me to find this information on many Power BI reports at the same time. 

This is the use case:

We have about 50 Power BI reports using multiple datasets. Most of them where built using SQL queries pulling data from a MS SQL server. IT advised us there will be a change in one of the systems which will affect how we are calling some of the tables we are using. So we need to know which Power BI dashboards will be impacted.

I know I can open one by one and verify but there has to be a more efficient way. I know the PBIX files are zip files and the mqueries are in there, but how can I search thorugh all the 50 pbix files automatically?

 

Thanks in advance,

 

Jorge Quintero

I've recently ran into this same issue (400+ reports and 300+ tables in my workspace) and couldn't find a simple solution to find which reports are utilizing which tables. I wrote a simple Python script to help with this. I posted it on my GitHub and hope that someone else that searches these forums finds it useful. I have directions for it in the README. 

https://github.com/Askrr/PBI_tableLookUp/tree/main

Shahfaisal
Solution Sage
Solution Sage

The best way is to download the report and look at the code in Power BI Desktop. If you don't have that option, you can try the below:

1. If you are uding Direct Query: Setup a trace in SQL Server to see what tables are being queried when users are interacting with the report.

2. If you are using Import Mode: Setup a trace in SQL Server to see what tables are being queried when the data refresh is taking place.

Again, best is to download the file and look at the code behind it.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.