Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
AL___
New Member

Direct Query MySQL

Hello,

 

maybe i can find some help here...

Is it possible to connect our Power BI Desktop to MySQL with an "direct query"? Is there any way to reach this or is there an alternatvie (timetable-refreshing?) to have refreshed datas at Power BI Desktop?

 

We desperately need automatic refreshed datas. If there is no possibility to achieve this with MySQL, maybe we should switch to an SQL database?

 

Thanks in advance and best regards,

AL

1 ACCEPTED SOLUTION
edhans
Super User
Super User

MySQL is currently not supported. You can see a list of all currently supported data sources for Power BI that can be used with Direct Query here.

 

There is an open UserVoice request for Direct Query MySQL support here.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

20 REPLIES 20
victorviro
Frequent Visitor

I was able to connect to a MySQL database with direct query mode using this connector MariaDB Direct Query Adapter

All I'm doing is adding a table, and I get "This step results in a query that is not supported in DirectQuery mode" on the very first "Source" step...

hbwebdev_0-1724810895595.png

 

to all those, who are disappointed by the fact that powerbi directQuery is not supported with mySQL- "this is not full truth."

 

mySql and mariadb are developed by same people, and are almost identical. so direct query connector/adapter for mariadb

also work for the mySQL.

 

1. just download it from above link.

2. select 'mariadb' in 'get data' option in powerbi

3. put your 'mySQL' server and database name.

4. select directQuery mode.

5. put in your mySQL credentials, and there you are.

 

additional: in case you want to publish your report to pbi service-

 

6. publish your pbi desktop direct query report to service.

7. install a on-premises data gateway on your machine (pc/server)

8. configure your data gateway in 'manage gateways and connections' in pbi service.

9. finally go to the dataset setting of the report you just published to pbi service, and connect your data gateway with your pbi dataset.

10. now whenever you update your mySQL database, and query your report (means open or interact with your report) over pbi service or desktop, that query will securely directly go into your database via that data gateway, and will fetch the updated data to the report, this way you'll see the updated report.

 

Thanks @victorviro for suggesting this amazing trick.

I get the following error when I try to connect to my MySQL database through MariaDB, with MySQL credentials:

 

"[ma-3.1.4]Can't connect to MySQL server 'localhost' (10061)"

 

What am I doing wrong?

super! what about if I'd like to edit a custom SQL query. The custom sql box is hidden, and I'm stuck on this. Many thanks

I tried it, for the connection if it lets me do it, but when I make the relationships of the tables in power bi, I get errors, the same when making measurements, for example the following I am counting the amount of ticket, but filtering those that have id = 1, when I put it on a card I get that error

 

Roger14_0-1696828712423.png

 

When I try to relate tables I get the same error

Roger14_1-1696829004079.png

 

i don't understand what the error message does say.

However, here might be a fix- https://community.fabric.microsoft.com/t5/Desktop/Relationship-Direct-Query/td-p/2255900

In case anyone reads this whole thread, this is the solution. It worked perfect for me. It didn't occur to me to use the MariaDB connector to connect to MySQL. Thank you victorviro!!

bizzybisnette
Frequent Visitor

MySql is one of the larger databases out there I cannot believe directquery is not supported. I was shocked to find that out. 

eram
Regular Visitor

We're using CData's DSN/ODBC solution. It's working fine. Expensive though. Progress has a similar ODBC driver.

kapsule
New Member

Hi,

 

I'm starting with PowerBI and a Mysql database. From what I could read from your post, I have the same problem. Did you find a solution? I would like to use directquery to use the new update page function.

 

Thanks,

@kapsule There is no solution. Either it is supported, or it isn't, and right now it isn't. There is no workaround for the page update function for direct query for non-direct query sources. DQ must be supported by Power BI.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
cyclist007
Responsive Resident
Responsive Resident

I would request all interested to support this idea (if you need this feature): https://ideas.powerbi.com/ideas/idea/?ideaid=f2bcf6ef-2f88-41c5-98aa-ae1ecc1f83ed

 

Other related idea (to make it a secure connection): https://ideas.powerbi.com/ideas/idea/?ideaid=1673aa83-99c0-4f2c-9e04-6eef0122aa2c

edhans
Super User
Super User

MySQL is currently not supported. You can see a list of all currently supported data sources for Power BI that can be used with Direct Query here.

 

There is an open UserVoice request for Direct Query MySQL support here.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

It's possible with the correct drivers. Refer this video:

 

https://youtu.be/EL3DdMVAUnQ?si=WQ7-G69CQ7-VSP4U

i don't think any other solution is required.

mySql and mariadb works almost the same, thus the connector for mariadb works for the mysql as well.

Yeah, no, there is a workaround. Just use MariaDB connection as mentioned in the above thread.

Thanks for the update @TaylorRodieck 

 

You'll note the original thread started in 2019 and my reply was in march of 2020. Mariadb didn't support Power BI until December of 2020. So then, there was no workaround. Today, there is. Glad to know this.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

I did see that, I just wanted to point anyone who came here looking for that answer in the right direction. That workaround saved my behind!😅

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.