How many levels of subqueries in SQL does Power BI support?

[croberts21]

I have Power BI Desktop for March 2023. I'm trying to use an ODBC connector to access a Postgresql v9.6.17 database. (I realize this versin of Pgsql is unsupported, we cannot upgrade it, the system is a black box.)

I have an SQL statement that is 150 lines long, with subqueries, and some subqueries also have subqueries, so that's 1 main level of SQL plus 2 levels of subqueries. I started this subquery in PBI and it ran for 3 hours and never finished. 

Is there a limit on the levels of subqueries PBI can support?

Thank you.

EDIT: I was able to enter the whole complex 130 line query into PBI. Each step of getting that query entered, validated, and getting data, took 12-24 hours. It's a complex query that took a long time but it works. Hopefully changing a filter on a viz will not take 12 hours. 

[lbendlin]

Power BI doesn't care about the subquery depth. That's a problem for the Postgresql engine. Examine the query execution plan there and optimize the query through indexes, statistics and refactoring.

[croberts21]

Thank you. The database is a black box, no one here can do anything with it. I only have PBI to use to get data from it. 

I am getting this giant query to work with many sublevels. But it takes 12+ hours to do each step in PBI. Hopefully no one will reboot the PBI machine while I do this work. 

I'm worried how long it will take if the user changes just one filter. 

[lbendlin]

The database being a black box should be a red flag (pardon the pun). If you cannot ask them for the query plan, or for optimizaton through indexes, statistics and refactoring  then you shouldn't rely on that data source.

As you know the Power BI service has a hard limit of 5 hrs, so your setup is not sustainable anyway.