Overview
The OData interface provides flexible, powerful access to datasets. However, if used without care, it can lead to performance bottlenecks, service timeouts, or degraded user experience for both clients and backend systems.
This guide outlines best practices and common pitfalls when interacting with Aptem’s OData endpoints.
Contents
- Query Scope
- Incremental Data Retrieval
- Rate Limiting and Concurrency
- Managing Data Updates
- Error Handling and Retries
Query Scope
When using the OData API, the key principle is: only request the data you actually need.
Explicit projection
By default, an OData query returns all available fields for a given entity, including large text fields or metadata you may not require. Always use the $select query parameter to specify exactly which fields you need. Refer to the OData dictionary for a complete list of available columns.
For example, instead of:
GET /odata/1.0/LearningPlanComponents
Use:
GET /odata/1.0/LearningPlanComponents?$select=Id,Title,StartDate
This approach - known as explicit projection - ensures you receive only the data that’s relevant to your application.
Filtering
In addition to limiting columns, apply $filter clauses to restrict the number of rows returned. Filtering early and specifically reduces the amount of data processed, transmitted, and parsed. This is especially important for large datasets or time-sensitive queries.
For example:
GET /odata/1.0/LearningPlanComponents?$filter=Status eq 'Active' and StartDate ge 2024-01-01&$select=Id,Title,StartDate
This retrieves only active Learning Plan Components that start after 1 January 2024, returning only the required fields.
Power Query
If you’re using Power Query-based tools like Excel or Power BI, it may be tempting to rely on “query folding“, where statements like Table.SelectColumns(Source,{"Id","Title","StartDate"}) or Table.SelectRows(Source, each [Status] = "Active" and [StartDate] >= #date(2024,1,1)), generated using UI, would automatically translate into requests above.
But not every Power Query transformation is foldable, so, for maximum efficiency, specify projection and filters explicitly at source:
OData.Feed("https://tenant.aptem.co.uk/odata/1.0/LearningPlanComponents?$filter=Status eq 'Active' and StartDate ge 2024-01-01&$select=Id,Title,StartDate")
Incremental Data Retrieval
Paging
To manage large datasets efficiently, use paging to retrieve data in smaller, manageable chunks rather than all at once.
The OData API supports paging via $top and $skip parameters:
$top– number of records per response (recommended: up to 10,000 rows)$skip– number of records to skip before returning results
For example, to retrieve the first 10000 records:
GET /odata/LearningPlanComponents?$top=10000&$skip=0
To retrieve the next 10000 records:
GET /odata/LearningPlanComponents?$top=10000&$skip=10000
Power Query
If you’re using Power Query-based tools like Excel or Power BI, you may find the following guide to implement paging using Power Query helpful.
Large datasets
This pattern can be repeated until all needed records are obtained. However, when working with very large datasets - such as millions of records - paging through all the data can require thousands of requests. This not only increases processing time and network traffic, but also puts sustained pressure on backend systems, potentially leading to degraded performance or timeouts:
To mitigate performance issues caused by attempting to retrieve too many pages at once, see: Rate Limiting and Concurrency Management.
To optimize queries for data changes and avoid unnecessary full refreshes, see: Managing Data Updates.
Rate Limiting and Concurrency
To maintain a consistent and responsive experience for all users of our OData API, it’s important to manage the frequency of your requests. Excessive concurrency or high request rates may result in slower responses, timeouts, or throttling, as we take steps to safeguard system reliability and ensure a positive experience for everyone.
General recommendations
Avoid making concurrent requests to OData API - when multiple requests need to made, run them sequentially
Introduce intentional delays or spacing between calls to avoid spikes in activity.
Avoid redundant refreshes - use a shared data source or centralised refresh process where possible.
-
Stagger refresh jobs - if multiple reports or systems need to refresh data, schedule them at different times to avoid clashing with other reports.
For example, try to avoid running four reports every hour on the hour, run one at 5 minutes past, and one at 20 minutes past, one at 35 minutes past, and one at 50 minutes past. This will still give hourly data updates, and will improve the performance of each of them.
Power Query
If you’re using Power Query-based tools like Excel or Power BI:
Make sure data sources are queried sequentially by setting Maximum number of concurrent jobs to One.
Avoid querying the same data feed multiple times - if you need different representations of the same dataset, create a single base query using
OData.Feedonce, set Enable load = Off and use reference queries to apply additional transformations.Avoid sharing same report files with users. You can publish reports for your users using Power BI, sharing the report output and avoid them making requests for the same data repeatedly.
Managing Data Updates
Another important factor in building efficient integrations with the OData API is deciding how recent your data needs to be and aligning your queries accordingly. Not all use cases require real-time or near real-time updates.
Refresh frequency
Consider data purpose and tolerance:
Operational dashboards may require hourly updates, so query only once per hour.
Strategic reports and analytics often tolerate daily or weekly refreshes, so schedule data refreshes accordingly, ideally outside peak business hours or maintenance windows.
Data caching
Store older, already retrieved data locally or in a data warehouse, and only request new or updated records from the API using fields such as UpdatedDate or CreatedDate to retrieve only recently changed records, matching your refresh frequency.
For example:
GET /odata/LearningPlanComponents?$filter=UpdatedDate ge 2024-08-18T12:00:00Z
This query retrieves only components updated since last refresh took place, while previously retrieved data can remain cached on the client.
Power Query
You can configure scheduled refresh to control how often your published Power BI data sources are refreshed.
You can publish a shared semantic model querying OData API updated per schedule for all your reports to pull data from minimising amount of time spent querying the API.
Error Handling and Retries
When working with the OData API, it’s important to design your integration to handle errors gracefully. Not all requests will succeed, and clients must be prepared to retry responsibly, and avoid patterns that can overload the system.
Retrying wisely
Pause before retrying: Immediate retries after failure amplify backend load and can worsen service availability.
Use exponential backoff: Increase the wait time after each failed attempt (e.g. 1s, 2s, 4s, 8s...).
Add jitter: Introduce random variation to retry intervals. This avoids a “thundering herd” problem where many clients retry simultaneously after an outage.
Limit the number of retries to a sensible maximum to avoid infinite loops.
Additional recommendations
Timeouts: Don’t set short client-side timeouts against long requests to avoid getting stuck in timeouts loop. Test without timeouts first.
Graceful degradation: When errors persist, an older set of data may be served temporarily, see Managing Data Updates above for details.
Power Query
Published Power BI reports, in addition to benefits described above, also present last successfully obtained data to the user, making report usable even if refresh is temporarily unavailable.