Forum Discussion

SteffenH82's avatar
SteffenH82
New Member
2 years ago
Solved

Dataset refresh timeout

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

Need Help
  • Joe_Barry's avatar
    Joe_Barry
    2 years ago

    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

     

1 Reply

  • Joe_Barry's avatar
    Joe_Barry
    Icon for Solution Sage rankSolution Sage
    2 years ago

    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