cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
francesca_gatch
Community Member

Wrong enrollment type

Jump to solution
From downloading Canvas Data, we see some roles that seem to be tagged incorrectly.
For example from Canvas data, Canvas ID 145 belongs to Maria. On Canvas site, she is registered as a Teacher.
293215_names-redacted.png
^ (Note from the Community Team: This image has been edited to protect personal information) ^
293190_Screen Shot 2018-10-08 at 2.53.01 PM.png
But from checking the downloaded enrollment_dim, it shows that the Enrollment Type to that Canvas ID is Student Enrollment.
293189_Screen Shot 2018-10-08 at 2.51.21 PM.png
We want to ask if this is the correct data set to see the roles (Teacher/Student)? Or how can we correct this? Thanks
1 Solution

Accepted Solutions
James
Community Champion

 @francesca_gatch ,

A quick glance at your screen shots suggest that you're confusing IDs. The fact that the same ID appears in multiple places only helps reinforce the misunderstanding.

Almost every dimension table has its own ID column that refers the ID for that table. That's a primary key and every row gets a unique value. When it's inside the user_dim table, id is the user_id; when it's inside the enrollment_dim table, id is the enrollment_id; when it's inside the course_dim table, id is the course_id.

When an ID is needed from a different table, it's prefixed with the table name. When enrollment_dim needs to refer to a user ID, it is called user_id. When enrollment_dim needs to join up with the course table, then it has a field called called course_id. When the name of the table is omitted, then id is for the current table.

In the first table, which looks like user_dim, the id and canvas_id fields are for the user. That is, Maria is canvas_user_id 145. In the enrollment_dim table, the id and canvas_id fields are for the enrollment. The 145 there is NOT referring to a user, but an enrollment, and it's not Maria. There are some other fields in enrollment_dim that are going to explain things, but they're not showing.

enrollment_dim.course_id is joined with the course_dim.id field and you can use that to figure out what course that enrollment is in.

enrollment_dim.user_id is joined with the user_dim.id field and you can use that to figure out what user we're talking about.

Here's some SQL (MySQL flavored) that will show all of the enrollments and the courses for Maria.

SELECT 
  u.name AS user_name,
  c.name AS course_name,
  e.type AS roll_type,
  u.canvas_id AS user_id,
  c.canvas_id AS course_id
FROM enrollment_dim e
JOIN user_dim u ON (e.user_id = u.id)
JOIN course_dim c ON (e.course_id = c.id)
WHERE e.workflow_state = 'active'
  AND c.workflow_state = 'available'
  AND u.canvas_id = 145;

View solution in original post

4 Replies
chofer
Community Coach
Community Coach

Hello  @francesca_gatch ...

I'm not sure of an answer for you, but I wanted to let you know that I'll share your question with the https://community.canvaslms.com/groups/big-data group here in the Canvas Community in hopes that your question will get some additional exposure.  If you are not yet following this group, please use the link that I have provided, and then click on the "Follow" button at the top right corner of the page.  Also, near that button is a link for "Actions".  Click that, and then click on "Join group".  I hope this will be helpful to you, Francesca.  Good luck!

James
Community Champion

 @francesca_gatch ,

A quick glance at your screen shots suggest that you're confusing IDs. The fact that the same ID appears in multiple places only helps reinforce the misunderstanding.

Almost every dimension table has its own ID column that refers the ID for that table. That's a primary key and every row gets a unique value. When it's inside the user_dim table, id is the user_id; when it's inside the enrollment_dim table, id is the enrollment_id; when it's inside the course_dim table, id is the course_id.

When an ID is needed from a different table, it's prefixed with the table name. When enrollment_dim needs to refer to a user ID, it is called user_id. When enrollment_dim needs to join up with the course table, then it has a field called called course_id. When the name of the table is omitted, then id is for the current table.

In the first table, which looks like user_dim, the id and canvas_id fields are for the user. That is, Maria is canvas_user_id 145. In the enrollment_dim table, the id and canvas_id fields are for the enrollment. The 145 there is NOT referring to a user, but an enrollment, and it's not Maria. There are some other fields in enrollment_dim that are going to explain things, but they're not showing.

enrollment_dim.course_id is joined with the course_dim.id field and you can use that to figure out what course that enrollment is in.

enrollment_dim.user_id is joined with the user_dim.id field and you can use that to figure out what user we're talking about.

Here's some SQL (MySQL flavored) that will show all of the enrollments and the courses for Maria.

SELECT 
  u.name AS user_name,
  c.name AS course_name,
  e.type AS roll_type,
  u.canvas_id AS user_id,
  c.canvas_id AS course_id
FROM enrollment_dim e
JOIN user_dim u ON (e.user_id = u.id)
JOIN course_dim c ON (e.course_id = c.id)
WHERE e.workflow_state = 'active'
  AND c.workflow_state = 'available'
  AND u.canvas_id = 145;

View solution in original post

Thank you, James! That makes sense as canvas_id is a bit confusing for me.

Thank you, Chris! I am now following the group.