Skip navigation
All Places > Data and Analytics > Blog > Author: fernerj@erau.edu

Data and Analytics

7 Posts authored by: fernerj@erau.edu Expert

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.  

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. 

This blog entry shares a basic Tableau report (and sql file) that provides the advising and administrative staff a window to understand student grade history for assignments in courses taken the current or previous terms.  An accompaniment to the Early Warning report, this grade history report enables detailed understanding of historical student performance and achievement over time.  Such a report may be useful when reviewing 'at risk' students.

 

This report uses the following tables from the Data Schema:

  • account_dim
  • assignment_dim
  • assignment_group_fact
  • assignment_group_dim
  • course_score_fact
  • enrollment_dim
  • submission_dim
  • submission_fact
  • user_dim
  • pseudonym_dim


 

To run the report, select the desired term(s) and enter the student ID #. This works best reviewing one student at a time.

 

 

Grade History

 

 

For the future we may include additional filters and/or colors.  For example, a filter can be created so that only assignments where there is an assignment grade below xx% displays.  In addition, color such as red can be added for failing grades, etc.

 

 

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 the attachments for information on connecting to other data sources.

 

------

We thank the Canvas Data team for their support and welcome all suggestions and derivative works.

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.