Overview

The data that feeds from SIS into Slate into SIS needs to conform to specific standards for proper processing. This document will cover the synchronization feed that has been configured, including the detailed file specifications, use cases, and development cycles.

Stakeholders

SPS AdmissionsAdvising

Special Considerations

Feed Processing

• Slate has been configured to watch for an updated file named "Slate Feed Synchronization.csv" files in the "incoming/sis" SFTP folder and then process the records using the "SIS Data Synchronization" appropriate source format.
  • The CSV file is files are generated nightly through a scheduled Crystal Report that uses a procedure called "a2_slate_import_pbi" using procedures stored on the CEREPORTS server.
    • The file is All files are saved on the H: drive in the following location: H:\Systems and Data Analysis\DATA_EXPORTS\SIS_to_Slate+Advising.
    • The file is All files are transferred nightly to the Slate SFTP folder using a script on the AIS terminal server.
      • students.csv
        • Includes all SPS students that have active STUD records in SIS
        • As students graduate or withdraw, their records fall out of the file but will remain in Slate unless deleted
        • Data is overwritten daily if there are any changes
        • New records are added daily if they fit the population
        • This file is loaded first in the import sequence as all other files are dependent on the base student record
        • Supports the 'incremental' parameter for pulling records changed in the last seven days
      • student_details.csv
        • Includes all SPS students that have active STUD records in SIS
        • Include additional details for the same group in the students.csv output
        • Supports the 'incremental' parameter for pulling records changed in the last seven days
      • enrollments.csv
        • Default is to load the last two terms of enrollment history for all students in the population
          • This is to ensure that we get updated grades for the previous term, as well as to cover overlap in registration windows between Summer and Fall
          • Slate is configured to remove the latest two terms of enrollment history for each person, which is then replaced by the current data from this file
        • A full export of all enrollment history can be pulled with a designated parameter in the SQL procedure (if needed)
        • The file is loaded second in sequence
      • holds.csv
        • Includes the latest hold and details by code for all students in the population
        • The file is loaded third in sequence

Requested Features/Issues

Current Cycle

Release

2024.1.

0 [

January 2024]

Features to Complete

• Cases where a course schedule row has changed but is older than the latest two terms will need to be managed through an alternate update procedure
  • This is uncommon, but happens enough that it has to be covered
    • Create an updated pull of the same enrollment history data, but with a changed after a certain number of days parameter
    • Schedule a weekly run of the changed rows from this updated extract feed
• Need a process to gracefully handle students that drop from the student feed due to deactivated STUD record in SIS
  • Graduates should be fine, since they should move to the Graduate status
    • Still need to handle cases when a graduate returns; does the student status revert back to student and lose the fact that they are alums?

Features to Consider

• NoneOne time import of advising notes from previous terms (pending data file)

Features Ready for Release

• NoneNot started

Previous Cycles

Release 

2023.1.

0 [

December 2023]

• Added a date check to the HLDS and TDET updates to get anything that changed in those tables within the specified numbers of daysBackloaded 90 days to ensure the sync was caught up outside the normal seven day rangeUpdate student procedure to include program codes for any graduates in the "Program_Name" field of the output
  • NOTE: This is a repurposing of an existing field that was not being used in the import
  • The source format was updated to map these comma separated values to the new "graduated_programs" field at the person level

Release 2022.1.

2 [

November 2022]

• Add a field in Slate to store any registration holds on the applicant's account in SIS and keep it up to date with the daily sync
  • App-Scoped: SIS Registration Holds (sis_reg_holds)
  • Add the new field to the SIS form for viewing and clearing (for cloned apps)
• Updated source format settings to replace the previous day's import file instead of saving every file forever

Release 2021.1.0 [February 2021]

• SIS → Slate sync process updates
• Add a check to the STUD check process to look for a REG-GRP indicator (STUD_STUDENT.Registration_Group_Code)
• If present, sync the code back to an application-scoped field in Slate
• SIS Registration Group (sis_reg_group)
• Add a check for any registered courses for the APPL entering term and store them in an application-scoped field in Slate (comma delimited list of course identifiers)
• SIS Registered Courses (sis_reg_courses)
• Update the sync source format to add the new fields

Release 2020.2.4 [October 2020]

• The Slate sync process needs to be updated to include an indicator of whether the student has a valid STUD record in SIS
• Create a new field in Slate to store Y/N for the STUD presence
Include the value in the SIS to Slate export file and add to the source format for inclusion into SIS
• Update student procedure to include details feed with additional fields to supplement the base data extract
  • Added an incremental switch to pull only records that have been modified in the last seven days

Release 2022.1.1 [November 2022]

• Update the null handling in source format imports
  • In the holds feed to clear out old values for new holds of the same type
  • In the students feed, clear out the Latest Registered Term value to catch cases where students register and then withdraw

Release 2022.1.0 [September 2022]

• All feeds have been automated and a the weekly retention policy to clear out dropped courses configured.
  • This ensures that Slate is in sync with SIS as of the previous day's activity.
• Data Management
  • CEREPORTS procedure to pull integration data for import into Slate
    • a2_slate_advising_enrollments
      • Add parameter to allow for a full history pull, or just the latest term (max from took)
      • Full history can be used to reset enrollment history (as needed)

Release 2022.0.1 [July 15, 2022]

• CEREPORTS procedure to pull daily file for import into Slate
  • a2_slate_advising_students.sql
    • Population includes all existing students in SIS for MS and MPS degree types
    • Includes SPWD hold codes (permanent withdrawal) to allow tracking
      • Update the Slate record on import to set the Leave status
    • Always perform a full pull of data for the population
    • This is primarily for record generation, but can contain updates
    • Data to include
      • CUID, UNI, first & last name, birth date, email, cu email, gender, program and code, admit term
      • Advisor (ADSL_ADVISOR_ADVISEE)
      • Address, phone, additional contact info
  • a2_slate_advising_student_holds.sql
    • Same population
    • Initial pull of the latest cases by distinct hold type code for all students
    • Data to include
      • Date hold was applied and/or lifted
      • Status of the hold
      • Hold code, name, and notes (if any)
  • a2_slate_advising_enrollments.sql
    • Same population
    • Data to include
      • All registered courses current and history
      • Grades, instructor, term, start/end date