Description

Discuss the need for accurate data, and show how Data Validation can be a valuable tool to help districts find and correct issues.


Time Allotment

120 Minutes


Expected Outcomes


Upon completing this Aeries conference session, attendees should be familiar with:

  • Data Validation Results
  • Data Validation Configuration
  • Data Validation Definitions
  • Creating new definitions


Content:


Data Validation

This powerful feature enables rules to be defined for any criteria, and then the data is validated on a daily basis automatically, or on demand, and reports any data anomalies 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. 


Results

The 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. The user can click a button to Keep all the students easily and make corrections. A hyperlink can be added to allow the user to easily click on a page. 


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. 


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. 


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.


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. 


Definition Rules

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 on 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 (e.g. [UID?] for a "UID" column).
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.



Attached are the Data Validation Groups and Definitions used in the demo (Security and Aeries Communications). Also included is the automated population of the Attendance Percentage by Date Range table (requires the APDS table).