Powering the assessment dashboards from Canvas to Data Studio

A series of dashboards have been designed to meet the insight and assessment needs of instructional coordinators,  program coordinators, and counselors. Demonstration dashboards have also been built for instructors and academic advisors. All of these many dashboards are powered by data from just two comma separated value tables exported from Instructure Canvas. Canvas refers to these as reports, they are both accessed from the admin reports. 

Up front I want to thank Kansas State University's office of assessment whose assessment workshop videos from 2020 started me on this journey in March 2021. At first I thought I needed Canvas Data and either PowerBI or Tableau. Then I watched the Kansas State videos more carefully and realized that the data was available as a report. I could also see that these reports were being used to drive dashboards. Although I lacked PowerBI and Tableau, I stumbled into Data Studio in the newly acquired Google Workspace for Education. By late March I was building my first dashboards. 

Although I could see the general outlines of the distant coastline, I had no map. I had to stumble my own way to the process of building, maintaining, and feeding dashboards. The following outlines the feeding process for the dashboards that have been developed.

The two reports that drive the dashboards from the data side are the Grade Export report....

...and the Outcome Results report. 

There is only a minimal amount of extract, transform, and load work to be done. 

In the grade export report a sort is done by student sis and rows without a null student sis are deleted. This is not strictly necessary, but the most common join will be on the student sis. The join is done on the student sis rather than the student id because the the student id is local to Canvas, that is a Canvas id, while student sis is the student sis id, although the grade report omits the word id. Eventual connection of the two reports to SIS data will require a valid student sis id. 

The student sis field appears to rarely be null. Spring 2022 with 1850 grades and 995 students included a single student with two grades that had no student sis value, these may be configuration or other issues external to Canvas. In theory, someone with SIS access could manually enter the missing values.

Oddly enough the outcome results report uses the field name student sis id. If Instructure ever harmonizes those two field names then dashboards everywhere will break. In the outcome results report rows with null student sis id values are deleted. While outcome results report student sis id null values have been seen slightly more frequently than null values in the grade report, in fall 2021 and spring 2022 the issue has been a duplication of a single student's learning outcome results. In both terms these duplicate rows were manually deleted after a sort on student sis id. 

A dashboard for use by a dean of academic programs provides insight into systemwide current score including a histogram of score distribution (score decades) and course learning outcomes assessment means. 

The Data Studio dashboards retrieve data through a Google Sheets connector to a spreadsheet in Google Drive. The two reports are each imported to their respective Google Sheets file, using the option to replace the existing sheet on import. This approach has been necessary because the CSV connector in Google Data Studio cannot handle spaces in field names. The Google Sheets connector is space agnostic.

The connection above drives dashboards used by a dean of academic programs and, with appropriate filters, campus level instructional coordinators. 

The join is a double join on course id and section id.

Counseling dashboards only pull data from the Grade export spreadsheet. There are some calculated fields that return the campus, course prefix, and term information. 

Another dashboard provides aggregated assessment data. This dashboard uses only the outcome results report and a dimension table that maps aggregation of the learning outcomes. 

The dimensions table is linked on a left join using the learning outcome name field.

The learning outcome name is coming in via an outcomes CSV import file, column C, title, is the unique learning outcome name. Both the vendor_guid and the title must be unique. There does not seem to be a way, unless there is API access, to obtain the vendor_guid after the outcomes have been imported into the outcomes bank. This means that editing and deleting outcomes that have been uploaded requires use of a CSV copy of this outcomes import file [restricted link].

While the above is a prototype based on a limited subset of data, this dashboard joins SIS data to outcome results and outcome dimensions tables. 

This was the join that was not possible in Google Data Studio until spring 2022. Data Studio could only join tables on a left outer join and only if a common join key field existed in all of the joined tables. The above joins, however, are a right outer join on a student sis id key and a left outer join on a learning outcome name key. This is now possible in Data Studio. The only join that is not yet possible is a union, and that is apparently in development.

The bulk of the dashboards either use the above joins or variations on the above joins. Updating the two tables, usually weekly, updates all of the dashboards. There is some upkeep work to be done. New course outlines mean new outcomes have to be added to the system each with a unique vendor_guid and title/learning outcome name. The outcomes dimension table needs to be updated with the new outcomes as well. There is mapping work to be done in the outcomes dimension table, which requires an understanding of grey goo. 

There is also the ongoing work of managing access permissions to reflect changes in personnel. If instructor and academic advising dashboards were developed and deployed, there would be a significant increase in the workload both in terms of building/retiring dashboards as well as managing course permissions. Course/section level filters would need manual updating each term. This is largely because the reports do not provide instructor information, thus filters at present are based on course/section information. No field exists that reports who is teaching a course/section. A true SIS table with instructor information could be used in conjunction with a filter to automatically update a dashboard term to term. Student SIS data would permit disaggregation by any variable for all students - not just the subset in the prototype dashboard.