Creating MySQL Select Query vs Importing Entire Table

[jfenico]

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

[corbusier · Accepted solution]

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]

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