Replicate Twinfield through a Proxy

Summary

The Twinfield proxy from BI-ON-FOCUS can be used by Invantive Data Replicator to replicate many Twinfield companies into a relational database for use with BI and analysis tools. This note describes how to code the Twinfield replication.

Since 2020 Invantive SQL provides native support on Twinfield API in the form of a new connector.

Twinfield

Currently, neither Invantive Data Replicator nor Invantive SQL provides a connector with the Dutch accounting package Twinfield. However, Twinfield partners with a number of app suppliers to provide an interface to the data managed by Twinfield. One of these partners is BI-ON-FOCUS, which provides a proxy gateway between Twinfield and Power BI.

This proxy can be used - when used in accordance to the agreement - to also open up Twinfield for other sources using Invantive Data Replicator and so provide massive reporting across thousands of companies with a variety of solutions such as Exact Online, Visma.net, Salesforce or Loket.

Invantive Data Replicator

Invantive Data Replicator uses the connectors provided by Invantive SQL to access source systems and replicate the data contained in those to a traditional platform such as SQL Server, Oracle, MySQL, PostgreSQL or the Invantive storage format. These replicas and their metadata is maintained automatically. Depending on the needs and supported possibilities, a number of optimizations are present like incremental trickle loading or smart replication. The data flow is depicted below:

Retrieving Data from a Proxy

The Twinfield for Power BI solution from BI-ON-FOCUS uses a proxy on HTTP which retrieves, caches and provides Twinfield contents in JSON format. Authentication and autorization is done using credentials per office provided transported using Basic Authorization. The credentials include the Office number (a Twinfield concept) as a user name and an access token as password.

Twinfield Proxy Credentials

Since Data Replicator is normally used for hundreds or thousands of source, we will use a small in-memory structure to contain the credentials per Twinfield Office. For improved security, we recommend to use an encrypted storage like the bundled KDBX connector or encrypted storage in a relation database.

The credentials for the proxy are made available as an in-memory structure using:

select crd.*
from   csvtable
       ( passing '372012345012,abcdef1234567890'
                 || '#483122355900,abcdef1234567890'
         row     delimiter '#' 
         column  delimiter ','
         columns office_number varchar2 position 1
         ,       access_token  varchar2 position 2
       ) crd

The results resembles:

twinfield-credentials-storage

Twinfield Transaction Download

The transaction data from the Twinfield proxy can then be downloaded by extending the query by a HTTP download, setting the Content-Type request header to application/json and presenting the credentials using basic authorization:

select crd.office_number
from   csvtable
       ( passing '372012345012,abcdef1234567890'
                 || '#483122355900,abcdef1234567890'
         row     delimiter '#' 
         column  delimiter ','
         columns office_number varchar2 position 1
         ,       access_token  varchar2 position 2
       ) crd
join   httpdownload@datadictionary
       ( url => 'https://twinfield.bi-on-focus.nl/transactions?type=current'
       , contentType => 'application/json'
       , headers => 'Authorization=Basic%20' 
                    || urlencode(base64_encode(ascii_to_blob(crd.office_number || ':' || crd.access_token)))
       ) htp

The httpdownload table function is available with the DataDictionary connector. To see all available options, please press F4 in the Invantive Query Tool with the cursor positioned on httpdownload.

The ascii_to_blob encodes the Basic authentication text with user and password to a BLOB, which is then encoded using base64. Ultimately, the urlencode ensures that special characters such as trailing ‘=’ are escaped sufficiently for use in a header.

Twinfield Transactions in Table Format

The download from the Twinfield proxy is returned in JSON format. In this last step, we transform the data in JSON format to a table format:

select crd.office_number
,      dta.*
from   csvtable
       ( passing '372012345012,abcdef1234567890'
                 || '#483122355900,abcdef1234567890'
         row     delimiter '#' 
         column  delimiter ','
         columns office_number varchar2 position 1
         ,       access_token  varchar2 position 2
       ) crd
join   httpdownload@datadictionary
       ( url => 'https://twinfield.bi-on-focus.nl/transactions?type=current'
       , contentType => 'application/json'
       , headers => 'Authorization=Basic%20' 
                    || base64_encode(ascii_to_blob(crd.office_number || ':' || crd.access_token))
       ) htp
join   jsontable
       ( '[*][*]'
         passing htp.content_clob
         columns period             varchar2 path 'Period'
         ,       name               varchar2 path 'Name'
         ,       status             varchar2 path 'Status'
         ,       customer           varchar2 path 'Customer'
         ,       currency           varchar2 path 'Currency'
         ,       value_fc           varchar2 path 'Value'
         ,       value_eur          varchar2 path 'Euro'
         ,       transaction_type   varchar2 path '[''Transaction type'']'
         ,       transaction_date   varchar2 path '[''Trans. date'']'
         ,       value_usd          varchar2 path '[''$'']'
         ,       open_amount        varchar2 path '[''Open amount'']'
         ,       invoice_number     varchar2 path '[''Invoice no.'']'
         ,       due_date           varchar2 path '[''Due date'']'
         ,       payment_status     varchar2 path '[''Payment status'']'
         ,       payment_number     varchar2 path '[''Payment no.'']'
         ,       pay_date           varchar2 path '[''Pay date'']'
       ) dta