HR data to BI
Loket to Power BI integration: payroll costs, FTE and absence per employer and department, including retroactive corrections
With an integration between Loket and Power BI, payroll results, FTE, employments and absence from Loket sit per employer and department in a fixed data model that Power BI refreshes after every payroll run. Retroactive corrections are included in the period they belong to, so the figures in Power BI match Loket.
What the integration does
Employers and payroll bureaus that process salaries in Loket often line up Loket exports in Excel for management information on payroll costs, headcount and absence. A retroactive correction changes the figures of an earlier month, and the export from then no longer matches.
- Someone with rights on the employers gives the integration read access in Loket once.
- After a payroll run KoppelHet fetches the payroll results, including earlier periods changed by the run.
- Every day KoppelHet fetches employments, working hours and absence per employer.
- After fetching, KoppelHet starts the refresh of the Power BI model.
- Managers see only their own employer or department in Power BI.
What to watch for with Loket and Power BI
- A payroll run in Loket can contain payroll results for an earlier period, for example a correction in July for January. Loket can give the current state per period. After every run KoppelHet fetches the changed earlier periods again, so January's payroll costs in Power BI are right after the correction.
- With the payroll results per employment Loket also supplies department, job, gross pay, employer costs and FTE. Payroll costs and headcount per department therefore come from the same source as the payslip, without the model having to calculate them.
- When requesting payroll results Loket can filter on pay components or a fixed set of components. KoppelHet only requests the components the report needs, so for example net pay and deductions per person do not end up in Power BI.
- For on-call workers and min-max contracts Loket can record an average number of hours per week that does not affect the payroll calculation. The model uses that average alongside the contract hours, so flexible employees do not count as zero hours in headcount.
- Loket records for each absence progress the percentage for which someone is unfit for work. KoppelHet calculates absence pro rata to that percentage, so someone who is half sick does not count as full absence in Power BI.
- Employee numbers in Loket are per employer. In the model the key is employer plus number, so a group with several employers in one Loket environment can report per employer and for the whole.
- Loket versions the shape of its responses per resource, and an old version expires about six months after a new one. KoppelHet requests a fixed version and switches deliberately, so a column in the Power BI model does not change name or meaning unnoticed.
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 load the Loket export into Power BI?
An export is a snapshot. After a retroactive correction it is no longer right. The integration fetches the current state per period and keeps history.
Does this work for a payroll bureau with many employers?
Yes. Per employer we record who sees the reporting. A separate report per end client can come later as an extension.
What about the GDPR?
You are the controller and KoppelHet is the processor. Citizen service number, addresses and bank details do not go into the model.
Technical details for IT
For those who want to know how it works under the hood: APIs, fields and sources used.
- Loket through REST API v2 with OAuth 2.0 (authorization code with refresh token) and read rights only. Payroll results through GetPayrollPeriodResultsByPayrollAdministrationIdAndPayrollPeriodId (current state per period, with payrollComponents or payrollComponentSet) and GetPayrollResultsByPayrollRunId; employments and working hours per employer (averageHoursPerWeek, isOnCallEmployment); absence through GetCalendarAbsencesByEmployerId and GetAbsenceByAbsenceId (incapacityPercentage). Resource version in the Accept header; paging with pageNumber and pageSize.
- Power BI semantic model in import mode on a database KoppelHet fills, refreshed through the Power BI REST API after every payroll run and daily. Shared capacity - eight scheduled refreshes a day; Premium, PPU or Fabric - 48. Row-level security per employer or department, viewers only.
Which fields are transferred
The key fields. The full mapping is agreed per customer.
| Field in Loket | Field in Power BI | Notes |
|---|---|---|
Werkgever + personeelsnummer | Medewerkersleutel | Employee numbers are per employer |
Loonresultaten per periode (bruto, werkgeverslasten, fte) | Tabel loonkosten per periode | |
Afdeling en functie bij het loonresultaat | Dimensies afdeling en functie | |
Dienstverband (begin, einde, oproepkracht) | Tabel dienstverbanden | |
Werkuren (gemiddeld per week, werkpatroon) | Tabel uren | |
Verzuim met percentage arbeidsongeschikt | Tabel verzuim | Partial absence counts pro rata |
Related
ADP Workforce Now → Loket
ADP Workforce Now to Loket integration: hires, pay, hours and bonuses from ADP to the Dutch payroll run
AlexisHR → Loket
AlexisHR to Loket integration: employees of the Dutch company from AlexisHR into payroll
BambooHR → Loket
BambooHR to Loket integration: HR changes from BambooHR for Dutch payroll
Bullhorn → Loket
Bullhorn to Loket integration: placement as a concept employee in the right payroll administration