Dynamics 365 Odata feed Refresh Timeout

Hello,

I received a huge report that is linked to D365 f&o.
i worked a lot on it to have it go from 120 MB to 6MB and removed 50 of 60 calculated columns to optimize it as much as possible as well as did incremental refresh.

the problem is with the salesInvoiceLines table because it was about 600k rows. now with the filtering in Query editor i’m down to 290k rows.

Power bi service is still not able to refresh it within 2 hours and is timing out.

the whole row count is about 400k now between all reports.

Can anybody help?

thank you

Have you established if query folding is taking place?
That will depend on the source and the transformations… read more here.

hi @Melissa

Source is Odata feed and query folding is not taking place.

The only two changes ive made are filtering to decrease number of rows till 299k and set incremental refresh.

Is it normal that 400k rows can’t be refreshed in service?

@rawad_hachem,

I have a D365-based report using an odata connector that does a nightly scheduled refresh over 300K records and has no problems.

  • Brian

hi @BrianJ,

I have 10 calculated columns in the salesInvoiceLines to be able to have the invoiced items for the past 10 weeks. do you think this might be the problem?

@rawad_hachem,

I just looked at my model again, and I have 11 calculated columns. However none of them are particularly calculation intensive - mostly just lookups to provide plain language labels for the Dynamics choice set fields. Do you have any complex ones built on integrating functions?

  • Brian

no it’s just looking for invoiced quantity between 2 dates that’s it

@rawad_hachem,

Hmmm…that shouldn’t present a problem then. I’m looping in Enterprise DNA expert @Harsh, who knows a lot more about the nuts and bolts of operating in the service than I do to see if he has any ideas.

  • Brian
3 Likes

Hello @rawad_hachem,

Thank You for posting your query onto the Forum.

@BrianJ - Thank You for providing me the opportunity to answer this question. :slightly_smiling_face:

Well, although the Power BI is not able to refresh in the Service. I strongly feel that the real problem lies into the data itself. I saw the post where you’ve mentioned that your data contains the “10 Calculated Columns”. (10 calculated columns in the salesInvoiceLines to be able to have the invoiced items for the past 10 weeks and that too for looking at some invoiced quantity between 2 dates).

Just few minutes before @BrianJ looped me into this post. I was watching one of the video from Guy In A Cube where Adam Saxton was talking about this similar of scenario about the “Slow Refresh”. Just to get going I’m providing a link of that video below. Also is it possible for you to convert those 10 columns into the measures because it’s just simply increasing the breadth of the data and therefore every time you write a formula it will have to do tremendous iterations through the entire table.

Also came across through the article from the Microsoft based on this topic. I’m providing a screenshot below of the main points as well as link of the entire article. (2nd Point is very important in your case)

Lastly, if you’re having and using a DAX Studio in your machine. Can you please check which column is actually taking the huge amount of time? And accordingly we can take further course of action since I also not a PRO using at DAX Studio and then accordingly we can loop in the experts, if required at all.

Hoping you find this useful and atleast we can start onto something using this links of articles and videos. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

https://prologika.com/configuring-power-query-odata-feeds/

2 Likes

hi @Harsh,

Thank you for your fast reply. I will take a look at the links you’ve sent to be able to solve this issue.

cheers

Hello @rawad_hachem,

You’re Welcome. :slightly_smiling_face:

I’m glad that we all were able to assist you.

Thanks and Warm Regards,
Harsh