An analysis has been carried out. It turns out that when all administrations are selected (choose “All” in the pie chart menu in the Invantive Control ribbon), the following formulas are expected to approach all administrations, even though they only need to read 1 Exact Online administration:
I_EOL_GL_SCHEME_CODEI_EOL_GL_SCHEME_DESCRIPTIONI_EOL_GL_SCHEME_IS_MAINI_EOL_TXN_AMOUNT_DCI_EOL_TXN_AMOUNT_FCI_EOL_TXN_QUANTITY
The underlying cause is the use of a query such as:
select *
from ExactOnlineREST..GLSchemes
where division = 123456
The tables that can be accessed too often are:
ExactOnlineREST..GLSchemesExactOnlineREST..TransactionLinesExactOnlineREST..TransactionLinesBulk
This query itself will only access the administration with division code 123456, as described in Betere prestaties op Power BI voor Exact Online accountants.
For performance purposes, Invantive Control for Excel skips interpretation by the SQL engine and works directly with so-called QueryObjects. In versions up to 22.0.690, a different casing convention for division was used than the one the optimisation expected. The SQL engine automatically rewrites to the correct casing, but the optimisation improvement via the QueryObject-only approach does not. Since the Exact Online SQL driver works with non-case-sensitive object names, this matching has been made case-insensitive.
The behaviour of versions prior to 22.0.690 did not lead to incorrect results, because filtering on division code also takes place on the client side.
However, the behaviour of versions prior to 22.0.690 does, solely for the formulas mentioned, sometimes lead to significantly worse performance (in the original case, it cost around 30 extra seconds per refresh after logging in, because around 800 administrations were read unnecessarily). The performance degradation will be most noticeable when using the I_EOL_TXN... formulas, which process a lot of information. In practice, the aforementioned 30 seconds turns out to lie within users’ frustration tolerance threshold. The speed of other (most intensively used) formulas such as I_BAL_PDE... has not been changed by this behaviour.
In addition, this behaviour results in unjustly high invoices.
An analysis will be carried out to determine which invoices need to be adjusted and in what way, and a credit note will follow accordingly. This only applies to accountancy subscriptions. Business subscriptions include a high limit of 100 administrations in the price, and few businesses have more than 100 administrations in combination with Invantive Control for Excel.
All accountants using an incorrect version of Invantive Control will be asked to upgrade.
The issue described above only applies to Invantive Control for Excel, since the problem was in code specific to Invantive Control formulas. Other products, such as the Exact Online Power BI connector or Get My Report, do not use this logic.