Gateway datasource limit breaking ISV use case

[Piloos]

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...
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)..."

[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.

[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:

1. 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.
2. 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.
3. 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:

Use service principal profiles to manage customer data in multitenant apps - Power BI | Microsoft Le...

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?

[lbendlin · Accepted solution]

Enforcing item limits in a Fabric workspace - M365 Admin

[Piloos]

Thanks @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-hashadapu]

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:

1. 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.
2. Instead of directly connecting to a specific database, modify your Power Query (M) script to dynamically specify the database.

1. 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.
2. 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.

Please try this:

1. When setting up the gateway data source, specify any valid database as a placeholder. Ensure that the user has privileges to access all required databases on the MySQL instance.
2. Modify the Power Query (M) script to dynamically switch databases. Example:

let

    db_server = "your-mysql-server",

    db_name = "dynamic_db_name",  // Replace dynamically

    Source = MySQL.Database(db_server, db_name, [ReturnSingleDatabase=true, CreateNavigationProperties=false])

in

    Source

3. Ensure the dataset credentials match those set in the gateway.

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 ,

Thanks for your feedback.

I couldn't get it to work.

These are the steps I took:

1) I create a gateway datasource with database A

2) I have a semantic model with db_name A specified in a parameter

3) I bind the gateway datasource to semantic model.  So far so good.  I can refresh the semantic model via the gateway.

4) I change the parameter db_name to B --> the semantic model does not seem to be bound anymore.  If I try to refresh, I indeed get the confirmation that there is something wrong and I need to specify a gateay.

In the mean time, I also got an update from the product team.  In order for this to work, it should be possible to create a MySQL gateway datasource without a specific database.  At the moment this does not seem possible.  They said that they would look into it.

[v-hashadapu]

Hi @Piloos , Thank you for reaching out to the Microsoft Community Forum.

Could you please confirm if you have raised the support ticket and if the issue has been resolved after raising a support ticket? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.

Thank you for your understanding and assistance.

[Piloos]

Hi @v-hashadapu ,
The support ticket is not closed yet.  It is confirmed that there is a 1000 gateway datasource limit per gateway, so irrespective of the user (or service principal).
The possible options to fix this issue:

1. A gateway datasource that can be used for multiple MySQL databases with the same credentials.  This is AFAIK not possible today.  The product team said they would check if they could support this.  @lbendlin suggests to write our own connector.  I am not so familiar with that and I would probably need to start from the .pq-file of the current MySQL connector.  Not sure where to find this.
2. The second option is to ditch the gateway.  It is not really a solution, but a workaround.  It would require us to expose our sources on the internet and make "straight" connections to them.  This just eliminates the need of gateway datasources :-).  However, it reduces security.

[v-hashadapu]

Hi @Piloos , thank you for reaching out to the Microsoft Fabric Community Forum.

Thanks for the update. We appreciate the details. Hope your issue gets resolved soon. when it does, please share the insights here and mark it or any other helpful answer 'Accept as Solution', which will help others with similar queries.

Thank you.

[lbendlin]

You could wait for them or you could write your own custom connector.

[lbendlin]

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.