MySQL Timeout OLE DB or OBDC error

[ADevgun]

I am new to power bi and wanted to bring in data from a mysql server but it keeps timing out.

OLE DB or OBDC error: timeout expired. the timeout period elapsed prior to completion of the operation or the server is not responding.

I believe the tables have over 40 million rows, it is data logged every second for the past 3 years for a industrial plant. Is there a work around to the timeout error? Not sure if this needs to be done on the mysql end or within power bi.

Or if its possible to transform the data to a smaller quantity in seperate tables although i would prefer it all together.

Any help is appreciated, thanks.

[Anonymous]

Hi @ADevgun ,

Please try below ways:

1. Please try to do data cleaning in MySql Server side, filter some unuseful data firstly

2. Please try to optimize your MySql table, it will speed up the query efficiency

3. In Power BI, Use measure as much as possible and avoid using calculated column, also could try to optimize the dax 

4. In Power BI Service, bacause your data is too many, if you want to refresh many data by once, please try incremental refresh, it will reduce the refresh time when you refresh the dataset secondly.(Incremental refresh for datasets and real-time data in Power BI - Power BI | Microsoft Learn)

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GilbertQ]

Hi @ADevgun 

You could use incremental refresh for your dataset so that it only loads the last few days worth of data instead of all the data?

Here is a blog post on how to set this up: Incremental refresh for datasets and real-time data in Power BI - Power BI | Microsoft Learn

[ADevgun]

Unfortunately the data I need access too is between 2020-2021, so i will need incremental refreshes for real time analysis but the access to the older data is also very necessecary.

Even when applying a filter for data between a 6 month period and deleting half the columns to break the data down further it continues to time out.