Page Comparison

In this primer on data quality, you will walk through the process of checking your data for errors via provided stored procedures. In the Introduction to API - Quick Start you were guided to explore the API through the use of Postman.  The first collection, SEA Starter Kit, that was run not only exercised the API by executing GET/POST requests but introduced a couple of data anomalies for use in this validation guide. 

NOTE: In the API guide you were instructed to import the Postman environment and collection.  There is a second collection called "SEA Modernization Starter Kit Rectification" that is required for this Validation exercise, so if you imported it as well as you worked through the Introduction to API guide, then you are set. If you did not, please import it using Postman as you did the others but do not run that collection yet.

Now it's time to run our validation process. Using SQL Server Management Studio (SSMS) connect to your server and in the Object Explorer navigate to your Ed-Fi ODS.  Expand the "Programmability" folder and then "Stored Procedures."  You will find two included stored procedures that will perform some validation checks on your data. 

Execute each of these stored procedures by right-clicking on one of them and selecting "Execute Stored Procedure."

You will be prompted to provide values for the parameters, so proceed to add the values "all" and "2022" for @StateOrganizationId and @Datayear respectively as shown below and click "Ok" to execute the procedure.

As each stored procedure executes, you will be able to monitor the results in your SSMS window. The following example shows result of executing "dbo.ValidationError_705" below. You'll notice that the validation procedure found one student record with over 100% FullTimeEquivalency, triggering inclusion in the validation report. 

Once you've run both "dbo.ValidationError_705" and "dbo.ValidationError_743" you can navigate to the "Tables" folder in the Object Explorer and expand it to view the database tables. Locate "dbo.DistrictErrorStaging." This table is where validation errors created by the above stored procedures are stored. 

You can now write a simple query to view the records on the table, or simply right-click and select "Select Top 1000 Rows" if you have fewer errors on the table and have no need to filter the results. 

The result of the query for our example shows all the errors both stored procedures found and stored to the "dbo.DistrictErrorStaging" table.

Now that we've worked through running validations and examining the errors, let's use another Postman collection to resolve the errors.  Open up Postman as an Administrator if it's not already open.  Navigate to the Collections tab on the left side of the application screen and select it.  You'll see the collections you've imported, including the "SEA Modernization Starter Kit Rectification" collection we covered in the opening section of this guide.  Locate the collection and select it, then click on the "Run" button.

This will pull up the Runner window in Postman. Just like when you ran the initial collection, you will see all of the GET/POST requests selected. Click on "Run SEA Modernization Starter Kit Rectification" button as shown below. 

A successful run will show all "Pass" values and status codes of 200 or 201. 

After the Rectification collection has run, return to SSMS and rerun both Validation stored procedures.  Your output should now show no errors, as the Rectification collection has resolved the data errors.

NOTE: Currently, the stored procedures do not delete records from the "dbo.DistrictErrorStaging" table once errors have been resolved. You should choose to delete the table records and rerun the validation procedures to get current results in the table, or rely solely on the output of the procedures as the source of truth for current error status.