Autotask PSA to Power BI, done for you
Zone-resolved extraction from the Autotask REST API — or SQL against your Autotask data warehouse — modelled into tickets, time entries and contracts, with DAX for contract margin, effective rate, technician utilization and SLA attainment. A model you own, not a monthly connector subscription.
Representative dashboard — sample data
Where these projects actually stall
Autotask PSA already records everything an MSP needs to know about its own economics. What it does not do is hand that record over in a shape a report can use. The REST API lives on a numbered zone that differs per tenant, returns paged results you have to follow, encodes half the fields you care about as picklist integers, and buries your user-defined fields in a nested array. None of that is difficult once you know it. All of it stops a first attempt cold.
So the real work is upstream of any visual: resolve the zone, authenticate an API-only user, page the history once and pull incrementally afterwards, pivot the user-defined fields, resolve picklist IDs to labels, and conform companies, resources and contracts into dimensions that mean the same thing across every fact table. Then — and only then — the DAX for margin and utilization is a day's work rather than a quarter's.
What we build from Autotask PSA
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 report page, and every one is where an in-house attempt tends to stop.
A dedicated API user with the API security level, its own secret, and an integration code identifying the caller. Reporting never runs on a technician's login, and the credential it does run on cannot write back into the PSA.
Autotask databases sit on numbered web-services zones and the base URL differs per tenant. We query the zone information endpoint for yours, so the connection is correct on the first refresh and stays correct after a migration.
User-defined fields arrive as name/value pairs inside each record and status, queue, priority and issue type arrive as integers. Both get turned into real, labelled columns you can slice by without a lookup sheet.
Recurring service, fixed price, time and materials, block hours and retainer contracts earn revenue in genuinely different ways. Margin is modelled per type rather than averaged into a single number that describes none of them.
The PSA records hours worked, not hours available. We build a resource-capacity table from your schedules and holiday calendar so utilization divides by something you can defend in a comp conversation.
Scheduled or incremental refresh inside your tenant, a gateway where a SQL warehouse copy sits behind your firewall, and any failure fixed by us out of banked hours rather than landing on whoever built it.
Your administrator creates an API-only user inside Autotask, assigns it the API user security level, and generates a username and secret. That user is also associated with an integration code — a tracking identifier for the application making the calls. Power Query sends the username, the secret and the integration code as headers on every request. There is no interactive sign-in, and nothing about the connection depends on a person who might leave.
Before any of that works you need the right address. Autotask tenants are distributed across numbered web-services zones, so the API base URL is specific to your database rather than shared across all customers. The zone information endpoint returns yours when queried with the API user name, and we resolve it at build time instead of pasting a zone from a forum thread. Requests are also paged, so a query returns a block of records plus a pointer to the next page; the extraction follows that pointer rather than assuming one call returns everything.
One more constraint shapes the design: Autotask meters API usage against an hourly threshold per database, so a naive full reload every hour is not a viable pattern. We page the history once, then request only records whose last-modified stamp has moved. That keeps a multi-year model inside both the refresh window and the request budget.
Fewer than most people expect. Almost every question an MSP asks — contract profitability, technician utilization, effective rate, SLA attainment — resolves to a combination of the same handful of entities. The table below maps each one to the job it does once the data is modelled rather than merely listed.
| Entity | What it holds | Role in the model |
|---|---|---|
Tickets |
Queue, status, priority, issue and sub-issue type, company and assigned resource, create and complete stamps, SLA reference and response timestamps | Fact table for ticket lifecycle, backlog and SLA measures |
TimeEntries |
Hours worked and hours to bill, billing code and role, resource, and the ticket, task or contract the time was charged against | Fact table for labour — the backbone of utilization, cost and effective rate |
Contracts + ContractServices |
Contract type, period and dates, the services and units on a recurring agreement, and block-hour balances | Contract dimension, and the structure margin is grouped by |
BillingItems |
Approved and posted billing lines by company, contract and period, covering labour, recurring services, charges and milestones | Fact table for revenue actually billed, not revenue contracted |
Companies |
Client records, company type, territory, classification and parent relationships | Client dimension — the key every fact table conforms to |
Resources + Roles |
Technicians and engineers, their departments, and the roles that carry billing and internal rates | Resource dimension, joined to the cost rates you supply |
Projects + Tasks |
Project phases, estimated versus actual hours, and task-level assignment | Second labour context, so project work is not mistaken for service desk work |
ConfigurationItems |
Managed devices and subscriptions per client, with contract association | Denominator for cost and effort per endpoint or per seat |
Two structural details cause most of the rework. User-defined fields do not appear as columns — they come back inside a nested collection of name/value pairs on each record, which has to be expanded and pivoted before any slicer can use them. And a large share of the interesting fields are picklist integers rather than text, so status, priority, queue and issue type need their labels resolved from the entity's field metadata before a report reads as English. Both are cheap once, expensive if discovered late, and are the reason a copy-pasted Power Query snippet from a forum rarely survives contact with a real tenant.
There are two supported ways into the same data, and the right answer depends on what you already license and how much history you want. The REST API is universal and needs no add-on. The Autotask data warehouse is a SQL Server-backed copy of your database that Power BI can query directly, which changes the economics of a multi-year load considerably. Neither is automatically better.
| Consideration | Autotask REST API | Autotask data warehouse (SQL) | Native LiveReports & widgets |
|---|---|---|---|
| Availability | Every tenant, once an API user exists | Where the add-on is licensed | Included in the PSA |
| Multi-year history | Workable, but a staged, paged load | Cheapest route — set-based SQL, no paging | Constrained by report and widget limits |
| Joins across entities | Done in Power Query or in the model | Done in the database before load | Limited to what the report writer exposes |
| Unattended refresh | Scheduled or incremental in your workspace | Scheduled via gateway where the copy is private | Refreshes on page reload, no wallboard mode |
| Labour costed at your rates | Requires a cost-rate table you supply | Requires the same table, joined in SQL | Not possible — cost rates are not in the PSA |
| Where the logic lives | A model file in your own tenant, versioned and owned by you | Inside Autotask, tied to the subscription | |
In practice a good number of MSPs end up using both data paths: the warehouse for the deep history that would take hours to page, and the API for the recent window that has to be current by the morning stand-up. That is a normal, supportable design rather than a compromise. The last column is the honest reason people go looking in the first place — native Autotask reporting is genuinely useful inside the PSA, but its widgets refresh when someone reloads the page, there is no TV or wallboard display mode for a service-desk screen, and it cannot cost a technician's hour because your cost rates were never in Autotask to begin with.
In DAX, over a star schema, against a date table that spans the full history. The measures below are the ones that get argued about in every MSP engagement, because each one encodes a definition your leadership team has to agree with before the number means anything. We write them explicitly rather than hiding the logic in a query step.
| Measure | Definition | Why it is written this way |
|---|---|---|
| Hours Worked | SUM ( 'Time Entry'[Hours Worked] ) |
Everything downstream costs or divides from this, so it stays deliberately plain |
| Hours To Bill | SUM ( 'Time Entry'[Hours To Bill] ) |
Autotask records both; the gap between them is written-off effort, which is the thing worth seeing |
| Utilization % | DIVIDE ( [Hours To Bill], [Capacity Hours] ) |
Capacity comes from a resource-schedule table, because the PSA records time worked, not time available |
| Labour Cost | SUMX ( 'Time Entry', 'Time Entry'[Hours Worked] * RELATED ( 'Resource'[Cost Rate] ) ) |
Row-level, so a rate change or a mixed-seniority ticket costs correctly |
| Contract Margin % | DIVIDE ( [Billed Revenue] - [Labour Cost] - [Allocated Product Cost], [Billed Revenue] ) |
Revenue from billing items, not the contract header, which stops being true the first time services change |
| Effective Rate | DIVIDE ( [Billed Revenue], [Hours Worked] ) |
Divides by hours worked, since unbilled effort on a recurring contract is exactly the problem being measured |
| SLA Attainment % | DIVIDE ( [Tickets Meeting SLA], [Tickets With An SLA] ) |
Excludes tickets that never carried an SLA, so the rate is not quietly inflated by out-of-scope work |
Effective rate by contract is usually the measure that changes a conversation. It is arithmetically trivial and organisationally uncomfortable: it puts a number on the flat-fee agreements absorbing more labour than they price for, and it does it per contract rather than as an average that hides them. The gap between hours worked and hours to bill does the same job at resource level. The modelling discipline underneath — conformed dimensions, one date table, measures instead of calculated columns — is the same craft described on our Power BI data modeling and DAX page.
Three things, settled during the build rather than discovered on the first Monday. First, the gateway question: a cloud-only API pull normally refreshes without one, but a SQL warehouse copy inside your network needs Microsoft's on-premises data gateway (opens in new tab). Our walkthrough of Power BI gateway setup covers the install and data-source registration step by step.
Second, dynamic data sources. Following a paged API means building request URLs in code, and the Power BI service refuses to refresh a query whose data source it cannot determine in advance. The fix is to keep the base address fixed and vary only the relative path and query parameters, which keeps the source statically analysable. Third, cadence and volume: refresh frequency follows Microsoft's documented data refresh limits (opens in new tab), and for multi-year ticket and time history we configure incremental refresh (opens in new tab) so only a recent window is re-pulled each cycle — which also keeps you comfortably inside Autotask's hourly request budget.
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 ticket, backlog and SLA reporting plus technician utilization from a single Autotask instance usually fits a Starter block. Full contract profitability — cost rates, product allocation, billing-item reconciliation across several contract types — typically needs Growth, because agreeing the allocation rules takes discussion as well as development.
Most tasks ship within 3–5 business days of scope approval. You get a written scope and hours estimate before anything starts, and hours are deducted only for work you approve. Our guide to Power BI dashboard development cost covers what moves the number, and how prepaid Flex hours work explains the model in full.
MSPs running Autotask PSA with enough history to trend and no in-house data engineer — typically the point where someone exports to Excel every month and nobody quite trusts the result. Owners, service delivery managers and finance leads are the people who usually get in touch, often just after a renewal conversation went badly.
If your PSA is ConnectWise rather than Autotask, the ConnectWise Power BI dashboard page covers that platform's own API, schema and gateway questions — a different PSA with a genuinely different integration. And if you want the commercial picture across your whole toolset rather than the Autotask plumbing, start with Power BI dashboards for MSPs, which stays at the industry level on the KPIs themselves. 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 Autotask build usually fits inside one Flex block, and hours deduct only for work you approve. See Flex pricing →
How it works
Your administrator creates a read-only API user and issues the secret and integration code; we resolve your web-services zone, confirm whether a data warehouse is licensed, agree the cost-rate and allocation inputs, and reply with a scope and hours estimate before any work begins.
A senior US-based engineer pages the history, pivots the user-defined fields, resolves picklists to labels, builds the star schema, date table and resource-capacity table, and writes the DAX for contract margin, effective rate, utilization and SLA attainment — validating totals against your own Autotask reports.
Published to your workspace with scheduled or incremental refresh, a gateway where a warehouse copy sits behind your firewall, row-level security so each client or manager sees only their slice, and documentation you keep. We maintain it from your banked hours.
Representative perspective
Hours worked against hours to bill, per engineer, per contract. We had been arguing about utilization for a year without ever seeing the write-offs that caused it.Owner, managed service provider
Representative perspective
Our first attempt failed on the base URL and the second died on paging. Having someone who had already solved both was most of the value.Service delivery manager, IT services firm
Tell us whether you have the data warehouse add-on, what you hold for resource cost rates, and the reporting you need. We'll confirm the access route, scope the task, and tell you the hours before anything starts — no project minimums, no obligation.
FAQ
Your administrator creates an API-only user in Autotask, assigns it the API user security level, and issues a username and secret plus an integration code that identifies the calling application. Power Query sends all three as request headers. We ask for read-only permissions, so the credential reporting runs on cannot change anything in your PSA.
Autotask databases are spread across numbered web-services zones, so the correct base address is per tenant rather than global. The zone information endpoint returns yours when queried with the API user name. Hardcoding a zone someone found in a forum post is the single most common reason a first Autotask connection attempt fails outright.
Use whichever you already have. The REST API is available to every tenant and is the usual route. The SQL Server-backed data warehouse, where it is licensed, makes multi-year history far cheaper to load and lets joins happen in the database. Several MSPs use the warehouse for history and the API for recent activity.
Yes. User-defined fields arrive as a nested array of name and value pairs rather than as columns, so they have to be expanded and pivoted before anything can filter on them. We map the ones you actually report on, resolve any picklist values to their labels, and leave the rest out so the model stays fast.
Native Autotask reporting is good at operational lists inside the PSA, but its widgets refresh on a page reload rather than on a schedule, and there is no wallboard mode. It also cannot cost labour, because your resource cost rates do not live in the PSA. Both gaps are modelling problems, which is what Power BI is for.
This page is deliberately the technical half of the story. For the KPI and commercial picture across your whole stack, read Power BI dashboards for MSPs and IT services next; for the same job on a different PSA, the ConnectWise Power BI dashboard build covers that platform's API and agreement structure. MSPs share their underlying economics with other people-based businesses, so professional services dashboards goes deeper on utilization and realization, and field-service operators running a dispatch platform instead of a PSA should look at the ServiceTitan Power BI dashboard build. Delivery data from Jira can be modelled alongside your contracts, the accounting join is covered on our QuickBooks Power BI dashboard page, and the craft underneath is set out in data modeling and DAX and Power BI dashboard development. If the wider question is strategy and governance rather than one source system, Power BI consulting is the place to start. 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.