Last Updated 5/24/2021

Overview
Location
Schedule
Details
Change Log
Troubleshooting Notes

Overview:

The SQL Agent Job for Qualtrics Surveys loads survey response data from Qualtrics related to COVID.  It runs every 20 minutes because the responses power dashboards that need to be as near real-time as possible. 

The primary purpose of this job is to load data from Qualtircs surveys related to COVID and populate COVID related tables every 20 minutes.  The details of downstream applications are in the next section

Location:

SQL Agent Job in the dataMarq data warehouse. It runs packages in the staging package ETL-Qualtrics along with a variety of stored procedures – detailed below – to build COVID tables.

Schedule:

The jobs in this project run as part of multiple SQL Agent jobs (full dataMarq schedule)

ETL - Qualtrics Surveys â€“ Daily - Everyday every hour from 7am to 6pm

ETL - Qualtrics Surveys -- Full â€“ Not scheduled, can be run ad-hod

Job Details:

STEP 1: Execute Survey Load Packages

This  step runs the qx_load_surveys package in the ETL-Qualtrics project. This populates all of the qualtrics schema tables with the latest responses in the datamarq_staging database.

STEP 2: Execute Lists Proc

ops.sp_d_covidcheq_list
This procedure populates the table ops.d_covidcheq_list.  This table has all of the people and data needed for the COVID Cheq survey directory.  It is used to both push data to Qualtrics for people who should be added/disabled, it also contains data on last responses to track delinquency.  This table also powers the COVID Cheq dashboards used by faculty to track student responses status.

STEP 3: Execute OIRA Procs

These procedures populate tables that OIRA uses for COVID tracking.  They are all based on survey responses and fairly straightforward.

oira.sp_s_covid_units

oira.sp_s_covid_test_supply

oira.sp_s_covid_call_tracking

oira.sp_s_covid_ppe_supply

 

Change Log:

Date	Developer	Change Record
5/24/2021	N/A	Initial Documentation

Troubleshooting Notes:

1. Intermittent Failure on Qualtrics Survey load

   Occassionally the integration with Qualtrics will fail due to a connection error or a timeout on the Qualtrics side.  We use the API to interact with Qualtrics and they sometimes throttle the connection.  If there are more than two consecutive failures in this job it is usually something on our end, not the Qualtrics. But an occassional faillure is to be expected.