Skip navigation
All Places > Data and Analytics > Blog

It's not designed for what we're trying to use it for

- almost anyone, including the Canvas Data team

 

The Canvas Data Portal - Requests documentation states

Pageview requests. Disclaimer: The data in the requests table is a 'best effort' attempt, and is not guaranteed to be complete or wholly accurate. This data is meant to be used for rollups and analysis in the aggregate, _not_ in isolation for auditing, or other high-stakes analysis involving examining single users or small samples. As this data is generated from the Canvas logs files, not a transactional database, there are many places along the way data can be lost and/or duplicated (though uncommon). Additionally, given the size of this data, our processes are often done on monthly cycles for many parts of the requests tables, so as errors occur they can only be rectified monthly.

 

When we started looking into this data's use, we provided a similar disclaimer.

Then Neal Shebeck, would consistently add "This is a conversation starter, not a smoking gun."

 

The requests table is unlike any other table in Canvas Data. With the exception of Pageviews, all other tables record events that are triggered when a teacher or student saves something in Canvas, such as when a teacher creates a page or assignment, or when a student submits an assignment or takes a quiz.

 

Pageviews are different, a user might click a link on a website from the moment the page loads until they stop clicking. Each of these events is useful.

 

It might tell us

  • how the user navigated the site, the order in which they clicked to get to content
  • how long before the user changed pages, which can give us some insight into how long the content was accessed
    • helping with... did the user find the content they wanted, or did they leave quickly (wrong place, or step between)

And much more

 

Except the requests table doesn't just contain clicks, it contains logs. Anyone who has ever seen web server logs, knows that any transaction or request over HTTP is logged in whatever detail the engineers decide suits their needs. To give you some scope, here is a list of HTTP response status codes - HTTP | MDN. You would expect any of these to be a line for each request made to the server. Along with the URL, timestamp, ip address, user agent, and more.

 

Some of it's useful, most of it's completely undocumented. I tried compiling a spreadsheet once, to catalog my best effort at understanding the various web_application_/controller/action/context_type. Most of them appear to be routes. Canvas is nice and links up the user, course if /courses, and some other useful information.

 


 

Part of the problem with the Requests table, is the beauty of the web, and the Canvas LMS REST API. The same API that allows Canvas Developers to integrate their institution and extend Canvas or create tools is the same API that Canvas itself is built on. This means that any requests to the server made by Canvas are also logged, not just clicks or transactions made by the user.

 

Here's the best way I can demonstrate. Open Canvas and go to the Dashboard.

Right click the page and choose Inspect or Inspect Element, to open the Developer Tools - for the Canvas User

Click on the Network Tab, then the ⃠ Clear button. Cleared? Good.

Now start moving your mouse over the interface, here's some targets.

Canvas Dashboard - Network Tab

 

Now, look at the Network tab again, most of your actions were clicks or even hovering.

Look closely, do you see the unread_count? This is not something you performed, this was Canvas checking for new messages in your inbox to update the flag in the navigation.

 

Cool.

 

The Problem with the requests table, is what also makes Canvas a great LMS. "Born in the Cloud"

 

This, and LTI's. LTI's and more, are hosted outside of Canvas in the Cloud creating a lot of noise in the table, rows which contain requests not triggered by the user, or Canvas, that we might not need for these purposes.

 

I found this, because like many of you, we have full time ** students. One of my early questions was geared toward understanding if all our users were local, or if they roamed. Can we make instructors aware of when students are traveling? Can we be empathetic to timezone differences? To answer this, I used some of the many Geo Location API's on the web to collect the location data of the remote_ip's in the requests table. At first I was extremely impressed with how many students we had traveling. Then I counted... there were too many students.

 

Using the Pseudonym Dim - Canvas Data Portal

which contains the user's last_login_ip and current_login_ip

Here's an overlay of logins vs. requests in Tableau.      Student Logins      Noise

Requests vs Logins

I generated this map to share at Hack Night. Before that, we generated a map for NVLA with just student logins.

NV Learning Academy on Twitter: "Redefining "Study Abroad".

 

As you can see, the physical location of a user is different from some of their requests. If you understand the Cloud, then you can also see that a traveling student start's triggering cloud services in the regions they travel. It's also possible that a student sitting at home using Canvas on their laptop, while also using their phone can have a mobile IP address from another state. Solved: IP address in another state? - Verizon Fios Community

 

Along with the web_application_* fields, URL path's like /api/ping,/pageviews, and others make filtering out the massive amount of data that grows in the request table difficult. Let's say you want to try anyway, check out Requests Table and the discussion about how to host and handle the large table, filter or delete rows.

 

OK, Let's Try

Here's a scenario. A common question in the Community.

 

Daily User Activity in a Course

where
course_idis not null and course_id = #
user_idis not null and user_id = #
grouping by
course_id
user_id
timestampthe complete date time, helps narrow down sessions
timestamp_dayquickly group by day - redundant, but really happy they provide this
session_idhelpful for trying to separate windows of user activity, this helps reduce idle time from our collection
remote_ipidentifies the user on the internet/location, this can change throughout the day, it also helps separate sessions 

 

If a user walks away from the screen while Canvas is open, Canvas will run /ping requests that keep things alive. We can use session_id and remote_ip in order to attempt to filter data for active sessions. If you don't filter, and remove inactive requests, you will likely end up with data that shows user activity for hours, all day, sometimes multiple days.

 

Breaking it up into sessions, with a stacked bar chart – minutes on the y-axisccsd/lti/palette/teacher

Course Daily Student Activity - More

compared to a student with less activity

Course Daily Student Activity - Less

The plot line, showing average student activity for the course

 

Let's zoom out, to all students in the course

Course - All Students Activity

This at least measures all users equally. Whether it's fully accurate is questionable, and mobile?

Back to 'conversation starters, not smoking guns'.

 

Here's a query - How Do I Determine Time Spent on Site #comment-97617 

 

Nevada Learning Academy at CCSD uses this data, along with course activity by hour and submission times to identify when students are active, to schedule Live Sessions, for the most popular time of day or weekday. A teacher with full time and part time students, can schedule sessions when the most students will be available, or do more and split days and hours to be available for different groups. Teachers can flex their time to make these accomodations.

 

From here, you can expand queries and join tables to do a decent number of user analytics.

Here's some examples, I will try to update, add, and curate.

User Attendance - Users Browsing By Day (maybe at least reading content) 

Course - User Wiki Page Views 

Course - Content areas including Assignments, Discussions, Quizzes, Modules etc 

 

All of this, doesn't tell us whether the user was truly active, or help us mimic the Total Activity calculation Canvas uses. Which seems to be just a tiny bit of JavaScript code Canvas uses to count the seconds of activity on the page, and submit it on the next page? These are the /pageview requests, which I can't seem to gather any useful information.

 

Where does that bring us? We can keep trying to filter and define the data, helping make it more manageable, and a little more accurate for these purposes. But maybe there's another way?

 

I will share more in a future post, but this is relevant now

Live Events

Which is an experimental beta feature from Canvas, which sends messages to AWS Simple Queue Service. The messages are events and transactions, which are consumable in real time. During Hack Night, a member of the Canvas Data Team stated the paraphrased quote at the top of this post, adding that Live Events is a better way of dealing with requests and events. What about both?

 

I also had an opportunity at Hack Night to discuss this issue with some Canvas Engineers. While I have some other use cases for this, which I will share at a later time, my only request was to add the IP address of the user to the login event. I have been after the IP of each login for about 2 years now. With the IP of the login, we can filter out or specifically collect just the requests of the user's computer* instead of the noise, getting us closer to user activity and clicks.

* You might ask, why not just use the last_login_ip and current_login_ip from the pseudonym table?

      - Canvas Data compiles once a day, 1 row per user. If the user logs into 3 or more devices, something is lost.

 

I invite any questions, comments, or contributions below; adapt the queries, post results, maybe a visualization.

What questions does this table help answer?

 

CCSD Canvas Team

CCSD Canvas Team #ElvisPanda

 

** I have tried adding 'o-n-l-i-n-e' to this sentence a dozen times. Jive keeps removing it!

Also getting removed before LMS. What gives Jive?

This blog entry shares a set of Tableau visualizations that summarize assignment and final course grades.  These help us understand:

  • the frequency and distribution of assignment grades
  • how many 100's are assigned (especially important for large stakes assignments)
  • the frequency and distribution of final course letter grades
  • the percentage of students who fail the course(s)
  • what trends can be observed for the college / department/ course/ instructor(s)

 

The attached Tableau workbook is functional however may have a bug or two.  Feel free to send any observations my way.

 



Data Source

 

The following tables from the Data Schema are used

  • account_dim
  • assignment_dim
  • assignment_group_dim
  • assignment_group_fact
  • course_dim
  • course_score_fact
  • course_section_dim
  • pseudonym_dim
  • student_enrollment_dim
  • submission_dim
  • submission_fact
  • teacher_enrollment_dim
  • teacher_user_dim


Note how the enrollment_dim was added twice and a unique name (student/ teacher) is assigned.

 

grade analysis data structure

 

 

 

Field (as named)TableOriginal Field Name
Collegeaccount_dimsubaccount2
Departmentaccount_dimsubaccount3
Subaccount2 Idaccount_dimsubaccount2_id
Workflow Stateaccount_dimworkflow_state
Id (Assignment Dim)assignment_dimid (assignment_dim)
Points Possibleassignment_dimpoints_possible
Titleassignment_dimtitle
Id (Assignment Group Dim)assignment_group_dimid (assignment_group_dim)
Name (Assignment Group Dim)assignment_group_dimname (assignment_group_dim)
Workflow State (Assignment Group Dim)assignment_group_dimworkflow_state (assignment_group_dim)
Assignment Group Id (Assignment Group Fact)assignment_group_factassignment_group_id (assignment_group_fact)
Course Id (Assignment Group Fact)assignment_group_factcourse_id (assignment_group_fact)
Group Weightassignment_group_factgroup_weight
Account Idcourse_dimaccount_id
Canvas Id (Course Dim)course_dimcanvas_id (course_dim)
Codecourse_dimcode
Conclude Atcourse_dimconclude_at
Course Namecourse_dimname (course_dim)
Start Atcourse_dimstart_at
Workflow State (Course Dim)course_dimworkflow_state (course_dim)
Course Id (Course Score Fact)course_score_factcourse_id (course_score_fact)
Enrollment Idcourse_score_factenrollment_id
Final Scorecourse_score_factfinal_score
Score Idcourse_score_factscore_id
Course Idcourse_section_dimcourse_id
Sis Source Id (Course Section Dim)course_section_dimsis_source_id (course_section_dim)
Workflow State (Course Section Dim)course_section_dimworkflow_state (course_section_dim)
Emailpseudonym_dimunique_name
Id (Pseudonym Dim)pseudonym_dimid (pseudonym_dim)
User Id (Pseudonym Dim)pseudonym_dimuser_id (pseudonym_dim)
Workflow State (Pseudonym Dim)pseudonym_dimworkflow_state (pseudonym_dim)
Id (Enrollment Dim)student_enrollment_dimid (enrollment_dim)
Type (Enrollment Dim)student_enrollment_dimtype (enrollment_dim)
User Idstudent_enrollment_dimuser_id
Workflow State (Enrollment Dim)student_enrollment_dimworkflow_state (enrollment_dim)
Published Gradesubmission_dimpublished_grade
Workflow State (Submission Dim)submission_dimworkflow_state (submission_dim)
Assignment Idsubmission_factassignment_id
Course Id (Submission Fact)submission_factcourse_id (submission_fact)
Scoresubmission_factscore
Submission Idsubmission_factsubmission_id
User Id (Submission Fact)submission_factuser_id (submission_fact)
Course Id (Enrollment Dim) #1teacher_enrollment_dimcourse_id (enrollment_dim) #1
Id (Enrollment Dim) #1teacher_enrollment_dimid (enrollment_dim) #1
Type (Enrollment Dim) #1teacher_enrollment_dimtype (enrollment_dim) #1
Workflow State (Enrollment Dim) #1teacher_enrollment_dimworkflow_state (enrollment_dim) #1
Canvas Id (User Dim)teacher_user_dimcanvas_id (user_dim)
Id (User Dim)teacher_user_dimid (user_dim)
Instructorteacher_user_dimsortable_name

 

         Note: unused fields are hidden within Tableau.

 

Data Source Filters

 

FilterDetails
Workflow Statekeeps active
Workflow State (Course Dim)keeps available and completed
Workflow State (Course Section Dim)keeps active
Workflow State (Submission Dim)keeps graded
Workflow State (Pseudonym Dim)keeps active
Workflow State (Enrollment Dim)keeps active and completed
Workflow State (Enrollment Dim) #1keeps active and completed
Workflow State (Assignment Group Dim)keeps available
Type (Enrollment Dim)keeps StudentEnrollment
Type (Enrollment Dim) #1keeps TeacherEnrollment
Conclude Atincludes the last 12 months
Assignment Score Groupsexcludes Null
Subaccount 2 Idkeeps yoursubaccount numbers

 

 

Calculated fields created in Tableau:

 

Applicable WorksheetCalculation nameCalculation
Grouped Assignment Score DistributionPercentage of TotalCOUNT([User Id])/ TOTAL(COUNT([User Id]))
Assignment Score GroupsIF [Score] = 0 then 'Fail - 0'
ELSEIF [Score] >0 AND [Score] <60 THEN "Fail"
ELSEIF [Score] >=60 AND [Score] <70 then "D"
ELSEIF [Score] >=70 AND [Score] <80 then "C"
ELSEIF [Score] >=80 AND [Score] <90 then "B"
ELSEIF [Score] >=90 AND [Score] <100 then "A"
ELSEIF [Score] =100 then "100"
END
Assignment Scores - # 100's# StudentsCOUNTD([Id (Enrollment Dim)])
# Assign.COUNTD([Id (Assignment Dim)])
Approx. # of ScoresCOUNT( [Score])
# of 100'sCOUNTD(IF [Score] = 100 THEN [Submission Id] END)
% 100's[# of 100's] / [Approx. # of Scores]
% 100 > 50%?[% 100's] > .5
% 100 > 75%?[% 100's] >= .75
Grades URL'http://xyz.instructure.com/courses/' + STR([Canvas Id (Course Dim)]) + '/gradebook/'
Final Grade DistributionFinal Score GroupsIF [Career] = 'Grad' AND [Final Score] <70 THEN 'Fail'
ELSEIF [Career] = 'UGrad' AND [Final Score] <60 THEN 'Fail'
ELSEIF [Career] = 'UGrad' AND [Final Score] >=60 AND [Final Score] <70 then "D"
ELSEIF [Final Score] >=70 AND [Final Score] <80 then "C"
ELSEIF [Final Score] >=80 AND [Final Score] <90 then "B"
ELSEIF [Final Score] >=90 AND [Final Score] <=100 then "A"
ELSE 'Incomplete'
END
Percentage of TotalCOUNT([User Id])/ TOTAL(COUNT([User Id]))
Course InfoCourse URL'http://xyz.instructure.com/courses/' + STR([Canvas Id (Course Dim)]) 
% Students Failing Course# StudentsCOUNTD([Id (Enrollment Dim)])
# FailingCOUNTD(IF [Career] = 'Grad' AND [Final Score] < 70 THEN [Enrollment Id]
ELSEIF [Career] = 'UGrad' AND [Final Score] < 60 THEN [Enrollment Id] END)
% Failing[# Failing]/ [# Students]
CareerIF [Crse. No.] < '500' then "UGrad"
ELSE "Grad"
END

 


 

Tableau Worksheet Filters

 

Most of the worksheets have the following filters:

  • College
  • Department
  • Term
  • Code (i.e. course prefix and number)
  • Section

 

These are applied to all of the worksheets using this setting.

 

filter all

 

 

For the worksheet 'Assignment Scores - # 100's - By Course & Teacher': 

 

  • Group Weight >= 20:  set to True, this filters out low stakes assignment groups.
  • AGG %100 > 50%?:  set to True, this serves to display only those assignments where more than 50% of the grades are 100
  • AGG %100 > 75%?:  when set to True, this serves to display only those assignments where more than 75% of the grades are 100
    • Note: set whatever threshold you need here - just change the values.
  • AGG # Assign.:  a slider filter that helps to ensure only large stakes assignments are displayed.  For example, if the course has an Assignment Group named 'Discussions' with nine weekly assignments and this Assignment Group has a weight of 20%, then each discussion is worth 2.2% of the grade.  This filter serves to exclude such assignments, if desired. Tip: experiment by moving the slider to see how the results change.  

 

100s

 

 

Important notes:

  • This is a work in progress and may have a bug or two.  For example, one or two of our courses use an unusual point structure and the gradebook is used in an atypical manner.  I may opt to filter out such courses out at the data level.
  • Our Sis Source Id field is split into six parts in order to create fields for our Term, Course Prefix, Course Number, Section, Location code, etc.  You will likely need to rework this if you intend to use the attached Tableau workbook as a starting point. 
  • The Tableau workbook and a text file that contains the sql are attached. The sql may not be of much value without the calculated fields.  Sorry, but I did not take the time to clean the sql export from the Tableau file.
  • Upon opening the .twb file in Tableau, you will be prompted to provide a password to access the data source.  Because you don't have access to our data source, you'll need to click on the "Edit Connection" button.  Once the connection information is updated to access your Redshift data source you should be able to begin editing the workbook.  See attached files for more info on connecting to other data sources.

 

------

 

We thank the Canvas Data team for their support and welcome all suggestions and derivative works.  If you make it better, please share.  

R is a free software environment for statistical computing and graphics. Shiny is an open source R package that provides an elegant and powerful web framework for building web applications straight from R.

 

As learning management systems (LMS) become more widely and deeply adopted to support teaching and learning, a substantial amount of data about how students participate in learning activities is available. How can we analyze the data and translate it into a useful form? How can we make the LMS data accessible to faculty to inform the efficacy of the instruction and the quality of students’ learning experience? To support the effort of exploring LMS data to address teaching and learning related questions, we leveraged R Shiny and developed a number of analytical applications that graphically analyze LMS data using R.

 

The following examples demonstrate three Shiny applications that analyzes and visualizes three common types of LMS (Canvas) learning data, which can be harvested using Canvas APIs:

  • Quiz submission data
  1. https://dartmouth-edtech.shinyapps.io/samplequizexam/
  2. Using quiz submission data to inform quiz design
  • Discussion interaction data
  1. https://dartmouth-edtech.shinyapps.io/networkvisualizationprototype/
  2. Using social network analysis to model online interactions
  3. Role modeling in Online discussion forums/
  • LMS access data
  1. https://dartmouth-edtech.shinyapps.io/contentaccessexample/
  2. LMS course content access analytics

Do you have a desire to easily locate all instances of a specific word, software tool, string of text, etc. within wiki pages, assignment descriptions, quiz descriptions, and discussion prompts? If yes, and if you have Tableau (or perhaps other visualization tool), then continue reading.

 

The attached Tableau workbook is a work in progress.  It has three worksheets and while it is structured to focus on the subaccounts containing our course templates, this can be easily adapted to search subaccounts that contain actual courses.

  1. Template List: a simple visualization that lists all course templates.
  2. Tool List - Search Module Content (shown below): this uses multiple calculated fields as well as a Parameter containing a list of predefined text strings.  We use this to locate software tools used within courses.
  3. Keyword Search: this also uses a Parameter with a 'Type In' open search box. Useful for searching for any word or pattern of words.

 

search modules

 

 


 

Data Source

 

table relationships

 

 

Field NameTable
Idaccount_dim
Subaccount2account_dim
Subaccount2 Idaccount_dim
Sis Source Idaccount_dim
Id (Course Dim)course_dim
Account Idcourse_dim
Name (Course Dim)course_dim
Workflow State (Course Dim)course_dim
Id (Wiki Page Dim)wiki_page_dim
Title (Wiki Page Dim)wiki_page_dim
Wiki Bodywiki_page_dim
Workflow State (Wiki Page Dim)wiki_page_dim
Url (Wiki Page Dim)wiki_page_dim
Revised Atwiki_page_dim
Id (Assignment Dim)assignment_dim
Title (Assignment Dim)assignment_dim
Assignment Descriptionassignment_dim
Workflow State (Assignment Dim)assignment_dim
External Tool Id (Assignment Dim)assignment_dim
Id (Discussion Topic Dim)discussion_topic_dim
Titlediscussion_topic_dim
Discussion Messagediscussion_topic_dim
Type (Discussion Topic Dim)discussion_topic_dim
Workflow State (Discussion Topic Dim)discussion_topic_dim
Discussion Typediscussion_topic_dim
Course Id (Discussion Topic Dim)discussion_topic_dim
Id (Module Dim)module_dim
Course Idmodule_dim
Workflow State (Module Dim)module_dim
Name (Module Dim)module_dim
Id (Module Item Dim)module_item_dim
Canvas Id (Module Item Dim)module_item_dim
Assignment Idmodule_item_dim
Discussion Topic Idmodule_item_dim
Module Idmodule_item_dim
Quiz Idmodule_item_dim
Wiki Page Idmodule_item_dim
Content Typemodule_item_dim
Workflow State (Module Item Dim)module_item_dim
Title (Module Item Dim)module_item_dim
Urlmodule_item_dim
Module Item Idmodule_item_fact
Account Id (Module Item Fact)module_item_fact
Assignment Id (Module Item Fact)module_item_fact
Module Id (Module Item Fact)module_item_fact
Id (Quiz Dim)quiz_dim
Name (Quiz Dim)quiz_dim
Quiz Descriptionquiz_dim
Assignment Id (Quiz Dim)quiz_dim
Workflow State (Quiz Dim)quiz_dim

 

 

Data Source Filters

 

FilterDetails
Workflow State (Course Dim)keeps available, claimed, created
Workflow State (Module Dim)keeps active and unpublished
Subaccount2 IdYour target subaccount #.

 

 

 

 

Calculated fields created in Tableau:

 

Applicable WorksheetCalculation nameCalculation
Tool List - Search Module ContentAssignment Contains Tool?IF CONTAINS([Assignment Description], [Tool Filter]) THEN 'True'
ELSEIF CONTAINS([Assignment Description], LOWER([Tool Filter])) THEN 'True'
ELSE '--'
END
Discussion Contains Tool?IF CONTAINS([Discussion Message], [Tool Filter]) THEN 'True'
ELSEIF CONTAINS([Discussion Message], LOWER([Tool Filter])) THEN 'True'
ELSE '--'
END
Quiz Contains Tool?IF CONTAINS([Quiz Description], [Tool Filter]) THEN 'True'
ELSEIF CONTAINS([Quiz Description], LOWER([Tool Filter])) THEN 'True'
ELSE '--'
END
Wiki Page Contains Tool?IF CONTAINS([Wiki Body], [Tool Filter]) THEN 'True'
ELSEIF CONTAINS([Wiki Body], LOWER([Tool Filter])) THEN 'True'
ELSE '--'
END
Combined SearchIF [Assignment Contains Tool?] = 'True'
OR [Discussion Contains Tool?] = 'True'
OR [Wiki Page Contains Tool?] = 'True'
OR [Quiz Contains Tool?] = 'True'
THEN 'Uses Tool'
ELSE 'No Usage'
END
Keyword SearchContains Keyword?

CONTAINS(LOWER([Discussion Message]),LOWER([Search Anything]))

OR

CONTAINS(LOWER([Quiz Description]),LOWER([Search Anything]))

OR

CONTAINS(LOWER([Wiki Body]),LOWER([Search Anything]))

OR

CONTAINS(LOWER([Assignment Description]),LOWER([Search Anything]))

BothCourse URL'http://xyz.instructure.com/courses/' + STR([Canvas Id (Course Dim)])
Modules URL[Course URL] + '/modules'
Page URL[Course URL] + '/pages/' + [Url (Wiki Page Dim)]
Item URLIF [Content Type] = 'WikiPage' THEN 'http://xyz.instructure.com/courses/' + STR([Canvas Id (Course Dim)]) + '/pages/' + STR([Url (Wiki Page Dim)])
ELSEIF [Content Type] = 'Assignment' THEN 'http://xyz.instructure.com/courses/' + STR([Canvas Id (Course Dim)]) + '/assignments/' + STR([Assignment Id])
ELSEIF [Content Type] = 'DiscussionTopic' THEN 'http://xyz.instructure.com/courses/' + STR([Canvas Id (Course Dim)]) + '/discussion_topics/' + STR([Discussion Topic Id])
ELSEIF [Content Type] = 'Quiz' THEN 'http://xyz.instructure.com/courses/' + STR([Canvas Id (Course Dim)]) + '/quizzes/' + STR([Id (Quiz Dim)])
ELSE 'http://xyz.instructure.com/courses/' + STR([Canvas Id (Course Dim)]) + '/modules/'
END
wgr wwo

TRIM( SPLIT( [Name (Course Dim)], "_", 1 ) )

Note: our course templates begin with either wwo or wgr.  This split enables creation of a filter.

 

 

Parameters

 

parameter tool filter

 

search anything

 

 

Important notes:

  • This is a work in progress.  On the to-do list is to resolve a few bugs. For example, searching for the tool 'Canva' returns all the occurrences of Canvas. (7/8 Update:  added a space after the word 'Canva ' in the search parameter so as to exclude 'Canvas')
  • This search only locates items included in the modules.  Don't use Modules? No problem - simply remove the three module tables.
  • The Tableau workbook and a text file that contains the sql are attached. However the sql may not be of much value without the calculated fields and parameters.  Sorry, but I did not take the time to clean the sql export from the Tableau file.
  • Upon opening the .twb file in Tableau, you will be prompted to provide a password to access the data source.  Because you don't have access to our data source, you'll need to click on the "Edit Connection" button.  Once the connection information is updated to access your Redshift data source you should be able to begin editing the workbook.  See attached files for more info on connecting to other data sources.

 

------

 

We thank the Canvas Data team for their support and welcome all suggestions and derivative works. 

This blog entry shares a basic Tableau report (and sql file) that provides the advising and administrative staff a window to understand student grade history for assignments in courses taken the current or previous terms.  An accompaniment to the Early Warning report, this grade history report enables detailed understanding of historical student performance and achievement over time.  Such a report may be useful when reviewing 'at risk' students.

 

This report uses the following tables from the Data Schema:

  • account_dim
  • assignment_dim
  • assignment_group_fact
  • assignment_group_dim
  • course_score_fact
  • enrollment_dim
  • submission_dim
  • submission_fact
  • user_dim
  • pseudonym_dim


 

To run the report, select the desired term(s) and enter the student ID #. This works best reviewing one student at a time.

 

 

Grade History

 

 

For the future we may include additional filters and/or colors.  For example, a filter can be created so that only assignments where there is an assignment grade below xx% displays.  In addition, color such as red can be added for failing grades, etc.

 

 

Important notes:

  • Our Sis Source Id field from the course_section_dim is split into six parts in order to create fields for our Term, Course Prefix, Course Number, Section, Location code, etc.  You will likely need to rework this if you intend to use the attached Tableau workbook as a starting point. 
  • Upon opening the .twb file in Tableau, you will be prompted to provide a password to access the data source.  Because you don't have access to our data source, you'll need to click on the "Edit Connection" button.  Once the connection information is updated to access your Redshift data source you should be able to begin editing the workbook.  See the attachments for information on connecting to other data sources.

 

------

We thank the Canvas Data team for their support and welcome all suggestions and derivative works.

Ever have the need to download a large number of student submission files for the purposes of accreditation or other academic assessment reviews?  While it is possible to go to each course assignment and click the 'download submissions' button to generate a zip file that downloads after it is generated, doing so is a repetitive and time consuming task.  Especially if you have many courses and assignments to collect files from.

 

In case you have not been following the Canvas Data release notes, as of this past month we have a new table:  submission_file_fact, which fixed a bug with the file_dim table.  After seeing this (thank you, Canvas Data gurus), I needed to explore.

 

It turns out this new table is very helpful as new approaches to accessing and collecting submission files can be set up.  The method detailed here works quite well when you are downloading submission files for a single assignment across course sections within a given term, and also across terms for the academic year.  It can of course be used other ways, but I prefer to collect submission files for one assignment at a time so I can appropriately label the folder I place them in.

 



The Nitty Gritty Details

The example below uses an sql file and also Tableau to provide a visual.  Within we can filter by term, course and assignment.  Of course such filters can alternatively be set up within your sql file.  Portrayed here are links to over one thousand student file submissions to a single course assignment for just part of an academic year.  How long would it take us to go to each of the 50 course sections to manually collect the files from each assignment?  Too long. ;o)

 

filter

 

Data Source Filters

filters

 

Calculated Field

Description

Calculation for the Download File URL column

Link to download individual student file submissions

'h ttps:// xyz.instructure.com/files/' + STR([canvas_id _file_dim_]) + '/download?download_frd=1'

 

Example: 

h ttps:// xyz.instructure.com/files/12345678/download?download_frd=1

 

 

Steps to run the report using Tableau:

  1. Review your default Downloads folder on your local hard drive. Consider deleting old content so there is no confusion with the files about to be downloaded.
  2. Run the report ‘Student File Submissions’; apply desired filters, select the term(s), course, & assignment name
  3. Export the output to csv and open into Excel.
  4. Copy the list of links and paste into a Chrome web browser extension that enables bulk downloading. Example: https://melanto.com/apps/bulk-url-opener/     **
  5. The files are then downloaded.  Perhaps do this in batch such as 50 at a time until you are comfortable and then experiment.  I don't suggest downloading a very large number at one time.  As you progress, count the number of rows on the spreadsheet and verify the number of files downloaded match.  Below are the settings for the above tool that I've experimented with.

    settings
  6. Create a new folder and name the folder for the course and assignment. Move the files from the download directory to the new folder ahead of downloading additional files.


** Note: writing an Excel macro was considered, however I didn’t want to over complicate this.

 



Notes:

  • I am sharing this as a ‘work-in-progress’ and I hope it is built upon. Note the sql has a few extra fields that are not being used.  Feel free to remix and reuse any or all of this.
  • Our Sis_Source_Id field is split into six parts in order to create fields for our Term, Course Prefix, Course Number, Section, Location code, etc.  You will likely need to rework this if you intend to use the attached Tableau workbook as a starting point. 
  • Upon opening the .twb file in Tableau, you will be prompted to provide a password to access the data source.  Because you don't have access to our data source, you'll need to click on the "Edit Connection" button.  Once the connection information is updated to access your Redshift data source you should be able to begin editing the workbook.  See attached files for more info on connecting to other data sources.

 

Please feel free to add your ideas.  Enjoy ~!

One of our many goals with Canvas Data is to develop a report that would permit our advising and administration teams quick access to see a list of students who are ‘at risk’ of not passing their course(s).   This blog entry shares a Tableau report that was created to meet this need.  

 

While we started by connecting the various tables together within the data source tab, we found that a custom SQL query to be more efficient.  This report uses the following tables from the Data Schema:

  • account_dim
  • assignment_dim
  • course_score_fact
  • discussion_entry_fact
  • enrollment_dim
  • submission_dim
  • submission_fact
  • user_dim
  • pseudonym_dim

 Early Warning

 

Calculated fields created in Tableau:

 

Calculation nameCalculation
# Discuss. PostsCOUNTD([id_discussion_entry_dim])
# of ZerosCOUNTD(if [Score] = 0 then [Submission Id] END)
# Submitted & UngradedCOUNTD(if [workflow_state_submission_dim] = 'submitted'
AND ISNULL([graded_at])
THEN [id_submission_dim]
END)

# Unsubmitted & Ungraded

COUNTD(if ISNULL([submitted_at])
AND ISNULL([grade])
AND [Due] < TODAY() -1
then [id_submission_dim]
END)


this calculation is being fine tuned.  Results may include a count of assignments given a zero )

Unsubmitted 5+?

IF [# Unsubmitted & Ungraded] >= 5 THEN '5+'
ELSE "4 or fewer"
END

Current Score < 70?[Current Score] < 70
Status

IF [Current Score < 70?] = TRUE then "At Risk"

ELSEIF ISNULL([Current Score < 70?]) THEN 'No Grades'

ELSE "Passing"

END

Activity last 8 days?

IF [Last Activity]>= NOW() - 8 THEN 'True'

ELSE 'False'

END

Career

IF [Crse No.] < '500' then "UG"
ELSE "GRAD"
END

Current Week

 

(We have courses that run 4, 6, 9, and 12 weeks.)

IF TODAY()< [term start]-1 THEN "Term hasn't started"
ELSEIF TODAY() > [term end] THEN "Term has ended"
ELSE "Week "+STR(FLOOR(1+(TODAY()-[term start])/7))
END

(Note -   We publish courses 1 business day ahead of term start, hence the -1 in the calculation)   

 

Important notes:

  • Our Sis Source Id field is split into six parts in order to create fields for our Term, Course Prefix, Course Number, Section, Location code, etc.  You will likely need to rework this if you intend to use the attached Tableau workbook as a starting point. 
  • We have over 130 locations and these are grouped by region or market.  I left the Group in the workbook so as to demonstrate how it can be done, if desired.
  • We use the pseudonym_dim integration ID to add identifying information such as the student home location, Campus name, SIS ID#, etc. which are concatenated.  This is part of a custom field we created using data from our SIS.
  • The report has two sheets with Undergraduate and Graduate students separated by default.
  • The report is published to Tableau Online and the advising team has Tableau accounts and can pull the necessary information when needed; i.e. after grading is complete for weeks 1, 3, etc.
  • Upon opening the .twb file in Tableau, you will be prompted to provide a password to access the data source.  Because you don't have access to our data source, you'll need to click on the "Edit Connection" button.  Once the connection information is updated to access your Redshift data source you should be able to begin editing the workbook.  See attached files for more info on connecting to other data sources.

 

------

 

We thank the Canvas Data team for their support and welcome all suggestions and derivative works. 

 

For the future I will explore ways to identify 'at risk' students using more than just the course grade.  For example, it is possible for a student to have a passing grade such as a 'B' yet have a notable number of missing assignments.  Or someone could be passing yet not be participating in the discussions, etc.  But this can be viewed with the current report by changing a few of the default filters.

 

The Tableau workbook and a text file that contains the sql are attached.

fernerj@erau.edu

Announcement Posted?

Posted by fernerj@erau.edu Expert Mar 16, 2018

This basic Tableau workbook provides a report that details the status of announcements within courses. The report is published to Tableau Online and the quality team uses this as part of pre-term checks. This is primarily used to confirm at least one announcement has been posted to upcoming term courses prior to the term start. Instructors are required to post a welcome announcement that contains relevant information (late policies, etc.).

 

Included in the report is a count of the number of announcements posted and the number of delayed post announcements.  This can be the same number.  A filter is available to narrow the data shown to only those that have or do not have an announcement posted.   

 

Tableau Workbook

 

 

Computed columns:

 

DescriptionCalculation

# Announ

COUNTD(if [Type (Discussion Topic Dim)]= "Announcement" THEN [Id (Discussion Topic Dim)]
END)

# Delayed Post

COUNT([Delayed Post At])

Announcement?

IF [# Announ] >=1 THEN "Has Announcement"
ELSEIF [# Announ] = 0 THEN "No Announcement"
END

Announ Link

"https:// xyz.instructure.com/courses/"+STR([Canvas Id (Course Dim)])+"/announcements"

 

 

 

Data source tables and (example) filters:

 

tableau relationships

 

 

Important notes:

  • Our Sis Source Id field from the course_section_dim is split into six parts in order to create fields for our Term, Course Prefix, Course Number, Section, Location code, etc.  You will likely need to rework this if you intend to use the attached Tableau workbook as a starting point. 
  • Upon opening the .twb file in Tableau, you will be prompted to provide a password to access the data source.  Because you don't have access to our data source, you'll need to click on the "Edit Connection" button.  Once the connection information is updated to access your Redshift data source you should be able to begin editing the workbook.  See attached files for information on connecting to other data sources.

 

------

We thank the Canvas Data team for their support and welcome all suggestions and derivative works.

This Tableau workbook provides a report that details the status of quiz availability dates within courses. This is primarily used to confirm settings for midterm and final exams prior to the term start. If the availability date is specified, the report shows that date under 'Unlock At (Quiz Dim)'.  Otherwise, this column shows 'null'.  Included in the report is a red x mark for when there is no 'available from' date set and a hyperlink that points to the quiz page.  

 

availability dates

 

 

Computed columns:

 

DescriptionCalculation

Midterm or Final

(Quiz Type)

IF CONTAINS([Title (Assignment Dim)],"Final")then "Final"
ELSEIF CONTAINS([Title (Assignment Dim)],"Midterm")then "Midterm"
ELSEIF CONTAINS([Title (Assignment Dim)],"Mid-term")then "Midterm"
else "" END

Quiz Date Set?

IIF(ISNULL([Unlock At (Quiz Dim)]), 'Date Not Set', 'Date Set')

Assignment Link

'https:// xyz.instructure.com/courses/'+ STR([Canvas Id (Course Dim)]) + '/assignments/' + STR([Canvas Id (Assignment Dim)])

Career

IF [Crse No.] < '500' then "UG"
ELSE "GRAD"
END

 

 

Data source tables and filters:

 

data source

filters

 

Important notes:

  • Our Sis Source Id field from the course_section_dim is split into six parts in order to create fields for our Term, Course Prefix, Course Number, Section, Location code, etc.  You will likely need to rework this if you intend to use the attached Tableau workbook as a starting point. 
  • The report is published to Tableau Online and the quality team uses this as part of pre-term checks.
  • Upon opening the .twb file in Tableau, you will be prompted to provide a password to access the data source.  Because you don't have access to our data source, you'll need to click on the "Edit Connection" button.  Once the connection information is updated to access your Redshift data source you should be able to begin editing the workbook.  See attached files for information on connecting to other data sources.

 

------

We thank the Canvas Data team for their support and welcome all suggestions and derivative works.

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 NewColumn.name.1 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. 

Found this article interesting about setting up a learning analytics tool using xAPI.

 

A Technical Look into Learning Analytics Data and Visualisations | Effective Learning Analytics

 

Note: While this article isn't about using Canvas data it is about using LMS data.

Just came across this news post from Instructure about anonymized data from the Canvas Network MOOC's being made available.

 

Canvas Offers Anonymized Canvas Network Data to Researchers

https://www.instructure.com/news/pr/canvas-offers-anonymized-canvas-network-data-to-researchers&122589

We reached out to Matt Lewis, who posted information about an LTI tool designed to help teachers and students visualize their participation in Canvas discussions. The tool is called Threadz.

 

Q: What was the motivation for developing an LTI like this?

 

Threadz was developed out of the faculty's desire to leverage more from their online discussions. "Conducting a discussion online was a little out of their comfort zones and they were trying to understand more about what was actually happening besides what Canvas was showing in Speedgrader," Lewis said.

 

In their search to find a solution, the development team looked at integrating SNAPP (Social Network Adapting Pedagogical Practice), a tool that performs real-time social network analysis and visualization of discussion forum activity within popular commercial and open source Learning Management Systems (LMS). "For various reasons, this tool proved too difficult to integrate into Canvas, so we built our own," Lewis concluded. "We knew we wanted to tap into the API to extract all the discussion data, and a LTI would help facilitate that." Although the SNAPP tool was never used, the team relied on SNAPP research regarding the use of social network data and learning analytics.

 

Once enabled in a course, Threadz can be utilized by any student and/or teacher enrolled in that course. "It provides valuable information about the constructs of the social network patterns for individual discussions," says Lewis. "These patterns can help highlight successful student behavior characteristics about student interactions within a discussion as well as assist the instructor with adjustments to the course’s learning environment to improve student success."

 

Q. What information are teachers finding to be most valuable?

 

By assessing the social network connections in a discussion, teachers can hone in on patterns that emerge related to the activities and behaviors of individuals and groups. An instructor can use these patterns to identify behaviors and characteristics within the course, such as:

  • learner isolation
  • non-integrated groups
  • instructor-centric discussions
  • key integration (power) users and groups.

 

 

"I think the classic network diagram provides a good overview about who is influencing the discussion and to a certain level the amount a student is engaged with others," Lewis points out. "It is easy-to-read at-a-glance and is intuitive to most instructors to understand."

 

 

The chord diagram however provides more information about the direction and count of the interactions between two people. "While maybe not as familiar, the chord diagram can provide a more complete look at a discussion," says Lewis.

 

Q: How are teachers leveraging this information about their discussions?

 

Threadz provides information about which students might be isolated within the group or which groups are dominating a discussion. Instructors use this information to determine when to intervene and interact with individuals or groups. "A forgotten element within discussions is the impact of instructor interactions," stresses Lewis. "Threadz assists the instructor in understanding if they are too involved in leading the discussion or too hands-off."

 

While Threadz does not provide any qualitative insights on whether a discussion is good or bad, it certainly adds another layer of assessment to course discussions. Teachers are empowered to leverage this data to design and/or modify discussions, accordingly. On the flip side, students can also self-assess their own involvement in course discussions and determine the frequency and intensity in which they are participating in their course discussions.

 

Q: What's next for Threadz?

 

Sentiment analysis is certainly not off the table, but may be a bit ambitious at this stage. "Exploring the language of the posts would be very interesting even at a rudimentary level," shared Lewis. "But this is getting a little ahead of the game."

 

At this time, the development team is looking to add features related to data file downloads and include additional statistics around the ratio of posts and word counts. (Cool, right?!)

 

Q: How can the Canvas Community be helpful to your team?

 

The Threadz team is interested in making this tool easy-to-use. Canvas admins should be able to easily install the LTI and students and teachers should be able to intuitively use it. Their team would sincerely appreciate any feedback about it's application and functionality. And welcome all feature suggestions for Threadz.

 

Thanks to Matt Lewis for your insights. Feel free to reach out to him for additional information.

 

 

As teachers, it’s probably safe to assume students prioritize their assignments based on deadlines, right? But how close to deadlines are students submitting their work? And what does this insight reveal about student behaviors and teacher expectations?

 

One higher ed institution* decided to find out.

 

At the beginning, this school’s analytics team set out to explore student course participation patterns in Canvas by looking at a typical week and gathering discussion, assignment, and quiz submission activity. But preliminary results revealed patterns, which led to further examination the relationship between submission time and assignment due time.

 

What parts of Canvas Data were used?

The analytics team leveraged assignment submission dimension and assignment dimension table. Further analysis results were derived from course assignment information and submission data. Online quizzes and the assignments that had an ‘online’ submission type were included in the analysis. The assignments that did not have a due date/time were excluded from the analysis.

 

What were the results of the analysis?

Most students submitted assignments before due date/time than past due. (That’s good, right?) However, the median submission time before due date was 30 minutes and the median past due submission time was 1.2 hours.

 

What does this say about student behavior and teacher expectations?

After a few outliers were identified and removed, the median submission time before due date was 30 minutes and the median submission time past due time was 1.2 hours. The chart below shows that the number of before due submissions was much greater than the total number of past due submissions. Also, the variation in past due submission hour was wider than before due submission hour.

 

The results imply that majority students tended to submit assignments more often before due time than past due time; and, the likely assignment submission time is 30 minutes prior to assignment due time.

 

 

The bar chart below revealed that a number of assignments contained due date/time that were set between midnight and 7am Eastern time, which led to some students working overnight to submit the assignment right around its due time. Taking all terms in the year of 2015 into consideration, the evening period from 8 pm to 10 pm was a popular time for assignment submissions, and 10 pm was the peak assignment due time (when assignments were due).

 



Therefore, a discussion with faculty to carefully consider their assignments’ deadlines might be warranted.

 

What’s next?

Some interesting follow-up questions might include:

 

  • Does submission time impact the quality (points/grade) of submission?
  • Does submission time change over the course of the semester?
  • Are more people submitting on time if deadline is morning, night, evening?
  • What is submission grading time? In other words, how quickly are teachers grading assignments after submission?

 

What other analytics around assignments do you think might be interesting to pursue?

 

Post your thoughts below!

 

 

 

* School requested to remain anonymous for this post.