Overview
Phase 1 Map Status Calculation will feature a cron job that calculate plan statuses for all active plans as detailed in the Phase 1 requirements doc (SSP v2.4 MAP Status Calculation- Phase 1).
MAP Plan Status Calculation Implementation Details - Phase 1
Map Plan Status Calculation Flow Chart for Phase 1
Map Plan Status Schema for Phase 1
Map Status Data Dictionary - Phase 1
MAP_STATUS_REPORT (Process will produce one row per active plan.)
| Column | Type | Notes |
|---|---|---|
| id | uuid | primary (technical) key, unique identifier |
| plan_status | varchar(enum) | originally proposed as a boolean, I think an enum here will give us more flexability |
| note | varchar | high level detail about the status calculation |
| plan_id | uuid | foreign key to MAP_PLAN(id) |
| person_id | uuid | foreign key to PERSON(id) - student |
| modified_by | uuid | id of person modifying reference |
| created_by | uuid | id of person creating reference |
| modified_date | date | date modified |
| created_date | date | date created |
| object_status | enum | standard object status |
Comments
- plan_status: An enum would be better. That would support the multiple statuses that will be added in phase 2 (on track, off track, etc)
MAP_STATUS_REPORT_COURSE_DETAILS (Process will produce one row per anomaly per active plan. An errorless plan will produce zero rows.)
| Column | Type | Notes |
|---|---|---|
| id | uuid | primary (technical) key, unique identifier |
| report_id | uuid | foreign key to MAP_STATUS_REPORT(id) |
| term_code | varchar | soft reference to EXTERNAL_TERM(code) |
| formatted_course | varchar | soft reference to EXTERNAL_COURSE(formatted_course) |
| course_code | uuid | soft reference to EXTERNAL_COURSE(code) |
| anomaly_code | varchar | code that identifies the specific course level violation |
| anomaly_note | varchar | detailed information about specific course level violation |
| modified_by | uuid | id of person modifying reference |
| created_by | uuid | id of person creating reference |
| modified_date | date | date modified |
| created_date | date | date created |
| object_status | enum | standard object status |
MAP_STATUS_REPORT_TERM_DETAILS (Process will produce one row per plan_course_term per active plan.)
| Column | Type | Notes |
|---|---|---|
| id | uuid | primary (technical) key, unique identifier |
| report_id | uuid | foreign key to MAP_STATUS_REPORT(id) |
| term_code | varchar | soft reference to EXTERNAL_TERM(code) |
| anomaly_code | varchar | code that identifies the specific course level violation |
| anomaly_note | varchar | detailed information about specific course level violation |
| term_status | varchar(enum) | the on/off plan status of the term |
| modified_by | uuid | id of person modifying reference |
| created_by | uuid | id of person creating reference |
| modified_date | date | date modified |
| created_date | date | date created |
| object_status | enum | standard object status |
Comments
- course_code: This may or may not be applicable here. We are seeing more and more schools loading data into external_course with the same formatted_course but different course_id values. This represents different versions of the course and is completely legitimate in the field. Many schools start with this concept because the SIS table structures require different course ids for each term the course is offered. However, different course ids in MAP are important when the course has a different definition (title, course hours, etc).
New Configurations for Phase 1
| name | possible values | purpose |
|---|---|---|
| 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) or blank | In addition to the static matching criteria (term_code, formatted_course, and course_code), implementors have the option to add additional criteria. COURSE_TITLE will match PLAN_COURSE.COURSE_TITLE to EXTERNAL_STUDENT_TRANSCRIPT_COURSE.TITLE and CREDIT_HOURS will match PLAN_COURSE.CREDIT_HOURS with EXTERNAL_STUDENT_TRANSCRIPT_COURSE.CREDIT_EARNED |
| map_plan_status_use_current_term_grades | true,false | Some schools definitely want the ability to use current term grades in plan status calculation but it's also conceivable that school may not want to consider grades when looking at the current term. If set to false, current terms would be handled as a future term where simply a transcript match (regardless of the grade) will only be considered. |
Comments
passing_grades: Dan also questioned the need for more complexity. Maybe I'm missing something that you both are seeing. The basic functionality is to check to see if the grade the student was awarded on their transcript is a passing grade.
Matching criteria
Different schools may have their course/transcript data organized differently. This means they may want different criteria to drive the matching logic between plan and transcript. We will assume that three criteria are non-negotiable and static; term_code, formatted_course, and course_code. In addition to the static matching criteria, implementors have the option to add additional criteria. COURSE_TITLE will match PLAN_COURSE.COURSE_TITLE to EXTERNAL_STUDENT_TRANSCRIPT_COURSE.TITLE and CREDIT_HOURS will match PLAN_COURSE.CREDIT_HOURS with EXTERNAL_STUDENT_TRANSCRIPT_COURSE.CREDIT_EARNED.
Implementation Concerns for Phase 1
- System load and memory footprint will be big concerns for the calculation process. We don't want to load all active plans into memory at once. Implementors may not want to have cron job run during business hours as it could put load on the web server and database.
- Configuration map_plan_status_cutoff_term_code is a maintenance concern. Will most likely have to be updated every term. This must be clear to implementors.
- map_plan_status_passing_grades may not be very flexible enough for a lot of schools. We may want to explore a hierarchical configuration approach.
Open Questions
- Do we want to store one status per plan? If we want to store multiple statuses per plan, that may have performance and storage concerns.
- JME: During the original discussion we agreed for phase 1 that we would record only the current plan status and off plan reason. The one per is actually buried in the phase 1 specs in the Operational Table description.