ConnectWise PSA to Power BI, done for you
Authenticated extraction from the Manage REST API, a modelled star schema over tickets, time entries, agreements and invoices, and DAX measures for agreement margin, effective rate and technician utilization — built by a senior US-based engineer, not assembled from a gauge library.
Representative dashboard — sample data
Where these projects actually stall
ConnectWise PSA holds everything you need and hands almost none of it over conveniently. The REST API is paged, throttled, and authenticated with a key pair you have to provision yourself. Ticket, time-entry, agreement and invoice records each arrive as nested JSON with reference objects rather than foreign keys. Nothing about that shape is ready for a report, and that is where most in-house attempts run out of road — long before anyone argues about chart colours.
So the work is extraction, then modelling, then measures. Page the history once and pull incrementally after that. Flatten the reference objects into real dimension keys. Build a star schema with a proper date table and a member-capacity table the PSA does not give you. Then write the DAX that turns logged hours and invoiced amounts into agreement margin and utilization you would put in front of a partner.
What we build from ConnectWise 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 is upstream of the report page, and every one of them is where a DIY attempt tends to stop.
A dedicated API member with a read-only security role and its own key pair, plus a registered client ID. Reporting never runs on a technician's login, and the credential it does run on cannot write back into your PSA.
History is pulled once in staged pages; after that only records whose last-updated stamp has moved are requested. That is the difference between a refresh that finishes and one that times out at year three.
Company, member, agreement and board arrive as embedded reference objects. They become surrogate keys against real dimension tables, so a client means one thing across tickets, time and invoices.
Invoiced revenue against labour costed at each member's rate, with product and licence spend allocated to the agreement it serves. Not the agreement header amount, which stops being true the first time an addition changes.
The API gives you hours logged, not hours available. We build a member-capacity table from your schedules so utilization divides by something defensible rather than a hard-coded eight-hour day.
Scheduled or incremental refresh, gateway configured where the instance is partner-hosted, and failures fixed by us out of your banked hours rather than landing on whoever built it.
You create an API member inside ConnectWise — a member record that exists only for integrations, with its own security role — and generate a public/private key pair against it. Requests then authenticate over HTTPS using that key pair alongside your company identifier, with a registered client ID sent as a header on every call. There is no interactive sign-in and no OAuth consent screen to click through.
Two things matter here beyond getting a 200 back. First, scope: we ask for a read-only security role, because a reporting credential has no business being able to modify a ticket. Second, ownership: the key pair belongs to your instance and is provisioned by your administrator, so nothing about the connection depends on us continuing to exist. If you later take the model in-house, the credential is already yours.
Six or seven, in practice. Everything an MSP asks for — margin, utilization, effective rate, SLA detail — resolves to some combination of tickets, time entries, agreements, invoices, companies and members. The table below maps each endpoint to the role it plays once the data is modelled rather than listed.
| Endpoint | What it holds | Role in the model |
|---|---|---|
service/tickets |
Board, status, type, priority, company and member references, entered and closed timestamps, SLA status | Fact table for ticket lifecycle and SLA measures |
time/entries |
Actual hours, billable option, work type and work role, member, and the ticket or agreement the time was charged to | Fact table for labour — the backbone of both utilization and cost |
finance/agreements |
Agreement header, type, bill cycle, start and end dates, plus additions carrying seat and unit changes | Dimension for contract, and the structure margin is grouped by |
finance/invoices |
Invoiced amounts by company, agreement and period, including one-off and project billing | Fact table for revenue actually recognised, not contracted |
company/companies |
Client records, company types, territories, status and site structure | Client dimension — the key every fact table conforms to |
system/members |
Technicians and engineers, default work role, location and team | Member dimension, joined to the cost rates you supply |
procurement/products |
Product and licence lines sold against tickets, projects and agreements | Cost input for margin once allocated to the agreement it serves |
Two structural details cause most of the rework. Reference objects — company, member, agreement, board — arrive nested inside each record rather than as flat foreign keys, so they have to be expanded and conformed into the star schema before any relationship exists. And cost rates are not sitting in the API waiting to be read; they come from you, usually as a small maintained table, which is a conversation worth having in scoping rather than at the end.
In DAX, over a star schema, against a date table that covers the full history. The measures below are the ones that get argued about in every MSP engagement, because each embeds a definition your leadership team has to agree with before the number means anything. We write them explicitly rather than burying the logic in a query.
| Measure | Definition | Why it is written this way |
|---|---|---|
| Logged Hours | SUM ( 'Time Entry'[Actual Hours] ) |
Everything downstream divides or costs from this, so it stays deliberately plain |
| Billable Hours | CALCULATE ( [Logged Hours], 'Time Entry'[Billable Option] = "Billable" ) |
Filters on the billable flag rather than inferring intent from work type |
| Utilization % | DIVIDE ( [Billable Hours], [Capacity Hours] ) |
Capacity comes from a member-schedule table, because the PSA reports time logged, not time available |
| Labour Cost | SUMX ( 'Time Entry', 'Time Entry'[Actual Hours] * RELATED ( 'Member'[Cost Rate] ) ) |
Row-level so a rate change or a mixed-seniority ticket costs correctly |
| Agreement Margin % | DIVIDE ( [Invoiced Revenue] - [Labour Cost] - [Allocated Product Cost], [Invoiced Revenue] ) |
Revenue from invoices, not the agreement header, which drifts the moment an addition changes |
| Effective Rate | DIVIDE ( [Invoiced Revenue], [Logged Hours] ) |
Divides by logged hours, since unbillable time on a fixed-fee agreement is exactly the problem being measured |
Effective rate by agreement is usually the measure that changes a conversation. It is arithmetically simple and organisationally uncomfortable: it puts a number on the flat-fee contracts absorbing more labour than they price for, and it does it per agreement rather than as an average that hides them. 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.
Sometimes, and it is worth settling before the build rather than discovering it on the first scheduled refresh. A partner-hosted or on-premises ConnectWise instance needs Microsoft's on-premises data gateway (opens in new tab), and so does any staged SQL copy sitting inside your network. Cloud-hosted instances normally refresh without one. Our walkthrough of Power BI gateway setup covers the install and data-source registration in detail.
The exception catches people out. Paging a REST API means building request URLs in code, and the Power BI service refuses to refresh a query whose data source it cannot determine ahead of time. The fix is to keep the base address constant and vary only the relative path and query parameters, or to register the source through a gateway configured to skip the connection test. Either way, refresh cadence then follows Microsoft's documented data refresh limits (opens in new tab), and for multi-year ticket and time history we usually configure incremental refresh (opens in new tab) so only a recent window is re-pulled each cycle.
BrightGauge is a capable product and ConnectWise has owned it since 2019, so the integration is close. It is built for live gauges, scorecards and client-facing dashboards over its own ConnectWise-shaped datasets, and for that it is a reasonable answer. What it is not is a semantic modelling tool, and the questions MSPs bring us are almost always modelling questions.
| What you are trying to do | Dataset-driven gauge tooling | Custom Power BI model |
|---|---|---|
| Live ticket and SLA scorecards | Its core strength — prebuilt and fast to stand up | Also straightforward, but you build it rather than pick it |
| Margin including labour at cost | Needs cost rates the PSA does not hold | Cost table joined to members and costed row by row |
| Vendor and licence spend allocated per client | Sits outside the PSA dataset entirely | Modelled as an additional fact with agreed allocation rules |
| Multi-year trend across restructured agreements | Constrained by how the dataset is scoped | Slowly changing dimension keeps history comparable |
| Custom time intelligence and cohorts | Limited to what the metric builder exposes | Any DAX you can justify, versioned and documented |
| Where the logic lives | Inside the vendor's platform | In a model file in your own tenant, which you own outright |
The practical answer is often both. Keep the gauge wall for the service desk, and build the model for the numbers that decide pricing, staffing and which renewals to reprice. If what you actually want is the wider operational and commercial picture across your whole toolset rather than the ConnectWise plumbing, start with Power BI for MSPs and IT services instead — this page is deliberately the technical half.
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 and SLA reporting plus technician utilization from a single ConnectWise instance usually fits a Starter block. Full agreement profitability — cost rates, product allocation, invoice reconciliation — typically needs Growth, because the allocation rules take discussion as well as development.
Most tasks ship within 3–5 business days of scope approval. You get a scope and hours estimate before anything starts, and hours deduct 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 ConnectWise PSA with enough history to trend and no in-house data engineer — typically the point where someone is exporting to Excel monthly and nobody trusts the result. Owners, service delivery managers and finance leads are the people who usually get in touch, often after an agreement renewal went badly.
If your delivery work runs through a project tool as well as the service desk, the Jira Power BI dashboard page covers modelling that source alongside a PSA, and accounting-side joins for cost and revenue are described on our QuickBooks Power BI dashboard 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 ConnectWise 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 member and key pair; we confirm whether the instance is cloud or partner-hosted, 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, flattens the reference objects into conformed dimensions, builds the star schema and date table, and writes the DAX for margin, effective rate and utilization — validating totals against your own PSA reports.
Published to your workspace with scheduled or incremental refresh, gateway configured where needed, 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
Effective rate per agreement was the number that ended the argument. We had three contracts we were proud of that were quietly absorbing a technician each.Owner, managed service provider
Representative perspective
Our own attempt worked in Desktop and died on the first scheduled refresh. Getting the paging and the gateway sorted out properly was most of the value.Service delivery manager, IT services firm
Tell us whether your instance is cloud or partner-hosted, what you hold for 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
An API member is created inside ConnectWise with its own security role and a public/private key pair. Requests authenticate over HTTPS using that key pair together with your company identifier, plus a registered client ID header. We ask for a read-only role, so the credential used by reporting can never write back into your PSA.
It depends on where your instance lives and how the queries are written. A partner-hosted or on-premises ConnectWise instance, or a staged SQL copy behind your firewall, needs the gateway. Cloud instances usually refresh without one, unless paginated queries build their URLs dynamically, which the service refuses to refresh unattended.
BrightGauge is strong at live gauges and client-facing scorecards over its own ConnectWise datasets. It is not a semantic modelling tool. Once you need agreement margin that includes labour at cost and allocated vendor spend, or several years of trend across restructured agreements, you need a model and DAX rather than a gauge.
Yes, provided the inputs exist. We recognise revenue from invoiced amounts rather than the agreement header, cost labour by multiplying logged hours against each member's cost rate, and allocate product and licence spend to the agreement it serves. Where an input is missing we surface the gap instead of quietly assuming it.
The API is paged and throttled, so a full historical extract of tickets and time entries is a staged job rather than one request. We page through history once, then use the last-updated field to pull only changed records on each refresh, which keeps a multi-year model inside a normal refresh window.
For the commercial picture across your whole stack rather than the PSA plumbing, Power BI for MSPs and IT services is the page to read next. MSPs share their underlying economics with other people-based businesses, and MSPs running the other major PSA should read the Autotask Power BI dashboard build, which covers that platform's zone-resolved REST API, user-defined fields and data-warehouse route. 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. Staffing and recruiting firms billing placements from an ATS have the same modelling problem in a different shape — the Bullhorn Power BI dashboard build covers time-to-fill, submittal ratios and recruiter margin. Delivery data from Jira or monday.com can be modelled alongside your agreements, the accounting join is covered on our QuickBooks page, and the craft underneath is described in data modeling and DAX and dashboard development. 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.