Migration

Generate Data Migration Overview

The Generate SQL Server database contains a set of stored procedures and data layers for transforming data into the final “Reports Tables” layer used by the web-based application to create EDFacts reports.

Data Layers

Data Layer	Description
Staging Tables	Tables are flattened to make it easy to ETL data into the Staging Tables. Built specifically for data needed for Generate. Load raw data into this layer.
CEDS Data Warehouse	A Star Schema database model used to store atomic level data longitudinally.
CEDS Semantic Layer	Flattened tables used to store aggregate report data. This structure is ideal for populating reports rapidly.

Extract, Transform, Load (ETL) Stored Procedures

All Generate data migrations are accomplished by running SQL Server Stored Procedures. They can be run either from the Generate Web-based application “Data Store” pages or directly through SQL Server. Running from the Web-based application is recommended, especially when running Data Warehouse and Report Migration steps. Code-snippets below are included as examples of how to run Generate ETL Stored Procedures from SQL Server Management Studio (SSMS). Reach out to your technical assistance (TA) provider if any questions arise.

	SEA Source to Staging Migration	Data Warehouse Migration	Report Migration
Generate Web- based application (user interface) Button	Staging Migration	RDS Migration	Report Migration
Source Schema	SEA Source Schema	Generate.Staging	Generate.RDS (Dimension and Fact tables)
Destination Schema	Generate.Staging	Generate.RDS	Generate.RDS (Report tables)

SEA Source to Staging

These procedures migrate data from the SEA Source Systems to the Generate Staging Tables. The approach to creating these stored procedures is to create a single stored procedure for each domain (such as Child Count, Discipline, Exiting, etc.) that migrates data for that domain into the staging tables. By default, Generate has empty template scripts available by data domain (example: Source.[Source-to-Staging_ChildCount]) which have a record in the App.DataMigrationTaskstable.

These stored procedures can be run from the Generate Web Application using the Staging Data Migration option.

The Generate Web Application “Data Store” migration pages use the App.DataMigrationTaskstable to determine what stored procedures can be run from the web interface. Using one of these template scripts is recommended for new SEA Source to Staging ETL because no updates to App.DataMigrationTasksrecords is needed to migrate using one of these stored procedures. These stored procedures can be run from the Generate Web Application using the Staging Migration option.

Reference the ETL Documentation Template for each domain to get a list of the tables/fields that need to be populated to create this ETL.

These stored procedures have a school year parameter that should be passed in if run from SQL Server Management Studio (note: the @SchoolYear SMALLINT parameter lines up with how the Generate Web App calls these scripts). For example, to run with 2023 school year data:

EXECUTE [Staging].[Migrate_SourceToStaging_Organization] 2023

Data Warehouse Migration

These procedures migrate data from the CEDS Staging Environment to the CEDS Data Warehouse.

Starting in Generate version 3.7, wrapper scripts are available for each domain of data that is run at this stage. These wrapper scripts can be selected from the Generate web application or run from the SQL Server database. The wrapper scripts follow this naming convention:

App.Wrapper_Migrate_Directory_to_RDS

The wrapper scripts call a set of stored procedures used at this stage:

[Staging].[Staging-to-DimSeas] 'directory', NULL, 0

[Staging].[Staging-to-DimLeas] 'directory', NULL, 0

[Staging].[Staging-to-DimK12Schools] NULL, 0

[Staging].[Staging-to-DimCharterSchoolAuthorizers]

[Staging].[Staging-to-DimCharterSchoolManagementOrganizations]

[Staging].[Staging-to-FactOrganizationCounts]

• These stored procedures can be run from the “Data Store” page within the Generate web application using the RDS Migration option.

UPDATE rds.DimSchoolYearDataMigrationTypes
SET IsSelected = 0
UPDATE rds.DimSchoolYearDataMigrationTypes
SET IsSelected = 1
FROM rds.DimSchoolYearDataMigrationTypes sydmt
JOIN rds.DimSchoolYears sy
on sydmt.DimSchoolYearId = sy.DimSchoolYearId
WHERE DimDateId = 11 –2020-21
EXEC
WHERE SchoolYear = 2020 -- Update to the year you are migrating
EXEC
Exec [App].[Wrapper_Migrate_Directory_to_RDS]

Report Migration

• The RDS.CreateReports Stored Procedure migrates data from the Staging to the Report Tables.

• These stored procedures can be run from the Generate Web Application using the Report Migration option.

• The RDS.CreateReports Stored Procedure kicks off the Create_ReportData Stored Procedure for each EDFacts Report based on the fact type for that report. Fact types can be found in the App.FactTables table and information for which fact types are used is stored in the FactTableId field of the App.GenerateReports.

• The RDS.Create_ReportData Stored Procedure uses Dynamic SQL to migrate data into the reporting tables.

• The Stored Procedures at this migration stage do not take a School Year parameter. They loop through school years based on the selections in the web application front end which are stored in RDS.DimSchoolYearDataMigrationTypes. These can be updated manually by setting the IsSelect field to 1 for all years you wish to run from SQL Server Management Studio. For example, when running Directory data migrations from SQL Server Management Studio the code would look like this:

UPDATE app.GenerateReports
SET IsLocked = 1
WHERE ReportCode IN ('c029', 'c039')
EXEC RDS.Create_Reports 'directory', 0, 'organizationcounts'

Source System Reference Data Mapping

The Generate table Staging.SourceSystemReferenceData is used in the Staging Encapsulated Code Migration stage to determine how source system option set values map to CEDS option set values. This table needs to be updated with the complete set of values for all categorical fields by school year. If a migration is run and no mapping exist for the corresponding school year, then the Encapsulated Code will update the Staging.SourceSystemReferenceData table with a copy of mapping records from the most recent school year.

The Staging.SourceSystemReferenceData tables are noted below:

K12Staff
RefK12StaffClassification
RefSpecialEducationStaffCategory
RefSpecialEducationAgeGroupTaught
RefTitleIProgramStaffCategory
RefCredentialType

Assessments
RefAcademicSubject
RefAssessmentPurpose
RefAssessmentType
RefAssessmentTypeChildrenWithDisabilities
AssessmentPerformanceLevel_Identifier
RefAssessmentParticipationIndicator
RefGradeLevel (Filter = ‘000126’)
RefAssessmentReasonNotCompleting

Discipline
RefFirearmType
RefDisciplineReason
RefDisciplinaryActionTaken
RefIdeaInterimRemoval
RefIDEAInterimRemovalReason
RefDisciplineMethodOfCwd
RefDisciplineMethodFirearms
RefIDEADisciplineMethodFirearm

PPSE
RefIDEAEducationalEnvironmentEC
RefIDEAEducationalEnvironmentSchoolAge
RefSpecialEducationExitReason

PersonStatus
RefHomelessNighttimeResidence
RefLanguage
RefMilitaryConnectedStudentIndicator
RefDisabilityType
RefFoodServiceEligibility

PersonRace
RefRace

K12Enrollment
RefSex
RefExitOrWithdrawalType
RefGradeLevel (Filter = '000100')
RefHighSchoolDiplomaType

K12Organization
RefOrganizationType (Filter = ‘001156’)
RefInstitutionTelephoneType
RefOperationalStatus (Filter = ‘000174’)
RefOperationalStatus (Filter = ‘000533’)
RefCharterSchoolAuthorizerType
RefLeaType
RefCharterLeaStatus
RefK12LeaTitleISupportService
RefTitleInstructionalServices
efTitleProgramType
RefMepProjectType
RefSchoolType
RefStatePovertyDesignation
RefGradeLevel (Filter = ‘000131’)
RefOrganizationLocationType
RefState
RefOrganizationIdentificationSystem (Filter = ‘Organization’)
RefFederalProgramFundingAllocationType
RefGunFreeSchoolsActReportingStatus
RefReconstitutedStatus
RefProgressAchievingEnglishLanguageProficiencyIndicatorStatus
RefTitleISchoolStatus
RefComprehensiveAndTargetedSupport
RefComprehensiveSupport
RefTargetedSupport
RefSchoolDangerousStatus
RefMagnetSpecialProgram
RefVirtualSchoolStatus
RefNSLPStatus

Generate contains validation tools to assist ETL developers in verifying that data has successfully migrated across the Generate data layers. Errors and issues captured in the first stage of data migration (SEA Source to Staging Migration) are logged to the Staging.StagingValidationResults table. The logs can be explored from the SQL Server database.

Staging.StagingValidationResults Table

The Staging.StagingValidationResults table holds logs of certain errors and issues created during the data migration process. It is designed for troubleshooting and provides information such as stored procedure name, table name, specific error messaging, type of error, and datetime created.

Currently, there is a process that writes to the table:

1. The Stored Procedure Staging.ValidateStagingData can be called after each SEA Source to Staging Migration Stored Procedure.

Error Group	Description	Logged by Stored Procedure
1	Code Execution Failure	Staging.ValidateStagingData
2	Table Did Not Populate	Staging.ValidateStagingData
3	Field Required Not Populated	Staging.ValidateStagingData
4	Source Value Not In CEDS Option Set Mapping	Staging.ValidateStagingData