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: