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.
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?
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?
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.
@BrianJ - Thank You for providing me the opportunity to answer this question.
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.
