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:
Klik hier op “Add” om een nieuwe Azure Automation Instance aan te maken. Je krijgt een venster zoals hier onder te zien:
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:
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:
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
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.
Als het aanmaken gelukt is kan je de nieuw aangemaakte resource openen:
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.
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.
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.
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:
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:
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.
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.
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:
Als je hier de stappen volgt kan je een script toevoegen dat bijvoorbeeld ieder uur of iedere dag draait:
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.
- In het eerste blok worden de inloggegevens voor de Profit omgeving vastgelegd. En de header opgebouwd
- Hier worden de Variabelen voor het Azure Storage account vastgelegd.
- Hier maken we verbinding met de Azure Storage omgeving
- 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
- 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.
- 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).
- Je krijgt vervolgens een pagina te zien met alle beschikbare files op de Blob, je mag hier op laden klikken
- Als je vervolgens de Query Editor opent zie je een aantal regels, klik in de eerste regel op “Binary”
- In het volgende venster zie je weer een aantal regels, klik hier op “List”
- In de nieuwe weergave mag je in het ribbon op de “Naar tabel” knop drukken
- Vervolgens kan je op de onderstaande knop drukken om de tabel om te zetten in alle kolommen.
- Selecteer hier de kolommen die je wil gebruiken.
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 :).
Hoi,
Ik heb je blog posts t.a.v. Afas Profit en PowerBI gelezen. En ik merkte dat veel mensen het alsnog lastig vinden. We hebben daarom een andere oplossing bedacht.
Misschien wil je ernaar kijken en uitproberen? Ik hoor graag wat je ervan vindt.
Groet,
https://github.com/dutchgrit/afasodataserver
De grootste beperking is dat de rapporten dan alleen werken op het systeem waar je de tool draait. Je kan de live rapporten dus niet delen met collega’s zonder dat je ook de tool en het token zou gaan verspreiden. Dus ik snap dat het simpeler is, maar het sluit ook een hoop use-cases uit.
Daarbij gaat het in dit artikel juist om de integratie met Azure Storage omdat je dan grotere hoeveelheden data kan uitwisselen zonder meteen performance problemen te hebben. De data wordt immers maar 1x per x tijd uit Profit gehaald en kan daarna in principe door een ongelimiteerde hoeveelheid gebruikers ingezet worden.
Dus voor test en ontwikkeldoeleinden, of single user omgevingen zie ik inderdaad de toegevoegde waarde, maar voor grotere productie omgevingen is dit geen passende oplossing denk ik.
Bedankt voor je snelle reactie!
Nee hoor, de rapporten werken vervolgens overal, ook zonder de tool uiteraard. Althans.. PowerBI werkt namelijk (nagenoeg nooit) met live data! Het leest de data in en slaat deze in de pbix zelf op, en neemt desgewenst de ingelezen data mee naar de cloud / online powerbi. Zo kan iedereen ermee werken met de (op dat moment) ingelezen data, dus ongelimiteerd hoeveelheid gebruikers! Zelfs als de tool uit staat. Dat principe geldt overigens niet alleen voor deze odata-tool , ook deze blogpost, als mede de vorige post met de REST API’s: het is niet een échte live data verbinding die je legt, nooit niet… Je “laad” de gegevens in en je hebt daarna de verbinding niet meer nodig voor rapportages/delen van de data. De desktop tool is ideaal als je 1x per week of maand ‘verse’ data nodig hebt, maar het sluit niet ongelimiteerd hoeveelheid gebruikers of het delen van het rapport met data uit.
Naast de desktop applicatie hebben we (uiteraard) ook de hosted variant van de odata server. Daar kan je dan idd wel de data (bijvoorbeeld) elk uur laten verversen. Wat overigens in Online PowerBI (nadat je je rapport geupload hebt) gewoon door PowerBI zelf kan. Dus zonder aparte Automation te hoeven instellen en zonder de Blob Storage tussenstap, die imho ook geen performance probleem oplost (wat voor performance probleem denk je aan dan?). Linksom/rechtsom moet de data van Afas naar PowerBI zien te komen. Afas moet data spuwen (dat moet ie ook in deze Blob Storage oplossing) en PowerBI moet de data inlezen (of het nu via blob, of via rest api, of via odata komt). Again : PowerBI zelf werkt (live) met zijn EIGEN database, ongeacht welke bron data wij ook aanbieden.
Als je leuk vindt, geef ik je graag een proef versie van de hosted versie van de tool anders ook! Ben oprecht benieuwd naar je mening namelijk. Dank voor je reactie weer!
Het performance probleem zit hem in de snelheid van de getconnector bij het ophalen van grote hoeveelheden data. Als ik de rapporten met de webconnector (zie een van de eerste posts) direct aan Profit koppel kan iedere gebruiker zelf de data “live” refreshen en dat levert bij grotere groepen gebruikers een performance probleem op. Het refreshen van de data vanaf de blob kan zo ongeveer ongelimiteerd. Daarbij voorkomt het dat de rapport gebruiker zelf toegang krijgt tot de volledige AFAS connector zoals wel het geval is in een aantal van mijn eerdere posts.
De hosted variant komt het dichtste bij wat ik ook in deze post zelf omschrijf en zal dus vast een prima oplossing zijn als je een meer out of the box oplossing wil, maar dan heb je voor de ontwikkeling van de tool een afhankelijkheid van jullie en of andere ontwikkelaars van de tool. Dat hoeft op zichzelf natuurlijk geen probleem te zijn maar is een keuze.
Ik zeg dus ook niet per definitie dat jullie oplossing geen goede is. Alle drie de opties hebben zo hun plek, wij prefereren alleen sterk de Azure integratie omdat we die kennis ook in huis hebben. Voor ons vinden de integraties met onze andere systemen ook via Azure plaats en daarmee is alles mooi gebundeld en centraal geregeld.
Hoi Herman,
Ik heb het volgende probleem en ben benieuwd of ik dit kan oplossen met Azure of dat je een andere oplossing weet wellicht. Ik schiet de actuele openstaande posten doormiddel van een afas getconnector naar Power BI. Deze ververs ik elke nacht. Op deze manier weet ik elke dag mijn actuele debiteurenstand. Echter kan ik de debiteurenstand van gisteren niet meer terugzien, aangezien dat niet meer actueel is.
Ik hoop dat je de situatie snapt en of dit ook met Azure kan worden opgelost.
De netste oplossing zou zijn om de exports uit AFAS in een database te zetten, je kan dan het meest efficient het verloop ophalen, dit is wel even een puzzelwerkje om dat netjes te doen (ooit komt daar nog wel een post over).
Het alternatief is om de json iedere dag te overschrijven, maar te “appenden” (dus aansluiten op de bestaande file door de nieuwe regels toe te voegen). Het is dan wel zaak dat je een kolom mee neemt met de huidige datum en uiteindelijk ooit een stukje script toevoegt dat de oude regels verwijderd (afhankelijk van hoeveel historie je wil bewaren).
Ik heb het momenteel erg druk, maar ik zal eens kijken of ik hier een dezer dagen een kort script voor kan publiceren.
Hoi Herman, Top Bedankt! Je zou me er erg mee helpen!
Hi Herman!
I’m trying to do what your code in Powershell does however in Python and using Azure Functions in VS, in addition I want to be able to use PANDAS to process these json files.
I don’t quite understand what you do in part of block number 3, and if you need to do it in Python too.
Following the steps of your tutorial, working perfectly in Powershell, what I need is to understand what should I include from libraries to make it work in Functions.
Which part do you mean with block number 3?