Updating STEP Records

Updating STEP Records
Automating STEP Table Maintenance

The database import/append feature of the STEP database combines new information with information already in STEP. New features for STEP_200 facilitate combining records from various sources into a unified format. They reflect the requirements of appending a new year of data to existing STEP tables. They also align with the simplified ProgramServicePeriod table record requirements (explained further elsewhere). These improvements reduce the burden of creating and maintaining STEP records. Building on existing concepts, they establish a reliable flexible method for processing records to update a STEP database. This relieves local systems from the annual requirement of generating a complete historical STEP dataset, and so also the implicit requirement to maintain past years’ history in any other electronic database. Once a STEP database is begun, each subsequent year of STEP information may be added to the prior year STEP file to produce the new annual STEP database. Once the rules of this automated procedure are understood, the procedure will become a power tool in the hands of the administrative record keeper.

Two alternative approaches to appending to STEP

STEP must incorporate new data records as an extension of existing records in the database. A most important decision becomes how much of the original submission record should be retained in STEP. There are two mutually exclusive approaches:

Treat the imported record as an update transaction to revise existing records in STEP. This is referred to as the merge option.
Treat the imported record as a discrete record in STEP and as such retain the import record identity into the active data tables. This is referred to as the append-only option.

Consequences of Alternative Approaches

A cautious user wants to be able to "roll back" inadvertently erroneous transactions. Combining new records with existing records (alternative #1, the merge option) will create some records in the database that will not match any source records, so such cautious users may find greater comfort in the second approach (alternative #2, the append-only option) . However, the append-only option proliferates the number of records in the database and stands in opposition to the reduction of record counts possible through combination of the import-event records with existing records. The append-only option also requires that inconsistencies in records from different sources be resolved by intervention through the EditCheckErrorLog correction process, while the merge option is more accepting of discrepant record dates, presuming that each is likely correct in its own way, and so combines them according to the rules described in the table below.

Simple Data Version Control

In the interest of simplicity and security, it is strongly suggested that users make a backup copy of the back-end database file (STEP_???B.mdb) before any batch update. Only after the updated data table quality is verified should the backup copy be discarded or overwritten. This fundamental "roll-back" protection is a user responsibility, and is conceptually simple and complete, regardless of update procedure. In most circumstances, the small cost of redundant storage used by this "save a copy" approach will be more than offset by the savings in internal complexity and operational training that would be required by attempting a programmed "roll-back" feature. With greater logical complexity comes a greater likelihood of unexpected results and corresponding frustration for all involved. Let us strive for simplicity. Keep a recent file backup.

Local Control with Statewide Consistency

Local circumstances may make one or the other approach more beneficial for different local purposes in constructing the STEP file. Local expertise may suggest one or another approach to achieve a desired effect, depending on the specific circumstances of the local STEP file and the data resources available. Since the summary reporting requirements may be met with either form of data storage, this option may be left to local decisions. The default operational approach is to combine import records with existing records (the merge option). When the Department receives SPD data files, records will be processed into new submission tables at the Department in the same form as they are stored in the local district STEP database, to assure that all reported data are processed through the same procedures into the same status summaries for State analysis, research, and public summary reporting as are available locally by using the STEP application.

Import/Append Record Handling

Figure 1 – Decision Logic for Program Service Period Record Merge (Approach #1)

The preceding flow chart expresses the general logic for program service period (PSP) records that is also described in latter part of the following table. These are mutually supporting descriptions of the same process. This PSP processing logic is challenging to communicate clearly, so it is presented in both ways. The other update rules are relatively more straightforward. Some simplifications for clarity may appear inconsistent or incomplete. If greater precision and completeness is required, please refer to the program code implementation for the final word on the logic.

Record Type		Merge (Approach #1)	Append (Approach #2)
Student Demographic	Matching Key	BEDSOfResponsibility and StudentID
	On Match	Replace existing field values with new values where new values are not Null.	Disregard import rows matched in StudentDemographicData table. Write EditCheckErrorLog record for the attempted import.
	On No Match	Append new (unmatched) record
Student Assessment	Matching Key:	BEDSOfResponsibility, StudentID, Measure, DateOfAdministration year, and DateOfAdministration month
	On Match	Replace existing field values with new values where new values are not Null.	Disregard import records matched in StudentAssessmentData table. Write EditCheckErrorLog record for the attempted import.
	On No Match	Append new (unmatched) record
Program Service Period	Matching Key	BEDSOfResponsibility, StudentID, PSID, and (for School PSP) BEDSOfService
(PSP records that may extend across school years)	On Match	If an import record begins before the most recent matching working record begins, then write EditCheckErrorLog record for the attempted import. If an import record overlaps or abuts the most recent matching working record, that working record is updated with EndingDate and Reason (if any) from the import record. Enrollment PSP with Reason 85 (Earned IEP Diploma) will not have this information overwritten. Instead, overlapping import records that would cause this are an error, and abutting records are appended, rather than merged. Additional processing handles overlapping enrollment records that may result from this procedure (see note following table).	Disregard import records that overlap¹ ProgramServicePeriods records in time. Write EditCheckErrorLog record for the attempted import.
	On No Match	Append new (unmatched) record
(Annual PSP records that may NOT extend across school years)	On Match	Same as for continuous PSP records (those that may extend across school years), except that annual PSP records may not cross the June30 – July 1 change of reporting school year. Records that abut on that point in the year will not be combined. Import records also may not span two reporting school years. Import records for prior reporting years will not be merged if they overlap with matching working table records. Circumstances where a record cannot be merged generate EditCheckErrorLog records.	Disregard import records that overlap¹ ProgramServicePeriods records in time. Write EditCheckErrorLog record for the attempted import.
	On No Match	Append new (unmatched) record

^{1 A PSP record overlaps another PSP record with the same Matching Key if the first record has BeginningDate or EndingDate in range between the second record BeginningDate and EndingDate (inclusive of both). A Null date is the most recent possible date, and can only exist as an EndingDate.

^{2 Abutting records are identified from a list sorted on Matching Key and BeginningDate. Consider record_1 to be a record in the sorted list with a following record called record_2. If record_1 EndingDate = record_2 BeginningDate – 1 day, then record_2 abuts record_1. Beginning Date begins at midnight of the beginning of BeginningDate, and EndingDate ends at midnight at the end of EndingDate.

Note:
After the PSP merge process has completed, the resulting working records are updated to close certain records that overlap¹ for a student. Enrollment records are the primary focus of this activity. For each student that has enrollment records that overlap, EndingDate for the overlapping record that has the earlier BeginningDate is set to the day prior to BeginningDate of the later overlapping enrollment record. The enrollment record assigned the new Ending Date is also assigned Reason 153 (Transfer to Another School Within District). Any other School PSP records for the same student that also have the same BEDSOfService as the newly closed Enrollment PSP and which are not closed with a date before the new EndingDate are also assigned the new EndingDate. If these rules do not properly handle circumstances for some students, those students’ records should be appended in a separate activity rather than merged along with records for which the merge algorithm is appropriate. Such non-standard cases must have their particular circumstances explicitly detailed in their import record set.

Data Validity Errors and New Software Versions
Ongoing development and examination of collected records in detail will very probably identify possible error conditions that previous software accepts. True data that conforms with the intention of the STEP manual and which is stored in old systems will pass all future edit error detection procedures, but some data previously stored and used in good faith may still require correction when scrutinized by new procedures. In some cases these errors may be evidence of difficulties in prior conversions, confusion about the nature of certain program functions, or even inadvertent malfunction of old software. Other cases may be the result of misunderstanding of the underlying intention of some rather consequential instructions or data record definitions. Newfound errors may indicate a need for an alternative approach to data transfer or conversion from other systems to STEP record form. If users have records that generate errors that seem impossible to fix, a conversation with regional or State system support staff should clarify the intended mechanism for storage of that information, or if that information is even within the scope of STEP. Please keep in mind that the error check rules and their software implementation are intended to protect the integrity of the data, and to help assure that the database contains the smallest possible number of invalid records. Cleaning up data will always be a chore, but in our estimation it is less stressful before the reports are final.
Responsibility for Data Quality
Institutions that must act as BEDSOfResponsibility institutions are responsible for the quality of the data they report. The Department makes a good-faith effort to provide economical tools that perform the tasks they are specified to perform. The more any system uses automated update procedures, the more likely it is that erroneous data may taint otherwise perfect data. Users should make every effort to assure the quality of the data before appending it into STEP. Although STEP provides many utilities to assist in identification and correction of logical inconsistencies of the data, it cannot determine the correctness of any data. Passing STEP edit checks is an assurance of logical consistency and acceptability. Accuracy, however, requires local knowledge to confirm the summary and detail reports. Please be careful. It is much easier to correct errors at the source than after the submission deadline.

Last Revised: 5/16/03}}