Downloadable 2015–16 Student-Level Data File
March 4, 2016
Page 2
March 4, 2016
Dear CALPADS Administrator:
DOWNLOADABLE 2015–16STUDENT-LEVEL dATA FILE
The purpose of this letter is to inform you that the California English Language Development Test (CELDT)/California Longitudinal Pupil Achievement Data System (CALPADS) Comparison Data file is available to download today. The file provides a comparison between the data on the CELDT answer book and the data obtained from the CALPADS OperationalData Store, which was extracted on January 12, 2016. The file contains all students who participated in the 2015–16 CELDT during the annual assessment (AA) window from July 1, 2015 through October 31, 2015. Assessments shipped after the cutoff for the AA window are not included.
The CELDT/CALPADS comparison data file is only available to LEA CALPADS administrators. Due to the current heightened scrutiny over personally identifiable student level data, CALPADS administrators must only extract information pertaining to students enrolled at their own districts. The CELDT/CALPADS comparison data file must be securely destroyed after information pertaining to students enrolled at their own district has been extracted.
The following fields will be extracted from the CALPADS ODS on April 1, 2016to generate the 2015–16 CELDT and Title III Accountability Reports. The table below provides how data are used in CELDT and Title III Accountability reporting. If the data are correct in CALPADS, then no action needs to be taken. Please make corrections in the CALPADS ODS before the data extraction date for the following demographic data:
CALPADS Field Number / CALPADS Field Name / Field Use1.06 / School of Attendance NPS / CELDT reporting
2.13 / Student Legal Last Name / Record Matching
2.18 / Student Birth Date / Record Matching
2.19 / Student Gender Code / CELDT reporting
2.45 / Student Initial U.S. School Enrollment Date / Establishing Title III Accountability AMAO 2 cohorts
3.13 / Education Program Code: 135 Migrant / CELDT reporting
3.21 / Primary Disability Code / CELDT reporting
3.22 / District of Special Education Accountability / CELDT reporting
12.15 / Primary Language Code / CELDT reporting
Please refer to the following information to access the CELDT/CALPADS comparison data file:
Attachment 1: PC User Instructions for Downloading the Student-level Data File
Attachment 2: Business Rules for Using CALPADS ODS Data for 2015–16 CELDT and Title III Accountability Reporting
Questions regarding the CALPADS data corrections should be directed to the CALPADS Service Desk by submitting a service request using the CALPADS Service Request Form on the CDE Web page at or bye-mail at
.
For questions regarding this letter please contact Stephanie Woo, Education Research and Evaluation Consultant, Data Visualization and Reporting Office, by phone at
916-323-3071 or by e-mail at .
Sincerely,
/s/
Jonathan Isler, PhD
Education Research Evaluation Administrator
Data Visualization and Reporting Unit
Analysis, Measurement,and Accountability ReportingDivision
JI/sw
California Department of Education - Analysis, Measurement, and Accountability Reporting Division
Attachment 1
Page 1 of 3
PC User InstructionsforDownloading theStudent-levelData File
Thisdocumentprovidesinstructionsforaccessinganddownloading theCalifornia English LanguageDevelopmentTest(CELDT)/California LongitudinalPupilAchievementData System(CALPADS) ComparisonDataFilefor PC users.For securityreasons,the decryptionpassphrase, neededinStep12,wassentinaseparatee-mail.Pleaseensure you have thedecryption passphrasebeforebeginning.
For PC Users (Internet Explorer11):
1. Go tothe CaliforniaDepartmentofEducation(CDE) exFilesFileTransfer system
Websiteat
2. In the ProjectAuthenticationbox, enter theProjectCode(case-sensitive):CompCeldt15
3. SelecttheSubmit Codebutton.
4. In theUserAuthenticationbox,youwill be promptedtoentertheUpload/Download Password (case-sensitive):!15ADriveR16!
5. SelecttheSubmitPasswordbutton.
California Department of Education - Analysis, Measurement, and Accountability Reporting Division
Attachment 1
Page 1 of 3
6. Locatethe Tab-delimited TXT filetodownloadandselectiton the File Listingpage.Note: Due tothesizeofthefile,DO NOT IMPORT INTO MICROSOFT EXCEL. The tab-delimited TXT file may be imported into your database software (e.g. Access, SQL, etc.)
7. SelecttheDownloadFile option.
8. Youwill be prompted bythe Websitetodownloadthefile.SelectOK.
9. After downloadingthefile,youwill be prompted toeither Runor Save thefile.
SelecttheRunbutton.
10.This InternetSecurity warningmaypopup.Ifso,selecttheRunbutton.
11.Select Browse tosavethefileinyour desiredlocation.
12.Youwill be promptedforadecryption passphrase.For securityreasons,the decryptionpassphrase was sentinaseparatee-mail.Enter thepassphrasein the boxandselectOK.
13.Openthefileinthelocationyousaveditin.
14.Download thefilerecordlayoutPDFfileontheFile Listing page.Thefilerecord layoutwill provide explanations anddefinitions ofthefieldscontainedinthe CELDT/CALPADSComparisonfile.
California Department of Education - Analysis, Measurement, and Accountability Reporting Division
Attachment 2
Business Rules for Using CALPADS Data for 2015–16 CELDT and Title III Accountability Reporting
Objective
This document provides detailed business rules for acquiring and integrating California Longitudinal Pupil Achievement Data System (CALPADS) data in order to process and generate California English Language Development Test (CELDT) and Title III Accountability reports. In addition, this document explains the content of the CELDT/CALPADS comparison data file used by local educational agencies (LEAs) to verify and correct their CALPADS data.
Background
Title III of the Elementary and Secondary Education Act provides supplemental funding to LEAs to help English learners (ELs) and immigrant students attain English language proficiency (ELP). LEAs receiving Title III funding are required to meet Annual Measurable Achievement Objectives (AMAOs) each year. Title III AMAO target calculations are based upon proficiency measurements and other demographic variables captured in the CELDT Answer Book. Starting with the 2013–14 school year, the CDE began using data elements from CALPADS for the processing of Title III Accountability reports. Specifically, the Student Birth Date and Student Initial U.S. School Enrollment Date fields that were previously collected from the CELDT Answer Book are now derived from CALPADS. Beginning in 2014–15, and continuing with the 2015–16 academic year, the CDE extracts additional demographic fields from CALPADS that were formerly captured in the CELDT Answer Book and includes them in the reporting of the summary CELDT test results. This is consistent with the overall goal of using CALPADS instead of the CELDT as the primary source for student demographic information.
Data Source
CALPADS is a longitudinal data system used to maintain student-level data, including student demographics, course data, discipline, assessments, staff assignments, and other information for state and federal reporting. CALPADS provides schools and LEAs with the opportunity to collect and correct select data directly online, instead of making the request through Educational Data Systems (EDS).
Scope
In order to augment the CELDT score file with student data from CALPADS, a student record from the CELDT score file must be matched to that same student’s record in CALPADS. The business rules outlined in this document are designed to provide the logic for determining a record match and illustrate the methods for joining corresponding student records between the two data sources. An output file is generated containing matched student records and corresponding data elements from both the CELDT and CALPADS. This comparison file is intended to help LEAs reconcile any discrepancies and make corrections to their student level records in CALPADS or through the CELDT Data Review Module (DRM).
The following table identifies the data elements extracted from CALPADS and how the fields are used in CELDT and Title III accountability reporting.
CALPADS Field Number / CALPADS Field Name / Field Use1.04 / Reporting LEA / Record Matching
1.05 / School of Attendance / Record Matching
1.08 / SSID / Record Matching
2.13 / Student Legal Last Name / Record Matching
2.18 / Student Birth Date / Record Matching/CELDT reporting
2.19 / Student Gender Code / CELDT reporting
2.45 / Student Initial US School Enrollment Date / Establishing student cohorts in AMAO 2
12.15 / Primary Language Code / CELDT reporting
3.21 / Primary Disability Code / CELDT reporting
3.13 / Education Program Code: 135 Migrant / CELDT reporting
1.06 / School of Attendance NPS / CELDT reporting
3.22 / District of Special Education Accountability / CELDT reporting
California Department of Education - Analysis, Measurement, and Accountability Reporting Division
Business Rules for Using CALPADS Data for 2015–16 CELDT and Title III Accountability Reporting
Business Rules
The record matching process is governed by a set of business rules. The rules describe the specific data processing steps used in matching corresponding student records and provide the business logic used in evaluating and selecting the best record match.
- Pre-processing CELDT records
- Matching CELDT records to CALPADS enrollment records
- Matching the selected student records to CALPADS Student Initial U.S. School Enrollment Date
- Matching selected student enrollment records to CALPADS program records
- Matching selected student records to CALPADS primary language records
- Pre-processing CELDT records
- County-District-School Code Considerations
The CDS code is a 14-digit code used to identify a school, district, and county.
The14-digit CDS code is the official, unique identification of a school within California. The first two digits identify the county, the next five digits identify the school district, and the last seven digits identify the school.The format of the code is as follows: The 2-digit County Code comes first, then the 5-digit District Code, followed by the 7-digit School Code, with hyphens separating the three distinct codes. The image below depicts the CDS code format:
The CELDT/CALPADS comparison was first processed on matching the full 14-digit CDS code. This means that the CDS code in the CALPADS enrollment file needed to match the CDS code in the CELDT Answer Book file to be considered a match. After the initial processing, the remaining CELDT records that were not matched to CALPADS data were processed by matching only the school code in the CALPADS enrollment file to the CELDT Answer Book file. The processing on school code was done to accommodate the following scenario:
- Merged Districts or other reorganizations: Schools that are merged, have district reorganizations, or changes in charter school authorizers may not have the same district code in the CELDT Answer Book as the CALPADS enrollment data, but the school code is the same. The data are matched on the school code. However, the district where the student took the CELDT will be used in the accountability roll-ups.
- Retired SSIDs
The SSID assigned to a student may have been retired as the result of a multiple ID (MID) anomaly resolution and replaced with a new SSID; however, the retired SSID may be reported on the CELDT Answer Book file. To ensure the correct information is extracted from CALPADS for the correct student, any retired SSIDs reported in the CELDT Answer Book file are replaced with the new SSIDs prior to the matching process.
- Matching CELDT records to CALPADS enrollment records
In 2015-16, the CDE is implementing a fuzzy match process for the CELDT CALPADS data merge. Fuzzy matching is an advanced process that identifies similarities between data sets. The process compares student demographic data types of any length in a field to find non-exact matches. This checking process will improve data accuracy by identifying and eliminating duplicate records.
California Department of Education - Analysis, Measurement, and Accountability Reporting Division
Understanding the Fuzzy Match
Fuzzy matching, or approximate string matching, is the technique of finding strings that match a pattern approximately (rather than exactly) based on Soundex. Soundex is a phonetic algorithm for indexing names by sound, as pronounced in English. The purpose for Soundex is for homophones to be encoded to the same representation so that they can be matched despite minor differences in spelling.
The CDE will be implementing Soundex to compare the last name field from CALPADS to the student answer document. The table below illustrates the use of Soundex technology in evaluating matches.
CALPADS Last Name / Student Answer Book Last Name / Match (Yes/No)Smith / Smythe / Yes
Smythe / Smith / Yes
Smith / Doe / No
The following fields from the CELDT answer book will be used for the fuzzy merging process:
- CDS
- SSID
- Last Name – Based on Soundex
- Date of Birth
The fuzzy match process is a two-step process to obtain a student’s enrollment record.
1.A first match of CDS code, SSID, Last Name, and Date of Birth will result in a subset. This subset will then be removed from further comparison.
2.A second match of CDS code, Last Name, and Date of Birth will result in another subset. This subset will then be removed from further comparison.
Step 2 will help with matches where the SSID is either blank or invalid. The resulting match will provide an SSID that can be provided back to the field. A check will be implemented to ensure that the results from steps 1 and 2 will not yield duplicate records. If multiple records in the answer book extract contain duplicate records than the match results would result in identical data.
A new Match Flag Field #26 is included in the 2015–16 CELDT/CALPADS Comparison Data File Record Layout to indicate the resulting match. See Record Layout for 2015–16 CELDT/CALPADS Comparison Data File in Appendix A for details.
- If a single student enrollment record is (1) at the same school as where the student took the CELDT, and (2) the enrollment record overlaps with the test date, then it is selected as a match to the CELDT record.
In the following example, Enrollment Record 1 is selected as the match to the CELDT record.
- If the student has more than one enrollment record in CALPADS at the same school where he or she took the CELDT, but the test date does not overlap with any of the enrollment records, then the closest enrollment record to the CELDT test date is selected. Only the enrollment records for the same school where the student took the CELDT are considered for matching.
In the following example, Enrollment Record 1 is selected as the match to the CELDT record.
- If the student has more than one enrollment record in CALPADS at the same school where he or she took the CELDT and the two enrollment records have the same date difference from the test date, but the test date does not overlap with the enrollment records, then the enrollment record immediately following the CELDT test date is selected. This ensures that the most recent enrollment dates are used.
In the following example, Enrollment Record 2 is selected as the match to the CELDT data.
- Matching the selected student record to CALPADS Student Initial U.S. School Enrollment Date
Using the SSID obtained from section 2. “Matching CELDT records to CALPADS enrollment records” the Student Initial U.S. School Enrollment Date is selected from the CALPADS demographic records.
There should never be two demographic records for the same data with overlapping effective start and end dates. Using the table below as an example, record one should never have an effective end date that is later than the effective start date of record two.
Record / SSID / Student Initial U.S. Enrollment Date / Effective Start Date / Effective End Date1 / 1111111111 / 8/1/2008 / 1/15/2015 / 9/1/2015
2 / 1111111111 / 8/10/2008 / 9/2/2015 / 12/31/2015
- If a single Student Initial U.S. Enrollment Date record exists then it is selected as a match to the CELDT record.
- If a student has two or more CALPADS Student Initial U.S. Enrollment Date Record, then the record that is closest to the CELDT Test Date will be selected.
In the following example, Record 1 is selected as a match to the CELDT record.
- Matching selected student enrollment records to CALPADS program records
The Primary Disability Code, District of Special Education Accountability, and Migrant Program Participation are selected from the CALPADS program records using SSID, CDS code, and enrollment start and withdrawal dates from the CALPADS enrollment data and SSID, CDS code, and program membership start and end dates from the CALPADS program data.
A.If only one CALPADS program record is (1), at the same school where the student took the CELDT, and (2) has a membership date that overlaps with the CALPADS enrollment period, then that program record will be selected as a match. This will happen regardless of when the test date occurs.
Program Record 2 is selected in each of the following examples as a match to the CELDT/CALPADS enrollment data.