cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GuestUser
Helper V
Helper V

Check Backend SQL Query Generated By Power BI Reports

Hi,

 

We have Power BI Report Server installed , 

 

We are building reports by connecting it to SSAS .(tabular model)

 

We need to check the Full backend SQL Query that is generated by Power BI Reports. Could anyone please provide the steps of how can we check the same

 

Thanks !!

3 REPLIES 3
jarretl
Frequent Visitor

Hi @GuestUser,
Are you using SSAS tabular with in-memory mode, or direct query mode?
Regardless of the mode, I believe powerbi only generates DAX code to query the tabular model, not SQL.

If the tabular model is set up as in-memory, the DAX code generated will query the tabular model directly, if not it will be converted to the necessary source SQL behind the scenes in the SSAS engine and will pull in and manipulate whatever data is needed to satisfy the DAX queries received from the report in real time.

You can view what DAX code is being passed from a powerbi report to the tabular model by using the Trace feature in DAX Studio, which is pretty handy to see what DAX calls powerbi is making for any given report or visual.  However, I'm unaware of a way to see what underlying SQL is being created by the tabular model to reach into a SQL backend when the SSAS instance is set up as Direct Query.  If you trace the DAX being sent from powerbi, you may be able to infer what types of sql queries would be needed to what source tables in order to satisfy the DAX queries being generated...


Not sure if that helps you at all.

-Jarret.

Thanks for the Update !!

 

We are connecting to SSAS using Connect Live Option.

 

Basically we need to backtrace what sql queries are generated in the backend so that it will be helpful in our debugging process

 

Has anyone tried this out or Any ideas please? 

 

Thanks !!

 

 

 

stpnet
Impactful Individual
Impactful Individual

If you are using a SSAS Tabular model as your source then you can use an xEvent trace to see the queries. There is a project on SQLBI.com that sets this up and will run the traces into tables for you to query. Its not terribly practical to run constantly if your traces are big as they can take a while to import but we run this on our production front ends  here with some PowerShell doing the heavy lifting of the initial import. So we can monitor pretty much everything that's being queried all the time.

 

The link for it is here

 

https://www.sqlbi.com/tools/ssas-events-analyzer/

 

A simpler option if you just want to look at stuff now and again is to use the SQL Server Profiler tool that should have installed with SSMS (SQL Server Management Studio). If you connect to the SSAS Tabular instance and run a standard Analysis Services Trace You will See Query Begin and Query End events. The generated queries are available in these events , some will be MDX, others will be DAX, depending on the tool you access the tab model with (Excel picot tables will issue MDX and PBI will issue DAX).

 

It is possible to run Profiler and indeed the SSAS Event Analyser against the "captive" SSAS instance that sits behind PBI-SSRS for your imported data sets if you really want to. You have to amend the PBI-SSRS config so its accessible remotely and figure out the port number the captive instance is running on and then connect to that SSAS Instance with Profiler or the xEvent traces used by SSAS Events Analyser, but it's do-able.

 

 

 

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps