From Your SIS to Students at Risk
Step through standing up Actful-KPI for a school district, from connecting its Student Information System (SIS) to tuning thresholds and touring the dashboard.
1Connect your SIS to your data store
Actful-KPI runs inside your environment. Its ingestion app comes with the appropriate queries for most SIS vendors. If yours isn’t covered yet, a consult can be arranged to adapt it to your SIS. For this district’s PowerSchool that means 14 tables, only the columns the KPIs need, scoped to the current school year. The app copies them into a SQL Server database you own. Celerity provides the code and never receives, stores or sees your student data. Choose where your data will live.
Fully managed in your Azure subscription. The connection uses Microsoft Entra authentication or a SQL login held in Azure Key Vault.
# Ingestion app and dashboard read the same key: ConnectionStrings:<ShortName> Client:ShortName = HSD ConnectionStrings:HSD = Server=tcp:hsd-sql.database.windows.net;Database=HSD-KpiGrdAttn;Authentication=Active Directory Default ConnectionStrings:HSD-PowerSchool = <your PowerSchool Oracle connection> # Values come from Key Vault or environment variables; nothing is committed.
{
"schoolYear": { "yearId": 35, "termLow": 3500, "termHigh": 3599,
"yearStart": "2025-08-01", "yearEnd": "2026-06-30" },
"extracts": [
{ "target": "Students", "loadOrder": 6, "incremental": true,
"select": "DCID, ID, SchoolID, Student_Number, Last_Name, First_Name,
Grade_Level, Enroll_Status, EntryDate, ExitDate, ...",
"from": "Students",
"filter": "EntryDate <= DATE '{YearEnd}' AND
(ExitDate IS NULL OR ExitDate >= DATE '{YearStart}')",
"dcidColumn": "DCID", "timestampColumn": "TRANSACTION_DATE" },
{ "target": "Attendance", "loadOrder": 8, "incremental": true,
"from": "Attendance",
"filter": "Att_Mode_Code = 'ATT_ModeMeeting' AND
Att_Date BETWEEN DATE '{YearStart}' AND DATE '{YearEnd}'",
... },
... 12 more tables
]
} 2Build the tiered database
The database is published from a versioned package into the same data store. It uses a medallion architecture: Bronze, Silver and Gold are three progressive layers of data refinement that turn raw inputs into trusted, business-ready metrics. Bronze keeps an exact copy of your SIS data, Silver standardises it, and Gold holds the pre-computed KPIs the dashboard reads. Click a layer to see what’s in it.
An exact copy of the 14 PowerSchool tables: same names and columns, keyed on DCID, every row stamped with when it was loaded. No transformations, so anything can be traced back to the source.
raw.Studentsraw.Sectionsraw.CCraw.Attendanceraw.Attendance_Coderaw.PGFinalGradesraw.Teachersraw.Calendar_Day… 6 more
Views that translate PowerSchool into the SIF education data standard: SIF field names and code lists. Everything above this line is PowerSchool-specific; everything below it isn’t, so another SIS could feed the same KPIs.
sif.StudentPersonalsif.SectionInfosif.StudentSectionEnrollmentsif.StudentDailyAttendancesif.StudentSectionMarks
Pre-computed KPI tables, rebuilt by stored procedures from the silver views. Each row carries a risk tier: OnTrack, Watch, AtRisk or Critical. Thresholds are read from kpi.Configuration, so tuning them needs no redeploy.
kpi.CourseAttendanceRatekpi.CourseGradeStatuskpi.CourseAttendanceWindowkpi.StudentRiskSummarykpi.vw_* dashboard viewskpi.Configuration
meta Pipeline state: meta.Watermarks (what was last loaded per table) and meta.PipelineRunLog (every run, row counts and errors).
ui Dashboard settings and the access model: ui.Configuration, roles, assets, the role × asset grid and the Entra group mapping.
3Schedule the refresh jobs
Each night, after data entry ceases on your SIS, the ingestion app pulls only what has changed since its last run, using a watermark it keeps per table, and the KPIs are rebuilt. A weekly job catches records deleted in your SIS. Once a year a rollover moves everything to the new school year by changing one block of settings, not code.
| Job | When | What it does | Runs |
|---|---|---|---|
| Nightly extract | Every night, 02:00 | New and changed rows from all 14 tables, from each table’s watermark | KpiGrdAttn.Ingestion.exe |
| KPI rebuild | Right after the extract | Recomputes the gold KPI tables from the fresh data | EXEC kpi.usp_RebuildAllKPIs |
| Weekly reconcile | Sundays, 03:00 | Finds rows deleted in your SIS, with a safety limit on how much it will remove | KpiGrdAttn.Ingestion.exe --reconcile |
| Year rollover | Once, at the new school year | Change the schoolYear block in the manifest, then trim last year’s data per each table’s retention rule | KpiGrdAttn.Ingestion.exe --rollover |
4Deploy the dashboard
The dashboard is a .NET web app that runs on your own web host, next to your data. Choose a host. The steps have the same shape on each: publish the app, hand it the database connection from your secrets store, and point sign-in at your Microsoft Entra ID.
- HTTPS on your own domain
- Database connection from your secrets store, never in the code
- Sign-in through your Microsoft Entra ID, with groups mapped to roles (step 7)
- Your name on it: OrganizationName = HSD
5Tune it in Settings
Settings is where the district tunes the KPIs to its own policies, with no code and no redeploy. Every threshold starts at the product default, and your changes survive upgrades. You’re signed in as App Support.
- Lower AttendanceRiskCriticalThreshold to 0.85 and watch the preview.
- Save, then press “Re-run KPIs now”, and check the Teacher or District tab.
- Switch “Acting as” to the superintendent: Settings becomes read-only.
6Check the pipeline
Pipeline (IT) shows whether the data can be trusted today: when each KPI table was rebuilt, when each PowerSchool table was last loaded, the watermarks the nightly job resumes from, and every run with its row counts and errors.
- Find the failed run on 11 February, and how the next night caught up on its own.
7Roles and access
Six roles cover everyone who uses the dashboard. Each role unlocks tabs and actions, and has a data scope: all schools, its own school, or only its own classes. Your Microsoft Entra ID groups map to these roles, so access follows your HR changes. Only App Support can change this grid. In this demo, the “Acting as” switcher stands in for Entra sign-in so you can try each role.
- Give Support Staff “Re-run KPIs”, then save.
- Switch “Acting as” to a teacher and watch the tabs shrink to one.
8View as another user
Principals and district staff can see the dashboard exactly as one of their teachers does, which helps with coaching and support. Scope still applies: a principal can only pick teachers at their own school, and teachers can’t view as anyone.
- Pick another teacher from “View as”.
- Switch “Acting as” to App Support: every teacher in the district is listed.
- Switch to the teacher: the picker disappears and the roster is pinned to their own classes.
9Tour the dashboard
Three views for three audiences, from the same data. Teacher (Today) lists the students in my classes who need attention. Principal ranks the sections in a school, and District ranks the schools. The Risk / Promise switch flips every view from who is slipping to who is thriving.
- Flip to Promise: Climbing students, the recent turnarounds, come first.
- Open Principal and change school.
- Note the “As of” badge: this demo is pinned to a reporting date, so it is never mistaken for live data.
10Read the district fan chart
The District tab can also show the whole district as one fan chart, a sunburst. It answers “where is the risk concentrated?” at a glance, and lets a superintendent drill from the district to a school to a single course without leaving the page.
- The centre is the district. Hover it for district totals.
- The inner ring is the schools. A wedge’s size is its number of students, and its colour deepens with its share of Critical and AtRisk students.
- The middle ring is the courses in each school, coloured by the course’s tier, from the same thresholds as the tables.
- The outer band splits each course’s students by tier: red Critical, orange AtRisk, amber Watch, pale green OnTrack.
- The most concerning wedges start at 12 o’clock and each ring runs clockwise to the healthiest, so start reading at the top.
- Hover any wedge for its breakdown and the suggested action: Act today, Outreach this week, Monitor.
- Click a wedge to zoom into it, and click the centre to step back out. The breadcrumb shows where you are.
- Flip School → Course to Course → School to ask “which course is struggling across the district?” instead.
- Max wedges keeps a busy ring readable: the most concerning wedges stay, and the rest fold into a grey “Other”.
- Switch to Promise to see where students are thriving: Climbing, Star and Steady.
What you just saw
A fictional district of eight schools and 4,463 students goes from its Student Information System (SIS) data to a working “Students at Risk” dashboard, all inside its own environment:
- Connect your SIS to your data store. Queries for most SIS vendors come with the product, and a consult can adapt it to one that isn’t covered yet. Your data lands in your own SQL Server: Azure SQL, Amazon RDS for SQL Server, or on your own servers.
- Build the tiered database. In a KPI database using medallion architecture, Bronze, Silver, and Gold refer to three progressive layers of data refinement that transform raw inputs into trusted, business-ready metrics. Bronze keeps an exact copy of the SIS data, Silver standardises it to the SIF education data model, and Gold holds the pre-computed KPIs.
- Schedule the refresh jobs. Nightly incremental loads and KPI rebuilds, a weekly check for deleted records, and a yearly rollover that is a settings change, not a code change.
- Deploy the dashboard to Azure, AWS, your own IIS servers or Docker.
- Tune, monitor and govern it: thresholds in Settings, data freshness in Pipeline, and access by role, including viewing the dashboard as another user.
- Read the district fan chart: the whole district on one chart, from schools to courses to students by tier, with click-to-zoom.
Your data stays home
Celerity Software provides the product code and updates. Student data never leaves your environment: we don’t receive it, store it or see it.
Who it’s for
Superintendents, principals and teachers who want to know which students need help this week, and IT teams who need to run that without adding another vendor holding student records.
Ready to Make More Room for Learning?
With offices in Calgary, AB; Desert Hot Springs, CA; and Brooksville, FL, we serve schools, districts and educational organizations across North America. Let's talk about where you are and where you want to go.
Contact Us