Staging.Staging-to-FactK12StudentCounts_MigrantEducationProgram
Overview & Purpose
This stored procedure migrates Migrant Education Program (MEP) data from staging tables to the RDS.FactK12StudentCounts fact table. It specifically processes files 054, 121, 122, and 145 as noted in the procedure header. The procedure filters data for a specific school year, transforms it according to business rules, and loads it into the fact table.
Main Functions:
Data Migration
Transfer MEP student data from staging tables to the fact table for reporting and analysis
Dimension Mapping
Map staging data to appropriate dimension IDs for the star schema
Key Calculations:
Data Migration: Each record represents a single student count in the fact table
Formula:
StudentCount = 1Business Significance: Enables accurate counting of students in the Migrant Education Program
Example: For each qualifying student record, a count of 1 is inserted
Data Transformations:
Mapping student records to appropriate dimension IDs
Filtering records based on enrollment dates and status dates
Joining multiple staging tables to create a comprehensive student record
Handling NULL values by replacing them with default dimension IDs (-1)
Expected Output:
Populated RDS.FactK12StudentCounts table with Migrant Education Program data for the specified school year
Business Context
System: K-12 Education Data Reporting System
Necessity: Required for federal reporting of Migrant Education Program participation and services
Business Rules:
Each student is counted once in the fact table
Students must have valid enrollment records
Migrant status is determined by matching status dates with enrollment periods
IDEA status, English Learner status, and other attributes are linked to student records
Result Usage:
The migrated data is used for federal reporting, program evaluation, and educational decision-making regarding migrant students
Execution Frequency:
Likely annual or semi-annual, based on school year parameter
Critical Periods:
End of school year reporting periods
Federal education data submission deadlines
Parameters
@SchoolYear
SMALLINT
Specifies the school year for which data should be migrated
True
Source Tables
Staging.K12Enrollment
Business Purpose: Contains student enrollment data including school, LEA, and enrollment dates
Columns:
id
int
Unique identifier for the enrollment record
SchoolYear
smallint
Identifies the school year of the enrollment
StudentIdentifierState
varchar
State-assigned student identifier
Staging.PersonStatus
Business Purpose: Contains student status information including migrant and English learner statuses
Columns:
StudentIdentifierState
varchar
State-assigned student identifier
MigrantStatus
varchar/int
Indicates if student is part of Migrant Education Program
Migrant_StatusStartDate
date
Date when migrant status became effective
Temporary Tables
#vwGradeLevels
Purpose: Temporary storage of grade level dimension data for the specified school year
Columns:
All columns from RDS.vwDimGradeLevels
Various
Direct copy from view
#vwMigrantStatuses
Purpose: Temporary storage of migrant status dimension data for the specified school year
Columns:
All columns from RDS.vwDimMigrantStatuses
Various
Direct copy from view
#Facts
Purpose: Temporary storage of transformed fact records before final insert
Columns:
StagingId
int
Direct copy from Staging.K12Enrollment.id
Various dimension ID columns
int
Lookup from dimension tables or default to -1
StudentCount
int
Set to 1 for each record
Potential Improvements
Error Handling
Description: Implement more granular error handling with specific error codes and messages
Benefits: Better troubleshooting and error resolution
Priority: Medium
Transaction Management
Description: Add explicit transaction control
Benefits: Ensures data consistency if errors occur during processing
Priority: High
Performance
Description: Optimize the complex query that populates #Facts table
Benefits: Reduced execution time and resource usage
Priority: Medium
Execution Steps
Step 1: Initialize and set up environment
Input Data: @SchoolYear parameter
Transformations: None
SET NOCOUNT ON; IF OBJECT_ID(N'tempdb..#vwMigrantStatuses') IS NOT NULL DROP TABLE #vwMigrantStatuses
Step 2: Retrieve school year information
Input Data: @SchoolYear parameter
Transformations: None
SELECT @SchoolYearId = DimSchoolYearId FROM RDS.DimSchoolYears WHERE SchoolYear = @SchoolYear
Step 3: Create temporary dimension tables
Input Data: RDS.vwDimGradeLevels, RDS.vwDimMigrantStatuses
Transformations: Filter by school year
SELECT * INTO #vwGradeLevels FROM RDS.vwDimGradeLevels WHERE SchoolYear = @SchoolYear
Step 4: Determine fact type ID
Input Data: rds.DimFactTypes
Transformations: None
SELECT @FactTypeId = DimFactTypeId FROM rds.DimFactTypes WHERE FactTypeCode = 'mep'
Step 5: Clear existing fact data
Input Data: RDS.FactK12StudentCounts
Transformations: None
DELETE RDS.FactK12StudentCounts WHERE SchoolYearId = @SchoolYearId AND FactTypeId = @FactTypeId
Step 6: Create and populate facts temp table
Input Data: Multiple staging and dimension tables
Transformations: Complex joins and lookups
CREATE TABLE #Facts (...); INSERT INTO #Facts SELECT ...
Step 7: Insert data into fact table
Input Data: #Facts temp table
Transformations: None
INSERT INTO RDS.FactK12StudentCounts (...) SELECT ... FROM #Facts
Step 8: Rebuild indexes
Input Data: RDS.FactK12StudentCounts
Transformations: None
ALTER INDEX ALL ON RDS.FactK12StudentCounts REBUILD
Was this helpful?