There are various advanced techniques available with Invantive Data Replicator to speed up downloading facts from sources. One of these techniques is “trickle loading“.
What is Trickle Loading?
Trickle loading is a performance improvement technique. It starts with a full initial replication. The source system informs the Invantive infrastructure of each and every change. The changes come “trickling“ in on an individual change basis. These changes to the data can be downloaded are applied to the full data set. Trickle loading is available for a limited number of tables, depending on the capabilities of the platform connected to. Using trickle loading, you can often reduce replication duration by a factor 100 or more.
What is an Exact Online Webhook?
An Exact Online webhook is a registration through the Exact Online API by which an application can subscribe to receive messages upon changes in the data. The registration is made using the user credentials and each application can have their own webhook subscriptions. An application can’t see or access webhook subscriptions of other applications on Exact Online.
The actual implementation of trickle loading varies per platform. On Exact Online, webhooks are used to implement trickle loading in Invantive Data Replicator. On the popular Exact Online platforms Invantive Data Replicator offers trickle loading on approximately 30 tables. Most of these Exact Online tables are of transactional nature.
Exact Online webhooks are conceptually different from Exact Online sync APIs.
Analyzing Seemingly Lost Changes in Tricke Loading
However, changes can get lost or seem to get lost in which they do not arrive in the replicated table. The following steps help you analyze and solve such issues.
Emergencies
In case of emergencies, you can always choose to run a manual full replication using the following Invantive SQL statement from the SQL documentation:
alter persistent cache force refresh approach copy
or for one table:
alter persistent cache table NAME force refresh table approach copy
Possibly combined with setting the partition desired using a statement like:
alter persistent cache table NAME force refresh partition NAME approach copy
This will download a full copy without using trickle loading. It can take some clock time to execute
You can also follow the data along the data flow when you have reason to doubt completeness of the replicated data set. The data flow on Exact Online with Invantive Data Replicator trickle loading is (see picture):
- User makes a change in a screen (add, delete, update) (red 1).
- Exact Online forwards the change to the Invantive queuing system using a webhook containing a unique ID (red 2). A separate queuing system is used given the up-time and performance requirements. No security confidential data of Exact Online is included in the webhook.
- A receiver of Invantive Cloud receives the change (red 3).
- The change is routed through the Invantive Message Bus into the output queue, waiting for your pickup (red 4).
- The pickup is executed using the
alter persistent cache download feedstatement and stored in the repository tabledc_incoming_messages(red 5). - On replication, Data Replicator takes the unique identification from the change and looks up the then current data in Exact Online (red 6).
- When there are any changes, a new table partition version is created in the replication database (red 7).
To analyze an issue, you can follow the flow of the data as shown above and check:
- whether it arrives;
- whether it arrives in time.
Change in Exact Online
Perform the following steps to trigger a change to be replicated to the replicated database:
- Check in the backing database view which tables and partitions (Exact Online companies) are replicated using trickle loading, using a query such as (see also documentation of repository data model):
select ptn_name
, tbe_full_qualified_name
from dc_table_partitions_r
where tpt_seeding_approach = 'T'
- Log on to Exact Online.
- Switch to a company.
- Open the form whose contents are replicated.
- Make a change using a unique and easy to query text like a random text string in the description or code.
- Note the time of making the change.
Forwarding Changes from Exact Online
Within a few minutes, Exact Online will pick up the change, register it internally and forward it to the Receiver on Invantive Cloud. For most sites, a custom client ID is used, so you can analyze in your Exact Online subscription the status of the forwarding:
- Go the the Exact Online form “Log Webhook Messages“:
- Look whether you can visually find a recent message that corresponds to your change.
- The column (1) displays the time in UTC at which the last effort to deliver the webhook was made.
- The column (2) displays the unique ID of the object involved. This unique ID is transported along the whole data flow.
- The column (3) displays the number of times a delivery was tried.
- The column (4) displays the last response code. The delivery was successful only when it displays ‘OK’.
Receiver for Changes
The Invantive Cloud Receiver has retrieved the change successfully when the response code is ‘OK’. As a user, you can query the Invantive Cloud Receiver outcome in the Invantive Message Bus as described in Inzicht in Exact Online webhooks via de Invantive Message Bus-schermen van Invantive Cloud (Dutch only).
Queuing Changes
The message is stored in the out queue of the Invantive Message Bus.
Download Feed of Changes
Once the response code ‘OK’ is displayed, you should wait two additional minutes to allow Invantive Cloud to complete processing the message even when at the same time a so-called webhook storm is triggered by Exact Online.
Then download the messages queued for your repository using:
alter persistent cache download feed
Once it completes, the table dc_incoming_messages contains all received and unprocessed changes. You can analyze whether the change was propagated correctly using the query:
select *
from dc_incoming_messages
Optionally, for large environments you can add a where-clause where you specify the value of the resource key to be matched against the object_key in the table:
select *
from dc_incoming_messages
where object_key = 'RESOURCE KEY'
Remember that rows are removed from dc_incoming_messages once the changes have been processed.
Merge Incoming Trickle Loading Messages
The changes stored in dc_incoming_messages are merged with the last known replica on the next refresh.
A refresh only takes place when the data is no longer considered “fresh”. The default expiry date of a table partition can be queried using:
select tpn_date_becomes_obsolete
, tbe_full_qualified_name
, ptn_name
from dc_table_partition_versions_r
where tpn_active_flag = 'Y'
and tbe_full_qualified_name like '%TABLE NAME%'
and ptn_name = 'EXACT ONLINE DIVISION CODE'
However, you can force merging by running Invantive Query Tool, logging on to the Data Replicator environment and entering:
use DIVISION
select /*+ ods(true, interval '1 seconds') */ count(*)
from TABLENAME
This statement will update the data set, since the freshness is older than 1 second. A new table partition version will be introduced when there are any changes.
You can check the actual work done using a query on dc_event_log:
select top(100) *
from dc_event_log_r
where ptn_name = 'PARTITION NAME'
and tbe_full_qualified_name like '%TABLE NAME%'
order
by elg_id desc
Check Contents of Facts Table
Now the changes have all been merged into a new table partition version (when deemed necessary), you should be able to see the new row in the view generated for you.
The actual view name depends on your set up, but in general resembles the originating table name:
select *
from eol_transactionlinesbulk_r
Check whether the view contains the changed data.