Comparing Extracts in PowerSchool to CALPADS ODS Option

Contents

Introduction

Under normal circumstances, tracking updates for Transactional and Effective Date processing occurs by comparing current records to those that have been stored in the CALPADS History Table after submission to CALPADS. In certain circumstances, it may be desirable to compare the extracts produced in PowerSchool to what is already stored in CALPADS. This functionality can be used to "sync up" records in CALPADS with records in PowerSchool.

Important: This function assumes that the records stored in PowerSchool are correct and any differences found in CALPADS should be corrected with the latest PowerSchool information. Great care must be used before submitting files to CALPADS when using this option.

The compare to CALPADS ODS option is available on the following reports:

• SSID Enrollment Records
• Student EL Acquisition Records
• Student Information Records
• Student Program Records
• Staff Demographic Records

Download ODS File from CALPADS

The CALPADS Operational Date Store (ODS) Extract file may be used to compare and identify differences between the CALPADS data and the data in PowerSchool.

Follow the steps below to download an ODS file from CALPADS.

1. Log into the CALPADS website.
2. Navigate to Reports > Extracts > ODS Downloads.
3. Choose SSID Enrollment ODS, Student Information ODS, Student Program ODS, or Staff Demographics ODS Download from the ODS Download Record Type pop-up menu.
4. Run the report noting the exact parameters used. You will need to run the extract in PowerSchool with these same parameters to get an accurate comparison.
5. Once the CALPADS ODS Download File has completed processing, save the ODS result file.

Upload ODS File to PowerSchool

Follow the steps below to upload the ODS file into PowerSchool.

1. Log into PowerSchool.
2. Navigate to Start Page > Reports (State Reports) > CALPADS ODS File Upload.
3. Click Choose File and choose the ODS text file with the extension .txt that was saved from the CALPADS application.
4. Choose the Report that is in the ODS text file to be uploaded.
5. Click Import. Data from the CALPADS ODS file is uploaded into the PowerSchool database.

Run Compare to CALPADS ODS Function

The CALPADS Compare to State function compares data stored in the PowerSchool database with records from the CALPADS ODS. PowerSchool returns a file with records to be updated, added, or deleted from the CALPADS ODS.

To automatically locate and correct data errors using the Compare to State function:

1. Navigate to Start Page > Reports > Run Reports > State Reports > CALPADS Reports (select report to compare to the file uploaded).
2. Run the CALPADS report with the following values:

Use: Choose "Current School Only" or "District Wide," depending on the option used to generate the CALPADS ODS File.

Students: All Students. Do not run the report for a selection of students.

Start Date: Enter the start date used when generating the CALPADS ODS File

End Date: Enter the end date used when generating the CALPADS ODS File

Report Mode: Submission mode

Compare to CALPADS ODS File: Select the checkbox to use the Compare to ODS functionality. Note: When Compare to State ODS File is selected on SSID Enrollment Records, the following warning message appears. Select the checkbox to acknowledge that you have read the warning.

Bypass Validation: Yes

WARNING: The School Entry Date must be populated for every enrollment for every student. If the School Entry Date is blank, the student record will be missing from the SSID Enrollment Records report and deleted from CALPADS. To automatically populate the School Entry Date field for all students, either run the Update Mobility Script for each school, or perform an import. Otherwise, manually enter a value for each student.

3. Click Submit. A caution message appears regarding the Compare to State function:

CAUTION: The Compare to State function in PowerSchool must be run for the same date range and school/district parameters that were used for the SSID Enrollment ODS Download file in the CALPADS application.

Failure to use the same parameters can result in improper reconciliation and possible deletion of data from CALPADS.

If you are unsure, cancel this operation and verify before proceeding.  In all cases, review the file produced prior to submitting to CALPADS.

4. Click OK or Cancel. If you click OK, the report identifies matching records between the PowerSchool database and the ODS file based on whether all the operational keys match.
5. Click the link Click to Download Result File. Review the file for the following types of records:

[null] (Add or Update):

• Add: Records from PowerSchool with no match in the ODS file. When the result file is submitted to CALPADS, these records are added to the CALPADS ODS.
• Update: Records with a match in PowerSchool and the ODS file, where non-operational key field values are updated. When the result file is submitted, these records are updated in the CALPADS ODS.

D (Delete):

• Delete records without an accompanying [null] record. Records from ODS file with no match in PowerSchool. When the result file is submitted to CALPADS, these records are deleted from the CALPADS ODS. Before submitting, verify that these records should be deleted.
• Delete records with an accompanying [null] record. Records from ODS file with a different operational key from what is in PowerSchool.

Note: It is recommended to open the result file in Excel and sort by SSID so that all records for a student are grouped together.

6. Submit the result file to CALPADS.
7. Accept Updates in PowerSchool. See Tracking CALPADS Submission Updates for details.