Run OnSIS Demographic Snapshot
Introduction
This report runs a process that stores the values of certain student demographic fields as of the OnSIS submission count dates entered on the District Setup > District Information page. Values for the following fields are stored in the ON_OnSIS_Snapshot table (except when calculating enrolment):
Grade Level
Province Of Entry
Country Of Entry
Entry Language
Postal Code
Board Residence Status
Residence Status
OYAP Flag
Independent Study Flag
Country (Current Residence)
Province (Current Residence)
Gender and Gender Identity (this field is only populated when Gender is Prefer to Specify (S))
Online Graduation Requirement
Each time the process is run, it determines whether values have already been stored for the latest count date that is earlier than or equal to the system date. If records have been stored for that count date, the process simply returns and no further processing is performed. If records have not been stored for that date, the process stores the value of each of the above fields for each student on the database along with the applicable count date. The stored values are used by the OnSIS submission reports for the appropriate submission period. The report can be run immediately or scheduled to run periodically.
The report also stores, in the S_ON_CC_X table, the number of work units completed by students in independent study classes on each of the October, March, and June the count dates. These values are used to calculate independent study ADE and High Credit ADE for OnSIS.
IMPORTANT NOTES:
This process must be run at least once prior to running an OnSIS Submission extract.
The OnSIS submission count dates must be entered on the District Setup > District Information page prior to running the demographic snapshot.
Selection Criteria
If the process determines that a snapshot is required, all records in the Students table where [Students]Entry_Date is on or after the earliest [Terms]FirstDay for any term for the current year.
Report Input
For help with navigation and running the report, see How to Find and Generate a Report.
Field | Description |
---|---|
Repopulate OnSIS Independent Study Work Units | If selected, the process re-calculates Work Units Completed for the previous submission period, overwriting previous values. This calculation is done regardless of whether or not the process takes a snapshot. This parameter should be selected only if it is necessary to create or correct Work Units Completed calculation for the previous submission. This parameter should not be selected if the report is being run for the current submission period. |
Re-run OnSIS Snapshot | Select to update OnSIS Snapshot Data for a Selected Count Date. ON submit the report should deletes the stored data, then insert new records for the selected count date. If Repopulate OnSIS Independent Study Work Units is selected this field is disabled and if this field is selected then Repopulate OnSIS Independent Study Work Units cannot be selected. |
Count Date | This field is only visible if the Re-run OnSIS Snapshot is selected. This field is Mandatory. It list of all count dates that are applicable to the selected year that cannot be processed using the regular processing. Dates are shown as defined in district. |
It is highly recommended that the report be scheduled to run nightly, at least as an OnSIS submission count date approaches. To schedule the process:
Select the Schedule option under Scheduling.
Enter an appropriate Start Date and Start Time.
Select the Daily option.
Click Submit.
Important Note:
When the Re-run Snapshot is ON, the user should only run the report manually. The report can still be scheduled but to run for the selected parameters only.
To schedule it to run, the user should select the count date, this will mean that as long a value is selected on the run page, the report will keep running for that count date over an over as per the schedule. unless it is only scheduled to run once or until the “Re-run OnSIS Snapshot “ parameter is deselected from the run page.
Snapshot Process
Demographics
When the Demographic snapshot process runs, either started manually by a user or as a scheduled event, the snapshot process operates as follows:
It looks in the PREFS table for the entries with the following names: "ON_AUG_DATE", "ON_JUN_DATE", "ON_MAR_DATE", and "ON_OCT_DATE".
It looks at the VALUE column for these entries. If any of them are "today", it knows it needs to run the snapshot process.
The process then looks at two other factors. First it looks at the last time it should have run. It determines this from the same PREFS entries above finding the most recent date (before today). It then looks at an entry in the PREFS table called "ON_ONSIS_CREATE_SNAPSHOT_last_run_date".
If it sees that it hasn't run since the last time it should have run, then it knows it needs to run the snapshot process. This is a safeguard to protect the process in case the system is down. If, for example, the snapshot is scheduled to run on March 30, but the system is down on that day (perhaps it's a long weekend and nobody realized it until April 1), then the snapshot process will not run. But it should have. When the process runs again (typically as a scheduled job), it will realize a scheduled run date was missed and run immediately.
If both of these checks determine a run is not necessary, the process returns without storing any values.
If either of these indicates that a snapshot is necessary it will begin by deleting the records from the ONSIS_SNAPSHOT table for the latest count date if any currently present in the table. It will then get the id, schoolid, grade_level, Entry_Country, Entry_Province, Entry_Language, Brd_Res_Status, Residence_Status, and Gender fields from the Students table. OYAP is obtained from the [S_ON_STU_X] extended schema table. Independent_Study is obtained from the [S_ON_CC_X] extended schema table.
If the snapshot process runs, it will then update the "ON_ONSIS_CREATE_SNAPSHOT_last_run_date" record in the PREFS table to today.
Also, the Snapshot Table ON_ONSIS_Snapshot now retains previous snapshots data. The number of previous snapshots that have to maintained in the database can be entered through the field "Number of Previous ONSIS Snapshots to Retain" under District -> District Info page.
When nothing is specified for the parameter under District info, it retains only the most recent count_date info else the snapshot table stores the current snapshot data and as many previous snapshots as mentioned under that parameter.
If the parameter "Number of previous ONSIS snapshots to retain" value is altered at any point and the snapshot process is run then immediately the snapshot table updates the table accordingly.
If the Student Met the Online Learning Graduation Requirement override flag is ON, then the Online Graduation Requirement field is updated to Successfully Completed (1).
Re-run OnSIS Snapshot
This feature provides the ability to rerun snapshot for a selected count date. for the current academic year. It allows users reruning the report for count dates that were previously run or could not be run through the regular process.
Important Considerations:
Please exercise caution when using this option as it is not reversible. Additionally, it is important to note that certain student data, such as gender and postal code, are not date-stamped. Therefore, the data collected during the rerun will reflect the data as of the rerun date, rather than the original count date. Same with the Online learning requirement: Optout/Epempt flag and Met the online requirement override flag may change the output after reruning the snapshot. The OYAP flag value might also be changed on rerun. This may result in inaccurate OnSIS reporting.
Re-run rules
ON submit that report should attempt to delete stored data for the count date, then insert new records for the selected count date.
The report will determine students to include based on the selected count date and create the snapshot records.
If the Student Met the Online Learning Graduation Requirement override flag is ON, then the Online Graduation Requirement field is updated to Successfully Completed (1).
If the count date falls in an enrollment then snapshot is created based on that enrollment
If the count date does not fall in any enrollment
If the count date is after the last day of the school then enrollment spanning last day of school is picked
OR
Picks the latest enrollment ( sorted by entryDate DESC, exitDate DESC, DCID desc) whose exitDate falls in submission period of the selected year
Overlapping Enrolment Student issue
Duplicate snapshot records will be generated if overlapping enrollment exists for the student.
To prevent duplicate snapshot records, the code will generate the record from the school with the latest Entry date.
The issue will be if the OnSIS report is run from the other school that does not have the snapshot record, data will be retrieved from the students/Reenrolment records (Which might be OK), but any changes to the student snapshot record in UI will not be reflected in the OnSIS report for that school. This behaviour will not be obvious to the user.
Scheduling When Rerun OnSIs Snapshot flag is ON
When the Re-run Snapshot is ON, It is preferrable that report is run manually. It can still be scheduled to run but for the selected parameters only.
When scheduled to run, the user should select the count date. but this will mean that as long a value is selected on the run page, the report will keep running for that count date over an over as per the schedule. unless it is only scheduled to run once or until the “Re-run Snapshot “ parameter is deselected on the report run page.
Work Units Completed
Current Submission
When the process determines, as described above, that a snapshot is to be taken, it performs the following processing for each class in which the student is enrolled in the current school year for which ([S_ON_SEC_X]IsIndependentStudy_Class or [S_ON_CC_X]IsIndependentStudy_Class) is true:
If the process is being run for the October count date, either [S_ON_CC_X]NumberOfCompletedUnits, if it is defined and non-zero, or, if not, the count of records for the class in the [S_ON_CC_WKUNOLM_C] table for which a mark is defined is stored in [S_ON_CC_X]WorkUnitsOctober. If WorkUnitsOctober is already defined (i.e. is not null), the process does not update the field. If a record does not exist for the class, it is created.
If the process is being run for the March count date, either [S_ON_CC_X]NumberOfCompletedUnits, if it is defined and non-zero, or, if not, the count of records for the class in the [S_ON_CC_WKUNOLM_C] table for which a mark is defined is stored in [S_ON_CC_X]WorkUnitsMarch. If WorkUnitsMarch is already defined (i.e. is not null), the process does not update the field. If a record does not exist for the class, it is created.
If the process is being run for the June count date, either [S_ON_CC_X]NumberOfCompletedUnits, if it is defined and non-zero, or, if not, the count of records for the class in the [S_ON_CC_WKUNOLM_C] table for which a mark is defined is stored in [S_ON_CC_X]WorkUnitsJune. If WorkUnitsJune is already defined (i.e. is not null), the process does not update the field. If a record does not exist for the class, it is created.
Previous Submission
If the "Repopulate OnSIS Independent Study Work Units" parameter is selected, the following processing is performed:
If the process is run prior to the October count date, no processing occurs.
If the process is run on or after the October count date and before the March count date, either [S_ON_CC_X]NumberOfCompletedUnits, if it is defined and non-zero, or, if not, the count of records with a date on or before the October count date for the class in the [S_ON_CC_WKUNOLM_C] table for which a mark is defined is stored in [S_ON_CC_X]WorkUnitsOctober. If WorkUnitsOctober is already defined (i.e. is not null), the process overwrites the field. If a record does not exist for the class, it is created.
If the process is run on or after the March count date and before the June count date, either [S_ON_CC_X]NumberOfCompletedUnits, if it is defined and non-zero, or, if not, the count of records with a date on or before the March count date for the class in the [S_ON_CC_WKUNOLM_C] table for which a mark is defined is stored in [S_ON_CC_X]WorkUnitsMarch. If WorkUnitsMarch is already defined (i.e. is not null), the process overwrites the field. If a record does not exist for the class, it is created.
Note: This processing is done independently of whether the process takes a snapshot or not. This parameter should be checked only if it is necessary to create or correct Work Units Completed as calculated in the previous submission, prior to running for the current submission. It should not be checked when being run for the current submission period.
Report Output
Except for storing data as described above the report creates no output other than a blank HTML page.
Each of the fields displayed in the output of the report are described below. See Understanding the Report Output Table for a definition of each column in the table.
Demographic snapshot data is stored in the ON_ONSIS_SNAPSHOT table:
Column Name | Comments | Data Type |
---|---|---|
ID | Primary key generated by the snapshot process | Integer (not null) |
COUNT_DATE | Count date as defined in the PREFS table for the current school academic year, submission period type. | Date (not null) |
STUDENTID | From Students table, indexed | Integer (not null) |
SCHOOLID | From Students table, indexed | Integer (not null) |
Grade_Level | From Students table, -1, 0, 1 to 12 | Integer |
ON_Entry_Country | From Students table | Varchar2(3) |
ON_Entry_Province | From Students table | Varchar2(3) |
ON_Entry_Language | From Students table | Varchar2(7) |
Zip | From Students table | Varchar2(10) |
ON_Brd_Res_Status | From Students table | Varchar2(3) |
ON_Residence_Status | From Students table | Varchar2(3) |
OYAP | From S_ON_STU_X table | Varchar2(1) |
Independent_Study | From S_ON_CC_X table | Varchar2(1) |
ON_Timetable_Type | From Students table (note that starting in 2014-2015 this value will be null) | Varchar2(3) |
Curr_Country | From S_ON_STU_X | Varchar2(3) |
Curr_Province | From S_ON_STU_X | Varchar2(3) |
Gender | From Students table | Varchar(2) |
CREATETMS | Default to 'sysdate' | Timestamp (not null) |
CREATEDBY | Default to 'system' | Varchar2(25) (not null) |
ON_ONLINE_GRAD_REQ | [ON_OnSIS_Snapshot]On_Online_Grad_Req | |
STUMATURE | [ON_OnSIS_Snapshot]STUMATURE When OnSIS Demographic Snapshot report is run on the count date, the value is copied from the student’s record When the report is run/rerun after the count date, the value is only copied if the student is 18 or over on Aug 31 of the current school year. | Varchar2(1) |
Work units completed snapshot data is stored in the ON_S_CC_X table:
Column Name | Comments | Data Type |
---|---|---|
CCDCID | Identifies the associated class. | Number(11,0) |
STUDENTID | Identifies the student. | Number(11,0) |
WORKUNITSOCTOBER | Number of work units completed in the class as of the October count date. | Number(11,0) |
WORKUNITSMARCH | Number of work units completed in the class as of the October count date. | Number(11,0) |
WORKUNITSJUNE | Number of work units completed in the class as of the October count date. | Number(11,0) |