Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

How to get SQL query for report

I want to see the Oracle SQL query generated by the PBI report (PBI service), I am using the Oracle database as my source and connecting using Live connection, please let me know how and where we can see the SQL Query.

 

  • v-frfei-msft's avatar
    v-frfei-msft
    8 years ago

    Hi Anonymous,

     

    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:
     
     
     
    Regards,
    Frank
     
  • v-frfei-msft's avatar
    v-frfei-msft
    8 years ago

    Hi Anonymous,

     

    Does this make sense? If yes, kindly mark the answer as solution please. Thanks!

     

    Regards,
    Frank

6 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    8 years ago

    Hi Anonymous,

     

    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:
     
     
     
    Regards,
    Frank
     
  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    8 years ago

    Hi Anonymous,

     

    Does this make sense? If yes, kindly mark the answer as solution please. Thanks!

     

    Regards,
    Frank

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Yes that helped

      Thanks

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        v-frfei-msft: i am nt able to find the folder path given in the solution. can you help? struggling with the same problem.

         

        Priya