Overview

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

Stakeholders

Faculty Affairs

Special Considerations

Feed Processing

• Employee data is sourced from PAC and fed into a CEREPORTS table called Slate_Employee daily (see Base Data Model file linked above for details).
• Slate has been configured to watch for an updated files in the "incoming/sis" SFTP folder and then process the records using the appropriate source format.
  • CSV files are generated nightly through a scheduled Crystal Report that using procedures stored on the CEREPORTS server.
    • All files are saved on the H: drive in the following location: H:\Systems and Data Analysis\DATA_EXPORTS\Slate+Academics.
    • All files are transferred nightly to the Slate SFTP folder using a script on the AIS terminal server.
      • faculty_full.csv / faculty_incremental.csv
        • Includes all faculty members, including associates, that have had a teaching role at SPS (according to PAC)
        • As faculty become inactive or retire, their records may 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 faculty record
        • Supports the 'incremental' parameter for pulling records changed in the last seven days
      • positions_full.csv / positions_incremental.csv
        
        • Includes all positions for the faculty population, including non-teaching assignments
        • There is one row per position number per person
        • Salary details are included for SPS teaching positions (no salaries for administrative positions)
        • Supports the 'incremental' parameter for pulling records changed in the last seven days
      • teaching_full.csv / teaching_incremental.csv
        
        • Includes full teaching history for all in population
          • Note: Associate history is a new data point, and there is very little in SIS at the moment
        • The file is loaded last in sequence
        • Supports the 'incremental' parameter for pulling records changed in the last seven days

Requested Features/Issues

Current Cycle

Release 2024.1.1 [October 2023]

Features to Complete

• None

Features to Consider

• Not started

Features Ready for Release

• None

Previous Cycles

Release 2024.1.0 [September 2023]

• Create service account (sps-integration) and allow access to SFTP
• Faculty feed
  • Create and schedule Crystal Reports for full and incremental files dropped to H: drive
  • Create and schedule script to copy report files to Slate SFTP
  • SQL: a2_slate_academics_faculty
  • • Pull data required for import to Slate for base faculty records
    • Include full and incremental parameters
  • Setup source format in Slate to process incoming records
    • Automate watch folder for file drops (pac/faculty_*.csv)
• Positions feed
  • Create and schedule Crystal Reports for full and incremental files dropped to H: drive
  • Create and schedule script to copy report files to Slate SFTP
  • SQL: a2_slate_academics_positions
  • • Pull data required for import to Slate for faculty positions
    • Include full and incremental parameters
  • Setup source format in Slate to process incoming records
    • Automate watch folder for file drops (pac/positions_*.csv)
• Teaching feed
  • Create and schedule Crystal Reports for full and incremental files dropped to H: drive
  • Create and schedule script to copy report files to Slate SFTP
  • SQL: a2_slate_academics_teaching
  • • Pull data required for import to Slate for faculty teaching history
    • Include full and incremental parameters
  • Setup source format in Slate to process incoming records
    • Automate watch folder for file drops (sis/teaching_*.csv)