Power BI -How to get the report query (Full query) in sql format

[Krupar]

Hi Team,

Currently we are migrating from Yellowfin to Power BI.The backend database is oracle

We are new to Power BI and for faster migration we need the entire Report query (Full query) in sql format to compare with the Old reports.

Pls let us know how to fetch the full report query

[saud968 · Accepted solution]

To fetch the full report query in SQL format from Power BI, you can follow these steps:

1. Open the Report in Power BI Desktop:

   • Load your Power BI report in Power BI Desktop.

2. Use Performance Analyzer:

   • Go to the “View” tab and click on “Performance Analyzer”.
   • Click on “Start Recording” and then refresh the visuals in your report.
   • Once the visuals are refreshed, you will see the queries listed in the Performance Analyzer pane.

3. Copy the Query:

   • Expand the visual you are interested in and click on “Copy Query”.
   • This will copy the DAX query generated by the visual.

4. Convert DAX to SQL:

   • Since Power BI primarily uses DAX (Data Analysis Expressions), you might need to convert the DAX query to SQL if your backend is Oracle. Tools like DAX Studio can help you analyze and convert DAX queries.

5. Using DAX Studio:

   • Download and install DAX Studio.
   • Connect DAX Studio to your Power BI model.
   • Run the copied DAX query in DAX Studio to analyze and convert it to SQL.

6. Direct Query Mode:

   • If your Power BI report is using Direct Query mode, you can directly see the SQL queries being sent to the Oracle database. This can be done by checking the query logs on your Oracle server.

By following these steps, you should be able to fetch the full report query and compare it with your old reports.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[Anonymous · Accepted solution]

Hi @Krupar ,

Thanks saud968  for the quick reply. I have some other thoughts to add:

Here I made a test to connect to Oracle database using Direct Query in Power BI desktop. To see the query for Power BI desktop report, we can find the file named FlightRecorderCurrent.trc in the file path as below. And then use SQL server Profiler to open the file to get SQL query .

C:\Users\Username\Microsoft\Power BI Desktop Store App\AnalysisServicesWorkspaces\AnalysisServicesWorkspacexxxx\Data

As in the picture, we can find the SQL query easily.

For the report in power Service, aiming to see the SQL query, we can change the setting of Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config file, Just change the EmitQueryTraces value from False to True. Please refer to the online document.Through this way we can get the SQL query like this:

Best Regards,

Neeko Tang

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

[saud968]

Great I was not aware of this. This is helpful thanks for adding this. 

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[saud968 · Accepted solution]

To fetch the full report query in SQL format from Power BI, you can follow these steps:

1. Open the Report in Power BI Desktop:

   • Load your Power BI report in Power BI Desktop.

2. Use Performance Analyzer:

   • Go to the “View” tab and click on “Performance Analyzer”.
   • Click on “Start Recording” and then refresh the visuals in your report.
   • Once the visuals are refreshed, you will see the queries listed in the Performance Analyzer pane.

3. Copy the Query:

   • Expand the visual you are interested in and click on “Copy Query”.
   • This will copy the DAX query generated by the visual.

4. Convert DAX to SQL:

   • Since Power BI primarily uses DAX (Data Analysis Expressions), you might need to convert the DAX query to SQL if your backend is Oracle. Tools like DAX Studio can help you analyze and convert DAX queries.

5. Using DAX Studio:

   • Download and install DAX Studio.
   • Connect DAX Studio to your Power BI model.
   • Run the copied DAX query in DAX Studio to analyze and convert it to SQL.

6. Direct Query Mode:

   • If your Power BI report is using Direct Query mode, you can directly see the SQL queries being sent to the Oracle database. This can be done by checking the query logs on your Oracle server.

By following these steps, you should be able to fetch the full report query and compare it with your old reports.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[Krupar]

Many Thanks @saud968 for the Prompt Response.

I executed the steps as per your given solution.I was able to get the DAX query and for the sql query conversion i used the DAX studio.

Below is the screeshot..Just want a confirmation whether this query will include all the filters and the formulas .Is it the correct way to check the query..Pls advice

Thanks for ur help

[saud968]

Yes, this should have the filters but this is Dax format. You can also work with steps shared by @Anonymous . Another option is that the SQL query generated by Power Query, go to the “Advanced Editor” in the Power Query Editor. The M code in the Advanced Editor shows the transformations applied. While this is not SQL, it gives you an idea of the data shaping steps. For a more SQL-like query, you can use the “View Native Query” option (if available) by right-clicking on the last step in the “Applied Steps” pane. This shows the SQL query sent to the Oracle database.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[saud968]

@Krupar If the suggestion works then please accept it as a solution

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!