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.

...

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
   DROP ALTER PROCTABLE dv.RunValidationRules;ValidationResult DROP CONSTRAINT FK_ValidationResult_ValidationRunId
END
go



-- 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 9

New Version Current

Key

Introduction

Page Comparison

Versions Compared

Old Version 9

New Version Current

Key

Introduction