Use Trickle Loading with Exact Online Webhooks

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;