Terug naar lijst

Bij een ‘lokale’ installatie van Exact is het heel makkelijk om gegevens uit de database op te halen om verder te analyseren. Mijn collega Gertjan heeft hier al eens eerder over geblogd (zie https://growteq.nl/2016/06/28/correcte-auditfiles-genereren-uit-exact-globe/ ).

Als je gebruik maakt van Exact Online is dit minder recht toe recht aan. Exact Online werkt met een RESTfull API webservice (REST API) waarmee via het internet gegevens kunnen worden uitgelezen. De techniek rondom REST API’s bestaat al veel langer en hier is ook veel informatie over beschikbaar. Exact geeft ook zelf veel informatie op haar website beschikbaar over de mogelijke koppelingen (https://developers.exactonline.com/).

In onze projecten hebben wij de REST API van Exact Online al gebruikt. Hierbij maakten wij dan gebruik van SQL Server Integration Services in combinatie met C# scripts. Deze oplossing is vaak onderdeel van een grotere datawarehouse implementatie. Voor de gebruiker die ook ‘Self Service‘ gegevens uit Exact Online wil gebruiken voor analyses kan de REST API ook rechtstreeks in Power BI worden ingelezen. In deze blog laat ik zien welke stappen hiervoor moeten worden doorlopen.

Benodigdheden

Proces van authenticatie en autorisatie

Eén van de grootste uitdagingen bij het aanroepen van de REST API van Exact Online is het doorlopen van het proces van authenticatie en autorisatie. Exact Online maakt namelijk gebruik van de techniek OAUTH 2.0. Power BI ondersteunt deze techniek helaas (nog) niet vanuit de standaard functionaliteit.

OAUTH 2.0 maakt gebruik van tokens die worden uitgegeven nadat de klant met zijn inloggegevens is ingelogd in Exact Online.
Schematisch ziet dat er als volgt uit (op hoofdlijnen):

auth

Voor de uitwisseling van de gegevens is het niet nodig dat de inloggegevens elke keer moeten worden meegestuurd om de webservice aan te roepen. De toestemming die aan de app wordt gegeven is een jaar geldig. Na een jaar moet wederom toestemming gegeven worden.

Stappenplan

De volgende stappen zijn nodig om de gegevens vanuit de REST API in te kunnen lezen:

  1. Registratie van een app bij Exact Online
  2. Aanvragen access token via Postman
  3. Aanroepen van de API in Power BI

 

Registratie van een app bij Exact Online

Om de RESTfull API van ExactOnline te kunnen gebruiken moet je je eerst registreren als Developer in de App center van Exact Online. Deze registratie verloopt via https://www.exact.com/nl/exact-online/partners/app-center-partner.
Naast de mogelijkheid om de REST API’s te gebruiken, krijg je ook toegang tot een test administratie.

Nadat je een account hebt aangemaakt, kun je inloggen in de App Center via de link https://apps.exactonline.com/

Nadat je bent ingelogd, klik je bovenin op Registreer API Keys.

Klik op ‘Een nieuwe app toevoegen:

2

Voeg een App naam toe en vul als Redirect URL https://www.getpostman.com/oauth2/callback in.
Klik op Bewaren.

3

De App verschijnt nu in het overzicht.

4

Klik op Bewerken.

Onder het kopje Authenticatie vind je de Client ID en de ClientSecret.
Deze gegevens hebben we straks nodig, dus sla deze ergens op of houdt de website open.

5

Aanvragen access token via Postman

Zoals aangegeven ondersteunt Power BI standaard geen OAUTH 2.0 authenticatie flow. Daarom maken wij hier gebruik van Postman om deze authenticatie te regelen. De verleende toestemming is een jaar geldig, dus dan kunnen we in ieder geval even vooruit.

Open Postman en klik in het rechterscherm op Autorization:

6

Selecteer als Type OAuth 2.0.

Klik nu op Get New Access Token

7

Vul het pop-up scherm dat volgt in zoals in de afbeelding is aangegeven. Gebruikt als Client_id en Client_secret de gegevens van de app die in de eerste stap van deze blog is aangemaakt.

8

Als je kiest voor Request Token kom je in het volgende scherm van Exact Online.
9

 

Vul jouw inloggegevens voor Exact Online in. In het daarop volgende scherm geef je als gebruiker toestemming aan de App om verbinding te mogen maken met Exact Online.

Klik op Toestaan en je keert weer terug naar Postman.

Selecteer de zojuist aangemaakte Token ‘ExactOnline’.
Aan de rechterkant van het scherm zie je nu de gegevens van de token verschijnen.

10

De access_token is 600 seconden geldig. Daarna kan met behulp van een refresh_token een nieuw access_token worden opgevraagd.

Als je iets naar beneden scrolt zie je ook de gegevens van het refresh_token.

11
Deze token gaan we gebruiken in Power BI.

Aanroepen van de API in Power BI

Open Power BI desktop.

Kies in het lint op het tabblad Home voor Edit Queries

12

Kies in de Query Editor in het lint op tabblad Home nu voor Manage Parameters.

13

Maak via de knop New de volgende parameters aan:

14

Client_id  & Client_secret
Gebruik de gegevens voor het client-id en client_secret de gegevens die in stap 1 zijn aangemaakt.

URL
Vul hier de waarde https://start.exactonline.nl in.

Refresh_token
Als waarde voor de refresh_token moet de waarde van de refresh_token worden ingevuld die we in Stap 2 in Postman hebben opgevraagd.

URI

Exact Online kent vele verschillende resources die kunnen worden opgevraagd. Via de volgende link is de uitgebreide lijst terug te vinden: https://start.exactonline.nl/docs/HlpRestAPIResources.aspx?SourceAction=10

In deze blog gaan we een lijst met Grootboekrekeningen en Omschrijving ophalen. Vul als waarde bij de URI ‘financial/GLAccounts’ in.

DivisionCode

Power BI moet weten voor welke administratie de gegevens opgehaald moeten worden. Vul hier de DivisionCode vanuit Exact in.

Dit is overigens niet hetzelfde als het administratienummer in Exact Online.
Mocht je de DivisionCode niet weten ga dan naar Postman.

Vul achter GET de volgende URL in: https://start.exactonline.nl/api/v1/current/Me?$select=CurrentDivision

15

Vraag een nieuwe Access Token aan

16

Eventueel kun je de Access Token uit Stap 2 gebruiken, maar als deze verlopen is, kun je een nieuwe aanvragen.

Selecteer de Access Token en klik aan de rechterkant op Use Token.

17

Klik bovenin op de blauwe knop Send.

18

Onderin Postman krijg je nu het resultaat.

Tussen de tags CurrentDivision vind je de DivisionCode die je kunt gebruiken.

 19

Gegevens ophalen

Klik op OK en kies in het lint op het tabblad Home nu voor New Source en dan Blank Query.

20

Vul in het Advanced Editor scherm de volgende code in:


let

actualUrl =  URL & "/api/oauth2/token",

cntnt = Text.ToBinary(Uri.BuildQueryString(

[refresh_token= Refresh_token

,grant_type="refresh_token"

,client_id= Client_id

,client_secret=Client_secret

])),


options = [Headers =[#"Content-type"="application/x-www-form-urlencoded"],Content=cntnt],


result = Web.Contents(actualUrl, options),

#"JSON" = Json.Document(result),

access_token= #"JSON"[access_token],


AccessTokenHeader = "Bearer  " & access_token,

GetJsonQuery = (Web.Contents( URL & "/api/v1/"& Number.ToText(DivisionCode) & "/" & URI,

[Headers=[Authorization=AccessTokenHeader]])),

#"Imported XML" = Xml.Tables(GetJsonQuery,null,65001),

#"Changed Type" = Table.TransformColumnTypes(#"Imported XML",{{"id", type text}, {"updated", type datetime}}),

#"Expanded link" = Table.ExpandTableColumn(#"Changed Type", "link", {"Attribute:rel", "Attribute:title", "Attribute:href"},

{"link.Attribute:rel", "link.Attribute:title", "link.Attribute:href"}),

#"Expanded entry" = Table.ExpandTableColumn(#"Expanded link", "entry",

{"id", "title", "updated", "author", "link", "category", "content"}, {"entry.id", "entry.title", "entry.updated",

"entry.author", "entry.link", "entry.category", "entry.content"}),

#"Expanded entry.content" = Table.ExpandTableColumn(#"Expanded entry", "entry.content",

{"http://schemas.microsoft.com/ado/2007/08/dataservices/metadata", "Attribute:type"},

{"entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata", "entry.content.Attribute:type"}),

#"Removed Other Columns" = Table.SelectColumns(#"Expanded entry.content",{"link.Attribute:title",

"entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"}),

#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([#"link.Attribute:title"] = "GLAccounts")),

#"Expanded entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" =

Table.ExpandTableColumn(#"Filtered Rows", "entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata",

{"properties"}, {"entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties"}),

#"Removed Other Columns1" = Table.SelectColumns(#"Expanded entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata",

{"entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties"}),

#"Expanded entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties" =

Table.ExpandTableColumn(#"Removed Other Columns1",

"entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties", {"http://schemas.microsoft.com/ado/2007/08/dataservices"},

{"entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices"}),

#"Expanded entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices"

= Table.ExpandTableColumn(#"Expanded entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties",

"entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices",

{"Code", "Description"}, {"Code", "Description"}),

#"Sorted Rows" = Table.Sort(#"Expanded entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.

http://schemas.microsoft.com/ado/2007/08/dataservices",{{"Code", Order.Ascending}})

in

#"Sorted Rows"

In hoofdlijnen doet deze code het volgende:

  • Deze code vraagt met behulp van de refresh_token een nieuw access_token aan;
  • Deze access_token wordt gebruikt in de aanroep van de API URL financials/GetAccounts;
  • Het laatste gedeelte van de code zet de response van de API om in een tabel en verwijderd de gegevens die we hier niet nodig hebben.

Het resultaat van deze code is dat er nu een tabel verschijnt met de grootboekrekeningen van de geselecteerde administratie:

21

Conclusie

Het gebruiken van de REST API van Exact Online in Power BI is niet geheel recht toe recht aan.  Met name het gedeelte rondom de authenticatie vraagt een aantal extra handelingen. Maar als één en ander eenmaal is ingericht, kun je je met behulp van de refresh_token jezelf in Power BI authentiseren.

Exact Online is een grote bron van gegevens waar zeker veel informatie uitgehaald kan worden met behulp van Power BI. Meer dan genoeg om uitgebreid te analyseren in ieder geval.

Succes!


MEER WETEN OVER EXACT ONLINE EN ONZE POWER BI DIENSTVERLENING? https://growteq.nl/microsoft-power-bi/