Snel en met minder API-calls Exact Online uitlezen met de incrementele "sync API"-tabellen

De Exact Online REST API biedt zogenaamde “sync API’s”. Sync API’s zijn nieuwe REST API’s die een aantal nadelen bij het gebruik van Exact Online webhooks voor incrementeel replicatie oplossen.

Voor omgevingen op basis van Invantive Cloud of de Excel add-in Invantive Control zijn de sync API’s van Exact Online een welkome verbetering:

  • Eenvoudig: de sync API’s zijn eenvoudig in het gebruik en werken meteen de eerste keer.
  • Betrouwbaarder: doordat de keten van afhankelijkheden is vergeleken met webhooks veel korter is. Het storingsgevoelige opsturen en distribueren van mutatieberichten via Exact Online webhooks blijft achterwege.
  • Minder API calls: de sync API’s beperken het aantal API calls aanzienlijk voor grote Exact administraties.
  • Efficient: het resourcegebruik is meetbaar lager.
  • Goedkoop: de sync API tabellen zijn beschikbaar met elk Invantive Office abonnement en zelfs met de gratis Power BI abonnementen op Exact Online.

Meer achtergrondinformatie is te vinden in de Engelse post Exact Online sync APIs.

Voor elke beschikbare sync API voor Exact Online is er een bijbehorende tabel in Invantive SQL die de complete inhoud weergeeft. Voor bijvoorbeeld Items (artikelen) is dat de tabel ItemsIncremental.

De velden van sync API tabellen zoals bijvoorbeeld ItemsIncremental zijn vrijwel identiek aan de oorspronkelijke tabel (hier ItemsBulk). Echter, afgeleide velden zoals ItemGroupDescription zijn verwijderd. De incrementele aard van ItemsIncremental maakt het gebruik van een genormaliseerd datamodel noodzakelijk. Voor correcte rapportages dienen de gerelateerde tabellen gekoppeld te worden in bijvoorbeeld het Microsoft Power BI datamodel.

Bijwerkfrequentie van sync REST API tabellen

De data van een sync API tabel wordt automatisch bijgewerkt als een tabel op basis van de sync API’s gebruikt wordt in een query. De standaardcaches in het geheugen en op schijf worden niet gebruikt.

Het raadplegen gaat vlot. Een query op Exact Online die minder dan 1% van de data teruggeeft blijft vaak sneller door een query te formuleren op de normale API met de juiste filters in de where-clause. De automatische optimizer van Invantive SQL zorgt er voor dat een slim filter samengesteld en doorgegeven aan de API servers van Exact. Daardoor zijn bijvoorbeeld de juiste 10 artikelen binnen een miljoen artikelen dan in enkele honderden miljoenen gevonden en opgehaald.

Gebruik van incrementele tabellen

Het gebruik van de incrementele tabellen voor Exact Online is eenvoudig.

Met Invantive SQL kun je het actuele en bijgewerkte artikelenbestand opvragen voor alle geselecteerde Exact administraties met bijvoorbeeld:

select * 
from   ItemsIncremental@eol

Dit ziet er in de Invantive Cloud editor als volgt uit:

Voor Microsoft Power BI, Microsoft Power Query en andere OData4 consumers is aanklikken van de tabelnaam voldoende.

Analyse werking sync API’s

Alhoewel de sync API’s een veel eenvoudiger structuur kennen dan de webhooks gebruikt voor Trickle Loading, kan er toch soms reden zijn om de juistheid te controleren. Daarom biedt Invantive SQL een aantal analytische views voor de Exact Online sync API’s:

  • IncrementalLoadStatuses: de status per tabel en administratie.
  • IncrementalLoadStatistics: statistieken zoals aantal verwijderde rijen en snelheid per mutatie in de status voor tabellen en administraties.
  • IncrementalLoadEventLogEntries: tekstuele meldingen bij elke mutatie in de status voor tabellen en administraties.

De query select * from IncrementalLoadEventLogEntries toont bijvoorbeeld:

Prestaties

De prestaties van queries op de incrementele tabellen zijn grofweg een factor vijf beter dan de oorspronkelijke queries voor kleine tabellen. De factor kan variëren omdat de prestaties voornamelijk bepaalt worden door de netwerksnelheid en de snelheid van de lokale PC of server, terwijl voorheen de snelheid van de Exact Online servers de snelheid bepaalde.

Voor grote tabellen met 500.000 of meer rijen loopt de prestatiewinst op naar circa 25x sneller. Zo duurt het ophalen van 1,4 miljoen boekstukregels via TransactionLinesIncremental normaliter circa 5 kwartier en nu 3 minuten.

Releases

De sync API tabellen ItemsIncremental en TransactionLinesIncremental zijn beschikbaar vanaf release 20.1.365 BETA van Invantive SQL in alle producten en op alle operating systems.

Andere sync API tabellen worden geleidelijk beschikbaar gemaakt. Dit zal naar verwachting in het eerste kwartaal van 2021 afgerond zijn.

De sync API tabellen waren sinds de zomer van 2020 beschikbaar in Invantive SQL; de problemen van toendertijd zijn opgelost.

Lokale opslag

Invantive SQL biedt de mogelijkheid om gegevens in lokale caches bij te houden. Dit gebeurt grotendeels automatisch. De sync-API tabellen vereisen een langdurige opslag; de gegevens kunnen eenvoudig uit Exact Online gereconstrueerd worden, maar het actualiseren voor grote administraties met miljoenen boekingen per jaar kost veel tijd. De benodigde gegevens worden na versleuteling opgeslagen in de “Incrementele Cache”. De standaardlocatie hiervan is %USERPROFILE%\invantive\incdata. Voor webproducten geldt dezelfde structuur als voor andere lokale caches. De standaardlocatie kan aangepast worden via de omgevingsvariabele INVANTIVE_CONFIGURATION_INCREMENTAL_DATA_FOLDER. De inhoud van deze map ziet er ongeveer als volgt uit:

In geval van problemen kan de inhoud van de incdata-map verwijderd worden. Zorg er voor dat Invantive SQL-producten op dat moment afgesloten zijn. De Invantive SQL-engine zal de inhoud automatisch opnieuw opbouwen daarna.

Wat zijn dan de Exact Online Sync* tabellen?

Binnen Invantive SQL zijn zowel tabellen zichtbaar die beginnen met Sync zoals SyncItems als tabellen die eindigen op Incremental zoals ItemsIncremental. Wat is het verschil? Ze bevatten beiden dezelfde rijen.

De Sync tabellen zijn gebaseerd op de Exact Online sync API’s zoals SyncItems gebaseerd is op Sync/Logistics/Items. Met de Sync tabellen kun je alle mutaties vanaf een bepaalde waarde voor Timestamp ophalen. Alle rijen worden opgehaald als er geen waarde voor Timestamp wordt opgegeven; dit kost circa 1 API call per 1.000 rijen.

Het gebruik van de Sync tabellen wordt niet aangeraden tenzij men zelf de volledige logica wil schrijven in bijvoorbeeld Invantive PSQL voor het combineren van mutaties en beperken van API calls. In plaats daarvan raden we aan om de Incremental tabellen te gebruiken.

De Incremental tabellen borduren voort op de Sync API’s van Exact Online. Ze gebruiken na eerste gebruik meestal 2 API calls bij Exact Online, onafhankelijk van het aantal rijen:

  • een aanroep naar de bijbehorende Sync Exact Online REST API om alle mutaties sinds de laatste keer op te halen, en
  • een aanroep naar de Deleted API van Exact Online om alle verwijderde rijen op te halen.

Deze informatie wordt dan gecombineerd met de uitkomsten van de vorige keer tot een nieuw resultaat. Vooral bij grote aantallen rijen is dit significant sneller en eenvoudiger dan het rechtstreekse gebruik van de Sync API’s.

En de Exact Online Webhooks dan?

Voor kleinere aantallen administraties is de verwachting dat het gebruik van webhooks binnen enkele maanden serieus zal verminderen door de nieuwe mogelijkheden. Voor grotere aantallen administraties is het de verwachting dat het vervangen van mutatieberichten in Data Replicator door sync API’s pas doorgevoerd zal worden in de grote omgevingen in de tweede helft van dit jaar.

Invantive SQL in combinatie met Invantive Data Replicator is een high-end oplossing voor data replicatie en synchronisatie met Exact Online. Data Replicator is geschikt tot datavolumes van meerdere terabytes. Deze datareplicatie software is en blijft de aanbevolen keuze voor omgevingen met duizend of meer Exact Online administraties en op Exact Online blijft het advies om voor trickle loading voorlopig webhooks te blijven gebruiken.

Voor near real-time event triggering zoals marketingevents zullen de Exact Online webhooks in gebruik blijven. Dergelijke berichten kunnen via de Message Bus-schermen van Invantive Cloud beheerd worden.

Automatisch Herladen/Vergeten

Op 2 juli 2021 bleek dat de verwijderingen in de Deleted API van Exact Online maximaal 2 maanden bewaard worden. De volledigheid van gegevens is in het gedrang als er langer dan 2 maanden niet ververst wordt bij releases tot 20.1.437. Het is aan te raden vaker dan eens per maanden de historie bij te werken.

Vanaf release 20.1.437 is het gedrag veranderd van de *Incremental tabellen. Als de tabel 50 dagen niet bijgewerkt is, dan wordt de tabel volledig herladen. Deze periode kan ingesteld worden via max-age-sync-last-unused-days:

Maximum age in days of the last download for the *Incremental tables when it has not been refreshed, before being obsoleted and reloaded.

Daarnaast is het mogelijk geworden om de inhoud volledig te laten vergeten via max-age-sync-initial-days na een instelbaar aantal dagen dat standaard op 90 dagen staat:

Maximum age in days of the initial download for the *Incremental tables before being obsoleted and reloaded.

Niet Ondersteunde Sync API’s

Voor een aantal sync API’s zijn er momenteel geen *Incremental tabellen. De volgende lijst geeft een overzicht:

  • DocumentAttachments (entitype ID 4): door twijfels aan correctheid momenteel niet ondersteund. Wordt in loop van 2021 geherevalueerd.
  • Document (entitype ID 6): door twijfels aan correctheid momenteel niet ondersteund. Wordt in loop van 2021 geherevalueerd.
  • SalesItemPrices (entitype ID 8): door twijfels aan correctheid momenteel niet ondersteund. Wordt in loop van 2021 geherevalueerd.
  • PaymentTerms (entity type ID 10): had eerder geen endpoint, kwam alleen voor bij DeletedItems. Zal toegevoegd worden.
  • TimeCostTransactions (entity type 11): heeft geen endpoint, komt alleen voor bij DeletedItems.
  • GLClassifications: is wel gedocumenteerd als API, maar schijnt niet te bestaan. Bestaat ook geen entity type ID voor.
  • GoodsDeliveries (geen entity type): er bestaat wel een sync API voor, maar door ontbreken van een entity type ID bij DeletedItems zal bij herstel van een backup de dataset incorrect zijn. Voorrang wordt gegeven aan correctheid boven snelheid.
  • GoodsDeliveryLines (geen entity type): er bestaat wel een sync API voor, maar door ontbreken van een entity type ID bij DeletedItems zal bij herstel van een backup de dataset incorrect zijn. Voorrang wordt gegeven aan correctheid boven snelheid.

De documentatie over sync API’s en correctheid van implementaties laat ruimte voor verbetering in toekomstige Exact Online releases. We betrachten daarom terughoudendheid bij het verwerken van toevoegingen. Verder waren in het verleden drie sync API’s per abuis gedocumenteerd die niet bestonden en zover bekend ook niet gepland zijn:

  • DeductibilityPercentages
  • StockBatchNumbers
  • StockSerialNumbers

Deze drie zijn inmiddels uit de documentatie verwijderd.

Klopt het dat ik in de Query Tool met een Incremental query geen resultaten heb omdat er geen timestamp meegestuurd wordt? Of zou dit ondanks dat dan gewoon alles binnen de limieten van de query moeten ophalen?

Ook met Query Tool moeten rijen terugkomen. Zolang de *Incremental-tabellen gebruikt worden, worden alles qua compleet maken oorspronkelijke sync API data, mutaties en verwijderde onder de motorkap opgelost. Mochten er toch rijen terugkomen uit een *Bulk-tabel en niet uit *Incremental, gelieve dan expliciet de versie te vermelden van Invantive Query Tool en de gebruikte queries op Exact Online.