cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jfenico Regular Visitor
Regular Visitor

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 Regular Visitor
Regular Visitor

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
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 251 members 2,875 guests
Please welcome our newest community members: