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)