Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
useruserhi91
Helper I
Helper I

Dataflow Gen2 execution extremely slow and fails when expanding BC365 data

In dataflow gen2, I am extracting BC365 data from an advanced/custom API. The data is salesInvoiceLines from BC (so the parent for this entity is "salesInvoices").

 

There are 350.000 rows for salesInvoiceLines, so this is not a lot of rows.

 

Even though there are not many rows, the dataflow gen2 has extremely poor performance. Pulling GLentries (8 million rows), I can easily do this by just selecting the GLentry API/table.

 

But for salesInvoiceLines of 350.000 rows, it can't even complete. Note that the query WORKS when I just e.g. select 10 rows, so I am completely sure that the reason is NOT the query.

 

So my question is: How do I performance optimize this? Incremental load is not an option. Also, I have tried with staging enabled/disabled and fast copy already. What am I doing wrong?

 

Here are my steps, and i HIGHLY think that the "Unpivot" and "Expand" steps are problematic. In the applied steps, I first navigate to "salesInvoices" (the parent) and then expand "salesInvoiceLines". Note that I cannot simply go straight to salesInvoiceLines, as I would then get this error: "(You must specify an Id or a Document Id (parent id from 'salesInvoices') to get the lines.)"

useruserhi91_0-1738925916513.png

 

Thanks.

 

1 ACCEPTED SOLUTION

Hi @useruserhi91 

 

I understand your frustration. If you have already done these things, then you might want to consider whether it is related to capacity.

 

Capacity issues can sometimes affect the performance of Dataflow Gen2. Make sure Dataflow Gen2 has enough resources allocated to handle your data volume. If there is insufficient capacity, it may also cause performance degradation or job failures.

 

You can get detailed refresh activity information in the Monitoring Center, including the status, start and end time, duration, and CU usage of each activity. This may be helpful to you.

 

If you are still not sure what the issue is, you can create a support ticket and a dedicated Microsoft engineer will resolve the issue for you.

 

It would be great if you continue to share in this issue to help others with similar problems after you know the root cause or solution.

 

The link of Power BI Support:  https://powerbi.microsoft.com/en-us/support/

 

For how to create a support ticket, please refer to How to create a support ticket in Power BI - Microsoft Fabric Community

 

Thank you for your understanding.

 

Regards,

Nono Chen

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

 

 

 

View solution in original post

4 REPLIES 4
CelesteBledsoe
New Member

My problem is solved, Thank you so much.

v-nuoc-msft
Community Support
Community Support

Hi @useruserhi91 

 

It sounds like you're running into a performance bottleneck with Dataflow Gen2, so let me offer you some suggestions for optimizing your data flow:

 

Given the structure of your data and the requirement to navigate through "salesInvoices" to access "salesInvoiceLines", apply filters as early as possible in your dataflow to reduce the amount of data being processed. For example, if you can filter "salesInvoices" before expanding "salesInvoiceLines," it will reduce the data volume.

 

Perform unpivot operations as early as possible and only on the necessary columns. This can help reduce the data volume and improve performance.

 

If possible, process the data in smaller batches. You can create multiple dataflows, each handling a subset of the data, and then combine the results.

 

Regards,

Nono Chen

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

I did everything, and smaller batches do work, but only occasionally. It is highly unstable. Also, the total dataset has 350k rows only, so I don't see why this is an issue. If that's the case, DF gen2 is really not a scalable or viable solution for data ingestion. Especially also due to transient errors and debug codes that are very hard to understand. Any other suggestions?

Hi @useruserhi91 

 

I understand your frustration. If you have already done these things, then you might want to consider whether it is related to capacity.

 

Capacity issues can sometimes affect the performance of Dataflow Gen2. Make sure Dataflow Gen2 has enough resources allocated to handle your data volume. If there is insufficient capacity, it may also cause performance degradation or job failures.

 

You can get detailed refresh activity information in the Monitoring Center, including the status, start and end time, duration, and CU usage of each activity. This may be helpful to you.

 

If you are still not sure what the issue is, you can create a support ticket and a dedicated Microsoft engineer will resolve the issue for you.

 

It would be great if you continue to share in this issue to help others with similar problems after you know the root cause or solution.

 

The link of Power BI Support:  https://powerbi.microsoft.com/en-us/support/

 

For how to create a support ticket, please refer to How to create a support ticket in Power BI - Microsoft Fabric Community

 

Thank you for your understanding.

 

Regards,

Nono Chen

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

 

 

 

Helpful resources

Announcements
MarchFBCvideo - carousel

Fabric Monthly Update - March 2025

Check out the March 2025 Fabric update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

"); $(".slidesjs-pagination" ).prependTo(".pagination_sec"); $(".slidesjs-pagination" ).append("
"); $(".slidesjs-play.slidesjs-navigation").appendTo(".playpause_sec"); $(".slidesjs-stop.slidesjs-navigation").appendTo(".playpause_sec"); $(".slidesjs-pagination" ).append(""); $(".slidesjs-pagination" ).append(""); } catch(e){ } /* End: This code is added by iTalent as part of iTrack COMPL-455 */ $(".slidesjs-previous.slidesjs-navigation").attr('tabindex', '0'); $(".slidesjs-next.slidesjs-navigation").attr('tabindex', '0'); /* start: This code is added by iTalent as part of iTrack 1859082 */ $('.slidesjs-play.slidesjs-navigation').attr('id','playtitle'); $('.slidesjs-stop.slidesjs-navigation').attr('id','stoptitle'); $('.slidesjs-play.slidesjs-navigation').attr('role','tab'); $('.slidesjs-stop.slidesjs-navigation').attr('role','tab'); $('.slidesjs-play.slidesjs-navigation').attr('aria-describedby','tip1'); $('.slidesjs-stop.slidesjs-navigation').attr('aria-describedby','tip2'); /* End: This code is added by iTalent as part of iTrack 1859082 */ }); $(document).ready(function() { if($("#slides .item").length < 2 ) { /* Fixing Single Slide click issue (commented following code)*/ // $(".item").css("left","0px"); $(".item.slidesjs-slide").attr('style', 'left:0px !important'); $(".slidesjs-stop.slidesjs-navigation").trigger('click'); $(".slidesjs-previous").css("display", "none"); $(".slidesjs-next").css("display", "none"); } var items_length = $(".item.slidesjs-slide").length; $(".slidesjs-pagination-item > button").attr("aria-setsize",items_length); $(".slidesjs-next, .slidesjs-pagination-item button").attr("tabindex","-1"); $(".slidesjs-pagination-item button").attr("role", "tab"); $(".slidesjs-previous").attr("tabindex","-1"); $(".slidesjs-next").attr("aria-hidden","true"); $(".slidesjs-previous").attr("aria-hidden","true"); $(".slidesjs-next").attr("aria-label","Next"); $(".slidesjs-previous").attr("aria-label","Previous"); //$(".slidesjs-stop.slidesjs-navigation").attr("role","button"); //$(".slidesjs-play.slidesjs-navigation").attr("role","button"); $(".slidesjs-pagination").attr("role","tablist").attr("aria-busy","true"); $("li.slidesjs-pagination-item").attr("role","list"); $(".item.slidesjs-slide").attr("tabindex","-1"); $(".item.slidesjs-slide").attr("aria-label","item"); /*$(".slidesjs-stop.slidesjs-navigation").on('click', function() { var itemNumber = parseInt($('.slidesjs-pagination-item > a.active').attr('data-slidesjs-item')); $($('.item.slidesjs-slide')[itemNumber]).find('.c-call-to-action').attr('tabindex', '0'); });*/ $(".slidesjs-stop.slidesjs-navigation, .slidesjs-pagination-item > button").on('click keydown', function() { $.each($('.item.slidesjs-slide'),function(i,el){ $(el).find('.c-call-to-action').attr('tabindex', '-1'); }); var itemNumber = parseInt($('.slidesjs-pagination-item > button.active').attr('data-slidesjs-item')); $($('.item.slidesjs-slide')[itemNumber]).find('.c-call-to-action').attr('tabindex', '0'); }); $(".slidesjs-play.slidesjs-navigation").on('click', function() { $.each($('.item.slidesjs-slide'),function(i,el){ $(el).find('.c-call-to-action').attr('tabindex', '-1'); }); }); $(".slidesjs-pagination-item button").keyup(function(e){ var keyCode = e.keyCode || e.which; if (keyCode == 9) { e.preventDefault(); $(".slidesjs-stop.slidesjs-navigation").trigger('click').blur(); $("button.active").focus(); } }); $(".slidesjs-play").on("click",function (event) { if (event.handleObj.type === "click") { $(".slidesjs-stop").focus(); } else if(event.handleObj.type === "keydown"){ if (event.which === 13 && $(event.target).hasClass("slidesjs-play")) { $(".slidesjs-stop").focus(); } } }); $(".slidesjs-stop").on("click",function (event) { if (event.handleObj.type === "click") { $(".slidesjs-play").focus(); } else if(event.handleObj.type === "keydown"){ if (event.which === 13 && $(event.target).hasClass("slidesjs-stop")) { $(".slidesjs-play").focus(); } } }); $(".slidesjs-pagination-item").keydown(function(e){ switch (e.which){ case 37: //left arrow key $(".slidesjs-previous.slidesjs-navigation").trigger('click'); e.preventDefault(); break; case 39: //right arrow key $(".slidesjs-next.slidesjs-navigation").trigger('click'); e.preventDefault(); break; default: return; } $(".slidesjs-pagination-item button.active").focus(); }); }); // Start This code is added by iTalent as part of iTrack 1859082 $(document).ready(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); $("#tip2").attr("aria-hidden","true").addClass("hidden"); $(".slidesjs-stop.slidesjs-navigation, .slidesjs-play.slidesjs-navigation").attr('title', ''); $("a#playtitle").focus(function(){ $("#tip1").attr("aria-hidden","false").removeClass("hidden"); }); $("a#playtitle").mouseover(function(){ $("#tip1").attr("aria-hidden","false").removeClass("hidden"); }); $("a#playtitle").blur(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); }); $("a#playtitle").mouseleave(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); }); $("a#play").keydown(function(ev){ if (ev.which ==27) { $("#tip1").attr("aria-hidden","true").addClass("hidden"); ev.preventDefault(); return false; } }); $("a#stoptitle").focus(function(){ $("#tip2").attr("aria-hidden","false").removeClass("hidden"); }); $("a#stoptitle").mouseover(function(){ $("#tip2").attr("aria-hidden","false").removeClass("hidden"); }); $("a#stoptitle").blur(function(){ $("#tip2").attr("aria-hidden","true").addClass("hidden"); }); $("a#stoptitle").mouseleave(function(){ $("#tip2").attr("aria-hidden","true").addClass("hidden"); }); $("a#stoptitle").keydown(function(ev){ if (ev.which ==27) { $("#tip2").attr("aria-hidden","true").addClass("hidden"); ev.preventDefault(); return false; } }); }); // End This code is added by iTalent as part of iTrack 1859082
Top Solution Authors
Users online (1,207)