App.FS040_TestCase
Overview & Purpose
This stored procedure is designed to test and validate the accuracy of diploma/credential reporting data in the EdFacts reporting system. It creates test cases for various category sets (CSA through CSF, ST1, TOT) across different reporting levels (SEA, LEA, SCH) and compares the expected results with actual results from the reporting system.
Main Functions:
Data Validation
Validates that the student counts in the reporting system match the expected counts calculated from source data
Test Case Generation
Creates test cases for different category sets and reporting levels
Result Comparison
Compares calculated results with stored results in the reporting system
Key Calculations:
Data Validation: Counts unique students for each combination of reporting attributes
Formula:
COUNT(DISTINCT StudentIdentifierState)Business Significance: Ensures accurate reporting of student diploma/credential data to federal education authorities
Example: Counting students by diploma type, race, and sex for the CSA category set
Data Transformations:
Filtering students based on reporting period dates
Mapping diploma types to EdFacts codes
Categorizing students by various demographic attributes
Aggregating student counts by different category combinations
Expected Output:
Records in App.SqlUnitTestCaseResult table indicating whether each test case passed or failed
Business Context
System: EdFacts Reporting System
Necessity: Federal education reporting compliance for diploma and credential data
Business Rules:
Reporting period is from October 1 of the previous year to September 30 of the current year
Students must have valid diploma type codes
Students are categorized by various demographic attributes including race, sex, disability status, etc.
Data must be reported at SEA (state), LEA (district), and SCH (school) levels
Result Usage:
Validation of federal education reporting data before submission to authorities
Execution Frequency:
Likely annual or as needed before federal reporting deadlines
Critical Periods:
Before EdFacts submission deadlines
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 enrollment information
Columns:
SchoolYear
Unknown
Identifies the school year of the student record
StudentIdentifierState
Unknown
Unique identifier for students within the state
Various demographic fields
Various
Store student demographic information
RDS.vwDimK12AcademicAwardStatuses
Business Purpose: Maps diploma types to EdFacts codes
Columns:
SchoolYear
Unknown
School year for the diploma type mapping
HighSchoolDiplomaTypeCode
Unknown
Internal code for diploma types
HighSchoolDiplomaTypeMap
Unknown
Mapping value for diploma types
RDS.DimK12AcademicAwardStatuses
Business Purpose: Stores EdFacts codes for diploma types
Columns:
HighSchoolDiplomaTypeCode
Unknown
Internal code for diploma types
HighSchoolDiplomaTypeEdFactsCode
Unknown
EdFacts reporting code for diploma types
staging.K12Enrollment
Business Purpose: Stores student enrollment data
Columns:
StudentIdentifierState
Unknown
Unique identifier for students
LeaIdentifierSeaAccountability
Unknown
Identifier for the LEA (district)
SchoolIdentifierSea
Unknown
Identifier for the school
HighSchoolDiplomaType
Unknown
Type of diploma received by student
staging.PersonStatus
Business Purpose: Stores various status indicators for students
Columns:
EconomicDisadvantageStatus
Unknown
Indicates if student is economically disadvantaged
MigrantStatus
Unknown
Indicates if student has migrant status
HomelessnessStatus
Unknown
Indicates if student is homeless
RDS.ReportEdFactsK12StudentCounts
Business Purpose: Stores the actual reported student counts
Columns:
ReportCode
Unknown
Identifies the specific EdFacts report
ReportYear
Unknown
School year of the report
ReportLevel
Unknown
Level of reporting (SEA, LEA, SCH)
CategorySetCode
Unknown
Category set for reporting
StudentCount
Unknown
Count of students for the specific combination
Temporary Tables
#vwDiplomaType
Purpose: Stores diploma type mappings for the specified school year
Columns:
SchoolYear
Unknown
Direct from source
HighSchoolDiplomaTypeCode
Unknown
Direct from source
HighSchoolDiplomaTypeMap
Unknown
Direct from source
HighSchoolDiplomaTypeEdFactsCode
Unknown
Direct from source
#vwStudentDetails
Purpose: Stores filtered student demographic data
Columns:
SchoolYear
Unknown
Direct from source
StudentIdentifierState
Unknown
Direct from source
Various demographic fields
Various
Direct from source or derived
#staging
Purpose: Combines all necessary student data for analysis
Columns:
All columns from #vwStudentDetails
Various
Direct from source
EconomicDisadvantageStatusEdFactsCode
Unknown
CASE statement based on EconomicDisadvantageStatus
MigrantStatusEdFactsCode
Unknown
CASE statement based on MigrantStatus
HomelessnessStatusEdFactsCode
Unknown
CASE statement based on HomelessnessStatus
HighSchoolDiplomaTypeEdFactsCode
Unknown
Joined from #vwDiplomaType
Various category set temp tables (#SEA_CSA, #LEA_CSA, etc.)
Purpose: Store aggregated student counts for each category set and reporting level
Columns:
Various grouping columns
Various
Direct from #staging
StudentCount
Unknown
COUNT(DISTINCT StudentIdentifierState)
Potential Improvements
Error Handling
Description: Add TRY/CATCH blocks to handle potential errors during execution
Benefits: Improved reliability and easier troubleshooting
Priority: Medium
Performance
Description: Add indexes to temporary tables for better join performance
Benefits: Faster execution, especially with large datasets
Priority: Medium
Code Organization
Description: Refactor repetitive code blocks into reusable modules
Benefits: Improved maintainability and reduced code duplication
Priority: Low
Documentation
Description: Add more detailed comments explaining business rules and calculations
Benefits: Improved understanding for future maintainers
Priority: Low
Execution Steps
Step 1: Initialize and clean up temporary objects
Input Data: None
Transformations: None
IF OBJECT_ID('tempdb..#Staging') IS NOT NULL DROP TABLE #Staging
Step 2: Define test metadata
Input Data: Test name, procedure name, scope, report code
Transformations: None
DECLARE @UnitTestName VARCHAR(100) = 'FS040_UnitTestCase'
Step 3: Create or retrieve test record
Input Data: App.SqlUnitTest table
Transformations: None
IF NOT EXISTS (SELECT 1 FROM App.SqlUnitTest WHERE UnitTestName = @UnitTestName) BEGIN...
Step 4: Clear previous test results
Input Data: App.SqlUnitTestCaseResult table
Transformations: None
DELETE FROM App.SqlUnitTestCaseResult WHERE SqlUnitTestId = @SqlUnitTestId
Step 5: Define reporting period
Input Data: @SchoolYear parameter
Transformations: Calculate start and end dates
DECLARE @ReportingStartDate Date = CAST(CAST(@SchoolYear - 1 AS CHAR(4)) + '-10-01'AS DATE)
Step 6: Create diploma type mapping table
Input Data: RDS.vwDimK12AcademicAwardStatuses, RDS.DimK12AcademicAwardStatuses
Transformations: Join tables to get EdFacts codes
select vaward.SchoolYear, vaward.HighSchoolDiplomaTypeCode, vaward.HighSchoolDiplomaTypeMap, award.HighSchoolDiplomaTypeEdFactsCode into #vwDiplomaType...
Step 7: Create student details table
Input Data: debug.vwStudentDetails
Transformations: Filter by school year, transform grade levels
select SchoolYear, StudentIdentifierState, LeaIdentifierSeaAccountability, SchoolIdentifierSea, EnrollmentEntryDate...
Step 8: Create main staging table
Input Data: #vwStudentDetails, staging.K12Enrollment, various dimension tables
Transformations: Join tables, filter by reporting period, add status codes
select vsd.*, case when eco.EconomicDisadvantageStatus = 1 then 'ECODIS' else '' end EconomicDisadvantageStatusEdFactsCode...
Step 9: Process each category set and reporting level
Input Data: #staging table
Transformations: Group by different attribute combinations
select @CategorySet = 'CSA'...
Step 10: Compare results and record test outcomes
Input Data: Category set temp tables, RDS.ReportEdFactsK12StudentCounts
Transformations: None
INSERT INTO App.SqlUnitTestCaseResult...
Was this helpful?