Staging.Staging-to-FactK12StudentCounts_SpecEdExit
Overview & Purpose
This stored procedure migrates special education exit data from staging tables to the RDS.FactK12StudentCounts fact table. It specifically processes data related to students who have exited special education services within a given school year. The procedure handles data transformation, joins across multiple dimension tables, and ensures proper attribution of demographic and program participation information.
Main Functions:
Data Migration
Transfers special education exit data from staging tables to the fact table with proper dimension keys
Key Calculations:
Data Migration: Determines the age of students at the time of the child count
Formula:
Age calculation using RDS.Get_Age(ske.Birthdate, IIF(rdd.DateValue < @ChildCountDate, @PreviousChildCountDate, @ChildCountDate))Business Significance: Ensures accurate age reporting for federal/state compliance
Example: If a student was born on 01/15/2010 and the child count date is 10/01/2021, the age would be 11
Data Transformations:
Maps staging table IDs to dimension table surrogate keys
Determines student race/ethnicity based on Hispanic/Latino ethnicity flag and race data
Links special education exit data with enrollment records to ensure valid reporting periods
Updates IDEA status and primary disability information
Updates English Learner status information
Expected Output:
Populated RDS.FactK12StudentCounts table with special education exit data, including all relevant dimension keys and a student count of 1 for each record
Business Context
System: K-12 Education Data Reporting System
Necessity: Required for federal and state reporting of special education exit data
Business Rules:
Students must have a valid special education program participation end date
Students must have enrollment records that overlap with their special education exit date
Age is calculated based on the child count date for the reporting period
Race/ethnicity is determined based on federal reporting guidelines (Hispanic/Latino takes precedence)
Result Usage:
The data is used for federal reporting requirements, state accountability, and analysis of special education program outcomes
Execution Frequency:
Annually during the federal/state reporting cycle
Critical Periods:
End of school year reporting period
Federal submission deadlines
Parameters
@SchoolYear
SMALLINT
Specifies the school year for which to process special education exit data
True
Source Tables
Staging.ProgramParticipationSpecialEducation
Business Purpose: Contains information about students' participation in special education programs, including exit dates and reasons
Columns:
Id
int
Unique identifier for the record
StudentIdentifierState
varchar
State-assigned student identifier
ProgramParticipationEndDate
date
Date when student exited special education services
SpecialEducationExitReason
varchar
Reason code for why the student exited special education
Staging.K12Enrollment
Business Purpose: Contains student enrollment information
Columns:
StudentIdentifierState
varchar
State-assigned student identifier
EnrollmentEntryDate
date
Date when student enrolled
EnrollmentExitDate
date
Date when student exited enrollment
Birthdate
date
Student's date of birth
Sex
varchar
Student's sex
HispanicLatinoEthnicity
bit
Indicates if student is Hispanic/Latino
Temporary Tables
#vwRaces
Purpose: Temporary storage of race dimension data for the specified school year
Columns:
RaceMap
varchar
Direct copy from RDS.vwDimRaces
RaceCode
varchar
Direct copy from RDS.vwDimRaces
DimRaceId
int
Direct copy from RDS.vwDimRaces
#Facts
Purpose: Temporary storage of fact records before final insert into fact table
Columns:
StagingId
int
Direct copy from Staging.ProgramParticipationSpecialEducation.Id
SchoolYearId
int
Lookup from RDS.DimSchoolYears based on @SchoolYear parameter
FactTypeId
int
Lookup from RDS.DimFactTypes where FactTypeCode = 'specedexit'
StudentCount
int
Hard-coded as 1
#uniqueLEAs
Purpose: Temporary storage of unique LEA information for Title I and Migrant updates
Columns:
LeaIdentifierSea
VARCHAR(50)
Direct copy from Staging.K12Organization
LEA_RecordStartDateTime
DATETIME
Direct copy from Staging.K12Organization
LEA_RecordEndDateTime
DATETIME
Direct copy from Staging.K12Organization
Potential Improvements
Error Handling
Description: Enhance error handling to include more specific error messages and handling for different types of errors
Benefits: Better troubleshooting and more robust execution
Priority: Medium
Performance
Description: Add appropriate indexes to temp tables to improve join performance
Benefits: Faster execution, especially for large datasets
Priority: Medium
Code Structure
Description: Modularize the procedure by breaking it into smaller, reusable components
Benefits: Improved maintainability and potential code reuse
Priority: Low
Execution Steps
Step 1: Initialize variables and clean up any existing temp tables
Input Data: None
Transformations: None
IF OBJECT_ID(N'tempdb..#Facts') IS NOT NULL DROP TABLE #Facts
Step 2: Set up variables for date calculations and lookups
Input Data: App.ToggleQuestions, App.ToggleResponses, RDS.DimSchoolYears
Transformations: Calculate child count date and reference period dates
SELECT @ChildCountDate = CAST(CAST(@SchoolYear - 1 AS CHAR(4)) + '-' + CAST(MONTH(@ChildCountDate) AS VARCHAR(2)) + '-' + CAST(DAY(@ChildCountDate) AS VARCHAR(2)) AS DATE)
Step 3: Create and populate temporary tables
Input Data: RDS.vwDimRaces
Transformations: Copy race dimension data for the specified school year
SELECT * INTO #vwRaces FROM RDS.vwDimRaces WHERE SchoolYear = @SchoolYear
Step 4: Delete existing fact records for the specified school year and fact type
Input Data: RDS.FactK12StudentCounts
Transformations: None
DELETE RDS.FactK12StudentCounts WHERE SchoolYearId = @SchoolYearId AND FactTypeId = @FactTypeId
Step 5: Create and populate #Facts temp table with base data
Input Data: Multiple staging and dimension tables
Transformations: Join staging data with dimension tables to get surrogate keys
INSERT INTO #Facts SELECT...
Step 6: Create and populate #uniqueLEAs temp table
Input Data: Staging.K12Organization
Transformations: Filter for LEAs that are reported federally
INSERT INTO #uniqueLEAs SELECT DISTINCT...
Step 7: Update #Facts with IDEA data
Input Data: Staging.ProgramParticipationSpecialEducation, Staging.IdeaDisabilityType
Transformations: Join to get IDEA status and disability information
UPDATE #Facts SET IdeaStatusId = ISNULL(rdis.DimIdeaStatusId, -1)...
Step 8: Update #Facts with English Learner data
Input Data: Staging.PersonStatus
Transformations: Join to get English Learner status information
UPDATE #Facts SET EnglishLearnerStatusId = ISNULL(rdels.DimEnglishLearnerStatusId, -1)...
Step 9: Insert data from #Facts into RDS.FactK12StudentCounts
Input Data: #Facts
Transformations: None
INSERT INTO RDS.FactK12StudentCounts (...) SELECT ... FROM #Facts
Step 10: Rebuild indexes on fact table
Input Data: RDS.FactK12StudentCounts
Transformations: None
ALTER INDEX ALL ON RDS.FactK12StudentCounts REBUILD
Was this helpful?