Use Trickle Loading with Exact Online Webhooks

Wiki (en)
,
1

This note explains the advantages of trickle loading replicas of Exact Online companies such as shorter replication times, including steps to activate trickle loading. Another topic elaborates on Trickle Loading when using an OEM license of Invantive Data Replicator to serve many customers.

Trickle Loading

Data Replicator provides a variety of approaches to reduce the strain placed on your infrastructure especially cloud platforms by replicating data into a central database. One of the most popular optimizations is to switch from the complete copy approach to incremental loading using “trickle loading”.

Trickle loading is a technique where initially a full replica is created from your sources and after that only changes are applied.

Versioning still applies with trickle loading: as an atomic action your users will switch between current and new versions of the integral contents of a table.

Changes from cloud applications are typically collected using Invantive Cloud and made available in queue for download by the authorized owner. Currently, only Exact Online is covered using webhooks. A quick set-up is given below.

Changes from on-premise platforms such as databases can be collected using database triggers or redo log mining, and require manual labor and consulting from Invantive.

Advantages

Trickle loading has a number of advantages:

  • Trickle loading shortens the duration of the daily load phase for example from 30 minutes for 100.000 accounts on Exact Online to 30 seconds.
  • In case no changes have been registered, the version expiration date is bumped forward without changing version numbers.
  • The decrease in amount of CPU and disk I/O required significantly reduces resource consumption, especially when over 1,000 partitions have been replicated into a database.
  • For 24x7 loading cycles, the shortened load phases also reduce the time window during which related tables have inconsistent contents due to foreign key constraints being violated.

Risks

Trickle loading also introduces a number of risks, especially since most webhooks used for trickle charges in general lack concepts like heartbeat and health of service indicators:

  • No updates being applied, when in the repository a table partition has been configured for trickle loading, but the platform does not deliver the charges. Recommended action: carefully and globally activate trickle loading on the platform.
  • Subscription loss, when in the repository a table partition has been configured for trickle loading, but the platform does not deliver the charges. Exact Online on a number of occasions has lost subscriptions. Recommended action: carefully and globally activate trickle loading on the platform. Apply application controls to detect the loss of subscriptions.
  • Charge delays, where the charges to be loaded arrive with a significant delay. Recommended action: apply application controls.

Activate Trickle Loading

To activate trickle loading on Exact Online, you configure a Data Replicator connection string and execute the following statements:

--
-- Select the partitions (Exact Online companies) to be replicated.
--
use 123456,345678
 
--
-- Initial setup steps.
--
begin
  --
  -- Create an initial complete copy using an easy-to-use 'select count(*)'.
  --
  select count(*) from exactonlinerest..salesinvoices;
  select count(*) from exactonlinerest..salesinvoicelines;
  --
  -- Change strategy for the tables to use trickle loading.
  --
  -- Tables for which trickle loading is not available will raise an error. You
  -- can still run a complete copy.
  --
  alter persistent cache set table exactonlinerest..salesinvoices approach trickle;
  alter persistent cache set table exactonlinerest..salesinvoicelines approach trickle;
  --
  -- Configure your local Data Replicator repository with credentials to download
  -- the webhook events received.
  --
  -- Receive your token from Invantive.
  --
  alter persistent cache set token 'secret'
  --
  -- Now activate on https://data-replicator.cloud the webhooks for Exact Online.
  -- For volumes above 30 companies, please arrange a consultancy call to
  -- to get help running the SQL statements to ensure you register the correct webhooks
  -- across your companies.
  --
  -- From here on in time, changes in Exact Online will be registered and collected.
  --
  -- To ensure no changes got lost between the start of this PSQL block and now,
  -- execute a full reload.
  --
  alter persistent cache
  table exactonlinerest.salesinvoice.salesinvoices
  force refresh approach copy
  ;
  alter persistent cache
  table exactonlinerest.salesinvoice.salesinvoicelines
  force refresh approach copy
  ;
end;
 
--
-- Steps for daily or more frequent replication.
--
begin
  --
  -- Download all events collected.
  --
  alter persistent cache download feed;
  --
  -- Refresh the contents of two tables using the default strategy configured
  -- on the table.
  --
  -- Even with large volumes and sufficient disk I/O capacity,
  -- this step in general takes less than 1 minute.
  --
  alter persistent cache table exactonlinerest.salesinvoice.salesinvoicelines refresh;
  alter persistent cache table exactonlinerest.salesinvoice.salesinvoices refresh;
  --
  -- Or use the following statement, which does a complete download of all tables where
  -- trickle loading is not configured. And uses trickle loading for the ones with trickle
  -- loading configured.
  --
  alter persistent cache refresh;
end;
2

When I use:

alter persistent cache download feed

and then, for instance

alter persistent cache table ExactOnlineREST.SalesOrder.SalesOrdersBulk refresh approach trickle

it ends up saying:

No messages received ever for table ‘ExactOnlineREST.SalesOrder.SalesOrdersBulk’ for partition ‘999’ according to database. Switch from trickle loading to complete replication till the trickle loading loop is closed.

Is there a way to force trickle loading, and if no changes are present, do NOT do a complete replication?

3

Invantive Data Replicator can use trickle loading, which means that only deltas (changes consisting of insert, update and delete) are applied on the previous table partition version (on Exact Online; a completed load of a table and an Exact Online company) leading to a new table partition version. For instance:

  • old table partition version Exact Online company 123, table TransactionLinesIncremental, ready version 8
  • take deltas and apply on old version
  • make new version 9 and assign it state ‘Ready’.

The trickle loading process is also elaborated on in the topic Enable webhook trickle loading on OEM licenses. More precise, for Exact Online the delivery of webhooks is a sensitive process since Exact Online currently lacks any form of heart beat to report that the loop has been established:

Therefore Data Replicator on Exact Online uses the first delta as a heart beat signal to indicate the loop has been closed.

A request has been registered with Exact for some years to improve the reliability of webhook event delivery, but as of 2021 there is no final plan to add such functionality. As an alternative to reduce loading times, there is an option to resort to the new *Incremental tables such as ItemsIncremental. They combine a reduced API consumption with always consistent data contents. For more background refer to:

Small number of companies

For small numbers of Exact Online companies, the easiest way is to wait a few days and then check in dc_table_partition_versions that the actual approach used is trickle loading (value T).

Note that dc_table_partition_versions contains a column for the request seeding approach and the actual approach used.

For those tables still not using tricke loading, you can ask a key user to create and delete a transaction such as updating the description of a General Ledger Account.

Large number of Exact Online companies

The manual approach to get the first signal over the wire can be labor intensive, especially when replicating hundreds or thousands of Exact Online companies. This holds especially for accounting firms where many Exact Online companies are only updated once per year, such as during the yearly accounting and/or regulatory reporting on holdings. Till the first message is processed, trickle loading on these companies will resort to full copy approach.

There is no supported way to manually force the loading approach for large number of Exact Online companies from Copy to Trickle Loading. However, an Invantive consultant can make the necessary internal changes to the Data Replicator repository on basis of Time & Material to simulate that a message was received. This typically takes 30 minutes per Data Replicator repository.

4

When I use:

use all;

alter persistent cache table ExactOnlineREST.Financial.GLAccountsBulk force refresh approach copy;

Then I see in the screen that the copy approach is used, and I see that all rows are downloaded. But when I view the dc_table_partition_versions, it still says:

seeding_approach_used = T

Where do I view the actual approach used?

5

What Invantive SQL release is it?

6

20.0.143

sau
copy

7

I am uncertain what release is in use, but probably the queue for trickle loading messages is large. If so, then Data Replicator reverts to full copy.

Please make sure to be running 20.0.142 or newer and read the explanation on Purge obsoleted change events.

8

The problem is the opposite:

when I explicitly ask for a force refresh approach copy, then verify in the ‘Uitvoeren’ window that there is a Forced copy performed, the seeding_approach_used in the table partition versions is still shown as T.

Version is 20.0.143 as stated.