How We Connect
Once Aspen is connected to Schoolzilla, Aspen data is pulled nightly via a secured Open Database Connection (ODBC). Traffic is encrypted in transit and at rest and a successful connection requires an SSH server or VPN. For more information about ODBC and VPN, please review our Connecting your SIS to Schoolzilla using ODBC overview.
For more information about getting connected, please review our setup article here.
Mappings Overview
If a field can be further configured by a Schoolzilla admin or relates to metric configurations, then there will be a link to the relevant part of Data Configuration Settings.
Students
Student demographic data is sourced from the STUDENT, PERSON, PERSON_RACE and the STUDENT_PROGRAM_PARTICIPATION tables.
The documentation below presents the default mappings in place. Some of these mappings can be customized and are typically updated during initial setup. Customizable fields have an asterisk (*).
*During the summer months, the "Active Enrollment Date" is used as today's date. While Active Enrollment Date is determined on a school-by-school basis, for each school, we determine the date based on the following logic:
- If today's date is in session → today's date
- If today's date is less than the roster rollover window (14 days unless configured differently) before the start of the upcoming school year → First day of school
- Otherwise → Last day of previous school year
| Key Field(s) | Mapping(s) |
|---|---|
| System Student ID | Values are populated using the STD_OID field in the STUDENT table. Since Aspen is case-sensitive, this internal ID field is hashed in the Schoolzilla database (which is not case-sensitive) to remain a unique system identifier. This means the ID value will not be recognizable to Aspen users. |
| Full Name | Values are populated using the PSN_NAME_LAST and PSN_NAME_FIRST fields in the PERSON table. |
| Current Grade Level | Values are populated using the STD_GRADE_LEVEL field in the STUDENT table. We translate the number to a descriptor with an ordinal indicator. Current grade level is the grade a student is actively in during this school year. |
| EL Status* | Values are equal to "ESL" when the student has an active record in the STUDENT_PROGRAM_PARTICIPATION table for the PGM_PROGRAM_CODE of "ESL". The record is considered active if today's** date is on or after the PGM_START_DATE and today's** date is on or before the PGM_END_DATE if it is populated. |
| FRL Status* | Values are equal to "Free/Reduced Lunch" when the student has an active record in the STUDENT_PROGRAM_PARTICIPATION table for the PGM_PROGRAM_CODE of "Free/Reduced Lunch". The record is considered active if today's** date is on or after the PGM_START_DATE and today's** date is on or before the PGM_END_DATE if it is populated. |
| Foster Care Status* | Values are not populated by default. |
| Gender* | Values are populated using the PSN_GENDER_CODE field in the PERSON table. |
| Grade Level at the Time | Values are populated using the STD_GRADE_LEVEL field in the STUDENT table for all current year data. Values are populated using the SXA_GRADE_LEVEL field in the STUDENT_CONTEXT_ATTRIBUTES table for all historical years. The SXA_GRADE_LEVEL for the SXA_ENROLLMENT_STATUS of "Active" will be used in favor of the "Inactive" record if there are multiple records in a school year. Grade Level at the Time is the Grade level the student was in during the test, class, or behavior event was recorded. |
| Homeless Status* | Values are equal to "Is Homeless" when the student has an active record in the STUDENT_PROGRAM_PARTICIPATION table for the PGM_PROGRAM_CODE of "Homeless". The record is considered active if today's** date is on or after the PGM_START_DATE and today's** date is on or before the PGM_END_DATE if it is populated. All other students are considered "Not Homeless". |
| Is SPED* | Values are equal to "In Special Education" when the STD_SPED_STATUS field in the STUDENT table is equal to "Active". Values are equal to "Not in Special Education" when the STD_SPED_STATUS field in the STUDENT table is equal to "Inactive" or "Exited". The status for all other students is considered "Unknown". |
| Migrant Status* | Values are not populated by default. |
| Military Family Status* | Values are not populated by default. |
| Primary Ethnicity* |
Values are populated using the first valid option:
|
| SPED Status* | Values are not populated by default. |
Staff
A comprehensive teacher user is sourced from the PERSON and STAFF tables.
| Key Field(s) | Mapping(s) |
|---|---|
| Full Name | Values are populated using the PSN_NAME_LAST and PSN_NAME_FIRST fields from the PERSON table. |
| Values are populated using the PSN_EMAIL_01 field in the PERSON table. |
Calendars
School calendar data is sourced from the CALENDAR_SCHOOL_DATE, CALENDAR_SCHOOL and DISTRICT_SCHOOL_YEAR_CONTEXT tables.
| Key Field(s) | Mapping(s) |
|---|---|
| School Year | Calendars are linked to school years using the CTX_SCHOOL_YEAR field in the DISTRICT_SCHOOL_YEAR_CONTEXT table. |
| In Session |
Days are considered "In Session" for a school if the CSD_IN_SESSION_IND field in the CALENDAR_SCHOOL_DATE table is 1 for any calendar associated with a school. Days are considered "In Session" for a student if the CSD_IN_SESSION_IND field in the CALENDAR_SCHOOL_DATE table is 1 for the student's calendar. A student's calendar is identified using the SXA_CALENDAR_CODE field in the STUDENT_CONTEXT_ATTRIBUTES table. If no SXA_CALENDAR_CODE is available for the student and year, the school logic above is used and applied to the student. |
School Enrollment & Retention
Student school enrollment history is populated using the STUDENT_ENROLLMENT table. Both single-year and multi-year enrollment tracking is supported.
Note: Schoolzilla assumes that a student is still enrolled at any school in which they were entered and never exited, regardless of student status.
The documentation below represents the default mappings in place. The fields can be configured to pull from ENR_ENROLLMENT_CODE, ENR_ENROLLMENT_REASON_CODE, or ENR_ENROLLMENT_STATUS_CODE.
| Key Field(s) | Mapping(s) |
|---|---|
| School Year | Enrollments are linked to school years using the CTX_SCHOOL_YEAR field in the DISTRICT_SCHOOL_YEAR_CONTEXT table where the ENR_ENROLLMENT_DATE field in the STUDENT_ENROLLMENT is between (inclusive) the CTX_START_DATE and CTX_END_DATE fields. |
| Entry Date | Values are populated using the ENR_ENROLLMENT_DATE field in the STUDENT_ENROLLMENT table for ENR_ENROLLMENT_TYPEs of "E". For multi-year enrollments, the first day of the school year is used. |
| Exit Date | Values are populated using the ENR_ENROLLMENT_DATE field in the STUDENT_ENROLLMENT table for ENR_ENROLLMENT_TYPEs of "W". For multi-year enrollments, the last day of the school year is used. |
| Exit Code * | Values are populated using the ENR_ENROLLMENT_CODE field in the STUDENT_ENROLLMENT table for ENR_ENROLLMENT_TYPEs of "W". For multi-year enrollments, a generated exit code of "(SZ) Multi-Year" is used. |
| No Show |
Enrollments are considered "No Show" records if the ENR_ENROLLMENT_STATUS_CODE is "No Show" or "NoShow" for the "W" ENR_ENROLLMENT_TYPE record. For multi-year enrollments, this logic will only apply to the last year of the enrollment record. |
| School | Enrollments are linked to schools using the ENR_SKL_OID field in the STUDENT_ENROLLMENT table. |
Student Rosters (Class & Teacher Associations)
Students are rostered to classes and teachers using the STUDENT_SCHEDULE, SCHEDULE_MASTER, SCHEDULE_MASTER_TEACHER, SCHEDULE and STUDENT_SCHEDULE_CHANGE tables.
| Key Field(s) | Mapping(s) |
|---|---|
| School Year | Class enrollments are linked to school years using the CTX_SCHOOL_YEAR field in the DISTRICT_SCHOOL_YEAR_CONTEXT table. |
| Class Entry Date | Values are populated using the SCC_DATE field in the STUDENT_SCHEDULE_CHANGE table for the SCC_CHANGE_TYPE_CODE of "Add". If no value is in that table, we assume the student was enrolled for the full schedule of the course and use the SCH_START_DATE field in the SCHEDULE table. |
| Class Exit Date | Values are populated using the SCC_DATE field in the STUDENT_SCHEDULE_CHANGE table for the SCC_CHANGE_TYPE_CODE of "Drop". If no value is in that table, we assume the student was enrolled for the full schedule of the course and use the SCH_END_DATE field in the SCHEDULE table. |
| Staff | Class enrollments are linked to staff using the MTC_STF_OID field in the SCHEDULE_MASTER_TEACHER table. |
Attendance
Daily attendance is calculated using enrollment data and attendance records from the STUDENT_ATTENDANCE table.
Schoolzilla assumes a student is present for every in-session school day they were enrolled at their school for which they are not otherwise marked absent.
| Key Field(s) | Mapping(s) |
|---|---|
| Attendance Value | Values are populated using the ATT_PORTION_ABSENT field in the STUDENT_ATTENDANCE table. We calculate a daily attendance value using 1 - ATT_PORTION_ABSENT. Additionally, a student is considered fully present (1) for all in-session days they were enrolled for which they have no record in the STUDENT_ATTENDANCE table. |
| Absence Code | Values are populated using the ATT_ABSENT_IND field in the STUDENT_ATTENDANCE table (1 = Absent). |
| School | Attendance is linked to schools using the student's enrollment record. |
Behavior
Behavior data is loaded from the STUDENT_CONDUCT_INCIDENT, STUDENT_CONDUCT_OFFENSE, and the STUDENT_CONDUCT_ACTION tables.
| Key Field(s) | Mapping(s) |
|---|---|
| School Year | Behavior incidents are linked to school years using calendar data based on the CND_INCIDENT_DATE and CND_SKL_OID fields in the STUDENT_CONDUCT_INCIDENT table. |
| School | Behavior incidents are linked to school using the CND_SKL_OID field in the STUDENT_CONDUCT_INCIDENT table. |
| Incident Date | Values are populated using the CND_INCIDENT_DATE field in the STUDENT_CONDUCT_INCIDENT table. |
| Incident Time of Day | Values are populated using CND_INCIDENT_TIME field in the STUDENT_CONDUCT_INCIDENT table. |
| Behavior Category |
Values are populated using the RCD_DESCRIPTION field in the REF_CODE table for the CND_INCIDENT_CODE in the STUDENT_CONDUCT_INCIDENT table. If no description is available, the CND_INCIDENT_CODE is used. Values are also loaded from the RCD_DESCRIPTION field in the REF_CODE table for the CNO_OFFENSE_CODE in the STUDENT_CONDUCT_OFFENSE table. If no description is available, the CNO_OFFENSE_CODE is used. |
| Behavior Location | Values are populated using the RCD_DESCRIPTION field in the REF_CODE table for the CND_INCIDENT_LOCATION in the STUDENT_CONDUCT_INCIDENT table. If no description is available, the CND_INCIDENT_LOCATION is used. |
| Behavior Response | Values are populated using the RCD_DESCRIPTION field in the REF_CODE table for the ACT_ACTION_CODE in the STUDENT_CONDUCT_ACTION table. |
GPA
GPAs are sourced from the STUDENT_GRADE_POINT
| Key Field(s) | Mapping(s) |
|---|---|
| GPA Calculation | Values are populated using the GPD_NAME field in the GRADE_GPA_DEFINITION table. |
| GPA Value | Values are populated using the GPT_CUMULATIVE_GPA and GPT_GRADE_POINT_AVERAGE fields in the STUDENT_GRADE_POINT table. |
By default,
- Cumulative GPA will be loaded using the GRADE_GPA_DEFINITION of "Official Un-Weighted GPA".
Please contact support to configure which GPA calculations are used on your dashboards!
Grades
Grades data is sourced from the STUDENT_TRANSCRIPT table.
The documentation below represents the default mappings in place. Some of these mappings can be customized and are typically updated during initial setup. Customizable fields have an asterisk (*).
| Key Field(s) | Mapping(s) |
|---|---|
| School Year | Grades are linked to school years using the CTX_SCHOOL_YEAR field in the DISTRICT_SCHOOL_YEAR_CONTEXT table. |
| School | Grades are linked to schools using the TRN_SKL_OID field in the STUDENT_TRANSCRIPT table. |
| Course Subject* | Values are populated using the RCD_DESCRIPTION field in the REF_CODE table for the CSK_DEPARTMENT_CODE field in the COURSE_SCHOOL table. If no description is available the CSK_DEPARTMENT_CODE is used. |
| Class Period | Values are populated using the PER_NAME field in the SCHEDULE_PERIOD table. |
| Grade Name |
Final grade values are populated using the TRN_FINAL_GRADE field in the STUDENT_TRANSCRIPT table. Float values are rounded to 0 decimal places. In progress grade values are populated using dynamically determined fields configured in Aspen. Only grade values stored in column types 3 (Term Average) and column type 5 (Grade) are currently supported. |
| Grading Period |
Final grades are not linked to Grading Periods. In progress grades are linked to grading periods using the GTC_GTM_OID, TRN_SKL_OID, and TRN_CTX_OID fields. Values are populated using the GTC_GRADE_COLUMN_HEADER field in the GRADE_TRANS_COLUMN_DEFINITION table. |
| Term | Grades are linked to terms through the master schedule and using the TRM_OID field in the SCHEDULE_TERM table. Values are populated using the TRM_TERM_CODE field in the SCHEDULE_TERM table. |
| Grade Type | Values are populated using the GTC_COLUMN_TYPE field in the GRADE_TRANS_COLUMN_DEFINITION table. |
| Grade Status | All grades pulled from the TRN_FINAL_GRADE field are considered "Final". Grades pulled dynamically from customer-configured columns of type 3 & 5 are considered "In Progress". |
Known Limitations
Grade Level At The Time - Schoolzilla sources grade level at the time data from a table which only tracks a student's grade level for the entirety of a school year. If a student changes grade levels mid-year, this is not tracked in Aspen and, is therefore, not tracked in Schoolzilla.
Schoolzilla Supports 1 Calendar per School - Aspen allows for multiple calendars at a given school differentiated by CAS_CALENDAR_ID. At this time, Schoolzilla only supports 1 calendar per year and school for determining if a day is in or out of session.
Frequent Data Quality Issues
For the issues below, Follett support and help documentation is the best resource for helping to answer and resolve configuration questions/issue. Schoolzilla has, with the assistance of customers like you, documented these issues as they have arisen but has limited knowledge and expertise of how to resolve errors within Aspen.
Issue 1: Students not withdrawn from the same school in which they were entered.
This can cause:
- Inflated total enrollment for current and historical years.
- Students enrolled in grade levels outside of the grade level range of a school (e.g. 9th graders at a middle school).
Because Aspen supports multi-year enrollments, Schoolzilla will continue to generate a new year's enrollment record for any student who was enrolled at a school and not withdrawn from that school. If a student is "inactive" in Aspen but still showing as currently enrolled in Schoolzilla, you should ensure the student was withdrawn from every school they have an entry record for across all years.
Issue 2: Missing school calendar data.
This can cause:
- No data to show up from a given year for any metrics from the SIS (e.g. enrollment, attendance).
- No data to show up for a given year for a subset of schools for any metrics from the SIS (e.g. enrollment, attendance).
This occurs when there is no record in the CALENDAR_SCHOOL table for the calendar school specified in CALENDAR_SCHOOL_DATE. See information on calendar mappings above.
Issue 3: Student action taken in Aspen without a corresponding incident.
This can cause:
- Lower than expected suspension and expulsion count totals.
- Lower than expected suspension and expulsion rate totals.
This occurs when an action is recorded for a student (data appears in the STUDENT_CONDUCT_ACTION table) but the related incident is somehow removed.
Issue 4: Student entry or exit date outside any district school year in Aspen.
This can cause:
- Lower than expected enrollments (if entry dates).
- Higher than expected enrollments (if exit dates).
This occurs when an ENR_ENROLLMENT_DATE occurs outside any CTX_SCHOOL_YEAR. This is typically caused by an unintentional gap in the end date of one calendar year and the start date of the next and can be addressed in the DISTRICT_SCHOOL_YEAR_CONTEXT.
Frequently Asked Questions
None at this time.