File Interaction

The mart_course_offering.file_interaction mart keeps track of file interactions within a course offering. The mart_course_section.file_interaction mart serves the same purpose, except at the course section level.

BQ Prod Dataset Locations

mart_course_offering

mart_course_section

Interactive Mart Dependency Diagram

The following visualization shows the construction of this data mart defined in the UDP marts repository. More information on the repository and diagram can be found on this marts page.

Click here to open interactive the chart.

mart_course_offering.file_interaction

Schema

Mart/Course_offering/file_interaction

Field Name	Type	Description
udp_course_offering_id	INTEGER	The UDP ID of the Course offering associated with the file.
udp_file_id	INTEGER	The UDP ID of the file.
lms_int_file_id	STRING	The LMS internal ID of the file.
num_views	INTEGER	Number of times the file was viewed.
num_distinct_students	INTEGER	Number of unique students who viewed the file.
num_enrolled_students	INTEGER	Number of students enrolled in the Course offering associated with the file.
student_udp_id_array	ARRAY<INTEGER>	An array of the UDP IDs of the enrolled students in the Course offering associated with the file.
instructor_name_array	ARRAY<STRING>	An array of the instructor’s names for the Course offering associated with the file.
instructor_display	STRING	The array of the instructor’s names for the Course offering written as a string.
instructor_email_address_array	ARRAY<STRING>	An array of the instructor emails for the Course offering.
instructor_email_address_display	STRING	An array of the instructor emails for the Course offering written as a comma-separated string.
course_offering_subject	STRING	The subject of the Course offering associated with the file, i.e. MATH.
course_offering_number	STRING	The number of the Course offering, i.e. 310
lms_course_offering_id	STRING	The LMS ID of the Course offering.
course_offering_code	STRING	The code of the Course offering associated with the file, i.e. MATH 310.
academic_term_name	STRING	The name of the academic term, i.e. Fall 2020.
term_start_date	DATE	The start date for the academic term.
term_end_date	DATE	The end date for the academic term.
learner_activity_id	INTEGER	The UDP ID for the learner activity if the file is associated with a learner activity.
learner_activity_title	STRING	The title of the learner activity if the file is associated with a learner activity.
learner_activity_due_date	DATETIME	The due date of the learner activity if the file is associated with a learner activity.
uploader_id	INTEGER	The UDP ID for the person who uploaded the file.
quiz_id	INTEGER	The UDP ID for the quiz if the file is associated with a quiz.
quiz_title	STRING	The title of the quiz if the file is associated with a quiz.
quiz_due_date	DATETIME	The due date of the quiz if the file is associated with a quiz.
content_type	STRING	The type of content that defines the file.
content_sub_type	STRING	The subtype of content that defines the file.
display_name	STRING	The name displayed for the file.
owner_entity_type	STRING	The type of entity that owns the file.
size	NUMERIC	The size of the file in bytes.
created_date	DATETIME	The date the file was created.
unlocked_date	DATETIME	The date the file was unlocked.
updated_date	DATETIME	The date the file was last updated.
accessible_date	DATETIME	The date the file became accessible.
most_recent_version_date	DATETIME	The date of the most recent version of the file.
students_who_viewed_udp_id_array	INTEGER	An array of the UDP IDs of the students who viewed the file.
students_who_did_not_view_udp_id_array	INTEGER	An array of the UDP IDs of the students who did not view the file.
pct_class_viewed	FLOAT	The percentage of students enrolled in the Course offering who viewed the file.

Mart/Course_section/file_interaction

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

Field Name	Type	Description
udp_course_section_id	INTEGER	The UDP ID of the Course section associated with the file.
lms_course_section_id	STRING	The LMS ID of the Course section associated with the file.
sis_course_section_id	STRING	The SIS ID of the Course section associated with the file.

Fields

num_views

The field num_views is the number of times the file has been viewed. We identify that a file has been viewed using events from the learning environment and the file table in the context store. First, we identify the subset of events in which an interaction with an object occurred. A student is considered to have interacted with an object when the object ID for an event is not null. We extract the object ID from the object.id field in the expanded events table in UDP’s event store. The number of times an object has been viewed is the number of events corresponding to the object. For this mart, however, we only want to include student interactions with files, not everything that is considered an object. Therefore, we only include objects that have an ID corresponding to a file ID found in the file table in the context store.

num_distinct_students

The field num_distinct_students is the number of distinct students who viewed the file. The way this is computed is similar to how the num_views field is computed. Again, we identify the subset of events in which an interaction with an object occurred, and only include objects that have an ID corresponding to a file ID found in the context store’s file table. However, for this field, we do not count all the events that correspond to the file, but instead count the number of distinct person IDs found in the events.

num_enrolled_students

The field num_enrolled_students is the number of enrolled students in the Course offering associated with the file. This number is computed using the course_section_enrollment table in UDP’s context store, which keeps track of enrollments in Course sections. We count the number of enrollments with a role of ‘Student’ or ‘Observer’ and a role status that is not ‘Dropped’, ‘Withdrawn’, or ‘Not-enrolled’.

content_type

The field content_type describes the type of content that defines the file. This field is extracted from the content_type field in the file table in the context store. We extract the part of the string before the ‘/’ from the file table’s field to define this field. For instance, if the value in the file table is ‘application/pptx,’ then the value for this mart’s field will be ‘application’. Examples of common content type values are ‘Application’, ‘PDF’, ‘Text’, ‘Video’, ‘Audio’, ‘Image’, ‘MS Word’, and ‘MS Powerpoint’.

content_sub_type

The field content_sub_type describes the subtype of content that defines the file. This field is also extracted from the content_type field in the file table in the context store. For this field, however, we extract the part of the string after the ‘/’ from the file table’s field to define it. So, if the value in the file table is ‘application/pptx’, the value for this mart will be ‘pptx’.

owner_entity_type

The field owner_entity_type defines the type of entity that owns the file. This field is found in the file table from the context store. Some possible values for this field are ‘learner activity’, ‘conversation message’, ‘course offering’, ‘learner group’, ‘quiz’, ‘quiz result’, ‘learner activity result’, and ‘person’.

accessible_date

This field defines the date that the file is made accessible to students. We determine this date based on the unlocked_date field, the date the file was unlocked. If the unlocked_date field is null, we then define the date based on the created_date field, the date the file was created.

most_recent_version_date

This field defines the date the file was most recently updated. This date is determined based on the updated_date field, the date the file was last updated. If the updated_date field is null, then the date is defined by the created_date field, the date the file was created.

students_who_viewed_udp_id_array

The field students_who_viewed_udp_id_array is an array of the UDP IDs of the students who viewed the file. This array is created using the same process as described for the num_views and num_distinct_students fields. First, we identify the subset of events in which an interaction with an object occurred, and only include objects that have an ID corresponding to a file ID found in the context store’s file table. Then, we extract the distinct UDP person IDs from these events into an array that identifies all the students that viewed the file. Since we only want to include students that are enrolled in the course associated with the file, we only keep IDs that are also in the student_udp_id_array field, the array of UDP IDs of students enrolled in the course.

students_who_did_not_view_udp_id_array

The field students_who_did_not_view_udp_id_array is an array of the UDP IDs of the students who are enrolled in the course and did not view the file. This array includes all the UDP IDs that are represented in the student_udp_id_array field, the array of UDP IDs of students enrolled in the course, but not in the students_who_viewed_udp_id_array, the array of UDP IDs of students who viewed the file.

pct_class_viewed

The field pct_class_viewed identifies the percentage of students in the course that viewed the file. This is the proportion of the number of students who viewed the file to the number of students enrolled in the course. This is calculated by dividing the array length of students_who_viewed_udp_id_array by the array length of student_udp_id_array.