PowerSchool SIS to Power BI, done for you
The extraction route gets settled first — read-only ODBC, a PowerQuery published through a plugin, or the plugin REST API — and then a senior US-based engineer models the enrollment, attendance, grade and behavior tables into one report your cabinet, principals and board open themselves.
Representative dashboard — sample data
Where these builds stall
District teams rarely get stuck on which numbers matter. Enrollment, attendance, grades and behavior are the four conversations every cabinet and school board already has. Where a PowerSchool reporting project stalls is one step earlier, at how those records leave the SIS at all — because there is more than one answer, they are governed by different permissions, and the one you can actually use depends on your hosting arrangement and your contract.
There is no first-party PowerSchool connector in Power BI. That leaves a read-only ODBC connection to the database, a PowerQuery published through a plugin and executed on the PowerSchool server, or the plugin REST API under OAuth credentials your district registers and can revoke — with the scheduled exports your team already builds for state reporting as a fallback, and PowerSchool Connected Intelligence as the vendor-managed option. Picking wrongly is not fatal, but it is expensive: it usually surfaces three weeks in, when the refresh that worked on someone’s laptop will not run in the service.
What we build from the PowerSchool extract
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 run out of road.
API access comes from a plugin your district installs, with OAuth enabled and field-level permissions declared in its access request. We write that request to the fields the reporting actually needs, so your PowerSchool administrator approves a short list rather than a blank cheque — and can disable it in one click.
Where ODBC access is granted it is the richest route, and it behaves like what it is: a read-only account against the SIS database, reached through a gateway, on a schedule chosen so reporting never competes with attendance-taking at 8:45am.
A PowerSchool PowerQuery is a named SQL query published in a plugin and run on the server, returning exactly the columns you asked for. It is the most precise route and the most maintainable — and it has nothing to do with the Power Query window in Power BI Desktop, which is a genuine source of crossed wires.
PowerSchool keeps every student it has ever enrolled. Status separates pre-registered from active from exited, and a student who withdraws and returns leaves more than one enrollment span. Headcount, membership days and mobility are three different questions, and only one of them is a row count.
Elementary schools usually take attendance once a day; secondary schools take it by period. Codes carry a presence meaning, and turning periods into a day of attendance runs through your attendance conversion and the in-session days on each school calendar. Skip that and the rate is confidently wrong.
The GPA on a transcript is derived from stored historical grades using your district’s GPA calculation method — weighting, credit types, which terms count. We either have PowerSchool calculate it server-side or re-implement the method as documented DAX, and we tie the result to a transcript before anyone presents it.
Through one of three documented routes, and the first job of the engagement is deciding which of them your district can actually use. Microsoft does not publish a PowerSchool connector, so nothing here is a two-click setup: each route is a different permission conversation, returns a different shape, and supports a different refresh cadence. We establish which one your hosting arrangement and contract allow before designing around it, and we confirm the method in the scope estimate before any hours are spent.
Two mechanics shape everything that follows. The API routes authenticate with OAuth client credentials issued by a plugin your district installs, and that plugin’s access request is what grants field-level visibility — so the fields nobody requested are the fields nobody can read, which is a feature rather than an obstacle. And the ODBC route, where it is open, is a database connection: Microsoft documents the Power Query ODBC connector (opens in new tab) as import only, which means a scheduled refresh reloads data rather than querying live, and a source inside your network needs the on-premises data gateway (opens in new tab) before the service can refresh anything at all.
| Route | How it authenticates and what it returns | When we use it |
|---|---|---|
| ODBC | A read-only database account and driver, reached over your network or a gateway. Returns tables and views as tables, so joins and filters are ordinary SQL and the whole schema is visible | Where your district already has ODBC access provisioned — it is the richest route for multi-year history and the fastest to model against. Import only, so we schedule it off-peak and keep history in a store you own |
| PowerQuery (PowerSchool’s) | A named SQL query published inside a plugin and executed on the PowerSchool server, returned as JSON through the API. Access is tied to the plugin and, for the report-tag flavour, to page permissions | Our default where ODBC is not available. One query per reporting entity, written once and version-controlled, returns the exact grain the model needs instead of a client-side reshape of everything |
| Plugin REST API | OAuth client credentials from a plugin with field-level access declared in its access request. Core resources under /ws/v1, paged with page and pagesize (100 by default), with expansions and extensions for extra field groups |
For entities the core endpoints cover cleanly, and wherever a district prefers a documented interface over SQL. We page into storage rather than querying live, because a full student pull is many round trips |
| Scheduled exports | Data Export Manager output and the state-reporting extracts your team already produces, delivered to a location you control | The pragmatic fallback when API and ODBC access are both blocked, and a good reconciliation source even when they are not — someone has already validated those files |
| Connected Intelligence or an existing warehouse copy | PowerSchool’s own managed data platform, which the vendor describes as supporting ODBC/JDBC, secure APIs and SFTP and invites you to “build real-time dashboards using your favorite BI tool”; or SIS data an internal pipeline already lands somewhere | Always our first question. If a governed copy exists and is trustworthy, we model on it and skip the extraction work entirely — and we will say plainly when the vendor platform is the cheaper answer |
One naming collision deserves flagging early, because it wastes meetings. A PowerQuery in PowerSchool is a named query published through a plugin, documented by PowerSchool alongside its PowerQuery data access tags (opens in new tab), and it runs on the SIS server. Power Query in Power BI is the transformation layer in Desktop. A real build uses both — a PowerSchool PowerQuery to get the rows out, Microsoft Power Query to shape them on the way in — so we name them separately in every document and ticket. Practitioners hit the same confusion in public: the Microsoft Fabric Community thread on connecting PowerSchool to Power BI (opens in new tab) is district staff working through exactly this question.
Four areas of the SIS do most of the work, and each one arrives at a grain that is not the grain the dashboard needs. That gap — between what PowerSchool stores and what a cabinet asks for — is most of the build. The table below is the set of derivations that come up on nearly every district engagement, written as documented DAX measures over a star schema with a school calendar rather than as one-off report filters.
| Reporting need | What PowerSchool holds | What the model adds |
|---|---|---|
| Enrollment and mobility | Student records with an enrollment status, plus entry and exit dated enrollment spans per school and year | Point-in-time headcount, membership days and entries and withdrawals as separate measures, so a re-enrolled student is one child and two spans rather than two children |
| Attendance | Daily or meeting-level attendance records, attendance codes with a presence meaning, attendance conversion, and in-session days on each school calendar | One attendance fact with both grains reconciled, absences classified consistently, and the rate expressed against days enrolled rather than days in the year |
| Grades and GPA | Stored historical grades, current-term grades from the gradebook, credit types and your district GPA calculation method | A documented GPA measure reconciled to a transcript, plus grade distribution and failure counts by course and teacher that the transcript never shows |
| Behavior | Log entries against a student, or incidents with participants, roles, behaviors and actions in Incident Management — often both, used differently by school | One behavior fact at a declared grain (incident, participation or action), so referrals, suspensions and days of instruction lost stop being three counts of the same thing |
| Course and schedule load | Course enrollments, sections, terms and teacher assignments keyed to a school and an internal year identifier | Section fill, seat utilization and class size on a conformed term dimension, joined to the same student key the other three facts use |
| Multi-year trend | Prior years held in the same tables, behind school, year and term keys that change as schools open, merge or recode | A history store you own with slowly changing school and student dimensions, so last year’s numbers still reconcile after this year’s recode |
Two of these deserve naming plainly, because they are where district dashboards most often lose credibility. Enrollment status separates pre-registered students from active students from students who have exited — pre-registered sits below the active value and exited above it — so a query that forgets it counts every student your district has ever enrolled. And GPA is calculated rather than stored: PowerSchool derives it from stored grades using your own calculation method, which means a DAX re-implementation has to encode that method deliberately, in writing, and be tied out against a real transcript before anyone shows it to a principal.
What belongs on the page — which enrollment, attendance and assessment metrics a district should actually track, and how chronic absenteeism is defined — is covered on our Power BI dashboards for school districts page, and we deliberately do not restate it here. This page is about getting those records out of PowerSchool correctly and keeping them coming. The modeling discipline underneath both — one date table, conformed dimensions, measures rather than calculated columns — follows Microsoft’s star schema guidance (opens in new tab) and is described on our data modeling and DAX page.
The model is published to your Power BI workspace and refreshed on a cadence the chosen route can sustain: for most districts an overnight pull of attendance, grades and behavior with a changed-date filter, not a full reload of a decade. Districts with long attendance histories get incremental refresh (opens in new tab) so each nightly run touches only the days that changed, inside Microsoft’s documented data refresh limits (opens in new tab). Where the source sits inside your network, the gateway runs on a server your IT team controls, with credentials they hold.
Student records carry obligations that pay data does not. Visibility is enforced by row-level security in the Power BI service (opens in new tab), mapped from accounts to schools, grade spans or roles, so a principal opens their own campus, a counselor sees their caseload and the cabinet sees the district — enforced by the model rather than by who was sent which file. Our walkthrough of a Power BI row-level security setup shows the roles and the test steps. Those FERPA obligations stay with the district, and the US Department of Education’s Student Privacy Policy Office (opens in new tab) is the authoritative reference; what we do is build to the access rules your student data privacy officer sets, test the roles against real accounts before go-live, and document who can see what. Where a subgroup is small enough that a single row identifies a child, we aggregate or suppress rather than publishing and hoping nobody drills.
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 enrollment, attendance, grade distribution and behavior for a single district on one route usually fits a Starter block. Adding several years of history, reconciling to state submissions, or joining site spending from the finance system typically moves it to Growth — less because of the development and more because agreeing definitions across assessment, student services and the business office takes conversation.
Most tasks ship within 3–5 business days of scope approval, and you get a scope and hours estimate before anything starts, which is what makes this purchasable against a line item rather than a procurement project. 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.
Districts, charter management organizations, county offices and independent schools running PowerSchool SIS with enough history to trend and no dedicated analytics engineer. Superintendents and cabinets, chief business officials, assessment and accountability directors, student services teams working chronic absenteeism, and the IT director who ends up owning the extract all tend to be in the room. Most reach out after assembling the same board packet by hand twice in a row, or after two schools presented different enrollment counts at the same meeting.
Writing back to PowerSchool is out of scope: this is a read-only reporting build, and student records belong in the SIS where they are auditable. State reporting stays untouched — your submissions keep originating in PowerSchool and your existing certification process, while the dashboard reads the same underlying data so problems surface while the window is still open. This is a management reporting tool, not a compliance system, and we do not give legal, funding or accountability advice. Other PowerSchool products — Enrollment, Naviance, Schoology, Unified Talent — are separate sources with separate access, and we will scope them rather than pretend they arrive in the same extract. 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 PowerSchool build usually fits inside one Flex block, and hours deduct only for work you approve. See Flex pricing →
How it works
We confirm which of ODBC, a plugin PowerQuery or the plugin API your hosting and contract actually allow, write the access request down to the fields reporting needs, and agree the definitions — how your year and terms run, which attendance grain each school uses, which GPA method is authoritative. Then you get a scope and hours estimate before any work begins.
A senior US-based engineer builds the scheduled extraction, lands enrollment spans, attendance, grades and behavior in a store you own, resolves the grain of each one, builds the star schema and school calendar, and writes the DAX — validating every total against a report your team already runs in PowerSchool.
Published to your workspace with scheduled or incremental refresh, row-level security so each principal opens their own school, and documentation you keep. We maintain it from your banked hours instead of it landing on the one person who understood the query.
Representative perspective
Two schools brought different enrollment counts to the same board meeting, both pulled from PowerSchool. Neither was lying. They had counted different things, and nobody had written down which was right.Assessment and accountability director, mid-sized district
Representative perspective
Our extract worked perfectly on a laptop and would not refresh in the service. Getting the route, the permissions and somewhere to keep the history right turned out to be most of the value.IT director, charter management organization
Tell us roughly how many students and schools you run, which PowerSchool exports your team rebuilds each month, and what the board asks for. 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 PowerSchool connector, so one of three documented routes carries the data: a read-only ODBC connection to the PowerSchool database, a PowerQuery published through a plugin and run server-side, or the plugin REST API under OAuth client credentials. Which routes are open to you depends on your hosting arrangement and contract.
No, and the collision causes real confusion. A PowerSchool PowerQuery is a named SQL query published inside a plugin and executed on the PowerSchool server, returning JSON through the API. Microsoft Power Query is the transformation layer inside Power BI Desktop. A build often uses both, which is why we name them separately in every document.
Where ODBC access is granted, yes, with two caveats. Microsoft documents the ODBC connector as import only, so scheduled refresh reloads rather than queries live, and a connection to a server inside your network needs an on-premises data gateway. We also stage the pull so reporting never competes with the SIS during the school day.
Because GPA is calculated, not stored as a column. PowerSchool derives it from stored historical grades using your district GPA calculation method, including weighting, credit types and which terms count. Rebuilding that method in DAX without writing it down reproduces a number that looks close and disagrees at the margin, which is where transcripts get challenged.
In log entries, in Incident Management, or in both, and they are different shapes. Log entries are one record against one student. Incident Management models an incident with participants, roles, behaviors and actions, which is what state discipline reporting usually rides on. We confirm which your schools use before counting anything.
We are an independent US-based Power BI consultancy with no PowerSchool partnership to claim and no data product to resell. We work under read-only access your district grants and can revoke, publish into your own Power BI workspace, and build to the access rules your student data privacy officer sets under FERPA.
If you are deciding what a district dashboard should show rather than how to extract it — enrollment trends, chronic absenteeism, assessment results, per-school spending — start with Power BI dashboards for school districts, which covers the metric definitions this page deliberately leaves alone. If your district runs Infinite Campus rather than PowerSchool, the routes out are different ones — an Ad hoc Reporting export, the Data Extract Utility or the OneRoster API — and are covered on our Infinite Campus Power BI dashboard page. Colleges and universities are a different build: the buyer is institutional research or enrollment management, the metrics are the admissions funnel, retention and net tuition revenue, and the systems are Banner, Colleague or Workday Student, so start with Power BI dashboards for higher education instead. When the same figures have to reach trustees on a fixed cadence, the pack and period discipline is on Power BI dashboards for board reporting, and the modeling craft underneath all of them is on our data modeling and DAX page. 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.