Paylocity to Power BI, done for you
Headcount, turnover, total labor cost including employer burden, and overtime by cost center — extracted from the Paylocity REST API v2, landed in a store you own, and modelled by a senior US-based engineer instead of exported to a workbook after every pay run.
Representative dashboard — sample data
Why these builds stall
Paylocity is unusual among the platforms we connect: HR, payroll and time run on one system rather than three that have to be stitched together afterwards. The worker, the check, the hours and the labor level allocation already share keys, which means a single semantic model can put turnover next to labor cost without anyone reconciling two vendors’ employee identifiers first.
What stops teams is the plumbing, not the modelling. There is no Paylocity connector in Power BI Desktop, so someone has to request API credentials, authenticate on every run, work within a rate ceiling, and decide where the extracted pay statements are going to live — because the report needs several years of history and the API is designed to answer questions about a company, a year and a check date, one call at a time.
What we build from Paylocity
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.
Paylocity issues OAuth 2.0 client credentials and a sandbox company on request, and the request comes from you, not from us. We tell your administrator exactly which endpoints to ask for so approval is one conversation rather than three.
Bearer tokens are short-lived and the API carries a published ceiling of 40,000 calls per hour. The extraction re-authenticates each run, batches by company and pay date rather than looping per employee, and backs off instead of hammering.
Pay statements are pulled once and retained in a store you own, so a cost center that was reorganised last year still reconciles, and a five-year trend does not depend on re-reading the API every morning.
Your Paylocity cost center and labor level structure is flattened into numbered levels plus a path, so a division rolls up cleanly, allocations tie to the general ledger, and one slicer drives every page.
Gross pay is not labor cost. Employer taxes and employer-paid benefits are separated from employee deductions so the burden is visible, and overtime premium is split from base so the real price of an hour is on the page.
Scheduled refresh timed to your pay calendar, row-level security by cost center on a dataset nobody should browse freely, and failures fixed by us out of your banked hours rather than landing on whoever built it.
Through the Paylocity REST API, because Power BI Desktop ships no first-party Paylocity connector and there is no supported way to point it at the platform’s database. Access is requested from Paylocity by the client rather than bought from a third party: on approval they issue OAuth 2.0 client credentials and a sandbox company to build against. Every call then carries a bearer token obtained from Paylocity’s identity server, and company-scoped paths are keyed by your Paylocity company code rather than a generated identifier, which matters the moment an employer runs more than one company.
Two constraints shape the design more than anything else. Tokens are short-lived, so the extraction re-authenticates on each run instead of storing one forever. And the API carries a published ceiling of 40,000 calls per hour, with guidance to cache, request only what you need, and retry with exponential back-off — which rules out the naive pattern of one call per employee per check date across five years of history on every refresh.
| Route | What it gives you | When we use it |
|---|---|---|
| Pay statement endpoints (Pay Details API) | Check-level pay statements: check date, pay period and check type, summary totals, and line-item earnings, deductions, taxes and benefits with per-check and year-to-date amounts | The default for cost, overtime and burden reporting — Paylocity documents these endpoints for clients storing payroll statement data in a data warehouse or reporting solution |
| Employee endpoints plus webhooks | The worker master: status, hire and termination dates, job title, pay type and labor level assignment | Maintaining a synchronised worker dimension, with webhooks notifying changes so a nightly job is not re-reading the entire roster |
| Cost center / labor level endpoints | The company’s labor level structure and cost center codes | Building the dimension every allocation, chargeback and manager view depends on, instead of typing the hierarchy into a spreadsheet |
| Scheduled report exports | Standard, saved or Report Writer output exported on a schedule to storage you control | Where API access is not approved in time, or a field your team needs exists only inside an established report |
| An existing warehouse copy | Paylocity already landed into Azure SQL, Microsoft Fabric, Snowflake or Databricks by an internal pipeline | Always our first question — it is faster to refresh, cheaper to model, and trivially joinable to the general ledger |
One Power BI-specific trap is worth naming before it costs a week. Pay statement URLs embed the company, the year and the check date in the path, so it is natural to build the address by string concatenation in Power Query. That produces what the service classifies as a dynamic data source, and it will refuse to refresh it on a schedule even though it worked perfectly in Desktop. The supported form is a constant base address with everything variable passed as a relative path and query parameters, which the Power Query Web connector (opens in new tab) handles directly — and the more durable answer is to move the paging out of Power Query altogether.
Because the API is built to answer operational questions and a dashboard asks historical ones. Paylocity’s own documentation points at this pattern: the pay statement endpoints are described as valuable for clients who want to store payroll statement data in a data warehouse or use it to power integrations and reporting solutions. That is the vendor telling you the extraction is a one-way copy into your own store, not a live query behind a report.
The practical case is the same three constraints in sequence. Token lifetime means every refresh re-authenticates. The rate ceiling means a full history re-read is wasteful at best and throttled at worst. And retention means the past has to be captured rather than re-derived — when a labor level is renamed or a department is folded into another, the API tells you what is true now, and a five-year cost trend needs what was true then. A modest landing store, whether Azure SQL, a Fabric lakehouse or files in your own storage account, solves all three and turns the Power BI refresh into a fast local read.
| Paylocity concept | What it carries | Role in the model |
|---|---|---|
| Employee record | Status, hire and termination dates, job title, pay type, FTE status and labor level assignment | Worker dimension, kept slowly changing on cost center, job and pay type so a transfer does not rewrite history |
| Pay statement header | Check date, pay period start and end, process identifier and check type | The pay run grain — and the keys that make check date and pay period two different, deliberate date relationships |
| Earnings lines | Regular, overtime, bonus, commission and paid-leave codes with hours and amounts | Earnings fact — the source of gross pay, overtime premium and hours-based rates |
| Deduction, tax and benefit lines | Employee and employer portions of taxes, benefit plans and other deductions | Deduction fact, with the employer portions flagged so burden is a measure rather than an estimate |
| Cost centers and labor levels | The company’s labor level codes and the structure above them | Conformed cost center dimension, flattened to levels and a path for roll-up, allocation and security |
| Time and attendance hours | Worked, scheduled and premium hours where the time module is in use | Hours fact — what makes overtime rate and scheduled-versus-worked answerable rather than inferred |
| Year-to-date totals | Running employee totals returned alongside each check | Reconciliation checkpoints, never summed — the single most common cause of a doubled number in a payroll model |
Both patterns follow Microsoft’s star schema modeling guidance (opens in new tab), and the general craft is covered on our star schema guide. The detail that causes most rework is the earnings and deduction code list. Every employer has its own, several codes usually mean the same thing to finance, and one or two behave differently from how they are named. We map them with your payroll administrator in scoping and keep the mapping as data, so a new code next January is a row to add rather than a query to reopen.
The set below is what payroll, HR and finance consistently ask Paylocity for and end up assembling by hand. 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 pay calendar, then validate every total against a report your team already runs inside Paylocity.
| Measure | How it is defined | Why it is written this way |
|---|---|---|
| Total labor cost | Gross pay plus employer taxes and employer-paid benefits, by cost center and period | Gross alone understates a headcount decision by the entire burden, which is exactly the part finance is asked about |
| Labor cost per FTE | Total labor cost over average full-time equivalents for the same window | Makes cost comparable between a growing cost center and a shrinking one instead of tracking headcount by accident |
| Overtime hours and premium | Overtime hours, and the premium portion of overtime dollars separated from base pay | Splitting premium from base shows what overtime actually costs above what those hours would have cost at straight time |
| Overtime rate | Overtime hours as a share of total hours worked, by cost center, week and job | A rate travels across sites of different sizes; raw overtime hours only ever flag the biggest department |
| Pay run variance | Gross and total cost per cost center this run against the last, with the largest movers ranked | Catches a miscoded earning or an unplanned payout in the days before it reaches the ledger, not at month-end |
| Headcount and FTE | Active workers and full-time equivalents as of the selected date, by cost center and job | Point-in-time, so a month-end figure never depends on the hour the refresh happened to run |
| Turnover | Terminations over average headcount for the period, split voluntary and involuntary, annualized | Sits on the same worker dimension as the cost measures — the whole point of a single unified source system |
| Deduction and benefit take-up | Participation and employer contribution by plan, by cost center and eligibility group | Benefit spend is usually the second largest line and is almost never trended anywhere near the pay register |
Overtime by cost center is usually the measure that changes a conversation. Arithmetically it is trivial; organisationally it is uncomfortable, because a company-wide overtime rate that looks tolerable frequently resolves into two or three cost centers carrying nearly all of it. The discipline that makes it defensible — one date table, conformed dimensions, measures rather than calculated columns — is the same craft described on our Power BI data modeling and DAX page.
Paylocity ships a real reporting layer — standard reports, saved reports, Report Writer output and in-product dashboards — and for questions inside the platform, in Paylocity’s own shape of the data, it is the right tool. The requests that reach us are the ones that cross a boundary: into the general ledger and the budget, across several years of restructured cost centers, or onto a screen in front of people who do not have a Paylocity login and should not be given one.
| What you are trying to do | Reporting inside Paylocity | Custom Power BI model |
|---|---|---|
| Run a cycle and check a register | Its core strength — live, in context, already secured | Possible, but not a reason to build a model |
| Multi-year cost trend across restructures | Anchored to the current labor level structure | Retained extracts keep prior structures comparable through a reorganisation |
| Labor cost against the GL and the budget | Plan and actual accounting data live outside the platform | Budget modelled as a second fact against the same cost center and calendar |
| Overtime read against scheduled hours | Available per report, harder to hold beside cost on one page | Hours and pay joined at one grain, so rate and dollars move together |
| One pack across HR, finance and operations | Needs each viewer to hold a Paylocity login and know where to look | One published report, secured by cost center, 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 Paylocity reporting for running payroll, and build the model for the numbers that decide staffing plans, overtime policy and where next year’s labor budget goes.
The model is published to your Power BI workspace and refreshed on a cadence that matches how the data actually moves — for most employers that means a daily worker refresh and a pay statement pull timed to the pay calendar, since nothing changes in a closed pay period — inside Microsoft’s documented data refresh limits (opens in new tab). Where several years of pay statements have accumulated, we configure incremental refresh (opens in new tab) so each cycle re-reads only a recent window.
Security is not an afterthought on a pay dataset. Visibility is handled with row-level security in the Power BI service (opens in new tab), driven from a mapping of viewers to cost centers and evaluated against the flattened labor level hierarchy, so a manager sees their own department, a regional lead sees their branch of the structure and HR and finance leadership see everything. Our guide to Power BI row-level security setup walks through the same configuration step by step. Small-population views get the same care: where a cost center is small enough that a single row identifies a person’s pay, we aggregate or suppress rather than publishing 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 labor cost, overtime and headcount by cost center from an approved API connection usually fits a Starter block. Adding budget comparison, several companies under one employer, or a backfill of years of pay statements typically needs Growth, because agreeing earning-code and burden definitions across payroll 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 Paylocity with enough pay history to trend and no dedicated analytics engineer — typically the point where someone exports the same reports every pay period and two departments quote different labor cost figures in the same meeting. Payroll managers, HR directors and the finance partners who own the labor line are the people who usually get in touch.
Writing back to Paylocity is out of scope: this is a read-only reporting build, and the pay entry side of the API is a payroll processing path rather than a reporting source. If your payroll runs somewhere else, or you want one model spanning registers from several providers, our Power BI payroll dashboard page covers that at the pay-register level regardless of vendor. Enterprise HR estates where the people data lives in a separate HCM tenant are a different build again — that is on our Workday Power BI dashboard page, where headcount, turnover and requisitions come from RaaS reports and payroll cost is deliberately excluded. 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 Paylocity build usually fits inside one Flex block, and hours deduct only for work you approve. See Flex pricing →
How it works
Your administrator requests API access from Paylocity for the endpoints we list; meanwhile we agree earning and deduction code mappings, what counts in burden, how turnover is defined and how far back usable history goes, then reply with a scope and hours estimate before any work begins.
A senior US-based engineer builds the scheduled extraction inside your rate budget, lands pay statements and worker records in a store you own, flattens the labor level hierarchy, builds the star schema and pay calendar, and writes the DAX — validating every total against a Paylocity report your team already runs.
Published to your workspace with scheduled or incremental refresh, row-level security so each manager sees only their cost centers, and documentation you keep. We maintain it from your banked hours.
Representative perspective
Our overtime number looked manageable at company level. Split by cost center, two sites were carrying most of it, and one of them had never come up in a budget meeting.Payroll manager, multi-site healthcare employer
Representative perspective
Our own pull worked on the laptop and died on the first scheduled refresh. Getting the credentials, the paging and somewhere to keep the history right was most of the value.Finance director, mid-market manufacturer
Tell us how many companies you run in Paylocity, which reports your team exports today, and what leadership asks for each pay period. 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 ships no first-party Paylocity connector, so the data leaves through the Paylocity REST API. Your administrator requests access from Paylocity, who issue OAuth 2.0 client credentials and a sandbox company. A scheduled job then authenticates, pulls the employee, pay statement and cost center endpoints, and lands the results somewhere Power BI can refresh from.
The pay statement endpoints, documented as the Pay Details API. They return check-level records with check date, pay period and check type, summary totals for hours, earnings and deductions, and a line-item breakdown of earnings, deductions, taxes and benefits carrying both per-check and year-to-date amounts.
Three practical reasons. Bearer tokens expire after an hour, the API carries a published ceiling of 40,000 calls per hour, and pay statement URLs embed the company, year and check date in the path, which the Power BI service treats as a dynamic data source and will not refresh on a schedule. A small store you own removes all three.
On Paylocity, yes, and that is the advantage worth using. HR, payroll and time sit on one platform, so the worker record, the pay statement and the labor level allocation already share keys. Headcount and turnover can sit beside labor cost and overtime without first reconciling two vendors’ employee identifiers.
Yes. We build a cost center dimension from your Paylocity labor level structure, flatten it into levels and a path, then drive row-level security from a mapping of viewers to cost centers. A department manager sees their own labor cost, leadership sees the company, and individual pay detail stays off summary pages unless you ask for it.
No. We are an independent US-based Power BI consultancy with no Paylocity partnership to claim and no prebuilt connector to sell. We build against the documented REST API using credentials your administrator requests and owns, and everything we produce is published into your own Power BI workspace.
If your payroll does not run in Paylocity, or you need one model spanning registers from more than one provider, the Power BI payroll dashboard page covers that at the pay-register level and is the better starting point. Where the people questions matter more than the pay ones and HR lives in its own tenant, read the Workday Power BI dashboard build instead. Paycom shares Paylocity’s single-platform advantage but not its documented API route, so the extraction leans on scheduled Report Center delivery — the Paycom Power BI dashboard page covers that build. Finance teams reading labor cost against budget and the rest of the P&L will want Power BI for finance, the modelling craft underneath all of it is described in data modeling and DAX, and custom Power BI dashboard development covers the wider service. Every task is scoped before work begins from your prepaid block of senior-led hours — no project minimums, no change-order surprises, and hours are deducted only for work you approve.
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.