Chronic Absenteeism Use Case

Owned by Stephen Fuqua
Last updated: Jul 29, 2020

Introduction

"Students who are chronically absent—meaning they miss at least 15 days of school in a year—are at serious risk of falling behind in school. Yet, for too long, this crisis in our nation's public elementary and secondary schools has not been fully understood."

From the introduction to CHRONIC ABSENTEEISM IN THE NATION'S SCHOOLS: A hidden educational crisis by the U.S. Department of Education.

Functional Vision

A new use case, Chronic Absenteeism or "chrab", will expand the Analytics Middle Tier Enhancements by providing one or more views that facilitate exploration of chronic absenteeism across various demographic perspectives, as available from the Ed-Fi ODS database.

As noted in the quotation above, "chronic absenteeism" will be defined as missing at least 15 days of school in a year, whether through an excused or unexcused absence. An early warning can be signaled during the year by looking at the percentage of absences relative to number of instructional days: for example if the school calendar has 180 instructional days, then a student could be flagged with a warning at any time if attendance falls below 92% (that is, (180-15/180)). In keeping with the spirit of the Analytics Middle Tier, the views provided will not calculate attendance rate or determine chronic absenteeism in and of themselves: rather, they will make the raw data more accessible, facilitating the ability of education agency data analysts to implement locally defined algorithms.

Although there are similarities with the Early Warning System views, this Chronic Absenteeism use case will provide different shapes to the data and may provide additional demographic information that were not relevant to the Balfanz-model of early warning for potential high school dropout.

User Stories

Attendance Fact View

As an education agency data analyst, I want a query that tells me if a student was absent on any given day, so that I can use the results to calculate total absences and generate chronic absenteeism alerts.

  • Reports data at the level of the school.
  • Treats both excused and unexcused absences as "absent", for agencies that calculate based on recorded absence from school.
  • Provides "presence" data for agencies that calculate on recorded presence at school.
  • To support varying requirements, provides separate data points for absence/presence:
    • At school
    • From any section
    • From the "homeroom" section
  • Returns student data for instructional days on which the student is actively enrolled.
  • Can be joined to other student data in the Analytics Middle Tier to derive grade level and demographics.

Expected columns:

  • StudentSchoolKey
  • StudentKey
  • SchoolKey
  • DateKey
  • IsAbsentFromSchool
  • IsAbsentFromHomeRoom
  • IsAbsentFromAnySection
  • IsPresentAtSchool
  • IsPresentAtHomeRoom
  • IsPrsentInAllSections

Eligible Attendance View

As an education agency data analyst, I want a query that tells me if a student was eligible for attendance on a given day and a count of days prior from the given date to the beginning of the school calendar for the current session or school year.

  • Enumerates each date of the calendar for which the school was in session, removing any that are excluded for any reason.
  • Given a student, applies each inclusive range for which there is an enrollment start and end date.
    • A student enrollment may start and end multiple times within a session
    • The latest start date for a student with no end date, or end date outside of range, implies that the student is still enrolled within the date range analyzed.
  • Can be used with the Student Attendance Fact to determine rate of attendance.


The first fact view above should be able to give the desired end result with something like the following query. This sample query assumes the Texas model of counting attendance by home room, and it assumes that attendance is being handle "negatively": that is, a student is assumed present unless marked as absent.

Attendance Rate "Now"
with aggr as (
  select
    StudentKey,
    SchoolKey,
    count(1) as DaysEnrolled,
    sum(IsAbsentFromHomeRoom) as DaysAbsent,
  from
	analytics.chrab_AttendanceFact
  group by
    StudentKey,
    SchoolKey
)
select
  (DaysEnrolled - DaysAbsent) / DaysEnrolled as AttendanceRate
from
  aggr
Historic Attendance Rate
select
  DateDim.DateKey,
  Attendance.StudentKey,
  Attendance.SchoolKey,
  (Attendance.DaysEnrolled - Attendance.DaysAbsent) / Attendance.DaysEnrolled as AttendanceRateToDate
from 
  analytics.DateDim
outer apply (
  select
    StudentKey,
    SchoolKey,
    count(1) as DaysEnrolled,
    sum(IsAbsentFromHomeRoom) as DaysAbsent
  from
    analytics.chrab_AttendanceFact
  where
    chrab_AttendanceFact.DateKey < DateDim.DateKey
  group by
    StudentKey,
    SchoolKey
) as Attendance