Create a Live Canvas Dashboard with Canvas API, Google Sheets,
and Tableau

Fritz Vandover, Ph.D.

Liberal Arts Technologies and Innovation Services

College of Liberal Arts, University of Minnesota

MN Summit on Learning and Technology, 2019

Thursday, August 1, 2019 - 12:30 - 1:45 pm

Our Structure for Today

  • Our Challenges
  • Our Need
  • What I Built
  • How I Did It
  • Canvas Reports and API
  • Moodle Reports
  • Google Sheets
  • Tableau

2

https://z.umn.edu/dashboard-summit-2019

Before We Begin

  • Play along at https://z.umn.edu/dashboard-summit-2019
  • This dashboard and the data could be better
  • But it works, and pragmatism matters

3

https://z.umn.edu/dashboard-summit-2019

Our Challenge(s)

  • 2 year transition (Fall 2017-Spring 2019) from Moodle to Canvas for the entire University of Minnesota system

4

https://z.umn.edu/dashboard-summit-2019

Our Challenge(s)

  • In the College of Liberal Arts (CLA), ~300 instructors and ~1,500 course sections were using Moodle during each term

5

https://z.umn.edu/dashboard-summit-2019

Our Challenge(s)

  • 3 educational technology staff members in Liberal Arts Technology Innovation Services (LATIS), 2 of whom joined the staff in July 2017
  • We work with a lot of faculty, but most used Moodle on their own
  • It was very difficult to know precisely how many instructors and departments were using Moodle and where they were in their adoption of Canvas

6

https://z.umn.edu/dashboard-summit-2019

Our Need

  • We needed to be able to see the current and past state of CLA’s presence in Moodle and in Canvas so that we could better direct our limited personnel resources to departments that needed it during the transition process

7

https://z.umn.edu/dashboard-summit-2019

Moodle was going away, so we needed to see who was using it

What I Built

8

https://z.umn.edu/dashboard-summit-2019

It shows a series of information in several panes, from left to right:

How I Did It

  • I Connected with Other Institutions Transitioning to Canvas
  • I Got Messy with
    • Canvas Reports and API
    • Moodle Reports
    • Google Sheets
    • Tableau

9

https://z.umn.edu/dashboard-summit-2019

Connecting with Other Institutions

  • UW-Madison was 1 year ahead of us in their 2 year transition to Canvas
  • A member of their College of Letters & Sciences staff showed me how they were using Canvas data about courses to see transition activity at a course, dept, and college level

10

https://z.umn.edu/dashboard-summit-2019

Canvas Reports and API

  • The Rough Process, after painful trial and error
    • Get the Provisioning report for the course term you want for a particular Subaccount (CLA, in my case).
      • In addition to course information, it has a ‘status’ field that says if a course is unpublished or active (ie, published). But it doesn’t say if a course has been edited. So...
    • I found that the Unused Courses report shows courses that have not been edited at all. It allowed me to infer if a course had been edited but not published.
    • Why was knowing what was edited so important?
      • If no one in a department was editing courses in the run-up to a semester, we knew we needed to reach out to those departments with support and information about the transition.

11

https://z.umn.edu/dashboard-summit-2019

You’ll see in the data example that I have raw data for Moodle and Canvas that I then pull into other sheets and then transform to make the Moodle-Canvas data have an identical structure.

Canvas Reports and API

  • Canvas API guide: https://canvas.instructure.com/doc/api/
    • Not well-documented, which is why this was painful trial and error
  • With the help of my colleague Colin McFadden, we automated pulling the Provisioning and Unused Courses reports. Those get pushed into my Google Sheet every morning around 4am so that the transformation scripts can do their magic

12

https://z.umn.edu/dashboard-summit-2019

Moodle Reports

  • We wanted to know which instructors were using Moodle in past semesters so that we could send resources and support directly to them.
  • I also had to do some transformations so that I had the data structure in Moodle and Canvas data to accommodate displaying this data in Tableau.
    • PSY3061_001F18 a shortname in Moodle
    • PSY 3061 (001) is the shortname for the same course in Canvas
  • The Annoying News
    • Moodle does not have an API, so I had to download and import this data into Google Sheets. Annoying, but not too painful.

13

https://z.umn.edu/dashboard-summit-2019

Google Sheets

  • I had to dust off old knowledge and learn lots of new tools like using QUERY and SQL to get data from other sheets, nested IF and COUNTIF statements, string functions (INDEX, LEFT, RIGHT, REGEXREPLACE, etc.), and more.
  • They are all in the Google Sheet copy. Borrow and steal away.

14

https://z.umn.edu/dashboard-summit-2019

I probably could have made the sheet much better and the transformations faster by using Google App Scripts, but there was a lot of other work to do with the actual migrations once I got it working, so I called it good enough.

Tableau

  • Tableau is a powerful data visualization software
  • We could spend a whole semester learning about Tableau
  • I would encourage you to take advantage of the Tableau learning resources out there
  • You may be eligible to get a free educational license for Tableau

15

https://z.umn.edu/dashboard-summit-2019

Tableau

  • I have created a copy of our Fall 2018 Dashboard Tableau workbook file and data that is available in Google Drive. Download the workbook file (.twbx file) and Excel file so that you can open them in Tableau to see how I built them.
  • Do your data transformation and calculations in your data source before it comes into Tableau, when possible
    • You can do transformations, calculations, and create variables in Tableau, but the options are more limited than what you can do in a spreadsheet tool, in my experience

16

https://z.umn.edu/dashboard-summit-2019

Thank You!

Please feel free to send your questions to me!

vandover@umn.edu

17

https://z.umn.edu/dashboard-summit-2019

Canvas Dashboard Roundtable: Learning Summit 2019 - Google Slides