Hi Thanks for this I Installed the Mariadb ODBC connecter and on my PC, when I try to connect, I get the below error message: Details: "The 'Driver' property with value '{MariaDB ODBC 3.1 Driver}' doesn't correspond to an installed ODBC driver" I have Mysql 8.0 on my remote server, Any Idea about solving this? Please note that if I open ODBC from outside of Power BI (from my Windows ODBC) I can connect smoothly!
Do you know what could be reason behind “Details: “Invalid URI: Invalid port specified” error? I’ve used same URI string to connect database via DBeaver tool and it is working, but Power BI gives me error of invalid URI
I still facing the same error after install MYsql connector of 8.3.0 , 9.1.0 version of 32 and 64 bit. when try to connect showing error - This connector requires one or more additional components to be installed before it can be used. I have reboot the PowerBI and System as well, kindly help as soon as possible.
Hi is the 3.1.2 version the only one which works and connects to mysql? i tried a higer version and it would not even connect. I get thiis connection error [ma-3.1.20]TLS/SSL error: no cipher match. Error 0x80090331(SEC_E_ALGORITHM_MISMATCH) with any higer version connector
Hello! Thank you for the video, it's very useful. I was able to connect to the database in Direct Query mode, but when I tried to add a numeric field to a chart, I got this error: "OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression." I know with DirectQuery you're not able to make transformations on the data but this would be just a simple sum or count of the values. I don't have any DAX queries, it's a blank report. Weirdly enough, it can display the median of the data. Has anybody solved this problem?
Thanks for watching. That’s the problem with direct query. Sometimes it has a hard time folding queries back to the source. Even simpler queries for that matter. We’re still figuring out if there’s a way to fix this but in the meantime please create a view in your DB with the SUM or COUNT query logic. Then you can easily pull that in your report.
Thanks for the video. I was able to connect to to our DB, but when I create relationships between tables I get SQL compilation errors. Do you know if there is a solution for this?
Thanks for watching! Can you confirm if you are using direct query mode or import mode? Also, please provide more details so that we will try to reproduce the same scenario at our end to see if there’s a fix.
@@InfernusTech Hi, yes it is with direct query mode. It seems like a syntax error with the query PBI is sending to the DB: [ma-3.1.20][5.7.16-10-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DOUBLE)) as `C1` from `registration_reports`.`fact_registration`' at line 1
Thanks for watching! That’s right. You can’t use queries with the MariaDB driver. There is a workaround though if you want to supply queries, you can create an ODBC data source in system ODBC data sources with the Maria DB driver and then in Power BI desktop you can use ODBC as a data source in Get data tab but there’s still a caveat that direct query won’t work with ODBC as a data source. All in all the best approach would be to take the SQL statement that you wish to supply and create a database VIEW of it so that you don’t have to worry about supplying it in Power BI side.
@@InfernusTech True. So as of today we dont have a way to use SQL statements and direct query together on MySQL database. Yeah, I am using view only, even that is not working properly as I mentioned in my next comment. thank you for your response.
Unfortunately, you can’t. The Maria DB driver doesn’t have the functionality to supply a custom SQL query. There’s a workaround where you could create an ODBC data source in your system and use ODBC as a data source but that also doesn’t help with direct query. All in all the best way would be to take whatever custom SQL statement you want and create a database VIEW out of it. So you don’t have to worry about supplying a SQL query on Power BI side and makes things a lot simpler. Hope this helps!
There was one more glitch I came across! To solve that, I stored all connectors in the folder where all powerbi files get stored in the documents, and from settings of powerbi, made the connectors access allowed
Thanks for sharing these details. It will definitely help others who run into similar problems. If you get a chance please help with listing step by step guide with more details.
I am receiving below error when I try to select columns loaded from MySQL database. Specially I select the column(that is generated by aggregation functions in SQL Query in the view that I am accessing). Any clue? OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source.
That’s the caveat with direct query and this driver. Hope you are facing this problem with direct query? It has a hard time folding queries to the source. Could you please share the SQL view definition? We will try to reproduce the same scenario on our end and see if we can fix it.
@@InfernusTech sure, this query generates 5734 records. select sl.userid, concat(firstname,' ',lastname) 'UserName', date(sl.createdAt) date, ifnull(count(sl.serviceLogId),0) 'ServicesCreated' from v_servicelogs sl left outer join v_users u on sl.userId=u.userid where action='add' group by sl.userid, date(sl.createdAt)
Please confirm if this is correct understanding: The above SELECT query is the one which you have used to create the view in database. And in Power BI you are using the Maria DB driver with direct query to get data from that view. But when you do that you get an error. Also is the error coming in Power query while fetching the data or is it coming when you drag the fields in a visual?
I have done all the steps but when I finally connect the DB to powerBI I get this error: Details: "The 'Driver' property with the value '{MariaDB ODBC 3.1 Driver}' does not match an installed ODBC driver." I downloaded ODBC version 3.1.20 as in the tutorial but it didn't work for me. Any solution?
Or can you provide these details for troubleshooting? 1. Open This PC > Right click > properties > System type: Is it 64bit? 2. Open Power BI Desktop > File > About: Provide the complete version here 3. Open Control Panel > Programs > Programs & Features > search for MariaDB: Provide the name and version here
@@InfernusTech Hi, I had the same error. Power BI Product Version: 2.130.930.0 (24.06) (x64) OS Version: Microsoft Windows NT 10.0.22631.0 (x64 en-US) Maria DB: Name: MariaDB ODBC Driver 64-bit Version 3.2.2
@@pragneshbhalala2555 Can you try installing Power BI Desktop Version: 2.127.1327.0 64-bit (March 2024) and along with that Maria DB ODBC driver version 3.1.20
I got tyhe same error with 3.2 driver. just remove this one and use the 3.1.20. It should work. Also test connection to your mysql using this driver in ODBC data source administrator tool(default windows tool). this is isolate if the issue is with pbi or with the driver
Considering your data sources are private or on premises you’ll have to set up an on-premises data gateway. Once the data gateway is installed on a Windows machine, you’ll have to follow the same steps mentioned in the video for installing the respective driver on the Windows machine where gateway is installed. After that, in Power BI service you can easily create a gateway connection in manage connections and gateways section. Just go to connections tab and create a new one with the same credentials you used with Power BI desktop. If you have any questions during the process feel free to post them here.
Bhai can you make a small real time project. Fetching data from a api and then automating it to give a real-time report. Please bhai make a video on this 🙏
Thank you!! I've been struggling to connect through MySQL. The Maria DB ODBC worked perfectly.
Glad it helped!
Very nice explanation. We were facing the same issue with our MySQL connection. We didn't have direct query option. Thanks for this.
Hi
Thanks for this
I Installed the Mariadb ODBC connecter and on my PC, when I try to connect, I get the below error message:
Details: "The 'Driver' property with value '{MariaDB ODBC 3.1 Driver}' doesn't correspond to an installed ODBC driver"
I have Mysql 8.0 on my remote server, Any Idea about solving this?
Please note that if I open ODBC from outside of Power BI (from my Windows ODBC) I can connect smoothly!
This is the only thing that worked for me!! Thank you @Infernus Tech❤!!!
Glad it worked for you. Please like the video and subscribe to the channel for more such content!
Thank you, sir you are the best, it worked perfectly
Thanks for watching! Please like the video and subscribe to our channel for more such content.
thank you, man. For your helpful video.
Glad it helped! Don’t forget to like the video and share it with your colleagues and friends as well.
Do you know what could be reason behind “Details: “Invalid URI: Invalid port specified” error? I’ve used same URI string to connect database via DBeaver tool and it is working, but Power BI gives me error of invalid URI
thank you my Mysql was not connecting to power BI at all but with maria db it works fine
Glad it worked for you! Please like the video and subscribe to the channel for more such videos.
I still facing the same error after install MYsql connector of 8.3.0 , 9.1.0 version of 32 and 64 bit. when try to connect showing error - This connector requires one or more additional components to be installed before it can be used. I have reboot the PowerBI and System as well, kindly help as soon as possible.
Hi is the 3.1.2 version the only one which works and connects to mysql? i tried a higer version and it would not even connect. I get thiis connection error [ma-3.1.20]TLS/SSL error: no cipher match. Error 0x80090331(SEC_E_ALGORITHM_MISMATCH) with any higer version connector
It's really helpful🎉
Thanks for watching! Glad it helped you.
Very informative
Hello! Thank you for the video, it's very useful.
I was able to connect to the database in Direct Query mode, but when I tried to add a numeric field to a chart, I got this error:
"OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression."
I know with DirectQuery you're not able to make transformations on the data but this would be just a simple sum or count of the values. I don't have any DAX queries, it's a blank report. Weirdly enough, it can display the median of the data. Has anybody solved this problem?
Thanks for watching. That’s the problem with direct query. Sometimes it has a hard time folding queries back to the source. Even simpler queries for that matter. We’re still figuring out if there’s a way to fix this but in the meantime please create a view in your DB with the SUM or COUNT query logic. Then you can easily pull that in your report.
Thanks for the video. I was able to connect to to our DB, but when I create relationships between tables I get SQL compilation errors. Do you know if there is a solution for this?
Thanks for watching! Can you confirm if you are using direct query mode or import mode?
Also, please provide more details so that we will try to reproduce the same scenario at our end to see if there’s a fix.
@@InfernusTech Hi, yes it is with direct query mode. It seems like a syntax error with the query PBI is sending to the DB:
[ma-3.1.20][5.7.16-10-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DOUBLE)) as `C1`
from `registration_reports`.`fact_registration`' at line 1
@@delderf-c1s I have tis problem too, have you find the solution?
Informative.. Thanks a lot!
But we cannot use queries in MariaDB driver right?
Thanks for watching! That’s right. You can’t use queries with the MariaDB driver. There is a workaround though if you want to supply queries, you can create an ODBC data source in system ODBC data sources with the Maria DB driver and then in Power BI desktop you can use ODBC as a data source in Get data tab but there’s still a caveat that direct query won’t work with ODBC as a data source.
All in all the best approach would be to take the SQL statement that you wish to supply and create a database VIEW of it so that you don’t have to worry about supplying it in Power BI side.
@@InfernusTech True. So as of today we dont have a way to use SQL statements and direct query together on MySQL database.
Yeah, I am using view only, even that is not working properly as I mentioned in my next comment.
thank you for your response.
Very helpful tutorial
Hi, is there any MySQL server version limitation that need to use with this Maria DB driver?
So far it worked for the recent versions of MySQL, so presumably it should be good with the recent versions.
The question is, with direct query can you use a custom SQL query statement (as you could in the import query), rather than just selecting tables?
Unfortunately, you can’t. The Maria DB driver doesn’t have the functionality to supply a custom SQL query. There’s a workaround where you could create an ODBC data source in your system and use ODBC as a data source but that also doesn’t help with direct query.
All in all the best way would be to take whatever custom SQL statement you want and create a database VIEW out of it. So you don’t have to worry about supplying a SQL query on Power BI side and makes things a lot simpler.
Hope this helps!
Thank you so much❤
Thanks for watching! Please like the video and subscribe to the channel for more such content.
its helpful!!
Glad you think so!
There was one more glitch I came across! To solve that, I stored all connectors in the folder where all powerbi files get stored in the documents, and from settings of powerbi, made the connectors access allowed
Thanks for sharing these details. It will definitely help others who run into similar problems. If you get a chance please help with listing step by step guide with more details.
I am receiving below error when I try to select columns loaded from MySQL database.
Specially I select the column(that is generated by aggregation functions in SQL Query in the view that I am accessing). Any clue?
OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source.
That’s the caveat with direct query and this driver. Hope you are facing this problem with direct query?
It has a hard time folding queries to the source.
Could you please share the SQL view definition? We will try to reproduce the same scenario on our end and see if we can fix it.
@@InfernusTech sure, this query generates 5734 records.
select sl.userid, concat(firstname,' ',lastname) 'UserName', date(sl.createdAt) date, ifnull(count(sl.serviceLogId),0) 'ServicesCreated'
from v_servicelogs sl left outer join v_users u on sl.userId=u.userid
where action='add'
group by sl.userid, date(sl.createdAt)
Please confirm if this is correct understanding:
The above SELECT query is the one which you have used to create the view in database. And in Power BI you are using the Maria DB driver with direct query to get data from that view. But when you do that you get an error. Also is the error coming in Power query while fetching the data or is it coming when you drag the fields in a visual?
@@InfernusTech correct. Issue is coming when I start dragging some fields into visuals.
Great Explanation
Thanks Ayush. Glad you liked it!
I have done all the steps but when I finally connect the DB to powerBI I get this error: Details: "The 'Driver' property with the value '{MariaDB ODBC 3.1 Driver}' does not match an installed ODBC driver." I downloaded ODBC version 3.1.20 as in the tutorial but it didn't work for me. Any solution?
Or can you provide these details for troubleshooting?
1. Open This PC > Right click > properties > System type: Is it 64bit?
2. Open Power BI Desktop > File > About: Provide the complete version here
3. Open Control Panel > Programs > Programs & Features > search for MariaDB: Provide the name and version here
@@InfernusTech Hi, I had the same error.
Power BI Product Version:
2.130.930.0 (24.06) (x64)
OS Version:
Microsoft Windows NT 10.0.22631.0 (x64 en-US)
Maria DB:
Name: MariaDB ODBC Driver 64-bit
Version 3.2.2
@@pragneshbhalala2555 Can you try installing Power BI Desktop Version: 2.127.1327.0 64-bit (March 2024) and along with that Maria DB ODBC driver version 3.1.20
I got tyhe same error with 3.2 driver. just remove this one and use the 3.1.20. It should work.
Also test connection to your mysql using this driver in ODBC data source administrator tool(default windows tool).
this is isolate if the issue is with pbi or with the driver
Thank you
Thanks for watching! Please subscribe to the channel for more such videos
It worked well until I started developing Power Apps. There it gives connection error, can't find ODBC.
Can you provide more details? Will try to reproduce the same issue and see if any luck with it.
After publishing into powerbi service , how it will work?
Considering your data sources are private or on premises you’ll have to set up an on-premises data gateway.
Once the data gateway is installed on a Windows machine, you’ll have to follow the same steps mentioned in the video for installing the respective driver on the Windows machine where gateway is installed.
After that, in Power BI service you can easily create a gateway connection in manage connections and gateways section. Just go to connections tab and create a new one with the same credentials you used with Power BI desktop. If you have any questions during the process feel free to post them here.
@@InfernusTech Does it also work in Personal Mode Data Gateway?
@lakshmiprasanna9261 Did you publish to powerbi service and did it work for you?
Bhai can you make a small real time project. Fetching data from a api and then automating it to give a real-time report. Please bhai make a video on this 🙏
Definitely! Stay tuned for more such videos.
Such a disruptor !
Thanks for watching!