OLD DB or ODBC error received when applying query changes

[jreynoldsfw]

We were successfully using PowerQueryBuilder until a few weeks ago. This past week we started working with it again, and we are having trouble. It's taking 3-4 times as long to build the query results. Then when we close and apply it seems to process for a while and then results in errors on each query indicating

"Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [DataSource.Error] The request was aborted: The request was canceled.."

We are simply working with the account entity and only selected fields. Our database has approximately 320,000 account records. We have the Queries set up for the ServiceRootURL and Dyn365CEBaseURL as you've had described a while back. Did these change at all with the release you did in April 2019? Or is there something different we should be doing in the process to grab the fetch xml?

Coding is not my thing, so I'm struggling to figure out what might be causing the error. Any help or direction you can provide is much appreciated. This tool and your process had significantly improved our Power BI report building. Now I just need to figure out why it has stopped working.

[UlrikBC]

I have occasionally seen that error, although it has always been resolved by retrying or refreshing the query again. I've seen it mostly in context of adding another table to an existing dataset. I have not seen the error where it persisted. The only change to the query since the last version was adding the RelativePath which gives you some benefits in the Power BI service. On the desktop, the RelativePath performs the same as the previous version. I've tested that with up to 275k records. I will say anecdotally, that I have seen cases where the load takes longer than it used to, but I haven't seen a pattern on this yet or been able to identify any issues specifically.

[jreynoldsfw]

Thanks for your reply, Ulrik. I have continued testing today to see if I can see any patterns. I just rebuilt a account FetchXML query that was still working for us without the RelativePath. When I rebuilt it, it now uses the RelativePath and it worked. What I noticed in the FetchXML was it listed every attribute in the first section after S = Json…. on the one that worked. On the ones that are failing, it says "all-attributes". I've made sure not to select the all attributes checkbox, so I know that's not causing the difference. Further testing showed that I can select 14 attributes and to include the link and I'll get the list of attributes. If I have 15 attributes and include the link, it switches to "all-attributes". Is that the expected behavior?

15 isn't any where near all of the attributes on the account form. I'm wondering if it's dying because it's trying to pull all the attributes from the account form and just takes forever.

Hopefully, my explanation makes sense. I don't know the technical language!

[UlrikBC]

I see. Yes, that makes sense. When you include a certain number of fields, the tool will switch to using instead. That is because the fetchxml is limited on a character count when specifying fields etc. If you have a lot of short field names, you could likely have a lot more than 14, so it depends. You do get better performance on the refresh if you can specify the individual fields. That being said, it shouldn't fail when using , but with your high record count, that could be an issue. Especially if you have a lot of fields. I'd recommend creating a query with the 14 fields, and then manually add fields until you get an error. Hopefully you can include everything you need.

[jreynoldsfw]

Ok, that's helpful. Thanks, Ulrik!