Overview

Blue is the course and instructor evaluation system used at Tufts. Each year, the evaluation data needs to be collected, processed and transmitted to two systems: Symplectic Elements (SoE instructor and course data) and Faculty Success (A&S instructor and course data). Additionally, all the ASE data and Fletcher data is typically consumed in a Tableau dashboard.

To facilitate this process, a workflow has been created that incorporates the following:

• AWS Lambda, for the targeted fetching and parsing of Blue data as well handling of Blue API key

• AWS Batch, for large-scale data processing and loading of Blue into the Aurora cloud DW

• Denodo, for data virtualization and preparation

• Snaplogic, for trasmitting the data to Elements and Activity Insight

• Box, for managing an access control file used to control access in Blue and Tableau

Design

Components

Bold views are final views in use by other systems
Italic views are cached

• Lambda

  • blue-data

• Batch

  • blue-batch

• Box

  • https://tufts.box.com/s/0mu3synulw1mnuq6f938fpg73uy0ljpl

• Denodo VDP

  • education_technology

    • 01-base_layer

      • 01-connections

        • coursescore

        • instructorscore

        • get_blue_api_key

        • getallprojectmetadata

        • ds_box_blue_report_by_school

        • ds_box_blue_report_by_subject

      • 02-base_views

        • bv_coursescore

        • bv_get_blue_api_key

        • bv_getallprojectmetadata

        • bv_instructorscore

        • bv_blue_subject_fact

        • bv_blue_question

        • bv_blue_question_fact

        • bv_blue_data

        • bv_box_blue_report_by_school

        • bv_box_blue_report_by_subject

    • 02-transformation

      • blue

        • p_coursescore

        • p_instructorscore

        • p_blue_secondary_subject

        • p_blue_subject

        • p_course_evaluation

        • p_course_evaluation_fltr

    • 03-integration

      • j_coursescore_apikey_projectmetadata

      • j_coursescore_vw

      • j_getallprojectmetadata_api_key

      • j_instructorscore_apikey_projectmetadata

      • j_instructorscore_vw

      • j_blue_loaded_projects

      • j_blue_project_load_list

      • j_blue_secondary_subject_detail

      • j_blue_subject_detail

      • j_course_evaluation_all

    • 04-interface_fulfillment

      • coursescore_vw

      • instructorscore_vw

      • course_evaluation_fltr

      • course_evaluation

      • course_eval_sec_model

      • d_blue_fltr_deptid

• Denodo Scheduler

  • blue_data_cache

  • blue-subject-details-cache

Loading of Blue Data into Aurora DW

The AWS batch job blue-batch linked above is responsible for loading Blue data into the Aurora DW which course_evaluation views in Denodo rely on. The jobs runs daily for each environment. The job loads projects based on the output of j_project_load_list in Denodo. The job also relies on bv_get_blue_api_key which returns the API key from Secrets Manager via Denodo.

Access Control

A consolidated access control file (linked above) has been created in Box to handle access control in both Blue and Tableau. The sheets ReportBy-School and ReportBy-Subject list UTLNs by school or subject values. A column in each sheet called auth_managers has all UTLNs comma separated. For Blue, these sheets are exported and loaded in manually. For Denodo, connections and views for each sheet allow the data to be loaded in directly and joined to the bv_blue_subject_fact view within j_course_evaluation_all. This creates a unioned auth_managers column combining school and subject access which can be used in Tableau.

Changes between Denodo and DW course_evaluation views

1. answer_response (eval response rate) and answer_stddev (score stddev) no longer null when classification = ‘Essay’. For item_count (students who participated in eval) is also no longer 0 in this case.

2. answer_response will be based on combined participation across course and instructor evaluations. This brings this metric in line with the Elements and Faculty Success integration. It was previously calcuated separately for course and intructor evaluations. We are now loading a combined file from Blue for both course and instructor questions, making this previous approach not possible. Loading of instructor-level response rate was attempted, but abandonded due to the resolution of the TaskStatus value not being at the instructor-level, despite requesting separate course and instructor files. See https://tuftswork.atlassian.net/browse/DSDW-715

3. D/A, N/A answers are not loaded/included. Should have no impact, as they are excluded from the Elements and Faculty Success integration and offer little/no value and add uneccessary storage and compute costs.

Resources

• Blue API Docs

  • On-server API docs

  • Explorance API docs