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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
jfenico
Helper III
Helper III

Creating MySQL Select Query vs Importing Entire Table

Hello folks,

 

I'm at a crossroad and need some advice on best practices. Right now, the power bi dashboard I'm creating is working great and what we plan to do later is roll out to more viewers. However, thinking ahead, the database that we are bringing information from is both a MySQL database and is responsible for running other services. Right now. Power BI imports records via a regular MySQL data source import. Certain tables have over 135,000 rows before filtering out the data we want. My thought is, as this grows it could become harder for PowerBI and MySQL to manage this information without causing performance issues.

 

I'm wondering if it possible to run MySQL Select statements within Power BI so that instead of importing the 135,000 rows, I can filter it before importing into Power BI. I know you can run Direct Queries with SQL, but not MySQL. And I know you can create Blank Queries from scratch but it appears a simple copy/paste from MYSQL does not work as Power BI has its own way of formatting. Are there any instructions on how to bring queries from MYSQL over to Power BI? OR is my thought of bringing in more and more rows even an issue down the line?

 

Thanks,

 

J

1 ACCEPTED SOLUTION
corbusier
Advocate IV
Advocate IV

When you are importing the tables, you should be able to click the "Advanced" button and then enter the MySQL query, which should work unless you are using some MySQL functions that somehow don't work when passed via the Power BI JDBC interface. 

 

As for what is better, it really depends on your needs - however, in your position I would try to set up a slave DB at least, so that you're not hitting the Production database with every query. You can further reduce the load by using Import instead of DirectQuery, and setting up a refresh schedule that hits the DB at non-peak times, once published.

View solution in original post

2 REPLIES 2
corbusier
Advocate IV
Advocate IV

When you are importing the tables, you should be able to click the "Advanced" button and then enter the MySQL query, which should work unless you are using some MySQL functions that somehow don't work when passed via the Power BI JDBC interface. 

 

As for what is better, it really depends on your needs - however, in your position I would try to set up a slave DB at least, so that you're not hitting the Production database with every query. You can further reduce the load by using Import instead of DirectQuery, and setting up a refresh schedule that hits the DB at non-peak times, once published.

Thanks corbusier, for some reason I wasn't seeing the advanced option until you called it out! 

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.