The Data Validation system in Aeries is a powerful feature that enables rules to be defined for any criteria, and then data is validated on a daily basis automatically, or on demand. Reports of any data anomalies are sent to the appropriate staff who can take action to correct the bad data. Users can view all validations on the Data Validation Results page for their school.


Data Validation Results

Below is an example of the Data Validation Results page.



The end user sees all the results on the Data Validation Results page for the results they have permissions to. The My Results tab displays results that the user is responsible for handling, whereas the All Results tab displays all results the user would have permission to. 


Up to two hyperlinks can be added to the Validation Definition to allow the user to  be taken to the page in Aeries where the issue exists or information can be found.




The Keep Students will Keep the listed students for easier data correction.



Configuration

The Data Validation Config page is used to initially set up Data Validations, but also to maintain the Data Validation Groups which is an integral part of the data validation system. The data validation engine runs as a scheduled task, which processes the data, and sends alerts via email to end users. The Scheduled Process can be configured with a specific time each day and days of the week in the Scheduled Process section under System Setup. When the scheduled process runs, it always sends the summary email to end users. This email is optional when the process is run from the admin tab of the Data Validation results page because it can be run anytime as to not bombard users with mostly redundant emails.



The Aeries Defaults section displays an Add/Update Defaults button. Clicking this button will add a set of default rules to AeriesWeb. Loading the defaults is recommended to get a feel for how the system works. These definitions can then be edited or inactivated as desired. When loading the defaults, regular schools are selected by default, however no users or groups are selected to be responsible for the rules. No users will see the results of these rules unless they have appropriate permissions as described in the next section. A list of all the current Default Data Validations is attached below. 



Note: When editing an existing default definition, the name will be automatically changed when the definition is saved to indicate that the definition has been altered. Any default definition may be recreated or duplicated if the original was altered by reloading the default definitions. Definitions can also be disabled to prevent duplication of messaging efforts if that is desired.


Security

Security for Data Validations is broken into two-parts, similar to the way security works for student documents. In order for an end user to have access to the Data Validations Results page, they need to have access to the Data Validation Results (DVR) security area. In order for them to see specific results, they also need access to whatever security is defined for the Data Validation Group. In addition, users will not receive the summary email unless they are considered a Responsible User as defined in the Data Validation Group, nor will they see the My Results tab of the Data Validation Results page.


Note: Because editing definitions feature requires the use and knowledge of backend Aeries SQL databases, Multi-Factor Authentication is also required to access the Data Validation Definition's page. Tip: This can be configured to not impact any users except Admins, and only those admins who visit the Data Validation Definitions page by checking the Secure Areas Independently box, but NOT checking any of the Internal or External checkboxes. This will enable multifactor for only the Data Validation Definitions page.


Data Validation Groups

Groups contain the various Data Validation definitions, and also contain various settings which will be applied to the validation definitions associated with the group. The settings for the group are accessible from the Data Validation Config page.


Each Data Validation Group can have Responsible Users defined. This deems these specific users (or users contained within specific security groups) responsible for managing the errors contained within the group. Once the user is assigned, they will see a My Results tab on the results page containing the results of the validations. This lists errors for any results they are responsible for. These users will also receive an email summary at the time specified in the scheduled process.



Note:  Data Validation Responsible Groups and Security Groups are different. Each desired Data Validation Responsible Group must be added to the desired Data Validation. Nested Security Groups are not processed as being a Data Validation Responsible Group.


Data Validation Definitions

Validation Definitions are the rules which are run against the database whenever the data validation process is run. This is where the Aeries Administrator will likely spend most of their time working with these definitions, making adjustments as needed, or at a minimum reviewing statistics about the validations.


This page lists all data validation definitions which have been defined. There are filtering options at the top of the page to filter for Active only, Student-related only, or to display specific groups. There is also a search text option which can be used to filter the list.



Clicking on one of the definitions will expand the rule and display the detailed settings which can be edited. The bottom line will display some statistics on this definition including First Encountered, Last Encountered, Last Run Count, and Last Execution Time in milliseconds. This data can be used to analyze usage, as well as look at performance of specific queries.



When editing a rule, the following fields can be defined. Below is an explanation about how each field is used.


Short Name - A short name or code for the rule. For example, AERIES001, or DIS005. Use a naming convention that is easy for your district and users to reference.

Description - A short but descriptive description of the rule. For example, "Invalid Contact Email Address" Note: This is different than the description column for the results.

Validation Group - This is the group that this validation definition belongs.

Key Field 1 and 2 - These are up to two columns that will display on the Data Validation Results page. These can be friendly names. For student-related definitions, Key Field 1 would likely be Student ID.

Link to Page 1 and 2 - Specific page in Aeries can be selected. If selected a hyperlink to that particular page and particular record. Use the SQL syntax below to properly define your query to automatically be taken to the correct record for supported pages.

Severity Level - This is a code-table dropdown currently just used as a descriptor for reference. It will be displayed on the Results page.

Student Related - Set to Yes if this is a student-related rule

User Can Refresh - The recommended settings is Yes so that end-users can click a Refresh button on the Results page, however this can be turned off in rare cases where you don't want an end-user to be able to refresh a particular result.

Disabled - This is used to disable a particular rule and prevent it from running or displaying to end users. Note: The Group can also be disabled, which will also disable all rules within that group.

Start and End date - If specified, the definition will only run during this date range. This is useful for rules that are only applicable during a certain time of the year. Outside of the date range, the rule is considered disabled.

Possible Cause - A description displayed to the end users on the results page on what might be the cause. HTML formatting is supported. This is optional.

Possible Solution - A description displayed to the end users on the results page on what might be the solution to this problem, or how users should resolve the issue.

Query Definition - SQL query. The below SQL Syntax should be kept in mind when creating or editing queries. It is recommended to look at the sample defaults to get an idea of the formatting. There is a Test button to test your SQL query before saving.


SQL Syntax Notes for Validation Definition


@SchoolCode

If the Validation Group is School-scoped, then this variable must appear in the SQL query.  A parameter will be added during execution so that the query is run for one school at a time.  This is essential for properly breaking down results by school.  If the Validation Group is District-scoped, this should be omitted unless - for some reason - you actually need something filtered for school code 0.

AND STU.ID IN (@StudentID)

For student-related Validation Definitions, this should be included somewhere in the WHERE clause.  During regular execution, the @StudentID will be replaced with one of the following:

  • If the query is District-scoped, @StudentID will be replaced with "SELECT ID FROM STU WHERE DEL = 0"
  • If the query is School-scoped, @StudentID will be replaced with "SELECT ID FROM STU WHERE DEL = 0 AND SC = @SchoolCode
  • If the query is being run for a single student (future enhancement), @StudentID will be used as an actual parameter, taking the value of the individual student's ID.
Key Field 1Key Field 1 is required for each Validation Definition.  The name given for Key Field 1 must appear as a column name in the SQL output.
Key Field 2Key Field 2 is optional for each Validation Definition.  The name given for Key Field 2 should appear as a column name in the SQL output.
DescriptionThe SQL output should have a column named "Description".  This, along with the school code and the key field(s), forms the business key for a Validation Result record. The Description field is used to store additional information that could help the user identify the specific record that may have the issue. It could be blank, but likely is most useful if it contains concatenated variable data.
StudentIDFor student-related Validation Definitions, there should be a column named "StudentID", which may or may not also be a Key Field (probably will be in most cases).  This causes the Student ID field to be populated in the Validation Result (DVR.SID). Note: an optional space is supported (i.e., "Student ID").  No other names are supported at this time (e.g., ID, Permanent ID, Perm ID, PID, etc.).
TargetedStaffIDIf this column name exists in the SQL output, it will populate the Targeted Staff ID field in the Validation Result (DVR.TID).  This limits "My Results" to an individual, rather than anyone in the Responsible Users/Groups.
?Any column name in the SQL output that ends with a question mark will be added to the Query String in the Validation Result (DVR.QS).  On the Validation Results page, this will be appended to the deep link URL for each record.
SchoolCodeIf this column name exists in the SQL output, it will populate the School Code in the Validation Result (DVR.SCL).  Otherwise, DVR.SCL will be populated with the school code of the current execution (see the @SchoolCode parameter).  Think of this as an override to control the school to which the result is associated, or to associate the actual school code with a District-scoped result for query/reporting purposes.