Source System Reference Data Mapping Utility

Overview

The Staging.SourceSystemReferenceData table is used by the Generate application to map state values for option sets to the corresponding CEDS values. When starting the ETL work for a new file or set of files this mapping is critical for getting accurate results in the data warehouse and submission reports. This utility will show the mappings for all the file specifications in a report group. The first parameter in this stored procedure is the Generate report group, they are listed at the end of this document with their associated file specifications for reference.

There are 3 parameters for the stored procedure:

@generateReportGroup = The report group name used by the Generate application.
@schoolYear = The 4 digit School Year to limit the mappings to.
@showUnmappedOnly = Show only the results for CEDS values that have no corresponding state value mapped to them

If the generatReportGroup or schoolYear parameters are missing the stored procedure will not execute.

The last parameter is used to show all the mappings in that Report Group using @showUnmappedOnly = 0, or only show the CEDS values that do not have a corresponding mapped value using @showUnmappedOnly = 1. It defaults to showing all the mappings.

Sample Execution

exec [Utilities].[Check_SourceSystemReferenceData_Mapping] 'childcount', '2023', 0 -- This will show all mappings for the Child Count report group (files 002 and 089) for School Year 2023

exec [Utilities].[Check_SourceSystemReferenceData_Mapping] 'discipline', '2023', 1 -- This will show only the CEDS unmapped values for the Discipline report group (files 005, 006, 007, 086, 088, 143, and 144) for School Year 2023

Report Group Reference

Generate Report Groups

File Specs

Assessment

175, 178, 179, 185, 188, 189, 113, 125, 126, 137, 138, 139, 142

ChildCount

002,089

ChronicAbsenteeism

195