Smarter staffing through automatic insights into unusual logged hours
GVB
AFAS Payroll Power BI dashboard
We set up BI models, access rights and data refreshes for HR and finance, as we did for GVB to make overtime and sickness absence visible by project.
GVB tracks productivity, completeness of time records, overtime and sickness absence by project
GVB’s Rail Equipment department wanted insight into hours logged outside expected patterns. We introduced an anomaly detection model, giving GVB a view of productivity, completeness of time records, overtime and sickness absence for each project, so it can plan more effectively.
Why do HR and finance report different payroll costs every month?
HR tracks FTEs and absence in Profit, while finance looks at the payroll journal entry in the general ledger. The monthly report is an export that someone puts together. A single model built on AFAS’s BI models gives both teams the same figures, with role-based access.
An AFAS salary dashboard relies on BI models, an authorisation model and scheduled refreshes
Payroll costs, absence and staffing levels
We start with AFAS templates, such as Payroll Costs, Absence and Workforce Planning, and extend the model with your own structure and data sources.
Permissions from Profit
Filter-based authorisation by employee or organisational unit is carried over to Power BI through an authorisation model, providing row-level security for each user.
Hours and absence by team
Absence and staffing levels alongside hours and projects, as we did for GVB when we made overtime and sickness absence visible by project.
Refresh and management
The Power BI refresh runs after the scheduled task in Profit, with a system user for the token and an owner for each report.
We agree who can see which salary data before we start building
-
Questions and access rights
What questions HR, finance and managers need answered, and who can see which salary and absence data.
-
Model and access control
We publish the BI models with the access control model, build an initial report and test it for each role.
-
Rollout and management
Data refresh is aligned with Profit, the app goes live for each target group, and staffing data or other sources follow.
Aren’t the dashboards included with AFAS enough?
A good fit if
You use AFAS Profit HRM and Payroll, want to see payroll costs, absence and staffing alongside budgets or planning in one place, and need managers to see only their own teams.
Not a good fit if
The AFAS templates already answer your questions and you don’t need to add other data sources. In that case, you can publish them yourself, perhaps with the optimisation session AFAS offers for this.
What determines the scope
How many BI models you use, whether filter permissions in Profit are already set up correctly, which additional sources you need, such as planning or budgets, and whether your team will take over management.
Is this secure enough for salary data?
Only if filter permissions in Profit are correct. If a filter is accidentally set to full access, someone will see too much in Power BI as well. That’s why we check permissions for each role before rollout.
The AFAS template, your own model or a GetConnector?
The AFAS template
Choose this if the included dashboards, such as Sickness Absence or Labour Costs, answer your questions and your team can publish and share them with some Power BI knowledge.
Your own model built on the BI models
Choose this if HR and finance want to report together, using their own definitions, other data sources and role-based permissions. We recommend this as soon as more than one department uses the figures to guide decisions.
A GetConnector
Choose this if you need fresh data more often than once every ten hours. AFAS warns that this can cause performance issues and may be subject to the API’s fair-use policy.
We were already working with GVB on hours and absence data, closely linked to planning
Data Science Lab has worked with data and AI since 2016 and is a Microsoft Partner for Fabric. For GVB, we made anomalies in logged hours visible so each project can be planned more effectively.
Your AFAS environment and who can see what: that’s where our first conversation starts
Stef Stoelwinder
Business DevelopmentStef is your first point of contact. In our first conversation, we discuss your AFAS environment, the questions from HR and finance, and access rights.
E-mail info@datasciencelab.nl
Phone +31 (0)20 3636 724
Which HR and payroll figures should be included in Power BI?
Tell us which parts of AFAS you use and who needs to see the figures. We’ll reply by email or arrange an initial conversation.
Message sent
Thanks for your message. We’ll be in touch as soon as possible.
Frequently asked questions
AFAS Payroll is the payroll component of AFAS Profit, alongside HRM. To use it with Power BI, you work with the BI models in Profit: activate the feature, unblock the BI odata connector, create a user token and schedule a task. Then copy one of the supplied BI models, publish it and open the template (.pbit) in Power BI Desktop.
You can read how we build a dashboard around a decision on our Power BI dashboards page.
AFAS provides template dashboards, each with its own BI model, including payroll costs, absence, workforce planning, loans and wage garnishments, the Balanced Labour Market Act, and HR data. The last of these covers HR metrics, FTE and headcount, employee movement, contracts, absence, leave, work anniversaries and vacancies.
That is a good starting point for an AFAS payroll or HR dashboard. AFAS does note, however, that it does not provide support for setting up Power BI itself.
With filter authorisation. Since Profit 7, filter authorisation from Profit carries over to Power BI as row-level security (RLS). Alongside the BI model, you need an authorisation model and each user’s Power BI UPN in the Authorisation tool. Without filter authorisation, anyone with access to the dashboard can see all the data it contains.
Pay attention to Power BI permissions too: RLS only applies to viewers. Anyone with an Admin, Member or Contributor role in the workspace can see everything.
Profit makes the data available in the AFAS Online data layer at most twice a day, with at least ten hours between updates. Power BI can retrieve it eight times a day. You therefore need to align the two schedules and show the time of the last refresh on the dashboard.
For absence data, the Vullen HRM tabellen tbv Profit BI task also runs every night. Changes to access rights only take effect after the next refresh.
It depends on the model. According to AFAS, the absence dashboard goes back no more than two years, and you cannot change that. The HR data BI model has filters on its fact tables; if you remove them to see more history, the limit is three years.
If you want to track longer-term trends, such as workforce levels or payroll costs, you can periodically save a snapshot in your own database and combine it with the current BI models.
A GetConnector calculates the data when you call it. According to AFAS, that can mean long waits for complex datasets involving many tables. BI models calculate the data in advance, making them better suited to reporting on large datasets.
AFAS recommends a GetConnector only if you need the data more often than once every ten hours. It also warns of performance issues and the API’s fair-use policy.
Through the payroll journal entry, which records payroll processing in the financial accounts. In the model, you link payroll costs from Payroll to those entries by period and general ledger account, and by cost centre if you record it. HR and finance can then see the same total and identify the source of any difference.
If Profit Payroll posts to AFAS SB, read about the AFAS SB Power BI dashboard too. For budgets, margins and cash flow, see the financial dashboard.
When the AFAS templates answer your questions and no one needs to combine them with other sources. Or when filter authorisation in Profit is not yet set up properly: start there, and with data governance, because Power BI inherits the access rights as they are.
If your question concerns Power BI more broadly, read about our Power BI consultant or business intelligence. If a simpler solution would work, we’ll say so.
Is it about more than payroll and HR, or a different software package?
- Power BI dashboardsHow we build a dashboard around the decision it needs to support.
- Financial dashboardRevenue, margin, cash flow and budget versus actuals in one model, alongside your payroll costs.
- AFAS SB Power BI dashboardReporting on accounting data in AFAS SB, including historical data and multiple sets of accounts.