Synchronize all accounts from an Exact Online company in a Visma.net company with one statement. This second note on one-statement synchronization (mono- as well as bi-directional) illustrates a practical synchronization task.
Synchronization from Exact Online accounts to Visma.net customers
A global innovation on top of Invantive SQL enables one-statement synchronization (mono- as well as bi-directional) data between applications. The concept and grammar were introduced in Synchronize your Data with one Statement across multiple Applications.
This note provides a practical example of ensuring that all accounts of an Exact Online company are present in a Visma.net company as customers.
The implementation of a mono-directional synchronization from Exact Online to Visma.net is solely one Invantive statement. However, the text of the functionality already highlights the importance of clear terms and mapping functionality between systems:
- In Exact Online, an “account” refers to a counter party in a (possible future) legal agreement with the executing company. The term “account” in Visma.net however refers to an account in the General Ledger.
- In Visma.net, a “customer” refers to a dedicated list of parties that act as customer in a legal agreement to transfer products. Besides “customers”, there is a separate list of suppliers, while in Exact Online all customers, suppliers and other counter parties such as prospects are managed largely as one data set.
In this case, the mapping will be rather straightforward.
In Exact Online, the source is an account:
In Visma.net, the result should be:
Basically the mapping is:
- Exact Online accounts will be identified by one-and-exactly-one Visma.net supplier by the value of the “Reference” field in Visma.net.
- The account reference starts of with ‘EOL-’ to indicate the originating system.
- The name is copied from Exact Online without changes.
There is no need for changing characters since both systems seem to support a rather wide set from the Unicode character sets. Truncation is not needed since the originating system Exact Online restricts the name of a party to 50 characters, whereas Visma.net allows up to 60 characters.
Out-of-scope here is splitting apart the Exact Online list of accounts in customers, suppliers (possibly overlapping) and all others. The criteria for such a mapping can be found in documentation.
Neither in scope are updating (just add “or update” to the statement) nor deletion of customers (Visma.net does not support API-based deleted of customers).
The replication could be formulated as an insert into Visma.net from a select with a full outer join on both systems. However, the synchronize statement makes it easier:
use 102673@eolnl,1896329@vismanet create or replace table exactaccounts@inmemorystorage as select to_char(code) number , name , 'EOL-' || to_char(code) accountreference from exactonlinerest..accounts@eolnl synchronize exactaccounts@inmemorystorage to customer_getall@vismanet with insert identified by accountreference ignore nulls
The use statement selects one Exact Online company and one Visma.net company. Multiple Exact Online or Visma.net companies can be selected by adding more items, one or more selection queries or the special names all and default.
The in-memory table exactaccounts is an interim-table to illustrate the mapping from the Exact Online concepts to Visma.net words. For instance, an account number in Visma.net is considered a text in the tables. This query for the in-memory table would be the right place to trim the account names, prefix numbers, change names to uppercase, etc.
The synchronize statement uses the insert statement to upload customers to Visma.net. The ignore nulls clause is needed since the field ‘Reference’ in Visma.net will probably have some null values for manually entered customers and reference is therefore not a fully covering unique key on the Visma.net customer base.
eolnl and vismanet are two data containers defined on a distributed database using the following settings.xml contents:
<?xml version="1.0" encoding="utf-16"?> <settings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="5" forcedefault="false" > <group name="sample"> <connection name="exact online nl + visma.net"> <database alias="eolnl" provider="ExactOnlineAll" connectionString="api-url=https://start.exactonline.nl" /> <database alias="vismanet" provider="VismaNet" /> </connection> </group> </settings>
The performance of the synchronization process depends on:
- Number of customers currently in Exact Online and Visma.net: the synchronize statement needs to reconciliate both lists of customers either from an existing replica on a SQL Server database powered by Invantive Data Replicator or by downloading the data real-time from the two cloud platforms. Of course, other Invantive Data Replicator platforms such as Oracle and PostgreSQL could be used too.
- Number of customers to be exchanged: runtime will increase with more data to be exchanged.
- Presence of bulk loading features: the synchronize statement prefers bulk inserts when available on platforms such as SQL Server, but resorts to one-by-one inserts when not available.
Visma.net currently has no bulk insert features and in this test Invantive Data Replicator and the various cache mechanisms were disabled. At start of the test Visma.net contained approximately 5.000 customers and Exact Online contained more. No use was made of Invantive’s features for parallel processing within or across companies.
The reconciliation phase takes approximately 17 seconds (plus/min 2 seconds), whereas loading and validating customers takes approximately 500 ms per customer (plus/min 200 ms).
Initial loading of 1.000 customers would therefore take approximately 10 minutes. With 10 new customers per day and daily synchronization, the customer base of Visma.net and Exact Online would be up-to-date with a runtime of less than 30 seconds.