ADP to Power BI, done for you
We scope the extraction route before anything else — the marketplace connector gated behind DataCloud Enhanced Insights, the Workforce Now REST APIs with their certificate-based OAuth, or scheduled report exports — then model worker, department and time data into a star schema with DAX for headcount, turnover, labor cost and overtime.
Representative dashboard — sample data
Why these projects stall
Search for a way to report on ADP in Power BI and every result on the first page is somebody selling you a pipe — driver vendors, integration platforms, a marketplace listing. ADP's own connector is real, but it is attached to a DataCloud Enhanced Insights subscription, which is a separate paid tier. If you are not on it, the trail goes cold fast, which is why the community threads about connecting Power BI to ADP tend to end without an answer.
None of that is the hard part of the project, though. Once the route is settled, the work is the same discipline any people dataset needs: agree what a termination counts as, decide whether overtime is measured in hours or dollars, pin the department and location hierarchy to something that survives a reorganisation, and secure the result so a manager sees their own crew and nobody else's pay. That is what turns a monthly export into a report leadership will actually argue from.
What we build from ADP
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
Each one sits upstream of the first chart, and each one is where an in-house attempt usually runs out of road.
We check whether the marketplace connector and its DataCloud tier is worth buying for your metric list, or whether the API and export routes get you there without a new subscription line. That comparison happens before you commit hours.
ADP's APIs expect a client certificate alongside the OAuth credentials, and Power Query cannot present one. We put a small service or scheduled job in the middle, running under credentials your administrator issues and can revoke.
RUN, Workforce Now, Vantage HCM and a TotalSource PEO arrangement expose different things. Confirming which one you are on, and what your agreement actually permits, is the first question we ask rather than the surprise in week two.
Biweekly and semimonthly periods do not line up with months, so a naive monthly labor cost is wrong twice a year. We map periods to a proper date table and allocate across boundaries, then show the rule on the page.
Voluntary, involuntary, annualized, rolling twelve months, regretted: all different numbers. We settle the definitions with HR and operations, then write each one as an explicit DAX measure instead of a single ambiguous percentage.
Scheduled refresh sized to your headcount, row-level security by department and location, small-population cells suppressed, and any failure fixed by us out of your banked hours rather than by whoever built it last.
Through one of four routes, and choosing between them is most of the early value. Power BI Desktop ships no first-party ADP connector, so something has to publish the data. ADP's own answer is the Microsoft Power BI Connector for ADP DataCloud Enhanced Insights (opens in new tab), whose listing states plainly that it requires a subscription to DataCloud Enhanced Insights. That is a real product and a fair option if you already hold the tier. If you do not, buying an analytics subscription to feed a dashboard you are building anyway deserves an honest comparison rather than a shrug.
The second route is the ADP developer platform, where Workforce Now exposes REST endpoints for workers, time and payroll-adjacent data. The catch is authentication: ADP issues a client certificate to your organization and expects it presented alongside the OAuth credentials on every call. Power Query has no data source authentication option that does that, which is exactly where most in-house attempts stop. The fix is unglamorous and durable — a small service, function or scheduled job you own makes the authenticated call and lands the result somewhere Power BI can read on a schedule.
| Route | What it is | When we use it |
|---|---|---|
| ADP Marketplace Power BI connector | ADP's listed connector for DataCloud Enhanced Insights, which its listing states requires a subscription to that tier | You already hold Enhanced Insights and its packaged metric set covers what leadership asks for |
| Workforce Now REST APIs | The documented endpoints on ADP's developer platform, authorised with OAuth 2.0 plus a client certificate issued to you | You need fields the packaged analytics does not carry, or you would rather not add a subscription tier |
| Scheduled custom report exports | Reports built in Workforce Now's own reporting tools, delivered on a schedule as files to storage you control | The fastest honest start — no developer registration, and your HR team owns the field list |
| Third-party drivers and integration platforms | Commercial ODBC drivers and iPaaS tools that wrap the same ADP APIs behind a licence | Rarely our first suggestion — it adds a renewal and a vendor between you and your own data |
| An existing warehouse copy | ADP extracts already landing in SQL Server, Snowflake, Databricks or Microsoft Fabric through an internal pipeline | Always our first question — faster to refresh, cheaper to model, and already joinable to finance data |
Whichever route wins, one Power BI-specific trap is worth naming early. Building request URLs dynamically in Power Query, by concatenating a date or a location code straight into the address, produces a query the service cannot classify and will refuse to refresh on a schedule — even though it worked in Desktop. Keep the base address constant and pass everything variable as relative path and query parameters, which the Power Query Web connector (opens in new tab) supports directly. Where the landing zone is on your network, the on-premises data gateway (opens in new tab) carries the refresh.
Because ADP is not one system. It is a family of platforms sold to different sizes of employer, and what you can extract, at what effort, follows from which one your company bought. Getting this wrong is the single most common reason an ADP reporting estimate turns out to be optimistic, so we confirm it in scoping and write the route into the scope document before hours start.
| Product | Who it is sold to | What it means for extraction |
|---|---|---|
| RUN Powered by ADP | Small businesses running payroll with a light HR layer | Usually scheduled report exports rather than an API project — and that is normally the proportionate answer |
| ADP Workforce Now | Mid-sized employers, and the most common case we are asked about | Both a reporting layer and a documented API surface, so the route is a genuine choice rather than a constraint |
| ADP Vantage HCM | Large enterprises with a broader HCM footprint | Typically agreed data feeds and scheduled extracts; we confirm what your contract covers before scoping |
| ADP Enterprise HR | Long-standing large-employer implementations | Often already landing into a warehouse, which usually makes that copy the better source |
| ADP TotalSource (PEO) | Employers in a co-employment arrangement | Your data sits inside the PEO environment, so available fields and access depend on the service agreement |
| Multi-country ADP payroll | Employers running several countries through ADP services | Consolidation work first — currencies, calendars and country files aligned before a single measure is written |
Two modelling details do most of the damage if they are left implicit. The first is worker type: employees, part-timers, seasonal staff and contractors all sit in the population, and almost every company counts them differently. We encode that as a flag on the worker dimension so the rule is visible and changeable. The second is the department and location hierarchy. ADP carries home department, location and job codes, and those codes get renamed and re-parented over time, so we build the hierarchy as a dimension with its own history rather than reading today's labels back over last year's numbers. The wider craft behind both is on our Power BI data modeling and DAX page, and the structure follows Microsoft's star schema guidance (opens in new tab).
The set below is what HR directors and the operations leaders who own the labor line consistently ask for, and each one hides a definition somebody has to agree with before the number means anything. We write them as documented DAX measures over the star schema and a real date table, then validate every total against a report your team already runs inside ADP.
| Measure | How it is defined | Why it is written this way |
|---|---|---|
| Headcount and FTE | Active workers as of the selected date, with FTE reported next to the body count | Point-in-time, so a month-end figure never depends on when the refresh happened to run |
| Headcount movement | Month-end headcount split into hires, terminations and net change by department and location | A flat total can hide heavy churn underneath, and movement is the part leadership can act on |
| Annualized voluntary turnover | Voluntary terminations in the period over average headcount, scaled to a full year | Annualizing puts a month, a quarter and a year on one comparable axis |
| Rolling 12-month turnover | Terminations across the trailing twelve months over average headcount for the same window | Smooths seasonal exits so the trend shows direction rather than one heavy month |
| New-hire retention | Share of each hire cohort still active at 90 days, six months and one year | Cohorted by hire date, so a bad hiring quarter stays attached to the quarter that caused it |
| Labor cost by department and location | Paid labor from ADP allocated to calendar periods and rolled up the department and location hierarchy | Pay periods do not align to months, so the allocation rule has to be explicit and visible |
| Overtime hours and overtime share | Overtime and premium hours, and overtime as a percentage of total hours, by department, location and week | The share is the number that travels — it is comparable across sites of different sizes |
| Absence and PTO balances | Absence occurrences and hours, plus outstanding accrued balances where ADP carries them | Absence is usually the leading indicator that shows up before the overtime bill does |
Overtime by location is normally the measure that changes a conversation. Arithmetically it is trivial. Organisationally it is uncomfortable, because an overtime percentage that reads as acceptable company-wide often resolves into two or three sites carrying nearly all of it, and those sites are frequently the same ones carrying the turnover. Putting both measures on one page, against one department dimension, is the entire point of building the model rather than exporting two reports.
ADP's reporting and its DataCloud analytics are capable inside their own boundaries, and for questions about ADP data in ADP's shape they are the right tool. The requests that reach us are the ones that cross a boundary: into finance and the general ledger, into scheduling or point-of-sale volumes, or across several years in which departments were renamed and merged.
| What you are trying to do | Reporting and analytics inside ADP | Custom Power BI model |
|---|---|---|
| Operational HR and payroll lists | Its core strength — live, in context, already secured | Possible, but not a reason on its own to build a model |
| Labor cost against the GL and budget | Budget and actuals usually live in accounting, outside ADP | Labor modelled beside ledger and plan data as conformed facts |
| Overtime against demand or volume | The demand signal is in scheduling, POS or your ops system | Hours and volume on one date table, so the ratio is a measure |
| Turnover your way | Fixed to the definitions the packaged report or tier chose | Every variant an explicit, documented measure you can change |
| Multi-year trend across reorganisations | Constrained by today's department and location codes | A historised hierarchy keeps comparisons honest through a reorg |
| Where the logic and access live | Inside the vendor's platform, and behind its subscription tiers | In a model file in your own tenant, which you own outright |
The practical answer is usually both. Keep ADP for running payroll and HR operations, and build the model for the numbers that decide staffing levels, overtime approvals and where a retention budget goes next quarter.
Carefully, because this is the most sensitive dataset most companies own. We pull the narrowest field list the agreed metrics require and leave identifiers out of the model where a measure does not need them. Visibility is then handled with row-level security in the Power BI service (opens in new tab), driven from a mapping of viewers to departments and locations, so a site manager sees their own crew, an HR business partner sees their assigned groups and leadership sees everything — all from one published report. Our guide to Power BI row-level security setup walks through how those roles are built and tested.
Small-population views get deliberate treatment too: where a filter would leave a cell describing a handful of employees, we suppress or aggregate it rather than publish an average that identifies someone. Refresh is scheduled to match how the data actually moves — daily is right for most HR reporting, with a tighter cadence around each pay run — inside Microsoft's documented data refresh limits (opens in new tab). Where years of time and pay history have accumulated, incremental refresh (opens in new tab) keeps each cycle to a recent window.
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, overtime and labor cost by department, from scheduled exports or an existing warehouse copy, usually fits a Starter block. Growth is the realistic tier when the API route with certificate authentication has to be stood up, when several ADP companies or countries need consolidating, or when definitions have to be negotiated between HR, operations and finance before development starts.
Most tasks ship within 3–5 business days of scope approval, and you get a scope and hours estimate before anything begins. 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 ADP with enough history to trend and no dedicated analytics engineer — typically the point where somebody exports the same reports every month and leadership has quietly stopped trusting the reconciliation. HR directors, operations leaders at multi-site businesses, and the finance partners who own the labor line are the people who usually get in touch, often after a meeting where two turnover numbers disagreed.
Generic payroll cost reporting is deliberately not this page. Gross-to-net, deduction and tax categories, pay-register reconciliation and labor cost by cost center are provider-agnostic work that we cover on the Power BI payroll dashboard page, which builds from any provider's registers. This page is the ADP-specific half: getting the data out, and the HR and labor metrics that follow. If your HR system of record is Workday rather than ADP, the extraction story is completely different and the Workday Power BI dashboard page covers it. For Paylocity customers, see the Paylocity Power BI dashboard page. Multi-site operators watching labor as a share of sales will also want the industry view on Power BI for restaurants or Power BI for franchise owners, where overtime sits next to revenue. 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 ADP build usually fits inside one Flex block, and hours deduct only for work you approve. See Flex pricing →
How it works
We establish which ADP product you are on, whether DataCloud Enhanced Insights is already in your contract, and which fields the metrics need — then agree worker-type and turnover definitions and reply with a scope and hours estimate before any work begins.
A senior US-based engineer stands up the extraction, historises the department and location hierarchy, maps pay periods onto a real date table, builds the star schema and writes the DAX for headcount, turnover, retention, labor cost and overtime — validating totals against reports you already run in ADP.
Published to your workspace with scheduled refresh, row-level security so each manager sees only their departments, small-population cells suppressed, and documentation you keep. We maintain it from your banked hours.
Representative perspective
We were quoted an analytics subscription to get our own numbers into Power BI. What we actually needed was someone to tell us which of the four routes fit our contract.HR director, multi-site services company
Representative perspective
Overtime looked fine at company level. Split by location against one department hierarchy, two sites were carrying most of it, and the same two led turnover.Operations leader, regional healthcare group
Tell us which ADP product you run, whether DataCloud is in your contract, and what leadership asks for each month. We'll confirm the extraction route, scope the task, and tell you the hours before anything starts — no project minimums, no obligation.
FAQ
Yes. The connector ADP lists on its own marketplace is tied to a DataCloud Enhanced Insights subscription, which is a paid upsell many employers do not hold. The alternatives are the Workforce Now REST APIs on the ADP developer portal and scheduled custom report exports out of Workforce Now, and both are supported routes we build against.
ADP authenticates API clients with OAuth 2.0 plus a client certificate issued to your organization, so the caller has to present that certificate on every request. Power Query has no data source authentication option that does this, so the call is made by a small service or scheduled job you own, and Power BI reads what it lands.
It decides the extraction route, so it is the first thing we confirm. Workforce Now is the common mid-market case and has both a reporting layer and a documented API surface. RUN is smaller and usually points at scheduled report exports. Vantage, Enterprise HR and a TotalSource PEO arrangement each depend on what your service agreement covers.
Only the people you decide should. We pull the narrowest field list the metrics need, leave identifiers out of the model where a measure does not require them, and drive row-level security from a mapping of viewers to departments and locations. Where a filtered cell would describe a handful of employees, we suppress or aggregate it instead.
No. This page is about getting data out of ADP specifically and reporting the HR and labor side of it: headcount, turnover, retention, overtime and labor cost by department. Gross-to-net, deduction and tax categories and pay-register reconciliation are provider-agnostic work that belongs on our payroll dashboard page. Plenty of clients end up with both, sharing one worker dimension.
No. We are an independent US-based Power BI consultancy with no ADP partnership to claim and no connector of our own to license you. We build using documented, supported extraction routes under credentials your administrator provisions and owns, and everything we produce is published into your own Power BI workspace.
If the question is pay rather than people — gross-to-net, deduction categories, labor cost by cost center, registers from any provider — the Power BI payroll dashboard page is the better starting point, and it is provider-agnostic by design. Where the HR system of record is Workday instead, the Workday Power BI dashboard page covers a different extraction story built on custom reports enabled as web services. Employers who moved to a single-database platform will find the equivalent write-up for Paycom — where no connector exists and scheduled Report Center delivery carries the data — on our Paycom Power BI dashboard page. Where the people system of record is an SMB HRIS rather than a payroll platform, headcount, turnover, time-off liability and hiring pipeline are covered on our BambooHR Power BI dashboard page. Securing any of it is covered step by step in Power BI row-level security setup, and the modelling underneath is described in data modeling and DAX. If a scheduled refresh is already failing on an existing ADP export, start with fixing a failed Power BI scheduled refresh. Field-service and trade businesses watching overtime against job profitability will find that angle on Power BI for HVAC and field 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.