Aeries Web New Year Rollover
|[Aeries Web New Year Rollover - Updated May 26, 2017]|
Aeries Web Version - New Year Rollover
The New Year Rollover is now available in the Aeries Web Version. The AdminCS version is still available for districts who do not want to use this functionality in the Aeries Web-Based Environment; however, this new method drastically streamlines the rollover process in that all schools are rolled in one run of the rollover process, and it has been tested to work about 10 times faster than the AdminCS version.
The New Year Rollover page is in the Navigation menu under School Info | Functions. There are 2 tabs on the New Year Rollover page: Settings and Processes. The Settings tab includes District Wide, Inactive School and School Based settings, with the option to push one school's settings to selected schools. The School Based settings will display while selecting the District School Code (0) to facilitate easily setting up the defaults from the district and pushing the School Based settings to other schools. The District Wide settings will not display unless viewing the District School Code. All the settings are stored in the Rollover Settings (ROL) table that will roll forward to the new year. The Processes tab includes three categories: Pre-Rollover, Rollover, and Post-Rollover. The Pre-Rollover section includes reports to be run prior to the rollover: Pre-Rollover Audit Listing, and Table Record Counts (Pre). The Rollover section includes details on how to setup the rollover, and a button to Start Rollover. The Post-Rollover section includes reports to be run upon completion of the rollover: Missing Students Report, and Table Record Counts (Post).
The New Year Rollover page can be found under School Info | Functions in the Navigation Menu. Security to access this page can be granted under the School Information | New Year Rollover area of the Permissions page.
User accounts with Read access to the New Year Rollover can view the Settings tab and run the reports in the Processes tab.
User accounts with Update access to the New Year Rollover can view and update the information in the Settings tab and also push the settings to other schools. These users can also run the reports in the Processes tab.
Only Admin accounts have full access to the New Year Rollover. Admins can view and edit the Settings tab and push the settings to other schools, run the reports in the Processes tab, and initiate the Rollover.
For more information on how to set up security please refer to the AeriesNet_Security.pdf document on our Documents and Downloads page.
New Year Rollover Requirements
The New Year Rollover in Aeries Web Version is initiated from the current year database and rolled from the current year database into the new year database. It is very important that the most recent updates of both the Aeries Web Version and the Aeries Client Version are utilized. Prior to running the New Year Rollover process, the new year database needs to be created in SQL Server Management Studio. The new year database must have the same Database Suffix as the current year database with the year increased by one (for example DST17000EagleUSD), and the SQLModel from the most recent Aeries Client Version update needs to be run against it to create the table structure. Note: There is no need to connect to the new year database in AdminCS prior to running the Rollover in Web as long as the latest SQLModel is used.
The New Year Rollover process requires the website to be accessed via HTTPS. If HTTPS is not used a message will display in the Rollover section of the Processes tab indicating that the rollover must be run under HTTPS.
The administrator performing the rollover will also need to enter the DBO User Name and DBO Password to begin the rollover.
NOTE: This elevated SQL login is necessary because the rollover entails copying more than just data (e.g., security and some table definitions). At minimum, the login must be a member of the db_owner database role on both the current and new year databases and have the ALTER ANY LOGIN server-level permission. Using a login that is a member of the sysadmin fixed server role is one way to guarantee that permissions are adequate.
Prior to initiating the New Year Rollover, verifications should be performed to ensure that the data will be copied over correctly.
Current Year Database Verifications include:
- Run the utilities under Maintenance Functions in AdminCS on the current year database to help clean up the database prior to rollover – Repair Indexes and Relationships, Force Cascading DEL Tags, Database Cleanup, Fix ENR Records.
- Review all CALPADS requirements for exiting students. (Reference: Reporting_Graduating_Students_to_CALPADS.pdf, Reporting_Matriculating_Students_to_CALPADS.pdf, and CALPADS_Student_Withdrawal_Data_Population_Guide.pdf). Students that have End of Year Status (STU.EOY) populated may also need to have the STU.ENS populated. Please refer to the CALPADS_Student_Withdrawal_Data_Population_Guide.PDF for more information.
NOTE: The STU.ENS does not affect rollover; it is populated for CALPADS reporting.
- Verify School Options Low and High Grade Ranges (LOC.LO and LOC.HI) for all schools and the district. Districts should not have the district high grade, stored in LOC.HI, be above 12 even if they are using higher grade levels for Special Ed, Adult Ed or other students. The District high grade of 12 ensures that the graduating seniors are dropped during the rollover. Please note that the only reason a school's high grade, stored in LOC.HI, should ever be above 12, is if the low grade is also above 12 (i.e., Adult Ed with grade-range of 15-15). Students with grade levels higher than 13 will roll with the same grade level during the rollover if the Do Not Bump Grade option is selected for that grade level; otherwise, they will roll with the grade level in the Next Grade (STU.NG) field.
- Verify ALL schools have set up their pre-enrolled students with the correct next grade and that there is an asterisk (*) in the STU.TG field.
- Consider any changes that will need to be made to tables that are district owned such as the code table, course data, etc.
- New schools need to be added to the current school year database, including any grade level changes.
- Verify the Next School (STU.NS) field is populated with the school the students will be attending next year, even if the students are returning to the same school. The STU.NS can be populated with either the current school code if they are returning to the same school in the New Year database, the Next School code that they will be attending if they are moving to a different school within the district, or with the Inactive School code if they are leaving the district.
NOTE: the STU.NS field will determine which school the student will roll into during rollover.
- Verify that the Next Grade (STU.NG) field is increased by one from the current Grade field to ensure that the student will roll over properly (unless the student has been retained, is pre-enrolled, etc.).
- If the student will be assigned a teacher for the upcoming school year, verify the Next Teacher (STU.NT) field. If the student will be assigned a track for the upcoming school year, verify the Next Track (STU.NTR) field.
- If any of the following options will be used during the rollover, then the values stored in these fields will need to be verified:
Copy Next School of Residence to School of Residence – verify STU.NRS Copy Next Inter/Intra-District to Inter/Intra-District – verify STU.NIT and STU.NTD
Copy Next Program (s) to Program(s) – verify STU.NSP, STU.NP1, STU.NP2
Copy Next Grid Code to Grid Code – verify STU.NGC
- Check Cumulative Credits for seniors and determine if they will need to be retained and still held in the 12th grade when rollover is complete.
- Print the Pre-Rollover Audit Listing and the Table Record Counts (Pre) from the Pre-Rollover section of the Processes tab. Make any corrections necessary to minimize the errors on the Audit Listing.
New Year Database Set Up includes:
- From SQL Server Management Studio create a new Database for next year with same naming convention as this year and increase the year by one. For example, DST17000EagleUSD.
Detailed instructions are available for creating a new database in the Client Server Administration and Management Studio (CS_Administration_and_Management_Studio.pdf) document.
- Run the latest SQLMODEL.sql script (located in the directory where UPDATECS.exe was extracted to) against the new database for next year to create the table structure. This procedure MUST be done prior to rollover.
NOTE: SQLModel.SQL will make all tables in the new year database ID-based tables.
The New Year Rollover Settings tab contains all the options available for the New Year Rollover. The first time the user opens the New Year Rollover page the settings will be populated into the Rollover (ROL) table.
On the left side of the page is a list of all the schools in the database. Clicking on a school code or name will display the New Year Rollover options available for that school on the right side of the screen. The options are separated into 3 areas – District Wide, Inactive School, and School-Based.
When the District (school code 0) is selected all options can be viewed or updated.
When a School is selected only the School Based options can be viewed or updated.
Admins and users with update permission to the New Year Rollover will have the Change button and Push to Other Schools button available above the New Year Rollover settings. Clicking on the Change button will switch the screen to edit mode and the user will be able to select or de-select the rollover options for that school code. To save the settings click on the Update button.
Once all rollover options have been set up for that school code they can be quickly pushed to other schools that require the same settings by clicking on the Push to Other Schools button, selecting the schools that will receive the same rollover options, and clicking on the OK button.
The Green Check and Red X under the Push? header can be used to Select All or Deselect All schools with one click.
After the rollover options have successfully copied to the selected schools a message will display indicating that the Rollover Options Push has completed.
Continue reviewing/updating the rollover options for every school. Keep in mind that different types of schools (elementary schools, secondary schools, special schools) may use different rollover options.
District Wide and Inactive School Options
The District Wide and Inactive School options can be set up when the District (school code 0) school is selected.
- Clear existing District Resource Assignments will clear the data from the DRA table. Not selecting this option will copy over all the data in the DRA table.
- Increment credit requirements in Graduation Requirements. This option is available when the Graduation Requirements table contains a Subject Area code with different credit requirements values for the grade levels. If this option is selected the credit requirements will be incremented.
- Copy next year's schools to current year in Streets. This option is available when the Next School (STR.N#) fields are different than the School fields (STR.G#) in the Street table. Selecting this option will copy the Street Next School field into the Street School field.
- Only copy the latest set of Gradebook Backups. When selected, only the most recent backup of each gradebook number in the GBB table will be copied. Otherwise, all GBB records will be copied.
- Only copy the most recent Student Photo. When selected, only the most recent photo for each student in the PIC table will be copied. Otherwise, all PIC records will be copied.
- Bypass Teacher Briefcase Files (TFL) will not copy the Teacher Briefcase Files (TFL) records from last year.
- Bypass Student Backpack Files (SFL) will not copy the Student Backpack Files (SFL) records from last year.
- Bypass Medical Log (MED) will not copy the Medical Log (MED) records from last year.
- Bypass Attendance Notes (ATN) will not copy the Attendance Notes (ATN) records from last year.
- Copy Only Outstanding Fees will only copy the outstanding Fee (FEE) records from last year's database. Not selecting this option will copy all Fee Records.
- Close out open Authorizations on… enter a date into the Authorizations End Date (AUT.EED) that will be used to close out any open Authorization (AUT) records.
- Inactive School Code – enter the school code for the Inactive School.
- Inactive Status Tag – enter the Status Tag for currently active students that will roll into the Inactive school.
NOTE: Students that are not rolled forward into a valid school will be added to the Inactive School based on the values in the Inactive School Code and Inactive Status Tag fields, unless the "Bypass the Copy of Inactive School Process" option is selected (see below).
- Bypass the Copy to Inactive School Process – This option is for districts that have not traditionally maintained an Inactive School. If this option is selected, the Rollover process will NOT copy ANY students to an Inactive School. This means that students who became inactive during the year, as well as active students with an invalid Next School, will NOT be in next year's database and will therefore appear on the Missing Students report. This option should only be used by districts who understand the ramifications.
School Based Options
The School Based Options can be set up when the District (school code 0) school is selected or when another school code is selected.
- Do NOT Copy Student Records from This School will not process student records from this school. This option is usually used to skip processing schools that are set up as after school programs or summer schools where students are actively enrolled in this school and in their regular school.
- Bypass Master Schedule (MST) will not copy the Master Schedule (MST) records from last year
- Clear existing Textbook Assignments will clear the textbook assignment fields in the TBC table. Not selecting this option will copy over the data in the TBC table.
- Change New Students School Mobility to new grade. When selected and a student is added to a new school in the new year database, their School Mobility (STU.SM) is updated to their new year grade level.
- Change New Students Enter Date. When selected and a date is entered, as a student is added to a new school in the new year database, their Enter Date (STU.ED) will be updated to the date selected in this option.
NOTE: when the "Use Schools New Year Start Date when populated" option is selected in conjunction with this option, the date entered in the New Students Enter Date option will be used when the school's Next Year Start Date (LOC.NSD) is not populated.
- Use Schools Next Year Start Date when populated. When selected, the school's Next Year Start Date (LOC.NSD) will be used to populate the Student's Enter Date (STU.ED) for students that are added to a new school in the new year database. The Next Year Start Date field can be found in School Info | School Options page of Aeries Web Version.
- Copy Next Grade to Grade, add 1 to Next Grade. Will copy the Next Grade (STU.NG) field from last year into the Grade (STU.GR) field for the new year. It will also copy over the Next Grade field and increase it by one.
- Do NOT Bump Grades (14, 15, 16, 17) when the grade is checked, the rollover will not increase the Grade (STU.GR) in the new year.
- Only copy Student (STU) records under 25 years of age - This option will skip students 25 years of age and older.
- Keep the same Locker assignments will leave the locker number the same as the current year if previously assigned.
- Keep Secondary Counselors will leave the counselor (STU.CU) the same as in the current year.
NOTE: This option is meant for Secondary Schools only and should NOT be checked for Elementary and Elementary with Master schools
- Copy Next Teacher to Teacher will copy over the Next Teacher field (STU.NT) from last year into the CU field.
- Copy Next School of Residence to School of Residence - will copy over the Next Residence School field (STU.NRS) from last year into the Residence School field (STU.RS).
- Copy Next Inter/Intra District to Inter/Intra District will copy over the Next Inter/Intra District field (STU.NIT/STU.NTD) from last year into the IT/ITD field.
- Copy Next Track to Track will copy over the Next Track field (STU.NTR) from last year into the Track (STU.TR) field.
- Copy Next Program(s) to Program(s) will copy over the Next Program fields (STU.NSP/STU.NP1/STU.NP2) from last year into the Program fields (STU.SP/STU.AP1/ STU.AP2)
- Copy Next Grid Code to Grid Code will copy over any non-blank Next Grid Code field (STU.NGC) from last year into the Grid Code field (STU.GC).
NOTE: Some schools may have different settings. Remember to update all schools with the appropriate rollover settings. Also, the Inactive School does not need special School Based rollover settings configured because these settings are ignored anyway when a student is copied to the Inactive School.
The New Year Rollover Processes tab contains the reports and processes necessary to run the New Year Rollover. The Processes tab is divided into three sections to guide the flow of steps necessary to perform the rollover.
The Pre-Rollover Audit Listing is run on the current year database. This report needs to be run prior to starting the Rollover so that it can be used to locate and correct errors in student records that have potential rollover issues.
Clicking on the Pre-Rollover Audit Listing button will display the report options in a new tab. Select the appropriate options and click on the Run Report button.
The following report will be generated. This report can be used to locate errors in student records with potential rollover issues.
Refer to the following document for more information on this report: Pre_Rollover_Audit_Listing.pdf
The Table Record Counts (Pre) report displays a record count for every table in the Aeries database and should be run on the current year database after all records have been cleaned up and before the Rollover process has been initiated. Once Rollover has completed it should also be run on the New Year database. The two reports can then be compared to determine if the appropriate amount of records were copied to the new year. Clicking on the Table Record Counts (Pre) button will immediately run the report since there are no options to select.
The following report will be generated. Save this report or print it out for later reference.
As each report is run a message will display on the Processes tab with the last run date and time.
After the data in the current year database has been cleaned up and the Pre-Rollover reports have been run, the Rollover process can begin. We highly recommend that a test run of the rollover is performed on a copy of the current year database and results are verified before it is run against the live database.
Below are important points to remember:
- The New Year Rollover process requires that the website is accessed via HTTPS
- Only Admin user types can perform the Rollover.
- The New Year Database must be created in SQL Server Management Studio with the year incremented by 1 and using the same Database Suffix as the current year database. Also the current SQLModel.sql needs to be run against the New Year Database in order to create the Aeries Table Structure.
- The Admin user needs to populate the DBO User Name and DBO Password to perform the Rollover.
- The New Year Rollover can only be performed when the New Year Database has no records. If the New Year database already has records, a warning message will display:
- All the schools will be rolled to the New Year database with one run of the New Year Rollover. Separate runs of the "Regular", "Special" and "Inactive" schools no longer need to be performed. Therefore, the Settings tab must be correct for all schools prior to Rollover.
To perform the New Year Rollover enter the DBO User Name and DBO Password into the appropriate fields.
The Bypass Updating Scheduled Processes (only if testing the rollover) option allows users to test the Rollover without it updating the AeriesReporting database to the New Year. Select this option when testing the Rollover. When running the Rollover on live data this option should be de-selected.
Click on the Start Rollover button. A message will display asking if you want to Start the New Year Rollover. Click on the OK button to continue.
The screen will refresh with the DBO information blanked out and two messages will be displayed. One message will indicate that an email will be sent when the New Year Rollover has completed, and the other message will display the Start time of the Rollover.
The New Year Rollover in Aeries Web Version has been tested to complete in significantly less time than running the process in Aeries Client Version. An email will be sent to the Admin once the Rollover has completed, and the Processes screen will display the New Year Rollover completion date and time.
The Rollover Process
This section describes what actually occurs while the New Year Rollover process is running. Unlike the Aeries Client, all the steps described below are encompassed in a single Rollover process rather than multiple interactive steps.
- The School Supplemental (SUP) and District Supplemental (DSD) table definitions are copied.
- Data in non-student-related tables (e.g., LOC, TCH, CRS, STF, COD and many others) are copied.
- Some tables (e.g., DRA, REQ, STR, GBB, TFL, SFL, MST) have a specific Rollover Setting that will determine how that table is processed or if its records are copied at all.
- Scheduling totals will be cleared in the TCH, MST, and SMS tables. These totals can be recomputed after the Rollover by running the Master Schedule and/or Scheduling Master Schedule reports in the new year database.
NOTE: Non-student-related custom tables will NOT be copied at all. Both the table definitions and the data must be copied separately by the database administrator if these tables are needed in the new year database.
- Data in the IDN table and all tables related to IDN (except STU) are copied.
NOTE: Data in custom tables that have a foreign key relationship to IDN will be copied automatically; however, the SQL table definitions will not be copied. Therefore, all IDN-related custom tables must be created in the new year database using SQL Server Management Studio prior to initiating the Rollover process.
- Student (STU) records and STU-related records (e.g., SSD, SUP) are rolled over based on the Rollover Settings and in the following sequence:
- All pre-enrolled students (status tag *) are copied forward to the school in which they are pre-enrolled regardless of the Next School (STU.NS) value and made active, keeping their school-based Student Number (STU.SN). Therefore, it is critical to manage pre-enrolled records effectively prior to initiating the rollover.
- All active students that are NOT moving to a different school are copied forward to their same school as long as their Next Grade (STU.NG) is valid for that school, keeping their school-based Student Number (STU.SN).
NOTE: If a student has multiple active records (e.g., concurrent enrollment), it is possible for multiple records to be copied forward to different schools.
- All active students that are moving to a different school are copied to their Next School (STU.NS) as long as their Next Grade (STU.NG) is valid for that school. A student will NOT be copied to the same school more than once!
- All students that have NOT yet been copied to any school are copied to the Inactive School unless the Bypass the Copy to Inactive School Process option was selected.
NOTE: Students with Next Grade (STU.NG) = 13 are never copied (not even to the Inactive School). Students with Next Grade greater than the District High Grade (e.g., 8th grade completers in a K-8 district) also are not copied. However, students in the special grades 14-18 are exceptions and copy regardless of the District High Grade.
NOTE: Data in custom tables that have a foreign key relationship to STU will be copied automatically after each STU record is copied; however, the SQL table definitions will not be copied. Therefore, all STU-related custom tables must be created in the new year database using SQL Server Management Studio prior to initiating the Rollover process.
- Security is copied. This includes Aeries security (e.g., UGN, UGA, UGP, etc.) and SQL Server security (database users and roles).
There are two reports that should be run after the New Year Rollover has completed.
The Print Missing Students Report will compare the new year database to the current year database and create a report of all students who are not in the new year database. Clicking on the Missing Students Report button will display the report options in a new tab. Select the appropriate options and click on the Run Report button.
If all students rolled from the current year database to the New Year database, then the report should not have any data. Any students that do display on the report will need to be reviewed and appropriate steps will need to be taken to add these students to the New Year database where necessary.
NOTE: If the Bypass the Copy to Inactive School Process option was selected, the Missing Students Report will likely have many students listed.
Below is an example of the report where several students did not roll from the current year database to the New Year database.
The Table Record Counts (Post) report displays a record count for every table in the New Year database. This report should be run so that the record counts can be compared to the Table Record Counts (Pre) report to determine if the appropriate amount of records were copied to the new year. Clicking on the Table Records Counts (Post) button will immediately run the report since there are no options to select.
The following report will be generated. The Server and Database information is printed in the header to help identify which database the report was run against.
As each report is run a message will display on the Processes tab with the last run date and time.
3rd Party Integration Considerations
Considerations need to be made on how rollover and summer school could impact 3rd party software with which Aeries integrates.
Clever, one of our Partners in Education, has provided the following link to documentation on managing school-year rollover with Clever: