Power BI maxing connections to DB :( Can we populate multiple tables with single Sql.Database call?

Question

I am assisting my team troubleshoot an issue with a Power BI report we are developing. We have a rather complex data model in the source SQL database, so we have created 5-6 views to better manage the data. We have a requirement to use DirectQuery, as one key requirement for the report is that the most up-to-date data in the database is visible, rather than having a delay in loading/caching the data. We also have the single data source, just the one database.

When we run the report, we see a spike of 200-500 connections to the database from the specific user for the report data source, and those connections don't close. This is clearly an issue and unsustainable for any product. We have a ticket open with Microsoft premium support to address the connections not closing, but in the meantime, I'm wondering if we're doing something wrong inside the report?

When I view the queries in the query editor, we basically have one query for each view, and it's a simple:

let
  Source = Sql.Database(Server, Database)
  query_view_name = Source{[Schema ......]}[Data]
in
  query_view_name

(I don't have the raw code in front of me, but that's the gist of it.)

It seems to me, based on analytics in the database, that "Sql.Database" is opening a new connection every time this view is called. And with 5-6 views, that's 5-6 connections at a minimum; then each time a filter is changed, it's more connections, and it's compounds from there until the database connection pool is maxed out.

Is there a way to populate all the tables using a single connection to the database? Why would Power BI be using so many connections? Can we populate multiple tables in the advanced query editor? Using DirectQuery, are there any suggestions for what we can look at/troubleshoot/change in the report?

Thanks!

Andrey Nikolov Andrey Nikolov · Accepted Answer · 2019-04-15T06:05:55

Power BI establishes multiple connections to the database to load multiple tables in parallel. If you don't want this, you can turn it off from Options->Current file->Data Load->Enable parallel loading of tables:

Keep in mind, that turning this option off most likely will increase the model loading time.

You may want to take a look at Maximum connections per data source option in Options->Current file->Direct query and the whole section Query reduction beneat it. Turning on Slicer selection and Filter selection on this page is highly recommended for cases like yours, but you need to train your users that they need to click on apply to see the results.

Power BI maxing connections to DB :( Can we populate multiple tables with single Sql.Database call?

2 Answers