Forum Discussion
Advocate IGateway datasource limit breaking ISV use case
We are an ISV using Power BI Embedded to embed reports in our web application. The data sources are MySQL databases.
We have a setup where every customer (58k+) has its own database, its own workspace, etc... These workspaces are all refreshed daily through a single gateway cluster (7 gateway servers). The complete setup is automated using the Power BI REST API.
We noticed lately that Microsoft is imposing limits in the Power BI service, probably to address performance.
Creating more than 1000 gateway datasources fails on new gateway cluster with the following error:
{"error":{"code":"DMTS_PowerBIDataMovementGatewayExceededDatasourceLimit","pbi.error":{"code":"DMTS_PowerBIDataMovementGatewayExceededDatasourceLimit","details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"Gateway 'bd45b391-782b-4051-9330-9bc0c6c3f719' has exceeded the datasource limit. Delete some existing datasources on this gateway before creating more."}}],"exceptionCulprit":1}}}
This limit completely blocks the ISV setup. At the same time, they introduced Service Principal Profiles, which seems to encourage the ISV setup. With SPP you can easily get around the 1000 workspaces limit on a single user or service principal.
So our question is, how can an ISV handle 1000+ gateway datasources? The documentation seems to hint on using Service Principal Profiles as well for gateway datasources, but I could not get that one working. Even when creating gateway datasources and making sure that the only gateway datasource user is a SPP, it still gives the error.
https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-onprem-faq#what-do-i-need-to-do-if-i-reach-the-maximum-limit-of-1-000-data-sources-on-a-gateway-cluster--and-how-do-i-avoid-reaching-this-limit-
It mentions: "...How do I avoid reaching this limit? If you're an ISV or any other Power BI embedded app owner with many customers, use service principal profiles for multi-tenancy apps in Power BI embedded to avoid this limit for gateways (personal mode only)..."
15 Replies
- lbendlin
Super User
Are they seriously suggesting you use a personal gateway?!?
The "1000 items" limit keeps popping up in all kinds of places. Sounds like they forgot to enforce it for the number of connections per gateway.
Be aware that if your databases are all on the same instance, and your account has access to all databases then you can use a single connection, and then override that in the custom query by connecting to a different database.
PiloosThanks lbendlin for the feedback.
I also found the personal gateway thing weird. You can't even create a cluster of gateways in personal mode, as far as I tried.
Be aware that if your databases are all on the same instance, and your account has access to all databases then you can use a single connection, and then override that in the custom query by connecting to a different database.
How does this exactly work? You bind multiple datasets to the same gateway datasource? I tried that, but it gives me a credentials error (ModelRefreshFailed_CredentialsNotSpecified) when I try to refresh the second dataset. Both datasets connect to different databases on the same MySQL database server but the user making the connection has rights on both databases.
v-hashadapuCommunity Support
Hi Piloos , Thank you for reaching out to the Microsoft Community Forum.
In Power BI, if multiple databases are present on the same MySQL instance, you can link multiple datasets to a single gateway datasource by using dynamic database switching in the query. This is possible if the gateway datasource is set to point to the MySQL instance rather than a specific database, and the Power Query (M) or SQL statement dynamically references the necessary database.
Please try the following steps:
- In Power BI Service, go to Manage Gateways and create one gateway datasource that connects to the MySQL server instance. Ensure the credentials used have access to all target databases.
- Instead of directly connecting to a specific database, modify your Power Query (M) script to dynamically specify the database.
- The ModelRefreshFailed_CredentialsNotSpecified error occurs because Power BI stores credentials at the datasource level. If different datasets override the database dynamically, Power BI may not automatically pass credentials. To resolve this, Go to Power BI Service -> Settings -> Dataset Settings. Locate the dataset that fails to refresh. Ensure it’s using the same credentials as the gateway datasource. Set the authentication method to Basic or OAuth (depending on your setup). Save and apply the changes.
- If Power BI still fails to refresh, consider using DirectQuery mode instead of Import mode. DirectQuery allows Power BI to send queries dynamically without needing separate refresh credentials for each dataset. If your data volumes are manageable, switching to DirectQuery may eliminate the need for individual refreshes.
If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.- Piloos
Hi v-hashadapu
In Power BI, if multiple databases are present on the same MySQL instance, you can link multiple datasets to a single gateway datasource by using dynamic database switching in the query. This is possible if the gateway datasource is set to point to the MySQL instance rather than a specific database, and the Power Query (M) or SQL statement dynamically references the necessary database.
Sounds like a plan. I tried it out, but I couldn't manage to create a MySQL datasource without specifying the database. Any clue how to achieve this? Before I start doing this using the REST API, I first want to validate the solution manually in ther service. If I create a gateway datasource there, the database is a required field.
Could it have something to do with the Power Query statement?
Source = MySQL.Database(db_server, db_name, [ReturnSingleDatabase=true, CreateNavigationProperties=false]),
- v-hashadapu
Hi Piloos , Thank you for reaching out to the Microsoft Community Forum.
The 1000 data sources per gateway cluster limit is enforced at the gateway level, not at the workspace or service principal level. Service Principal Profiles (SPP) work for workspaces but do not currently override the gateway datasource limit. Microsoft’s documentation mentions SPP for “gateways (personal mode only)”, meaning it does not apply to standard enterprise gateways.
Please try below possible workarounds:
- Create additional gateway clusters to distribute data sources. Each cluster will have its own 1000 data source limit, so by adding more clusters, OP can bypass the limitation.
- If Import mode is being used, consider switching to DirectQuery, which removes the need for scheduled refreshes via the gateway. In DirectQuery mode, the dataset connects directly to MySQL at query time, eliminating the need for per-datasource definitions in the gateway.
- Instead of connecting each customer database directly to Power BI, create an ETL pipeline that Extracts customer data, loads it into a centralized data store, Power BI then connects to this single centralized data source instead of creating one gateway data source per customer.
Also please refer to the below documentation for more information:
Move multi-customer apps to service principal profile model - Power BI | Microsoft Learn
Develop scalable multitenancy applications with Power BI embedding - Power BI | Microsoft Learn
What is data warehousing in Microsoft Fabric? - Microsoft Fabric | Microsoft Learn
Manage on-premises data gateway high-availability clusters and load balancing | Microsoft Learn
A special thanks to lbendlin for your valuable input.
If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.- lbendlin
This enforcement of "1000 items" limits coming up on March 25th is mildly alarming. Reading between the lines this is Microsoft telling us that they misjudged usage patterns and ran out of resources without telling us.
- Piloos
lbendlin wrote:This enforcement of "1000 items" limits coming up on March 25th is mildly alarming. Reading between the lines this is Microsoft telling us that they misjudged usage patterns and ran out of resources without telling us.
Sounds indeed alarming. Do you have a link to a source in order for us to investigate the possible impact?
- v-hashadapu
Hi Piloos, As haven’t heard back from you in a long while, we’re closing this thread per our follow-up policy. For any further discussions or questions, please start a new post in the Microsoft Fabric Community Forum — we’ll be happy to assist.
Thank you for being part of the Microsoft Fabric Community.