# Daily Course Grade Record

The *mart\_course\_offering.daily\_course\_grade\_record* presents all LMS course grade records beginning from the current term or session, 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.

## Purpose  <a href="#interactionsessions-purposeandpotentialuse-cases" id="interactionsessions-purposeandpotentialuse-cases"></a>

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.&#x20;

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 or session. This archive is maintained by a daily append of *course\_grade* rows for the current term or session 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.&#x20;

## BQ Prod Dataset Locations <a href="#fileinteraction-bqproddatasetlocations" id="fileinteraction-bqproddatasetlocations"></a>

mart\_course\_offering

mart\_course\_section

## Interactive Mart Dependency Diagram <a href="#fileinteraction-interactivemartdependencydiagram" id="fileinteraction-interactivemartdependencydiagram"></a>

The following visualization shows the construction of this data mart defined in the [UDP marts](https://gitlab.com/unizin-community/unizin-data-platform/marts) repository. More information on the repository and diagram can be found on [this marts page](https://resources.unizin.org/products/data-and-analytics/unizin-data-platform/data-stores/data-marts).

{% hint style="info" %}
[Click here to open the interactive chart. ](https://assets.public.unizin.org/udp-marts/mart/mart__course_offering__daily_course_grade_record.svg)
{% endhint %}

<figure><img src="https://3709019308-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FKYwtHNGgdPXS3PWAlZUr%2Fuploads%2F8L5VVcehPXyOPeE8oiyW%2Fmart__course_offering__daily_course_grade_record.svg?alt=media&#x26;token=9b73deb2-70fa-4b8f-bf1d-f0e7e12f90b1" alt=""><figcaption><p>mart_course_offering.daily_course_grade_record</p></figcaption></figure>

## Schema <a href="#fileinteraction-schema" id="fileinteraction-schema"></a>

### **Mart/Course\_offering/daily\_course\_grade\_record**

<table data-full-width="true"><thead><tr><th width="365.33333333333337">Field Name</th><th width="177">Type</th><th>Description</th></tr></thead><tbody><tr><td>udp_person_id</td><td>INTEGER</td><td>The UDP ID of the student.</td></tr><tr><td>lms_person_id</td><td>STRING</td><td>The LMS external ID of the student.</td></tr><tr><td>udp_course_offering_id</td><td>INTEGER</td><td>The UDP ID of the course offering.</td></tr><tr><td>lms_course_offering_id</td><td>STRING</td><td>The LMS external ID of the course offering.</td></tr><tr><td>udp_academic_term_id</td><td>INTEGER</td><td>The UDP ID of the academic term.</td></tr><tr><td>lms_academic_term_id</td><td>STRING</td><td>The LMS external ID of the academic term. </td></tr><tr><td>term_name</td><td>STRING</td><td>The name of the academic term, i.e. Fall 2024. </td></tr><tr><td>term_type</td><td>STRING</td><td>The type of the academic term, i.e. Fall. </td></tr><tr><td>term_year</td><td>INTEGER</td><td>The year of the academic term, i.e. 2024. </td></tr><tr><td>term_begin_date</td><td>DATE</td><td>The date the academic term begins. </td></tr><tr><td>term_end_date</td><td>DATE</td><td>The date the academic term ends. </td></tr><tr><td>created_date</td><td>DATETIME</td><td>The time the course grade record was created. </td></tr><tr><td>updated_date</td><td>DATETIME</td><td>The time the course grade record was last updated. </td></tr><tr><td>le_final_score_status</td><td>STRING</td><td>The status of the final score in the LMS. </td></tr><tr><td>le_current_score</td><td>NUMERIC</td><td>The current score in the LMS. </td></tr><tr><td>le_final_score</td><td>NUMERIC</td><td>The final score in the LMS. </td></tr><tr><td>le_hidden_current_score</td><td>NUMERIC</td><td>The current score in the LMS including hidden assignments. </td></tr><tr><td>le_hidden_final_score</td><td>NUMERIC</td><td>The final score in the LMS including hidden assignments.  </td></tr><tr><td>run_date</td><td>DATE</td><td>The date the record was added to the mart.</td></tr><tr><td>run_time</td><td>DATETIME</td><td>The time the record was added to the mart. </td></tr></tbody></table>

### **Mart/Course\_section/daily\_course\_grade\_record**

The course section mart includes all the fields found in the course offering mart, along with three extra fields.

<table data-full-width="true"><thead><tr><th width="283.33333333333337">Field Name</th><th width="193">Type</th><th>Description</th></tr></thead><tbody><tr><td>udp_course_section_id</td><td>INTEGER</td><td>The UDP ID of the course section.</td></tr><tr><td>lms_course_section_id</td><td>STRING</td><td>The LMS ID of the course section.</td></tr></tbody></table>

## Logic <a href="#fileinteraction-fields" id="fileinteraction-fields"></a>

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 or session are added to the marts. The logic to filter out records for prior terms or sessions is:

```sql
  COALESCE(act.term_begin_date,acs.instruction_begin_date) <= CURRENT_DATE()
  and COALESCE(act.term_end_date,acs.instruction_end_date) >= CURRENT_DATE()
```

&#x20;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:

```sql
  COALESCE(le_current_score,le_hidden_current_score,le_final_score,le_hidden_final_score) IS NOT NULL
  and le_final_score_status is distinct from 'deleted'
```

&#x20;