cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jfenico Member
Member

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

Accepted Solutions
corbusier Regular Visitor
Regular Visitor

Re: Creating MySQL Select Query vs Importing Entire Table

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.

2 REPLIES 2
corbusier Regular Visitor
Regular Visitor

Re: Creating MySQL Select Query vs Importing Entire Table

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.

jfenico Member
Member

Re: Creating MySQL Select Query vs Importing Entire Table

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 135 members 1,570 guests
Please welcome our newest community members: