SSP v2.3.0 MAP Plan Status Calculation
- Jason Elwood
Applicable version: 2.3 or later
Description
- Prior to SSP v2.3, the Plan status was calculated externally to SSP with the results being stored with external data. The external data option, external_person_planning_status, remains available for implementers that choose to calculate the status externally. New functionality in SSP will calculate the status internally based on transcript data in the external table containing transcript information and plans saved in SSP. The new logic will calculate a Plan status for each active student with an active plan. Only the active Plan will be examined for a status calculation.
Process Description
- For each student with an active plan (map_plan.object_status = 1), SSP will compare the courses in external_student_transcript_course (formatted_course) for each term (term_code) with the planned courses (map_plan_course.formatted_course) for equivalent terms (map_plan_course.term_code) starting with the oldest Plan term through the calculation cutoff term. If no cutoff term is defined, the current term will be used. A match will have matching criteria (configurable) for both the Plan and transcripted courses. By default the formatted_course and term_code are used for matching. Additionally, the course title, course code and course hours can be used to provide a more precise match. The result of the calculation is a database update to store the Plan status and reasons for being off plan (if they exist). Each time the calculation is performed, the previous status calculation is removed so that only the most current calculation is stored in the database. The external_person_planning_status table will remain for implementers choosing to provide the calculation through the data integration.
Process Diagram
Database Tables
Features added in 2.4.0
MAP Status Calculation | Feature Additions | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Feature Description | Feature Definition | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Ratio to On Plan Status for the entire Plan | To further define the Plan Status, a ratio of overall matched transcripted to planned courses will demonstrate how close to On Plan the student is. For students who are Off Plan, the following questions are answered. What percentage of the matched transcripted courses satisfy the plan? During the calculation, the calculation process will identify courses on the plan and match the courses to transcripted courses but ignore the term. The calculation process is the same as phase 1 (course identifiers, term handling, etc). The ratio calculation is simply the number of matched courses divided by the number of planned courses. Planned Courses = 24 Transcript Courses = 27 Matched Courses = 21 Ratio = 87.5% Labels (names) can be assigned to ratio ranges. A label could be defined for a ratio range. IE 0-19 Way Off, 20-39 Kinda Off, 40-59 Halfway There, 60-79 Not Bad, 80-99 Pretty Good | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| On Track (Substitution): On Plan but with substitutable courses. | Phase 1 implementation used a strict definition of Plan Status. In order to be On Plan, the student has to take and pass every course on the plan in the same term. Students can still accomplish the overall goal of the plan by taking courses that still meet the course requirements, elective list, etc. For example: Psychology 101 is on the plan, but the student registers for Philosophy 101 because the Psychology was full. Philosophy meets the program requirements the same as Psychology. When the calculation logic attempts to find a transcripted course to match, the search will include the substitutable courses from an external database table populated with the associations for substitutable courses. Each course could have more than one substitutable course. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
On Track (Sequence): On Plan but a different order of courses. | Phase 1 implementation used a strict definition of Plan Status. In order to be On Plan, the student has to take and pass every course on the plan in the same term. Students can still accomplish the overall goal of the plan by taking courses in a different order. As long as all the planned courses are passed, the plan would be satisfied. The matching logic from the ratio feature could be used to determine the matching courses (term exclusion). This concept adds a distinction in the overall progress of completing a plan. Students may not follow the exact order of courses specified in the plan but remain On Track if all the courses are taken to date. The student took each course on the planned regardless of the term before the cutoff calculation term. All planned courses have been completed, just not in the order and term defined on the plan.
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Email service for calculation results |
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Manual execution of the plan calculation per student | The purpose is to allow for a new status calculation to be run for a single student outside of the calculation schedule. This may accommodate major plan changes, current calculation after removing an override, new student with a new plan or too much time before the next scheduled calculation. Coaches/advisors would start the calculation via a button in the MAP UI. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Ratio to On Plan status by term | To further define the Plan Status, a ratio per term of transcripted to planned courses will demonstrate how close to On Plan the student is. A ratio would exist for each term. For students who are Off Plan, the following questions are answered. What percentage of the transcripted courses satisfy the plan term? During the calculation, the calculation process will identify the courses for each term on the plan and match the courses to transcripted courses for the same term. The calculation process is the same as phase 1 (course identifiers, term handling, etc). The ratio calculation is simply the number of matched courses divided by the number of planned courses for each term. Term 1
Term 2
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Display for Plan Status Details and Configuration to control display of new calculation logic/results | The new matching logic will be included in the calculation process, and the results will be saved. Configurations will be added to control the display of calculation variations (2106, 2107, 2108, 2110) A new configuration entry will be used to determine what is displayed:
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Extend the external_term table definition to include columns for registration start and registration end dates.
| The registration dates could be used for SSP to automatically calculate the cutoff term. A concern of using a manually configured cutoff term is that implementers may forget to update the cutoff term value that could negatively affect the status calculation for a large number of students. If the registration dates were defined for terms, SSP could set the cutoff term to be the term from external_term with the most recent registration end date. Examples of extending the external_term table to include registration dates when the current Term is SP 2014 On 2/24/2014, the calculated current term is SP 2014
On 3/30/2014, the calculated current term is SU 2014
On 4/21/2014, the calculated current term is SU 2014
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Add the course_code to the transcript_course to increase the matching accuracy | Add a course_code column to the external_student_transcript_course to match the external_course definition. Since implementers may be defining multiple versions of courses in external_course and defining uniqueness by the course_code, the course_code on external_student_transcript_course would ensure a 100% match during the calculation process. If this is added, a new matching criteria (course_code) should be added to the configuration item. IMPORTANT- to begin using the course_code criteria, the map_plan_course.course_code table also needs to be updated so that previously planned courses will match the external_student_transcript_course.course_code values | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Adjustments to Main Tool, MAP, Student Search and Reports for new statuses | Adjustments to displays and reports for new features added to the plan status calculation logic and results |
Plan Status Report (applicable to 2.4.0 and later)
The plan status report will identify the results of the calculation for each planned course. Per the calculations described above, the report shows overall, term and course based matches and anomalies. Example Report:
Anomaly Codes
MULTIPLE_ANOMALIES_IN_TERM: multiple course anomalies in the term
NO_ANOMALY: not anomalies existed in the term
CURR_OR_FUT_COURSE_NO_GRADE: a missing or failed grade in a current or future term
- COURSE_NOT_PASSED: a missing or failed grade in a past term
COURSE_NOT_REGISTERED: a missing course in a current or future term
COURSE_NOT_TAKEN: a missing course in a past term
Queries (applicable to 2.3.0)
- The 2.3.0 version of the Plan status calculation does not produce any reports for the calculation process. The student's current status and high level explanation of an off plan status will display in the Main Tool, MAP Tool and MAP Plan Status report. For details of most recent calculation, example query are described below
- Identify Term Anomalies
select p.school_id, p.first_name, p.last_name, mr.plan_id, mr.plan_status, mr.plan_note, mct.term_code, mct.anomaly_code from person p, map_status_report mr, map_status_report_Term_details mct where p.id = mr.person_id and mr.id = mct.report_id order by p.school_id
- Identify Course Anomalies
select p.school_id, p.first_name, p.last_name, mr.plan_id, mr.plan_status, mr.plan_note, mcd.formatted_course, mcd.anomaly_code from person p, map_status_report mr, map_status_report_course_details mcd where p.id = mr.person_id and mr.id = mcd.report_id order by p.school_id
Definitions
- Past Term: any term with a start date before the current term's start date
- Current Term: same definition that we are using for the MAP plan/template term grid.
- Future Term: any term with a start date on or after the current term’s start date
- Cutoff Term: the last term to consider for plan to transcript term matching. The cutoff term is configurable while the current term is used if the configuration item does not exist or if the configured value is in the past.
- Passing Grade: any grade determined to be a passing grade and fulfill completion of a planned course
- Anomaly: for each instance where a planned course or term does not match the transcript data, an anomaly code describes the reason for
- On Plan: there is an exact match of courses for each transcripted term with the terms and courses on the active plan. For planned terms after the current term including the calculation cutoff term without a matching transcript term, the Plan is considered on plan until the transcript data diverges from the plan.
- Off Plan: at least one term has a planned course that does not exist in the matching transcripted term or a transcripted course matching a planned courses was not passed.
- On Track (Sequence): at least one term has a planned course that does not exist in the matching transcripted term but the course(s) that did not match was completed in a different transcripted term with a passing grade
- On Track (Substitutions): at least one term has a planned course that does not exist in the matching transcripted term but the course(s) that did not match fulfill the planned course requirement based on a substitutable course the student completed with a passing grade
Configuration items
| Name | Possible Values | Description |
|---|---|---|
| calculate_map_plan_status | true, false | turns on/off cron job that will drive the calculation |
| map_plan_status_passing_grades | school specific but something like (A,B,C) | In cases where a plan course and taken course line up, the student must have passed the class in order to not cause a anomaly |
| map_plan_status_cutoff_term_code | term_code (EXTERNAL_TERM.CODE) | The calculation cutoff term is the latest future term to be included in the matching logic. The term range for a given calculation will start with the oldest term for the student transcripted courses up to the cutoff term. The term must be a valid term from external_term.
|
| task_scheduler_map_plan_status_calculation_trigger | cron expression (i.e., 0 0 1 * * *) | drives the frequency of the cron job that will calculate status |
| map_plan_status_addition_course_matching_criteria | available options are (COURSE_TITLE,CREDIT_HOURS, COURSE_CODE) or blank | In addition to the static matching criteria (term_code and formatted_course), implementers have the option to add additional criteria.
|
map_plan_status_email | email address | Email address that status email should be sent to. map_plan_status_send_report_email should be set to true for email to be sent |
map_plan_status_send_report_email | true, false | If set the true, an email will be sent to the address listed in config map_plan_status_email |
| map_plan_status_send_off_plan_coach_email | true, false | Send off plan emails to coaches after status calc run |
map_plan_status_term_bound_strict | true, false | If set to true, course matches must be term bound to be considered on plan |
map_plan_status_use_substitutable_courses | true, false | Drives the use of substitutable courses when calculating plan status |