First please note that Invantive UniversalSQL is real-time by design: once data is asked, the data retrieval starts. For slow data sets, it is recommended to use filter (query folding in Power BI and/or where clauses in SQL) and refresh them in advance using the Power BI Service during the night.
Fortnox has several APIs that are only suitable for SMEs with a low volume of transaction lines. Typically B2B works fine, but B2C or a webshop can be problematic.
InvoiceLines is a table (actually view) that uses such an API. It is defined as:
select ive.* prefix with 'Invoice'
, ile.*
from Invoices ive
join InvoiceLinesByDocumentNumber(ive.DocumentNumber) ile
The join contains a table function (see What are table functions and table function parameters?), once for every invoice. With a rate limit of 25 API calls per 5 seconds, this means on the average at most the lines of 5 invoices can be retrieved per second.
With a monthly volume of say 500 invoices, retrieval for that specific month already takes 100 seconds at least and typically more when other parallel downloads are running. Assuming typically 2 lines per invoices, the throughput is 1000 invoicelines per 100 seconds is 10 lines per second.
Fortnox as a SME accounting software is not alone with this problem. For instance Visma Teamleader Focus has an almost identical problem, although over the years Visma has worked to improve the situation considerably for larger sites. Other platforms, such as AFAS Profit (accounting) and Exact Online (accounting) allow access to all invoice lines with a single API call and paging between 1,000 and 50,000 lines per API. Such an approach increases throughput to typically somewhere between 500 and 10,000 invoice lines per second (approximately 50 to 1,000 times faster).
Besides the preloading and filtering improvements suggested above, the following workarounds might help:
- only use the invoice headers when possible,
- use data from the SIE, which contain the transaction part of the invoices.
Since the generation of the SIE audit files is really extremely fast on Fortnox (maybe hundreds of times faster), we are considering adding tables on top of these, but the investment in the SIE parser is quite large. So far Invantive SQL only supports the various Dutch auditfile formats in various versions (XAA, XAS, XAF).
Of course you can always file a support ticket to ask for an enhancement of the Fortnox APIs, since that will benefit their business too allowing customers with larger volumes of invoices to viably run on Fortnox.