fernerj@erau.edu

Searching for Words, Tools, Text Strings

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

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. 

Outcomes