Forum Discussion
oData Query To Dynamics 365 Does Not Fold
Ok, this actually works and it's significantly faster. URL-length doesn't seem to be a problem.
OData.Feed("https://xxx.operations.dynamics.com/data/VendorProductDescriptionsV2?$select=ExternalItemId,ItemNumber&$filter=ExternalItemId eq '" & Text.Combine(#"externalIDlist", "' or ExternalItemId eq '") & "'", null, [Implementation="2.0"])It's still quite slow though.
Why are these "professional" Microsoft products such a mess. 🙃
Thanks for the input!
Thanks for the quick response.
buffering will stop the folding.
I know, I only buffer after the filtering, once I got all my data from that feed, so the query is not triggered multiple times during further processing.
Also, ignore what Power Query indicates about folding, as that might be wrong.
That it good to know, lol.
Just check with monitoring the web traffic.
Will do. Maybe it's just slow, because our Dynamics instance is a mess. 🙃
This simple example query above takes forever.
I don't believe D365 using OData supports much query folding, if any. OData is inherently slow and I strongly recommend that you use a different approach. OData is not reliable unless your data volume is really low (less than 20,000 rows in each table)
Assuming you are using D365 F&SC (aka D365 F&O), I advise you to use Export to Azure Data Lake
The query result is not very large, like not even 100 tables per query. The table I query from however is very large. So if I just write my own oData query (never done that before), I should be good, right?
Like this?
OData.Feed("https://xxx.operations.dynamics.com/data/VendorProductDescriptionsV2", null, [Implementation="2.0", Query="$select=ExternalItemId,ItemNumber&$filter=ExternalItemId in ('F000_68465165848', 'F000_68465165849', 'F000_68465165850')"])
Julian23K wrote:The query result is not very large, like not even 100 tables per query. The table I query from however is very large. So if I just write my own oData query (never done that before), I should be good, right?
You can give it a try. Please note that the objects you query in D365 are entities (since tables are not directly exposed via OData). An entity is like a SQL view, which means each entity you are querying might be executing a complicated sql code involving several other tables.
Please note this important advice - Everything may still work for you now, but a year or two later it may not. I am saying this based on on my own experience. Also keep in mind that the refresh duration usually takes longer after you publish your model to Power BI service. If it takes longer than 90 minutes or so (I don't remember the exact limit), it will time-out Power BI service will cancel the refresh (if you are using Power BI pro).Ok, this actually works and it's significantly faster. URL-length doesn't seem to be a problem.
OData.Feed("https://xxx.operations.dynamics.com/data/VendorProductDescriptionsV2?$select=ExternalItemId,ItemNumber&$filter=ExternalItemId eq '" & Text.Combine(#"externalIDlist", "' or ExternalItemId eq '") & "'", null, [Implementation="2.0"])It's still quite slow though.
Why are these "professional" Microsoft products such a mess. 🙃
Thanks for the input!