Forum Discussion
Gateway datasource limit breaking ISV use case
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]),
Hi Piloos , Thank you for reaching out to the Microsoft Community Forum.
Please try this:
- 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.
- 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
- 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.
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.
You could wait for them or you could write your own custom connector.
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.
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:- 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.
- 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.