Performance dips due to Concurrent usage of report

[Anonymous]

hello,

We have been using Power Bi reports in the direct query mode. Database table structures has been adjusted to make sure we minimum joins and calculations are done on the fly. The schema user has also been allocated proper resources on the DB. The execution time ranges on the DB are on an average 1.6 sec. Individual users of the reports see mostly under 20 sec load times. 

The problem arises when we have concurrent users in the system(ranging from 40- 60 users). The load time for the reports are in minutes. 

There is a setting on the pbix file to control the concurrency. 

Maximum connections per data source.(https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about#guidance-for-using-directquery-s...)

The default setting for this is 10. can anyone please explain how this works?

We also use a gateway cluster to load balance the request from the reports to the datasource.

Also any help to tackle concurrency on reports is appreciated. 

Thanks,

Anu 

[v-piga-msft]

Hi @Anonymous ,

I have still a little confused about your requirement.

If you want to know more about Maximum number of connections option for DirectQuery, please have a good look at this document. If you want to know how it works, I have not find any document to explain it currently.

However, you may try to check the process in SQL Profiler.

Best  Regards,

Cherry

[Anonymous]

Thanks for the reply.

Let me try to clarify. 

If i set the value of the setting to say 20, does it mean that only 20 queries will be sent to DB at a time, the response for which will then need to come back before another 20 gets fired? Or does it mean that all 20 are passed to DB and if there are more requests then that is passed on as well? There seems to be some kind of throttling thats limiting the full capacity of the underlying datasource being used and we are trying to find that out. 

The data storage is on vertica. 

Also on an average there are about 10 queries that need to run to load all the visuals in the report.

Regards,

Anu