cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
darroyo Frequent Visitor
Frequent Visitor

Fill 1 table with 1 MySQL query executing on multiple (equal) databases

Hello.

I have a database for each of our customers. Every database has the same tables and fields. I wan to fill a Power BI table with some common data of all these databases with a MySQL query.

 

Now I have defined a parameter and I can choose the database I want to load data, but then my table has data for only one customer. What I want is to get data for every customer in order to analyze their behaviour.

 

I have read about copy paste the same query for every customer but this is not a good solution for me because the query could need a change and the maintenance would be hard.

 

Thanks in advance for your support.

 

Best regards.

3 REPLIES 3
Microsoft v-yulgu-msft
Microsoft

Re: Fill 1 table with 1 MySQL query executing on multiple (equal) databases

Hi @darroyo ,

 

You can paste the query which joins multiple tables from different database under SQL statement as below. It will load all data into a single query. For how to write the MySQL query, please post a thread on MySQL forum to get professional advice.

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
darroyo Frequent Visitor
Frequent Visitor

Re: Fill 1 table with 1 MySQL query executing on multiple (equal) databases

Thanks Yuliana, but I think that solution is not good for me.

OK I can make a great query with copy/paste of my query connecting each database using prefix before tables, but keep in mind I would have abot 50 copies of the main query. The great query would be so large but that is not the problem. The problem would be the time expended in managing that query when some change is needed.

 

I keep your idea as the last chance, but I hope there's another method.

 

Best regards.

David.

darroyo Frequent Visitor
Frequent Visitor

Re: Fill 1 table with 1 MySQL query executing on multiple (equal) databases

Hi, I just want to reactivate this question. I have no solution by now, but I think it should be a way to do this with PowerBI.

 

For example, when I work with Qlik Sense I can make a first query in order to get a list of all database names, and then I can write a loop sentence executing my queries in every database of the list.

 

Thanks for your attention!

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)