fernerj@erau.edu

Assignment Score & Final Course Grade Distributions

Blog Post created by fernerj@erau.edu Expert on Jul 28, 2018

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.  

Outcomes