How to find all tables used in .pbix?

[ovonel]

I have a .pbix that consumes LIVE from an SSAS cube, that has 50 tables.

The .pbix has 7 pages and several visuals/KPIs per page...

I need to know all the tables used in the .pbix (my guess is only 8 out of the 50 tables from the SSAS cube are used).

Is there a way of doing this; other than clicking every visual and seeing the "ticks" on the right?

[Askrr]

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

[Alyn]

Which cloud where you using? I'm using gcp. Do you think that this could be a good methodology to find does tables. The amount of dashboards I'm using is enormous.

[Askrr]

I run this locally, but it could be modified to point to cloud storage as well (gcp, aws, azure, etc) and do the same thing. However, you would need to have write permissions as part of your IAM policy in GCP if you were to use it that way. 

[Anonymous · Accepted solution]

Hi @ovonel ,

Use SQL Server Profiler can track the tables you used in power bi. But there is a premise that your SSAS database can be accessed through SQL Server .

(1)Refresh in power bi desktop ,the query will be sent to SSAS, so the SQL Server Profiler Will be record the query .(I have two visuals in the desktop)

(2)Select all events related to Query in the Events Selection and run .Like this

(3)Then you will get the details of the query, it will contain the name of the table related to the query .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataVitalizer]

Hi  @ovonel 

For this you would need to install DAXSTUDIO, once intalled try following these steps:

1. Launch your powerbi report
2. Launch DAXSTUDIO and select your model
3. Run this query 
   SELECT * FROM $System.DBSCHEMA_TABLES WHERE TABLE_TYPE='SYSTEM TABLE'

Did it work ? Mark it as a solution to help spreading knowledge.

A kudos would be appreciated  🙂

[DataVitalizer]

Hi @ovonel 

If it worked please don't forget to accept it as a solution 👍

Did it work ? Mark it as a solution to help spreading knowledge.

A kudos would be appreciated  

[ovonel]

Once I open the model, the query returns all the 50 tables in the model and not just the ones used in the Pbix...

[DataVitalizer]

Actually you will have to deep dive in that resulted table, here you will find the steps you must follow in order to identify the used/referenced tables in your report.

Did it work ? Mark it as a solution to help spreading knowledge.

A kudos would be appreciated