Forum Discussion

OneWithQuestion

Post Prodigy

8 years ago

View SQL query in direct query mode

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

ricardocamargos

Continued Contributor

8 years ago

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

OneWithQuestion

Post Prodigy

8 years ago

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

ricardocamargos
Continued Contributor
8 years ago
Hi OneWithQuestion,

Which sql profiler's version are u using ?
- OneWithQuestion
  Post Prodigy
  8 years ago
  2017, the one with SSMS 17.4
  - Anonymous
    Not applicable
    8 years ago
    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
Anonymous
Not applicable
8 years ago
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