Staging.Staging-to-FactK12StudentEnrollments
Overview & Purpose
This stored procedure transfers K12 student enrollment data from staging tables to the fact table RDS.FactK12StudentEnrollments and related bridge tables. It processes student enrollment records, enriches them with dimensional data, and loads them into the data warehouse structure for reporting and analysis purposes.
Main Functions:
Data Extraction and Transformation
Extract student enrollment data from staging tables, join with dimension tables to get dimension keys, and prepare data for loading into fact tables
Fact Table Loading
Load transformed student enrollment data into the fact table
Bridge Table Population
Populate bridge tables for many-to-many relationships like student races and disability types
Key Calculations:
Data Extraction and Transformation: Handle NULL values throughout the procedure
Formula:
ISNULL(value, default)Business Significance: Ensures data integrity by providing default values when source data is missing
Example: ISNULL(ske.Sex, 'MISSING')
Fact Table Loading: Set the student count measure to 1 for each enrollment record
Formula:
StudentCount = 1Business Significance: Enables counting of students in reporting
Example: 1 as [StudentCount]
Data Transformations:
Creating temporary tables with filtered data based on school years
Joining staging data with dimension tables to get dimension keys
Handling NULL values with ISNULL function
Converting boolean values (0/1) to 'Yes'/'No' strings
Mapping codes to dimension table IDs
Expected Output:
Populated fact table (RDS.FactK12StudentEnrollments) and bridge tables (RDS.BridgeK12StudentEnrollmentRaces, RDS.BridgeK12StudentEnrollmentIdeaDisabilityTypes, RDS.BridgeK12StudentEnrollmentPersonAddresses) with student enrollment data
Business Context
System: K12 Education Data Warehouse
Necessity: To transform staging data into a dimensional model for reporting and analysis of student enrollments
Business Rules:
Student enrollment records are linked to multiple dimensions including schools, LEAs, demographics, and program participations
Students can have multiple race designations requiring a bridge table
Students can have multiple disability types requiring a bridge table
Default dimension key value of -1 is used when no match is found
Result Usage:
The fact and bridge tables are used for reporting and analysis of student enrollment data across various dimensions
Execution Frequency:
Likely executed after each data collection or data load
Critical Periods:
End of school year reporting periods
State and federal education reporting deadlines
Parameters
@DataCollectionName
VARCHAR(60)
Filters the data processing to a specific data collection
False
Source Tables
Staging.K12Enrollment
Business Purpose: Stores staged student enrollment data
Columns:
Id
INT
Primary key for the staging table
SchoolYear
VARCHAR
Identifies the school year of the enrollment
Staging.PersonStatus
Business Purpose: Stores various status indicators for students
Staging.ProgramParticipationSpecialEducation
Business Purpose: Stores special education program participation data
Staging.IdeaDisabilityType
Business Purpose: Stores disability type information for students
Staging.ProgramParticipationCte
Business Purpose: Stores career and technical education program participation data
Staging.Migrant
Business Purpose: Stores migrant student information
Staging.ProgramParticipationTitleIII
Business Purpose: Stores Title III program participation data
Staging.Military
Business Purpose: Stores military connection information for students
Staging.K12PersonRace
Business Purpose: Stores race information for students
Staging.K12StudentAddress
Business Purpose: Stores address information for students
Temporary Tables
#SchoolYears
Purpose: Stores distinct school years from the filtered enrollment data
Columns:
SchoolYear
VARCHAR
Direct selection from Staging.K12Enrollment
#vwDimK12Demographics
Purpose: Temporary copy of demographics dimension view filtered by relevant school years
#Facts
Purpose: Temporary storage for fact data before insertion into the fact table
Columns:
StagingId
INT
Direct selection from Staging.K12Enrollment.Id
#temp
Purpose: Temporary storage for race data before insertion into the bridge table
Potential Improvements
Performance
Description: Add explicit transaction handling to ensure data consistency
Benefits: Ensures all data is committed or rolled back as a unit
Priority: Medium
Error Handling
Description: Add explicit error handling with TRY/CATCH blocks
Benefits: Better error reporting and recovery
Priority: High
Performance
Description: Optimize the multiple UPDATE statements on #Facts table
Benefits: Reduced execution time
Priority: Medium
Maintainability
Description: Add comments to explain complex logic and business rules
Benefits: Easier maintenance and knowledge transfer
Priority: Medium
Execution Steps
Step 1: Create temporary table of distinct school years
Input Data: Staging.K12Enrollment filtered by @DataCollectionName
Transformations: SELECT DISTINCT SchoolYear
SELECT DISTINCT SchoolYear INTO #SchoolYears FROM Staging.K12Enrollment WHERE (@DataCollectionName IS NULL OR DataCollectionName = @DataCollectionName)
Step 2: Create temporary tables with filtered dimension views
Input Data: Various RDS dimension views joined with #SchoolYears
Transformations: Filter dimension views by relevant school years
SELECT v.* INTO #vwDimK12Demographics FROM RDS.vwDimK12Demographics v JOIN #SchoolYears t on v.SchoolYear = t.SchoolYear
Step 3: Create and populate #Facts temporary table
Input Data: Staging.K12Enrollment and related staging tables
Transformations: Join with dimension tables to get dimension keys
INSERT INTO #Facts (...) SELECT DISTINCT ... FROM Staging.K12Enrollment ske ...
Step 4: Update missing dimension keys in #Facts
Input Data: #Facts table and dimension tables
Transformations: Update NULL dimension keys with valid values
UPDATE #Facts SET K12StudentId = p.DimPersonId FROM #Facts f JOIN Staging.K12Enrollment ske ON f.StagingId = ske.Id JOIN (...) p ON ske.StudentIdentifierState = p.K12StudentStudentIdentifierState AND p.RecNum = 1
Step 5: Insert data into fact table
Input Data: #Facts temporary table
Transformations: Convert NULL dimension keys to -1
INSERT INTO RDS.FactK12StudentEnrollments (...) SELECT ISNULL([SchoolYearId], -1), ..., 1 as [StudentCount] FROM #Facts
Step 6: Populate bridge tables
Input Data: Fact table, dimension tables, and staging tables
Transformations: Join fact table with dimension and staging tables
Insert Into RDS.BridgeK12StudentEnrollmentRaces ([FactK12StudentEnrollmentId], [RaceId]) Select distinct t.FactK12StudentEnrollmentId, rdr.DimRaceId From #temp t join RDS.vwDimRaces rdr on t.SchoolYear=rdr.SchoolYear and ... = rdr.RaceMap
Was this helpful?