Modeled for longitudinal storage and reporting of P-20W data, the Common Education Data Standards (CEDS) Data Warehouse implements star schema data warehouse normalization techniques for improved query performance.
This is for capturing needs not currently supported by the CEDS model. Please do not send or share actual data as examples in this issue or in attachments.
Author(s)
Lakshmana Kommuri
Authoring Organization(s)
CEPI - State Of Michigan
Email Address
kommuril@michigan.gov
Use Case Title
Add CountDateId as a foreign key reference to DimDates to several Fact tables.
Use Case Overview
CountDateId is needed to populate bridge tables for three Fact tables.
FactK12StudentEnrollments
FactPsStudentAcademicRecords
FactSpecialEducation
Pull Request Number(s) (If applicable)
Use Case Background
A specific point-in-time date is needed to capture the appropriate details from the staging for the associated Fact records in order to fill the bridge tables.
Components AffectedCEDS Data Warehouse (complete DW Design Overview Table)
CEDS Data Migrations (complete Data Migration Impact Analysis Table)
DATA WAREHOUSE DESIGN OVERVIEW (Includes Dim, Fact, and Report tables)
Description of Proposed Changes
Add CountDateId column to the three fact tables
Make those CountDateId Foreign key references to DimDates
DATA MIGRATION IMPACT ANALYSIS
Description of Proposed Changes
Update the Staging-to-RDS data migration scripts to populate necessary bridge tables related to FactK12StudentEnrollments, FactPsStudentAcademicRecords, and FactSpecialEducation by using the new CountDateId fields to select the correct point-in-time data from the Staging tables.
This is for capturing needs not currently supported by the CEDS model. Please do not send or share actual data as examples in this issue or in attachments.
Author(s) Lakshmana Kommuri Authoring Organization(s) CEPI - State Of Michigan Email Address kommuril@michigan.gov Use Case Title Add CountDateId as a foreign key reference to DimDates to several Fact tables.
Use Case Overview CountDateId is needed to populate bridge tables for three Fact tables.
Pull Request Number(s) (If applicable)
Use Case Background A specific point-in-time date is needed to capture the appropriate details from the staging for the associated Fact records in order to fill the bridge tables.
Components Affected CEDS Data Warehouse (complete DW Design Overview Table)
CEDS Data Migrations (complete Data Migration Impact Analysis Table)
DATA WAREHOUSE DESIGN OVERVIEW (Includes Dim, Fact, and Report tables)
Description of Proposed Changes
DATA MIGRATION IMPACT ANALYSIS
Description of Proposed Changes Update the Staging-to-RDS data migration scripts to populate necessary bridge tables related to FactK12StudentEnrollments, FactPsStudentAcademicRecords, and FactSpecialEducation by using the new CountDateId fields to select the correct point-in-time data from the Staging tables.