If you find that pulling data from MYOB or Xero is slow, the Archive Paging setting may be just what you are looking for.
This setting, added in August 2024, can dramatically increase the performance and reliability of OdataLink when pulling data from MYOB or Xero.
What does the new Archive Paging setting do?
The Archive Paging option controls how OdataLink requests data from your MYOB or Xero Data File. It provides three options:
- Default
- By Month
- By Year
Depending on the option selected, OdataLink will optimise the requests made to your MYOB or Xero data file in a number of different ways.
How does OdataLink work when the Archive Paging setting is set to ‘Default’?
The ‘Default‘ option is the current legacy behaviour of OdataLink. When used, the data is requested as instructed. The filters specified will be sent as-is to your data file.
While most customers will benefit from using the ‘By Month‘ or ‘By Year‘ option, we kept this legacy mechanism for very small data files or for those that use complex queries.
How does OdataLink work when the Archive Paging setting is set to ‘By Month’ or ‘By Year’?
Using either the ‘By Month‘ or ‘By Year‘ options, the data is requested in smaller amounts. These options are best used for very large data files.
The ‘By Month‘ option will get OdataLink to query the data one month at a time.
The ‘By Year‘ option will get OdataLink to query the data one calendar year at a time.
OdataLink will still download all the data you want to see. It will simply do this over a greater number of calls. The option purely controls how the data is paged and sent to Power BI, Excel (or other tech).
It’s important to note that the option only kicks in when one of the following conditions occur:
- you specify a Default Start Date in your model
- you include a filter in your query to request data GREATER THAN or GREATER OR EQUAL THAN the transaction date.
What do we recommend for those using MYOB?
MYOB’s API is more designed to handle small volume of data. Because of this, using the ‘By Year‘ or ‘By Month‘ option can provide a lot of performance gains.
We recommend you use the ‘By Year‘ if you have 2,000 transactions per year and require 3 or more years of data.
We recommend you use the ‘By Month‘ if you have 1,000 transactions per month and require multiple months of data.
In our tests, pulling 20,000 transactions across 10 years from MYOB took:
- 11 minutes with the legacy ‘Default‘ option
- 4.5 minutes with the ‘By Month’ option
- 3.5 minutes with the ‘By Year’ option.
We strongly recommend you test the different option and choose the one that provides the best performance for your MYOB data file.
What do we recommend for those using Xero?
Xero’s API is very well structured to handle large volumes of data.
However, Xero have introduced limits to the number of transactions that can be requested at a single time. If you need to report on more than 100,000 transactions, then using the ‘By Month‘ or ‘By Year‘ option is recommended.
These options will ensure that each request stay below the 100,000 transactions cap.
You can find further information about the record limits that Xero imposes via the following page.
https://developer.xero.com/documentation/api/efficient-data-retrieval#high-volume-threshold-limits