Daily Course Grade Record
Last updated
Last updated
The mart_course_offering.daily_course_grade_record presents all LMS course grade records beginning from the current term, updated daily. This mart allows users to track how students' grades in the LMS change throughout a term.
The mart_course_section.daily_course_grade_record mart serves the same purpose, except at the course section level.
The Canvas Data 2 table scores used to populate the UDP context_store_entity.course_grade table only presents the latest snapshot of grades for students in Canvas. This means that the scores populated from Canvas in the course_grade entity, specifically the le_current_score and le_final_score fields, are the latest scores for students enrolled in the course. No history of the students' course grades is maintained, and there is no way to determine how their grades have changed throughout the term from the course_grade entity alone.
The purpose of the daily_course_grade_record mart is to maintain the history of students' course grades in Canvas. This will allow users to track students' grade changes in courses throughout a term. This archive is maintained by a daily append of course_grade rows for the current term to the daily_course_grade_record mart. Users can reference the run_date and run_time fields in the mart for the date the row was appended to the mart.
mart_course_offering
mart_course_section
The following visualization shows the construction of this data mart defined in the repository. More information on the repository and diagram can be found on .
udp_person_id
INTEGER
The UDP ID of the student.
lms_person_id
STRING
The LMS external ID of the student.
udp_course_offering_id
INTEGER
The UDP ID of the course offering.
lms_course_offering_id
STRING
The LMS external ID of the course offering.
udp_academic_term_id
INTEGER
The UDP ID of the academic term.
lms_academic_term_id
STRING
The LMS external ID of the academic term.
term_name
STRING
The name of the academic term, i.e. Fall 2024.
term_type
STRING
The type of the academic term, i.e. Fall.
term_year
INTEGER
The year of the academic term, i.e. 2024.
term_begin_date
DATE
The date the academic term begins.
term_end_date
DATE
The date the academic term ends.
created_date
DATETIME
The time the course grade record was created.
updated_date
DATETIME
The time the course grade record was last updated.
le_final_score_status
STRING
The status of the final score in the LMS.
le_current_score
NUMERIC
The current score in the LMS.
le_final_score
NUMERIC
The final score in the LMS.
le_hidden_current_score
NUMERIC
The current score in the LMS including hidden assignments.
le_hidden_final_score
NUMERIC
The final score in the LMS including hidden assignments.
run_date
DATE
The date the record was added to the mart.
run_time
DATETIME
The time the record was added to the mart.
The course section mart includes all the fields found in the course offering mart, along with three extra fields.
udp_course_section_id
INTEGER
The UDP ID of the course section.
lms_course_section_id
STRING
The LMS ID of the course section.
The daily_course_grade_record marts are updated daily. Every day, a script appends records from the context_store_entity.course_grade entity to the daily_course_grade_record marts. Only records for courses in the current term are added to the marts. The logic to filter out records for prior terms is:
Even if there has not been a change in a student's grade, new grade records are appended to the marts daily. Grade records are not added to the marts only if the status of the final score in the LMS is deleted or all of the LMS score fields are missing. The logic to filter such records out is:
