koppeling AFAS Profit & Microsoft Azure & Power BI

De eerdere posts waren gericht op het live (of near live) ontsluiten van data uit AFAS Profit naar een Power BI rapport. Alhoewel dit erg goed werkt voor rapporten waarbij de data zo live mogelijk moet zijn is het niet de beste optie als performance belangrijker is dan de meest actuele data. Zeker in de gevallen waarbij je de data maar eens per dag of nog minder hoeft te verversen.

Voor dit scenario maken we van de volgende diensten gebruikt:

  • AFAS Profit (on-prem of AFAS Online)
  • Azure Automation
  • Azure (Blob) Storage
  • Microsoft Power BI

De kosten van de Azure storage zijn afhankelijk van het gebruik, maar normaal gezien zouden we het over maximaal enkele euro’s per maand moeten hebben. De precieze kosten van Azure Blob Storage vind je eventueel hier (https://azure.microsoft.com/nl-nl/pricing/details/storage/blobs/)

Het stappenplan bevat de volgende onderdelen:

  • Aanmaken getconnectoren AFAS Profit
  • Aanmaken app-connector AFAS Profit en genereren token
  • Aanmaken Azure Automation instance
  • Aanmaken Azure Blob Storage
  • Invoeren Automation Runbook
  • Koppelen Power BI rapport

Aanmaken getconnectoren AFAS Profit

Binnen AFAS Profit kan je middels het aanmaken van een getconnector data buiten het pakket beschikbaar stellen. Deze connector is na het uitvoeren van de volgende stap vai de REST API beschikbaar.

De belangrijkste keuze die je in deze stap moet maken (naast de te ontsluiten velden natuurlijk) is waar je de filtering van de data uit wil voeren. Het is altijd een goed idee om niet meer data beschikbaar te stellen dan strikt noodzakelijk. Het heeft dan ook mijn persoonlijke voorkeur om filters waarvan je zeker weet dat die nooit veranderen aan de Profit kant in te regelen en alle overige filters af te handelen in de scripts die de data ophalen (of Power BI zelf).

Hoe je een getconnector in AFAS Profit aanmaakt vind je hier. (https://help.afas.nl/help/NL/SE/App_Cnr_XML_Get_Build.htm)

Aanmaken app-connector AFAS Profit en genereren token

De getconnectoren die we in de vorige stap aangemaakt hebben moeten nu nog naar buiten toe bescchikbaar gesteld worden. In de onderstaande twee stukken heb ik uitgewerkt hoe je de connectoren kan toevoegen aan een App connector en hoe je de autorisatie tokens klaar kan maken voor gebruik:

Normaal gezien gebruik ik een systeem gebruiker (Binnen AFAS Profit) en een specifieke groep van connectoren zodat ik de autorisatie van deze user middels groep autorisatie in kan regelen.

Alle get-connectoren die je mee neemt in de app connector zullen worden gedownload. Je kan de lijst later dus ook nog uitbreiden. Als je de lijst wil inkrimpen zal je handmatig de json’s van de verwijderde connectoren moeten verwijderen uit je blob-storage.

Aanmaken Azure Automation instance

Tot dusver hebben we nog niets gedaan dat we in eerdere stappen niet ook al gedaan hebben. Voor de volgende stappen moeten we in de Azure omgeving een aantal dingen voorbereiden. Je kan als je nog geen account hebt via https://portal.azure.com een nieuw account aanmaken. De kosten van de onderdelen die we inzetten zijn we erg afhankelijk van hoeveel data we opslaan, maar normaal gezien zou het maar een paar euro per maand moeten zijn.

In de Azure portal kan je zoeken naar “Azure Automation” je krijgt dan een optie om een Azure Automation Acocunt aan te maaken, als je dat onderdeel opent krijg je een beeld zoals hier onder:

Azure Automation menu

Klik hier op “Add” om een nieuwe Azure Automation Instance aan te maken. Je krijgt een venster zoals hier onder te zien:

Azure Automation Account aanmaken

Je moet de instance een naam geven en eventueel aan een bestaande resource group koppelen, als je die nog niet hebt kan je die ook meteen even aanmaken. Tot slot moet je een regio kiezen, qua functionaliteit maakt het niet uit, maar het is sterk aan te raden om de dichtstbijzijnde regio te kiezen. In ons geval dus West Europa.

Als alles goed gegaan is krijg je uiteindelijk een venster zoals dit te zien:

Azure Automation overview

Om er voor te zorgen dat je alle acties uit kan voeren is het noodzakelijk om te zorgen dat de juiste modules geactiveerd zijn (via de modules knop kan je de lijst aanpassen). In ons geval is dit de lijst die we gebruiken:

Azure Automation Modules

Dit zijn er iets meer dan strikt noodzakelijk voor dit artikel maar dat is verder geen probleem. Let er wel op dat je deze modules af en toe zal moeten updaten.

Het aanmaken van het Automation Account kan even duren, je kan onderstussen aan de slag met de volgende stap, we maken het Automation Account verderop af.

Vanuit hier kunnen we verder met de voorbereidingen en gaan we een Storage account aanmaken.

Aanmaken Azure Blob Storage

In tegenstelling tot de directe koppelingen waar ik eerder over schreef maken we nu een tussenstap door de data in Azure Blob Storage te zetten. Dat komt de performance ten goede en bied ook een aanzienlijk aantal extra mogelijkheden als we meer met de data zouden willen doen.

Op de Azure portal zoeken we naar “Storage Acocunts”. Als je daar op klikt krijg je een overzicht met de bestaande storage accounts. Indien nodig kan je nu zelf een nieuw acocunt aanmaken door op “Add” te klikken

Microsoft Azure Storage Accounts

Over de gevraagde velden in, normaal gezien staat alles goed, let wel op de regio waar je het storage account aanmaakt. Voor de beste performance houd je deze in dezelfde regio als je automation account.

Microsoft Azure Storage account aanmaken

Als het aanmaken gelukt is kan je de nieuw aangemaakte resource openen:

Micorosoft Azure Storage Account overview

Kies hier voor “Blob” om de container aan te maken waar we de JSON files die uit de AFAS Profit connector komen op gaan slaan.

Microsoft Storage Blob aanmaken

Tot slot hebben we nog twee zaken nodig uit ons Azure Storage account. Allereerst hebben we het access token nodig. Deze kan je vinden door op de Azure Storage Acocunt overview pagina in het menu op “Access Keys” te klikken en key 1 of 2 te kopiëren.

Azure Storage acces keys

Vervolgens hebben we de URL nodig waarop je Blob beschikbaar is. Open daarvoor vanuit  de Azure Storage Acocunt overview pagina het “Blobs” menu en open daar de Blob die we net aangemaakt hebben. Open daar vervolgens de properties en kopier de URL.

Microsoft Azure Storage Blob url

Invoeren Automation Runbook

De eerste stap die we moeten zetten in het Azure Automation account is het aanmaken van een runbook voor het draaien van het script. Ga hiervoor naar het runbook menu:

Azure Automation Runbook

Mogelijk staan hier een aantal voorbeeldscripts, die kan je verwijderen om het beeld wat overzichtelijker te maken. Vervolgens klik je op de “nieuw” knop, en vul je de velden in. De naam die je het script geeft is niet zo van belang, maar zorg er voor dat “type” op “Powershell” staat:

Azure Automation aanmaken runbook

Vervolgens kan je het runbook bewerken om het script toe te voegen. Het script heb ik onder aan deze post staan en nog even toegelicht.

Azure Automation Runbook bewerken

Wanneer je de code ingevoerd hebt kan je op “Publish” drukken, er is ook een save knop, maar het script draait altijd de laatste versie die gepubliceerd is, dus zeker voor de eerste keer opslaan is het van belang om “Publish” te gebruiken.

Je kan nu terug gaan naar het runbook overzicht zoals hier onder, als je op het script klikt ga je na de status pagina van dat script/runbook. Als je hier op “run” drukt zal het script in de wachtrij gezet worden, je gaat dan naar een status pagina waar je de “job” kan bekijken.

Azure Automation Runbook overzicht
Azure Automation Job status

Als alles goed gegaan is zal de job naar completed springen en zie je geen errors. (mogelijk moet je even op refresh drukken). Je kan ondertussen ook de blob storage opnieuw openen en controleren of de files daar verschenen zijn.

Tot slot willen we nog een schema toevoegen om het script/runbook periodiek te draaien. Je kan in de runbook op “Schedule” klikken:

Azure Automation Scheduling

Als je hier de stappen volgt kan je een script toevoegen dat bijvoorbeeld ieder uur of iedere dag draait:

Azure Automation Schedule aanmaken

Script

Je vind de code van het script hier (op Github, https://github.com/HermanRonk/ProfitExportToAzureBlob ), de nummers hier onder verwijzen naar de verschillende blokken in dat script.

  1. In het eerste blok worden de inloggegevens voor de Profit omgeving vastgelegd. En de header opgebouwd
  2. Hier worden de Variabelen voor het Azure Storage account vastgelegd.
  3. Hier maken we verbinding met de Azure Storage omgeving
  4. Hier halen we eerst de lijst met geautoriseerde connectoren op en vervolgens loopen we door deze connectoren heen. Voor iedere connector wordt een JSON file naar de storage geschreven.

Koppelen Power BI rapport

Het koppelen van de databron werkt niet helemaal intuitief, het is dus zaak om deze stappen goed te volgen.

Open de Power BI client en begin een nieuw rapport

  • Kies voor een nieuwe gegevensbron, gebruik eventueel het zoekveld en zoek naar Azure Blob
Power BI Gegevensbron toevoegen
  • Je krijgt vervolgens een veld waar je het adres van de Azure Blob in kan voeren. Je vind dit adres op de properties pagina van de Storage Blob in Azure.
Power BI gegevensbron URL
  • In het volgende veld moet je de “Key” van het storage account invoeren. Je vind deze “Key” op de “access keys” pagina van het storage account (dus 1 niveau hoger dan de Storage Blob).
Power BI Blob Accountsleutel
  • Je krijgt vervolgens een pagina te zien met alle beschikbare files op de Blob, je mag hier op laden klikken
Power BI Tabel
  • Als je vervolgens de Query Editor opent zie je een aantal regels, klik in de eerste regel op “Binary”
Power BI Tabel
  • In het volgende venster zie je weer een aantal regels, klik hier op “List”
Power BI List
  • In de nieuwe weergave mag je in het ribbon op de “Naar tabel” knop drukken
Power BI convert to table
  • Vervolgens kan je op de onderstaande knop drukken om de tabel om te zetten in alle kolommen.
Power BI Expand
  • Selecteer hier de kolommen die je wil gebruiken.
Power BI Kolommen selecteren

Je kan deze stappen herhalen voor iedere JSON / Connector die je in je rapportage wil gebruiken. Om de eerste stappen wat te versnellen kan je eventuele in de advanced editor de code die je heb na stap 4 kopieren en als nieuwe query plakken. Dat scheel eventueel wat handwerk.

Als je deze stappen doorlopen hebt is de data “live” beschikbaar in Power BI. Afhankelijk van de refresh die je in Azure Automation hebt ingesteld zal de data periodiek worden bijgewerkt. Als je de rapportage open hebt staan kan je op “Refresh” drukken om de nieuwe data op te halen.

Tot slot

Als je de bovenstaande stappen gevolgd heb zal alles normaal gezien naar behoren moeten werken. Er is dan wel nog ruimte voor verbetering, maar om het voorbeeld een beetje overzichtelijk te houden heb ik het hier bij gehouden. De eerste stap die eigenlijk nog gezet moet worden is het het gebruiken van de Azure Credential Store in je Azure Automation account, dat voorkomt dat de autorisatie tokens zichtbaar in het script staan en is dus uiteindelijk een veiligere implementatie.

Verder kan je het versiebeheer en de uitrol van het script nog mooi inregelen in Azure Devops of Github, maar dat is een post voor later :).