eSchoolPlus SIS¶
| Vendor: | PowerSchool |
|---|---|
| Frequency: | Nightly |
| Method: | Download definition files are configured in eSchoolPlus. Exports are run nightly creating CSV files in the eSchoolPlus SFTP server. Data is picked up from there, transformed and sent to the Ed-Fi ODS via the API. |
Initial setup¶
Airflow connections¶
Our DAGs look for two Airflow connections:
edfi_api
- Login: your Ed-Fi key
- Password: your Ed-Fi secret
eschool_sftp
- Conn type: SSH
- Host: SFTP address
- Username: SFTP username
- Password: SFTP password
- Port: SFTP port number
Configuration¶
Data we pull¶
Schools¶
School names for each site, their physical address, and grade levels served.
| Table Name | Column Name | Ed-Fi API Element |
|---|---|---|
| reg_building | CITY | schools.addresses.city |
| reg_building | ZIP | schools.addresses.postalCode |
| reg_building | STATE | schools.addresses.stateAbbreviationDescriptor |
| reg_building | STREET1 | schools.addresses.streetNumberName |
| reg_building_grade | GRADE | schools.gradeLevels |
| reg_district | DISTRICT | schools.localEducationAgencyReference.localEducationAgencyId |
| reg_building | NAME | schools.nameOfInstitution |
| reg_building | BUILDING | schools.schoolId |
Sessions¶
Sessions and marking periods for the school year.
| Table Name | Column Name | Ed-Fi API Element |
|---|---|---|
| reg_mp_dates | START_DATE | gradingPeriods.beginDate |
| reg_mp_dates | END_DATE | gradingPeriods.endDate |
| reg_mp_dates | MARKING_PERIOD | gradingPeriods.gradingPeriodDescriptor |
| reg_mp_weeks | MP_ORDER | gradingPeriods.periodSequence |
| reg_mp_dates | BUILDING | gradingPeriods.schoolReference.schoolId |
| reg_mp_dates | START_DATE | sessions.beginDate |
| reg_mp_dates | END_DATE | sessions.endDate |
| reg_mp_dates | BUILDING | sessions.schoolReference.schoolId |
| reg_mp_dates | SCHOOL_YEAR | sessions.schoolYearTypeReference.schoolYear |
| reg_mp_dates | SCHOOL_YEAR | sessions.schoolYearTypeReference.schoolYear |
| reg_mp_weeks | SCHD_INTERVAL | sessions.termDescriptor |
Calendar dates¶
Academic calendar for the year noting which days are instructional days and which are not.
| Table Name | Column Name | Ed-Fi API Element |
|---|---|---|
| reg_cal_days | MEMBERSHIP_DAY | calendarDates.calendarEvents.calendarEventDescriptors |
| reg_cal_days | SCHOOL_YEAR | calendarDates.calendarReference.schoolYear |
| reg_cal_days | CAL_DATE | calendarDates.date |
| reg_cal_days | BUILDING | calendars.schoolReference.schoolId |
| reg_cal_days | SCHOOL_YEAR | calendars.schoolYearTypeReference.schoolYear |
| reg_calendar | DESCRIPTION | |
| reg_cal_days | DAY_TYPE | |
| reg_cal_days | MEMBERSHIP_VALUE | |
| reg_cal_days | TAKE_ATTENDANCE |
Courses¶
Course information.
Course Enrollments¶
This is a comprehensive export of the things needed to associate students and staff with class sections. This includes locations, class periods, course offerings, sections, student associations, and staff associations.
Staff¶
Staff demographics and school assignments.
| Table Name | Column Name | Ed-Fi API Element |
|---|---|---|
| reg_district | DISTRICT | staffEducationOrganizationEmploymentAssociations.educationOrganizationReference.educationOrganizationId |
| reg_district | DISTRICT | staffEducationOrganizationAssignmentAssociations.educationOrganizationReference.educationOrganizationId |
| reg_staff_bldgs | BUILDING | staffSchoolAssociations.schoolReference.schoolId |
| reg_building | NAME | |
| reg_staff | STAFF_ID | staffs.staffUniqueId |
| reg_staff | STAFF_ID | staffEducationOrganizationEmploymentAssociations.staffReference.staffUniqueId |
| reg_staff | STAFF_ID | staffEducationOrganizationAssignmentAssociations.staffReference.staffUniqueId |
| reg_staff | STAFF_ID | staffSchoolAssociations.staffReference.staffUniqueId |
| reg_staff | LAST_NAME | staffs.lastSurname |
| reg_staff | FIRST_NAME | staffs.firstName |
| reg_staff | MIDDLE_NAME | staffs.middleName |
| reg_staff | FMS_DEPARTMENT | |
| reg_staff | GENDER | staffs.sexDescriptor |
| reg_staff | FED_RACE_ETHNIC | |
| reg_staff | staffs.loginId | |
| reg_staff | staffs.electronicMails.electronicMailAddress |
Student Demographics¶
Student demographics, program info, and enrollments.
| Table Name | Column Name | Ed-Fi API Element |
|---|---|---|
| reg_district | DISTRICT | studentEducationOrganizationAssociations.educationOrganizationReference.educationOrganizationId |
| reg_personal | HISPANIC | studentEducationOrganizationAssociations.hispanicLatinoEthnicity |
| reg_personal | ESL | studentEducationOrganizationAssociations.limitedEnglishProficiencyDescriptor |
| reg_personal | ETHNIC_CODE | studentEducationOrganizationAssociations.raceDescriptors |
| reg | GENDER | studentEducationOrganizationAssociations.sexDescriptor |
| reg | STUDENT_ID | studentEducationOrganizationAssociations.studentReference.studentUniqueId |
| reg | BIRTHDATE | students.birthDate |
| reg | GENDER | students.birthSexDescriptor |
| reg | FIRST_NAME | students.firstName |
| reg | LAST_NAME | students.lastName |
| reg | MIDDLE_NAME | students.middleName |
| reg | STUDENT_ID | students.studentUniqueId |
| reg_entry_with | ENTRY_DATE | studentSchoolAssociations.entryDate |
| reg | GRADE | studentSchoolAssociations.entryGradeLevelDescriptor |
| reg_entry_with | WITHDRAWAL_DATE | studentSchoolAssociations.exitWithdrawDate |
| reg_entry_with | BUILDING | studentSchoolAssociations.schoolReference.schoolId |
| reg | STUDENT_ID | studentSchoolAssociations.studentReference.studentUniqueId |
| reg_personal | HAS_IEP | studentSpecialEducationProgramAssociations |
| reg_district | DISTRICT | studentSpecialEducationProgramAssociations.educationOrganizationReference.educationOrganizationId |
| reg_district | DISTRICT | studentSpecialEducationProgramAssociations.programReference.educationOrganizationId |
| reg | STUDENT_ID | studentSpecialEducationProgramAssociations.studentReference.studentUniqueId |
| reg_building | NAME | |
| reg | HOMEROOM_PRIMARY | |
| reg_entry_with | WITHDRAWAL_CODE |
Student Attendance¶
All day student attendance events. This export includes whether a student is in attendance for a school day.
| Table Name | Column Name | Ed-Fi API Element |
|---|---|---|
| att_stu_day_totals | ATTENDANCE_CODE | studentSchoolAttendanceEvents.attendanceEventCategoryDescriptor |
| att_code | DESCRIPTION | studentSchoolAttendanceEvents.attendanceEventReason |
| att_stu_day_totals | ATTENDANCE_DATE | studentSchoolAttendanceEvents.eventDate |
| att_stu_day_totals | ATT_CODE_VALUE | studentSchoolAttendanceEvents.eventDuration |
| att_stu_day_totals | BUILDING | studentSchoolAttendanceEvents.schoolReference.schoolId |
| att_stu_day_totals | SCHOOL_YEAR | studentSchoolAttendanceEvents.sessionReference.schoolYear |
| reg_mp_weeks | SCHD_INTERVAL | studentSchoolAttendanceEvents.sessionReference.sessionName |
| att_stu_day_totals | STUDENT_ID | studentSchoolAttendanceEvents.studentReference.studentUniqueId |
Discipline Incidents and Actions¶
| Table Name | Column Name | Ed-Fi API Element |
|---|---|---|
| disc_off_action | ACTUAL_DURATION | disciplineActions.actualDisciplineActionLength |
| disc_off_action | ACTION_NUMBER | disciplineActions.disciplineActionIdentifier |
| disc_off_action | SCHD_DURATION | disciplineActions.disciplineActionLength |
| disc_off_action | DATE_DETERMINED OR START_DATE | disciplineActions.disciplineDate |
| disc_incident | INCIDENT_ID | disciplineActions.studentDisciplineIncidentAssociations.incidentIdentifier |
| disc_incident | BUILDING | disciplineActions.studentDisciplineIncidentAssociations.schoolId |
| disc_offender | PERSON_ID | disciplineActions.studentDisciplineIncidentAssociations.studentUniqueId |
| disc_incident | INCIDENT_DATE | disciplineIncidents.incidentDate |
| disc_incident | INCIDENT_ID | disciplineIncidents.incidentIdentifier |
| disctb_location | DESCRIPTION | disciplineIncidents.incidentLocationDescriptor |
| disc_incident | INCIDENT_TIME | disciplineIncidents.incidentTime |
| disc_incident | POLICE_NOTIFIED | disciplineIncidents.reportedToLawEnforcement |
| disc_incident | BUILDING | disciplineIncidents.schoolReference.schoolId |
| disctb_off_action | DESCRIPTION | studentDisciplineIncidentAssociations.behaviors.behaviorDetailedDescription |
| disc_witness | PERSON_ID | studentDisciplineIncidentAssociations.studentReference.studentUniqueId |
| disc_victim | PERSON_ID | studentDisciplineIncidentAssociations.studentReference.studentUniqueId |
| disc_incident | PERSON_ID | studentDisciplineIncidentAssociations.studentReference.studentUniqueId |