Business Central API limitations

Question

Hello Everyone,

I have been working on project aiming to build a Dashboard for my company's management team. I have been using Azure Data Factory to copy data from Business Central to Azure SQL BD. At some point, I realized that the API does not return the maximum page size. As an Example, one of the pages I need to extract has 26800 entities and I am only getting 20000 entities in the API JSON response. I have done some research and I found out that Microsoft limits the usage of the API, to manage the performance of the Business Central SaaS servers. These Limitations also apply to OData and SOAP web services where each page can have a maximum of 20k entities. I am looking for a solution that enables me to get all the data entities from each table I am using to build the dashboard. I spent the last 3 weeks reading Microsoft Learn but I have not found anything yet and I am writing this up seeking help from experts ore developers who have faced the same issue before. Thank you in Advance.

Answer 1

Hi @Osama Ahmed ,

Thanks for posting query in Microsoft Q&A Platform.

As your API response crossing the allowed limit, you need to consider making multiple API calls which gives the response in multiple splits or parts. That way, you can collect all response in multiple splits or chunks and combine them if needed on Storage.

Please let us know how it goes.

----------

Please consider hitting Accept Answer button. Accepted answers help community as well.

Answer 2

Hope below may help you (still not sure, how consumptions of APIs are flexible at Azure Data Factory). Using below parameters/filters you may get the next set of record until you get the complete: combination of $Count & $skip will help to analyze for number of api calls (for each 20K recorded until all records are retrieved).

$top: Default optional filter in API accepts integer value. Can be used to get the defined top records in response. For example: https://{uat/prod-domain}/companies?$top=10

$count: Default optional filter in API accepts true or false. Can be used to get the count of all records available in system. For example: https://{uat/prod-domain}/companies?$count=true

$orderby: Default optional filter in API accepts value as filed name to sort the records. For example: https://{uat/prod-domain}/itemMaster?$orderby=name

$skip: Default optional filter in API accepts integer value to skip those records. For example: https://{uat-domain}/companies?$skip=10 skip the first 10 records in the item table based on applied filter/parameters.

$expand: Use this filter to expand (show) the child entity/objects if any nested child entity exists.
Filters on Filed Value:
{$filter=<API Field Name> <Operator> <Value>}

To add a filter to URI, add $filter= to the end of the name of the published API service. For example, the following URI filters the name field in the companies to return all information belongs to given name. Eg.: https://{uat/prod-domain}/companies?$filter=name eq 'Demo'

Please go through the link Using Filter Expressions in API/OData URIs - Click Here for more details on filter expressions and go through the link Nested query options in select query - Click Here for nested query option.

Hope this may help you.