Performance Issues with ODBC

[thmonte]

We keep most of our data within MongoDB and have setup BI Connector mySQL to access this data in other reporting programs.  We havent ever ran into any issues using BI Connector until we've started moving forward with PowerBI.  First off, BI Connector for MongoDB requires Clear Text Authentication plugin.  The native data connector in PowerBI for mySQL doesn't support this so I've been forced to connect to mySQL using ODBC.

My ODBC is setup using MySQL ODBC 8.0 Unicode Driver

This data source pulls in 3,000,000 plus rows.  I try to limit and data shaping to MySQL query rather than doing any work in Power Query.

The amount of time it takes for me to load this into a Power BI report and work on it is painful.  I'm scared to make any changes to my reports.  If I were to make any Power Query changes I have to wait over 2 hours some times for it to fully load in.

How can I make this expereince better?  Is there any benfit to using the native MySQL source rather than ODBC?  Can I make changes to the Power Query for the ODBC? I noticed there was a Row Reduction Clause option in the Power Query but will this limt the data across all reports or just for loading into Power Query?  When my report runs an update it takes over 40 minutes on app.powerbi.

Please help!  I can't get any work done because I am sitting here watching a spinning wheel all day.

[v-yiruan-msft]

Hi @thmonte

Hope the following documentations can help you.

Optimization guide for Power BI

ODBC Data Sources, The SqlCapabilities Option And Power Query/Power BI Data Refresh Performance

Connect to MY SQL from Power BI

PowerBI With MySQL: Connection Challenges And Alternatives

Best Regards

Rena

[thmonte]

@v-yiruan-msft I've read these documentations.  I had follow up questions which is why I made this post.

How can I make this expereince better?  Is there any benfit to using the native MySQL source rather than ODBC?  Can I make changes to the Power Query for the ODBC? I noticed there was a Row Reduction Clause option in the Power Query but will this limt the data across the reports (meaning will only 1000 rows ever be available to report on) or just for loading into Power Query?

[v-yiruan-msft]

Hi @GilbertQ ,

Could you please help answer these problems from @thmonte ? Thanks a lot for your help and time in advance.

---

How can I make this expereince better?  Is there any benfit to using the native MySQL source rather than ODBC?  Can I make changes to the Power Query for the ODBC? I noticed there was a Row Reduction Clause option in the Power Query but will this limt the data across the reports (meaning will only 1000 rows ever be available to report on) or just for loading into Power Query?

---

Best Regards

Rena

[GilbertQ]

Hi there

Using the Navtive MySQL source will allow for potential query folding, which means you could then use incremental refreshing too.

With regards to the Query Reduction, my understanding is that this will limit the amount of rows returned when using Power Query. But once the dataset loads it will load all the data.