Dataset refresh timeout

[SteffenH82]

Hi community,

we have our sales data and employee working hours in MySQL tables. I import this data and transform it for Power BI using Power Query. However, this process now takes several hours, which leads to a timeout during the dataset refresh in Power BI online.

What would you suggest? Should I perform the data processing beforehand, e.g., using a PHP script, and make the prepared data available in a new MySQL table for Power BI? What is the best practice here?

Thanks for your help!

Steffen

[Joe_Barry · Accepted solution]

Hi @SteffenH82 

I would recommend preparing the data as much as possible at the source. Power Query is powerful, but many transformations on big datasets can cause these issues. When connecting to the Database, you can use the Advanced Options to set a timeout

You will be probably aware, but I will mention it anyway. Try and keep the transaction tables as slim as possible, only have ids, dates etc in these tables. If the employee table has Date/Time columns, if you don't need the time, then convert to date only. If you do need the time, create a separate column for the time part. The more cardinality in the columns the faster the load. Move all teh information like products, employee, date, time infomation etc.. to dimension tables. More info here

In the options section of Power BI Desktop, you can set some settings to help with loading times. Turn off Time intelligence if you are using a date table.

Hope these tips help

Joe