Skip navigation
All Places > Data and Analytics > Blog > 2018 > March
2018

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.