BambooHR to Power BI, done for you
Headcount and turnover the way your board defines them, time-off liability, hiring pipeline and new-hire retention — pulled from the BambooHR API, landed in a store you own, and modelled by a senior US-based engineer instead of exported to a spreadsheet at the end of every month.
Representative dashboard — sample data
Why these builds stall
BambooHR is usually the cleanest system a growing company owns. The roster is accurate, time off is approved in one place, and the hiring pipeline is not living in three inboxes. What it is not is an analytics platform — and the moment someone asks for turnover by department over three years, or what the accrued time-off balance is worth in dollars, the answer starts with an export.
There is no BambooHR connector in Power BI Desktop, so a pipeline has to be built: a key generated by an administrator, endpoints chosen deliberately, throttling respected, and somewhere for the history to live — because the API answers what is true today, and every people metric worth reporting is a question about change over time.
What we build from BambooHR
Buy a block of prepaid Flex hours and, if you’re unhappy within the first 4 hours, we refund you in full — no questions asked. Hours are deducted only for work you’ve reviewed and approved, there’s no project minimum, and your banked hours never expire.
What the build covers
Every one of them sits upstream of the first visual, and every one is where an in-house attempt tends to stop.
API keys are created from inside your own account and carry the permissions of the user who holds them. We ask for one issued to a dedicated reporting user with read access to the fields in scope, so the extraction can never see more than you intended and can be revoked in a click.
BambooHR publishes no call ceiling but states plainly that requests can be throttled and that integrations should expect a 503. The extraction pages through datasets, pulls only what changed since the last run, and backs off rather than hammering a live HR system during business hours.
Dated job, compensation and employment-status rows are pulled once and retained in a store you own, so a department that was renamed last year still reconciles and a three-year headcount trend does not depend on what the roster happens to say this morning.
Reporting relationships and departments are flattened into a proper hierarchy with levels and a path, so a division rolls up cleanly, span of control is a measure rather than a manual count, and one slicer drives every page of the report.
Is a contractor headcount? Does an internal transfer count as a termination? Is regrettable attrition tracked? Every people metric hides a decision, so we settle them with you in scoping and write them as documented DAX rather than burying them in a query.
Scheduled refresh in your own tenant, row-level security so a department head sees their own team, salary detail kept off shared pages unless you ask for it, and small-group views aggregated so a single row never identifies one person’s pay.
Through the documented BambooHR API, because Power BI Desktop ships no first-party BambooHR connector and there is no supported way to point it at the underlying database. Requests go to a company-domain address — BambooHR’s technical overview (opens in new tab) states that requests are made to a URL beginning with your own company domain — and authenticate with an API key sent over HTTP Basic authentication, the key acting as the username with any string as the password. OAuth is available for applications registered in the developer portal, which matters if you would rather a published app held the grant than a key.
The key is the governance conversation worth having first. Keys are generated by a user from inside their own account, under the user menu, and they carry that user’s permissions with them. A key minted from an HR administrator’s profile can read everything that administrator can read, which is almost always more than a dashboard needs. We ask for one issued to a dedicated reporting user with a permission set scoped to the fields in the build, and you can revoke it at any time without touching a line of our code.
The second design constraint is throttling. BambooHR does not publish a request ceiling; it states that requests can be throttled if they are deemed too frequent and that implementations should always be ready for a 503 response, and that repeated use of an unknown key will get access disabled for a period. That rules out the naive pattern of one call per employee per metric on every refresh, and points the design at bulk dataset reads, change-tracking pulls and a store of your own.
| Route | What it gives you | When we use it |
|---|---|---|
| Dataset endpoints | The wide employee field catalog — several hundred discoverable fields under fully qualified names such as jobInformationJobTitle, jobInformationDepartment and employmentStatus — returned as paged JSON rows | The default for the worker dimension. Fields are sourced from the dataset field list rather than guessed, because dataset names differ from the shorter names the employee endpoints use |
| Employee table endpoints | Dated rows from the tabular tables — job information, compensation, employment status — each row carrying its own effective dates | Anything historical: turnover, promotion and comp-change rates, tenure and point-in-time headcount all depend on these rather than on current values |
| Change tracking endpoints | Employees, and table rows, changed since an ISO 8601 timestamp you supply | Keeping nightly refreshes small. A change to any field, or to the employment status, job info or compensation tables, surfaces the employee here |
| Time-off endpoints | Requests within a required start and end date window, filterable by status, employee or type, plus policies and balances | Usage, absence and accrued-balance reporting — and the liability calculation once balances are joined to a rate |
| Applicant tracking endpoints | Job and application summaries and detail, including status | Pipeline, time-to-fill and source-effectiveness reporting, joined to the same department hierarchy the headcount pages use |
| Saved custom reports | The output of a report your team already maintains, by report ID, in JSON or CSV | Where a field your team needs is easiest to reach through an existing report. Note that report IDs are company-specific and standard reports are not exposed this way |
| Webhooks | A POST to an endpoint you host when watched fields change, carrying employee IDs, the changed field names and the field values, batched when several change at once | Near-real-time roster sync where a nightly pull is not enough. Fields in custom tables are not currently supported, so they stay on the scheduled path |
One detail catches teams who built their own pull a couple of years ago: BambooHR has been moving this surface forward. The older custom-report request endpoint is now marked deprecated in favour of the dataset endpoints, and the older saved-report endpoint points at a newer report-by-ID route. An extract written against the deprecated path still runs today, but it is the kind of dependency that fails on a Monday morning with no warning, which is why we check the current documentation for every endpoint in scope before we quote the work.
Because the API answers questions about now, and a people dashboard asks questions about change. BambooHR keeps dated rows for job information, compensation and employment status, which is exactly what makes real history possible — but only if something captures them. Pull them once into a landing store, and a headcount figure as of last March stops being a screenshot somebody saved.
The practical case is the throttling guidance plus retention. A store you own — Azure SQL, a Microsoft Fabric lakehouse, or files in your own storage account — lets the extraction page politely through datasets overnight, pull only changed employees on subsequent runs, and keep what BambooHR has no obligation to keep in the shape you need. The Power BI refresh then reads locally and finishes in seconds, and no HR system is being queried while people are trying to use it.
| BambooHR field or object | What it carries | Role in the model and the visual it drives |
|---|---|---|
| Employee record (dataset fields) | Employee ID and employee number, name, work email, hire date, employment status, job title, department, division, location and supervisor | Worker dimension — the slicers and the axis on every page; kept slowly changing so a transfer does not rewrite last year |
| Employment status table | Dated status rows: active, leave, terminated, with effective dates and status reason | The fact behind headcount, turnover and tenure — a point-in-time headcount card and a monthly turnover column chart |
| Job information table | Dated title, department, division, location and reporting-line rows | Movement fact — promotion and transfer rates, plus the dated department key that keeps history comparable through a reorganization |
| Compensation table | Dated pay rate rows with rate, type, exempt flag, change reason and effective dates | Compensation fact — pay-band and comp-change visuals on secured pages, and the rate that turns a time-off balance into a dollar figure |
| Reporting line (supervisor) | The supervisor reference on each worker | Flattened org hierarchy — a decomposition tree and the span-of-control measure by manager and level |
| Time-off requests | Request dates, type, status and amount, within the date window the endpoint requires | Absence fact — usage by type and month, an approved-versus-taken view, and a coverage calendar by team |
| Time-off policies and balances | Accrual policy assignment and current balance per employee and type | Balance snapshot — a liability card once joined to compensation, and a stacked balance-by-department bar |
| Jobs and applications (ATS) | Requisition and application records with status, department and dates | Recruiting facts — a pipeline funnel by stage, time-to-fill distribution, and offer-acceptance by source |
| Custom fields and custom tables | Whatever your company added — certifications, badge IDs, cost centers, engagement survey results | Extra dimension attributes or a small fact of their own, mapped explicitly in scoping rather than inferred from a column name |
The modelling follows Microsoft’s star schema modeling guidance (opens in new tab), and the general craft is covered on our star schema guide. The step that causes most rework if it is skipped is the field inventory. BambooHR installs diverge quickly — two companies on the same plan rarely use the same custom fields for the same purpose — so we read the dataset field list against your actual account and agree what each field means before a single query is written.
The set below is what HR leaders consistently want from BambooHR and end up assembling by hand each month. Each one embeds a definition someone has to agree with before the number means anything, so we write them as documented DAX measures over the star schema and a proper date table, then validate every total against a report your team already runs inside BambooHR.
| Measure | How it is defined | Why it is written this way |
|---|---|---|
| Headcount and FTE | Active workers as of the selected date from dated status rows, with part-time weighted to full-time equivalents | Point-in-time, so a month-end figure never depends on the hour the refresh happened to run |
| Turnover rate | Terminations over average headcount for the period, split voluntary and involuntary, annualized | An average denominator and an annualized rate make a 12-person team and a 200-person one comparable on the same chart |
| New-hire retention | Share of a hire cohort still active at 90, 180 and 365 days, by department and hiring manager | Separates a hiring problem from an onboarding one — a headline turnover number hides both |
| Tenure distribution | Active workers bucketed by service length, with median tenure by department | Shows where institutional knowledge actually sits, and which teams are one resignation away from a gap |
| Time-off liability | Accrued balances by type, valued at the current rate from the compensation table | The number finance asks for at close and HR has to build by hand — it needs both systems of record joined at one grain |
| Time-off usage and coverage | Days taken by type and month against entitlement, plus concurrent absence by team and week | Turns a who-is-out calendar into a planning input, and flags the teams quietly accruing an unusable balance |
| Time-to-fill and time-to-hire | Days from requisition open to offer accepted, and from application to hire, by department and source | Two different questions that get conflated — one measures the requisition, the other the candidate experience |
| Pipeline conversion | Applications progressing between stages, by role and source, with offer-acceptance rate | Shows where candidates are actually lost, which is rarely the stage recruiters assume |
| Span of control | Direct and total reports per manager from the flattened reporting hierarchy, by level | A structural read on the org chart that BambooHR holds the data for but does not chart over time |
| Headcount plan versus actual | Approved plan modelled as a second fact against the same department and calendar | Puts the recruiting pipeline and the budget on one page, which is the meeting these dashboards are usually built for |
Where your account includes employee satisfaction or engagement survey results, those can be exported and trended in the same model — scored by department and against the turnover measures rather than read once and filed. We keep small-group results aggregated so a response is never traceable to an individual.
BambooHR ships a genuine reporting layer — standard reports, saved custom reports and in-product charts — and for questions inside BambooHR, in the shape BambooHR keeps the data, it is the right tool and we will say so. The requests that reach us cross a boundary: into payroll or the general ledger, across several years of restructured departments, or onto a screen in front of people who do not have a BambooHR login and should not be given one.
| What you are trying to do | Reporting inside BambooHR | Custom Power BI model |
|---|---|---|
| Check a roster or run a standard HR report | Its core strength — live, in context, already permissioned | Possible, but not a reason to build a model |
| Turnover on your own definition | Fixed report logic; voluntary and regrettable splits usually end up in a spreadsheet | DAX measures over dated status rows, documented and reused everywhere |
| Three years of trend through reorganizations | Anchored to the current department structure | Retained dated rows keep prior structures comparable |
| HR joined to payroll, finance or the GL | Out of scope — the other systems are not in BambooHR | Conformed dimensions across sources, one calendar, one department key |
| Time-off balances valued in dollars | Balances and pay rates are reported separately | One measure, because both facts hang off the same worker dimension |
| One pack for HR, finance and the board | Needs each viewer to hold a BambooHR login and know where to look | One published report, secured by department, in tools they already use |
| Where the logic lives | Inside the vendor’s reporting layer | In a model file in your own tenant, which you own outright |
The practical answer is usually both. Keep BambooHR reporting for running HR day to day, and build the model for the numbers that decide hiring plans, retention spend and what the board sees each quarter.
The model is published to your Power BI workspace and refreshed on a cadence that matches how the data moves — for most employers a nightly pull is ample, since a roster does not change hourly — inside Microsoft’s documented data refresh limits (opens in new tab). Where years of history have accumulated, we configure incremental refresh (opens in new tab) so each cycle re-reads only a recent window.
Security is not optional on HR data. Visibility is handled with row-level security in the Power BI service (opens in new tab), driven from a mapping of viewers to departments and evaluated against the flattened reporting hierarchy, so a department head sees their own team, a division lead sees their branch, and HR leadership sees everything. Our guide to Power BI row-level security setup walks through the same configuration. Compensation detail is kept on separately secured pages, and where a team is small enough that a single row identifies one person, we aggregate or suppress rather than publish it.
Prepaid Flex hours that never expire: Starter 20 hours at $125/hr ($2,500), Growth 40 hours at $115/hr ($4,600), Power 80 hours at $100/hr ($8,000), with hourly work from $100/hour on the largest block. A first build covering headcount, turnover and time off from an approved API key usually fits a Starter block. Adding recruiting pipeline, a headcount plan to compare against, or a join to payroll or finance data typically needs Growth, because agreeing definitions across HR and finance takes discussion as well as development.
Most tasks ship within 3–5 business days of scope approval, and you get a scope and hours estimate before anything starts. Our guide to Power BI dashboard development cost covers what moves the number, how prepaid Flex hours work explains the model in full, and custom Power BI dashboard development describes the wider service this build sits inside.
Employers running BambooHR with enough history to trend and no dedicated analytics engineer — typically between fifty and a few hundred people, at the point where someone exports the same reports every month and two departments quote different headcount figures in the same meeting. HR directors, people ops managers and the finance partners who own the headcount line are the people who usually get in touch.
Writing back to BambooHR is out of scope: this is a read-only reporting build. Gross-to-net payroll registers stay where they belong — if you want labor cost, employer burden and overtime at the pay-run grain, our Power BI payroll dashboard page covers that at any provider, and the vendor-specific routes live on the ADP Power BI dashboard and Paylocity Power BI dashboard pages. Enterprise HR estates on a separate HCM tenant are a different build again, described on our Workday Power BI dashboard page. If recruiting throughput rather than internal headcount is the point — submittals, fill rate and spread across client requisitions — that is on our Power BI for staffing agencies page. Contact is form-only — send your request and we’ll reply with a scope estimate.
business days, typical
hours, never expire
own everything
A first BambooHR build usually fits inside one Flex block, and hours deduct only for work you approve. See Flex pricing →
How it works
We agree what headcount, turnover and time off mean in your company, read the dataset field list against your actual account, and list the endpoints in scope. Your administrator generates an API key for a dedicated reporting user, and we reply with a scope and hours estimate before any work begins.
A senior US-based engineer builds the scheduled extraction with paging, change tracking and back-off, lands employee, table, time-off and applicant records in a store you own, flattens the reporting hierarchy, builds the star schema and date table, and writes the DAX — validating every total against a BambooHR report your team already runs.
Published to your workspace with scheduled or incremental refresh, row-level security so each manager sees only their own departments, compensation kept on secured pages, and documentation you keep. We maintain it from your banked hours.
Representative perspective
Our turnover number was rebuilt from scratch every quarter and never quite matched the last one. Having the definition written down and calculated the same way every time was worth more than the charts.HR director, mid-market professional services firm
Representative perspective
We had a script that pulled the roster and worked fine until it needed three years of history. Knowing which endpoints keep dated rows, and where to put them, was most of the value.Operations lead, growing SaaS company
Tell us how many people you have in BambooHR, which reports your team exports today, and what leadership asks for each quarter. We’ll confirm the extraction route, scope the task, and tell you the hours before anything starts — no project minimums, no obligation.
FAQ
Power BI Desktop ships no first-party BambooHR connector, so the data leaves through the BambooHR API. Requests go to a company-domain address and authenticate with an API key sent over HTTP Basic authentication, with the key as the username and any string as the password. A scheduled job pulls the endpoints you need and lands them where Power BI can refresh from.
Four groups do most of the work: the dataset endpoints for the wide employee field catalog, the employee table endpoints for dated job, compensation and employment-status rows, the time-off endpoints for requests, policies and balances, and the applicant tracking endpoints for job and application summaries and detail.
Because BambooHR documents that requests can be throttled if it deems them too frequent, and tells integrators to be ready for a 503 response. A landing store lets the extraction pull once, page politely, keep the history that a current-state report cannot give you, and turn every Power BI refresh into a fast local read.
Yes, and that is usually the reason to build. Turnover is written as DAX over dated employment-status rows, so voluntary and involuntary split the way your board expects, the denominator is average headcount for the window rather than a month-end snapshot, and the rate annualizes consistently across departments of different sizes.
No. BambooHR reporting stays the right tool for questions inside BambooHR, in the shape BambooHR keeps the data. The model earns its place on the questions that cross a boundary: HR data joined to payroll or finance, several years of history through reorganizations, and a report seen by people who hold no BambooHR login.
No. We are an independent US-based Power BI consultancy with no BambooHR partnership to claim and no connector subscription to sell. We build against the documented API using a key your administrator generates and can revoke, and everything we produce is published into your own Power BI workspace.
Book a free demo
Tell us the task. We scope it, you approve, and a senior consultant delivers — typically in 3–5 business days. Prepaid hours that never expire.
The fastest way to reach us is the form — tell us the task and we'll reply within one business day.