Problem

You have various advanced techniques available with Invantive Data Replicator to speed up downloading facts from sources. One of these techniques is “trickle loading“, which after a completed full replication only downloads changes from the source system and applies them. 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.

However, changes can get lost or seem to get lost in which they do not arrive in the replicated table. This article helps you to analyze such issues.

Solution

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
CODE

or for one table:

alter persistent cache table NAME force refresh table approach copy
CODE

Possibly combined with setting the partition desired using a statement like:

alter persistent cache table NAME force refresh partition NAME approach copy
CODE

This will download a full copy without using trickle loading. It can take some clock time to execute

Analysis

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):

  1. User makes a change in a screen (add, delete, update) (red 1).

  2. 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.

  3. A receiver of Invantive Cloud receives the change (red 3).

  4. The change is stored in a queue, waiting for your pickup (red 4).

  5. The pickup is executed using the alter persistent cache download feed statement and stored in the repository table dc_incoming_messages (red 5).

  6. On replication, Data Replicator takes the unique identification from the change and looks up the then current data in Exact Online (red 6).

  7. 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 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'
CODE
  • 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

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:

  • 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

The Invantive Cloud Receiver has retrieved the change successfully when the response code is 'OK'. As a user, you can not directly query the Receiver. Invantive Support can analyze the reception and queue when you provide them with the resource key in column (2) of the picture above, but first validate with Download Feed whether the change arrives in your repository.

Queuing

The message is stored in the queue. See notes with Receiver.

Download Feed

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
CODE

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
CODE

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'
CODE

Remember that rows are removed from dc_incoming_messages once the changes have been processed.

Merge

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'
SQL

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
SQL

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
SQL

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
SQL

Check whether the view contains the changed data.