fernerj@erau.edu

Early Warning for Students

Blog Post created by fernerj@erau.edu Expert on Mar 18, 2018

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.

Outcomes