Recently we had an issue come up in our organization where we needed some analytics on teachers who have attended educational webinars. This data is housed in two separate places, our teachers are obviously identified in Canvas but the teachers who have been attending webinars are identified in Go-To-Meeting. So we had to find some way to pull our data from these two separate sources and combine it into one file.
Here I will talk about how I used Canvas Data and Excel 2017 to extract which of the users in our course were teachers and identify which course they are teaching.
You actually do not need any _fact tables for this query. The three unpacked files you need to query into Excel are:
Enrollment_Dim has a default table header called "Type" which identifies what type (go figure) of enrollment the particular user has. The purpose of the User_Dim file is to put a name on it. So you should perform a merge between these two files by the user_id column in Enrollment_Dim and the id column in the User_Dim. What you just did here is essentially add names to your Enrollment_Dim table, so you can filter by "type" and know who you are looking at. I'll refer to this merge as "merge-1" for the rest of this post.
The second merge slaps the name of the course onto merge-1. So, in addition to having a name on the enrollment_dim table (which had your enrollment type) you can also see the course the user is in. To achieve this you should perform another merge, merge-1 with Course_dim by the course_id column. This I will call merge-2.
After merge-2 the excel workbook has everything I need. All that is left is to filter by enrollment type and term and I have a complete list of every user enrolled in my institution as a teacher.