Forum Discussion

aspardo's avatar
aspardo
Regular Visitor
10 years ago

New to BI. Writing SQL queries to parse data. I am obviously missing something.

Started using Power BI on the recommendation of a friend. It looks well made and powerful, so I hope to stick with it.   I am pretty decent with SQL and can write some lengthy queries. My goal is t...
Need Help
newpi's avatar
newpi
Icon for Helper V rankHelper V
6 years ago

alwweb 
I'm trying to run a MYSQL query within Power BI. I have connected to MYSQL using an ODBC connector. I was facing issues connecting directly with MYSQL and hence went the ODBC route and that worked for.
Now all I want to do is run a  MYSQL query I have written with multiple joins and everything within Power BI. The query runs fine and gives me result in a MYSQL client but unable to make it run in Power BI.

alwweb's avatar
alwweb
Icon for Advocate II rankAdvocate II
6 years ago

newpi  Thank you for the additional info.

 

I just tested an ODBC connection to one of my Microsoft SQL databases (this is all I have right now) and it worked.  I created an ODBC connection that said it was 32/64 bit and a User DSN.  I would assume it needs to match the PowerBI "bitness" and the server bitness.

 

I then chose Import from ODBC and my DSN and query was as shown in the image below. It connected without any errors. On the security page, I picked Windows security, but make sure that you are connecting with a method supported by MySQL. ODBC connections can be weird, so I'm sorry that I don't have a  MySQL server to test this with to help you out. 

Please let me know if this helps.

 

  • alwweb's avatar
    alwweb
    Icon for Advocate II rankAdvocate II
    6 years ago

    newpi  - my default database name is part of my DSN definition on a page with an option to change the default database. 

     

    If your databasename can't be defined in the DSN, is there a way to set it in the connection string above the SQL statement by using the database= option? 

  • newpi's avatar
    newpi
    Icon for Helper V rankHelper V
    6 years ago

    This is now solved. The 4 missing columns data was loading as Binary and I converted it to text type in the query editor and could load all columns

  • alwweb's avatar
    alwweb
    Icon for Advocate II rankAdvocate II
    6 years ago

    newpi  So glad you figured that out.  I was not even considering that as one of the troubleshooting steps I was trying to put together for you.  

  • newpi's avatar
    newpi
    Icon for Helper V rankHelper V
    6 years ago

    alwweb Doing the same steps and when I click ok. I get the error of no database selected.
     Whereas If I run without this query, I'm able to connect to the data from the pop up and selecting raw tables.

  • newpi's avatar
    newpi
    Icon for Helper V rankHelper V
    6 years ago

    alwweb Thanks. Just solved this. I just had to modify my query. In MYSQL client I was just writing and doing a select * from table name and that was working fine there but in power bi I had to do Select * from database.tablename and it worked.

    Also, but I'm facing another issue here. I have close 8 columns in my output but in the power bi after connecting with SQL optional statement I'm only seeing first 4 columns and missing the others. This query has multiple left joins so I don't know if that is the issue.

    Power BI Columns

     

    All columns in MYSQL client