Prepared for the Ed-Fi Alliance by: Jonathan Hickam, Learning Tapestry

Contents

Table of Contents

maxLevel	2

Introduction

This document has been prepared as part of a larger initiative that is looking at scalable, economical, and reusable solutions for level 2 validations. For further context, architecture, and vocabulary refer to the associated Ed-Fi Validation Architecture document.

A high-level assessment of a wide variety of open source solutions was undertaken that could potentially address all or some of the Ed-Fi Level 2 Data Validation System Requirements. A high-level analysis of the tools that were considered can be found at Survey of Rule Engines. However, the final approach taken was to take a deeper look at three different types of solutions from this list. The first one was to look at an open-source business rules engine . The second was to experiment with using a database-centric testing framework. The third was to look at what a pure-SQL implementation without external tool dependencies might look like.

Option 1: Business rule engine solution

Our initial hypothesis when we started this evaluation was that one of the many open source business rules engines would be able to address a significant part of our requirements. During our analysis we settled in on two Java-based business rules engines, Drools (https://www.drools.org/) and Open Rules (https://openrules.com/). For our prototype we decided to try Open Rules, which is geared more towards business analysts instead of Java developers.

...

If an organization is already using a business rules infrastructure, it could be adapted to address the validation engine requirements. This would be especially well suited for complex validations that had multiple decision points. However, for an organization that is just looking to solve for the requirements identified, the overhead and complexity of this type of solution would be hard to justify.

Option 2: Testing framework solution

Test driven and continuous integration development methodologies are widely considered best practices in contemporary software development shops, especially those embracing agile development. The ubiquity of these approaches has led to a wide variety of automated testing frameworks, some of which are very database-centric. Our hypothesis is that such a tool could be useful for implementing a data validation testing infrastructure.

...

Testing infrastructures do provide a tool set that would allow for a standardized methodology for storing and defining tests. Potentially this could be leveraged for data validation tests, but the toolset would have to be heavily customized so that it would output specific instances of non-compliant data and so that it would output results to the API. If an organization was already heavily invested in a testing infrastructure this work could be justified, otherwise it might be a hard sell because the amount of effort to implement the customizations is probably less then just doing something from the ground up (per the previous example).

Option 3: Pure SQL Ed-Fi Data Validation Proof-Of-Concept

Overview

This solution shows a simplified version of a data validation engine built using only dynamic SQL.

...

Obtain the Glendale sample database from here and restore it to a local SQL Server database.
Change to the local SQL Server database.
Create a new schema 'dv' for the data validation objects.
Code Block
language sql
create schema dv;

Create the Validation API tables and constraints.

IF

Code Block

language	sql

language	sql

Create the rules table.

Code Block

language	sql

Create the stored procedure.

collapse	true

-- drop FK(s) at the beginning
IF (OBJECT_ID('dv.RunValidationRulesFK_ValidationResult_ValidationRunId', 'PF') IS NOT NULL)
BEGIN
    DROPALTER PROC dv.RunValidationRulesTABLE dv.ValidationResult DROP CONSTRAINT FK_ValidationResult_ValidationRunId
END

-- tracking table for validation runs
IF OBJECT_ID('dv.ValidationRun', 'U') IS NOT NULL 
BEGIN
  DROP TABLE dv.ValidationRun; 
END
  

IF OBJECT_ID('dv.ValidationRun', 'U') IS NOT NULL 
BEGIN
  DROP TABLE dv.ValidationRun; 
END
  
Create table dv.ValidationRun
	(ValidationRunId int not null identity primary key,
	ValidationRunStatus varchar(60)  not null,
	ValidationRunStartDateTime datetime not null default current_timestamp,
      ValidationRunSource varchar(60),
	ValidationRunFinishDateTime datetime);

--tracking table for validation results
IF OBJECT_ID('dv.ValidationResult', 'U') IS NOT NULL 
BEGIN
  DROP TABLE dv.ValidationResult; 
END
 
Create table dv.ValidationResult
	(ValidationResultId int not null identity primary key,
	ValidationRunId int ,
	ValidationRuleId Varchar(60) not null,
	ValidationResourceId varchar(60) not null,
	ValidationResourceType varchar(60) not null,
    ValidationSeverity varchar(60),
EducationOrganizationId int,
NameSpace varchar(255),
ValidationDetails varchar(2056),
ValidationCategory varchar(60),
	ResultCreatedTime datetime default current_timestamp);



-- foriegn key
ALTER TABLE dv.ValidationResult 
	ADD  CONSTRAINT FK_ValidationResult_ValidationRunId FOREIGN KEY(ValidationRunId)
		REFERENCES dv.ValidationRun (ValidationRunId)
GO

Create the rules table.

Code Block

language	sql
collapse	true

IF OBJECT_ID('dv.ValidationRules', 'U') IS NOT NULL
BEGIN 
  DROP TABLE dv.ValidationRules; 
 END

create table dv.ValidationRules
(ValidationRuleId Varchar(60) not null primary key,
ValidationSQL Varchar(2024) not null,
ValidationRuleSet	varchar(60),
ValidationSeverity varchar(60),
ValidationCategory varchar(60),
RuleDescription Varchar(2024)
)
go

Create the stored procedure.

Code Block

language	sql
collapse	true

IF OBJECT_ID('dv.RunValidationRules', 'P') IS NOT NULL
BEGIN 
  DROP PROC dv.RunValidationRules; 
 END
go


CREATE PROC  dv.RunValidationRules  @ValidationRuleSet varchar(60)
AS
	DECLARE @ValidationRuleId  varchar(max);
	DECLARE @ValidationSQL  varchar(max);
	DECLARE @ValidationSeverity  varchar(max);
	DECLARE @ValidationCategory  varchar(max);
	
	Declare @RunId INT;

	-- build a cursor that gets all of the validations for a given rule set
	DECLARE db_cursor CURSOR for 
		SELECT ValidationRuleId,
				ValidationSQL,
				ValidationSeverity,
				ValidationCategory
		FROM dv.ValidationRules
	WHERE ValidationRuleSet = @ValidationRuleSet;


	-- insert a row in the tracking table
	insert into dv.ValidationRun(
	validationRunStatus, ValidationRunSource) VALUES
	('Running', @ValidationRuleSet);

	--capture the ID from that insert
	SELECT @RunId =  SCOPE_IDENTITY() ;

	OPEN db_cursor;

	FETCH NEXT FROM db_cursor INTO @ValidationRuleId,
				@ValidationSQL,
				@ValidationSeverity,
				@ValidationCategory;
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- sub in the following values
		set @ValidationSQL= REPLACE(@ValidationSQL,'~SEVERITY~',@ValidationSeverity);
		set @ValidationSQL= REPLACE(@ValidationSQL,'~VALIDATION_RULE_ID~',@ValidationRuleId);
		set @ValidationSQL= REPLACE(@ValidationSQL,'~VALIDATION_CATEGORY~',@ValidationCategory);
		set @ValidationSQL= REPLACE(@ValidationSQL,'~VALIDATION_RUN_ID~',@RunId);
		set @ValidationSQL = concat('
			INSERT into dv.ValidationResult
				(ValidationRunId, ValidationRuleId, ValidationResourceId, ValidationResourceType, ValidationSeverity,
				EducationOrganizationId, Namespace, ValidationCategory, ValidationDetails) 
				 ', @ValidationSQL);
	
		print(@ValidationSQL);
		exec(@ValidationSQL);
				
	FETCH NEXT FROM db_cursor INTO @ValidationRuleId,
				@ValidationSQL,
				@ValidationSeverity,
				@ValidationCategory;

	-- Tell the tracking table we are done
	
	update dv.validationRun
	set ValidationRunStatus = 'Finished',
		ValidationRunFinishDateTime = current_timestamp
	where ValidationRunId = @RunId;
	END

	close db_cursor
	DEALLOCATE db_cursor
go

Insert the sample rules into the rules table.

Code Block

language	sql
collapse	true

INSERT INTO dv.ValidationRules (ValidationRuleId, 
ValidationSQL,
ValidationRuleSet,
ValidationSeverity,
ValidationCategory,
RuleDescription)
VALUES
('1.1',
'SELECT top(10)
	~VALIDATION_RUN_ID~ ValidationRunId,
	''~VALIDATION_RULE_ID~'' ValidationRuleId,
	StudentUniqueId as ValidationResourceId,
	''Student'' as ValidationResourceType,
	''~SEVERITY~'' as ValidationSeverity,
	SchoolId as EducationOrganizationId,
	'''' Namespace,
	''~VALIDATION_CATEGORY~'' ValidationCategory,
	'''' ValidationDetails
FROM (	
	select DISTINCT
		c.StudentUniqueId ,
		SchoolId
	from edfi.student c
	JOIN edfi.StudentSchoolAssociation on c.studentUSI = StudentSchoolAssociation.StudentUSI
	LEFT JOIN 
		(SELECT studentUSI, b.CodeValue
		FROM edfi.studentAddress a
			JOIN edfi.AddressType b	on a.AddressTypeId = b.addressTypeId
		and b.CodeValue in ( ''Physical'', ''Home'') 
		) d on c.studentUSI = d.studentUSI
		)e',
'nightly',
'Warning',
'Student',
'Raise a validation warning for every student that does not have a physical address specified. Limit result set to 10.');



INSERT INTO dv.ValidationRules (ValidationRuleId, 
ValidationSQL,
ValidationRuleSet,
ValidationSeverity,
ValidationCategory,
RuleDescription)
VALUES
('1.2',
'SELECT top(10)
	~VALIDATION_RUN_ID~ ValidationRunId,
	''~VALIDATION_RULE_ID~'' ValidationRuleId,
	StudentUniqueId as ValidationResourceId,
	''Student'' as ValidationResourceType,
	''~SEVERITY~'' as ValidationSeverity,
	SchoolId as EducationOrganizationId,
	'''' Namespace,
	''~VALIDATION_CATEGORY~'' ValidationCategory,
	ValidationDetails
FROM (
SELECT distinct studentUniqueId, SchoolId,
concat(''exit date '',ExitWithdrawDate) ValidationDetails
from edfi.studentSchoolAssociation JOIN
     edfi.Student on student.StudentUSI = StudentSchoolAssociation.StudentUSI
where ExitWithdrawDate is not null
and ExitWithdrawTypeDescriptorId is null) tab',
'nightly',
'Major Validation Error',
'Student',
'Raise a major validation error for every StudentSchoolAssociation where there is an ExitWithdrawDate specified but not an ExitWithdrawDescriptor specified. Limit result set to 10.');

Run the stored procedure and check out the results.

Code Block

language	sql

exec dv.RunValidationRules 'Nightly';
Select * from dv.ValidationRun;
Select * from dv.ValidationResult;

...

Versions Compared

Old Version 7

New Version Current

Key

Introduction

Option 1: Business rule engine solution

Option 2: Testing framework solution

Option 3: Pure SQL Ed-Fi Data Validation Proof-Of-Concept

Overview

Page Comparison

Versions Compared

Old Version 7

New Version Current

Key

Introduction

Option 1: Business rule engine solution

Option 2: Testing framework solution

Option 3: Pure SQL Ed-Fi Data Validation Proof-Of-Concept

Overview