Staff
This page describes the steps needed to use Generate to produce EDFacts Files for the Staff Fact Type.
Last updated
Was this helpful?
This page describes the steps needed to use Generate to produce EDFacts Files for the Staff Fact Type.
Last updated
Was this helpful?
Was this helpful?
Set Up: Data Mapping & Settings
Migration: Building and Running ETLs
Validation: Verifying Data Results
The following files have been created in Generate and submitted to EDFacts:
--What EDFacts Files are included in the Staff Fact Type?
SELECT agrft.FactTypeId
,agrft.GenerateReportId
,rdft.FactTypeCode
,rdft.FactTypeDescription
,agr.ReportCode
,agr.ReportName
FROM App.GenerateReport_FactType AS agrft
LEFT JOIN RDS.DimFactTypes AS rdft
ON rdft.DimFactTypeId
The following files are in pilot status or are available for piloting in Generate:
The Generate ETL Documentation Templates give a detailed breakdown of all data elements needed for each Fact Type and show how data are transformed through each stage of the data migration. After completing the CEDS alignment process these templates can be used to document data transformation notes and option set mappings. They also contain a description of the CEDS data elements needed and what they are called throughout the Generate database. The ETL Templates documentation has a detailed instruction tab to help you know how to utilize this tool effectively. If you need clarification, please reach out to your CIID TA provider.
The Generate metadata tables can be queried to determine which Staging tables need to be populated for a Fact Type.
The following script will return the needed staging table, and columns for Staff:
-- How do I know what data needs to be mapped for this Fact Type?
-- Get table list of report codes, tables, and fields by fact type.
SELECT DISTINCT FactTypeCode, ReportCode, StagingTableName, StagingcolumnName
FROM app.vwStagingRelationships
WHERE FactTypeCode = 'staff'
ORDER BY FactTypeCode, ReportCode, StagingTableName, StagingcolumnNameThe Generate Toggle tables store information from the EDFacts Metadata and Process System (EMAPS) survey that impacts the business logic used to ETL the data for EDFacts reporting. It is important to make sure these questions are completed before data is migrated and that they match what was entered in EMAPS. These items can be updated on the Toggle page(s) in the Generate web application. The Toggle page is largely organized by Fact Type, though there may be cases where a setting from a different Fact Type or section may be required. We recommend updating all Toggle settings annually after you complete your EMAPS survey. Instructions for how to find and update the Toggle page are available in the Toggle documentation.
Source System Reference Data is used in the Staging to RDS Migration 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.
To find the Source System Reference Data needed for each Fact Type, you can query the system by running the following script by FactTypeCode and ReportCode.
You can also filter the Source System Reference Data table by FactTypeCode and ReportCode as shown below.
SELECT DISTINCT FactTypeCode, ReportCode, StagingTableName, StagingcolumnName, SSRDRefTableName, SSRDTableFilter
FROM app.vwStagingRelationships
WHERE FactTypeCode = 'staff' and ReportCode = '070'
ORDER BY FactTypeCode, ReportCode, StagingTableName, StagingcolumnNameIn some instances, the CEDS reference table needs to be further qualified to determine what level or type of data is being referenced by the Table Filter field. For example, the fallowing fields will need to be mapped using the value in the SSRD table using these filters. For further information please review Source System Reference Data.
Staff Reports ('059', '065', '067', '070', '099', '112', '203') does not have filters.
Source System Reference Data Mapping Utility can be used to determine which option-set value mappings are needed for a Fact Type and which have been mapped. Note that new installations of Generate will come with both the InputCode and OutputCode fields loaded and you will need to review and update any values in the InputCode field to match your source data.
exec [Utilities].[Check_SourceSystemReferenceData_Mapping] 'staff', '2024', 0 -- This will show all mappings for the Staff Fact Type for School Year 2023-24The Generate database has a stored procedure for each Fact Type which is empty in the default load of the Generate database and serves as a placeholder. Since this Stored Procedure ETLs data from the education agency's source system(s) into the Generate Staging environment, the ETL code will be customized to your education agency's context.
For Staff, this Stored Procedure is called [Source].[Source-to-Staging_Staff].
The tools from the Set Up phase (ETL Checklist and Generate metadata) are used to guide writing the ETL Code in this Stored Procedure. Additionally, ETL code written previously to perform this work in the education agency's source system(s) can also be a useful resource at this step, particularly for ensuring critical data handling and business rules from the source system are retained in the Generate Source to Staging ETL.
Migrating Source to Staging
The Source to Staging code can be run from SQL Server Management Studio (SSMS) by passing in the current school year as a parameter. Generate uses the end school year. For example, 2023-24 would be specified as '2024'.
exec [Source].[Source-to-Staging_Staff] 2024Once data has been migrated to the Staging tables there are two Generate tools that can be used to validate the data.
Staging Validation Utility
Staging Table Debug View
Generate has a Staging Validation Process which can be called at the Fact Type or EDFacts file level.
The following is an example code snippet of how to call these Stored Procedures by the Staff Fact Type.
exec [Staging].[StagingValidation_Execute] 2024,'staff'
exec [Staging].[StagingValidation_GetResults] 2024,'staff'To aid validation we developed Staging Table Debug views that join together the Staging data for a Fact Type in a standard format that can be used for Generate testing. You can utilize these views in researching specific subsets of data or specific student data. These views can be found in the debug schema and will automatically be filtered by the school year(s) selected in the Generate web application. Opening the view in SSMS will provide you with a variety of filtering options to modify the query as needed during testing. Detailed instructions on how to utilize this process to debug Staging table data can be found in the Staging Table Validation Process.
The following is an example code snippet of how to select the Staff Staging Table Debug view:
select * from [debug].[vwStaff_StagingTables]Migrating Staging to CEDS Data Warehouse in the Reporting Data Store (RDS) (Manually)
To migrate data from Staging to the CEDS Data Warehouse you will need to call the [App].[Wrapper_Migrate_Staff_to_RDS] Stored Procedure. This wrapper will call several Stored Procedures to migrate data to the dimension and fact tables in the CEDS Data Warehouse as well as log this activity in the App.DataMigrationHistories table. This process will also create debug tables that contain the information that is utilized in the counts and can be used in the validation process.
Once data has been migrated to the Staging tables the Fact Table Debug View can be used to validate the data.
The Fact Table Debug view joins together the CEDS Data Warehouse data for a Fact Type in a standard format that is used for Generate testing. This view will automatically be filtered by the school year(s) selected in the Generate web application and stored in the RDS.DimSchoolYearDataMigrationTypes table. However, opening the view in SSMS will provide you with a variety of filtering options to modify this query as needed during testing. Detailed instructions on how to utilize this process to debug Fact Table data can be found in the Fact Type Table Validation Process guide.
The following is an example code snippet of how to select the Staff Staging Table Debug view:
select * from [debug].[vwStaff_FactTable]To migrate data from the CEDS Data Warehouse to the Report Tables in SSMS you will need to update some settings in the database and call the [rds].[create_reports] Stored Procedure.
-- A. Make sure the Report Data Migration Type is selected
DECLARE @SchoolYearId int = (SELECT DimSchoolYearId FROM RDS.DimSchoolYears WHERE SchoolYear = 2024)
UPDATE RDS.DimSchoolYearDataMigrationTypes SET IsSelected = 0
UPDATE RDS.DimSchoolYearDataMigrationTypes
SET IsSelected = 1
WHERE DimSchoolYearId = @SchoolYearId and DataMigrationTypeId = 3 -- Reports
-- B. Lock the reports to be run
UPDATE App.GenerateReports set IsLocked = 0
UPDATE App.GenerateReports
SET IsLocked = 1
WHERE ReportCode IN ('059', '065', '067', '070', '099', '112', '203')
-- C. Empty the reports table for the specific reports
EXEC [rds].[Empty_Reports] @FactTypeCode = 'staff'
-- D. Perform the reports migration
EXEC [rds].[create_reports] 'staff',0
The process of migrating data to Report Tables creates a set of tables in the [debug] schema that provide the student IDs that make up the aggregated counts. These tables are especially useful when doing file comparisons and matching work. The sample below would return the list of students making up the counts.
-- simply query a corresponding table
-- (using the table name to identify the category set contents)
-- This example will only work if the above code has been executed.
SELECT * FROM [debug].[070_lea_CSA_2023_AGEGROUP_QUALSTATSPEDTCH]Over time these tables will accumulate and create clutter in the Generate database debug schema. You can easily remove unneeded debug tables using the Clean Up Debug Tables utility.
The File Comparison Utility allows you to compare EDFacts submission files to data stored in the Report Tables in the Generate database. Instructions on how to use the Utilities.CompareSubmissionFiles Stored Procedure are available here. Typically, this step is performed in the first year of reporting a file through Generate to compare it to previous submission files produced by the legacy system.
-- Once you have followed the steps in the File Comparison Utility, you can run this to find the results.
exec Utilities.CompareSubmissionFiles
@DatabaseName = 'Generate', -- Your database name
@SchemaName = 'XX', -- Your schema name
@SubmissionYear = 2023, -- The report year
@ReportCode = '070', -- EdFacts File Number – 059, 065, 067, 070, 099, 112, 203
@ReportLevel = 'LEA', -- 'SEA', 'LEA', 'SCH'
@LegacyTableName = 'Generate.XX.070_LEA_2023_Legacy', -- Legacy table
@GenerateTableName = 'Generate.XX.070_LEA_2023_Generate', -- Generate table
@ShowSQL = 0,
@ComparisonResultsTableName = 'generate.XX.070_LEA_2023_COMPARISON' -- Comparison table nameIf you need further assistance validating your data or have data mismatches that you cannot resolve, please reach out to your TA provider for assistance.
This IDEA Part B Data Review Checklist helps state teams review and validate IDEA EDFacts files. It includes a tab for each IDEA EDFacts file specification, and lists considerations and types of validations to ensure accurate and complete data file submissions. Please review the Instruction tab and look for the associated report codes for the Staff Fact Type.
Generate allows states to create a backup or “snapshot” of staging tables. This is an optional utility that can be executed as needed or embedded into the State’s ETL workflow logic. The Staging Table Snapshot Utility provides a method to create a backup copy of staging tables for future use and reference after an ETL has populated Generate’s staging tables. This helps to ensure consistency across all EDFacts reports for a given year and developers can preserve data in staging tables across ETL executions. This is best utilized after you have confirmed the Fact Type has successfully produced an accurate EDFacts file. Test
-- You will need to make sure the year is set to the school year you are migrating data. This script lets you check and then update if needed.
--Set school year for the RDS migrations (if necessary)
--check if the correct SY is already selected
SELECT sy.SchoolYear, dm.*
FROM rds.DimSchoolYearDataMigrationTypes dm
INNER join rds.dimschoolyears sy
ON dm.dimschoolyearid = sy.dimschoolyearid
WHERE IsSelected = 1
--IF THE ABOVE QUERY DOESN'T RETURN THE SY YOU NEED, RUN THE NEXT 2 QUERIES
--reset, then set the appropriate year for this migration
UPDATE rds.DimSchoolYearDataMigrationTypes
SET IsSelected = 0
-- Update to the year you are migrating
UPDATE rds.DimSchoolYearDataMigrationTypes
SET IsSelected = 1
FROM rds.DimSchoolYearDataMigrationTypes sydmt
JOIN rds.DimSchoolYears sy
ON sydmt.DimSchoolYearId = sy.DimSchoolYearId
WHERE SchoolYear = 2024
-- call the wrapper script to migrate the Fact Type data
exec [app].[Wrapper_Migrate_Staff_to_RDS]![A SQL Server Management Studio window displaying a stored procedure named "[Source].[Source-to-Staging_Staff]" in the Generate database. The Object Explorer panel on the left shows a list of stored procedures under the "Source" schema, while the query editor on the right contains a template for the stored procedure with a placeholder for ETL code.](https://center-for-the-integration-of-id.gitbook.io/generate-documentation/~gitbook/image?url=https%3A%2F%2F2887782118-files.gitbook.io%2F%7E%2Ffiles%2Fv0%2Fb%2Fgitbook-x-prod.appspot.com%2Fo%2Fspaces%252FrRyeWMyPKDUxlv4sroOL%252Fuploads%252Fk8q0uLUHVO9GuxiTUbR9%252FSource-to-Staging_Staff.png%3Falt%3Dmedia%26token%3D5594e0b8-b564-4f67-9935-5682ef0168e5&width=768&dpr=4&quality=100&sign=d4ffda20&sv=2)