Power BI Gateway/Service DirectQuery Query Concurrency

[Anonymous]

Hello,

My team is looking for ways to improve performance on a Power BI report serving data from AWS Redshift using DirectQuery through a Power BI Gateway. This report is accessed via licensed PowerBI Pro users (no premium capacity is previsioned). Our report functions well with an individual user, but performance degrades as the number of active users increases.

We've taken steps to improve performance via recommendations laid out in documentation, and I'm currently looking for guidance on how to increase the number of active database connections and number of concurrent queries that can be submitted. In testing, I've found that there is a limit of 10 active connections between the PowerBI Gateway and Redshift regardless of the number of active users (tested up to 45 users submitting actions at once). 

My team's initial steps were:

• Set a high value for "Maximum connections per data source" in the Data model (documentation reference)
• Override auto-configured MashupDQ settings settings (documentation reference)
• Validate DbConnectionPoolMinSize & DbConnectionPoolMaxSize settings are not reducing number of available connections (set to 128 & 256 respectively)
• Validate Redshift configurations are capable of supporting  >10 active connections/concurrent queries (JMeter tests)
• Configure Load balanced gateway

After these steps, we found a few pieces of information in the logs that could be helpful:

• MashupContainerProfiles.log - The ContainerMaxCount for the DirectQueryPoolMashupContainerPoolTypewas set to 40
• Mashup<Date>_log.txt - The preferredContainerCount option was set at 10. I'm unsure if this value is generated or if there is a way to directly influence it. 

After this investigation, my team was unable to find a way to get more than 10 active connections from the PowerBI Service to Redshift at any given point in time. I'm hoping someone in the community can provide some additional reccomendations, or call out some other Power BI service limits that could be limiting the level of concurrency in our set up. 

Thank you, 

Kyle

[v-yuaj-msft]

Hi @Anonymous ,

I think you can put your reports into Premium environment to optimize the Power BI environment. Please refer to this link to check if some suggestions can help you.

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

In such a situation I would recommend you consider switching to Import Mode, or to aggregations (which is a hybrid between Direct Query and Import).