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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
OneWithQuestion
Post Prodigy
Post Prodigy

View SQL query in direct query mode

Is there anyway to view the SQL generated by Power BI desktop for direct SQL queries?

 

Thanks!

15 REPLIES 15
nedpbi
Helper I
Helper I

Just to add in case anyone else is facing the "Unable to open trace definition file" error, I was able to resolve this by doing the following in the SQL profiler:

 

File ->Templates -> New Template -> Select the correct server type from the drop down -> Template name -> Select events -> Save

 

Once I did the above I was able to set the trace on a database in power bi service. 

RobertSlattery
Responsive Resident
Responsive Resident

After opening SQL Profiler from SSMS Tools, have PBI Desktop open and connect to the localhost:XXXXX server that you can see in Dax Studio.  Then, open the trace file and it will work.

dexterz
Helper II
Helper II

OneWithQuestion
Post Prodigy
Post Prodigy

I am trying to read the trace file for PowerBI Desktop, but it says I cannot open the file:

 

----

Unable to locate trace definition file Microsoft Analysis Services TraceDefinition 15.0.0.xml for open trace file FlighRecorderBack.trc.....

----

 

How are we supposed to obtain this file?

 

I have SQL 2017 installed on my local and SSMS 17.4 but don't see where we are supposed to get the TraceDefinition 15.0.0.xml file?

ricardocamargos
Continued Contributor
Continued Contributor

Hi @OneWithQuestion,

 

Power BI Desktop creates a file "FlightRecorderCurrent.trc." on:

 

"\<User>\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces"

 

Or you can also enable it on the file -> options -> diagnostics

 

References: https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about

 

Thanks,

 

Ricardo

So it appears that the trace files generated cannot be opened which is really odd.

 

When I go to open it with Profiler I get this error: Can't open PowerBI trace file: Needs TraceDefinition 15.0.0.xml 

 

 

 

 

@OneWithQuestion,

Please update your SSMS from 17.4 to 17.5, then start the SQL Profiler trace in Admin mode, this way, you will be able to open the trace file.



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @OneWithQuestion,

 

Which sql profiler's version are u using ?

2017, the one with SSMS 17.4

@OneWithQuestion,

Go to C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Profiler\TraceDefinitions, you can find that the version of  Analysis Services TraceDefinition xml file is 14.0.0, and this is the highest version of the file in SQL Server.

However, the trace file generated by Power BI Desktop requires Microsoft Analysis Services TraceDefinition 15.0.0.xml, thus you get the above error.

There are two workarounds for you solve this issue.
1. Use December update of Power BI Desktop to connect to your data source, the trace file generated by the version can be opened in your SQL profiler.
2. Use native query option in Power BI Desktop to check undelying sql query.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

To clarify on Native Query:  That only works for the ETL stage right?  When I an viewing the edit query screen and seeing the "raw data tables" returned and the various steps from the source (like renaming columns, adding calculated columns etc...)

 

How can I view the "native query" for a table on the visual designer?

 

In my scenario I am placing a table on the visual layout page and adding a filter and want to see what query it is sending back?

 

Second question:

 

WHY is PowerBI using a version of profiler that is not available?

 

Where do we get that XML 15 definition?

To see the query that is generated for a visual, go to View --> Performance analyzer. Click Start recording, and Refresh visuals. Once the visual has been refreshed, click Stop. Then click Copy query and paste in Notepad++, etc. This query contains both DAX and SQL (for DirectQuery tables).

 

DataInsights_0-1657892323737.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I copied the latest TraceDefinition on the folder "14.0.801" , changed the name to "15.0.0" and changed the first elements of the XML (major version ,minor version and build number) to 15, 0 and 0 respectively and I became able to open the .trc file

Hoped it help

PS: I really hope that next time we will have the proper xml version launched together with PowerBI, workarounds are not ok

@OneWithQuestion,

We are only able to view Native query in Query Editor of Power BI Desktop.

In addition, regarding to the XML 15 definition issue, I will consult it internally and post back.

Regards,

Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Any progress on this? Using SQL Server Profiler 18 and opening traces from Power BI gathered via XMLA endpoint throw the error "Unable to locate trace definition file"

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors