Be smart about Business Central OData, $filter like a Pro!

[@phduck]

This is an absolute must.
This is often called predicate pushdown, essentially taking a filter predicate and applying it further down the stack. We in the server take the filter predicate and apply it to the record and create a filtered SQL query over a unfiltered query.
The main reason it can affect your other processing in BC is that other (f.e. UI) operations relies on the same database and putting more load on it will affect all operations.

[@leendertpaulditerwich8626]

Hi Erik, thank you so much for these video's. It's incredibly valuable for this lone AL developer saving countless hours of googling. However from BC14 -> BC20 ->BC21 we are seeing longer response times for both OData and API calls made to BC sandbox docker images via Postman (100ms vs 1500ms) any thoughts or tips on this?

[@Hougaard]

Sounds like you got stuff on the onopencompany trigger

[@leendertpaulditerwich8626]

@@Hougaard Been guilty of that! For a rainy afternoon: pull plain Onprem BC14 and BC21 in docker. One Extension Codeunit just returning a string. Unbound call in rest client: BC14 takes 10ms, BC21 takes 1300ms. Still looking why...

[@Hougaard]

Put it on Twtiter, tag me, Kennie and Jesper.

[@bjarkihall2876]

How about paging, like getting records 100-150 and then 150-200, etc. I used to think $top or $skip would make sense but I can see client-driven pagination is discouraged by MS in the docs (titled OData Query Performance, can't link to it from a youtube comment though).
Have you tried server-driven pagination?

[@Hougaard]

That's absolute needed, BC OData requires paging for larger datasets.

[@clarasdk]

Great video! Do you have any good hacks to handle reporting across multiple companies using standard Odata? I know there is a hack when using the dedicated Dynamics 365 Business Central (On-premises) service as a source for data in Power BI, but that source type is not working when using the onprem gateway.....

[@Hougaard]

I'll suggest you take a look at the Cloud Replicator from EFOQUS www.efoqus.ca/products/cloud-replicator/

[@yuryzolotykh8564]

We performed tests for PowerBI and OData. The source of OData was app tables (e.g. T32 & T32$guid1 & T32$guid2 and so on). And we found out that OData feed is 5-10 times slower then direct SQL access to database...

[@clarasdk]

@@yuryzolotykh8564 We are fully aware that the Odata integration is slower than direct SQL. But they give alot of benefits in regards of integration, access to "business logic" data like type values instead of the int numbers stored in the database and most important it handles joining extension tables into one instance. Joining extensions in a SQL interface is a pain in the ass. But yes, hopefully Microsoft finds a way to make the odata import faster. We are currently looking into doing delta updates to improve load times.

[@joshanglesea9017]

Power BI removes any filters you do on an endpoint. You have to redo them in Power Query. I think some of the one’s at the bottom of the list aren’t supported for use with API pages. I got errors testing in postman and power automate

[@Hougaard]

What errors?

[@joshanglesea9017]

@@Hougaard example is "An unknown function with name 'substringof' was found. This may also be a function import or a key lookup on a navigation property, which is not allowed. That's when doing this filter: $filter=substringof(displayName, 'Xpe') with the vendors endpoint. Flip it to one like $filter=startswith(displayName, 'Xpedition') and it works no problem

[@Hougaard]

I don't see substringof on this list docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/webservices/use-filter-expressions-in-odata-uris