Student Archive Table Changes - September 2024

This page shows the updates made to the student archive table in September 2024.

Detailed Information

Background

The student extract archive table (UM_STUD_EXT_ARC) is built by the daily process in CSRPT. It captures the new or changed records for the fields in the UM_STUD_EXTRACT table. The table has grown very large and the build process failed frequently. The review of the archive table was conducted and changes were made to make the process run more efficiently.

Changes

1. Archived a part of the data into new tables:
   - UM_STUD_EXT_ARC: This table has the same table name as the original table. It now includes the data since Summer 2019 (STRM>=1930). The plan is to include the entire five years plus the current year of the data. The terms will be switched annually.
   - UM_STUD_EXT_ARC_1530_2030 :  New table. It includes the data between Summer 2015 and Summer 2020. This table is not available in PS Query.
   - UM_STUD_EXT_ARC_0630_1530 : New table. It includes the data Summer 2015 and before (STRM<=1530). This table is not available in PS Query.
    
2. Split the SQR process (UMSTUARC.SQR) into two processes.
   A new SQR process, UMENRARC.SQR, was created to build student enrollment archive table (UM_STD_ENR_ARC). The revised UMSTUARC.SQR now build UM_STUD_EXT_ARC only. This enables us to make troubleshooting easier.
    
3. Stopped comparing the data with the previous day on these fields:
   - RESPONSE_REASON
   - RESPONSE_DT
   - UM_RSP_REASN_DESCR
   - TUIT_CALC_REQ
   - TUIT_CALC_DTTM
   These fields inflated the size of the table. Removing the logic will not affect much as they are rarely used in reporting.
    
4. Modified the RESPONSE_DT logic in the UM_STUD_EXTRACT table.​​​​​​​
   ​​​​​​​

Environment

• CSRPT