Staging.Staging-to-FactK12StudentCounts_Dropout
Overview & Purpose
This stored procedure migrates dropout data from staging tables to the RDS.FactK12StudentCounts fact table for a specific school year. It processes file type 032 (dropout data) and transforms the data from various staging tables into a consolidated fact table format for reporting and analysis purposes.
Main Functions:
Data Migration
Transfers dropout-related student data from staging tables to the RDS fact table
Dimension Mapping
Maps staging data to appropriate dimension IDs for the fact table
Key Calculations:
Data Migration: Counts each student record as 1 in the fact table
Formula:
StudentCount = 1Business Significance: Enables accurate counting of dropout students for reporting
Example: Each student record in the staging table becomes one row in the fact table with StudentCount=1
Data Transformations:
Mapping student demographic data to dimension tables
Joining multiple staging tables to create a comprehensive student record
Filtering data based on date ranges to ensure accurate time period representation
Handling NULL values by replacing them with default values (-1 for dimension IDs)
Expected Output:
Populated RDS.FactK12StudentCounts table with dropout data for the specified school year, with each record representing a student dropout instance with associated dimension references
Business Context
System: K-12 Education Data Reporting System
Necessity: Required for federal and state reporting of student dropout statistics
Business Rules:
Each student is counted once in the dropout statistics
Students must have valid enrollment records
Various demographic and status indicators must be properly mapped to dimension tables
Time periods must align with the specified school year
Result Usage:
The data is used for educational reporting, analysis of dropout trends, and compliance with reporting requirements
Execution Frequency:
Likely annual, aligned with school year reporting cycles
Critical Periods:
End of school year reporting periods
Federal and state education data submission deadlines
Parameters
@SchoolYear
SMALLINT
Specifies the school year for which dropout data should be processed
True
Source Tables
Staging.K12Enrollment
Business Purpose: Contains student enrollment data including entry and exit dates
Columns:
id
int
Unique identifier for the enrollment record
SchoolYear
smallint
Indicates the school year of the enrollment
StudentIdentifierState
varchar
State-assigned student identifier
Staging.PersonStatus
Business Purpose: Contains various status indicators for students
Columns:
StudentIdentifierState
varchar
State-assigned student identifier
HomelessnessStatus
smallint
Indicates student's homelessness status
Temporary Tables
#vwRaces
Purpose: Temporary storage of race dimension data for the specified school year
Columns:
RaceMap
varchar
Direct copy from source view
DimRaceId
int
Direct copy from source view
#vwEconomicallyDisadvantagedStatuses
Purpose: Temporary storage of economic status dimension data
Columns:
EconomicDisadvantageStatusMap
smallint
Direct copy from source view
DimEconomicallyDisadvantagedStatusId
int
Direct copy from source view
#Facts
Purpose: Temporary storage of transformed fact data before final insert
Columns:
StagingId
int
Direct copy from source staging ID
SchoolYearId
int
Lookup from DimSchoolYears
Potential Improvements
Error Handling
Description: Implement more granular error handling for each major step
Benefits: Better error diagnostics and recovery options
Priority: Medium
Transaction Management
Description: Implement explicit transaction handling
Benefits: Ensures data consistency if errors occur mid-process
Priority: High
Performance
Description: Optimize the complex join in the #Facts population step
Benefits: Reduced execution time and resource usage
Priority: Medium
Documentation
Description: Add more detailed inline comments explaining business logic
Benefits: Improved maintainability and knowledge transfer
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..#vwRaces') IS NOT NULL DROP TABLE #vwRaces
Step 2: Retrieve school year information and set date variables
Input Data: @SchoolYear parameter
Transformations: Lookup DimSchoolYearId from RDS.DimSchoolYears
SELECT @SchoolYearId = DimSchoolYearId FROM RDS.DimSchoolYears WHERE SchoolYear = @SchoolYear
Step 3: Create and populate temporary dimension view tables
Input Data: RDS dimension views filtered by school year
Transformations: Copy dimension data to temp tables
SELECT * INTO #vwGradeLevels FROM RDS.vwDimGradeLevels WHERE SchoolYear = @SchoolYear
Step 4: Retrieve fact type ID for dropout data
Input Data: RDS.DimFactTypes table
Transformations: Lookup DimFactTypeId for 'dropout' code
SELECT @FactTypeId = DimFactTypeId FROM rds.DimFactTypes WHERE FactTypeCode = 'dropout'
Step 5: Delete existing fact records for the school year and fact type
Input Data: RDS.FactK12StudentCounts table
Transformations: None
DELETE RDS.FactK12StudentCounts WHERE SchoolYearId = @SchoolYearId AND FactTypeId = @FactTypeId
Step 6: Create and populate temporary facts table
Input Data: Multiple staging tables joined together
Transformations: Complex joins and mappings to dimension IDs
INSERT INTO #Facts SELECT DISTINCT ske.id StagingId, ...
Step 7: Insert data from temporary facts table to destination fact table
Input Data: #Facts temporary table
Transformations: None - direct copy
INSERT INTO RDS.FactK12StudentCounts (...) SELECT ... FROM #Facts
Step 8: Rebuild indexes on fact table
Input Data: RDS.FactK12StudentCounts table
Transformations: None
ALTER INDEX ALL ON RDS.FactK12StudentCounts REBUILD
Was this helpful?