Skip navigation
All Places > Data and Analytics > Blog > Author: Kevin Wright

Data and Analytics

2 Posts authored by: Kevin Wright

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: 

  1. Enrollment_Dim
  2. User_Dim
  3. Course_Dim


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. 

Being a content provider and not an educational institution, measuring the effectiveness of our course and student performance presented a challenge for us. We do not have teachers reporting grades to us, there is no one we can check in with, this is all work that had to be extracted from Canvas. This is how Canvas Data simplified this process for us. 


As mentioned in the title, we needed to know the quiz scores across our entire LMS for every class and every student. This required four unpacked Canvas Data files:

  1. Quiz Submission Fact
  2. User Dimension
  3. Quiz Dimesnion
  4. Course Dimension

These files were queried into Excel 2018. 


Afterwards, two separate merges had to take place. 


  • Quiz Submission & User ID
    • By User ID

The picture below shows the columns that were included

This image shows the columns that were included in the merge of the Quiz Submission and User ID

Note that is the name of the quiz. 


  • Merge one with Course Dimensions
    • By Course ID

The picture below shows the columns that I left included


This image shows the columns that were included in the merge of Merge One and Course Dimensions


Some of the columns picture above are custom. For example, the average column is Score/(points possible). The index gives a count of the students. 


What this left me with is a new query with the ability to sort through the results by course, course ID, term. With the information to be able to identify the student and add more information. 


For example, in our case, we held a series of webinars before each lesson, we were able to identify the teachers who did and did not attend the webinar, sort through them, and compare the averages between the two to judge the effectiveness of our webinars.