Canvas to UCDM

Mapping from Camvas Data to UCDM


Notes about the Canvas Data -> UCDM Mapping

  1. The Canvas Data schema is a denormalized, Kimball-style data warehouse schema. The UCDM schema, by constrast, is a highly normalized relational warehouse schema. Given this, the majority of foreign keys in Canvas Data's dimensional and fact tables will not be mapped to a particular Element in the UCDM. This is important to keep in mind as you explore the mappings.
  2. The id and canvas_id values for each dimensional table are not mapped. This is because, upon ingestion, the UDP replaces source primary keys with UCDM surrogate keys.
  3. All sis_source_id values are not mapped. Again, all source data primary identifiers are collapsed into UCDM surrogate identifiers.

Canvas data tables

assignment_dim

CD Column Entity Element
course_id Learner activity course_offering_id
assignment_group_id Learner activity learner_activity_group_id
title Learner activity title
description Learner activity description
due_at Learner activity due_date
points_possible Learner activity possible_points
grading_type Learner activity grade_type (option set)
submission_types Learner activity allowable_submission_types
workflow_state Learner activity status (option set)
created_at Learner activity created_date
updated_at Learner activity updated_date
peer_review_count Learner activity peer_review_count
peer_reviews_due_at Learner activity peer_reviews_due_date
peer_reviews Learner activity has_peer_reviews
automatic_peer_reviews Learner activity is_automatic_peer_reviews
all_day Learner activity is_all_day
all_day_date Learner activity all_day_date
grade_group_students_individually Learner activity is_grade_group_students_individually
anonymous_peer_reviews Learner activity is_anonymous_peer_reviews
muted (deprecated) Learner activity is_hidden (deprecated)
position Learner activity position
visibility Learner activity visibility (option set)
peer_reviews_assigned Learner activity has_peer_reviews_assigned
top

assignment_fact

CD Column Entity Element
points_possible Learner activity points_possible
top

assignment_group_dim

CD Column Entity Element
course_id Learner activity group course_offering_id
name Learner activity group name
default_assignment_name Learner activity group default_learner_activity_name
workflow_state Learner activity group status (option set)
position Learner activity group position
created_at Learner activity group created_date
updated_at Learner activity group updated_date
top

assignment_group_fact

CD Column Entity Element
group_weight Learner activity group group_weight
top

assignment_group_rule_dim

CD Column Entity Element
drop_lowest Learner activity group drop_lowest_amount
drop_highest Learner activity group drop_highest_amount
top

assignment_group_score_dim

CD Column Entity Element
assignment_group_id Learner activity group grade learner_activity_group_id
created_at Learner activity group grade created_date
updated_at Learner activity group grade updated_date
workflow_state Learner activity group grade status (option set)
top

assignment_group_score_fact

CD Column Entity Element
current_score Learner activity group grade current_score
final_score Learner activity group grade final_score
muted_current_score Learner activity group grade hidden_current_score
muted_final_score Learner activity group grade hidden_final_score
top

assignment_override_dim

CD Column Entity Element
assignment_id Learner activity override learner_activity_id
course_section_id Learner activity override course_section_id
group_id Learner activity override learner_group_id
quiz_id Learner activity override quiz_id
all_day Learner activity override all_day_type (option set)
all_day_date Learner activity override all_day_date
assignment_version Learner activity override learner_activity_version
created_at Learner activity override created_date
due_at Learner activity override due_date
due_at_overridden Learner activity override override_due_date
set_type Learner activity override type (option set)
title Learner activity override title
updated_at Learner activity override updated_date
quiz_version Learner activity override quiz_version
workflow_state Learner activity override status (option set)
top

assignment_override_user_fact

CD Column Entity Element
user_id Learner activity override person_id
top

assignment_rule_dim

CD Column Entity Element
drop_rule Learner activity drop_rule
top

conference_dim

CD Column Entity Element
group_id Participant session learner_group_id
course_id Participant session course_offering_id
title Participant session title
conference_type Participant session type
start_at Participant session scheduled_start_time
started_at Participant session actual_start_time
end_at Participant session scheduled_end_time
ended_at Participant session actual_end_time
created_at Participant session created_date
updated_at Participant session updated_date
top

conference_fact

CD Column Entity Element
duration Participant session duration
top

conference_participant_dim

CD Column Entity Element
user_id Participant session role person_id
conference_id Participant session role participant_session_id
participation_type Participant session role role (option set)
created_at Participant session role created_date
updated_at Participant session role updated_date
top

conversation_dim

CD Column Entity Element
course_id Conversation course_offering_id
group_id Conversation learner_group_id
has_attachments Conversation has_attachments
has_media_objects Conversation has_media_objects
subject Conversation subject
top

conversation_message_dim

CD Column Entity Element
conversation_id Conversation message conversation_id
author_id Conversation message author_id
created_at Conversation message created_date
generated Conversation message is_system_generated
has_attachments Conversation message has_attachments
has_media_objects Conversation message has_media_objects
body Conversation message body
top

conversation_message_participant_fact

CD Column Entity Element
course_id Conversation message course_offering_id
group_id Conversation message learner_group_id
message_size_bytes Conversation message message_size_bytes
message_character_count Conversation message message_character_count
message_word_count Conversation message message_word_count
message_line_count Conversation message message_line_count
top

course_dim

CD Column Entity Element
name Course offering title
workflow_state Course offering status (option set)
created_at Course offering created_date
publicly_visible Course offering is_public
syllabus_body Course offering syllabus_content
top

course_score_dim

CD Column Entity Element
created_at Course grade created_date
updated_at Course grade updated_date
top

course_score_fact

CD Column Entity Element
current_score Course grade current_score
muted_current_score Course grade hidden_current_score
muted_final_score Course grade hidden_final_score
top

course_section_dim

CD Column Entity Element
created_at Course section created_date
updated_at Course section updated_date
top

discussion_entry_dim

CD Column Entity Element
message Discussion entry body
workflow_state Discussion entry status (option set)
depth Discussion entry position
created_at Discussion entry created_date
updated_at Discussion entry updated_date
deleted_at Discussion entry deleted_date
top

discussion_entry_fact

CD Column Entity Element
user_id Discussion entry person_id
topic_id Discussion entry discussion_id
message_length Discussion entry message_length
top

discussion_topic_dim

CD Column Entity Element
group_id Discussion learner_group_id
title Discussion title
message Discussion body
type Discussion type (option set)
workflow_state Discussion status (option set)
last_reply_at Discussion last_reply_date
created_at Discussion created_date
updated_at Discussion updated_date
delayed_post_at Discussion delayed_post_date
posted_at Discussion posted_date
deleted_at Discussion deleted_date
discussion_type Discussion discussion_type (option set)
pinned Discussion is_pinned
locked Discussion is_locked
top

discussion_topic_fact

CD Column Entity Element
course_id Discussion course_offering_id
assignment_id Discussion learner_activity_id
message_length Discussion message_length
top

enrollment_dim

CD Column Entity Element
user_id Course section enrollment person_id
course_section_id Course section enrollment course_section_id
type Course section enrollment role (option set)
workflow_state Course section enrollment role_status (option set)
workflow_state Course section enrollment enrollment_status (option set)
created_at Course section enrollment created_date
updated_at Course section enrollment updated_date
start_at (deprecated) Course section enrollment entry_date (deprecated)
end_at Course section enrollment exit_date
completed_at Course section enrollment completed_date
top

enrollment_fact

CD Column Entity Element
user_id Grading period grade person_id
course_section_id Grading period grade course_section_id
top

enrollment_term_dim

CD Column Entity Element
name Academic term name
top

file_dim

CD Column Entity Element
uploader_id File uploader_id
assignment_id File learner_activity_id
submission_id File learner_activity_result_id
quiz_id File quiz_id
quiz_submission_id File quiz_result_id
conversation_message_id (deprecated) File conversation_message_id (deprecated)
course_id File course_offering_id
group_id File learner_group_id
replacement_file_id File replacement_file_id
root_file_id File root_file_id
display_name File display_name
owner_entity_type File owner_entity_type
content_type File content_type
file_state File status (option set)
could_be_locked File can_be_locked
lock_at File locked_date
unlock_at File unlocked_date
viewed_at File viewed_date
created_at File created_date
updated_at File updated_date
deleted_at File deleted_date
user_id File owner_id
top

file_fact

CD Column Entity Element
assignment_group_id File learner_activity_group_id
conversation_id (deprecated) File conversation_id (deprecated)
conversation_message_author_id (deprecated) File conversation_person_id (deprecated)
wiki_id File wiki_id
size File size
top

grading_period_dim

CD Column Entity Element
grading_period_group_id Grading period grading_period_group_id
title Grading period title
close_date Grading period close_date
created_at Grading period created_date
end_date Grading period end_date
start_date Grading period start_date
updated_at Grading period updated_date
workflow_state Grading period status
top

grading_period_fact

CD Column Entity Element
weight Grading period weight
top

grading_period_group_dim

CD Column Entity Element
course_id Grading period group course_offering_id
created_at Grading period group created_date
title Grading period group title
updated_at Grading period group updated_date
workflow_state Grading period group status (option set)
top

grading_period_score_dim

CD Column Entity Element
created_at Grading period grade created_date
updated_at Grading period grade updated_date
workflow_state Grading period grade status (option set)
top

grading_period_score_fact

CD Column Entity Element
grading_period_id Grading period grade grading_period_id
grading_period_group_id Grading period grade grading_period_group_id
current_score Grading period grade current_score
final_score Grading period grade final_score
muted_current_score Grading period grade hidden_current_score
muted_final_score Grading period grade hidden_final_score
top

group_dim

CD Column Entity Element
name Learner group name
description Learner group description
created_at Learner group created_date
updated_at Learner group updated_date
deleted_at Learner group deleted_date
is_public Learner group is_public_contents
workflow_state Learner group status (option set)
context_type Learner group context_type
category Learner group category
join_level Learner group join_level
default_view Learner group default_view
top

group_fact

CD Column Entity Element
parent_course_id Learner group course_offering_id
max_membership Learner group max_membership
storage_quota Learner group storage_quota
top

group_membership_dim

CD Column Entity Element
group_id Learner group membership learner_group_id
moderator Learner group membership is_moderator
workflow_state Learner group membership status
created_at Learner group membership created_date
updated_at Learner group membership updated_date
top

group_membership_fact

CD Column Entity Element
user_id Learner group membership person_id
top

learning_outcome_dim

CD Column Entity Element
course_id Learning outcome course_offering_id
short_description Learning outcome short_desc
description Learning outcome description
workflow_state Learning outcome status
created_at Learning outcome created_date
updated_at Learning outcome updated_date
vendor_guid Learning outcome vendor_guid
display_name Learning outcome display_name
calculation_method Learning outcome calculation_method
calculation_int Learning outcome calculation_int
top

learning_outcome_fact

CD Column Entity Element
enrollment_term_id Learning outcome academic_term_id
mastery_points Learning outcome mastery_points
top

learning_outcome_group_dim

CD Column Entity Element
course_id Learning outcome group course_offering_id
title Learning outcome group title
workflow_state Learning outcome group status
description Learning outcome group description
created_at Learning outcome group created_date
updated_at Learning outcome group updated_date
vendor_guid Learning outcome group vendor_guid
top

learning_outcome_group_fact

CD Column Entity Element
enrollment_term_id Learning outcome group academic_term_id
top

learning_outcome_question_result_dim

CD Column Entity Element
learning_outcome_id Learning outcome item response learning_outcome_id
assessment_question_id Learning outcome item response quiz_item_id
title Learning outcome item response title
created_at Learning outcome item response created_date
updated_at Learning outcome item response updated_date
assessed_at Learning outcome item response assessed_date
submitted_at Learning outcome item response submitted_date
top

learning_outcome_question_result_fact

CD Column Entity Element
user_id Learning outcome item response person_id
course_id Learning outcome item response course_offering_id
enrollment_term_id Learning outcome item response academic_term_id
quiz_id Learning outcome item response quiz_id
mastery Learning outcome item response has_mastery
score Learning outcome item response score
attempts Learning outcome item response number_attempts
possible Learning outcome item response points_possible
original_score Learning outcome item response original_score
original_mastery Learning outcome item response has_original_mastery
percent Learning outcome item response percent
top

learning_outcome_result_dim

CD Column Entity Element
course_id Learning outcome result course_offering_id
assignment_id Learning outcome result learner_activity_id
quiz_id Learning outcome result quiz_id
learning_outcome_id Learning outcome result learning_outcome_id
user_id Learning outcome result person_id
created_at Learning outcome result created_date
updated_at Learning outcome result updated_date
assessed_at Learning outcome result assessed_date
submitted_at Learning outcome result submitted_date
hide_points Learning outcome result is_hide_points
hidden Learning outcome result is_hidden
top

learning_outcome_result_fact

CD Column Entity Element
enrollment_term_id Learning outcome result academic_term_id
mastery Learning outcome result has_mastery
score Learning outcome result score
attempts Learning outcome result attempts
possible Learning outcome result points_possible
original_score Learning outcome result original_score
original_possible Learning outcome result original_possible
original_mastery Learning outcome result has_original_mastery
percent Learning outcome result percent
top

learning_outcome_rubric_criterion_dim

CD Column Entity Element
learning_outcome_id Learning outcome rubric criteria learning_outcome_id
description Learning outcome rubric criteria description
top

learning_outcome_rubric_criterion_fact

CD Column Entity Element
course_id Learning outcome rubric criteria course_offering_id
enrollment_term_id Learning outcome rubric criteria academic_term_id
points Learning outcome rubric criteria points_possible
top

module_completion_requirement_dim

CD Column Entity Element
requirement_type Module item completion_type
top

module_dim

CD Column Entity Element
course_id Module course_offering_id
require_sequential_progress Module sequential
workflow_state Module status (option set)
position Module position
name Module name
created_at Module created_date
deleted_at Module deleted_date
unlock_at Module unlocked_date
updated_at Module updated_date
top

module_item_dim

CD Column Entity Element
assignment_id Module item learner_activity_id
course_id Module item course_offering_id
discussion_topic_id Module item discussion_id
file_id Module item file_id
module_id Module item module_id
quiz_id Module item quiz_id
wiki_page_id Module item wiki_page_id
content_type Module item type (option set)
workflow_state Module item status (option set)
position Module item position
title Module item title
url Module item url
created_at Module item created_date
updated_at Module item updated_date
top

module_item_fact

CD Column Entity Element
user_id Module item person_id
top

module_prerequisite_dim

CD Column Entity Element
prerequisite_module_id Module prerequisite_module_id
top

module_progression_completion_requirement_dim

CD Column Entity Element
module_progression_id Module item completion module_progression_id
module_item_id Module item completion module_item_id
requirement_type Module item completion requirement_type
completion_status Module item completion completion_status
top

module_progression_completion_requirement_fact

CD Column Entity Element
min_score Module item completion min_score
score Module item completion score
top

module_progression_dim

CD Column Entity Element
module_id Module progression module_id
user_id Module progression person_id
collapsed Module progression is_collapsed
is_current Module progression is_current
workflow_state Module progression status (option set)
current_position Module progression current_position
lock_version Module progression lock_version
created_at Module progression created_date
completed_at Module progression completed_date
evaluated_at Module progression evaluated_date
updated_at Module progression updated_date
top

quiz_dim

CD Column Entity Element
course_id Quiz course_offering_id
assignment_id Quiz learner_activity_id
name Quiz title
points_possible Quiz points_possible
description Quiz description
quiz_type Quiz type (option set)
workflow_state Quiz status (option set)
scoring_policy Quiz scoring_policy (option set)
anonymous_submissions Quiz is_allow_anonymous_submissions
display_questions Quiz is_display_multiple_questions
answer_display_order Quiz is_shuffled_answer_display_order
go_back_to_previous_question Quiz is_allow_go_back_to_previous_question
could_be_locked Quiz is_can_be_locked
browser_lockdown Quiz is_browser_lockdown
ip_filter Quiz ipfilter
show_results Quiz show_results
created_at Quiz created_date
updated_at Quiz updated_date
published_at Quiz published_date
unlock_at Quiz unlocked_date
lock_at Quiz locked_date
due_at Quiz due_date
deleted_at Quiz deleted_date
top

quiz_fact

CD Column Entity Element
time_limit Quiz time_limit
allowed_attempts Quiz allowed_attempts
question_count Quiz quiz_item_count
top

quiz_question_answer_dim

CD Column Entity Element
quiz_question_id Quiz item response quiz_item_id
text Quiz item response body
html Quiz item response html
comments Quiz item response comments
text_after_answers Quiz item response text_after_answers
answer_match_left Quiz item response answer_match_left
answer_match_right Quiz item response answer_match_right
matching_answer_incorrect_matches Quiz item response incorrect_matches
numerical_answer_type Quiz item response numeric_answer
blank_id Quiz item response blank
exact Quiz item response exact
margin Quiz item response margin
starting_range Quiz item response starting_range
ending_range Quiz item response ending_range
top

quiz_question_answer_fact

CD Column Entity Element
weight Quiz item response weight
top

quiz_question_dim

CD Column Entity Element
quiz_id Quiz item quiz_id
quiz_question_group_id Quiz item quiz_item_group_id
position Quiz item position
workflow_state Quiz item status
created_at Quiz item created_date
updated_at Quiz item updated_date
name Quiz item name
question_type Quiz item quiz_item_type (option set)
question_text Quiz item body
regrade_option Quiz item regrade_option
correct_comments Quiz item correct_comments
incorrect_comments Quiz item incorrect_comments
neutral_comments Quiz item neutral_comments
top

quiz_question_fact

CD Column Entity Element
assignment_id Quiz item learner_activity_id
points_possible Quiz item points_possible
top

quiz_question_group_dim

CD Column Entity Element
quiz_id Quiz item group quiz_id
name Quiz item group name
position Quiz item group position
created_at Quiz item group created_date
updated_at Quiz item group updated_date
top

quiz_question_group_fact

CD Column Entity Element
pick_count Quiz item group pick_count
question_points Quiz item group quiz_item_points
top

quiz_submission_dim

CD Column Entity Element
quiz_id Quiz result quiz_id
submission_id Quiz result learner_activity_result_id
user_id Quiz result person_id
workflow_state Quiz result status (option set)
quiz_state_during_submission Quiz result state_in_submission (option set)
submission_scoring_policy Quiz result submission_scoring_policy (option set)
submission_source Quiz result submission_source
has_seen_results Quiz result is_student_viewed
created_at Quiz result created_date
updated_at Quiz result updated_date
started_at Quiz result started_date
finished_at Quiz result finished_date
due_at Quiz result due_date
top

quiz_submission_fact

CD Column Entity Element
assignment_id Quiz result learner_activity_id
score Quiz result score
kept_score Quiz result kept_score
date Quiz result result_date
quiz_points_possible Quiz result points_possible
score_before_regrade Quiz result score_before_regrade
fudge_points Quiz result fudge_points
total_attempts Quiz result total_attempts
extra_attempts Quiz result extra_attempts
extra_time Quiz result extra_time
time_taken Quiz result time_taken
top

role_dim

CD Column Entity Element
name Role name
base_role_type Role base_role_type (option set)
workflow_state Role status (option set)
created_at Role created_date
updated_at Role updated_date
deleted_at (deprecated) Role deleted_date (deprecated)
top

submission_comment_dim

CD Column Entity Element
submission_id Annotation learner_activity_result_id
author_name Annotation author_name
anonymous Annotation is_anonymous
comment Annotation body_value
hidden Annotation is_hidden
teacher_only_comment Annotation has_teacher_only_comments
created_at Annotation created_date
updated_at Annotation updated_date
top

submission_comment_fact

CD Column Entity Element
message_size_bytes Annotation message_size_bytes
message_character_count Annotation message_character_count
message_word_count Annotation message_word_count
message_line_count Annotation message_line_count
top

submission_dim

CD Column Entity Element
process_attempts (deprecated) Learner activity result process_attempts (deprecated)
body Learner activity result body
url Learner activity result url
grade Learner activity result grade
submitted_at Learner activity result response_date
submission_type Learner activity result type (option set)
workflow_state Learner activity result grading_status (option set)
created_at Learner activity result created_date
updated_at Learner activity result updated_date
processed Learner activity result is_processed
grade_matches_current_submission Learner activity result gradebook_status (option set)
published_grade Learner activity result published_grade
graded_at Learner activity result graded_date
has_rubric_assessment (deprecated) Learner activity result has_rubric_assessment (deprecated)
attempt Learner activity result attempt
has_admin_comment (deprecated) Learner activity result has_admin_comment (deprecated)
assignment_id Learner activity result learner_activity_id
excused Learner activity result is_excused
graded_anonymously Learner activity result is_grade_anonymously
grader_id Learner activity result grader_id
group_id Learner activity result learner_group_id
quiz_submission_id Learner activity result quiz_result_id
user_id Learner activity result person_id
grade_state Learner activity result grade_state
posted_at Learner activity result posted_at
top

submission_fact

CD Column Entity Element
quiz_id Learner activity result quiz_id
score Learner activity result score
published_score Learner activity result published_score
submission_comments_count Learner activity result submission_comments_count
top

user_dim

CD Column Entity Element
top

wiki_dim

CD Column Entity Element
parent_type Wiki owner_entity
title Wiki title
created_at Wiki created_date
updated_at Wiki updated_date
front_page_url Wiki front_page_url
has_no_front_page Wiki has_no_front_page
top

wiki_page_dim

CD Column Entity Element
title Wiki page title
body Wiki page body
workflow_state Wiki page status (option set)
created_at Wiki page created_date
url Wiki page url
protected_editing Wiki page is_protected_editing
editing_roles Wiki page editing_roles
revised_at Wiki page revised_date
could_be_locked Wiki page could_be_locked
top

wiki_page_fact

CD Column Entity Element
wiki_id Wiki page wiki_id
user_id Wiki page person_id
wiki_page_comments_count (deprecated) Wiki page comments_count (deprecated)
view_count (deprecated) Wiki page view_count (deprecated)
top