View SQL query in direct query mode

[OneWithQuestion]

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

Thanks!

[nedpbi]

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]

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]

dax studio

[OneWithQuestion]

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]

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]

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 

[v-yuezhe-msft]

@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

[ricardocamargos]

Hi @OneWithQuestion,

Which sql profiler's version are u using ?

[OneWithQuestion]

2017, the one with SSMS 17.4

[v-yuezhe-msft]

@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

[OneWithQuestion]

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?

[DataInsights]

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).

[dgolhiardi]

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

[v-yuezhe-msft]

@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

[Anonymous]

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"