Overview

Attendance data for students has gone through multiple iterations that affect the state of Student Attendance Metrics in the dashboard application. This article covers the evolution of the student data in the Operational Data Store (ODS) and the metrics it populates.

Version 1.0

In Ed-Fi Dashboards v1.0, the Database and ETL setup were as follows:

Database

In the Ed-Fi v1.0 of the ODS, the table for student attendance is edfi.AttendanceEvent. This table was comprised of the following primary composite key:

• StudentUSI
• SchoolId
• ClassPeriodName
• ClassroomIdentificationCode
• LocalCourseCode
• TermTypeId
• SchoolYear
• EventDate
• AttendanceEventCategoryTypeId

Since all the elements were required to create an attendance event, the only data that could be loaded was section attendance. Implementations that did not track daily attendance with section data found it difficult to load data. As a workaround, these implementations created dummy records with a fake section and the section was used for each students attendance event.

ETL

In the v1.0 version of the StudentAttendanceMetrics package, Daily Attendance was calculated with the student’s section event. Based on the school’s calendar, the start date of the school year the package calculates the numbers of days for a student should be in attendance. It counts the number of days the student has had an attendance event for negative attendance and then calculates the daily attendance metric. This process then created a couple of issues that had to be resolved.

1. With section attendance data, multiple attendance events would be recorded for a student, a single section would need to be selected as the final attendance record for the day. The home room indicator was used to identify the daily attendance record.
2. Since this attendance event data was for sections, implementations with daily attendance only could not load its attendance records. So a work around was created where a dummy record for section was created and the homeroom indicator was set to true.

Version 1.1 and 1.2

Ed-Fi Dashboards v1.1 and v1.2 changed the database and ETL configuration as follows:

Database

In Ed-Fi ODS v1.1, the edfi.AttendanceEvent was deprecated and two new tables were created:

edfi.StudentSchoolAttendanceEvent. Used to track daily attendance at the school level.

Composite keys:

• StudentUSI
• SchoolId
• TermTypeId
• SchoolYear
• EventDate
• AttendanceEventCategoryTypeId

edfi.StudentSectionAttendanceEvent. Used to track attendance at the section level.

Composite keys:

• StudentUSI
• SchoolId
• ClassPeriodName
• ClassRoomIdentificationCode
• LocalCourseCode
• TermTypeId
• SchoolYear
• EventDate
• AttendanceEventCategoryTypeId

ETL

In the v1.1 revision of the StudentAttendanceMetrics package, the new tables were not added and no changes were made to the metrics packages.

In the v1.2 revision of the StudentAttendanceMetrics and SchoolAttendanceMetrics packages, the use of the edfi.StudentSchoolAttendanceEvent was added. To support backward compatibility and the use of the edfi.StudentSectionAttendanceEvent to calculate daily attendance by using the homeroom indicator workaround. A union was added to the two packages in the source queries that read the Attendance data. This would allow implementations to submit attendance data at the School level in the StudentSchoolAttendanceEvent to calculate the Daily Attendance Metric.

Version 2.0

Database

In Ed-Fi ODS v2.0, the type id fields were converted to descriptor id fields:

edfi.StudentSchoolAttendanceEvent. Used to track daily attendance at the school level.

Composite keys:

• StudentUSI
• SchoolId
• TermDescriptorId
• SchoolYear
• EventDate
• AttendanceEventCategoryDescriptorId

edfi.StudentSectionAttendanceEvent. Used to track attendance at the section level.

Composite keys:

• StudentUSI
• SchoolId
• ClassPeriodName
• ClassRoomIdentificationCode
• LocalCourseCode
• TermDescriptorId
• SchoolYear
• EventDate
• AttendanceEventCategoryDescriptorId

Assumptions

With attendance data now in two different places, the following assumptions were made for implementations for backward compatibility:

1. Daily Attendance. Schools that have attendance data for the day and not at a lower granular level would submit data as School Attendance.

2. Section Attendance. Schools that have attendance data for the sections would submit attendance data and would not submit the same attendance data for the school attendance.