Illuminate SIS

Vendor:Illuminate Education Frequency:Nightly Method:SQL queries are run against the Illuminate database. Data is transformed and sent to the Ed-Fi ODS via the API.

Prerequisites

Illuminate allows for data out in a few ways. We detail this on our blog. For Landing Zone we pull Illuminate SIS data into the ODS via an ODBC connection. Email help@illuminateed.com to open a support ticket requesting ODBC access to your database. Connections are only allowed from IP addresses that have been whitelisted by Illuminate. Reference our PyPi Packages section to see how to assign static IPs to your Airflow nodes. Once you’ve done that, you’ll send Illuminate those IPs along with your the IP you’ll be accessing the database from when testing locally on your machine.

Initial setup

Airflow connections

Our DAGs look for two Airflow connections:

edfi_api

• Login: your Ed-Fi key
• Password: your Ed-Fi secret

illuminate_odbc

• Conn type: Postgres
• Host: Illuminate database IP address
• Schema: Illuminate database name
• Login: Illuminate odbc username
• Password: Illuminate odbc password
• Port: 5432

Configuration

Copy the illuminate-sis folder from our repo into your git repo. There is a config.py file in the root of this folder that must be configured. Complete the INSTANCES variable by naming your instance and entering your first day of school for the current school year.

Data we pull

Schools

School names for each site, their physical address, and grade levels served.

Table Name	Column Name	Ed-Fi API Element
public.sites	city	schools.addresses.city
public.sites	zip	schools.addresses.postalCode
codes.states	code_key	schools.addresses.stateAbbreviationDescriptor
public.sites	address	schools.addresses.streetNumberName
public.grade_levels	short_name	schools.gradeLevels
public.sites	parent_site_id	schools.localEducationAgencyReference.localEducationAgencyId
public.sites	site_name	schools.nameOfInstitution
public.sites	local_site_id	schools.schoolId

Sessions

Terms for the school year.

Table Name	Column Name	Ed-Fi API Element
codes.term_type	code_translation	sessions.termDescriptor
public.terms	start_date	sessions.beginDate
public.terms	end_date	sessions.endDate
public.sites	local_site_id	sessions.schoolReference.schoolId
public.sessions	academic_year	sessions.schoolYearTypeReference.schoolYear
public.terms	term_name	sessions.sessionName
public.terms	term_num

Marking Periods

Grading periods for the school year.

Table Name	Column Name	Ed-Fi API Element
public.grading_periods	grading_period_name	gradingPeriods
public.grading_periods	grading_period_start_date	gradingPeriods.beginDate
public.grading_periods	grading_period_end_date	gradingPeriods.endDate
codes.term_type	code_translation	gradingPeriods.gradingPeriodDescriptor
public.grading_periods	grading_period_num	gradingPeriods.periodSequence
public.sites	local_site_id	gradingPeriods.schoolReference.schoolId
public.sessions	academic_year	sessions.schoolYearTypeReference.schoolYear

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
public.day_types	in_session	calendarDates.calendarEvents.calendarEventDescriptors
public.calendar_days	date	calendarDates.date
public.sites	local_site_id	calendars.schoolReference.schoolId
public.sessions	academic_year	calendars.schoolYearTypeReference.schoolYear, calendarDates.calendarReference.schoolYear
public.day_types	status_text

Courses

Course information.

Table Name	Column Name	Ed-Fi API Element
public.courses	site_id	courses.educationOrganizationReference.educationOrganizationId
public.site_types	site_type_name
public.courses	school_course_id	courses.courseCode
public.courses	school_course_id	courses.identificationCodes.identificationCode
public.courses	long_name	courses.courseTitle
public.courses	is_weighted	courses.courseGPAApplicabilityDescriptor
public.course_weight_aff	weight	courses.level_characteristics
public.courses	credits	courses.minimumAvailableCredits
public.courses	max_credits	courses.maximumAvailableCredits
public.department	department_name	courses.academicSubjectDescriptor
public.courses	cbeds_course_id_num	courses.identificationCodes.identificationCode

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.

Table Name	Column Name	Ed-Fi API Element
function	get_section_timeblocks(ssc.section_id)	classPeriods.classPeriodName
public.sites	local_site_id	classPeriods.schoolReference.schoolId
public.courses	school_course_id	courseOfferings.courseReference.courseCode
public.courses	site_id	courseOfferings.courseReference.educationOrganizationId
public.courses	long_name	courseOfferings.courseTitle
public.courses	school_course_id	courseOfferings.localCourseCode
public.sites	local_site_id	courseOfferings.schoolReference.schoolId
public.sites	local_site_id	courseOfferings.sessionReference.schoolId
matviews.ss_cube	academic_year	courseOfferings.sessionReference.schoolYear
public.rooms	room_number	locations.classroomIdentificationCode
public.sites	local_site_id	locations.schoolReference.schoolId
public.courses	credits	sections.availableCredits
function	get_section_timeblocks(ssc.section_id)	sections.classPeriods.classPeriodReference.classPeriodName
public.courses	school_course_id	sections.courseReference.localCourseCode
public.sites	local_site_id	sections.courseReference.schoolId
matviews.ss_cube	academic_year	sections.courseReference.schoolYear
public.rooms	room_number	sections.locationReference.classroomIdentificationCode
public.sites	local_site_id	sections.locationReference.schoolId
matviews.ss_cube	section_id	sections.sectionIdentifier
public.courses	school_course_id	staffSectionAssociations.sectionReference.localCourseCode
public.sites	local_site_id	staffSectionAssociations.sectionReference.schoolId
matviews.ss_cube	academic_year	staffSectionAssociations.sectionReference.schoolYear
matviews.ss_cube	section_id	staffSectionAssociations.sectionReference.sectionIdentifier
matviews.ss_cube	user_id	staffSectionAssociations.staffReference.staffUniqueId
matviews.ss_cube	entry_date	studentSectionAssociations.beginDate
matviews.ss_cube	leave_date	studentSectionAssociations.endDate
public.courses	school_course_id	studentSectionAssociations.sectionReference.localCourseCode
public.sites	local_site_id	studentSectionAssociations.sectionReference.schoolId
matviews.ss_cube	academic_year	studentSectionAssociations.sectionReference.schoolYear
matviews.ss_cube	section_id	studentSectionAssociations.sectionReference.sectionIdentifier
public.students	local_student_id	studentSectionAssociations.studentReference.studentUniqueId
public.section_term_aff	term_name
public.section_term_aff	term_num
public.departments	department_name

Staff

Staff demographics and school assignments.

Table Name	Column Name	Ed-Fi API Element
public.sites	parent_site_id	staffEducationOrganizationEmploymentAssociations.educationOrganizationReference.educationOrganizationId
public.sites	parent_site_id	staffEducationOrganizationAssignmentAssociations.educationOrganizationReference.educationOrganizationId
public.sites	local_site_id	staffSchoolAssociations.schoolReference.schoolId
public.users	user_id	staffs.staffUniqueId
public.users	user_id	staffEducationOrganizationEmploymentAssociations.staffReference.staffUniqueId
public.users	user_id	staffEducationOrganizationAssignmentAssociations.staffReference.staffUniqueId
public.users	user_id	staffSchoolAssociations.staffReference.staffUniqueId
public.users	last_name	staffs.lastSurname
public.users	first_name	staffs.firstName
public.users	middle_name	staffs.middleName
public.users	job_title	staffEducationOrganizationAssignmentAssociations.positionTitle
public.departments	department_name
public.users	gender	staffs.sexDescriptor
public.users	is_hispanic
codes.race_slash_ethnicity	code_translation
public.users	email	staffs.loginId, staffs.electronicMails.electronicMailAddress

Student Demographics

Student demographics, program info, and enrollments.

Table Name	Column Name	Ed-Fi API Element
public.sites	parent_site_id	studentEducationOrganizationAssociations.educationOrganizationReference.educationOrganizationId
public.students	email	studentEducationOrganizationAssociations.electronicMails
public.students	is_hispanic	studentEducationOrganizationAssociations.hispanicLatinoEthnicity
codes.english_proficiency	code_translation	studentEducationOrganizationAssociations.limitedEnglishProficiencyDescriptor
public.students	email	studentEducationOrganizationAssociations.loginId
public.program_504	start_date	studentEducationOrganizationAssociations.programParticipations
public.student_session_aff	student_id	studentEducationOrganizationAssociations.programParticipations.identificationCode
public.students	state_student_id	studentEducationOrganizationAssociations.programParticipations.identificationCode
codes.ethnicity	code_translation	studentEducationOrganizationAssociations.raceDescriptors
public.students	gender	studentEducationOrganizationAssociations.sexDescriptor
public.students	local_student_id	studentEducationOrganizationAssociations.studentReference.studentUniqueId
public.students	birth_date	students.birthDate
public.students	gender	students.birthSexDescriptor
public.students	first_name	students.firstName
public.students	last_name	students.lastName
public.students	middle_name	students.middleName
public.students	local_student_id	students.studentUniqueId
public.student_session_aff	entry_date	studentSchoolAssociations.entryDate
public.grade_levels	short_name	studentSchoolAssociations.entryGradeLevelDescriptor
public.student_session_aff	leave_date	studentSchoolAssociations.exitWithdrawDate
public.sites	site_id	studentSchoolAssociations.schoolReference.schoolId
public.sessions	academic_year	studentSchoolAssociations.schoolYearTypeReference.schoolYear
public.students	local_student_id	studentSchoolAssociations.studentReference.studentUniqueId
public.student_common_demographics	is_specialed	studentSpecialEducationProgramAssociations
public.sites	parent_site_id	studentSpecialEducationProgramAssociations.educationOrganizationReference.educationOrganizationId
public.sites	parent_site_id	studentSpecialEducationProgramAssociations.programReference.educationOrganizationId
public.students	local_student_id	studentSpecialEducationProgramAssociations.studentReference.studentUniqueId
public.sites	site_name
codes.countries	code_translation
public.students	mentor_name
public.program_504	end_date
codes.exit_codes	code_translation

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
public.sessions	academic_year	studentSchoolAttendanceEvents.sessionReference.schoolYear
public.terms	term_name	studentSchoolAttendanceEvents.sessionReference.sessionName
attendance.daily_records	site_id	studentSchoolAttendanceEvents.schoolReference.schoolId
public.students	local_student_id	studentSchoolAttendanceEvents.studentReference.studentUniqueId
attendance.daily_records	date	studentSchoolAttendanceEvents.eventDate
public.attendance_flags	display_code	studentSchoolAttendanceEvents.attendanceEventCategoryDescriptor
public.attendance_flags	apportionment	studentSchoolAttendanceEvents.eventDuration
public.attendance_flags	flag_text	studentSchoolAttendanceEvents.attendanceEventReason

Current Overall Gradebook Scores

Current overall gradebook scores. These are for the current marking period.

Table Name	Column Name	Ed-Fi API Element
public.section_term_aff	code_translation	grades.gradingPeriodReference.gradingPeriodDescriptor
public.section_term_aff	grading_period_num	grades.gradingPeriodReference.periodSequence
public.sites	local_site_id	grades.gradingPeriodReference.schoolId
matviews.ss_cube	academic_year	grades.gradingPeriodReference.schoolYear
gradebook.overall_score_cache	mark	grades.letterGradeEarned
gradebook.overall_score_cache	percentage	grades.numericGradeEarned
matviews.ss_cube	entry_date	grades.studentSectionAssociationReference.beginDate
public.courses	school_course_id	grades.studentSectionAssociationReference.localCourseCode
matviews.ss_cube	section_id	grades.studentSectionAssociationReference.sectionIdentifier
public.section_term_aff	term_name	grades.studentSectionAssociationReference.sessionName
public.students	local_student_id	grades.studentSectionAssociationReference.studentUniqueId
public.section_term_aff	term_num
public.section_term_aff	grading_period_name
public.courses	long_name

Discipline Incidents

Table Name	Column Name	Ed-Fi API Element
behavior.incidents	description	disciplineIncidents.behaviors.behaviorDetailedDescription
behavior.incidents	incident_date	disciplineIncidents.incidentDate
codes.behavior_descriptions	code_translation	disciplineIncidents.incidentDescription
behavior.incidents	incident_id	disciplineIncidents.incidentIdentifier
codes.behavior_locations	code_key	disciplineIncidents.incidentLocationDescriptor
public.sites	site_id	disciplineIncidents.schoolReference.schoolId
public.users	user_id	disciplineIncidents.staffReference.staffUniqueId
behavior.incidents	description	studentDisciplineIncidentAssociations.behaviors.behaviorDetailedDescription
behavior.incidents	incident_id	studentDisciplineIncidentAssociations.disciplineIncidentReference.incidentIdentifier
public.sites	site_id	studentDisciplineIncidentAssociations.disciplineIncidentReference.schoolId
codes.behavior_participants_roles	code_translation	studentDisciplineIncidentAssociations.studentParticipationCodeDescriptor
public.students	local_student_id	studentDisciplineIncidentAssociations.studentReference.studentUniqueId

Discipline Actions

Table Name	Column Name	Ed-Fi API Element
behavior.consequences	assigned_duration_days	disciplineActions.actualDisciplineActionLength
behavior.consequences	consequence_id	disciplineActions.disciplineActionIdentifier
behavior.consequences	served_duration_days	disciplineActions.disciplineActionLength
behavior.consequences	date_assigned	disciplineActions.disciplineDate
codes.behavior_consequences	code_translation	disciplineActions.disciplines.disciplineDescriptors
behavior.participants	incident_id	disciplineActions.studentDisciplineIncidentAssociations.incidentIdentifier
public.sites	site_id	disciplineActions.studentDisciplineIncidentAssociations.schoolId
public.students	local_student_id	disciplineActions.studentDisciplineIncidentAssociations.studentUniqueId

Transcript Data