PowerBi Time Out error

[Addy2020]

I'm merging couple of objects in Salesforce, around half a million records. Worked fine until last merging but when I merged a csv with it & then expanded columns, it took lot of time & ultimately timed out. Kindly help.

[ReportBuilder07]

Hi @Addy2020 I know this reply is very late, but were you able to find a solution? I've used the Salesforce connector but I had some issues with my data. As a workaround, maybe you can try to test your connection with a 3rd party connector. I've tried windsor.ai, supermetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the Salesforce connector in the data sources list:

After that, just grant access to your Salesforce account using your credentials, then on preview and destination page you will see a preview of your Salesforce fields:

 There just select the fields you need. It is also compatible with custom fields and custom objects, so you'll be able to export them through windsor.  Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url. 

[v-stephen-msft]

Hi @Addy2020 ,

Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your problem, such as screenshots.

Best Regards,
Stephen Tao

[Addy2020]

Hi @v-stephen-msft ,

Not yet. What you provided is a lot of suggestions. Will take me time to try each of them in an already slow PowerBi Salesforce connection with more than half a million records.

[v-stephen-msft]

Hi @Addy2020 ,

Please check if the following posts are helpful to you:

https://community.powerbi.com/t5/Desktop/How-to-set-command-timeout-option/td-p/487941

https://community.powerbi.com/t5/Desktop/Query-Timeout-on-update/td-p/53459

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Addy2020]

Hi @v-stephen-msft 

Thanks for your response. But its timing out after 5 hours, it should not be taking that much time. Its half a miillion Salesforce record I'm merging with 17k csv file. Any tips to reduce that time?

[v-stephen-msft]

Hi @Addy2020 ,

I listed some optimization operations, please check the reference link for specific information.

Data reduction techniques for Import modeling:

   Remove unnecessary columns
   Remove unnecessary rows
   Group by and summarize
   Optimize column data types
   Preference for custom columns
   Disable Power Query query load
   Disable auto date/time
   Switch to Mixed mode

A DirectQuery model can be optimized in many ways, as described in the following bulleted list.

   Hide the one-side column of relationships
   Avoid relationships on calculated columns
   Keep measures simple
   Do not use Power Query relative date filtering
   Examine the use of calculated columns and data type changes
   Avoid complex Power Query queries
   Avoid relationships on "Unique Identifier" columns
   Set relationships to enforce integrity
   Avoid use of bi-directional relationship filtering
   Limit parallel queries

Reference: Optimization guide for Power BI

                 Data reduction techniques for Import modeling

                 Optimize data source performance

                 Optimize model design

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ibarrau]

Hi. I'm sorry to say that power query merge operations have horrible performance :S
You may want to try some magic from Chris Webb blog. For example:

https://blog.crossjoin.co.uk/2020/06/07/optimising-the-performance-of-power-query-merges-in-power-bi...

Hope that helps,

[Addy2020]

As per Chris's solution one Join columns should be already sorted. Not true in my case. Moreover its timing out during Expand Columns step.