HR data to BI
BambooHR to Power BI integration: employees, job history and pay costs in one currency
With an integration between BambooHR and Power BI, employees, job and department history and salaries from BambooHR sit in a fixed Power BI model, with pay costs from all countries converted to one currency. KoppelHet reads the history from BambooHR's tables, so Power BI also shows who worked in which department last quarter.
What the integration does
Organisations that use BambooHR across several countries want to see payroll costs per department in euros next to the budget in Power BI. Salaries in BambooHR are in different currencies, and an export only gives the current job, not last quarter's.
- BambooHR administration creates an API key for a user with access to the required fields and to datasets.
- Every day KoppelHet asks which employees have changed since the previous run.
- For those employees all rows of the job and compensation tables are fetched again and replaced.
- Salaries are converted to euros at that day's rate, and the rate is stored.
- KoppelHet refreshes the Power BI model; department heads see only their own department.
What to watch for with BambooHR and Power BI
- BambooHR returns changed table data per employee, but any change in the record brings along all of that employee's table rows, including unchanged ones. KoppelHet therefore replaces the whole history per employee instead of appending rows, so Power BI does not count duplicate job rows.
- The employee number in BambooHR is an editable field and not the internal key. Anyone whose number is changed later would appear in Power BI as a new employee. KoppelHet links on BambooHR's internal ID and shows the employee number only as a label.
- BambooHR has replaced the old way of requesting company reports with datasets and saved reports, with different field names. A Power BI report on the old column names then breaks. KoppelHet works with the new datasets, and BambooHR's mapping table helps convert existing reports.
- In the old report the salary amount and currency were in one column; in the datasets they are two fields. KoppelHet fetches them separately and converts to one currency using the exchange rates BambooHR itself supplies, so pay costs per country can be added up in Power BI.
- BambooHR only provides the latest exchange rate, not past rates. KoppelHet stores the rate per load date, so last month's pay costs in Power BI do not change if the dollar rises tomorrow.
- What the API returns depends on the permissions of the user behind the API key, and datasets require extra permissions. Without them the integration is refused, whatever fields are requested. KoppelHet checks the permissions during setup, so the first report is not left empty.
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
Can we not just load the BambooHR reports into Power BI?
A saved report can be retrieved through the API, but it shows today's position. History and pay costs in one currency need the table history.
Which exchange rate do you use?
By default the rate BambooHR itself supplies, recorded per load date. If finance wants the rate from its own finance package, we use that.
What about the GDPR?
You are the controller and KoppelHet is the processor, under a data processing agreement. Salaries per person are only visible to roles allowed to see them; managers see totals.
Technical details for IT
For those who want to know how it works under the hood: APIs, fields and sources used.
- BambooHR REST API with API key. Changed employees through /api/v1/employees/changed and table rows through /api/v1/employees/changed/tables/{table} with since (jobInfo, compensation); a change in any field returns all table rows of that employee. Current data through Get Data from Dataset (v2) with filter, orderBy and pageSize up to 1,000; the old Get Company Report has been replaced, the Legacy Report Field Map translates old field names (payRate becomes compensationPayRate and compensationPayRateCurrencyCode).
- Exchange rates through /api/v1/meta/currency-conversions with baseCurrency; only the latest snapshot with lastUpdated and nextUpdate. Power BI semantic model in import mode, refreshed through the Power BI REST API, with row-level security on department.
Which fields are transferred
The key fields. The full mapping is agreed per customer.
| Field in BambooHR | Field in Power BI | Notes |
|---|---|---|
id (eeid) | Medewerkersleutel | Not the editable employee number |
employeeNumber | Personeelsnummer (weergave) | |
jobInfo (date, jobTitle, department, division, location, reportsTo) | Tabel functiehistorie | |
compensation (startDate, rate, currency) | Tabel salarishistorie | |
Currency conversion rates (baseCurrency EUR) | Tabel wisselkoersen per laaddag | |
hireDate, terminationDate en employmentStatus | Tabel dienstverbanden en verloop |
Related
Ashby → BambooHR
Ashby to BambooHR integration: hire, changed start date and employment status in BambooHR
BambooHR → 4PS Construct
BambooHR to 4PS Construct integration: employees from BambooHR in the 4PS company of their location
BambooHR → Active Directory
BambooHR to Active Directory integration: accounts in your own domain from HR
BambooHR → ADP iHCM
BambooHR to ADP iHCM integration: salary, bonuses and commissions to Dutch payroll