App.FS032_TestCase
Overview & Purpose
This stored procedure is designed to test and validate the accuracy of student count data in the EdFacts reporting system. It creates test cases for various category sets (CSA through CSF, ST1, and TOT) across different reporting levels (SEA, LEA, SCH) and compares expected results with actual results from the reporting system.
Main Functions:
Student Count Validation
To verify that student counts in the EdFacts reporting system match expected counts based on source data
Test Case Management
To create, track, and record test case results for data validation
Key Calculations:
Student Count Validation: To count unique students across various demographic and educational categories
Formula:
COUNT(DISTINCT StudentIdentifierState)Business Significance: Ensures accurate reporting of student populations for federal/state compliance
Example: Counting students by grade level, race, and sex for the CSA category set
Test Case Management: To determine if expected and actual student counts match
Formula:
CASE WHEN s.StudentCount = ISNULL(Fact.StudentCount, -1) THEN 1 ELSE 0 ENDBusiness Significance: Provides a pass/fail indicator for each test case
Example: Comparing expected student counts from staging data with actual counts in the reporting system
Data Transformations:
Creating temporary tables for each category set and reporting level combination
Aggregating student counts by various demographic and educational attributes
Comparing calculated counts with stored counts in the reporting system
Recording test results in the App.SqlUnitTestCaseResult table
Expected Output:
The procedure populates the App.SqlUnitTestCaseResult table with test case results, including test case names, details, expected and actual counts, and pass/fail indicators.
Business Context
System: EdFacts Reporting System
Necessity: To ensure accurate reporting of student demographic and educational data to federal and state authorities
Business Rules:
Student counts must be calculated based on distinct student identifiers
Students must be categorized by various demographic and educational attributes
Data must be aggregated at SEA (State), LEA (District), and SCH (School) levels
Test cases must validate all required category sets (CSA through CSF, ST1, and TOT)
Result Usage:
Results are used to validate the accuracy of the EdFacts reporting system and identify discrepancies that need correction
Execution Frequency:
Likely executed on an as-needed basis during testing cycles or after data updates
Critical Periods:
Before EdFacts submission deadlines
After major data updates or system changes
Parameters
@SchoolYear
SMALLINT
Specifies the school year for which to run the test cases
True
Source Tables
debug.vwStudentDetails
Business Purpose: Provides student demographic and educational data for analysis
Columns:
SchoolYear
Unknown
Identifies the academic year
StudentIdentifierState
Unknown
Unique identifier for students within the state
GradeLevelEdFactsCode
Unknown
Standardized code for student grade level
staging.K12Enrollment
Business Purpose: Stores student enrollment data
Columns:
StudentIdentifierState
Unknown
Unique identifier for students within the state
LeaIdentifierSeaAccountability
Unknown
Identifier for the Local Education Agency (district)
RDS.ReportEdFactsK12StudentCounts
Business Purpose: Stores the actual student count data for EdFacts reporting
Columns:
ReportCode
Unknown
Identifies the specific EdFacts report
ReportYear
Unknown
Identifies the reporting year
StudentCount
Unknown
The count of students for a specific category combination
Temporary Tables
#vwStudentDetails
Purpose: Temporary storage of filtered student details from the source view
Columns:
SchoolYear
Unknown
Direct copy from source
StudentIdentifierState
Unknown
Direct copy from source
GradeLevelEdFactsCode
Unknown
Derived from GradeLevelEdFactsCode with transformation for grades below 7
#staging
Purpose: Combines student details with additional attributes needed for testing
Columns:
All columns from #vwStudentDetails
Various
Direct copy from source
EconomicDisadvantageStatusEdFactsCode
Unknown
Derived from staging.PersonStatus.EconomicDisadvantageStatus
MigrantStatusEdFactsCode
Unknown
Derived from staging.PersonStatus.MigrantStatus
HomelessnessStatusEdFactsCode
Unknown
Derived from staging.PersonStatus.HomelessnessStatus
#SEA_CSA, #LEA_CSA, #SCH_CSA (and similar for other category sets)
Purpose: Store aggregated student counts for each category set and reporting level
Columns:
GradeLevelEdFactsCode, RaceEdFactsCode, SexEdFactsCode (varies by category set)
Unknown
Direct copy from #staging
StudentCount
Unknown
COUNT(DISTINCT StudentIdentifierState)
Potential Improvements
Error Handling
Description: Add comprehensive error handling with TRY/CATCH blocks and specific error messages
Benefits: Better troubleshooting and more robust execution
Priority: Medium
Performance
Description: Add appropriate indexes to temporary tables for better join performance
Benefits: Faster execution, especially with large data volumes
Priority: Medium
Code Organization
Description: Refactor repetitive code blocks into dynamic SQL or table-driven approach
Benefits: Reduced code size, easier maintenance
Priority: Low
Documentation
Description: Add more detailed inline documentation about business rules and data transformations
Benefits: Better understanding for future maintainers
Priority: Low
Execution Steps
Step 1: Initialize by dropping any existing temporary tables
Input Data: None
Transformations: None
IF OBJECT_ID('tempdb..#Staging') IS NOT NULL DROP TABLE #Staging
Step 2: Set up unit test metadata
Input Data: Test name, procedure name, scope, and report code
Transformations: None
IF NOT EXISTS (SELECT 1 FROM App.SqlUnitTest WHERE UnitTestName = @UnitTestName) BEGIN INSERT INTO App.SqlUnitTest... END
Step 3: Clear previous test results
Input Data: SqlUnitTestId
Transformations: None
DELETE FROM App.SqlUnitTestCaseResult WHERE SqlUnitTestId = @SqlUnitTestId
Step 4: Create and populate temporary student details table
Input Data: debug.vwStudentDetails
Transformations: Grade level transformation for grades below 7, disability status derivation
select SchoolYear, StudentIdentifierState, ... into #vwStudentDetails from debug.vwStudentDetails vw where SchoolYear = @SchoolYear
Step 5: Create and populate main staging table
Input Data: #vwStudentDetails, staging.K12Enrollment, staging.PersonStatus, RDS dimension tables
Transformations: Derivation of status codes for economic disadvantage, migrant, and homelessness
select vsd.*, case when sps.EconomicDisadvantageStatus = 1 then 'ECODIS' else '' end EconomicDisadvantageStatusEdFactsCode, ... into #staging from #vwStudentDetails vsd inner join staging.K12Enrollment ske ...
Step 6: Process each category set (CSA through TOT) for each reporting level (SEA, LEA, SCH)
Input Data: #staging
Transformations: Aggregation by various dimension combinations
SELECT GradeLevelEdFactsCode, RaceEdFactsCode, SexEdFactsCode, COUNT(DISTINCT StudentIdentifierState) AS StudentCount INTO #SEA_CSA FROM #staging GROUP BY GradeLevelEdFactsCode, RaceEdFactsCode, SexEdFactsCode
Step 7: Compare expected and actual results for each test case
Input Data: Temporary tables with expected counts, RDS.ReportEdFactsK12StudentCounts
Transformations: None
INSERT INTO App.SqlUnitTestCaseResult ... SELECT @SqlUnitTestId, @CategorySet + ' ' + @ReportLevel, 'Grade: ' + s.GradeLevelEdFactsCode + ..., s.StudentCount, Fact.StudentCount, CASE WHEN s.StudentCount = ISNULL(Fact.StudentCount, -1) THEN 1 ELSE 0 END, GETDATE() FROM #SEA_CSA s JOIN RDS.ReportEdFactsK12StudentCounts Fact ...
Was this helpful?