AFAS is een van de grote jongens als het gaat om ERP en boekhoud systemen in Nederland. Veel bedrijven en organisaties maken er al gebruik van en ook Conspect is sinds 2023 een klant van AFAS. Na de implementatie kwam intern al snel de vraag of wij zelf de data kunnen ontsluiten om zo onze eigen rapportages ontwikkelen.
Als consultancy bedrijf heeft het management van Conspect met name behoefte aan informatie over de prestaties van haar consultants, zoals bijvoorbeeld de geboekte uren, projecten en omzetcijfers. Hoewel AFAS zelf al een verscheidenheid aan dashboards en rapportages aanbiedt kan je, door data te ontsluiten, zelf dashboarding en rapportages ontwikkelen in Power BI of andere rapportage tools. Als lead data engineer bij het ‘AFAS rapportage’ project van Conspect wil ik jullie graag meenemen in de evolutie van ons platform en de uitdagingen en voordelen die we ervaren met het gebruik van ons eigen datawarehouse en rapportages.
1). Proof of concept – Direct van AFAS naar Power BI
Als eerste een korte toelichting over de werking van data ontsluiten uit AFAS; om data uit AFAS te ontsluiten maak je gebruik van drie technieken; GetConnectoren, gegevensverzamelingen en een app connector. Een gegevensverzameling is een set aan elkaar gerelateerde gegevens uit de onderliggende AFAS database. Deze kan je op meerdere wijzen exporteren of gebruiken. Een GetConnector is een manier om gegevens uit de AFAS Profit omgeving te ontsluiten, samen met een appconnector en GetConnector kan de gekoppelde gegevensverzameling van buitenaf benaderd worden via een REST API call, de data krijg je dan aangeleverd in een JSON formaat. Dit wordt door Power BI weer geaccepteerd en geopend in Power Query in tabelvorm.
De eerste versie van wat intern de ‘AFAS rapportages’ genoemd worden was een proof of concept waarbij data direct uit AFAS in Power BI werd geladen met gebruik van Power Query. In figuur 1 zie je de eerste versie van het datamodel, en ook hoe dit nog niet voldoet aan de best practices van een goed gemodelleerd stermodel.
Al snel werd duidelijk dat de data zoals we deze uit AFAS ontsloten niet direct geschikt was om rapportages mee te maken. Enkele observaties waren;
- Zoals benoemd, de data zou eigenlijk nog verder opgeschoond en verbeterd moeten worden in een tussenstap om deze goed bruikbaar te maken.
- De mogelijkheden om het ETL proces inzichtelijk en flexibel te houden zijn beperkt in Power Query.
- Door te data apart op te slaan kan deze ook in andere toepassingen of verschillende rapportages gebruikt worden.
Onze redenering was dat bovenstaande problemen gemakkelijk opgelost konden worden met een tussenlaag, de volgende stap was dan ook om een datawarehouse in te richten waar de data uit AFAS kan landen en bewerkt worden voor verder gebruik.
2). Doorontwikkeling – Datawarehouse
Hoewel voor simpele use cases het direct laden van AFAS naar Power BI prima is waren de wensen bij Conspect verder gegroeid en moest het bestaande datamodel herzien worden. De eerste stap was het opnieuw modelleren van de data, samen met de stakeholders vaststellen welke requirements er zijn en welke data nodig is om hier invulling aan te geven. In figuur 2 is het vernieuwde datamodel te zien.
Tegelijkertijd met het uitwerken van het datamodel is nagedacht over met welke technieken wij het datawarehouse willen ontwikkelen. De keuze voor Azure was logisch gezien de brede adoptie in de markt en de uitgebreide ervaring die onze consultants met dit platform hebben. Daarna was de keuze tussen een meer traditioneel datawarehouse op basis van een SQL server of Databricks. Er is gekozen voor een combinatie van Azure Data Factory als ETL tooling samen met SQL databases voor een staging en warehouse laag. De belangrijkste reden is dat de datavolume relatief klein is, en om complexiteit tegen te gaan een enkele SQL server voldoende is. In figuur 3 is de datastroom te zien, aangevuld met een toelichting.
De data wordt wekelijks in het weekend opgehaald uit AFAS door middel van de API, de data wordt dan opgeslagen op een Azure storage omgeving als JSON files. Dit minimaliseert de belasting op AFAS als productiesysteem. De data in de JSON files wordt dan opgeschoond, genormaliseerd en in tabelvorm ingeladen in de eerste SQL database. Vanuit deze staging laag wordt de business logica toegepast en de data opgeslagen in het stermodel op de tweede SQL database. Op dit stermodel haakt Power BI in en hoeft deze data niet verder getransformeerd te worden met Power Query.
Het gebruik van dit model geeft ook de mogelijkheid verschillende soorten databronnen aan te sluiten als hier een behoefte uit ontstaat. Of het ontwikkelen van nieuwe stermodellen op dezelfde data voor andere doelgroepen.
3). Tweede versie – Rapportages
Nadat het stermodel was ontworpen en de back-end infrastructuur was ingericht kon worden gewerkt aan de rapportages, deze worden ontwikkeld in Power BI Desktop en via de cloud service beschikbaar gesteld aan de eindgebruikers. Tijdens de proof of concept lag de focus op een algemeen management dashboard waarmee het managementteam inzage verkreeg in de prestaties van het bedrijf op basis van omzet en gewerkte uren.
Voor de nieuwe versie is gekozen om de rapportages uit het proof of concept opnieuw te bouwen, dit omdat de datakwaliteit ook aanzienlijk verbeterd is. De bestaande rapportages zijn ook uitgebreid met forecasting over de verwachte uren en omzet voor de rest van het lopende jaar. In het dashboard kan op meerdere perspectieven naar de data gekeken worden, zoals bijvoorbeeld de expertise/domein van de medewerker, individuele medewerkers, opdrachten en tijdsperioden.
Het belangrijkste voordeel wat de nieuwe infrastructuur en rapportages Conspect geven is meer flexibiliteit als het gaat om stuurinformatie. De nieuwe rapportages geven meer inzicht in de data en de verbeterde datakwaliteit maakt het mogelijk om trends te voorspellen. Daarnaast kan, afhankelijk van de stakeholder ook nieuwe rapportages gemaakt worden gericht op individuele consultants om hun eigen KPI’s te meten of HR voor bijvoorbeeld personeelsinformatie en trends.
4). Tot slot
Ik hoop dat ik hiermee een goed beeld hebt gegeven van de mogelijkheden van Power BI in combinatie met AFAS en op welke wijze wij bij Conspect zelf onze datawarehousing en rapportages inrichten. Ben je benieuwd naar de mogelijkheden die Conspect voor jouw organisatie kan bieden? Of wil je meer weten hoe je jouw eigen data uit AFAS effectief kan inzetten voor stuurinformatie met Power BI? Neem dan contact op!
Pim Schilder
Data Engineer bij Conspect
4.1). AFAS Help Center
Zie de volgende artikelen voor GetConnectoren en gegevensverzamelingen in AFAS;
- GetConnector: GetConnector Bouwen en testen – AFAS Help Center
- Gegevensverzamelingen: Gegevensverzamelingen – AFAS Help Center
- Aanroepen GetConnector: Connector aanroepen via Profit Rest Service – AFAS Help Center
