HR data to BI
AFAS to Power BI integration: HR data from Profit in Power BI, with history and its own security
With an integration between AFAS Profit and Power BI, employees, employments, job history and absence from AFAS sit in a fixed Power BI model, per employer and across the whole organisation. AFAS itself supplies Power BI templates based on BI models; KoppelHet comes in when you want to keep history per reference date, combine AFAS with other sources or let managers see only their own department.
What the integration does
Anyone using AFAS has the absence dashboard that AFAS provides, but for a report on headcount and turnover a controller combines AFAS lists with the budget from another package. That is manual work every month, and last year's position cannot be seen because the lists only show the current state.
- AFAS refreshes the BI layer at the scheduled time. KoppelHet then reads only the tables the report needs.
- Every month KoppelHet records a snapshot of who worked in which job and department on that date.
- The data is combined with the budget in one model, with the employer as a fixed dimension.
- After loading, KoppelHet starts the refresh of the Power BI model.
- Managers open the report and see only the employees of their own department.
What to watch for with AFAS Profit and Power BI
- AFAS fills the BI layer with a scheduled task at most twice a day. A Power BI refresh that starts earlier loads yesterday's position. KoppelHet only refreshes the Power BI model after AFAS has updated the BI layer.
- Without filter authorisation everyone with access to an AFAS dashboard sees all data in it, according to AFAS itself. KoppelHet builds the security per department in the Power BI model from the organisational units in AFAS, so a new department is protected straight away.
- AFAS recommends BI models for reporting and GetConnectors only when data must be refreshed more often than once every ten hours, at the risk of reaching the API's fair-use limit. KoppelHet therefore uses the BI models for the large tables and a narrow GetConnector only for what really needs to be more frequent.
- The AFAS templates show the current position. Anyone who wants to know how many people worked in a department last March needs their own history. KoppelHet keeps a monthly snapshot outside AFAS, so that question can still be answered after a reorganisation.
- One AFAS environment can contain several employers, with their own numbering per employer. In the model the employer is therefore part of the key, so figures per employer and for the group are both right.
- With the action to prepare a limited set of rows, AFAS loads at most 1,000 rows per table into the BI layer. KoppelHet first tests a new field on that small set, before the full refresh in Power BI includes it.
How we build and run it
- The integration runs on the KoppelHet platform, hosted in a data centre in the EU.
- Every sync and every error is visible in the functional log; on an error your administrator is notified.
- A change that cannot go through is not half-applied but queued for another attempt.
- KoppelHet is ISO 27001 certified. Data travels encrypted and stays on the platform no longer than needed.
- After go-live we manage the integration and adjust it when your process changes.
- The integration fits next to your existing integration layer. Only the fields in the design are transferred; the platform keeps no register of its own.
Implementation and price
This is not an app you switch on. We build the integration for your organisation on our platform, with building blocks we reuse on every project. So it is custom work that does not start from scratch.
A consultant prepares the intake and analyses both systems. We then record the mapping and exceptions field by field, test in the test or acceptance environment and go live at a moment you choose. Costs are a one-off amount for the setup and a monthly amount for platform, monitoring and support.
Frequently asked questions
Why not just use the AFAS Power BI templates?
For a single dashboard on the current position they are often enough. Choose custom work when you want to look back in time, combine AFAS with other sources or let security per department move with the organisation.
Can Power BI show new AFAS data more than twice a day?
Only for a small part of the data, through a dedicated GetConnector. AFAS advises against that for large volumes. For HR reporting a daily position is usually enough.
Do BSN and bank details end up in Power BI?
No. The BI model and the GetConnector contain only the fields the report needs. You are the controller and KoppelHet is the processor, under a data processing agreement.
Technical details for IT
For those who want to know how it works under the hood: APIs, fields and sources used.
- AFAS Profit is read through the OData connector on the BI layer (BI models), with a user token. Where needed this is supplemented with a dedicated GetConnector through the AFAS Profit REST API. An AFAS Power BI template (.pbit) asks for the OData feed URL and the token when opened.
- Power BI semantic model in import mode, refreshed through the Power BI REST API after the AFAS task. Row-level security with roles based on the organisational units; RLS only applies to viewers in the workspace.
Which fields are transferred
The key fields. The full mapping is agreed per customer.
| Field in AFAS Profit | Field in Power BI | Notes |
|---|---|---|
Werkgever en medewerkernummer | Medewerkersleutel | |
Dienstverband (begin, einde, soort, uren) | Tabel dienstverbanden | |
Functie en organisatorische eenheid per ingangsdatum | Tabel functiehistorie | |
Verzuimmeldingen | Tabel verzuim | |
Organisatorische eenheden met bovenliggende eenheid | Dimensie organisatie | Also used for security per department |
Stand op de laatste dag van de maand | Tabel momentopnamen | Built by KoppelHet, not from AFAS |
Related
ADP Workforce Now → AFAS Profit
ADP Workforce Now to AFAS integration: Dutch employees from the US HR system in AFAS Profit
AFAS Profit → 4PS Construct
AFAS to 4PS Construct integration: payroll journal per employer in the right 4PS company
AFAS Profit → Adobe Creative Cloud
AFAS to Adobe Creative Cloud integration: users and product profiles from AFAS Profit
AFAS Profit → AG5
AFAS to AG5 integration: employees, employments and absence from AFAS Profit in the skills matrix