Skip navigation
All Places > Data and Analytics > Blog > 2018 > July
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.  

R is a free software environment for statistical computing and graphics. Shiny is an open source R package that provides an elegant and powerful web framework for building web applications straight from R.

 

As learning management systems (LMS) become more widely and deeply adopted to support teaching and learning, a substantial amount of data about how students participate in learning activities is available. How can we analyze the data and translate it into a useful form? How can we make the LMS data accessible to faculty to inform the efficacy of the instruction and the quality of students’ learning experience? To support the effort of exploring LMS data to address teaching and learning related questions, we leveraged R Shiny and developed a number of analytical applications that graphically analyze LMS data using R.

 

The following examples demonstrate three Shiny applications that analyzes and visualizes three common types of LMS (Canvas) learning data, which can be harvested using Canvas APIs:

  • Quiz submission data
  1. https://dartmouth-edtech.shinyapps.io/samplequizexam/
  2. Using quiz submission data to inform quiz design
  • Discussion interaction data
  1. https://dartmouth-edtech.shinyapps.io/networkvisualizationprototype/
  2. Using social network analysis to model online interactions
  3. Role modeling in Online discussion forums/
  • LMS access data
  1. https://dartmouth-edtech.shinyapps.io/contentaccessexample/
  2. LMS course content access analytics

Do you have a desire to easily locate all instances of a specific word, software tool, string of text, etc. within wiki pages, assignment descriptions, quiz descriptions, and discussion prompts? If yes, and if you have Tableau (or perhaps other visualization tool), then continue reading.

 

The attached Tableau workbook is a work in progress.  It has three worksheets and while it is structured to focus on the subaccounts containing our course templates, this can be easily adapted to search subaccounts that contain actual courses.

  1. Template List: a simple visualization that lists all course templates.
  2. Tool List - Search Module Content (shown below): this uses multiple calculated fields as well as a Parameter containing a list of predefined text strings.  We use this to locate software tools used within courses.
  3. Keyword Search: this also uses a Parameter with a 'Type In' open search box. Useful for searching for any word or pattern of words.

 

search modules

 

 


 

Data Source

 

table relationships

 

 

Field NameTable
Idaccount_dim
Subaccount2account_dim
Subaccount2 Idaccount_dim
Sis Source Idaccount_dim
Id (Course Dim)course_dim
Account Idcourse_dim
Name (Course Dim)course_dim
Workflow State (Course Dim)course_dim
Id (Wiki Page Dim)wiki_page_dim
Title (Wiki Page Dim)wiki_page_dim
Wiki Bodywiki_page_dim
Workflow State (Wiki Page Dim)wiki_page_dim
Url (Wiki Page Dim)wiki_page_dim
Revised Atwiki_page_dim
Id (Assignment Dim)assignment_dim
Title (Assignment Dim)assignment_dim
Assignment Descriptionassignment_dim
Workflow State (Assignment Dim)assignment_dim
External Tool Id (Assignment Dim)assignment_dim
Id (Discussion Topic Dim)discussion_topic_dim
Titlediscussion_topic_dim
Discussion Messagediscussion_topic_dim
Type (Discussion Topic Dim)discussion_topic_dim
Workflow State (Discussion Topic Dim)discussion_topic_dim
Discussion Typediscussion_topic_dim
Course Id (Discussion Topic Dim)discussion_topic_dim
Id (Module Dim)module_dim
Course Idmodule_dim
Workflow State (Module Dim)module_dim
Name (Module Dim)module_dim
Id (Module Item Dim)module_item_dim
Canvas Id (Module Item Dim)module_item_dim
Assignment Idmodule_item_dim
Discussion Topic Idmodule_item_dim
Module Idmodule_item_dim
Quiz Idmodule_item_dim
Wiki Page Idmodule_item_dim
Content Typemodule_item_dim
Workflow State (Module Item Dim)module_item_dim
Title (Module Item Dim)module_item_dim
Urlmodule_item_dim
Module Item Idmodule_item_fact
Account Id (Module Item Fact)module_item_fact
Assignment Id (Module Item Fact)module_item_fact
Module Id (Module Item Fact)module_item_fact
Id (Quiz Dim)quiz_dim
Name (Quiz Dim)quiz_dim
Quiz Descriptionquiz_dim
Assignment Id (Quiz Dim)quiz_dim
Workflow State (Quiz Dim)quiz_dim

 

 

Data Source Filters

 

FilterDetails
Workflow State (Course Dim)keeps available, claimed, created
Workflow State (Module Dim)keeps active and unpublished
Subaccount2 IdYour target subaccount #.

 

 

 

 

Calculated fields created in Tableau:

 

Applicable WorksheetCalculation nameCalculation
Tool List - Search Module ContentAssignment Contains Tool?IF CONTAINS([Assignment Description], [Tool Filter]) THEN 'True'
ELSEIF CONTAINS([Assignment Description], LOWER([Tool Filter])) THEN 'True'
ELSE '--'
END
Discussion Contains Tool?IF CONTAINS([Discussion Message], [Tool Filter]) THEN 'True'
ELSEIF CONTAINS([Discussion Message], LOWER([Tool Filter])) THEN 'True'
ELSE '--'
END
Quiz Contains Tool?IF CONTAINS([Quiz Description], [Tool Filter]) THEN 'True'
ELSEIF CONTAINS([Quiz Description], LOWER([Tool Filter])) THEN 'True'
ELSE '--'
END
Wiki Page Contains Tool?IF CONTAINS([Wiki Body], [Tool Filter]) THEN 'True'
ELSEIF CONTAINS([Wiki Body], LOWER([Tool Filter])) THEN 'True'
ELSE '--'
END
Combined SearchIF [Assignment Contains Tool?] = 'True'
OR [Discussion Contains Tool?] = 'True'
OR [Wiki Page Contains Tool?] = 'True'
OR [Quiz Contains Tool?] = 'True'
THEN 'Uses Tool'
ELSE 'No Usage'
END
Keyword SearchContains Keyword?

CONTAINS(LOWER([Discussion Message]),LOWER([Search Anything]))

OR

CONTAINS(LOWER([Quiz Description]),LOWER([Search Anything]))

OR

CONTAINS(LOWER([Wiki Body]),LOWER([Search Anything]))

OR

CONTAINS(LOWER([Assignment Description]),LOWER([Search Anything]))

BothCourse URL'http://xyz.instructure.com/courses/' + STR([Canvas Id (Course Dim)])
Modules URL[Course URL] + '/modules'
Page URL[Course URL] + '/pages/' + [Url (Wiki Page Dim)]
Item URLIF [Content Type] = 'WikiPage' THEN 'http://xyz.instructure.com/courses/' + STR([Canvas Id (Course Dim)]) + '/pages/' + STR([Url (Wiki Page Dim)])
ELSEIF [Content Type] = 'Assignment' THEN 'http://xyz.instructure.com/courses/' + STR([Canvas Id (Course Dim)]) + '/assignments/' + STR([Assignment Id])
ELSEIF [Content Type] = 'DiscussionTopic' THEN 'http://xyz.instructure.com/courses/' + STR([Canvas Id (Course Dim)]) + '/discussion_topics/' + STR([Discussion Topic Id])
ELSEIF [Content Type] = 'Quiz' THEN 'http://xyz.instructure.com/courses/' + STR([Canvas Id (Course Dim)]) + '/quizzes/' + STR([Id (Quiz Dim)])
ELSE 'http://xyz.instructure.com/courses/' + STR([Canvas Id (Course Dim)]) + '/modules/'
END
wgr wwo

TRIM( SPLIT( [Name (Course Dim)], "_", 1 ) )

Note: our course templates begin with either wwo or wgr.  This split enables creation of a filter.

 

 

Parameters

 

parameter tool filter

 

search anything

 

 

Important notes:

  • This is a work in progress.  On the to-do list is to resolve a few bugs. For example, searching for the tool 'Canva' returns all the occurrences of Canvas. (7/8 Update:  added a space after the word 'Canva ' in the search parameter so as to exclude 'Canvas')
  • This search only locates items included in the modules.  Don't use Modules? No problem - simply remove the three module tables.
  • The Tableau workbook and a text file that contains the sql are attached. However the sql may not be of much value without the calculated fields and parameters.  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.