With the gradually decrease the number of API calls (Dutch) per day to 5,000 per Exact Online company per application it is often useful to make a rough estimate of the size of a company.
With this estimate applications can be optimally tuned at the lowest possible (consultancy) costs. Also the costs for interfacing with Exact Online remain low while the use on the Exact Online API is kept to a minimum. The actual usage can be measured as described in Analyseer Exact Online API gebruik met `RateLimits` of `SystemPartitions` (Dutch).
A good indicator for size is the number of accounting lines. On Exact Online it’s not unusual for a company to have 5,000 accounting entries but it’s no strange thing when there are 10 million entries in one company. The architecture of Exact Online is such that even huge companies are rapidly accessible. At most, the access through the web screens and APIs can be difficult depending on how well they are optimized for large volumes of transactions.
All techniques described below for estimating the size of an Exact Online environment work for Exact Online Accountancy, Exact Online Accounting as well as the Exact Online ERP modules such as trade and manufacturing. The techniques can be used with any Exact Online add-on from Invantive with SQL support. The figures can be counted just as easily for multiple companies as per individual company. Use the use SQL statement or the company selector in the menu to select multiple companies.
The size can be used in combination with the age of a company (Dutch link) to predict the size in the future. If necessary, the current size can be corrected for the initially loaded number of rows from a conversion. Rows from a conversion can often be recognized by a large number of rows with a creation date within, for example, the first three months after the creation date of the records.
Questions for Initial Estimate
A first starting point for determining whether a company has many accounting entries is through asking questions:
- Is it an internal company within an Exact Online accountancy subscription? If so: small.
- Is it an operating company? If no, then small.
- Is the annual turnover more or less than 3 million Euros?
- Is it a company that supplies capital goods with turnover or mainly to other companies? If yes, then small to medium, depending on the annual turnover.
- Is it a company that supplies consumer goods or mainly to consumers? If yes, then medium to large, depending on the annual turnover.
In real-life especially webshops with large volumes of cheap consumer products and many Exact Online users appear to have a very large company. Many webshops have grown enormously in volume since the start of the Corona pandemic. And the turnover and payments are often booked with an app in unconsolidated form, which means that every order of a few euros results in about 10 accounting lines.
The Exact Online sync APIs offer possibilities to relate the load on the Exact Online side pro-rata to the daily turnover, while the traditional APIs are often pro-rata to the cumulative turnover over the years.
This is a far-reaching simplification:
- when annual turnover doubles, the number of calls for sync APIs doubles,
- for traditional Exact Online APIs, the number of calls doubles with a doubling of the company’s lifecycle.
The *Incremental tables of the Invantive SQL Exact Online driver offer a reliable and smooth way to work with complete and up-to-date figures, and still keep the load of the available Exact Online APIs low.
Exhaustive Counting
An accurate approach to determining the size is to count the rows. Since Exact Online has a mixed ledger, a count on TransactionLinesIncremental is a great indicator:
select count(*)
from TransactionLinesIncremental
As of release 24.0.182, this can be done in a few hundred milliseconds via a query on RowCounts such as:
select count(*)
from rowcounts@eol
where table_name = 'SyncTransactionLines'
We usually calculate a storage capacity of 2 KB per accounting entry line. This query can also be further easily broken down by type of entry, such as “Sales” by grouping by type of entry.
Sometimes it is necessary to also specifically count the size of the article file because you have a lot of dormant articles that do get dragged along or count the documents because you are using Exact mainly as a DMS:
select count(*)
from ItemsIncremental
and
select count(*)
from DocumentsIncremental
When Counting Fails (as of release 24.0.182)
Sometimes there is no more API space to count the numbers because Exact Online only allows 5,000 API calls per day. A new accounting with 15 million rows simply cannot be counted in 1 day.
The fastest alternative is a query on RowCounts as described in Sneller schatten aantal rijen op Exact Online met de tabel "RowCounts" (Dutch).
A company with 15 million lines can then be counted within seconds.
When Counting Fails (before release 24.0.182)
Sometimes there is no API space left within the limits to count the numbers because Exact Online only allows 5,000 API calls per day. A new company with 15 million lines just can’t be counted in one day.
However, there are good alternatives to estimate the size faster.
Estimate Exact Online Size using ReportingBalance
If little use is made of cost centers and cost units, then the table ReportingBalance can be used to estimate the numbers of bookings. Normally, we recommend against using ReportingBalance in favor of BalanceLinesPerPeriod because it is very slow and requires a lot of API calls, especially when working with cost centers and cost units. But ReportingBalance does show the numbers of entries per combination of ledger number, entry type, period and company.
The following SQL query shows a fairly accurate estimate of the number of accounting entry lines in the Exact Online selected companies:
select divisionlabel
, sum(Count)
from reportingbalance@eol
group
by divisionlabel
It was not smoothly possible to find an exact match with the number of rows in TransactionLinesIncremental (also taking into account special line numbers), but the deviation is less than 1%.
This query can also still be easily broken down by type of entry, such as “Sales” by grouping by type of entry.
Superfast Estimate Size through $count
It is also possible to retrieve the size within seconds for many tables by using the $count option in the OData3 standard that Exact Online largely supports.
Using $count is not possible on some APIs and cannot be combined with filters under version 3 of OData, but it is super fast when available.
Using the SQL below on Exact, the size can usually be determined within a minute even for very large companies:
--
-- Combination of all companies and (in this example) all Sync
-- API tables of Exact Online.
--
create or replace table countqueries@inmemorystorage
as
select ste.name
, sdn.label
, sdn.code
, replace(ODATA_SERVICE_URL_FOR_SELECT, '{division}', sdn.code) || '/$count'
url
, ste.name || ' @ ' || sdn.label
orig_system_group
from SystemDivisions@eol sdn
join SYSTEMTABLES@DataDictionary ste
on ste.PROVIDER_NAME = 'ExactOnlineAll'
and ste.CATALOG = 'ExactOnlineREST'
and ste.schema = 'Sync'
--
-- Send the API requests untranslated as a
-- "Native Scalar Request". Results will be registered in
-- the same table "NativePlatformScalarRequest".
--
-- Use is made of XML-output. JSON can be acquired by
-- setting Content-Type.
--
insert into exactonlinerest..nativeplatformscalarrequests@eol
( url
, orig_system_group
, fail_on_error
)
select url
, orig_system_group
--
-- Replace by true if you don't have permissions on everything.
-- Tables which the current Exact Online user does not have permissions to
-- will then be silently skipped.
--
, false
from COUNTQUERIES@InMemoryStorage
--
-- Per table and company the number of rows.
--
select orig_system_group
, RESULT_TEXT
from exactonlinerest..NATIVEPLATFORMSCALARREQUESTS@eol
where SUCCESSFUL = 'Y'
and orig_system_group is not null
It also occurs that administrators do not hand out permissions to users. These can be recognized by a Forbidden / 403 error as a result of the query:
select orig_system_group
, RESULT_TEXT
from exactonlinerest..NATIVEPLATFORMSCALARREQUESTS@eol
where SUCCESSFUL = 'N'
and orig_system_group is not null
zoals:
<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
<code></code>
<message xml:lang="">Forbidden</message>
</error>