Kopieer Exact Online naar SQL Server

[forums]

English version

Naast het gebruik van beheerde massale data replicatie met Invantive Data Replicator of Azure Data Factory, kunt u ook een kopie maken van tabellen in de 70+ ondersteunde platformen naar een Microsoft SQL Server omgeving. Dit item beschrijft hoe u een geautomatiseerde dagelijkse kopie kunt configureren vanuit Exact Online naar een on-premise Microsoft SQL Server.

Voor SQL Server op Azure of AWS kunt u deze aanpak ook gebruiken, maar ook Data Replicator, een Invantive Cloud-applicatie of de Exact Online Azure Data Factory Connector van Invantive Cloud

Invantive Cloud biedt ook ETL functionaliteit vergelijkbaar met Azure Data Factory. Voor een voorbeeld, zie:

Elementaire datareplicatie tussen Exact Online en Azure SQL Server

Go to English version Dit artikel leidt u door de creatie van een elementaire applicatiemodule op Invantive Cloud om data te repliceren van Exact Online naar een tabel die opnieuw wordt aangemaakt in SQL Server elke keer dat de module wordt uitgevoerd. Geavanceerde en/of geplande replicatie van Exact Online naar Microsoft SQL Server is beschikbaar via Invantive Data Replicator en/of Invantive Data Hub. Deze stappen zijn zowel bruikbaar voor Azure SQL Server als on-premises Microsoft SQL Server…

De Exact Online kopie bevat in dit voorbeeld:

• grootboekrekeningen,
• alle transacties en hun regels,
• kosteneenheid definities,
• werknemers,
• verkooporders plus regels,
• verkoopfacturen plus lijnen
• rekeningen en hun adressen en classificaties,
• artikelen, artikelgroepen en voorraadposities,
• prijslijsten.

Het toevoegen van een van de duizend andere tabellen is eenvoudig: u kopieert het SQL-statement en verandert de naam van de tabel. De lijst van de ruim 1.100 beschikbare Exact Online tabellen staat op:

SQL-stuurprogramma voor Exact Online API

De stappen in dit artikel staan op zichzelf, maar gezien de vele en complexe onderdelen die erbij betrokken zijn, is het aan te raden de gratis uitleg van een uur te gebruiken om snel aan de slag te kunnen. De gratis uitleg kan worden ingepland via:

Introduction | Invantive Tools

Software installeren

Eerst zult u Invantive Data Hub moeten installeren op een PC of server. Meestal wordt dit gedaan met een proefversie van Invantive Data Hub, maar u kunt ook Invantive Query Tool gebruiken met een premium licentie. U vindt een download op https://download.invantive.com zoals https://download.invantive.com/current/Invantive%20Data%20Hub-current.msi.

Start vervolgens Invantive Data Hub en configureer een proeflicentie die onbeperkte toegang geeft tot bijna alle Invantive SQL functionaliteit en stuurprogramma’s voor 30 dagen.

Bereid omgeving voor

U heeft credentials nodig om in te loggen op Exact Online en SQL Server, en een database om de kopie in te laden. Voer de volgende stappen uit:

• Krijg toegang tot een Exact Online omgeving met één of meerdere bedrijven met behulp van een aanmeldcode met wachtwoord.
• Bewaar de QR-code en de gekoppelde secret key van de gebruiker (uitleg).
• Registreer een Exact Online applicatie.
• Open SQL Server Management Studio en log in als gebruiker ‘sa’.
• Creëer een nieuwe database met de naam exactonline.
• Maak een nieuwe database gebruiker exactonline met SQL authenticatie.
• Maak de nieuwe database gebruiker db_owner van de nieuwe database.

Configureer gedistribueerde database

Standaard configureert Invantive Data Hub automatisch meer dan 70 verbindingen voor platformen. Dit zijn echter allemaal databases die elk één platform aanspreken, zoals Salesforce. Om gegevens van het Exact Online platform naar Microsoft SQL Server te kopiëren moet u een zogenaamde “gedistribueerde database” definiëren. Een gedistribueerde database bundelt meerdere datacontainers zoals heel Exact Online Nederland, Salesforce en SQL Server in één overkoepelende virtuele database.

Maak een bestand settings-distributed.xml aan in %USERPROFILE%invantive. Als u %USERPROFILE% in de adresbalk van de Windows Verkenner invoert, navigeert u naar de juiste map met de naam C:\users\LOGONCODE.

Het settings.xml XML-gebaseerd bestandsformaat staat beschreven in Settings.xml XML-formaat voor virtuele SQL databases.

De inhoud van dit bestand is:

<?xml version="1.0" encoding="utf-16"?>
<settings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
 version="5" 
 forcedefault="false"
>
  <group name="***BEDRIJFSNAAM***" >
    <connection name="exact-online-nl-sql-server"
    >
      <database order="0" 
       alias="eol" 
       provider="ExactOnlineAll"
       defaultUserLogonCode="EXACTONLINEUSERCODE"
       defaultPassword="EXACTONLINEUSERPASSWORD"
       connectionString="api-url=https://start.exactonline.nl;api-client-id=***CLIENT ID***;api-redirect-url=https://clientredirect.invantive.com;totp-secret=TOTPSECRET" 
       AllowConnectionStringRewrite="false"
      />
      <database alias="sqlserver" 
        provider="SqlServer" 
       connectionString="Data Source=***HOST***;Database=***DATABASE NAME***;UID=***DATABASE USER LOG ON CODE***;PWD=***DATABASE USER PASSWORD***"
       AllowConnectionStringRewrite="false" 
      />
    </connection>
  </group>
</settings>

Het aanmelden gebruikt de Implicit Grant Flow.

Het gebruik van de Code Grant Flow met een OAuth refresh token is ook mogelijk, maar vereist enige achtergrondkennis door de hedendaagse beperkingen op Exact Online. Indien er niet parallel gedraaid wordt, dan volstaat het toevoegen van het client secret in de connection string als client-secret=secret.

Vergeet niet om de volgende plaatshouders te vervangen:

• BEDRIJFSNAAM: uw gewenste bedrijfsnaam zoals ‘Acme Corp.’.
• CLIENT ID: het client ID zoals vastgelegd bij Apps Beheren op apps.exactonline.com.
• HOST: de naam of het IP-adres van uw SQL Server provider.
• DATABASE NAME: de naam van de database die u heeft aangemaakt, dus exactonline.
• DATABASE USER LOG ON CODE: de naam van de gebruikersaccount, dus exactonline.
• DATABASE USER PASSWORD: het wachtwoord van de gebruikersaccount.
• EXACTONLINEUSERCODE: de Exact Online aanmeldcode.
• EXACTONLINEUSERPASSWORD: het wachtwoord van de Exact Online gebruiker.
• TOTPSECRET: het TOTP-secret dat hoort bij de QR-code.

SQL-kopieerscript

Het volgende Invantive SQL script maakt en werkt de kopie van Exact Online bedrijven in SQL Server bij.

Sla de volgende inhoud op als exact-online-copy.sql in een map met de naam C:\test:

local remark Wanneer er een fout optreedt bij het repliceren, 
local remark ga dan door met repliceren en meld de fout 
local remark aan het einde van het proces.

local on error continue

local remark Selecteer alle beschikbare Exact Online bedrijven.

use all@eol

local remark Sla de HTTP verzoeken niet op in een cache om 
local remark geheugen te besparen voor zeer grote omgevingen.

set use-http-memory-cache@eol false

create or replace table eol_salesorders@sqlserver
as 
select *
from   exactonlinerest..salesordersbulk@eol

create or replace table eol_salesorderlines@sqlserver
as 
select *
from   exactonlinerest..salesorderlinesbulk@eol

create or replace table eol_salesinvoices@sqlserver
as 
select *
from exactonlinerest..salesinvoicesbulk@eol

create or replace table eol_salesinvoicelines@sqlserver
as 
select *
from   exactonlinerest..salesinvoicelinesbulk@eol

create or replace table eol_addresses@sqlserver
as 
select *
from   exactonlinerest..addressesbulk@eol

create or replace table eol_accounts@sqlserver
as 
select *
from   exactonlinerest..accountsbulk@eol

create or replace table eol_items@sqlserver
as 
select *
from   exactonlinerest..itemsbulk@eol

create or replace table eol_stockpositions@sqlserver
as 
select *
from   stockpositionsincremental@eol

create or replace table eol_pricelists@sqlserver
as 
select *
from   exactonlinerest..pricelists@eol

create or replace table eol_itemgroups@sqlserver
as 
select *
from   exactonlinerest..itemgroups@eol

create or replace table eol_costunits@sqlserver
as 
select *
from   exactonlinerest..costunits@eol

create or replace table eol_glaccounts@sqlserver
as 
select *
from   exactonlinerest..glaccountsincremental@eol

create or replace table eol_employees@sqlserver
as 
select *
from    exactonlinerest..employees@eol

create or replace table eol_accountclassifications@sqlserver
as 
select *
from   exactonlinerest..accountclassifications@eol

create or replace table eol_transactionlinesbulk@sqlserver
as 
select *
from   exactonlinerest..transactionlinesbulk@eol

Gebruik de Exact Online API data model documentatie om te zoeken naar meer benodigde tabellen, of blader door het grafische tabel relatie diagram.

Maak vervolgens een batch-bestand aan om dit script uit te voeren. De gemakkelijkste manier om dit te doen is door:

• Start de Invantive Query Tool.
• Log in op de database exact-online-nl-sql-server in je bedrijfsgroep.
• Open het bestand c:\test\exact-online-copy.sql.
• Kies ‘Editor’ in het menu en vervolgens ‘Create Data Hub Batch Script’.
• Selecteer ‘Opslaan’.
• Noem het batchbestand c:\test\exact-online-kopie.bat.
• Het resulterende batchbestand zal er zo uitzien:

@echo off
rem
rem Script om een Invantive SQL bestand uit te voeren met mogelijke Invantive Script verklaringen.
rem

set INVANTIVE_PRG=C:\Program Files (x86)\Invantive Software BV\Invantive Data Hub\Invantive.Producer.QueryEngine.exe
set INVANTIVE_CONN=BEDRIJFSNAAM\exact-online-nl-sql-server
set INVANTIVE_SQL_FILE=c:\test\exact-online-copy.sql
set INVANTIVE_LOG_FILE=c:\test\exact-online-copy.log
set INVANTIVE_LOG_FILE_OVERWRITE=False
set INVANTIVE_INTERACTIVE=False

"%INVANTIVE_PRG%" /connection:"%INVANTIVE_CONN%" /file:"%INVANTIVE_SQL_FILE%" /logfile:"%INVANTIVE_LOG_FILE%" /logoverwrite:"%INVANTIVE_LOG_FILE_OVERWRITE%" /interactive:%INVANTIVE_INTERACTIVE%

Kopieer Exact Online tabellen

Om de gegevens van Exact Online daadwerkelijk naar SQL Server te kopiëren, voert u het batch-bestand c:\test\exact-online-copy.bat uit.

Dit zal iets weergeven als:

De tabellen in SQL Server worden aangemaakt met behulp van bulk inserts van de gegevens die worden opgehaald uit Exact Online. Waar nodig zult u de select-clausule moeten aanpassen om kolomnamen zoals ‘User’ te herschrijven naar iets dat wordt ondersteund op SQL Server. Anders zal Invantive SQL deze automatisch herschrijven naar een toegestane kolomnaam.

Nadat het laden is voltooid, zullen er een aantal indexen op de tabellen zijn, gebaseerd op de verdeling en hoeveelheid van de gegevens. U kunt meer indexen toevoegen op de tabellen voor betere prestaties, gebaseerd op uw kennis van het datamodel en de omgeving.
Identieke tabelgegevens van alle geselecteerde Exact Online bedrijven worden automatisch opgeslagen in één tabel voor alle bedrijven. Dit vergemakkelijkt de consolidatie tussen bedrijven.

Natuurlijk kunt u de datasets die u ophaalt uit Exact Online filteren voor betere prestaties, zoals filteren op status om alleen open verkooporders op te halen. Invantive SQL vertaalt automatisch uw where-clausules naar filters die worden doorgestuurd naar Exact Online voor betere ophaalprestaties.

Het wordt aanbevolen om de Microsoft Windows Taakplanner te gebruiken om het batchbestand dagelijks uit te voeren zoals beschreven op Starting Invantive Data Hub scripts from Windows Task Scheduler - Super User.

Verschillen

Vergeleken met Invantive Data Replicator, zijn er een aantal nadelen waar u zich bewust van moet zijn als u deze aanpak gebruikt:

• Versiebeheer: Data Replicator voegt automatische versiebeheer van data toe om 24x7 beschikbaarheid van datasets mogelijk te maken.
• Gegevensmodel: Data Replicator onderhoudt automatisch het datamodel en beeldt kolomnamen en tabelnamen af volgens de mogelijkheden van het doelplatform.
• Prestaties: Data Replicator stemt automatisch de data af en partitioneert deze voor zeer grote datasets, zowel in termen van partities als individuele partitiegrootte. Hierdoor kunt u ook duizenden administraties in 1x verwerken.
• Incrementeel: Data Replicator biedt incrementele laadstrategieën zoals het gebruik van web hooks en smart sampling gericht op grote volumes.
• Audit: Data Replicator voegt extra logging en auditing functies toe om compliance met wettelijke rapportage applicaties te vergemakkelijken.

In het algemeen, gebruik Invantive Data Replicator voor data volumes tussen 10 GB en 5 TB en/of meer dan 100 bedrijven. Gebruik Data Hub voor kleinere volumes.