Basic enrolment report

[natacha_hes]

Hi All, I have been struggling to get my head around Canvas data. I just want a basic report that shows student name, what course they are enrolled in, the date they were enrolled into the course and the date they were concluded in the course. 

Could anyone point me in the right direction please?

[a1222252]

Hi Natacha,

Do you have the Canvas Data files downloaded and used to populate a database? If so the ENROLLMENT_DIM table will provide what you need. Join it with the USER_DIM to get the name of the student, with the COURSE_DIM table to get the course name, the workflow_state needs to be 'active', the type needs to be 'StudentEnrollment'. Join with the ROLE_DIM table to get only records with a role of 'StudentEnrollment'.

Regards,

Stuart.

[robotcars]

Hi  @natacha_hes ,

ROLE_DIM is built into enrollment_dim as type, maybe this depends on whether you have custom roles for enrollments, or just assume the base_role_type? I just use the base role name

Here's a view I typically use for everything from basic queries to LTI's and analytics. It brings in global, local, and sis ids of each entity for linking across data sources, or querying depending on the source parameter for querying and some of the useful timestamps. You can add the other table columns or eliminate as needed.

Some of the joins result in NULL values, I usually see less than a couple dozen at any given time. Sometimes I think this is a result of when CD is batched or out-of-bounds course creations (things I didn't automate). Today as I looked through this there were 16, and none of them were SIS courses.

My main unit test for this query is making sure the total rows matches enrollment_dim. One tricky part of this is pseudonym_dim, users can have multiple, if you don't add the conditions for workflow_state and position you can see duplicate enrollments when users have extra.

I have commented out my sub account logic (but left it for examples), we have a mess of nested sub accounts, and the logic helps me track enrollments by subject and school instead of it's location in Canvas. Essentially department_name is Math, Science, Foreign Language and sub_account_name is the school.

I do not have WHERE clause for things like enrollment_dim.workflow_state = 'active' since I use this as a VIEW, and just query on the view with additional conditions.

CREATE VIEW enrollments_view AS
SELECT
	user_dim.global_canvas_id
	, user_dim.canvas_id AS canvas_user_id
	, pseudonym_dim.sis_user_id
	, pseudonym_dim.unique_name AS user_login
	, user_dim.sortable_name
	, enrollment_dim.type AS enrollment_type
	, enrollment_dim.workflow_state AS enrollment_status
	, enrollment_dim.created_at
	, enrollment_dim.updated_at
	, enrollment_dim.last_activity_at
	, pseudonym_dim.current_login_at
	, pseudonym_dim.last_login_at
	, course_dim.id AS course_id
	, course_dim.canvas_id as canvas_course_id
	, course_dim.sis_source_id AS course_sis_id
	, course_dim.name AS course_name
	, course_dim.enrollment_term_id
	, enrollment_term_dim.canvas_id AS canvas_term_id
	, account_dim.id AS account_id
	, account_dim.canvas_id AS canvas_account_id
	, account_dim.sis_source_id AS account_sis_id
	, account_dim.id AS account_id -- AS department_id
	, account_dim.name AS account_name -- AS department_name
-- 	, CASE
-- 		WHEN account_dim.depth = 1 THEN account_dim.id
-- 		WHEN account_dim.depth = 2 THEN account_dim.parent_account_id
-- 		WHEN account_dim.depth = 3 AND parent_account = 'Middle Schools' THEN account_dim.id
-- 		WHEN account_dim.depth = 3 AND parent_account = 'Alternative' THEN account_dim.id
-- 		WHEN account_dim.depth = 3 THEN account_dim.grandparent_account_id
-- 		WHEN account_dim.depth = 4 THEN account_dim.grandparent_account_id
-- 		WHEN account_dim.depth = 5 AND parent_account = 'Live SIS Courses' THEN account_dim.grandparent_account_id
-- 		WHEN account_dim.depth = 5 AND parent_account = 'Manually Created Courses' THEN account_dim.grandparent_account_id
-- 	END AS 'sub_account_id'
-- 	, CASE
-- 		WHEN account_dim.depth = 1 THEN account_dim.name
-- 		WHEN account_dim.depth = 2 THEN account_dim.parent_account
-- 		WHEN account_dim.depth = 3 AND parent_account = 'Middle Schools' THEN account_dim.name
-- 		WHEN account_dim.depth = 3 AND parent_account = 'Alternative' THEN account_dim.name
-- 		WHEN account_dim.depth = 3 THEN account_dim.grandparent_account
-- 		WHEN account_dim.depth = 4 THEN account_dim.grandparent_account
-- 		WHEN account_dim.depth = 5 AND parent_account = 'Live SIS Courses' THEN account_dim.grandparent_account
-- 		WHEN account_dim.depth = 5 AND parent_account = 'Manually Created Courses' THEN account_dim.grandparent_account
-- 	END AS 'sub_account_name'
FROM CanvasLMS.dbo.enrollment_dim
	LEFT JOIN CanvasLMS.dbo.user_dim ON (enrollment_dim.user_id = user_dim.id)
	LEFT JOIN CanvasLMS.dbo.pseudonym_dim ON (pseudonym_dim.user_id = user_dim.id AND pseudonym_dim.workflow_state = 'active' AND pseudonym_dim.position = 1)
	LEFT JOIN CanvasLMS.dbo.course_section_dim ON (enrollment_dim.course_section_id = course_section_dim.id)    
	LEFT JOIN CanvasLMS.dbo.course_dim ON (course_dim.id = course_section_dim.course_id)
	LEFT JOIN CanvasLMS.dbo.enrollment_term_dim ON (enrollment_term_dim.id = course_dim.enrollment_term_id)
	LEFT JOIN CanvasLMS.dbo.account_dim ON (course_dim.account_id = account_dim.id)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[a1222252]

Hi Robert, Natacha,

We have custom roles, for example Tutor, Marker etc. so we need to look at both the enrolment type, (aka base_role_type), and role name.

We use a similar custom table which brings in the enrolment term, course name, person name and restrict it to workflow_state = 'active'. This filters out a lot of noise. We then use this table with requests data to provide a metric of the activity of actively enrolled people by day.

I'm not clear how the workflow_state is updated. Active enrolments appear to remain active forever.

Regards,

Stuart.

[robotcars]

 @stuart_smith ,

Thanks for sharing that, it's good to know how that gets used. We update the enrollment via SIS import integration, so if the enrollment is updated in the SIS it gets updated in Canvas and I get it in CD, of course missing any change history between.

RE Type 2 dimensions, Live Events will help with the workflow_state changes for enrollments if you need.

[a1222252]

I should probably have also said that these are not type 2 dimensions, so they don't record the workflow_state change history, just the date and state of the last change. We have been considering creating type 2 dimensions to show the history.

[stimme]

Hi  @natacha_hes , if all you need is a basic enrollment report, you could also skip Canvas Data. There are reports available through the Admin panel under Settings > Reports tab. How do I view reports for an account?

The provisioning report would let you know about current enrollments. Select the Users and Enrollments objects and filter by term if desired. In a few minutes you can download CSVs to work with in Excel. With the Users spreadsheet, you can add names to enrollments using VLOOKUP on Canvas User Id.

This won't indicate the date the enrollment was created or concluded, I'm sorry to say. But it is quite fast.