Import Self-Service Attendance for NCCCS/CSV Institutions

Overview

This document provides instructions on how to replace WebAttend data with Self Service Attendance data on the Transcript Course Attendance data feed. If both are needed, see step “Both WebAttend and SelfService” below.

In order for attendance data to be pulled in from an outside source, SS&E attendance tracking must not be enabled (SS&E Administration > Attendance Configuration > Enable Attendance Tracking set to No). Attendance may be imported OR attendance may be taken using the attendance tracking features in SS&E, but both cannot be used at the same time.

For additional guidance and consulting through this process, please see the Small Project in the Professional Services Offers in the Watermark Academy. 

Before Updates

Check if there are any active attendance alerts in Aviso Engage > Alerts > Automated Alerts. If so, it is recommended to disable automated attendance alerts during the import/transition to avoid retroactive alerts from being triggered. They can be reenabled after all attendance data is imported for the first time.

Create Self Service Informer Report

A new Informer report named “AVISO - LIVE - Transcript Course Attendance - Self Service” must be created, following the format below.

1. Mapping: STUDENT.COURSE.SEC
   1. Filter:
      1. Any STUDENT.COURSE.SEC → SCS.ABSENT.DATES is not empty
      2. Any STUDENT.COURSE.SEC → SCS.ABSENT.DATES on or after ‘TODAY-365’
   2. Add the columns specified in the table below.
2. Normalize the report on the following columns:
   1. SCS.ATTENDANCE.END.TIMES, SCS.ATTENDANCE.TYPES, SCS.ATTENDANCE.START.TIMES, SCS.ATTENDANCE.INSTR.METHODS, SCS.ABSENT.DATES
3. Schedule the report you just created to be exported four times daily to the SS&E (Aviso) file directory.
   1. By default, this usually is located on the Connect Server C:\Aviso\csv but may vary by institution. You can look at another report to get this path value.
   2. Cron example: “0 0 7,11,16,21 * * ?”
   3. File name: transcript_course_attendance_self_service.csv
   4. Ensure that “Show Column Headers” is unchecked/disabled.
   5. For Encoding, select “UTF-8”.
   6. Export the file.
4. Verify that the file is present on the Connect Server, with the correct name and formatting.

Update Accelerate Configuration

Accelerate must be updated to accept the new file and match attendance records with the course section schedule elements.

1. From SS&E Administration, navigate to SIS Integration > Datafeeds.
2. Temporarily disable the import for the Transcript Course Attendance while updating Accelerate, and until JSON data is verified.
3. From SS&E Administration, navigate to SIS Integration > Accelerate Configuration.
4. Both WebAttend and Self Service: If you want to have both queries running, specify the query below as a separate <Query> in the <Queries> XML. The name of this query would then be “selfServiceAttendanceQuery”, and the original WebAttend query would be “webAttendQuery”.
5. To replace Web Attend and use Self Service only (most common), add the following XML to the Child XML in alphabetic order by stream name. This overrides the Web Attend attendance and replaces it with Self Service attendance only.

<Stream name="TranscriptCourseAttendance"> <SetupQueries> <SetupQuery type="TempTable" connection="SIS" name="entryDatesByTranscriptCourseIds" bindingOrder="0" executionOrder="0"> <Value> CREATE TEMPORARY TABLE entryDatesByTranscriptCourseIds as select 'DUMMY_RECORD' as transcriptCourseId, 'DUMMY_RECORD' as entryDate </Value> </SetupQuery> </SetupQueries> <Queries> <Query connection="SIS" name="mainQuery"> <Value> select concat('STUDENT.COURSE.SEC.', tcass.COLUMNS[0], '.', tcass.COLUMNS[6], '.', tcass.COLUMNS[7], '.', tcass.COLUMNS[8], '.', tcass.COLUMNS[4]) as id, cast(tcass.COLUMNS[4] as date) as attendanceDate, case when tcass.COLUMNS[5] in ('A') then 'ABSENT' when tcass.COLUMNS[5] in ('E') then 'EXCUSED' when tcass.COLUMNS[5] in ('P') then 'PRESENT' when tcass.COLUMNS[5] in ('L') then 'TARDY' else 'UNKNOWN' end as attendanceType, tcass.COLUMNS[2] as transcriptCourseId, tcass.COLUMNS[6] as meetingType, csse.COLUMNS[0] as courseSectionMeetingId from dfs.root.`transcript_course_attendance_self_service.csv` tcass inner join transcriptCourseIds tci on tcass.COLUMNS[2] = tci.transcriptCourseId inner join dfs.root.`course_section_schedule_element.csv` csse on csse.COLUMNS[16] = tcass.COLUMNS[3] <!-- Course Section Id --> and csse.COLUMNS[3] = tcass.COLUMNS[6] <!-- Meeting Type --> and csse.COLUMNS[4] = tcass.COLUMNS[7] <!-- Start Time --> and csse.COLUMNS[5] = tcass.COLUMNS[8] <!-- End Time --> and ( (csse.COLUMNS[9] = 'Y' and TO_CHAR(cast(tcass.COLUMNS[4] as date), 'e') = 1) or (csse.COLUMNS[10] = 'Y' and TO_CHAR(cast(tcass.COLUMNS[4] as date), 'e') = 2) or (csse.COLUMNS[11] = 'Y' and TO_CHAR(cast(tcass.COLUMNS[4] as date), 'e') = 3) or (csse.COLUMNS[12] = 'Y' and TO_CHAR(cast(tcass.COLUMNS[4] as date), 'e') = 4) or (csse.COLUMNS[13] = 'Y' and TO_CHAR(cast(tcass.COLUMNS[4] as date), 'e') = 5) or (csse.COLUMNS[14] = 'Y' and TO_CHAR(cast(tcass.COLUMNS[4] as date), 'e') = 6) or (csse.COLUMNS[15] = 'Y' and TO_CHAR(cast(tcass.COLUMNS[4] as date), 'e') = 7) ) where nullif(tcass.COLUMNS[3],'') is not null and nullif(tcass.COLUMNS[4],'') is not null and nullif(tcass.COLUMNS[6],'') is not null <!-- and nullif(tcass.COLUMNS[7],'') is not null --> <!-- and nullif(tcass.COLUMNS[8],'') is not null --> </Value> </Query> </Queries> </Stream>

6. Run Connect manually from the Connect Server (instructions here). This will transfer the data to Watermark.
7. When the process completes, confirm that new self-service attendance data is available:
   1. From SS&E Administration, navigate to SIS Integration > Datafeeds.
   2. Scroll down to the data feed named “Transcript Course Attendance”.
   3. Click the “Download Extract” link. This will open the JSON file that has been transformed via Accelerate, but has not yet been imported into SS&E.
8. Verify that records exist with an id value prefix of “STUDENT.COURSE.SEC”. If records do exist, then move on to the Import section below. If not, check for transcriptCourseAttendance errors in SS&E Administration > Advanced > External Logging.

Import Data

The import must be enabled/re-enabled to import the JSON data into SS&E.

1. When JSON data is confirmed to contain the new records like “STUDENT.COURSE.SEC”, the Transcript Course Attendance import can be reenabled on the Datafeeds screen.
2. After the import job runs again, verify that the imported data is visible in the admin screens (SIS Integration > Datafeeds > “View” Imported Data) and on at least one student profile.

Troubleshooting

• If the data is not transferring from the CSV file on the Connect Server to the JSON file, check for transcriptCourseAttendance errors in SS&E Administration > Advanced > External Logging.
• If the data is in the JSON file but is not importing, check for Import errors in SS&E Administration > SIS Integration > Import Errors.
• Some institutions have reported missing attendance records, specifically for Online/Hybrid courses. This may occur when attendance records do not have a meeting type, so the record cannot be matched with a course section schedule element delivery method. To solve this, the meeting type join can be removed from Accelerate. The consequence of this is if there is an attendance record with no meeting type it will show up in SS&E as an attendance record in all the meeting types that section has on a given day. So for example if there is a CLASS and a LAB meeting type it will duplicate the record onto both meetings. It has to do this because there is no way to know which it should be applied to.
• All days should be set to “Y” for online courses to allow attendance data to be reported on any day.